将宽表转换为长表：Google Sheets公式无法适配多行数据问题

针对包含SERIAL、NAME字段及多组CITYn/TOWNn列的宽表，以下是适配多行的长表转换公式，解决单个行有效、多行失效的问题：

核心公式（自动识别CITY/TOWN列） #

=ARRAYFORMULA(
  LET(
    data, Sheet1!A2:F, // 替换为你的原始数据范围（含SERIAL、NAME及所有CITYn/TOWNn列）
    serial, INDEX(data,,1),
    name, INDEX(data,,2),
    // 自动识别所有CITY列（列名以CITY开头）
    city_cols, FILTER(SEQUENCE(COLUMNS(data)), LEFT(CELL("address",INDEX(data,,SEQUENCE(COLUMNS(data)))),4)="CITY"),
    // 对应识别TOWN列
    town_cols, FILTER(SEQUENCE(COLUMNS(data)), LEFT(CELL("address",INDEX(data,,SEQUENCE(COLUMNS(data)))),4)="TOWN"),
    group_count, COUNTA(city_cols),
    // 重复SERIAL/NAME，匹配每组CITY/TOWN的行数
    repeated_serial, FLATTEN(INDEX(serial, SEQUENCE(ROWS(serial), group_count))),
    repeated_name, FLATTEN(INDEX(name, SEQUENCE(ROWS(serial), group_count))),
    // 提取所有CITY/TOWN数据并转纵向
    all_cities, FLATTEN(INDEX(data,,city_cols)),
    all_towns, FLATTEN(INDEX(data,,town_cols)),
    // 过滤空数据，输出结果
    QUERY(
      {repeated_serial, repeated_name, all_cities, all_towns},
      "select * where Col3 is not null",
      0
    )
  )
)

公式说明 #

• LET：定义变量简化公式结构，避免重复引用原始数据
• city_cols/town_cols：通过列名前缀自动识别所有CITY/TOWN列，无需手动指定固定列位置
• repeated_serial/repeated_name：利用SEQUENCE生成重复的SERIAL/NAME数组，确保每组CITY/TOWN都关联原始行的标识信息
• FLATTEN：将多列横向排布的CITY/TOWN数据转为纵向单行形式
• QUERY：过滤掉CITY为空的无效行，只保留有实际数据的记录

简化版（固定列位置场景） #

如果CITY列从第3列开始、TOWN列紧随其后（如C=CITY1, D=TOWN1, E=CITY2, F=TOWN2...），可使用更简洁的公式：

=ARRAYFORMULA(
  QUERY(
    {
      FLATTEN(INDEX(Sheet1!A2:A, SEQUENCE(ROWS(Sheet1!A2:A), COUNTA(Sheet1!C1:1)/2))),
      FLATTEN(INDEX(Sheet1!B2:B, SEQUENCE(ROWS(Sheet1!B2:B), COUNTA(Sheet1!C1:1)/2))),
      FLATTEN(Sheet1!C2:E), // 替换为所有CITY列范围
      FLATTEN(Sheet1!D2:F)  // 替换为所有TOWN列范围
    },
    "select * where Col3 is not null",
    0
  )
)

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