فهرست مطالب
اگر جستجوی گوگل شما را به مطالعه این صفحه هدایت کرده باشد احتمالا بهخوبی اهمیت تابع Vlookup در اکسل را میدانید. کافی است با جدولهای متعدد و پیچیده روبرو شده باشید تا نیاز به این تابع را احساس کنید. Vlookup به منظم کردن دادههای جدول، جدا کردن ستونهای مورد نیاز با حفظ جایگاه و همچنین ادغام چند جدول مرتبط اما جدا از هم کمک میکند. در این مقاله بهطور مفصل به آموزش تابع vlookup و کاربردهای آن میپردازیم.
تابع VLOOKUP چیست؟
عبارت V+Lookup مخفف Vertical Lookup به معنای جستجوی عمودی است. درواقع تابع وی لوک آپ به اکسل اعلام میکند بر حسب داده مشخصی (مثلا تابلو فرش) در جدولی از یک مجموعه داده (مثلا لیست قیمت در 10 سال گذشته)، اطلاعات خاص و مجهولی (مانند قیمت در 1399) را بازخوانی کند. اگر این اطلاعات بهطور دقیق در دسترس باشد مقدار آن تعیین خواهد شد و در غیر این صورت، مقدار تقریبی را بهعنوان خروجی دریافت خواهیم کرد. این تابع هم برای ادغام دادهها در چند صفحه و هم برای مرتب کردن دادههای مشخص و محدودی از یک جدول بزرگ به کار میرود.
چگونه از تابع VLOOKUP استفاده کنیم؟
با یک مثال میخواهیم روش استفاده از تابع vlookup در اکسل را توضیح بدهیم. در این مثال یک فایل اکسل داریم که شامل دو صفحه است. دادههای هر دو صفحه مربوط به 20 سریال برتر از نظر سایت IMDB هستند. در صفحه اول نام آثار، ژانر، سازنده هر سریال و شناسه آن قرار گرفته است.
در جدول صفحه دوم، تاریخ شروع و پایان هر سریال، تعداد اپیزودها و نمره IMDB آنها مشخص شده است اما نام آثار تکرار نشده و تنها شناسه را داریم. (پیشتر در مقالهای فارسی کردن اعداد در اکسل را آموزش دادهایم.)
حالا فرض کنید قصد داشته باشیم با رسم جدول کوچکتری، ژانر و رتبه IMDB چند شناسه مشخص را در کنار هم جمعآوری کنیم. ابتدا این جدول را با سه ستون و تعداد ردیف مشخص (هماندازه با شناسههای معلوم) رسم نموده و ستونهای آن را نامگذاری میکنیم. در ستون اول شناسه سریالها را مینویسیم و دو ستون بعدی را به ژانر و IMDB اختصاص میدهیم. ستون اول حتما باید به متغیری اختصاص پیدا کند که بین دو جدول صفحه اول و دوم مشترک است. به این متغیر مشترک اصطلاحا کلید گفته میشود.
در حالت اول، میخواهیم بر حسب این شناسهها، ژانر آنها را در ستون دوم بازخوانی کنیم. Vlookup در این شرایط به کمک ما میآید. در اولین سلول از ستون دوم تابع را به این شکل مینویسیم:
=VLOOKUP(
تابع vlookup به چهار متغیر نیاز دارد:
1. متغیر اول؛ مقدار معلوم (lookup_value)
مقدار معلوم همان عدد یا واژهای است که به آن دسترسی داریم و میخواهیم دادههای بعدی را برحسب آن پیدا کنیم. در این مثال، شناسه هر سریال، معلوم ماست. بنابراین بعد از پرانتز باید آن را وارد کنیم. برای وارد کردن شناسه میتوانیم خود عبارت را داخل دو آپاستروف بنویسیم، آدرس سلول آن را وارد کنیم یا مستقیما روی سلول مورد نظر در جدول جدید کلیک کنیم. هر کدام از این سه روش، نتیجه مشابهی (در اینجا H8) را به ما میدهد. سپس از کاما (,) استفاده میکنیم:
=VLOOKUP(H8,
2. متغیر دوم؛ جایگاه جستجو (table_array)
در این قسمت باید مشخص کنیم که محدوده جستجوی مورد نظرمان کجاست. در واقع لازم است سلول شروع (شامل ستون و ردیف) و سلول پایان یعنی نقطه انتهایی جدول (شامل ستون و ردیف) را به تابع معرفی کنیم. میتوانیم به جای این کار، مستقیما از روی جدول محدوده را با حرکت ماوس انتخاب (Select) کنیم. هر یک از این دو سلول را با ترکیب حرفعدد (مانند: B15) نشان میدهیم؛ بین دو سلول نیز از علامت دو نقطه (:) استفاده میکنیم. نقطه شروع ما باید اولین سلول در ستون متغیر معلوم باشد و نقطه پایان به شکلی انتخاب شود که دستکم ستون مربوط به مجهول کاملا در آن جا بگیرد. در نهایت یک جدول معین را با این دو مقدار میتوانیم مشخص کنیم:
=VLOOKUP(H8, B2:F21,
راستی، کاما را در انتها فراموش نکنید!
3. متغیر سوم؛ ستون مورد نظر (col_index_num)
حالا باید شماره ستون مجهول را انتخاب کنیم. تنها نکته مهم این است که شمارش را باید از همان نقطه ابتدایی محدوده، که در متغیر قبلی به آن اشاره شد شروع کنیم. با توجه به اینکه متغیرهای «ژانر» را میخواهیم پیدا کنیم و شمارش از ستون B باید شروع شود، ستون مورد نظر ستون پنجم است:
=VLOOKUP(H8,B2:F21,5
4. متغیر نهایی؛ میزان دقت! (range_lookup)
ورودی مورد نظر ما در این بخش از سه حالت خارج نیست: یا آن را وارد نمیکنیم و بدون گذاشتن کاما، پرانتز را میبندیم. یا از یکی از دو واژه True و False استفاده میکنیم. در صورتیکه واژه False را تایپ کنیم داده مورد نظر بهشکل دقیق به ما ارائه میشود. اگر True را بزنیم نزدیکترین داده به مجهول را میبینیم و اگر پرانتز را ببندیم عملکردی مشابه True خواهد داشت.
در نهایت Enter را میزنیم و تابع را کپی کرده روی بقیه سلولهای ستون، Paste میکنیم:
این قسمت از جدول را با هم پر کردیم. در نظر بگیرید که در همین جدول ساده هم این کار بسیار کمککننده است اما در ابعاد بزرگتر و جدولهای پیچیدهتر با دادههای فراوان، این قابلیت تابع vlookup در اکسل حیاتی است!
حالا قصد داریم با استفاده از تابع vlookup و با توجه به صفحه دوم، رتبه IMDB هر شناسه را در ستون سوم این جدول بیاوریم. ابتدا روی سلول اول از ستون IMDB (در جدول کوچکتر) دو بار کلیک میکنیم و تابع VLOOKUP را بعد از مساوی مینویسیم:
=VLOOKUP(
حالا متغیرها را به ترتیب وارد میکنیم:
متغیر اول؛ معلوم تابع
مانند مثال قبلی، متغیر اول شناسه مربوط به سریالهاست. بنابراین مجددا به سلول اول از ستون اول جدول کوچک ارجاع میدهیم:
=vlookup(H8,
کاما فراموش نشود!
متغیر دوم؛ محدوده
اگر خاطرتان باشد، متغیر دوم در مثال قبلی، محدوده جستجو بود. با توجه به اینکه محدوده جستجو در این مثال، صفحه دوم فایل اکسل ماست، باید این را در فرمول مربوطه بنویسیم و در انتها از علامت تعجب (!) استفاده کنیم:
=vlookup(H8,Sheet2!
اگر مثل ما، صفحات اکسل را با نام دیگری ذخیره کردهاید، در این قسمت از همان نام (در این مثال: صفحه دوم) استفاده کنید. اما دقت داشته باشید در دو طرف این عبارت باید آپاستروف بگذارید. آپاستروف در اکسل باعث میشود هر فرمتی در تابع، بهشکل متن نمایش داده شده و روی آن عملیات خاصی انجام نشود. افزودن علامت تعجب را هم فراموش نکنید:
=VLOOKUP(H8,'صفحه دوم'!
هنوز کار ما با این قسمت تمام نشده است. حال باید سلول شروع و سلول پایان جدول موردنظر در صفحه دوم را نیز وارد کنیم و بین آنها از علامت دونقطه (:) استفاده نماییم. بنابراین اولین سلول ما B1 و سلول انتهایی S33 است. سلول نهایی کافی است از E21 بزرگتر باشد تا حتما مجهولهای ما را در بر بگیرد. ستون حاوی نقطه شروع هم لزوما باید با ستون معلوم ما یکسان باشد و ردیف آن ترجیحا از ابتدای جدول محاسبه شود:
=vlookup(H8, 'صفحه دوم'! B1:S33,
متغیر سوم؛ ستون حاوی مجهول
درست مانند حالت قبلی، شمارش ستونها در این مرحله انجام میشود. فاصله ستون حاوی معلوم تا ستون مجهول را تعیین میکنیم. در این مثال، جدول صفحه دوم ما شش ستون دارد و فاصله ستون معلوم و مجهول از هم 4 ستون است:
بنابراین در فرمول عدد 4 را قرار میدهیم:
=VLOOKUP(H8'صفحه دوم', !, 4
متغیر آخر؛
این متغیر هم درست مانند مرحله قبلی وارد میشود. اگر میخواهیم مقدار دقیق را داشته باشیم از FALSE استفاده میکنیم. سپس پرانتز را میبندیم و Enter را میزنیم. حالا فرمول نهایی را کپی کرده و به ردیفهای دیگر این ستون منتقل میکنیم.
خطای N/A چیست و چرا در VLOOKUP با آن روبرو میشویم؟
N/A یا بهطور دقیقتر Not Available خطایی است که بعد از استفاده از تابعهایی مانند vlookup یا Hlookup با آن مواجه میشویم. این خطا زمانی رخ میدهد که:
- مقدار معلوم واردشده وجود ندارد.
- مقدار معلوم واردشده درست و کامل نوشته نشده است.
- برای تابع مقدار دقیقی وجود ندارد و شما از گزینه False (یعنی پیدا کردن نتیجه دقیق و مستقیم) استفاده کردهاید.
- محدوده جدول بهدرستی تعیین نشده است.
با دانستن علت نمایش این ارور میتوانید آن را برطرف کنید.
محاسن و معایب تابع vlookup در اکسل
همانطور که گفتیم استفاده از این تابع کار ما را بسیار آسان میکند و به دادهها نظم میدهد. تابع vlookup در اکسل، کاربردی، آسان و رضایتبخش است. با اینحال چند ایراد کلی دارد که ممکن است باعث بروز مشکلاتی در کار ما شود. مهمترین ایراد این تابع، پیشفرضها و معیارهای دست و پاگیر آن است. این معیارها کار را سخت و طولانی میکند. مشکل دوم زمانی اتفاق میافتد که برای یک مقدار معلوم، بیش از یک جواب داشته باشیم. در این شرایط تابع Vlookup تنها مقدار اول را بازخوانی میکند و مقدار دوم را نادیده میگیرد.
نکات پایانی برای استفاده از تابع Vlookup
در انتهای این مقاله توجه شما را به چند نکته مهم در استفاده از این تابع جلب میکنیم:
- اولین سلولی که در بازه خواستهشده در تابع (متغیر دوم) قرار میدهیم حتما باید همسلول با معلوم تابع باشد.
- هم معلوم و هم مجهول باید در بازه تعیینشده قرار بگیرند.
- تابع Vlookup نسبت به حروف بزرگ و کوچک انگلیسی حساس نیست.
- جستجوی این تابع تنها رو به جلو و در مقادیر بعدی است. مقدارهای قبل از معلوم را نمیتوان با این تابع بازخوانی کرد و باید از توابع دیگر کمک گرفت.
در این مقاله تلاش کردیم بهطور جامع و کامل روش استفاده از تابع Vlookup را آموزش بدهیم. میتوانید سوالات خود را در قسمت نظرات بپرسید و یا برای دریافت نکات کاربردی بیشتر، آموزش پیشرفته اکسل ایران لرن را دریافت نمایید.
