موضوعات داغ

عملکرد تابع XLOOKUP در اکسل

توابع جدید اکسل 365

عملکرد تابع XLOOKUP در اکسل

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

تابع XLOOKUP چیست ؟

این تابع جدید بعضی از محدودیت های بزرگ VLOOKUP را حل کرده است . بعلاوه جایگزین HLOOKUP نیز شده است. برای مثال XLOOKUP در ستون های چپ خود نیز جستجوی میکند آنچه که VLOOKUP نیمتوانست انجام دهد. VLOOKUP یک تابع ساده و همه فن حریف نبود. در ادامه نحوه عملکرد این تابع نشان داده شده است.
در حال حاضر ، XLOOKUP برای کاربران داخلی اکسل دسترس است هر کسی میتواند به کاربران داخلی اکسل بپیوندد تا به محض ایجاد ویژگی های جدید در اکسل به آن دسترسی داشته باشد. مایکروسافت به زودی دسترسی را برای کاربران office 365 ایجاد خواهد کرد.

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

بیاید مستقیما با مثال تشریح کنیم. در داده های مثال زیر ، میخواهیم اطلاعات دپارتمان در ستون F رو بر اساس ID در ستون A برگردانده شود.

این یک نمونه جستجوی با اطلاعات کاملا مشابه است. تابع XLOOKUP 3 بخش دارد. تصویر زیر 5 آرگومان را نشان میدهد ولی برای اطلاعات کاملا مشابه 3 بخش اول مورد نیاز است پس روی آنها تمرکز میکنیم:
Lookup_value : آنچه در جستجوی آن هستید.
Lookup_array: محوطه جستجو
Return_array : دامنه حاوی مقدار برای برگشت اطلاعات

برای مثال فوق تابع به شکل زیر خواهد بود :

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

چند نمونه از مزایای XLOOKUP نسبت به VLOOKUP به شرح زیر است .
بدون نیاز به شماره ستون Column Index Number
سومین آرگومان VLOOKUP تعیین شماره ستون از جدول بود، در XLOOKUP امکان انتخاب دامنه مورد نظر وجود دارد. ( این مثال ستون F )

فراموش نکنید برخلاف VLOOKUP دامنه انتخابی در XLOOKUP میتواند سمت چپ ستون انتخابی باشد.
همچنین به هنگام ایجاد ستون جدید ، فرمول های شما به هم نمیخورد وآدرس دامنه انتخابی به محدوده جدید به صورت اتوماتیک اصلاح میشود.

آموزش اکسل در حسابداری

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

مطابقت دقیق به صورت یک پیش فرض

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

به طور خلاصه XLOOKUP سوالات کمتری از کاربرمیپرسد و کاربری آن آسان تر است.

XLOOKUP میتواند در سمت چپ جستجو کند

امکان انتخاب دامنه جستجوب باعث میوشد ترتیب ستون ها در جدول اهمیتی نداشته باشند. در VLOOKUP با جستجو در ستون سمت چپ با بازگشت به تعداد مشخصی از ستون سمت راست محدود شده است. در مثال زیر، باید یک ID درستون E را جستجو کرده در نام افراد را در ستون D برگرداند.

فرمول آن به شرح زیراست.

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

استفاده از VLOOKUP برای جستجو در دامنه

اگر چه به اندازه جستجومطابقت دقیق معمول نیست ، اما برای یافتن در یک مقدار در یک بازه به کار برده میشود. در مثال زیر تخیفیف به مبلغ خرید محاسبه میشود.
این بار مبلغ خاصی وجود ندارد باید مقادیر ستون B در محدوده E مشخص و میزان تخفیف تعیین شود.

آرگومان غیر اجباری چهارم در تابع XLOOKUP به نام match mode به صورت پیش فرض روی مطابقت دقیق است.


چنانچه مشخص است XLOOKUP در مقایسه با VLOOKUP از قابلیت های بیشتری برخوردار است.
1- : برای پیدا کردن همان مقدار یا نزدیکترین مقدار کوچکتر از آن
1 : برای پیدا کردن همان مقدار یا نزدیک ترین مقدار بزرگتر از آن
2 : برای استفاده از کاراکتر های عمومی مثل ؟(برای یک حرف) یا * (برای هر چند حرف)
در مثال زیر ، فرمول مقدار مشابه و در صورت عدم مطابقت مقدار کمتر را برمیگرداند:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

ضمنا خطا #N/A در سلول C7 به این دلیل است که 64 در دامنه خرید های تعریف شده وجود ندارد.
مزیت دیگر XLOOKUP این است که این تابع نیاز به مرتب بودن ( سورت ) جداول ندارد و میتوان مقادیر مورد نیاز را در پایین جدول اضافه نمود.
ردیف جدید را در زیر جدول اضافه کنید و فرمول را باز کنید و با درگ کردن جدول را تغییر دهید.

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

این موضوع که عدم ترتیب جدول باعث شکستگی اطاعات نشده است پیروزی بزرگی است.
XLOOKUP جایگزین HLOOKUP نیز میشود.

یک تابع برای جایگزینی 2 تابع. عملکرد HLOOKUP حجستجوی افقی در سطرهاست .اگر چه به خوبی VLOOKUP شناخته نشده است، اما برای مثالهایی مانند زیر که در آنها ID در ستون Aقرار دارد و اطلاعات در ردیف های 4 و 5 مفید است .
XLOOPUP میتواند به هر دوجهت نگاه کند ستون ها و ردیف ها و نیازی به دو تابع متفاوت نیست.
در این مثال فرمول میزان فروش برای نام مندرج در سلول A2 را بر اساس اطلاعات ردیف 4 و ردیف 5 برمیگرداند.

=XLOOKUP(A2,B4:E4,B5:E5)


XLOOKUP از پایین به بالاهم میتواند جستجو کند
به طور معمول نیاز است که اولین مقدار موجود در جدول که غالبا تنها یک بار تکرار شده است ، شناساسی شود. پنحمین آرگومان XLOOKUP به نام search mode این امکان را فراهم میکند تا از آخرین وقایع مقدایر را پیدا کند.

آموزش Power BI Desktop

این مجموعه در ده جلسه شما را با نرم افزار Power BI Desktop آشنا میکند. این نرم افزار برای ارتباط با پایگاه های داده و ارائه داده ها بصورت نمودار کاربرد دارد. با استفاده از Power BI Desktop شما میتوانید داده ها را از سایر نرم افزارها استخراج کرده و در جداول مورد نیاز خود دسته بندی کرده و آنها را بصورت نمودار و گراف و … نمایش دهید.

درمثال زیر میزا موجودی انبار برای هر محصول در ستون A مشخص شود. جدول اطلاعات به ترتیب تاریخ است و هر محصول چندین بار تکرار شده است و ما میخواهیم آخرین وضعیت موجودی انبار برگردانده شود.

در آرگومان پنجم ، چهار حالت مختلف ارائه میشود . در این مثال “جستجو از آخر به اول” مورد نیاز است.

فرمول نهایی به شرح زیر است :

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)


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

 

توضیح :

1- معمولا اکسل توابعی دارد که کارهای مشابهی انجام میدهند ولی تابع قدیمی از لیست توابع موجود حذف نشده است

2- این تابع فقط مختص اکسل موجود در آفیس 365 است و در اکسل 2007 تا 2019 وجود ندارد.

منبع

مترجم : مریم حائری نیا m_haerynia@yahoo.com

برچسب ها

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

دکمه بازگشت به بالا

بستن
بستن