SUMPRODUCT

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

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

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

היעילות של SUMPRODUCT

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

I5=SUMPRODUCT(--(C5:C13="אדום"),F5:F14) //
I6=SUMPRODUCT(--(B5:B13="צפון"),--(C5:C14="אדום"),F5:F14)
I7=SUMPRODUCT(--(B5:B13="שרון"),--(C5:C14="כחול"),F5:F14)

התוצאות נראות בתאים I5, I6 ו-I7. המאמר שלהלן מסביר כיצד ניתן להשתמש ב-SUMPRODUCT כדי לחשב סכומים מותנים מסוג זה, ואת מטרת השלילי הכפול (–).

דוגמא קלאסית

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

כדי לבצע חישוב זה, SUMPRODUCT משתמש בערכים בעמודות D ו-E באופן ישיר כך:

=SUMPRODUCT(D5:D15,E5:E15) // מחזיר 1224

התוצאה זהה לסיכום כל הערכים בעמודה F. הנוסחה מוערכת כך:

=SUMPRODUCT(D5:D14,E5:E14)
=SUMPRODUCT({10;12;5;4;6;8;4;3;1;12;10},{15;16;18;18;16;15;14;15;17;18;17})
=SUMPRODUCT({150;192;90;72;96;120;56;45;17;216;170})
=1224

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

SUMPRODUCT לסכומים מותנים וספירות

נניח שיש לך כמה נתוני הזמנה ב-A2:B6, עם איזור בעמודה A, מכירות בעמודה B:

	A	B
1	איזור	מכירות
2	צפון	75
3	צפון	100
4	מרכז	125
5	דרום	125
6	מרכז	150

עם SUMPRODUCT תוכלו לספור את סך המכירות של איזור מרכז כך:

=SUMPRODUCT(--(A2:A6="מרכז"))

ותוכלו לסכם את סך המכירות של איזור מרכז כך:

=SUMPRODUCT(--(A2:A6="מרכז"),B2:B6)

הערה: השלילי הכפול — הוא טריק נפוץ המשמש בנוסחאות אקסל מתקדמות יותר כדי לכפות ערכי TRUE ו-FALSE ל-1 ו-0.

עבור דוגמה הסיכום לעיל, הנה ייצוג וירטואלי של שני המערכים כפי שעובדו לראשונה על ידי SUMPRODUCT:

מערך 1	מערך 2
FALSE	75
FALSE	100
TRUE	125
FALSE	125
TRUE	150

לכל מערך 5 פריטים. מערך 1 מכיל את הערכים TRUE / FALSE הנובעים מהביטוי A2:A6=”מרכז”, ומערך 2 מכיל את הערכים ב-B2:B6. כלהפריט במערך 1 יוכפל בפריט המתאים במערך 2. עם זאת, במצב הנוכחי, התוצאה תהיה אפס מכיוון שהערכים TRUE ו-FALSE במערך 1 יוערכו כאפס. אנחנו צריכים שהפריטים במערך 1 יהיו מספריים, וכאן הכפול-שלילי שימושי.

שלילי כפול (–)

הזנת שלילי כפול (–) לפני בחירת המערכים היא אחת מכמה דרכים לכפות ערכי TRUE ו-FALSE לתוך המקבילות המספריות שלהם, 1 ו-0. ברגע שיש לנו 1 ו-0, נוכל לבצע פעולות שונות על המערכים עם לוגיקה בוליאנית. הטבלה שלהלן מציגה את התוצאה במערך1, בהתבסס על הנוסחה שלמעלה, לאחר שהשלילי הכפול (–) שינה את ערכי TRUE ו-FALSE ל-1 ו-0.

מערך 1		מערך 2		PRODUCT
0	*	75	=	0
0	*	100	=	0
1	*	125	=	125
0	*	125	=	0
1	*	150	=	150
			סה”כ	275

כשמתרגמים את הטבלה למעלה למערכים, כך מעריכים את הנוסחה:

=SUMPRODUCT({0,0,1,0,1},{75,100,125,125,150})

SUMPRODUCT לאחר מכן מכפיל את מערך 1 ומערך 2 יחד, וכתוצאה מכך מערך בודד:

=SUMPRODUCT({0,0,125,0,150})

לבסוף, SUMPRODUCT מחזיר את הסכום של כל הערכים במערך, 275.

התעלמות מתאים ריקים

כדי להתעלם מתאים ריקים עם SUMPRODUCT, אתה יכול להשתמש בביטוי כמו טווח”<>”. בדוגמה למטה, הנוסחאות ב-F5 ו-F6 מתעלמות שתיהן מתאי עמודה C שאינם מכילים ערך:

=SUMPRODUCT(--(C5:C14<>"")) // ספירה
=SUMPRODUCT(--(C5:C14<>"")*D5:D15) // סה"כ(סיכום)

SUMPRODUCT עם פונקציות אחרות

SUMPRODUCT יכול להשתמש בפונקציות אחרות באופן ישיר. ייתכן שתתקלו את SUMPRODUCT בשימוש עם הפונקציה LEN לספירת סך התווים בטווח, או עם פונקציות כמו ISBLANK, ISTEXT וכו’. בדרך כלל אלה אינן פונקציות מערך, אך כאשר ניתן להן טווח, הן יוצרות “מערך תוצאות”. מכיוון ש-SUMPRODUCT בנויה לעבודה עם מערכים, היא מסוגלת לבצע חישובים על המערכים ישירות. זו יכולה להיות דרך טובה לחסוך מקום בגליון עבודה, על ידי ביטול הצורך בעמודת “עזר”.

לדוגמא, נניח שיש לך 10 ערכי טקסט שונים ב-A1:A10 וברצונך לספור את סך התווים עבור כל 10 הערכים. תוכלו להוסיף עמודת עזר בעמודה B המשתמשת בנוסחה זו: LEN(A1) כדי לחשב את התווים בכל תא. לאחר מכן תוכלו להשתמש ב-SUM כדי לחבר את כל 10 המספרים. אבל למה לשבור את הראש? באמצעות SUMPRODUCT, תוכלו לכתוב נוסחה כזו:

=SUMPRODUCT(LEN(A1:A10))

בשימוש עם טווח כמו A1:A10, LEN יחזיר מערך של 10 ערכים. ואז SUMPRODUCT פשוט יסכם את כל הערכים ויחזיר את התוצאה, ללא צורך בעמודת עזר.

מערכים ואקסל 365

זה נושא מבלבל, אבל חייבים להתייחס אליו. ניתן להשתמש בפונקציה ליצירת נוסחאות מערך שאינן דורשות control + shift + enter. זוהי הסיבה העיקרית לכך ש-SUMPRODUCT נמצא בשימוש כה נרחב ליצירת נוסחאות מתקדמות יותר. בעיה אחת בנוסחאות מערך היא שבדרך כלל הן מחזירות תוצאות שגויות אם הן לא מוזנות עם control + shift + enter. המשמעות היא שאם מישהו שוכח להשתמש ב-CSE בעת בדיקה או התאמה של נוסחה, התוצאה עשויה להשתנות פתאום, למרות שהנוסחה בפועל לא השתנתה. שימוש ב-SUMPRODUCT פירושו שהנוסחאות יעבדו בכל גרסה של Excel ללא טיפול מיוחד.

ב-Excel 365, מנוע הנוסחה מטפל במערכים באופן מקורי. זה אומר שניתן להשתמש לעתים קרובות בפונקציה SUM במקום SUMPRODUCT בנוסחת מערך עם אותה תוצאה ואין צורך להזין את הנוסחה בצורה מיוחדת. עם זאת, אם אותה נוסחה נפתחת בגרסה קודמת של אקסל, זה ידרוש control + shift + enter.

השורה התחתונה היא ש-SUMPRODUCT היא אפשרות בטוחה יותר אם גליון עבודה ישמש בכל גרסה של Excel לפני Excel 365, גם אם גליון העבודה נוצר ב-Excel 365.

הערות

• הפונקציה מתייחסץ לפריטים לא מספריים במערכים כ-0.
• ארגומנטים של מערך חייבים להיות באותו גודל. אחרת, הפונקציה תפיק ערך #VALUE! ערך שגיאה.
• בדיקות לוגיות בתוך מערכים ייצרו ערכי TRUE ו-FALSE. ברוב המקרים, תרצה לכפות את אלה על 1 ו-0.
• הפונקציה יכולה לעתים קרובות להשתמש בתוצאה של פונקציות אחרות ישירות