تابع COUNTIFS اکسل و نحوه استفاده از آن

نحوه استفاده از تابع COUNTIFS اکسل

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

قاعده کلی تابع COUNTIFS اکسل به صورت زیر هست:

(…,[اختیاری]شرط2 ,[اختیاری]محدوده2 ,شرط1 ,محدوده1)COUNTIFS

حالا با هم پارامترهای تابع COUNTIFS اکسل رو بررسی میکنیم:

  • محدوده1: این پارامتر محدوده ای هست که قراره سلول های اون در صورت داشتن شرط خاصی شمارش بشن.
  • شرط1: با استفاده از این پارامتر ما اولین شرط خودمون رو براش شمارش تعریف مشخص میکنیم. این پارامتر میتونه یک عدد مثل 10، یک متن مثل قطعه 1 یا یک عبارت مثل “0=” باشه. در صورتی که این پارامتر عبارت باشه باید حتما بین دو علامت نوشته بشه.
  • محدوده2: این پارامتر دقیقاً مشابه پارامتر محدوده1 هست با این تفاوت که تعیین این پارامتر اختیاری هست. در صورتی که این پارامتر رو تعیین نکنیم شمارش ما تک شرطی خواهد شد و در واقع تابع ما به تابع COUNTIF تبدیل میشه. تعداد سلولهای موجود در این محدوده باید با تعداد سلولهای موجود در محدوده1 برابر باشه. به عنوان مثال اگر ما برای پارامتر محدوده1 مقدار A1:A10 رو تعیین کردیم میتونیم برای محدوده2 B2:B11 رو تعیین کنیم چون تعداد سلولهای این محدوده هم دقیقاً برابر 10 سلول هست، اما نمیتونیم A2:A12 رو تعیین کنیم چون تعداد سلول های اون از محدوده A1:A10 بیشتره.
  • شرط2: با استفاده از این پارامتر شرط موردنظرمون رو برای محدوده2 اعمال میکنیم.
  • افزودن بیش از دو شرط: در صورتی که نیاز به بیش از دو شرط داشته باشیم میتونیم به همین ترتیب محدوه3، شرط3، محدوده4، شرط4 و .. رو تا 127 شرط ادامه بدیم.

دقت داشته باشید که تابع COUNTIFS اکسل از اولین سلول محدوده هایی که براش تعریف شده شمارش رو شروع میکنه و در صورتی یک عدد به شمارش اضافه میکنه که شرط1 در سلول متناظر در محدوده1 برقرار باشه، شرط2 در سلول متناظر در محدوده2 برقرار باشه و الی آخر.

حالا با یک مثال به صورت عملی کار با تابع COUNTIFS اکسل رو تمرین میکنیم. جدول زیر رو در نظر بگیرید:

A B C
1 تامین کننده محصول تعداد خریداری شده
2 قطعه سازی هراز رینگ 472
3 قطعه سازی رضایی رینگ 261
4 قطعه سازان شرق رینگ 416
5 قطعه سازی رضایی رینگ 640
6 قطعه سازی هراز رینگ 814
7 قطعه سازی رضایی رینگ 296
8 قطعه سازی رضایی یاتاقان 210
9 قطعه سازان شرق یاتاقان 551
10 قطعه سازی هراز یاتاقان 112
11 قطعه سازان شرق یاتاقان 652
12 قطعه سازان شرق یاتاقان 647
13 قطعه سازی هراز یاتاقان 489
14 قطعه سازی رضایی یاتاقان 862
15 قطعه سازان شرق یاتاقان 505
16 قطعه سازی رضایی یاتاقان 101
17 قطعه سازی رضایی یاتاقان 466

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

  • محدوده1: اولین شرط ما برای شمارش تعداد دفعات خرید این هست که شرکت موردنظر “قطعه سازی هراز” باشه. از اونجایی که نام شرکت ها در ستون B قرار داره پس مقدار پارامتر محدوده1 ما B:B خواهد بود.
  • شرط1: ما میخوایم خریدهایی شمارش بشن که مربوط به شرکت قطعه سازی هراز هست. پس شرط1 ما “قطعه سازی هراز” هست. دقت کنید که در اینجا پون شرط ما متنی هست اون رو داخل علامت نقل قول قرار میدیم.
  • محدوده2: ما میخوایم خرید ها رو در خصوص محصول خاصی شمارش کنیم. پس نیاز به تعریف دومین محدوده و شرط داریم. نام محصولات ما در ستون C قرار داره. پس پارامتر محدوده2 ما C:C هست.
  • شرط2: ازونجایی که ما میخوایم خریدهای مربوط به محصول “رینگ” رو بشماریم پس شرط2 ما “رینگ” خواهد بود.

در نهایت تابع COUNTIFS اکسل ما به شکل زیر کامل میشه:

COUNTIFS(A:A;”قطعه سازی هراز”;B:B;”رینگ”)

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

دانلود فایل مثال تابع COUNTIFS اکسل:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

تمرین:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

103 Comments on “تابع COUNTIFS اکسل و نحوه استفاده از آن”

  1. با سلام و خسته نباشید
    من یه ستون از اعداد چند رقمی دارم ، مثلا 1000 تا عدد 10 رقمی. بعضی از این اعداد مشابه هم هستن . چطور میتونم شمارش تعداد ی اعداد10 رقمی و داشته باشم در صورتیکه اعداد تکراری فقط یه بار شمرده بشن ؟ اعداد 10 رقمی که بیشتر از یک بار تکرار شدن همون یه بار شمرده بشن .
    ممنونم

    1. با استفاده از ابزار remove duplicates در تب data می تونید داده های تکراری رو حذف کنید و از این طریق به راحتی داده های یونیک رو بشمرید.

  2. سلام
    من یه شیت دارم که به تعداد روزهای ماه ستون داره و به تعداد پرسنل سطر،جلوی هر اسم در ر روز وضعیت حض.ر و غیاب مشحص است .میخواهم تابعی رو بنویسم که تعداد سلول هایی که محتوی غ هستن رو نشون بده از تابع count if استفاده می کنم ارور میده.میشه راهنمایی بفرمایید؟
    ممنون

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

    1. سلام با این کار رو می تونید به وسیلۀ conditional formating در تب home انجام بدید. گزینه های موجود در این ابزار به شما کمک می کند تا با شرطی که دوست دارید گزینه های مورد نظرتون رو هایلایت کنه

  3. سلام خسته نباشید
    اگر بخوام در فرمول countifs یکی از شرط هام مساوی یکی از سلول ها مثلا سلول m6 باشد مثلا اینطوری وارد کنم =m6 جواب اشتباه میده باید چکار کنم

  4. سلام خسته نباشید
    اگر بخوام در فرمول countifs یکی از شرط هام مساوی یکی از سلول ها مثلا سلول m6 باشد مثلا اینطوری وارد کنم =m6 جواب اشتباه میده باید چکار کنم.

  5. سلام
    میخوام مقادیری که از یک نوع هستند رو جمع بزنم چیکار کنم؟
    مثلا:
    آبی 50
    قرمز 30
    سبز 10
    آبی 10
    زرد 20
    سبز 30
    ————
    آبی 60
    قرمز 30
    سبز 40
    زرد 20
    اینم بگن که تعداد رکوردها خیلی زیاده وگرنه هر مورد رو فیلتر می کردم
    در واقع همون countif هست ولی نمیخوام درمورد خاصی جستجو و عمل جمع رو انجام بدم
    میخوام همه مقادیر ستون B که مقادیر ستون A اونها با هم برابره باهمدیگه جمع بشه

  6. ببخشید یه سوال داشتم میتونید کمک کنید . وقی ی فرمولی مینویسم مقدار اعداد متغیر وارد میکنیم ی جوابی بهمون میده . بعد مرحله بعد متغیرها تغییر میکنه ی جواب میده . همینجور ادماه داره. دستوری هست جواب هر مرحله که بدست میاد یک جوا نوشته شود . وقتی متغیر ها عوض میکنیم و جواب جدید بدست میاد اونم کنارش نوشته . و همینجور ادامه داشته باشه؟؟؟ یعنی منظورم این اون خروجی که داریم هربار حساب میکنیم چون فرمول تو ورودی ها عوض مکنیم دوباره عوض میشه و باید یاداشت کرد راهی هست خودش یجا یاداشت کن؟

  7. سلام خسته نباشید
    یه سوال داشتم
    2 تا Sheet داریم به اسم a و b میخوام توی جدول a یک ستونش که یه متنی های متفاوتی رو داخلش نوشتم بره با تعدادی از ستون های جدول b مقایسه بشه و در صورت تطابق تعداد اون متنی رو که توی جدول b هست در یک سلول مقابل همون متنی که در جدول a هستش نمایش بده
    مثلاً اسم علی چندبار در جول b تکرار شده و همینطور رو به پایین که اسم ها هم تغییر میکنند
    ممنونم

  8. ببخشید اگه بخوایم مجموع دوباری که رینگ و از هراز خریدیم بدست بیاریم چجوری محاسبه میشه؟

  9. جسارتا اگه بخوایم تعداد رینگ های خریداری شده توسط قطعه سازی هراز جمع بشن چه دستوری باید نوشت؟

  10. سلام
    من یه جدول دارم که حدود 6000 سطر داره/ یکی از ستون هام حدودا 40 تا اسم از رنگ موهای مختلف توش نوشته شده
    یه فرمول میخوام که بهم بگه تو هر شهر چه رنگ مویی بیشتر از همه وجود داره

  11. سلام
    با تشکر از پاسخ دهی به موقع و سایت خوبتون
    میشه یه فرمول نوشت که نامهای تکراری وارد شده در ستون A رو در یک سلول روبروی همون نام حساب کنه مثلا 50 تا اسم در ستون A داریم نام رضا 6 بار تکرار شده با استفاده از اون فرمول عدد 6 رو جلوی همه نامهای رضا در یک ستون دیگه قرار بده و به محض اضافه شدن یک اسم رضا همه اعداد به 7 تغییر کنه . به همین صورت برای همه اسامی وارد شده در ستون A محاسبه کنه

  12. سلام چطور در اکسل از بین 0 تا 100 اعداد فرد را با هم دیگر جمع کنم
    یعنی فرمول حاصل جمع اعداد فرد بین 0 تا 100 رو چطور باید بنویسم
    ممنون از پاسخگویی

  13. سلام مهندس
    اگه بخواهیم 1….19 >20-25< 26….100 را در اکسل به نحوی که فقط سلول اعداد 20 تا 25 سبز رنگ و اعداد بزرگتر از 25 و همچنین اعداد کوچکتر از20 به رنگ قرمز در بیاد چکار کنیم ممنووون

  14. سلام-من عینا همین مطالبو کپی کردم توی اکسل اما اعمال نشد فقط فرمولو نشون میده

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

پاسخ دهید

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