一、案例

如下圖所示，B6:D15為各銷售部門業務員銷售額數據。要求對D列“銷售額”數據進行篩選時，分别統計銷售1部、銷售2部、銷售3部在篩選條件下的銷售額合計。

例如，當未對銷售額篩選時，C2：D4統計的是各銷售部所有業務員銷售額合計。

當篩選銷售額大于500的數據時，C2：D4統計的是各銷售部銷售額超過500的業務員銷售額合計。

二、解決方法

在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)))

拖動填充柄向下複制公式。

公式解析：

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

{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}。返回的True和False邏輯值會在乘法運算時轉為1和0。

（2）($B$7:$B$15=B2)*($D$7:$D$15)用于将不同部門的銷售額數據區分開。當B7:B15中的部門是“銷售1部”時，返回D7：D15中對應的銷售額；反之返回0。

（3）SUBTOTAL函數用于判斷D7:D15中的銷售額數據是否在篩選範圍之内。如果D7:D15的數據被篩選掉，SUBTOTAL函數返回0；如果D7:D17的數據未被篩選掉，SUBTOTAL函數返回1。

（4）SUBTOTAL的語法為SUBTOTAL(function_num,ref1,[ref2],...)。本例中，參數function_num為102，統計區域中包含數字的單元格的個數；參數ref1為OFFSET函數返回的引用。

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

返回數組{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單元格為起點，分别向下移動0、1、2…8行。

（7）SUBTOTAL函數判斷OFFSET返回的每個引用是否被篩選出來。當篩選銷售額大于500的數據時，SUBTOTAL返回的結果為{0;0;1;0;0;1;1;0;1}，“0”表示該行的數據被篩選掉，“1”表示該行數據未被篩選掉。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!