模块4 Excel数据管理与分析2

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,模块4 Excel数据管理与分析,项目二 职员工资表,1,项目要求,外部数据的导入,VLOOKUP(),、,IF(),、,SUM(),、,AVERAGE(),函数的使用,公式的使用,巧用“辅助序列”和“定位”快速制作工资条的方法,2,具体任务,任务一：制作简单的职员工资表,任务二：导入数据并添加列标题,任务三：“,VLOOKUP()”,函数的使用,任务四：“,IF,（）、,YEAR(),和,NOW()”,函数的综合应用,任务五：用公式计算“三金”,任务六：使用“,IF()”,函数计算“个人所得税”,任务七：使用公式计算“应发工资”、“实发工资”,任务八：“,SUM()”,和“,AVERAGE()”,函数的使用,任务九：巧用“辅助序列”和“定位”制作工资条,相关知识,3,任务一：制作简单的职员工资表,使用公式和“SUM()”、“AVERAGE()”、“MAX()”、“MIN()”等函数。,4,启动,Excel,软件，并输入基本内容。,使用公式计算“应发工资”、“公积金”、“扣款合计”和“实发合计”。,使用自动求和功能插入,SUM(),函数计算工资“各项合计”。,使用函数向导功能插入“,AVERAGE()”,函数计算工资各项平均数。,使用直接输入方法插入“,MAX()”,、“,MIN()”,函数计算最高与最低工资。,5,任务二：导入数据并添加列标题,从上一项目的“职工基本情况表”中导入数据到“员工工资表”中，并根据制表需要隐藏或添加行、列数据。,6,新建工作簿，选择“,sheet1”,工作表标签，单击“数据”“导入外部数据”“导入数据”命令，如图,4- 53,所示。,在对话框中选择到“职工基本情况表”工作簿，然后单击“打开”按钮，打开“选择表格”对话框，并在此对话框中选择“职员基本情况表,$”,，如图,4- 54,所示，单击“确定”按扭，打开“导入数据”对话框。,在此对话框中选择数据放置的为新建工作表，如图,4- 55,所示。,单击“确定”按扭。,1.从“职工基本情况表”工作簿中导入数据。,7,8,2.隐藏、添加行或列，如图4- 57所示。,选择“性别”所在列列,“格式”“列”“隐藏”命令，隐藏列。,用相同的方法将“工作部门”、“学历”、“身份证号”、“联系电话”、“,Email,地址”列隐藏。,在表头右边添加如图,4- 57,所示列标题。,将“工龄”列的数字类型设置为“数值”，小数点后保留,0,位；其余列数字格式设置为“数值”类型且小数点后保留,2,位。,提示：将单元格数字类型设置为“数值”方法与设置为“文本”方法相同，,如未设置“工龄”列的数字类型设置为“数值”，则在后面计算“工龄”时显,示结果将出错。,9,将,sheet1,工作表重命名为“员工工资表”。,将此工作簿保存，文件名为“工资表作业”，保存位置与“职工基本情况表”在同一文件夹中。,提示：此时，“工资表”工作簿中的“员工工资表”中的导入数据已经与“职工基本情况表”建立链接关系。将它们保存在同一个文件夹下，可防止文件在复制或移动的操作中丢失数据。,10,任务三：“VLOOKUP()”函数的使用,使用VLOOKUP()函数在“各项工资对照表”中的“基本工资对照表”区域内查找到相应职务的基本工资数据，并将结果放在相应的单元格中。,11,复制“职员工资表（素材）”工作簿中的“各项工资对照表”工作表中“,A1:I8”,单元格的数据复制到“工资表作业”工作簿的,sheet2,工作表中“,A1:I8”,单元格，并将,sheet2,重命名为“各项工资对照表”。,12,定义名为“基本工资对照表”的单元格区域。,选择“工资表作业”工作簿中的“各项工资对照表”中的“,A3:B8”,单元格。,单击“插入”“名称”“定义”命令，打开“定义名称”对话框，然后将文本框中“总经理”删除，并输入如图,4- 59,所示内容。,单击“添加”按钮，即可将新定义名称添加到下面的列表中，如图,4- 60,所示。,13,使用查找函数“,VLOOKUP()”,计算员工“杨林”作为“总经理”职务的“基本工资”，并将结果放在相应的单元格中。,将光标定位于,K3,单元格中，单击“插入”“函数”命令，并在对话框中 选择如图,4- 62,所示类别。,在对话框中选择“,VLOOKUP()”,函数，如图,4- 63,所示。,单击“确定”按扭，并在此对话框中输入如图,4- 64,所示各个参数，最后单击“确定”按扭。,14,任务四：“IF（）、YEAR()和NOW()” 的综合应用,综合应用“IF（）、YEAR()和NOW()”函数的计算工龄工资。工龄工资计算方法如表4- 2所示。,15,使用“,YEAR(),和,NOW()”,函数计算“杨林”的工龄，并将结果放置在相应的单元格中。,将光标定位于,L3,单元格中，并在编辑栏中输入公式，如图,4- 66,所示。,按下回车键或单击编辑栏左边的按钮即可完成公式的输入。,16,使用“,IF()”,函数计算“杨林”的工龄工资。,将光标定位于,M3,单元格中，并在编辑栏中输入函数，如图,4- 67,所示公式。,按下回车键或单击编辑栏左边的按钮，即可完成公式的输入。,17,任务五：用公式计算“三金”,“三金”，即：“养老保险”、“医疗保险”和“失业保险”，每种保险的缴纳都与员工的工资相关，缴纳方案是以员工工资为基数，乘以不同的比例，具体比例如表4- 3所示。,18,计算“杨林”的养老保险。,将光标定位于,N3,单元格中，并在编辑栏中输入公式，如图,4- 68,所示，然后按下回车键。,用相同的方法计算“杨林”的医疗保险和失业保险。,医疗保险计算公式为：“,=(K3+M3)*2%”,失业保险计算公式为：“,=(K3+M3)*1%”,19,任务六：使用“IF()”函数计算“个人所得税”,个人所得税的征收办法如表4- 4所示。,20,计算“杨林”应缴纳个人所得税的应税金额。,计算公式：“应税金额”基本工资,+,工龄工资（养老保险,+,医疗保险,+,失业保险）,2000,将光标定位于,Q3,单元格，并输入如图,4- 69,所示公式。,21,用“,IF()”,函数计算“杨林”的个人所得税。,将光标定位于,R3,单元格，并输入如图,4- 70,所示公式，然后按下回车键。,22,任务七：计算“应发工资”、“实发工资”,计算“杨林”的应发工资：应发工资基本工资,+,工龄工资（养老保险,+,医疗保险,+,失业保险）,将光标定位于,S3,单元格，并在编辑栏中输入公式，如图,4- 71,所示，然后按下回车键。,23,计算“杨林”的实发工资：实发工资应发工资个人所得税,将光标定位于,T3,单元格，并在编辑栏中输入公式，如图,4- 72,所示，然后按下回车键。然后隐藏“参加工作时间”、“职务”、“工龄”列。,24,快速复制公式与函数，填充其余员工的各项工资,。,选择“,K3:T3”,单元格，然后将光标移到,T3,单元格的右下角，当光标变成图标时，如图,4- 73,所示，双击鼠标即可，完成结果如图,4- 74,所示。,25,任务八：“SUM()”和“AVERAGE()”函数,使用“,SUM()”,函数计算本月各项工资总和,将“,A31:B31”,单元格合并及居中，并输入“各项工资总和”。,将光标定位于,K31,单元格，单击“插入”“函数”命令，并在对话框中选择“,SUM()”,函数，参数如图,4- 75,所示。,单击“确定”按扭 。,26,使用“,AVERAGE()”,函数计算本月各项工资平均数,将“,A32:B32”,单元格合并及居中，并输入“各项工资平均数”。,将光标定位于,K32,单元格，并用上述相同的方法插入“,AVERAGE()”,函数，使编辑栏中显示“,=AVERAGE(K3:K30)”,。,将,A31,和,A32,单元格中函数向右复制，复制后如图,4- 77,所示。,27,任务九：巧用“辅助序列”和“定位”制作工资条,在,U,列和,V,列中添加辅助数据。,在,U4,和,V5,单元格中分别输入,1,，然后选择“,U4,：,V5”,单元格，并用自动填充法，将其复制到工资表结尾处，如图,4- 78,所示。,28,在“,U4:V30”,单元格区域中空格所在行添加空行。,选择“,U4:V30”,单元格，单击“编辑”“定位”，打开“定位”对话框，如图,4- 79,所示。,单击“定位条件”按钮，打开“定位条件”对话框，并选择如图,4- 80,所示“空值”单选按钮，然后单击“确定”按钮。,单击“插入”“行”命令即可，如图,4- 81,所示。,29,在每行工资空行中粘贴工资表表头各标题。,选择工资表表头各标题所在行，并单击工具栏中“复制”按钮。,选择“,A2:A57”,单元格，并选择此区域中的空值单元格。,单击工具栏中“贴粘”按钮。,删除,U,列和,V,列中的辅助数据，完成后效果图如图,4- 45,所示。,30,相关知识,单元格引用及其分类？,单元格引用，就是在公式和函数中使用单元格地址来表示单元格中的数据。,相对引用：,=8*A5,绝对引用：,$A$8,，,$B$3,混合引用：,$B1,、,D$5,31,常用函数及其功能说明。,COUNT():,计数函数,格式：,COUNT(,参数,1,，参数,2),功能：统计参数表中的数字参数和包含数字的单元格的个数，只有数值型数据能被统计。,例如：,COUNT(B5:E7),，用于统计,B5:E7,区域单元格中数值型数据的单元格个数。,32,AVERAGE():,平均值函数,格式：,AVERAGE(,参数,1,，参数,2),功能：计算所有参数的算术平均值。,例如：,AVERAGE (B5:E7),，用于统计,B5:E7,区域单元格中数据的平均值。,AVERAGE (B5,E7),，用于统计,B5,和,E7,两个单元格中数据的平均值。,33,SUM():,求和函数,格式：,SUM (,参数,1,，参数,2),功能：计算所有参数的和。,例如：,SUM (B5:E7),，用于统计,B5:E7,区域单元格中数据的和。,SUM (B5,E7),，用于统计,B5,和,E7,两个单元格中数据的和。,34,MAX():,求最大值,格式：,MAX (,参数,1,，参数,2),功能：计算所有参数的最大值。,例如：,MAX (B5:E7),，用于统计,B5:E7,区域单元格中数据的最大值。,MAX (B5,E7),，用于统计,B5,和,E7,两个单元格中数据的最大值。,35,MIN():,求最小值,格式：,MIN (,参数,1,，参数,2),功能：计算所有参数的最小值。,例如：,MIN (B5:E7),，用于统计,B5:E7,区域单元格中数据的最小值。,MIN (B5,E7),，用于统计,B5,和,E7,两个单元格中数据的最小值。,36,MOD():,求余函数,格式：,MOD (,参数,1,，参数,2),功能：求参数,1,除以（整数）参数,2,的余数。,例如：,MOD (12,5),，返回值为,2,。,37,IF():,条件函数,格式：,IF (,条件，值,1,，值,2),功能：,IF,函数是一个逻辑函数，条件为真时返回值,1,，条件为假时返回值,2,。,例如：,IF,（,B2=50,”Y”,”N”,），,B2,单元格中数据大于等于,50,时，返回字符“,Y”,，否则返回字符“,N”,。,38,项目练习,打开“光盘4-2作业学生成绩表（素材）.xls”工作簿，并用函数或公式完成各项数据添加，最终完成效果见“光盘4-2作业学生成绩表（样例）.xls”。,39,