كيف (ولماذا) استخدام وظيفة القيم المتطرفة في Excel

القيم الخارجية هي قيمة أعلى أو أقل بكثير من معظم القيم في بياناتك. عند استخدام Excel لتحليل البيانات ، يمكن للقيم المتطرفة أن تحرف النتائج. على سبيل المثال ، قد يعكس متوسط ​​مجموعة البيانات قيمك حقًا. يوفر Excel بعض الوظائف المفيدة للمساعدة في إدارة القيم المتطرفة ، لذلك دعونا نلقي نظرة.

مثال سريع

في الصورة أدناه ، من السهل تحديد القيم المتطرفة - قيمة اثنين مخصصة لإريك وقيمة 173 المخصصة لريان. في مجموعة مثل هذه البيانات ، من السهل تحديد القيم المتطرفة والتعامل معها يدويًا.

في مجموعة أكبر من البيانات ، لن يكون هذا هو الحال. من المهم أن تكون قادرًا على تحديد القيم المتطرفة وإزالتها من الحسابات الإحصائية - وهذا ما سننظر في كيفية القيام به في هذه المقالة.

كيف تجد القيم المتطرفة في بياناتك

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

1. احسب الربعين الأول والثالث (سنتحدث عن ماهية هذين الربعين قليلاً).
2. قم بتقييم المدى الربيعي (سنشرح هذه الأمور أكثر قليلاً).
3. قم بإرجاع الحدين العلوي والسفلي لنطاق بياناتنا.
4. استخدم هذه الحدود لتعريف نقاط البيانات البعيدة.

سيتم استخدام نطاق الخلايا الموجود على يمين مجموعة البيانات الموضحة في الصورة أدناه لتخزين هذه القيم.

هيا بنا نبدأ.

الخطوة الأولى: حساب الربعية

إذا قمت بتقسيم بياناتك إلى أرباع ، فإن كل مجموعة من هذه المجموعات تسمى الربع. يشكل أقل 25٪ من الأرقام في النطاق الربيع الأول ، والربع الثاني 25٪ التالي ، وهكذا. نتخذ هذه الخطوة أولاً لأن التعريف الأكثر استخدامًا للعناصر الخارجية هو نقطة البيانات التي تكون أكثر من 1.5 نطاقات رُبعية (IQR) أقل من الربع الأول ، و 1.5 نطاقات رُبعية أعلى من الربع الثالث. لتحديد هذه القيم ، علينا أولًا معرفة ما هي الربعية.

يوفر Excel دالة QUARTILE لحساب الرباعية. يتطلب جزأين من المعلومات: المصفوفة والربع.

= QUARTILE (مصفوفة ، ربع)

في مجموعة هي مجموعة من القيم التي يتم تقييم. و رابعا هو الرقم الذي يمثل الربع كنت ترغب في عودة (على سبيل المثال، 1 لالربع 1ST، (2) لالربع 2nd، و هلم جرا).

ملاحظة: في Excel 2010 ، أصدرت Microsoft الدالتين QUARTILE.INC و QUARTILE.EXC كتحسينات على وظيفة QUARTILE. QUARTILE أكثر توافقًا مع الإصدارات السابقة عند العمل عبر إصدارات متعددة من Excel.

لنعد إلى جدول المثال الخاص بنا.

لحساب الربع الأول ، يمكننا استخدام الصيغة التالية في الخلية F2.

= QUARTILE (B2: B14،1)

أثناء إدخال الصيغة ، يوفر Excel قائمة بالخيارات للوسيطة الرباعية.

لحساب الربع الثالث ، يمكننا إدخال صيغة مثل السابقة في الخلية F3 ، ولكن باستخدام ثلاثة بدلاً من واحد.

= QUARTILE (B2: B14،3)

الآن ، لدينا نقاط البيانات الربعية معروضة في الخلايا.

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

النطاق الربيعي (أو IQR) هو متوسط ​​50٪ من القيم في بياناتك. يتم حسابه على أنه الفرق بين قيمة الربع الأول وقيمة الربع الثالث.

سنستخدم صيغة بسيطة في الخلية F4 تطرح الربع الأول من الربع الثالث:

= F3-F2

الآن ، يمكننا أن نرى النطاق الربيعي معروضًا.

الخطوة الثالثة: أعد الحدين السفلي والعلوي

الحدود الدنيا والعليا هي أصغر وأكبر قيم نطاق البيانات التي نريد استخدامها. أي قيم أصغر أو أكبر من هذه القيم المقيدة هي القيم المتطرفة.

سنحسب الحد الأدنى في الخلية F5 بضرب قيمة IQR بمقدار 1.5 ثم نطرحها من نقطة بيانات Q1:

= F2- (1.5 * F4)

ملاحظة: الأقواس في هذه الصيغة ليست ضرورية لأن جزء الضرب سيحسب قبل جزء الطرح ، لكنها تجعل الصيغة أسهل في القراءة.

لحساب الحد الأعلى في الخلية F6 ، سنقوم بضرب معدل الذكاء IQR بمقدار 1.5 مرة أخرى ، ولكن هذه المرة نضيفه إلى نقطة بيانات Q3:

= F3 + (1.5 * F4)

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

الآن بعد أن تم إعداد جميع بياناتنا الأساسية ، حان الوقت لتحديد نقاط البيانات البعيدة - تلك التي تكون أقل من قيمة الحد الأدنى أو أعلى من قيمة الحد الأعلى.

سنستخدم الدالة OR لإجراء هذا الاختبار المنطقي وإظهار القيم التي تلبي هذه المعايير عن طريق إدخال الصيغة التالية في الخلية C2:

= أو (B2 $ F $ 6)

سنقوم بعد ذلك بنسخ هذه القيمة في خلايا C3-C14 الخاصة بنا. تشير القيمة TRUE إلى قيمة خارجية ، وكما ترى ، لدينا اثنان في بياناتنا.

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

باستخدام وظيفة QUARTILE ، دعنا نحسب معدل الذكاء ونعمل مع التعريف الأكثر استخدامًا للنافذة. ومع ذلك ، عند حساب المتوسط ​​المتوسط ​​لمجموعة من القيم وتجاهل القيم المتطرفة ، هناك وظيفة أسرع وأسهل للاستخدام. لن تحدد هذه التقنية حالة خارجية كما كانت من قبل ، ولكنها ستتيح لنا التحلي بالمرونة مع ما قد نعتبره الجزء الخارجي.

الوظيفة التي نحتاجها تسمى TRIMMEAN ، ويمكنك رؤية بناء الجملة الخاص بها أدناه:

= TRIMMEAN (مجموعة ، في المائة)

في مجموعة هي مجموعة من القيم التي تريد المتوسط. و في المئة هي النسبة المئوية من نقاط البيانات استبعادها من الجزء العلوي والسفلي من مجموعة البيانات (يمكنك إدخاله كنسبة مئوية أو قيمة عشري).

أدخلنا الصيغة أدناه في الخلية D3 في مثالنا لحساب المتوسط ​​واستبعاد 20٪ من القيم المتطرفة.

= TRIMMEAN (B2: B14، 20٪)

هناك وظيفتان مختلفتان للتعامل مع القيم المتطرفة. سواء كنت تريد تحديدها لبعض احتياجات إعداد التقارير أو استبعادها من الحسابات مثل المتوسطات ، فإن Excel لديه وظيفة تناسب احتياجاتك.