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

1、模块4 Excel数据管理与分析 项目二 职员工资表 1项目要求外部数据的导入VLOOKUP()、IF()、SUM()、AVERAGE()函数的使用公式的使用巧用“辅助序列”和“定位”快速制作工资条的方法2具体任务任务一：制作简单的职员工资表 任务二：导入数据并添加列标题 任务三：“VLOOKUP()”函数的使用 任务四：“IF（）、YEAR()和NOW()”函数的综合应用 任务五：用公式计算“三金” 任务六：使用“IF()”函数计算“个人所得税” 任务七：使用公式计算“应发工资”、“实发工资”任务八：“SUM()”和“AVERAGE()”函数的使用任务九：巧用“辅助序列”和“定位”制作工资条

2、 相关知识3任务一：制作简单的职员工资表 使用公式和“SUM()”、“AVERAGE()”、“MAX()”、“MIN()”等函数。 4启动Excel软件，并输入基本内容。使用公式计算“应发工资”、“公积金”、“扣款合计”和“实发合计”。使用自动求和功能插入SUM()函数计算工资“各项合计”。 使用函数向导功能插入“AVERAGE()”函数计算工资各项平均数。 使用直接输入方法插入“MAX()”、“MIN()”函数计算最高与最低工资。5任务二：导入数据并添加列标题 从上一项目的“职工基本情况表”中导入数据到“员工工资表”中，并根据制表需要隐藏或添加行、列数据。6新建工作簿，选择“sheet1”工

3、作表标签，单击“数据”“导入外部数据”“导入数据”命令，如图4- 53所示。在对话框中选择到“职工基本情况表”工作簿，然后单击“打开”按钮，打开“选择表格”对话框，并在此对话框中选择“职员基本情况表$”，如图4- 54所示，单击“确定”按扭，打开“导入数据”对话框。 在此对话框中选择数据放置的为新建工作表，如图4- 55所示。 单击“确定”按扭。 1.从“职工基本情况表”工作簿中导入数据。782.隐藏、添加行或列，如图4- 57所示。 选择“性别”所在列列 “格式”“列”“隐藏”命令，隐藏列。 用相同的方法将“工作部门”、“学历”、“身份证号”、“联系电话”、“Email地址”列隐藏。 在表头

4、右边添加如图4- 57所示列标题。 将“工龄”列的数字类型设置为“数值”，小数点后保留0位；其余列数字格式设置为“数值”类型且小数点后保留2位。 提示：将单元格数字类型设置为“数值”方法与设置为“文本”方法相同，如未设置“工龄”列的数字类型设置为“数值”，则在后面计算“工龄”时显示结果将出错。 9将sheet1工作表重命名为“员工工资表”。将此工作簿保存，文件名为“工资表作业”，保存位置与“职工基本情况表”在同一文件夹中。 提示：此时，“工资表”工作簿中的“员工工资表”中的导入数据已经与“职工基本情况表”建立链接关系。将它们保存在同一个文件夹下，可防止文件在复制或移动的操作中丢失数据。10任务

5、三：“VLOOKUP()”函数的使用 使用VLOOKUP()函数在“各项工资对照表”中的“基本工资对照表”区域内查找到相应职务的基本工资数据，并将结果放在相应的单元格中。11复制“职员工资表（素材）”工作簿中的“各项工资对照表”工作表中“A1:I8”单元格的数据复制到“工资表作业”工作簿的sheet2工作表中“A1:I8”单元格，并将sheet2重命名为“各项工资对照表”。12定义名为“基本工资对照表”的单元格区域。选择“工资表作业”工作簿中的“各项工资对照表”中的“A3:B8”单元格。 单击“插入”“名称”“定义”命令，打开“定义名称”对话框，然后将文本框中“总经理”删除，并输入如图4- 5

6、9所示内容。单击“添加”按钮，即可将新定义名称添加到下面的列表中，如图4- 60所示。 13使用查找函数“VLOOKUP()”计算员工“杨林”作为“总经理”职务的“基本工资”，并将结果放在相应的单元格中。将光标定位于K3单元格中，单击“插入”“函数”命令，并在对话框中 选择如图4- 62所示类别。在对话框中选择“VLOOKUP()”函数，如图4- 63所示。 单击“确定”按扭，并在此对话框中输入如图4- 64所示各个参数，最后单击“确定”按扭。 14任务四：“IF（）、YEAR()和NOW()” 的综合应用 综合应用“IF（）、YEAR()和NOW()”函数的计算工龄工资。工龄工资计算方法如表

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

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

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

10、标即可，完成结果如图4- 74所示。 25任务八：“SUM()”和“AVERAGE()”函数使用“SUM()”函数计算本月各项工资总和将“A31:B31”单元格合并及居中，并输入“各项工资总和”。 将光标定位于K31单元格，单击“插入”“函数”命令，并在对话框中选择“SUM()”函数，参数如图4- 75所示。单击“确定”按扭 。26使用“AVERAGE()”函数计算本月各项工资平均数将“A32:B32”单元格合并及居中，并输入“各项工资平均数”。将光标定位于K32单元格，并用上述相同的方法插入“AVERAGE()”函数，使编辑栏中显示“=AVERAGE(K3:K30)”。将A31和A32单元格

11、中函数向右复制，复制后如图4- 77所示。 27任务九：巧用“辅助序列”和“定位”制作工资条 在U列和V列中添加辅助数据。在U4和V5单元格中分别输入1，然后选择“U4：V5”单元格，并用自动填充法，将其复制到工资表结尾处，如图4- 78所示。28在“U4:V30”单元格区域中空格所在行添加空行。选择“U4:V30”单元格，单击“编辑”“定位”，打开“定位”对话框，如图4- 79所示。单击“定位条件”按钮，打开“定位条件”对话框，并选择如图4- 80所示“空值”单选按钮，然后单击“确定”按钮。单击“插入”“行”命令即可，如图4- 81所示。 29在每行工资空行中粘贴工资表表头各标题。选择工资表

12、表头各标题所在行，并单击工具栏中“复制”按钮。选择“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区域单元格中数值型

13、数据的单元格个数。32AVERAGE():平均值函数格式：AVERAGE(参数1，参数2)功能：计算所有参数的算术平均值。例如：AVERAGE (B5:E7)，用于统计B5:E7区域单元格中数据的平均值。AVERAGE (B5,E7)，用于统计B5和E7两个单元格中数据的平均值。33SUM():求和函数格式：SUM (参数1，参数2)功能：计算所有参数的和。例如：SUM (B5:E7)，用于统计B5:E7区域单元格中数据的和。SUM (B5,E7)，用于统计B5和E7两个单元格中数据的和。 34MAX():求最大值格式：MAX (参数1，参数2)功能：计算所有参数的最大值。例如：MAX (B5:E7)，用于统计B5:E7区域单元格中数据的最大值。MAX (B5,E7)，用于统计B5和E7两个单元格中数据的最大值。 35MIN():求最小值格式：MIN (参数1，参数2)功能：计算所有参数的最小值。例如：MIN (B5:E7)，用于统计B5:E7区域单元格中数据的最小值。MIN (B5,E7)，用于统计B5和E7两个单元格中数据的最小值。 36MOD():求余函数格式：MOD (参数1，参数2)功能：求参数1除以（整数）参数2的余数。例如：M