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

预备知识

数据有效性

我们在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. 介绍完了，上述的函数之后，我们开始非常重要的第三个模块的表格布局。

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

其实样式的调整，不是最重要的，重要是，我们要实现考勤表的功能
目前我们需要的功能如下:

1. 出勤记录

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

2. 考勤统计和报表生成

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

第一个功能的视线

1. 首先我们要完善整个月份的表格，因为每个月份的天数是不一样的，有点可能是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，否则返回空字符串。

最终的实现效果如下

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

我先一个一个看。
年份的设置

月份的设置

具体员工信息的考勤情况设置

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

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

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

3. 做好这些之后，我们的最后一步去做统计每个人的出勤时间
   这里就是一个简单的函数计算
   
   根据这四种情况，去做判断。
   =COUNTIF(D10:AH10,“✔”)
   =COUNTIF(E10:AI10,“×”)
   =COUNTIF(F10:AJ10,“休”)
   =COUNTIF(G10:AK10,“迟”)

我们之所以用这个函数，用于统计一个范围内满足特定条件的单元格数量。

第二个功能的实现

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

=VLOOKUP($AF$3,$C$10:$AL$31,33,0)

第一个参数代表要查找的值
第二个参数代表要查找的范围
第三个参数代表在查找范围内，要匹配的值
第四个参数代表 0 代表精准匹配 1代表模糊匹配。

最后我们的考勤表想要的功能就已经整合完毕了。