用于按行“连接 If”的 VBA 用户定义函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22639868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA User Defined Function for "Concatenate If" by rows
提问by vbastrangledpython
I have some data of whether or not a particular "service" (e.g. Bird Survey) has been performed for a particular site, with a "yes" or "no" for each service.
我有一些关于是否为特定站点执行特定“服务”（例如鸟类调查）的数据，每个服务都带有“是”或“否”。
E.G.
例如
Column A contains site names, say A, B, C, D and E with the title "Site Name" in A1 then "A" in A2 etc.
Column B contains "Bird Survey" in B1, then either a "yes" or "no" for B2-B6.
Ditto for other services in columns C, D and E, lets say "Bat Survey", "LVI" and "Land Registry" respectively.
In F I want to concatenate the service names for each row containing a "yes". E.G. lets say the values for B2,C2,D2 and E2 are "yes", "yes", "no" and "yes", I want F2 to contain Bird Survey, Bat Survey.
A 列包含站点名称，例如 A、B、C、D 和 E，标题为“站点名称”在 A1 中，然后在 A2 中为“A”等。
B 列包含 B1 中的“鸟类调查”，然后是 B2-B6 的“是”或“否”。
C、D 和 E 列中的其他服务同上，让我们分别说“蝙蝠调查”、“LVI”和“土地登记处”。
在 FI 中，要连接包含“是”的每一行的服务名称。EG 可以说 B2、C2、D2 和 E2 的值是“是”、“是”、“否”和“是”，我希望 F2 包含鸟类调查、蝙蝠调查。
As I understand it there are no native functions in excel that can do this, and so I've been trying to create a user defined function in VBA. I've tried two approaches
据我了解，excel 中没有本机函数可以做到这一点，所以我一直在尝试在 VBA 中创建一个用户定义的函数。我尝试了两种方法
one based on feeding two ranges (column names and row of "yes/no"'s) into the UDF and then combining these into an array to apply some sort of lookup criteria
and another returning column letter from one the yes/no range only then selecting from the column names based on column letter.
一种基于将两个范围（列名和“是/否”行）输入 UDF，然后将它们组合成一个数组以应用某种查找标准
另一个从是/否范围返回列字母，然后才根据列字母从列名中选择。
I've not been able to get either to work though. Note that in the end I need to create a UDF that works for a varying number of services, they won't be pre-defined as in this example.
不过，我无法让两者都工作。请注意，最后我需要创建一个适用于不同数量服务的 UDF，它们不会像本例中那样预先定义。
Any suggestions?
有什么建议？
Many thanks in advance.
提前谢谢了。
回答by John Bustos
Based upon what you're looking for, I found this function a long time ago and it's worked a charm:
根据您正在寻找的内容，我很久以前就发现了这个功能，它很有魅力：
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function
Given your question, it would be used as follows:
鉴于您的问题，它将按如下方式使用：
=ConcatenateIf(B2:E2,"yes",$B:$E,", ")
Initial credit goes to this link.
最初的功劳转到此链接。
Hope this does the trick!!
希望这能解决问题！！
