اگر از اکثر افرادی که ادعایی در اکسل دارند، جویا ­شوید که از اکسل چه می ­دانید، سریعا نام یک تابع را می­آورند، تابع وی لوکاپ (VLOOKUP)! این تابع از پرکاربردترین توابع اکسل است که خیلی بعید است که با اکسل کار کنید، اما این تابع به دردتان نخورد. پس با این مقاله همراه شوید تا یک بار برای همیشه نحوه استفاده از این تابع را یاد بگیرید. ضمنا من فیلم یکی از کلاس­هایم را هم در آپارات آپلود کرده ­ام که بعد از خواندن این مقاله، آن ویدیو را هم مشاهده بفرمائید تا دیگر نکته ­ی مبهمی برایتان باقی نماند. (توصیه می­کنم اول این مقاله را بخوانید تا کاملا با اصول استفاده از این تابع آشنا شوید سپس ویدیو را ببینید که مثال دیگری از تابع VLOOKUP است).

من علیرضا مقربی، مدرس اکسل در سازمان­ها و شرکت­های خصوصی و دولتی هستم. دوره ­های متعددی هم در موسسات آموزشی نظیر مجتمع فنی تهران، مجتمع فنی نوین پارسیان، دانشگاه علمی کاربردی، دانشگاه خوارزمی، مرکز آموزش مخابرات ایران و ... برگزار کرده ­ام. توضیحاتی که در ادامه آمده، ماحصل تجربه­ ی دست کم پنج ساله ­ی تدریس این تابع برای دانشجویان است که امیدوارم مورد توجه شما قرار گیرد.

در صورت درخواست برگزاری کلاس خصوصی اکسل و برگزاری دوره­ی عمومی در موسسه یا شرکتتان با شماره­های زیر تماس بگیرید:

09392218434-09211895199

تابع VLOOKUP دقیقا چه کاری انجام می­دهد؟

اجازه بدهید با یک مثال نحوه ­ی عملکرد این تابع را توضیح دهم. شکل زیر را در نظر بگیرید:

آموزش فارسی تابع Vlookup در اکسل وی لوکاپ تدریس خصوصی اکسل مدر اکسل 

صورت مسئله این است: لیستی از کالاهای مختلف داریم که هر کدام یک قیمت واحد دارند ( محدوده­ ی H1 تا I8 در شیت بالا). حال می­خواهیم رکوردهای فروش کالاها را در ستون­ های A تا D وارد کنیم. هدف این است که وقتی نام کالایی وارد شد ( در ستون نام کالا یعنی ستون A)، قیمت واحد از جدول قیمت­ ها بصورت اتوماتیک در ستون قیمت واحد (یعنی ستون B)، درج شود. در مثال بالا، مثلا وقتی نام کالا، کشک انتخاب شد، قیمت آن یعنی 1995 به صورت اتوماتیک در ستون قیمت واحد درج شود. این کار به سادگی با تابع VLOOKUP قابل انجام است.

نحوه­ ی استفاده از تابع VLOOKUP

وی­-لوکاپ همچون دیگر توابع اکسل، یک اصطلاحا سینتکس (Syntax) دارد! به عمد این اصطلاح را به ­کار بردم تا اگر قبلا به گوشتان نخورده، با آن آشنا شوید. به بیان ساده، سینتکس نوع ورودی­ هایی که تابع (هر تابعی و نه فقط وی­-لوکاپ) می ­گیرد را مشخص می ­کند. ضمنا علاوه بر آن، ترتیب گرفتن ورودی­ ها را هم مشخص می­کند. منظور از ورودی، مقادیر ثابت، آدرس سلول یا آدرس محدوده­ هایی است که طبق سینتکس، به تابع می­دهیم تا با توجه به آن، محاسبات مد نظر ما را انجام دهد. شما برای استفاده از توابع اکسل، احتیاجی به حفظ کردن سینتکس آنها ندارید، چون خود اکسل موقعی که نام آن تابع را در سلولی وارد می ­کنید، سینتکس آن را به شما نشان می­دهد. به عنوان نمونه، همین مثال خودمان را در نظر بگیرید، سینتکس وی­-لوکاپ موقع ورود آن در سلول C4 توسط یک تول­تیپ (Tooltip) نمایش داده شده ­است:

آموزش فارسی تابع Vlookup وی لوکاپ 

 عبارتی در کادر مستطیلی باریک زیر نام تابع می­بینید، همان سینتکس تابع است. اما برویم سراغ توضیح آن:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

ورودی LOOKUP_VALUE

این ورودی، مقداری است که شما بر مبنای آن می­خواهید جستجوی خود را انجام دهید. در واقع از خود این سوال را بپرسید: دنبال چه هستم؟ در این مثال ما دنبال نام کالایی که در سلول A4 وارد شده هستیم، پس مقدار این ورودی را A4 (یعنی همان کشک) قرار می­دهیم. در واقع ما به دنبال کشک می­گردیم!

ورودی Table_Array

این ورودی، محدوده یا جدولی است که می­خواهیم جستجو در آن انجام شود. در این مثال ما می­خواهیم در محدوده­ی H1 تا I8 جستجو انجام شود. یک نکته­ی حیاتی در این قسمت وجود دارد؛ تابع VLOOKUP تنها در ستون اول محدوده­ی Table_Array جستجو انجام می­دهد. در این مثال، ستون اول محدوده می­شود H1 تا H8، که در آن، نام کالاها وارد شده است. لطفا به این نکته شدیدا توجه بفرمائید، VLOOKUP تنها در ستون اول محدوده­ی Table_Array جستجو انجام می­دهد؛ VLOOKUP تنها در ستون اول محدوده­ی Table_Array جستجو انجام می­دهد؛ VLOOKUP تنها در ستون اول محدوده­ی Table_Array جستجو انجام می­دهد؛ VLOOKUP تنها در ستون اول محدوده­ی Table_Array جستجو انجام می­دهد؛ آیا باز تکرار کنم یا کافی است؟؟؟

معنی این جمله این است که اگر ستون قیمت واحد، اولین ستون بود و در مقابل آن نام کالا قرار داشت (جای دو ستون Table_Array جابه­جا بود) قطعا وی­لوکاپ مقدار #N/A را بر می­گرداند (وقتی وی­لوکاپ مقدار مورد جستجوی شما یعنی همان Lookup_Value را در Table_Array پیدا کند خطای #N/A برمی­گرداند به این معنی که پیدا نکردم! یا همان Not Available ). به بیان دقیق­تر، وی­لوکاپ در ستون اول محدوده­ای که به عنوان Table_Array داده­اید، جستجو انجام می­دهد. این را به این دلیل گفتم که در اینجا راست یا چپ بودن ستون ملاک ستون اول بودن نیست، چون اگر شیت من راست به چپ بود، ستون اول یعنی همان ستون نام کالا در سمت راست و ستون قیمت در سمت چپ قرار می­گرفت.

خلاصه اینکه مقدار Table_Array در این مثال می­شود H1:I8 . به دلیل اینکه می­خواهیم با درگ کردن یا همان فیل کردن (Fill) سلول­های بالایی و پایینی سلول C4 را هم پر کنیم، بهتر این است آدرس محدوده را مطلق کنیم (اگر با مفهوم آدرس دهی مطلق و نسبی آشنایی ندارید، بعدا مفصلا راجع به آن توضیح خواهم داد). پس به جای درج H1:I8 آنرا به­صورت $H$1:$I$8 درج می­کنیم. یک نکته اضافه­تر اینکه در این مثال دادن آدرس محدوده به­صورت H$1:I$8 هم کفایت می­کرد، چون حرکت ما (همان فیل کردن ما) تنها به سمت بالا و پایین بوده و بین ستون­ها حرکتی نداریم که نگران تغییر ستون باشیم.

اگر متوجه این قسمت نشده­ اید به دلیل آن است که احتمالا با آدرس ­دهی مطلق و نسبی و تاثیر علامت $ یا دالِر ساین (Dollar Sign) آشنایی ندارید. یا به منابع دیگر مراجعه کنید یا منتظر آموزش ­های بعدی باشید. در اینجا فقط بدانید که محدوده را باید به صورت H$1:I$8 و نه H1:I8 وارد کنیم تا بتوانیم با استفاده از امکان فیل کردن یا همان درگ کردن به سمت بالا و پایین، بقیه­ ی قیمت­ها را هم به صورت اتوماتیک درج کنیم.

ورودی col_index_num

این ورودی، شماره ستونی از محدوده ­ای که به عنوان Table_Array به تابع داده ­ایم است که مقدار آن را می­خواهیم (چند بار این جمله را بخوانید و بعد ادامه دهید!). در این مثال، محدوده ­ی Table_Array را H$1:I$8 داده­ایم. این محدوده دارای دو ستون است؛ ستون اول نام کالا و ستون دوم قیمت واحد، تابع VLOOKUP شروع به جستجوی مقدار کشک در ستون اول محدوده­ ی Table_Array می­کند، نهایتا در یک سطری آن را می ­یابد، حال ما مقدار کدام ستون در همان سطری که کشک پیدا شده ­است را می­ خواهیم؟ در اینجا مقدار نظیر کشک در ستون دوم یعنی قیمت واحد آن را می­ خواهیم، پس ورودی col_index_num می ­شود 2. تصویر زیر را ببینید تا این موضوع برایتان بهتر روشن شود:

تدریس خصوصی اکسل مدرس اکسل 

ورودی range_lookup

این ورودی دو مقدار True یا False را می ­پذیرد. در 90 درصد کاربردها ما مقدار False را وارد می­ کنیم، به این معنی که از وی­لوکاپ می­ خواهیم، اگر مقدار مورد نظر ما یعنی کشک را پیدا نکرد، آن را به ما اطلاع دهد (از طریق برگرداندن خطای #N/A ). شما هم فعلا همین مقدار وارد کنید تا بعد در مثال­ های دیگری، در خصوص اینکه چه مواقعی مقدار True را وارد می­کنیم توضیح دهم. دقت بفرمائید که در سینتکس تابع وی­لوکاپ، ورودی range_lookup داخل یک براکت قرار داده شده­ است. به طور کلی و در همه ­ی توابع، ورودی­ هایی که در سینتکس با براکت مشخص شده ­اند اختیاری یا Optional هستند، یعنی بسته به کاربرد می ­توانیم آن را وارد کنیم و یا مختاریم چیزی وارد نکنیم. در اینجا اگر به عنوان ورودی آخر، چیزی وارد نکنید، خود وی­لوکاپ مقدار ورودی آخر را True در نظر می­گیرد که معمولا مطلوب ما نیست. فقط کوتاه اشاره کنم که دادن ورودی True در معمولا در مواقعی است که Lookup_Value مقداری عددی است و ما می­خواهیم که اگر وی­لوکاپ مقدار مورد نظر ما را پیدا نکرد، عددی نزدیک به آن را به ما برگرداند (کلی باید سر این عبارت کوتاه به شما توضیح دهم که منظور از مقداری نزدیک به عدد مورد نظر ما چیست؟ که باشد به وقتش و در پستی دیگر!) که در این حالت ستون اول Table_Array حتما باید مرتب شده (Sort) باشد.

جمع­ بندی مطالب بالا در تصویر زیر آمده است:

تدریس خصوصی اکسل مدرس اکسل

 

ضمن تشکر از توجه شما، امیدوارم توضیحات حقیر برای شما مفید بوده باشد. لطفا هر نظری در خصوص مطلب بالا و نحوه­ ی تدریس بنده دارید از طریق شماره 09392218434 با بنده درمیان بگذارید. برای مشاهده ویدیوی آموزشی تابع VLOOKUP بنده روی آپارات اینجا کلیلک کنید.

با تشکر