excel - 如何根据条件附加到文本

Question

如何根据前一列的值在结果列中创建公式


如果任何先前列(从 c1 到 c5)中的值大于零，我想将其添加到结果中。

Answer 1

如果您碰巧可以访问 TEXTJOIN您可以使用以下功能:


F2 中的公式:

=TEXTJOIN(",",TRUE,IF(A2:E2>0,$A$1:$E$1,""))

注1:是数组公式，需要通过CtrlShiftEnter确认

注2:无法访问 TEXTJOIN这将成为一个更加痛苦的过程，但您可以尝试仅 5 列 =IF(A2>0,A$1&",","")&IF(B2>0,B$1&",","")&IF(C2>0,C$1&",","")&IF(D2>0,D$1&",","")&IF(E2>0,E$1&",","") ，但是您必须扩展以摆脱尾随逗号

如果上述两个选项都不可行，最好的办法是编写一个 UDF。对于本练习，您可以编写一个相当简单的练习:

Public Function TextJoin2(rng1 As Range, rng2 As Range) As String

With Application
    Dim arr As Variant: arr = .Evaluate("IF(" & rng1.Address & ">0," & rng2.Address & ","" "")")
    TextJoin2 = Replace(.Trim(Join(arr, " ")), " ", ",")
End With

End Function

来电 F2喜欢: =TEXTJOIN2(A2:E2,A$1:E$1)并向下拖动。