Excel中篩選后的條件求和公式 圖解教程

有時需要在Excel表格中動態地反映篩選后數值的變化情況，如下圖中F32單元格中的合計值會隨著不同的篩選情況而變化。我們知道，用SUBTOTAL函數可以求出篩選后可見單元格的數值和或平均值等，因為SUBTOTAL函數會忽略不包括在篩選結果中的行。例如下圖是篩選“商品名稱=襪子”、“類別=男”時的結果，其中F32單元格中的公式為：

=SUBTOTAL(9,F10:F31)

但如果此時還需要動態地反映篩選后各城市的合計值，即進行篩選后的條件求和，僅僅使用SUBTOTAL函數無法實現。雖然用多條件求和公式可以得到計算結果，但我們的目的是要隨著不同的篩選操作，能夠動態實時地反映數據的變化情況，因而此處不宜使用多條件求和公式。關于多條件求和公式，可以參考本站《用公式進行多條件求和》一文。

要在篩選后進行條件求和，可以使用下面的幾個公式。以下圖為例，假如數據在A10:F31區域中。

1.統計篩選后“廣州”的銷售數量：

在B2單元格中輸入公式：

=SUMPRODUCT(SUBTOTAL(9,OFFSET($F,(ROW($F:$F)-MIN(ROW($F:$F))),)),–($B:$B=A2))

說明：

① ROW($F:$F)-MIN(ROW($F:$F))返回一個包含22個數值的數組{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}。

② 用OFFSET函數返回包含F10:F31各單元格中的數值的數組。

③ 用SUBTOTAL函數返回包含篩選后可見單元格數值的數組，不可見單元格對應數組中的數值為0。如本例中返回包含22個數值的數組{359;535;0;0;0;297;0;308;0;0;0;1021;0;0;0;0;0;506;392;0;0;0}。

④ –($B:$B=A2)返回一個包含數值1和0的數組，其中值為“廣州”的單元格對應數組中的數值為1。本例中返回包含22個數值的數組{1;0;0;1;0;1;0;0;0;0;1;1;0;1;0;1;0;1;0;1;0;1}。

⑤ 最后用SUMPRODUCT函數返回上述兩個數組的乘積和，得到所需結果。

另一個類似的數組公式為：

=SUM(SUBTOTAL(9,OFFSET($F,ROW(:),))*($B:$B=A2))

該公式為數組公式，輸入完畢后按Ctrl+Shift+Enter結束。

2.統計篩選后“廣州”的記錄數：

只需將上述公式中SUBTOTAL函數的參數“9”改為“3”即可。如在C2單元格中輸入公式：

=SUMPRODUCT(SUBTOTAL(3,OFFSET($F,(ROW($F:$F)-MIN(ROW($F:$F))),)),–($B:$B=A2))

或數組公式：

=SUM(SUBTOTAL(3,OFFSET($F,ROW(:),))*($B:$B=A2))

都可以返回數值4，表示篩選后有4條“廣州”的記錄