vba 对 Excel 电子表格中的可见（或过滤）范围求和

Sum visible (or filtered) range in an Excel Spreadsheet

提问by CustomX

In Excel, I need to filter and display the COUNTIF& SUMof both the global rangeand the visible (or filtered) range.

在Excel中，我需要过滤并显示COUNTIF＆SUM两者的全球范围和可见（或经过滤的）范围。

I can already display the COUNTIF& SUMof the global rangewith the following code.

我已经可以显示COUNTIF和SUM的的全球范围用下面的代码。

AtmCount = Application.WorksheetFunction.CountIf(Range("X3:X4533"), ">0")
AtmSum = Application.WorksheetFunction.Sum(Range("X3:X4533"))

I can also get the COUNTof the visible (or filtered) rangeas follows:

我还可以获得可见（或过滤）范围的计数，如下所示：

AtmCurrentCount = Range("X3:X4533").SpecialCells(xlCellTypeVisible).Count

However, this still leaves the SUMof visible (or filtered) rangeoutstanding.

然而，这仍然让SUM的可见（或过滤）范围内突出。

AtmCurrentSum = ???

I really stuck. Please, can somebody help me?

我真的卡住了。拜托，有人可以帮我吗？

采纳答案by Jon Crowell

This will do what you want. Set visibleTotal to the appropriate data type for the total, and change the ws and rng objects to match what you have in your workbook.

这会做你想做的。将visibleTotal 设置为合适的总计数据类型，并更改ws 和rng 对象以匹配您在工作簿中拥有的内容。

Sub SumVisible()
    Dim ws As Worksheet
    Dim rng As Range
    Dim visibleTotal As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("B1:B7")

    ws.AutoFilterMode = False
    rng.AutoFilter field:=1, Criteria1:=5

    visibleTotal = Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible))
    ' print to the immediate window
    Debug.Print visibleTotal
End Sub

In case you only want to sum part of the filtered range (e.g. you filter on column A but want the sum of column B), see this question and answer: Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table.

如果您只想对部分过滤范围求和（例如，您对 A 列进行过滤但想要 B 列的总和），请参阅此问题和答案：从过滤表的一列复制/粘贴/计算可见单元格。

回答by jainashish

If one need to COUNT the number of visible items in a filtered list, then use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter.

如果需要计算过滤列表中可见项目的数量，请使用 SUBTOTAL 函数，该函数会自动忽略过滤器隐藏的行。

The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, AVERAGE, PRODUCT and many more (See the table below). It automatically ignores items that are not visible in a filtered list or table. This makes it ideal for showing how many items are visible in a list, the subtotal of visible rows, etc. It also provide control rows hided manually manually.

SUBTOTAL 函数可以执行 COUNT、SUM、MAX、MIN、AVERAGE、PRODUCT 等计算（参见下表）。它会自动忽略过滤列表或表格中不可见的项目。这使其非常适合显示列表中可见的项目数、可见行的小计等。它还提供手动隐藏的控制行。

The solution to your question would be to count the number of non-blank rows visible in Column A and Column B when a filter is active, use:

您的问题的解决方案是计算筛选器处于活动状态时在 A 列和 B 列中可见的非空白行数，使用：

AtmCurrentSum = Application.WorksheetFunction.Subtotal(109, Range("$X:$X33"))

---

---

---

---

Points to remember when you apply SUBTOTAL formula:

应用 SUBTOTAL 公式时要记住的要点：

• When function_num (First argument) is between 1-11, SUBTOTAL includes values that are hidden manually but ignore hidden by filter.
• When function_num is between 101-111, SUBTOTAL excludes all kind of hidden values.
• In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
• SUBTOTAL ignores other subtotals that exist in references are ignored to prevent double-counting
• SUBTOTAL only work with vertical data values arranged vertically.
• In Horizontal Hidden Columns, values are always included and never ignored.

• 当 function_num（第一个参数）介于 1-11 之间时，SUBTOTAL 包括手动隐藏但忽略过滤器隐藏的值。
• 当 function_num 在 101-111 之间时，SUBTOTAL 排除所有类型的隐藏值。
• 在过滤列表中，SUBTOTAL 总是忽略隐藏行中的值，而不管 function_num。
• SUBTOTAL 忽略引用中存在的其他小计被忽略以防止重复计算
• SUBTOTAL 仅适用于垂直排列的垂直数据值。
• 在 Horizo​​ntal Hidden Columns 中，值总是被包含在内并且永远不会被忽略。

回答by JosieP

if you only want sum rather than sumif

如果你只想要 sum 而不是 sumif

AtmCurrentSum = application.worksheetfunction.subtotal(9, Range("X3:X4533"))

回答by Sudhanshu Soni

If you are using Excel 2016, this will work

如果您使用的是 Excel 2016，这将起作用

Sum = Application.WorksheetFunction.Subtotal(9, Range("$D2:D" & Rows(Rows.Count).End(xlUp).Row))

Sum = Application.WorksheetFunction.Subtotal(9, Range("$D2:D" & Rows(Rows.Count).End(xlUp).Row))

9 specifies that you want to sum of visible rows My data in which I had to find sum was in D column Hope this will help :)

9 指定您想要对可见行求和 我必须在其中找到总和的数据在 D 列中希望这会有所帮助:)

回答by SUNIL KUMAR

The give function Sub SumVisible() did not worked for me instead i used Subtotal function.Caution use function number 109,102,104,105 to ignores hidden values. i.e. to calculates only on visible cells.

给定函数 Sub SumVisible() 对我不起作用，而是我使用了 Subtotal 函数。注意使用函数编号 109,102,104,105 来忽略隐藏值。即只计算可见单元格。

"https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939"

“ https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939”

aSum = WorksheetFunction.Subtotal(109, CalcRange) 
aCount = Application.WorksheetFunction.Subtotal(102, CalcRange)
aMax = Application.WorksheetFunction.Subtotal(104, CalcRange)
aMin = Application.WorksheetFunction.Subtotal(105, CalcRange)

回答by gavin

I just came across this site. https://www.techrepublic.com/blog/microsoft-office/how-to-sum-values-in-an-excel-filtered-list/

我刚遇到这个网站。 https://www.techrepublic.com/blog/microsoft-office/how-to-sum-values-in-an-excel-filtered-list/

The basic formula is x = Application.WorksheetFunction.Subtotal('code number', 'range') The formula you want is x = Application.WorksheetFunction.Subtotal(109, range(x, y))

基本公式是 x = Application.WorksheetFunction.Subtotal('code number', 'range') 你要的公式是 x = Application.WorksheetFunction.Subtotal(109, range(x, y))