כאשר אתה עובד עם נתונים ב- Excel, לעיתים קרובות יהיו לך בעיות הטיפול במכשירי חריגה במערך הנתונים שלך.
קיום חריגים הוא די נפוץ בכל מיני נתונים, וחשוב לזהות ולהתייחס לנתונים אלה כדי לוודא שהניתוח שלך נכון ומשמעותי יותר.
במדריך זה, אראה לך כיצד למצוא חריגים ב- Excel, וכמה מהטכניקות שבהן השתמשתי בעבודתי להתמודד עם חריגים אלה.
מהם יוצאי דופן ומדוע חשוב למצוא אותם?
חריג הוא נקודת נתונים שהיא הרבה מעבר לנקודות הנתונים האחרות במערך הנתונים. כשיש לך חריג בנתונים, זה יכול להטות את הנתונים שלך מה שעלול להוביל למסקנות שגויות.
תן לי לתת לך דוגמא פשוטה.
נניח 30 אנשים נוסעים באוטובוס מיעד A ליעד B. כל האנשים נמצאים בקבוצת משקל וקבוצת הכנסה דומה. לצורך הדרכה זו, הבה נבחן את המשקל הממוצע ל -220 פאונד ואת ההכנסה השנתית הממוצעת ל -70,000 דולר.
עכשיו אי שם באמצע המסלול שלנו, האוטובוס עוצר, וביל גייטס קופץ פנימה.
עכשיו, מה אתה חושב שזה יעשה למשקל הממוצע ולהכנסה הממוצעת של האנשים באוטובוס.
המשקל הממוצע אמנם לא ישתנה הרבה, אך ההכנסה הממוצעת של האנשים באוטובוס הולכת להרקיע שחקים בכבדות.
הסיבה לכך היא שההכנסה של ביל גייטס היא חריגה בקבוצה שלנו, וזה נותן לנו פרשנות שגויה של הנתונים. ההכנסה הממוצעת לכל אדם באוטובוס תהיה כמה מיליארדי דולרים, וזה הרבה מעבר לערך האמיתי.
כאשר עובדים עם מערכי נתונים בפועל ב- Excel, יכולים להיות לכם חריגים לכל כיוון (כלומר, חריג חיובי או חריג שלילי).
וכדי לוודא שהניתוח שלך נכון, אתה צריך איכשהו לזהות את החריגים האלה ואז להחליט כיצד לטפל בהם בצורה הטובה ביותר.
עכשיו בואו נראה כמה דרכים למצוא חריגים ב- Excel.
מצא את יוצאי הדופן על ידי מיון הנתונים
עם מערכי נתונים קטנים, דרך מהירה לזהות יוצאי דופן היא פשוט למיין את הנתונים ולעבור ידנית על חלק מהערכים בחלק העליון של הנתונים הממוינים האלה.
ומכיוון שיכולים להיות חריגים לשני הכיוונים, הקפד למיין את הנתונים קודם בסדר עולה ולאחר מכן בסדר יורד ולאחר מכן לעבור על הערכים העליונים.
תן לי להראות לך דוגמא.
למטה יש לי מערך נתונים שבו יש לי משך שיחות (בשניות) ל -15 שיחות לשירות לקוחות.
להלן השלבים למיון נתונים אלה כך שנוכל לזהות את יוצאי הדופן במערך הנתונים:
- בחר בכותרת העמודה של העמודה שברצונך למיין (תא B1 בדוגמה זו)
- לחץ על הכרטיסייה דף הבית
- בקבוצת העריכה, לחץ על הסמל מיון וסינון.
- לחץ על מיון מותאם אישית
- בתיבת הדו-שיח מיון, בחר 'משך זמן' בתפריט הנפתח 'מיין לפי' ו'גדול עד הקטן ביותר 'בתפריט הנפתח' הזמנה '.
- לחץ על אישור
השלבים שלעיל ימיין את העמודה משך השיחה עם הערכים הגבוהים ביותר בחלק העליון. כעת תוכל לסרוק את הנתונים ידנית ולראות אם ישנם חריגים.
בדוגמה שלנו, אני יכול לראות ששני הערכים הראשונים גבוהים בהרבה משאר הערכים (והשניים התחתונים נמוכים בהרבה).
הערה: שיטה זו פועלת עם מערכי נתונים קטנים בהם ניתן לסרוק את הנתונים באופן ידני. זו לא שיטה מדעית אבל עובדת טוב
מציאת יוצאי דופן באמצעות פונקציות הרבעון
עכשיו בואו נדבר על פתרון מדעי יותר שיכול לעזור לך לזהות אם יש חריגים או לא.
בסטטיסטיקה, רבעון הוא רבע ממערך הנתונים. לדוגמה, אם יש לך 12 נקודות נתונים, הרי שהרבעון הראשון יהיה שלוש נקודות הנתונים התחתונות, הרביעון השני יהיה שלוש נקודות הנתונים הבאות וכן הלאה.
להלן מערך הנתונים שבו אני רוצה למצוא את החריגים. לשם כך אצטרך לחשב את הרבעון הראשון והשלישי, ולאחר מכן להשתמש בו לחשב את הגבול העליון והתחתון.
להלן הנוסחה לחישוב הרביעון הראשון בתא E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
והנה זה לחישוב הרביעון השלישי בתא E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
כעת, אני יכול להשתמש בשני החישובים שלעיל כדי לקבל את טווח הבין -רבעוני (שהוא 50% מהנתונים שלנו בתוך הרבעון הראשון והשלישי)
= F3-F2
כעת נשתמש בטווח הבין -רבעוני כדי למצוא את הגבול התחתון והעליון שיכיל את רוב הנתונים שלנו.
כל דבר הנמצא מחוץ לגבולות התחתונים והעליונים האלה ייחשב אז כבלתי חריג.
להלן הנוסחה לחישוב הגבול התחתון:
= רבעון 1 - 1.5*(טווח בין רבעון)
מה שבדוגמה שלנו הופך להיות:
= F2-1.5*F4
והנוסחה לחישוב הגבול העליון היא:
= Quartile3 + 1.5*(טווח בין רבעון)
מה שבדוגמה שלנו הופך להיות:
= F3+1.5*F4
כעת, כשיש לנו את הגבול העליון והתחתון במערך הנתונים שלנו, נוכל לחזור לנתונים המקוריים ולזהות במהירות את אותם ערכים שאינם נמצאים בטווח זה.
דרך מהירה לעשות זאת היא לבדוק כל ערך ולהחזיר TRUE או FALSE בעמודה חדשה.
השתמשתי בנוסחת OR למטה כדי לקבל TRUE עבור הערכים שהם חריגים.
= או (B2 $ F $ 6)
כעת תוכל לסנן את העמודה Outlier ולהציג רק את הרשומות בהן הערך TRUE.
לחלופין, תוכל גם להשתמש בעיצוב מותנה כדי להדגיש את כל התאים שבהם הערך הוא TRUE
הערה: זוהי אמנם שיטה מקובלת יותר לאיתור חריגים בסטטיסטיקה. אני מוצא את השיטה הזו מעט בלתי שימושית בתרחישים אמיתיים. בדוגמה לעיל, הגבול התחתון המחושב לפי הנוסחה הוא -103, בעוד שמערך הנתונים שיש לנו יכול להיות חיובי בלבד. אז שיטה זו יכולה לעזור לנו למצוא יוצאי דופן בכיוון אחד (ערכים גבוהים), היא חסרת תועלת בזיהוי חריגים בכיוון השני.מציאת חריגים באמצעות הפונקציות LARGE/SMALL
אם אתה עובד עם הרבה נתונים (ערכים במספר עמודות), תוכל לחלץ את 5 או 7 הערכים הגדולים והקטנים ביותר ולראות אם יש בו חריגים.
אם יש חריגים, תוכל לזהות אותם מבלי שתצטרך לעבור על כל הנתונים לשני הכיוונים.
נניח שיש לנו את מערך הנתונים שלהלן ואנחנו רוצים לדעת אם יש חריגים.
להלן הנוסחה שתיתן לך את הערך הגדול ביותר במערך הנתונים:
= LARGE ($ B $ 2: $ B $ 16,1)
באופן דומה, הערך השני בגודלו יינתן על ידי
= LARGE ($ B $ 2: $ B $ 16,1)
אם אינך משתמש ב- Microsoft 365, הכולל מערכים דינמיים, תוכל להשתמש בנוסחה שלהלן והיא תתן לך את חמשת הערכים הגדולים ביותר ממערך הנתונים עם נוסחה אחת אחת:
= LARGE ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
באופן דומה, אם אתה רוצה את 5 הערכים הקטנים ביותר, השתמש בנוסחה שלהלן:
= קטן ($ B $ 2: $ B $ 16, שורה ($ 1: 5))
או את הדברים הבאים במקרה שאין לך מערכים דינמיים:
= קטן ($ B $ 2: $ B $ 16,1)
ברגע שיש לך את הערכים האלה, ממש קל לגלות חריגים במערך הנתונים.
למרות שבחרתי לחלץ את 5 הערכים הגדולים והקטנים ביותר, אתה יכול לבחור לקבל 7 או 10 על סמך גודל הנתונים שלך.
אני לא בטוח אם זו שיטה מקובלת לאיתור חריגים ב- Excel או לא, אבל זו השיטה בה השתמשתי כאשר נאלצתי לעבוד עם נתונים פיננסיים רבים בעבודתי לפני מספר שנים. בהשוואה לכל שאר השיטות המכוסות במדריך זה, מצאתי שהשיטה הזו היא היעילה ביותר.
כיצד להתמודד עם יוצאי דופן בדרך הנכונה
עד כה ראינו את השיטות שיעזרו לנו למצוא את החריגים במערך הנתונים שלנו. אבל מה לעשות ברגע שאתה יודע שישנם חריגים.
להלן מספר שיטות בהן תוכל להשתמש כדי להתמודד עם חריגים כך שניתוח הנתונים שלך יהיה נכון.
מחק את יוצאי הדופן
הדרך הקלה ביותר להסיר חריגים ממערך הנתונים שלך היא פשוט למחוק אותם. כך זה לא יטות את הניתוח שלך.
זהו פתרון משתלם יותר כאשר יש לך מערכי נתונים גדולים ומחיקת כמה חריגים לא תשפיע על הניתוח הכולל. וכמובן, לפני מחיקת הנתונים וודא שאתה יוצר עותק ומתעמק במה שגורם לשפלות אלו.
לנרמל את חריגים (התאם את הערך)
נורמליזציה של יוצאי הדופן היא מה שהייתי עושה כשהייתי במשרה מלאה. עבור כל הערכים החריגים, פשוט הייתי משנה אותם לערך שהוא מעט גבוה מהערך המרבי במערך הנתונים.
זה מוודא שאני לא מוחק את הנתונים אך יחד עם זאת אני לא נותן לזה להטות את הנתונים שלי.
כדי לתת לך דוגמה ממציאות, אם אתה מנתח את שולי הרווח הנקי של חברות, בהן רוב החברות נמצאות בטווח של -10%עד 30%, ויש כמה ערכים שהם למעלה מ -100%, אני פשוט היה משנה את ערכי החריגה האלה ל -30% או 35%.
אז אלו חלק מהשיטות בהן תוכל להשתמש Excel לאיתור חריגים.
לאחר שזיהית את יוצאי הדופן, תוכל להתעמק בנתונים ולחפש מה גורם לכך, במקביל לבחור באחת הטכניקות להתמודדות עם חריגים אלה (שיכולה להסיר אותם או לנרמל אותם על ידי התאמת הערך)
אני מקווה שמצאת הדרכה שימושית.
