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

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

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

=SUBTOTAL(9,F10:F31)

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

要在篩選后進(jìn)行條件求和，可以使用下面的幾個(gè)公式。以下圖為例，假如數(shù)據(jù)在A10:F31區(qū)域中。

1.統(tǒng)計(jì)篩選后“廣州”的銷售數(shù)量：

在B2單元格中輸入公式：

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

說明：

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

② 用OFFSET函數(shù)返回包含F(xiàn)10:F31各單元格中的數(shù)值的數(shù)組。

③ 用SUBTOTAL函數(shù)返回包含篩選后可見單元格數(shù)值的數(shù)組，不可見單元格對應(yīng)數(shù)組中的數(shù)值為0。如本例中返回包含22個(gè)數(shù)值的數(shù)組{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)返回一個(gè)包含數(shù)值1和0的數(shù)組，其中值為“廣州”的單元格對應(yīng)數(shù)組中的數(shù)值為1。本例中返回包含22個(gè)數(shù)值的數(shù)組{1;0;0;1;0;1;0;0;0;0;1;1;0;1;0;1;0;1;0;1;0;1}。

⑤ 最后用SUMPRODUCT函數(shù)返回上述兩個(gè)數(shù)組的乘積和，得到所需結(jié)果。

另一個(gè)類似的數(shù)組公式為：

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

該公式為數(shù)組公式，輸入完畢后按Ctrl+Shift+Enter結(jié)束。

2.統(tǒng)計(jì)篩選后“廣州”的記錄數(shù)：

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

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

或數(shù)組公式：

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

都可以返回?cái)?shù)值4，表示篩選后有4條“廣州”的記錄

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。