《信息素养概论（第三版）》课件第3章 WPS 表格处理与分析

《信息素养概论》第三章WPS表格处理与分析第3章WPS表格处理与分析目录员工信息表中数据的编辑2员工工资表中数据的处理4员工工资表中数据的分析5员工工资表中数据的展示与打印6员工工资表中数据的计算3员工信息表的创建1第3章WPS表格处理与分析目录员工信息表的创建1员工信息表中数据的编辑2员工工资表中数据的计算3员工工资表中数据的处理4员工工资表中数据的分析5员工工资表中数据的展示与打印6问题引入小张是天天向上公司财务部员工，经理需要他制作一份公司员工信息表，用于本月员工工资的计算和分析等，表格文件以“天天向上员工信息表.xlsx”命名保存，表格文件里面包含“员工信息表”等工作表。完成该任务，需熟悉WPS表格的工作界面，掌握WPS表格中工作簿、工作表和单元格的基本操作等内容。操作演示：创建、打开、保存工作簿3.1.1工作簿的基本操作1.新建工作簿2.打开工作簿3.保存工作簿4.关闭工作簿相关知识1.创建一个空白WPS表格保存到D盘，命名为“天天向上员工信息表.xlsx”。任务实施3.1.1工作簿的基本操作学生练习教师操作演示3.1.2工作表的基本操作1.插入工作表2.删除工作表3.重命名工作表4.设置工作表标签颜色5.移动或复制工作表6.显示或隐藏工作表7.保护工作表相关知识1.将“第3章\任务1\标题.xlsx”文件中“表格标题”工作表复制到“天天向上员工信息表.xlsx”文件中；将“天天向上员工信息表.xlsx”文件中的“表格标题”工作表名称重命名为“员工信息表”，并且复制一个工作表并重命名为“备份”工作表；设置“备份”工作表标签颜色为“红色”及删除“Sheet1”工作表。任务实施3.1.2工作表的基本操作学生练习教师操作演示3.1.3单元格的基本操作1.基本概念2.选择单元格3.调整行高和列宽4.插入或删除行列5.合并或拆分单元格相关知识1.将“员工信息表”工作表中的A2单元格和第一行删除，然后将A1:H1单元格区域合并居中和调整行高为30。任务实施3.1.3单元格的基本操作学生练习第3章WPS表格处理与分析目录员工信息表中数据的编辑2员工信息表的创建1员工工资表中数据的计算3员工工资表中数据的处理4员工工资表中数据的分析5员工工资表中数据的展示与打印6问题引入小张通过上一任务的相关知识，创建了“员工信息表”工作表，现需录入工作表内容，并进行整理和美化，并在“员工信息表”工作表基础上快速制作“月考勤表”工作表。完成该任务，需掌握工作表中数据的编辑等操作，包括工作表中数据录入、查找与替换、批注、整理和修饰等内容。教师操作演示3.2.1数据录入1.认识数据类型2.输入数据3.修改数据4.快速填充数据5.输入有效数据6.使用下拉列表输入数据相关知识1.在“员工信息表”工作表中输入如图3-11所示的内容。任务实施3.2.1数据录入学生练习教师操作演示3.2.2查找与替换数据1.查找数据2.替换数据相关知识1.使用替换功能，把在“员工信息表”工作表中姓名为“雷大历”改为“雷大力”。任务实施3.2.2查找与替换数据学生练习教师操作演示3.2.3批注1.添加批注2.编辑批注3.查看批注4.删除批注相关知识1.为“员工信息表”工作表中姓名为“雷大力”的单元格添加批注，内容为“更改之前为：雷大历”。任务实施3.2.3批注学生练习教师操作演示3.2.4整理和修饰1.设置单元格格式2.应用单元格样式3.应用条件格式4.套用表格样式相关知识1.整理及美化“员工信息表”工作表，效果如图3-23所示。任务实施3.2.4整理和修饰学生练习2.使用“员工信息表”工作表，快速完成“月考勤表”工作表的创建、整理及美化，效果如图3-26所示。任务实施3.2.4整理和修饰学生练习第3章WPS表格处理与分析目录员工工资表中数据的计算3员工信息表中数据的编辑2员工信息表的创建1员工工资表中数据的处理4员工工资表中数据的分析5员工工资表中数据的展示与打印6问题引入小张通过上一任务完成了“员工信息表”和“月考勤表”工作表的创建、整理及美化。现需结合其他工作表及规定的数据计算每位员工的本月工资，如奖金、应发工资、个税、实发工资等，并统计各工资段的人数及各部门工资信息。完成该任务，需掌握工作表中数据的计算等操作，包括单元格引用、公式和函数的应用等内容。操作演示3.3.1公式与单元格引用1.输入公式2.修改公式3.复制公式4.公式中的运算符5.引用单元格相关知识1.将上一任务完成的“员工信息表”和“月考勤表”工作表复制到“\任务3\工资表.xlsx”工作簿中，并且计算“月考勤表”工作表中的资金项数据，使用J10:K16单元格区域中给定的条件计算每位员工的奖金，如图3-30所示。奖金的计算公式为：奖金=迟到次数ⅹ扣除金额+早退次数ⅹ扣除金额+加班天数ⅹ奖励金额+请假天数ⅹ扣除金额。因为迟到和早退扣除的金额相同，因此公式可以改为：奖金=(迟到次数+早退次数)ⅹ扣除金额+加班天数ⅹ奖励金额+请假天数ⅹ扣除金额。任务实施3.3.1公式与单元格引用学生练习教师操作演示3.3.2函数1.函数的概念2.函数的结构3.函数的类型4.使用函数相关知识1.用最大值函数MAX()，在“月奖金表”工作表中，计算员工所得的“最高奖金额”。2.用最小值函数MIN、求和函数SUM、求平均值函数AVERAGE，在“月奖金表”工作表中，计算出员工的“最低奖金额”、“总奖金额”、“平均奖金额”。3.用排序函数RANK，计算出员工所得奖金的“名次”。RANK函数：返回某数字在一列数字中相对于其他数值的大小排名。格式：RANK(number,ref,order)。Number(数值)：指定的数字。Ref(引用)：一组数或对一个数据列表的引用。Ref中的非数字值将被忽略。Order(排位方式)：指定排位的方式。如果order为0或省略，按照降序排列；非零，则按照升序排列。任务实施13.3.2函数学生练习4.使用日期函数TODAY、YEAR，在“员工信息表”工作表中计算出每位员工的“工龄”。计算“工龄”的公式为：工龄=当前年份-参加工作年份。其中，在计算“当前年份”时，先使用TODAY函数获取系统日期，再用YEAR函数获取当年的年份，即当前年份=YEAR(TODAY())；参加工作年份=YEAR(工作日期)。5.在“月应发工资表”工作表中的“工龄”列引入“员工信息表”工作表中的“工龄”列数据。任务实施23.3.2函数学生练习6.使用“VLOOKUP”函数，参照“工资对照表”工作表中的“职位工资”表，在“月应发工资表”工作表中计算出“职位工资”。VLOOKUP函数：按列查找，最终返回该列所需查询列序所对应的值。格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。Lookup_value(查找值)：为需要在数据表第一列中进行查找的数值，可以为数值、引用或文本字符串。Table_array(数据表)：为需要在其中查找数据的数据表。col_index_num(列序数)：为table_array中待返回的匹配值的列序号。col_index_num为1时，返回table_array第一列的数值，以此类推。Range_lookup(匹配条件)：为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果为false或0，则返回精确匹配，如果找不到，则返回错误值#N/A。任务实施33.3.2函数学生练习7.在“月应发工资表”工作表中的“奖金”列引入“月考勤表”工作表中的“奖金”列数据。8.使用逻辑判断函数IF，参照“工资对照表”工作表中的“工龄工资”表，实现在“月应发工资表”工作表中计算出“工龄工资”。IF函数：是判断一个条件是满足，如果满足返回一个值，如果不满足则返回另一个值。格式：IF(logical_test,Value_if_true,Value_if_false)。logical_test(测试条件)：表示计算结果可判断为True或False的任意值或表达式。Value_if_true(真值)：表示“logical_test”结果为True时的值。Value_if_false(假值)：表示“logical_test”结果为False时的值。例如：IF(1>=2,"条件为真","条件为假")，因为表达式“1>=2”的结果为False，所以该IF函数的返回结果为“条件为假”。IF函数还可以用“Value_if_true(真值)”及“Value_if_false(假值)”参数构造复杂的判断条件，形成函数的多层嵌套。任务实施43.3.2函数学生练习9.计算“月应发工资表”工作表中的“应发工资”，并把值引入到“月实发工资表”工作表中的“应发工资”。计算方法为：应发工资=工龄工资+职位工资+奖金。任务实施53.3.2函数学生练习10.计算“月实发工资表”工作表的养老保险、医疗保险、失业保险、住房公积金。三险一金一般包括养老保险、医疗保险、失业保险、住房公积金。其中，个人缴纳部分的计算方法如下：养老保险=基本工资*8%，医疗保险=基本工资*2%失业保险=基本工资*1%，住房公积金=基本工资*10%其中，基本工资=工龄工资+职位工资下面以“养老保险”计算为例进行介绍，操作如下：选中“月实发工资表”工作表中的F3单元格，输入“=”，之后单击“月应发工资表”工作表中的G3单元格，之后输入“+”，再点击“月应发工资表”工作表中的H3单元格，然后，将两者用括号括起来，再在后面乘以0.08，按【Enter】键，此时在G3单元格的编辑栏中显示的最终公式为“=(月应发工资表!G3+月应发工资表!H3)*0.08”，使用填充柄填充剩下的单元格。其余医疗保险、失业保险、住房公积金的计算方法类似，请读者自行完成。任务实施63.3.2函数学生练习11.参照“税率计算表”工作表中的“税率表”，计算“月实发工资表”工作表的“应纳税额”和“个税”。应纳税额是对月收入超过5000以上的部分进行征税，计算方法为：应纳税额=每月工资(薪金)所得-三险一金-起征点(5000)其中“每月工资(薪金)所得”为每月的“应发工资”，因此，应纳税额可以使用IF函数计算，公式为“=IF(E3>=5000,E3-F3-G3-H3-I3-5000,0)”。个税的计算方法为：个税=应纳税额(月)×适用"税率"。假设，在本任务该公司的应纳税额最高不超过12000的情况下，则K3单元格的计算公式为“=IF(J3<=3000,J3*0.03,J3*0.1-210)”。12.在“月实发工资表”工作表中计算出“实发工资”。计算方法为：实发工资=应发工资-个税任务实施73.3.2函数学生练习13.用统计函数COUNTA或COUNT，将“月实发工资表”工作表中实发工资的总人数统计到“月工资统计表”的相应单元格中。14.用条件统计函数COUNTIF，将“月实发工资表”中各实发工资段的人数统计到“月工资统计表”的相应单元格中。15.使用COUNTIF函数，将“月实发工资表”工作表中各部门的人数统计到“月工资统计表”工作表的相应单元格中。16.使用SUMIF函数，将“月应发工资表”工作表中各部门的奖金、应发工资和“月实发工资表”工作表中各部门实发工资统计到“月工资统计表”工作表的相应单元格中。任务实施83.3.2函数学生练习第3章WPS表格处理与分析目录员工工资表中数据的处理4员工信息表中数据的编辑2员工工资表中数据的计算3员工信息表的创建1员工工资表中数据的分析5员工工资表中数据的展示与打印6问题引入小张通过上一任务完成了“月应发工资表”工作表数据的计算。为了更直观地查看、理解并快速查找需要的数据，现需对工作表中的数据进行适当的处理，如排序、筛选、分类汇总等。完成该任务，需掌握工作表中数据的处理等操作，包括有数据的排序，数据的筛选、数据的分类汇总等内容。教师操作演示3.4.1数据的排序1.简单排序2.多条件排序相关知识1.在“第3章\任务4\工资表.xlsx”文件中，将“月应发工资表”工作表创建2个副本，分别重命名为“简单排序”和“多条件排序”；在“简单排序”工作表中实现所有数据按“应发工资”降序排序；在“多条件排序”工作表中实现所有数据按“应发工资”列降序排序，当“应发工资”相同时按“姓名”升序排序。任务实施3.4.1数据的排序学生练习教师操作演示3.4.2数据的筛选1.自动筛选2.高级筛选相关知识1.将“月应发工资表”工作表创建2个副本，分别重命名为“自动筛选”和“高级筛选”；在“自动筛选”工作表中，筛选出“销售部”并且“工龄”10年以上的员工信息；在“高级筛选”工作表中，筛选出“研发部”奖金大于等于2000或者“客服部”的“应发工资”大于等于10000的员工信息，并显示在L6单元格开始的区域中。任务实施3.4.2数据的筛选学生练习教师操作演示3.4.3数据的分类汇总1.分类汇总2.多重分类汇总相关知识1.将“月应发工资表”工作表创建2个副本，分别重命名为“分类汇总”和“多重分类汇总”；在“分类汇总”工作表中汇总出各部门“工龄工资”和“奖金”的最大值；在“多重分类汇总”工作表中汇总出各部门男女职工的“工龄工资”和“奖金”的最小值。任务实施3.4.3单元格的基本操作学生练习第3章WPS表格处理与分析目录员工工资表中数据的分析5员工信息表中数据的编辑2员工工资表中数据的计算3员工工资表中数据的处理4员工信息表的创建1员工工资表中数据的展示与打印6问题引入小张通过之前的任务完成了“月工资统计表”工作表的统计和“月应发工资表”工作表的计算。为了让平面、抽象的数据变得立体、形象并能直观地反映数据信息，现需要使用图形化表现形式对数据分析，如图表、数据透视表、数据透视图等。完成该任务，需掌握工作表中数据的分析等操作，包括有图表的创建、编辑和美化及数据透视表和数据透视图的操作等内容。操作演示：创建、打开、保存工作簿3.5.1创建图表1.插入图表2.移动图表3.图表的组成相关知识1.在“月工资统计表”工作表中，根据各部门总计金额制作图表，图表类型为“簇状柱形图”；将图表移动到新的工作表中并命名为“各部门工资统计图”。任务实施3.5.1创建图表学生练习教师操作演示3.5.2编辑并美化图表1.图表布局2.图表样式3.选择数据4.更改类型5.图表模板相关知识1.对上一任务创建的“各部门工资统计图”工作表进行编辑，要求：

更改图表的布局为“布局1”；将图表标题为“各部门工资统计图”；将横坐标轴标题为“部门”和纵坐标轴标题为“金额”并设置纵坐标轴标题的文字方向为“竖排”；从图表中删除“奖金总计”项数据。2.对“各部门工资统计图”工作表进行美化，要求：

将图表标题设置为“楷体、红色、20号”；设置绘图区的填充颜色为“金色，背景2，深色50%”；设置图例的轮廓颜色为“蓝色”。任务实施3.5.2编辑并美化图表学生练习教师操作演示3.5.3数据透视表1.创建数据透视表2添加字段3.设置值字段数据格式4.设置值字段汇总方式5.排序和筛选字段项6.美化数据透视表相关知识1.在“月应发工资表”工作表中，根据每个部门男女职工的应发工资数据创建一个以部门为列，性别为行，对应发工资求和的数据透视表，并命名为“应发工资分析表”，完成后如图3-71所示。2．将数据透视表中的数据格式设置为货币型并将数据透视表中的值字段汇总方式设置为平均值，完成后如图3-72所示。3.将数据透视表按性别“降序”排列，再筛选出部门的应发工资平均值大于或等于“11000”的记录；将数据透视表应用“数据透视表样式中等深浅13”样式并添加边框，完成后如图3-73所示。。任务实施3.5.3数据透视表学生练习教师操作演示3.5.4创建数据透视图1.创建数据透视图2.移动数据透视图3.更改数据透视图类型相关知识1.在“月应发工资表”工作表中，根据每个部门男女职工的应发工资数据创建一个以部门为列，性别为行，对应发工资求和的数据透视图，并命名为“应发工资分析图1”，完成后如图3-74所示。2.将数据透视图移到一个新的工作表中并命令为“应发工资分析图2”；更新数据透视