如何在Google Sheets中生成含主分类、子分类及空白行的表格

需求回顾 #

需要将包含主分类、子分类的原始数据，转换为以下格式：

• 每个主分类单独占一行（对应列A，列B为空）
• 主分类下方紧跟该分类下所有子分类，每个子分类占一行（列A为空，列B为子分类内容）

原始示例数据 #

目标输出 #

函数解决方案 #

假设原始数据范围为 A2:B（表头在A1、B1），可以使用以下数组公式直接生成目标布局：

=LET(
  unique_cats, UNIQUE(A2:A),
  cat_details, MAP(unique_cats, LAMBDA(cat, {cat, ""; "", FILTER(B2:B, A2:A=cat)})),
  final_output, REDUCE("", cat_details, LAMBDA(acc, curr, {acc; curr})),
  final_output
)

公式拆解 #

1. UNIQUE(A2:A)：提取所有不重复的主分类，得到主分类列表。
2. MAP(unique_cats, LAMBDA(cat, {cat, ""; "", FILTER(B2:B, A2:A=cat)}))：遍历每个主分类，生成该分类对应的行组：
   • 第一行是「主分类+空值」，对应目标格式的主分类行
   • 后续行是「空值+该分类下的子分类」，对应子分类行
3. REDUCE("", cat_details, LAMBDA(acc, curr, {acc; curr}))：将每个主分类的行组拼接成完整的二维数组，得到最终布局。

兼容旧版函数的替代方案 #

如果你的Google Sheets版本不支持LET/MAP/REDUCE，可以使用以下组合公式：

=ARRAYFORMULA(
  SPLIT(
    FLATTEN(
      QUERY(
        {A2:A&"|", B2:B, COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))},
        "select Col1, '' where Col3=1 union all select '', Col2 order by Col1",
        0
      )
    ),
    "|"
  )
)

替代方案说明 #

1. COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))：标记每个主分类的第一行（值为1）。
2. QUERY(...)：
   • 先提取所有主分类的第一行，格式化为「主分类|, 空值」
   • 再提取所有子分类行，格式化为「空值, 子分类」
   • 合并后按主分类排序
3. FLATTEN+SPLIT：处理拼接的分隔符，还原为二维表格。

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