در این آموزش به بررسی توابع VLOOKUP، HLOOKUP و XLOOKUP در اکسل و سایر نرم‌افزارهای صفحه گسترده می‌پردازیم و کاربردها و مزایا و معایب هر یک را در مقایسه با یکدیگر بررسی می‌کنیم.
معرفی توابع LOOKUP
کاربرد این دستورها برای پیدا کردن یک مقدار از داخل مجموعه‌ای از مقادیر است که به صورت جدولی ذخیره شده‌اند. برای مثال هنگامی که بخواهیم نام و خانوادگی یک کارمند را بر اساس کد ملی ایشان پیدا کنیم، مجموعه دستورهای LOOKUP مورد استفاده قرار می‌گیرند.
تفاوت این سه دستور چیست ؟
پارامترهای قابل ارسال به هر یک از این دستورها با یکدیگر تفاوت دارد. همچنین تفاوت‌های عملکرد جزئی میان این سه دستور وجود دارد که گاهی استفاده از آن‌ها را محدود می‌کند. و در نهایت این دستورها هر سه در یک زمان به اکسل اضافه نشده‌اند و ممکن است هنگام استفاده از نسخه‌های متفاوت اکسل یا نرم‌افزارهای آنلاین صفحه گسترده مثل Google Sheets یا Office 365 نتوانید از برخی از عملکردهای این توابع استفاده کنید.
آشنایی با تابع VLOOKUP
این تابع قدیمی‌ترین تابع میان مجموعه توابع LOOKUP است و شکل فراخوانی آن به صورت زیر است:
1 | VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) |
- lookup_value
مقداری که که در جستجوی آن هستیم در این پارامتر قرار می‌گیرد. برای مثال اگر جستجو بر اساس کد ملی است، مقدار این پارامتر را با نشانی سلولی که کد ملی در آن نگهداری می‌شود، مقدار دهی می‌کنیم.
- table_array
در این پارامتر مجموعه‌ی کامل اطلاعات قابل جستجو را به صورت یک Range ارسال می‌کنیم. ضروری است که اولین ستون رنج حاوی ستون جستجوی ما باشد. به عبارت دیگر اگر قصد جستجوی کد ملی را داریم، کد ملی حتما باید در اولین ستون این Range قرار بگیرد. - col_index_num
شماره ستونی که مقدار بازگشتی ما در آن قرار گرفته در این پارامتر قرار می‌گیرد. برای مثال اگر نام و نام خانوادگی کارمند در ستون دوم جدول درج شده است، مقدار این پارامتر برابر ۲ خواهد بود. - range_lookup
مقدار این پارامتر ضروری در بیشتر موارد می‌بایست برابر false باشد تا جستجو به صورت دقیق انجام شود. اگر این پارامتر را خالی بگذارید یا مقدار آن را true قرار دهید، جستجوی غیر دقیق انجام خواهد شد و ممکن است به جای کد ملی اصلی، کدی که شباهت زیادی به آن دارد، پیدا شود.
مثال استفاده از VLOOKUP
در تصویر زیر جدولی از کد ملی و نام کارمندان را مشاهده می‌کنید. در مقابل این جدول هم یک فیلد برای جستجوی کد ملی درج شده است و در زیر آن نام مربوط به آن کد ملی با استفاده از تابع VLOOKUP که فرمول آن در بالای صفحه قابل مشاهده است، نمایش داده می‌شود:
آشنایی با تابع HLOOKUP
تابع HLOOKUP امضایی شبیه به VLOOKUP دارد و عملکرد آن بسیار شبیه به VLOOKUP است. با این تفاوت که اطلاعات جدول مورد نظر ما می‌بایست افقی ثبت شده باشند. برای مثال اطلاعات جدول نمونه‌ی قبلی اگر به صورت زیر باشد، از HLOOKUP استفاده خواهیم کرد:
آشنایی با تابع XLOOKUP
جدیدترین و کارآمدترین تابع از مجموعه توابع LOOKUP دستور XLOOKUP است که محدودیت‌های موجود در توابع قبلی را ندارد و امکان جستجوی جدول بر اساس ستون یا ردیفی غیر از ردیف اول را در اختیار ما قرار می‌دهد. لازم به ذکر است که اگر از نسخه‌های قدیمی اکسل یا برخی نرم‌افزارهای آنلاین استفاده کنید، ممکن است دسترسی به XLOOKUP مقدور نباشد.
امضای تابع XLOOKUP به شکل زیر است:
1 | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
- lookup_value
مقداری که در جستجوی آن هستیم را اینجا وارد می‌کنیم. مثلا سلولی که حاوی کد ملی است. - lookup_array
مقدار ستون یا ردیفی که حاوی اطلاعات قابل جستجو است در این پارامتر درج می‌شود. در دو تابع VLOOKUP و HLOOKUP لازم بود که کل جدول را به تابع ارسال کنیم ولی در تابع XLOOKUP فقط ردیف یا ستون اصلی که می‌تواند لزوما در ستون و ردیف اول نباشد به صورت جداگانه تعیین می‌شود. - return_array
در این پارامتر یک Range که حاوی نتایج مورد انتظار است، مقدار دهیم می‌کنیم. دقت کنید که تعداد سلول‌های این پارامتر با تعداد سلول‌های پارامتر قبلی یا lookup_array می‌بایست یکسان و ارتباط یک به یک میان آن‌ها برقرار باشد. - if_not_found
اگر مقدار مورد نظر پیدا نشد، این مقدار در سلول نتیجه نمایش داده می‌شود. - match_mode
این پارامتر چهار مقدار را می‌پذیرد.
0 » حالت پیش‌فرض - جستجو به صورت دقیق انجام می‌شود و تنها زمانی که مقدار مورد نظر موجود باشد، نتیجه برگردانده می‌شود.
1» جستجوی دقیق با این تفاوت که اگر نتیجه‌ای پیدا نشود، مقدار بزرگ بعدی برگردانده می‌شود.
1- » جستجوی دقیق با این تفاوت که اگر نتیجه‌ای پیدا نشود، مقدار کوچک‌تر بعدی برگردانده می‌شود.
2 » جستجوی Wildcard برای مقادیر رشته‌ای که در آن علامت‌های * و ? و ~ برای جستوی جزئی رشته‌ها کاربرد دارند. - search_mode
این پارامتر هم نوع جستجو را مشخص می‌کند و چهار مقدار می‌پذیرد:
1 » حالت پیش‌فرض - شروع جستجو از ابتدای Range انجام می‌شود.
1- » شروع جستجو از پایان Range به ابتدا انجام می‌شود. انتخاب جهت جستجو در بهینه‌سازی سرعت و برگشت نتیجه ناموجود تفاوت ایجاد می‌کند.
2 » جستجوی سریع دودویی( Binary ) هنگامی که اطلاعات از بالا به پایین مرتب باشند.
2- » جستجوی سریع دودویی( Binary ) هنگامی که اطلاعات از پایین به بالا مرتب باشند.
برخی از پارامترهای مورد استفاده در دستور XLOOKUP امکان بهینه‌سازی جستجو را فراهم می‌کنند تا زمانی که حجم اطلاعات بسیار زیاد است، پیدا کردن نتیجه به صورت بهینه و با سرعت زیاد انجام شود.
مثال استفاده از XLOOKUP
برای استفاده از XLOOKUP در مثال قبل کافی است از فرمول زیر استفاده کنیم:
1 | =XLOOKUP(D3,A2:A6,B2:B6,"not found",0,1) |
- در قطعه کد بالا ابتدا مشخص کردیم که به دنبال محتوای سلول D3 هستیم
- کد ملی‌های مورد نظر ما در رنج A2:A6 قرار دارند
- نام و نام خانوادگی‌های ما در سلول B2:B6 قرار دارند
- اگر کد ملی مورد نظر پیدا نشد، مقدار not found را نمایش بده
- جستجو به صورت دقیق انجام شود و اگر نتیجه پیدا نشد، ردیف دیگری را برنگرداند
- جستجو از ابتدای جدول به انتها به صورت معمولی انجام شود. جدول ما ممکن است مرتب‌شده نباشد
دقت کنید که این تابع در نسخه‌های قدیمی اکسل موجود نیست و می‌بایست از نسخه‌های جدیدتر از 2019 استفاده کنید.
