איך מחשבים ממוצע משוקלל ב-Excel
כדי לחשב ממוצע משוקלל ב-Excel, הכפל כל ערך במשקל שלו, חבר את המכפלות וחלק בסכום המשקלים: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). AVERAGE רגיל מתייחס לכל ערך כחשוב באותה מידה, ולכן הוא שגוי כשיש ערכים שחשובים יותר מאחרים. המדריך מציג את הנוסחה, בדיקה, והטעויות הנפוצות.
1. מתי צריך ממוצע משוקלל
השתמש בממוצע משוקלל כשהפריטים לא שווים בחשיבותם. קורס שבו הבחינה היא 50% מהציון, ממוצע מחירים שבו מוצר אחד נמכר הרבה יותר יחידות, או ציון שבו קריטריון אחד חשוב במיוחד. שים את הערכים בעמודה אחת והמשקלים בעמודה הסמוכה, שורה לכל פריט.
2. הנוסחה
SUMPRODUCT מכפיל את התאים של שני טווחים שורה אחרי שורה ומחבר את המכפלות, כך שאין צורך בעמודת עזר. חלק ב-SUM של המשקלים כדי לקבל את הממוצע. המשקלים יכולים להיות אחוזים, נקודות או ספירות, כי החלוקה מנרמלת אותם.
דוגמה: ציון קורס משלושה חלקים
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | חלק | ציון | משקל | ממוצע משוקלל | |
| 2 | בחינה | 80 | 50 | 81 | |
| 3 | פרויקט | 90 | 30 | ||
| 4 | בוחן | 70 | 20 |
80 כפול 50 ועוד 90 כפול 30 ועוד 70 כפול 20 הוא 8,100. בחלוקה בסך המשקלים, שהוא 100, התוצאה היא 81.
3. השוואה לממוצע רגיל
אותם שלושה ציונים נותנים AVERAGE רגיל של 80, כי הנוסחה הרגילה מתייחסת לבוחן באותה חשיבות כמו לבחינה. התוצאה המשוקללת, 81, גבוהה יותר, כי ציון הפרויקט החזק נספר יותר מהבוחן החלש. אם שתי התוצאות זהות, בדוק שהמשקלים לא שווים כולם.
דוגמה: הממוצע הרגיל של אותם ציונים
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | חלק | ציון | ממוצע רגיל | ||
| 2 | בחינה | 80 | 80 | ||
| 3 | פרויקט | 90 | |||
| 4 | בוחן | 70 |
240 חלקי 3 הוא 80, וזה מתעלם מהמשקלים.
4. משקלים באחוזים
אם המשקלים שלך הם אחוזים שכבר מסתכמים ב-100%, אפשר לדלג על החלוקה: =SUMPRODUCT(B2:B4,C2:C4). השאר את החלוקה בכל זאת כשאינך בטוח, כי היא נותנת תשובה נכונה גם כשהמשקלים מסתכמים בערך אחר, והיא נשארת נכונה כשמוסיפים שורה.
5. משקלים שהם כמויות
בממוצע מחיר, המשקלים הם היחידות שנמכרו. שים את המחירים בעמודה אחת והיחידות בעמודה הסמוכה, והשתמש באותה נוסחה. התוצאה נותנת את מחיר היחידה הממוצע האמיתי, וזה לא אותו דבר כמו הממוצע הרגיל של המחירים.
טעויות נפוצות
טווחים בגדלים שונים. SUMPRODUCT של B2:B4 ו-C2:C5 מחזיר #VALUE!.
תיקון: הפוך את שני הטווחים למספר שורות זהה.
השמטת החלוקה. =SUMPRODUCT(B2:B4,C2:C4) הוא סכום משוקלל, לא ממוצע, אלא אם המשקלים מסתכמים ב-1.
תיקון: חלק ב-SUM של המשקלים.
טקסט בתא משקל. SUMPRODUCT מתייחס לטקסט כאל אפס, ולכן הפריט נעלם מהתוצאה בשקט.
תיקון: בדוק שכל משקל הוא מספר, והשתמש ב-COUNT כדי לוודא כמה יש.
שאלות נפוצות
האם יש ב-Excel פונקציה מובנית לממוצע משוקלל?
לא. SUMPRODUCT חלקי SUM הוא הפתרון הסטנדרטי.
האם המשקלים חייבים להסתכם ב-100?
לא. כשמחלקים בסכום המשקלים, כל סקלה עובדת: 5, 3 ו-2 נותנים את אותה תוצאה כמו 50, 30 ו-20.
האם אפשר להתעלם מתאים ריקים?
תא ציון ריק נחשב כ-0 ב-SUMPRODUCT. כדי להשמיט אותו, סנן את השורות קודם, או השתמש בעמודת עזר שריקה כשהציון ריק.
פונקציות קשורות
- AVERAGEAVERAGE מחזירה את הממוצע החשבוני של מספרים או של טווח, ומתעלמת מטקסט ומתאים ריקים.
- SUMSUM מחברת מספרים, תאים וטווחים שלמים בנוסחה אחת.
- AVERAGEIFAVERAGEIF מחזירה את הממוצע של הערכים שעומדים בתנאי אחד, למשל ההזמנה הממוצעת באזור אחד.
- SUMIFSUMIF מחברת את הערכים שעונים על תנאי אחד, כמו כל המכירות של אזור אחד או כל הסכומים מעל 100.
עוד על ממוצעים משוקללים
המדריך נותן את נוסחת SUMPRODUCT ובדיקה. החלק הזה מוסיף איך לבנות את הטבלה שמזינה אותה, איך להשתמש בממוצע משוקלל עם תנאים, ודרך לבדוק את התוצאה ביד.
בנה את הטבלה כך שהנוסחה תישאר פשוטה
שמור ערכים ומשקלים בשתי עמודות זו לצד זו, שורה לכל פריט, עם כותרת למעלה. תן שם למשקלים כאחוזים אם הם מגיעים כך. אל תשאיר שורה ריקה בתוך הטבלה, כי שני הטווחים חייבים להיות באותה צורה. כשאתה מוסיף פריט מאוחר יותר, הוסף שורה בתוך הטווח כך שהנוסחה תגדל איתו. הוספה מתחת לשורה האחרונה לא מרחיבה את הטווח, לכן בדוק את הנוסחה אחרי כל שינוי. טבלה של Excel פותרת את זה, כי הטווחים שלה גדלים לבד כשמוסיפים שורה בתחתית.
ממוצע משוקלל לקבוצה אחת
כדי לממוצע רק את השורות של קטגוריה אחת, הכפל בתנאי בתוך SUMPRODUCT. השוואה מחזירה TRUE או FALSE, והכפל הופך אותם ל-1 ו-0, כך שהשורות האחרות נספרות כאפס.
=SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMPRODUCT((A2:A7="North")*C2:C7)בדוק את התוצאה ביד
ממוצע משוקלל חייב להיות בין הערך הקטן ביותר לגדול ביותר. אם לא, המשקלים שגויים. בדוק עם שתי שורות: הערכים 10 ו-20 עם המשקלים 1 ו-3 נותנים (10 + 60) / 4 = 17.5, קרוב יותר ל-20 כי הערך הזה נספר שלוש פעמים. השווה את התוצאה ל-AVERAGE הרגיל: הפער מראה כמה המשקלים משנים.
תרגל ב[שיעור SUMPRODUCT](/learn/sumproduct) וב[יסודות סטטיסטיקה](/learn/statistics-basics).