如果下列行中的值不为空，则连接列标题 [英] Concatenate column headers if value in rows below is non-blank

问题描述

我想要完成表格中填充空白列，如下方突出显示的那样。

所以，我需要一个公式或VBA，它将返回并连接每一行中非空白单元格的标题。

解决方案

使用¹作为数组公式。

  = TEXTJOIN（ - >，TRUE，IF （L EN（C3：I3），C $ 2：I $ 2，））
  

Pre-Excel 2016版本

虽然您可以将一系列IF语句串在一起，但更清晰的备选方案可能是编写用户定义的函数（也称为UDF）。

在标准VBA模块代码表：

 函数udf_Stitch_Together（r As Range，_ 
h As Range，_ 
可选d As String =  - >，_ 
可选的blnks As Boolean = False）As String 
 Dim s As String，c As Long 
对于c = 1 To r.Cells.Count 
如果CBool​​（Len（r.Cells（c）.Text））Then _ 
s = s& IIf（Len（s），d，vbNullString）& h.Cells（c）.Text 
下一个c 
 udf_Stitch_Together = s 
结束函数
  

---

¹_{用excel引入了一个href =https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c =nofollow noreferrer> TEXTJOIN 2016年以下版本：适用于Android手机，Excel Mobile，Excel 2016与Office 365，Excel 2016 for Mac，Excel Online，Excel for iPad，Excel for iPhone和Excel for Android平板电脑的Excel。}

I have table with data in the format below.

Data

I want the finished table to have the blank column to be populated like the highlighted one below.

Finished Table

So, I need a formula or VBA that will return and concatenate the headers of non-blank cells in each row.

解决方案

Use¹ the following as an array formula.

=TEXTJOIN("-->", TRUE, IF(LEN(C3:I3), C$2:I$2, ""))

Pre-Excel 2016 versions

While you could just string together a series of IF statements, a cleaner alternate might be to write a user defined function (aka UDF).

In a standard VBA module code sheet:

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.}

这篇关于如果下列行中的值不为空，则连接列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！