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

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