جستجوی موارد تکراری

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

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

• فرمول نویسی آرایه ای
• تابع INDEX
• تابع IF
• تابع SMALL

قبل از ادامه این آموزش، حتما مقالات بالا رو مطالعه کنید. حالا بریم سراغ شرح مسئله: در جدول شکل ۱ داده هایی داریم راجع به فروش محصولات مختلف در زمان های متفاوت و به خریداران مختلف. حالا میخواهیم نام هر محصول رو که انتخاب میکنیم لیست همه فروش های مربوط به این محصول رو ببینیم.

شکل ۱ – جستجوی داده تکراری – ساختار داده ها

گام اول: شناسایی مواردی که باید در گزارش بیایند

در گام اول، باید بتونیم جای محصولات مورد نظرمون رو پیدا کنیم. برای این کار از تابع If و بصورت آرایه ای استفاده میکنیم. توجه داشته باشید که فرمول نویسی آرایه ای با Ctrl+Shift+Enter ثبت میشه:

=IF(A2:A40=F2, ROW(A2:A40) ,””)

شکل ۲ – جستجوی داده تکراری – پیدا کردن مکان سلول های معادل شرط مورد نظر

خروجی این فرمول مکان (شماره ردیف) سلول هایی است که در اونها نوشته شده محصول ۱ (سلول F2). در واقع اگر شرط مورد نظر رو پیدا کنه، بجاش شماره ردیف اون سلول رو میذاره، و در غیر اینصورت خروجی خالی خواهد بود. حالا اگر این فرمول رو دیباگ کنیم، نتیجه بصورت زیر خواهد بود:

شکل ۳- جستجوی داده تکراری – نتیجه فرمول آرایه IF

در شکل ۳ مشاهده میکنیم که عدد ۲، ۱۳ و ۳۴ خروجی این فرمول هست. این اعداد نشان دهنده شماره ردیف سلول هایی است که نوشته شده محصول ۱ (شرط مورد نظر در سلول F2)

گام دوم: تخصیص شماره به ردیف های مورد نظر

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

=SMALL(IF(A2:A40=F2,ROW(A2:A40),””),ROW(a1))

این فرمول میاد از بین مجموعه اعدادی که خروجی IF بود یعنی {۳۴,۱۳,۲}، اعداد رو یکی یکی از کوچک به بزرگ بهمون میده. در واقع وقتی این فرمول رو مینویسیم و درگ میکنیم، خروجی بصورت شکل ۴ خواهد بود:

شکل ۴ – جستجوی داده تکراری – فراخوانی اعداد بدست آمده

گام سوم: فراخوانی داده های تکراری

حالا که شماره ردیف این داده ها رو داریم و شماره ستون داده مورد نظر هم مشخص هست، کافیه با استفاده از تابع Index داده مورد نظر رو فراخوانی کنیم. مثلا میخوایم تاریخ های مربوط به محصول ۱ رو پیدا کنیم. برای این کار خروجی فرمول بالا رو میذاریم توی Index:

=INDEX($A$1:$C$40,SMALL(IF($A$2:$A$40=$F$2,ROW($A$2:$A$40),””),ROW(A1)),2)

آرگومان اول، Array: کل دیتابیس مورد نظر ما هست که جستجو رو در اون انجام میدیم. آرگومان دوم، Row_num: خروجی تابع Small هست و شماره ردیف داده های مورد نظر ما رو نشون میده. آرگومان سوم، Column_num: شماره ستون داده مورد نظر، یعنی تاریخ رو در دیتابیس نمایش میده. پس با اینکار تاریخ های مربوط به محصول مورد نظر رو فراخوانی کردیم. حالا کافیه همون فرمول رو با Column_num شماره ۳ بنویسیم و اسم مشتری رو فراخوانی کنیم.

شکل ۵ – جستجوی داده تکراری – فراخوانی داده های تکراری

در نهایت برای اینکه فرمول رو برای ۱۰ ردیف درگ کنیم و با خطا مواجه نشیم، فرمول بالا رو با تابع IFERROR ترکیب میکنیم.

=IFERROR(INDEX($A$1:$C$40,SMALL(IF($A$2:$A$40=$F$2,ROW($A$2:$A$40),””),ROW(A1)),2),“”)

نتیجه فرمول آرایه ای بالا بصورت زیر خواهد بود: یکبار برای اینکه بصورت کلی عملکرد این فرمول رو ببینیم و درک بهتری داشته باشیم، به ویدئو زیر دقت کنید. مرحله به مرحله فرمول رو دیباگ میکنیم و نتیجه مشخص میشه. بصورت کلی، اول مکان (شماره ردیف) داده های معادل با شرط مورد نظر پیدا میشه و بعد با تابع small، یکی یکی فراخوانی میشه و در نهایت شماره ردیف بدست آمده و ستون مورد نظر در تابع Index داده مورد نظر رو فراخوانی میکنند.

  در این مقاله روش جستجو موارد تکراری با استفاده از فرمول نویسی آرایه ای و بدون سلول کمکی تشریح شد. یک روش دیگه هم برای جستجوی موارد تکراری هست، برای کسایی که از ورژن ۲۰۲۱ اکسل استفاده میکنند، که استفاده از تابع فیلتر در اکسل هست. پیشنهاد میکنیم برای یاد گرفتن این روش مقاله تابع Filter در اکسل ۲۰۲۱ و آفیس ۳۶۵ را مطالعه کنید.

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

دانلود فایل این آموزش

برای دانلود فایل این آموزش روی دکمه زیر کلیک کنید: