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

۳۸۵ بازدید
آخرین به‌روزرسانی: ۲۵ دی ۱۴۰۲
زمان مطالعه: ۶ دقیقه
ویلوکاپ در اکسل چیست؟ – نحوه استفاده و کار با فرمول VLOOKUP

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

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

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

=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 قرار گیرد تابع ویلوکاپ با مطابقت دقیق به جستجو می‌پردازد.

در مثال زیر فرمول در سلول 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)

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

وجود دو نتیجه ممکن برای تابع ویلوکاپ

اگر بیشتر از یک جواب برای تابع ویلوکاپ وجود داشته باشد، تابع اولین یافته را نمایش می‌دهد. برای مثال در تصویر زیر تابع ویلوکاپ باید قیمت رنگ «Green» را پیدا کند در جدول Green دو بار تکرار شده است، در نتیجه تابع ویلوکاپ در این مثال، قیمت متناظر اولین Green یافت شده از بالای ستون را نمایش می‌دهد. فرمول این عملیات در سلول F5 به شرح زیر است:

=VLOOKUP(E5,B5:C11,2,FALSE)

اگر در چنین مثالی قصد یافتن آخرین Green را داشته باشید، باید از تابع «XLOOKUP» استفاده کنید و برای یافتن قیمت تمامی Greenها، تابع «FILTER» استفاده می‌شود.

جستجوی وایلد کارت (Wildcard) با تابع ویلوکاپ

ویلوکاپ از جستجوی وایلد کارت اکسل پشتیبانی می‌کند به این معنا که این تابع توانایی اجرای جستجوی جزئی را دارد. برای استفاده از وایلد کارت در تابع ویلوکاپ، باید آرگومان چهارم روی False یا 0 تنظیم شده باشد. در مثال زیر بعد از اینکه در سلول H4 عبارت «Aya» تایپ شود، ویلوکاپ در سلول H7‌، نام «Michael» را برمی‌گرداند.

در فرمول زیر به جایگاه «*» و «&» دقت کنید.

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)
جستجوی وایلد کارت با تابع ویلوکاپ

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

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

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)
جستجوی دو سویه ا تابع وی لوکاپ در اکسل

تابع Match ماه «Feb» را در بازه B4 تا E4 پیدا می‌کند و عدد 3 را به تابع ویلوکاپ به عنوان آرگومان سوم باز می‌گرداند، چراکه «Feb» در ستون سوم جدول قرار دارد.

جستجوی چند شرطی با تابع ویلوکاپ در اکسل

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

=VLOOKUP(H4&H5,B5:E13,4,0)
تابع ویلوکاپ اولین یافته را به عنوان خروجی نمایش می‌دهد

ارور #N/A و تابع ویلوکاپ

در استفاده از تابع ویلوکاپ در اکسل گاهی با خطای #N/A مواجه خواهید شد. خطا به این معنی است که عبارت نظر یافت نشده است. در مثال زیر عبارت «Toy Story 2» در جدول وجود ندارد. در نتیجه تابع ویلوکاپ خطای #N/A را باز می‌گرداند.

خطا در ویلوکاپ

#N/A خطای کارآمدی است، چراکه به شما می‌گوید در داده‌ها نقصی وجود دارد. این خطا می‌تواند در حالات مختلف زیر ایجاد شود:

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

اگر مایلید به جای خطای #N/A عبارت دلخواه دیگری نمایش داده شود، می‌توانید از تابع «IFNA» استفاده کنید.

استفاده از تابع IFNA با تابع ویلوکاپ

در مثال بالا فرمول به شرح زیر خواهد بود:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

حتی می‌توانید تعیین کنید که در صورت وجود خطای #N/A ، تابع ویلوکاپ، سلول را خالی بگذارد. در مثال ذکر شده، این کار با فرمول زیر انجام می‌شود:

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

در مثال بالا از مجله فرادرس، اگر خطای #N/A را داشته باشیم،‌ سلول موردنظر، خطا را نمایش نمی‌دهد و سلول خالی داریم.

بر اساس رای ۰ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
EXCELJETSimplilearn
۱ دیدگاه برای «ویلوکاپ در اکسل چیست؟ – نحوه استفاده و کار با فرمول VLOOKUP»

با سلام
با تشکر و قدردانی یه سوالی داشتم: آیا در گزارش گیری پیوت تیبل این امکان وجود دارد که:
یک ستون داریم نام اشخاص که هر کدام مثلا 20 انتخاب دارند. مثلا غذا- در یک بازه زمانی مشخص میخوام در ستون جلوی هر شخص فقط نام آن غذائی باشد که بیشترین دفعه مورد استفاده قرار گرفته استو نام و تعداد غذای دیگه مشاهده نشه و دقیقا این اسم جلوی اسم شخص باشد. یعنی در سلول a1 اسم شخص- در b1 نام غذائی که بیشترین دفعه استفاده شده و در ستون c1 تعداد دفعات استفاده شده ممکنه این شخص در بازه زمانی از هر 20 نوع استفاده کرده باشد ولی میخوام فقط غذائی را بیاره که بیشترین مصرف را داشته است برای هر شخص. و فقط میخوام با پیوت تیبل باشه. با تشکر

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *