לְהִצטַיֵן

SUMPRODUCT עם IF

Sumproduct With If

נוסחת אקסל: SUMPRODUCT עם IFנוסחה כללית | _+_ | סיכום

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





= SUMPRODUCT (expression,range)

היכן הדברים הבאים טווחים בשם מוגדרים:

 
= SUMPRODUCT (--(color='red'),quantity,price) = SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price) = SUMPRODUCT (--(state='co'),--(color='blue'),quantity,price)

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





הֶסבֵּר

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

 
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14

התוצאה היא מערך או ערכי TRUE FALSE, אשר נאלצים לאחד ולאפסים עם שלילי כפול (--) פעולה. התוצאה היא מערך זה:



 
--(color='red')

שימו לב שהמערך מכיל 10 ערכים, אחד לכל שורה. אחד מציין שורה שבה הצבע הוא 'אדום' ואפס מציין שורה עם כל צבע אחר.

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

 
{1010001000}

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

 
= SUMPRODUCT ({1010001000},quantity,price)

התנהגות הליבה של SUMPRODUCT היא להכפיל ואז לסכם מערכים. מכיוון שאנו עובדים עם שלושה מערכים, אנו יכולים לדמיין את הפעולה כפי שמוצג בטבלה שלהלן, כאשר עמודת התוצאה היא תוצאה של הכפלה מערך 1 * מערך 2 * מערך 3 :

מערך 1 מערך 2 מערך 3 תוֹצָאָה
1 10 חֲמֵשׁ עֶשׂרֵה 150
0 6 18 0
1 14 חֲמֵשׁ עֶשׂרֵה 210
0 9 16 0
0 אחת עשרה 18 0
0 10 18 0
1 8 חֲמֵשׁ עֶשׂרֵה 120
0 9 16 0
0 אחת עשרה 18 0
0 10 16 0

הודעה מערך 1 עובד כמסנן - אפס ערכים כאן ערכי 'אפס החוצה' בשורות שבהן הצבע אינו 'אדום'. מחזירים את התוצאות ל- SUMPRODUCT, יש לנו:

כיצד לסנן נתונים ב- Excel באמצעות הנוסחה
 
= SUMPRODUCT ({1010001000},{1061491110891110},{15181516181815161816})

מה שמחזיר תוצאה סופית של 480.

הוספת קריטריונים נוספים

ניתן להרחיב קריטריונים על ידי הוספת ביטוי לוגי נוסף. לדוגמה, כדי למצוא את סך המכירות בהן הצבע הוא 'אדום' והמצב הוא 'TX', H6 מכיל:

 
= SUMPRODUCT ({1500210000120000})

הערה: SUMPRODUCT אינו תלוי רישיות.

פישוט עם מערך יחיד

המקצוענים של Excel לרוב יפשטו את התחביר בתוך SUMPRODUCT מעט על ידי הכפלת מערכים ישירות פנימה מערך 1 ככה:

 
= SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price)

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

הסופר דייב בראנס


^