۸ بازدید

آخرین به‌روزرسانی: ۲۴ بهمن ۱۴۰۲

زمان مطالعه: ۵ دقیقه

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

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

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

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

سینتکس تابع 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» ستون اول را به طور صعودی مرتب می‌کنیم.

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

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

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

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

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

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

در سلول اول ستون جدید فرمول =C2&”-”&D2

  (یا CONCATENATE(B15,”-“,C15)

 ) را وارد می‌کنیم. سپس با کشیدن مربع کوچک پایین سلول تا انتهای جدول، در باقی سلول‌ها فرمول تکرار می‌شود.

حال با وارد کردن فرمول =VLOOKUP(B15&”-”&C15,B1:G11,5,FALSE)

  می‌توانیم نام محصول به همراه نام شرکت جستجو کنیم.

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

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

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

• #N/A!

    این خطا وقتی رخ می‌دهد که تابع نتواند مقدار جستجویی را پیدا کند که در آرگومان اول ذکر شده است.

• #REF!

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

• #VALUE!

    این خطا در دو حالت اتفاق می‌افتد، وقتی آرگومان سوم کمتر از یک باشد یا به عنوان یک مقدار عددی تشخیص داده نشود، یا آرگومان چهارم یکی از دو مقدار منطقی True یا False را نداشته باشد.

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

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

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

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