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

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

في التدوينة السابقة
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)

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




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