Excel在没有VBA的情况下连接未知数量的单元格值

Question

我需要将 Excel 文件发送给第 3 方以捕获数据，并且我不能使用 VBA。

用户必须至少捕获一个字符串，默认情况下最多可以捕获 10 个字符串，例如在 D23:D32 范围内。
第 33 行是空行。
从第 34 行开始，必须捕获其他数据。我可以为 D33 或 D34 定义一个名称。

如果用户需要捕获超过 10 个字符串，他可以插入额外的行。

现在我需要连接用户捕获的字符串，用分号分隔它们。
澄清我的意思。如果用户无法插入行，则此公式将执行此操作:

=D23 & IF(D24="","",";"&D24) & IF(D25="","",";"&D25) & IF(D26="","",";"&D26)&IF(D27="","",";"&D27)&IF(D28="","",";"&D28)&IF(D29="","",";"&D29)&IF(D30="","",";"&D30)&IF(D31="","",";"&D31)&IF(D32="","",";"&D32)

有没有办法包含用户可能添加的行？

Answer 1

一个简单的 VBA 用户定义函数将提供一个更简单、更强大的解决方案。

如果您可以确保您的用户只会在 处插入行底部 在列表中，您可以使用表格和隐藏列。

D22:  Data
E22: Concatenation

E23: =D23
E24: =CONCATENATE(E23,";",D24)

将 E24 填写到表格底部。

显示结果的公式(在下面的屏幕截图中的 D19 中):

=LOOKUP(2,1/(RIGHT(Table1[concatenation],1)<>";"),Table1[concatenation])

因为这是一个表格，所以当插入行并创建其他条目时，公式将扩展。然而，该方法将需要用户的纪律，因为结构很容易搞砸。

这是它的样子，列 E 不是 隐藏，并在相邻单元格中显示公式:



另一方面，可以设计一个简单的用户定义函数，将 D23 中的所有数据连接起来返回到第一个空白行；或者如果范围内可能有空白，则可以以其他方式选择范围。

范围内的空白是指 D23:D32 范围内的条目可能为空白，有效数据低于该范围。例如。 D23、D24 和 D30 中的有效数据，D25:D29 中没有任何内容

下面是 UDF 的样子:

Function ConcatLines(rg As Range, Optional Delimiter As String = " ") As String
Dim C As Range
For Each C In rg
    ConcatLines = ConcatLines & Delimiter & C
Next C
ConcatLines = Mid(ConcatLines, 2)
End Function

显示结果的公式，从 D23 到它下面的第一个空白行，将是 数组输入 :

=ConcatLines(OFFSET($D$23,0,0,MATCH(TRUE,ISBLANK($D$23:$D$100),0)-1),";")

至 数组输入 公式，按住 ctrl-shift 击中 输入 .如果您正确执行此操作，Excel 将放置大括号 {...} 围绕公式。