Excel公式: List headers where cell has value

Question

如果单元格具有一行中所有单元格的数据，在标题中显示值的公式是什么？该表有许多列。

示例数据:

+----+----+-------+
|One |Two |Output |
+----+----+-------+
|data|    |One    |
+----+----+-------+
|    |data|Two    |
+----+----+-------+
|data|data|One,Two|
+----+----+-------+
|    |    |       |
+----+----+-------+

Answer 1

由于 Excel for Mac 2011 的限制，我找不到有效的公式，所以我使用了用户定义函数。

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

然后在使用的单元格中

=ConcatenateIf($A1:$B1, "data", $A$1:$B$1, ",")

归功于 this link