ویدیو آموزشی تابع XLOOKUP
ویدیو آموزش Xlookup در ابتدای مطلب قرار داده شده است و اگر نیاز به توضیحات بیشتر داشتید در نظرات عنوان نمائید.
مثال های XLOOKUP
ساده ترین مثال از این تابع را می توان در یک خط فرمول زیر و تصویر دید که به ساده ترین و سریع ترین شکل ممکن ، کار VLOOKUP را انجام میدهد و همانطور که در مثال میبیند برای این تابع مهم نیست که محدوده و یا سلولی که میخواهید بعد از جستجو نمایش داده شود ، قبل از محدوده جستجو باشد یا بعد از آن.
=XLOOKUP(G2,C2:C23,B2:B23)
نمایش چند ستون در نتیجه Xlookup
در این مثال فرض میکنیم که جدولی داریم شامل کد پرسنلی و کمپانی و فروش و پاداش و می خواهم با وارد کردن کد پرسنلی ، بقیه اطلاعات نمایش داده شود ، در تابع Vlookup مجبور بودیم سه فرمول جداگانه بنویسیم ولی اینجا با یک خط فرمول به شکل زیر انجام می شود .
نکته : در آرگومان سوم به جای یک ستون ، ستونهای مد نظر خود را انتخاب می کنیم .
نمایش نتیجه با اعمال چند شرط در XLOOKUP
در این مثال فرض کنیم که اسامی دانش آموزان را با درس مربوطه و نمراتی که اخذ شده را داریم حالا می خواهم با انتخاب نام دانش آموزش و انتخاب درس ، نمره مورد نظر را نمایش دهد که استفاده از XLOOKUP با چند شرط را می خواهیم بررسی کنیم .
=XLOOKUP(J2,C3:C10,XLOOKUP(J3,D2:G2,D3:G10))
البته همانطور که در تصویر می بینید ، از دو روش برای نمایش نتیجه استفاده شده در واقع تفاوتی با هم ندارند و این تابع به صورت تو در تو استفاده شده و در فقط جای آنها تفاوت کرده است . اگر بخواهیم این کار را ترکیب توابع دیگر امجام دهیم می توانیم به سراغ تابع INDEX و تابع MATCH برویم .
=INDEX(D3:G10,MATCH(J2,C3:C10,0),MATCH(J3,D2:G2,0))
نمایش پیغام مناسب در صورت پیدا نشدن نتیجه در XLOOKUP
فرض می کنیم لیستی از کالاها داریم شامل کد کالا و شرح و تعداد و می خواهیم با وارد کردن کد کالا ، شرح آن نمای داده شود که بسیار ساده است و در اولین مثال این پست آموزش داده شده .
اما اگر کد کالایی که به دنیال آن هستیم در لیست کالاها وجود نداشته باشد ، آیا ارور می دهد ؟ تابع VLOOKUP که در این مواقع ارور #N/A را نمایش می داد .
ولی در این تابع توسط آرگومان چهارم که اختیاری هست می توانیم این موضوع را مدیریت کنیم و دقیقا برای این مسئله در نظر گرفته شده اشت که به تابع بگوئیم اگر مقدار مورد نظر را پیدا نکردی چه کاری انجام بده.
در تصویر زیر ما به XLOOKUP گفته ایم که اگر پیدا نکردی ، عبارت وجود ندارد را نمایش بده . به همین سادگی 😊
=XLOOKUP(H2,C3:C10,D3:D10,"وجود ندارد")
جستجو در چند محدوده مختلف با XLOOKUP
فرض کنیم که در دو محدوده مختلف اطلاعاتی با ساختار یکسان ولی داده های متفاوت داریم که شامل کد کالا و شرح کالا هستند.
می خواهیم توسط این تابع ابتدا در محدوده اول به دنبال کد کالا بگردیم و اگر وجود داشت که مقدار متناظر با آن را نمایش دهیم و اگر وجود نداشت محدوده بعدی را جستجو کنیم و در واقع چند تابع را با هم استفاده کنیم .
البته در نظر داشته باشید که این محدوده های متفاوت می توانند در یک شیت و یا در چند شیت و یا حتی در فایلهای متفاوت وجود داشته باشند .
=XLOOKUP(I2,B3:B7,C3:C7,XLOOKUP(I2,E3:E7,F3:F7))
نمایش کوچکترین و بزرگترین توسط XLOOKUP
فرض کنیم که لیستی از کالاها داریم که دارای یک ستون هستند با نام تاریخ خرید و یا تاریخ ورود به انبار ، خب در این شرایط احتمال تکرار کد کالا در تاریخ خای مختلف وجود دارد .
حالا ما می خواهیم تاریخ اولین خرید و تاریخ آخرین خرید یک کالا را نمایش دهیم .
در این روش آرگومان ششم اهمیت بالایی پیدا میکند چرا که مشخص می کند اگر نتیجه تکراری وجود داشت من از بالا به پائین جستجو کنم و اولین نتیجه که پیدا کردم را نمایش دهم یا از پائین به بالا ؟
پس می بینید که در این روش مرتب سازی از کوچک به بزرگ ستون مد نظر بسیار اهمیت دارد .👍
به یاد داشته باشید که مقدار پیشفرض آرگومان ششم عدد 1 می باشد و از بالا به پائین است واگر خالی بگذارید به اولین نتیجه متناظر که برسد جستجو متوقف شده و همان را نمایش می دهد مانند VLOOKUP .
=XLOOKUP(H2,B3:B11,E3:E11,0,,-1)
نمایش پاداش فروش با XLOOKUP مقدار تقریبی
فرض کنیم که لیستی از پرسنل به همراه مقدار فروش آنها داریم و بر اساس میزان فروش می خواهیم برای آنها پاداش در نظر بگیریم.
برای فروشهای زیر 20 هزارتومان 5 هزار تومان پاداش و فروش بین 20 هزار تا 30 هزار ، مبلغ 10 هزارتومان پاداش و بالای 30 هزار تومان فروش هم مبلغ 20 هزار تومان پاداش در نظر گرفته شود .
در این حالت چون احتمال دارد میزان فروش دقیقا برابر با میزان فروش در نظر گرفته شده نباشد ، پس باید از تابع XLOOKUP جوری استفاده کنیم که اگر مقدار را پیدا نکرد نزدیک ترین مقدار به آن را نمایش دهد .
این کار را می توانیم با آرگومان پنجم این تابع انجام دهیم که در توضیح آرگومانهای تابع XOOKUP توضیح عملکرد آن را داده ایم . به تصویر زیر دقت کنید .
=XLOOKUP (C3, $F$3:$F$5,$G$3:$G$5,9G$5,1)
بالاترین نمره را در درس انتخابی چه کسی دریافت کرده ؟
فرض کنیم که لیستی از دانش آموزان داریم که برای هر کدام و برای هر درس یک نمره وارد شده و می خواهیم با انتخاب درس توسط تابع XLOOKUP نام شخصی که بالاترین نمره را در آن درس داشته به دست آوریم.
بدیهی است که ابتدا باید با تابع MAX بالاترین نمره را به دست آوریم و مقدار به دست آمده را در آرگومانهای تابع قرار دهیم تا به نتیجه برسیم .
=XLOOKUP(MAX(XLOOKUP(I3,$D$2:$G$2,$D$3:$G$10)),XLOOKUP(I3,$D$2:$G$2,$D$3:$G$10),$C$3:$C$10)
نمایش نتیجه بر اساس تایپ بخشی از متن
تابع XLOOKUP همانطور که در ویدیو هم گفته شد از وایلدکارها پشتیبانی می کند که کاراکتر * یعنی هر چند کاراکتر و هر کاراکتری و کاراکتر ? یعنی یک کاراکتر هر کاراکتری و با در نظر داشتن این اصل ، اگر ابتدا و انتهای متن مورد جستجو کاراکتر * قرار دهیم به نتیجه خواهیم رسید.
البته شایان ذکر است که برای انجام این مثال تابع FILTER قطعا مناسب تر است و همه نتایج شامل را نمایش می دهد و این قسمت بیشتر جنبه آموزشی امکانات این تابع را دارد.
=XLOOKUP("*"&E3&"*",C3:C10,C3:C10,,2)بسیار بعید می دانم با مشاهده کامل این آموزش باز هم سوالی در خصوص تابع XLOOKUP در ذهن شما باقیمانده باشد ولی اگر چیزی بود که من فراموش کرده بودم در این آموزش به اون بپردازم ، حتما در قسمت نظرات عنوان بفرمائید .
