第8章(excel在人力资源管理中的应用).ppt

1、第8章 经典实例：管理员工休假,8.1要点分析 8.2制作员工请假登记表 设置员工请假登记表的格式 利用函数进行请假统计与分析 利用数据透视表进行请假统计与分析, 8.3计算加班时间 8.4休假管理 计算员工工龄 统计一年中的节假日 根据社会工龄计算年休假天数 根据员工请假条的起止日期确定休假天数, 8.1 要点分析,本章主要介绍如何使用Excel制作管理员工休假，在制作过程中会用到DAYS360、WEEKDAY等函数来计算加班时间、工作日、双休日等信息。为了便于读者使用函数，下面先来了解每个函数的含义及使用方法。,1DAYS360函数,DAY360函数是按照一年360天的算法，返回两个日期相

2、差的天数。其语法格式为： DAYS360(start_date,end_date,method) 其中各参数的含义如下。 start_date (起始日期) 和 end_date (结束日期)：用于计算期间天数的起止日期。 method：一个逻辑值，指示计算时应该使用美国还是欧洲方法。默认为FALSE。 Method定义：如果 method 为 FALSE 或被省略，则使用美国 (NASD) 方法；如果起始日期是一个月的 31 日，则等于同月的 30 日；如果终止日期是一个月的 31 日，并且起始日期早于 30 日，则终止日期等于下一个月的 1 日；如果终止日期是一个月的 31 日，并且起始日

3、期是同月的 30 日，则终止日期等于同月的 30 日，结果为零； 如果 method 为 TRUE，则使用欧洲方法。发生在一个月的 31 日的起始日期和终止日期都将等于同月的 30 日。,2WEEKDAY函数,WEEKDAY函数是指返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。其语法格式为 WEEKDAY(serial_number，return_type) 其中各参数的含义如下。 serial_number：指定的日期或序列号。 return_type：为确定返回值类型的数字，返回数字1 或省略，则1 至7 代表星期天到星期六；返回数字2，则1 至7 代

4、表星期一到星期天；返回数字3，则0至6代表星期一到星期天。, 8.2 制作员工请假登记表, 8.2.1 设置员工请假登记表的格式,各种表格都有自己的格式，用来与其他的表格进行区分，员工请假登记表中主要应该有姓名、请假时间、请假的天数和事由。下面将介绍设置员工请假登记表的格式，具体操作步骤如下。 步骤1 新建一个空白工作簿，并保存该工作簿。然后在Sheet1工作表中的A1单元格中输入表格标题，在A2单元格中输入“序号”，在A3单元格中输入序列号“1”，并向下填充序列数据。,步骤2 在B2单元格中输入“姓名”、在C2单元格中输入“请假时间”、在C3单元格中输入“ 月 日”，然后向下自动复制填充“

5、月 日” 。 步骤3 选中C3:C20单元格区域，然后在“开始”选项卡下的“字体”组中，单击“字号”下拉列表按钮，从打开的列表中选择“9”选项，调整字体大小。 步骤4 在D2、E2、F2单元格中分别输入“天数”、“事由”和备注。 步骤5 选中A1:F1单元格区域，在“开始”选项卡下的“对齐方式”组中单击“合并后居中”按钮。 步骤6 按Ctrl+A组合键，将工作表全部选中，右击选中的工作表，在弹出的快捷菜单中选择“设置单元格格式”命令。 步骤7 弹出“设置单元格式”对话框，切换到“对齐”选项卡，设置 “水平对齐”为“居中”、“垂直对齐”为“居中” 。 步骤8 切换到“边框”选项卡，选择线条样式，

6、再单击“外边框”按钮和“内部”按钮，为表格添加边框线，再单击“确定”按钮。 步骤9 在A21单元格中输入备注内容。 步骤10 右击A21单元格，按照上面的方法打开“设置单元格格式”对话框，切换到“对齐”选项卡，设置“水平对齐”为“靠左(缩进)”、“垂直对齐”为“居中”，单击“确定”按钮。至此，员工请假登记表的格式就设置完成了。,添加表格边框线,完成后的员工请假登记表, 8.2.2 利用函数进行请假统计与分析,使用函数可以使数据统计变得简单，工作效率加快，下面就介绍一下如何利用函数进行请假统计与分析。 步骤1 在Sheet2工作表中制作“请假统计情况”表格。 步骤2 在“请假统计情况”工作表中输

7、入相应的数据。,制作“请假统计情况”表格,步骤3 选中单元格D3，在“公式”选项卡中的“函数库”组中单击“日期和时间”按钮，在展开的下拉列表中单击DAYS360选项。 步骤4 弹出“函数参数”对话框，在Start_date文本框中输入“B3”，在End_date文本框中输入“C3”。 步骤5 选中D3单元格，将公式更改为=DAYS360(B3,C3)+1，计算出第一个员工的请假天数。,输入数据,单击DAYS360选项,“函数参数”对话框,步骤6 选中D3单元格，向下拖动填充柄，复制公式一直到D14单元格，计算出其他员工的请假天数。,更改计算公式,复制公式计算各员工请假天数,选择“数据透视表”命

8、令, 8.2.3 制用数据透视表进行请假统计与分析,当数据量非常大时，使用数据透视表可以使工作变得更加简单方便，具体操作步骤如下。 步骤1 在“插入”选项卡下的“表格”组中单击“数据透视表”按钮，在展开的下拉列表中选择“数据透视表”命令。 步骤2 弹出“创建数据透视表”对话框，设置数据源地址和数据透视表的存放位置。,“创建数据透视表”对话框,步骤3 单击“确定”按钮，选中右侧窗格中的“选择要添加到报表的字段”列表框中的所有字段。,步骤4 进行请假统计分析，比如要了解王小的请假情况可以在“行标签”下拉列表中只选中“王小”，然后单击“确定”按钮，就可以看到王小的请假情况。,添加字段到报表,利用数据

9、透视表统计分析王小的请假情况,制作“员工加班登记表”表格, 8.3 计算加班时间,员工加班一般来说是要给加班费的，所以对员工加班时间的计算就显得很重要了。加班时间为结束时间与起始时间的差，因此要计算加班时间时，可使用结束时间减去起始时间。在此可使用Text函数以文本形式显示计算。 步骤1 在Sheet3工作表中制作“员工加班登记表”表格。 步骤2 选中E3单元格，然后在“公式”选项卡下，单击“函数库”组中的“插入函数”按钮，接着在弹出的“插入函数”对话框中搜索TEXT函 数，再单击“确定”按钮。,“插入函数”对话框,“函数参数”对话框,步骤4 弹出“函数参数”对话框，在Value文本框中输入“

10、D3-C3”，在Format_text文本框中输入“h:mm:ss”。 步骤5 单击“确定”按钮，返回工作表，此时计算出第一个员工的加班时间。,计算出第一个员工的加班时间,步骤6 选中E3单元格，向下拖动填充柄，复制公式一直到E18单元格，统计出其他员工的加班小时数。, 8.4.1 计算员工工龄,工龄是指该员工从进入企业工作到当前的年限，而工龄一般跟员工的待 遇、福利是相挂钩的，工龄越长，该员工享受的待遇和福利也相对于其他员工要高。虽然可以通过手工来计算员工的工龄再输入，但是员工的工龄会随着时间的增长而不断增加，手工输入的工龄每年都需要更改。针对这种情况，可以使用TODAY和YEAR函数来自动

11、计算工龄，具体操作如下。 步骤1 新创建“员工信息”工作表，然后在工作表中制作员工信息表格。 步骤2 选定C2单元格，在“公式”选项卡下的“函数库”组中单击“插入函数”按钮。 步骤3 弹出“插入函数”对话框，选择函数类别为“逻辑”，再选择逻辑函数中的IF函数，并单击“确定”按钮。 步骤4 弹出“函数参数”对话框，在Logical_test文本框中输入设置的判断条件，在Value_if_true文本框中输入条件为“真”时返回的值，在Value_if_false文本框中输入条件为“假”时返回的值。 步骤5 单击“确定”按钮，返回工作表，此时可以看到在C2单元格中显示了计算的结果，即第一位员工当前的

12、工龄为18。接着向下填充公式，计算其他员工的工龄。, 8.4 休假管理,创建“员工信息”工作表,计算员工的工龄,创建新的工作表,选择函数,输入数值,选择DATE函数, 8.4.2 统计一年中的节假日,下面以公历为例，介绍制作一份节假日统计表的方法，学习利用函数自动判断当年每个节日的具体日子是哪天，具体操作步骤如下。 步骤1 新插入一张工作表，然后在工作表中设置“节日名称”、“月”、“日”、“日期”和“星期”等列。 步骤2 这里以统计2012年的节假日为例，在要查询的年份里输入“2012”，然后选中单元格D3，并打开“插入函数”对话框，选择函数类别为“日期与时间”，再选择DATE函数，并单击“确

13、定”按钮。,设置DATE函数参数,计算第一个节日的日期,步骤3 弹出“函数参数”对话框，在Year文本框中输入“$B$1”，在Month文本框中输入“B3”，在Day文本框中输入“C3”。 步骤4 单击“确定”按钮，返回工作表，得到指定年份的元旦节的日期。,步骤5 选定单元格E3，然后打开“插入函数”对话框，设置函数类别为 “日期与时间”，并选择WEEKDAY函数，再单击“确定”按钮。 步骤6 弹出“函数参数”对话框，在Serial_number文本框中输入“D3”，再单击“确定”按钮，返回工作表，可以知道指定年份中元旦节是星期几。 步骤7 选中D3单元格，向下拖动填充柄，复制公式一直到D6单

14、元格，得到其他节日的日期。,设置WEEKDAY函数参数,步骤8 选中E3单元格，向下拖动填充柄，复制公式一直到E6单元格，获取其他节日的星期。 步骤9 选中E3:E6单元格区域，然后在“设置单元格格式”对话框中设置数字格式为“日期”，并在“类型”列表框中选中“星期三”选项，再单击“确定”按钮。,获取其他节日的星期, 8.4.3 根据社会工龄计算年休假天数,法律规定员工累计工作已满1年不满10年的，年休假5天；已满10年不满20年的，年休假10天；已满20年的，年休假15天。这里我们按照这个规定来计算出年休假天数，操作步骤如下。,在E2单元格中输入公式,计算其他员工的年休假天数,步骤1 在“员工

15、信息”工作表中选中E2单元格，输入公式“=IF(C21,0,IF(C210,5,IF (C220,10,15)”，计算出第一个员工的年休假天数。 步骤2 选中E2单元格，向下拖动填充柄，复制公式一直到E13单元格，通过复制公式获取其他员工的年休假天数。, 8.4.4 根据工龄和岗位级别计算年休假天数,有些企业还会根据工龄和岗位的级别来计算年休假的天数。我们这里假设市场总监满15年就有10天休假不满则有5天休假；销售经理满20年有休假15 天，不满则有10天休假；采购员满10年有10天休假，不满则有6天休假；市场实习满5年有4天休假，不满则2天休假；管理助理满10年有6天休假，不满则有3天。按照这个条件来计算年休假天数，操作步骤如下。 步骤1 打开“员工信息”工作表，选中F2单元格，输入公式“=IF(AND(D2= “市场总监”,C2=15),10,I