如何根据条件将多个单元格中的值合并？

Question

我需要搜索一行单元格，对于每个包含特定值的单元格，返回上面单元格中的值。

例如，考虑以下示例：

+---+--------+--------+--------+--------+--------+----------+
|   |   A    |   B    |   C    |   D    |   E    |     F    |
+---+--------+--------+--------+--------+--------+----------+
| 1 |   UK   |   DE   |   FR   |   HK   |   TW   |          |
+---+--------+--------+--------+--------+--------+----------+
| 2 |   YES  |        |   YES  |   YES  |        |          |
+---+--------+--------+--------+--------+--------+----------+
| 3 |        |   YES  |        |   YES  |   YES  |          |
+---+--------+--------+--------+--------+--------+----------+
| 4 |   YES  |        |        |   YES  |        |          |
+---+--------+--------+--------+--------+--------+----------+

我想在F2、F3和F4单元格中插入一个公式，以得到以下结果。

F2 = UK,FR,HK
F3 = DE,HK,TW
F4 = UK,HK

这个可以做到吗？

谢谢

- Typhoon101

Answer 1

我找到了一个简单而可扩展的解决方案，使用数组公式来连接满足特定条件的多个单元格。

应用于您的示例中，请复制到单元格F2：

=TEXTJOIN(",", TRUE, IF(B3:F3 = "YES", B$2:F$2, ""))

按下Ctrl+Shift+Enter键以进入数组公式，并复制单元格F3-F4。

为什么这样做有效的原因留给读者作为练习。这很明显，但我更喜欢“魔法”。

希望这对有类似问题的人有所帮助。

- Mourits de Beer

---

请注意，TEXTJOIN仅适用于Excel 2016和Office 365。 - Alex Jorgenson

Answer 2

编写自己的UDF

原始解决方案。

文章节选

1. 通过单击“开发人员”选项卡上的“Visual Basic”或使用组合键Alt+F11打开VBA编辑器。
2. 右键单击左上角的“Microsoft Excel对象”，选择“插入->模块”来创建新模块。
3. 插入以下代码：

UDF:

Function ConcatenateIf(CriteriaRange As Range, _
                       Condition As Variant, _
                       ConcatenateRange As Range, _
                       Optional Separator As String = ",") As Variant
'Update 20150414
Dim xResult As String
On Error Resume Next
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
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

如果在工作簿中启用宏，稍后您可以使用它。

对于您的具体示例，请将以下公式写入 F2 单元格，并复制所需范围。

=ConcatenateIf($A2:$E2,"YES",$A$1:$E$1,",")

- AntiDrondert

Answer 3

在F2中复制并粘贴以下公式：

=CONCATENATE(IF($A2="YES",A$1&",",),IF($B2="YES",B$1&",",),IF($C2="YES",C$1&",",),IF($D2="YES",D$1&",",),IF($E2="YES",E$1&",",))

并向下拖动列。

说明：

IF($A2="YES",A$1&",",)
IF($B2="YES",B$1&",",)
IF($C2="YES",C$1&",",)
IF($D2="YES",D$1&",",)
IF($E2="YES",E$1&",",)

以上代码已经重写了5次，并更改了列名。它检查当前行中的单元格是否有“YES”。如果有，则将进入列头，即'A$1'。请注意，$1是对第一行即标题的绝对引用。

最后，我使用CONCATENATE语句封装了所有五个IF语句。

希望这能帮到你。

- Zeeshan S.

---

谢谢Zeeshan。这个方法很好，但我想知道是否有更容易管理的解决方案 - 也许可以使用数组公式。我在原始帖子中从未考虑过提到它，但我的电子表格有几百列。这意味着有几百个IF语句。如果我随时添加或删除列（这是相当频繁发生的事情），我将需要不断更新所有IF语句。 - Typhoon101

我会检查并回复您。 - Zeeshan S.