إكسل: كيفية حساب المتوسط باستثناء القيم المتطرفة

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

1. احسب المتوسط واستخدم TRIMMEAN لاستبعاد القيم المتطرفة

2. احسب المتوسط واستخدم المدى الربيعي لاستبعاد القيم المتطرفة

سنستخدم مجموعة البيانات التالية في Excel لتوضيح كيفية استخدام كلتا الطريقتين:

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

يمكن استخدام الدالة TRIMMEAN في Excel لحساب متوسط نطاق من القيم مع استبعاد نسبة معينة من الملاحظات من أعلى وأسفل مجموعة البيانات.

على سبيل المثال، يمكننا استخدام الصيغة التالية لحساب متوسط القيمة في العمود A باستثناء إجمالي 20% من الملاحظات (أعلى 10% وأدنى 10%):

 =TRIMMEAN( A2:A16 , 20%)

بما أن لدينا 15 قيمة في مجموعة البيانات لدينا، 10% تساوي 1.5، وهي مقربة إلى 1. لذلك ستحسب هذه الصيغة متوسط القيم في النطاق مع استبعاد أصغر قيمة وأكبر قيمة:

المتوسط، باستثناء القيم المتطرفة، تبين أنه 58.30769 .

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

النطاق الربيعي (IQR) هو الفرق بين المئين الخامس والسبعين (Q3) والمئين الخامس والعشرين (Q1) في مجموعة البيانات. وهو يقيس توزيع متوسط 50% من القيم.

يمكننا تعريف الملاحظة على أنها قيمة متطرفة إذا كانت 1.5 مرة من المدى الربيعي فوق الربع الثالث (Q3) أو 1.5 مرة من المدى الربيعي أسفل الربع الأول (Q1).

يمكننا استخدام الصيغة التالية لحساب النطاق الربعي لمجموعة البيانات الخاصة بنا في Excel:

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

توضح لقطة الشاشة التالية كيفية استخدام هذه الصيغة:

ثم يمكننا استخدام الصيغة التالية لاستخدام IQR لتحديد القيم المتطرفة وتعيين “1” لأي قيمة تعتبر قيمة متطرفة في مجموعة البيانات:

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

توضح لقطة الشاشة التالية كيفية استخدام هذه الصيغة:

نرى أن قيمة واحدة فقط – 164 – تبين أنها قيمة شاذة في مجموعة البيانات هذه.

أخيرًا، يمكننا استخدام الصيغة التالية لحساب متوسط جميع القيم في مجموعة البيانات التي لا تعتبر قيمًا متطرفة:

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

توضح لقطة الشاشة التالية كيفية استخدام هذه الصيغة:

المتوسط، باستثناء القيم المتطرفة، تبين أنه 55.42857 .

مصادر إضافية

تشرح البرامج التعليمية التالية كيفية تنفيذ المهام الشائعة الأخرى في Excel:

كيفية حساب المدى الربيعي (IQR) في برنامج إكسل
كيفية حساب المتوسط حسب المجموعة في إكسيل
كيفية حساب المتوسطات المتحركة المرجحة في إكسيل