=AVERAGEIF(A2:A7,"North",C2:C7) מחשבת את הממוצע של המכירות ב-C2:C7 בשורות שבהן עמודה A היא North. היא עובדת כמו SUMIF, אלא שהיא מחלקת את הסכום במספר השורות המתאימות.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Average | |
| 2 | North | Apple | 120 | North | 90 | |
| 3 | South | Pear | 45 | North, Apple | 80 | |
| 4 | North | Pear | 110 | Over 50 | 120 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 195 | |||
| 7 | North | Apple | 40 |
F2 מחשב את הממוצע של שלוש השורות של North, 120, 110 ו-40, ומציג 90. F3 צריך שני תנאים, North וגם Apple, ולכן הוא משתמש ב-AVERAGEIFS: (120 + 40) / 2 = 80. ל-F4 אין טווח ממוצע נפרד, ולכן הוא מחשב את הממוצע של המכירות המתאימות עצמן.
התחביר של AVERAGEIF ו-AVERAGEIFS
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
סדר הארגומנטים הוא אותה מלכודת כמו ב-SUMIF וב-SUMIFS: AVERAGEIF שמה את הטווח לממוצע בסוף (ומאפשרת להשמיט אותו), ו-AVERAGEIFS שמה אותו בהתחלה. הקריטריונים נכתבים באותה דרך בשתיהן: "North", ">50", "<>0", "*apple*", או אופרטור שמחובר לתא, ">"&F5. תאים ריקים וטקסט בטווח הממוצע מדולגים.
ממוצע שמתעלם מאפסים
AVERAGE סופרת 0 כערך, ולכן שני תלמידים שנעדרו עם ציון 0 מורידים את ממוצע הכיתה. תאים ריקים הם סיפור אחר: AVERAGE מדלגת עליהם. =AVERAGEIF(B2:B7,"<>0") מדלגת גם על האפסים.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Method | Result | |
| 2 | Ana | 80 | AVERAGE | 48 | |
| 3 | Ben | 0 | Ignore zeros | 80 | |
| 4 | Cara | 90 | Count of zeros | 2 | |
| 5 | Dan | Count of numbers | 5 | ||
| 6 | Eva | 70 | |||
| 7 | Finn | 0 |
AVERAGE מחלקת 240 ב-5, כי התא הריק של Dan נשאר בחוץ אבל שני האפסים נספרים, ומציגה 48. AVERAGEIF עם "<>0" מחלקת 240 ב-3 ומציגה 80. הקלידו 60 ב-B5 ושתיהן משתנות; הקלידו 0 ב-B5 ורק AVERAGE זזה. כדי להתעלם גם מאפסים וגם ממספרים שליליים, השתמשו ב-">0".
למה AVERAGEIF מחזירה #DIV/0!
כששום דבר לא מתאים, ל-AVERAGEIF אין במה לחלק והיא מחזירה #DIV/0!. עטפו אותה ב-IFERROR כדי להציג במקום זאת קו, הודעה או תא ריק.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | West average | #DIV/0! | |
| 3 | South | Pear | 45 | With IFERROR | No sales | |
| 4 | North | Pear | 110 | North max | 120 | |
| 5 | East | Apple | 55 | North min | 40 | |
| 6 | South | Apple | 195 | Apple max | 195 | |
| 7 | North | Apple | 40 |
#DIV/0! הנוסחה מחלקת באפס או בתא ריק.אין שורה של West, ולכן F2 מציג #DIV/0! ו-F3 מציג את ההודעה. שנו את A3 ל-West ושניהם יציגו 45.
MAXIFS ו-MINIFS
F4 עד F6 בגיליון שלמעלה מוצאים את הערך הגדול ביותר והקטן ביותר עם תנאי. הן משתמשות בסדר של AVERAGEIFS, הטווח שמחפשים בו קודם: =MAXIFS(C2:C7,A2:A7,"North") מחזירה 120 ו-=MINIFS(C2:C7,A2:A7,"North") מחזירה 40. בניגוד ל-AVERAGEIF, הן מחזירות 0 כששום דבר לא מתאים, ולא שגיאה.
MAXIFS ו-MINIFS דורשות Excel 2019 ואילך, או Microsoft 365. ב-Excel 2016 ובגרסאות קודמות, =MAX(IF(A2:A7="North",C2:C7)) עושה את אותו הדבר; לחצו Ctrl+Shift+Enter (Cmd+Shift+Enter ב-Mac) כדי להזין אותה בגרסאות האלה.
תרגול: ממוצע עם שני תנאים
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Class | Score | Condition | Average | |
| 2 | Ana | A | 80 | Class A, no zeros | ||
| 3 | Ben | B | 75 | |||
| 4 | Cara | A | 0 | |||
| 5 | Dan | B | 60 | |||
| 6 | Eva | A | 90 | |||
| 7 | Finn | B | 0 | |||
| 8 | Gus | A | 70 |
תורכם: חשבו את ממוצע הציונים של כיתה A, בלי האפסים (תלמידים שנעדרו). כתבו את הנוסחה ב-F2.
ממוצע של ממוצעים: טעות נפוצה
ממוצע של הממוצעים של קבוצות בגדלים שונים נותן ממוצע כולל שגוי. ל-North יש שלוש שורות ול-South שתיים, ולכן כל שורה של South שווה בממוצע של שני הממוצעים יותר ממה שהיא צריכה.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Formula | Result | |
| 2 | North | 120 | North | 90 | |
| 3 | South | 45 | South | 120 | |
| 4 | North | 110 | Average of the two | 105 | |
| 5 | South | 195 | All rows | 102 | |
| 6 | North | 40 |
E4 מציג 105, ו-E5 את הממוצע האמיתי של חמש השורות, 102. כשהקבוצות שונות בגודלן, חשבו את הממוצע של השורות עצמן עם AVERAGEIFS אחת, או חלקו SUMIFS ב-COUNTIFS על אותם תנאים:
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
ציון שמשוקלל לפי נקודות זכות או לפי כמות הוא חישוב אחר לגמרי: זה ממוצע משוקלל.
שאלות נפוצות
מה ההבדל בין AVERAGEIF ל-AVERAGEIFS?
AVERAGEIF מקבלת תנאי אחד ושמה את טווח הממוצע בסוף: =AVERAGEIF(A2:A7,"North",C2:C7). AVERAGEIFS מקבלת כמה תנאים ושמה את טווח הממוצע בהתחלה: =AVERAGEIFS(C2:C7,A2:A7,"North",B2:B7,"Apple").
איך מחשבים ממוצע באקסל ומתעלמים מאפסים?
השתמשו ב-=AVERAGEIF(B2:B7,"<>0"). היא מחשבת ממוצע רק של התאים שאינם 0. AVERAGE ו-AVERAGEIF כבר משאירות בחוץ תאים ריקים, ולכן רק אפסים אמיתיים צריכים את התנאי.
למה AVERAGEIF מחזירה #DIV/0!?
אף תא לא התאים לתנאי, ולכן אקסל מחלק סכום של 0 בספירה של 0. עטפו אותה כדי להציג משהו אחר: =IFERROR(AVERAGEIF(A2:A7,"West",C2:C7),"No data").
איך מוצאים את הערך הגדול ביותר עם תנאי?
השתמשו ב-MAXIFS, כשהטווח שמחפשים בו בא ראשון: =MAXIFS(C2:C7,A2:A7,"North") מחזירה את הערך הגדול ביותר של North. MINIFS עובדת באותה דרך לערך הקטן ביותר. שתיהן דורשות Excel 2019 ואילך, או Microsoft 365.
