حساب المتوسط حسب المعايير باستخدام AVERAGEIF

لقد حسبت الإجمالي باستخدام SUMIF، والعدد باستخدام COUNTIF. أما العنصر الثالث في هذه المجموعة فهو AVERAGEIF، الذي يحسب متوسط القيم التي تستوفي شرطًا فقط.

فبدلًا من حساب متوسط كل المبيعات، يمكنك حساب متوسط مبيعات منطقة East فقط، أو الدرجات الأكبر من 50 فقط. فهي تجمع بين التصفية وحساب المتوسط في خطوة واحدة واضحة.

تستخدم AVERAGEIF البنية الثلاثية نفسها التي تستخدمها SUMIF:

• range الخلايا المطلوب اختبارها
• criteria الشرط
• average_range الخلايا المطلوب حساب متوسطها

تبحث في range، وتعثر على التطابقات، ثم تحسب متوسط الخلايا المقابلة في average_range.

مع وجود المناطق في A2:A10 والمبالغ في B2:B10، تختبر صيغة حساب متوسط مبيعات منطقة East العمود A بحثًا عن East، ثم تحسب متوسط القيم المطابقة في العمود B.

تجري الصيغة في الخلفية عملية SUMIF مقسومة على COUNTIF، لكن AVERAGEIF تجمع ذلك في صيغة واحدة سهلة القراءة.

إذا كان العمود الذي تختبره هو نفسه العمود الذي تريد حساب متوسطه، يمكنك حذف الوسيط الثالث. لحساب متوسط الدرجات في B2:B10 التي تتجاوز 70:

يُختبر العمود B ويُحسب متوسطه في الوقت نفسه. وعند حذف average_range، يستخدم جدول البيانات range لكلا الغرضين.

كما هو الحال مع دوال IF الأخرى، خزّن الشرط في خلية لزيادة المرونة. إذا كانت D1 تحتوي على اسم منطقة، فأشر إلى D1 في المعيار.

تتحكم خلية واحدة الآن في المتوسط. وسيؤدي تغيير D1 من East إلى West إلى إعادة الحساب فورًا، وهذا مثالي لجدول ملخص أو محدد في لوحة معلومات.

تقبل AVERAGEIF العوامل داخل علامتي اقتباس، مثل الدوال المشابهة لها. لحساب متوسط المبيعات التي لا تقل عن 100:

• ">=100" تساوي 100 أو أكثر
• "<50" أقل من 50
• "<>0" باستثناء الأصفار

الخيار الأخير مفيد جدًا، لأنه يحسب المتوسط مع تجاهل الإدخالات الصفرية التي قد تخفض المتوسط لولا ذلك.

لاستخدام حدّ مأخوذ من خلية، صِل العامل بها باستخدام علامة العطف. إذا كانت D1 تحتوي على حدّ، فاحسب متوسط كل القيم الأكبر منه.

تنشئ & نص الشرط من ">" والقيمة الموجودة في D1. وهذه هي حيلة الوصل نفسها التي استخدمتها مع SUMIF وCOUNTIF.

تحتوي AVERAGEIF على مشكلة لا تواجهها الدوال الأخرى. إذا لم تطابق أي صفوف الشرط، فلن يوجد شيء للقسمة عليه وستحصل على خطأ #DIV/0!.

فمثلًا، يؤدي حساب متوسط منطقة غير موجودة في البيانات إلى هذا الخطأ. ويعني ذلك أن عامل التصفية طابق صفرًا من الخلايا، وليس أن الصيغة معطّلة.

ضع الصيغة داخل IFERROR لعرض رسالة واضحة بدلًا من #DIV/0! عندما لا توجد تطابقات.

ستعرض المنطقة الفارغة الآن النص لا توجد بيانات بدلًا من خطأ مقلق. ويساعد ذلك في الحفاظ على مظهر التقارير منظمًا حتى عند غياب بعض الفئات.

تحسب AVERAGEIF متوسط الخلايا التي تحتوي على أرقام فقط من بين الخلايا المطابقة. وتُتجاهل الخلايا الفارغة فعلًا في average_range، لذلك لا تُعامل على أنها أصفار.

وهذا مهم: فالقيمة المفقودة لن تسحب المتوسط نحو الصفر كما يفعل الرقم 0 الفعلي. وإذا أردت استبعاد الأصفار أيضًا، فأضف المعيار "<>0" إلى عمود القيم.

أنشئ ملخصًا منظمًا: أدرج كل فئة في الخلايا D2 وD3 وD4، ثم اكتب صيغة AVERAGEIF واحدة تشير إلى خلية التسمية واسحبها إلى الأسفل.

سيعرض كل صف الآن متوسط القيمة لفئته، إلى جانب إجمالي SUMIF وعدد COUNTIF من الدروس السابقة. وتشكل هذه العناصر معًا تقريرًا موجزًا يعتمد بالكامل على الصيغ.

اختبر مدى فهمك لسلوك AVERAGEIF.

يمكنك الآن حساب المتوسط وفقًا لشرط باستخدام AVERAGEIF. إليك أهم النقاط:

• الترتيب هو range, criteria, average_range؛ احذف الوسيط الأخير لحساب متوسط نطاق الاختبار نفسه.
• تدعم النصوص والأرقام والعوامل مثل ">=100".
• يؤدي عدم وجود تطابقات إلى خطأ #DIV/0!، لذا عالجه باستخدام IFERROR.
• تُتجاوز الخلايا الفارغة ولا تُعامل على أنها أصفار.

ستتعلّم بعد ذلك مطابقة النصوص الجزئية باستخدام أحرف البدل.