Google Sheets中基于数组公式拆分单元格生成多行数据的问题

原始数据集 #

目标输出格式 #

原公式的空值错位问题 #

感谢用户player0提供的参考公式：

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT(B1:B, ","))="",,
 SPLIT(B1:B, ", ", )&"​"&C1:C&"​"&D1:D&"​"&E1:E/LEN(SUBSTITUTE(FLATTEN(
 QUERY(TRANSPOSE(IFERROR(1/(1/(SPLIT(B1:B, ",")<>"")))),,9^9)), " ", ))&"​"&A1:A)), "​"), 
 "where Col3 is not null format Col2'mm/dd/yy', Col4'$0'", ))

该公式核心逻辑是拆分客户列后将每行数据展平，但当日期等字段为空时，拼接的字符串会缺少对应分隔符的占位，导致SPLIT后字段错位，错误结果如下：

自定义公式的问题分析 #

你修改后的公式存在以下问题，导致"Company"被错误拼接至公司列末尾：

1. 语法错误：IF(C2:C<>",C2:C," ")、!D2:D这类写法不符合表格函数语法，空值判断逻辑完全失效，空值错位问题未解决。
2. 分隔符缺失：添加"Company"时未使用统一的分隔符（即公式中的特殊空格​），直接将其拼接在公司列之后，导致SPLIT时无法识别为独立字段，最终合并为同一列内容。
3. 逻辑混乱：公式中多余的!符号、错误的引用（如!A1）导致数据引用错误，进一步破坏了拼接结构。

解决方案 #

以下公式可同时解决空值错位问题，并正确添加固定值为"Company"的费用类型列：

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))="",,
 SPLIT(B2:B, ", ", )&"​"&C2:C&"​"&D2:D&"​"&E2:E/LEN(SUBSTITUTE(FLATTEN(
 QUERY(TRANSPOSE(IFERROR(1/(1/(SPLIT(B2:B, ",")<>"")))),,9^9)), " ", ))&"​"&A2:A&"​Company")), "​"), 
 "where Col3 is not null format Col2'mm/dd/yy', Col4'$0' label Col1'客户', Col2'日期', Col3'类型', Col4'金额', Col5'公司', Col6'费用类型'", 1))

关键修正点： #

1. 保留空值占位：无论日期等字段是否为空，都保留分隔符​，确保SPLIT后每个字段位置固定，避免错位。
2. 独立添加固定列：在拼接字符串的末尾通过&"​Company"添加固定值，用分隔符与前一列隔离，确保SPLIT后成为独立的第6列。
3. 完善QUERY标签：通过label参数明确指定各列标题，与目标格式完全匹配。

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