篩選這個(gè)功能，某些情況下讓人歡喜讓人憂，可以篩選固然方便，但是如果一個(gè)表有各種計(jì)算項(xiàng)，能否在篩選后忽略隱藏單元格進(jìn)行計(jì)算？

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡體中文
• 時(shí)間：2024-09-12

查看詳情

案例：

下圖 1 是某小區(qū)的餛飩團(tuán)購表，請按以下要求統(tǒng)計(jì)各項(xiàng)數(shù)據(jù)：

• 在 A 列僅對篩選后的可見行編號(hào)；
• D2 單元格：計(jì)算可見行的購買總數(shù)
• E2 單元格：計(jì)算可見行的總價(jià)

效果如圖 2 所示。

解決方案：

1. 在 A5 單元格中輸入以下公式 --> 下拉復(fù)制公式：

=SUBTOTAL(3,C$5:C5)

公式釋義：

subtotal 函數(shù)的作用是返回列表或數(shù)據(jù)庫中的分類匯總；

語法為 SUBTOTAL(function_num,ref1,[ref2],...)

以下是所有 function_num 對應(yīng)的功能；在篩選的情況下，該函數(shù)都只統(tǒng)計(jì)可見單元格內(nèi)容；

從上圖可以看出，參數(shù) 3 的作用是統(tǒng)計(jì)區(qū)域內(nèi)可見單元格的數(shù)量；

區(qū)域的起始單元格必須絕對引用，結(jié)尾單元格須相對引用，這樣就能讓區(qū)域隨著公式下拉不斷增加，從而起到序列遞增的效果

不管怎么篩選，序號(hào)都會(huì)自動(dòng)按顯示行重新順序編號(hào)。

2. 在 D2 單元格中輸入以下公式：

=SUBTOTAL(9,D5:D36)

公式釋義：

參數(shù) 9 的作用是求和

不管怎么篩選，序號(hào)和總數(shù)都正確。

3. 在 E2 單元格中輸入以下公式：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C4,ROW(A1:A32),))*D5:D36*E5:E36)

公式釋義：

OFFSET(C4,ROW(A1:A32),)：以 C4 單元格為起點(diǎn)，向下依次偏移 1 至 32 行，即遍歷品類的所有選項(xiàng)；

SUBTOTAL(3,...)：對遍歷的數(shù)組忽略隱藏行計(jì)數(shù)，就會(huì)生成由 1 和 0 組成的數(shù)組，1 代表未隱藏，0 則是隱藏行；

SUMPRODUCT(...*D5:D36*E5:E36)：將上述數(shù)組與 D 和 E 的數(shù)值相乘求和，只有值為 1 的能求出結(jié)果，即計(jì)算出所有未隱藏的乘積之和

通過一個(gè) subtotal 函數(shù)，就實(shí)現(xiàn)了篩選狀態(tài)下的 3 種計(jì)算需求。

以上就是Excel表格篩選后，編號(hào)、總數(shù)、總價(jià)計(jì)算只統(tǒng)計(jì)可見行的實(shí)現(xiàn)方法，希望大家喜歡，請繼續(xù)關(guān)注腳本之家。

最新評(píng)論