לְהִצטַיֵן

פונקציית SUMPRODUCT של Excel

Excel Sumproduct Function

פונקציית SUMPRODUCT של Excelסיכום

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



מטרה כפל, ואז סכום מערכים ערך החזרה התוצאה של מערכים מוכפלים ומסוכמים תחביר = SUMPRODUCT (מערך 1, [מערך 2], ...) ארגומנטים
  • מערך 1 - המערך או הטווח הראשון שיש להכפיל ולאחר מכן להוסיף.
  • מערך 2 - [אופציונלי] המערך או הטווח השני שיש להכפיל ולאחר מכן להוסיף.
גִרְסָה Excel 2003 הערות שימוש

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

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





להמחשת אופן הפעולה של SUMPRODUCT, להלן מספר דוגמאות נפוצות.

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

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



ל ב
1 מדינה מכירות
2 הַחוּצָה 75
3 מה 100
4 TX 125
5 מה 125
6 TX 150

אתה יכול להשתמש ב- SUMPRODUCT לספור סך המכירות לטקסס ('TX') עם נוסחה זו:

 
= SUMPRODUCT (--(A2:A6='TX'))

ואתה יכול סְכוּם סך המכירות לטקסס ('TX') עם נוסחה זו:

 
= SUMPRODUCT (--(A2:A6='TX'),B2:B6)

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

כיצד לקבוע שונות באקסל

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

מערך 1 מערך 2
שֶׁקֶר 75
שֶׁקֶר 100
נָכוֹן 125
שֶׁקֶר 125
נָכוֹן 150

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

על ידי שימוש בשלילי הכפול - (כפול אחד השונים, עבורך סוגים טכניים) אנו מסוגלים לכפות את TRUE/FALSE לערכים המספריים אחד ואפס, כפי שמוצג בייצוג הווירטואלי להלן. העמודה האחרונה 'מוצר' מייצגת את התוצאה של הכפלת שני המערכים יחד. התוצאה המסוכמת, 275, היא הערך שמחזיר SUMPRODUCT.

מערך 1 מערך 2 מוצר
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 ({0,0,125,0,150})

הדוגמה הזו מרחיב את הרעיונות שלמעלה בפירוט רב יותר.

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 פשוט יסכם את כל הערכים ויחזיר את התוצאה, ללא צורך בעמודת עזר.

ראה דוגמאות להלן דרכים רבות אחרות לשימוש ב- SUMPRODUCT.

הערות:

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


^