篩選后再按平常的方式用函數(shù)按條件計(jì)數(shù)、按條件求和等會(huì)出現(xiàn)錯(cuò)誤。這個(gè)時(shí)候怎么辦？推薦用SUBTOTAL+OFFSET組合。

在求和和計(jì)數(shù)前先看一個(gè)篩選后的序號(hào)處理。

1.篩選后序號(hào)也保持連續(xù)

默認(rèn)情況下，篩選后序號(hào)是不連續(xù)的，如下。

如何讓篩選后序號(hào)保持連續(xù)？

取消篩選，然后在A2中輸入下方公式生成序號(hào)：

=SUBTOTAL(3,$B$1:B2)-1

再篩選，序號(hào)保持了連續(xù)。

2.篩選后按條件計(jì)數(shù)

接著上方，求篩選后產(chǎn)品銷售大于150的有多少人。

直接輸入公式=COUNTIF(D2:D17,">150")的話，結(jié)果是錯(cuò)的。

當(dāng)前的錯(cuò)誤在于它統(tǒng)計(jì)的是整個(gè)數(shù)據(jù)而不是篩選后的數(shù)據(jù)。

同序號(hào)類似，要用SUBTOTAL對可見單元格計(jì)數(shù)。

公式=SUMPRODUCT(SUBTOTAL(3,OFFSET($D$1,ROW(1:16),))*(D2:D17>150))

公式解析：

最核心的是SUBTOTAL(3,OFFSET($D$1,ROW(1:16),))。OFFSET($D$1,ROW(1:16),)逐一取D1下方的第1、2、3……16行數(shù)據(jù)。實(shí)際就是將D2:D17單元格分別引用一次。然后用SUBTOTAL判斷每個(gè)數(shù)是否可見，可見就計(jì)數(shù)為1，不可見計(jì)數(shù)為0，得到一組由1和0組成的數(shù)組。最后與大于150的條件判斷結(jié)果相乘，并通過SUMPRODUCT對乘積求和。

3.篩選后求和和按條件求和

1）求和

很簡單，與序號(hào)處理類似，只是把3改成了9。如圖。

2）按條件求和

求單價(jià)大于10的銷售數(shù)量。處理辦法與篩選后按條件計(jì)數(shù)類似。

公式=SUMPRODUCT(SUBTOTAL(3,OFFSET($D$1,ROW(1:16),))*D2:D17*(E2:E17>10))

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

雙色圖表直接表明業(yè)績是否超過平均值

一文講懂indirect函數(shù)在跨表匯總數(shù)據(jù)時(shí)的使用方法

Excel動(dòng)態(tài)圖表入門

用SORTBY函數(shù)進(jìn)行排序

版權(quán)申明：

本文作者Mutou；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。