Google Sheets合并重复表单问题列 按首项分组整理数据

实现思路 #

• 用FLATTEN函数将循环出现的「物品需求」「数量」列分别展平，同时通过序列函数匹配对应列位置
• 重复生成前4列的用户标准数据，与展平后的物品、数量数据一一对应
• 用QUERY过滤未填写的空行，得到干净的结果

具体操作（假设原始数据在Sheet1，预期结果在Sheet2） #

方法1：分步公式 #

1. 在Sheet2中手动输入前4列（A-D）和目标列（E:「what article do you need?」、F:「How many?」）的表头
2. 在Sheet2!A2输入公式，下拉并横向复制到B2、C2、D2：

   =INDEX(Sheet1!A:A,ROUNDUP(ROW(A1)/20,0))
   

3. 在Sheet2!E2输入公式，下拉：

   =FLATTEN(INDEX(Sheet1!E:ZZ,,SEQUENCE(1,20,1,3)))
   

4. 在Sheet2!F2输入公式，下拉：

   =FLATTEN(INDEX(Sheet1!E:ZZ,,SEQUENCE(1,20,2,3)))
   

方法2：一键生成的数组公式 #

在Sheet2!A1直接输入以下公式，自动生成所有整理后的数据：

=ARRAYFORMULA(
  QUERY(
    {
      INDEX(Sheet1!A:D,ROUNDUP(SEQUENCE(COUNTA(Sheet1!A:A)*20)/20,0),),
      FLATTEN(INDEX(Sheet1!E:ZZ,,SEQUENCE(1,20,1,3))),
      FLATTEN(INDEX(Sheet1!E:ZZ,,SEQUENCE(1,20,2,3)))
    },
    "select * where Col5 is not null",
    1
  )
)

参数说明 #

• 公式中的20对应你提到的板块数量，若后续板块数变化，直接替换该数字即可
• SEQUENCE(1,20,1,3)用于定位所有「what article do you need?」列（从第1列开始，每3列取1列）
• ROUNDUP(SEQUENCE(...)/20,0)实现前4列数据的重复匹配，确保每个用户的标准数据对应20个板块的填写内容

内容的提问来源于stack exchange，提问作者Dice 0ne