Excel篩選狀態下的計算總結

這篇文章為大家帶來了關於excel的相關知識，主要介紹了關於篩選後怎麼添加序號、篩選後相乘、篩選後按條件計數等內容，下面一起來看一下，希望對大家有幫助。

相關學習推薦：excel教學

1、篩選後新增序號

如下圖所示，要在篩選狀態下也能保持連續的序號，咱們可以先取消篩選，在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當成匯總行而始終顯示。

2、篩選後相乘

如下圖所示，在對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函數對乘積進行求和。

3、篩選後依條件計數

如下圖所示，對​​E列部門執行篩選後，要計算工齡大於3的人數。

E2單元格公式為：

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

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

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

4、篩選後自動更正標題

如下圖所示，對​​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列的內容。

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

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

相關學習推薦：excel教學

以上是Excel篩選狀態下的計算總結的詳細內容。更多資訊請關注PHP中文網其他相關文章！