第三章 值班安排与出勤统计

第三章值班安排与出勤统计重要知识点：1、编辑和设置拼音的内容2、SUMPRODUCT()函数的使用3、COUNTIF（）函数的使用4、TIMEVALUE()函数的使用5、创建和设置数据透视表Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表1、制作基本数据表Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表2、编辑拼音单击“字体”选项组中“显示或隐藏拼音字段”的下拉按钮，在展开的列表中单击“编辑拼音”选项Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表3、插入符号在拼音编辑框中输入拼音，由于不能输入带声调的字母，因此要设置插入符号在插入选项卡下单击“特殊符号”选项组“符号”按钮，在展开的列表中单击“更多”选项Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表4、计算迟到次数【条件】迟到：9:00--9:30TIMEVALUE返回由文本字符串所代表的时间的小数值TIMEVALUE(time_text)Time_text文本字符串，代表以MicrosoftExcel时间格式表示的时间例如本例D3单元格是否大于9:00的时间可以如下表示=D3TIMEVALUE(9:00)D3单元格是否小于9:30的时间可以如下表示=D3TIMEVALUE(9:30)Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表SUMPRODUCT（）在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT(array1,array2,array3,...)数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。一、基本用法例如：=SUMPRODUCT(A1:B3,C1:D3)【3*2+4*7+8*6+6*7+1*5+9*3=156】Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表SUMPRODUCT（）二、用于多条件计数用数学函数SUMOPRODUCT计算符合2个及以上条件的数据个数统计上面数据表中男、女性中分别是中一、中二的人数。=SUMPRODUCT(($B$2:$B$11=$E2)*1,($C$2:$C$11=F$1)*1)Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表公式分解及分析①条件1——$B$2:$B$11=$E2在计算过程中，条件1是一个数组，返回多值，写成公式如下：=$B$2:$B$11=$E2具体操作：选中10个连续的单元格，输入上述公式后，按Ctrl+Shift+回车键确认，返回10个逻辑判断值——TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE。这10个逻辑判断值构成一个新的由TRUE主FALSE组成的数组1。②条件2——$C$2:$C$11=F$1与条件1相同，是一个数组，返回多值，写成公式如下：=$C$2:$C$11=F$1具体操作：同样的方法，选中对应的10连续单元格，输入上述公式，按Ctrl+Shift+回车键确认，返回10个逻辑判断值——TRUE；FALSE；TRUE；TRUE；TRUE；FALSE；FALSE；TRUE；TRUE；TRUE。这10个逻辑判断值构成另一个新的由TRUE主FALSE组成的数组2。=SUMPRODUCT({TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE}*1，{TRUE；FALSE；TRUE；TRUE；TRUE；FALSE；FALSE；TRUE；TRUE；TRUE}*1)=SUMPRODUCT({1;0;0;1;0;1;0;1;1;0},{1;0;1;1;1;0;0;1;1;1})=SUMPRODUCT({1*1;0*0;0*1;1*1;0*1;1*0;0*0;1*1;1*1,0*1})=SUMPRODUCT({1;0;0;1;0;0;0;1;1,0})=4注意：TRUE*1=1，FALSE*1=1*FALSE=0，TRUE*0=0*TRUE=0。数组中用分号分隔，表示数组是一列数组，分号相当于换行。两个数组相乘是同一行的对应两个数相乘。Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表5、计算迟到次数在Z3单元格中输入=SUMPRODUCT((D3:Y3TIMEVALUE(9:00))*1,(D3:Y3TIMEVALUE(9:30))*1)然后向下填充即可Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表5、计算早退次数在AA3单元格中输入=SUMPRODUCT((D4:Y4TIMEVALUE(17:00))*1,(D4:Y4TIMEVALUE(18:00))*1)然后向下填充即可Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表6、计算旷工次数=SUMPRODUCT((D5:Y5TIMEVALUE(9:30))*1,(D5:Y51)*1)+SUMPRODUCT((D5:Y5=TIMEVALUE(9:30))*1,(D6:Y6TIMEVALUE(17:00))*1,(D6:Y60)*1)Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表7、计算事假次数COUNTIF计算区域中满足给定条件的单元格的个数COUNTIF(range,criteria)range是一个或多个要计数的单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。Criteria为计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为32、32、32、apples或B4。=COUNTIF(D3:Y4,事假)Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表8、计算病假次数=COUNTIF(D3:Y4,病假)Excel高级处理及应用技巧李晨曦制作员工出勤统计表1、粘贴数据Excel高级处理及应用技巧李晨曦制作员工出勤统计表2、利用VLOOUKUP函数自动计算出迟到、早退、旷工、病假、事假的次数Excel高级处理及应用技巧李晨曦制作员工出勤统计表Excel高级处理及应用技巧李晨曦制作员工出勤统计表Excel高级处理及应用技巧李晨曦制作员工出勤统计表Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表3、选中单元格区域H3：K14，在“开始”选项卡下单击“数字”选项组中“会计数字格式”的下拉按钮，在展开的列表中单击“中文（中国）”选项Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表4、计算规则1、迟到一次或早退扣除1%的工资2、矿工一次扣除5%的工资3、病假一次扣除当天20%的工资4、事假一次扣除当天全部工资5、全勤奖为当月工资的20%Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表5、计算应扣工资Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表6、计算全勤奖Excel高级处理及应用技巧李晨曦制作员工出勤情况登记表7、计算实际工资Excel高级处理及应用技巧李晨曦创建数据透视表1、创建数据透视表选中单元格区域A2：A14，单击“插入”选项卡下“表”选项组中的“数据透视表”按钮，在展开的列表中单击“数据透视表”选项Excel高级处理及应用技巧李晨曦创建数据透视表2、设置创建选项Excel高级处理及应用技巧李晨曦创建数据透视表3、生成数据透视表Excel高级处理及应用技巧李晨曦创建数据透视表4、添加字段Excel高级处理及应用技巧李晨曦创建数据透视表5、筛选数据Excel高级处理及应用技巧李晨曦创建数据透视表6、设置数据透视表样式在“数据透视表样式选项”选项组中勾选“镶边行”复选框，设置透视表样式效果Excel高级处理及应用技巧李晨曦本章到此结束，谢谢聆听！