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

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

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

4
XLOOKUP توابع مورد علاقه من

خیلی بهتر از VLOOKUP

من برای مدت طولانی از VLOOKUP استفاده کرده‌ام، اما این تابع در مواجهه با داده‌های پیچیده محدودیت‌هایی دارد. از سوی دیگر، XLOOKUP اکثر این مشکلات را برطرف می‌کند و عملکرد و انعطاف‌پذیری بهتری ارائه می‌دهد.

هنگام استفاده از XLOOKUP، شما ستون‌های جستجو و بازگشت را به طور جداگانه انتخاب می‌کنید، به این معنی که می‌توانید سرنوشتاً به سمت چپ ستون جستجو نگاه کنید بدون اینکه داده‌هایتان را جابجا کنید. این یکی از دلایلی است که XLOOKUP بهتر از VLOOKUP است.

XLOOKUP دارای ساختار زیر است:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

بیایید پارامترها را بررسی کنیم:

• lookup_value: آنچه شما به دنبال آن هستید.
• lookup_array: بازه یا آرایه‌ای که به‌درّی آن جستجو می‌کنید.
• return_array: مقادیری که می‌خواهید بازیابی کنید.

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

• [if_not_found]: به شما امکان می‌دهد پیام‌های خطای سفارشی تعیین کنید به‌جای اینکه خطاهای رایج اکسل مانند #N/A را ببینید و نیاز به IFERROR را از بین می‌برد.
• [match_mode]: امکان مطابقت دقیق، کاراکترهای جایگزین (wildcards) یا مطابقت تقریبی را فراهم می‌کند.
• [search_mode]: تعیین می‌کند که اکسل از ابتدا به انتها یا از انتها به ابتدا جستجو کند.

بیایید یک مثال مرتبط با اداره را بررسی کنیم. اگر آدرس ایمیل یک کارمند را داشته باشید و نیاز به یافتن نام کامل او داشته باشید، باید با نگاه به سمت چپ این کار را انجام دهید. VLOOKUP به راحتی این کار را نمی‌تواند انجام دهد زیرا ستون «Full Name» در سمت چپ ستون «AD Email» قرار دارد. اما این کار با XLOOKUP امکان‌پذیر است.

به عنوان مثال، اگر آدرس ایمیل uriah.bridges@bilearner.com را در سلول J2 داشته باشید، می‌توانید از این فرمول برای یافتن نام کامل استفاده کنید:

=XLOOKUP(J2, J:J, D:D, "Employee Not Found")

در اینجا، فرمول ایمیل را در ستون J جستجو می‌کند و نام مربوطه را از ستون D برمی‌گرداند. اگر ایمیل یافت نشود، به‌طور تمیز «Employee Not Found» را به‌جای خطای ناخوشایند #N/A برمی‌گرداند. این مدیریت خطای داخلی باعث می‌شود ابزار مقاوم‌تر و کاربرپسندتر باشد.

3
من LET و XLOOKUP را ترکیب می‌کنم

از بین بردن محاسبات تکراری

فرمول‌ها گاهی می‌توانند طولانی و پیچیده شوند، به‌ویژه وقتی که نیاز به انجام محاسبهٔ یکسان چندین بار در یک فرمول داشته باشید. این نه تنها فرمول را خواندن دشوار می‌کند بلکه عملکرد را نیز تحت تأثیر قرار می‌دهد، چرا که اکسل مجبور است نتایج یکسان را بارها محاسبه کند.

راه‌حل این مسئله، تابع LET است که امکان اعلان متغیرها را مستقیماً درون یک فرمول می‌دهد. شما چیزی را یک بار محاسبه می‌کنید، نامی به آن می‌دهید و سپس هر زمان که به نتیجه نیاز دارید، از همان نام استفاده می‌کنید. ساختار ساده است:

=LET(name1, name_value1, [name2, name_value2], calculation)

• name1: نام محاسبهٔ اول شما (مثلاً «rating»).
• name_value1: خود محاسبه (مثلاً یک فرمول XLOOKUP).
• calculation: فرمول نهایی که از نام تعریف‌شده استفاده می‌کند.

فرض کنید من یک وظیفه دارم که نیاز به اختصاص وضعیت بر اساس امتیاز فعلی یک کارمند دارد. اگر امتیاز آن (با استفاده از شناسهٔ کارمندشان) پنج باشد، وضعیت «Top Tier» است. اگر یک باشد، «On Watchlist» است. در غیر این صورت، «Standard».

بدون استفاده از LET، مجبور می‌شدم تابع XLOOKUP را دو بار بنویسم، به‌صورت زیر:

=IF(XLOOKUP(A2, A:A, AC:AC)=5, "Top Tier", IF(XLOOKUP(A2, A:A, AC:AC)=1, "On Watchlist", "Standard"))

اما با استفاده از LET، فرمول بسیار تمیزتر می‌شود. من XLOOKUP را یک بار محاسبه می‌کنم و به نام «rating» اختصاص می‌دهم.

=LET(rating, XLOOKUP(A2, A:A, AC:AC), IF(rating=5, "Top Tier", IF(rating=1, "On Watchlist", "Standard")))

این فرمول شناسهٔ کارمند را از سلول A2 در ستون A جستجو می‌کند، امتیاز عددی او را از ستون AC بازیابی می‌کند و سپس عملکرد را دسته‌بندی می‌کند.

تابع LET یک بار جستجوی امتیاز را ذخیره می‌کند و از تکرار محاسبهٔ XLOOKUP در عبارات IF تو در تو جلوگیری می‌کند. این فرمول به‌طرزی چشمگیری خواناتر و قابل اشکال‌زدایی است.

2
INDEX و MATCH به‌خوبی با هم کار می‌کنند

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

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

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

ابتدا به ساختار MATCH نگاه می‌کنیم. این به شما می‌گوید داده‌ها کجا قرار دارند.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: مقدار مورد جستجو.
• lookup_array: ستون یا ردیف تک‌تایی که در آن جستجو می‌کنید.
• [match_type]: برای مطابقت دقیق از مقدار 0 استفاده کنید، که در 99٪ موارد مورد نیاز است.

بعد، ساختار INDEX که بر پایه موقعیتی که MATCH فراهم می‌کند، مقدار مورد نظر را بازیابی می‌کند.

=INDEX(array, row_num, [column_num])

• array: بازهٔ سلول‌ها یا آرایه‌ای که از آن مقدار بازیابی می‌شود.
• row_num: شماره ردیفی که مقدار از آن استخراج می‌شود.

فرض کنید می‌خواهم ناحیهٔ پرداخت برای کارمندی با شناسه 3,427 را پیدا کنم. من فرمول زیر را استفاده می‌کنم:

=INDEX(F:F, MATCH(A2, A:A, 0))

در این مثال، بخش MATCH ابتدا شماره ردیف برای شناسهٔ کارمند موجود در سلول A2 را پیدا می‌کند. سپس بخش INDEX آن شماره را می‌گیرد و مقدار مربوطه را از همان ردیف در ستون ناحیهٔ پرداخت (که ستون F است) برمی‌گرداند. این روش سبک و کارآمد است.

1
FILTER تابع نهایی جستجو است

جایگزین چندین جستجو به‌طور همزمان می‌شود

جستجوهای استاندارد، حتی XLOOKUP، محدودیت اساسی دارند—آنها پس از یافتن اولین تطابق، جستجو را متوقف می‌کنند و همان را برمی‌گردانند. اما اگر به فهرستی از تمام رکوردهای منطبق نیاز داشته باشید چه می‌شود؟ در نسخه‌های اکسل پیش از 2021، این نیاز به فرمول‌های آرایه‌ای پیچیده داشت. اکنون من به‌جای آن از تابع FILTER استفاده می‌کنم. این تابع هر رکوردی که با معیارهای شما مطابقت داشته باشد استخراج می‌کند.

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

ساختار تابع FILTER به شرح زیر است:

=FILTER(array, include, [if_empty])

• array: کل بازهٔ داده‌هایی که می‌خواهید نتایج از آن استخراج شود. می‌تواند یک ستون یا چندین ستون باشد.
• include: تست منطقی شماست. یک بازهٔ سلول به‌ضمیمهٔ یک شرط، مانند بررسی این‌که آیا مقدار یک ستون برابر با «Active» است.
• [if_empty]: آرگومان اختیاری برای نمایش چیزی در صورتی که هیچ نتیجه‌ای یافت نشود.

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

این فرمول کار را انجام می‌دهد:

=FILTER(D:F, I:I="Peter Oneill", "No Employees Found")

این فرمول به اکسل می‌گوید داده‌ها را از ستون‌های D تا F برای هر ردیفی که مقدار در ستون I برابر با «Peter O’Neill» باشد، برگرداند.

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

این چهار رویکرد تمام سناریوهای جستجویی که با آن‌ها مواجه می‌شوم را پوشش می‌دهد. XLOOKUP جایگزین مدرن VLOOKUP است. من ترکیب‌های LET را برای بهینه‌سازی استفاده می‌کنم، INDEX/MATCH برای سرعت خالص، و FILTER برای نتایج متعدد.

منبع مقاله