چگونه از تابع XLOOKUP در اکسل استفاده کنیم؟

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

تابع XLOOKUP درمقایسه‌با VLOOKUP، امکانات، انعطاف‌پذیری و کارایی بیشتری دارد. در ادامه ویژگی‌هایی که XLOOKUP را به تابع جست‌وجوی برتر در اکسل تبدیل می‌کند، آورده شده است:

جستجو در هر سمت

ارائه‌ی مقدار پیش‌ فرض در صورت پیدا نکردن نتیجه

VLOOKUP تنها می‌تواند یک خطای N/A# را در صورت پیدا نکردن نتیجه نمایش دهد؛ اما XLOOKUP می‌تواند مقدار پیش‌فرضی را به‌جای خطای N/A# نشان دهد که این ویژگی باعث بهبود تجربه‌ی کاربری می‌شود.

افزایش یا حذف ستون‌ ها

یکی از مسائل آزاردهنده با VLOOKUP این است که اضافه یا حذف‌کردن ستون‌ها باعث خراب‌شدن فرمول‌ها می‌شود؛ اما با XLOOKUP می‌توانید به هر تعداد که نیاز دارید ستون‌ها را اضافه یا حذف کنید، بدون اینکه چیزی خراب شود.

عملکرد بهتر در جست‌وجوهای بزرگ و پیچیده

XLOOKUP در جست‌وجوهای پیچیده و زمانی‌ که به جست‌وجو در چندین محدوده‌ی داده نیاز دارید، عملکرد بهتری نسبت به VLOOKUP دارد.

در نهایت، تفاوت vlookup و xlookup باعث می‌شود که xlookup به‌دلیل انعطاف‌پذیری بیشتر و سهولت استفاده، گزینه‌ای برتر محسوب شود.

ساختار تابع XLOOKUP به‌صورت زیر است:

• lookup_value: مقداری که می‌خواهید جست‌وجو کنید. می‌تواند یک عدد، متن یا ارجاع به یک سلول باشد.
• lookup_array: محدوده‌ای که می‌خواهید به‌دنبال مقدار جست‌وجو بگردید. باید یک ردیف یا ستون باشد.
• return_array: محدوده‌ای که می‌خواهید نتیجه‌ی جست‌وجو از آن استخراج شود. اندازه‌ی آن باید با lookup_array هم‌خوانی داشته باشد.
• if_not_found (اختیاری): مقدار پیش‌فرضی که اگر نتیجه‌ای پیدا نشد، نمایش داده شود. اگر این آرگومان وارد نشود، به‌طور پیش‌فرض خطای N/A# نمایش داده می‌شود.
• match_mode (اختیاری): نوع تطبیق را مشخص می‌کند. مقدار صفر برای تطبیق دقیق (پیش‌فرض)، مقدار ۱- برای یافتن مقدار کمتر یا مساوی مقدار جست‌جو، مقدار ۱ برای مقدار بیشتر یا مساوی، و مقدار ۲ برای تطبیق با الگو (مثل استفاده از *).
• search_mode (اختیاری): می‌توانید مشخص کنید که جست‌وجو از بالا به پایین (که پیش‌فرض است) انجام شود یا از پایین به بالا. اگر مقدار search_mode برابر با یک باشد، جست‌وجو از ابتدا (بالا به پایین) محدوده صورت می‌گیرد. در صورتی‌که مقدار آن ۱- باشد، جست‌وجو از انتها (پایین به بالا) انجام خواهد شد.

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

مثال: نمایش اطلاعات مربوط به گوشی براساس کد محصول

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

۱- روی سلولی کلیک کنید که می‌خواهید داده‌ی مورد نظر در آن نمایش داده شود. در این مثال، سلول G7 را انتخاب می‌کنیم.

۲- عبارت زیر را وارد کنید، دکمه‌ی اینتر را فشار دهید. اکسل به‌طور خودکار یک پرانتز باز اضافه می‌کند و فرمول به این شکل خواهد بود:

۳- مقادیر زیر را بعد از پرانتز بنویسید و بین آن‌ها از کاما استفاده کنید:

• مقدار جست‌وجو به‌طور مطلق: $F$7
• محدوده‌ی جست‌وجو به‌طور مطلق: $A$2:$A$7
• محدوده مورد نظر برای بازگشت داده‌ها (مثلاً ستون نام گوشی): B2:B7

۴- پرانتز را ببندید تا فرمول کامل شود:

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

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

آرگومان if_not_found:

فرض کنید در جست‌وجوی کد محصول ۸۰۰ باشید که در جدول وجود ندارد. کد زیر را در بخش نام  گوشی وارد می‌کنیم:

برای گسترش فرمول به دو سلول دیگر از ابزار AutoFill استفاده می‌کنیم. این فرمول در صورت پیدانکردن کد محصول، مقدار «not found» را در ستون‌های مربوطه نمایش می‌دهد.

آرگومان match_mode:

تابع XLOOKUP امکان کنترل نحوه‌ی جست‌وجو را فراهم می‌کند:

• صفر: جست‌وجو دقیق باشد.

• ۱-: به‌صورت دقیق جست‌جو می‌کند؛ اما اگر پیدا نشد، مقدار کوچک برگردانده شود. به‌عنوان مثال اگر عدد ۱۱۱ را وارد کنیم، چون بین ۱۱۰ و ۱۹۰ قرار دارد، مقادیر مربوط به ۱۱۰ را برمی‌گرداند.

• ۱: به‌صورت دقیق جست‌وجو می‌کند؛ اما اگر پیدا نشد، مقدار بزرگ‌تر برگردانده می‌شود. به‌عنوان مثال اگر عدد ۱۱۱ را وارد کنیم، چون بین ۱۱۰ و ۱۹۰ قرار دارد، مقادیر ۱۹۰ را برمی‌گرداند.

• ۲: اگر بخواهیم از Wildcard کمک بگیریم، عدد ۲ را تایپ می‌کنیم، در آرگومان دوم باید محدوده‌ی نام گوشی یعنی B2:B7 را قرار بدهیم و مطلق کنیم، سپس برای گسترش فرمول به دو سلول دیگر، از ابزار AutoFill بهره می‌بریم.

برای افرادی که اطلاع ندارد، باید بگوییم که وایلد کارد در اکسل (Wildcard) کاراکترهایی هستند که قابلیت‌های خاصی را به توابع هنگام کار با مقادیر متنی می‌دهند.

• به‌عنوان مثال، اگر در کد محصول ش* وارد کنیم، اکسل به‌دنبال نام محصولی می‌گردد که با حرف «ش» شروع می‌شود.

آرگومان search_mode:

آرگومان search_mode تعیین می‌کند که اکسل چگونه داده‌ها را جست‌وجو کند. این آرگومان اختیاری مشخص می‌کند که جست‌وجو از ابتدا یا انتهای محدوده انجام شود و همچنین نوع تطبیق (ترتیب مرتب‌سازی داده‌ها) چگونه باشد. آرگومان اختیاری search_mode چهار حالت مختلف دارد:

• ۱: عملیات جست‌وجو را از ابتدای محدوده شروع می‌کند. (پیش‌فرض)
• ۱-: عملیات جست‌وجو را از انتهای محدوده انجام می‌دهد.
• ۲: جست‌وجوی دودویی (binary search) روی داده‌های صعودی مرتب‌شده (Sort) انجام می‌دهد.
• ۲-: جست‌وجوی دودویی (binary search) روی داده‌های نزولی مرتب‌شده انجام می‌دهد.

وقتی از مقادیر ۲ یا ۲- استفاده می‌کنید، داده‌ها باید به‌ترتیب صعودی (۲) یا نزولی (۲-) مرتب شده باشند. اگر داده‌های شما مرتب نیستند، از مقادیر ۲ یا ۲- استفاده نکنید؛ زیرا نتیجه‌ی تابع ممکن است نادرست باشد.

تابع XLOOKUP علاوه‌بر جست‌وجوی عمودی، قابلیت جست‌‌وجوی افقی را نیز دارد. برای این‌ کار کافی است محدوده‌ی داده‌های خود را به حالت افقی تبدیل کنید. به این ترتیب:

• ابتدا از جدول خود کپی بگیرید، سپس با استفاده از ابزار Transpose، داده‌ها را به‌صورت افقی در سلول‌های جدید درج کنید. حالا می‌توانید با استفاده از تابع XLOOKUP به‌راحتی در این محدوده‌ی افقی جست‌وجو کنید.

فرمول جست‌وجوی افقی برای نمایش نام گوشی به‌صورت زیر تعریف می‌شود:

سپس با استفاده از ابزار AutoFill فرمول را به سایر سلول‌ها (تعداد فروش در ماه و قیمت) گسترش می‌دهیم؛ اما یادتان نرود که پس از AutoFill، باید محدوده‌های تعداد فروش در ماه و قیمت در آرگومان سوم هم تغییر کند:

برای تعداد فروش در ماه:

برای قیمت:

به عنوان مثال، با وارد کردن کد ۳۵۹، اطلاعات در ردیف‌های مربوط نمایش داده می‌شود.

تابع XLOOKUP یکی از ابزارهای قدرتمند اکسل است که قابلیت جست‌وجوی پیشرفته، مدیریت خطا و انعطاف‌پذیری بالا را فراهم می‌کند. با استفاده از این تابع، می‌توانید به‌راحتی اطلاعات مورد نظر را از جداول به‌صورت عمودی یا افقی استخراج کنید.

شما بیشتر از XLOOKUP استفاده می‌کنید یا VLOOKUP؟ به‌نظر شما کدام‌یک از این دو فرمول برای کارهای روزمره و حرفه‌ای مناسب‌تر است؟ تجربیات خودتان را در بخش نظرات با ما به‌اشتراک بگذارید.