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

فكرة التدوينة اليوم عن إستخراج المتوسط الحسابي لنطاق معين بإستثناء القيم المكررة, فلقد قدمنا في التدوينات السابقة عدة أفكار:

1) حساب المتوسط مع إستثناء القيم المساوية لصفر على هذا الرابط

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

2) حساب المتوسط في نطاقات متباعدة و بإستثناء القيم المساوية على هذا الرابط

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

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

استاذنا الكبير تحية طيبه في الوقت الذي اشكركم فيه على هذا التفاني في ايصال المعلومة والفكرة بهذا الشكل الرائع اتساءل عن كيفية استخدام اي من المعادلات الخاصة باصدار 2003 لايجاد المتوسط الحسابي في حالة وجود ارقام مكررة ويجب استثناء المكرر من الجمع والقسمة مع الامتنان

و لتوضيح الفكرة أكثر لو إفترضنا وجود البيانات التالية:

فعند إستخدام معادلة المتوسط الحسابي العادي بالمعادلة التالية:

=AVERAGE(A1:A10)

سيكون الناتج 2.8 و ذلك بسبب حساب القيم المكررة

و ما نريده من معادلتنا ان يتم إستثناء القيم المكررة , و لعمل ذلك سنستخدم المعادلة التالية : ( و هي معادلة صفيف يجب الضغط علىCtrl+Shift+Enter)

=AVERAGE(IF(FREQUENCY(IF(A1:A10<>”",MATCH(A1:A10,A1:A10,0)),ROW(A1:A10)-ROW(A1)+1),A1:A10))

و لفهم آلية عمل المعادلة أعلاه:

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

{1;1;3;3;1;6;7;1;6;7}

و الذي سينتج نفس الأرقام مع إرتباطه بالدالةIf

و من ثم سيمثل الصفيف أعلاه الجزء الأول من

data_array

للمعادلة

FREQUENCY

و التي ستقوم بحساب التوزيع التكراري بناء على

bins_array

و التي سنحددها من خلال بناء تسلسل رقمي بالجزء المتعلق بها من خلال:

ROW(A1:A10)-ROW(A1)+1

و التي ستبني لنا تسلسل بالشكل التالي:

{1;2;3;4;5;6;7;8;9;10}

و عند ربط جزئي المعادلة سينتج الرقم التالي:

{4;0;2;0;0;2;2;0;0;0;0}

و من ثم سيأتي دور المعادلة الثانية IFو التي ستقوم بعملية تقييم TRUE and FALSE

و ربطهما مع جزء التحقق في حالة TRUE

لينتج لدينا الصفيف التالي :

{3;FALSE;2;FALSE;FALSE;1;5;FALSE;FALSE;FALSE;FALSE}

و هنا تكون المرجلة الأخيرة التي سيتم بعدها إستخدام الدالة

AVERAGE

بالشكل التالي :

=AVERAGE({3;FALSE;2;FALSE;FALSE;1;5;FALSE;FALSE;FALSE;FALSE})

و سيكون الناتج النهائي لهذه المعادلة 2.75

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

أتمنى أن أكون قد وفقت في شرح هذه الحالة

دمتم في حفظ الله