提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何僅在Excel中求和過濾或可見的單元格?

您可能很容易在Excel中總結一列數字,但有時您需要過濾或隱藏一些數據以符合您的標準。 隱藏或過濾後,現在只需要合計已過濾或可見的值。 如果您在Excel中應用Sum函數,則會添加包括隱藏數據在內的所有值,在這種情況下,如何才能在Excel中求和已過濾或可見的單元格值?

用公式只計算已過濾或可見的單元格值

用戶定義函數只求過濾或可見單元格值

總和/計數/平均值僅使用Kutools for Excel過濾或可見單元格

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

正常的SUM / Count / Average函數將計算指定範圍內所有單元格上的細胞被隱藏/過濾或不被過濾。 雖然小計函數只能通過忽略隱藏行來求和/計數/平均值。 但是,Kutools for Excel SUMVISIBLE / COUNTVISIBLE / AVERAGEVISIBLE 函數將輕鬆計算指定範圍,忽略任何隱藏的單元格,行或列。 全功能免費試用30天!
廣告總數只計算平均可見單元格

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文本; 將數字和貨幣轉換為英語單詞...
  • 合併工具:多個工作簿和表格合二為一; 合併多個單元格/行/列而不丟失數據; 合併重複行和總和...
  • 拆分工具:根據價值將數據拆分為多個表格; 一個工作簿到多個Excel,PDF或CSV文件; 一列到多列......
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 創建郵件列表和 通過Cell的價值發送電子郵件...
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 與Office 2007-2019和365一起使用; 支持所有語言; 在您的企業或組織中輕鬆部署。

箭頭藍色右泡 用公式只計算已過濾或可見的單元格值

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

假設您有一系列數據,並且已根據需要進行過濾,請參閱截圖:

1。 在一個空白單元格中,例如C13輸入這個公式: =小計(109,C2:C12) (109 表示當你總結數字時,隱藏的值將被忽略; C2:C12 是您將忽略過濾行的總和。),然後按 輸入 鍵。

注意: 如果工作表中存在隱藏行,此公式也可以幫助您僅匯總可見單元格。 但是,這個公式不能與忽略隱藏列中的單元格。


箭頭藍色右泡 用戶定義函數只求過濾或可見單元格值

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

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。 然後按 輸入 鍵,你會得到你想要的結果。


箭頭藍色右泡 總和/計數/平均值僅使用Kutools for Excel過濾或可見單元格

如果您安裝了Excel的Kutools,您可以快速計算Excel中的總和/計數/平均值,只需在Excel中可視化或過濾出單元格。

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

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

僅計算可見單元格,請應用此公式 = COUNTVISIBLE(C3:C12); 為了僅平均可見單元格,請應用此公式 = AVERAGEVISIBLE(C3:C12).

注意: 如果您完全不記得公式,則可以按照以下步驟輕鬆地對可見單元求和/計數/平均:

1。 選擇您要將求和結果放入的單元格,然後單擊 Kutools > 功能 > 統計與數學 > SUMVISIBLE (或 AVERAGEVISBLE, COUNTVISIBLE 如你所需)。 看截圖:

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

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

然後計算求和結果,忽略所有隱藏的單元格。


在這個視頻中, Kutools - Kutools Plus 選項卡添加 Kutools for Excel。 如果需要,請點擊 30天免費試用,不受限制!

通過刪除Excel中的隱藏行,輕鬆地僅對已過濾/可見單元格進行求和/計數

當在Excel中對過濾出的單元格進行求和/計數時,SUM函數或Count函數不會忽略隱藏的單元格。 如果隱藏/過濾行被刪除,我們只能簡單地求和或計算可見單元格。 您可以嘗試Kutools for Excel's 刪除隱藏(可見)行和列 實用程序來解決它。 全功能免費試用30天!
廣告刪除隱藏行列3

箭頭藍色右泡相關文章:

如何在Excel中求和絕對值?

如何快速匯總Excel中的每一行或第n列?


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

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

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    half yodha · 1 years ago
    how to skip hidden cells from excel formulae in filtered sheet....

    yellow coloured cells are involving hidden cells due to filter....
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      What kinds of calculation do you need to do? Kutools for Excel supports three functions to count/sum/average ignoring all hidden cells.
      • To post as a guest, your comment is unpublished.
        half yodha · 1 years ago
        or like this



        column a column d

        100 10

        90 10

        80 10



        where 90=100-10, 80=90-10, and so on.........

        dragging the formula includes hidden cells in formula
      • To post as a guest, your comment is unpublished.
        half yodha · 1 years ago
        When i am applying say G3-K2 Formula in filtered excel sheet & dragging the formula, it includes hidden cells

        for example



        Validity Lifting Qty
        27054.59 123.41
        26931.18 330.98
        26600.20 493.66
        26106.54 476.38
        25630.16 480.64
        25149.52 577.06


        now value in validity comes after get subtracted by lifted qty (like 2nd value 26931.18=27054.59-123.41)

        3rd value in validity=2nd value in validity-2nd value in lifted qty. and so on.....

        now this sheet is filtered & when i drag the formulae in validity column, it includes hidden cells, due to filter.

        which gives me wrong result
      • To post as a guest, your comment is unpublished.
        half yodha · 1 years ago
        Validity Lifting Qty
        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
      • To post as a guest, your comment is unpublished.
        half yodha · 1 years ago
        When i am applying say G3-K2 Formula in filtered excel sheet & dragging the formula, it includes hidden cells

        for example



        Validity Lifting Qty
        27054.59 123.41
        26931.18 330.98
        26600.20 493.66
        26106.54 476.38
        25630.16 480.64
        25149.52 577.06


        now value in validity comes after get subtracted by lifted qty (like 2nd value 26931.18=27054.59-123.41)

        3rd value in validity=2nd value in validity-2nd value in lifted qty. and so on.....

        now this sheet is filtered & when i drag the formulae in validity column, it includes hidden cells, due to filter.

        which gives me wrong result
        • To post as a guest, your comment is unpublished.
          Tang Kelly · 1 years ago
          Hi,
          The normal =G3-K2 will not ignore any hidden cells/rows/column by dragging to copy. I am sorry I can not figure out a proper formula for your. All formulas or methods talked in this article is just about sum/count/average ignoring hidden cells.
  • To post as a guest, your comment is unpublished.
    half yodha · 1 years ago
    how i can skip hidden cells from excel formulae in filtered sheet
  • To post as a guest, your comment is unpublished.
    Sab6247 · 3 years ago
    Im trying to use the AVERAGEVISIBLE function to average the 12 largest values in a column, this works in the normal AVERAGE function as

    =AVERAGE(LARGE(E971:E1540,{1,2,3,4,5,6,7,8,9,10,11,12})) however when i try to use the average visible function it returns #VALUE!, any ideas?
  • To post as a guest, your comment is unpublished.
    JPas · 4 years ago
    This is awesome! Thanks so much.
  • To post as a guest, your comment is unpublished.
    DataCruncher · 4 years ago
    Sorry I messed up the posts security code at bottom and seems like my question may have been deleted as it asked me to try another one.

    I can get the VBA code for SUMVISIBLE to work well if my data is vertical and I hide rows. However it doesn't if my data runs horizontal and I want to hide columns. Is there a way to program this? Thanks!
  • To post as a guest, your comment is unpublished.
    DataCrucher · 4 years ago
    I am able to get your VBA for =SUMVISIBLE above to work good.
    However just if my data runs vertically and I am hiding rows.
    Is there a way to program it so you can have your data run horizontally and it still work when you hide columns? THANKS!
  • To post as a guest, your comment is unpublished.
    ExcelNewbie · 4 years ago
    The solution worked for me. :D
  • To post as a guest, your comment is unpublished.
    MJK · 5 years ago
    Thanks a lot!! It was very helpful:)
  • To post as a guest, your comment is unpublished.
    Steve Ostroff · 5 years ago
    I had a similar issue where I wanted to calculate differences from two cells in different rows where I use a Data Filter to discriminate which rows are displayed. I solved the problem in the following method:

    1. Create a new column [or Row depending on what is hidden] and label it Visible.
    2. Create a new column that will be used to maintain the value from the last "visible" row that you want to bring down.
    3. In the "Visible" column, use the formula: =+SUBTOTAL(102,cell_in_row_or_column)
    This will have a 1 as soon as you type it, but will be 0 if the cell is not visible.
    4. In the column created for step 2, you need a simple formula: +IF(A,B,C); where A=cell returning Visible status, B the value you want to operate on from the same row [just a copy], C the value from the same cell in the row previous since the cell is not visible.

    In this way, the last "Visible" item is propagated behind the scenes to the row above the visible row(s). This allows you to create simple formulas performing the operations on the preceding row. This works for many situations where you need to deal with individual data points, as opposed to ranges which are better with Subtotal.
  • To post as a guest, your comment is unpublished.
    Pretti · 5 years ago
    hi when i sue this function, and filter, the totals dont display. why?
  • To post as a guest, your comment is unpublished.
    Roy Rouessart · 5 years ago
    When you unhide rows then "=SumVisible()" reflects error.
    Is it possible to change "Function" to reflect either "Zero" or "No hidden Rows"?
    • To post as a guest, your comment is unpublished.
      Roy Rouessart · 5 years ago
      Thanks Milinda - I have inserted the module in both "Book 1" and "Personal.xlsb" but when I open a new file I don't seem able to access the function. Please what have I done wrong?
  • To post as a guest, your comment is unpublished.
    alex17111 · 5 years ago
    How do i add criteria to the 2nd option.
    Eg.if i have a range of values i want it to select values which have criteria of "X" in a column.
  • To post as a guest, your comment is unpublished.
    Paul_007 · 5 years ago
    Hi All,

    I would like to add two values in different collumns, but only with visible cells when I use a filter.
    eg. B3=B2+A3
    I have tried to use the SUBTOTAL function, but that does not work.
    Please advise, thanks in advance!
    regards,
    • To post as a guest, your comment is unpublished.
      Milind Ranka · 5 years ago
      Create a row on top of the row you will choose to apply filter.
      In the column for which you need total write the formula
      =subtotal(9,{start column no.},{end column no})

      For example
      if for column A i need the subtotals based on value in column b
      then if the header row for data is in row 2 and row 1 is blank
      then put the formula in column A1 as =subtotal(9,A2:a999)

      As you change the filter condition on column B you will see total changing.