Excel学习05

员工排班表

通过之前的学习，目前的我们已经具备了Excel的初步制作能力。接下来将从实际出发制作一个员工排班表。在制作排版表之前，先来看我们要用到的函数。

DATE函数

date函数是Excel中处理日期的核心函数之一，它能够将单独的年、月、日数值组合成有效的日期序列值，是日期计算和日期构造的基础工具。

语法

=DATE(year, month, day)

参数	说明
year	年份（1900-9999），支持2位数简写（00-29解释为2000-2029，30-99解释为1930-1999）
month	月份（支持超出1-12范围的值，会自动跨年计算）
day	天数（支持超出当月天数的值，会自动跨月计算）

TEXT函数

 TEXT函数是Excel中强大的文本格式化工具，它能够将数值、日期等数据转换为特定格式的文本，实现数据显示与存储的灵活控制。

语法

=TEXT(value, format_text)

参数	说明
value	要转换的值（数字、日期、时间或可转换为数值的文本）
format_text	格式代码（用双引号括起，定义输出文本的显示格式）

 而关于格式代码常用的主要有以下分类：

一、数字格式代码

代码	说明	示例(值:1234.5)	输出结果
0	强制显示数字位	"00000"	01235
#	可选数字位	"#####"	1235
.	小数点位置	"0.000"	1234.500
,	千位分隔符	"#,###"	1,235
%	百分比显示	"0%"	123450%

二、日期格式代码

1. 年/月/日代码

代码	说明	示例(2023-08-20)	输出结果
yyyy	四位年份	"yyyy"	2023
yy	两位年份	"yy"	23
mmmm	月份全名	"mmmm"	August
mmm	月份缩写	"mmm"	Aug
mm	两位月份	"mm"	08
m	无前导零月份	"m"	8
dddd	星期全名	"dddd"	Sunday
ddd	星期缩写	"ddd"	Sun
dd	两位日期	"dd"	20
d	无前导零日期	"d"	20

2.中文日期格式

代码	输出示例
"yyyy年mm月dd日"	2023年08月20日
"aaa"	日
"aaaa"	星期日
"dbnum1"	二〇二三
"dbnum2"	贰零贰叁

三、时间格式代码

代码	说明	示例(14:30:45)	输出结果
hh	两位小时(24h)	"hh"	14
h	无前导零小时	"h"	14
mm	两位分钟	"mm"	30
m	无前导零分钟	"m"	30
ss	两位秒数	"ss"	45
s	无前导零秒数	"s"	45
AM/PM	12小时制时段	"h:mm AM/PM"	2:30 PM

 ROW函数

ROW函数是Excel中用户获取行号信息的核心函数之一。它在动态引用、数组公式和复杂计算中发挥着重要作用。

语法

=ROW([reference])

参数	是否必需	说明
[reference]	否	要获取行号的单元格或区域引用，若省略则返回公式所在单元格的行号

 ISSOD函数

ISSOD函数是Excel中的一个信息函数，用于判断数值是否为奇数。

语法：

=ISODD(number)

参数	是否必需	说明
number	是	要检测的数值（可以是直接数值、单元格引用或计算结果）

WEEKDAY函数

WEEKDAY函数是Excel中处理日期分析的重要工具，它能够返回某个日期对应的星期几信息，在日程安排、周报统计等场景中非常实用。

语法

=WEEKDAY(serial_number, [return_type])

参数	是否必需	说明
serial_number	是	要分析的日期（可以是日期序列值、日期文本或DATE函数结果）
[return_type]	否	确定返回值类型的数字（默认为1，详细类型见下文）

return_type参数详解

return_type	返回值范围	对应关系	适用地区
1 或省略	1-7	1=周日，2=周一，...7=周六	美国标准
2	1-7	1=周一，2=周二，...7=周日	国际标准(ISO 8601)
3	0-6	0=周一，1=周二，...6=周日	欧洲部分地区
11	1-7	1=周一，2=周二，...7=周日	Excel 2010+国际标准
12	1-7	1=周二，2=周三，...7=周一	特殊财务系统
13	1-7	1=周三，2=周四，...7=周二	特殊需求
14	1-7	1=周四，2=周五，...7=周三	中东地区(周四为周末首日)
15	1-7	1=周五，2=周六，...7=周四	穆斯林国家
16	1-7	1=周六，2=周日，...7=周五	以色列等
17	1-7	1=周日，2=周一，...7=周六	埃及等

EMONTH函数

EMONTH函数用于返回指定日期之前或之后某个月份的最后一天的日期，名称中的“EO”代表“End Of”（月末）。这个函数在财务计算、租赁合同、工资结算等场景中特别有用。

语法

EOMONTH(start_date, months)

参数	说明
start_date	基准日期，Excel可识别的任何日期格式
months	正数：返回未来某月的最后一天 负数：返回过去某月的最后一天 0：返回当月的最后一天

最后制成图如下。

表格的下拉框可以在数据选项栏的下拉列表中实现。

特殊的表格颜色设置依靠的是条件格式，新建规则实现。

出勤日期的计算主要用到了NETWORKDAYS函数的使用。

=NETWORKDAYS($E$11,EOMONTH($E$11,0),节假日!$B$2:$B$4)-SUM(AJ12:AL12)

 这个表格最主要的一点是依靠首行的日期公式，其他的日期都依靠这个日期来进行计算。

 上面的星期显示可以通过TEXT函数实现。

 员工标题则可以通过CONCAT函数来进行拼接，这样的好处是表格日期只用修改一处，便于调整。

 例如，我们只需要更改表格上方的月份，整个表格就会随之发生变动。