ثبت نام دوره آنلاین VBA به زبان ساده
سبد خرید
0

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

جستجو
Generic filters

تابع Match، تابعی قدرتمند برای ترکیب با سایر توابع

تابع match در اکسل
۵/۵ - (۱۲ امتیاز)

تابع match در اکسل

برای اینکه به یک فرمول نویس حرفه ای در اکسل تبدیل بشید، علاوه بر اینکه باید به اصول و قواعد فرمول نویسی حرفه ای آشنا باشید، باید به یک سری توابع خاص نیز مسلط باشید. یکی از اون توابع خاص، تابع Match در اکسل از دسته توابع Lookup & Reference هست. درک مفهوم این تابع خیلی اهمیت داره. چرا که در حل خیلی از مسائل و در فرمول نویسی ترکیبی، از این تابع استفاده میشه. این تابع مکان یک داده رو در یک محدوده تک بعدی به ما میده. یعنی چی؟

فرض کنید در شکل ۱، میخوایم ببینیم کلمه “شعبه۳” چندمین سل در این محدوده است و انتظار داریم که خروجی فرمول، عدد ۳ باشد، چرا که این کلمه (شعبه۳)، سومین سل در محدوده مورد نظر است.

تابع match در اکسل - شروع کار

شکل ۱- پیدا کردن مکان کلمه “شعبه۳”

تشریح آرگومان ها

Lookup_Value: عبارتیست که میخواهیم ببینیم چندمین سل در محدوده مورد نظر است. در مثال بالا، کلمه شعبه ۳ است.

Lookup_Array: آرایه ای است تک بعدی که در آن، دنبال عبارت Lookup_Value می گردیم. در مثال بالا، B2:G2 هست.

[Match_Type]: سه مقدار ۱، ۰ و -۱ میگیرد:

مقدار ۰ ، اولین داده ای که دقیقا معادل Lookup_Value هست رو پیدا میکنه و شماره مکانشو میگه. در این حالت توالی قرارگیری محتوا در Lookup_Array به هر طریقی می تواند باشد.

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

نکته:
در این حالت مقادیر درون Lookup_Array باید به صورت صعودی باشند. به عنوان مثال: -۲، -۱، ۰، ۱، ۲، …

 

مقدار ۱-، اگر عین عبارت رو پیدا نکنه، بزرگترین مقدار کوچکتر و یا مساوی Lookup_Value را پیدا و جایگاهش رو میده.

نکته:
در این حالت مقادیر درون Lookup_Array باید به صورت نزولی مرتب شده باشند. به عنوان مثال: ۲، ۱، ۰، -۱، -۲، …

 

در مثال بالا، تابع به اینصورت نوشته میشه:

=Match(D2,B2:G2,0)

خروجی این تابع ۳ خواهد بود. چون همونطور که توضیح داده شد، کلمه شعبه۳، سومین سل در محدوده مورد نظر است.

نکته:
دقت داشته باشید که Lookup_Array، حتما یک محدوده تک بعدی، یعنی یک ستون یا یک ردیف باید باشه.

 

چون خروجی تابع Match، عدد هست، با اکثر توابعی که آرگومان هایی از جنس عدد دارن قابلیت ترکیب داره. یکی از این ترکیب های کاربردی رو در مثال زیر می بینیم:

ترکیب تابع Match و تابع Vlookup

همونطور که میدونید آرگومان سوم در تابع Vlookup ، یا همون Col_Index عدد هست. پس میتونیم از تابع Match توی این آرگومان استفاده کنیم. همون مثال تشریح شده در تابع Vlookup رو در نظر بگیرید. میخواهیم تابع رو طوری بنویسیم که با تغییر سل H2، جواب نیز تغییر کنه. یعنی (مطابق زیر) هر بار محاسبه بشه که عبارتی که در سل H2 نوشته میشه، چندمین سل در محدوده A1:E1 است.

تابع match در اکسل - نحوه استفاده

برای این کار در آرگومان سوم Vlookup از تابع Match استفاده می کنیم.

=Vlookup(G3,A1:E16,Match(H2,A1:E1,0),0)

با این کار شماره ستون در تابع Vlookup رو وصل میکنیم به تابع Match که هر بار با تغییر H2، محاسبات بروز شود.

تابع match در اکسل - ترکیب با تابع Vlookup در اکسل

شکل ۲- ترکیب تابع Match و Vlookup

خب، ماهیت تابع Match در اکسل رو با یک ترکیب کاربردی شرح دادیم. این تابع یکی از مهم ترین توابع جستجو به حساب میاد که تسلط به آن، در حل خیلی از مسائل کمک کننده خواهد بود.

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

دیدگاه کاربران
  • مرزبان ۸ آبان ۱۳۹۸ / ۱۱:۰۴ ب٫ظ

    با عرض سلام و خسته نباشید من ی جدول فروش دارم که طبق تاریخه و فروش در هر تاریخ بین ۶ تا ۷ مورد یا بیشترم میشه حالا میخوام که گزارش فروش به تفکیک تاریخ بگیرم که مثلا فلان تاریخ فروش چقدر بوده و مبلغ فروش به کجا رفته مثلا از طریق کارتخوان بوده یا واریز به حساب و … از طریق کدوم فرمول اینکارو بکنم لطفا کمکم کنید چون خیلی درگیرشم اگه لطف کنید فرمولشم بفرستید ممنون میشم

    • سامان چراغی ۱۰ آبان ۱۳۹۸ / ۹:۱۶ ق٫ظ

      سلام
      برای ایجاد این گزارش حتما از Pivot Table استفاده کنید.

  • ارمشی ۳۱ شهریور ۱۳۹۸ / ۱۲:۰۴ ب٫ظ

    سلام
    اگه شعبه ۱ تا ۶ از سمت راست شروع شده باشه؟
    غاطی میکنه!!!

    • حسنا خاکزاد ۳۱ شهریور ۱۳۹۸ / ۸:۱۹ ب٫ظ

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

  • مهدی ۹ اردیبهشت ۱۳۹۸ / ۱:۵۵ ب٫ظ

    باسلام
    فرمان VLOOKUP(D3, G3:J5, MATCH(C3, H2:J2, 0) + 1, FALSE) مشکلی نداره ولی وقتی به جای D3 از فرمول Cells(3,4) استفاده می کنم با پیغام خطا مواجه میشم. مشکل فرمول چیه؟

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

    باسلام
    ممنون از سایت خوبتون
    یه سوال داشتم از خدمتتون
    یه مثال از ترکیب Match در Vlookup زدید
    =Vlookup(G3,A1:E16,Match(H2,A1:E1,0),0)
    حالا اگر بخواهیم همین ترکیب را در VBA به کار ببریم با این پیغام خطا مواجه می شیم که دستور Match تعریف نشده است. برای حل این مسئله باید چیکار کنم؟

    • سامان چراغی ۹ اردیبهشت ۱۳۹۸ / ۱۰:۱۷ ب٫ظ

      سلام و تشکر از لطف شما،
      برای استفاده از تابع Match باید به صورت زیر کد بزنید:

  • محمدرضا ۱۲ شهریور ۱۳۹۷ / ۱۰:۳۴ ق٫ظ

    با عرض سلام

    در استفاده از تابع match میخواهم دنبال اولین سلول خالی بگردم. وقتی در آرگومان اول مقدار ۰ رو قرار میدم خطا #N/A میده. باید مقدار آرگومان اول رو چی قرار بدم؟

    با تشکر از تیم اکسل پدیا.

    • حسنا خاکزاد ۱۲ شهریور ۱۳۹۷ / ۱۱:۱۰ ق٫ظ

      درود بر شما

  • سیاوش ۲۰ مرداد ۱۳۹۷ / ۷:۲۲ ب٫ظ

    سلام
    یه مشکلی داشتم که تقریبا ۲-۳ روزه درگیرش هستم ولی نتونستم درستش کنم
    من میخوام برای حداکثر ۸ تا محصول در ۱۲ ماه سال گرافی از نرخ ترسیم کنم
    در جدول جداگونه برای هر محصول جداگونه یه فرمول IF نوشتم و با گزینه TRUE (که با تیک فعال میشه) میتونم توی نمودار اضافش کنم اما مشکل اصلی اینه که برای ۱۲ ماه سال الزاما قیمت نیست مثلا ۴ ماه اول سال هست بعد توی مرداد قیمت جدید اضافه میشه و به همین ترتیب. با این حال تمام ماه ها رو بهم نشون میده. برای سایر نمودارها هم همین مشکل رو دارم.
    حتی فرمول با OFFSET و MATCH هم نوشتم اما برای نمودار وقتی ادیت رو میزنم و میخوام محدوده دیتا رو با فرمول مشخص کنم اصلا کار نمی کنه و ارور میده.
    ممنون میشم کمک کنید

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

      درود بر شما
      برای ایجاد نمودار با محدوده متغیر از این لینک کمک بگیرید:

      https://excelpedia.net/dynamic-chart/

  • علی ۱۸ اردیبهشت ۱۳۹۷ / ۰:۵۹ ق٫ظ

    مهندس اون دوتا فرمولی آخری که بهم دادین من وارد میکنم دوباره #REF مینویسه و جوابی نمیده

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

      درود
      خطای Ref به این معنی که به یجا خارج از محدوده ارجاع میده
      باید منطق فرمول رو خوب درک کنید تا بتونید خطا رو رفع کنید.
      https://excelpedia.net/error-management/

      • علی ۱۸ اردیبهشت ۱۳۹۷ / ۸:۴۷ ب٫ظ

        منم دقیقا همین فرمولی که مهندس دادن رو وارد میکنم
        بازده اطلاعاتیم بین A1 تا L1 و A2 تا L2 هست که فرمولیم که ایشون دادن بصورت پیشفرض رو همین بازده فرمول نویسی شده..ولی باز ref میزنه.من هیچی تغییر نمیدم

        میخواین شما خودتون تو اکسل a1 تا L1 ماههای سالو بنویسید و در a2 تا L2 یسری اعداد بنویسید بعد ببینید جوابش چی میاد

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

          بله
          به این صورت اصلاح کنید

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

          دقت کنید فرمول آرایه ای بصورت Ctrl+shift+Enter ثبت میشه.

  • روحان ۳ اردیبهشت ۱۳۹۷ / ۱۲:۲۲ ب٫ظ

    با سلام میخواهم تکراری کالاها حذف شود بالاترین قیمت بماند راهنماییم کنید تشکر

  • علی ۲۹ فروردین ۱۳۹۷ / ۱۱:۳۰ ب٫ظ

    سلام
    بلخشید من یه سوال دارم که جوابشو هیچ جایی پیدا نکردم…
    من دوتا سطر دارم.(سطر ۱ و ۲)
    سطر ۱ شامل ۱۲ماه سال هستش که هر ماهو داخل یه ستون نوشتم

    سطر ۲ شامل حقوقی هست که من تو هر ما میگیرم

    حالا من چجوری میتونم یه فرمولی بنویسم که توی یه سلول یا توی دوتا سلول بهم بگه که به عنوان مثال خرداد ماه فلان حقوقو گرفتی

    ینی تو بازده ۱۲ ماهه خودش بیشترین حقوقو پیدا کنه بعد ماهشم بگه
    ینی فقط مبلغ حقوق نباشه، ماهشم باشه

    خواهشا کمکم کنید
    برام مهمه

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

      درود بر شما
      اگر عدد تکراری نداشته باشید(اگر هم بیشترین حقوق دوبار یا بیشتر بود، اولی رو در نظر بگیرید)، به این روش میشه:

      با فرض اینکه در ردیف ۱ ماهها نوشته شده و در ردیف ۲ حقوق ها

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

      • علی ۳۱ فروردین ۱۳۹۷ / ۱:۵۸ ق٫ظ

        مهندس دستت درد نکنه
        همونی بود که میخواستم
        حالا یه سوال دیگه
        من از این فرمول تو چنتا چیز استفاده کردم
        مثل بیشترین حقوق،کمترین حقوق،بیشترین اضافه کار،کمترین اضافه کار،حالا بعدش اومدم رو بیشترین مرخصی هم همین کارو کنم ولی وقتی تایید میکنم N/A میاد

        مطمئنم فرمول درست وارد کردم
        مشکلم کجاست؟

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

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

          • علی ۳۱ فروردین ۱۳۹۷ / ۹:۰۰ ب٫ظ

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

            دستتون درد نکنه

      • علی ۹ اردیبهشت ۱۳۹۷ / ۱۰:۵۸ ب٫ظ

        مهندس یه سوالی برام پیش اومده

        من الان ۱۲تا سطر دارم برای آمار اضافه کار ۱۲ ماهم.(همون توضیحاتی که بالا دادم)… که همشونم با sum فرمول نویسی کردم که خودش جمع کنه.الان دوماه از سال میگذره و من اطلاعات دو ماهو وارد کردم.پس بقیه ماها بصورت پیشفرض تو قسمت جمعشون صفر هستش.
        حالا مشکل من اینجاس که شما این فرمولی که بالا به من دادین برای max که طبیعتا درسته،،ولی در حالت min خودش میره اون ماهایی رو که من اطلاعاتی وارد نکردم و به صورت پیشفرض صفر خورده رو به عنوان مثال کمترین اضافه کار تو فلان ماه میشناسه.که طبیعتا در این حالت پس پیدا کردن کمترین ماه چه از لحاظ اضافه کارو… بی فایدس

        حالا سوال من اینه که نمیشه کاری کرد که تو حالت min ، اون ماهایی روکه پیشفرض صفر خورده رو تو نتیجه نیاره؟؟

        • سامان چراغی ۱۰ اردیبهشت ۱۳۹۷ / ۹:۳۴ ق٫ظ

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

          • علی ۱۰ اردیبهشت ۱۳۹۷ / ۸:۳۸ ب٫ظ

            مهندس اول از همه ممنونم از پاسخگوییتون

            این فرمولی که نوشتین من وارد میکنم ولی جوابش REF# مینویسه

            بعد اینکه من دوتا سطر دارم که اولی ماهها و دومی اضافه کارا،، ولی شما برای یه سطر (اولی) فرمول نویسی کردین

            من میخوام که جوابش تو دوتا سلول بیاد،یعنی سلول اول ماهو پیداکنه و سلول زیریش کمترین اضافه کارو (بجز صفرهای پیشفرض) پیدا کنه که فکر کنم باید دوتا فرمول بنویسم(تو هر سلول جدا)

            بنظرتون مشکلم کجاست؟؟

          • سامان چراغی ۱۱ اردیبهشت ۱۳۹۷ / ۸:۴۷ ق٫ظ

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

            بدست آوردن کمترین مقدار:

  • رمضانی ۲۲ فروردین ۱۳۹۷ / ۱۲:۵۲ ب٫ظ

    متشکر از آموزش های عاااالیتون-من توی یکی از شیت هام نوع محصول و وزن محصول فروخته شده به مشتری های مختلف رو دارم -میخواستم جمع هر کدوم از محصول های فروخته شدم رو توی یه شیت دیگه داشته باشم -برای این کار قبلا فیلتر میکردم نوع محصول رو وجمعشونو در میاوردم -راه بهتر و سریع تری هست امکانش هست کمکم کنید

  • کیوان ۱۰ دی ۱۳۹۶ / ۱۱:۵۹ ب٫ظ

    واقعا” بصورت واضح و جالب توضیح داده اید.

ارسال دیدگاه

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

توسط
تومان