آغاز ثبت نام دوره آنلاین مقدماتی تا میانی اکسل (ظرفیت محدود)
سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

جستجو پیشرفته با Wild Card
۵/۵ - (۱ امتیاز)

جستجو پیشرفته در اکسل

یکی از پر استفاده ترین کارها در اکسل جستجو و جایگزینی (یا جستجو و فراخوانی) در رشته های متنی، جداول، بانک های اطلاعاتی، سلول های اکسل و… هست. جستجو در اکسل روش های بسیار گوناگونی داره و با توجه به خواسته و جزئیات مسئله، میتونیم از این روش ها استفاده کنیم. اما گاهی اوقات جستجو بصورتی هست که عبارت مورد جستجو دقیقا مشخص نیست و باید یک قاعده کلی تری رو برای جستجو پیدا کنیم که این کار نیازمند یک جستجو پیشرفته است. مثلا دنبال شماره موبایل هایی بگردیم که دو رقم آخرشون ۳۴ باشه (یعنی هر شماره موبالی که به ۳۴ ختم بشه). پس مشخصه که دقیقا نمیتونیم دنبال ۳۴ بگردیم. باید الگویی پیدا کنیم که بهمون بگه داده دو کاراکتر اخر داده مور نظر ۳۴ هست. این قبیل جستجوها رو می تونیم با استفاده از Wildcard انجام بدیم. در این مقاله قصد داریم کاربردهای Wildcard رو تشریح کنیم. به طور کلی Wildcard ها در جستجو و جایگزینی (Find & Replace)، فیلتر کردن و فرمول نویسی مورد استفاده قرار می گیرن که در ادامه هر کدوم رو به طور مجزا توضیح میدیم. اما قبل از اون باید انواع Wildcardها و عملکرد هر کدوم رو بشناسیم.

معرفی کاراکترهای Wildcard

در اکسل سه نوع کاراکتر Wildcard به شرح زیر داریم:

کاراکتر ستاره(*): یکی از پرکاربردترین کاراکتر های Wildcard در اکسل علامت * هست که نشان دهنده هر تعداد کاراکتر هست. به طور مثال عبارت “ش*ر” رو در نظر بگیرید. این عبارت نشان دهنده کلمه ای هست که مشخص نیست چند کاراکتر دارد و حرف اول اون “ش” و حرف آخر “ر” هست پس کلماتی مثل “شیر”، “شبستر” و “شکر” که همگی با “ش” شروع شدن و به حرف “ر” ختم شدن با این ساختار تطابق دارن اما کلمه ای مثل “سر شیر” با این ساختار مطابقت نداره.

کاراکتر علامت سوال (?): نشان دهنده یک کاراکتر هست. به طور مثال:

???-??? نشان دهنده یک عبارت شش کاراکتری هست که به صورت سه تا سه تا از هم جدا شدن مثال عبارتهای “ABC-DEF” یا “۱۱۱-۲۲۲”

Pri?e نشان دهنده یک کلمه چهار حرفی هست که کلمات price، pride و prize با این الگو مطابقت دارن.

کاراکتر مد(~): این علامت رو زمانی استفاده می کنیم که بخواهیم کاراکترهای ستاره (*) و علامت سوال (؟) به صورت Wildcard استفاده نشن و به صورت کاراکتر متنی ساده درنظر گرفته بشن. به عنوان مثال کلمه “ش~*ر” فقط به عنوان جایگزین کلمه “ش*ر” استفاده میشه. چون قبل از علامت * از علامت ~ استفاده کردیم، اکسل علامت * رو به عنوان Wildcard در نظر نمی گیره.

به عنوان یک مثال عبارت *~? نشان دهنده هر عبارتی هست (با هر تعداد کاراکتر) که با علامت سوال تموم میشه مثلا Why?، .Anybody there? دقت کنید که در این مثال علامت * در نقش Wildcard ظاهر شده اما چون قبل از علامت سوال علامت ~ قرار گرفته برای همین علامت سوال دیگه در نقش Wildcard نیست و فقط صرفا یک علامت سوال هست.

نکته:
پدقت داشته باشید که علامت سوال حتما باید در حالت زبان انگلیسی کیبورد تایپ بشه یعنی ? در حالت کیبورد فارسی، ؟ به معنی Wildcard نخواهد بود.

 

حالا که با کاراکترهای Wildcard و عملکرد هر کدوم آشنا شدیم، در ادامه این مقاله کاربردهای کاراکترهای Wildcard رو بررسی می کنیم.

جستجو پیشرفته (Find) با استفاده از کاراکترهای Wildcard

استفاده از کاراکترهای Wildcard در ابزار Find and Replace دراکسل بسیار بحث گسترده و کاربردی و جذابی هست که سعی می کنیم با مثال این مبحث رو توضیح بدیم.

ابزار Find and Replace به طور پیش فرض به دنبال عبارت مورد نظر در هر جایی از سلول می گرده و در واقع کل عبارت موجود در سلول رو با عبارت جستجو تطابق نمیده. برای مثال فرض کنید اگر عبارت “AA” رو جستجو کنیم اکسل در خروجی جستجو سلول هایی که حاوی عبارت های AA-01، ۰۱-AA و ۰۱-AA-02 هستند رو هم به عنوان نتیجه نمایش میده چون تمام سه عبارت بالا چیزی که به دنبال اون هستیم (AA) رو دارن. این ویژگی ابزار Find and Replace بعضی مواقع کمک کننده هست اما در بعضی مواقع می تونه دردسر ساز باشه. (البته میتونیم با زدن تیک Contents Match Entire Cell دنبال سلول هایی که فقط AA هستن بگردیم. اما در اینجا انعطاف بیشتری لازم داریم که در ادامه تشریح خواهیم کرد)

فرض کنید بانک اطلاعاتی از کد محصولات و قیمت آن ها رو در اختیار داریم (مطابق شکل ۱). در این بانک اطلاعاتی می خواهیم به دنبال کد محصولاتی بگردیم که از ۴ کاراکتر تشکیل شدن که به صورت ۲ تا ۲ تا بایک خط تیره از هم جدا شدن. می خواهیم با استفاده از کارکترهای Wildcard عبارت مورد نظرمون رو جستجو کنیم. برای اینکار اول با زدن دکمه های Ctrl + F پنجره Find and Replace رو فعال می کنیم. حالا در قسمت Find What عبارت ??-?? رو می نویسیم و دکمه Find All رو می زنیم. نتیجه ای مطابق شکل ۱ نمایش داده میشه.

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

شکل ۱- استفاده از کاراکترهای Wildcard در ابزار Find and Replace

براساس منطق پیش فرض جستجو در اکسل عبارت های مثل AAB-01 یا BB-002 با معیار جستجو همخوانی دارن چون حاوی عبارت ??-?? هستند. اما برای اینکه بخواهیم در نتیجه جستجو فقط سلول هایی نمایش داده بشن که دقیقا ۴ کاراکتر دارن که به صورت دوتایی از هم جدا شدن، روی دکمه Options در شکل ۱ کلیک می کنیم و تیک عبارت Match entire cell contents رو مطابق شکل ۲ می زنیم. این گزینه عبارت جستجو رو (در اینجا عبارت ??-??) با کل محتوای سلول مطابقت میده. حالا دوباره روی دکمه Find All کلیک می کنیم. نتایج جستجو در ستون Value در شکل ۲ نمایش داده شده که همه نتایج جستجو شامل ۴ کاراکتر هستن که به صورت دوتایی از هم جدا شدن.

Match entire cell contents

شکل ۲- Match entire cell contents

 جایگزینی (Replace) با استفاده از کاراکترهای Wildcard

همان طور که کاراکترهای Wildcard در جستجو کاربرد دارند، در جایگزینی نتایج جستجو هم مورد استفاده قرار می گیرن. به طور مثال فرض کنید داده هایی مطابق شکل زیر داریم:

اطلاعات فروش براساس منطقه

شکل ۳- اطلاعات فروش براساس منطقه

همان طور که مشخص هست در ستون “منطقه” اطلاعات به روش های مختلفی وارد شده (شمال غرب، شمال-غرب، شمال شرق، شمال-شرق). این اتفاق غالبا در داده‌های فروش می افتد. برای تمیز کردن این داده‌ها و یک دست کردن آن ها، می‌تونیم از کاراکترهای Wildcard اکسل در Find and Replace استفاده کنیم. برای انجام اینکار مراحل زیر رو طی می کنیم:

  • در شیتی که داده ها در آن ثبت شدن دکمه های Ctrl + H را می زنیم تا پنجره Find and Replace و تب Replace فعال بشه؛
  • در قسمت Find What مطابق ویدئو زیر عبارت “شمال*ش*” رو وارد می کنیم؛
  • در قسمت Replace With عبارت “شمال-شرق” رو وارد می کنیم؛
  • روی دکمه Replace All کیلک می کنیم. مشاهده می کنیم که تمام عبارت های “شمال شرق” به صورت یکدست به عبارت “شمال-شرق” تبدیل شدن.

جایگزینی وایلد کارد

توضیح Wildcard: در قسمت  Find، از عبارت “شمال*ش*” استفاده کردیم که هر متنی را که کلمه‌ی “شمال” داشته باشه و حاوی حرف “ش” در هر جایی بعد از اون باشه رو پیدا می کنه. به همین دلیل این روش جستجو تمام حالت های “شمال شرق” و “شمال-شرق” رو در نظر می گیره و با زدن دکمه Replace All همه حالت ها رو به “شمال-شرق” تبدیل می کنیم.

نکته کاربردی:
برای جستجوی خودِ کاراکترهای Wildcard در قسمت Find What باید از ترکیب علامت مد و این کاراکترها استفاده کنیم. مثلا اگر بخواهیم علامت * رو در فایل اکسل جستجو کنیم، در قسمتFind What عبارت *~ رو می نویسیم. (میدونیم که علامت * به تنهایی یعنی هر تعداد کاراکتر و اگر فقط * رو در Find What تایپ کنیم، هر سلولی که پر باشه، پیدا میشه.)

همان طور که قبلا گفتیم علامت ~ عملکرد کاراکترهای Wildcard رو خنثی می کنه و اون ها رو به یک کاراکتر معمولی تبدیل می کنه. مثلا در جدول شکل ۴ قصد داریم تمام علامت های * رو حذف بکنیم. برای اینکار اول باید علامت های * رو پیدا کنیم و به جای اون ها جای خالی رو جایگزین کنیم (قسمت Replace With رو خالی می گذاریم) مطابق شکل ۴

جستجو و جایگزینی کاراکترهای Wildcard

شکل ۴- جستجو پیشرفته و جایگزینی کاراکترهای Wildcard

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

یکی دیگر از کاربردهای کاراکترهای Wildcard در فیلتر کردن داده ها هست مخصوصا وقتی حجم زیادی از داده ها داریم که می خواهیم با شرط های مختلف اون ها رو فیلتر کنیم.به طور مثال فرض کنید جدولی از اسامی افراد مطابق ویدئو زیر داریم و می خواهیم اسامی افرادی رو فیلتر کنیم که اول اسم کوچک آن ها با حرف “م” شروع میشه. برای این کار ابتدا فیلتر رو روی جدول فعال می کنیم و بعد عبارت “م*” رو در ستون “نام و نام خانوادگی” فیلتر می کنیم. مشاهده می کنیم که هر اسمی که در لیست با حرف “م” شروع شده، فیلتر شدن.

فیلتر کردن وایلد کارد

نکته:
در Text Filters یک سری الگوهای آماده برای فیلتر کردن وجود داره. مثلا End with یا Begin With داده هایی رو فیلت رمیکنه که با عبارت خاصی تمام یا شروع میشه. اما استفاده از ترکیب های Wildcard، به ما امکان اینو میده فیلترهای پیشرفته تری اعمال کنیم. کاربرد این کاراکترها در Advanced Filter رو حتما چک کنید.

 

استفاده از کاراکترهای Wildcard برای جستجو پیشرفته در فرمول نویسی

کاراکترهای Wildcard در تعداد محدودی از توابع اکسل قابل استفاده هستن که لیستی از این توابع که شناخته شده تر هستن ارائه شده:

AVERAGEIF LOOKUP
AVERAGEIFS HLOOKUP
COUNTIF SEARCH
COUNTIFS VLOOKUP
SUMIF HLOOKUP
SUMIFS

در ادامه این مقاله چند نمونه از توابع رو انتخاب کردیم تا کاربرد کاراکترهای Wildcard رو با ذکر مثال در این توابع توضیح بدیم.

ترکیب تابع COUNTIF با کاراکترهای Wildcard

فرض کنید در جدول شکل ۵ می خواهیم تعداد سلول هایی که دارای عبارت “AA” هستند رو شمارش کنیم. این کار رو با سه روش انجام میدیم:

جستجو پیشرفته برای شمارش تعداد کد کالا حاوی عبارت "AA"

شکل ۵- جستجو پیشرفته برای شمارش تعداد کد کالا حاوی عبارت “AA”

روش اول: در این روش کاراکتر Wildcard را مستقیما به عنوان یکی از آرگومان های تابع COUNTIF و در قسمت شرط تابع (Criteria) تعریف می کنیم که به صورت زیر خواهد بود:

=COUNTIF(B2:B13, “*AA*”)

چون در شرط مشخص نشده که “AA” در کجای متن باشه و تعداد کاراکتر هم برامون مهم نیست، به همین دلیل از Wildcard ستاره استفاده کردیم.

روش دوم: فرمول بالا کاملا درست هست اما اشکالی که داره این هست که اگر شرط مسئله تغییر کنه مجبوریم فرمول رو ویرایش کنیم. برای اینکه هر بار مجبور نباشیم فرمول رو ویرایش کنیم به جای اینکه عبارت مورد نظرمون (مثلا در اینجا AA) رو در فرمول تایپ کنیم اون رو به یه سلول ارجاع میدیم مطابق فرمول زیر:

=COUNTIF(B2:B13, “*”&E1&”*”)

در فرمول بالا با استفاده از علامت & محتوای سلول E1 رو با Wildcard ستاره ترکیب کردیم. که فرمول دوم دقیقا همون خروجی فرمول اول رو داره با این تفاوت که در صورت تغییر شرط کافیه فقط محتوای سلول E1 تغییر بکنه.

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

=COUNTIF(B2:B13,E1)

در فرمول بالا عبارت *AA* رو در سلول E1 وارد می کنیم و مشاهده می کنیم که خروجی فرمول سوم دقیقا مثل دو فرمول قبل هست. دو روش آخر هر دو پویا و داینامکی هستن. ویژگی روش دوم اینه که نیازی نیست علامت های Wildcard تایپ بشه و فقط کافیه مقدار مورد نظر در سلول تایپ بشه. اما ویژگی روش دوم اینه که هر مدل کاراکتری رو میتونیم داخل سلول تایپ کنیم و نتیجه رو سریعا ببینیم.

عملکرد هر سه فرمول در مثال بالا در ویدئو زیر نمایش داده شده:

جستجو پیشرفته برای شمارش سلول ها با استفاده از وایلدکارد

نکته:
وقتی از کاراکترهای Wildcard در جستجو استفاده می کنیم باید دقت داشته باشیم که این کاراکترها نسبت به بزرگ و کوچک بودن حروف حساس نیستند. یعنی اگر به دنبال عبارت *AA* می گردیم، عبارت *aa* هم در شمارش لحاظ می شن.

 

ترکیب تابع VLOOKUP با کاراکترهای Wildcard

فرض کنید جدولی مطابق شکل ۶ زیر در اختیار داریم که حاوی اطلاعاتی از چند دوره آموزشی به همراه اسامی اساتید دوره هست. می خواهیم با وارد کردن کد دوره، نام استاد در سلول مشخص شده نمایش داده بشه.

ترکیب تابع VLOOKUP و کاراکترهای Wildcard برای جستجو پیشرفته

شکل ۶- ترکیب تابع VLOOKUP و کاراکترهای Wildcard برای جستجو پیشرفته

با توجه به اینکه کد دوره بخشی از متن وارد شده در عنوان درس هست، در این حالت تابع VLOOKUP نمی تونه به تنهایی “نام استاد” رو نمایش بده و خطای N/A# نمایش داده میشه. برای رفع این مشکل باید در قسمت lookup_value از کاراکترهای Wildcard استفاده کنیم. با توجه به اینکه کد درس بخشی از عنوان درس هست و تعداد کاراکترها هم برامون مهم نیست پس lookup_value رو به شکل زیر تعریف می کنیم:

=VLOOKUP(“*”&A11&”*”,A3:B8,2,FALSE)

در سلول A11 کد دوره وارد میشه که با ترکیب کاراکترهای Wildcard (که در این مثال از علامت ستاره استفاده کردیم) به شکل “*”&A11&”*” تبدیل میشه. در ویدئو زیر عملکرد تابع VLOOKUP رو در حضور و عدم حضور کاراکترهای Wildcard نشان داده شده:

Vlookup با استفاده از وایلدکارد

نکته:
با توجه به اینکه تابع Vlookup اولین گزینه ای که با مقدار lookup_value مطابقت داشته باشه رو نشون میده لذا وقتی داریم از کاراکترهای Wildcard استفاده می کنیم باید خیلی دقت داشته باشیم مخصوصا وقتی lookup_value در محدوده جستجو با بیش از یک مورد تطابق داشته باشه. در این خصوص مقاله جستجوی موارد تکراری رو مطالعه کنید.

 

ترکیب کاراکترهای Wildcard با اعداد برای جستجو پیشرفته

در جستجو پیشرفته کاراکترهای Wildcard علاوه بر اینکه با داده های متنی ترکیب می شن، با داده های عددی هم قابل استفاده هستند. به طور مثال در ابزار جستجو (Find) و فیلتر می تونیم از ترکیب کاراکترهای Wildcard با اعداد استفاده کنیم. مطابق شکل ۷ از ترکیب علامت ستاره با عدد ۴ (*۴*) استفاده کردیم تا تمام سلول هایی که حاوی عدد ۴ هستند رو پیدا کنیم. مشاهده می کنید سلول هایی که حاوی متن هستند و در اون ها از عدد ۴ استفاده شده هم در نتایج جستجو نمایش داده شدن.

جستجو پیشرفته اعداد با استفاده از کاراکترهای Wildcard

شکل ۷- جستجو پیشرفته اعداد با استفاده از کاراکترهای Wildcard

در مورد ابزار فیلتر هم هیچ محدودیتی برای استفاده از ترکیب اعداد و کاراکترهای Wildcard وجود ندارد مطابق شکل ۸ مشاهده می کنید که برای فیلتر کردن اعدادی که رقم ۴ دارن از ترکیب علامت ستاره با عدد ۴ استفاده کردیم

فیلتر کردن اعداد با استفاده از کاراکترهای Wildcard (جستجو پیشرفته در فیلتر)

شکل ۸- فیلتر کردن اعداد با استفاده از کاراکترهای Wildcard (جستجو پیشرفته در فیلتر)

همان طور که در شکل های ۷ و ۸ نشان داده شد در ابزارهای جستجو و فیلتر هیچ محدودیتی برای استفاده از ترکیب کاراکترهای Wildcard و اعداد نداریم اما در فرمول ها نمی تونیم از این ترکیب استفاده کنیم دلیل این محدودیت این هست که وقتی اعداد رو با کاراکترهای Wildcard ترکیب می کنیم در واقع داده عددی رو به داده متنی تبدیل کردیم.

برای مثال هر دو فرمول زیر مطابق شکل ۹ تعداد داده های متنی که در آن ها عدد ۴ وجود دارد رو شمارش می کنند و نتیجه هر دو فرمول هم درست هست چون داریم در یک محدوده حاوی داده های متنی شمارش رو انجام میدیم.

=COUNTIF(A3:A13, “*4*” )

=COUNTIF(A3:A13, “*”&E1&”*” )

جستجو پیشرفته برای شمارش رشته متنی در محدوده داده های متنی

شکل ۹- جستجو پیشرفته برای شمارش رشته متنی در محدوده داده های متنی

حالا اگر همین فرمول رو در ستون “قیمت کالا” که داده های عددی داره استفاده کنیم نتیجه ای نخواهیم گرفت (مطابق شکل ۱۰) چون داریم به دنبال یک رشته متنی (*۴*) در محدوده ای عددی می گردیم.

جستجو پیشرفته برای شمارش رشته متنی در محدوده عددی

شکل ۱۰- جستجو پیشرفته برای شمارش رشته متنی در محدوده عددی

راه حلی که برای از بین بردن این محدودیت وجود داره این هست که اول محدوده داده های عددی رو به داده های متنی تبدیل کنیم (برای مثال از طریق  Text to Columnsیا با استفاده از تابع Text) و بعد از توابع VLOOKUP, COUNTIF, MATCH و غیره استفاده کنیم البته این راه حل در همه مواقع جوابگو نخواهد بود.

برای مثال در ویدئو زیر برای تعیین تعداد قیمت هایی که با عدد ۴ شروع شدن، ابتدا اعداد وارد شده در ستون “قیمت کالا” رو به Text تبدیل می کنیم (با استفاده از تابع Text) و بعد فرمول COUNTIF رو می نویسیم.

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

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

فرض کنید در جدولی مطابق شکل ۱۱ می خواهیم تعداد قیمت هایی که رقم ۴ دارند رو بدست بیاریم. در این حالت برای تبدیل عدد اعشاری به متنی، از کد “۰.۰۰” در تابع Text استفاده میکنیم.

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

=SUMPRODUCT(- – (ISNUMBER(SEARCH(“4”, B4:B14))))

شمارش اعداد اعشاری با استفاده از کاراکترهای Wildcard

شکل ۱۱- شمارش اعداد اعشاری با استفاده از کاراکترهای Wildcard

 توضیح فرمول:

تابع Search موقعیت عدد مشخص شده رو در سلول تعیین می کنه و اگر عدد رو پیدا نکنه خطای #VALUE را نشان می دهد.

{#VALUE!;1;#VALUE!;#VALUE!;3;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}

تابع ISNUMBER در عبارت بالا اعداد رو به TRUE و خطای #VALUE! رو به FALSE تبدیل می کنه. خروجی تابع ISNUMBER به شکل زیر خواهد بود:

{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

علامت (- -) TRUE رو به عدد ۱ و FALSE رو به صفر تبدیل می کنه به شکل زیر:

{۰;۱;۰;۰;۱;۰;۰;۱;۰;۰;۰}

و نهایتا تابع SUMPRODUCT اعداد “۱” رو با هم جمع می کنه که نتیجش تعداد اعدادی هست که در محدوده B4:B14 رقم “۴” رو دارن یعنی عدد ۳.

در این مقاله سعی کردیم کاراکترهای Wildcard رو معرفی کنیم و کاربردهاشون رو در جستجو، جایگزینی (Find And Replace)، فیلتر کردن حرفه ای و فرمول نویسی با ذکر مثال توضیح بدیم همچنین با محدودیت های استفاده از کاراکترهای Wildcard در فرمول نویسی آشنا شدیم و یک مثال رو هم در این خصوص بررسی کردیم.

مقالات پیشنهادی:

نحوه فیلتر کردن داده ها بر اساس جنس آنها

آموزش کار با تابع Replace

دانلود فایل این مقاله

برای دانلود فایل این مقاله روی دکمه زیر کلیک کنید:

126

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان