《信息技术基础实训（WPS Office）》课件 实训项目3 使用WPS表格

实训项目3

使用WPS表格信息技术基础目录CONTENTS任务3.1制作与美化课程表任务3.2统计分析学生成绩任务3.3综合实训——企业收入数据的统计与分析01PARTONE任务3.1制作与美化课程表【任务描述】用WPS表格处理软件制作与美化课程表：录入的信息包含课程、时间等，要求录入完整。录入信息的格式要设置正确。通过设置行高、列宽、边框和底纹等，保证表格美观，易于阅读。【任务描述】最终效果如下图所示【任务分析】启动WPS表格处理软件，新建空白表格。根据表格内容，依次录入标题和数据。选中相应的单元格区域，打开“单元格格式”对话框，设置单元格格式。设置对齐方式、行高列宽、边框和底纹等，排版美化工作表。【任务实现】1.

新建“课程表”工作簿双击桌面“WPSOffice”图标启动软件，或在“开始”菜单中选择“WPSOffice教育版”里的“WPSOffice”命令启动软件，选择“新建→表格→空白表格”命令，即可创建新的工作簿和工作表，单击快速访问工具栏中的“保存”按钮，弹出“另存为”对话框，设置文件名为“课程表”，保存至指定的目录，即完成创建。【任务实现】2.

录入数据打开“课程表”，选中A1单元格，参照素材文件“课程表.txt”，依次将素材的内容复制并粘贴到编辑区域。注意，在输入不同的列数据时，要设置对应的单元格格式如“制表时间”单元格需要设置单元格格式为“日期”，如右图所示：【任务实现】2.

录入数据录入数据后，表格效果如右图所示：【任务实现】3.

排版工作表选择A1:G1区域；单击“开始”选项卡的“合并居中”按钮，合并该区域单元格并将单元格里的内容居中显示；单击字号的下拉菜单，选择字号为26，并单击“加粗”按钮将所选字体内容加粗；单击字体的下拉菜单，选择字体为“宋体”；单击“开始”选项卡的“字体颜色”下拉菜单，选择“标准色”中的“蓝色”作为字体颜色。【任务实现】3.

排版工作表选择A3单元格；单击“字号”的下拉菜单，设置字号为12；分别选择C3:G3和A4:B11两个区域，设置字号为14。分别选择A2:B2和A12:B13两个区域，设置字号为11。选择C4:G11区域，设置字号为16；选择A2:G13区域，设置字体为“宋体”,并单击“加粗”按钮将所选字体内容加粗，选择“标准色”中的“蓝色”作为字体颜色。【任务实现】3.

排版工作表分别选择C4:C5、C6:C7、C8:C9、C10:C11、D4:D5、D6:D7、D8:D9、D10:D11、E4:E5、E6:E7、E8:E9、E10:E11、F4:F5、F6:F7、F8:F9、F10:F11、G4:G6、G8:G9、G10:G11区域，单击“开始”选项卡的“合并居中”按钮，合并该区域单元格并将单元格里的内容居中显示。分别选择A4:A7、A8:A11、B12:C12、B13:C13区域，单击“开始”选项卡的“合并居中”按钮，合并该区域单元格并将单元格里的内容居中显示。【任务实现】3.

排版工作表选择A3:B3区域，单击“开始”选项卡的“合并居中”按钮，合并该区域单元格并将单元格里的内容居中显示；选择A3:G3区域，单击“开始”选项卡的“填充颜色”下拉菜单，选择“巧克力黄，着色2，浅色60%”作为填充颜色，如右图所示。【任务实现】3.

排版工作表分别选择区域，单击“开始”选项卡的“合并居中”按钮，合并该区域单元格并将单元格里的内容居中显示。分别选择A4:A7、A8:A11、B12:C12、B13:C13区域，单击“开始”选项卡的“合并居中”按钮，合并该区域单元格并将单元格里的内容居中显示。【任务实现】3.

排版工作表选择第1行，单击“开始”选项卡的“行和列”下拉菜单，单击“行高”按钮，在弹出的对话框中设置行高为固定值35磅，再选择第3行到第11行以相同的方式设置行高为固定值40磅；选择第2行、第12行、第13行，单击“行和列”下拉菜单中“最适合的行高”按钮，让每行的行高自动适配其内容；【任务实现】3.

排版工作表选择第A列，单击“开始”选项卡的“行和列”下拉菜单，单击“列宽”按钮，在弹出的对话框中设置列宽为固定值9字符，再选择第B列、第C列到第G列以相同的方式分别设置列宽为固定值6字符、固定值20字符。【任务实现】3.

排版工作表分别选择B4:B11、C3:G3两个区域，单击“开始”选项卡的“水平居中”按钮，将文字居中对齐；选择A3:G11区域，右击选择“设置单元格格式”按钮，打开“单元格格式”对话框，单击“边框”选项卡，给表格添加框线，设置框线颜色为蓝色，所有外边框线条样式为双线型，内边框线条样式为单线型，A3单元格对角线线条样式为虚线型。【任务实现】3.

排版工作表排版美化后的效果如右图所示。【任务实现】4.

打印工作表单击“页面”选项卡的“纸张方向”按钮，选择纸张方向为“横向”。单击“页面”选项卡的“打印预览”按钮，查看文档打印时的效果，对打印做详细设置。设置完成后，单击打印设置中的“打印”按钮，即可打印文档，打印完成后单击“退出预览”按钮即可退出打印预览。如右图所示。【任务实现】4.

打印工作表单击“页面”选项卡的“纸张方向”按钮，选择纸张方向为“横向”。单击“页面”选项卡的“打印预览”按钮，查看文档打印时的效果，对打印做详细设置。设置完成后，单击打印设置中的“打印”按钮，即可打印文档，打印完成后单击“退出预览”按钮即可退出打印预览。如右图所示。【任务实现】5.保护工作簿单击“审阅”选项卡，在“保护”功能区单击“保护工作簿”按钮，弹出“保护工作簿”对话框，如右图所示。【任务实现】在对话框中输入密码“123”，单击“确定”按钮，会弹出“确认密码”对话框，如图3-1-8所示，输入相同的密码，开启工作簿的保护功能。5.保护工作簿【任务拓展】根据给定素材“公司员工信息表.xlsx”（如图所示），完成以下设置。1.设置文字样式。设置第一行文字的字体为仿宋，字号为16，并加粗处理。设置其余行文字的字体为仿宋，字号为14。2.设置表格边框和背景。设置表格线框为无线框。设置第一行单元格背景颜色为“钢蓝，着色1”，设置第2、4、6、8、10行单元格背景颜色为“白色，背景1”，设置第3、5、7、9、11行单元格背景颜色为“钢蓝，着色1，浅色80%”。3.设置单元格格式。设置第F列单元格格式为“日期”。4.设置最合适的行高和列宽。02PARTTWO任务3.2

统计与分析学生成绩任务描述统计出每个学生的总分、平均分，各班级的平均分、人数。根据公式计算出每个学生的评级。生成班级总平均分统计图用公式和函数进行统计计算：任务描述完成效果如下图所示：任务分析打开已经创建的“学生成绩分析表”工作表，添加“总分”、“平均分”和“评级”三列。运用SUM求和函数计算每个学生的总分。运用AVERAGE函数，计算每个学生的平均分。运用IF函数，计算每个学生的评级。运用AVERAGEIF函数，计算班级总平均分。运用COUNTIF函数，计算班级总人数。生成图表。【任务实现】1.

打开并编辑“学生成绩分析表”工作表双击“学生成绩分析表.xlsx”工作簿图标，打开“学生成绩分析表”工作表，在表格最后一列后继续添加数据，在H2、I2、J2单元格中分别输入标题“总分”、“平均分”和“评级”。设置第I列单元格数字格式为“数值”，小数位数2位。如图所示。【任务实现】2.

计算“总分”列选择H3单元格，单击“公式”选项卡中的“求和”按钮，调整数值区域为“D3:G3”，单击编辑栏前的“输入”按钮，或按【ENTER】键完成当前单元格的数值计算。如图所示。【任务实现】2.

计算“总分”列拖动填充柄计算其他学生的总分，如图所示。【任务实现】3.

计算“平均分”列选择I3单元格，单击“公式”选项卡中的“求和”菜单下的“平均值”按钮，调整数值区域为“D3:G3”，单击编辑栏前的“输入”按钮，或按【ENTER】键完成当前单元格的数值计算。如图所示。【任务实现】3.

计算“平均分”列拖动填充柄计算其他学生的平均分，如图所示。【任务实现】4.

计算“评级”列计算本列数值需要使用条件函数IF，该函数的功能是判断一个条件是否满足：如果满足则返回一个值，如果不满足则返回另外一个值。选中I3单元格，单击“公式”选项卡的“插入”按钮，选择函数类别为“逻辑”，选择“IF”函数，点击确定打开“函数参数”对话框，在测试条件框中输入“I3>=90”，在真值框中输入“"优秀"”，在假值框中输入“IF(I3>=75,"良好",(IF(I3>=60,"及格","不及格")))”，如图所示，按【ENTER】键完成学生评级计算，再拖动填充柄计算其他学生的评级。【任务实现】5.

计算“班级总平均分”列计算本列数值需要使用函数AVERAGEIF，该函数的功能是返回某个区域内满足给定条件的所有单元格的算术平均值。选中B20单元格，单击“公式”选项卡的“插入”按钮，选择函数类别为“统计”，选择“AVERAGEIF”函数，点击确定打开“函数参数”对话框，在区域框中输入“$A$3:$A$17”，在条件框中输入“A20”，在求平均值区域框中输入“$I$3:$I$17”，其中“$A$3:$A$17”、“$I$3:$I$17”代表对区域“A3:A17”和区域“I3:I17”的绝对引用，如图所示，按【ENTER】键完成班级总平均分计算，再拖动填充柄计算其他班级的总平均分。【任务实现】6.

计算“班级总人数”列计算本列数值需要使用函数COUNTIF，该函数的功能是计算区域中满足给定条件的单元格的个数。选中C20单元格，单击“公式”选项卡的“插入”按钮，选择函数类别为“统计”，选择“COUNTIF”函数，点击确定打开“函数参数”对话框，在区域框中输入“$A$3:$A$17”，在条件框中输入“A20”，其中“$A$3:$A$17”代表对区域“A3:A17”的绝对引用，如图所示，按【ENTER】键完成班级总人数算，再拖动填充柄计算其他班级的总人数。【任务实现】7.

插入分班级显示平均分柱状图选中A19:B22区域，选择“插入”选项卡中“全部图表”按钮，打开“图表”对话框，选择“柱形图”全部样式的第一种样式创建好一张柱形图，如图所示。【任务实现】8.

保存工作表单击快速访问工具栏中的“保存”按钮或使用Ctrl+S组合键，将修改过的表格保存至计算机中。单击标题栏的“关闭”按钮，关闭工作簿，退出WPS软件。【任务实现】完成效果如下图所示：【任务拓展】根据给定素材“业务员销售情况.xlsx”（如图所示），完成以下设置。1.在I列计算第一到第四季度销售总额。2.在J列计算四个季度平均销售额。3.在K列计算评级。平均销售额大于或等于30万元的为“优秀”，大于或等于15万元且小于30万元的为“良好”，小于15万元的为“一般”。4.在B15:B17区域计算三个营销部的平均销售额。5.在C15:C17区域计算三个营销部的人数。6.插入徐萍各季度销售额折线图。03PARTTHREE任务3.3综合实训企业收入数据的统计与分析【任务描述】企业收入数据的统计与分析：小刘作为某建筑业企业的会计，肩负着公司财务工作的重要任务。年底公司进行反思总结，部门经理交给小刘两项任务，一是要求小刘对公司职工全年收入数据进行统计分析，二是要求小刘对建筑业企业相关指标进行分析并做出图形化说明。接到任务后，小刘认真的对数据报表进行图形化制作，并完成了图表的美化。【任务描述】完成效果如下图所示：【任务分析】提取员工姓氏。计算员工月收入。计算员工工龄并隐藏截止日期列。设置各部门工资的分类汇总。制作建筑企业相关指标数据透视表。制作建筑业企业从业人员数组合图。批量替换数据。设置筛选。【任务实现】1.打开工作表双击“建筑企业收入数据表.xlsx”工作簿图标，打开“员工工资统计表”工作表。【任务实现】2.提取员工姓氏计算本列数值需要使用函数LEFT，该函数的功能是从一个文本字符串的第一个字符开始返回指定个数的字符。选中C2单元格，单击“公式”选项卡的“插入”按钮，选择函数类别为“文本”，选择“LEFT”函数，点击确定打开“函数参数”对话框，在字符串框中输入“B2”，在字符个数框中输入“1”，如图所示。【任务实现】2.提取员工姓氏按【ENTER】键完成员工姓氏统计，再拖动填充柄统计其他员工的姓氏，如图所示。【任务实现】3.计算员工月收入选择E2单元格，设置单元格格式为数值，小数位数为0，如图所示。【任务实现】3.计算员工月收入在E2单元格中输入“=D2/12”，如图所示，按【ENTER】键完成计算，再拖动填充柄计算其他员工的月收入。【任务实现】4.计算员工工龄并隐藏截止日期列计算本列数值需要使用函数DATEDIF，该函数的功能是计算两个日期间的天数、月数、或年数，其中"Y"代表计算时间段中的整年数，"M"代表计算时间段中的整月数，"D"代表计算时间段中的天数。选中H2单元格，单击“公式”选项卡的“插入”按钮，选择函数类别为“日期与时间”，选择“DATEDIF”函数，点击确定打开“函数参数”对话框，在开始日期框中输入“F2”，在终止日期框中输入“G2”，在比较单位框中输入“"Y"”，如图所示。按【ENTER】键完成员工工龄计算，再拖动填充柄计算其他员工的工龄。【任务实现】4.计算员工工龄并隐藏截止日期列选中G列，单击“开始”选项卡的“行和列”下拉菜单，单击“隐藏与取消隐藏”菜单中“隐藏列”按钮，即可隐藏截止日期列，如图所示。【任务实现】5.设置各部门工资的分类汇总选择A1:H17区域，单击“数据”选项卡中的“分类汇总”按钮，打开“分类汇总”对话框。在“分类汇总”对话框内的分类字段设置为“部门”，汇总方式为“求和”，选定汇总项为“年收入”、“月收入”，如图所示。【任务实现】5.设置各部门工资的分类汇总单击“确认”按钮，完成分类汇总设置。【任务实现】6.制作建筑企业收入数据表双击“建筑企业收入数据表.xlsx”工作簿图标，打开“建筑企业相关指标分析表”工作表。【任务实现】7.制作建筑企业相关指标数据透视表选中E7单元格，单击“插入”菜单中的“数据透视表”按钮，打开“创建数据透视表”对话框，选择单元格区域“A1:C37”，选择放置透视表位置“现有工作表”，如图所示。【任务实现】7.制作建筑企业相关指标数据透视表单击数据透视表区域，在“数据透视表字段”列表中勾选“指标”、“年份”、“数量”字段，并将“指标”拖动到“数据透视表区域”的行中，将“年份”拖动到“数据透视表区域”的筛选器中，将“数量”拖动到“数据透视表区域”的值中，如图所示。【任务实现】7.制作建筑企业相关指标数据透视表单击“求和项：数量”下拉菜单中的“值字段设置”按钮，选择用于汇总所选字段数据的计算类型为“平均值”如图所示。【任务实现】7.制作建筑企业相关指标数据透视表最后选择年份筛选器为“2020年”，完成设置，如图所示。【任务实现】8.