کاراکترهای عمومی در اکسل، Wildcard همراه با نکات کاربردی

فهرست مطالب و زمان بندی ویدیو

00:00 معرفی کاراکترهای عمومی یا Wildcard در اکسل
00:13 کاربرد Wildcard در جستجو و تطبیق متن
00:18 علامت سؤال و تعیین تعداد کاراکترها
00:22 علامت ستاره و جایگزینی هر تعداد کاراکتر
02:17 کاربرد Wildcard در فرمول‌نویسی
02:23 استفاده از علامت سؤال در تابع COUNTIF

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

برای مثال، ممکن است در یک فهرست نام محصول، فقط بدانید که نام محصول با عبارت «موبایل» شروع می‌شود؛ اما مدل و مشخصات کامل آن را ندانید. در چنین شرایطی می‌توان از علامت ستاره استفاده کرد تا اکسل هر متنی را که بعد از کلمه موبایل قرار گرفته است، بپذیرد.

سه کاراکتر عمومی اصلی در Microsoft Excel عبارت‌اند از علامت ستاره، علامت سؤال و علامت مد یا Tilde. ستاره نماینده هر تعداد کاراکتر، علامت سؤال نماینده دقیقاً یک کاراکتر و Tilde برای جست‌وجوی خود علامت‌های ستاره و سؤال استفاده می‌شود.

در این آموزش جامع، کاربرد Wildcardها را در توابع COUNTIF، SUMIF، AVERAGEIF، COUNTIFS، SUMIFS، XLOOKUP، MATCH، VLOOKUP، SEARCH و FILTER بررسی می‌کنیم. همچنین با استفاده از کاراکترهای عمومی در Find and Replace، فیلتر اطلاعات، قالب‌بندی شرطی، اعتبارسنجی داده و مثال‌های واقعی آشنا می‌شوید.

کاراکترهای عمومی یا Wildcard در اکسل چیست؟

Wildcard به علامتی گفته می‌شود که می‌تواند به‌جای بخشی از یک متن قرار بگیرد. این قابلیت زمانی کاربرد دارد که متن کامل را نمی‌دانید یا می‌خواهید چند مقدار دارای یک الگوی مشترک را پیدا کنید.

برای مثال، معیار زیر تمام متن‌هایی را پیدا می‌کند که با عبارت «تهران» شروع شده‌اند:

="تهران*"

علامت ستاره در این معیار می‌تواند نماینده هیچ کاراکتر، یک کاراکتر یا تعداد زیادی کاراکتر باشد. بنابراین عبارت‌های «تهران»، «تهران مرکزی» و «تهران منطقه 1» با این الگو مطابقت دارند.

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

کاربردهای اصلی کاراکترهای عمومی در اکسل

• پیداکردن متن‌هایی که با عبارت مشخصی شروع می‌شوند.
• پیداکردن متن‌هایی که به عبارت مشخصی ختم می‌شوند.
• پیداکردن متن‌هایی که یک کلمه را در هر بخش خود دارند.
• شمارش کدهایی با ساختار مشابه.
• جمع‌کردن مبالغ مربوط به محصولات یا گروه‌های مشابه.
• محاسبه میانگین مقادیری که معیار متنی مشخصی دارند.
• جست‌وجوی بخشی از نام محصول یا مشتری.
• شناسایی داده‌های ناقص یا دارای الگوی اشتباه.
• جست‌وجو و جایگزینی گروهی اطلاعات.
• فیلترکردن داده‌ها براساس الگوی متنی.
• پیداکردن خود علامت‌های ستاره و سؤال در متن.
• ساخت معیارهای پویا با استفاده از آدرس سلول.

انواع کاراکترهای عمومی در اکسل

اکسل سه علامت اصلی برای جست‌وجوی عمومی دارد:

• علامت ستاره برای جایگزینی صفر، یک یا چند کاراکتر
• علامت سؤال برای جایگزینی دقیقاً یک کاراکتر
• علامت Tilde برای جست‌وجوی خود علامت ستاره، سؤال یا Tilde

علامت ستاره

*

علامت ستاره نماینده هر تعداد کاراکتر است. این تعداد می‌تواند صفر، یک یا بیشتر باشد.

علامت سؤال

?

علامت سؤال دقیقاً نماینده یک کاراکتر است.

علامت Tilde

~

علامت Tilde باعث می‌شود کاراکتر بعد از آن به‌عنوان یک علامت واقعی در نظر گرفته شود، نه یک Wildcard.

علامت ستاره در اکسل

ستاره پرکاربردترین کاراکتر عمومی در Excel است. این علامت می‌تواند به‌جای صفر، یک یا تعداد نامحدودی کاراکتر قرار بگیرد.

اگر معیار شامل یک ستاره بعد از متن باشد، اکسل مقادیری را پیدا می‌کند که با متن موردنظر شروع شده‌اند:

="علی*"

این الگو می‌تواند با مقادیر زیر مطابقت داشته باشد:

• علی
• علی رضایی
• علی اکبری
• علی‌پور

پیداکردن متن‌هایی که با یک عبارت شروع می‌شوند

برای شناسایی متن‌هایی که با یک کلمه مشخص شروع می‌شوند، ستاره را بعد از کلمه قرار دهید.

="فروش*"

این معیار مقادیری مانند «فروش»، «فروش داخلی»، «فروش آنلاین» و «فروش ویژه» را پیدا می‌کند.

شمارش نام‌هایی که با علی شروع می‌شوند

=COUNTIF(A2:A100,"علی*")

شمارش کدهایی که با PRD شروع می‌شوند

=COUNTIF(A2:A100,"PRD*")

جمع فروش محصولات دارای پیشوند موبایل

اگر نام محصول در ستون A و مبلغ فروش در ستون B قرار داشته باشد:

=SUMIF(A2:A100,"موبایل*",B2:B100)

پیداکردن متن‌هایی که به یک عبارت ختم می‌شوند

برای پیدا کردن متن‌هایی که با عبارت مشخصی تمام می‌شوند، ستاره را قبل از عبارت قرار دهید.

="*تهران"

این معیار می‌تواند مقادیری مانند «شعبه تهران»، «نمایندگی تهران» و «مرکز تهران» را پیدا کند.

شمارش نام خانوادگی‌هایی که به پور ختم می‌شوند

=COUNTIF(A2:A100,"*پور")

شمارش فایل‌هایی با پسوند xlsx

=COUNTIF(A2:A100,"*.xlsx")

جمع مبلغ سفارش‌های مربوط به شعبه تهران

=SUMIF(A2:A100,"*تهران",B2:B100)

پیداکردن متن‌هایی که شامل یک عبارت هستند

برای پیدا کردن متن‌هایی که یک عبارت را در هر بخش خود دارند، ستاره را در ابتدا و انتهای عبارت قرار دهید.

="*ویژه*"

این معیار با متن‌هایی مانند «فروش ویژه»، «مشتری ویژه»، «طرح ویژه تابستان» و «ویژه اعضا» مطابقت دارد.

شمارش سلول‌های شامل کلمه اکسل

=COUNTIF(A2:A100,"*اکسل*")

شمارش سفارش‌هایی که شامل عبارت لغو هستند

=COUNTIF(A2:A100,"*لغو*")

جمع مبلغ محصولات دارای کلمه لپ‌تاپ

=SUMIF(A2:A100,"*لپ‌تاپ*",B2:B100)

محاسبه میانگین نمره دوره‌های شامل اکسل

=AVERAGEIF(A2:A100,"*اکسل*",B2:B100)

استفاده از ستاره به‌تنهایی

علامت ستاره به‌تنهایی معمولاً برای شناسایی سلول‌های دارای محتوای متنی استفاده می‌شود.

=COUNTIF(A2:A100,"*")

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

شمارش تمام سلول‌های غیرخالی

اگر هدف شمارش تمام سلول‌های غیرخالی شامل متن، عدد و تاریخ است، استفاده از تابع COUNTA مناسب‌تر است:

=COUNTA(A2:A100)

علامت سؤال در اکسل

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

?

برای مثال، معیار زیر متن‌های سه‌کاراکتری را پیدا می‌کند:

="???"

پیداکردن متن با تعداد کاراکتر مشخص

برای پیداکردن متن‌هایی با طول مشخص، به تعداد کاراکترهای موردنظر از علامت سؤال استفاده کنید.

شمارش متن‌های سه‌کاراکتری

=COUNTIF(A2:A100,"???")

شمارش کدهای پنج‌کاراکتری

=COUNTIF(A2:A100,"?????")

هر علامت سؤال نماینده دقیقاً یک کاراکتر است. بنابراین پنج علامت سؤال فقط متن‌های دارای پنج کاراکتر را پیدا می‌کند.

پیداکردن متن با ساختار مشخص

علامت سؤال می‌تواند با متن ثابت ترکیب شود تا الگوی مشخصی ایجاد شود.

کدهایی با PRD و سه کاراکتر بعد از آن

=COUNTIF(A2:A100,"PRD???")

این معیار کدهایی مانند PRD001، PRD125 و PRDABC را پیدا می‌کند.

کدهایی با یک کاراکتر نامشخص در ابتدا

=COUNTIF(A2:A100,"?123")

این معیار با A123، B123 و 5123 مطابقت دارد؛ اما با AB123 مطابقت ندارد.

کدهایی با یک کاراکتر نامشخص در وسط

=COUNTIF(A2:A100,"AB?12")

این معیار مقادیری مانند ABA12، ABB12 و AB712 را پیدا می‌کند.

ترکیب علامت ستاره و سؤال

می‌توان ستاره و سؤال را در یک معیار ترکیب کرد. در این حالت، ستاره هر تعداد کاراکتر و سؤال دقیقاً یک کاراکتر را پوشش می‌دهد.

شروع با A و داشتن حداقل یک کاراکتر بعد از آن

=COUNTIF(A2:A100,"A?*")

این معیار فقط مقادیری را می‌پذیرد که با A شروع شوند و بعد از آن حداقل یک کاراکتر داشته باشند.

متن‌هایی که کاراکتر دوم آن‌ها عدد 5 است

=COUNTIF(A2:A100,"?5*")

کدهایی که با PR شروع و به 9 ختم می‌شوند

=COUNTIF(A2:A100,"PR*9")

کدهایی که با PR شروع شده و دقیقاً سه کاراکتر قبل از 9 دارند

=COUNTIF(A2:A100,"PR???9")

تفاوت علامت ستاره و علامت سؤال

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

استفاده از ستاره

=COUNTIF(A2:A100,"AB*")

تمام متن‌هایی که با AB شروع می‌شوند، با هر طولی شمارش می‌شوند.

استفاده از سؤال

=COUNTIF(A2:A100,"AB???")

فقط متن‌هایی شمارش می‌شوند که با AB شروع شده و دقیقاً سه کاراکتر بعد از آن داشته باشند.

علامت Tilde در اکسل

علامت Tilde برای خنثی‌کردن کاربرد Wildcard استفاده می‌شود. اگر بخواهید خود علامت ستاره یا سؤال را در متن پیدا کنید، باید قبل از آن‌ها Tilde قرار دهید.

~

به این کار Escape کردن کاراکتر عمومی گفته می‌شود.

جست‌وجوی خود علامت ستاره

علامت ستاره معمولاً به معنی هر تعداد کاراکتر است. برای جست‌وجوی یک ستاره واقعی باید قبل از آن Tilde قرار دهید.

سلول‌هایی که فقط شامل ستاره هستند

=COUNTIF(A2:A100,"~*")

سلول‌هایی که در هر بخش خود ستاره دارند

=COUNTIF(A2:A100,"*~**")

در این معیار، ستاره اول و آخر Wildcard هستند و ترکیب Tilde و ستاره، خود علامت ستاره را جست‌وجو می‌کند.

جست‌وجوی خود علامت سؤال

سلول‌هایی که فقط شامل علامت سؤال هستند

=COUNTIF(A2:A100,"~?")

سلول‌هایی که در هر بخش خود علامت سؤال دارند

=COUNTIF(A2:A100,"*~?*")

جست‌وجوی خود علامت Tilde

برای جست‌وجوی خود علامت Tilde باید آن را دو بار بنویسید.

سلول‌هایی که فقط شامل Tilde هستند

=COUNTIF(A2:A100,"~~")

سلول‌هایی که در هر بخش خود Tilde دارند

=COUNTIF(A2:A100,"*~~*")

استفاده از Wildcard در تابع COUNTIF

تابع COUNTIF یکی از پرکاربردترین توابع برای استفاده از کاراکترهای عمومی است. این تابع تعداد سلول‌هایی را می‌شمارد که با یک معیار مشخص مطابقت دارند.

ساختار تابع COUNTIF

=COUNTIF(range,criteria)

شمارش نام‌هایی که با م شروع می‌شوند

=COUNTIF(A2:A100,"م*")

شمارش نام‌هایی که به ی ختم می‌شوند

=COUNTIF(A2:A100,"*ی")

شمارش متن‌هایی که شامل آموزش هستند

=COUNTIF(A2:A100,"*آموزش*")

شمارش کدهای دقیقاً هشت‌کاراکتری

=COUNTIF(A2:A100,"????????")

شمارش کدهای دارای ساختار AB و چهار کاراکتر

=COUNTIF(A2:A100,"AB????")

استفاده از مقدار یک سلول به‌عنوان معیار Wildcard

به‌جای نوشتن مستقیم عبارت جست‌وجو در فرمول، می‌توان آن را از یک سلول دریافت کرد. برای ساخت معیار پویا باید Wildcardها را با علامت & به مقدار سلول متصل کنید.

فرض کنید عبارت جست‌وجو در سلول E2 قرار دارد.

متن‌هایی که با مقدار E2 شروع می‌شوند

=COUNTIF(A2:A100,E2&"*")

متن‌هایی که به مقدار E2 ختم می‌شوند

=COUNTIF(A2:A100,"*"&E2)

متن‌هایی که شامل مقدار E2 هستند

=COUNTIF(A2:A100,"*"&E2&"*")

با تغییر مقدار سلول E2، نتیجه فرمول نیز به‌صورت خودکار تغییر می‌کند.

شمارش مقادیری که شامل یک عبارت نیستند

برای شمارش متن‌هایی که شامل یک عبارت مشخص نیستند، می‌توان عملگر نامساوی را با Wildcard ترکیب کرد.

شمارش مقادیری که شامل لغو نیستند

=COUNTIF(A2:A100,"<>*لغو*")

اگر نمی‌خواهید سلول‌های خالی در نتیجه قرار بگیرند، از COUNTIFS استفاده کنید:

=COUNTIFS(A2:A100,"<>",A2:A100,"<>*لغو*")

شمارش مقادیری که با تست شروع نمی‌شوند

=COUNTIFS(A2:A100,"<>",A2:A100,"<>تست*")

استفاده از Wildcard در تابع COUNTIFS

تابع COUNTIFS برای شمارش براساس چند معیار استفاده می‌شود. می‌توان در یک یا چند معیار آن از Wildcard استفاده کرد.

ساختار تابع COUNTIFS

=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2)

شمارش محصولات شامل لپ‌تاپ با وضعیت فعال

=COUNTIFS(A2:A100,"*لپ‌تاپ*",B2:B100,"فعال")

شمارش مشتریانی که نام آن‌ها با م شروع و شهرشان تهران است

=COUNTIFS(A2:A100,"م*",B2:B100,"تهران")

شمارش کدهای PRD با فروش بیشتر از یک میلیون

=COUNTIFS(A2:A100,"PRD*",B2:B100,">1000000")

شمارش سفارش‌های شامل فوری و پرداخت‌نشده

=COUNTIFS(A2:A100,"*فوری*",B2:B100,"<>پرداخت شد")

استفاده از Wildcard در تابع SUMIF

تابع SUMIF مقادیر یک محدوده را براساس یک معیار جمع می‌کند. معیار این تابع می‌تواند شامل ستاره، سؤال یا Tilde باشد.

ساختار تابع SUMIF

=SUMIF(range,criteria,sum_range)

جمع فروش تمام محصولات با نام موبایل

=SUMIF(A2:A100,"*موبایل*",B2:B100)

جمع مبلغ سفارش‌هایی که با کد WEB شروع می‌شوند

=SUMIF(A2:A100,"WEB*",B2:B100)

جمع مبلغ مشتریانی که نام خانوادگی آن‌ها به پور ختم می‌شود

=SUMIF(A2:A100,"*پور",B2:B100)

جمع مبلغ براساس عبارت موجود در یک سلول

اگر عبارت جست‌وجو در E2 قرار داشته باشد:

=SUMIF(A2:A100,"*"&E2&"*",B2:B100)

استفاده از Wildcard در تابع SUMIFS

تابع SUMIFS برای جمع‌کردن مقادیر براساس چند شرط استفاده می‌شود.

جمع فروش محصولات شامل موبایل در شهر تهران

=SUMIFS(C2:C100,A2:A100,"*موبایل*",B2:B100,"تهران")

جمع فروش کدهای PRD با وضعیت فعال

=SUMIFS(C2:C100,A2:A100,"PRD*",B2:B100,"فعال")

جمع سفارش‌های شامل ویژه و بیشتر از یک میلیون

=SUMIFS(C2:C100,A2:A100,"*ویژه*",C2:C100,">1000000")

جمع فروش براساس معیار موجود در سلول

=SUMIFS(C2:C100,A2:A100,"*"&E2&"*",B2:B100,F2)

استفاده از Wildcard در تابع AVERAGEIF

تابع AVERAGEIF میانگین مقادیری را محاسبه می‌کند که با معیار مشخصی مطابقت دارند.

میانگین فروش محصولات شامل لپ‌تاپ

=AVERAGEIF(A2:A100,"*لپ‌تاپ*",B2:B100)

میانگین نمره دوره‌هایی که با Excel شروع می‌شوند

=AVERAGEIF(A2:A100,"Excel*",B2:B100)

میانگین مبلغ سفارش‌های یک مشتری

اگر بخشی از نام مشتری در E2 قرار داشته باشد:

=AVERAGEIF(A2:A100,"*"&E2&"*",B2:B100)

استفاده از Wildcard در تابع AVERAGEIFS

میانگین فروش محصولات شامل موبایل در شعبه تهران

=AVERAGEIFS(C2:C100,A2:A100,"*موبایل*",B2:B100,"تهران")

میانگین نمره دوره‌های اکسل با وضعیت تکمیل‌شده

=AVERAGEIFS(C2:C100,A2:A100,"*اکسل*",B2:B100,"تکمیل شده")

استفاده از Wildcard در تابع XLOOKUP

تابع XLOOKUP در نسخه‌های جدید اکسل امکان جست‌وجو با Wildcard را فراهم می‌کند. برای فعال‌کردن حالت Wildcard باید آرگومان match_mode را برابر 2 قرار دهید.

ساختار مرتبط تابع XLOOKUP

=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode)

جست‌وجوی محصولی که نام آن شامل مقدار E2 است

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"یافت نشد",2)

این فرمول اولین موردی را پیدا می‌کند که نام آن شامل عبارت موجود در E2 باشد و مقدار متناظر را از ستون B برمی‌گرداند.

جست‌وجوی محصولی که با مقدار E2 شروع می‌شود

=XLOOKUP(E2&"*",A2:A100,B2:B100,"یافت نشد",2)

جست‌وجوی محصولی که به مقدار E2 ختم می‌شود

=XLOOKUP("*"&E2,A2:A100,B2:B100,"یافت نشد",2)

جست‌وجوی کدی با سه کاراکتر نامشخص

=XLOOKUP("PRD???",A2:A100,B2:B100,"یافت نشد",2)

نکته مهم درباره XLOOKUP و Wildcard

اگر آرگومان match_mode برابر 2 نباشد، XLOOKUP ممکن است ستاره و سؤال را به‌عنوان کاراکتر عادی در نظر بگیرد یا نتیجه موردنظر را پیدا نکند.

فرمول صحیح برای جست‌وجوی Wildcard:

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"یافت نشد",2)

استفاده از Wildcard در تابع XMATCH

تابع XMATCH موقعیت یک مقدار را در محدوده برمی‌گرداند. برای استفاده از Wildcard باید match_mode برابر 2 باشد.

پیداکردن موقعیت اولین متن شامل عبارت E2

=XMATCH("*"&E2&"*",A2:A100,2)

پیداکردن موقعیت اولین کد با پیشوند PRD

=XMATCH("PRD*",A2:A100,2)

ترکیب XMATCH با INDEX

=INDEX(B2:B100,XMATCH("*"&E2&"*",A2:A100,2))

این فرمول موقعیت متن موردنظر را پیدا کرده و مقدار متناظر را از ستون B برمی‌گرداند.

استفاده از Wildcard در تابع MATCH

تابع MATCH در حالت تطبیق دقیق می‌تواند از Wildcard استفاده کند. برای این کار آرگومان match_type باید برابر صفر باشد.

پیداکردن موقعیت متن شامل عبارت E2

=MATCH("*"&E2&"*",A2:A100,0)

پیداکردن موقعیت کدی که با AB شروع می‌شود

=MATCH("AB*",A2:A100,0)

ترکیب INDEX و MATCH

=INDEX(B2:B100,MATCH("*"&E2&"*",A2:A100,0))

استفاده از Wildcard در تابع VLOOKUP

تابع VLOOKUP در حالت جست‌وجوی دقیق می‌تواند از Wildcardها استفاده کند. آرگومان آخر باید FALSE باشد.

جست‌وجوی کدی که با مقدار E2 شروع می‌شود

=VLOOKUP(E2&"*",A2:C100,3,FALSE)

جست‌وجوی نامی که شامل مقدار E2 است

=VLOOKUP("*"&E2&"*",A2:C100,3,FALSE)

VLOOKUP اولین مورد مطابق را برمی‌گرداند. اگر چند ردیف با الگو مطابقت داشته باشند، فقط نتیجه اولین ردیف نمایش داده می‌شود.

استفاده از Wildcard در تابع HLOOKUP

تابع HLOOKUP نیز در حالت تطبیق دقیق از Wildcard پشتیبانی می‌کند.

=HLOOKUP(E2&"*",A1:Z5,3,FALSE)

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

استفاده از Wildcard در تابع SEARCH

تابع SEARCH موقعیت شروع یک متن را در متن دیگر پیدا می‌کند و از علامت‌های ستاره و سؤال پشتیبانی می‌کند.

ساختار تابع SEARCH

=SEARCH(find_text,within_text)

پیداکردن الگوی کد با سه کاراکتر

=SEARCH("PRD???",A2)

بررسی وجود یک عبارت در متن

=ISNUMBER(SEARCH("*اکسل*",A2))

اگر عبارت اکسل در A2 پیدا شود، SEARCH یک عدد و ISNUMBER مقدار TRUE برمی‌گرداند.

نمایش نتیجه متنی با IF

=IF(ISNUMBER(SEARCH("*اکسل*",A2)),"مرتبط","نامرتبط")

تفاوت SEARCH و FIND در استفاده از Wildcard

تابع SEARCH از Wildcardها پشتیبانی می‌کند و نسبت به حروف کوچک و بزرگ انگلیسی حساس نیست. تابع FIND برای جست‌وجوی دقیق‌تر و حساس به حروف کوچک و بزرگ استفاده می‌شود، اما Wildcardهای ستاره و سؤال را مانند SEARCH پردازش نمی‌کند.

جست‌وجوی Wildcard با SEARCH

=SEARCH("AB?12",A2)

جست‌وجوی متن دقیق با FIND

=FIND("AB12",A2)

استفاده از Wildcard با تابع FILTER

تابع FILTER آرایه‌ای از ردیف‌های مطابق با شرط را برمی‌گرداند. FILTER به‌صورت مستقیم آرگومان Wildcard ندارد، اما می‌توان آن را با SEARCH ترکیب کرد.

نمایش تمام ردیف‌هایی که نام محصول شامل مقدار E2 است

=FILTER(A2:C100,ISNUMBER(SEARCH(E2,A2:A100)),"موردی یافت نشد")

از آنجا که SEARCH از Wildcard پشتیبانی می‌کند، مقدار E2 نیز می‌تواند شامل ستاره یا سؤال باشد.

نمایش کدهایی با الگوی PRD و سه کاراکتر

=FILTER(A2:C100,ISNUMBER(SEARCH("PRD???",A2:A100)),"موردی یافت نشد")

فیلتر محصولاتی که شامل موبایل هستند و وضعیت فعال دارند

=FILTER(A2:D100,ISNUMBER(SEARCH("*موبایل*",A2:A100))*(D2:D100="فعال"),"موردی یافت نشد")

استفاده از Wildcard در IF

تابع IF به‌تنهایی الگوهای Wildcard را در مقایسه مستقیم پردازش نمی‌کند. برای استفاده از Wildcard در IF باید از توابعی مانند COUNTIF یا SEARCH کمک بگیرید.

روش نادرست

=IF(A2="*اکسل*","مرتبط","نامرتبط")

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

روش صحیح با COUNTIF

=IF(COUNTIF(A2,"*اکسل*")>0,"مرتبط","نامرتبط")

روش صحیح با SEARCH

=IF(ISNUMBER(SEARCH("اکسل",A2)),"مرتبط","نامرتبط")

استفاده از Wildcard در قالب‌بندی شرطی

برای برجسته‌کردن سلول‌ها یا ردیف‌هایی که دارای یک الگوی مشخص هستند، می‌توان از COUNTIF یا SEARCH در Conditional Formatting استفاده کرد.

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

=COUNTIF($A2,"*فوری*")>0

برجسته‌کردن کدهایی که با PRD شروع می‌شوند

=COUNTIF($A2,"PRD*")>0

برجسته‌کردن متن‌های دقیقاً پنج‌کاراکتری

=COUNTIF($A2,"?????")>0

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

=ISNUMBER(SEARCH("*موبایل*",$A2))

استفاده از Wildcard در Data Validation

در Data Validation می‌توان از فرمول‌های سفارشی برای کنترل الگوی داده‌های واردشده استفاده کرد.

اجازه ورود کدهایی با پیشوند PRD و سه کاراکتر

=COUNTIF(A2,"PRD???")=1

اجازه ورود کدهای دقیقاً شش‌کاراکتری

=COUNTIF(A2,"??????")=1

اجازه ورود متن‌هایی که با INV شروع می‌شوند

=COUNTIF(A2,"INV*")=1

توجه داشته باشید که ستاره می‌تواند صفر کاراکتر را نیز پوشش دهد. بنابراین مقدار INV به‌تنهایی نیز با معیار INV* مطابقت دارد.

محدودکردن طول متن در Data Validation

اگر فقط تعداد کاراکترها مهم است، استفاده از تابع LEN معمولاً دقیق‌تر و خواناتر است.

اجازه ورود متن دقیقاً شش‌کاراکتری

=LEN(A2)=6

Wildcard برای بررسی ساختار کلی مناسب است؛ اما LEN برای بررسی طول دقیق متن انتخاب ساده‌تری است.

استفاده از Wildcard در Find and Replace

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

پیداکردن تمام متن‌هایی که با PRD شروع می‌شوند

PRD*

پیداکردن تمام متن‌هایی که به تهران ختم می‌شوند

*تهران

پیداکردن تمام متن‌های شامل فروش

*فروش*

پیداکردن کدهای دارای یک کاراکتر نامشخص

AB?12

پیداکردن خود علامت ستاره

~*

پیداکردن خود علامت سؤال

~?

قبل از اجرای Replace روی حجم زیادی از داده‌ها، ابتدا چند مورد را با Find بررسی کنید یا از فایل نسخه پشتیبان تهیه کنید؛ زیرا الگوهای Wildcard ممکن است تعداد زیادی سلول را انتخاب کنند.

استفاده از Wildcard در فیلتر اکسل

در بخش Text Filters می‌توان از الگوهای Wildcard برای فیلتر داده‌ها استفاده کرد.

نمایش متن‌هایی که با کد مشخص شروع می‌شوند

PRD*

نمایش متن‌هایی که یک عبارت را شامل می‌شوند

*آنلاین*

نمایش کدهایی با طول و ساختار مشخص

INV????

در بسیاری از پنجره‌های فیلتر می‌توانید از گزینه‌های Begins With، Ends With و Contains نیز استفاده کنید. Wildcard زمانی مفیدتر است که بخواهید یک الگوی ترکیبی و انعطاف‌پذیر بسازید.

استفاده از Wildcard برای جست‌وجوی کد کالا

فرض کنید کد محصولات با PRD شروع می‌شود و پس از آن سه کاراکتر قرار دارد.

شمارش کدهای معتبر

=COUNTIF(A2:A100,"PRD???")

پیداکردن اولین کد معتبر

=XLOOKUP("PRD???",A2:A100,B2:B100,"یافت نشد",2)

نمایش تمام کدهای معتبر

=FILTER(A2:B100,ISNUMBER(SEARCH("PRD???",A2:A100)),"موردی یافت نشد")

مثال فهرست مشتریان

شمارش مشتریانی که نام آن‌ها با محمد شروع می‌شود

=COUNTIF(A2:A100,"محمد*")

شمارش مشتریانی که نام خانوادگی آن‌ها شامل رضایی است

=COUNTIF(A2:A100,"*رضایی*")

جمع خرید مشتریانی که نام آن‌ها شامل رضایی است

=SUMIF(A2:A100,"*رضایی*",B2:B100)

جست‌وجوی اولین مشتری مطابق عبارت واردشده

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"یافت نشد",2)

مثال مدیریت محصولات

شمارش محصولات شامل موبایل

=COUNTIF(A2:A100,"*موبایل*")

جمع موجودی محصولات شامل موبایل

=SUMIF(A2:A100,"*موبایل*",B2:B100)

جمع فروش موبایل‌های فعال

=SUMIFS(C2:C100,A2:A100,"*موبایل*",B2:B100,"فعال")

نمایش تمام محصولات شامل موبایل

=FILTER(A2:C100,ISNUMBER(SEARCH("موبایل",A2:A100)),"موردی یافت نشد")

مثال مدیریت فایل‌ها

اگر نام فایل‌ها در ستون A ثبت شده باشند، می‌توان با استفاده از Wildcard پسوند فایل‌ها را بررسی کرد.

شمارش فایل‌های اکسل

=COUNTIF(A2:A100,"*.xlsx")

شمارش فایل‌های PDF

=COUNTIF(A2:A100,"*.pdf")

شمارش فایل‌هایی که نام آن‌ها با Report شروع می‌شود

=COUNTIF(A2:A100,"Report*")

شمارش فایل‌هایی که شامل عبارت Final هستند

=COUNTIF(A2:A100,"*Final*")

مثال شماره فاکتور

فرض کنید شماره فاکتورها با INV شروع می‌شوند و بعد از آن چهار کاراکتر قرار دارد.

شمارش شماره فاکتورهای دارای الگوی موردنظر

=COUNTIF(A2:A100,"INV????")

شمارش فاکتورهایی که با INV-2026 شروع می‌شوند

=COUNTIF(A2:A100,"INV-2026*")

پیداکردن اولین فاکتور سال مشخص

=XLOOKUP("INV-2026*",A2:A100,B2:B100,"یافت نشد",2)

مثال سفارش‌های فروشگاه

شمارش سفارش‌های فوری

=COUNTIF(A2:A100,"*فوری*")

شمارش سفارش‌های غیرلغوشده و غیرخالی

=COUNTIFS(A2:A100,"<>",A2:A100,"<>*لغو*")

جمع مبلغ سفارش‌های ویژه

=SUMIF(A2:A100,"*ویژه*",B2:B100)

جمع سفارش‌های فوری و پرداخت‌شده

=SUMIFS(C2:C100,A2:A100,"*فوری*",B2:B100,"پرداخت شد")

تفاوت Wildcard با جست‌وجوی دقیق

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

جست‌وجوی دقیق

=COUNTIF(A2:A100,"موبایل")

این فرمول فقط سلول‌هایی را می‌شمارد که دقیقاً برابر «موبایل» هستند.

جست‌وجوی شامل عبارت

=COUNTIF(A2:A100,"*موبایل*")

این فرمول مقادیری مانند «موبایل سامسونگ»، «قاب موبایل» و «فروش موبایل» را نیز شمارش می‌کند.

آیا Wildcard به حروف کوچک و بزرگ حساس است؟

بیشتر توابع رایج اکسل مانند COUNTIF، SUMIF، SEARCH، VLOOKUP و XLOOKUP در تطبیق متن به حروف کوچک و بزرگ انگلیسی حساس نیستند.

برای مثال، معیار زیر ممکن است هر دو مقدار Excel و EXCEL را مطابق در نظر بگیرد:

=COUNTIF(A2:A100,"excel*")

اگر حساسیت به حروف کوچک و بزرگ ضروری است، باید از روش‌های دیگری مانند EXACT همراه با توابع متنی یا آرایه‌ای استفاده شود.

آیا Wildcard روی اعداد کار می‌کند؟

Wildcardها برای مقایسه متن طراحی شده‌اند. اگر عددها به‌صورت متن ذخیره شده باشند، استفاده از ستاره و سؤال روی آن‌ها قابل‌اعتمادتر است.

برای مثال، معیار زیر کدهای متنی شروع‌شده با 12 را پیدا می‌کند:

=COUNTIF(A2:A100,"12*")

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

=COUNTIFS(A2:A100,">=1200",A2:A100,"<1300")

تأثیر فاصله‌های اضافی بر Wildcard

فاصله‌های ابتدا، انتها یا میان متن می‌توانند باعث نتایج غیرمنتظره شوند. ستاره ممکن است بعضی فاصله‌ها را پوشش دهد؛ اما اگر به تطبیق دقیق‌تری نیاز دارید، بهتر است داده‌ها را با TRIM پاک‌سازی کنید.

حذف فاصله‌های اضافی

=TRIM(A2)

بررسی عبارت پس از حذف فاصله‌ها

=COUNTIF(B2,"*اکسل*")

در این مثال فرض شده است که در B2 نتیجه پاک‌سازی‌شده متن A2 قرار دارد.

پاک‌سازی کاراکترهای نامرئی

گاهی داده‌های واردشده از وب یا نرم‌افزارهای دیگر دارای کاراکترهای نامرئی هستند. در این شرایط می‌توان از CLEAN و TRIM استفاده کرد.

=TRIM(CLEAN(A2))

پس از پاک‌سازی داده‌ها، نتیجه Wildcardها قابل‌اعتمادتر خواهد بود.

خطاهای رایج در استفاده از Wildcard

1. استفاده از ستاره در مقایسه مستقیم IF

فرمول زیر معمولاً متن شامل اکسل را شناسایی نمی‌کند:

=IF(A2="*اکسل*","بله","خیر")

روش صحیح:

=IF(COUNTIF(A2,"*اکسل*")>0,"بله","خیر")

2. اشتباه‌گرفتن ستاره با علامت ضرب

علامت ستاره داخل معیار متنی Wildcard است؛ اما خارج از متن و میان دو مقدار، عمل ضرب انجام می‌دهد.

ستاره به‌عنوان ضرب

=A2*B2

ستاره به‌عنوان Wildcard

=COUNTIF(A2:A100,"*اکسل*")

3. استفاده از علامت سؤال برای چند کاراکتر

هر علامت سؤال فقط یک کاراکتر را پوشش می‌دهد. برای سه کاراکتر باید سه علامت سؤال نوشته شود.

=COUNTIF(A2:A100,"PRD???")

4. فراموش‌کردن Tilde برای جست‌وجوی ستاره

اگر هدف یافتن خود علامت ستاره است، باید قبل از آن Tilde قرار دهید.

=COUNTIF(A2:A100,"*~**")

5. قرارندادن معیار داخل نقل‌قول

معیار متنی ثابت باید داخل علامت نقل‌قول دوتایی قرار بگیرد.

=COUNTIF(A2:A100,"PRD*")

6. قراردادن آدرس سلول داخل نقل‌قول

اگر عبارت جست‌وجو در E2 باشد، نباید E2 را به‌صورت متن داخل نقل‌قول بنویسید.

روش صحیح:

=COUNTIF(A2:A100,"*"&E2&"*")

7. فراموش‌کردن match_mode در XLOOKUP

برای فعال‌کردن Wildcard در XLOOKUP باید match_mode برابر 2 باشد.

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"یافت نشد",2)

8. استفاده از Wildcard برای عدد واقعی

Wildcard روی متن عملکرد مناسب‌تری دارد. برای اعداد واقعی از معیارهای عددی استفاده کنید.

=COUNTIF(A2:A100,">=1000")

9. انتظار دریافت تمام نتایج از XLOOKUP

XLOOKUP اولین مورد مطابق را برمی‌گرداند. برای نمایش تمام نتایج باید از FILTER استفاده کنید.

=FILTER(A2:C100,ISNUMBER(SEARCH(E2,A2:A100)),"موردی یافت نشد")

10. نادیده‌گرفتن فاصله‌های اضافی

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

=TRIM(CLEAN(A2))

11. استفاده از ستاره زمانی که حداقل یک کاراکتر لازم است

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

=COUNTIF(A2:A100,"PRD?*")

12. جایگزینی گسترده بدون بررسی

استفاده از ستاره در Find and Replace ممکن است بخش بزرگی از متن را انتخاب کند. ابتدا نتایج را با Find بررسی کنید و سپس Replace را اجرا کنید.

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

• برای جست‌وجوی «شروع با» ستاره را در انتهای عبارت قرار دهید.
• برای جست‌وجوی «پایان با» ستاره را در ابتدای عبارت قرار دهید.
• برای جست‌وجوی «شامل» ستاره را در دو طرف عبارت قرار دهید.
• برای هر کاراکتر اجباری از یک علامت سؤال استفاده کنید.
• برای جست‌وجوی خود ستاره یا سؤال از Tilde استفاده کنید.
• برای جست‌وجوی خود Tilde آن را دو بار بنویسید.
• برای ساخت جست‌وجوی پویا، Wildcard را با & به آدرس سلول متصل کنید.
• برای نمایش تمام نتایج از FILTER همراه با SEARCH استفاده کنید.
• برای نمایش اولین نتیجه از XLOOKUP در حالت match_mode برابر 2 استفاده کنید.
• برای شمارش چندشرطی از COUNTIFS استفاده کنید.
• برای جمع چندشرطی از SUMIFS استفاده کنید.
• قبل از جست‌وجو، فاصله‌ها و کاراکترهای نامرئی را پاک‌سازی کنید.
• برای داده‌های عددی واقعی از معیارهای عددی به‌جای Wildcard استفاده کنید.
• برای کنترل طول دقیق متن، LEN ممکن است از چند علامت سؤال خواناتر باشد.
• قبل از Replace گسترده، از فایل نسخه پشتیبان تهیه کنید.
• در فرمول‌های پیچیده، معیار Wildcard را در یک سلول جداگانه قرار دهید.

استفاده از Named Range با Wildcard

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

اگر محدوده نام محصولات با نام ProductNames و سلول جست‌وجو با نام SearchText تعریف شده باشد:

=COUNTIF(ProductNames,"*"&SearchText&"*")

اگر محدوده مبلغ فروش با نام SalesAmount تعریف شده باشد:

=SUMIF(ProductNames,"*"&SearchText&"*",SalesAmount)

ساخت کادر جست‌وجوی پویا در اکسل

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

فرض کنید متن جست‌وجو در E2 قرار دارد و جدول در A2:C100 است:

=FILTER(A2:C100,ISNUMBER(SEARCH(E2,A2:A100)),"موردی یافت نشد")

اگر E2 شامل کلمه موبایل باشد، تمام ردیف‌هایی که نام محصول آن‌ها شامل موبایل است نمایش داده می‌شوند.

نمایش تمام اطلاعات در صورت خالی‌بودن کادر جست‌وجو

=IF(E2="",A2:C100,FILTER(A2:C100,ISNUMBER(SEARCH(E2,A2:A100)),"موردی یافت نشد"))

ترکیب Wildcard با IFERROR

توابع جست‌وجو ممکن است در صورت پیدانشدن مقدار، خطا نمایش دهند. می‌توان این خطا را با IFERROR مدیریت کرد.

مدیریت خطای MATCH

=IFERROR(MATCH("*"&E2&"*",A2:A100,0),"یافت نشد")

مدیریت خطای INDEX و MATCH

=IFERROR(INDEX(B2:B100,MATCH("*"&E2&"*",A2:A100,0)),"یافت نشد")

مدیریت خطای VLOOKUP

=IFERROR(VLOOKUP("*"&E2&"*",A2:C100,3,FALSE),"یافت نشد")

تمرین‌های پیشنهادی Wildcard در اکسل

تمرین اول: شمارش نام‌های شروع‌شده با م

=COUNTIF(A2:A100,"م*")

تمرین دوم: شمارش نام‌های ختم‌شده به پور

=COUNTIF(A2:A100,"*پور")

تمرین سوم: شمارش متن‌های شامل اکسل

=COUNTIF(A2:A100,"*اکسل*")

تمرین چهارم: شمارش کدهای شش‌کاراکتری

=COUNTIF(A2:A100,"??????")

تمرین پنجم: شمارش کدهای PRD با سه کاراکتر بعدی

=COUNTIF(A2:A100,"PRD???")

تمرین ششم: جست‌وجوی خود ستاره

=COUNTIF(A2:A100,"*~**")

تمرین هفتم: جمع فروش محصولات شامل موبایل

=SUMIF(A2:A100,"*موبایل*",B2:B100)

تمرین هشتم: جست‌وجوی پویا با مقدار سلول

=COUNTIF(A2:A100,"*"&E2&"*")

تمرین نهم: پیداکردن اولین نتیجه با XLOOKUP

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"یافت نشد",2)

تمرین دهم: نمایش تمام نتایج با FILTER

=FILTER(A2:C100,ISNUMBER(SEARCH(E2,A2:A100)),"موردی یافت نشد")

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

=COUNTIF($A2,"*فوری*")>0

تمرین دوازدهم: کنترل ساختار شماره فاکتور

=COUNTIF(A2,"INV????")=1

سازگاری با نسخه‌های مختلف اکسل

کاراکترهای عمومی ستاره، سؤال و Tilde از قابلیت‌های قدیمی و پایه اکسل هستند و در نسخه‌های مختلف Microsoft Excel پشتیبانی می‌شوند.

توابع COUNTIF، SUMIF، AVERAGEIF، MATCH، VLOOKUP و SEARCH در نسخه‌های قدیمی‌تر نیز قابل استفاده هستند. توابع XLOOKUP، XMATCH و FILTER به نسخه‌های جدیدتر اکسل از جمله Microsoft 365 و نسخه‌های دارای آرایه پویا نیاز دارند.

در نسخه‌هایی که XLOOKUP یا FILTER در دسترس نیست، می‌توان از ترکیب INDEX و MATCH، VLOOKUP یا ستون‌های کمکی استفاده کرد.

جداکننده آرگومان‌های توابع ممکن است براساس تنظیمات منطقه‌ای سیستم به‌صورت ویرگول یا نقطه‌ویرگول باشد؛ اما منطق Wildcardها تغییر نمی‌کند.

سؤالات پرتکرار درباره Wildcard در اکسل

Wildcard در اکسل چیست؟

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

علامت ستاره در اکسل چه کاری انجام می‌دهد؟

ستاره نماینده صفر، یک یا چند کاراکتر است.

=COUNTIF(A2:A100,"*اکسل*")

علامت سؤال در اکسل چه کاری انجام می‌دهد؟

هر علامت سؤال دقیقاً نماینده یک کاراکتر است.

=COUNTIF(A2:A100,"PRD???")

علامت Tilde چه کاربردی دارد؟

Tilde برای جست‌وجوی خود علامت ستاره، سؤال یا Tilde استفاده می‌شود.

=COUNTIF(A2:A100,"*~**")

چگونه متن‌هایی را پیدا کنیم که با عبارت مشخصی شروع می‌شوند؟

=COUNTIF(A2:A100,"عبارت*")

چگونه متن‌هایی را پیدا کنیم که به عبارت مشخصی ختم می‌شوند؟

=COUNTIF(A2:A100,"*عبارت")

چگونه متن‌هایی را پیدا کنیم که شامل یک عبارت هستند؟

=COUNTIF(A2:A100,"*عبارت*")

چگونه دقیقاً یک کاراکتر نامشخص را جست‌وجو کنیم؟

=COUNTIF(A2:A100,"AB?12")

چگونه خود علامت سؤال را پیدا کنیم؟

=COUNTIF(A2:A100,"*~?*")

چگونه معیار Wildcard را از یک سلول دریافت کنیم؟

=COUNTIF(A2:A100,"*"&E2&"*")

آیا IF مستقیماً Wildcard را می‌شناسد؟

مقایسه مستقیم IF معمولاً Wildcard را پردازش نمی‌کند. بهتر است از COUNTIF یا SEARCH داخل IF استفاده شود.

=IF(COUNTIF(A2,"*اکسل*")>0,"بله","خیر")

چگونه با XLOOKUP جست‌وجوی Wildcard انجام دهیم؟

آرگومان match_mode را برابر 2 قرار دهید.

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"یافت نشد",2)

چگونه تمام نتایج مطابق را نمایش دهیم؟

از FILTER همراه با SEARCH استفاده کنید.

=FILTER(A2:C100,ISNUMBER(SEARCH(E2,A2:A100)),"موردی یافت نشد")

چرا XLOOKUP فقط یک نتیجه را برمی‌گرداند؟

XLOOKUP برای برگرداندن یک تطبیق طراحی شده است و معمولاً اولین نتیجه مطابق را نمایش می‌دهد. برای چند نتیجه از FILTER استفاده کنید.

آیا Wildcard به حروف بزرگ و کوچک حساس است؟

بیشتر توابع رایج Wildcard در اکسل به حروف بزرگ و کوچک انگلیسی حساس نیستند.

چرا Wildcard روی بعضی اعداد کار نمی‌کند؟

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

چگونه فقط سلول‌های دارای متن را شمارش کنیم؟

=COUNTIF(A2:A100,"*")

چگونه تمام سلول‌های غیرخالی را شمارش کنیم؟

=COUNTA(A2:A100)

چگونه متن‌های فاقد یک عبارت را شمارش کنیم؟

=COUNTIFS(A2:A100,"<>",A2:A100,"<>*لغو*")

تفاوت ستاره و سؤال چیست؟

ستاره هر تعداد کاراکتر و سؤال دقیقاً یک کاراکتر را جایگزین می‌کند.

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

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

• ستاره نماینده صفر، یک یا چند کاراکتر است.
• علامت سؤال نماینده دقیقاً یک کاراکتر است.
• Tilde کاربرد Wildcard کاراکتر بعدی را خنثی می‌کند.
• برای جست‌وجوی شروع متن، ستاره در انتهای معیار قرار می‌گیرد.
• برای جست‌وجوی پایان متن، ستاره در ابتدای معیار قرار می‌گیرد.
• برای جست‌وجوی عبارت در هر بخش متن، ستاره در دو طرف آن قرار می‌گیرد.
• Wildcardها در COUNTIF، SUMIF، AVERAGEIF و توابع چندشرطی کاربرد دارند.
• XLOOKUP در حالت match_mode برابر 2 از Wildcard پشتیبانی می‌کند.
• برای نمایش تمام نتایج می‌توان FILTER را با SEARCH ترکیب کرد.
• برای جست‌وجوی خود ستاره و سؤال باید از Tilde استفاده شود.

پیشنهاد می‌شود پس از یادگیری Wildcardها، آموزش توابع COUNTIF، SUMIF، XLOOKUP، SEARCH، FILTER، قالب‌بندی شرطی و ابزار Find and Replace را دنبال کنید. ترکیب این ابزارها به شما کمک می‌کند جست‌وجو، دسته‌بندی و تحلیل داده‌های متنی را حرفه‌ای‌تر انجام دهید.

آنچه در ویدیو گفته ام

قسمت بعدی، گزینه Wildcard در اکسل است که همان‌طور که در این توضیح برایتان نوشتم، کاراکترهای عمومی هستند و ابزارهای مفیدی برای جست‌وجو و تطبیق‌های متنی هستند که خیلی راحت می‌توانیم از آن‌ها استفاده کنیم. اینجا یک جدول نمونه برایتان گذاشتم که علامت سؤال منظورش یک کاراکتر است. مثلاً دو تا علامت سؤال بگذاریم، منظورش دو تا کاراکتر است. ستاره منظورش هر تعداد کاراکتر است و مثال‌هایی هم اینجا برایتان گذاشتم که می‌توانید از آن‌ها استفاده کنید. اما این بخش Wildcard شاید بخش پیشرفته‌ای باشد که در اکسل استفاده می‌شود. من یک مثال ساده برای شما می‌زنم. فرض کنید یک فهرست از حساب‌هایتان به شما داده‌اند که مثلاً واریز به بانک، مبلغ و موارد دیگری دارد. شما می‌خواهید از گزینه فیلتر استفاده کنید؛ مثلاً واریزها یا برداشت‌های یک بانک را استخراج کنید. خیلی ساده، اگر داخل فیلتر و قسمت Search برویم، مثلاً من دنبال بانک شهر هستم. اگر بانک شهر را انتخاب کنم و فقط کلمه «شهر» را بنویسم، تمام مواردی که داخلشان کلمه «شهر» نوشته شده برای من فیلتر می‌شوند. خیلی ساده نوشته: واریز به بانک شهر، برداشت از بانک شهر، واریز به بانک شهر؛ یعنی فقط کلمه «شهر» را بررسی کرده است. حالا این مثال ساده است، اما اگر کسی بگوید تعداد این فهرست کم است و مثلاً فهرست ۱۰٬۰۰۰ ردیف دارد و بگوید من فقط می‌خواهم واریزها به بانک شهر را ببینم و نمی‌خواهم همه موارد بانک شهر را ببینم، اینجا چه‌کار می‌کنیم؟ برای راحتی کار می‌توانیم دوباره همین فیلتر را باز کنیم و بگوییم می‌خواهیم واریز به شهر را ببینیم. پس می‌خواهم مواردی را ببینم که با «واریز» شروع می‌شوند. کلمه «واریز» را می‌نویسم و ستاره را می‌زنم؛ یعنی هر چیزی که بود. بعد کلمه «شهر» را می‌نویسم، یعنی بانک شهر، و دوباره ستاره را می‌زنم؛ یعنی هر چیزی که بود. اکسل متن‌ها را بررسی می‌کند؛ «واریز» داخلشان باشد و بین «واریز» و «شهر» هر چیزی که بود، برایش مهم نیست؛ یعنی هر تعداد کاراکتری که بود، مشکلی ندارد. بعد کلمه «شهر» و پس از آن هم هر تعداد کاراکتری وجود داشته باشد، برای اکسل قابل‌قبول است. حالا اگر دکمه OK را بزنم، فقط واریزهای بانک شهر را به من گزارش می‌دهد. این قسمت مربوط به فیلتر بود که به شما آموزش دادم. ما از این قابلیت در فرمول‌نویسی‌هایمان هم استفاده می‌کنیم. برای مثال، اینجا از تابع COUNTIF استفاده کرده‌ایم و گفته‌ایم محدوده A1:A100 را بشمار، زمانی که مقادیر فقط سه‌کاراکتری هستند. با این شرطی که برای آن گذاشته‌ایم، یعنی سه علامت سؤال، شرط سه‌کاراکتری‌بودن را تعیین کرده‌ایم. بنابراین در فرمول‌نویسی هم از این قابلیت استفاده می‌شود.