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

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

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

عملکرد VLOOKUP در اکسل

VLOOKUP مخفف جستجوی عمودی (Vertical Lookup) است و همانطور که از نام آن مشخص است به شما کمک می‌کند داده‌ها را با جستجوی عمودی در جدول‌ها بیابید. هرچند ممکن است این تابع به نظر پیچیده بیاید اما بعد از مطالعه این مطلب استفاده از آن را به سادگی خواهید آموخت.

در تصویر زیر مثالی از کاربرد ویلوکاپ اکسل را می‌بینید که با استفاده از شماره درج شده در ستون A، نام شرکت از ستون B به عنوان نتیجه تابع نمایش داده شده است که در تصویر زیر، برای مثال، با وارد کردن عدد «3» نتیجه «hp» را می‌بینید.

جدولی از داده ها و نشان داده جایگاه آرگومان های تابع VLOOKUP که با فلش قرمز رنگ نمایش داده شده اند.
برای نمایش تصویر در ابعاد بزرگتر، روی تصویر کلیک کنید.

سینتکس تابع VLOOKUP در اکسل

در فرمول زیر، سینتکس تابع VLOOKUP در اکسل را می‌بینیم:

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

جستجو با تطبیق دقیق با استفاده از VLOOKUP در اکسل

با استفاده از تابع VLOOKUP می‌توانید به سادگی مقادیر را با تطبیق دقیق در میان داده‌ها جستجو کنید.

در تصویر زیر از این تابع برای یافتن معادل دقیق ID نوشته شده در سلول H5 در ستون سوم جدول استفاده کرده‌ایم.

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

در آرگومان دوم این فرمول آدرس جدول را می‌بینید که A2:F11   وارد شده است. در آرگومان سوم شماره ستونی را قرار داده‌ایم که قصد داریم نتیجه در آن ستون جستجو شود. در آرگومان آخر مقدار روی False قرار دادیم تا تابع VLOOKUP با تطبیق دقیق نتیجه را به ما برگرداند. در صورتی که تابع نتواند مقدار دقیق را به ما برگرداند خطای #N/A! نمایش داده می‌شود.

داده های در اکسل و نمایش مقدار جستجو و نتیجه جستجو در جدولی در سمت راست تصویر
برای نمایش تصویر در ابعاد بزرگتر، روی تصویر کلیک کنید.

جستجو در اکسل با تابع VLOOKUP می‌تواند با تطبیق تقریبی نیز انجام شود. در ادامه به بررسی این حالت می‌پردازیم.

جستجو با تطبیق تقریبی با استفاده از VLOOKUP در اکسل

به عنوان مثال در داده‌هایی که در جدول زیر آمده‌اند از تابع VLOOKUP استفاده می‌کنیم تا رم معادل هر قیمت لپ‌تاپ را بیابیم. لپ‌تاپ مد نظر ما ۱۳۰۰ یورو قیمت دارد. این مقدار به طور دقیق در جدول وجود ندارد لذا با استفاده از تابع VLOOKUP با تطبیق تقریبی، می‌توانیم حداکثر رمی را بیابیم که با بودجه مدنظر ما تناسب دارد. ابتدا در جدول کناری ۱۳۰۰ را وارد می‌کنیم.

جدولی دو ستونه از داده ها در اکسل و جدول جستجو در سمت راست تصویر برای جستجو با تطبیق حدودی

سپس جدول داده‌ها را انتخاب کرده و در بخش «Sort and Filter» ستون اول را به طور صعودی مرتب می‌کنیم.

گزینه Filter در نوار ابزار بالایی اکسل

کلیک بر روی گزینه فیلتر در کنار هر سرستون دکمه‌ای برای نمایش منوی کشویی اضافه می‌شود. در منوی باز شده داده‌ها را به صورت صعودی مرتب کرده و روی OK کلیک کنید.

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

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

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

با وارد کردن فرمول، نتیجه نمایش داده می‌شود. در این مثال تابع از سلول A2 شروع می‌کند به پایین می‌رود تا به اولین مقدار بزرگتر از ۱۳۰۰ برسد که در این جدول سلول A9 با مقدار ۱۳۳۹٫۶۹ یورو اولین عدد بزرگتر از ۱۳۰۰ است. سپس به یک سلول قبل می‌رود و مقدار نظیر این سلول را در ستون دوم به عنوان نتیجه برمی‌گرداند.

جدولی دو ستونه از داده ها و نمایش نتیجه تابع رVLOOKUP در اکسل
برای نمایش تصویر در ابعاد بزرگتر، روی تصویر کلیک کنید.

در بخش بعدی به بررسی استفاده از تابع VLOOKUP در اکسل در حالت چند شرطی می‌پردازیم.

استفاده از VLOOKUP در حالت چند شرطی

تابع VLOOKUP به طور پیش‌فرض، توانایی استفاده از چند شرط در جستجو را ندارد. هرچند با استفاده از یک ستون کمکی می‌توانید بخش‌های مختلف را به هم ارتباط دهید و از آن‌ها به عنوان چند شرط جستجو در VLOOKUP استفاده کنید. برای مثال فرض کنید به دنبال قیمت MacBook Pro Apple هستیم، در حالی که نام محصول و نام شرکت در دو ستون مختلف قرار دارند. از یک ستون کمکی استفاده می‌کنیم که محتوای این دو ستون را در خود داشته باشد. ابتدا روی ستون B (نام شرکت‌ها) کلیک راست می‌کنیم و روی «Insert» می‌زنیم. با این کار ستونی در سمت چپ اضافه می‌شود.

منوی کلیک راست روی یتون B اکسل و انتخاب INSERT

در سلول اول ستون جدید فرمول =C2&”-”&D2   (یا CONCATENATE(B15,"-",C15)  ) را وارد می‌کنیم. سپس با کشیدن مربع کوچک پایین سلول تا انتهای جدول، در باقی سلول‌ها فرمول تکرار می‌شود.

جدول از داده ها در اکسل و اصافه کردن یک ستون برای ترکیب دو ستون C و D
برای نمایش تصویر در ابعاد بزرگتر، روی تصویر کلیک کنید.

حال با وارد کردن فرمول =VLOOKUP(B15&”-”&C15,B1:G11,5,FALSE)   می‌توانیم نام محصول به همراه نام شرکت جستجو کنیم.

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

نتیجه جستجو را در جدول می‌بینید. در آرگومان اول تابع VLOOKUP در واقع مقدار دو ستون C و D با یکدیگر ترکیب شده و در ستون B قرار گرفتند. دقت کنید اضافه کردن ستون در جدول جستجو، بعد از وارد کردن تابع VLOOKUP، در عملکرد تابع مشکل ایجاد می‌کند، لذا ویرایش‌های لازم را قبل از نوشتن فرمول تابع در جدول جستجو ایجاد کنید.

جدولی از داده ها و نمایش نتیجه جستجوی شرطی با تابع VLOOKUP در اکسل
برای نمایش تصویر در ابعاد بزرگتر، روی تصویر کلیک کنید.

خطاهای رایج در نتیجه VLOOKUP

در نتیجه تابع VLOOKUP ممکن است خطاهای مختلفی رخ دهد که به خطا در نوشتن فرمول یا فرمت داده‌ها مرتبط است. در لیست زیر به این خطاها اشاره کرده‌ایم:

  • #N/A!   این خطا وقتی رخ می‌دهد که تابع نتواند مقدار جستجویی را پیدا کند که در آرگومان اول ذکر شده است.
  • #REF!   وقتی رخ می‌دهد که که آرگومان سوم وجود نداشته باشد یعنی یعنی شماره ستونی که برای جستجو انتخاب کردیم، در جدول موجود نباشد.
  • #VALUE!   این خطا در دو حالت اتفاق می‌افتد، وقتی آرگومان سوم کمتر از یک باشد یا به عنوان یک مقدار عددی تشخیص داده نشود، یا آرگومان چهارم یکی از دو مقدار منطقی True یا False را نداشته باشد.

محدودیت‌های VLOOKUP در اکسل

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

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

با وجود همه این محدودیت‌ها تابع ویلوکاپ همچنان از توابع کاربردی اکسل است که برای اکثر پروژه‌های مدیریت داده کاربرد دارد و تسلط بر آن برای تمامی کاربران اکسل ضروری به نظر می‌آید.

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

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