Excel/Power Query如何按列唯一值生成新列实现翻译表格式转换

你需要完成的是长表转宽表的操作，之前用Power Query没得到预期结果，大概率是透视列时选了默认的聚合函数，正确操作步骤如下：

Power Query 操作步骤 #

• 将原始数据导入Power Query编辑器：选中原始数据区域，点击「数据」选项卡下的「从表格/区域」导入
• 在Power Query编辑器中，选中Language列
• 点击顶部菜单栏「转换」选项卡下的「透视列」按钮
• 在弹出的配置窗口中：
  • 「值列」选择Translation
  • 展开「高级选项」，聚合值函数选择不要聚合（这是核心步骤，默认选求和/计数都会得到错误结果）
• 点击确认后即可得到目标格式，最后点击「关闭并上载」将结果导出到Excel工作表即可

你也可以直接在Power Query的高级编辑器中粘贴以下M代码，修改源对应的原始数据表名即可直接生效：

let
    源 = Excel.CurrentWorkbook(){[Name="你的原始数据表名称"]}[Content],
    透视列 = Table.Pivot(源, List.Distinct(源[Language]), "Language", "Translation")
in
    透视列

替代实现方案 #

如果你不想用Power Query，也可以用以下两种常规方法实现：

方法1：数据透视表 #

• 选中原始数据区域，点击「插入」选项卡下的「数据透视表」
• 行区域拖拽添加Field字段，列区域拖拽添加Language字段，值区域拖拽添加Translation字段
• 打开值字段设置，将计算类型改为「最大值」即可得到对应翻译内容

方法2：函数公式 #

先提取不重复的Field列表作为行标题、不重复的Language列表作为列标题，在对应单元格输入以下公式：

=XLOOKUP($A2&"|"&B$1, 原始表!$A:$A&"|"&原始表!$B:$B, 原始表!$C:$C,"无翻译")

旧版Excel输入后按Ctrl+Shift+Enter生效，365/2021及以后版本直接回车即可。

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