موضوعات داغ

جستجوی مقادیر تکراری با تابع VLOOKUP

جستجوی مقادیر تکراری با تابع VLOOKUP

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

اما اگر در لیست ما مقادیر مورد جستجو تکراری باشند چه باید کرد؟

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

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

  • استفاده از ستون کمکی و ساخت مقدار منحصر به فرد
  • استفاده از فرمول نویسی آرایه ای
  • استفاده از پاور کوئری

1) استفاده از ستون کمکی و ساخت مقدار منحصر به فرد

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

=COUNTIF( $A$2:$A$14 , A2)

=A2 & COUNTIF( $A$2:$A$14 , A2)

 

 

آموزش Power BI Desktop

این مجموعه در ده جلسه شما را با نرم افزار Power BI Desktop آشنا میکند. این نرم افزار برای ارتباط با پایگاه های داده و ارائه داده ها بصورت نمودار کاربرد دارد. با استفاده از Power BI Desktop شما میتوانید داده ها را از سایر نرم افزارها استخراج کرده و در جداول مورد نیاز خود دسته بندی کرده و آنها را بصورت نمودار و گراف و … نمایش دهید.

Download

2) استفاده از فرمول نویسی آرایه ای

در این روش نیازی به ایجاد ستون کمکی نیست و از ترکیب توابع زیر استفاده خواهیم کرد:

VLOOKUP

IF

SMALL

ROW

COLUMNS

در سلول E2 فرمول زیر را می نویسیم:

=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,””),COLUMNS($E$1:E1))),””)

بعد از نوشتن فرمول به جای Enter از دکمه های ترکیبی Ctrl + Shift + Enter استفاده می کنیم. بعد از انجام این کار در ابتدا و انتهای فرمول بالا علامت {} قرار داده می شود:

{=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,””),COLUMNS($E$1:E1))),””)}

اگر بخواهیم فرمول بالا را تشریح کنیم ابتدا پارامتر اول تابع IF بررسی می شود یعنی مقادیر محدوده A2:A14 با سلول D2 مقایسه خواهند شد:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

 

نتیجه پارامتر اول تابع IF به صورت مقدار منطقی TRUE و FALSE خواهد شد چرا که مقدار سلول D2 با تک تک سلول های محدوده A2:A14 مقایسه شده و مقادیری که برابر بوده اند TRUE و در غیر این صورت FALSE شده اند. در واقع قبل از نتیجه بالا فرمول به صور ت زیر خواهد بود:

{“مهران”=”مهران”;”مهران”=”هومن”;”مهران”=” کامران “;” مهران “=”سهراب”;”مهران”=”ساسان”;”مهران”=”مهرداد”;”مهران”=”مهران”;”مهران”=”فرخنده”;”مهران “=”پگاه”;”مهران”=”ساسان”;”مهران”=”مهران”;”مهران”=”کامران”;”مهران”=”سهراب”}

در مرحله بعد پارارمتر دوم تابع IF شماره سطر محدوده انتخاب شده را به صورت یک لیست برمی گرداند:

{2;3;4;5;6;7;8;9;10;11;12;13;14}

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

{1;2;3;4;5;6;7;8;9;10;11;12;13}

اما این مجموعه اعداد زمانی نمایش داده خواهند شد که شرط تابع IF برقرار باشد یعنی مقادیری که در این شرط برابر با TRUE هستند شماره سطر اکسل را برمی گردانند و در غیر این صورت مقدار خالی را برگشت می دهند. در نتیجه نتیجه تابع IF به صورت زیر خوهد شد:

{1;””;””;””;””;””;7;””;””;””;11;””;””}

در نتیجه مقادیر پارامتر اول تابع SMALL مقدار بدست آمده در بالا خواهند بود و برای اینکه بخواهیم اولین، دومین و سومین مقدار را بدست بیاوریم از تابع COLUMNS استفاده می کنیم تا بواسطه آن اولین، دومین و سومین مقدار کوچک لیست بالا را استخراج کنیم

در نتیجه مقدار استخراج شده به پارامتر دوم تابع INDEX سپرده می شود و بواسطه آن مقادیر مربوطه استخراج خواهد شد.

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

0 تا 100 ساخت فاکتور فروش با برنامه نویسی در اکسل

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

3) استفاده از پاور کوئری

یکی از ابزارهای کاربردی در اکسل افزونه پاور کوئری است که به واسطه آن می توانیم داده ها را به هر صورتی که مد نظرمان هست شکل دهی کنیم.

Download

Download

 

مدرس: یاسر طاهرخانی

 

برچسب ها

یاسر طاهرخانی

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

نوشته های مشابه

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

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

همچنین ببینید

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

بستن
بستن