【原】如何對(duì)篩選后的數(shù)據(jù)條件求和

一、案例

如下圖所示，B6:D15為各銷售部門業(yè)務(wù)員銷售額數(shù)據(jù)。要求對(duì)D列“銷售額”數(shù)據(jù)進(jìn)行篩選時(shí)，分別統(tǒng)計(jì)銷售1部、銷售2部、銷售3部在篩選條件下的銷售額合計(jì)。

例如，當(dāng)未對(duì)銷售額篩選時(shí)，C2：D4統(tǒng)計(jì)的是各銷售部所有業(yè)務(wù)員銷售額合計(jì)。

當(dāng)篩選銷售額大于500的數(shù)據(jù)時(shí)，C2：D4統(tǒng)計(jì)的是各銷售部銷售額超過(guò)500的業(yè)務(wù)員銷售額合計(jì)。

二、解決方法

在C2單元格輸入公式

=SUMPRODUCT(($B$7:$B$15=B2)*($D$7:$D$15)*SUBTOTAL(102,OFFSET($D$7,ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15)),0)))

拖動(dòng)填充柄向下復(fù)制公式。

公式解析：

（1）($B$7:$B$15=B2)用于判斷B7:B15中的部門數(shù)據(jù)是否等于“銷售1部”，返回的結(jié)果為

{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}。返回的True和False邏輯值會(huì)在乘法運(yùn)算時(shí)轉(zhuǎn)為1和0。

（2）($B$7:$B$15=B2)*($D$7:$D$15)用于將不同部門的銷售額數(shù)據(jù)區(qū)分開。當(dāng)B7:B15中的部門是“銷售1部”時(shí)，返回D7：D15中對(duì)應(yīng)的銷售額；反之返回0。

（3）SUBTOTAL函數(shù)用于判斷D7:D15中的銷售額數(shù)據(jù)是否在篩選范圍之內(nèi)。如果D7:D15的數(shù)據(jù)被篩選掉，SUBTOTAL函數(shù)返回0；如果D7:D17的數(shù)據(jù)未被篩選掉，SUBTOTAL函數(shù)返回1。

（4）SUBTOTAL的語(yǔ)法為SUBTOTAL(function_num,ref1,[ref2],...)。本例中，參數(shù)function_num為102，統(tǒng)計(jì)區(qū)域中包含數(shù)字的單元格的個(gè)數(shù)；參數(shù)ref1為OFFSET函數(shù)返回的引用。

（5）ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15))

返回?cái)?shù)組{0;1;2;3;4;5;6;7;8}。

（6）OFFSET($D$7,ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15)),0)即

OFFSET($D$7,{0;1;2;3;4;5;6;7;8},0)。指以D7單元格為起點(diǎn)，分別向下移動(dòng)0、1、2…8行。

（7）SUBTOTAL函數(shù)判斷OFFSET返回的每個(gè)引用是否被篩選出來(lái)。當(dāng)篩選銷售額大于500的數(shù)據(jù)時(shí)，SUBTOTAL返回的結(jié)果為{0;0;1;0;0;1;1;0;1}，“0”表示該行的數(shù)據(jù)被篩選掉，“1”表示該行數(shù)據(jù)未被篩選掉。