Excel SumProduct函數數組與多條件表達的使用方法及7個實例

在 excel 中，SUMPRODUCT函數既可用于求和又可用于求乘積之和，它分為數組和多條件兩種表達式，其中數組表達式用于求乘積之和，最多可以有 255 個數組；多條件表達用于求滿足指定條件的和，它又分為兩種形式，一種是用逗號分隔且條件前加 --，另一種用星號（*）把求和區域和條件連接，無論哪種條件形式，各條件之間表示“與”的關系。

SUMPRODUCT函數各條件之間除可用星號（*）表示“與”關系外，還可以用加號（+）表示“或”的關系，通常是在一個公式中用加號把幾個條件連接起來表示“或”關系。

一、excel SumProduct函數語法

1、數組表達式：SUMPRODUCT(Array1, [Array2], [Array3], ...)

中文表達式：SUMPRODUCT(數組1, [數組2], [數組3], ...)

說明：SumProduct函數最少要有一個數組，最多只能有 255 數組；若有兩個以上數組，數組必須有相同的維數，否則會返回值錯誤#VALUE!；非數值型被當作0處理；每個數組必須有相同的維數；計算方法為：把每個數組的對應元素相乘，然后求和。

2、條件表達式：SUMPRODUCT(求和區域, --(條件1)，[--(條件2)，...])

或 SUMPRODUCT((求和區域)*(條件1)*[(條件2)，...])

說明：條件表達式中用 [] 括起來的為可選項，即SumProduct函數至少要有一個條件；若有兩個以上的條件，條件之間是“與”關系，即要同時滿足多個條件；“求和區域”與條件的順序可以交換；條件前面的 -- 是把邏輯值或空值轉為數值以便于計算，星號（*）表示“與”關系。

二、Excel SumProduct函數數組表達式使用實例

（一）有兩個數組的實例

1、假如要求服裝銷量的營業額。選中 F11 單元格，把公式 =SUMPRODUCT(E2:E10,F2:F10) 復制到 F11，按回車，返回 509432.3；操作過程步驟，如圖1所示：

圖1

2、公式共引用了兩個單元格區域（數組），即 E2:E10 和 F2:F10，計算方法為：E2 * F2 + E3 * F3 + … + E10 * F10。

提示：如果僅求和只寫一個數組即可，例如對服裝銷量求和 =SUMPRODUCT(F2:F10)。

（二）非數值型被當作0處理的實例

1、選中 B1 單元格，把公式 =SUMPRODUCT(A1:A5) 復制到 B1，按回車，返回 3；操作過程步驟，如圖2所示：

圖2

2、A1:A5 中只有一個數字 3，而返回結果為 3，說明非數值型都被忽略，邏輯值 True 也不轉為 1。

（三）返回值錯誤 #VALUE! 的實例

1、選中 C1 單元格，把公式 =SUMPRODUCT(A1:A5,B2:B5) 復制到 C1，按回車，返回值錯誤 #VALUE!；操作過程步驟，如圖3所示：

圖3

2、公式中 A1:A5 比 B2:B5 多一個單元格，也就是數組維數不同，因此返回值錯誤 #VALUE!。

三、Excel SumProduct函數多條件表達式使用實例

（一）SUMPRODUCT(求和區域, --(條件1)，[--(條件2)，...])

1、假如要求“分類”為“女裝”且“價格”大于 70 的服裝銷量之和。選中 F11 單元格，把公式 =SUMPRODUCT(F2:F10, --(C2:C10="女裝"),--(E2:E10>70)) 復制到 F11，按回車，返回求和結果 1420；操作過程步驟，如圖4所示：

圖4

2、公式說明：

A、公式的求和區域為 F2:F10，條件為 --(C2:C10="女裝"),--(E2:E10>70)；第一個條件 --(C2:C10="女裝") 用于從 C2 到 C10 中依次取出每一個分類，并跟“女裝”比較，如果相等返回 True，否則返回 False；最后返回數組{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}。

B、第二個條件 --(E2:E10>70) 用于從 E2 到 E10 中依次取出每一個價格并跟 70 比較，如果大于 70，返回 True，否則返回 False；最后返回數組 {TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}。

C、則兩個條件  --(C2:C10="女裝"),--(E2:E10>70) 變為 --({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}),--({TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE})，進一步計算把 True 轉為 1，False 轉為 0，即 {1;1;1;0;1;1;0;1;1},{1;0;0;1;1;1;1;0;0}，再把兩個數組對應元素相乘，結果為 {1;0;0;0;0;1;1;0;0}。

D、F2:F10 返回數組 {329;569;638;897;528;563;982;765;892}，則公式變為 =SUMPRODUCT({329;569;638;897;528;563;982;765;892},{1;0;0;0;0;1;1;0;0})，再把兩個數組對應元素相乘并相加，因此求和結果為 1420。

（二）SUMPRODUCT((求和區域)*(條件1)*[(條件2)，...])

1、同樣以求“分類”為“女裝”且“價格”大于 70 的服裝銷量之和為例。選中 F11 單元格，把公式 =SUMPRODUCT((F2:F10)*(C2:C10="女裝")*(E2:E10>70)) 復制到 F11，按回車，返回求和結果 1420；操作過程步驟，如圖5所示：

圖5

2、公式說明：

公式 =SUMPRODUCT((F2:F10)*(C2:C10="女裝")*(E2:E10>70)) 是上例中的公式 =SUMPRODUCT(F2:F10, --(C2:C10="女裝"),--(E2:E10>70)) 的另一種寫法，只不過它把三個參數用 * 連接來表示“與”的關系。

四、Excel SumProduct函數擴展使用實例

（一）用 * 和 + 組合“與”和“或”的條件

1、假如要求“大類”為“女裝”且“小類”為“襯衫或T恤”的所有服裝銷量之和。選中 F11 單元格，把公式 =SUMPRODUCT((F2:F10)*(C2:C10="女裝")*((D2:D10="襯衫")+(D2:D10="T恤"))) 復制到 F11，按回車，返回求和結果 2312；操作過程步驟，如圖6所示：

圖6

2、公式說明：

公式的條件為 (C2:C10="女裝")*((D2:D10="襯衫")+(D2:D10="T恤"))，前一個條件與后兩個條件是“與”關系，用 * 連接；后兩個條件是“或”關系，用 + 連接；意思是選出 C2:C10 為“女裝”且 D2:D10 中為“襯衫”或“T恤”的服裝銷量。

（二）SumProduct函數求和區域有文本的處理方法

1、假如要求“大類”為“女裝”且“小類”為“襯衫” 服裝銷量之和，銷量列有文本。選中 F11 單元格，把公式 =SUMPRODUCT(IF(ISTEXT(F2:F10),0,F2:F10)*(C2:C10="女裝")*(D2:D10="襯衫")) 復制到 F11，按 Ctrl + Shift + 回車，返回求和結果 1221，操作過程步驟，如圖7所示：

圖7

2、公式說明：

A、公式中的 IF(ISTEXT(F2:F10),0,F2:F10) 為求和區域部分，由于求和區域 F2:F10 有文本（無），如果直接寫 F2:F10 會返回值錯誤#VALUE!，所以要用 If 來判斷。

B、ISTEXT(F2:F10) 用于判斷 F2:F10 中每個元素是否為文本，如果是文本，返回 True，否則返回 False，由于只有 F7 是文本，所以返回數組 {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}，

C、接著，依次從數組中取出每一個元素，如果為真，返回 0，否則返回 F2:F10 中對應的值，例如第一次取數組第一個元素 FALSE，返回329，第二次取數組第二個元素 FALSE，返回 569，其它的以此類推，最后返回數組 {329;569;638;897;528;0;982;765;892}。

3、上面的公式也可以改為 =SUM(IF(ISTEXT(F2:F10),0,F2:F10)*(C2:C10="女裝")*(D2:D10="襯衫"))，也要按 Ctrl + Shift + 回車。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。