使用以下内容作为数组公式.
=TEXTJOIN("-->", TRUE, IF(LEN(C3:I3), C$2:I$2, ""))
Excel 2016 之前的版本
虽然您可以将一系列 IF 语句串在一起,但更简洁的替代方案可能是编写用户定义的函数(也称为 UDF)。
在标准 VBA 模块代码表中:
Function udf_Stitch_Together(r As Range, _
h As Range, _
Optional d As String = "-->", _
Optional blnks As Boolean = False) As String
Dim s As String, c As Long
For c = 1 To r.Cells.Count
If CBool(Len(r.Cells(c).Text)) Then _
s = s & IIf(Len(s), d, vbNullString) & h.Cells(c).Text
Next c
udf_Stitch_Together = s
End Function
¹ The TEXTJOIN was introduced with Excel 2016 in the following versions:Excel for Android phones, Excel Mobile, Excel 2016 with Office 365, Excel 2016 for Mac, Excel Online, Excel for iPad, Excel for iPhone and Excel for Android tablet.