عملکرد Xlookup

عملکرد اکسل Xlookup جایگزینی مدرن و انعطاف پذیر برای عملکردهای قدیمی مانند VLOOKUP ، HLOOKUP و LOOKUP است. Xlookup از تطبیق تقریبی و دقیق ، کارتهای وحشی (*؟) برای مسابقات جزئی و جستجوی در محدوده عمودی یا افقی پشتیبانی می کند.

هدف

مقادیر جستجو در محدوده یا آرایه

مقدار بازگشت

مقدار (های) تطبیق از آرایه بازگشت

استدلال

• Lookup - مقدار جستجو.
• lookup_array - آرایه یا دامنه جستجو.
• Retu_array - آرایه یا دامنه بازگشت.
• NOT_FOUND - [اختیاری] مقدار برای بازگشت در صورت عدم یافتن مسابقه.
• match_mode - [اختیاری] 0 = مسابقه دقیق (پیش فرض) ، -1 = مسابقه دقیق یا کوچکترین بعدی ، 1 = مسابقه دقیق یا بعدی بزرگتر ، 2 = WildCard Match.
• search_mode -[اختیاری] 1 = جستجو از اول (پیش فرض) ، -1 = جستجو از آخرین ، 2 = جستجوی باینری صعود ، -2 = نزول جستجوی باینری.

نحو

= Xlookup (Lookup ، lookup_array ، Retu_array ، [not_found] ، [match_mode] ، [search_mode])

یادداشت های استفاده

Xlookup جایگزینی مدرن برای عملکرد VLookup است. این یک عملکرد انعطاف پذیر و همه کاره است که می تواند در طیف گسترده ای از موقعیت ها مورد استفاده قرار گیرد. Xlookup می تواند مقادیر را در محدوده های عمودی یا افقی پیدا کند ، می تواند مسابقات تقریبی و دقیق را انجام دهد و از کارتهای وحشی (*؟) برای مسابقات جزئی پشتیبانی می کند. علاوه بر این ، XLOOKUP می تواند داده ها را از اولین مقدار یا آخرین مقدار جستجو کند (به نوع مسابقه و جزئیات حالت جستجو در زیر مراجعه کنید). در مقایسه با توابع قدیمی مانند VLOOKUP ، HLOOKUP و LOOKUP ، XLookup چندین مزیت کلیدی را ارائه می دهد.

پیام یافت نشد

هنگامی که Xlookup نمی تواند یک مسابقه را پیدا کند ، مانند سایر توابع مسابقه در اکسل ، خطای #N/A را برمی گرداند. بر خلاف سایر توابع مسابقه ، Xlookup از یک آرگومان اختیاری به نام NOT_FOUND پشتیبانی می کند که می تواند برای غلبه بر خطای #N/A در صورت ظاهر شدن در غیر این صورت استفاده شود. مقادیر معمولی برای not_found ممکن است "یافت نشد" ، "بدون مسابقه" ، "بدون نتیجه" و غیره.

توجه: اگر یک رشته خالی ("") را برای not_found تهیه می کنید ، مراقب باشید. اگر هیچ مسابقه ای پیدا نشود ، Xlookup به جای #N/A هیچ چیزی را نشان نمی دهد. اگر می خواهید خطای #N/A را هنگام پیدا کردن مسابقه مشاهده کنید ، این استدلال را کاملاً حذف کنید.

نوع مسابقه

به طور پیش فرض ، Xlookup یک مسابقه دقیق را انجام می دهد. رفتار مسابقه توسط یک آرگومان اختیاری به نام match_type کنترل می شود ، که گزینه های زیر را دارد:

حالت جستجو

به طور پیش فرض ، XLOOKUP از اولین مقدار داده شروع می کند. رفتار جستجو توسط یک آرگومان اختیاری به نام search_mode کنترل می شود ، که گزینه های زیر را ارائه می دهد:

جستجوهای باینری بسیار سریع است ، اما داده ها باید طبق نیاز مرتب شوند. اگر داده ها به درستی مرتب نشده باشند ، یک جستجوی باینری می تواند نتایج نامعتبر را که کاملاً طبیعی به نظر می رسند بازگرداند. مثال مفصل در اینجا.

مثال شماره 1 - مسابقه دقیق اساسی

به طور پیش فرض ، Xlookup یک مسابقه دقیق را انجام می دهد. در مثال زیر ، از Xlookup برای بازیابی فروش بر اساس یک مسابقه دقیق در فیلم استفاده می شود. فرمول در H5:

= Xlookup (H4 ، B5: B9 ، E5: E9) 

مثال شماره 2 - بازی تقریبی اساسی

برای فعال کردن یک مسابقه تقریبی ، مقداری برای آرگومان match_mode تهیه کنید. در مثال زیر ، از Xlookup برای محاسبه تخفیف بر اساس کمیت استفاده می شود ، که به یک مسابقه تقریبی نیاز دارد. فرمول در F 5-1 را برای MATCH_MODE تهیه می کند تا مطابقت تقریبی با رفتار "مطابقت دقیق یا کوچکترین بعدی" را فعال کند:

= XLOOKUP (E5 ، B5: B9 ، C5: C9 ،-1) 

مثال شماره 3 - مقادیر چندگانه

Xlookup می تواند بیش از یک مقدار را همزمان برای همان مسابقه برگرداند. مثال زیر نشان می دهد که چگونه می توان XLOOKUP را برای بازگشت سه مقدار تطبیق با یک فرمول واحد تنظیم کرد. فرمول در C5:

= Xlookup (B5 ، B8: B15 ، C8: E15) 

توجه کنید که آرایه بازگشت (C8: E15) شامل 3 ستون است: اول ، آخرین و بخش. هر سه مقدار برگردانده شده و به محدوده C5: E5 ریخته می شوند.

مثال شماره 4 - جستجوی دو طرفه

Xlookup را می توان برای انجام یک جستجوی دو طرفه ، با لانه سازی یک Xlookup در داخل دیگری استفاده کرد. در مثال زیر ، Xlookup "داخلی" یک ردیف کامل (تمام مقادیر شیشه ای) را بازیابی می کند ، که به عنوان آرایه بازگشت به xlookup "بیرونی" تحویل داده می شود. Xlookup بیرونی گروه مناسب (B) را پیدا می کند و مقدار مربوطه (17. 25) را به عنوان نتیجه نهایی باز می گرداند.

= Xlookup (i6 ، C4: F4 ، Xlookup (i5 ، B5: B9 ، C5: F9)) 

مثال شماره 5 - پیام یافت نشد

مانند سایر توابع جستجو ، اگر Xlookup مقداری پیدا نکند ، خطای #N/A را برمی گرداند. برای نمایش یک پیام سفارشی به جای #N/A ، مقداری برای آرگومان اختیاری "not_found" ارائه دهید ، که در نقل قول های مضاعف ("") محصور شده است. به عنوان مثال ، برای نمایش "یافت نشد" وقتی که هیچ فیلم تطبیق یافت نمی شود ، بر اساس صفحه کار زیر ، از آنها استفاده کنید:

= Xlookup (H4 ، B5: B9 ، E5: E9 ، "یافت نشد") 

شما می توانید این پیام را همانطور که دوست دارید سفارشی کنید: "بدون مسابقه" ، "فیلم یافت نشد" و غیره.

مثال شماره 6 - معیارهای پیچیده

با امکان رسیدگی به آرایه ها به صورت بومی ، از Xlookup با معیارهای پیچیده استفاده می شود. در مثال زیر ، Xlookup با رکورد اول مطابقت دارد که در آن: حساب با "x" آغاز می شود و منطقه "شرق" است و ماه آوریل نیست:

= Xlookup (1 ، (سمت چپ (B5: B16) = "x")*(C5: C16 = "East")*نه (ماه (D5: D16) = 4) ، B5: E16) 

مثال شماره 7 - جستجوی باینری

XLOOKUP دارای گزینه حالت جستجوی باینری است که خیلی سریع جستجو را انجام می دهد. برای فعال کردن حالت جستجوی باینری ، داده ها باید به ترتیب صعودی یا نزولی طبقه بندی شوند. اگر مقادیر به ترتیب صعودی مرتب شده اند ، از مقدار 2 برای search_mode استفاده کنید. اگر مقادیر به ترتیب نزولی طبقه بندی شده اند ، از مقدا ر-2 استفاده کنید. در زیر نحو عمومی برای فعال کردن حالت جستجوی باینری برای جستجوی دقیق مسابقه وجود دارد:

= Xlookup (a1 ، lookup_array ، retu_array ، ، 0،2) // جستجوی باینری a-z = xlookup (a1 ، lookup_array ، retion_array ، ، 0 ، -2) // جستجوی دودویی Z-a 

برای مثال بیشتر ، به جستجوی دودویی Xlookup مراجعه کنید.

مزایای Xlookup

Xlookup چندین مزیت مهم را ارائه می دهد ، به خصوص در مقایسه با Vlookup:

• Xlookup می تواند داده ها را به سمت راست یا چپ مقادیر جستجو جستجو کند
• Xlookup به یک مسابقه دقیق پیش فرض می کند
• Xlookup می تواند با داده های عمودی و افقی کار کند
• Xlookup می تواند یک جستجوی معکوس را انجام دهد (آخرین تا اول)
• Xlookup می تواند کل ردیف ها یا ستون ها را برگرداند ، نه فقط یک مقدار

یادداشت

1. Xlookup می تواند با هر دو آرایه عمودی و افقی کار کند.
2. اگر مقدار جستجو پیدا نشود ، Xlookup #N/A را برمی گرداند.
3. مانند عملکرد شاخص ، Xlookup در نتیجه یک مرجع را برمی گرداند.
4. اندازه LookUp_array باید با Retu_array سازگار باشد ، یا Xlookup #Value را برمی گرداند!
5. اگر Xlookup در یک کتاب کار خارجی به یک جدول اکسل اشاره کند ، کتاب دیگر باید باز باشد یا Xlookup یک #REF را برگرداند! خطا