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

模块4 Excel数据管理与分析 项目二 职员工资表 项目要求 外部数据的导入 VLOOKUP()、IF()、SUM()、AVERAGE()函数的使用 公式的使用 巧用“辅助序列”和“定位”快速制作工资条的方法 具体任务 任务一：制作简单的职员工资表 任务二：导入数据并添加列标题 任务三：“VLOOKUP()”函数的使用 任务四：“IF（）、YEAR()和NOW()”函数的综合应用 任务五：用公式计算“三金” 任务六：使用“IF()”函数计算“个人所得税” 任务七：使用公式计算“应发工资”、“实发工资” 任务八：“SUM()”和“AVERAGE()”函数的使用 任务九：巧用“辅助序列”和“定位”制作工资条 相关知识 任务一：制作简单的职员工资表 使用公式和“SUM()”、“AVERAGE()”、“MAX()”、“MIN()”等函数。 启动Excel软件，并输入基本内容。 使用公式计算“应发工资”、“公积金”、“扣款合计”和“实发合计”。 使用自动求和功能插入SUM()函数计算工资“各项合计”。 使用函数向导功能插入“AVERAGE()”函数计算工资各项平均数。 使用直接输入方法插入“MAX()”、“MIN()”函数计算最高与最低工资。 任务二：导入数据并添加列标题 从上一项目的“职工基本情况表”中导入数据到“员工工资表”中，并根据制表需要隐藏或添加行、列数据。 1.从“职工基本情况表”工作簿中导入数据。 新建工作簿，选择“sheet1”工作表标签，单击“数据”｜“导入外部数据”｜“导入数据”命令，如图4- 53所示。 在对话框中选择到“职工基本情况表”工作簿，然后单击“打开”按钮，打开“选择表格”对话框，并在此对话框中选择“职员基本情况表$”，如图4- 54所示，单击“确定”按扭，打开“导入数据”对话框。 在此对话框中选择数据放置的为新建工作表，如图4- 55所示。 单击“确定”按扭。 2.隐藏、添加行或列，如图4- 57所示。 选择“性别”所在列Ｃ列︱ “格式”｜“列”｜“隐藏”命令，隐藏Ｃ列。 用相同的方法将“工作部门”、“学历”、“身份证号”、“联系电话”、“Email地址”列隐藏。 在表头右边添加如图4- 57所示列标题。 将“工龄”列的数字类型设置为“数值”，小数点后保留0位；其余列数字格式设置为“数值”类型且小数点后保留2位。 将sheet1工作表重命名为“员工工资表”。 将此工作簿保存，文件名为“工资表作业”，保存位置与“职工基本情况表”在同一文件夹中。 任务三：“VLOOKUP()”函数的使用 使用VLOOKUP()函数在“各项工资对照表”中的“基本工资对照表”区域内查找到相应职务的基本工资数据，并将结果放在相应的单元格中。 复制“职员工资表（素材）”工作簿中的“各项工资对照表”工作表中“A1:I8”单元格的数据复制到“工资表作业”工作簿的sheet2工作表中“A1:I8”单元格，并将sheet2重命名为“各项工资对照表”。 定义名为“基本工资对照表”的单元格区域。 选择“工资表作业”工作簿中的“各项工资对照表”中的“A3:B8”单元格。 单击“插入”｜“名称”｜“定义”命令，打开“定义名称”对话框，然后将文本框中“总经理”删除，并输入如图4- 59所示内容。 单击“添加”按钮，即可将新定义名称添加到下面的列表中，如图4- 60所示。 使用查找函数“VLOOKUP()”计算员工“杨林”作为“总经理”职务的“基本工资”，并将结果放在相应的单元格中。 将光标定位于K3单元格中，单击“插入”｜“函数”命令，并在对话框中 选择如图4- 62所示类别。 在对话框中选择“VLOOKUP()”函数，如图4- 63所示。 单击“确定”按扭，并在此对话框中输入如图4- 64所示各个参数，最后单击“确定”按扭。 任务四：“IF（）、YEAR()和NOW()” 的综合应用 综合应用“IF（）、YEAR()和NOW()”函数的计算工龄工资。工龄工资计算方法如表4- 2所示。 使用“YEAR()和NOW()”函数计算“杨林”的工龄，并将结果放置在相应的单元格中。 将光标定位于L3单元格中，并在编辑栏中输入公式，如图4- 66所示。 按下回车键或单击编辑栏左边的　按钮即可完成公式的输入。 使用“IF()”函数计算“杨林”的工龄工资。 将光标定位于M3单元格中，并在编辑栏中输入函数，如图4- 67所示公式。 按下回车键或单击编辑栏左边的按钮，即可完成公式的输入。 任务五：用公式计算“三金” “三金”，即：“养老保险”、“医疗保险”和“失业保险”，每种保险的缴纳都与员工的工资相关，缴纳方案是以员工工资为基数，乘以不同的比例，具体比例如表4- 3所示。