如何将双列表格转换为带分类的列表式表格？

一、动态公式修正方案 #

你遇到的复制公式参数互换问题，核心是相对引用导致的列标题关联错误，可通过以下方式修正：

1. 生成表头：保持TRANSPOSE(SORT(UNIQUE(Table[Category])))（假设你的表格名为Table，结构化引用更稳定），输入到目标区域首行即可自动溢出所有分类作为表头。
2. 生成数据列：避免手动复制公式，改用BYCOL函数批量生成每列数据，公式如下：

   =BYCOL(TRANSPOSE(SORT(UNIQUE(Table[Category]))), LAMBDA(col, SORT(FILTER(Table[Data], Table[Category]=col))))
   

   该公式会自动遍历每个分类表头，筛选并排序对应的数据，无需手动复制，且不会出现引用错乱问题。
   • 若使用旧版Excel不支持BYCOL，可对单个数据列使用绝对引用固定分类列和数据列：

     =SORT(FILTER($Table[Data], $Table[Category]=A$1))
     

     这里$Table[Data]和$Table[Category]是绝对结构化引用，A$1是表头的混合引用，复制公式时即可保持关联正确。

二、Lambda+Let实现动态自适应方案 #

使用LET定义变量简化逻辑，结合Lambda实现全动态生成，支持新增自动更新：

=LET(
    Categories, SORT(UNIQUE(Table[Category])),
    Headers, TRANSPOSE(Categories),
    DataCols, BYCOL(Categories, LAMBDA(cat, SORT(FILTER(Table[Data], Table[Category]=cat)))),
    MaxRows, MAX(BYCOL(DataCols, LAMBDA(col, ROWS(col)))),
    FilledCols, BYCOL(DataCols, LAMBDA(col, IFERROR(INDEX(col, SEQUENCE(MaxRows)), ""))),
    HSTACK(Headers, FilledCols)
)

• 逻辑说明：
  1. Categories：获取去重排序后的分类列表
  2. Headers：转置分类作为表头
  3. DataCols：生成每个分类对应的排序后数据列
  4. MaxRows：计算最长数据列的行数，保证各列长度一致
  5. FilledCols：用空值填充短列，使所有列格式统一
  6. HSTACK：组合表头和数据列，生成最终表格

三、PowerQuery实现反向透视方案 #

反向操作（双列转多分类列）可通过PowerQuery的透视列功能实现，无需聚合：

1. 选中双列表格，点击「数据」选项卡→「从表格/区域」导入PowerQuery编辑器
2. 选中「Category Column」列，点击「转换」选项卡→「透视列」
3. 在弹出窗口中：
   • 值列选择「Data Column」
   • 高级选项→聚合函数选择「不要聚合」（部分版本显示为「所有行」）
4. 点击确定后，每个分类列会显示对应的数据列表，点击列标题右侧的展开按钮，选择「展开到新行」
5. 选中所有数据列，点击「排序」→「升序/降序」，最后点击「关闭并上载」即可生成动态表格
   • 新增分类后，右键表格→「刷新」即可自动更新列和数据

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