تابع VLOOKUP و نحوه رفع محدودیت این تابع
تابع VLOOKUP پرکاربردترین تابع اکسلی جستجوگر می باشد که به شما امکان می‌دهد برای مقدار داده‌ای خاص در یک ستون، مقدار متناظر در ستون دیگری را بازیابی کنید.
و با جستجو در ستون اول جدول داده ها ، مقدار مشخصی را یافته و سپس مقدار متناظر با آن را در ستون های بعدی پیدا کرده و به ما نمایش می دهد.در اینجا مراحل استفاده از تابع ویلوکاپ در اکسل را برای شما شرح می‌دهم:
تابع VLOOKUP:
= VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
اجزاء تابع VLOOKUP:
lookup_value
داده ای که میخواهید در محدوده جدول به دنبال آن بگردید. دقت کنید که این داده باید در اولین ستون محدوده انتخابی مرحله بعد وجود داشته باشد.
table_array
جدولی که میخواهید درون آن کد یا عبارت مورد جستجو را پیدا کنید. عبارت مورد جستجو باید اولین ستون این جدول باشد و ستون نتیجه نیز در محدوده جدول انتخاب شده موجود باشد.
col_index_num
شماره ستون در جدول که میخواهید مقدار آن را به ما نمایش دهد.
range_lookup
این قسمت تابع اختیاری می باشد. در صورتی که این قسمت را وارد نکنید مقدار پیش فرض (TRUE) در نظر گرفته خواهد شد.
اگر مقدار TRUE یا 1 را وارد کنید و عبارت مورد جستجو یافت نشد نزدیکترین عدد یا عبارت را نشان خواهد داد. در صورتی که جدول مرتب (سورت) نباشد حتی اگر عبارت جستجو وجود داشته باشد اولین نتیجه مشابه را نشان خواهد داد.
اگر مقدار FALSE یا 0 را وارد کنید دقیقا همان عبارت مورد جستجو را پیدا می کند و در صورتی که نتیجه ای در برنداشت خطای #N/A را نشان می دهد.
رفع محدودیت تابع Vlookup:
در اینکه تابع VLOOKUP یکی از قوی ترین و مفیدترین فرمول هاست شکی نیست. اما این تابع در بحث آموزش اکسل دارای یک اشکال اساسی است.
شما نمی توانید توسط این تابع عملیات جستجو به سمت راست را انجام دهید. (اگر جهت صفحه فارسی باشد جستجو به سمت راست و اگر انگلیسی باشد جستجو به سمت چپ امکان پذیر نیست). به طور مثال اگر ستون نتیجه قبل از ستون lookup_value باشد این تابع نمیتواند اینکار را انجام دهد.
راه حل اول که شما در مواجهه با این مشکل میتوانید انجام دهید جابجایی ستونی که lookup_value درون آن وجود دارد به ابتدای محدوده جدول انتخابی است اما این راه حل نیز چندان مناسب نیست و در مواردی که ما قصد این جابجایی را نداریم ما را با مشکل روبرو می کند.
راه حل دوم استفاده از ترکیب تابع index و match می باشد که البته بسیار راه حل مناسبی بوده و حتی می تواند جایگزین مناسبی برای Vlookup باشد آموزش این دوتابع را در آینده در سایت قرار خواهیم داد.
راه حل سوم که ما میخواهیم در اینجا ارائه دهیم شامل کنار گذاشتن تابع Vlookup و استفاده از تابع دیگر نیست بلکه شما می توانید با ترکیب تابع Vlookup با تابع Choose به طور کلی محدودیت مذکور را از بین ببرید و همچنان طرفدار این تابع پرکاربرد باقی بمانید.
روش کار:
با استفاده از تابع Choose به صورت آرایه ای می توانید یک جدول را در حافظه تابع ذخیره کنید.
این جدول می تواند شامل دو ستون باشد که باید طوری این کار را انجام دهیم که ستون اول جدول ستونی که کد جستجوی ما درون آن قرار دارد باشد و ستون دوم ستون نتیجه ما باشد سپس ما نتیجه این تابع که یک جدول شامل دو ستون می باشد را در قسمت Table_array قرار میدهیم و به این صورت محدودیت تابع Vlookup به طور کلی از بین می رود.
=choose({1,2},lookup column,result column)
در فرمول فوق بجای lookup column ستونی که کد مورد جستجو درون آن قرار دارد را انتخاب می کنید و بجای result column ستونی که نتیجه درون آن قرار دارد را انتخاب می کنید.
نکته: اگر تابع فوق را به تنهایی بنویسید با توجه به اینکه نتیجه آن یک آرایه (جدول) شامل دو ستون می باشد در یک سلول قابل نمایش نخواهد بود و نتیجه خطا مشاهده خواهید کرد اما وقتی آن را درون Vlookup قرار می دهید نتیجه درستی به ما خواهد داد.
پس از این کار ما یک جدول دو ستونه در حافظه تابع Choose داریم و کافیست این تابع را درون تابع vlookup قرار دهیم.
=vlookup(lookup_value,choose({1,2},lookup column,result column),2,0)
در نتیجه ما هیچ محدودیتی برای اینکه ستون مورد جستجو قبل از ستون نتیجه باشد را نخواهیم داشت.
امیدوارم توضیحات واضح بوده و از این آموزش لذت ببرید.
نظرات شما باعث دلگرمی ماست…
نکات مهم تابع ویلوکاپ در اکسل
در استفاده از تابع ویلوکاپ در اکسل، برخی نکات مهم و قابل توجه وجود دارند. در ادامه، برخی از این نکات را بررسی می‌کنیم:
- تطابق دقیق: پارامتر range_lookup در تابع ویلوکاپ، که اختیاری است، به شما اجازه می‌دهد تا بررسی تقریبی یا تطابق دقیق را بر روی داده‌ها انجام دهید. با استفاده از FALSE، شما تطابق دقیق را فعال می‌کنید، به این معنی که مقدار lookup_value باید دقیقاً با مقادیر در ستون جستجو شده همخوانی داشته باشد.
- ترتیب ستون‌ها: برای استفاده صحیح از تابع ویلوکاپ، توجه کنید که ستونی که مقادیر را در آن جستجو می‌کنید (ستون جستجو) باید در ستون‌های اول محدوده داده‌ها (table_array) قرار گیرد.
- محدوده داده‌ها: محدوده داده‌ها باید حاوی ستون‌هایی باشد که میخواهید در آن‌ها جستجو کنید و ستونی که مقدار متناظر را برای آن میخواهید بازیابی کنید. معمولاً این محدوده را با استفاده از محدوده نام‌گذاری شده یا اشاره مستقیم به محدوده داده‌ها مشخص می‌کنید.
- نتایج متناظر: تابع ویلوکاپ تنها مقدار اولیه متناظر با lookup_value را بازیابی می‌کند. اگر بیش از یک نتیجه متناظر وجود داشته باشد، تنها اولین مقدار بازیابی شده را نشان می‌دهد. بنابراین، اگر بیش از یک نتیجه متناظر وجود داشته باشد، ممکن است نتیجه‌ای نامطلوب به دست آید.
در استفاده از تابع ویلوکاپ در اکسل، برخی اشتباهات متداول وجود دارد که باید به آن‌ها توجه کنید. در ادامه، برخی از این اشتباهات را بررسی می‌کنیم:
- ترتیب پارامترها: در استفاده از تابع ویلوکاپ، باید ترتیب صحیح پارامترها را رعایت کنید. ابتدا lookup_value باید قرار گیرد، سپس table_array، col_index_num و در نهایت range_lookup. رعایت نکردن ترتیب صحیح ممکن است باعث خطا در نمایش نتایج شود.
- نام‌گذاری محدوده‌ها: در صورت استفاده از نام‌گذاری محدوده‌ها، مطمئن شوید که نام‌ها صحیح استفاده شده‌اند و با نام‌های تعریف شده مطابقت دارند. اگر نام‌گذاری محدوده‌ها اشتباه باشد، خطاها در نمایش نتایج به وجود خواهد آمد.
- ارجاع به محدوده‌های صحیح: مطمئن شوید که محدوده‌ها که به عنوان table_array در تابع ویلوکاپ استفاده می‌شوند، حاوی تمام داده‌های مورد نیاز شما است. در غیر این صورت، ممکن است نتیجه مورد انتظار را دریافت نکنید.
- استفاده از تطابق تقریبی: در صورت استفاده از تطابق تقریبی (approximate match) با تنظیم پارامتر range_lookup به TRUE، باید مطمئن شوید که داده‌ها به ترتیب صعودی در ستون جستجو قرار دارند. در غیر این صورت، نتایج نادرستی دریافت خواهید کرد.
- بررسی وجود مقدار جستجو شده: در صورتی که مقدار lookup_value در ستون جستجو یافت نشود، تابع ویلوکاپ مقدار خطا #N/A را برگردانده و نتیجه‌ای نمایش داده نخواهد داد .
- استفاده از ستون‌های اصلاح شده: در صورتی که ستون‌ها در محدوده داده‌ها اصلاح شده یا تغییر کرده‌اند، ممکن است تابع ویلوکاپ نتایج نادرستی به دست آورد. در این صورت، اطمینان حاصل کنید که محدوده داده‌ها بدون تغییر است و اصلاحات در ستون‌های مورد نیاز را اعمال کنید.
- استفاده از مقادیر متناوب: در صورتی که مقادیر جستجو و مقادیر در ستون جستجوی تابع ویلوکاپ از نوع متناوب (text-based) باشند، مطمئن شوید که هر دو جداول دارای یکسانی در مورد حروف بزرگ و کوچک هستند. استفاده از تطابق بزرگی/کوچکی (case-sensitive) ممکن است به نتایج نادرستی منجر شود.
- بررسی اندازه محدوده‌ها: در صورتی که محدوده‌های داده‌ها بسیار بزرگ باشند، ممکن است زمان اجرا و پاسخگویی تابع ویلوکاپ کاهش یابد. در چنین مواردی، بهینه‌سازی محدوده‌ها و استفاده از محدوده‌های کوچکتر می‌تواند مفید باشد.
- تطابق داده‌های عددی: در صورت استفاده از تطابق دقیق برای مقادیر عددی، مطمئن شوید که تمام مقادیر در ستون جستجو و lookup_value از نوع عددی هستند و همه اعداد دارای فرمت یکسانی هستند.
دیدگاهتان را بنویسید