如何用单个公式合并非空单元格与对应表头（含指定分隔符）

以下是满足需求的单单元格公式，输入到结果列的顶部单元格（比如F2）即可自动填充整列：

=ARRAYFORMULA(IF(A2:A="",,TRIM(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE(IF(A2:E="",,A1:E1&I1&""""&A2:E&""""&I2)),,9^9)), I2&"$", ""))))

公式逻辑拆解 #

• ARRAYFORMULA：实现整列批量计算，无需手动下拉公式
• IF(A2:A="",, ...)：跳过首列（A列）为空的行，避免生成无效内容
• IF(A2:E="",,A1:E1&I1&""""&A2:E&""""&I2)：对每个非空数据单元格，拼接表头、I1分隔符、带双引号的单元格内容，再追加I2分隔符；空单元格留空
• TRANSPOSE：转置数据结构，让QUERY函数可以按行合并内容
• QUERY(...,9^9)：利用超大行数参数，将转置后的每行内容合并为单个字符串（此时字符串末尾会多一个I2分隔符）
• FLATTEN：将QUERY返回的多行结果展平为一列
• REGEXREPLACE(..., I2&"$", "")：移除每行末尾多余的I2分隔符
• TRIM：清除字符串首尾可能存在的空格

特殊情况处理 #

如果I2的分隔符包含正则特殊字符（如.、*、+、?等），需要在公式中对这些字符加反斜杠转义，例如分隔符是|，则需写成\|；也可以替换为SUBSTITUTE函数去除末尾分隔符，示例：

=ARRAYFORMULA(IF(A2:A="",,LEFT(FLATTEN(QUERY(TRANSPOSE(IF(A2:E="",,A1:E1&I1&""""&A2:E&""""&I2)),,9^9)), LEN(FLATTEN(QUERY(TRANSPOSE(IF(A2:E="",,A1:E1&I1&""""&A2:E&""""&I2)),,9^9)))-LEN(I2))))

内容的提问来源于stack exchange，提问作者Víctor Enrich