excel - 如何忽略Excel公式中过滤掉的数据

Question

我正在使用索引/匹配从相关表中获取数据以填充到第一个表中。在我的相关表中，我已经过滤掉了值，但过滤掉的值仍在我的第一个表中填充。如果索引/匹配不够智能，无法仅获取过滤后的值，我该如何解决此问题(首选公式，但 VBA 可接受)以仅获取过滤后的值。

这是我当前的公式:

=INDEX(Table_owssvr__1[MyValues],MATCH([@[ID]],Table_owssvr__1[ID],0))

Answer 1

您可能会找到SUBTOTAL函数很有用，因为它仅适用于可见行。 ( Here 关于 SUBTOTAL 的一些更一般性的讨论)

但如果这不足以满足您的需求，here's如何检查某个单元格是否被过滤掉。

使用它，我编写了一些 VBA 代码来对仅对可见单元格求和的列进行求和。对于做你需要做的事情来说，这应该是一个非常有用的开始。

如果对单元格求和不是您想要做的，只需更改注释中指示的部分即可。 (显然，您必须将函数名称从 sumFilteredColumn 更改为其他名称!)

Public Function sumFilteredColumn(startCell As Range)

    Dim lastRow As Long ' the last row of the worksheet which startCell is on
    Dim currentCell As Range
    Dim runningTotal As Long ' keeps track of the sum so far

    lastRow = lastRowOnSheet(startCell)
    Set currentCell = startCell

    ' Loop until the last row of the worksheet
    Do While currentCell.Row <= lastRow
        ' Check currentCell is not hidden
        If Not cellIsOnHiddenRow(currentCell) Then
            ' -------------------------------------------------
            ' Here's where the magic happens. Change this to
            ' change sum to, e.g. concatenate or multiply etc.
            If IsNumeric(currentCell.Value) Then
                runningTotal = runningTotal + currentCell.Value
            End If
            ' -------------------------------------------------
        End If
        Set currentCell = currentCell.Offset(1) ' Move current cell down
    Loop

    sumFilteredColumn = runningTotal

End Function

' return the number of the last row in the UsedRange
' of the sheet referenceRange appears in
Public Function lastRowOnSheet(referenceRange As Range) As Long

    Dim referenceSheet As Worksheet
    Dim referenceUsedRange As Range
    Dim usedRangeCellCount As Long
    Dim lastCell As Range

    Set referenceSheet = referenceRange.Parent
    Set referenceUsedRange = referenceSheet.usedRange
    usedRangeCellCount = referenceUsedRange.Cells.CountLarge

    Set lastCell = referenceUsedRange(usedRangeCellCount)
    lastRowOnSheet = lastCell.Row

End Function

' Is the row which referenceCell is on hidden by a filter?
Public Function cellIsOnHiddenRow(referenceCell As Range) As Boolean

    Dim referenceSheet As Worksheet
    Dim rowNumber As Long

    Set referenceSheet = referenceCell.Parent
    rowNumber = referenceCell.Row

    cellIsOnHiddenRow = referenceSheet.Rows(rowNumber).EntireRow.Hidden

End Function