Google Sheets按序拼接值时自定义表头配对与分隔符方法

需求背景 #

表格指定区域的数值需按预设顺序拼接，在保留原有排序规则的前提下，需要支持两个可配置控制项：

• 控制是否将「表头值」与「单元格值」组合为「表头-值」配对格式
• 控制是否添加分隔符，以及分隔符的具体类型

现有实现 #

当前用于生成表头-值配对拼接结果的公式如下：

=ARRAYFORMULA(TRIM(SUBSTITUTE(TRIM(FLATTEN(QUERY(REGEXREPLACE(SORT(TRANSPOSE( IF((A2:D4<>"")*(REGEXMATCH(A1:D1, "^\d+_")), "♥"&A1:D1&" "&A2:D4, )), FLATTEN(A1:D1), 1), "\d+_", ),,9^9))), "♥", CHAR(10)))

现有逻辑下，所有带数字前缀的表头对应值，均可按预设顺序正常展示。

目标效果 #

实际使用需支持两类自定义需求：

1. 拼接时可选择不包含表头值，仅输出单元格内容
2. 可在单元格值字符串后追加一个或多个自定义分隔符，支持CHAR(10)、", "等任意合法分隔符写法
   

配置规则设计 #

直接通过表头命名规则承载配置，无需额外设置参数单元格，规则如下：

• 用表头前缀区分是否展示表头：
  • 命名格式为数字+下划线（例：1_Name，对应B1单元格）：拼接时保留表头，生成「表头 值」的配对内容
  • 命名格式为数字+波浪号（例：4~Math，对应A1单元格）：拼接时排除表头，仅输出单元格值
• 用表头后缀配置分隔符：在表头名称后通过竖线|追加零个或多个分隔符配置，多个分隔符用&连接，用于指定当前字段拼接完成后追加的分隔符。

现有公式默认全局添加CHAR(10)换行分隔符，通过竖线追加单字段分隔符的配置可直接嵌入公式对应逻辑，配置方式参考下图：

配置示例：

• B1单元格竖线后配置CHAR(10) & CHAR(10)：该字段拼接完成后追加两个换行
• A1单元格竖线后配置", "：该字段拼接完成后仅追加逗号+空格，不换行

内容的提问来源于stack exchange，提问作者MMsmithH