شرح تابع VLOOKUP

درجه اهمیت: بسیار کاربردی

تابع VLOOKUP یک ورودی موجود در اولین ستون از سمت چپ یک جدول یا آرایه را پیدا کرده و مقداری که در ستون مورد نظر دیگری که از نظر سطر یا ردیف با آن مقدار ورودی همتراز باشد را بازمی‌گرداند.  این تابع در اکسل در طبقه توابع Lookup/Reference قرار دارد.

نوشتار این تابع به صورت زیر است:

=VLOOKUP( دقت جستجو , شماره ستون مورد نظر , بازه جدول یا آرایه , مقدار جستجو )

ورودی های این تابع به شرح زیر هستند

مقدار مورد جستجو :   مقداری را که میخواهید جستجو کنید.

بازه جدول یا آرایه :   بازه ای از جدول یا آرایه ای که مقدار مورد جستجو در اولین ستون سمت چپ آن باشد (که میخواهیم در این بازه جستجو صورت گیرد).

شماره ستون مورد نظر :   شماره ستونی که مقدار مورد نظر ما به عنوان خروجی در آن قرار دارد (این ستون باید در بازه جدول یا آرایه موجود باشد).

[دقت جستجو]:   وارد کردن این مورد اختیاری است و تعین می‌کند که در صورتی که مقداری دقیقا مطابق با مقدار مورد جستجو یافت نشد چه مقداری بازگردانده شود. دقت جستجو باید به صورت TRUE یا FALSE وارد ‌شود (در صورت خالی گذاشتن این قسمت به طور پیشفرض TRUE در نظر گرفته می‌شود) که معنای هر کدام داخل این تابع در زیر توضیح داده شده است:

TRUE: اگر تابع نتواند دقیقا مقداری مطابق با مقدار مورد جستجو را پیدا کند، به عنوان خروجی نزدیکترین مقدار کمتر از  مقدار مورد جستجو را بازمی‌گرداند (برای این کار اولین ستون سمت چپ جدول یا آرایه باید از بالا به پایین افزایشی باشد).

FALSE: : اگر تابع نتواند دقیقا مقداری مطابق با مقدار مورد جستجو را پیدا کند، یک خطا بازگردانده می‌شود.

(برای درک بهتر این ورودی به مثال ۲ مراجعه کنید)

آنچه که تابع باز می گرداند

تابع VLOOKUP یک مقدار را بازمی‌گرداند (که می‌تواند هرگونه دیتا تایپی نظیر عددی، متنی یا زمانی باشد).

نکات

– عملکرد تابع VLOOKUP به  صورت عمودی است. برای استفاده از تابع LOOKUP به صورت افقی باید از تابع HLOOKUP استفاده کنید.

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

– به جای مقدار منطقی FALSE می‌توان صفر وارد کرد (که همان معنی FALSE را می‌دهد)، و به جای TRUE هر عددی غیر صفر می‌تواند قرار گیرد.

مثال های تابع VLOOKUP

مثال ۱

در مثال زیر نحوه کاربری تابع VLOOKUP را مشاهده می‌کنید.

در مثال بالا میخواهیم نمره زبان انگلیسیِ احمد مظفری را به عنوان خروجی داشته باشیم. برای این کار ابتدا به عنوان “مقدار مورد جستجو” نام وی یا سلول حاوی نام وی را وارد می‌کنیم. سپس به عنوان “بازه جدول یا آرایه” محدوده ای که می‌خواهیم مورد جستجو قرار گیرد را به شکلی که مشاهده می‌کنید وارد می‌کنیم. توجه داشته باشید که این محدوده را باید طوری تنظیم کنیم که ستون مورد نظر که قرار است از آن خروجی گرفته شود در آن وجود داشته باشد و همچنین ستونی که نام احمد مظفری (مقدار مورد جستجو) در آن وجو دارد اولین ستون از سمت چپ جدول باشد. بعد از آن به عنوان “شماره ستون مورد نظر” به ترتیب از چپ به راست ستون هایی که در بازه جدولی که در مرحله قبل تعین کردیم را می‌شماریم و شماره ستونی که خروجی مورد نظر ما در آن وجود دارد را وارد میکنیم. در مرحله آخر در قسمت “دقت جستجو” مقدار FALSE را وارد کردیم تا تابع دقیقا مقدار مورد نظر ما را پیدا کند و در صورت عدم وجود آن پیغام خطا دهد (که در مثال بالا مقدار مورد نظر یافت شده که برابر با ۹۴% است).

مثال ۲

در مثال بالا مقداری که دقیقا مطابق با مقدار مورد جستجو باشد وجود ندارد. در ردیف ۲ هیچ مقداری برای “دقت جستجو” وارد نشده و به صورت پیشفرض TRUE در نظر گرفته شده است که در نتیجه در قسمت خروجی میوه ای که نزدیکترین قیمت را به مقدار مورد جستجوی ما دارد و در عین حال ارزانتر است بازگردانده شده است. همانطور که مشاهده می‌کنید توت فرنگی به قیمت مورد نظر ما نزدیکتر است، ولی چون قیمت آن بالاتر است به جای آن طالبی بازگرداند شده است که هم نزدیک به قیمت ما باشد (نسبت به لیست ما) و هم ارزانتر باشد. دقت کنید که در این لیست مقادیر ستون A که بیانگر قیمت است به صورت افزایشی از بالا به پایین تنظیم شده تا هنگام استفاده از TRUE با پیغام خطا مواجه نشویم.

در ردیف ۴ مقدار منطقی FALSE وارد شده و از آنجاییکه هیچ مقداری دقیقا برابر با مقدار مورد جستجوی ما یافت نشده است با پیغام خطا مواجه شدیم.

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

برای یادگیری برنامه نویسی به زبان VBA به دوره «آموزش VBA در اکسل» مراجعه نمایید.

خطاهای معمول

#VALUE! :  ۱) شماره ستون مورد نظر کمتر از ۱ بوده و یا یک مقدار عددی نیست.   ۲) مقدار وارد شده برای قسمت دقت جستجو TRUE یا FALSE نبوده و یا یک مقدار عددی که بیانگر این مقادیر منطقی باشد نیست.

#REF! :   ۱) شماره ستون مورد نظر فراتر از بازه جدول یا آرایه است.   ۲) سلول هایی که آدرس آنها در تابع وارد شده در صفحه وجود ندارند که معمولا زمانی اتفاق می‌افتد که یک فرمول VLOOKUP از سلولی دیگری کپی شوند.

#N/A :   تابع VLOOKUP نمی‌تواند هیچ مقداری مطابق با مقدار مورد جستجو پیدا کند که این مشکل معمولا به مقدار دقت جستجو مربوط می‌شود. به این صورت که اگر مقدار برای دقت جستجو TRUE یا یک عدد غیر صفر وارد شود و یا به کلی جای آن خالی گذاشته شود و با این خطا مواجه شویم به این دلیل است که: ۱) کوچکترین مقدار موجود در ستون اول از سمت چپ جدول یا آرایه از مقدار مورد جستجوی ما بیشتر است. (همانطور که گفته شد در حالت TRUE تابع نزدیکترین میزان کوچکتر از مقدار مورد جستجو را بازمی‌گرداند)   ۲) اولین ستون از سمت چپ به صورت افزایشی از بالا به پایین تنظیم نشده است.

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