我们平时从考勤机导出的考勤数据，通过Excel打开后基本都是这样的格式：

为了进一步统计考勤数据，希望将上面的这种格式整理为这样的效果：

每人每天的打卡记录在同一行，并且根据上下班时间比对后标注出异常情况。

上下班时间规定如下：

上午上班时间8:00，上午下班时间12:00，下午上班时间13:30，下午下班时间17:30

要实现这种效果的转换，感觉是非常麻烦的一件事，其实只要掌握两个公式和一些基本的操作技巧，一分钟就可以完成，下面就来看看如何实现吧。

一、基础数据整理

在基础数据的右边添加几列，将时间分为上午上班、上午下班、下午上班和下午下班四列，并且标注出对应的时间：

将卡号、人员和日期三列复制到右边对应的位置，然后使用“删除重复项”功能：

点击确定后会删除重复的内容，每人每天只保留一行：

接下来的任务就是将对应的打卡时间填入对应的位置，并且对异常数据不显示具体时间，只显示异常两个字。为了实现这个目的，需要使用两个公式来配合，下面先看第一个公式。

二、使用公式备注打卡时间

为了便于对打卡时间进行统计，首先要根据上下班时间进行备注，实现下图中的效果：

根据上下班时间需要分为四种情况：

1、8点以前打卡视为上午上班；

2、12点以后打卡视为上午下班，考虑到还有下午上班这个因素，人为规定12点到12点30之间打卡为上午下班；

3、同理，人为规定13点到13点30之间打卡为下午上班；

4、17点30以后打卡为下午下班；

5、除此之外的时间打卡均为无效，显示空白。

E2单元格公式为：

，双击填充可实现图中的效果。

第一个IF为：

当d2（打卡时间）小于等于k1（上午上班时间）时，if函数的结果为k2（上午上班这四个字），否则返回空值；

第二个if为：

当d2（打卡时间）大于等于L2（上午下班时间）同时小于等于o1（人为规定下班打卡截止时间）时，if函数的结果为L2（上午下班这四个字），否则返回空值。

第三个if为：

当d2（打卡时间）大于等于p2（人为规定上班打卡开始时间）同时小于等于M1（下午上班时间）时，if函数的结果为M2（下午下班这四个字），否则返回空值。

第四个if为：

当d2（打卡时间）大于等于N1（下午下班时间）时，if函数的结果为N2（下午下班这四个字），否则返回空值。

完成了备注信息之后，就该把对应的时间填入对应的区域内，这时候可以用一个公式右拉下拉就能完成时间的填充，一起来看看是哪个神奇的公式吧。

三、填充时间

在K2单元格输入公式：

右拉下拉即可完成时间的填充。

这个公式用到了两个函数，text和sumifs，来看看公式的原理吧。

sumifs函数的结构为，在今天的例子里我们用了三个条件，实际求和的是D列，三个条件分别是日期、卡号和备注信息，符合三个条件的数字都是唯一的，所以求和结果和引用结果是一致的。

因此公式为：

使用公式得到结果是这样的一些数字，因为在Excel中，日期和时间的本质都是数字，整数代表日期，而小数就代表时间，将上述区域单元格格式改为时间再看看效果，数字都变成了具体的时间，如下所示：

实际上在进行了单元格格式设置后基本就达到目的了，为了完善显示效果，同时强制显示为时间格式，我们在sumifs外面再加了一个text函数，即使在常规格式下，也是按时间来显示的，同时0所在的位置显示为异常。

简单解释一下text的用法，text（数据，指定的格式），在本例中，第二参数格式定义为时分秒的显示方式，字母h、m和s分别表示时分秒，都是两位数字显示。

格式代码中的分号，可以按照数据类型单独设置显示方式，text规定将数据分成四种：正数;负数;零;文本。本例中正数按照时间格式显示，负数和文本没有指定格式就不显示，而零显示为异常两个字。

关于text函数今天只是了解在本例的用法即可。

小结：

1、合理利用辅助列：考勤数据的整理历来都是比较麻烦的问题，一步到位往往非常困难，此时合理的利用辅助列就能将问题的难度一下子降低不少。

2、公式不能万能的：考勤的基础数据就是时间，还有相关的规定共同组成了数据之间的逻辑关系，本例适合比较规范的情况，如果是多种班次并存的情况，就不能通过这种方法来实现了，还要结合每个人的班次对应的上下班时间来综合考虑，就需要考勤机的配套软件来完成数据的统计汇总。

你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

撤销：Ctrl/Command + Z 重做：Ctrl/Command + Y 加粗：Ctrl/Command + B 斜体：Ctrl/Command + I 标题：Ctrl/Command + Shift + H 无序列表：Ctrl/Command + Shift + U 有序列表：Ctrl/Command + Shift + O 检查列表：Ctrl/Command + Shift + C 插入代码：Ctrl/Command + Shift + K 插入链接：Ctrl/Command + Shift + L 插入图片：Ctrl/Command + Shift + G 查找：Ctrl/Command + F 替换：Ctrl/Command + G

直接输入1次#，并按下space后，将生成1级标题。 输入2次#，并按下space后，将生成2级标题。 以此类推，我们支持6级标题。有助于使用TOC语法后生成一个完美的目录。

强调文本 强调文本

加粗文本 加粗文本

标记文本

删除文本

引用文本

H2O is是液体。

210 运算结果是 1024.

链接: link.

图片:

带尺寸的图片:

居中的图片:

居中并且带尺寸的图片:

当然，我们为了让用户更加便捷，我们增加了图片拖拽功能。

去博客设置页面，选择一款你喜欢的代码片高亮样式，下面展示同样高亮的 代码片.

一个简单的表格是这么创建的：

使用:---------:居中 使用:----------居左 使用----------:居右

SmartyPants将ASCII标点字符转换为“智能”印刷标点HTML实体。例如：

一个具有注脚的文本。2

Markdown将文本转换为 HTML。

您可以使用渲染LaTeX数学表达式 KaTeX:

Gamma公式展示 Γ ( n ) = ( n − 1 ) ! ∀ n ∈ N \Gamma(n) = (n-1)!\quad\forall n\in\mathbb N Γ(n)=(n−1)!∀n∈N 是通过欧拉积分

Γ ( z ) = ∫ 0 ∞ t z − 1 e − t d t   . \Gamma(z) = \int_0^\infty t^{z-1}e^{-t}dt\,. Γ(z)=∫0∞​tz−1e−tdt.

你可以找到更多关于的信息 LaTeX 数学表达式here.

可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图：

这将产生一个流程图。:

我们依旧会支持flowchart的流程图：

如果你想尝试使用此编辑器, 你可以在此篇文章任意编辑。当你完成了一篇文章的写作, 在上方工具栏找到 文章导出 ，生成一个.md文件或者.html文件进行本地保存。

如果你想加载一篇你写过的.md文件，在上方工具栏可以选择导入功能进行对应扩展名的文件导入， 继续你的创作。

mermaid语法说明 ↩︎

注脚的解释 ↩︎