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

《模块4 Excel数据管理与分析2》由会员分享，可在线阅读，更多相关《模块4 Excel数据管理与分析2（39页珍藏版）》请在装配图网上搜索。

1、单击此处编辑母版标题样式,,单击此处编辑母版文本样式,,第二级,,第三级,,第四级,,第五级,,,,*,模块4 Excel数据管理与分析,项目二 职员工资表,1,项目要求,外部数据的导入,,VLOOKUP(),、,IF(),、,SUM(),、,AVERAGE(),函数的使用,,公式的使用,,巧用“辅助序列”和“定位”快速制作工资条的方法,2,,具体任务,任务一：制作简单的职员工资表,,任务二：导入数据并添加列标题,,任务三：“,VLOOKUP()”,函数的使用,,任务四：“,IF,（）、,YEAR(),和,NOW()”,函数的综合应用,,任务五：用公式计算“三金”,,任务六：使用“,IF()

2、”,函数计算“个人所得税”,,任务七：使用公式计算“应发工资”、“实发工资”,,任务八：“,SUM()”,和“,AVERAGE()”,函数的使用,,任务九：巧用“辅助序列”和“定位”制作工资条,,相关知识,3,,任务一：制作简单的职员工资表,,使用公式和“SUM()”、“AVERAGE()”、“MAX()”、“MIN()”等函数。,4,,启动,Excel,软件，并输入基本内容。,,使用公式计算“应发工资”、“公积金”、“扣款合计”和“实发合计”。,,使用自动求和功能插入,SUM(),函数计算工资“各项合计”。,,使用函数向导功能插入“,AVERAGE()”,函数计算工资各项平均数。,,使用直接

3、输入方法插入“,MAX()”,、“,MIN()”,函数计算最高与最低工资。,5,,任务二：导入数据并添加列标题,,从上一项目的“职工基本情况表”中导入数据到“员工工资表”中，并根据制表需要隐藏或添加行、列数据。,6,,新建工作簿，选择“,sheet1”,工作表标签，单击“数据”｜“导入外部数据”｜“导入数据”命令，如图,4- 53,所示。,,在对话框中选择到“职工基本情况表”工作簿，然后单击“打开”按钮，打开“选择表格”对话框，并在此对话框中选择“职员基本情况表,$”,，如图,4- 54,所示，单击“确定”按扭，打开“导入数据”对话框。,,在此对话框中选择数据放置的为新建工作表，如图,4- 5

4、5,所示。,,单击“确定”按扭。,1.从“职工基本情况表”工作簿中导入数据。,7,,8,,2.隐藏、添加行或列，如图4- 57所示。,,选择“性别”所在列Ｃ列,︱,“格式”｜“列”｜“隐藏”命令，隐藏Ｃ列。,,用相同的方法将“工作部门”、“学历”、“身份证号”、“联系电话”、“,Email,地址”列隐藏。,,在表头右边添加如图,4- 57,所示列标题。,,将“工龄”列的数字类型设置为“数值”，小数点后保留,0,位；其余列数字格式设置为“数值”类型且小数点后保留,2,位。,,提示：将单元格数字类型设置为“数值”方法与设置为“文本”方法相同，,,如未设置“工龄”列的数字类型设置为“数值”，则在后面

5、计算“工龄”时显,,示结果将出错。,9,,将,sheet1,工作表重命名为“员工工资表”。,,将此工作簿保存，文件名为“工资表作业”，保存位置与“职工基本情况表”在同一文件夹中。,,提示：此时，“工资表”工作簿中的“员工工资表”中的导入数据已经与“职工基本情况表”建立链接关系。将它们保存在同一个文件夹下，可防止文件在复制或移动的操作中丢失数据。,10,,任务三：“VLOOKUP()”函数的使用,,使用VLOOKUP()函数在“各项工资对照表”中的“基本工资对照表”区域内查找到相应职务的基本工资数据，并将结果放在相应的单元格中。,,11,,复制“职员工资表（素材）”工作簿中的“各项工资对照表”工

6、作表中“,A1:I8”,单元格的数据复制到“工资表作业”工作簿的,sheet2,工作表中“,A1:I8”,单元格，并将,sheet2,重命名为“各项工资对照表”。,12,,定义名为“基本工资对照表”的单元格区域。,,选择“工资表作业”工作簿中的“各项工资对照表”中的“,A3:B8”,单元格。,,,单击“插入”｜“名称”｜“定义”命令，打开“定义名称”对话框，然后将文本框中“总经理”删除，并输入如图,4- 59,所示内容。,,单击“添加”按钮，即可将新定义名称添加到下面的列表中，如图,4- 60,所示。,,13,,使用查找函数“,VLOOKUP()”,计算员工“杨林”作为“总经理”职务的“基本工

7、资”，并将结果放在相应的单元格中。,,将光标定位于,K3,单元格中，单击“插入”｜“函数”命令，并在对话框中 选择如图,4- 62,所示类别。,,在对话框中选择“,VLOOKUP()”,函数，如图,4- 63,所示。,,单击“确定”按扭，并在此对话框中输入如图,4- 64,所示各个参数，最后单击“确定”按扭。,14,,任务四：“IF（）、YEAR()和NOW()” 的综合应用,,综合应用“IF（）、YEAR()和NOW()”函数的计算工龄工资。工龄工资计算方法如表4- 2所示。,15,,使用“,YEAR(),和,NOW()”,函数计算“杨林”的工龄，并将结果放置在相应的单元格中。,,将光标定位

8、于,L3,单元格中，并在编辑栏中输入公式，如图,4- 66,所示。,,按下回车键或单击编辑栏左边的　按钮即可完成公式的输入。,16,,使用“,IF()”,函数计算“杨林”的工龄工资。,,将光标定位于,M3,单元格中，并在编辑栏中输入函数，如图,4- 67,所示公式。,,按下回车键或单击编辑栏左边的按钮，即可完成公式的输入。,17,,任务五：用公式计算“三金”,“三金”，即：“养老保险”、“医疗保险”和“失业保险”，每种保险的缴纳都与员工的工资相关，缴纳方案是以员工工资为基数，乘以不同的比例，具体比例如表4- 3所示。,,18,,计算“杨林”的养老保险。,,将光标定位于,N3,单元格中，并在编辑

9、栏中输入公式，如图,4- 68,所示，然后按下回车键。,,,,,,用相同的方法计算“杨林”的医疗保险和失业保险。,,医疗保险计算公式为：“,=(K3+M3)*2%”,,失业保险计算公式为：“,=(K3+M3)*1%”,,19,,任务六：使用“IF()”函数计算“个人所得税”,,个人所得税的征收办法如表4- 4所示。,20,,计算“杨林”应缴纳个人所得税的应税金额。,,计算公式：“应税金额”＝基本工资,+,工龄工资－（养老保险,+,医疗保险,+,失业保险）－,2000,,将光标定位于,Q3,单元格，并输入如图,4- 69,所示公式。,21,,用“,IF()”,函数计算“杨林”的个人所得税。,,将

10、光标定位于,R3,单元格，并输入如图,4- 70,所示公式，然后按下回车键。,,22,,任务七：计算“应发工资”、“实发工资”,,计算“杨林”的应发工资：应发工资＝基本工资,+,工龄工资－（养老保险,+,医疗保险,+,失业保险）,,,将光标定位于,S3,单元格，并在编辑栏中输入公式，如图,4- 71,所示，然后按下回车键。,,23,,计算“杨林”的实发工资：实发工资＝应发工资－个人所得税,,,将光标定位于,T3,单元格，并在编辑栏中输入公式，如图,4- 72,所示，然后按下回车键。然后隐藏“参加工作时间”、“职务”、“工龄”列。,24,,快速复制公式与函数，填充其余员工的各项工资,。,,,选择

11、“,K3:T3”,单元格，然后将光标移到,T3,单元格的右下角，当光标变成图标时　，如图,4- 73,所示，双击鼠标即可，完成结果如图,4- 74,所示。,,25,,任务八：“SUM()”和“AVERAGE()”函数,使用“,SUM()”,函数计算本月各项工资总和,,将“,A31:B31”,单元格合并及居中，并输入“各项工资总和”。,,将光标定位于,K31,单元格，单击“插入”｜“函数”命令，并在对话框中选择“,SUM()”,函数，参数如图,4- 75,所示。,,单击“确定”按扭 。,26,,使用“,AVERAGE()”,函数计算本月各项工资平均数,,将“,A32:B32”,单元格合并及居中，

12、并输入“各项工资平均数”。,,将光标定位于,K32,单元格，并用上述相同的方法插入“,AVERAGE()”,函数，使编辑栏中显示“,=AVERAGE(K3:K30)”,。,,将,A31,和,A32,单元格中函数向右复制，复制后如图,4- 77,所示。,,27,,任务九：巧用“辅助序列”和“定位”制作工资条,,在,U,列和,V,列中添加辅助数据。,,在,U4,和,V5,单元格中分别输入,1,，然后选择“,U4,：,V5”,单元格，并用自动填充法，将其复制到工资表结尾处，如图,4- 78,所示。,28,,在“,U4:V30”,单元格区域中空格所在行添加空行。,,选择“,U4:V30”,单元格，单击

13、“编辑”｜“定位”，打开“定位”对话框，如图,4- 79,所示。,,单击“定位条件”按钮，打开“定位条件”对话框，并选择如图,4- 80,所示“空值”单选按钮，然后单击“确定”按钮。,,单击“插入”｜“行”命令即可，如图,4- 81,所示。,,29,,在每行工资空行中粘贴工资表表头各标题。,,选择工资表表头各标题所在行，并单击工具栏中“复制”按钮。,,选择“,A2:A57”,单元格，并选择此区域中的空值单元格。,,单击工具栏中“贴粘”按钮。,,删除,U,列和,V,列中的辅助数据，完成后效果图如图,4- 45,所示。,,30,,相关知识,单元格引用及其分类？,,单元格引用，就是在公式和函数中使用

14、单元格地址来表示单元格中的数据。,,相对引用：,=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:E

15、7),，用于统计,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,区域单

16、元格中数据的最大值。,,,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,,