Google Sheets：如何在拆分并扁平化数据时保留多列？

问题场景 #

表单响应数据中，复选框的选择结果以逗号分隔存储在单个单元格（如示例中的E列），需要将这些选项拆分后，与其他关联列（如姓名、邮箱、提交时间等）一一对应，实现数据扁平化，同时完整保留所有需要的列数据。现有公式仅能处理两列关联，无法满足多列保留的需求。

现有公式局限 #

你当前的公式仅关联了B列和E列，拆分后仅输出两列数据，无法扩展到更多需要保留的列。

通用解决方案公式 #

假设需要保留的列是B列（姓名）、C列（邮箱）、D列（提交时间）、E列（复选框选项），可以使用以下公式：

=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(
  IF(IFERROR(SPLIT('Form Responses'!E2:E, ","))="",,
  'Form Responses'!B2:B&"|"&'Form Responses'!C2:C&"|"&'Form Responses'!D2:D&"|"&SPLIT('Form Responses'!E2:E, ",")
  )), "|"), "where Col4 is not null"))

公式说明 #

• SPLIT('Form Responses'!E2:E, ",")：拆分E列中逗号分隔的复选框选项，得到每行的多个选项数组
• IF(IFERROR(...)="",, ...)：过滤拆分后产生的空值，避免生成无效的空行
• 'Form Responses'!B2:B&"|"&...：将所有需要保留的列数据用唯一分隔符（这里用|，注意不要和数据内的字符重复）拼接，同时与每个拆分后的选项组合成完整的字符串
• FLATTEN：将二维数组转换为一维，实现数据扁平化，让每个选项都对应一行完整的关联数据
• SPLIT(..., "|")：把拼接的字符串重新拆分成独立的列，恢复原来的多列结构
• QUERY(..., "where Col4 is not null")：过滤掉没有复选框选项的无效行
• TRIM：自动去除每个单元格内的多余空格，保证数据整洁

扩展方法 #

如果需要增加更多保留列，只需在拼接部分继续添加&"|"&'Form Responses'!X2:X（X为目标列的列标），同时修改QUERY中的Col4为对应最后一列的索引（比如新增一列后改为Col5）。

例如，若要增加F列（手机号），公式调整为：

=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(
  IF(IFERROR(SPLIT('Form Responses'!E2:E, ","))="",,
  'Form Responses'!B2:B&"|"&'Form Responses'!C2:C&"|"&'Form Responses'!D2:D&"|"&'Form Responses'!F2:F&"|"&SPLIT('Form Responses'!E2:E, ",")
  )), "|"), "where Col5 is not null"))

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