自动处理考勤表——如何使用Power Query，步步为营，一点点探索自定义函数

附件下载

我们来看一份考勤数据的格式。假如公司有200名员工，要整理出所有人的缺卡、迟到、早退等数据，如果要手工处理的话，可能是件很费时间的事情。那有没有好的办法呢？当然有的。

1. 将1张工作表分割为3个单个员工的表

1.1. 起点与终点

刚拿到问题没有思路是很正常。不管三七二十一，我们先读一个工作表进来。从上图也可以看来，每14列是一个表，表之间一列空列隔开。将这个查询命名为 参数表-分割表

然后引用这个表，以这个表为起点，我们进行转换，转换的终点如下图。我们得到这样一个表：这个表的第2列是3个子表，每个子表都是一名员工的考勤表；这个表的第2列是员工姓名。

1.2. 转换过程

上面所提的转换的过程如下：

1. 先使用 Table.ToColumns 将起点表转换为所有列的列表。
2. 然后以每15个分一段，将这个列表分成3个子列表。
   这个列表中的每个子列表，都正好对应一名员工的考勤表。但是以列的列表形式存在的。如图：这个子列表里面还有15个子子列表，每个子子列表正好是一列数据。
3. 通过使用 List.Tranform( arr1, (x)=>Table.FromColumns(x) )，将上一步中的每个子列表还原为单个表。!
   选中任意一个Table，我们可以看到它正好是一名员工的考勤表
4. 使用 Table.Skip 将最上面不需要的2行删除掉。
5. 到上一步为止，我们得到的还是一个表的列表。使用"转换到表"功能，将它变成一个只有一列的表的表。
6. 我们可以看到第10列的第一个值就是员工姓名。对上一步新增一列，取到员工姓名。

1.3. 将查询转化为函数，以便于复用

1. 复制一份上面的查询。打开高级编辑器。
2. 在 let 前加入一行 (inputTable)=>，定义一下输入参数。
3. 选中原先的第一步 源=#"参数表-分割表"，按CTRL+/，将其转化为注释
4. 新增一行 源=inputTable 以引用上面的参数。
   

到此为止，我们实现了将一个工作表分割为3个单名员工的考勤表并取到了员工姓名，并将这个过程定义为了一个函数。

我们可以尝试调用一下这个函数。输入参数名，并点击调用。生成了调用结果。

发现报错了。因为参数是个文本，而不是表格。在"之前加上#，使参数成为了表对象，就正常了。

2. 处理单个员工的考勤表

读取考勤表，并按以下图示的过程调用上面定义的函数处理。

我们得了所有员工的考勤表原表和他们的姓名。

但这里的考勤表格式还不是我们最终想到的。
所以我们还需要再构建一个函数。首先还是确定我们这个函数的起点和终点。

2.1. 起点与终点

起点如上图。终点如下图，一个仅含日期、星期、上班打卡时间、下班打卡时间4列的格规的表格。

2.2. 转换过程

1. 引用上面的一个当参数表，并将查询命名为"参数表-处理表"。
2. 新建一个名为"处理表"的查询，引用上面这个参数表。
3. 删除最上方用不到的7行，再提升一次标题。结果如图：
4. 以空格拆分列，将Column1拆分为日期和星期几。
5. 将所有时间列的数据类型改为时间。
6. 将上班列以后的所有列，合并为一列“下班”，合并规则为取其中最大的数。因为观查考勤表可以发现，后面的各种列表述的都是下班时间，只不过情况各有不同。合并方法如下：
   先点击菜单中的合并列 按扭，按提示生成一个自动的合并列
   然后将其中的合并规则函数 Combiner.CombineTextByDelimiter("", QuoteStyle.None) 改为我们想要的规则 List.Max
   最后修改一下列名，改为"下班"
7. 修改列名，调整格式，得到结果。

2.3. 将查询转化为函数

方法与上一个函数一样：

1. 复制一份上面的查询。打开高级编辑器。
2. 在 let 前加入一行 (inputTable)=>，定义一下输入参数。
3. 选中原先的第一步 源=#"参数表-处理表"，按CTRL+/，将其转化为注释
4. 新增一行 源=inputTable 以引用上面的参数。

3. 引用构建好的函数解决问题

我们对上一步未处理完的考勤表，添加自定义列，在自定义列中引用刚刚定义好的函数 fx处理表 来处理单名员工的考勤表。

我们发现第3、6行出现了错误。点一下错误，看到错误提示：在“”查询中出错。Expression.Error: 找不到表的“Column15”列。 结合出错的正好是第3、6个表，我们不难测出原因是，我们的原工作表只有44列，不存在第3个表后面的第45列这个空列。

回到名为处理表的这个查询中，寻找有用到Column15的步骤。发现合并列 这一步有用到Column15。

将代码中的 ,“Column15” 删除。所有表格都正常了。

如果错误源头不好找，可以将查询 参数表-处理表，改为出错的那个表。比如上面出错的法正、赵云。然后 处理表 这个查询本身就会报错，解决它的报错。再将它的 源=XXX 这行以下的代码（不包括本行）直接复制，去替换掉 fx处理表 的相应代码就可以了。

至此，我们得到我们想要的结果。

展开后得到最终的规范的总表。

4. 分类并透视

将最终的查询加载到工作表，使用如下这个表格函数对出勤情况进行分类。

=SWITCH(TRUE(),(([@上班] = "") + ([@下班] = "")) = 2, "未出勤",[@上班] = "", "上班缺卡",[@下班] = "", "下班缺卡",AND([@上班] <= TIME(9, 0, 0),[@下班] >= TIME(17, 0, 0)), "正常出勤",IF([@上班] > TIME(9, 0, 0), "迟到", "") &IF([@下班] < TIME(17, 0, 0), "早退", "")
)

得到员工每天的考勤结果。

再对每天的考勤结果进行透视。就得到了所有员工的考勤汇总结果。结合请假记录，稍加调整便可以得到想要的结果了。