SUMIFS
הפונקציה SUMIFS מסכמת את התאים באוסף הכוללים ערכי-בדיקה שעומדים בתנאים שהוגדרו.
SUMIFS(ערכי-סכומים, ערכים-לבדיקה, תנאי, ערכים-לבדיקה…,תנאי…)
ערכי-סכומים: האוסף שמכיל את הערכים שייסכמו. ערכי-סכומים יכולים להכיל ערכים מספריים, ערכי תאריך ושעה או ערכי משך זמן.
ערכים-לבדיקה: אוסף המכיל ערכים לבדיקה. הערך ערכים-לבדיקה יכול להכיל כל ערך.
תנאי: ביטוי המשווה או בודק ערכים ותוצאתו היא הערך הבוליאני TRUE או FALSE. תנאי יכול לכלול אופרטורי השוואה, קבועים, את אופרטור החיבור אמפרסנד, הפניות ותווים משתנים. ניתן להשתמש בתווים משתנים כדי לבצע התאמה לכל תו יחיד או תווים מרובים בביטוי. ניתן להשתמש ב‑? (סימן שאלה) בכדי לייצג תו אחד, ב‑* (כוכבית) בכדי לייצג תווים מרובים, וב‑~ (טילדה) בכדי לציין שלתו הבא אמורה להימצא התאמה, וכי הוא לא אמור לשמש כתו משתנה. תנאים יכולים להכיל גם פונקציית REGEX במקום תווים משתנים.
ערכים-לבדיקה…: ניתן להוסיף עוד אוספים המכילים ערכים לבדיקה. אחרי כל אוסף של ערכים-לבדיקה צריך להופיע ביטוי של תנאי. ניתן לחזור על דפוס זה של ערכים-לבדיקה, תנאי פעמים רבות ככל הדרוש.
תנאי...: אם נכלל אוסף אופציונלי של ערכים-לבדיקה, תנאי... הוא ביטוי נוסף שהתוצאה שלו היא הערך הבוליאני TRUE או FALSE. צריך להופיע תנאי אחד לאחר כל אוסף של ערכים-לבדיקה, ולכן מספר הארגומנטים של פונקציה זו הוא תמיד אי-זוגי.
הערות
עבור כל צמד ערכים של בדיקה ותנאי, תתבצע השוואה בין התא או הערך המקביל (בעלי אותו מיקום באוסף) לתנאי. אם התא או הערך יעמדו בכל התנאים, ערכי-סכומים ייכלל בסכום.
על כל האוספים להיות באותו הגודל.
דוגמאות |
|---|
בהינתן הטבלה הבאה: |
A | B | C | |
|---|---|---|---|
1 | טון | דירוג | תאריך |
2 | 6 | 1 | 10/12/2012 |
3 | 15 | 2 | 10/12/2012 |
4 | 5 | 1 | 13/12/2012 |
5 | 7 | 2 | 13/12/2012 |
6 | 8 | 2 | 14/12/2012 |
7 | 6 | 1 | 15/12/2012 |
8 | 7 | 2 | 15/12/2012 |
9 | 4 | 2 | 16/12/2012 |
10 | 7 | 1 | 16/12/2012 |
11 | 8 | 2 | 16/12/2012 |
12 | 5 | 1 | 17/12/2012 |
13 | 11 | 2 | 20/12/2012 |
=SUMIFS(A2:A13, B2:B13, "=1", C2:C13, ">=13/12/2012", C2:C13, "<=17/12/2012") תחזיר את הערך 23, מספר הטונות של הסחורה שסופקה במהלך השבוע של ה‑17 בדצמבר והדירוג שלה היה 1. האוסף A2:A13 מכיל את מספר הטונות, כלומר הערכים שיש לסכום (ערכי-סכומים). האוסף B2:B13 (ערכים-לבדיקה) מכיל את הערך הראשון שיש לבדוק עבור דירוג של 1 (תנאי). האוסף C2:C13 (ערכים-לבדיקה...) מכיל את הערך השני שיש לבדוק כדי למצוא התאמה עבור תאריך התחלה (תנאי...). טווח זה מכיל גם את הערך השלישי לבדיקה, תאריך הסיום. שורות מספר 4 (5 טונות), 7 (6 טונות), 10 (7 טונות) ו‑12 (5 טונות) עומדות בכל התנאים, והסכום שלהן הוא 23 טונות. ניתן גם להשתמש באופרטורים ≥ ו-≤. =SUMIFS(A2:A13, B2:B13, "=2", C2:C13, ">=13/12/2012", C2:C13, "<=17/12/2012") תחזיר את הערך 34, מספר הטונות של הסחורה בעלת הדירוג ״2״, שסופקה במהלך אותו שבוע. ניתן גם להשתמש באופרטורים ≥ ו-≤. <> =SUMIFS(A2:A13, B2:B13, "<>2", C2:C13, ">=10/12/2012", C2:C13, "<=20/12/2012") תחזיר את הערך 29, מספר הטונות של הסחורות שאינן בעלות הדירוג ״2״, שסופקו במהלך אותו שבוע. ניתן גם להשתמש באופרטורים #, ≥ ו-≤. |
דוגמא לשימוש ב‑REGEX |
|---|
בהינתן הטבלה הבאה: |
A | B | |
|---|---|---|
1 | 45 | marina@example.com |
2 | 41 | אהרון |
3 | 29 | michael@example.com |
4 | 64 | jake@example.com |
5 | 12 | שרה |
הפונקציה =SUMIFS(A1:A5, B1:B5,REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})",), A1:A5, ">10") מחזירה 138, סכום התאים ב‑A1:A5 שתוכנם גדול מ‑10 ושבהם התא התואם ב‑B1:B5 מכיל כתובת דוא״ל. |