کاربرد دو تابع VLOOKUP و XLOOKUP در اکسل چیست؟
از دو تابع VLOOKUP و XLOOKUP در اکسل برای پیدا کردن اطلاعات مرتبط با داده های مورد نظر استفاده می‌شود. در واقع با استفاده از این توابع پر کاربرد می توان مقدار خاصی را در یکی از ستون های اکسل جستجو کرد و مقدار مرتبط با آن را که در ستون دیگری قرار دارد؛ استخراج نمود. به عنوان مثال می توان نمره درس ریاضی دانش‌آموزی به نام “علی احمدی” را برگرداند. تابع XLOOKUP نسبت به تابع VLOOKUP انعطاف پذیر‌تر و قدرتمند‌تر است؛ اما در اکسل 2021 و نسخه های بالاتر قابل استفاده می باشد. در ادامه تفاوت این دو تابع شرح داده می شود.
فرم کلی توابع VLOOKUP و XLOOKUP
فرم کلی تابع VLOOKUP به صورت زیر است:
|
1 |
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) |
- Lookup_value مقداری است که آن را جستجو می کنید.
- Table_array محدوده مورد جستجو است. مقدار مورد جستجو باید در اولین ستون این محدوده باشد.
- Col_index_num شماره ستونی است که اطلاعات مورد نظر شما در آن قرار دارد.
- Range_lookup آرگومانی اختیاری است. اگر مقدار آن صفر باشد به صورت دقیق جستجو انجام می‌شود. در صورتی که مقدار آن یک باشد؛ جستجو به صورت تقریبی انجام می گردد. پیش فرض اکسل برای این آرگومان مقدار یک است.
فرم کلی تابع XLOOKUP به صورت زیر است:
|
1 |
XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
- Lookup_value مقداری است که آن را جستجو می کنید.
- Lookup_array محل مورد جستجو را مشخص می کند.
- Return_array محل استخراج اطلاعات مرتبط با مقدار مورد جستجو را تعیین می کند.
- If_not_found آرگومانی اختیاری است که تعیین می کند در صورت پیدا شدن مقدار مورد جستجو، چه پیامی به کاربر نشان داده شود.
- Match_mode با استفاده از این آرگومان اختیاری، نوع جستجو که می تواند به صورت دقیق یا تقریبی باشد؛ تعیین می شود. مقدار این آرگومان می تواند یکی از موارد زیر باشد:
- مقدار 0 برای جستجوی دقیق
- مقدار 1- برای جستجوی مقدار دقیق یا کوچکترین مقدار نزدیک به مقدار مورد جستجو
- مقدار 1 برای جستجوی مقدار دقیق یا بزرگترین مقدار نزدیک به مقدار مورد جستجو
- مقدار 2 برای جستجو با کاراکترهای عمومی.
توجه کنید که مقدار پیش فرض این آرگومان صفر است.
Serach_mode آرگومانی اختیاری با مقدار پیش فرض یک می باشد. عدد یک به معنی آغاز جستجو از ابتدا است. عدد 1- برای تعیین جستجو از انتها، عدد 2 برای جستجوی دودویی صعودی و عدد 2- برای جستجوی دودویی نزولی است.
جهت جستجو دو تابع VLOOKUP و XLOOKUP در اکسل
یکی از تفاوت های اصلی دو تابع VLOOKUP و XLOOKUP در اکسل جهت جستجوی آنها است. تابع VLOOKUP تنها ستون های سمت راست ستون حاوی داده مورد نظر را جستجو می کند؛ اما تابع XLOOKUP سمت چپ و سمت راست داده مورد نظر را می گردد. به عنوان مثال در شکل زیر ما می خواهیم با توجه به کد کارمند، نام او را در ستون نام و نام خانوادگی پیدا کنیم. همانطور که در شکل می بینید ستون نام و نام خانوادگی در سمت راست کد قرار دارد.
استفاده از تابع VLOOKUP منجر به پیام خطای #N/A می شود. به این دلیل برای پیدا کردن نام کارمندی با کد 103 از تابع XLOOKUP استفاده کرده ایم.
جستجوی پیش فرض تقریبی برای تابع VLOOKUP
همانطور که گفته شد تابع VLOOKUP به طور پیش فرض جستجو را به صورت تقریبی انجام می دهد. در مثال زیر ما دنبال کارمندی هستیم که کد او برابر با 111 می باشد. این کد در ستون کد وجود ندارد. تابع VLOOKUP چون جستجو را به صورت تقریبی انجام می دهد نام الهام یوسفی را بر می گرداند. این پاسخ اشتباه است. اما اگر از تابع XLOOKUP که جستجو را به صورت دقیق انجام می دهد؛ استفاده شود چنین خطایی رخ نمی دهد. برای درک بهتر مطلب به شکل زیر دقت کنید.
تاثیر حذف و اضافه کردن ستون ها روی این توابع
تابع VLOOKUP برای پیدا کردن پاسخ، ستون ها را می شمارد. با تغییر ترتیب ستون ها یا اضافه کردن ستونی جدید این تابع گیج می شود و پاسخ نادرستی را بر می گرداند. تابع XLOOKUP چنین مشکلی ندارد و با ایجاد تغییرات در ستونها، باز هم پاسخ صحیح را بر می گرداند.
مدیریت خطا در دو تابع VLOOKUP و XLOOKUP
در تابع XLOOKUP با استفاده از آرگومان if_not_found می توان خطا را مدیریت کرد. با این آرگومان در صورت عدم یافتن مقدار مورد جستجو، پیغام مورد نظر شما به کاربر نشان داده می شود. تابع VLOOKUP در صورت پیدا نکردن مقدار مورد نظر خطای (#N/A) را نشان می دهد. برای مدیریت خطا در تابع VLOOKUP شما باید از تابع دیگری به نام IFERROR استفاده کنید. برای فهم بهتر مطلب به مثال ارائه شده در شکل زیر دقت کنید.
با استفاده از دو تابع VLOOKUP و XLOOKUP در اکسل 111 را جستجو می کنیم که در ستون کد وجود ندارد. در این جستجو تابع VLOOKUP پیغام خطای #N/A را نشان می دهد. تابع XLOOKUP عملکرد متفاوتی دارد. این تابع پیغام not found را که توسط کاربر برای مدیریت خطا تعیین شده است؛ نمایش می دهد.
تطابق تقریبی بدون مرتب سازی
هر دو تابع VLOOKUP و XLOOKUP در اکسل می توانند نزدیک ترین مقادیر به مقدار مورد جستجو را پیدا کنند. به عنوان مثال در حالتی که می خواهید با توجه به محدوده نمره یکی از حروف A تا E را به دانش آموزی اختصاص دهید؛ این نوع جستجو مفید است. در مثال زیر ما می خواهیم با توجه به نمره درج شده در ستون C حرف مناسبی را از روی جدول موجود در ستون های G تا I استخراج کنیم. شما می توانید با استفاده از تابع VLOOKUP حرف صحیحی را متناسب با نمره استخراج کنید؛ البته به شرطی که جدول نمرات شما مرتب شده باشد. اگر جدول نامرتب باشد؛ احتمال نمایش پاسخ غلط با تابع VLOOKUP وجود دارد.
هنگام استفاده از تابع XLOOKUP نگرانی در مورد مرتب بودن جدول داده ها وجود ندارد و این تابع حتی با داده‌های نامرتب نبز پاسخ صحیح را پیدا می کند.
نتیجه گیری
تابع VLOOKUP نسبت به تابع XLOOKUP محدودیت ها و معایبی دارد. در واقع تابع XLOOKUP نسخه توسعه‌یافته و اصلاح شده تابع VLOOKUP است. البته برای انجام جستجوهای پایه هنوز هم افراد زیادی از تابع VLOOKUP استفاده می کنند. هر دوی این توابع با توجه به نوع استفاده کاربر مفید هستند.
