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

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

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

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

از بین مسائل مربوط به جستجو در اکسل، یکی از مهم ترین و پرچالش ترین مسائل، جستجوی داده های تکراری هست. چون همونطور که میدونیم توابع جستجو مثل Vlookup همیشه به اولین مورد که برسه، همون رو به عنوان خروجی به ما میده و در واقع داده های تکراری رو نمایش نمیده.

در این مقاله میخوایم به حل این مسئله بپردازیم و با فرمول نویسی، داده های تکراری رو جستجو کنیم.

برای اینکه بتونیم این مسئله رو حل کنیم باید با چند تابع مهم و منطق فرمول نویسی آرایه ای آشنا باشیم:

حتما بخوانید:  استفاده آرایه ای از تابع Countifs

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

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

جستجوی داده تکراری - ساختار داده ها

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

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

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

=IF(A2:A40=F2ROW(A2:A40) ,””)

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

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

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

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

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

در شکل 3 مشاهده میکنیم که عدد 2، 13 و 34 خروجی این فرمول هست. این اعداد نشان دهنده شماره ردیف سلول هایی است که نوشته شده محصول 1 (شرط مورد نظر در سلول F2)

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

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

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

این فرمول میاد از بین مجموعه اعدادی که خروجی IF بود یعنی {34,13,2}، اعداد رو یکی یکی از کوچک به بزرگ بهمون میده. در واقع وقتی این فرمول رو مینویسیم و درگ میکنیم، خروجی بصورت شکل 4 خواهد بود:

فراخوانی اعداد بدست آمده

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

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

حالا که شماره ردیف این داده ها رو داریم و شماره ستون داده مورد نظر هم مشخص هست، کافیه با استفاده از تابع Index داده مورد نظر رو فراخوانی کنیم. مثلا میخوایم تاریخ های مربوط به محصول 1 رو پیدا کنیم. برای این کار خروجی فرمول بالا رو میذاریم توی 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 شماره 3 بنویسیم و اسم مشتری رو فراخوانی کنیم.

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

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

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

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

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

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

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

دیباگ فرمول

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

منبع

0 پاسخ

دیدگاه خود را ثبت کنید

تمایل دارید در گفتگوها شرکت کنید؟
در گفتگو ها شرکت کنید.

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

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