חישוב רווח סמך באקסל

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

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

חישוב רווחי סמך

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

קיימות שתי שיטות לביצוע משימה זו ב-Microsoft Excel:

• מַפעִיל נורמת אמון - משמש במקרים בהם הפיזור ידוע;
• מַפעִיל אמון.תלמידכאשר השונות אינה ידועה.

להלן ננתח צעד אחר צעד את שתי השיטות בפועל.

שיטה 1: הצהרת TRUST.NORM

פונקציה זו הוצגה לראשונה בארסנל של התוכנית במהדורת Excel 2010 (לפני גרסה זו, היא הוחלפה על ידי האופרטור "מהימן"). המפעיל נכלל בקטגוריית "סטטיסטיקה".

נוסחת פונקציה נורמת אמון נראה ככה:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

כפי שאנו יכולים לראות, לפונקציה יש שלושה ארגומנטים:

• "אלפא" מהווה אינדיקטור לרמת המובהקות, הנלקחת כבסיס לחישוב. רמת האמון מחושבת באופן הבא:
  • 1-"Альфа". ביטוי זה ישים אם הערך "אלפא" מוצג כמקדם. לדוגמה, 1-0,7 0,3 =, כאשר 0,7=70%/100%.
  • (100-"Альфа")/100. ביטוי זה ייושם אם ניקח בחשבון את רמת הביטחון עם הערך "אלפא" באחוזים. לדוגמה, (100-70) / 100 = 0,3.
• "סטיית תקן" – בהתאמה, סטיית התקן של מדגם הנתונים המנותח.
• "גודל" הוא גודל מדגם הנתונים.

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

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

נוסחת פונקציה אמון באופן הבא:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

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

גבול רווחי הסמך נקבע על ידי הנוסחה הבאה:

X+(-)ДОВЕРИТ.НОРМ

איפה Х הוא הערך הממוצע בטווח שצוין.

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

המשימה שלנו היא להשיג את הערך של רווח הסמך עם רמת ביטחון של 95%.

1. קודם כל, בחר תא כדי להציג את התוצאה. לאחר מכן אנו לוחצים על הכפתור "הכנס פונקציה" (משמאל לשורת הנוסחאות).
2. חלון אשף הפונקציות נפתח. בלחיצה על קטגוריית הפונקציות הנוכחית, הרחב את הרשימה ולחץ על השורה בה "סטָטִיסטִי".
3. ברשימה המוצעת, לחץ על המפעיל "נורם אמון", ולאחר מכן לחץ על OK.
4. נראה חלון עם ההגדרות של הארגומנטים של הפונקציה, מילוי אותו נלחץ על הכפתור OK.
   • בתחום "אלפא" לציין את רמת המשמעות. המשימה שלנו מניחה רמת ביטחון של 95%. החלפת ערך זה בנוסחת החישוב, עליה שקלנו לעיל, נקבל את הביטוי: (100-95)/100. נכתוב את זה בשדה הארגומנט (או שאתה יכול מיד לכתוב את תוצאת החישוב שווה ל-0,05).
   • בתחום "std_off" לפי התנאים שלנו, אנו כותבים את המספר 8.
   • בשדה "גודל", ציין את מספר האלמנטים שיש לבחון. במקרה שלנו בוצעו 10 מדידות, אז אנחנו כותבים את המספר 10.
5. כדי להימנע מהצורך להגדיר מחדש את הפונקציה כאשר הנתונים משתנים, אתה יכול להפוך אותה לאוטומטית. לשם כך אנו משתמשים בפונקציה "חשבון". שים את המצביע באזור הקלט של מידע הארגומנט "גודל", לאחר מכן לחץ על סמל המשולש בצד שמאל של שורת הנוסחאות ולחץ על הפריט "תכונות נוספות...".
6. כתוצאה מכך, ייפתח חלון נוסף של אשף הפונקציות. על ידי בחירת קטגוריה "סטָטִיסטִי", לחץ על הפונקציה "חשבון", אז בסדר.
7. המסך יציג חלון נוסף עם הגדרות הארגומנטים של הפונקציה, המשמש לקביעת מספר התאים בטווח נתון המכילים נתונים מספריים.

   נוסחת פונקציה לבדוק זה כתוב כך: =СЧЁТ(Значение1;Значение2;...).

   מספר הארגומנטים הזמינים עבור פונקציה זו יכול להיות עד 255. כאן אתה יכול לכתוב מספרים ספציפיים, או כתובות תאים, או טווחי תאים. נשתמש באפשרות האחרונה. כדי לעשות זאת, לחץ על אזור קלט המידע עבור הארגומנט הראשון, ולאחר מכן, החזק את לחצן העכבר השמאלי לחוץ, בחר את כל התאים של אחת העמודות של הטבלה שלנו (לא סופר את הכותרת), ולאחר מכן לחץ על הכפתור OK.

8. כתוצאה מהפעולות שננקטו, תוצאת החישובים של המפעיל תוצג בתא הנבחר נורמת אמון. בבעיה שלנו, התברר שהערך שלו שווה ל 4,9583603.
9. אבל זו עדיין לא התוצאה הסופית במשימה שלנו. לאחר מכן, עליך לחשב את הערך הממוצע על פני מרווח נתון. כדי לעשות זאת, עליך להשתמש בפונקציה "לֵב"A שמבצע את המשימה של חישוב הממוצע על פני טווח נתונים מוגדר.

   נוסחת האופרטור כתובה כך: =СРЗНАЧ(число1;число2;...).

   בחר את התא שבו אנו מתכננים להכניס את הפונקציה ולחץ על הכפתור "הכנס פונקציה".

10. בקטגוריה "סטָטִיסטִי" בחר מפעיל משעמם "לֵב" ולחץ על OK.
11. בפונקציה ארגומנטים בערך ארגומנט "מספר" ציין את הטווח, הכולל את כל התאים עם הערכים של כל המדידות. ואז אנחנו לוחצים בסדר.
12. כתוצאה מהפעולות שננקטו, הערך הממוצע יחושב אוטומטית ויוצג בתא עם הפונקציה החדשה שהוכנסה.
13. כעת עלינו לחשב את גבולות ה-CI (רווח סמך). נתחיל בחישוב הערך של הגבול הימני. אנו בוחרים את התא שבו אנו רוצים להציג את התוצאה, ומבצעים את הוספת התוצאות המתקבלות באמצעות האופרטורים "לֵב" ו "נורמות ביטחון". במקרה שלנו, הנוסחה נראית כך: A14+A16. לאחר ההקלדה, הקש זן.
14. כתוצאה מכך יתבצע החישוב והתוצאה תוצג מיד בתא עם הנוסחה.
15. לאחר מכן, באופן דומה, אנו מבצעים את החישוב כדי לקבל את הערך של הגבול השמאלי של ה-CI. רק במקרה זה הערך של התוצאה "נורמות ביטחון" אין צורך להוסיף, אלא להחסיר מהתוצאה המתקבלת באמצעות האופרטור "לֵב". במקרה שלנו, הנוסחה נראית כך: =A16-A14.
16. לאחר לחיצה על Enter, נקבל את התוצאה בתא הנתון עם הנוסחה.

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

• כדי לקבוע את הגבול הימני של ה-CI, הנוסחה הכללית תיראה כך:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• באופן דומה, עבור הגבול השמאלי, רק במקום פלוס, אתה צריך לשים מינוס:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

שיטה 2: אופרטור TRUST.STUDENT

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

נוסחת פונקציה אמון.תלמיד באופן הבא:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

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

1. ראשית, בחר את התא שבו אנו מתכננים להציג את התוצאה. לאחר מכן לחץ על הסמל "הכנס פונקציה" (משמאל לשורת הנוסחאות).
2. חלון אשף הפונקציות המוכר כבר ייפתח. בחר קטגוריה "סטָטִיסטִי", ולאחר מכן מרשימת הפונקציות המוצעת, לחץ על האופרטור "תלמיד מהימן", לאחר מכן - OK.
3. בחלון הבא, עלינו להגדיר את הארגומנטים של הפונקציה:
   • ב "אלפא" כמו בשיטה הראשונה, ציין את הערך 0,05 (או "100-95)/100").
   • נעבור לטיעון. "std_off". מכיוון שעל פי תנאי הבעיה, ערכה אינו ידוע לנו, עלינו לבצע את החישובים המתאימים, בהם האופרטור "STDEV.B". לחץ על כפתור הוסף פונקציה ולאחר מכן על הפריט "תכונות נוספות...".
   • בחלון הבא של אשף הפונקציות, בחר את האופרטור "STDEV.B" בקטגוריה "סטָטִיסטִי" ולחץ על OK.
   • אנו נכנסים לחלון הגדרות הארגומנטים של הפונקציה, שהנוסחה שלו נראית כך: =СТАНДОТКЛОН.В(число1;число2;...). כארגומנט הראשון, אנו מציינים טווח הכולל את כל התאים בעמודה "ערך" (לא סופר את הכותרת).
   • עכשיו אתה צריך לחזור לחלון עם הארגומנטים של הפונקציה "TRUST.STUDENT". כדי לעשות זאת, לחץ על הכיתוב באותו שם בשדה הקלט הנוסחה.
   • כעת נעבור לטיעון האחרון "גודל". כמו בשיטה הראשונה, כאן אתה יכול פשוט לציין טווח של תאים, או להכניס את האופרטור "חשבון". אנו בוחרים באפשרות האחרונה.
   • לאחר מילוי כל הארגומנטים, לחץ על הכפתור OK.
4. התא שנבחר יציג את הערך של רווח הסמך לפי הפרמטרים שציינו.
5. לאחר מכן, עלינו לחשב את הערכים של גבולות ה-CI. ובשביל זה אתה צריך לקבל את הערך הממוצע עבור הטווח הנבחר. לשם כך, אנו מיישמים שוב את הפונקציה "לֵב". אלגוריתם הפעולות דומה לזה המתואר בשיטה הראשונה.
6. לאחר קבלת הערך "לֵב", אתה יכול להתחיל לחשב את גבולות ה-CI. הנוסחאות עצמן אינן שונות מאלו המשמשות עם "נורמות ביטחון":
   • גבול ימין CI=ממוצע+ביטחון תלמידים
   • שמאלה CI=AVERAGE-STUDENT Confidence

סיכום

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