excel 处理数据的常用场景之考勤表的制作

文章目录

预备知识
- 数据有效性
- excel的图表知识
考勤表
- 效果图
- 开始制作
- - 1. 首先我们去分析这个表格，我们把它分为三个部分如下：
  - 2. 开始第一个部分的制作
  - 3. 开始制作第二部分。
  - 4. 第三部分也是最复杂的，因为会用到几个公式，我这里下面会给出这几个公式的语法。
  - - - if函数
    - - vlookup函数
    - - countif函数
    - - date函数
    - - today函数
  - 5. 介绍完了，上述的函数之后，我们开始非常重要的第三个模块的表格布局。
  - - 第一个功能的视线
    - 第二个功能的实现

预备知识

数据有效性

我们在Excel中输入数据时，有时候会进行数据有效性设置，可以给我们节约很多的时间，也可以提高输入的准确性。
那么，我们如何进行Excel数据有效性的设置呢？接下来，我通过1个实例，给大家讲解这个原因
1、我们首先打开我们制作好的Excel表格！
在这里插入图片描述

2、我们设置一下性别，可以不用往里面输入数据，我们直接点击选择“男”或者“女”，怎么操作呢？我们选择性别中的单元格，然后，点击【工具栏】中的【数据】的选项卡，再找到【数据验证】的按钮！

在这里插入图片描述
3、接着，我们在【数据验证】的弹框中来进行Excel数据有效性的设置，我们需要把数据有效性设置为【序列】,然后，我们在【来源】中输入【男】【女】，中间用【英文】状态下的逗号隔开！（如图所示）
在这里插入图片描述

4、这样一来，我们就不需要输入男女了，只需要用鼠标操作选择即可！这样可以节省我们输入的时间！
在这里插入图片描述
这就是，基本的数据有效的性的知识，我们下面的图表会用的知识，也就是这些。

excel的图表知识

考勤表

效果图

在这里插入图片描述

开始制作

1. 首先我们去分析这个表格，我们把它分为三个部分如下：

在这里插入图片描述

2. 开始第一个部分的制作

选中一个区域，然后再开始选项卡中点击合并单元格的操作。

在这里插入图片描述
在合并的单元格中输入员工考勤表字样

3. 开始制作第二部分。

这一部分也是一些简单的表格，考勤和员工的查询表，目前，只要按照这个架子，去把表格模型搭起来了，至于表格美化，后期都是可以做的。
在这里插入图片描述

4. 第三部分也是最复杂的，因为会用到几个公式，我这里下面会给出这几个公式的语法。

- if函数

语法：IF(logical_test, value_if_true, value_if_false)
解释：
logical_test：这是一个条件表达式，可以评估为 TRUE 或 FALSE。
value_if_true：如果 logical_test 结果为 TRUE，则返回此值。
value_if_false：如果 logical_test 结果为 FALSE，则返回此值。

=IF(A1 > 10, "大于10", "小于等于10")

这个公式会检查 A1 单元格中的值是否大于 10。如果是，则返回“大于10”，否则返回“小于等于10”。

- vlookup函数

语法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
解释：
lookup_value：要查找的值。
table_array：包含数据的表格区域。
col_index_num：在 table_array 中从左到右的列数，返回该列中的数值。
[range_lookup]（可选）：一个逻辑值，用于指定希望 VLOOKUP 是精确匹配还是近似匹配。通常使用 FALSE 进行精确匹配。

=VLOOKUP("Apple", A2:B10, 2, FALSE)

这个公式会在 A2:B10 范围内查找“Apple”，并返回同一行的第2列的值。

- countif函数

语法：COUNTIF(range, criteria)
解释：
range：要应用条件的区域。
criteria：定义哪些单元格将被计数的条件。可以是数字、文本、逻辑值等。

=COUNTIF(A1:A10, ">5")

这个公式会计算 A1:A10 范围内大于 5 的单元格数量。

- date函数

语法：DATE(year, month, day)
解释：
year：年份。
month：月份。注意，这里的月份是从1到12的数字。
day：天。注意，这里的天数是从1到31的数字。

=DATE(2024, 9, 29)

这个公式会生成日期2024年9月29日。

- today函数

语法：TODAY()
解释：
这个函数没有参数，直接调用即可返回当前日期。
返回值为系统当前日期。

=TODAY()

这个公式会返回当前系统日期。

5. 介绍完了，上述的函数之后，我们开始非常重要的第三个模块的表格布局。

在这里插入图片描述
这里我还是把表格分为了三个板块。因为以行做切分，这样也方便表格布局。

在这里插入图片描述
其实样式的调整，不是最重要的，重要是，我们要实现考勤表的功能
目前我们需要的功能如下:

出勤记录

员工的出勤记录是考勤表的核心功能。通过在表格中输入员工每天的上班时间和下班时间之一，Excel考勤表可以自动计算员工的工作时长，包括您每天的加班时间。可以根据实际情况需要设置工作时间的计算规则，如是否考虑迟到、早退，。

考勤统计和报表生成

考勤统计是Excel考勤表的重要功能。它可以根据员工的考勤记录，生成其中一个考勤统计报表，如月度出勤统计、考勤异常记录、加班情况分析等。这些报表能够帮助管理员全面了解员工出勤情况，发现问题，优化工作流程，并为薪资计算和绩效评估提供数据支持。

第一个功能的视线

首先我们要完善整个月份的表格，因为每个月份的天数是不一样的，有点可能是28、30、31天，所以，我们要根据不同的月份，展示出不同的天数。
具体我们要使用的到的函数如下
IF函数和 DATE函数

=IF(D7<>"",DATE($C$5,$E$5,D7),"")

我解释一下，上述的这个函数

IF(D7<>"",DATE($C$5,$E$5,D7),"")：这是一个IF函数，用于根据条件执行不同的操作。D7<>""：这是条件部分，检查单元格D7是否不为空。如果D7不为空，则执行IF函数内的TRUE部分；否则执行FALSE部分。DATE($C$5,$E$5,D7)：这是IF函数的TRUE部分，当条件满足时执行。这里使用了DATE函数来创建一个日期，参数分别是年份、月份和日期。年份来自单元格C5，月份来自单元格E5，日期来自单元格D7。
""：这是IF函数的FALSE部分，当条件不满足时执行。在这种情况下，返回一个空字符串。

在这里插入图片描述
根据上述的图片展示。我举一个例子
假设我们在单元格C5输入了年份2024，在单元格E5输入了月份11，在单元格D7输入了日期1。那么公式=IF(D7<>“”,DATE($C $5,$ E$5,D7),“”)将返回日期2024年8月15日。如果D7为空，公式将返回空字符串。

另外当你输入这个公式的时候，发现出现的是数字，这个时候千万不要慌张，因为这个时候，我们要设置单元格格式。具体的操作如下：
在这里插入图片描述
目前设置好以后，就是出现的这个界面。

在这里插入图片描述
解决了日期显示的问题，我们接下来思考一个月份的问题，相信你们已经看到了，我现在的月份天数是30天，不可能每个月都只有30天。
目前excel使用的策略是

我们先获取具体的月份

DATE($D$6,$G$6,1)

再根据具体的月份获取指定月份的最后一天的日期

 EOMONTH(DATE($D$6,$G$6,1),0)

从最后一天的日期去提取具体的天数

DAY(EOMONTH(DATE($D$6,$G$6,1),0))

这里就要体现一定的逻辑了。如果说最后一天提取日期大于前一个单元格的日期，后面的日期就+1，如果小于前一个单元格的日期就不显示，隐藏

=IF(AF9<DAY(EOMONTH(DATE($D$6,$G$6,1),0)),AF9+1,"")

总的来说，这个公式的主要目的是检查 AG9 中的数值是否小于某个特定月份的最后一天（由 D6 和 G6 决定），如果是，则将 AG9 加1，否则返回空字符串。

最终的实现效果如下
在这里插入图片描述

解决了日期显示的问题，我们就应该实现表内的勾选功能

我先一个一个看。
年份的设置
在这里插入图片描述

在这里插入图片描述

月份的设置
在这里插入图片描述
具体员工信息的考勤情况设置

在这里插入图片描述
设置好这之后，我们的下拉框的选项就已经全部设置完成了，接下来我们下拉到具体的选项，我们一般来说要有不同颜色的体现。
具体的步骤如下：

设置条件格式
选定同样的单元格范围：再次选择 A1:A10。
打开条件格式对话框：点击“开始”选项卡中的“条件格式”，然后选择“新建规则”。
选择规则类型：选择“使用公式确定要设置的单元格格式”。
输入公式：根据不同选项设置不同的公式。
选项1：=A1=“选项1”
选项2：=A1=“选项2”
选项3：=A1=“选项3”
设置格式：点击“格式”，选择填充颜色，例如红色、绿色和蓝色，分别对应选项1、选项2和选项3。
重复上述步骤：为每个选项分别添加一个条件格式规则并设置不同的颜色。

按照上面的操作之后，你下拉选项，就能出现你想要的颜色。

做好这些之后，我们的最后一步去做统计每个人的出勤时间
这里就是一个简单的函数计算

根据这四种情况，去做判断。
=COUNTIF(D10:AH10,“✔”)
=COUNTIF(E10:AI10,“×”)
=COUNTIF(F10:AJ10,“休”)
=COUNTIF(G10:AK10,“迟”)

我们之所以用这个函数，用于统计一个范围内满足特定条件的单元格数量。
在这里插入图片描述

第二个功能的实现

在这里插入图片描述
目前来说，我们需要在这里输入姓名，就能出现对应的一个人的出勤情况。
这里我们要使用的是vlookup函数去做查询。
回顾一下，VlookUp函数的语法
VLOOKUP（垂直查找）函数是Excel中常用的一个查找和引用函数，用于在一个表格的列中查找特定值，并返回相同行中另一列的值。
目前就是我们根据员工姓名作为一个中间值，去匹配另一个表格的值
具体的公式编写，如下