آموزش تابع Vlookup

تابع Vlookup در اکسل

تابع vlookup اکسل یکی از مهمترین و اساسی ترین توابع در اکسل هست که باید به آن مسلط باشید. اگر شما هم حسابدار یا مدیر مالی باشید قطعا برای تهیه گزارشات مختلف که بوسیله اکسل تهیه می شود نیازمند آن شده اید. بعضی افراد تصور می کنند که تابع اکسل فقط برای پیدا کردن و یا جستجوی ساده می باشد که این کاملا اشتباه است. تابع Vlookup کاربردهای بسیار زیادی دارد که تصمیم داریم برخی را در این مقاله و برخی دیگر از خواص آن را در مقالات بعدی بررسی کنیم. اگر بخواهیم با شکل کلی این تابع آشنایی پیدا کنید به تصویر زیر دقت کنید:

تاحالا براتون پیش اومده یه جدولی داشته باشین که توش کلی اطلاعات باشه، و بخواهین یه چیز خاص رو از داخل جدول براتون نمایش بده؟

اینجوری که شما یه ورودی بنویسین و هر اطلاعاتی که مد نظرتونه رو راجع به ورودی بهتون نشون بده؟

به این مثال دقت کنین:

شما یک جدول دارین که داخلش مشخصاتی مثل: کد پرسنلی، نام و نام خانوادگی، سمت، تحصیلات و درآمد مربوط به 5 نفر وارد شده.

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

اما این زمانی ممکنه که اولا اطلاعات کم باشه، دوما داخل همین شیت (صفحه) باشیم.

اگه داخل همین صفحه باشیم و بخواهیم فقط با وارد کردن کد پرسنلی ، همه اطلاعات مربوط بهش رو بهمون نمایش بده باید چیکار کنیم؟

مثلا به این شکل:

و فقط کد پرسنلی رو تغییر بدیم تا بقیه اطلاعات هر کدوم سر جای خودش بشینه مثل شکل زیر:

حالا اگه اطلاعاتمون مثلا 100 ردیف یا 1000 ردیف باشه باز هم میشه دونه دونه چشمی نگاه کنیم و اطلاعات مورد نظرمون رو پیدا کنیم؟
قطعا نمیشه و خیلی از زمان با ارزش مون به خاطر یه خروجی گرفته میشه.

یا اگه داخل یک صفحه ی دیگه قرار باشه اطلاعات مربوط به یک یا چند کد پرسنلی وارد بشه اون وقت چیکار کنیم؟

همه ی اینا فقط با یاد گرفتن تابع Vlookup ممکنه!

کلا این تابع چیکار میکنه؟

به زبان خیلی ساده و خودمونی: یه ورودی بهش میدی (مثلا کد پرسنلی) و اطلاعات رو به روش رو میخوای(مثلا نام و نام خانوادگی)

البته یه نکته ای رو همین الان بگم : این تابع اطلاعاتی که جلوی ورودیش هست رو میتونه پیدا کنه و بیاره.

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

 تابع Vlookup

اصلا ببینیم Vlookup یعنی چی؟

این اسم تابع، مخفف Vertical Lookup  به معنی «جستجوی عمودی» هست. یعنی میره جستجوی عمودی (ستونی) انجام میده، انگار که دونه دونه سلول های داخل ستون مورد نظر رو جستجو میکنه و اطلاعاتی که مد نظر شماست رو براتون پیدا میکنه و میاره.

شکل کلی تابع به این شکل هست:

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

خود Vlookup که تابع ما هست و گفتیم که معنیش میشه تابع جستجوی عمودی (توی ستونها میگرده)

قسمت اول تابع lookup_value میگه مقدار معلومت چیه؟ یعنی طبق چه چیزی میخوای برات دنبال اطلاعات بگردم؟ (توی مثال ما کد پرسنلی مون معیاره ، یعنی بر اساس کد پرسنلی بره اطلاعات روبروش رو از ستونهای دیگه بیاره )

قسمت دوم تابع table_array ازمون میخواد محدوده رو انتخاب کنیم، یعنی اطلاعات ما داخل کدوم سلول ها هست؟ باید انتخاب کنیم دقیقا توی چه محدوده و منطقه ای از سلول ها دنبال اطلاعات بگرده

قسمت سوم تابع col_index_num هست که میگه حالا که محدوده ات رو انتخاب کردی، بگو کدوم ستون رو میخوای، و اینجا باید شماره ستون مورد نظرمون رو وارد کنیم که متوجه بشه اطلاعاتی که ازش میخوایم توی کدوم ستون از محدوده ی انتخابی ما که توی قسمت دوم یعنی table_array قرار داره

قسمت چهارم تابع [range_lookup ] هست، این که یه قسمت از تابع داخل براکت [] قرار میگیره یعنی وارد کردنش اختیاریه . این قسمت از ما دو تا سوال میکنه آیا اطلاعاتی رو ک از تابع میخوایم رو نسبی پیدا کنه؟ یا دقیقا همون چیزی که میخوایم رو بیاره؟

که زدن گزینه True برابر نسبی پیدا کردن اطلاعات هست True_Approximate match که به جای کلمه True میتونیم عدد یک 1 رو وارد کنیم

و با زدن گزینه False از تابع میخوایم مقدار دقیق اطلاعاتی که ازش خواستیم رو نشون بده False_Exact match که ایجا هم میتونیم به جای کلمه False از عدد صفر 0 استفاده کنیم.

خلاصه کلام میشه:

حالا توی یه نگاه اگه بخوایم به تابع Vlookup نگاه کنیم میتونیم با نقشه ذهنی راحت به خاطر بسپاریمش که این روش هم برای اولین بار برای اکسل در سایت راوی حساب ایجاد و در خدمت شما عزیزان قرار گرفته.

خب بریم سراغ مثال خودمون و قدم به قدم اطلاعات تابع رو پر کنیم:

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

میدونیم که برای شروع فرمول نویسی همیشه با نوشتن = داخل سلول مورد نظرمون ، عملیات فرمول نویسی شروع میشه؛

,Vlookup(lookup_value=

,Vlookup(C9=

در قدم اول برای انتخاب مقدار معلوم که معیار جستجوی ما هم هست سلول C9 را انتخاب میکنیم چون کد پرسنلی ما در سلول C9 وارد شده و ما با تغییر اطلاعات داخل این سلول ، کد پرسنلی مان را تغییر خواهیم داد.

بریم برای قدم دوم :

وقتی که قسمت اول تابع رو وارد کردیم، با زدن کلید , روی کیبورد به قسمت دوم تابع میریم؛

قسمت دوم انتخاب محدوده است، شروع جدول ما سلول A2 هست که کلمه کد پرسنلی داخلش نوشته شده، آخرین سلول محدوده ما هم سلول E7 هست که مبلغ 35.000.000 ریال داخلش نوشته شده :

VLOOKUP(lookup_value,table_array=

VLOOKUP(C9,A2:E7=

بعد از انتخاب کردن محدوده نوبت به قسمت سوم تابع میرسه؛شماره ستون!

ستون دوم از محدوده ی انتخابی ما مربوط به نام و نام خانوادگی است، پس پارامتر سوم تابع، عدد 2 یعنی دومین ستون محدوده ما خواهد بود:

VLOOKUP(lookup_value,table_array,col_index_num=

VLOOKUP(C9,A2:E7,2=

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

از اونجایی که ما اطلاعات دقیق میخواهیم نه نسبی، پس کلمه False و یا عدد صفر 0 رو وارد میکنیم:

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

VLOOKUP(C9,A2:E7,2,False)=

یا

VLOOKUP(C9,A2:E7,2,0)=

با توجه به مدل کار کرد فرمول Vlookup برای وارد کردن اطلاعات در قسمت سمت خواهیم داشت:

همه ی تابع مانند فرمول نوشته شده برای نام و نام خانوادگی هست با این تفاوت که سمت در ستون 3 محدوده ما هست ، پس خواهیم داشت:

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

VLOOKUP(C9,A2:E7,3,0)=

به همین ترتیب برای تحصیلات و درآمد هم همین روند را طی میکنیم.

امیدوارم نحوه با چگونگی کارکرد تابع Vlookup آشنا شده باشین. فقط با تمرین و تکرار هست که مهارتتون در اکسل افزایش پیدا میکنه.در مقالات بعدی به بررسی دقیق تر این تابع خواهیم پرداخت.

نویسنده: سهیل سوهانی

توصییه می شود : ساخت تایم شیت حرفه ای با اکسل