تابع XLOOKUP جایگزین پیشرفته ای برای VLOOKUP است که امکان جست و جو در هر دو جهت و بازگشت چندین نتیجه را فراهم می کند.
تابع XLOOKUP در اکسل به عنوان جایگزین پیشرفته تری برای تابع VLOOKUP محسوب می شود و بسیاری از محدودیت های آن را برطرف کرده است. این تابع می تواند به راحتی مقادیر را در یک ستون جست وجو کند و مقدار مرتبط را در ستون دیگر بازگرداند. در ادامه به طور دقیق تر توضیح می دهیم که XLOOKUP چطور کار می کند.
چرا XLOOKUP بهتر از VLOOKUP است؟
تابع XLOOKUP درمقایسه با VLOOKUP، امکانات، انعطاف پذیری و کارایی بیشتری دارد. در ادامه ویژگی هایی که XLOOKUP را به تابع جست وجوی برتر در اکسل تبدیل می کند، آورده شده است:
جستجو در هر سمت
VLOOKUP فقط قادر است از یک جهت جست وجو کند، یعنی تنها می تواند مقادیری را در ستون های بعد از ستون جست وجو پیدا کند؛ اما XLOOKUP این محدودیت را ندارد و می تواند به هر جهت (راست، چپ، پایین یا بالا) جست وجو را انجام دهد.
ارائه ی مقدار پیش فرض در صورت پیدا نکردن نتیجه
VLOOKUP تنها می تواند یک خطای N/A# را در صورت پیدا نکردن نتیجه نمایش دهد؛ اما XLOOKUP می تواند مقدار پیش فرضی را به جای خطای N/A# نشان دهد که این ویژگی باعث بهبود تجربه ی کاربری می شود.
افزایش یا حذف ستون ها
یکی از مسائل آزاردهنده با VLOOKUP این است که اضافه یا حذف کردن ستون ها باعث خراب شدن فرمول ها می شود؛ اما با XLOOKUP می توانید به هر تعداد که نیاز دارید ستون ها را اضافه یا حذف کنید، بدون اینکه چیزی خراب شود.
عملکرد بهتر در جست وجوهای بزرگ و پیچیده
XLOOKUP در جست وجوهای پیچیده و زمانی که به جست وجو در چندین محدوده ی داده نیاز دارید، عملکرد بهتری نسبت به VLOOKUP دارد.
در نهایت، تفاوت vlookup و xlookup باعث می شود که xlookup به دلیل انعطاف پذیری بیشتر و سهولت استفاده، گزینه ای برتر محسوب شود.
نحوه ی استفاده از تابع xlookup در اکسل
ساختار تابع XLOOKUP به صورت زیر است:
lookup_value: مقداری که می خواهید جست وجو کنید. می تواند یک عدد، متن یا ارجاع به یک سلول باشد.
lookup_array: محدوده ای که می خواهید به دنبال مقدار جست وجو بگردید. باید یک ردیف یا ستون باشد.
return_array: محدوده ای که می خواهید نتیجه ی جست وجو از آن استخراج شود. اندازه ی آن باید با lookup_array هم خوانی داشته باشد.
if_not_found (اختیاری): مقدار پیش فرضی که اگر نتیجه ای پیدا نشد، نمایش داده شود. اگر این آرگومان وارد نشود، به طور پیش فرض خطای N/A# نمایش داده می شود.
match_mode (اختیاری): نوع تطبیق را مشخص می کند. مقدار صفر برای تطبیق دقیق (پیش فرض)، مقدار ۱- برای یافتن مقدار کمتر یا مساوی مقدار جست جو، مقدار ۱ برای مقدار بیشتر یا مساوی، و مقدار ۲ برای تطبیق با الگو (مثل استفاده از *).
search_mode (اختیاری): می توانید مشخص کنید که جست وجو از بالا به پایین (که پیش فرض است) انجام شود یا از پایین به بالا. اگر مقدار search_mode برابر با یک باشد، جست وجو از ابتدا (بالا به پایین) محدوده صورت می گیرد. در صورتی که مقدار آن ۱- باشد، جست وجو از انتها (پایین به بالا) انجام خواهد شد.
شاید توضیحات بالا کمی پیچیده به نظر برسند؛ اما با دیدن مثال زیر، متوجه می شوید که این تابع به سادگی قابل استفاده است.
مثال: نمایش اطلاعات مربوط به گوشی براساس کد محصول
یک صفحه ی اکسل شامل کد محصول، نام گوشی، تعداد فروش در ماه و قیمت داریم و هدف ما این است که با وارد کردن کد محصول، اطلاعات آن ردیف را مشاهده کنیم.
۱- روی سلولی کلیک کنید که می خواهید داده ی مورد نظر در آن نمایش داده شود. در این مثال، سلول G7 را انتخاب می کنیم.
۲- عبارت زیر را وارد کنید، دکمه ی اینتر را فشار دهید. اکسل به طور خودکار یک پرانتز باز اضافه می کند و فرمول به این شکل خواهد بود:
=XLOOKUP(
۳- مقادیر زیر را بعد از پرانتز بنویسید و بین آن ها از کاما استفاده کنید:
مقدار جست وجو به طور مطلق: $F$7
محدوده ی جست وجو به طور مطلق: $A$2:$A$7
محدوده مورد نظر برای بازگشت داده ها (مثلاً ستون نام گوشی): B2:B7
در فرمول های XLOOKUP، برای ثابت نگه داشتن محدوده ی جست وجو هنگام کپی یا AutoFill فرمول، از علامت دلار (مطلق کردن) استفاده کنید. در غیر این صورت، آدرس ها تغییر می کند و ممکن است به داده های اشتباه ارجاع دهند.
۴- پرانتز را ببندید تا فرمول کامل شود:
=XLOOKUP($F$7,$A$2:$A$7,B2:B7)
۵- پس از واردکردن فرمول XLOOKUP در اولین سلول، یعنی نام گوشی، با استفاده از ابزار AutoFill فرمول را به سایر سلول ها گسترش می دهیم تا به طور خودکار به جست وجوی اطلاعات در دیگر ردیف ها بپردازد.
استفاده از آرگومان های اختیاری در تابع xlookup
آرگومان های فوق اجباری بودند و باید در فرمول استفاده می شدند. اگر قصد دارید از آرگومان های اختیاری استفاده کنید، در ادامه به بررسی آن ها می پردازیم که به شما کمک می کند تا تغییرات بیشتری اعمال کنید.
آرگومان if_not_found:
فرض کنید در جست وجوی کد محصول ۸۰۰ باشید که در جدول وجود ندارد. کد زیر را در بخش نام گوشی وارد می کنیم:
=XLOOKUP($F$7, $A$2:$A$7, B2:B7, “not found”)
برای گسترش فرمول به دو سلول دیگر از ابزار AutoFill استفاده می کنیم. این فرمول در صورت پیدانکردن کد محصول، مقدار «not found» را در ستون های مربوطه نمایش می دهد.
آرگومان match_mode:
تابع XLOOKUP امکان کنترل نحوه ی جست وجو را فراهم می کند:
صفر: جست وجو دقیق باشد.
=XLOOKUP($F$7, $A$2:$A$7, B2:B7, “not found”, 0)
۱-: به صورت دقیق جست جو می کند؛ اما اگر پیدا نشد، مقدار کوچک برگردانده شود. به عنوان مثال اگر عدد ۱۱۱ را وارد کنیم، چون بین ۱۱۰ و ۱۹۰ قرار دارد، مقادیر مربوط به ۱۱۰ را برمی گرداند.
=XLOOKUP($F$7, $A$2:$A$7, B2:B7, “not found”, -1)
۱: به صورت دقیق جست وجو می کند؛ اما اگر پیدا نشد، مقدار بزرگ تر برگردانده می شود. به عنوان مثال اگر عدد ۱۱۱ را وارد کنیم، چون بین ۱۱۰ و ۱۹۰ قرار دارد، مقادیر ۱۹۰ را برمی گرداند.
=XLOOKUP($F$7, $A$2:$A$7, B2:B7, “not found”, 1)
۲: اگر بخواهیم از Wildcard کمک بگیریم، عدد ۲ را تایپ می کنیم، در آرگومان دوم باید محدوده ی نام گوشی یعنی B2:B7 را قرار بدهیم و مطلق کنیم، سپس برای گسترش فرمول به دو سلول دیگر، از ابزار AutoFill بهره می بریم.
برای افرادی که اطلاع ندارد، باید بگوییم که وایلد کارد در اکسل (Wildcard) کاراکترهایی هستند که قابلیت های خاصی را به توابع هنگام کار با مقادیر متنی می دهند.
=XLOOKUP($F$7, $B$2:$B$7, B2:B7, “not found”, 2)
به عنوان مثال، اگر در کد محصول ش* وارد کنیم، اکسل به دنبال نام محصولی می گردد که با حرف «ش» شروع می شود.
آرگومان search_mode:
آرگومان search_mode تعیین می کند که اکسل چگونه داده ها را جست وجو کند. این آرگومان اختیاری مشخص می کند که جست وجو از ابتدا یا انتهای محدوده انجام شود و همچنین نوع تطبیق (ترتیب مرتب سازی داده ها) چگونه باشد. آرگومان اختیاری search_mode چهار حالت مختلف دارد:
۱: عملیات جست وجو را از ابتدای محدوده شروع می کند. (پیش فرض)
۱-: عملیات جست وجو را از انتهای محدوده انجام می دهد.
۲: جست وجوی دودویی (binary search) روی داده های صعودی مرتب شده (Sort) انجام می دهد.
۲-: جست وجوی دودویی (binary search) روی داده های نزولی مرتب شده انجام می دهد.
وقتی از مقادیر ۲ یا ۲- استفاده می کنید، داده ها باید به ترتیب صعودی (۲) یا نزولی (۲-) مرتب شده باشند. اگر داده های شما مرتب نیستند، از مقادیر ۲ یا ۲- استفاده نکنید؛ زیرا نتیجه ی تابع ممکن است نادرست باشد.
جست و جوی افقی در تابع xlookup
تابع XLOOKUP علاوه بر جست وجوی عمودی، قابلیت جست وجوی افقی را نیز دارد. برای این کار کافی است محدوده ی داده های خود را به حالت افقی تبدیل کنید. به این ترتیب:
ابتدا از جدول خود کپی بگیرید، سپس با استفاده از ابزار Transpose، داده ها را به صورت افقی در سلول های جدید درج کنید. حالا می توانید با استفاده از تابع XLOOKUP به راحتی در این محدوده ی افقی جست وجو کنید.
فرمول جست وجوی افقی برای نمایش نام گوشی به صورت زیر تعریف می شود:
=XLOOKUP($F$7, $B$9:$G$9, B10:G10)
سپس با استفاده از ابزار AutoFill فرمول را به سایر سلول ها (تعداد فروش در ماه و قیمت) گسترش می دهیم؛ اما یادتان نرود که پس از AutoFill، باید محدوده های تعداد فروش در ماه و قیمت در آرگومان سوم هم تغییر کند:
برای تعداد فروش در ماه:
=XLOOKUP($F$7, $B$9:$G$9, B11:G11)
برای قیمت:
=XLOOKUP($F$7, $B$9:$G$9, B12:G12)
به عنوان مثال، با وارد کردن کد ۳۵۹، اطلاعات در ردیف های مربوط نمایش داده می شود.
تابع XLOOKUP یکی از ابزارهای قدرتمند اکسل است که قابلیت جست وجوی پیشرفته، مدیریت خطا و انعطاف پذیری بالا را فراهم می کند. با استفاده از این تابع، می توانید به راحتی اطلاعات مورد نظر را از جداول به صورت عمودی یا افقی استخراج کنید.
منبع: زومیت