ממוצע משוקלל באקסל: נוסחת SUMPRODUCT

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) מחשבת ממוצע משוקלל: כל ציון ב-B מוכפל במשקל שלו ב-C, המכפלות מתחברות, והסכום מחולק בסכום המשקלים.

נוסחאותאיפוס

F2fx

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

	A	B	C	D	E	F
1	Part	Score	Weight		Average	Result
2	Homework	85	20%		Weighted	81.2
3	Quizzes	78	30%		Plain AVERAGE	80.75
4	Midterm	72	20%
5	Final	88	30%

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

הציון המשוקלל הוא 81.2, ואילו AVERAGE רגילה נותנת 80.75, כי היא מתייחסת לשיעורי הבית, ששווים 20%, כאילו הם שווים כמו המבחן הסופי, ששווה 30%. שנו את הציון של המבחן הסופי והציון המשוקלל זז יותר מאשר באותו שינוי בשיעורי הבית.

לאקסל אין פונקציה WEIGHTED.AVERAGE, ולכן SUMPRODUCT חלקי SUM היא הנוסחה הסטנדרטית. ב-Google Sheets יש AVERAGE.WEIGHTED(B2:B5,C2:C5).

איך נוסחת הממוצע המשוקלל עובדת

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

נוסחאותאיפוס

D6fx

=SUM(D2:D5)

	A	B	C	D
1	Part	Score	Weight	Score x weight
2	Homework	85	20%	17.0
3	Quizzes	78	30%	23.4
4	Midterm	72	20%	14.4
5	Final	88	30%	26.4
6	Total		100%	81.2
7	Weighted average			81.2

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

כל חלק תורם את הציון שלו כפול המשקל שלו: 85 × 20% הוא 17.0, 78 × 30% הוא 23.4, וכן הלאה. הם מסתכמים ב-81.2. המשקלים מסתכמים ב-100%, ולכן החלוקה ב-C6 לא משנה כאן כלום, אבל היא מה ששומר על הנוסחה נכונה כשהם לא מסתכמים כך.

משקלים שלא מסתכמים ב-100%

משקלים לא חייבים להיות אחוזים. ממוצע ציונים (GPA) משוקלל לפי שעות זכות, ומחיר ממוצע לפי כמות. החלוקה ב-SUM של המשקלים מטפלת בכל סכום.

נוסחאותאיפוס

F2fx

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

	A	B	C	D	E	F
1	Course	Grade points	Credits		Average	Result
2	Math	4	4		Weighted GPA	3.51
3	History	3	3		Plain average	3.48
4	Biology	3.7	4		Total credits	14
5	Art	2.7	2
6	Lab	4	1

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

בלי החלוקה הנוסחה הייתה מחזירה את סכום נקודות הציון כפול נקודות הזכות, 49.2 כאן, ולא ממוצע. איתה, F2 מציג את הממוצע המשוקלל לפי 14 נקודות זכות. הקורסים של ארבע נקודות מושכים את הממוצע לכיוון הציונים שלהם, והמעבדה של נקודה אחת כמעט לא מזיזה אותו: שנו את B6 ל-2 וראו כמה מעט F2 משתנה לעומת F3.

אם המשקלים שלכם הם אחוזים שמסתכמים בדיוק ב-100%, =SUMPRODUCT(B2:B5,C2:C5) לבדה נותנת את אותה תוצאה. השאירו בכל זאת את /SUM(...): ביום שמשקל ישתנה והסכום יהפוך ל-105%, הנוסחה בלעדיה תהיה שגויה ושום דבר בגיליון לא יראה את זה.

ממוצע משוקלל עם תנאי

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

נוסחאותאיפוס

G2fx

=SUMPRODUCT((A2:A7=F2)*C2:C7*D2:D7)/SUMIF(A2:A7,F2,D2:D7)

	A	B	C	D	E	F	G
1	Region	Product	Price	Qty		Region	Average price
2	North	Apple	$1.20	100		North	$1.45
3	South	Pear	$1.50	40		South	$1.36
4	North	Pear	$1.50	60
5	South	Apple	$1.20	120
6	North	Plum	$2.00	40
7	South	Plum	$2.00	20

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

North מכר 100 תפוחים, 60 אגסים ו-40 שזיפים, ולכן המחיר הממוצע שלו הוא $1.45, קרוב יותר למחיר התפוח ממה שממוצע פשוט של שלושת המחירים היה נותן. התנאי (A2:A7=F2) הוא 1 בשורות של North ו-0 בכל מקום אחר, ולכן השורות האחרות לא מוסיפות כלום למונה, ו-SUMIF מחברת במכנה רק את הכמויות של North.

תרגול: מחיר ממוצע משוקלל

נוסחאותאיפוס

F2fx

	A	B	C	D	E	F
1	Batch	Price	Qty		Average	Result
2	Jan	$4.20	100		Weighted price
3	Feb	$4.50	40
4	Mar	$3.90	250
5	Apr	$4.80	10
6	May	$4.10	120

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

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

בדיקההצגת התשובה

טעויות שנותנות ממוצע משוקלל שגוי

• AVERAGE של המכפלות. =AVERAGE(D2:D5) על עמודה של ציון × משקל מחלקת במספר השורות, לא במשקלים, ונותנת מספר קטן וחסר משמעות. חלקו את ה-SUM של המכפלות ב-SUM של המשקלים.
• חלוקה בספירה במקום במשקלים. =SUMPRODUCT(B2:B6,C2:C6)/COUNT(B2:B6) נכונה רק כשכל משקל הוא 1.
• טווחים שלא מיושרים. =SUMPRODUCT(B2:B6,C3:C7) מצמדת כל ערך למשקל של השורה הבאה. שני הטווחים חייבים להתחיל ולהיגמר באותן שורות; גדלים שונים מחזירים #VALUE!.
• משקל ריק. משקל ריק נחשב 0, ולכן השורה הזו נשארת בחוץ בשקט. אם משקל חסר צריך לעצור את החישוב, בדקו קודם עם =COUNTBLANK(C2:C6).
• ממוצע של ממוצעים. שני ממוצעי כיתות של 70 (10 תלמידים) ו-90 (30 תלמידים) לא נותנים ממוצע של 80. שקללו אותם לפי גודל הכיתות והתוצאה היא 85; בעמוד של AVERAGEIF יש את אותה מלכודת עם תנאים.