Excel数据管理与分析项目二职员工资表

模块四EXCEL数据管理与分析项目二职员工资表项目要求外部数据的导入SUM()、AVERAGE()、VLOOKUP()、IF()等函数的使用。公式的使用。巧用“辅助序列”和“定位”快速制作工资条的方法。具体任务任务一：制作简单的职员工资表任务二：利用数据导入快速获取员工的基本信息任务三：VLOOKUP()函数的使用任务四：IF()函数、YEAR()函数和NOW()函数的综合应用任务五：用公式计算“三金”任务六：使用公式计算“应发工资”、“实发工资”任务七：巧用“辅助序列”和“定位”制作工资条相关知识任务一：制作简单的职员工资表任务要求：使用公式和SUM()、AVERAGE()、MAX()、MIN()等函数统计工资表中的各项数据。本任务完成后的效果如图4-46所示。图4-46员工工资表效果任务一：制作简单的职员工资表1．输入数据启动Excel2010软件，并在Sheet1工作表中输入如图4-47所示的表格内容。图4-47员工工资表基本数据任务一：制作简单的职员工资表2．使用公式计算“应发合计”、“公积金”、“扣款合计”和“实发合计”3．使用自动求和功能插入SUM()函数并计算工资“各项合计”4．使用函数向导功能插入AVERAGE()函数并计算工资各项平均数5．使用直接输入方法插入MAX()、MIN()函数并计算最高与最低工资任务二：利用数据导入快速获取员工的基本信息任务要求：从项目二中的“4-2-2素材.xlsx”中导入数据到“员工工资表”中，并根据制表需要隐藏或添加行、列数据。任务二：利用数据导入快速获取员工的基本信息1．导入数据①新建工作簿，单击Sheet1工作表标签，选择“数据”选项卡“连接”组中的“连接”命令，打开“工作簿连接”对话框，如图4-53所示。图4-53“工作簿连接”对话框任务二：利用数据导入快速获取员工的基本信息②在对话框中选择“添加”按钮，打开“现有连接”对话框，如图4-54所示。③在对话框中选择“浏览更多”按钮，打开“选取数据源”对话框，如图4-55所示。图4-54“现有连接”对话框图4-55“选取数据源”对话框任务二：利用数据导入快速获取员工的基本信息④在对话框中选择“”按钮，并在弹出的列表中选择Excel文件类型，如图4-56所示。⑤在“选取数据源”对话框的左侧文件列表中选择“4-2-2素材.xlsx”文件，打开“选择表格”对话框，并在对话框中选择“员工基本情况表$”，如图4-57所示，单击“确定”按钮即完成数据的连接，并返回到“工作簿连接”对话框，如图4-58所示，然后单击“关闭”按钮，将此对话框关闭。⑥选择“数据”选项卡“获取外部数据”组中的“现有连接”命令，打开“现有连接”对话框，如图4-59所示，在对话框中选择刚才所添加的连接。任务二：利用数据导入快速获取员工的基本信息图4-56选择Excel文件类型图4-57“选择表格”对话框图4-58“工作簿连接”对话框图4-59“现有连接”对话框任务二：利用数据导入快速获取员工的基本信息⑦在对话框中单击“打开”按扭，打开“导入数据”对话框，如图4-60所示，单击“确定”按钮，即可将数据导入到新工作簿中。⑧单击“文件”｜“保存”命令，将此工作簿文件名设置为“员工工资表.xlsx”，如图4-61所示。图4-60“导入数据”对话框图4-61导入数据并保存后的“员工工资表.xlsx”任务二：利用数据导入快速获取员工的基本信息2．清除表中数据格式①选择“开始”选项卡“样式”组中的“套用表格格式”命令，在打开的下拉列表中选择“新建表样式”命令，打开“新建表快速样式”对话框，如图4-62所示。图4-62“新建表快速样式”对话框任务二：利用数据导入快速获取员工的基本信息②在对话框中单击“格式”命令，打开“设置单元格格式”对话框，并在此对话框中将边框和填充均设置为无，然后在“新建表快速样式”对话框中单击“确定”按钮，完成新建表格样式。③选择“A1：J29”单元格，然后选择“开始”选项卡“样式”组中的“套用表格格式”命令，在打开的下拉列表中选择“自定义”样式中的“表样式1”命令，快速将原有格式清除。任务二：利用数据导入快速获取员工的基本信息④选择数据区域中的任一单元格，然后选择“数据”选项卡“排序和筛选”组中的“筛选”按钮，如图4-63所示。4-63清除格式后效果任务二：利用数据导入快速获取员工的基本信息3．隐藏数据隐藏“性别”、“工作部门”、“学历”、“身份证号”、“联系电话”、“Email地址”列和标题行，并在其右边添加“基本工资”、“工龄”、“工龄工资”、“养老保险”、“医疗保险”、“失业保险”、“应发工资”、“实发工资”列标题，如图4-64所示。图4-64工资表框架任务二：利用数据导入快速获取员工的基本信息①选择“性别”所在列Ｃ列，然后选择“开始”选项卡“单元格”组中的“格式”下拉按钮，并在下拉菜单中选择“隐藏和取消隐藏”子菜单中的“隐藏列”命令，如图4-65所示，即可将“性别”所在列Ｃ列隐藏。图4-65选择“隐藏”命令任务二：利用数据导入快速获取员工的基本信息②使用相同的方法将“工作部门”、“学历”、“身份证号”、“联系电话”、“Email地址”列隐藏。③在表头右边添加“基本工资”、“工龄”、“工龄工资”、“养老保险”、“医疗保险”、“失业保险”、“应发工资”、“实发工资”列标题。④将“工龄”列的数字类型设置为“数值”，小数点后保留0位，其他列的数字格式设置为“数值”且小数点后保留2位，其余格式设置如图4-64所示。任务三：VLOOKUP()函数的使用任务要求：使用VLOOKUP()函数在“各项工资对照表”中的“基本工资对照表”区域内查找相应职务的基本工资数据，并将结果放在相应的单元格中，如表4-1所示。职务基本工资职务基本工资总经理5000技术员2500副经理4000生产工人2000部门主管3200销售员2000表4-1基本工资对照表任务三：VLOOKUP()函数的使用1．选择工作簿并进行设置打开“4-2-2素材.xlsx”工作簿，并将此工作簿中的“各项工资对照表”工作表中A1:I8单元格中的数据复制到“工资表作业”工作簿的Sheet2工作表中的A1:I8单元格，然后将Sheet2工作表重命名为“各项工资对照表”。任务三：VLOOKUP()函数的使用2．定义名为“基本工资对照表”的单元格区域①选择“职员工资表”工作簿中的“各项工资对照表”中的A3:B8单元格。②选择“公式”选项卡“定义的名称”组中的“定义名称”命令，打开“新建名称”对话框，然后将“在当前工作簿中的名称”文本框中输入“基本工资对照表”，如图4-66所示。③单击“确定”按钮。图4-66“定义名称”对话框任务三：VLOOKUP()函数的使用3．查找函数VLOOKUP()的应用使用查找函数VLOOKUP()计算员工“杨林”作为“总经理”的“基本工资”，并将结果放在相应的单元格中。①选择“sheet1”工作表标签，将光标定位于K3单元格中，选择“公式”选项卡“函数库”组最左边的“插入函数”命令图标，打开“插入函数”对话框，在“或选择类别”下拉列表中选择“查找与引用”选项，如图4-69所示。图4-69选择函数类别任务三：VLOOKUP()函数的使用②在对话框中选择VLOOKUP()函数，如图4-70所示。③单击“确定”按扭，打开“函数参数”对话框。在此对话框中设置各个参数，如图4-71所示。④单击“确定”按扭。图4-70选择函数图4-71“函数参数”对话框任务四：IF()、YEAR()和NOW()函数的综合应用综合应用“IF（）、YEAR()和NOW()”函数的计算工龄工资，工龄工资计算方法如表4-2所示。工龄（年）工龄工资工龄（年）工龄工资工龄=2060012工龄=630020工龄=12500工龄6150表4-2工龄工资计算方法任务四：IF()、YEAR()和NOW()函数的综合应用1．YEAR()函数和NOW()函数的应用使用YEAR()函数和NOW()函数计算“杨林”的工龄，并将结果放置在相应的单元格中。①将光标定位于L2单元格中，并在编辑栏中输入公式，如图4-73所示。②按【Enter】键或单击编辑栏左边的按钮即可完成公式的输入。图4-73计算工龄的公式任务四：IF()、YEAR()和NOW()函数的综合应用2．IF()函数的应用使用IF()函数计算“杨林”的工龄工资。①将光标定位于M2单元格中，并在编辑栏中输入如图4-74所示的公式。②按【Enter】键或单击编辑栏左边的按钮即可完成公式的输入。图4-74计算工龄工资任务五：用公式计算“三金”任务要求：公司要为每位员工缴纳“三金”，即养老保险、医疗保险和失业保险，每种保险的缴纳都与员工的工资相关，缴纳方案是以员工工资为基数，然后乘以不同的比例，具体的比例如表4-3所示。表4-3三金缴纳比例以员工各项工资总额为基数保险类型企业个人养老保险20%8%医疗保险8%2%失业保险2%1%任务五：用公式计算“三金”1．计算“杨林”的养老保险，并将结果放在相应的单元格中将光标定位于N2单元格中，并在编辑栏中输入公式，如图4-75所示，然后按【Enter】键。图4-75计算养老保险的公式任务五：用公式计算“三金”2．计算“杨林”的医疗保险和失业保险，并将结果放在相应的单元格中“杨林”的医疗保险的计算公式为“=(K3+M3)*2%”。“杨林”的失业保险的计算公式为“=(K3+M3)*1%”。任务六：使用公式计算“应发工资”、“实发工资”任务要求：使用公式计算出“杨林”的、应发工资与实发工资。1．计算“杨林”的应发工资：应发工资＝基本工资+工龄工资2．计算“杨林”的实发工资：实发工资＝应发工资－（养老保险+医疗保险+失业保险）图4-76计算应发工资的公式图4-77计算实发工资的公式任务六：使用公式计算“应发工资”、“实发工资”3.将工资表中不需要出现的“参加工作年月”、“职务”、“工龄”列隐藏，如图4-78所示。图4-78完成后的结果任务七：巧用“辅助序列”和“定位”制作工资条任务要求：应用“辅助序列”和“定位”巧妙、快速地制作工资条，以确保能快速、简便地打印工资条。任务七：巧用“辅助序列”和“定位”制作工资条1．在S列和T列中添加辅助数据在S3和T4单元格中分别输入1，然后选择S3:T4单元格，并用自动填充法将其往下复制到工资表结尾处，如图4-79所示。图4-79自动填充序列任务七：巧用“辅助序列”和“定位”制作工资条2．在S3:T29单元格区域中的空格所在行添加空行①选择S3:T29单元格，选择“开始”选项卡“编辑”组中的“查找和选择”按钮，在打开的下拉菜单中选择“定位条件”命令，打开“定位条件”对话框，并在对话框中选择“空值”，图4-80所示。②单击“确定”按钮，选择S4:T30单元格区域中的所有空值的单元格。图4-80“定位条件”对话框任务七：巧用“辅助序列”和“定位”制作工资条③选择“开始”选项卡“单元格”组中的“插入”按钮，并在下拉菜单中选择“插入工作表行”命令，系统自动在每次所找到的空值单元格上插入空行，如图4-81所示。图4-81自动插入空行任务七：巧用“辅助序列”和“定位”制作工资条3．在每行的工资空行中粘贴工资表表头各标题①选择工资表表头各标题所在行（第1行），单击工具栏中的“复制”按钮，复制工资表表头各标题。②选择A3:A55单元格，使用上述方法选择此单元格区域中的空值单元格。③单击工具栏中的“贴粘”按钮，即可完成工资表表头各标题的贴粘。④删除S列和T列中的辅助数据。相关知识1．单元格引用及其分类所谓单元格引用，是指在公式和函数中使用单元格地址来表示单元格中的数据。例如，A1表示第A列与第1行交叉的单元格，A1:B5表示A1～B5的连续的单元格区域，“A1,B5”则表示A1和B5两个单元格。在Excel2010中，根据处理公式或函数的需要，引用方式有以下3种：相对引用、绝对引用、混合引用相关知识2．常用函数及其功能说明。（1）计数函数COUNT()格式：COUNT(参数1，参数2…)功