Excel公式返回多个列名称 [英] Excel formula to return multiple column names

问题描述

我正在尝试获取一个在每一行中搜索是"的公式.然后，它应该列出列名称.某些行可能只有1个是，某些行可能有3个或更多.我尝试搜索和编辑一些建议，但无法获得所需的建议.有些公式会给我一个随机的列名，只有1个.另一些只是出错了.

I am trying to get a formula that searches each row for a "Yes". It then should list the column name(s). Some row may only have 1 yes, some may have 3 or more. I have tried searching and editing several suggestions, but cannot get the one I need. Some formulas will give me a random column name, and only 1. Other just error out.

Header 1    Header 2    Header 3    Header 4    List
  No          Yes        Yes           No       Header 2, Header 3
 Yes           No        Yes          Yes       Header 1, Header 3, Header 4
  No           No         No          Yes       Header 4
 Yes          Yes         No          Yes       Header 1, Header 2, Header 4

推荐答案

您可以使用以下UDF:

You can use the following UDF:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

将其放入工作表所附的模块中.

Put it in a module attached to the worksheet.

然后，您将像使用以下数组公式的任何其他公式一样调用它:

Then you would call it like any other formula with the following array formula:

=TEXTJOIN(",",TRUE,IF(A2:D2="Yes",$A$1:$D$1,""))

作为数组，退出编辑模式时需要使用Ctrl-Shift-Enter而不是Enter进行确认.如果操作正确，则Excel会将 {} 放在公式周围.

Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

要获得IF公式，这将返回相同的结果，因为您只有四个.如果您有四个以上，这将变得很长.

To get it with IF formulas this will return the same thing, since you only have four. If you have more than four this would get quite long.

=LEFT(IF(A2="Yes",$A$1 & ",","") & IF(B2="Yes",$B$1 & ",","") & IF(C2="Yes",$C$1 & ",","") & IF(D2="Yes",$D$1 & ",",""),LEN(IF(A2="Yes",$A$1 & ",","") & IF(B2="Yes",$B$1 & ",","") & IF(C2="Yes",$C$1 & ",","") & IF(D2="Yes",$D$1 & ",",""))-1)

这篇关于Excel公式返回多个列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！