1、办公自动化实用教程（第三版）经济科学出版社任务3.1 体验公式和函数-员工薪酬核算 办公自动化实用教程（第三版）主编：石朝晖 刘文娟学习目标了解工资核算的业务处理流程工作表数据编辑与格式化数据有效性设置VLOOKUP、TEXT、MID、INT、TODAY、MAX、SUM、IF等函数的应用任务引入在日常办公中，经常要制作一些表格，并对其中的数据进行统计、计算、分析，Excel提供了丰富的内置函数以及强大的统计分析功能，足以满足用户的需要。某公司的财务人员每月都要根据员工的考勤情况进行员工薪酬核算，如何快速地实现数据录入与相关的数据处理呢？任务分析在公司的员工薪酬核算系统中，涉及到许多数据信息例如，职工基本信息、考勤记录、加班记录、个税税率等，将这些数据放置在多个独立的工作表中，最终生成员工工资表多个工作表之间存在数据链接关系，方便信息传递。由于表格设计的需求，需要进行如下的编辑、美化工作：工作表的创建和基本操作在单元格中输入各种类型数据设置单元格格式设置表格的边框和底纹应用公式和函数进行计算任务效果图：任务实施1.函数和单元格地址引用2.编制“基本数据”表3.编制“员工信息表”4.编制“

2、1月考勤表”5.制作“1月工资表”一、函数和单元格地址引用1.了解函数Excel 2010提供了许多内置函数，包括财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息、工程、多维数据集、兼容性等12类几百种函数，为用户对数据进行运算和分析带来极大的方便。Excel 2010函数由函数名、括号和参数组成。如“SUM（J3+J14）”。当函数以公式的形式出现时，则应在函数名称前面输入等号“=”，如“=SUM（J3+J14）”。2.单元格的引用类型在Excel中，每一个单元格都有唯一的行坐标和列坐标，我们将单元格的行坐标和列坐标称为“单元格的引用”。在Excel中可将引用单元格分为3类，相对引用、绝对引用和混合引用。单元格的相对引用如J3，绝对引用如$J$3，混合引用如J$3或$J3。一、函数和单元格地址引用相对引用是指公式所在的单元格与公式中引用的单元格之间的位置关系是相对的，如果公式所在的单元格的位置发生了变化，那么公式中引用的单元格的位置也将随之发生变化。如J3单元格有公式：=H3I3，当将公式复制到J4单元格时变为：=H4-I4。绝对引用是指被引用的单元格与公

3、式所在的单元格之间的位置关系是绝对的，即不管将公式粘贴到任何单元格，公式中所引用的还是原来单元格的位置。如J3单元格有公式：=$H$3$I$3，当将公式复制到J4单元格时仍为：=$H$3$I$3，当将公式复制到J14单元格时仍为：=$H$3$I$3。混合引用是介于相对引用和绝对引用之间的一种引用，也就是说饮用单元格的行和列之中一个是相对的，一个是绝对的。混合引用有两种：一种是行绝对、列相对，如J3单元格有公式：=H$3I$3，当将公式复制到J4单元格时仍为：=H$3I$3，另一种是行相对、列绝对，如J3单元格有公式：=$H3$I3，当将公式复制到J4单元格时变为：=$H4$I4。Excel工作表是承载信息数据的主体，这里，我们将需要的不同的数据分别存储在不同的工作表中，以便于数据之间的传递和链接。二、编制“基本数据”表在制作员工工资表时，需要用到很多基础信息例如，员工病事假扣款标准、加班费用标准、通讯费发放标准、个人税率标准等，可以事先建立一个工作表，将上述基础信息放置其中，方便取值、使用。1.新建工作簿运行Microsoft Office Excel 2016程序，创建一个新工作簿，

4、执行“文件”选项卡中的“保存”命令，打开“另存为”对话框，将其保存为“员工工资表”。右单击其中的sheet1工作表标签，在弹出的下拉列表中选择“重命名”命令，将其命名为“基础数据”。然后分别构建不同内容的数据。2.定义名称为了后边的其他表格取数方便，可以将“基础数据”工作表中的部分表格内容定义名称。步骤一 选择A3:D8单元格区域，单击“公式”功能区“定义的名称”组中的“根据所选内容创建”命令，弹出如图3.3所示对话框，勾选“首行”复选框，单击“确定”按钮完成设置。按照上述操作方法，如表3.1所示为其他表格内容定义名称。单元格区域名称A3:A8最高学历B3:B8职称C3:C8部门D3:D8职务V3:V10税率W3:W10速算扣除数2.定义名称步骤二 选择F3:H8单元格区域，单击“公式”功能区“定义的名称”组中的“定义名称”命令，弹出“新建名称”对话框，在“名称”框中输入“工资标准”，单击“确定”按钮完成设置。单击“公式”功能区“定义的名称”组中的“名称管理器”命令，可以打开“名称管理器”对话框，查看、编辑和删除定义好的各种名称。3.修改工作表标签颜色右单击“基础数据”工作表标签，从弹

5、出的列表中选择“工作表标签颜色”命令，设置工作表标签颜色为“红色”。4.取消网格线为了更清晰地显示表格中的文本和数字，可以通过取消“视图”功能区中“网格线”复选框的方法来实现，这样整个工作表就会呈现白纸状态。三、编制“员工信息表”员工信息表的结构包括“序号”、“姓名”、“性别”、“最高学历”、“职称”、“部门”、“职务”、“联系号码”、“身份证号码”、“入职时间”、“出生日期”和“工龄”等列字段。在已经建好的“基本数据”工作表右侧单击“新工作表”按钮（“”符号），新增一个空白工作表sheet2，然后将其重命名为“员工信息表”。首先在相应位置输入表头和标题行内容。1.输入文本型数据由于Excel单元格内默认的格式是“常规”，在“序号”列中直接填入“001”、“002”、样式编号的时候，Excel会将其处理成为“1”、“2”、数字格式，因此需要事先将该列设置为“文本”格式然后再输入编号。（1）序号选择A列，依次单击“开始”功能区“数字”组中的“数字格式”选项右侧的下三角按钮，从弹出的列表中选择“文本”选项。然后在A4单元格中输入“001”，选择A4单元格，拖动其填充句柄向下复制该序号直至出

6、现“015”为止（即A18单元格），完成“序号”列的填充。1.输入文本型数据（2）身份证号码同样，当向Excel单元格内输入的数字长度超过11位时就不能正常显示显示为科学计数法；而身份证的长度为18位，结果为后三位显示为“000”从而无法从中正确提取信息。因此，在填充“身份证号码”列时，也必须事先将该列（即：I列）设置为“文本”格式，然后再手工依次输入每位员工的身份证号码。另外，像“邮政编码”、“联系电话”等数字信息通常不需要参加公式计算，也可以先将该列设置为“文本”格式，然后再行输入。而像“姓名”列这样的信息，属于纯文本数值，没有规律，需要手动逐一输入。2.数据验证在Excel表格的某些数据列（例如：员工的“性别”）中，是由一系列固定内容构成的（例如：“男”或“女”），且不允许出现超出指定内容范围之外的其他数值输入，因此可以借助于“数据验证”功能来完成输入工作。（1）性别选择C列（即：“性别”列），依次单击“数据”功能区“数据工具”组中的“数据验证”命令，弹出如图3.8所示“数据验证”对话框。在“允许”框内选择“序列”，在“来源”框中输入“男,女”（注意：分隔符使用英文逗号或分号）。

7、单击“确定”按钮完成设置，单击C4单元格就可以看到其右侧出现了一个下三角按钮，从下拉列表中列出了“男”和“女”两个选项，按照实际情况，选择“男”输入到C4单元格中，其他“性别”列数据按照相同的方法依次输入。（2）最高学历在“基础数据”表中，事先已经构建了有关“最高学历”的全部种类名称，可以在上述对话框中的“来源”框中直接引用这些内容。选择D列（即：“最高学历”列），打开上述“数据验证”对话框，在“来源”框中引用“基础数据”中的相应单元格地址。单击“确定”按钮完成设置。单击D4单元格右侧的下三角按钮，从其下拉列表中即可看到数据验证的全部设置内容。按照实际情况，选择“本科”输入到D4单元格中。2.数据验证（3）其他数据验证按照上述方法，分别为“职称”、“部门”、“职务”、“联系号码”和“身份证号码”列设置相应的数据验证，并完成相应的数据填充。如表3.2所示。列标签列允许值数据来源/数据关系性别C序列男,女手动输入最高学历D序列研究生,本科,专科=最高学历职称E序列高级,中级,初级,无=职称部门F序列研发部,销售部,行政部,财务部,生产车间=部门职务G序列总经理,部门经理,管理人员,销售人员

8、,基本生产人员=职务联系号码H文本长度 11等于身份证号码I文本长度 18等于3.输入日期型数据自定义“入职日期”格式在Excel中，日期型数值有两种合法格式：例如“2010/4/20”和“2020-4-20”（分别使用斜杠“/”或短横“-”分隔年月日），在“员工信息表”中的“入职日期”列，我们使用自定义格式规范它的显示方式。右单击J列，选择下拉列表中的“设置单元格格式”命令，打开“设置单元格格式”对话框，切换至“数字”选项卡，选择“自定义”选项，然后在右侧的“类型”框中输入“yyyy-mm-dd”数字格式（使其整齐美观）。4.根据身份证号码获取信息（1）出生日期在K列中，需要通过员工身份证号码计算出每位员工的“出生日期”，在此可以利用文本类函数MID和TEXT来实现。在K4单元格中构建如下函数公式：=TEXT(MID(I4,7,8),0000-00-00)按下Enter键，得到计算结果：“1982-13-12”。向下复制公式直至该列最后一行的单元格内。【函数解读】：MID（文本类）函数：在一个文本字符串的指定起始位置开始截取指定长度的字符串。=MID(I4,7,8)的语法格式为：将I

9、4单元格（身份证号码）中的文本字符串自第7位开始，截取8位文本长度，得到文本字符串“19820312”。TEXT（文本类）函数：将数值转换为按照指定格式表示的文本。=TEXT(MID(I4,7,8),0000-00-00)的语法格式为：将I4单元格中的身份证号码利用MID函数截取得到出生日期数值“19820312”，然后利用TEXT函数，将其按照指定日期格式“0000-00-00”转换为真正的日期格式。（2）工龄员工的工龄要求按照周岁计算从“入职日期”到当前日期的年份数，在此可以利用INT函数和TODAY函数来实现。在L4单元格内构建如下函数公式：=INT(TODAY()-J4)/365)按下Enter键，得到计算结果（“5”）。利用填充句柄向下复制公式直至最后一位员工，得到“工龄”列的计算结果。【函数解读】：Today()函数：返回当前日期；(Today()-J4)/365公式：当前日期与入职时间的差值（天数），除以365，得到两者之间的年数差；INT(TODAY()-J4)/365)公式：INT函数的语法格式为“向下取整”，利用它将上述年数差的小数部分舍去，得到整数年数即“工龄”（

10、满整年记为1年）。5.工作表的格式化美化“员工信息表”分别为表头、列标签和数据区域设置适当的字体格式、对齐方式、边框和底纹，调整行高列宽，使其美观大方。（1）设置表头选择A1：L1单元格区域，设置字体格式为“微软雅黑 14磅”，打开“设置单元格格式”对话框，在“对齐”选项卡中设置“跨列居中”方式。（2）调整行高列宽根据每列实际内容和表格整体美观需要，分别为各列设置合适的列宽；适当调整表头行的行高。（3）设置对齐方式员工信息表中的数据列均可以采用“居中”对齐方式显示。（4）添加边框和底纹选择表格数据区域A3:L18，在“开始”功能区“字体”组中单击“边框”命令右侧的下三角按钮，从弹出的列表中选择“所有边框”；选择标题行A3:L3单元格区域，单击“开始”功能区“字体”组中的“填充颜色”命令右侧的下三角按钮，从弹出的列表中选择一种合适的底纹颜色。（5）取消网格线在“视图”功能区“显示”组中取消“网格线”复选框勾选，使得员工信息表中的内容更醒目、突出。5.工作表的格式化美化“员工信息表”6.打印设置在办公中，常常需要将制作好的表格打印在A4纸张上，便于查看。在此，要求设置“员工信息表”最终打印

《任务3.1 体验公式和函数-员工薪酬核算.pptx》由会员m****u分享，可在线阅读，更多相关《任务3.1 体验公式和函数-员工薪酬核算.pptx》请在八斗文库上搜索。