كيفية استخدام COUNTIFS و SUMIFS و AVERAGEIFS في Excel

الصيغ الثلاث الأكثر استخدامًا في Excel والتي تقوم بإجراء حسابات رياضية بسيطة هي COUNT و SUM و AVERAGE . سواء كنت تدير ميزانية مالية في Excel أو ببساطة تتبع عطلتك القادمة ، فمن المحتمل أنك استخدمت إحدى هذه الوظائف من قبل.

في هذه المقالة ، سنستعرض أساسيات هذه الوظائف الثلاث ونظيراتها المفيدة وذات الصلة: COUNTIFS و SUMIFS و AVERAGEIFS.

لنفترض أننا بدأنا نشاطًا تجاريًا جديدًا عبر الإنترنت لبيع الهواتف المحمولة ولدينا ورقة تسرد المبيعات التي حققناها في أول شهرين. قم بتنزيل مثال لجدول بيانات Excel هنا .

اكسل COUNT و SUM و AVERAGE

لمعرفة عدد الهواتف المحمولة التي قمنا ببيعها ، يمكننا استخدام صيغة COUNT بسرعة كما هو موضح أدناه:

= COUNT (E2: E16)

من ناحية أخرى ، للحصول على المبلغ الإجمالي للمبيعات التي قمنا بها ، يمكننا استخدام صيغة SUM كما هو موضح أدناه:

= SUM (E2: E16)

أخيرًا ، لمعرفة متوسط ​​المبيعات التي حققناها لجميع الهواتف ، يمكننا استخدام صيغة AVERAGE على النحو التالي:

= متوسط ​​(E2: E16)

يجب أن تكون النتيجة على النحو التالي:

لن تعمل صيغ COUNT و SUM و AVERAGE إلا مع السجلات التي تكون فيها قيمة الخلية بتنسيق رقمي. سيتم تجاهل أي سجل داخل نطاق الصيغة (مثل E2: E16 في هذا المثال) ليس بتنسيق الأرقام.

لذا ، يرجى التأكد من تنسيق جميع الخلايا داخل صيغة COUNT و SUM و AVERAGE على أنها رقم وليس نص . حاول استخدام نفس الصيغة ، ولكن مع E: E كنطاق بدلاً من E2: E16 . سيعيد نفس النتيجة كما كان من قبل لأنه يتجاهل العنوان (أي سعر البيع ) ، والذي يكون بتنسيق نصي.

الآن ، ماذا لو أردنا معرفة عدد المبيعات والمبلغ الإجمالي للمبيعات ومتوسط ​​حجم المبيعات لكل هاتف ، فقط للمبيعات في الولايات المتحدة الأمريكية؟ هذا هو المكان الذي تلعب فيه COUNTIFS و SUMIFS و AVERAGEIFS دورًا مهمًا. لاحظ الصيغة أدناه:

COUNTIFS

انهيار الصيغة:

1. = COUNTIFS ( - يشير "=" إلى بداية الصيغة في الخلية و COUNTIFS هو الجزء الأول من وظيفة Excel التي نستخدمها.
2. D2: D16 - يشير إلى نطاق البيانات للتحقق لمعرفة ما إذا كان يفي بالمعايير التي سيتم تضمينها في صيغة العد.
3. "الولايات المتحدة الأمريكية" - المعايير التي يجب البحث عنها في نطاق البيانات المحدد ( D2: D16 )
4. ) - قوس إغلاق يشير إلى نهاية الصيغة.

تُرجع الصيغة 6 وهو عدد المبيعات للمنتجات المشحونة من مستودع الولايات المتحدة الأمريكية.

موجزات

انهيار الصيغة:

1. = SUMIFS ( - يشير "=" إلى بداية الصيغة مرة أخرى.
2. E2: E16 - يشير إلى نطاق البيانات التي نرغب في جمعها ، أي سعر البيع في مثالنا.
3. D2: D16 - يشير إلى نطاق البيانات للتحقق لمعرفة ما إذا كان يفي بالمعايير التي سيتم تضمينها في المبلغ الإجمالي.
4. "الولايات المتحدة الأمريكية" - المعايير التي يجب البحث عنها في نطاق البيانات المحدد ( D2: D16 )
5. ) - قوس إغلاق يشير إلى نهاية الصيغة.

توضح الصيغة إجمالي مبيعات 6،050 دولارًا أمريكيًا للمنتجات المشحونة من مستودع الولايات المتحدة الأمريكية.

المتوسطات

انهيار الصيغة:

1. = AVERAGEIFS ( - يشير "=" إلى بداية الصيغة.
2. E2: E16 - يشير إلى نطاق البيانات التي نرغب في حساب متوسطها. في هذا المثال ، نريد الحصول على متوسط ​​حجم المبيعات لجميع الهواتف المباعة في الولايات المتحدة الأمريكية.
3. D2: D16 - يشير إلى نطاق البيانات للتحقق لمعرفة ما إذا كان يفي بالمعايير المراد تضمينها في صيغة المتوسط.
4. "الولايات المتحدة الأمريكية" - المعايير التي يجب البحث عنها في نطاق البيانات المحدد
5. ) - قوس إغلاق يشير إلى نهايات الصيغة.

توضح الصيغة أننا قمنا ببيع المنتج بحوالي 1008 دولارات للهاتف في الولايات المتحدة الأمريكية.

يمكن أن تأخذ الصيغة الثلاثة أكثر من معيار واحد. على سبيل المثال ، إذا أردنا معرفة نفس الأرقام (مثل COUNT و SUM و AVERAGE ) للمنتجات المباعة في الولايات المتحدة الأمريكية ، ولكن خصيصًا لعلامة  Samsung التجارية فقط ، نحتاج فقط إلى إضافة نطاق البيانات ليتم التحقق منه متبوعًا بمعاييره.

يرجى الاطلاع على المثال أدناه حيث تمت إضافة معيار ثانٍ إلى فحوصات المعايير الأولية. (يشير النص الأزرق إلى المعيار الأول ويشير اللون الأحمر إلى المعيار الثاني)

= COUNTIFS (D2: D16، "USA"، B2: B16، "Samsung") = SUMIFS (E2: E16، D2: D16، "USA"، B2: B16، "Samsung") = AVERAGEIFS (E2: E16، D2 : D16 ، "USA" ، B2: B16 ، "Samsung")

ستلاحظ أن Excel يحتوي أيضًا على  صيغ COUNTIF و SUMIF و AVERAGEIF بدون اللاحقة "S" . تلك المستخدمة على غرار COUNTIFS و SUMIFS و AVERAGEIFS . ومع ذلك ، فإن أولئك الذين ليس لديهم اللاحقة "S" في الصيغة لديهم قيود على السماح بمعيار واحد فقط لكل صيغة.

نظرًا لأن بناء الجملة مختلف قليلاً ، فإنني أوصي باستخدام  COUNTIFS و SUMIFS و AVERAGEIFS فقط حيث يمكن استخدامها لمعيار واحد أو أكثر ، إذا لزم الأمر. يتمتع!