فرادرسویلوکاپ در اکسل چیست و چگونه از VLOOKUP استفاده می شود؟
جست‌وجوی داده در اکسل کار پیچیده‌ای نیست و معمولا به راحتی انجام می‌شود. اما زمانی می‌خواهیم دادهای را با توجه به موقعیت داده دیگری پیدا کنیم، شرایط تغییر می‌کند. تابع ویلوکاپ در اکسل (VLOOKUP) برای چنین شرایطی ایجاد شده و می‌تواند با دریافت آدرس یک سلول و بررسی محتوای آن، سلول مشابه را در ستونی از دادهها پیدا کرده و محتوای سلول موجود در ستون متناظر با آن را نمایش دهد. این توضیحات شاید کمی پیچیده به نظر برسند، به همین خاطر در این مطلب ضمن توصیف کارکرد تابع ویلوکاپ در اکسل، به نحوه استفاده از آن نیز می‌پردازیم.
تابع ویلوکاپ در اکسل چیست؟
جدولی از دادهها را تصور کنید که در آن اطلاعات مربوط به صد نفر از کارکنان شرکتی در پانزده ستون جمع‌آوری شده است. ستون اول این جدول شامل نام افراد است و ستون سیزدهم هم دستمزد ماهانه آن‌ها را نشان میدهد. حالا می‌خواهیم بدون جست‌وجوی چشمی و تنها با استفاده از توابع اکسل، میزان دستمزد یکی از افراد را پیدا کنیم.
در چنین شرایطی تابع «VLOOKUP» کاربردی ظاهر شده و نتیجه دلخواه را به راحتی پیدا خواهد کرد. شکل کلی این تابع به صورت زیر نوشته می‌شود.
1=VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])- Lookup_Value: آدرس سلول موردنظر که جستوجو بر اساس محتوای آن انجام می‌شود.
- Table_Array: بازه یا جدول داده‌هایی که جستوجو در آن انجام می‌شود.
- Column_Index_Num: شماره ستونی که نتیجه باید در آن پیدا شود.
- Range_Lookup: مقدار منطقی True یا False که اولی نتایج مشابه سلول مرجع و دومی نتایج دقیقاً منطبق را پیدا می‌کند.
همان‌طور که اشاره شد، ویلوکاپ در اکسل آدرس داده موردنظر را دریافت و دادهای که در سلول متناظر با آن و در ستون اول از بازه دلخواه قرار دارد را پیدا می‌کند. بهتر است دادهای که می‌خواهیم در جدول جستوجو شود را در سلولی با فاصله از جدول بنویسیم.
بسته به مقدار «Range_Lookup»، دادهای که در ستون اول انتخاب می‌شود می‌تواند دقیقاً منطبق با سلول مرجع (False) یا مشابه با آن (True) باشد. در گام بعد، تابع ویلوکاپ ستون موردنظر را بر اساس شماره وارد شده یافته و سپس سلول متناظر با سلول داده ستون اول را در آن پیدا می‌کند. در اینجا توجه به دو نکته مهم است:
- نخست اینکه ملاک ویلوکاپ برای جستوجو، اولین ستون از جدول داده‌های انتخابی است. در بازه انتخابی، ستون‌ها از چپ به راست (در صفحه چپ‌چین) شماره‌گذاری می‌شوند.
- نکته دوم اینکه تابع VLOOKUP داده‌های ستون اول را از بالا به پایین بررسی می‌کند و اولین نتیجه مشابه یا منطبق را برمی‌گزیند. پس اگر در ستون اول داده‌های تکراری وجود داشته باشند، ویلوکاپ نتیجه را برای اولین داده مشابه یا منطبق نمایش خواهد داد.
مزایا و محدودیت‌های تابع ویلوکاپ در اکسل چیست؟
تابع VLOOKUP یکی از توابع مشهور اکسل است که این شهرت را به دلیل مزایا و محدودیت‌های خاص خود به دست آورده است. از نقطه نظر مزایا به خصوص برای کاربران تازه‌کار، طرز کار ویلوکاپ و توانایی آن در جستوجو و یافتن داده‌های موردنظر در جدول بزرگی از دادهها بسیار جالب توجه است.
اما از سوی دیگر محدودیت‌های ویلوکاپ هم قابل چشم‌پوشی نیستند. به عنوان مثال این تابع بر خلاف توابع Index و Match، به ستون اول بازه انتخابی به عنوان مبنای جستوجو محدود است. از سوی دیگر ساختار VLOOKUP باعث می‌شود اوایل کار با آن، به آسانی با خطا مواجه شوید. البته جای نگرانی نیست و با تسلط بر مبانی استفاده از ویلوکاپ در اکسل می‌توانید از بروز چنین خطاهایی جلوگیری کنید.
ویژگی‌های تابع ویلوکاپ در اکسل
با توجه به توضیحات قبلی، در ادامه هر یک از ویژگی‌های تابع ویلوکاپ را به‌صورت جداگانه بررسی می‌کنیم.
نوع داده موردنیاز تابع ویلوکاپ
حرف «V» در «VLOOKUP» از کلمه انگلیسی «Vertical» گرفته شده که به معنی «عمودی» است. در واقع هدف اصلی تابع ویلوکاپ، پیدا کردن داده موردنظر در جدولی از داده‌هایی است که به صورت عمودی در چند ستون مرتب شده‌اند. به مثال زیر توجه کنید:
با توجه به جدول بالا، اگر بخواهیم نام مربوط به شماره سفارش «1004» را پیدا کنیم، تابع را باید به صورت زیر بنویسیم:
1=VLOOKUP(1004,B5:F9,4,FALSE)همان‌طور که در فرمول مشاهده می‌کنید، چهارمین ستون برای جستوجو در بازه «B5:F9» انتخاب شده و دادهای پیدا می‌شود که در سطر سلول حاوی عدد 1004 است.
اهمیت شماره ستون در تابع ویلوکاپ
زمانی که از تابع ویلوکاپ استفاده می‌کنید، باید به صورت فرضی ستون‌ها را از چپ به راست (و معکوس آن در صفحه راست‌چین) شماره‌گذاری کنید. با این کار در وارد کردن مقدار Column_Index_Num اشتباه نخواهید کرد. به مثال زیر توجه کنید.
در این مثال می‌خواهیم به ازای هر شماره «ID»، اطلاعات فردی و ایمیل افراد را ببینیم. برای این کار کافی است در سلول‌های مورد نظر، تابع ویلوکاپ را نوشته و تنها شماره ستون را تغییر دهیم.
1=VLOOKUP(H3,B4:E13,2,FALSE) // اسم کوچک
2=VLOOKUP(H3,B4:E13,3,FALSE) // اسم بزرگ
3=VLOOKUP(H3,B4:E13,4,FALSE) // آدرس ایمیلحالا با نوشتن هر کد در سلول ID اطلاعات مربوط به آن شخص قابل مشاهده خواهد بود.
مسیر پیدا کردن دادهها در تابع ویلوکاپ
تابع ویلوکاپ همواره به سمت راست حرکت کرده و داده‌های مورد نیاز را از سلول‌های سمت راست ستون اول پیدا می‌کند (این مسیر در صفحه راست‌چین برعکس است).
به همین خاطر باید ستون مرجع به دقت انتخاب و تعریف شود تا در استفاده از ویلوکاپ مشکلی پیش نیاید.
انواع تطبیق داده در تابع ویلوکاپ
آرگومان Range_Lookup در ویلوکاپ دو مقدار منطقی True یا False را می‌پذیرد. اگر مقدار False در تابع قرار گیرد، ویلوکاپ در ستون اول جستوجو کرده و دادهای را می‌پذیرد که دقیقاً با مقدار Lookup_Value مطابق باشد. در اغلب مواقع نیز از همین مقدار استفاده می‌شود.
در مثال بالا برای پیدا کردن سال تولید اثر، نام فیلم در ستون اول جستوجو می‌شود و فرمول آن به صورت زیر است:
1=VLOOKUP(H4,B5:E9,2,FALSE)اما بعضی مواقع هم از مقدار True در تابع ویلوکاپ استفاده می‌شود تا نزدیک‌ترین مورد به Lookup_Value را بیابیم. این حالت بیشتر برای داده‌های عددی کاربرد دارد تا نزدیک‌ترین مورد پیدا شود. البته در نظر داشته باشید که در زمان استفاده از تابع ویلوکاپ با مقدار True، دادهها باید بر اساس ستون اول و به صورت صعودی مرتب شوند. در غیر این صورت ممکن است تابع نتیجه نادرستی را نمایش دهد.
انتخاب نتیجه در داده ویلوکاپ
تابع VLOOKUP همواره اولین نتیجه منطبق را انتخاب کرده و نمایش میدهد. در مثال زیر رنگ سبز (Green) سه بار تکرار شده اما ویلوکاپ تنها قیمت مقابل اولین مورد را شناسایی کرده و نشان میدهد.
1=VLOOKUP(E5,B5:C11,2,FALSE)
تابع ویلوکاپ در اکسل و وایلدکارت
پیش از این در «مجله فرادرس» با وایلد کارت‌ها آشنا شده‌اید. استفاده از وایلدکارت‌ها در ترکیب با تابع ویلوکاپ، امکان جست‌وجوی داده‌هایی که شامل Lookup_Value هستند را فراهم می‌کند. به عنوان مثال اگر در Lookup_Value مقدار «آب» را قرار دهید، تابع اولین سلولی که شامل کلمه «آب» باشد را انتخاب کرده و نتیجه را بر اساس آن نمایش میدهد.
در مثال زیر فهرستی از نام و نام خانوادگی افراد به همراه شماره شناسایی (ID) و واحد مربوطه آمده است. با توجه به توضیح فوق، جدولی درست شده که نام، نام خانوادگی، ID و دپارتمان مربوط به افراد را بر اساس Lookup_Value که در سلول جداگانه‌ای نوشته شده، پیدا می‌کند. فرمول به کار رفته در سلول‌های این جدول با توجه به ستون مورد نظر به شکل زیر نوشته می‌شود:
1=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)فرمول فوق نام افراد را بر اساس داده موجود در سلول مقابل «Find» پیدا کرده و نمایش میدهد. به همین ترتیب برای سایر موارد هم فرمول نوشته شده و نتیجه مطابق با Lookup_Value پیدا می‌شود. توجه داشته باشید که هنگام ترکیب وایلد کارت و ویلوکاپ، مقدار Range_Lookup همواره باید False باشد.
خطاهای تابع ویلوکاپ در اکسل
هنگام استفاده از تابع ویلوکاپ در اکسل ممکن است با خطاهای مختلفی مواجه شوید که احتمالاً یکی از موارد زیر عامل بروز خطا باشد:
- مقدار تعریف شده برای Lookup_Value در بازه انتخابی وجود ندارد.
- مقدار Lookup_Value اشتباه املایی دارد یا فاصله (Space) اضافی در آن تایپ شده است.
- مقدار Range_Lookup را False در نظر گرفته‌اید در حالی که باید True باشد (تطبیق دقیق یا تشابه).
- بازه جدول دادهها به درستی انتخاب نشده است.
- تابع VLOOKUP را کپی پیست کرده‌اید اما آدرس سلول مرجع آن را ثابت در نظر نگرفته‌اید.
در مورد آخر، برای ثابت نگه داشتن آدرس سلول مرجع، باید در دو سمت حرف مربوط به ستون آن از علامت «$» استفاده شود (مانند فرمول مثال وایلد کارت در بخش قبلی مطلب).
سوالات متداول
در انتها به سوالات متداول درباره تابع ویلوکاپ می‌پردازیم.
چرا تابع ویلوکاپ در اکسل ارور N/A# نشان میدهد؟
دلایل مختلفی برای نمایش خطای N/A# در تابع ویلوکاپ وجود دارد که مهم‌ترین آن‌ها اشتباه املایی در تعریف Lookup_Value یا تعریف اشتباه بازه جستوجو هستند.
ترکیب تابع ویلوکاپ و وایلد کارت ها چه کاربردی دارد؟
ترکیب این دو تابع برای تسهیل جستوجو در دادهها کاربرد داشته و به کاربر اجازه میدهد با نوشتن بخشی از عنوان مورد نظر، اولین دادهای که شامل عبارت تایپ شده باشد را بیابد.
