用分而治之的办法处理复杂表格

在《用PowerBI自定义函数批量处理复杂表格》中我提到用自定义函数处理复杂表格。
这是针对有N个复杂表格需要处理的情况——我们只需要把每个复杂表格处理的步骤用函数封装起来，就能在其他复杂表格上调用该函数了。
但是，在用自定义函数处理复杂一个表格时，当处理步骤很复杂的时候，还可以利用PowerQuery的特性，采用分而治之的办法，把复杂表格分割成不同的处理步骤，最后再把分步处理结果合并起来即可，

需求

现在有很多个下面这样的表格，每个表格是一个单独的sheet，同一个部门所有人都在一个Excel文件的N个sheets中。主管和员工的表格有差异（主管没有部门主管评分，有财务部评分，各层级打分者在四个一级维度中所占的权重不同，部门主管的考核项多了一个部门管理）。如下表所示：

xxx |  |  |  |  |  |  |  |  |

计算公式为：
员工工作态度考核得分=SUM(总经办评分)15%+SUM(部门主管评分)15%+SUM(行政人力评分)60%+SUM(自评)10%

员工工作态度奖励得分=SUM(总经办奖励评分)20%+SUM(部门主管奖励评分)20%+SUM(行政人力奖励评分)*60%

员工工作量考核得分=SUM(总经办评分)15%+SUM(部门主管评分)60%+SUM(行政人力评分)15%+SUM(自评)10%

员工工作量奖励得分=SUM(总经办奖励评分)20%+SUM(部门主管奖励评分)60%+SUM(行政人力奖励评分)*20%

员工工作成果考核得分=SUM(总经办评分)60%+SUM(部门主管评分)15%+SUM(行政人力评分)15%+SUM(自评)10%

员工工作成果奖励得分=SUM(经办奖励评分)60%+SUM(部门主管奖励评分)20%+SUM(行政人力奖励评分)*20%

员工最终考核得分=员工工作态度考核得分+员工工作量考核得分+员工工作成果考核得分

员工最终奖励得分=员工工作态度奖励得分+员工工作量奖励得分+员工工作成果奖励得分

管理员的得分计算公式与此类似，只是具体权重不同，而且部门主管变成了财务部，就不一一列举了。

要求是把所有人部门所有人（主管和员工）的打分结果汇总统计出来，便于财务发工资。

分析

这个表格变态复杂，填表的人头大如豆，最后统计汇总打分结果的人更是头大如豆。在既成事实的前提下，再提把表格变得更简单已经无意义。
现在最紧迫的是如何完成快速统计工作。

我想到三种方法：vba、python和powerquery。
vba我不会。
python尝试了下xlwings，只会绝对引用，那样的话如果有一个表格的某个单元格和别的不一样，就不行了。
最后还是回到我最熟悉的powerquery。

引入单一数据源

有N个Excel文件和M个sheets需要合并，因此首先引入来自文件夹的源，这样就把所有的Excel文件和sheets一次性导入powerquery了。

let
    源 = Folder.Files(path),
    删除的其他列 = Table.SelectColumns(源,{"Content", "Name"}),
    已添加自定义 = Table.AddColumn(删除的其他列, "自定义", each Excel.Workbook([Content])),
    #"展开的“自定义”" = Table.ExpandTableColumn(已添加自定义, "自定义", {"Data", "Item"}, {"自定义.Data", "自定义.Item"}),
    删除的列 = Table.RemoveColumns(#"展开的“自定义”",{"Content"})
in
    删除的列

Folder.Files()函数把文件夹下的所有文件引入powerquery；Excel.Workbook()函数解析Excel文件中的sheets，把每一个Excel文件中的每个sheets变成一个table，然后所有这些tables嵌套在表示单一Excel文件的table中。
待会儿的工作就要在这些嵌套tables上进行。

由于主管和员工的表格不一样，因此我决定先筛选出主管和员工表格，分开处理。
观察主管和员工表格，发现第八列【Column8】不一样：主管表格是财务评分，员工表格是部门主管评分。因此直接添加一列来判断是否主管。

= Table.AddColumn(源, "是否主管", each if  List.Contains([自定义.Data][Column8],"部门主管评分")  then "N" else "Y")

然后根据【是否主管】这个字段的值，将数据源分为两个查询：主管得分和员工得分。
当然，这一步不是必须，建立函数后，可以利用if判断语句来一次同时处理主管和员工的表格。

利用powerquery【let…in】语句特性分步处理同一个表格

在Powerquery中，【let…in】有一个特性，即可以在任意步骤中随时跳转，利用这个特性，就可以在同一个查询中，将复杂处理分为不同步骤，最后再把各步处理的结果合并起来。
具体而言，我将主管考核表中的四个考核项分别处理（以自定义函数的方式）：

(tbl as table)=>let
    源 = tbl,
    替换的值= Table.ReplaceValue(源,null,0,Replacer.ReplaceValue,{"姓名", "评价维度", "总经办评分", "总经办加减分", "财务评分", "财务加减分", "行政人力评分", "行政人力加减分", "自评"}),
    start = 替换的值,
    筛选的行10 = Table.SelectRows(start, each ([评价维度] = "部门管理")),
    已添加自定义 = Table.AddColumn(筛选的行10, "考核得分", each [总经办评分]*0.5+[财务评分]*0.25+[行政人力评分]*0.15+[自评]*0.10),
    rename = Table.AddColumn(已添加自定义, "奖励得分", each [总经办加减分]*0.50+[行政人力加减分]*0.15+[财务加减分]*0.35),
    重命名的列3 = Table.RenameColumns(rename,{{"总经办加减分", "总经办奖励得分"}, {"财务加减分", "财务奖励得分"}, {"行政人力加减分", "行政人力奖励得分"}}),
    部门管理 = Table.RenameColumns(重命名的列3,{{"总经办评分", "总经办考核评分"}, {"财务评分", "财务考核评分"}, {"行政人力评分", "行政人力考核评分"}}),
    自定义1 =start,
    筛选的行2 = Table.SelectRows(start, each ([评价维度] = "工作态度")),
    自定义2 = Table.Group(筛选的行2, {"姓名", "评价维度"}, {
        {"总经办考核评分", each 0.35*List.Sum([总经办评分]), type number},
         {"财务考核评分", each 0.35*List.Sum([财务评分]), type number}, 
         {"行政人力考核评分", each 0.20*List.Sum([行政人力评分]), type number}, 
         {"自评", each 0.1*List.Sum([自评]), type number},
         {"总经办奖励得分", each 0.35*List.Sum([总经办加减分]), type number}, 
         {"财务奖励得分", each 0.30*List.Sum([财务加减分]), type number}, 
         {"行政人力奖励得分", each 0.35*List.Sum([行政人力加减分]), type number}}),
    已添加自定义20 = Table.AddColumn(自定义2, "考核得分", each [总经办考核评分]+[财务考核评分]+[行政人力考核评分]+[自评]),
    工作态度 = Table.AddColumn(已添加自定义20, "奖励得分", each [总经办奖励得分]+[财务奖励得分]+[行政人力奖励得分]),
    自定义3 =  start,
    筛选的行3 = Table.SelectRows(自定义3, each ([评价维度] = "工作成果")),
    自定义4 = Table.Group(筛选的行3, {"姓名", "评价维度"}, {
        {"总经办考核评分", each 0.35*List.Sum([总经办评分]), type number},
         {"财务考核评分", each 0.35*List.Sum([财务评分]), type number}, 
         {"行政人力考核评分", each 0.20*List.Sum([行政人力评分]), type number}, 
         {"自评", each 0.1*List.Sum([自评]), type number},
         {"总经办奖励得分", each 0.40*List.Sum([总经办加减分]), type number}, 
         {"财务奖励得分", each 0.20*List.Sum([财务加减分]), type number}, 
         {"行政人力奖励得分", each 0.40*List.Sum([行政人力加减分]), type number}}),
    已添加自定义3 = Table.AddColumn(自定义4, "考核得分", each [总经办考核评分]+[财务考核评分]+[行政人力考核评分]+[自评]),
    工作成果 = Table.AddColumn(已添加自定义3, "奖励得分", each [总经办奖励得分]+[财务奖励得分]+[行政人力奖励得分]),
    自定义5 = start,
    筛选的行4 = Table.SelectRows(自定义5, each ([评价维度] = "工作量")),
    自定义6 = Table.Group(筛选的行4, {"姓名", "评价维度"}, {
        {"总经办考核评分", each 0.35*List.Sum([总经办评分]), type number}, 
        {"财务考核评分", each 0.35*List.Sum([财务评分]), type number}, 
        {"行政人力考核评分", each 0.20*List.Sum([行政人力评分]), type number}, 
        {"自评", each 0.1*List.Sum([自评]), type number},
        {"总经办奖励得分", each 0.35*List.Sum([总经办加减分]), type number}, 
        {"财务奖励得分", each 0.30*List.Sum([财务加减分]), type number}, 
        {"行政人力奖励得分", each 0.35*List.Sum([行政人力加减分]), type number}}),
    已添加自定义4 = Table.AddColumn(自定义6, "考核得分", each [总经办考核评分]+[财务考核评分]+[行政人力考核评分]+[自评]),
    工作量 = Table.AddColumn(已添加自定义4, "奖励得分", each [总经办奖励得分]+[财务奖励得分]+[行政人力奖励得分]),
    自定义7 = Table.Combine({部门管理,工作态度,工作成果,工作量}),
    分组的行1 = Table.Group(自定义7, {"姓名"}, {{"考核得分", each List.Sum([考核得分]), type number}, {"奖励得分", each List.Sum([奖励得分]), type none}})
in
    分组的行1

从第10行开始，我分别用了【自定义1】、【自定义3】和【自定义5】引用了同一个步骤【start】，【start】步骤就是包含【部门管理】、【工作态度】、【工作量】和【工作成果】四个考核指标的一个大表格，我每引用一次【start】，就对其筛选一次，处理四个考核指标中的一个，处理完了再回到【start】，筛选另一个指标进行处理，以此类推。四个指标处理完毕之后，得到四个表格，用Table.Combine()函数合并四个表格。

上面那段很长的代码，我是完全复现了每个考核指标的计算公式，如果只想要结果的话，可以略过公式计算，直接对表格中的【考核得分】列进行处理，这样步骤会简洁很多，甚至都不需要分而治之的策略了。

用同样的思路处理员工表格。最后将两个表格合并起来就ok了。