Excel可深可淺，遇到這個公式，咱的智商還夠用不？

06-08

如何按月分別匯總每個銷售員的銷售額

在工作中處理各種數據報表時，經常需要對明細數據進行分類匯總。如圖 90?1所示為某企業2009年銷售數據明細表，現要求按月匯總各個銷售員的銷售額數據，該如何操作呢？

圖90?1銷售數據明細表

→ 解決方案：

使用MMULT函數多單元格數組公式進行雙條件匯總數據。

→ 操作方法

選擇F3:J14單元格區域，輸入下列公式，按<Ctrl+Shift+Enter>組合鍵結束形成多單元格數組公式。

{=MMULT(--(E3:E14=TRANSPOSE(MONTH(B3:B100)&"月")),(A3:A100=F2:J2)*C3:C100)}

→ 原理分析

MMULT函數雙條件求和

本例中共有5名銷售員，需要按12個月分別匯總銷售額數據，而匯總表中以月份為行欄位，以銷售員為列欄位，因此，銷售額的匯總結果區域是一個12行5列的矩陣區域，即MMULT函數返回的結果矩陣應該是一個12行5列的數組。

1．根據MMULT函數結果矩陣的行數與array1 的行數相同特性，首先以月份為條件構造一個12行n列的矩陣作為array1參數，其中n由銷售明細表數據行數決定，即：

--(E3:E14=TRANSPOSE(MONTH(B3:B100)&"月"))

2．根據MMULT函數結果矩陣的列數與array2 的列數相同，以及Array1的列數與 array2 的行數相同的特性，以銷售員為條件構造一個n行5列的矩陣，然後乘以C列銷售額數據，作為array2參數，即：

(A3:A100=F2:J2)*C3:C100

3．最後使用MMULT函數運算，返回一個12行5列的結果矩陣，並使用多單元格數組公式方式存放多個計算結果：

{229,154,40,28,109;144,0,128,0,321;196,35,112,25,0;184,116,42,79,182;74,38,275,56,85;0,188,0,200,128;331,95,106,110,59;121,0,202,0,236;154,76,128,186,41;151,68,68,0,39;149,78,226,116,102;0,100,0,234,194}

其中，MMULT函數用於返回兩個數組的矩陣乘積。結果矩陣的行數與 array1 的行數相同，矩陣的列數與 array2 的列數相同。語法如下：

MMULT(array1, array2)

參數array1、array2為要進行矩陣乘法運算的兩個數組，可以是單元格區域、數組常量或引用。Array1 的列數必須與 array2 的行數相同，而且兩個數組中都只能包含數值，因此本例中array1使用減負運算將條件判斷返回的邏輯值轉換為數值。

→ 知識擴展

本例還可以使用SUMPRODUCT函數進行分類匯總，如在F3單元格輸入下列公式，向右、向下填充至J14單元格：

=SUMPRODUCT(($A$3:$A$100=F$2)*(MONTH($B$3:$B$100)&"月"=$E3)*$C$3:$C$100)

相比之下，由於MMULT函數使用了多單元格數組公式，其運算效率要高於SUMPRODUCT函數構成的多個公式。