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

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2023-12-17 06:46

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

چگونه XLOOKUP کار می کند

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

این تابع همچنین به شما امکان می دهد هم یک ستون و یک ردیف را جستجو کنید و مقدار را در سلول متقاطع قرار دهید.

پارامترهای تابع XLOOKUP به شرح زیر است:

=XLOOKUP (مقدار_جستجو، آرایه_جستجو، آرایه_بازگشت، [مطابق_حالت]، [حالت_جستجو])

lookup_value: مقداری که می خواهید جستجو کنید
lookup_array: آرایه (ستون) مورد نظر برای جستجو
return_array: نتیجه (ستون) که می خواهید مقداری را ازبازیابی کنید
match_mode (اختیاری): یک تطابق دقیق (0)، یک تطابق دقیق یا کوچکترین مقدار بعدی (-1)، یا یک تطابق عام (2) انتخاب کنید.
search_mode (اختیاری): انتخاب کنید که آیا جستجو با اولین مورد در ستون (1)، آخرین مورد در ستون (-1)، جستجوی باینری صعودی (2) یا جستجوی باینری نزولی (-2).

در زیر چند مورد از رایج ترین جستجوهایی است که می توانید با عملکرد XLOOKUP انجام دهید.

چگونه با استفاده از XLOOKUP یک نتیجه را جستجو کنیم
ساده ترین راه برای استفاده از XLOOKUP این است که یک نتیجه را با استفاده از یک نقطه داده از یک ستون جستجو کنید.


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







اگر می خواهید اولین فروش را در لیست ارسال شده توسط یک نماینده فروش خاص پیدا کنید، می توانید یک تابع XLOOKUP ایجاد کنید که ستون Rep را برای یک نام جستجو می کند. تابع نتیجه را از ستون Total برمی گرداند. تابع XLOOKUP برای این است:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)

I2: به Rep Name سلول جستجواشاره می کند
C2:C44: این ستون Rep است که آرایه جستجو است
G2:G33: این ستون کل است که آرایه بازگشتی است
0: مطابقت دقیق را انتخاب می کند
1: اولین مسابقه را در نتایج انتخاب می کند



وقتی Enter را فشار می دهید و نام نماینده فروش را تایپ می کنید، سلول نتیجه کل اولین نتیجه را در جدول برای آن نماینده فروش به شما نشان می دهد.







اگر می خواهید آخرین فروش را جستجو کنید (از آنجایی که جدول بر اساس تاریخ به ترتیب معکوس مرتب شده است)، آخرین آرگومان XLOOKUP را به - 1 تغییر دهید، که شروع می شود. جستجو از آخرین سلول در آرایه جستجو و در عوض آن نتیجه را به شما ارائه می دهد.







این مثال جستجوی مشابهی را نشان می دهد که می توانید با یک تابع VLOOKUP با استفاده از ستون Rep به عنوان اولین ستون جدول جستجو انجام دهید. با این حال، XLOOKUP به شما امکان می دهد هر ستونی را در هر جهت جستجو کنید.برای مثال، اگر می‌خواهید فروشنده‌ای را پیدا کنید که اولین سفارش بایندر سال را فروخته است، از تابع زیر XLOOKUP استفاده کنید:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)

D2: به سلول جستجوی مورد اشاره می کند
D2:D44: این ستون آیتم است که آرایه جستجو است
C2:C44: این ستون Rep است که آرایه برگشتی در سمت چپ آرایه جستجو است
0: مطابقت دقیق را انتخاب می کند
1: اولین مسابقه را در نتایج انتخاب می کند



این بار، نتیجه، نام نماینده فروش خواهد بود که اولین سفارش کلاسور سال را فروخت.








اجرای تطابق عمودی و افقی با XLOOKUP
یکی دیگر از قابلیت‌های XLOOKUP که VLOOKUP قادر به انجام آن نیست، توانایی انجام جستجوی عمودی و افقی است، به این معنی که می‌توانید یک مورد را در یک ستون و در یک ردیف جستجو کنید.
این ویژگی جستجوی دوگانه جایگزین مؤثری برای سایر توابع اکسل مانند INDEX، MATCH، یا HLOOKUP است.


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







با عملکرد XLOOKUP، این نوع جستجو آسان است. با استفاده از تابع بعدی XLOOKUP، می توانید فروش سه ماهه سوم را برای یک نماینده فروش خاص جستجو کنید:
=XLOOKUP(J2، B2:B42، XLOOKUP(K2، C1:H1، C2:H42))

J2: به سلول جستجوی Rep اشاره می کند
B2:B42: این ستون آیتم است که آرایه جستجوی ستون است
K2: به سلول جستجوی چهارم اشاره می کند
C1:H1: این آرایه جستجوی ردیف است
C2:H42: این آرایه جستجو برای مقدار دلار در هر سه ماهه است

این تابع XLOOKUP تو در تو ابتدا نماینده فروش را شناسایی می کند و تابع بعدی XLOOKUP سه ماهه مورد نظر را مشخص می کند. مقدار بازگشتی سلولی است که آن دو در آن قطع می‌شوند.


نتیجه این فرمول درآمد سه ماهه یک نماینده با نام تامپسون است.








استفاده از تابع XLOOKUP
عملکرد XLOOKUP فقط برای مشترکین Office Insider در دسترس است، اما به زودی برای همه مشترکین Microsoft 365 عرضه خواهد شد.
اگر می خواهید این عملکرد را خودتان آزمایش کنید، می توانید یک Office Insider شوید. File > Account را انتخاب کنید، سپس کشویی Office Insider را برای اشتراک انتخاب کنید. را انتخاب کنید.
هنگامی که به برنامه Office Insider ملحق شدید، نسخه نصب شده اکسل شما همه آخرین به روز رسانی ها را دریافت می کند و می توانید از تابع XLOOKUP استفاده کنید.