SUMIF ו-SUMIFS: סכימה מותנית באקסל

לחבר מספרים זה פשוט — פונקציית SUM של אקסל מטפלת בזה תוך שניות. אבל מה קורה כשאתם רוצים לסכום רק ערכים שעומדים בתנאי מסוים? כאן בדיוק SUMIF ו-SUMIFS הופכות לבלתי נמנעות. שתי הפונקציות הללו מאפשרות לכם לחבר מספרים באופן סלקטיבי, לפי תנאי אחד או רבים, והן נמצאות בין הנוסחאות המעשיות ביותר שתשתמשו בהן בעבודה היומיומית עם גיליונות אלקטרוניים.

מדריך זה מוביל אתכם דרך שתי הפונקציות מהיסוד: תחביר, דוגמאות אמיתיות, שגיאות נפוצות ותרחיש מעשי שתוכלו לעקוב אחריו. בין אם אתם עוקבים אחר מכירות, מנהלים תקציבים או מנתחים נתוני פרויקטים — סכימה מותנית תחסוך לכם כמויות עצומות של עבודה ידנית.

מהי SUMIF?

SUMIF מסכמת ערכים בטווח רק כאשר תא מתאים בטווח אחר עומד בתנאי שהגדרתם. היא מושלמת כשיש לכם קריטריון אחד — למשל, "סכם את כל המכירות מאזור המזרח" או "חבר הוצאות הגדולות מ-500 דולר."

תחביר SUMIF

=SUMIF(range, criteria, [sum_range])

• range — התאים שברצונכם להעריך מול הקריטריונים.
• criteria — התנאי שיש לעמוד בו. יכול להיות מספר, טקסט, הפניה לתא או ביטוי.
• sum_range — (אופציונלי) התאים שיש לסכום בפועל. אם הושמט, אקסל מסכם את הערכים ב-range עצמו.

דוגמאות בסיסיות ל-SUMIF

נניח שעמודה A מכילה קטגוריות מוצרים ועמודה B מכילה סכומי מכירות. כדי לסכום את כל המכירות של "Electronics":

=SUMIF(A2:A100, "Electronics", B2:B100)

כדי לסכום את כל הערכים בעמודה B הגדולים מ-1000:

=SUMIF(B2:B100, ">1000")

שימו לב שכאשר ה-range וה-sum_range זהים, ניתן להשמיט את הארגומנט השלישי. כמו כן, שימו לב שאופרטורי השוואה כמו >, <, >= ו-<> חייבים להיות מוקפים במרכאות.

מהי SUMIFS?

SUMIFS היא הגרסה הרב-תנאית של SUMIF. היא מאפשרת לכם לציין שני קריטריונים או יותר, ואקסל מסכמת ערכים רק כאשר כל התנאים מתקיימים בו-זמנית. מבנה הארגומנטים שלה שונה במקצת מ-SUMIF — טווח הסכום מגיע ראשון.

תחביר SUMIFS

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range — התאים לסכימה (תמיד הארגומנט הראשון).
• criteria_range1 — הטווח הראשון להערכה.
• criteria1 — התנאי לטווח הראשון.
• criteria_range2, criteria2 — זוגות טווח/תנאי נוספים (עד 127 זוגות).

דוגמה בסיסית ל-SUMIFS

באמצעות אותה מערכת נתונים, כדי לסכום מכירות של "Electronics" באזור "East" (בהנחה שעמודה C מכילה שמות אזורים):

=SUMIFS(B2:B100, A2:A100, "Electronics", C2:C100, "East")

נוסחה זו בודקת כל שורה: אם עמודה A היא "Electronics" וגם עמודה C היא "East", הערך המתאים בעמודה B נכלל בסכום הכולל.

הדרכה מעשית: ניתוח נתוני מכירות

בואו נבנה תרחיש ריאליסטי. דמיינו שאתם מנהלים דוח מכירות עם העמודות הבאות:

A: איש מכירות	B: אזור	C: מוצר	D: חודש	E: הכנסה
Alice	East	Laptops	January	$4,200
Bob	West	Phones	January	$3,800
Alice	East	Phones	February	$2,900
Carol	East	Laptops	February	$5,100
Bob	West	Laptops	February	$4,400

הנתונים שלכם משתרעים משורה 2 עד שורה 500. להלן נוסחאות למענה על שאלות עסקיות נפוצות:

סך ההכנסה של Alice:

=SUMIF(A2:A500, "Alice", E2:E500)

סך ההכנסה באזור East:

=SUMIF(B2:B500, "East", E2:E500)

סך הכנסות Laptops באזור East:

=SUMIFS(E2:E500, C2:C500, "Laptops", B2:B500, "East")

סך ההכנסה של Alice ממכירת Laptops בינואר:

=SUMIFS(E2:E500, A2:A500, "Alice", C2:C500, "Laptops", D2:D500, "January")

שימו לב כיצד כל תנאי נוסף מצמצם עוד יותר את התוצאה. סוג כזה של ניתוח ייקח דקות רבות לביצוע ידני, אך רץ באופן מיידי עם SUMIFS. אם אתם בונים כלי דיווח מלא, זה משתלב היטב עם הטכניקות המכוסות במדריך לוח מחוונים למכירות באקסל: מעקב אחר KPI וביצועים.

שימוש בהפניות לתאים במקום טקסט קשיח

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

מקמו את "Alice" בתא H2 ואת "Laptops" בתא H3. הנוסחה שלכם הופכת ל:

=SUMIFS(E2:E500, A2:A500, H2, C2:C500, H3)

כעת שנו את H2 ל-"Bob" והנוסחה מחשבת מחדש באופן מיידי עבור מכירות המחשבים הניידים של Bob. גישה זו חיונית ללוחות מחוונים אינטראקטיביים. הבנת הפניות לתאים באקסל — יחסיות מול מוחלטות תעזור לכם לוודא שהפניות אלו לא יזוזו בצורה בלתי צפויה כשתעתיקו נוסחאות.

תווי כלי עזר ב-SUMIF וב-SUMIFS

שתי הפונקציות תומכות בתווים כלליים (wildcards), שימושיים במיוחד כאשר הנתונים שלכם אינם עקביים לחלוטין:

• * — מתאים לכל רצף של תווים. "Lap*" מתאים ל-"Laptops", "Laptop Bag" וכו'.
• ? — מתאים לכל תו בודד. "Bo?" מתאים ל-"Bob", "Boy", "Bog".
• ~ — תו בריחה. השתמשו ב-~* כדי להתאים כוכבית מילולית.

דוגמה — סכום כל ההכנסות עבור מוצרים שמתחילים ב-"Lap":

=SUMIF(C2:C500, "Lap*", E2:E500)

סכימה מותנית לפי תאריך

SUMIFS מטפלת בתאריכים באופן טבעי מכיוון שאקסל מאחסנת תאריכים כמספרים סידוריים. ניתן להשתמש באופרטורי השוואה כדי לסכום ערכים בטווח תאריכים.

בהנחה שעמודה D מכילה ערכי תאריך אמיתיים (ולא טקסט רגיל), כדי לסכום הכנסות מ-1 בינואר עד 31 במרץ 2024:

=SUMIFS(E2:E500, D2:D500, ">="&DATE(2024,1,1), D2:D500, "<="&DATE(2024,3,31))

האופרטור & משרשר את אופרטור ההשוואה (כטקסט) עם התוצאה של פונקציית DATE. זהו תבנית נפוצה מאוד שכדאי לשנן.

שגיאות נפוצות וכיצד להימנע מהן

אי-התאמה בגודל הטווחים

כל הטווחים ב-SUMIFS חייבים להיות באותו גודל. אם ל-sum_range יש 500 שורות אך ל-criteria_range יש 499, אקסל מחזירה שגיאה. תמיד בדקו שהטווחים שלכם עקביים.

שכחת מרכאות סביב אופרטורים

כתיבת =SUMIF(B2:B100, >500, B2:B100) תיכשל. אופרטורים וקריטריוני טקסט חייבים להיות במרכאות: ">500" או "Electronics".

סדר הארגומנטים ב-SUMIF לעומת SUMIFS

ב-SUMIF, ה-sum_range הוא הארגומנט השלישי. ב-SUMIFS, הוא הראשון. ערבוב ביניהם הוא מקור נפוץ לתשובות שגויות — בדקו פעמיים את סדר הארגומנטים בכל פעם.

טקסט המאוחסן כמספרים (או להפך)

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

SUMIF ו-SUMIFS לעומת גישות אחרות

קיימות דרכים נוספות לסכום נתונים באופן מותנה באקסל, וכדאי לדעת מתי להשתמש בכל אחת:

• טבלאות Pivot — מצוינות לניתוח חקרני ולסיכומים מהירים, אך הפלט אינו נוסחה חיה שניתן להטמיע בתא לוח מחוונים. ראו את טבלאות Pivot: המדריך המלא למתחילים למתי גישה זו הגיונית יותר.
• SUMPRODUCT — גמישה יותר מ-SUMIFS לתנאים מורכבים הכוללים מערכים או קריטריונים מחושבים, אך איטית יותר על מערכי נתונים גדולים.
• IF + SUM (נוסחת מערך) — גישה ישנה יותר המשתמשת ב-Ctrl+Shift+Enter. SUMIFS נקייה ומהירה יותר למקרי שימוש סטנדרטיים.

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

שילוב SUMIFS עם פונקציות אחרות

SUMIFS הופכת לעוצמתית אף יותר כאשר היא מוטמעת בתוך נוסחאות אחרות:

חישוב אחוז מהסכום הכולל:

=SUMIFS(E2:E500, B2:B500, "East") / SUM(E2:E500)

השוואת שני סכומים מותנים:

=SUMIFS(E2:E500, B2:B500, "East") - SUMIFS(E2:E500, B2:B500, "West")

שימוש עם IF לטיפול אלגנטי בקריטריונים ריקים:

=IF(H2="", SUM(E2:E500), SUMIFS(E2:E500, A2:A500, H2))

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

קבלת הנוסחה הנכונה מהר יותר

אם אתם מוצאים את עצמכם מביטים ב-SUMIFS מורכבת עם ארבעה או חמישה קריטריונים ואינכם מצליחים להבין מדוע היא מחזירה אפס, נסו לתאר את מה שאתם צריכים בשפה פשוטה — כלים כמו GPTExcel יכולים לייצר את הנוסחה המדויקת מתיאור כגון "סכם הכנסה כאשר האזור הוא East, המוצר הוא Laptops והתאריך נמצא ברבעון הראשון של 2024", ומספקים לכם מיידית את התחביר הנכון לאימות ולשימוש.

שאלות נפוצות

האם SUMIF יכולה לטפל במספר קריטריונים?

לא ישירות. SUMIF מתוכננת לתנאי יחיד. אם אתם צריכים שני תנאים או יותר, השתמשו ב-SUMIFS. עם זאת, ניתן לעקוף זאת על ידי חיבור מספר תוצאות SUMIF יחד כאשר הקריטריונים חלים על אותו טווח ואתם רוצים תנאי OR (למשל, סכום שורות שהן "East" או "West").

מדוע נוסחת SUMIFS שלי מחזירה 0 כאשר אני יודע שיש נתונים תואמים?

הסיבות הנפוצות ביותר הן: קריטריונים שהוקלדו עם אות ראשונה שונה מהנתונים (SUMIFS אינה תלוית רישיות, אז זו לא הסיבה), מספרים המאוחסנים כטקסט בטווח הסכום או בטווח הקריטריונים, רווחים מיותרים בערכי תאים, או אי-התאמה בגודלי טווחים. השתמשו בפונקציית TRIM או בשלבי ניקוי נתונים כדי לפתור בעיות רווח לבן.

האם SUMIFS עובדת עם תאריכים?

כן, בתנאי שהתאריכים שלכם מאוחסנים כערכי תאריך אמיתיים של אקסל (ולא כטקסט). השתמשו באופרטורי השוואה עם פונקציית DATE או עם הפניות תאריך ישירות: =SUMIFS(E2:E500, D2:D500, ">="&H1, D2:D500, "<="&H2) כאשר H1 ו-H2 מכילים את תאריכי ההתחלה והסיום שלכם.

האם קיימת מגבלה למספר הקריטריונים ש-SUMIFS יכולה לטפל בהם?

אקסל מאפשרת עד 127 זוגות טווח/קריטריון בנוסחת SUMIFS אחת — הרבה יותר ממה שתזדקקו אי פעם בפועל. ביצועים עלולים להאט עם מערכי נתונים גדולים מאוד ומספר קריטריונים רב, אך עבור נתונים עסקיים אופייניים (עשרות אלפי שורות), SUMIFS נשארת מהירה ואמינה.