excel - CountIfs 函数的地址中的返回地址/信息？

Question

我有一个包含大量 COUNTIFS 函数的 Excel 文件。公式可以正常工作，但我需要能够找到一种方法，不仅可以返回每个 COUNTIFS 的地址，还可以返回单元格中的内容。

我创建了一个简化的工作表，其中包含数据集的外观。我的 countifs 函数如下所示：

=COUNTIFS(A$3:A$7,"="&G3,B$3:B$7,">="&H3,C$3:C$7,"<="&I3)

我在这里查看了类似的问题并尝试了两个用户定义的函数：

Public Function ListAddresses(SearchTerm As Variant, SearchRange As Range) As String
    Dim WS As Worksheet, rCell As Range

    Set WS = Sheets(SearchRange.Parent.Name)
    SearchTerm = UCase(SearchTerm)

    Set SearchRange = Intersect(WS.UsedRange, SearchRange)

    For Each rCell In SearchRange.Cells
        If UCase(rCell.Value) = SearchTerm Then
            ListAddresses = ListAddresses & rCell.Address(False, False) & " | "
        End If

    Next rCell

    If ListAddresses <> "" Then
        ListAddresses = Left(ListAddresses, Len(ListAddresses) - 3)
    Else
        ListAddresses = "<none>"
    End If

和

Public Function CountIfFinder(rng As Range, crit As String) As String
    Dim r As Range, DQ As String

    DQ = Chr(34)
    crit = DQ & crit & DQ
    CountIfFinder = ""
    Set rng = Intersect(rng, rng.Parent.UsedRange)

    For Each r In rng
        s = "=countif(" & r.Address & "," & crit & ")"
        If Evaluate(s) = 1 Then CountIfFinder = CountIfFinder & "," & r.Address(0, 0)
    Next r

    CountIfFinder = Mid(CountIfFinder, 2)

任何帮助都将不胜感激，因为到目前为止，我必须手动搜索 DapSeq 峰以找到匹配的峰，其中几乎有 118,000 个（哎呀）。

问号是来自我需要函数返回的地址的信息。

标签： excelexcel-formulauser-defined-functions