AVERAGEIFS
הפונקציה AVERAGEIFS מחשבת את הממוצע (החשבוני) של התאים בסדרה נתונה שכוללת סדרה אחת או יותר העומדות בתנאי קשור אחד או יותר.
AVERAGEIFS(ערכים-ממוצעים, ערכים-לבדיקה, תנאי, ערכים-לבדיקה…, תנאי…)
ערכים-ממוצעים: אוסף המכיל את הערכים שיש לבחון בחישוב ערך הממוצע. ערכים-ממוצעים הוא הפניה לטווח יחיד של תאים, שיכול להכיל כל ערך.
ערכים-לבדיקה: אוסף המכיל ערכים לבדיקה. הערך ערכים-לבדיקה יכול להכיל כל ערך.
תנאי: ביטוי המשווה או בודק ערכים ותוצאתו היא הערך הבוליאני TRUE או FALSE. תנאי יכול לכלול אופרטורי השוואה, קבועים, את אופרטור החיבור אמפרסנד, הפניות ותווים משתנים. ניתן להשתמש בתווים משתנים כדי לבצע התאמה לכל תו יחיד או תווים מרובים בביטוי. ניתן להשתמש ב‑? (סימן שאלה) בכדי לייצג תו אחד, ב‑* (כוכבית) בכדי לייצג תווים מרובים, וב‑~ (טילדה) בכדי לציין שלתו הבא אמורה להימצא התאמה, וכי הוא לא אמור לשמש כתו משתנה. תנאים יכולים להכיל גם פונקציית REGEX במקום תווים משתנים.
ערכים-לבדיקה…: ניתן להוסיף עוד אוספים המכילים ערכים לבדיקה. אחרי כל אוסף של ערכים-לבדיקה צריך להופיע ביטוי של תנאי. ניתן לחזור על דפוס זה של ערכים-לבדיקה, תנאי פעמים רבות ככל הדרוש.
תנאי...: אם נכלל אוסף אופציונלי של ערכים-לבדיקה, תנאי... הוא ביטוי נוסף שהתוצאה שלו היא הערך הבוליאני TRUE או FALSE. צריך להופיע ביטוי תנאי אחד לאחר כל אוסף של ערכים-לבדיקה, ולכן מספר הארגומנטים של פונקציה זו הוא תמיד אי-זוגי.
הערות
עבור כל צמד של ערכים לבדיקה ושל תנאי, תתבצע השוואה בין הערך המקביל (בעל אותו מיקום במערך) ובין מבחן התנאי. אם הערך עומד בכל בדיקות התנאי, הערך המקביל בערכים-ממוצעים ייכלל בממוצע.
על הגודל של ערכים-ממוצעים ושל כל האוספים של ערכים-לבדיקה להיות זהה.
דוגמאות |
|---|
בהינתן הטבלה הבאה: |
A | B | C | D | |
|---|---|---|---|---|
1 | גיל | מין | מצב משפחתי | משכורת |
2 | 35 | ז | ז | 71,000 |
3 | 27 | נ | ז | 81,000 |
4 | 42 | ז | ז | 86,000 |
5 | 51 | ז | ר | 66,000 |
6 | 28 | ז | ר | 52,000 |
7 | 49 | נ | ר | 62,000 |
8 | 63 | נ | ז | 89,000 |
9 | 22 | ז | ז | 34,000 |
10 | 29 | נ | ר | 42,000 |
11 | 35 | נ | ז | 56,000 |
12 | 33 | ז | ר | 62,000 |
13 | 61 | ז | ז | 91,000 |
=AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M") (כאשר M=״זכר״) תחזיר את הערך 54,750, המשכורת הממוצעת של גברים (כפי שצוין באמצעות האות ״ז״ בעמודה B) מתחת לגיל 40. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M", C2:C13, "=S") (כאשר M=״זכר״ ו‑S=״רווק״) תחזיר את הערך 57,000, המשכורת הממוצעת של גברים רווקים (כפי שצוין באמצעות האות ״ר״ בעמודה C) מתחת לגיל 40. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M", C2:C13, "=M") (כאשר M בעמודה B=״זכר״ ו‑M בעמודה C=״נשוי״) תחזיר את הערך 52,500, המשכורת הממוצעת של גברים נשואים (כפי שצוין באמצעות האות ״נ״ בעמודה C) מתחת לגיל 40. =AVERAGEIFS(D2:D13, A2:A13, ">=40", B2:B13, "=M", C2:C13, "=M") תחזיר את הערך 88,500, המשכורת הממוצעת של גברים נשואים (כפי שצוין באמצעות האות ״M״ בעמודה C) בני לפחות 40. ניתן גם להשתמש באופרטור ≥. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=F") (כאשר F=״נקבה״) תחזיר את הערך 59,667 בקירוב, המשכורת הממוצעת של נשים (כפי שצוין באמצעות האות ״נ״ בעמודה B) מתחת לגיל 40. הפונקציה =AVERAGEIF(C2:C13, "<>M", D2:D13) מחזירה 56,800, המשכורת הממוצעת של רווקים ורווקות שאינם בני 35. ניתן גם להשתמש באופרטור #. |
דוגמה לשימוש ב‑REGEX |
|---|
בהינתן הטבלה הבאה: |
A | B | |
|---|---|---|
1 | 45 | marina@example.com |
2 | 41 | אהרון |
3 | 29 | michael@example.com |
4 | 64 | katrina@example.com |
5 | 12 | שרה |
=AVERAGEIFS(A1:A5, B1:B5, REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"), A1:A5, ">10") מחזירה 46, ממוצע של כל התאים ב‑A1:A5 שתוכנם גדול מ‑10 ושבהם התא התואם ב‑B1:B5 מכיל כתובת דוא״ל. |