Office 365中如何将行值自动转换为按Function分组关联Scenario的表格？

嘿，我懂你要的效果啦——就是把原来的Scenario和Function对应关系，转成按Function分组、每个Function下面列全对应Scenario的表格对吧？在Office 365里有俩超方便的自动实现方法，一个用动态数组公式，一个用Power Query，我给你唠唠细节：

方法一：动态数组公式（适合爱折腾函数的同学） #

假设你的原始数据是A列存Scenario（比如A2到A7），B列存Function（B2到B7）。找个空白单元格（比如D2），粘贴下面这个公式回车就行，它会自动生成完整的分组表格：

=LET(
    Scenarios, A2:A7,
    Functions, B2:B7,
    unique_func, UNIQUE(Functions),
    grouped, BYROW(unique_func, LAMBDA(f, HSTACK(
        MAKEARRAY(COUNTA(FILTER(Scenarios, Functions=f)), 1, LAMBDA(r,c,f)),
        FILTER(Scenarios, Functions=f)
    ))),
    WRAPROWS(TOCOL(grouped), 2)
)

简单说下这个公式的逻辑：

• 先用UNIQUE揪出所有不重复的Function
• 挨个遍历每个唯一Function，用MAKEARRAY生成和对应Scenario数量一样多的重复Function值，再用FILTER把该Function对应的Scenario都捞出来
• 最后把这些数据平铺后转成2列的表格格式

而且原始数据改了的话，这个表格会自动跟着更新，完全不用手动调整。

方法二：Power Query（适合喜欢可视化操作的同学） #

要是觉得公式有点绕，Power Query绝对是友好之选，步骤超清晰：

1. 选中你的原始数据区域，点顶部「数据」选项卡，选「从表格/区域」（如果提示转成表格，记得勾上「我的表格有标题」）。
2. 进了Power Query编辑器后，选中Function列，点「转换」选项卡的「分组依据」按钮。
3. 分组对话框里这么设置：
   • 分组依据：选择「Function」
   • 新列名：随便起个名字，比如「Scenarios」
   • 操作：选择「所有行」，然后点击确定。
4. 现在你会看到每行是一个Function加它对应的所有原始行，点Scenarios列右边的双向箭头（展开按钮），只勾选「Scenario」列，确定就行。
5. 最后点「关闭并上载」，新表格就会出现在新工作表里。以后原始数据更新了，右键点这个新表格选「刷新」，分组结果就自动同步啦。

俩方法都能搞定，看你更习惯哪种操作～

备注：内容来源于stack exchange，提问作者Rob Clark