练习题045及答案：将不规范的考勤数据整理成清单式表格

本文是Excel偷懒的技术读者群练习题045号的答案，练习题传到群里一个月了，因过春节忘写答案了，今天把答案奉上。

《练习题045：将不规范的考勤数据整理成清单式表格（函数、基本操作）》是根据偷懒的技术读者群读友夜光shi色的问题改编的，原题如下：

上表是某公司的考勤表简表，每天的考勤都记录在同一单元格，按日期逐列排列，这个表显然不利于计算考勤和迟到早退。现要求将其整理成下表的样式以方便进行统计：

原考勤表是二维报表样式，目标表是一维清单式的，要将二维报表转换为一维的有一个专业名词，叫“逆透视”，如果在Power Query中，有逆透视功能，很容易完成转换。如果不用Power Query，可以数据透视表来完成。

具体操作如下：

步骤一：按Alt+D+P，打开数据透视表向导；选择“多重合并 计算区域”

步骤二：在弹出的对话框中选择“创建单页字段”

步骤三：然后选择要转换的数据区域B1:K11单元格（注意，不含A列），点击添加，然后点击下一步。

步骤四：选择“新建工作表”，点击完成，将透视表呈现在新的工作表，如下图所示

步骤五：用鼠标双击K15单元格，将数据源的记录罗列到新的表格

步骤六：修改列标题。删除D列，然后在A列前插入一列，使用INDEX和MATCH公式根据姓名查找引用的工号，A2单元格公式如下

=INDEX(源数据!$A$2:$A$11,MATCH(B2,源数据!$B$2:$B$11,0))

步骤七：在E2、F2分别输入公式下面的公式

=IF(LEN([@打卡时间])=5,IF(--[@打卡时间]<0.5,[@打卡时间],""),LEFT([@打卡时间],5))=IF(LEN([@打卡时间])=5,IF(--[@打卡时间]>=0.5,[@打卡时间],""),RIGHT([@打卡时间],5))

公式中的[@打卡时间]是表格的规范化引用，意思是取打卡时间字段对应本行的记录。E2单元格公式中的[@打卡时间]相当于D2单元格。

步骤八：将E列F列复制粘贴为数值，然后删除D列。整理工作完成。

另外还有一个考勤表整理的案例，如下图：

如何将其整理成下面的规范的清单式表格：

可以参看下面的视频课程，课程介绍了两种方法：用基本操作和函数、用Power Query。