همیشه نمیتوانید هنگام جستجو برای چیزی در اکسل، رشتهٔ کامل را در ذهن داشته باشید. شاید فقط بخشی از یک نام یا چند کاراکتر را به یاد داشته باشید. پیمایش در میان ردیفها و ستونهای بیپایان و تلاش برای یافتن آن ورودی مبهم میتواند حس جستجوی سوزن در کاه را القا کند، و مطمئناً راههای بهتری برای صرف وقت خود وجود دارد.
همیشه که بخواهید در اکسل چیزی را جستجو کنید، الزامی نیست که کل رشته را بهخاطر داشته باشید. شاید فقط بخشی از نام یا چند کاراکتر را بهخاطر داشته باشید. پیمایش بین ردیفها و ستونهای بیپایان برای یافتن یک ورودی مبهم میتواند حس پیدا کردن سوزن در انبار کاه را القا کند و مطمئنم روشهای بهتری برای صرف زمان شما وجود دارد.
خوشبختانه، کاراکترهای جایگزین در اکسل میتوانند شما را سریعتر به هدف برسانند. پس از یادگیری نحوه استفاده از آنها، خواهید دید که بهطور طبیعی در ویژگیهایی مانند فیلترها، ابزار Find and Replace و فرمولهای استفادهکننده از COUNTIF، VLOOKUP، SEARCH و سایر توابع شرطی هوشمند اکسل جای میگیرند. مهمتر از همه، این کاراکترها جستجوهای شما را سریعتر، هوشمندتر و بسیار کارآمدتر میسازند.
کاراکتر جایگزین ستاره (*)
زمانی که نیاز به مطابقت با هر توالی، بدون توجه به طول یا نامنظمی آن دارید
ستاره (*) کاراکتر جایگزین است که هر توالیای از کاراکترها را نشان میدهد، حتی وقتی هیچ کاراکتری وجود ندارد. این کاراکتر انعطافپذیرترین است، زیرا میتواند هر تعداد کاراکتر را در جستجوی شما جایگزین کند. میتوانید از آن برای یافتن متنی که با رشتهای شروع میشود، به آن ختم میشود یا صرفاً شامل یک رشته خاص است، استفاده کنید.
بهعنوان مثال، هنگام استفاده از ویژگی Find and Replace در اکسل برای جستجوی برچسبهای مختلف محصول، میتوانید ستاره را در ابتداء، وسط یا انتهای رشتهای که میدانید قرار دهید:
*sheet
Bed*
B*t
همچنین میتوانید همان ایده را با ویژگی فیلتر اکسل به کار ببرید. فرض کنید فهرستی طولانی از کارمندان دارید و میخواهید تنها آنهایی را نمایش دهید که عنوان شغلی آنها شامل واژه «Manager» باشد، بدون توجه به آنچه پیش یا پس از آن میآید، مانند Regional Manager یا Manager II. در جعبه فیلتر برای ستون عنوان شغلی، میتوانید فیلترهای متن -> شامل را انتخاب کنید و معیار *Manager* را وارد کنید. اکسل سپس هر عنوانی که شامل این واژه باشد را نشان میدهد و شما را از مرتبسازی دستی زیاد نجات میدهد.
همچنین میتوانید ستاره را مستقیماً در فرمولها برای تطبیق جزئی به کار ببرید. بهعنوان مثال، اگر بخواهید نام نماینده فروش خود را بر پایه مطابقت جزئی شهر جستجو کنید، میتوانید از فرمول زیر استفاده کنید:
=FILTER(C2:C30, ISNUMBER(SEARCH("Lagos*", B2:B30)))
اضافه کردن یک فاصله قبل از کاراکتر جایگزین نتایج را حتی دقیقتر میکند و تنها مقادیر Mark King را که مکان آن «Lagos Region, Nigeria» است، برمیگرداند:
=FILTER(C2:C30, ISNUMBER(SEARCH("Lagos *", B2:B30)))
بهطور خلاصه، ستاره به اکسل میگوید که هر متنی که با الگوی شناختهشده شما سازگار باشد را جستجو کند. علاوه بر FILTER، این کاراکتر در توابع دیگری که از معیارها یا تطبیق الگو استفاده میکنند، مانند COUNTIF، MATCH و AVERAGEIF نیز کار میکند.
کاراکتر جایگزین علامت سؤال (?)
برای مطابقت متن وقتی فقط چند کاراکتر متفاوت هستند
این کاراکتر جایگزین هر کاراکتر تک را نمایندگی میکند. بهعنوان یک جایگزین برای دقیقاً یک کاراکتر در یک رشته متن، بهویژه وقتی با دادههای ثابتطول یا رفع ناسازگاریها کار میکنید، بسیار مفید است.
من اغلب هنگام رفع مشکل یا شمارش رشتههایی که باید قالب یا طول خاصی را دنبال کنند از آن استفاده میکنم. برای مثال، در تابع COUNTIF اکسل، میتوانید از تعدادی علامت سؤال برای شمارش سلولهایی استفاده کنید که دقیقاً تعداد کاراکترهای مشخصی دارند. اگر ستونی از شناسههای فاکتور داشته باشید و بخواهید تعداد سلولهایی که دقیقاً شش کاراکتر طول دارند را شمارش کنید، میتوانید از فرمول زیر استفاده کنید:
=COUNTIF(A2:A6, "??????")
اکسل هر کاراکتر را شامل میشود، حتی نقطهگذاری. بنابراین، یک فاکتور مانند INV-002 بهعنوان شش کاراکتر شمارش نمیشود زیرا خط تیره نیز بهعنوان کاراکتر در نظر گرفته میشود.
میتوانید از علامت سؤال در تقریباً تمام همان توابع و ویژگیهای اکسل که از ستاره پشتیبانی میکنند، استفاده کنید. تفاوت فقط این است که علامت سؤال دقیقاً یک کاراکتر را مطابقت میدهد، در حالی که ستاره میتواند هر تعداد کاراکتر را نمایان سازد. بهعنوان مثال، اگر از Find and Replace برای جستجوی «p??ce» استفاده کنید، اکسل هر دو «piece» و «peace» را پیدا میکند.
این کاراکتر جایگزین در قالببندی شرطی نیز مفید است. فرض کنید لیستی از کدهای محصول در سلولهای D2:D6 دارید که هر کدام باید دقیقاً پنج کاراکتر طول داشته باشند (مانند CODE1). میتوانید از این فرمول برای پرچمگذاری هر ورودی که طول صحیح ندارد استفاده کنید:
=COUNTIF(D2,"?????")=0
اگر محتوای یک سلول با معیار «?????»، تابع COUNTIF مقدار TRUE (یا 1) بر میگرداند. اگر نه، بهدلیل طول بیش از حد یا کم بودن کد، مقدار FALSE (یا 0) بر میگردد.
برای اعمال این بهصورت بصری، محدوده سلولهای خود را انتخاب کنید، به خانه -> قالببندی شرطی -> قانون جدید… بروید و گزینه استفاده از فرمول برای تعیین سلولهای قالببندیشده را انتخاب کنید. فرمول بالا را وارد کنید، سپس قالببندی… را کلیک کنید تا رنگ پر (مانند قرمز) را برای سلولهایی که قانون را نقض میکنند انتخاب کنید. پس از کلیک بر تأیید، اکسل هر کد محصولی که دقیقاً پنج کاراکتر نباشد برجسته میکند.
این یک روش سریع و قابلاعتماد برای اعتبارسنجی دادههای شماست و یکی از کاربردهای عملی کاراکتر جایگزین علامت سؤال است.
کاراکتر جایگزین تمهید (~)
برای موارد نادری که واقعاً نیاز دارید بهجای * یا ? جستجو کنید
در اکسل، ستاره و علامت سؤال معمولاً بهعنوان کاراکترهای جایگزین در نظر گرفته میشوند. اما اگر بخواهید اکسل آنها را بهصورت لغوی، یعنی همان کاراکترهای واقعی، در نظر بگیرد، باید قبل از آنها تمهید (~) بگذارید. حال ممکن است بپرسید در چه شرایطی نیاز دارید تا اکسل کاراکترهای جایگزین را بهصورت لغوی تفسیر کند. بله، چنین شرایطی وجود دارد.
فرض کنید فهرستی از شناسهها دارید که برخی در میانهشان ستاره دارند تا بخشها را جدا کنند (مثلاً P*Laos*45 یا S*Bahrain*05)، که نشاندهنده این است که آیا یک محصول (P) یا یک سرویس (S) است، به همراه مکان و واحد سفارش. اگر بخواهید تنها محصولاتی که در لائوس سفارشدادهشدهاند را شمارش کنید، میتوانید با استفاده از تمهید، اکسل را مجبور کنید که ستاره ابتدایی را بهعنوان کاراکتر عادی در نظر بگیرد. فرمول زیر این کار را انجام میدهد:
=COUNTIF(A1:A43, "P~*Laos*")
این فرمول به اکسل میگوید که ستارهٔ اول را بهعنوان کاراکتر عادی در نظر بگیرد و ستارهٔ آخر را بهعنوان جایگزین برای هر متن پس از «Laos» استفاده کند. اگر آن ستارهٔ نهایی را حذف کنید، اکسل فقط بهدنبال تطبیق دقیق «P*Laos» خواهد گشت و بقیه را نادیده میگیرد.
همین ایده برای کاراکتر جایگزین علامت سؤال نیز صادق است. تصور کنید با پاسخهای نظرسنجی کار میکنید و میخواهید تمام مواردی که شامل یک علامت سؤال واقعی (?) هستند را بیابید، شاید برای مرور سریع سؤالاتی که شرکتکنندگان وارد کردهاند. در کادر Find and Replace (Ctrl + F)، بهجای «?»، «~?» را وارد کنید. اکسل سپس تمام سلولهایی را که حاوی کاراکتر واقعی علامت سؤال هستند، نشان خواهد داد.
بهطور خلاصه، تمهید کاراکتر جایگزین کلید Escape شما برای کاراکترهای خاص است. هر زمان که بخواهید بهصورت لغوی به دنبال ?، * یا حتی یک تمهید دیگر (~) بگردید، پیش از آن یک تمهید بگذارید تا اکسل آن را بهعنوان کاراکتر عادی درک کند.
جستجو در اکسل بسیار سریعتر است وقتی که کاراکترهای جایگزین را مسلط شدهاید
بهمحض این که با کاراکترهای جایگزین راحت شوید، جستجو در اکسل کمتر شبیه بهدنبال سوزن در انبار کاه و بیشتر شبیه به یک فیل در اتاق میشود. این نمادها ممکن است کوچک باشند، اما با آنها میتوانید دادههای خود را با دقت فیلتر، شمارش و مکانیابی کنید، حتی اگر جدول دادههای شما بسیار بزرگ باشد.
چه از ستاره برای جستجوهای انعطافپذیر، چه از علامت سؤال برای تطبیق دقیق، یا از تمهید برای کاراکترهای لغوی استفاده کنید، این ابزارهای کوچک میتوانند تفاوت بزرگی در سرعت یافتن آنچه نیاز دارید ایجاد کنند.