یکی از توابع اکسل، VLOOKUP است که برای یافتن سلول‌ها در جداول بزرگ بسیار مفید است. از این تابع برای جستجو استفاده می‌شود. به عنوان مثال نام یا یک ویژگی مشخص از محصول، کالا، مشتری یا غیره جستجو شده و یکی دیگر از ویژگی‌های آن مثل قیمت، نام، شناسه و غیره، به عنوان خروجی چاپ می‌شود.
قبلاً با تابع VLOOKUP و روش استفاده از آن آشنا شدیم و موضوع این مقاله، استفاده از تابع VLOOKUP برای جستجو در جدولی حاوی بازه‌های اعداد مرتب است. به عبارت دیگر هدف یافتن بازه‌ای است که عدد هر محصول و کالا و غیره در آن قرار می‌گیرد.
برای اطلاعات بیشتر در مورد تابع VLOOKUP و روش کار با آن در نرم‌افزارهایی مثل Excel مجموعه‌ی آفیس مایکروسافت، مطالعه کردن مطلب زیر را توصیه می‌کنیم:
استفاده از VLOOKUP برای جستجو در جدول بازه‌های اعداد
فرض کنید لیستی از نمرات دانش‌آموزان داریم و می‌خواهید برای هر نمره، درجه‌ای مثل A و B و C در نظر بگیریم. در اولین ستون امتیازات موجود است و می‌خواهیم در دومین ستون، درجات را مشخص کنیم. در این مثال برای درجات نیز جدول کوچکی در ستون‌های D و E تعریف کرده‌ایم.
با استفاده از VLOOKUP می‌توان نمرات را در جدول کوچک جستجو کرد و درجه‌ی هر نمره را دریافت کرد. به این درجه‌ی هر دانش‌آموز در ستون B با استفاده از VLOOKUP قابل محاسبه است.
سینتکس کلی تابع VLOOKUP به صورت زیر است:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
و ۴ آرگومان این تابع به ترتیب موارد زیر است:
- lookup_value: مقداری که جستجو می‌شود است. در این مثال نمره‌ی دانش‌آموز سرچ می‌شود که در سلول‌های ستون A قرار دارد.
- table_array: محدوده‌ی جستجو یا جدول اطلاعات است. در مثال ما جدولی حاوی سلول‌های D2 الی E7 موردنظر است.
- col_index_num: شماره‌ی ستون داده‌ی خروجی است که در مثال ما، عدد ۲ است.
- Range lookup مقدار اضافی است که اگر True باشد، مقادیر مشابه نیز به عنوان نتیجه‌ی جستجو معرفی می‌شوند و اگر False باشد، صرفاً مقدار خاصی معرفی می‌شود.
- range_lookup: پاسخ این سوال است که آیا به دنبال مقادیر مشابه از جدول مرتبی هستید یا خیر؟ در مثال ما قرار است جدول مرتب نمرات و درجات جستجو شود و حتی اگر عددی دقیقاً موجود نباشد، درجه‌ی مربوط به عدد قبلی دریافت شود. لذا پاسخ مثبت یا TRUE است.
بنابراین فرمولی که می‌بایست در سلول B2 قرار بگیرد، اینگونه خواهد بود:
=VLOOKUP(A2,$D$2:$E$7,2,TRUE)
پس از وارد کردن این فرمول در سلول B2، می‌توانید تمام سلول‌های ستون B به جز سلول عنوان را انتخاب کنید و با Ctrl + D ، فرمول را در دیگر سلول‌ها کپی کنید. به این ترتیب درجه‌ی نمرات دانش‌آموزان تکمیل می‌شود.
نکته‌ی مهم در فرمول ذکر شده این است که برای ثابت نگه داشتن محدوده‌ی جستجو که D2:E7 است، از $D$2:$E$7 استفاده شده است. با قرار دادن علامت $ قبل از نام ستون و شماره‌ی ردیف یک سلول، حین کپی کردن فرمول در سلول‌های بعدی، تغییری در این بخش از فرمول اعمال نمی‌شود و سایر بخش‌ها به صورت نسبی تغییر می‌کنند.
حین استفاده از VLOOKUP به ترتیب صعودی توجه کنید
حین استفاده از تابع VLOOKUP توجه کنید که اولین ستون از محدوده‌ی جستجو می‌بایست به ترتیب صعودی مرتب شده باشد. به عبارت دیگر در مثال ما می‌بایست نمرات از کم به زیاد چیده شود. در تصویر زیر حالت برعکس است و نمره‌ی بالاتر در صدر قرار گرفته است. در نتیجه تابع VLOOKUP عملکرد صحیحی ندارد:
اما اگر در پاسخ به این سوال که آیا قرار است در جدولی سرچ کنید؟، عبارت False را قرار بدهید و به بیان دیگر اگر آخرین آرگومان تابع VLOOKUP را FALSE وارد کنید، ترتیب مهم نیست اما در این صورت می‌بایست برای هر نمره به صورت مجزا یک درجه تعریف شده باشد. در غیر این صورت برای نمرات ناموجود مثل ۵۱ و ۹۲ و غیره، درجه‌ای چاپ نمی‌شود.
محاسبه‌ی تخفیف بسته به میزان خرید با VLOOKUP
مثال دوم ما در این مقاله، محاسبه‌ی تخفیف بر اساس میزان خرید است. مثل نمونه‌ی قبلی جدولی حاوی ستون A و B که به ترتیب مقدار خرید و مقدار تخفیف است داریم و جدول کوچکی نیز حاوی درصد تخفیف برای مقادیر مختلف خرید است.
برای محاسبه‌ی درصد تخفیف اولین مشتری، از فرمول زیر استفاده می‌شود:
=VLOOKUP(A2,$D$2:$E$7,2,TRUE)
اما برای محاسبه‌ی هزینه‌ی نهایی، می‌بایست مقدار سلول A2 یا خرید را منهای مقدار خرید کنیم. لذا فرمول محاسبه‌ی هزینه پس از تخفیف، به این صورت است:
=A2-A2*VLOOKUP(A2,$D$2:$E$7,2,TRUE)
howtogeekسیاره‌ی آی‌تی