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

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

یکی از پر استفاده ترین کارها در اکسل جستجو و جایگزینی (یا جستجو و فراخوانی) در رشته های متنی، جداول، بانک های اطلاعاتی، سلول های اکسل و… هست. جستجو در اکسل روش های بسیار گوناگونی داره و با توجه به خواسته و جزئیات مسئله، میتونیم از این روش ها استفاده کنیم. اما گاهی اوقات جستجو بصورتی هست که عبارت مورد جستجو دقیقا مشخص نیست و باید یک قاعده کلی تری رو برای جستجو پیدا کنیم که این کار نیازمند یک جستجو پیشرفته است. مثلا دنبال شماره موبایل هایی بگردیم که دو رقم آخرشون ۳۴ باشه (یعنی هر شماره موبالی که به ۳۴ ختم بشه). پس مشخصه که دقیقا نمیتونیم دنبال ۳۴ بگردیم. باید الگویی پیدا کنیم که بهمون بگه داده دو کاراکتر اخر داده مور نظر ۳۴ هست. این قبیل جستجوها رو می تونیم با استفاده از 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 رو بررسی می کنیم.

جستجو پیشرفته (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

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

شکل ۲- 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 رو خنثی می کنه و اون ها رو به یک کاراکتر معمولی تبدیل می کنه. مثلا در جدول شکل ۴ قصد داریم تمام علامت های * رو حذف بکنیم. برای اینکار اول باید علامت های * رو پیدا کنیم و به جای اون ها جای خالی رو جایگزین کنیم (قسمت Replace With رو خالی می گذاریم) مطابق شکل ۴

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

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

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

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

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

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

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

فرض کنید در جدول شکل ۵ می خواهیم تعداد سلول هایی که دارای عبارت “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 تایپ بشه و فقط کافیه مقدار مورد نظر در سلول تایپ بشه. اما ویژگی روش دوم اینه که هر مدل کاراکتری رو میتونیم داخل سلول تایپ کنیم و نتیجه رو سریعا ببینیم.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

شکل ۱۱- شمارش اعداد اعشاری با استفاده از کاراکترهای 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

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

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

ثبت ایمیل جهت دانلود فایل

مطمئن باشید ایمیل شما فقط در اختیار اکسل پدیا قرار خواهد گرفت و صرفا برای ارسال مطالب مرتبط استفاده خواهد شد.

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...