Note: The other languages of the website are Google-translated. Back to English

如何在Excel中僅匯總經過過濾或可見的單元格?

在Excel中對一列數字求和也許很容易,但是有時您需要過濾或隱藏一些數據以滿足您的條件。 隱藏或過濾後,現在您只想只累加過濾或可見值。 如果在Excel中應用Sum函數,則將添加所有值(包括隱藏數據),在這種情況下,如何只對Excel中經過過濾或可見的單元格值求和?


用公式對僅過濾或可見單元格值求和

有了這個 小計 該函數會忽略已被過濾器排除的行,因此您可以輕鬆地僅添加可見單元格。 您可以這樣做:

假設您具有一系列數據,並且已根據需要對其進行了過濾,請參見屏幕截圖:

1。 在空白單元格(例如C13)中,輸入以下公式: =小計(109,C2:C12) (109 表示在對數字求和時,隱藏的值將被忽略; C2:C12 是您將忽略忽略的行而求和的範圍。),然後按 Enter 鍵。

備註: 如果工作表中有隱藏的行,此公式還可以幫助您僅匯總可見的單元格。 但是,此公式不能等於忽略隱藏列中的單元格。

僅在指定範圍內對可見單元格進行求和/計數/平均,而忽略隱藏或過濾的單元格/行/列

通常,SUM / Count / Average函數將對是否隱藏/過濾的物質像元計數指定範圍內的所有像元。 小計功能只能忽略隱藏的行來求和/計數/求平均值。 但是,Kutools for Excel 可見的 / 清晰可見 / 平均可見度 函數將忽略任何隱藏的單元格,行或列,輕鬆地計算指定範圍。


廣告總數僅計算平均可見單元格

使用用戶定義函數僅對過濾後或可見的單元格值求和

如果您對以下代碼感興趣,它也可以幫助您僅匯總可見單元格。

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到“模塊”窗口中。

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

3。 保存此代碼並輸入公式 = SumVisible(C2:C12) 變成一個空白單元格。 看截圖:

4。 然後按 Enter 鍵,您將得到想要的結果。


使用Kutools for Excel僅對已過濾或可見的單元格求和/計數/平均

如果您安裝了Kutools for Excel,則可以快速輕鬆地計算Excel中僅可見或過濾出的單元格的總和/計數/平均值。

Excel的Kutools - 包括 300 多個方便的 Excel 工具。 全功能免費試用 30-天,無需信用卡! 立即行動吧!

例如,您只想對可見的單元格求和,請選擇將求和結果放在其中的單元格,鍵入公式 = SUMVISIBLE(C3:C12) (C3:C13是您僅對可見單元格求和的範圍),然後按 Enter 鍵。

然後,在忽略所有隱藏單元的情況下計算求和結果。 看截圖:

僅計算可見細胞,請應用此公式 = COUNTVISIBLE(C3:C12); 對於僅可見單元的平均,請應用此公式 =平均可見度(C3:C12).

備註: 如果您不記得確切的公式,可以按照以下步驟輕鬆地對可見單元格進行求和/計數/平均:

1.選擇要放入求和結果的單元格,然後單擊 庫工具 > 操作功能 > 統計與數學 > 可見的 (或 平均可見, 清晰可見 根據需要)。 看截圖:

2.在打開的“函數參數”對話框中,請指定匯總忽略的單元格的範圍,然後單擊 OK 按鈕。 看截圖:

Excel的Kutools - 包括 300 多個方便的 Excel 工具。 全功能免費試用 30-天,無需信用卡! 立即行動吧!

然後,在忽略所有隱藏單元的情況下計算求和結果。


演示:僅對過濾後或可見的單元格求和/計數/平均


Excel的Kutools 包含 300 多種 Excel 便捷工具,30 天內免費試用,無限制。 立即下載並免費試用!

通過在Excel中刪除隱藏的行,輕鬆匯總/計數僅過濾/可見的單元格

當在Excel中對過濾出的單元格進行匯總/計數時,SUM函數或Count函數將不會忽略隱藏的單元格。 如果隱藏/過濾的行被刪除,我們只能輕鬆地對可見單元格進行求和或計數。 您可以嘗試Kutools for Excel的 刪除隱藏(可見)行和列 實用程序來解決它。


廣告刪除隱藏行第3列

相關文章:


最佳辦公效率工具

Kutools for Excel 解決了你的大部分問題,並將你的生產力提高了 80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
按評論排序
留言 (26)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
大家好,我想在不同的列中添加兩個值,但只有在我使用過濾器時才使用可見單元格。 例如。 B3=B2+A3 我曾嘗試使用 SUBTOTAL 函數,但這不起作用。 請指教,提前謝謝! 問候,
網站主持人對此評論進行了最小化
在您將選擇應用過濾器的行的頂部創建一行。 在您需要總計的列中寫入公式 =subtotal(9,{start column no.},{end column no}) 例如,如果對於列 A,我需要基於 b 列中的值的小計,那麼如果標題行對於數據在第 2 行,第 1 行為空白,然後將公式作為 =subtotal(1,A9:a2) 放入 A999 列中。當您更改 B 列上的過濾條件時,您將看到總數發生變化。
網站主持人對此評論進行了最小化
如何將條件添加到第二個選項。 例如,如果我有一系列值,我希望它在列中選擇具有“X”標準的值。
網站主持人對此評論進行了最小化
當您取消隱藏行時,“=SumVisible()”會反映錯誤。 是否可以更改“功能”以反映“零”或“無隱藏行”?
網站主持人對此評論進行了最小化
謝謝 Milinda - 我已經在“Book 1”和“Personal.xlsb”中插入了模塊,但是當我打開一個新文件時,我似乎無法訪問該功能。 請問我做錯了什麼?
網站主持人對此評論進行了最小化
嗨,當我起訴此功能並進行過濾時,總數不顯示。 為什麼?
網站主持人對此評論進行了最小化
我有一個類似的問題,我想計算不同行中兩個單元格的差異,我使用數據過濾器來區分顯示哪些行。 我通過以下方法解決了這個問題: 1. 創建一個新列 [或行,取決於隱藏的內容] 並將其標記為可見。 2. 創建一個新列,用於維護要刪除的最後一個“可見”行的值。 3. 在“可見”列中,使用公式: =+SUBTOTAL(102,cell_in_row_or_column) 這將在您鍵入後立即為 1,但如果單元格不可見,則為 0。 4. 在為第 2 步創建的列中,您需要一個簡單的公式:+IF(A,B,C); 其中 A = 返回可見狀態的單元格,B 您要從同一行操作的值[只是一個副本],C 來自前一行中同一單元格的值,因為該單元格不可見。 這樣,最後一個“可見”項在幕後傳播到可見行上方的行。 這允許您創建對前一行執行操作的簡單公式。 這適用於您需要處理單個數據點的許多情況,而不是使用小計更好的範圍。
網站主持人對此評論進行了最小化
非常感謝!! 這很有幫助:)
網站主持人對此評論進行了最小化
該解決方案對我有用。 :D
網站主持人對此評論進行了最小化
我能夠讓您的 VBA 為上面的 =SUMVISIBLE 正常工作。 但是,如果我的數據垂直運行並且我正在隱藏行。 有沒有辦法對其進行編程,以便您可以讓數據水平運行並且在隱藏列時它仍然可以工作? 謝謝!
網站主持人對此評論進行了最小化
抱歉,我弄亂了底部的帖子安全代碼,似乎我的問題可能已被刪除,因為它要求我嘗試另一個問題。 如果我的數據是垂直的並且我隱藏了行,我可以讓 SUMVISIBLE 的 VBA 代碼正常工作。 但是,如果我的數據水平運行並且我想隱藏列,則不會。 有沒有辦法對此進行編程? 謝謝!
網站主持人對此評論進行了最小化
這太棒了! 非常感謝。
網站主持人對此評論進行了最小化
我試圖使用 AVERAGEVISIBLE 函數來平均一列中的 12 個最大值,這在正常的 AVERAGE 函數中工作為 =AVERAGE(LARGE(E971:E1540,{1,2,3,4,5,6,7,8,9,10,11,12 ,XNUMX})) 但是當我嘗試使用平均可見函數時,它返回#VALUE!,有什麼想法嗎?
網站主持人對此評論進行了最小化
我如何從過濾表中的 excel 公式中跳過隱藏單元格
網站主持人對此評論進行了最小化
如何從過濾表中的excel公式中跳過隱藏單元格......

由於過濾器,黃色單元格涉及隱藏單元格....
網站主持人對此評論進行了最小化
嗨,
你需要做什麼樣的計算? Kutools for Excel 支持三個函數來計數/求和/平均忽略所有隱藏單元格。
網站主持人對此評論進行了最小化
當我在過濾的 excel 表中應用 G3-K2 公式並拖動公式時,它包括隱藏的單元格

例如



有效期提升數量
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


現在有效值是在被提升數量減去後得出的(如第二個值 2=26931.18-27054.59)

有效性中的第三個值=有效性中的第二個值-提升數量中的第二個值。 等等.....

現在此工作表已過濾,當我在有效性列中拖動公式時,由於過濾器,它包括隱藏的單元格。

這給了我錯誤的結果
網站主持人對此評論進行了最小化
嗨,
正常的 =G3-K2 不會通過拖動複製來忽略任何隱藏的單元格/行/列。 很抱歉,我無法為您找到合適的公式。 本文中討論的所有公式或方法都是關於忽略隱藏單元格的總和/計數/平均值。
網站主持人對此評論進行了最小化
有效期提升數量
27054.59 123.41
26931.18 330.98
26600.20 (26600.2=26931.18-330.98) 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06
網站主持人對此評論進行了最小化
當我在過濾的 excel 表中應用 G3-K2 公式並拖動公式時,它包括隱藏的單元格

例如



有效期提升數量
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


現在有效值是在被提升數量減去後得出的(如第二個值 2=26931.18-27054.59)

有效性中的第三個值=有效性中的第二個值-提升數量中的第二個值。 等等.....

現在此工作表已過濾,當我在有效性列中拖動公式時,由於過濾器,它包括隱藏的單元格。

這給了我錯誤的結果
網站主持人對此評論進行了最小化
或像這樣



列 a 列 d

100 10

90 10

80 10



其中 90=100-10, 80=90-10,以此類推…………

拖動公式包括公式中的隱藏單元格
網站主持人對此評論進行了最小化
HOLA,

Quiero sumar dentro de un rango de fila que al ocultar columnas sume sólo las visibles。
不,他 logrado hacerlo,alguna fórmula? o tendría que hacerse con una marcro?

非常感謝你!
網站主持人對此評論進行了最小化
HOLA,

No me resulta sumar el rango de una fila considerando sólo las columnas visibles。

Alguna 公式或 alguna 宏?

謝謝!
網站主持人對此評論進行了最小化
你好,克里斯托瓦爾,
抱歉,我無法清楚地理解你的問題,你能用英語解釋一下你的問題嗎?
或者您可以插入屏幕截圖或文件來描述您的問題。
感謝您!
網站主持人對此評論進行了最小化
你好,
Moi j'ai un problème compare mais différent, en gros quand je filtre mes colonnes et que je veux copier la même valeur dans mes lignes visibles, toute les lignes qui sont entre mes lignes visibles sont elles aussi modifiées。
Comment faire pour que seule mes lignes visibles soient modifiés。
網站主持人對此評論進行了最小化
你好,盧克斯

您的意思是僅將單元格值複製並粘貼到可見單元格嗎? 如果是這樣,也許以下文章可以幫助您:
https://www.extendoffice.com/documents/excel/2331-excel-paste-data-into-filtered-list.html
https://www.extendoffice.com/documents/excel/2617-excel-paste-to-visible-filtered-cells.html

請嘗試一下,謝謝!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護