כיצד (ולמה) להשתמש בפונקציה Outliers ב- Excel

חריגה היא ערך הגבוה או נמוך משמעותית מרוב הערכים בנתונים שלך. כאשר משתמשים ב- Excel לניתוח נתונים, חריגים יכולים להטות את התוצאות. לדוגמה, הממוצע הממוצע של קבוצת נתונים עשוי לשקף באמת את הערכים שלך. Excel מספק כמה פונקציות שימושיות שיעזרו לך לנהל את החריגים שלך, אז בוא נסתכל.

דוגמה מהירה

בתמונה למטה קל לזהות את החריגים - הערך של שניים שהוקצו לאריק והערך של 173 שהוקצה לריאן. במערכת נתונים כזו, קל מספיק לזהות ולהתמודד עם אותם חריגים באופן ידני.

במערך נתונים גדול יותר, זה לא יהיה המקרה. היכולת לזהות את החריגים ולהסיר אותם מחישובים סטטיסטיים חשובה - וזה מה שנבדוק כיצד לעשות במאמר זה.

כיצד למצוא חריגים בנתונים שלך

כדי למצוא את החריגים במערכת נתונים, אנו משתמשים בשלבים הבאים:

  1. חשב את הרביעיות הראשונה והשלישית (נדבר רק על מה מדובר).
  2. הערך את הטווח הבין-רבעוני (נסביר אותם קצת יותר למטה).
  3. החזר את הגבול העליון והתחתון של טווח הנתונים שלנו.
  4. השתמש בגבולות אלה כדי לזהות את נקודות הנתונים המרוחקות.

טווח התאים בצד ימין של מערך הנתונים שנראה בתמונה למטה ישמש לאחסון ערכים אלה.

בוא נתחיל.

שלב ראשון: חישוב הרביעיות

אם אתה מחלק את הנתונים שלך לרבעים, כל אחת מהקבוצות האלה נקראת רביעייה. 25% המספרים הנמוכים ביותר בטווח מהווים את הרבעון הראשון, את 25% הבאים את הרבעון השני, וכן הלאה. אנו ננקוט צעד זה תחילה מכיוון שההגדרה הנפוצה ביותר של חריגה היא נקודת נתונים הנמצאת ביותר מ -1.5 טווחי בין-רביעיים (IQR) מתחת לרבעון הראשון, ו -1.5 טווחים בין-רבעוניים מעל לרבע השלישי. כדי לקבוע את הערכים האלה, ראשית עלינו להבין מה הם הרבעונים.

Excel מספק פונקציה QUARTILE לחישוב רביעיות. זה דורש שתי פיסות מידע: המערך והרביע.

= QUARTILE (מערך, רביעית)

המערך הוא הטווח של ערכים שאתה הערכת. וגם הליטר הוא מספר המייצג את הרבעון ברצונך חזרה (למשל, 1 עבור הרבעון ה -1, 2 עבור הרבעון ה -2, וכן הלאה).

הערה: ב- Excel 2010, מיקרוסופט פרסמה את הפונקציות QUARTILE.INC ו- QUARTILE.EXC כשיפורים בפונקציה QUARTILE. QUARTILE תואם יותר לאחור כאשר עובדים על פני מספר גרסאות של Excel.

נחזור לטבלת הדוגמה שלנו.

כדי לחשב את הרבעון הראשון נוכל להשתמש בנוסחה הבאה בתא F2.

= רבעון (B2: B14,1)

כשנכנסים לנוסחה, Excel מספק רשימת אפשרויות לטיעון הקוורט.

כדי לחשב את הרבעון השלישי, אנו יכולים להזין נוסחה כמו הקודמת בתא F3, אך באמצעות שלוש במקום אחת.

= רבעון (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:

= OR (B2 $ F $ 6)

לאחר מכן נעתיק את הערך הזה לתאי C3-C14 שלנו. ערך TRUE מציין חריג, וכפי שאתה יכול לראות, יש לנו שניים בנתונים שלנו.

התעלמות מהמצוינים בחישוב הממוצע הממוצע

באמצעות פונקציית QUARTILE נניח לחשב את ה- IQR ולעבוד עם ההגדרה הנפוצה ביותר של חריגה. עם זאת, כאשר מחשבים את הממוצע הממוצע עבור טווח ערכים ומתעלמים מחריגים, יש פונקציה מהירה וקלה יותר לשימוש. טכניקה זו לא תזהה חריגה כבעבר, אך תאפשר לנו להיות גמישים עם מה שנחשיב לחלק החריג שלנו.

הפונקציה שאנו זקוקים לה נקראת TRIMMEAN, ותוכלו לראות את התחביר עבורה למטה:

= TRIMMEAN (מערך, אחוז)

המערך הוא הטווח של ערכים שאתה רוצה ממוצע. אחוז הוא אחוז נקודות נתונים להוציא מהחלק העליון והתחתון של קבוצת נתונים (תוכל להזין אותו כאחוז או ערך עשרוני).

הכנסנו את הנוסחה למטה לתא D3 בדוגמה שלנו כדי לחשב את הממוצע ולא לכלול 20% מהחריגים.

= TRIMMEAN (B2: B14, 20%)

שם יש לך שתי פונקציות שונות לטיפול בחריגים. בין אם ברצונך לזהות אותם לצרכי דיווח מסוימים ובין אם לא לכלול אותם בחישובים כגון ממוצעים, ל- Excel יש פונקציה שתתאים לצרכים שלך.