使用Google Sheets公式从预制表格生成三级嵌套列表

数据结构说明 #

假设你的数据按以下区域存储：

• 主项：A列（序号）、B列（主项名称）
• 子项：C列（子项名称）、D列（关联主项名称）
• 子子项：E列（子子项名称）、F列（关联子项名称）

实现公式 #

在目标列（比如G1单元格）输入以下公式：

=LET(
  main, A2:B,
  sub, C2:D,
  subsub, E2:F,
  // 过滤各层级空行数据
  main_clean, FILTER(main, INDEX(main,,2)<>""),
  sub_clean, FILTER(sub, INDEX(sub,,2)<>""),
  subsub_clean, FILTER(subsub, INDEX(subsub,,2)<>""),
  // 建立层级关联与标识
  main_data, HSTACK(SEQUENCE(ROWS(main_clean)), INDEX(main_clean,,2), "main"),
  sub_data, HSTACK(XMATCH(INDEX(sub_clean,,2), INDEX(main_clean,,2)), INDEX(sub_clean,,1), "sub"),
  subsub_data, HSTACK(XMATCH(INDEX(subsub_clean,,2), INDEX(sub_clean,,1)), INDEX(subsub_clean,,1), "subsub"),
  // 合并并排序数据，保证嵌套顺序
  combined, VSTACK(main_data, sub_data, subsub_data),
  sorted, SORT(combined, 1, TRUE, 3, TRUE),
  // 添加层级缩进，生成最终列表
  result, BYROW(sorted, LAMBDA(row, 
    SWITCH(INDEX(row,3),
      "subsub", "  "&INDEX(row,2),
      "sub", " "&INDEX(row,2),
      INDEX(row,2)
    )
  )),
  // 过滤空值输出
  FILTER(result, result<>"")
)

公式逻辑说明 #

1. 数据清洗：用FILTER剔除各层级的空行，避免无效数据干扰
2. 层级关联：通过XMATCH建立子项与主项、子子项与子项的归属关联，确保嵌套关系正确
3. 排序合并：合并所有层级数据后，按主项索引+层级类型排序，保证主项→子项→子子项的嵌套顺序
4. 层级标识：用SWITCH为不同层级添加缩进空格，直观区分嵌套层级
5. 结果输出：过滤空值后得到符合需求的三级嵌套列表

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