السلام عليكم و رحمة الله و بركاته

في التدوينة السابقة

http://excel4us.com/blog/?p=335

تحدثنا عن حساب المتوسط مع إستثناء القيم المساوية للأصفار و لنها كان محدودة في نطاق واحد و هو النطاق منA1:A10

و لكننا في حالة اليوم سنتعامل مع المتوسط في عدة نطاقات مع تجاهل القم المساوية لصفر و التي ستؤثر على نتيجة المتوسط كما شرحناها سابقاً.

الصورة التالية توضح الفكرة

كما نلاحظ فإن كل نطاق من النطاقات التالية بها قيمA1:A4,D4:D7,G1:G4

و بعض هذه القيم صفرية و نريد إحتساب المتوسط بإستثناء الأصفار

لو إستخدمنا المعادلة التالية

=AVERAGE(A1:A4,D4:D7,G1:G4)

ستكون النتيجة خطأ

و الدوال

AVERAGEIF

و

AVERAGEIFS

لا تفي بالطلب

و لحل ذلك يجب ان نستخدم معادلات تستثني القيم الصفرية في عملية الإحتساب

و سكيون الحل بجمع النطاقات بإستخدام معادلات الجمع العادية

=SUM(A1:A4,D4:D7,G1:G4)

ثم قسمتها على جمع القيم بإستثناء القيم المساوية لصفر و للحصول عليها نستخدم المعادلة التالية:

=SUMPRODUCT(--(A1:A4<>0))+SUMPRODUCT(--(D4:D7<>0))+SUMPRODUCT(--(G1:G4<>0))

و بذلك سنحصل على حاصل جمع القيم و تساوي 42 و نقسمها على عدد هذه القيم بإستثناء الصفر و سيكون عددها 10 و الناتج النهائي هو 4.2

و تبقى الخطوة الأخيرة في وضع المعادلتين في معادلة واحدة:

=SUM(A1:A4,D4:D7,G1:G4)/(SUMPRODUCT(--(A1:A4<>0))+SUMPRODUCT(--(D4:D7<>0))+SUMPRODUCT(--(G1:G4<>0)))

==========================

و طريقة أخرى لتنفيذ الحل

بإستخدام الجزء الأول من المعادلة

=SUM(A1:A4,D4:D7,G1:G4)

و قسمته على المعادلة التالية:

=INDEX(FREQUENCY((A1:A4,D4:D7,G1:G4),{0}),2)

حيث سيقوم الجزء الداخلي بإحتساب التوزيع التكراري للنطاقات المحددة و التي سيأخذ الفاصل لها الرقم 0 و التي ستعطينا بالتالي نتيجة صفيف من رقمين الرقم الاول يمثل تكرار القيم المساوية للصفر و الرقم الثاني يمثل القيم الأخرى و في مثالنا ستكون النتيجة:

{2;10}

و في المعادلةINDEX حددنا الرقم 2  ليقرأ الجزء الثاني من الصفيف و الذي يساوي 10

ثم نقوم بعملية قسمة المجموع على عدد المرات و نحصل على نفس النتيجة 4.2 , و ستصبح المعادلة بشكلها النهائي

=SUM(A1:A4,D4:D7,G1:G4)/INDEX(FREQUENCY((A1:A4,D4:D7,G1:G4),{0}),2)

==========================================

أتمنى لكم المتعه و الفائدة