Excel篩選狀態(tài)下的計算總結

本篇文章給大家?guī)砹岁P于excel的相關知識，主要介紹了關于篩選后怎么添加序號、篩選后相乘、篩選后按條件計數(shù)等內(nèi)容，下面一起來看一下，希望對大家有幫助。

手機如何做表格：點擊查看

相關學習推薦：excel教程

1、篩選后添加序號

如下圖所示，要在篩選狀態(tài)下也能保持連續(xù)的序號，咱們可以先取消篩選，在D2單元格輸入以下公式，然后下拉：

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

SUBTOTAL函數(shù)只統(tǒng)計可見單元格內(nèi)容。

第一參數(shù)使用3，表示執(zhí)行COUNTA函數(shù)的計算規(guī)則，也就是對第二參數(shù)統(tǒng)計可見單元格的個數(shù)。

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

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

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

2、篩選后相乘

如下圖所示，在對E列執(zhí)行篩選后，需要計算數(shù)量乘以單價的總額。

E2單元格公式為：

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

要計算篩選后的乘積，問題的關鍵是判斷數(shù)據(jù)是不是處于可見狀態(tài)。

這個可見狀態(tài)怎么判斷呢？

需要讓OFFSET和SUBTOTAL函數(shù)來結合一下。

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

接下來使用SUBTOTAL函數(shù)，第一參數(shù)使用3，即依次統(tǒng)計E4~E16每個單元格中的可見單元格個數(shù)，如果單元格處于顯示狀態(tài)，則對這個單元格的統(tǒng)計結果為1，否則統(tǒng)計結果為0。得到類似以下效果的內(nèi)存數(shù)組：

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

再使用以上結果乘以F列的數(shù)量和G列的單價，如果單元格處于顯示狀態(tài)，則相當于1*數(shù)量*單價，否則相當于0*數(shù)量*單價。

最后使用SUMPRODUCT函數(shù)對乘積進行求和。

3、篩選后按條件計數(shù)

如下圖所示，對E列部門執(zhí)行篩選后，要計算工齡大于3的人數(shù)。

E2單元格公式為：

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

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

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

4、篩選后自動更正標題

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

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

SUBTOTAL與OFFSET函數(shù)結合部分，目的仍然是判斷D列的單元格是否為可見狀態(tài)。得到由0和1組成的內(nèi)存數(shù)組：

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

用0/這個內(nèi)存數(shù)組，得到由0和錯誤值構成的新內(nèi)存數(shù)組：

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

LOOKUP函數(shù)以1作為查詢值，在以上內(nèi)存數(shù)組中查找最后一個0的位置，并返回對應位置的E列的內(nèi)容。

最終目的就是實現(xiàn)篩選后，提取最后一個處于顯示狀態(tài)的單元格內(nèi)容。

將提取到的內(nèi)容與&”統(tǒng)計表”連接，變成可自動更新的表格標題。

相關學習推薦：excel教程