如何只对excel中的行进行求和?

I use the SUM(B1..B20) formula to sum a column, but when I filter the data by an adjacent column, the sum doesn't update to reflect only those rows not filtered out. How does one do this?

我使用SUM(B1..B20)公式对一列进行求和，但当我通过邻列过滤数据时，求和不会更新，只反映那些没有被过滤掉的行。怎么做呢?

3 个解决方案

#1

You need to use the SUBTOTAL function. The SUBTOTAL function ignores rows that have been excluded by a filter.

您需要使用SUBTOTAL函数。SUBTOTAL函数忽略了被筛选器排除的行。

The formula would look like this:

公式是这样的:

=SUBTOTAL(9,B1:B20)

The function number 9, tells it to use the SUM function on the data range B1:B20.

函数9，告诉它在数据范围B1:B20上使用SUM函数。

If you are 'filtering' by hiding rows, the function number should be updated to 109.

如果你通过隐藏行来“过滤”，函数号应该更新为109。

=SUBTOTAL(109,B1:B20)

The function number 109 is for the SUM function as well, but hidden rows are ignored.

函数号109也适用于SUM函数，但是隐藏行被忽略。

#2

If you aren't using an auto-filter (i.e. you have manually hidden rows), you will need to use the AGGREGATE function instead of SUBTOTAL.

如果您不使用自动过滤器(例如，您有手动隐藏的行)，您将需要使用聚合函数而不是SUBTOTAL。

#3

When you use autofilter to filter results, Excel doesn't even bother to hide them: it just sets the height of the row to zero (up to 2003 at least, not sure on 2007).

当您使用autofilter过滤结果时，Excel甚至不需要隐藏它们:它只是将行高度设置为0(至少到2003年为止，2007年还不确定)。

So the following custom function should give you a starter to do what you want (tested with integers, haven't played with anything else):

因此，下面的自定义函数应该为您提供一个启动器来执行您想要的操作(使用整数进行测试，没有使用其他任何东西):

Function SumVis(r As Range)
    Dim cell As Excel.Range
    Dim total As Variant

    For Each cell In r.Cells
        If cell.Height <> 0 Then
            total = total + cell.Value
        End If
    Next

    SumVis = total
End Function

Edit:

编辑:

You'll need to create a module in the workbook to put the function in, then you can just call it on your sheet like any other function (=SumVis(A1:A14)). If you need help setting up the module, let me know.

您需要在工作簿中创建一个模块来放置函数，然后您可以像调用其他函数一样在表中调用它(=SumVis(A1:A14)。如果您需要帮助设置模块，请告诉我。

#1