实用Excel技巧分享：快速整理考勤数据！

Excel快速填充数据，Ctrl + D #生活技巧# #数码产品使用技巧# #办公软件快捷键#

这里写自定义目录标题 欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants 创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入

分享一篇整理考勤数据的教程，就简单两个公式，学会后，不管你公司有多少人都不带怕的。
考勤机几乎是每个公司都会使用的，而每到月初月末，整理考勤机导出的考勤数据，需要耗费我们大量的时间，今天教给大家两个公式，一分钟就可以整理出考勤数据。

我们平时从考勤机导出的考勤数据，通过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编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

全新的界面设计 ，将会带来全新的写作体验；在创作中心设置你喜爱的代码高亮样式，Markdown 将代码片显示选择的高亮样式 进行展示；增加了 图片拖拽 功能，你可以将本地的图片直接拖拽到编辑区域直接展示；全新的 KaTeX数学公式 语法；增加了支持甘特图的mermaid语法1 功能；增加了 多屏幕编辑 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.

图片:

带尺寸的图片:

居中的图片:

居中并且带尺寸的图片:

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

如何插入一段漂亮的代码片

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

// An highlighted block var foo = 'bar'; 12

生成一个适合你的列表

项目 项目 项目 项目1项目2项目3 计划任务 完成任务

创建一个表格

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

设定内容居中、居左、居右

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

第一列第二列第三列第一列文本居中第二列文本居右第三列文本居左 SmartyPants

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

TYPEASCIIHTMLSingle backticks'Isn't this fun?'‘Isn’t this fun?’Quotes"Isn't this fun?"“Isn’t this fun?”Dashes-- is en-dash, --- is em-dash– is en-dash, — is em-dash

创建一个自定义列表

Markdown Text-to- HTML conversion tool Authors John Luke

如何创建一个注脚

一个具有注脚的文本。2

注释也是必不可少的

Markdown将文本转换为 HTML。

KaTeX数学公式

您可以使用渲染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.

新的甘特图功能，丰富你的文章

Mon 06 Mon 13 Mon 20

已完成 进行中 计划一 计划二 现有任务 Adding GANTT diagram functionality to mermaid

关于 甘特图 语法，参考 这儿,

UML 图表

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

张三 李四 王五

你好！李四, 最近怎么样? 你最近怎么样，王五？ 我很好，谢谢! 我很好，谢谢! 李四想了很长时间, 文字太长了 不适合放在一行. 打量着王五... 很好... 王五, 你怎么样? 张三 李四 王五

这将产生一个流程图。:

关于 Mermaid 语法，参考 这儿,

FLowchart流程图

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

Created with Raphaël 2.3.0 开始 我的操作

确认？ 结束

yes

no

关于 Flowchart流程图 语法，参考 这儿.

导出与导入

导出

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

导入

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

mermaid语法说明 ↩︎

注脚的解释 ↩︎

网址：实用Excel技巧分享：快速整理考勤数据！ https://www.yuejiaxmz.com/news/view/407557

相关内容

高效整理数据：Excel实用技巧指南(如何用excel整理.数据)
EXCEL函数及数据分析技巧整理备用
提升Excel数据处理效率的实用技巧与方法分享
Excel单元格拆分技巧：快速整理数据的4种方法
Excel​​快速整理表格的技巧！
快速整理excel
Excel去重技巧：快速清理数据的5种方法
Excel教程： 透视表整理考勤记录就是这么神速 – Office自学网
Excel空格删除技巧：快速清理数据的5种方法
Excel数据去重技巧：快速清理表格的四种方法

随便看看