1、篩選后添加序號(hào)
如下圖所示，要在篩選狀態(tài)下也能保持連續(xù)的序號(hào)，咱們可以先取消篩選，在D2單元格輸入以下公式，然后下拉：
=SUBTOTAL(3,E$1:E2)-1
SUBTOTAL函數(shù)只統(tǒng)計(jì)可見(jiàn)單元格內(nèi)容。
第一參數(shù)使用3，表示執(zhí)行COUNTA函數(shù)的計(jì)算規(guī)則，也就是對(duì)第二參數(shù)統(tǒng)計(jì)可見(jiàn)單元格的個(gè)數(shù)。
第二參數(shù)使用一個(gè)動(dòng)態(tài)擴(kuò)展的范圍E$1:E2，隨著公式的下拉，這個(gè)范圍會(huì)依次變成E$1:E3，E$1:E4，E$1:E5，……
公式始終計(jì)算E列從第一行至公式所在行這個(gè)區(qū)域中，處于可見(jiàn)狀態(tài)的非空單元格個(gè)數(shù)。用結(jié)果減1，計(jì)算結(jié)果就是和序號(hào)一樣了，而且篩選后也能保持連續(xù)。
注意，注意，這個(gè)公式如果換成從=SUBTOTAL(3,E$2:E2)，也就是從公式所在行開(kāi)始的話，序號(hào)結(jié)果雖然沒(méi)問(wèn)題，但是篩選時(shí)最后一行會(huì)被Excel當(dāng)成匯總行而始終顯示。
2、篩選后相乘
如下圖所示，在對(duì)E列執(zhí)行篩選后，需要計(jì)算數(shù)量乘以單價(jià)的總額。
E2單元格公式為：
=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)
要計(jì)算篩選后的乘積，問(wèn)題的關(guān)鍵是判斷數(shù)據(jù)是不是處于可見(jiàn)狀態(tài)。
這個(gè)可見(jiàn)狀態(tài)怎么判斷呢？
需要讓OFFSET和SUBTOTAL函數(shù)來(lái)結(jié)合一下。
首先使用OFFSET函數(shù)，以E3單元格為基點(diǎn)，依次向下偏移1~13行，得到一個(gè)多維引用。這個(gè)多維引用中包含13個(gè)一行一列的引用區(qū)域，也就是對(duì)E4~E16的單個(gè)單元格分別進(jìn)行引用。
接下來(lái)使用SUBTOTAL函數(shù)，第一參數(shù)使用3，即依次統(tǒng)計(jì)E4~E16每個(gè)單元格中的可見(jiàn)單元格個(gè)數(shù)，如果單元格處于顯示狀態(tài)，則對(duì)這個(gè)單元格的統(tǒng)計(jì)結(jié)果為1，否則統(tǒng)計(jì)結(jié)果為0。得到類似以下效果的內(nèi)存數(shù)組：
{1;0;1;1;1;1;0;0;1;1;0;1;0}
再使用以上結(jié)果乘以F列的數(shù)量和G列的單價(jià)，如果單元格處于顯示狀態(tài)，則相當(dāng)于1*數(shù)量*單價(jià)，否則相當(dāng)于0*數(shù)量*單價(jià)。
最后使用SUMPRODUCT函數(shù)對(duì)乘積進(jìn)行求和。
3、篩選后按條件計(jì)數(shù)
如下圖所示，對(duì)E列部門(mén)執(zhí)行篩選后，要計(jì)算工齡大于3的人數(shù)。
E2單元格公式為：
=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))
前半部分計(jì)算原理與上一個(gè)示例相同，核心也是判斷是單元格否處于可見(jiàn)狀態(tài)。
公式后半段的統(tǒng)計(jì)條件(G4:G16>3)與前半段的判斷結(jié)果相乘，表示兩個(gè)條件同時(shí)符合，也就是處于可見(jiàn)狀態(tài)、并且G列大于3的個(gè)數(shù)。
4、篩選后自動(dòng)更正標(biāo)題
如下圖所示，對(duì)E列部門(mén)名稱進(jìn)行篩選后，希望D1單元格的標(biāo)題自動(dòng)變更為對(duì)應(yīng)的部門(mén)名稱，公式為：
=LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&”統(tǒng)計(jì)表”
SUBTOTAL與OFFSET函數(shù)結(jié)合部分，目的仍然是判斷D列的單元格是否為可見(jiàn)狀態(tài)。得到由0和1組成的內(nèi)存數(shù)組：
{0;1;0;0;0;0;1;1;1;1;0;1;0;1;0}
用0/這個(gè)內(nèi)存數(shù)組，得到由0和錯(cuò)誤值構(gòu)成的新內(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ù)組中查找最后一個(gè)0的位置，并返回對(duì)應(yīng)位置的E列的內(nèi)容。
最終目的就是實(shí)現(xiàn)篩選后，提取最后一個(gè)處于顯示狀態(tài)的單元格內(nèi)容。
將提取到的內(nèi)容與&”統(tǒng)計(jì)表”連接，變成可自動(dòng)更新的表格標(biāo)題。
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任，
本站有權(quán)保留或刪除有爭(zhēng)議評(píng)論。
參與本評(píng)論即表明您已經(jīng)閱讀并接受
上述條款。