ویلوکاپ در اکسل چیست؟ – نحوه استفاده و کار با فرمول VLOOKUP

اکسل توابع زیادی را در اختیار کاربر قرار می‌دهد تا کار با انواع داده را در هر حجمی آسان‌تر کند. ویلوکاپ یکی از این توابع است. این تابع برای بازیابی و جستجوی داده‌ها در اکسل استفاده می‌شود. در ادامه به معرفی تابع ویلوکاپ در اکسل نحوه استفاده از آن، به تنهایی و در کنار سایر توابع، می‌پردازیم. در ابتدای این مطلب از مجله فرادرس، سینتکس تابع ویلوکاپ و عملکرد آن را توضیح می‌دهیم. در ادامه، روش‌های استفاده از تابع را برای مطابقت دقیق و تقریبی آموزش می‌دهیم و در انتها نیز جستجوی دوسویه و چندشرطی با به کمک این تابع بیان می‌کنیم.

سینتکس تابع ویلوکاپ در اکسل

ویلوکاپ (VLOOKUP) از محبوب‌ترین توابع اکسل است. این تابع داده‌ها را اسکن کرده و مقدار نظیر مد نظر شما را پیدا می‌کند. عملکرد ساده این تابع باعث شده در اکثر پروژه‌های اکسل کاربردی باشد. در فرمول زیر، سینتکس تابع ویلوکاپ در اکسل را می‌بینیم:

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

=VLOOKUP(lookup_value, table_array, col_index_number,[range_lookup])

1. lookup_value آرگومان اول مقداری است که قصد جستجوی آن را در جدول دارید.
2. table_array آرگومان دوم آدرس سلول‌هایی (جدولی) که قصد جستجو در آن‌ها را دارید مشخص می‌کند.
3. col_index_number آرگومان سوم شماره ستونی از جدول (آرگومان قبلی) است که قصد برگرداندن داده از آن را دارید.
4. [range_lookup] آرگومان چهارم دقت جستجو را تعیین می‌کند اگر این آرگومان در حالت «False» قرار بگیرد تابع دقیقاً به جستجوی داده‌ای می‌پردازد که در آرگومان اول مشخص کرده‌اید. اگر این آرگومان در حالت «True» قرار بگیرد نتیجه تابع مقادیر مشابه آرگومان اول خواهد بود. پیش فرض تابع ویلوکاپ برای این آرگومان، «True» یا جستجو با تطابق تقریبی است.

توضیح عملکرد تابع ویلوکاپ در اکسل با مثال

تابع VLOOKUP در اکسل جستجوی ستونی انجام می‌دهد. حرف «V» در ابتدای اسم تابع به معنی عمودی (Vertical) است.

برای مثال در جدول بالا می‌توانید از ویلوکاپ برای پیدا کردن یک داده، مشابه تصویر زیر، استفاده کنید.

برای مقدار جستجوی «1005» خروجی فرمول «125» دلار خواهد بود. تابع ویلوکاپ به صورت ستونی، از ستون B به دنبال مقدار جستجو (آرگومان اول) می‌گردد و داده نظیر آن را در سومین ستون می‌یابد.

=VLOOKUP(I4,B5:F9,3,FALSE)

طبق فرمول مقدار جستجو در سلول I4 مشخص شده، اگر محتوای این سلول تغییر کند، مقدار جستجو نیز تغییر خواهد کرد. در فرمول سلول B5 تا F9 برای جستجو انتخاب شده، که در این مثال کل جدول را شامل می‌شود. تابع در ستون اول مقدار جستجوی «105» را می‌یابد و در ستون سوم سلول نظیر آن را با محتوای 125، به عنوان خروجی، در سلول I5 نمایش می‌دهد.

همانطور که گفتیم تابع ویلوکاپ در اکسل جستجوی ستونی انجام می‌دهد. در جدولی که قصد جستجو در آن را دارید (آرگومان دوم)، شماره‌ای برای ستون‌ها از سمت چپ به راست، به ترتیب در نظر بگیرید. از این شماره برای آدرس دهی ستونی که قصد بازگرداندن نتیجه جستجو از آن را دارید، در آرگومان سوم تابع، استفاده کنید. برای مثال، طبق فرمول‌های زیر می‌توانیم نام، نام خانوادگی و ایمیل کاربر «622» را به صورت جداگانه بیابیم.

=VLOOKUP(H3,B4:E13,2,FALSE) // first name
=VLOOKUP(H3,B4:E13,3,FALSE) // last name
=VLOOKUP(H3,B4:E13,4,FALSE) // email address

تنها فرق این فرمول‌های بالا، در شماره ستونی است که در آرگومان سوم آمده است.

تابع ویلوکاپ جستجو را به طور پیش فرض از اولین ستون سمت چپ آغاز کرده به سمت راست می‌رود. البته دقت کنید که اگر جهت جدول را در «Page Layout» و «Sheet Options» از راست به چپ تنظیم کرده باشید، جستجو از اولین ستون سمت راست شروع شده و به سمت چپ می‌رود.

مقداری که برای جستجو انتخاب می‌کنید، باید در ستون اول جدول (آرگومان دوم) وجود داشته باشد. تابع پس از یافتن این مقدار در ستون اول، به سمت راست حرکت می‌کند و ستون‌ها را یکی پس از دیگری بررسی می‌کند تا به اولین نتیجه مطلوب برسد.

تابع ویلوکاپ و جستجو با مطابقت دقیق

همونطور که در سینتکس تابع توضیح دادیم، آرگومان چهارم دقت جستجو را تعیین می‌کند. اگر آرگومان چهارم در مقدار False قرار گیرد تابع ویلوکاپ با مطابقت دقیق به جستجو می‌پردازد.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

در مثال زیر فرمول در سلول H6 سال تولید را بر اساس نام دقیق فیلم پیدا کرده است.

=VLOOKUP(H4,B5:E9,2,FALSE)

تصویر زیر، این مثال را به‌خوبی نشان می‌دهد.

تابع ویلوکاپ و جستجو با مطابقت تقریبی

اگر آرگومان چهارم تابع ویلوکاپ مقدار True داشته باشد، تابع ویلوکاپ با مطابقت تقریبی به جستجو خواهد پرداخت. به عنوان مثال تقسیم بندی نمرات را با حروف A تا F در نظر بگیرید. در تصویر زیر نمرات موجود (در ستون C) را، با استفاده از جدول «key» (در ستون F و G)، دسته بندی می‌کنیم. از جایی که نمرات موجود در ستون C با دسته‌بندی‌های جدول Key دقیقاً یکسان نیستند، نیاز داریم از تابعی استفاده کنیم تا نمرات ستون C را به طور تقریبی با حروف ستون F تطبیق دهد. تابع ویلوکاپ با آرگومان چهارم True، دقیقاً همین کار را انجام می‌دهد. فرمول زیر را در ستون D، روبروی هر یک از نمرات ستون C تکرار می‌کنیم. دقت کنید که آرگومان اول آدرس سلولی است که قصد طبقه‌بندی نمره آن را داریم بنابراین آرگومان اول از سلول C5 تا C16 تغییر می‌کند.

=VLOOKUP(C5,$G$5:$H$10,2,TRUE)

در این مثال اگر تابع ویلوکاپ مقدار دقیق نمره از ستون C را در ستون F پیدا کند، حاصل تابع مقدار متناظر در ستون G خواهد بود. اما اگر نمره را در ستون F پیدا نکند، در اولین سلولی که نمره بیشتری دارد متوقف می‌شود، به سلول قبلی برمی‌گردد و خروجی تابع مقدار متناظر با سلول قبلی خواهد بود. مثلا برای نمره 75، در ستون F از ردیف پنجم به پایین پیش می‌رود تا به اولین عدد بزرگتر از 75 برسد، سپس یک خانه به عقب می‌آید و خروجی تابع مقدار متناظر با سلول F7=70 در ستون G، برابر با نمره «C» خواهد بود. دقت کنید برای استفاده از تابع لوکاپ در جستجوی تقریبی، داده‌های موجود در آرگومان دوم باید به ترتیب صعودی مرتب شده باشند. در غیر صورت تابع احتمالا به درستی کار نخواهد کرد.

حالت پیش فرض تابع ویلوکاپ در اکسل

به خاطر داشته باشید آرگومان چهارم تابع که دقت جستجو را تعیین می‌کند به طور پیش فرض در حالت تطابق تقریبی قرار دارد. اگر به دنبال تطبیق دقیق باشید، فراموش کردن این موضوع می‌تواند در استفاده از تابع مشکل ایجاد کند. برای مثال فرمول زیر را در نظر بگیرید:

=VLOOKUP(F5,B5:D10,3)

همانطور که می‌بینید مقدار 100235 در جدول وجود ندارد بنابراین لوکاپ سلول ها را اسکن می‌کند تا به اولین عدد بزرگ‌تر از 100235 برسد که این مقدار در جدول عدد ۱۰۰۲۳۶ است سپس یک پله به عقب می‌رود و در نتیجه به اشتباه مقدار 12000 دلار را برمی‌گرداند.

برای جلوگیری از این اشتباه دقت کنید که آرگومان چهارم را در حالت False یا 0 قرار دهید.

=VLOOKUP(F5,B5:D10,3,FALSE)