Excel篩選狀態下的計算總結

本篇文章給大家帶來了關于excel的相關知識,主要介紹了關于篩選后怎么添加序號、篩選后相乘、篩選后按條件計數等內容,下面一起來看一下,希望對大家有幫助。

Excel篩選狀態下的計算總結

相關學習推薦:excel

1、篩選后添加序號

如下圖所示,要在篩選狀態下也能保持連續的序號,咱們可以先取消篩選,在D2單元格輸入以下公式,然后下拉:

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

Excel篩選狀態下的計算總結

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

第一參數使用3,表示執行COUNTA函數的計算規則,也就是對第二參數統計可見單元格的個數。

第二參數使用一個動態擴展的范圍E$1:E2,隨著公式的下拉,這個范圍會依次變成E$1:E3,E$1:E4,E$1:E5,……

公式始終計算E列從第一行至公式所在行這個區域中,處于可見狀態的非空單元格個數。用結果減1,計算結果就是和序號一樣了,而且篩選后也能保持連續。

注意,注意,這個公式如果換成從=SUBTOTAL(3,E$2:E2),也就是從公式所在行開始的話,序號結果雖然沒問題,但是篩選時最后一行會被excel當成匯總行而始終顯示。

2、篩選后相乘

如下圖所示,在對E列執行篩選后,需要計算數量乘以單價的總額。

E2單元格公式為:

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

Excel篩選狀態下的計算總結

要計算篩選后的乘積,問題的關鍵是判斷數據是不是處于可見狀態。

這個可見狀態怎么判斷呢?

需要讓OFFSET和SUBTOTAL函數來結合一下。

首先使用OFFSET函數,以E3單元格為基點,依次向下偏移1~13行,得到一個多維引用。這個多維引用中包含13個一行一列的引用區域,也就是對E4~E16的單個單元格分別進行引用。

接下來使用SUBTOTAL函數,第一參數使用3,即依次統計E4~E16每個單元格中的可見單元格個數,如果單元格處于顯示狀態,則對這個單元格的統計結果為1,否則統計結果為0。得到類似以下效果的內存數組:

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

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

最后使用SUMPRODUCT函數對乘積進行求和。

3、篩選后按條件計數

如下圖所示,對E列部門執行篩選后,要計算工齡大于3的人數。

E2單元格公式為:

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

Excel篩選狀態下的計算總結

前半部分計算原理與上一個示例相同,核心也是判斷是單元格否處于可見狀態。

公式后半段的統計條件(G4:G16>3)與前半段的判斷結果相乘,表示兩個條件同時符合,也就是處于可見狀態、并且G列大于3的個數。

4、篩選后自動更正標題

如下圖所示,對E列部門名稱進行篩選后,希望D1單元格的標題自動變更為對應的部門名稱,公式為:

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

Excel篩選狀態下的計算總結

SUBTOTAL與OFFSET函數結合部分,目的仍然是判斷D列的單元格是否為可見狀態。得到由0和1組成的內存數組:

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

用0/這個內存數組,得到由0和錯誤值構成的新內存數組:

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

LOOKUP函數以1作為查詢值,在以上內存數組中查找最后一個0的位置,并返回對應位置的E列的內容。

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

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

相關學習推薦:excel

? 版權聲明
THE END
喜歡就支持一下吧
點贊11 分享