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

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

مثال سريع

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

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

كيفية البحث عن القيم المتطرفة في البيانات الخاصة بك

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

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

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

هيا بنا نبدأ.

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

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

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

= QUARTILE (صفيف ، رباعي)

ال مجموعة مصفوفة هو نطاق القيم التي تقوم بتقييمها. و ال رابعا هو رقم يمثل الشريحة الربعية التي ترغب في العودة إليها (على سبيل المثال ، الرقم 1 للنسبة 1^شارع ربع ، 2 للربيع الثاني ، وهكذا).

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

دعنا نعود إلى جدول الأمثلة.

لحساب 1^شارع الربعية يمكننا استخدام الصيغة التالية في الخلية F2.

= QUARTILE (B2: B14،1)

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

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

= QUARTILE (B2: B14،3)

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

الخطوة الثانية: تقييم نطاق Interquartile

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

سنستخدم صيغة بسيطة في الخلية F4 تطرح الرقم 1^شارع ربع من 3^{الثالثة} الربع:

= F3-F2

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

الخطوة الثالثة: إرجاع الحدود السفلى والعليا

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

سنقوم بحساب الحد المجرد الأدنى في الخلية F5 بضرب قيمة معدل الذكاء IQ بمقدار 1.5 ثم طرحها من نقطة بيانات Q1:

= F2- (1.5 * F4)

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

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

= F3 + (1.5 * F4)

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

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

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

= OR (B2 $ F $ 6)

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

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

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

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

= TRIMMEAN (مصفوفة ، النسبة المئوية)

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

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

= TRIMMEAN (B2: B14 ، 20٪)

---

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