篩選狀態下的計算套路，全網首發！

07-11

提示：本期難度係數稍高，建議先收藏。工作中如果遇到類似問題，能夠直接套用就好。

1、篩選後添加序號

D2單元格公式

=SUBTOTAL(3,E$1:E2)-1

簡要說明：

1、SUBTOTAL函數只統計可見單元格內容。

2、第一參數使用3，表示執行CoUNTA函數的計算規則。

3、公式始終計算E列從第一行至公式所在行這個區域中，處於可見狀態的非空單元格個數。用結果減1，實現序號效果。

2、篩選後相乘

E2單元格公式為：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

簡要說明：

1、OFFSET以E3單元格為基點，依次向下偏移1~13行，實現對E4~E16每個單元格的單獨引用。

2、SUBTOTAL函數第一參數使用3，即依次統計E4~E16每個單元格的可見單元格個數，如果單元格處於顯示狀態，則對這個單元格的統計結果為1，否則統計結果為0。

得到類似以下效果：

{1;0;1;1;1;1;0;0;1;1;0;1;0}

3、再使用以上結果乘以F列的數量和G列的單價，如果單元格處於顯示狀態，則相當於1*數量*單價，否則相當於0*數量*單價。

4、最後使用SUMPRODUCT函數對乘積進行求和。

3、篩選後按條件計數

E2單元格公式為：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>5))

簡要說明：

1、前半部分計算原理與示例2相同。

2、僅改變後半段的統計條件為(G4:G16>5)

4、篩選後自動更正標題

D1單元格公式為：

=LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),D:D)&"統計表"

簡要說明：

1、SUBTOTAL與OFFSET函數結合部分，計算原理與示例2相同。

得到由0和1組成的內存數組：

{0;1;0;1;0;0;0;1;0;0;0;1;0;0;0}

2、用0/這個內存數組，如果是1，得到0，如果是0，則為錯誤值。

{#DIV/0!;0;#DIV/0!;0;#DIV/0!;……;#DIV/0!;0;#DIV/0!;……}

3、LOOKUP函數以1作為查詢值，在以上內存數組中查找最後一個0的位置，並返回對應位置的D列的內容。

最終目的就是實現篩選後，提取最後一個處於顯示狀態的單元格內容。

4、將提取到的內容與&"統計表"連接，變成可自動更新的表格標題。

好了，今天的內容就好大家分享到這裡，祝各位一天好心情~~

圖文製作：祝洪忠

易學寶微視頻教程，1290個Office技巧精粹，每個技巧都與實際工作密切相關。輕鬆學習技巧，練就職場達人，淘寶搜索關鍵字：ExcelHome易學寶