编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

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

fwd.london 集合 2022-5-11 10:36 11人围观

腾讯云服务器
查找COUNTIFS函数的地址(单元格/行)的处理方法

我有一个包含大量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函数的地址(单元格/行)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^