کاراکترهای وایلدکارد (Wildcard) در اکسل [با آموزش ویدئویی]

فقط ۳ کاراکتر وایلدکارد (Wildcard) در اکسل وجود دارد (ستاره (asterisk)، علامت سؤال (question mark)، و مدک (tilde)) و با استفاده از این موارد می‌توان کارهای زیادی انجام داد.

در این آموزش با چهار مثال به شما نشان می‌دهیم که چگونه کاراکترهای Wildcard موجب صرفه‌جویی در وقت و سهولت کارها می‌شود.

کاراکترهای وایلدکارد (Wildcard) در اکسل – معرفی

وایلدکارها کاراکترهای ویژه‌ای هستند که می‌توانند جای هر کاراکتری بنشینند (از این رو است وایلدکارد (Wildcard) نامیده می‌شوند).

۳ کاراکتر وایلدکارد در اکسل وجود دارد:

1. * (ستاره) – این کاراکتر بیانگر هر تعداد از کاراکترها است. به عنوان مثال، Ex*‎ می‌تواند به معنای Excel، Excels، Example، Expert و غیره باشد.
2. ؟ (علامت سؤال) – این کاراکتر بیانگر تنها یک کاراکتر است. به عنوان مثال، c?r می‌تواند به معنای car یا cur باشد.
3. ~ (مدک) – از این کاراکتر برای شناسایی یک کاراکتر وایلدکارد (~، *، ?) در متن استفاده می‌شود. به عنوان مثال، فرض کنید می‌خواهید دقیقاً عبارت Excel*‎ را در یک لیست پیدا کنید. اگر از Excel*‎ به عنوان رشته‌ی جست‌وجو استفاده می‌کنید، هر کلمه‌ای را که در ابتدا Excel دارد و به دنبال آن تعدادی کاراکتر وجود دارد (مانند Excel، Excels، Excellent)، به شما می‌دهد‌. برای این‌که به طور خاص به دنبال خود Excel*‎ باشیم، باید از ~ استفاده کنیم؛ بنابراین رشته‌ی جستجوی ما Excel~*‎ خواهد بود. در این‌جا، وجود ~ تضمین می‌کند که اکسل کاراکتر ستاره را همان‌طور که هست می‌خواند، و آن را به عنوان وایلدکارد در نظر نمی‌گیرد.

توجه: شاید با موقعیت‌هایی که نیاز به استفاده از کاراکتر ~ باشد، زیاد مواجه نشوید. با این وجود، دانستن این ویژگی خوب است.

اکنون چهار مثال عالی را می‌گذرانیم که وایلدکاردها همه‌ی کارهای سنگین را انجام می‌دهند.

کاراکترهای وایلدکارد (Wildcard) در اکسل – مثال‌ها

حال اجازه دهید چهار مثال عملی را بررسی کنیم که در آن وایلدکاردها در اکسل می‌توانند بسیار مفید باشند:

1. فیلتر کردن داده‌ها با استفاده از وایلدکارد
2. جست‌وجوی تقریبی با استفاده از کاراکترهای وایلدکارد و VLOOKUP
3. جست‌وجو و جایگزینی مطابقت‌های ناقص
4. شمارش سلول‌های غیرخالی دارای متن

#1 فیلتر کردن داده‌ها با استفاده از وایلدکارد

کاراکترهای وایلدکارد هنگام سروکار داشتن با مجموعه داده‌های عظیم، وقتی که می‌خواهید داده‌ها را بر اساس شرط‌هایی فیلتر کنید، بسیار مفید است.

فرض کنید دیتابیسی مطابق شکل زیر دارید:

می‌توانید از کاراکتر وایلدکارد ستاره (*) در فیلتر داده استفاده کنید تا لیستی از شرکت‌هایی که با حرف A شروع می‌شوند، تهیه کنید.

در اینجا نحوه‌ی انجام این کار آمده است:

• سلول‌هایی را که می‌خواهید فیلتر کنید، انتخاب کنید.
• بروید به Data -> Sort and Filter -> Filter (یا با استفاده از میانبر صفحه‌کلید – Ctrl+Shift+L).
• بر روی نماد فیلتر در سلول عنوان (header) کلیک کنید.
  در فیلد موجود (زیر گزینه‌ی Text Filter)، A*‎ را تایپ کنید.
• OK را بزنید.

این کار فوراً نتایج را فیلتر کرده و به شما ۳ نام می‌دهد – ABC Ltd. ، Amazon.com و Apple Stores.

چگونه کار می‌کند؟ – هنگامی که ستاره (*) را بعد از A اضافه می‌کنید، اکسل هر چیزی را که با A شروع می‌شود فیلتر می‌کند. دلیل این است که یک ستاره (یک کاراکتر Wildcard اکسل) می‌تواند هر تعداد از کاراکترها را نشان دهد.

حال با همین روش می‌توانید از معیارهای مختلفی برای فیلتر نتایج استفاده کنید.

به عنوان مثال، اگر می‌خواهید شرکت‌هایی را که با حرف A شروع می‌شوند حاوی حرف C در آن هستند فیلتر کنید، از رشته‌ی A*C استفاده کنید. این کار تنها ۲ نتیجه به شما می‌دهد – ABC Ltd. و Amazon.com.

اگر به جای آن از A?C استفاده کنید، فقط نتیجه ABC Ltd را دریافت خواهید کرد (زیرا فقط یک کاراکتر بین “A” و “C” مجاز است)

توجه: همین کار را می‌توان هنگام استفاده از فیلترهای پیشرفته‌ی اکسل نیز به کار برد.

#2 جست‌وجوی تقریبی با استفاده از کاراکترهای وایلدکارد و VLOOKUP

وقتی باید یک مقدار را در یک لیست جست‌وجو کنید که مطابقت دقیق ندارد، جست‌وجوی تقریبی نیاز است.

به عنوان مثال، فرض کنید شما مجموعه داده‌ای مانند شکل زیر در اختیار دارید و می‌خواهید شرکت ABC را در این لیست جست‌وجو کنید، اما این لیست به جای ABC، ABC Ltd را دارد.

در این حالت شما نمی‌توانید از تابع معمول VLOOKUP استفاده کنید زیرا مقدار جست‌وجو تطابق دقیقی ندارد.

اگر از VLOOKUP برای یک مطابقت تقریبی استفاده کنید، نتایج اشتباهی به شما می‌دهد.

با این وجود، می‌توانید از کاراکترهای وایلدکارد در تابع VLOOKUP استفاده کنید تا نتایج مناسب را به دست آورید:

فرمول زیر را در سلول D۲ وارد کرده و آن را درسلول‌های دیگر کپی کنید:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)

این فرمول چگونه کار می‌کند؟

در فرمول فوق، به جای استفاده از مقدار جست‌وجو همانطوری که هست، کاراکتر وایلدکارد ستاره در هر دو طرف آن قرار گرفته – “*”&C2&”*”

این به اکسل می‌گوید که باید هر متنی را که حاوی کلمه C۲ است جست‌وجو کند. این می‌تواند هر تعداد کاراکتر قبل یا بعد از متن در C۲ داشته باشد.

از این رو، فرمول به دنبال یک مطابقت می‌گردد و به محض این‌که یک مطابقت را پیدا کرد، آن مقدار را برمی‌گرداند.

‎#3 جست‌وجو و جایگزینی مطابقت‌های ناقص

کاراکترهای Wildcard همه‌فن‌حریف هستند!

می‌توانید از آن‌ها در فرمول‌ها پیچیده و هم‌چنین در عملکردهای اساسی مانند یافتن و جایگزینی (Find and Replace) استفاده کنید.

فرض کنید داده‌هایی مطابق شکل زیر دارید:

در داده‌های فوق، منطقه (Region) به روش‌های مختلفی وارد شده‌است (مانند North-West، North West، NorthWest).

این اغلب در داده‌های فروش اتفاق می‌افتد.

برای تمیز کردن این داده‌ها و یکجور کردن آن، می‌توانیم از کاراکترهای وایلدکارد اکسل در Find and Replace استفاده کنیم.

در اینجا نحوه‌ی انجام این کار آمده است:

• داده‌هایی را که می‌خواهید متن را پیدا کنید و جایگزین کنید، انتخاب کنید.
• به صفحه اصلی بروید -> سپس Find & Select -> حال Go to. با این کار پنجره‌ی Find and Replace باز می‌شود. (هم‌چنین می‌توانید از میانبر صفحه‌کلید استفاده کنید – Ctrl+H).
• متن زیر را در پنجره‌ی Find and Replace وارد کنید:
  • در Find what تایپ کنید: North*W*‎
  • در Replace with تایپ کنید: North-West 
• بر روی Replace All کلیک کنید.

این کار فوراً تمام فرمت‌های مختلف را تغییر داده و آنرا با North-West یکجور می‌کند.

این چطور کار می‌کند؟

در قسمت Find، ما از North*W*‎ استفاده کرده‌ایم که هر متنی را که کلمه‌ی North داشته باشد و حاوی حروف “W” در هر جایی بعد از آن باشد، پیدا خواهد کرد.

از این رو، تمام سناریوها (NorthWest، North West و North-West) را دربرمی‌گیرد.

Find and Replace همه‌ی این موارد را پیدا کرده و آن را به North-West تغییر می‌دهد و آن را یکجور می‌کند.

#4 شمارش سلول‌های غیرخالی دارای متن

می‌دانیم که شما باهوش‌تر از این هستید و می‌دانید که اکسل در حال حاضر یک تابع توکار برای انجام این کار دارد.

کاملا حق با شما است!!

این کار را می‌توان با استفاده از تابع COUNTA انجام داد.

اما… یک مشکل کوچک با آن وجود دارد.

بارها هنگام ایمپورت کردن داده‌ها یا استفاده از ورک‌شیت سایر افراد، متوجه شده‌اید که سلول‌های خالی وجود دارد در حالی که ممکن است وضع این‌گونه نباشد.

این سلول‌ها خالی به نظر می‌رسند اما ‎=””‎ در خود دارند.

مشکل این است که تابع COUNTA این را به عنوان یک سلول خالی در نظر نمی‌گیرد (آن را به عنوان متن حساب می‌کند).

مثال زیر را ببینید:

در مثال بالا، از توابع COUNTA برای پیدا کردن سلول‌هایی که خالی نیستند استفاده کرده‌ایم و اما ۱۱ را به جای ۱۰ برمی‌گرداند (اما به وضوح می‌بینید که فقط ۱۰ سلول دارای متن هستند).

دلیل، همان‌طور که اشاره کردیم، این است که A۱۱ را خالی نمی‌داند (در حالی که باید بداند).

اما اکسل این‌گونه کار می‌کند.

راه حل، استفاده از کاراکترهای وایلدکارد اکسل در فرمول است.

در زیر فرمولی با استفاده از تابع COUNTIF وجود دارد که فقط سلول‌هایی را که متن در آن دارند، شمارش می‌کند:

=COUNTIF(A1:A11,"?*")

این فرمول به اکسل می‌گوید فقط در صورتی که سلول حداقل یک کاراکتر داشته باشد عمل شمارش را انجام دهد

علامت ?* در کومبو:

• ? (علامت سؤال) تضمین می‌کند که حداقل یک کاراکتر حضور داشته باشد.
• * (ستاره) فضای لازم را برای هر تعداد کاراکتر اضافی فراهم می‌کند.

توجه: فرمول فوق در صورتی کار می‌کند که فقط مقادیر متنی در سلول‌ها وجود داشته باشد. اگر لیستی دارید که هم متن و هم عدد دارد، از فرمول زیر استفاده کنید:

=COUNTA(A1:A11)-COUNTBLANK(A1:A11)

به همین ترتیب، می‌توانید در بسیاری از توابع Excel مانند IF()‎ ، SUMIF()‎ ، AVERAGEIF()‎ و MATCH()‎ از وایلدکاردها استفاده کنید.

همچنین شایان ذکر است در حالی که می‌توانید از کاراکترهای وایلدکارد در تابع SEARCH استفاده کنید، نمی‌توانید از آن در تابع FIND استفاده کنید.

امیدوارم این مثال‌ها به شما گوشه‌هایی از کاربردهای متعدد و قدرت کاراکترهای وایلدکارد (Wildcard) در اکسل را نشان داده باشد.

اگر روش ابتکاری دیگری برای استفاده از این ویژگی دارید، آن را در بخش نظرات با ما در میان بگذارید.

آموزش ویدئویی

در ویدئوی زیر نحوه‌ی استفاده از وایلدکاردها در تابع VLOOKUP آموزش داده شده‌است (برای نمایش بهتر، گوشی خود را در حالت افقی قرار دهید).