wildcardها در اکسل

قبل از تعریف دقیق کاراکترهای wildcard بگذارید با چند سوال ساده آن‌‌ها را معرفی کنم.

پرسش ۱) چند اسم فارسی بگویید که با نشانه «ف» شروع شوند؟

فرشید - فرید - فرزاد

پرسش ۲) چند اسم فارسی بگویید که حرف اول و آخر آنها نشانه «م» باشد؟

میثم - مریم

پرسش ۳) چند کلمه فارسی بگویید که به ترتیب در آنها ابتدا یک «ر» وجود داشته باشد و سپس «ی» ؟

فرشید - فرید - ارغوانی

پرسش ۴) آیا می‌توانید چند کلمه بگویید که دقیقا چهار حرف باشند و حرف اول آنها «آ» باشد؟

آرام - آسان - آراد - آیسا 

ما در دنیای کامپیوتر می‌‌توانیم پرسش‌های ۱ تا ۴ را به زبانی ساده بیان کنیم و برای این کار از wildcard ها (کاراکترهای عمومی) استفاده می‌کنیم. این کاراکترها چیزهایی هستند که فراتر از خودشان برای کامپیوتر معنا دارد. دقیقا مانند علامت ! که فراتر از یک خط با یک نقطه زیرش برای ما آدم‌ها معنی دارد و به معنی هشدار، توجه و خطر و تعجب است.

مفهوم * 

کاراکتر «*» یک wildcard‌ است و فراتر از خودش در اکسل معنا دارد. بیایید در اکسل یک آزمایش کنید:
۱) ابتدا در چندین سلول چیزهایی بنویسید (هر چیزی که مایلید) 
۲) بازدن CTRL + H پنجره Replace‌ را باز کنید.
۳) در Find what علامت * را تایپ کنید.
۴) در Replace with یک کاراکتر مانند a‌ را تایپ کنید.
۵) کلید Replace All‌ را بزنید.

خواهید دید که همه چیزهایی که تایپ کرده‌اید باحرف «a» جایگزین خواهند شد، زیرا:

کاراکتر «*» در اکسل فراتر از خودش معنا دارد و شامل هر چیزی می‌شود.

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

حال با استفاده از کاراکتر «*» می‌توانیم پرسش‌های ۱ تا ۳ را به زبان کامپیوتر اینگونه بنویسیم:

پرسش ۱) چند اسم فارسی «فـ*» بگویید؟

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

پرسش ۲) چند اسم فارسی «م*م» بگویید؟

یعنی اسامی که ابتدای آن «م» باشد و در ادامه آن هر چیزی می‌تواند باشد و در انتهای آن «م» باشد مانند مریم. 

پرسش ۴) چند اسم فارسی «*ر*ی*» بگویید؟

یعنی ابتدای آن هر چیزی می‌تواند باشد و سپس یک «ر» باشید و سپس هر چیزی و سپس «ی» و سپس هر چیزی می‌تواند باشد مانند فرشید

کاربرد wildcardها در اکسل

در اکسل تقریبا هر جایی که به دنبال چیزی می‌گردید، می‌توانید از wildcardها استفاده کنید مثلا در Filter کردن و یا Find‌ و همچنین Replace و در فرمول نویسی‌های Conditional Formatting ‌و Data Validation. اما مهم است که بدانید در فرمول نویسی اکسل هم می‌توانیم از wildcardها استفاده کنیم.

استفاده از * در تابع VLOOKUP اکسل

به دنبال اسم «kaveh» در یک لیست می‌گردیم اما می‌دانیم که در آن لیست دقیقا «kaveh» نوشته نشده است و ممکن است که نام او «kaveh jan» و یا «Mr kaveh» نوشته شده باشد. با «*kaveh*» از اکسل می‌خواهیم که به دنبال کاوه در هر جایی از مقدار سلول بگردد (مهم نیست که اولش و آخرش چه باشد).

=VLOOKUP( "*kaveh*", A:C, 3, 0)

استفاده از * در تابع SUMIFS اکسل

می‌خواهیم مبالغ تمام سندهایی که در شرح آن کلمه «keshavarzi» وجود دارد را جمع بزنیم. برای اینکار باید از wildcard استفاده کنیم و فرمول زیر را می‌نویسیم:

=SUMIFS( D:D, A:A, "*keshavarzi*" )

توجه ۱) استفاده از wildcard ها به صورت کلی کاری است که نیاز به پردازش بیشتری دارد و به همین دلیل استفاده از آنها فایل‌های بزرگ اکسل را کند خواهد کرد و به همین دلیل باید در استفاده از آنها احتیاط کرد.

توجه ۲) wildcardها فقط برای متن‌ها (TEXT) کاربرد دارند و روی عددها کار نمی‌کنند. فعلا در اکسل راهی برای استفاده از wildcard ها بر روی عدد نداریم و باید همه اعداد را به متن تبدیل کنیم تا بتوانیم از wildcardها استفاده کنیم.

مفهوم ؟ 

کاراکتر ؟ هم یک wildcard‌ محسوب می‌شود و به معنای فقط ۱ کاراکتر است و این کاراکتر می‌تواند هر چیزی باشد. حال برای پاسخ به پرسش ۴ (آیا می‌توانید چند کلمه بگویید که دقیقا چهار حرف باشند و حرف اول آنها «آ» باشد؟ ) می‌توانیم آن را با ؟ به شکل زیر بنویسیم:

آ؟؟؟

یعنی هر چیزی که ابتدا «آ» داشته باشد و کاراکتر دوم و سوم و چهارم آن می‌تواند هر کاراکتری باشد.

توجه: علامت * یعنی هر چیزی و به هر تعدادی اما ؟ یعنی هر چیزی اما فقط ۱ عدد از آن.

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

می‌خواهیم تعداد سلولهایی که دقیقا ۴ حرفی هستند را بشماریم. برای این کار از فرمول زیر استفاده می‌کنیم: (منبع این مثال: goskills.com)

=COUNTIFS( A:A, "????" )

نکته: بین علامت ؟ فارسی و انگلیسی ? تفاوتی وجود ندارد و فقط ظاهر آنها با هم متفاوت است.

نادیده گرفتن wildcardها با ~

حال فرض کنید که می‌خواهیم در یک شیت باید جای «؟» هایی که وجود دارند، علامت «‍‍‍!» بگذاریم. اگر این کار را به صورت زیر انجام دهیم،‌ همه چیز ‍! خواهند: 🙄

حال که معنای علامت ? را می‌دانید چیز عجیبی اتفاق نیافتاده است و شما از اکسل خواسته‌اید که هر کاراکتری که وجود دارد را با علامت ! جایگزین کنید و کلمه «سلام» به صورت «!!!!» خواهد شد. در اینجا شما باید معنای ?  را خنثی کنید تا اکسل فقط ? های که واقعا وجود دارند را جایگزین کند. در اینجا باید از ~ (tilde) کمک بگیرید. 

این علامت بر روی کیبورد شما در سمت چپ عدد ۱ بالای کیبورد قرار دارد و باید با کلید Shift  آن را تایپ کنید.

علامت ‍~ قبل از  ؟ یا * باید تایپ شود تا معنای wildcardی آن ها را خنثی کند یعنی برای جایگزین کردن علامت‌های ? که واقعا وجود دارند باید اینگونه Replace را انجام دهید:

نکته) به صورت کلی به کاراکترهایی که شبیه ~ در اکسل عمل می‌کنند، Escape Character می‌گویند. 

آشنایی با عبارت‌های با قاعده

ما در دنیای کامپیوتر مفهومی داریم که بسیار وسیع‌تر از wildcardها است که به آن Regular Expression (عبارت‌های با قاعده) می‌گویند که برای تطبیق الگوهای بسیار پیچیده بکار می‌رود. در حال حاضر اکسل از Regular Expression‌ پشتیبانی نمی‌کند و احتمال زیادی است که در نسخه‌های آینده اکسل این قابلیت در اکسل اضافه شود. گوگل شیت از این قابلیت پشتیبانی می‌کند و توابعی برای اینکار داریم:

REGEXMATCH  , REGEXEXTRACT  , REGEXREPLACE