جستجوی بازه ای با فرمول Vlookup در اکسل

کاربردی جالب از تابع Vlookup برای جستجوی حدودی

آیا تاکنون در اکسل به این مسئله برخورد کرده اید که بازه های عددی زیادی داشته باشید و بخواهید اعداد مختلفی را در آن ها جستجو کنید؟ (شکل ۱) مثلا میخواهیم بدانیم که عدد ۳۲۰ در کدام گروه قرار می گیرد؟ در واقع قصد داشته باشید جستجوی بازه ای یا جستجو حدودی انجام بدید. حل این مسئله روش های مختلفی دارد که در این آموزش قصد داریم کاربردی جالب از فرمول Vlookup در اکسل را برای حل این موضوع شرح بدیم.

شکل ۱- دیتابیس بازه ای

شاید راه حلی که در نگاه اول به ذهن برسد استفاده از if متداخل یا nested if باشد. به این صورت که برای هر بازه، یک if در نظر گرفته شود. اما همانطور که مشخص است، علاوه بر اینکه کار سخت و زمانبری به نظر می رسد، ویرایش آن بسیار دشوار خواهد بود. مثلا اگر ابتدا و انتهای بازه، تغییر کند، کل فرمول را باید اصلاح کرد.

در اینجا راه حلی را با استفاده از فرمول Vlookup در اکسل ارائه خواهیم کرد که بسیار سریع، راحت و انعطاف پذیر در صورت تغییر خواهد بود.

همانطور که قبلا در آموزش تابع Vlookup توضیح داده ایم، آرگومان چهارم این تابع می تواند دو مقدار ۰ یا ۱ را بگیرد. در این آموزش توضیح داده شد که این آرگومان اصولا ۰ قرار داده می شود که بتواند جستجوی دقیق یا Exact Match را انجام دهد. اما چه مواقعی این آرگومان رو ۱ یعنی Approximate Match میگذاریم؟ یکی از کاربردهای این حالت، همین جستجوی بازه ای (تخمینی) هست. با یک مثال بیشتر توضیح میدیم:

مثلا می خواهیم ببینیم عدد ۴۹۹ در کدام گروه قرار میگیرد؟ با توجه به اینکه عدد ۴۹۹ در هیچ یک از این سلول ها وجود ندارد، پس نمیتوانیم به صورت دقیق جستجو انجام بدیم و میخواهیم کاری کنیم که جستجوی بازه ای صورت گیرد.

برای این کار اول باید جدولی رو آماده کنیم که به عنوان Table Array مورد استفاده قرار گیرد. پس بازه های مورد نظر را تایپ کرده و جدول را ایجاد میکنیم. در شکل ۲ ستون A ابتدای بازه و ستون B انتهای بازه و ستون C گروه بندی وارد شده. دقت داشته باشید که این داده ها باید مرتب و از کوچک به بزرگ در جدول چیده شوند که جستجوی تخمینی بتواند صورت گیرد.

مطابق شکل ۲، در سلول F6 تابع Vlookup را می نویسیم و آرگومان آخر را ۱ قرار می دهیم. مقدار Lookup Value یا چیزی که مورد جستجو قرار گرفته، همان عدد ۴۹۹ هست. جدول جستجو هم محدوده A1:C8 هست. میخواهیم بصورت تخمینی جستجو کند و عدد ۴۹۹ را در ستون اول پیدا کند و داده مرتبط با آن را در ستون سوم نمایش دهد. پس آرگومان Col_index عدد ۳ خواهد بود. آرگومان آخر هم طبق توضیحاتی که ارائه شد، عدد ۱ یا مقدار True قرار داده می شود.

توجه داشته باشید که می توانیم ستون B را حذف کنیم (برای درک بهتر مفهوم بازه، جدول را به این صورت نمایش دادیم). در واقع تابع Vlookup جستجو را در ستون اول انجام می دهد و ملاک فقط ستون اول هست.

به این ترتیب این تابع با جستجوی تخمینی، اعداد بین بازه ها را تشخیص خواهد داد و داده مرتبط با ستون سوم را به عنوان خروجی نمایش می دهد. عدد ۴۹۹ در بازه ۳۵۰-۵۰۰ قرار میگیرد و گروه بندی D به عنوان خروجی نمایش داده خواهد شد.

شکل ۲- فراخوانی عدد مورد نظر در بازه های موجود

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

اگر در کار با تابع Vlookup به مشکل جستجوی موارد تکراری بر خوردید، حتما پیشنهاد میکنم مقاله جستجو موارد تکراری رو بخونی.

نکته خیلی مهم: باز و بسته بودن بازه ها به صورت (A B] خواهد بود. برای مثال، عدد ۵۰۰ در گروه بندی E قرار خواهد گرفت. پس انتخاب ابتدا و انتهای بازه ها (با توجه به مفهوم و خروجی مورد انتظار) از اهمیت بسیار بالایی برخوردار است.