excel篩選 SUBTOTAL篩選狀態(tài)下的計算套路,最近到了季度匯報的時候，掌握一手excel技能在此刻顯得多么重要，為了是你的excel看起來更高大上，今天教大家設(shè)置excel篩選 SUBTOTAL，今天我們的干貨專業(yè)全稱為“excel篩選 SUBTOTAL篩選狀態(tài)下的計算套路”能不能以絢爛的技能征服領(lǐng)導(dǎo)，就在這一波了~

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

一、篩選后添加序號

D2單元格公式

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

簡要說明：

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

2、第一參數(shù)使用3，表示執(zhí)行CoUNTA函數(shù)的計算規(guī)則。

3、公式始終計算E列從第一行至公式所在行這個區(qū)域中，處于可見狀態(tài)的非空單元格個數(shù)。用結(jié)果減1，實現(xiàn)序號效果。

二、篩選后相乘

E2單元格公式為：

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

簡要說明：

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

2、SUBTOTAL函數(shù)第一參數(shù)使用3，即依次統(tǒng)計E4~E16每個單元格的可見單元格個數(shù)，如果單元格處于顯示狀態(tài)，則對這個單元格的統(tǒng)計結(jié)果為1，否則統(tǒng)計結(jié)果為0。

得到類似以下效果：

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

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

4、最后使用SUMPRODUCT函數(shù)對乘積進(jìn)行求和。

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

E2單元格公式為：

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

簡要說明：

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

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

四、篩選后自動更正標(biāo)題

D1單元格公式為：

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

簡要說明：

1、SUBTOTAL與OFFSET函數(shù)結(jié)合部分，計算原理與示例2相同。

得到由0和1組成的內(nèi)存數(shù)組：

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

2、用0/這個內(nèi)存數(shù)組，如果是1，得到0，如果是0，則為錯誤值。

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

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

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

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