搜尋
首頁專題excelExcel篩選狀態下的計算總結

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

Excel篩選狀態下的計算總結

相關學習推薦:excel教學

1、篩選後新增序號

如下圖所示,要在篩選狀態下也能保持連續的序號,咱們可以先取消篩選,在D2單元格輸入以下公式,然後下拉:

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

Excel篩選狀態下的計算總結

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)

Excel篩選狀態下的計算總結

要計算篩選後的乘積,問題的關鍵在於判斷資料是否處於可見狀態。

這個可見狀態怎麼判斷呢?

需要讓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))

Excel篩選狀態下的計算總結

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

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

4、篩選後自動更正標題

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

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

Excel篩選狀態下的計算總結

#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中文網其他相關文章!

陳述
本文轉載於:Excel Home。如有侵權,請聯絡admin@php.cn刪除
Excel中的中位公式 - 實際示例Excel中的中位公式 - 實際示例Apr 11, 2025 pm 12:08 PM

本教程解釋瞭如何使用中位功能計算Excel中數值數據中位數。 中位數是中心趨勢的關鍵度量

Google電子表格Countif函數帶有公式示例Google電子表格Countif函數帶有公式示例Apr 11, 2025 pm 12:03 PM

Google主張Countif:綜合指南 本指南探討了Google表中的多功能Countif函數,展示了其超出簡單單元格計數的應用程序。 我們將介紹從精確和部分比賽到Han的各種情況

Excel共享工作簿:如何為多個用戶共享Excel文件Excel共享工作簿:如何為多個用戶共享Excel文件Apr 11, 2025 am 11:58 AM

本教程提供了共享Excel工作簿,涵蓋各種方法,訪問控制和衝突解決方案的綜合指南。 現代Excel版本(2010年,2013年,2016年及以後)簡化了協作編輯,消除了M的需求

如何將Excel轉換為JPG-保存.xls或.xlsx作為圖像文件如何將Excel轉換為JPG-保存.xls或.xlsx作為圖像文件Apr 11, 2025 am 11:31 AM

本教程探討了將.xls文件轉換為.jpg映像的各種方法,包括內置的Windows工具和免費的在線轉換器。 需要創建演示文稿,安全共享電子表格數據或設計文檔嗎?轉換喲

excel名稱和命名範圍:如何定義和使用公式excel名稱和命名範圍:如何定義和使用公式Apr 11, 2025 am 11:13 AM

本教程闡明了Excel名稱的功能,並演示瞭如何定義單元格,範圍,常數或公式的名稱。 它還涵蓋編輯,過濾和刪除定義的名稱。 Excel名稱雖然非常有用,但通常是氾濫的

標準偏差Excel:功能和公式示例標準偏差Excel:功能和公式示例Apr 11, 2025 am 11:01 AM

本教程闡明了平均值的標準偏差和標準誤差之間的區別,指導您掌握標準偏差計算的最佳Excel函數。 在描述性統計中,平均值和標準偏差為interinsi

Excel中的平方根:SQRT功能和其他方式Excel中的平方根:SQRT功能和其他方式Apr 11, 2025 am 10:34 AM

該Excel教程演示瞭如何計算正方根和n根。 找到平方根是常見的數學操作,Excel提供了幾種方法。 計算Excel中正方根的方法: 使用SQRT函數:

Google表基礎知識:了解如何使用Google電子表格Google表基礎知識:了解如何使用Google電子表格Apr 11, 2025 am 10:23 AM

解鎖Google表的力量:初學者指南 本教程介紹了Google Sheets的基礎,這是MS Excel的強大而多才多藝的替代品。 了解如何輕鬆管理電子表格,利用關鍵功能並協作

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
4 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
4 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您聽不到任何人,如何修復音頻
4 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.聊天命令以及如何使用它們
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

將Eclipse與SAP NetWeaver應用伺服器整合。

mPDF

mPDF

mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),

Dreamweaver Mac版

Dreamweaver Mac版

視覺化網頁開發工具

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

這個專案正在遷移到osdn.net/projects/mingw的過程中,你可以繼續在那裡關注我們。 MinGW:GNU編譯器集合(GCC)的本機Windows移植版本,可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔;包括對MSVC執行時間的擴展,以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。