تابع XLOOKUPیک جایگزین نو و انعطاف پذیر برای توابع قدیمی مثل VLOOKUP و HLOOKUP و LOOKUP است که در نسخه اکسل ۲۰۲۱ اضافه شده است. این تابع برای کشف انطباق های دقیق و تقریبی ، انطباق های جزئی با استفاده از wildcard ها (*؟) و جستجو در محدوده های افقی و عمودی کاربرد دارد . رفته رفته وجوه انعطاف پذیری این تابع را مورد بررسی قرار می دهیم .
به عنوان پیش نیاز ، مقالهء «آموزش تابع VLOOKUP در اکسل- قدم به قدم» را به دوستانی که تازه با توابع اکسل آشنا شده اند پیشنهاد می کنم.
مزایای تابع XLOOKUP
XLOOKUPدر مقایسه با VLOOKUP چند مزیت مهم دارد که سعی می شود در مثالهائی که می‌آورم این مزایا را نشان دهم :
XLOOKUPمی تواند داده ها را در راست یا چپ مقادیر جستجو ، بیابد.
XLOOKUPمی تواند چندین نتیجه را برگرداند.
پیش فرض XLOOKUPبرای انطباق دقیق است (پیش فرض VLOOKUP مطابقت تقریبی است ).
XLOOKUPمی تواند مورد جستجو را در محدوده های عمودی یا افقی رهگیری کند.
XLOOKUPاز پس جستجوی معکوس نیز بر می آید (آخرین به اولین).
XLOOKUPمی تواند نه فقط یک مقدار بَل کُل سطر یا ستون را برگرداند.
XLOOKUPطبعا می تواند با آرایه ها کار کند و شروط پیچیده را اعمال کند.
XLOOKUPمی تواند با آرایه های عمودی و افقی کار کند.
اگر مقدار جستجو پیدا نشود ، XLOOKUP خطای #N/A را برمی گرداند.
آرایه جستجو lookup_array باید دارای ابعادی سازگار با آرگومان return_array باشد، در غیر این صورت XLOOKUP خطای #VALUE را برمی گرداند . بدین معنا که اگر آرایهء جستجو A2:A10 است return_array نمی تواند مثلاً B2:B12 باشد
اگر از XLOOKUPبین فایل‌های مختلف استفاده می شود ، هر دو فایل باید باز باشد، در غیر این صورت باز گشت خطای REF# از سوی XLOOKUP اجتناب ناپذیر است .
آشنایی با ورودی‌های تابع XLOOKUPاکسل
=XLOOKUP(lookup_value , lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
ورودی اول lookup_value (مقدار جستجو) اگر یک رشته متنی باشد باید بین دو تا علامت (") محصور شود و وقتی به یک سلول اشاره می شود نیازی به این علامت (")نیست . در همین مثال اول به F4 اشاره شده (و نه به کلمه "iran")
ورودی دوم lookup_array محدوده ای که واجد مقدار جستجو است
ورودی سوم return_array محدوده ای که مقدار متناظر با آرگومان اول انتظار میرود در آن یافت شود ( XLOOKUPبرای احتمال یافت نشدن تمهیدی قائل شده است که ذیلا به آن اشاره خواهد شد ).
تا همینجا هم بدون وارد کردن آرگومانهای 4 تا 6 می توان از XLOOKUPسود جست.
مثال ۱)
می‌خواهیم با وارد کردن «کد پیمان»، نام راننده را پیدا کنیم:
تابع XLOOKUP در سلول G2 نوشته شده است.
کد پیمان 3460 در محدودهء C2:C6 موجود است . نام راننده هوشیار متناظر آن در محدوده B2:B6را در سلول G2 بعنوان خروجی ملاحظه می کنید آشکار است که برای XLOOKUP فرقی نمی کند خروجی تابع در سمت چپ یا راست مقدار یا ستون جستجو باشد و این یکی از وجوه انعطاف پذیری XLOOKUPاست .(ناسپاس نیستم زیرا به VLOOKUP و به مایکروسافت و کارکنانش تا همیشه مدیونم).
نکته) پیش از این وقتی جستجوی VLOOKUP با شکست مواجه می شد برای کاربر پسند شدن نمای سلول به تابع IFERROR متوسل می شدیم (زیرادر این صورت با خطای #N/A مواجهیم).فرض کنید مایلیم در صورتی که یافت نشد کاربر با عبارت "کد پیمان یافت نشد" روبرو شود. صورت کلی این کار با VLOOKUP چنین درج می شود:
=IFERROR( VLOOKUP( ), "code peyman yaft nashod!" )
ورودی چهارم [if_not_found] این آرگومان اختیاریست ولی وقتی مورد جستجو یافت نشود - بی که منت iferror را بکشد - کاربر می تواند انتظار پیغامی را داشته باشد نه نمایش خطای #N/A
=XLOOKUP(F2, C2:C6, B2:B5, "code peyman yaft nashod!")
مثال ۲)
ورودی پنجم [match_mode] چنانکه از نامش پیداست نوع انطباق با آن مشخص می شود بدین صورت که کاربر یکی از اعداد 1- تا 2 را به امید گرفتن خروجی دلخواه در این ورودی درج می کند . در هر دو مثال مربوط به بارنامه ها بطور پیش فرض آرگومان پنچم 0 (صفر) است . نظر به اینکه شماره بارنامه فقط و فقط به یک راننده و یک شماره پیمان مربوط است بنابر این فرقی نمی کرد که ما 0 را وارد کنیم یا نه . مثال مربوط به تخفیف در جدول زیر به درک بهتری از موضوع کمک می کند .
بیائید با هم برای خرید به فروشگاهی سر بزنیم . روی تابلوی اعلانات به جدولی بر می خوریم که مقدار تخفیف را بر اساس میزان خرید(مبالغ به میلیون ریال ) نشان می دهد. آیا مشتریان دقیقا مبالغ موجود در ستون یک را خرید می کنند ؟ مسلماً خیر .حالا فرض می کنیم مسئول فروشگاه فروش هایش را در اکسل ثبت می کند و می‌خواهد بداند هر مشتری با توجه به میزان خرید چند درصد تخفیف شامل حالش می شود.
توضیح چند نکته دراینجا خالی از لطف نخواهد بود چون انتظار نداریم در خروجی با خطا مواجه شویم از خیر آرگومان چهارم گذشته ایم (دو علامت "," پشت سر هم بیانگر این در گذشتگی است). هنگام درج آرگومان پنجم در XLOOKUP این پنجرهء کوچک بدرستی خودنمائی می کند تا با توضیحاتش به کمک مان بیاید(خود نمائی مطلقاً و همه جا بد نیست)
ملاحظه می کنید که در مقابل کد (1-) عبارت انطباق دقیق یا موردِ کوچکتر و نزدیک به معیار نوشته شده است. بابک را نگاه کنید که 20 میلیون خرید کرده است ، معادلِ دقیقی برای این میزان خرید در جدول پایه تخفیف نداریم پس XLOOKUP فرض می کند مشتری 15میلیون خرید کرده (کوچکتر و نزدیک به ده میلیون) و به او %8 تخفیف می دهد.
راستی بیائید فرض کنیم روز دوشنبه این هفته جشن تولد پسر صاحب فروشگاه است . وی تصمیم می گیرد یک حال اساسی به مشتریانش بدهد و انطباق دقیق یا مورد بزرگتر و نزدیک به معیار را (در فرمول 1 بجای کد 1- ) جایگزین کند. خوب در این صورت و بر طبق همان جدول، بابک می‌تواند از تخفیف %25 بهره مند شوند (بلا نسبت خواننده مثالش پنبه دانه ایست که به خواب شتر می آید)
مثال ۳)
در جدول زیر بدنبال ترانه سرائی هستیم که نامش با "Sha" شروع می شود ببینید آیتم چهارم آرگومان پنج چگونه به کمک مان آمده است .
شما هم تجربه یا دیدگاه خود را بنویسید:
شما واقعا یکی از بهترین ها هستین...