ترکیب توابع با تابع Vlookup در اکسل

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

1. مقایسه خروجی تابع Vlookup با یک عبارت مشخص

یکی از بیشترین مسائلی که از ترکیب توابع IF و Vlookup داریم، این هست که خروجی Vlookup رو با یک عبارت مشخص مقایسه میکنیم و خروجی بله/خیر یا غلط/درست  و … رو نمایش میدیم. ساختار کلی این مسئله بصورت زیر هست:

IF(VLOOKUP(…) = sample_value, TRUE, FALSE) 

مثال اول-مقایسه خروجی Vlookup با یک مقدار مشخص

 فرض کنید لیستی از محصولات به همراه موجودی اونها رو داریم. میخوایم محصولی رو انتخاب کنیم و اگر موجودی اون محصول صفر بود، بگه در انبار نیست.

=IF(VLOOKUP (E1; A1:B10; 2; 0) =0; “بله” ;  “خیر“ )

شکل ۱- مقایسه خروجی Vlookup با یک مقدار مشخص

مثال دوم- مقایسه خروجی  Vlookup با یک سلول دیگه

فرض کنید میخوایم میزان فروش محصول مورد نظر رو با بیشترین فروش (که در یک سلول دیگه هست) مقایسه کنیم:

=IF ( VLOOKUP(E1;A1:B10;2;0 ) = E9 ; “بله” ;  “خیر” )

شکل ۲- مقایسه خروجی Vlookup با یک سلول دیگه

 مثال سوم- جستجو در یک لیست کوچکتر-مقایسه دو لیست

فرض کنید میخوایم دو لیست رو با هم مقایسه کنیم. اگر داده های جدول در لیست دوم وجود داشت، کلمه “وجود دارد” رو نمایش بده. اگر هم وجود نداشت، کلمه “وجود ندارد” رو نشون بده.

=IF ( ISNA ( VLOOKUP(A2;$D$2:$D$4;1;0) ) ; “وجود دارد” ; “وجود ندارد” )

تابع Vlookup در صورتی که داده مورد نظر رو پیدا نکنه،#N/A هست. پس با تابع ISNA چک میکنیم که آیا خروجی تابع Vlookup خطا هست یا نه. اگر خطا بود، یعنی وجود نداشته پس عبارت “وجود ندارد” را نمایش خواهد داد.

شکل ۳- مقایسه دو لیست با استفاده از ترکیب توابع با تابع Vlookup

2.  ترکیب تابع Vlookup با IF برای اعمال محاسبات مختلف

میتونیم خروجی تابع Vlookup رو بررسی کنیم و یک سری محاسبات روش انجام بدیم.

فرض کنید میخوایم پورسانت فروشنده ها رو بر اساس میزان فروششون حساب کنیم. شرایط به این صورت است که هر نفر، فروش بیش از ۳۰۰ داشته باشد، بیست درصد و در غیر اینصورت ده درصد پورسانت دریافت خواهد کرد.

=IF(VLOOKUP(D2,A2:B9,2,0)>=300,0.2*VLOOKUP(D2,A2:B9,2,0),0.1*VLOOKUP(D2,A2:B9,2,0))

شکل ۴- انجام عملیات مختلف روی خروجی تابع Vlookup

3. ترکیب توابع با تابع Vlookup برای کنترل خطای #N/A

IF(ISNA(VLOOKUP(…)), “عبارت مورد نظر”, VLOOKUP(…)) 

اگر تابع Vlookup داده مورد جستجو رو پیدا نکنه، با خطای #N/A مواجه میشیم. یکی از راه های کنترل این خطا استفاده از تابع منطقی ISNA با IF هست.

=IF(ISNA(VLOOKUP(D2,A2:B9,2,0)),“موجود نیست”,VLOOKUP(D2,A2:B9,2,0))

شکل ۵- کنترل خطای تابع Vlookup

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

خروجی تابع ISNA، True یا False هست. اگر تابع Vlookup با خطا مواجه بشه، خروجی تابع IFNA(Vlookup(….) مقدار True خواهد بود و عبارت “موجود نیست” رو نمایش میده. در غیر اینصورت خروجی ISNA(Vlookup(…) مقدار False خواهد بود که نتیجه خود تابع Vlookup رو بر میگردونه. در اینجا چون “خرمی” در لیست وجود نداره، با خطا مواجه میشه و نتیجه “موجود نیست” نمایش داده میشه. به تصویر زیر دقت کنید.

این کار رو توابع دیگه هم انجام میدن. توابعی مثل IFNA، Iferror، Iserror و If(Iserror). جهت مطالعه بیشتر نحوه مدیریت خطا، مقاله مدیریت خطا در اکسل رو مطالعه کنید.

4. استفاده از Match و Index بجای Vlookup

خیلی از کاربران اکسل معتقدند که تابع Vlookup تنها راه جستجوی عمودی در اکسل نیست و میتونیم این کار رو با توابع index و Match هم انجام بدیم. به مثال زیر توجه کنید:

=IF (ISERROR (INDEX ( B2:B9, MATCH (D2,A2:A9,0),1)) ,“”, INDEX (B2:B9, MATCH(D2,A2:A9,0),1))

شکل ۶- استفاده از Index و Match بجای Vlookup (ترکیب توابع با تابع Vlookup)

همونطور که در مثال بالا می بینید، بجای استفاده از Vlookup از ترکیب Match و Index برای پیدا کردن فروشنده مورد نظر استفاده کردیم. این فرمول چطور کار میکنه؟

تابع Match به ما میگه که موردی که جستجو میکنیم چندمین سلول از محدوده مورد نظر هست. خروجی تابع Match (که عدد هست) به عنوان آرگومان شماره ردیف در تابع index استفاده میشه. اگر هم داده مورد نظر رو پیدا نکنه خروجی Match بصورت #N/A خواهد بود و ما برای مدیریت خطا از تابع Iserror استفاده کردیم.

به تصویر زیر دقت کنید و نحوه عملکرد ترکیب این دو تابع رو ببینید.

در این مقاله ترکیب توابع با تابع Vlookup رو دیدیم. اینکه چطور میتونیم این تابع رو با if ترکیب کنیم و نتایج متنوعی رو داشته باشیم. همونطور که قبلا گفتیم، یکی از راه های حرفه ای شدن در اکسل توانایی در ترکیب توابع و گرفتن خروجی های متنوع هست. برای آشنایی بیشتر با نحوه مدیریت خطا در اکسل، دیباگ کردن فرمول، آشنایی با تابع Index و Match مقالات مرتبط با این موضوعات رو در سایت مطالعه کنید.