如下圖所示，要在篩選狀态下也能保持連續的序号，咱們可以先取消篩選，在D2單元格輸入以下公式，然後下拉：=SUBTOTAL(3,E$1:E2)-1

SUBTOTAL函數隻統計可見單元格内容。第一參數使用3，表示執行COUNTA函數的計算規則，也就是對第二參數統計可見單元格的個數。第二參數使用一個動态擴展的範圍E$1:E2，随着公式的下拉，這個範圍會依次變成E$1:E3，E$1:E4，E$1:E5，……公式始終計算E列從第一行至公式所在行這個區域中，處于可見狀态的非空單元格個數。用結果減1，計算結果就是和序号一樣了，而且篩選後也能保持連續。

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

如下圖所示，在對E列執行篩選後，需要計算數量乘以單價的總額。E2單元格公式為：=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

要計算篩選後的乘積，問題的關鍵是判斷數據是不是處于可見狀态。這個可見狀态怎麼判斷呢？需要讓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函數對乘積進行求和。

如下圖所示，對E列部門執行篩選後，要計算工齡大于3的人數。E2單元格公式為：=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

前半部分計算原理與上一個示例相同，核心也是判斷是單元格否處于可見狀态。公式後半段的統計條件(G4:G16>3)與前半段的判斷結果相乘，表示兩個條件同時符合，也就是處于可見狀态、并且G列大于3的個數。

如下圖所示，對E列部門名稱進行篩選後，希望D1單元格的标題自動變更為對應的部門名稱，公式為：=LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&”統計表”

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列的内容。最終目的就是實現篩選後，提取最後一個處于顯示狀态的單元格内容。将提取到的内容與&”統計表”連接，變成可自動更新的表格标題。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!