1、第5章 制作员工出勤统计表,利用数据有效性定义单元格 数据透视表 数据透视图 函数的应用,知识点,主要函数,IF AND MOD INT YEAR TODAY,设计思路,5.2 创建员工出勤统计表,员工请假规范 建立基本表格 *利用数据有效性输入“请假类别”数据/有效性/设置/允许/序列/来源 *利用INT和MOD函数计算“请假天数” *利用IF函数计算“应扣工资”,INT函数,格式： INT(number) 对number取整 例： INT(3.9)=3,MOD函数,格式： MOD(number,divisor)例： MOD(8,3)=2,IF函数,IF(logical_test,value
2、_if_true,value_if_false)IF函数可以嵌套使用,数据透视表,数据透视图,两种方法： 向导 在数据透视表的基础上生成,YEAR函数,格式： YEAR(serial_number) 返回某个日期对应的年份 serial_number是一个日期值，应使用DATE函数来输入日期值。,TODAY函数,格式： TODAY() 返回当前日期的序列号 如果单元格设置为“常规”，则返回日期格式。,AND函数,格式： AND(logical1,logical2,)例： =IF(I3=“病假“,K3*(C3/30)*0.1,IF(AND(I3=“事假“,J3=“否“),K3*(C3/30),0
3、),员工请假规范 病假：员工因病请假需要出示有关医疗部门的证明，病假期间单位支付员工工资的90%； 事假：员工因为私人事务而请假，请假期间单位不支付工资； 婚假：限期3天，单位支付全额工资； 产假：女员工产假至少为90天，产假期间单位支付全额工资； 公休假：国家规定的法定假期，单位支付全额工资。请假的单位时间为“小时”。如果请假的时间少于正常的工作 时间（正常工作时间一天为8个小时），则每请假一个小时算是 请假0.1天，如果请假的时间等于8小时则按请假1天计算。例如有一个员工在本月内请了10个小时的病假，则该员工的 请假时间是1.2天。对于员工迟到或者早退的规定是这样的：如果员工迟到或早 退1
4、次，则按请事假0.1天算，依此类推。,建立基本表格员工出勤统计表应包括员工的请假日期、编号、姓名、所在部门、请假类别、请假时间、请假天数等方面的内容。 （1）新建一个工作簿并命名为“出勤表”，将工作表Sheet1更名为“员工出勤统计表”并在其中输入数据。,（2）继续输入员工编号、员工姓名、员工工资、所在部门、迟到或早退（次）这些字段的值。,（3）利用设置数据有效性输入“请假类别”字段的值。由于员工的请假种类只有上述5种，因此，为了避免每一次重复输入数据，可以利用数据有效性来定义单元格序列，这样就可以在下拉列表中选择要输入的数据。 具体的操作步骤如下： 选择单元格区域“G3：G22”，单击【数据
5、】/【有效性】菜 单项，打开“数据有效性”对话框，切换到“设置”选项卡中.在“允许”下拉列表中选择“序列”选项，在“来源”文本框中输入“病假,事假,婚假,产假,公休假,未请假”。 注意：这里各选项之间的分隔符是英文半角的逗号。,切换到“输入信息”选项卡中，然后在“输入信息”文本框中输入“请您输入请假类别”;切换到“出错警告”选项卡中，然后在“样式”下拉列表中选择“警告”选项，在“标题”文本框中输入“输入错误”，在“错误信息”文本框中输入“请单击下拉列表进行选择”。,（4）利用函数计算并填充“请假天数”字段的值。由于在计算员工因请假而扣除的工资时，需要按天数来计算 员工的请假时间，而在上述工作表
6、中的请假时间是以小时计算 的，因此在这里就需要根据员工的请假规范来计算出员工的请假天数。这里需要用到INT和MOD两个函数。下面就来介绍一下它们的用法。 INT函数的作用是对小数进行向下取整。 INT函数的语法结构如下： INT(number) 其中，参数number为需要进行向下取整的实数。 例如，当number为2.8时，INT(number)的值是2。MOD函数的作用是返回两个数相除的余数，其结果的正负号与除数相同。MOD函数的语法如下： MOD(number,divisor) 其中，参数number为被除数，divisor为除数。,计算员工请假天数的具体步骤如下： 选中单元格I3，在其
7、中输入公式：=E3*0.1+INT(H3/8)+MOD(H3,8)*0.1 输入完毕按下【Enter】键，在单元格I3中显示的计算结果 则为“0.4”，,（4）计算应扣工资在这里要有用到IF函数。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函数可以嵌套使用。假设员工每天的工资是
8、每月的工资除以30，那么，利用IF函数计算应扣工资的具体步骤如下： 选中单元格J3，并在其中输入如下公式： =IF(G3=“病假”,C3/30*I3*0.1,IF(G3=“事假”,C3/30*I3,IF(G3 =“未请假“,C3/30*I3,0) 输入完毕按下Enter键，在单元格J3中就会显示出计算结果,（5）利用公式计算“实发额”字段的值在K3单元格中输入公式“=C3-J3”，按下【Enter】键即可计算出实发额，利用自动填充功能自动计算该列其他单元格的值。 （6）对上述表格进行格式化。,建立数据透视表 创建数据透视表的具体步骤如下： 选择单元格区域“A3：K22”，然后单击【数据】/【数
9、据透视表和数据透视图】菜单项，随即会打开“数据透视表和数据透视图向导-3步骤之1”对话框，,保持默认选项不变，即在“请指定待分析数据的数据源类型”组 合框中选中“Microsoft Office Excel数据列表或数据库”单选按钮，在“所需创建的报表类型”组合框中选中“数据透视表”单选按钮。 单击“下一步”按钮，打开“数据透视表和数据透视图向导-3步骤之2”对话框，此时在“选定区域”文本框中即显示了要创建数据透视表的数据源区域 单击“下一步”按钮，打开“数据透视表和数据透视图向导-3步骤之3”对话框，然后在“数据透视表显示位置”组合框中选中 “新建工作表”单选按钮,然后单击“完成”按钮即 可
10、，此时创建的数据透视表的版式.同时系统会自动 地打开“数据透视表”工具栏和“数据透视表字段列表”对话框。 在数据透视表中设置列字 段、行字段、页字段以及 数据字段。使用鼠标将“员工姓名”和“请假日期”从“数据透视表字段列表”对话框中拖到数据透视表中的“将行字段拖至此处”区域，将“请假类别”拖到“将列字段拖至此处”区域，将“请假时间”拖到“请将数据项拖至此处”区域,编辑数据透视表 1修改数据透视表的布局创建了数据透视表之后，如果对数据透视表的布局不满意则可对其进行修改。修改数据透视表的布局可以通过【数据透视表向导】来进行。 修改数据透视表布局的具体步骤如下： 选中数据透视表中的任意一个单元格。
11、单击【数据透视表】工具栏中的“数据透视表”按钮，然后从打开的下拉列表中选择“数据透视表向导”菜单项，随即会打开 “数据透视表和数据透视图向导3步骤之3”对话框。 单“布局”按钮打开“数据透视表和数据透视图向导一一布局”对话框，在该对话框中可以在“页”、“行”、“列”和“数据”区域中重新设置数据透视表的布局。 设置完毕单击“确定”按钮返回“数据透视表和数据透视图向导3步骤之3”对话框中，然后单击“完成”按钮即可。,2修改数据透视表的数据源如果要修改数据透视表的数据源，则可按照上述方法打开“数据透视表和数据透视图向导3步骤之3”对话框，然后单击“上一步”按钮打开“数据透视表和数据透视图向导3步骤之
12、2”对话框，在该对话框中的“选定区域”文本框中即可输入新的数据源区域。设置完成后单击“下一步”按钮返回“数据透视表和数据透视图向导3步骤之3”对话框，然后单击“完成”按钮即可完成对数据源的修改。 3设置数据透视表的格式使用“自动套用格式”对话框可以快速地设置数据透视表的格式。具体的操作步骤如下： 选中数据透视表中的任意一个单元格。 选择【格式】/【自动套用格式】菜单项，打开“自动套用格式” 对话框。 在“自动套用格式”对话框中选择所需的格式。,4设置数据透视表选项对数据透视表选项进行设置能够决定透视表的外观和工作方式。用鼠标右键单击数据透视表的任意一个单元格，然后在弹出的快捷菜单中选择“表格选
13、项”菜单项打开“数据透视表选项”对话框.,5删除数据透视表删除数据透视表的具体步骤如下： 在数据透视表中选择任意一个单元格。 单击【数据透视表】工具栏中的“数据透视表”按钮，从打开的下拉列表中选择【选定】/【整张表格】菜单项。 单击【编辑】/【清除】/【全部】菜单项即可将数据透视表删除。,实例拓展不同企业制定的请假规定是不一样的。现有一家单位在上述请假规定的基础之上又有如下的规定，即增加年假一项。年假是根据员工的工龄计算年假应有的天数，年假期间单位支付全额工资。有年假的员工在请事假的时候可以选择是否使用年假，如果使用，则单位支付事假期间的全额工资，同时在本年的年假中扣除请事假所耗费的时间。,计
14、算员工工龄员工的工龄等于当前的时间减去员工加入公司的时间，并向下取整。在计算员工工龄之前我们先来了解一下函数YEAR和函数TODAY的用法。 YEAR函数的作用是返回某个日期对应的年份，返回值为1900到9999之间的整数。其语法结构如下： YEAR(serial_number） 参数serial_number为一个日期值，其中包含要查找年份的日期。应当使用DATE函数来输入日期，或者将日期作为其他公式或函数的结果输入，例如使用DATE(2006,12,25)输入2006年12月25日。如果日期以文本的形式输入则会出现错误。 TODAY函数的作用是返回当前日期的序列号。如果在输入函数之前单元格
15、的格式为“常规”，那么结果将设为日期格式。TODAY函数的语法结构为：TODAY(),计算员工工龄的具体步骤如下： 单击单元格F3，然后在其中输入公式：=YEAR(TODAY()-E3)-1900计算员工年假公司是按照阶段来划分员工的年假时间的。现假设工龄小于1年不能享有年假，工龄小于3年者年假为10天，工龄3年以上的员工工龄每增加一年年假就增加1天 . 计算员工应有年假的具体步骤如下： 单击单元格G3，然后在其中输入公式：=IF(F3=1,IF(F33,10,10+(F3-2),0) 输入完毕按下“回车”键，在单元格G3就会显示出计算结果。,计算应扣工资 在计算员工应扣工资之前，先利用前面讲
16、过的方法在“是否使用年假”一列中设置数据的有效性。单击单元格J3右侧的下箭头按钮，打开其下拉列表，然后在下拉列表中可以选择是否使用年假。 这里要用到AND函数。AND函数的语法结构如下： AND(logical1,logical2,) 参数“logical1,logical2,”表示待检测的条件值，最多可有30个条件，各个条件值可以为TRUE或者FALSE。 计算应扣工资的具体步骤如下： 单击单元格L3，然后在其中输入公式：=IF(I3=“病假“,K3*(C3/30)*0.1,IF(AND(I3=“事假“,J3=“否“),K3*(C3/30),0) 输入完毕按下“回车”键，在单元格L3中就会显示出计算结果。,
