نسخه 3 افزونه تقویم شمسی با امکانات بیشتر منتشر شد!!! (برای مشاهده اینجا کلیک کن)
سبد خرید
0

محصولی در سبد خرید نیست.

بازگشت به فروشگاه
تمام دسته ها
  • تمام دسته ها
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • Power BI
  • Power Pivot
  • Power Query
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

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

مشکل تابع 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 داده مورد نظر رو فراخوانی میکنند.

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

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

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

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

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

133

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

دیدگاه کاربران
  • رضا ۱۷ مهر ۱۴۰۳ / ۷:۳۲ ب٫ظ

    خبلی عالی بود. درود بر شما

  • الناز صفری ۷ شهریور ۱۴۰۲ / ۱:۴۸ ب٫ظ

    سلام من با گزینه find دفعه ی اول سرچ می کنم .اطلاعات رو برام پیدا می کنه برای دفعه دوم به دنبال چیز دیگری می گردم .ارور می دهد.

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

      درود
      گزینه find منظور، ابزار find هست یا تابع؟
      چه اروری؟

    • رضا ۱۷ مهر ۱۴۰۳ / ۷:۴۱ ب٫ظ

      با سلام فایل جهت دانلود به ایمیل ارسال نشد.

      • آواتار
        حسنا خاکزاد ۲۱ مهر ۱۴۰۳ / ۸:۴۳ ق٫ظ

        درود
        فولدر اسپم رو چک بفرمایید

  • حامد ۱۴ مرداد ۱۴۰۲ / ۱۲:۳۳ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۵ مرداد ۱۴۰۲ / ۱۲:۴۳ ب٫ظ

      درود بر شما
      باید با یک روش، دیتابیس ها رو یکی کنید
      حالا یا با پاورکوئری
      یا در گوگل شیت و با استفاده Vstack و …

  • حامد ۱۸ اردیبهشت ۱۴۰۲ / ۹:۳۴ ق٫ظ

    سلام وقت بخیر
    من این فرمول رو نوشتم ولی توی گام اول تابع Row خروجی درست نمیده که بخوام فرمول رو تا آخر پیش ببرم،یعنی بجای شمارش داده مورد نظر توی ستون مد نظر کل اون ستون رو شمارش میکنه.دیتابیس من داخل جدول هست و من فرمول رو هم در یک جدول دیگه نوشتم و هم توی رنج معمولی ولی خروجی نامیده.و هر بار با Ctrl+shift+enter ثبتش میکنم.
    لطفاً راهنمایی کنید.ممنون

    • آواتار
      حسنا خاکزاد ۱۸ اردیبهشت ۱۴۰۲ / ۱۱:۵۵ ق٫ظ

      درود بر شما
      Row کارش تولید عدد برای تعیین شماره ردیف مورد نظره
      احتمالا دیتابیس شما با این موضوع همخوان ینداره
      فرمول رو دیباگ کنید تا مفهوم row ر ومتوجه بشید که چه کار یانجام میده توی فرمول بعد میتونید اصلاح کنید

      چون بدون دیدن دیتابیس و نحوه فرمول نویسی نمیشه نظر دیگه ای داد.

  • فرید ۲۹ شهریور ۱۴۰۱ / ۶:۱۹ ب٫ظ

    سلام این اموزش خوب بود ولی یک بار نوشتم و مثل vlookup عمل کرد و فقط سطر اول رو برای من مشخص کرد
    خوشحال میشم راهنماییم کنید.(ورژن اکسل هم ۲۰۱۶ است)

    • آواتار
      حسنا خاکزاد ۱۶ مهر ۱۴۰۱ / ۱۲:۳۴ ب٫ظ

      درود
      فرمول آرایه ای است و باید با ctrl shift enter در ورژن ۲۰۱۶ که فرمودید ثبت بشه
      فایل نمونه رو دانلود کنید تا متوجه جزئیات بشید

  • امید ۷ مرداد ۱۴۰۱ / ۱۱:۱۰ ب٫ظ

    سلام، خدا قوت
    اگه بخواهیم در یک جدول مثلا ۸ ستون و ۴۰ ردیف، با شرط اینکه اگر مقادیر ردیف ۱ و ستون ۱،۲،۳ آن با هر ردیف در ستونهای ۴،۵،۶ برابر شد مقدار ستون ۷ از همین ردیف که برابری حاصل شده است را در ردیف ۱ ستون ۸ بنویسد، بهترین راهکار چی هست؟ ضمن اینکه تمام مقادیر ستون ۱ تا ۳ و ستون ۴ تا ۶ یکتا هستند.

    • آواتار
      حسنا خاکزاد ۸ مرداد ۱۴۰۱ / ۱۲:۱۴ ب٫ظ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ارسال دیدگاه

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

توسط
تومان