آموزش کامل تابع VLOOKUP در اکسل با مثال
معرفی تابع VLOOKUP
تابع VLOOKUP یک مقدار (مقدار معلوم) را در ستون اول یک محدوده‌ی مشخص شده جستجو می‌کند و نتیجه‌ای (مقدار مجهول) را از همان ستون یا ستون‌های بعدی در همان محدوده به عنوان خروجی بر می‌گرداند. این تابع امکان جستجوی دقیق و یا جستجوی بازه‌ای (غیر دقیق) را در محدوده مورد نظر دارد.
املا / نوشتار
| املای تابع |
|---|
| VLOOKUP(Lookup_valueمقدار معلوم, Table_arrayجدول جستجو و نتیجه, Col_index_numشماره ستون مجهول, [Range_lookup] جستجوی بازه‌ای) |
ورودی ها
| نام ورودی | نوع داده | توضیحات |
|---|---|---|
| Lookup_value | هر نوعی | (مقدار معلوم) مقداری که می‌خواهید جستجو کنید. |
| Table_array | محدوده | محدوده‌ای که باید جستجو شود. |
| Col_index_num | عدد صحیح بزرگتر از 0 | (مقدار مجهول) شماره ستونی که می‌خواهید از آن خروجی بگیرید. |
| [Range_lookup] | مقدار منطقی | [اختیاری] تعیین می‌کند که آیا جستجو دقیق باشد یا تقریبی. |
نوع خروجی
آزاد هر نوعی از داده وابسته به مقدار مجهول مورد نظر برگردانده می شود.
نحوه کارکرد
این تابع مقداری را به عنوان ورودی اول دریافت کرده و آن را در اولین ستون ورودی دوم خود جستجو می کند. در صورتی که پیدا شد بر اساس عددی که در ورودی سوم از کاربر دریافت گرفته شده مقدار دیگری را بر می گرداند.
نکته: نوع ورودی جستجو و نوع داده های ستون اول باید یکی باشند اما نوع خروجی آزاد است و وابسته به شماره ستون مجهول می‌باشد.
فیلم آموزشی
در این قسمت فیلم کامل آموزش تابع vlookup در نرم افزار اکسل را مشاهده می کنید.
خطاها
N/A#اگر تابع VLOOKUP مقدار معلوم را پیدا نکند خطای N/A# بر می گرداند.
!VALUE#اگر ورودی سوم تابع VLOOKUP عدد صفر یا منفی باشد این تابع خطای !VALUE# بر می گرداند.
!REF#اگر مقداری که در ورودی سوم داده می شود بیشتر از تعداد ستون های ورودی دوم باشد این تابع خطای !Ref# بر می گرداند.
?NAME# زمانی که نام تابع را اشتباه وارد کنید یا نام محدوده ها تعریف نشده باشند این خطا رخ می دهد.
نسخه و ملاحظات
این تابع نسبتاً قدیمی بوده و در تمام نسخه های اکسل دیده می شود. همانطور که در بخش توضیحات مشخص شد این تابع امکان برگرداندن اطلاعات ستون های قبل از ستون اول را ندارد که اصطلاحا گفته می شود این تابع قابلیت Backward ندارد. همچنین ورودی آخر این تابع بطور پیش فرض روی حالت یک قرار دارد و سعی می کند جستجوهای طبقه ای و غیر دقیق انجام دهد که این پیش فرض در بسیاری از اوقات کاربران را با چالش مواجه می کند چون فراموش می شود.راهکار جایگزین این تابع استفاده از تابع XLOOKUP می باشد که از اکسل 2021 به بعد در دسترس می باشد.
منابع و لینک ها
لینک آموزش تابع VLOOKUP در سایت W3Schools
لینک آموزش تابع VLOOKUP در سایت Microsoft
مثال
فرض کنید بخواهیم مطابق با تصویر ذیل نام یک محصول را وارد کرده (سلول E3) و قیمت آن را بدست آوریم (سلول F3). طبیعی است که می توانیم با فیلتر کردن کد محصول در جدول (ستون A) به راحتی و بصورت دستی قیمت محصول مورد نظر را در ستون قیمت یعنی ستون C پیدا کنیم اما همیشه مساله به این سادگی نیست، اغلب اوقات یا جدول سمت راست حاوی تعداد زیادی محصول است که پیدا کردن تک تک آن ها در جدول اصلی زمان بر است و یا می خواهیم با تغییر نام محصول (سلول E3) به سرعت قیمت محصول دیگر را پیدا کنیم.
در چنین شراطی با توجه به اینک محدوده داده ها بصورت ستونی ذخیره شده است می توانیم از تابع VLOOKUP به شکلی که در ادامه آمده استفاده کنیم.
در تصویر فوق و در ورودی اول تابع VLOOKUP مقدار معلوم که همان سلول E3 است و حاوی عبارت “بستنی وانیلی” می باشد داده شده است. سپس در ورودی دوم محدوده جستجو به شکلی انتخاب شده که ستون اول حاوی نام محصولات باشد چرا که تابع VLOOKUP می تواند مقدار معلوم را در اولین ستون محدوده جستجو کند. با توجه به اینکه ما به دنبال قیمت محصول هستیم و محدوده را از B تا C انتخاب کرده ایم همانطور که در شماره گذاری ستون ها مشاهده می کنید عدد 2 را به عنوان شماره ستون مجهول به ورودی سوم تابع داده ایم. نکته پایانی اینکه چون جستجوی مورد نظر از نوع دقیق است و می خواهیم دقیقا نام محصولی که در E3 نوشته شده را پیدا کنیم در ورودی آخر تابع مقدار FALSE را قرار می دهیم.
نکته: در اکسل یک نوع داده به نام مقدار منطقی یا همان Logical داریم که البته در بسیار از نرم افزار های دیگر نیز وجود دارد. مقدار منطقی یا 0 است یا 1 که اگر بخواهیم به جای این اعداد عنوان مناسبی استفاده کنیم به جای 0 مقدار FALSE و به جای 1 می توان از هر مقدار غیر صفری منجمله TRUE استفاده کنید. در حالتیکه بخواهیم تابع VLOOKUP را در حالت غیردقیق استفاده کنیم می توانیم ورودی آخر را وارد نکنیم یا در آن هر عددی غیر از صفر و یا مقدار TRUE را وارد کنیم.
نکته: اعدادی که در تصویر به زنگ زرد مشخص شده اند نتیجه فرمول می باشند. عدد سمت راست نتیجه خروجی تابع و عدد سمت چپ که در جدول قرار دارد مقداری است که تابع VLOOKUP با اجرا به آن دست یافته است.
کاربردهای تابع VLOOKUP
کاربردهای تابع VLOOKUP بسیار گسترده است و در بسیاری از حوزه‌ها می‌توان از آن استفاده کرد. یکی از اصلی‌ترین کاربردهای این تابع در زمینه مغایرت گیری و مقایسه‌ی جداول در اکسل می باشد. به عنوان مثال، در شرکت‌ها و سازمان‌ها، هنگام بررسی اطلاعات فروش، یا مالی و پرداخت ها و مقایسه اقدامات بانکی می‌توان از VLOOKUP برای جستجوی اطلاعات مربوط به مشتریان یا محصولات یا پرسنل یا شناسه های بانکی استفاده کرد. در ادامه بخشی از کاربردهای تابع VLOOKUP بصورت فهرست وار قید شده اند:
1- جستجوی مقداری خاص در یک ستون
2- استخراج اطلاعات مرتبط از جداول
3- جستجوی تقریبی برای یافتن بازه ها
4- گزارش‌گیری و دسته‌بندی داده‌ها
5- ایجاد سیستم‌های مدیریت داده
6- تبدیل داده‌های کدگذاری‌شده به اطلاعات قابل‌فهم
7- ارتباط بین جداول مختلف
توابع مشابه
HLOOKUP
MATCH
XLOOKUP
FILTER
LOOKUP
توابع مرتبط
CHOOSE
CONCATENATE
VALUE
TRIM
INDIRECT
IFERROR
IFNA
آقا پوریا م شما گررررم عالی و مفید توضیح دادی
ممنونم ازت
فقط سوالی که برام مونده اینه ک شما اگر ستون معلومت تو یک اکسل دیگه بود، اما ما میخواییم تو یک فایل جدید منتقلش کنیم، در اینصورت باید چیکار کنیم؟؟؟
خواهش میکنم. ستون معلوم یا محدوده مجهول هرجایی که باشند فرقی نداره. باید از آدرس دهی بین فایل ها استفاده کنید.