查找COUNTIFS函数的地址(单元格/行) [英] Find address (cell/row) for COUNTIFS function

问题描述

我有一个包含大量COUNTIFS函数(大于300)的Excel文件.公式工作正常，但由于数据源非常大，我需要能够找到每个COUNTIFS结果的地址.

I have an Excel file populated with a large amount of COUNTIFS functions (above 300). The formulas work fine but I need to be able to find the address for each COUNTIFS result as the data source is very large.

即如果COUNTIF对所选参数给出的结果为1，则我需要能够知道该函数正在从数据源进行计数的哪个单元格/行.

i.e. if COUNTIFs gives me result of 1 for the selected parameters, I need to be able to know which cell/row the function is counting from the data source.

我当时想可以通过ADDRESS函数来完成此操作，但是我不确定如何将其与COUNTIFS一起使用.

I was thinking this could be done with the ADDRESS function, but I am not sure how this can be used together with COUNTIFS.

推荐答案

我将使用用户定义的函数.

I would go with a user-defined function.

使用以下代码，您将获得以下结果:

Using the below code, you would get this result:

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

End Function

这篇关于查找COUNTIFS函数的地址(单元格/行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！