مقاله اکسلنرم‌افزارهای مهندسی صنایع

آموزش جستجو عمودی(VLOOKUP) در اکسل

تابع VLOOKUP در نرم افزار Excel

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

 

تابع VLOOKUP در نرم افزار Excel

 

VLOOKUP یعنی Vertical lookup (جستجوی عمودی)!

این تابع یک داده را از اولین ستون یک جدول جستجو میکند و از ستون دلخواه جواب مربوطه را برمی‌گرداند.
Lookup-value: مقدار یا شرطی که قرار است جستجو شود. Table-array جدولی که lookup-value در آن جستجو میشود
Col-index-num: در این آرگومان باید عدد بنویسید. این عدد بیانگر شماره ستونی است که میخواهید جواب از این ستون انتخاب
شود.
Range-lookup: این آرگون دو گزینه دارد.
True-Approximate match: اگر این گزینه را انتخاب کنید تطبیق تقریبی انجام می‌شود یعنی در صورتی که lookup-value از اولین ستون جدول یافت نشد اکسل نزدیکترین حد پایین عدد مورد نظر را یافت میکند.
FALSE-Exact match: اگر این گزینه را انتخاب کنید تطبیق کامل انجام می‌شود یعنی در صورتی که lookup-value از اولین ستون جدول یافت نشد دیگر بحث تقریب در کار نیست و خطای NANot Available# رخ میدهد یعنی میگوید مقدار مورد نظر یافت
نشد.

 

 

از آنجا که VLOOKUP جزء پرکاربردترین توابع در اکسل است لذا دانستن نکات زیر قبل از کار با این تابع مهم است.

نکات‌مهم تابع VLOOKUP در اکسل

۱) VLOOKUP از اولین ستون جدول داده کار جستجو را انجام می‌دهد.
۲) VLOOKUP تکراری ها را پیدا نمیکند در صورت تکراری بودن اولین مقدار را نشان می‌دهد.
۳) VLOOKUP رو به عقب جستجو نمی‌کند. (نکته ۳ بسیار مهم است و اکثر افراد به آن دقت نمی‌کنند.)

غلط: چون جهت Sheet اکسل از چپ به راست و جهت Table از راست به چپ است

 

 

درست: چون جهت Sheet اکسل از چاپ باه راسات اسات و جهت Table نیز از چپ به راست است

 

آموزش مرتبط برای شما : آموزش اکسل مقدماتی تا پیشرفته کاملا رایگان

مثال برای تابع Vlookup در نرم افزار اکسل

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

میخواهیم در سلول E8 فرمولی بنویسیم که حقوق فردی را که نام او در سلول D8 نوشته میشود را محاسبه کند .فرمول محاسبه حقوق بصورت زیر است:

حق اولاد + (نرخ هر ساعت کار × ساعت کار)

برای این کار در سلول E8 تابع VLOOKUP را فرامیخوانیم. کادر این تابع بصورت زیر است.

در کادر LOOKUP_VALUE باید مورد جستوجو وارد شود. در این مثال بجای نوشتن مورد جستوجو ،آدرس سلول آن نوشته میشود. چون قرار است نام فردی که در سلول D8 نوشته شده است جستوجو شود ،بنابراین در این کادر آدرس سلول D8 را مینویسیم یا روی آن کلیک میکنیم.

در کادر TABLE_ARRAY جدولی که باید در آن به جستوجو بپردازیم را وارد کنیم. در اینجا سه جدول داریم. برای شروع جدول اول را بدون سربرگ (نام ستون) انتخاب میکنیم.

درکادر COL_INDEX_NUM شماره ستونی را که میخواهیم نمایش داده شود را باید بنویسیم. در این مثال و در جدول تعداد ساعات کاری افراد، میخواهیم ساعت کاری نام فردی که جستوجو شده، نمایش داده شود. ستونی که تعداد ساعت کار افراد در جدول نوشته شده، ستون ۲ است. بنابراین در این کادر عدد ۲ را مینویسیم.

در کادر RANGE_LOOKUP یک مقدار دلخواه باید وارد شود. اگر این کادر خالی بماند یا عدد ۱ قرار گیرد، در صورت موجود نبودن عین عبارت جستوجو در ستون اول جدول، نزدیکترین یا شبیهترین عدد یا عبارت با آن انتخاب میشود. اگر در این کادر عدد صفر قرار گیرد، در صورت موجود نبودن مورد جستوجو در محدوده انتخاب شده ،خطای عدم وجود (#N/A) نمایش داده میشود. در این مثال عدد صفر را قرار میدهیم.

پس از کلیک بر OK تصویر زیر را شاهد خواهیم بود.

چون در کادر D8 مقداری وجود ندارد، تا اینجای کار خطای #N/A نمایش داده میشود. تا اینجا فقط ساعت کار فرد را جستوجو کردهایم. اما ساعت کار باید در نرخ هر ساعت کار ضرب شود. برای ادامه نوشته فرمول دوباره روی سلول E8 کلیک کرده و درپایان فرمول علامت ضرب “*” را قرار میدهیم.

برای ادامه فرمول دوباره در ادامه همین فرمول، تابع VLOOKUP را با کلیک بر نام فرمول فرامی‌خوانیم.

مطلب مرتبط : تابع ضرب دو ماتریس و معرفی ماتریس واحد در اکسل

در ادامه آموزش تابع Vlookup در پنجره جدید تابع VLOOKUP همانند تصویر زیر ادامه فرمول را مینویسیم. در کادر مورد جستوجو (LOOKUP_VALUE) همان سلول ۸D را قرار داده و در جدول جستوجو، جدول نرخ ساعت کاری را بدون سربرگ آن (محدوده D2 تا E5) را وارد میکنیم. درکادر COL_INDEX_NUM شماره ستونی که نرخ ساعت کار در آن نوشته شده یعنی ستون دوم جدول را قرار میدهیم .در کادر RANGE_LOOKUP هم عدد صفر را وارد میکنیم.

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

=VLOOKUP(D8,A2:B5,2,0)*VLOOKUP(D8,D2:E5,2,2)

برای جمع مبلغ حق اولاد فرد جستوجو شده با فرمول بالا ،در ادامه فرمول فوق در نوار فرمول سلول E8 کلیک کرده و علامت “+” قرار میدهیم و دوباره تابع VLOOKUP را فرامی‌خوانیم.

در کادر تابع VLOOKUP جدید در قسمت مورد جستوجو، آدرس سلول D8، در کادر جدول جستوجو محدوده جدول حق اولاد(G2 تا H5) را قرار میدهیم. در کادر ستون نمایش عدد ۲ و در کادر آخر عدد صفر را قرار میدهیم.

حالا با کلیک بر گزینه OK فرمول محاسبه حقوق کامل شده. این فرمول در سلول E8 بصورت زیر است.

=VLOOKUP(D8,A2:B5,2,0)*VLOOKUP(D8,D2:E5,2,2)+VLOOKUP(D8,G2:H5,2,0)

حال اگر در سلول D8 نام هر فردی را بنویسید، در سلول E8 حقوق وی نمایش داده میشود. برای مثال اگر در این سلول نام “محمد” را بنویسیم ،در سلول E8 حقوق وی که از فرمول زیر محاسبه شده است، نمایش داده میشود.

=(۴۰×۴۵۰۰)+۳۵۰۰۰=۲۱۵۰۰۰

حال اگر در سلول D8 نامی نوشته شود که در جداول موجود نباشد، خطای عدم وجود (#N/A) نمایش داده میشود.

همچنین تابع HLOOKUP برای جستوجو در سطرها بکار میرود.

مثال دیگر برای آموزش تابع VLOOKUP : جدول حقوق و دستمزد

باتوجه به جدول زیر فرمولی در سلول B2 بنویسید و آن را کپی کنید تا اطلاعات بقیه فیلدها را بصورت خودکار نشان دهد.

مثال دیگر برای آموزش تابع VLOOKUP

=VLOOKUP($B$2,$D$2:$N$23,COUNTA($A$2:A3)0)

 

امیدواریم از این مطلب در سایت صنایع سافت که درباره آموزش تابع Vlookup بود، لذت برده باشید. هر سوال و نظری دارید برای ما کامنت بذارید، کمتر از یک روز پاسخ داده می‌شه 😉

۵/۵ - (۲ امتیاز)

احمد جعفری

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

مقالات مرتبط

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد.

دکمه بازگشت به بالا