آغاز ثبت نام دوره آنلاین شروع حرفه ای اکسل (ظرفیت محدود)
سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

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

جستجو موارد تکراری
۵/۵ - (۱ امتیاز)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

شکل ۳- جستجوی داده تکراری – نتیجه فرمول آرایه 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)),۲)

آرگومان اول، 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)),۲),“”)

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

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

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

دیباگ فرمول

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

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

 

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

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

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

126

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

دیدگاه کاربران
  • Msoury ۸ اردیبهشت ۱۴۰۱ / ۴:۱۰ ق٫ظ

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

    • حسنا خاکزاد ۹ اردیبهشت ۱۴۰۱ / ۶:۳۴ ب٫ظ

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

  • علیرضا ۱ اردیبهشت ۱۴۰۱ / ۱۲:۳۰ ب٫ظ

    با سلام و تشکر از این آموزش.. اگر شروع این جدول از سلول دیگری غیر از A1 باشه مثلا A15 و یا سلولی در ستونهای دیگر… باز هم باید با تغییر محدوده جستجو به جواب برسه.. اما این اتفاق نمیافته و خطا میده…!! دلیل خاصی داره؟ با تشکر

    • حسنا خاکزاد ۱ اردیبهشت ۱۴۰۱ / ۴:۰۱ ب٫ظ

      درود بر شما
      بله چون شماره ردیف رو به عنوان خروجی نمایش میده باید اختلاف رو کم کنید
      مثلا اولین داده ای که پیدا میکنه، ردیف ۵۰ هست، د رحالیکه کل محدوده سرچ ۲۰ ردیفه. قاعدتا ردیف ۵۰ در محدوده ۲۰ ردیفه معن ینداره
      پس باید یا اختلاف از همم کم بشه
      یا محدوده index از ردیف ۱ حساب بشه

  • م م l ۲ فروردین ۱۴۰۱ / ۱۱:۰۹ ق٫ظ

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

    • سامان چراغی ۲ فروردین ۱۴۰۱ / ۱۱:۳۹ ب٫ظ

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

  • م م l ۲۰ اسفند ۱۴۰۰ / ۹:۵۶ ب٫ظ

    با سلام
    فرض کنید در ستون اول ۱۰۰ سوال نوشتیم
    در ستون های بعدی در سطر اول نام کلاس مثلا کلاس اول ، کلاس دوم ، کلاس سوم
    در زیر هر کلاس هر کدام از سوالات را که می خواهیم به ترتیب دلخواه شماره و ستاره می گذاریم
    می خواهیم وقتی یک کلاس را فراخوانی می کینم ، آن سوالات دلخواه برای آن کلاس مورد نظر به ترتیب زیر بیاید:
    ستاره دارها اول و بدون شماره بیایند
    شماره دار ها به ترتیب شماره ای که به انها دادیم همراه با همان شماره بیایند
    با تشکر

    • حسنا خاکزاد ۲۱ اسفند ۱۴۰۰ / ۹:۱۸ ق٫ظ

      درود بر شما
      باید از ترکیب همین توابع جستجو در همین مقاله استفاده کنید
      مطلب رو یاد بگیرید و با مسئله خودتون تطبیق بدید
      روی چیدت دیتابیس خوب فکر کنید که بتونید از توابعی که بهش مسلطید استفاده کنید

  • Abbas ۱۸ دی ۱۴۰۰ / ۱۰:۱۲ ب٫ظ

    سرکار خانم خاکزاد
    باسلام
    به استحضار میرساند فایل اکسلی دارم که از تعداد دو شیت تشکیل شده است که در شیت اول یک ستون کد ملی و یک ستون مبلغ که با فرمول VLOOKUP می خواهم اعدادی را در شیت دو م جایگزین کنم که در این شیت هم کد ملی و یک ستون خالی که بایستی مبالغ از شیت اول جایگزین شوند از آنجائیکه تعدادی از کدهای ملی تکراری می باشند این فرمول یک مبلغ را برای کدهای ملی که تکرار شده اند را درج می نماید
    میخواستم راهنمایی بفرمائید

    • حسنا خاکزاد ۱۹ دی ۱۴۰۰ / ۱۰:۳۲ ق٫ظ

      درود بر شما
      همین مقاله که کامنت گذاشتید برای این موضوعه
      مطالعه بفرمایید و مسئله رو حل کنید

  • ALIREZA AHMADI ۳۰ خرداد ۱۴۰۰ / ۴:۲۷ ق٫ظ

    سلام من اگه بخوام برای این فرمول دو تا if بزارم چطور میشه؟؟؟ یعنی دو شرطی کنم … با and هرکاری میکنم نمیشه

    • حسنا خاکزاد ۶ تیر ۱۴۰۰ / ۰:۳۳ ق٫ظ

      درود
      بستگی به سوالتون داره
      اعمال چند شرط دقیقا با همون and/or میشه
      میتونید سوال رو واضح بگید تا بهتر راهنمایی بشید

  • اکبر ۱۷ مرداد ۱۳۹۹ / ۱۰:۵۵ ق٫ظ

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

    • حسنا خاکزاد ۱۷ مرداد ۱۳۹۹ / ۶:۲۶ ب٫ظ

      درود
      منطق فرمول نویسی رو یاد بگیرید، بعد در هر جهتی مایل بودید بنویسید و درگ کنید!

  • مریم ۲۱ خرداد ۱۳۹۹ / ۲:۳۲ ب٫ظ

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

    • حسنا خاکزاد ۱ تیر ۱۳۹۹ / ۱۱:۵۷ ق٫ظ

      درود
      اگر دقیق اجرا شده باشه که مشکلی نباید باشه!
      باید فرمولتون و ببینیم تا مشخص بشه
      اما این نکته رو در نظر داشته باشید که این فرمول با Ctrl+shift+enter کار میکنه.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

سه + چهارده =

توسط
تومان