☰

使用 Excel 和 PowerQuery 设计大规模考勤系统

tag-icon • tag-icon microsoft-excel microsoft-excel-2016 power-query microsoft-powerbi
使用 Excel 和 PowerQuery 设计大规模考勤系统

我正在建立一个 Excel 电子表格，用于跟踪大量人员的日常出勤记录。此处显示的所有数据示例都是虚构的，包括数据结构。仅显示数据结构的要点，仅供说明之用，因为我正在使用严格锁定的计算机处理敏感的个人数据（即没有互联网，禁止使用任何形式的脚本，如 VBA，只能使用 Microsoft Office 套件来处理数据）。

包含人事记录的主表如下所示：

Master List

ID     Name     Home Address         Phone Number   Remarks
1      Alice    12 Somewhere Rd.     123456         remark 1 goes here
2      Bob      23 Someplace Dr.     234567         remark 2 goes here
3      Charlie  34 Somewhen Blvd.    345678         remark 3 goes here

可以看出，考勤时不需要所有信息。因此，我执行 Power Query 将其精简为必需信息

Attendance

ID     Name      Remarks
1      Alice     remark 1 goes here
2      Bob       remark 2 goes here
3      Charlie   remark 3 goes here

之后，我将出勤记录附加到表格的右侧。

Attendance

ID     Name      Remarks               2020/6/25   2020/6/26  ...............
1      Alice     remark 1 goes here    P           P          ...............
2      Bob       remark 2 goes here    P           VL         ...............
3      Charlie   remark 3 goes here    P           P          ...............

最后我建立一个自参考表这样，每次刷新考勤表时，考勤条目都会保留在原来的行中，这意味着如果我在中间某处插入人员，不会出现任何问题。

然而，当我意识到我需要为每个特殊出勤条目（例如病假）添加与休假本身相关的详细信息时，我很快就遇到了一个问题。我最初尝试直接在出勤单元格上添加注释，但意识到注释并不遵循 Power Query 生成的表格（即它停留在原始的绝对单元格位置，这意味着插入新人将使表格的下半部分错开一行）。

我后来尝试使用超链接将每个出勤条目链接到一个单独的表，该表包含有关休假的备注/详细信息，但在查询刷新后该链接变得无法点击。

我应该如何设计按天按人的出勤系统的备注系统，我一开始的方法是查询主列表，减少列，然后添加更多列，这是否是正确的方法？

谢谢你！

答案1

我一开始的方法是否正确？即查询主列表、减少列，然后添加更多列。

这是一种方法。我认为您只需为每个日期添加一个备注列，然后按照您已经采取的方法获得一个自引用表，就可以获得备注空间。如下所示：

在此处输入图片描述

就我个人而言，我不会这么做，因为当问题出现时（总是会出现），比如“你能告诉我 Alice 和 Bob 在过去 30 个工作日内一起缺勤了多少次吗？”或“你能告诉我平均缺勤天数吗？”等等，这些问题将更难解决（不是那虽然很难公平，但仍然如此。

因此，我更愿意使用规范化数据进行输入，然后可以将其转换为报告。

考虑到这一点，我从两个表开始：

  1. 人们
  2. 日期

在此处输入图片描述

Include 列使用这个公式（原因稍后会清楚）：

=[@Dates]<=TODAY()

我在每个表上创建了一个查询。在日期查询中，我过滤了 Include=TRUE，然后删除了 Include 列。然后，我在人员查询中添加了一个自定义列来引用日期查询。如下所示：

在此处输入图片描述

您可以看到它已将第三列添加到人员查询中。 展开后会给出所有人员的所有日期的漂亮列表（这本质上是笛卡尔连接）：

在此处输入图片描述

我将其加载到工作簿中并添加两列 - 出勤和备注。这样做的好处是，您现在可以添加任意数量的列来注释或标记每个人每天的记录。

在此处输入图片描述

此时，我读到了关联到你在帖子中引用的页面。一开始我搞错了。然后我又搞错了。然后我意识到它是如何实际上工作并最终得到了一个自参考表，现在我可以根据这个表编辑我的出勤情况、我的评论，并为新员工刷新信息，所有东西都保持在正确的位置。

这样做的另一个好处是，每天当我打开工作簿并刷新查询时，都会自动向当天每个员工的查询中添加一组新行（这最终是在日期表中定义“包含”列的目的）。

编辑：

您可以使用 PowerQuery 将数据转换为各种报告格式，包括您帖子中第三个表格的格式。

对我上面的回答的最终结果创建一个查询，然后执行以下操作：

选择出勤率和备注，然后使用转换>任意列>取消透视列>仅取消透视选定的列：

在此处输入图片描述

您会注意到，此操作会删除所有空单元格。如果要保留这些单元格，则需要在使用 Unpivot 之前填充它们。这可以通过将值从空值替换为您喜欢的任何值来完成。

取消透视后，我有属性和值列。现在我想选择日期。日期和属性，右键单击并使用合并列。我将分隔符设置为“-”（当然，您可以使用任何您喜欢的分隔符），最后得到一个表示日期及其引用的数据类型（出勤或备注）的列。

然后，我选择这个新的合并列并使用“变换”>“任何列”>“数据透视列”并按如下方式配置它：

在此处输入图片描述

最终结果就是您想要的格式。同样，如果您希望此报告包含所有人（无论是否输入了他们的出勤情况），则需要在使用 Unpivot 之前在 Attendance 列中设置一个虚拟值。

在此处输入图片描述

如果您不介意我使用口语，这一切可能看起来有点像在家里四处奔波。一般来说，我鼓励任何人在确定解决方案之前先考虑一下这些数据的使用方式。根据我的经验，首先将数据标准化（如我所建议的），然后根据该表构建报告是一种更可持续的方法，并且提供了更大的灵活性。

相关内容