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

مشکل تابع VLOOKUP در پیدا کردن مقادیر تکراری در اکسل – جستجوی مقادیر تکراری در اکسل

خانه » آموزش اکسل » حل محدودیت تابع VLOOKUP در مقادیر جستجوی تکراری

سلام همراه گرامی

احتمالا شما هم مثل خیلی از کاربرای اکسل زمانیکه جدولی شامل داده های تکراری دارید و میخواهید از تابع Vlookup برای یافتن نتایج استفاده کنید، با مشکل مواجه شدید. بله؛ مشکل اینجاست که وقتی یک عبارت جستجو به تابع Vlookup میدیم و در ازای اون نتیجه متناظرش رو میخوایم، تابع Vlookup فقط اولین نتیجه رو برای برمیگردونه. تابع VLOOKUP در جستجوی مقادیر تکراری درست عمل نمیکنه.

مثلاً فرض کنید یک شرکت پخش هستیم و مشتریان ما فروشگاه هایی هستند که به تعداد دفعات مختلف ازمون خرید کردن و میزان خریدشون رو در جدول زیر ثبت کردیم. حالا میخوایم در جدول کناری نام هر فروشگاه رو که وارد میکنیم، تمام خربدهای اون (توجه کنید تمام خرید ها نه اولین خرید) مقابلش لیست بشن. خوب این چیزی که میخوایم مشابه کارکرد Vlookup هستش. اما بیاید با هم فرمول Vlookup رو براش بنویسیم.

در عکس بالا فرمول با تابع ویلوکاپ رو میبینید که اگر Enter رو بزنیم، نتیجه فرمول میشه 1200000؛ در نگاه اول میگیم اوکی شد اما وقتی به سمت پایین فرمول رو تعمیم میدیم تا خریدهای دیگه فروشگاه کوروش رو لیست کنیم، میبینیم که همون 1200000 تکرار میشه و اینجوری میشه که متوجه میشیم تابع ویلوکاپ در تشخیص نتایج بعدی مربوط به یک عبارت جستجوی تکراری عمل نمیکنه. (عکس زیر)

برای حل این مشکل باید از یک ستون کمکی و ترکیب تابع های Index و Small استفاده کنیم.

ایجاد سلول کمکی با استفاده از تابع IF

ابتدا در کنار ستون میزان خرید یک ستون کمکی ایجاد میکنیم. میخواهیم اگر مقدار جستجوی ما (سلول E3) برابر با هر سلول ستون B بود، شماره ردیف آن سلول در ستون کمکی درج شود و اگر برابر نبود سلول کمکی آن خالی بماند (چرا؟! در ادامه میفهمیم). مثلا در عکس پایین عبارت جستجو کوروش است. پس باید در ستون D، سلول های D5، D3 و D9 به ترتیب برابر 1، 3 و 7 باشند و بقیه سلول ها خالی. به این نحوه ما داریم سلول های موردنظرمان را نشانه گذاری میکنیم. احتمالاً میپرسید شماره ردیف D3 که 3 است پس چرا باید 1 قرار گیرد؟ چون جدول از ردیف 3 شروع شده ما از شماره ردیف ها 2 تا کم میکنیم. پس به صورت زیر با استفاده از تابع IF در اکسل فرمول نویسی میکنیم:

IF($E$3=B3,ROW()-2,””)

در فرمول زیر تابع ROW بدون آرگومان است. این تابع زمانیکه آرگومانی وارد نشود، سطری که در آن قرار گرفته است را برمیگرداند.

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

تشریح فرمول اصلی (ترکیب INDEX و SMALL)

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

=INDEX($C$3:$C$10,SMALL($D$3:$D$10,ROW()-2)

در فرمول بالا با استفاده از تابع INDEX به اکسل گفتیم برو داخل سلول های C3 تا C10 بگرد و اون ردیفی که تابع SMALL میگه رو برگردون. در تابع SMALL هم گفتیم برو از بین سلول های D3 تا D10 که همون سلول های کمکی ما بودن، ROW()-2 امین سلول کوچک رو پیدا کن. بعد هم این فرمول رو تا پایین (اینجا تا سلول F5) تعمیم دادیم.

بیاید با هم نحوه عملکرد فرمول در اولین سلول که مربوط به اولین خرید فروشگاه کوروش هست رو بررسی کنیم.

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

حالا اگر همین فرمول رو برای سلول های پایینتر (F4، F5 و …) بررسی کنیم، میبینیم که به درستی نتیجه مطلوب ما رو برمیگردونه.

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

ویدیوی آموزش حل محدودیت تابع Vlookup در مقادیر جستجوی تکراری