حساب المتوسط وفق عدة شروط باستخدام AVERAGEIFS

تحسب AVERAGEIFS متوسط القيم التي تستوفي عدة شروط في الوقت نفسه. فبدلًا من حساب متوسط كل عملية بيع، يمكنكم أن تسألوا ما متوسط مبيعات المنطقة الشرقية في يناير؟

وبذلك تكتمل المجموعة الثلاثية: تجمع SUMIFS، وتعدّ COUNTIFS، وتحسب AVERAGEIFS المتوسط — وكلها تستخدم أسلوب المعايير المتعددة نفسه. إذا كنتم تعرفون إحداها، فأنتم تعرفون الثلاث جميعًا تقريبًا.

تتبع AVERAGEIFS بنية SUMIFS تمامًا. تأتي القيم التي يُراد حساب متوسطها أولًا، ثم أزواج الشروط.

• average_range — الأرقام التي يُراد حساب متوسطها
• criteria_range1، criteria1
• criteria_range2، criteria2

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

إذا كان العمود A يمثل المنطقة، والعمود B يمثل الشهر، والعمود C يمثل المبلغ، فإليك كيفية حساب متوسط مبيعات المنطقة الشرقية في يناير.

القيم الموجودة في C:C هي القيم التي نحسب متوسطها. ويعمل زوجا الشروط على تصفية الصفوف لتقتصر على المنطقة الشرقية وشهر يناير قبل حساب المتوسط.

يمكنك تصفية القيم التي تحسب متوسطها بناءً على قيمتها نفسها. لنفترض أنك تريد حساب متوسط مبيعات المنطقة الشرقية الكبيرة فقط، أي المبيعات التي تتجاوز 100.

يُستخدم العمود C هنا بصفته average_range وبصفته criteria_range أيضًا. ويمكن لـ AVERAGEIFS إعادة استخدام العمود نفسه لكلا الدورين.

لإنشاء تقرير يمكن للمستخدمين التحكم فيه، ارجع إلى الخلايا بدلًا من كتابة القيم مباشرة. ضع المنطقة في F1 والحد الأدنى للمبلغ في F2.

لإجراء المقارنة، صِل عامل المقارنة بالخلية: تعني ">"&F2 أكبر من القيمة الموجودة في F2 مهما كانت. أما المطابقات النصية البسيطة، مثل المنطقة، فلا تحتاج إلى عامل مقارنة، بل إلى مرجع الخلية فقط.

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

يختلف هذا عن SUMIFS، التي تُرجع 0، وعن COUNTIFS، التي تُرجع 0 أيضًا. فمتوسط صفر من العناصر غير معرّف، ولذلك يعرض Excel خطأً بدلًا من التخمين.

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

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

هناك نقطة مهمة: تتجاهل AVERAGEIFS الخلايا الفارغة في average_range، فلا تدخل في المجموع ولا في المقسوم عليه. أما الخلية التي تحتوي على 0 فهي رقم حقيقي، ولذلك تدخل في الحساب.

إذا كانت الأصفار تُستخدم كعناصر نائبة للبيانات المفقودة، فستخفض المتوسط. فكّر في إضافة معيار مثل C:C, "<>0" لاستبعادها عند الحاجة.

أضف العدد الذي تحتاج إليه من الشروط. إذا كان العمود D يمثل مندوب المبيعات، فاحسب متوسط المبيعات التي كانت في المنطقة الشرقية، وفي يناير، ونفذتها Maria.

يؤدي كل زوج إضافي إلى تضييق نطاق التصفية. وبما أن AVERAGEIFS تستخدم منطق AND، فلا تدخل في المتوسط إلا الصفوف التي تستوفي الشروط الثلاثة كلها.

احرص على تذكر ترتيب الوسيطتين:

• AVERAGEIF: range, criteria, [average_range] — يأتي النطاق الذي سيُختبر أولًا، بينما يكون average_range اختياريًا ويأتي أخيرًا.
• AVERAGEIFS: average_range, range1, crit1, ... — يأتي average_range دائمًا أولًا.

وكما هو الحال مع الدوال الأخرى، فإن اعتماد إصدار ...IFS الجمعي افتراضيًا يمنحك نمطًا موحدًا عبر SUM وCOUNT وAVERAGE.

تتيح AVERAGEIFS إنشاء العديد من الملخصات المتجاورة. أدرج أسماء المناطق أسفل العمود F، ثم احسب متوسط مبيعات كل منطقة باستخدام صيغة واحدة قابلة للنسخ، مع تثبيت عمود المعايير.

باستخدام $C:$C بصفته average_range، و$A:$A بصفته criteria_range، و$F2 بصفته خلية المنطقة النسبية، يؤدي نسخ الصيغة إلى أسفل إلى منحك متوسطًا واحدًا لكل منطقة فورًا.

يمكنك الآن حساب متوسط القيم وفق عدة شروط:

• AVERAGEIFS(average_range, range1, crit1, ...) — يأتي average_range أولًا.
• يؤدي عدم وجود تطابقات إلى #DIV/0! — عالج ذلك باستخدام IFERROR.
• تُتجاهل الخلايا الفارغة، لكن الأصفار تدخل في الحساب؛ استبعدها باستخدام "<>0" عند الحاجة.
• استخدم ">"&F1 لتحديد حدود رقمية ديناميكية.

التالي: التعامل مع نطاقات التواريخ داخل دوال المعايير هذه.