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

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

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

این عملکرد هنگام کار با داده های زیاد در یک برگه اکسل مفید است. در اکسل انواع مختلفی از توابع LOOKUP وجود دارد.

در این مقاله از سایت لرن پارسی ، همه آنچه را که باید در مورد عملکرد XLOOKUP در مایکروسافت اکسل بدانید، به همراه چند مثال مفید به شما خواهیم گفت.

تابع XLOOKUP در اکسل چیست؟

XLOOKUP جایگزین توابع قدیمی مانند HLOOKUP، VLOOKUP و LOOKUP می شود. از جستجوهای عمودی و افقی پشتیبانی می کند.

به عبارت دیگر، تابع XLOOKUP به شما این امکان را می دهد که به سرعت یک مقدار را در یک مجموعه داده ،  هم به صورت افقی و هم به صورت عمودی، پیدا کنید و مقدار متناظر آن را در یک سطر یا ستون متفاوت برمی گرداند.

راه های زیادی برای استفاده از تابع XLOOKUP وجود دارد تا بیشترین بهره را از آن ببرید. ما نگاهی به برخی از نمونه‌های XLOOKUP خواهیم داشت که به شما درک بهتری از آن می‌دهد.

نحوه دسترسی به تابع XLOOKUP

متأسفانه XLOOKUP برای کاربرانی که نسخه‌های آفیس ۲۰۱۰، ۲۰۱۳، ۲۰۱۶ و ۲۰۱۹ را اجرا می‌کنند، در دسترس نیست. این تابع فقط در مجموعه Microsoft Office 365 در دسترس است.

اگر از مجموعه Microsoft 365 در رایانه خود استفاده نمی کنید، ممکن است برای دسترسی به عملکرد XLOOKUP نیاز به ارتقا به Microsoft 365 داشته باشید. و اگر قبلاً از مایکروسافت ۳۶۵ استفاده می کنید، گزینه ای را که قبلاً در اکسل فعال شده است خواهید یافت. XLOOKUP در Office 365 Online نیز موجود است .

Syntax  برای تابع XLOOKUP

سینتکس تابع XLOOKUP شبیه به VLOOKUP و HLOOKUP است. اگر تا به حال از آنها استفاده کرده اید، استفاده از XLOOKUP راحت تر است. در اینجا Syntax تابع XLOOKUP در اکسل آمده است:

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

تابع XLOOKUP تا شش آرگومان را پشتیبانی می کند و در اینجا مقادیر آنها آمده است.

1. lookup_value (لازم): مقداری که می خواهید پیدا کنید.
2. lookup_array (لازم): آرایه ای که می خواهید به دنبال مقدار جستجو بگردید.
3. return_array (لازم): آرایه ای که می خواهید با یافتن مقدار جستجو، مقادیر را از جایی که می خواهید واکشی کنید و برگردانید.
4. [if_not_found] (اختیاری): وقتی مطابقت پیدا نشد، این مقداری است که باید برگردانده شود.
5. [match_mode] (اختیاری): این آرگومان به شما امکان می دهد نوع مسابقه ای را که به دنبال آن هستید مشخص کنید. مقادیر مختلفی برای تعیین آن وجود دارد:
   ۰ – به دنبال یک تطابق دقیق است و مقدار باید دقیقاً با مقدار lookup_array مطابقت داشته باشد. همچنین زمانی که ذکر نشده است به عنوان پیش فرض تنظیم می شود.
   ۱- – به دنبال مطابقت دقیق می گردد و پس از یافتن به مقدار کوچکتر بعدی برمی گردد.
   ۱ – به دنبال مطابقت دقیق می گردد و پس از یافتن به مقدار بزرگتر بعدی برمی گردد.
   ۲ – تطبیق جزئی را با استفاده از حروف عام انجام می دهد که در آن * , ? و ~ معنی خاصی دارند.
6. [search_mode] (اختیاری): برای تعیین حالت جستجوی XLOOKUP در lookup_array استفاده می شود. مقادیر مختلفی برای تعیین یکسان وجود دارد:
   ۱ – جستجو را با شروع از اولین مورد انجام می دهد. زمانی که چیزی مشخص نشده باشد به عنوان پیش فرض تنظیم می شود.
   ۱- – جستجوی معکوس را با شروع آخرین مورد انجام می دهد.
   ۲ – جستجوی باینری را در آرایه lookup_ انجام می دهد که در آن داده ها به ترتیب صعودی نیاز به مرتب سازی دارند. اگر داده‌ها مرتب نشده باشند، ممکن است خطا یا نتایج اشتباهی ایجاد کند.
   ۲- – جستجوی باینری را در آرایه lookup_ انجام می دهد که در آن داده ها به ترتیب نزولی نیاز به مرتب سازی دارند. اگر داده‌ها مرتب نشده باشند، ممکن است خطا یا نتایج اشتباهی ایجاد کند.

مزایا و معایب تابع XLOOKUP در اکسل

XLOOKUP هنوز هم تابعی است که مزایای خود را نسبت به VLOOKUP و INDEX/MATCH دارد. اما معایبی هم دارد.

مزایای عملکرد XLOOKUP

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

معایب عملکرد XLOOKUP

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

نحوه استفاده از تابع XLOOKUP در اکسل

تابع XLOOKUP مشابه تابع LOOKUP در اکسل کار می کند. شما می توانید از XLOOKUP به سادگی با انتخاب مراجع سلولی که در آن عملکرد باید عمل کند، استفاده کنید.

از طرف دیگر، می توانید از کادر “نوار فرمول” واقع در بالا نیز استفاده کنید. و سینتکس تابع XLOOKUP را در آنجا وارد کنید.

نمونه هایی از تابع XLOOKUP

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

مثال ۱

بیایید فرض کنیم شما مجموعه داده ای دارید که شبیه این است (در تصویر زیر) و می خواهید نمره Science  دانش آموز Matthew  را در سلول های F و G دریافت کنید.

و برای بدست آوردن این داده ها، از تابع XLOOKUP زیر در سلول F2 استفاده می کنیم :

=XLOOKUP(F2,A2:A15,B2:B15)

و این فرمول منجر به نمره علوم متیو در سلول بعدی می شود. این به این دلیل است که ما مقدار جستجو را به عنوان F2 دادیم که در مثال زیر Matthew است.

مثال ۲

ساده ترین مثال از تابع VLOOKUP را می توانید از شکل زیر مشاهده کنید.  همانگونه که در مثال پایین مشاهده می کنید ،  برای این تابع مهم نیست که محدوده و یا سلولی که میخواهید بعد از جستجو نمایش داده شود ، قبل از محدوده جستجو باشد یا بعد از آن.

=XLOOKUP(G2,C2:C23,B2:B23)

منبع تصویر : آفیس باز

• مرتبط : نحوه استفاده از تابع AVERAGEIF در اکسل

به جای VLOOKUP از XLOOKUP استفاده کنید

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

با این حال، می توانید از Office 365 Online به XLOOKUP دسترسی پیدا کنید. عملکرد XLOOKUP هنوز در مراحل اولیه خود است، بنابراین کاربران اکسل کمی زمان می‌برند تا آن را بپذیرند.