如何按月分別匯總每個(gè)銷售員的銷售額

在工作中處理各種數(shù)據(jù)報(bào)表時(shí)，經(jīng)常需要對明細(xì)數(shù)據(jù)進(jìn)行分類匯總。如圖 90?1所示為某企業(yè)2009年銷售數(shù)據(jù)明細(xì)表，現(xiàn)要求按月匯總各個(gè)銷售員的銷售額數(shù)據(jù)，該如何操作呢？

圖90?1銷售數(shù)據(jù)明細(xì)表

→ 解決方案：

使用MMULT函數(shù)多單元格數(shù)組公式進(jìn)行雙條件匯總數(shù)據(jù)。

→ 操作方法

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

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

→ 原理分析

MMULT函數(shù)雙條件求和

本例中共有5名銷售員，需要按12個(gè)月分別匯總銷售額數(shù)據(jù)，而匯總表中以月份為行字段，以銷售員為列字段，因此，銷售額的匯總結(jié)果區(qū)域是一個(gè)12行5列的矩陣區(qū)域，即MMULT函數(shù)返回的結(jié)果矩陣應(yīng)該是一個(gè)12行5列的數(shù)組。

1．根據(jù)MMULT函數(shù)結(jié)果矩陣的行數(shù)與array1 的行數(shù)相同特性，首先以月份為條件構(gòu)造一個(gè)12行n列的矩陣作為array1參數(shù)，其中n由銷售明細(xì)表數(shù)據(jù)行數(shù)決定，即：

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

2．根據(jù)MMULT函數(shù)結(jié)果矩陣的列數(shù)與array2 的列數(shù)相同，以及Array1的列數(shù)與 array2 的行數(shù)相同的特性，以銷售員為條件構(gòu)造一個(gè)n行5列的矩陣，然后乘以C列銷售額數(shù)據(jù)，作為array2參數(shù)，即：

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

3．最后使用MMULT函數(shù)運(yùn)算，返回一個(gè)12行5列的結(jié)果矩陣，并使用多單元格數(shù)組公式方式存放多個(gè)計(jì)算結(jié)果：

{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函數(shù)用于返回兩個(gè)數(shù)組的矩陣乘積。結(jié)果矩陣的行數(shù)與 array1 的行數(shù)相同，矩陣的列數(shù)與 array2 的列數(shù)相同。語法如下：

MMULT(array1, array2)

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

→ 知識擴(kuò)展

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

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

相比之下，由于MMULT函數(shù)使用了多單元格數(shù)組公式，其運(yùn)算效率要高于SUMPRODUCT函數(shù)構(gòu)成的多個(gè)公式。

版權(quán)所有 轉(zhuǎn)載須經(jīng)Excel技巧網(wǎng)許可