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

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

דוגמה מהירה

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

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

כיצד למצוא outliers בנתונים שלך

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

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

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

בואו נתחיל.

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

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

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

= QUARTILE (מערך, quart)

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

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

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

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

= QUARTILE (B2: B14,1)

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

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

= QUARTILE (B2: B14,3)

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

שלב שני: הערכת טווח בין-רבעוני

הטווח הבין-רבעוני (או IQR) הוא באמצע 50% מהערכים הנתונים שלך. הוא מחושב כהפרש בין ערך הרביע הראשון לבין הערך הרבעוני השלישי.

אנחנו הולכים להשתמש בנוסחה פשוטה לתוך התא F4 כי מחסר את 1^רחוב רבעון מ 3^{מחקר ופיתוח} רביע:

= F3-F2

עכשיו, אנחנו יכולים לראות את טווח הבין רבעוני שלנו מוצג.

שלב שלישי: החזרת הגבולות התחתונים והעליונים

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

נחשב את גבול הגבול התחתון בתא F5 על ידי הכפלת ערך IQR ב -1.5 ולאחר מכן חיסור אותו מנקודת הנתונים של Q1:

= F2- (1.5 * F4)

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

כדי לחשב את הגבול העליון בתא F6, נכפיל את IQR ב -1.5 שוב, אבל הפעם הוסף זה נקודת הנתונים Q3:

= F3 + (1.5 * F4)

שלב רביעי: זהה את Outliers

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

נשתמש בפונקציה OR כדי לבצע בדיקה לוגית זו ונציג את הערכים העומדים בקריטריונים אלה על ידי הזנת הנוסחה הבאה לתא C2:

= OR (B2 $ F $ 6)

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

התעלמות מן החוץ בעת חישוב ממוצע ממוצע

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

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

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

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

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

= TRIMMEAN (B2: B14, 20%)

---

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