信息技术基础（Windows10+WPSOffice2019）项目4 WPS电子表格应用操作

济源职业技术学院项目4WPS电子表格应用操作信息技术基础(Windows10+WPSOffice2019)学习目标（1）掌握WPS2019表格工作簿与工作表的基本操作。（2）掌握工作表的编辑和格式化操作。（3）熟悉WPS2019表格常用的公式和函数。（4）掌握WPS2019表格数据的排序、筛选、分类汇总等操作。（5）掌握WPS2019表格图表的创建与编辑。（1）能根据实际需求创建、编辑WPS2019表格工作簿与工作表。（2）能将工作表中的数据以指定的格式显示。（3）会使用WPS2019表格的公式和函数完成数据的统计。（4）能通过WPS2019表格的排序、筛选、分类汇总对数据进行处理与分析。（5）能将工作表中的数据以图表的形式显示出来。知识目标能力目标素质目标（1）通过对实际问题的解决，培养学生的信息素养，把已掌握的知识和技能当作工具，帮助自己学习或者工作。（2）通过项目小组的活动，培养学生的团队协作、友爱互助精神。（3）通过项目实践，培养学生规范操作、谨慎细致的职业习惯，务实高效的职业态度。（4）通过实训项目，培养学生自主学习和终身学习的意识，具备不断学习和适应发展的能力。☛☛☛目录任务4.1制作学生信息登记表制作学生成绩统计表任务4.2图书销售数据管理任务4.3制作职称结构统计图任务4.4销售数据分析任务4.5目录任务4.1

制作学生信息登记表制作学生成绩统计表任务4.2图书销售数据管理任务4.3制作职称结构统计图任务4.4销售数据分析任务4.5任务4.1制作学生信息登记表任务4.1制作学生信息登记表4.1.1认识WPS2019表格1.认识WPS2019表格工作界面任务4.1制作学生信息登记表4.1.1认识WPS2019表格2.认识工作簿、工作表、单元格和区域（1）工作簿在WPS2019表格中，一个表格文档就是一个工作簿。（2）工作表工作表是工作簿文件的一个组成部分，它是一个包含了行和列的二维表格。（3）单元格单元格是WPS2019表格工作簿的最小组成单位，也是用于存储数据的基本单位。（4）区域区域的引用常用左上角和右下角单元格的地址来标志，中间用“：”间隔。任务4.1制作学生信息登记表4.1.2工作簿与工作表的基本操作1.工作簿的建立与保存（1）WPS2019表格文件的建立单击“新建标签”按钮打开工作台界面选择“新建表格”→“新建空白表格”进行创建。（2）保存工作簿使用Ctrl+S快捷键，或按F12键，弹出“另存文件”对话框，选择保存的位置，输入文件名“学生信息登记表”，文件类型默认为“*.xlsx”，单击“保存”按钮即可完成工作簿保存。（3）关闭WPS2019表格工作簿单击要关闭的工作簿标签右侧的“关闭”按钮，可关闭当前工作簿。单击WPS2019窗口右侧的“关闭”按钮会关闭整个WPSOffice2019软件。任务4.1制作学生信息登记表4.1.2工作簿与工作表的基本操作2.管理工作表（1）插入新工作表单击工作表标签“Sheet1”右侧的“新建工作表”按钮，就可以在工作表“Sheet1”之后插入一张新的工作表。（2）选择多个工作表单击第一个工作表标签，按住“Ctrl”键，分别单击要选定的工作表标签。（3）重命名工作表双击工作表标签名“Sheet1”，输入“学生基本信息登记表”，按“Enter”键确认。（4）移动或复制工作表使用鼠标拖动工作表到某个工作表之前（或之后），可实现工作表的移动；如果在拖动的同时按住“Ctrl”键，可实现工作表的复制。（5）删除工作表将鼠标指针指向要删除的工作表，单击鼠标右键，在弹出的快捷菜单中选择“删除工作表”命令。（6）隐藏和取消隐藏工作表将鼠标指针指向要隐藏的工作表，单击鼠标右键，在弹出的快捷菜单中选择“隐藏工作表”命令，可将选定的工作表隐藏。（7）设置工作表标签颜色在工作表标签上单击鼠标右键，在快捷菜单中选择“工作表标签颜色”命令，然后在子菜单中选择所需颜色。任务4.1制作学生信息登记表4.1.2工作簿与工作表的基本操作3.编辑工作表（1）单元格与单元格区域的选择①单击某个单元格即可选中它。②按下鼠标左键不放，从第一个单元格拖曳到最后一个单元格，可以选中多个相邻的单元格区域；或单击要选择区域的第一个单元格，然后按住“Shift”键单击最后一个单元格，即可选中它们之间的所有单元格。③按“Ctrl”键可以选中多个不相邻的单元格区域。④按下“Ctrl+A”组合键，可以将工作表中的所有单元格选定。（2）选择、插入与删除行或列①用鼠标单击“行号”或“列号”，选择单行或单列。②按住鼠标左键不放，对行或列进行拖曳，可以选中多个相邻的行或列。③在行号或列号上单击鼠标左键的同时，按下“Ctrl”键，再单击其他行号或列号，可以选中多个不相邻的行或列。④在行号或列号上单击鼠标右键，在弹出的快捷菜单中选择“插入”命令，输入或选择行数/列数，可在选定的行或列前插入行或列。⑤选择要删除的行或列，单击鼠标右键，在弹出的快捷菜单中选择“删除”命令，删除选定的行或列。任务4.1制作学生信息登记表4.1.2工作簿与工作表的基本操作3.编辑工作表（4）选择性粘贴操作方法：选择需要复制的单元格，在选区中单击右键，在弹出的快捷菜单中选择“复制”命令。选定目标单元格，单击鼠标右键，在快捷菜单中单击“选择性粘贴”菜单命令，弹出“选择性粘贴”对话框，如图4-4所示。在“选择性粘贴”对话框中，选择所需粘贴的选项，单击“确定”按钮，退出对话框。（5）冻结窗格冻结首行首列：单击工作表中的任意单元格，选择“视图”选项卡，在功能区中单击“冻结窗格”按钮，在下拉菜单中选择“冻结首行”命令，此时纵向滚动工作表页面，第一行数据会始终保持在页面中显示。如选择“冻结首列”命令，当工作表横向滚动时，第一列数据会始终保持在页面中显示。冻结多行多列或指定区域：可选中某个单元格，此时在“冻结窗格”下拉菜单中就可以看到“冻结至第某行某列”的选项，可按需求进行设置。取消冻结：单击“冻结窗格”按钮，在下拉菜单中选择“取消冻结窗格”命令。图4-4

“选择性粘贴”对话框任务4.1制作学生信息登记表4.1.3编辑数据1.输入数据①

打开“学生基本信息登记表.xlsx”，在当前工作表“Sheet1”中，选中A1单元格，输入标题“学生基本信息登记表”。②在A2单元格中输入“学号”，并按“Tab”键，将B2单元格作为当前活动单元格，输入“姓名”，使用同样的方法依次输入表格标题行的内容，如图所示。③在A3单元格中输入学号“10220301”，按“Enter”键后，单元格中的内容默认为右对齐显示，默认格式为数字格式。因为学生学号不需要参与数学运算，需将其设置为文本类型。操作方法为：在“10220301”前输入西文单引号“’”；或先输入西文单引号“’”，然后输入学号“10220301”。④

将鼠标指针指向A3单元格的“填充柄”（位于单元格右下角的小方块），此时鼠标指针变为黑十字，按住鼠标左键向下拖动填充柄，拖至A22单元格时释放鼠标即可，此时A3:A22单元格区域中依次显示10220301～10220320的学生学号。1.输入数据⑤

选择B3单元格，在B3单元格中输入姓名“赵孟轲”，按“Enter”键。在B4单元格中输入姓名“郭晨旭”，按“Enter”键。用同样的方法依次输入其他学生的姓名。⑥选择C3单元格，在C3单元格中输入“男”，将鼠标指针指向C3单元格的右下角，当鼠标指针变为黑十字时，双击填充柄，将“性别”列的内容全部填充为“男”。⑦按住“Ctrl”键，依次单击性别需修改为“女”的单元格，选择完成后输入“女”，按“Ctrl+Enter”组合键，可将选中的单元格内容修改为“女”。⑧选中D3单元格，在D3单元格中输入出生日期“1991/5/21”，按“Enter”键。在D4单元格中输入出生日期“1990/9/16”，按“Enter”键。用同样的方法依次输入其他学生的出生日期。⑨依次输入学生的籍贯、联系电话和电子邮箱信息，完成表格数据的输入，将文档保存为“学生基本信息登记表.xlsx”，如图所示。任务4.1制作学生信息登记表4.1.3编辑数据1.输入数据任务4.1制作学生信息登记表4.1.3编辑数据2.设置单元格数据类型（1）数值型数据输入在WPS2019表格中，单元格中输入的数值自动向右对齐。如果单元格中以“#”显示，表示该单元格所在列的宽度不能足够显示数值，需调整所在列宽度或改变数字显示格式。（2）文本型数据输入文本型数据默认为左对齐。字符串文本可在选定的单元格中直接输入；对于学号、邮政编码、身份证号等不需要参与数学运算的数字信息，可将其设置为文本类型。在输入时需要先输入一个西文单引号“’”，然后再输入相关数字（3）时间和日期型数据输入日期型数据用形式“yy/mm/dd”表示，时间型数据用形式“hh:mm”表示，WPS2019表格会自动将输入的日期和时间型数据采用向右对齐显示。任务4.1制作学生信息登记表4.1.3编辑数据1.设置单元格对齐方式①在“学生基本信息登记表”中选中A1:G1单元格区域。②选择“开始”选项卡，在功能区中单击“对齐方式”选项组右下角的“对话框启动器”按钮，打开“单元格格式”对话框，在“对齐”选项卡中，将“水平对齐”方式设置为“居中”，“垂直对齐”方式设置为“居中”，在“文本控制”选项区域中选中“合并单元格”复选框，单击“确定”按钮，完成标题行设置。2．设置单元格字体格式①选中A1:G1区域，选择“开始”选项卡，在功能区中单击“字体”选项组右下角的“对话框启动器”按钮，打开“单元格格式”对话框，在“字体”选项卡中，将“字体(F):”设置为“微软雅黑”；“字形(O):”设置为“粗体”；“字号(S):”设置为“27”；颜色设置为“红色”，单击“确定”按钮即可。②选中区域A2:G22，将字体设置为“楷体”，字号设置为“12”，对齐方式设置为“水平居中”。任务4.1制作学生信息登记表4.1.4设置单元格格式3．设置单元格边框选中所有数据区域A2:G22，在选中的数据区域中单击鼠标右键，在弹出的快捷菜单中，选择“设置单元格格式”命令，打开“单元格格式”对话框。在“单元格格式”对话框中，选择“边框”选项卡，在线条样式中选择“双细线”，在“预置”栏内单击“外边框”按钮；然后在线条样式中选择“虚线”，在“颜色(C):”下拉列表中选择“红色”，在“预置”栏内单击“内部”按钮，如图所示，单击“确定”按钮完成边框设置。任务4.1制作学生信息登记表4.1.4设置单元格格式4．设置单元格图案选中A2:G2区域，在选区中单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“单元格格式”对话框，选择“图案”选项卡，如图4-14所示；单击“填充效果”按钮，在打开的“填充效果”对话框中，单击“颜色2”，选择主题颜色第一列第三行的“白色，背景1，深色15%”，“底纹样式”选择“水平”，变形效果1，如图4-15所示。单击“确定”按钮，为A2:G2区域设置渐变背景效果。任务4.1制作学生信息登记表4.1.4设置单元格格式图4-14

“单元格格式”对话框“图案”选项卡

图4-15

填充效果5．设置数字格式单击“数字”选项组右下角的“对话框启动器”按钮，在“设置单元格格式”对话框中，将数字分类设置为“数值”，在“小数位数”后选择“2”，即保留两位小数。小数位数也可通过“数字”选项组来快速设置。6．设置行高和列宽①选中标题行，选择“开始”选项卡，单击“行和列”按钮，在下拉菜单中选择“行高”项，打开“行高”对话框，在“行高”文本框中，输入“40”，单位“磅”，单击“确定”按钮，完成行高设置。②使用同样的方法，将其他数据信息行高设置为16磅。③选中G列，选择“开始”选项卡，单击“行和列”按钮，在下拉菜单中选择“最适合的列宽”项，对“电子邮箱”所在列的宽度进行自动调整。④选中A:F列，在选区中单击鼠标右键，在弹出的快捷菜单中，选择“列宽”命令。打开“列宽”对话框，在“列宽”文本框中，输入“13”，单位“字符”，单击“确定”按钮，完成列宽设置。任务4.1制作学生信息登记表4.1.4设置单元格格式7．设置工作表背景设置方法：选择“页面布局”选项卡，单击“背景图片”按钮，在打开的“工作表背景”对话框中，选择素材文件夹中名为“背景”的图片，单击“打开”按钮，完成工作表背景设置。8.设置单元格条件格式①

选中区域E3:E22，在“开始”选项卡中单击“条件格式”按钮，在弹出的下拉菜单中，选择“突出显示单元格规则”菜单下的“等于”命令。②在“等于”对话框中，左侧文本框内输入“河南省”，单击“设置为”右侧下拉列表按钮，选择“自定义格式”菜单，在弹出的“设置单元格格式”对话框中，将字体格式设置为“加粗、双下划线”。单击“确定”按钮，完成籍贯格式设置。任务4.1制作学生信息登记表4.1.4设置单元格格式完成条件格式的设置任务后，学生基本信息登记表效果如图所示。图4-22“条件格式”设置后效果任务4.1制作学生信息登记表4.1.4设置单元格格式1.页面设置①选择“页面布局”选项卡，在“页面设置”选项组中，单击右下角的“对话框启动器”按钮，打开“页面设置”对话框，选择“页面”选项卡，在“方向”选项区域选择“横向”单选按钮，纸张大小设置为“A4”，其他参数采用默认。②设置页边距，参数如图4-24所示。③

选择“页眉/页脚”选项卡，单击“自定义页脚”按钮，打开“页脚”对话框。在页脚“中部”文本框中插入页码、插入页数。④单击“确定”按钮，返回“页面设置”对话框，在“页眉/页脚”选项卡中设置页脚类型为“第1页，共？页”，单击“确定”按钮完成页脚设置。任务4.1制作学生信息登记表4.1.5页面设置

图4-24

设置“页边距”新增“自定义序列”操作方法：单击“文件”菜单，选择“选项”命令，打开“选项”对话框，在左侧列表中选择“自定义序列”，如图4-27所示。在“自定义序列”列表框中选择“新序列”，然后在“输入序列”列表框中依次输入26个英文字母序列，每输入一个字母后必须按“Enter”键结束，或者每一项之间用半角状态下的“,”分隔。输入完成后单击“添加”按钮，新定义的字母序列就会出现在“自定义序列”列表中。此时，在工作表单元格中任意输入一个英文字母，再用鼠标拖动填充柄填充，就可以实现自动填充英文字母序列了。任务4.1制作学生信息登记表4.1.6自定义填充序列

图4-27“自定义序列”对话框【能力拓展】实训1制作销货凭单【能力拓展】实训2制作员工信息登记表目录任务4.1

制作学生信息登记表制作学生成绩统计表任务4.2图书销售数据管理任务4.3制作职称结构统计图任务4.4销售数据分析任务4.5任务4.2制作学生成绩统计表任务4.2制作学生成绩统计表4.2.1成绩汇总①打开“各科成绩表”工作簿，在“语文成绩”工作表标签上单击右键，在弹出的快捷菜单中，选择“移动或复制工作表”，打开“移动或复制工作表”对话框，在该对话框中的工作簿下拉列表中选择“新工作簿”，并建立副本，将“各科成绩表”复制到一个新的工作簿1中，在新建工作簿1中，选择“文件”选项卡中的“另存为”命令，将该工作簿保存并命名为“学生成绩统计表.xlsx”。②将“各科成绩表”工作簿中“数学成绩”工作表中的数学成绩和“英语成绩”工作表中的英语成绩复制到“学生成绩统计表”工作簿中，并按照右图所示，增加其余行、列内容，完善学生成绩统计表的信息；将“语文成绩”工作表重命名为“学生成绩统计表”。任务4.2制作学生成绩统计表4.2.1成绩汇总③将A1:I1合并为一个单元格，文字居中对齐，字体为“华文中宋”，加粗，字号20；将A23:C23合并为一个单元格，文字居中对齐，字体加粗。④选中区域A2:I23，选择“开始”选项卡，在“对齐方式”选项组中，单击“垂直居中”、“水平居中”按钮，设置文字居中对齐；单击“表格样式”按钮，选择“表样式浅色9”，单击“确定”按钮。选中区域K3:M7，设置表格样式为“表样式浅色10”，内容水平居中；选中区域K12:M15，设置表格样式为“表样式浅色10”，内容水平居中；选中区域K20:M22，设置表格样式为“表样式浅色10”，内容水平居中。⑤将第一行的行高设置为40磅，内容垂直居中；第2至23行行高设置为20磅；A:M列宽为9字符。⑥在页面布局中，将页边距上下左右均设置为1.5厘米，将纸张方向设置为横向。完成效果如图4-34所示。任务4.2制作学生成绩统计表4.2.1成绩汇总图4-34

学生成绩统计表1．使用公式计算总成绩使用系统提供的函数或在单元格中输入公式可以实现许多复杂的运算，从而避免手动计算的复杂和易错问题，数据修改后公式的计算结果还可以自动更新。学生总成绩是各科成绩相加之和，下面利用公式来计算学生的总成绩，操作步骤如下。①选中G3单元格，输入“=D3+E3+F3”，按“Enter”键，计算出第一个学生的总成绩。②选中G3单元格，将鼠标指针移动到单元格的右下方，当鼠标指针的形状变为黑十字时，按住鼠标左键拖动至G22单元格，使用填充柄对公式进行复制，计算出所有学生的总成绩。③

设置单元格格式为“数值”，保留一位小数。任务4.2制作学生成绩统计表4.2.2创建公式2．公式中的运算符（1）算术运算符：算术运算符连接数字并计算结果，包括加（+）、减（-）、乘（*）、除（/）、幂（^）、百分号（%）、负号（-）等。（2）比较运算符：比较两个数据的大小并返回逻辑值真（True）或假（False），包括等于（=）、大于（>）、大于等于（>=）、小于（<）、小于等于（<=）和不等于（<>）。（3）文本运算符（&）：将多个字符连接成一个新的字符。（4）引用运算符：将运算区域合并运算，包括冒号（：）、逗号（，）和空格。在公式中如果同时使用多个不同类型运算符，将按照运算符的优先级顺序进行计算。冒号（：）→逗号（，）→负号（-）→百分号（%）→乘方（^）→乘法和除法运算（*和/）→加法和减法运算（+和-）→文本连接运算（&）→比较运算符（=、<、>、<=、>=、<>）。任务4.2制作学生成绩统计表4.2.2创建公式3．公式中的引用在编辑公式时，有时会引用单元格地址。在WPS2019表格中，根据引用的单元格与被引用的单元格之间的位置关系，将引用分为：相对引用、绝对引用、混和引用和跨工作表格引用。（1）相对引用。如计算总成绩时输入的公式“=D3+E3+F3”，其中“D3”“E3”“F3”均为相对引用的目标地址。（2）绝对引用。单元格中的绝对引用是指在行号和列标前写上“$”，如“$H$2”，它总是在指定位置引用单元格H2。（3）混合引用。混合引用是指单元格地址中既有相对引用，也有绝对引用。可以是绝对列和相对行（如$B6），也可以是绝对行和相对列（如B$6）。（4）跨工作表格引用。其形式为：“工作表名!单元格或单元格区域地址”。例如，“Sheet2!A1:D5”表示引用Sheet2工作表中A1:D5单元格区域中的数据。任务4.2制作学生成绩统计表4.2.2创建公式1．函数说明（1）求和函数：SUM()。（2）平均值函数：AVERAGE()。（3）最小值函数：MIN(

)（4）平均值函数：AVERAGE(

)（5）条件平均值函数：AVERAGEIF(

)（6）计数函数：COUNT(

)（7）条件统计函数：COUNTIF(

)任务4.2制作学生成绩统计表4.2.3使用函数（8）多区域条件统计函数：COUNTIFS(

)（9）条件求和函数：SUMIF(

)（10）判断函数：IF(

)（11）多条件判断函数：IFS(

)（12）RANK函数（13）TODAY函数（14）DATE函数2．使用函数（1）计算学生成绩。选中D23单元格，单击编辑栏中的“插入函数”按钮，打开“插入函数”对话框，在“选择函数”列表中选择“AVERAGE”函数，单击“确定”按钮。

弹出“函数参数”对话框，单击“折叠”按钮折叠“函数参数”对话框，拖动鼠标选择单元格D3:D22，按回车键或单击“还原”按钮返回“函数参数”对话框，单击“确定”按钮，计算出语文科目的平均成绩。任务4.2制作学生成绩统计表4.2.3使用函数

选中D23单元格，将鼠标移动到单元格的右下方，当鼠标变成实心的“+”形状时，按下鼠标左键并向右拖动，计算出数学和英语科目的平均成绩。设置数字格式为“数值”，保留一位小数。

选中L4单元格，单击编辑栏左侧的插入函数按钮，打开“插入函数”对话框，在查找函数文本框中输入“MAX”，然后在选择函数列表中选择“MAX”函数，单击“确定”按钮，弹出“函数参数”对话框，将数值1文本框中的数据地址设置为“D3:D22”，单击“确定”按钮，统计语文成绩最高分；使用相同的方法，分别在L5、L6、L7中统计出数学、英语、总成绩的最高分。

选中M4单元格，单击编辑栏左侧的插入函数按钮，打开“插入函数”对话框，在查找函数文本框中输入“MIN”，然后在选择函数列表中选择“MIN”函数；单击“确定”按钮，弹出“函数参数”对话框，将数值1文本框中的数据地址设置为“D3:D22”，单击“确定”按钮，统计语文成绩最低分；使用相同的方法，分别在M5、M6、M7中统计出数学、英语、总成绩的最低分。任务4.2制作学生成绩统计表4.2.3使用函数

选中L15单元格，单击编辑栏左侧的插入函数按钮，打开“插入函数”对话框，在“选择函数”列表中选择“COUNT”函数，单击“确定”按钮。弹出“函数参数”对话框，将值1文本框中的数据地址设置为“D3:D22”，单击“确定”按钮，计算全班总人数。

选中L13单元格，单击编辑栏左侧的插入函数按钮，打开“插入函数”对话框，将选择类别切换到“统计”，在“选择函数”列表中选择“COUNTIF”函数，单击“确定”按钮，弹出“函数参数”对话框，如图4-37所示。设置区域文本框中的数据范围为绝对引用C3:C22，即“$C$3:$C$22”，设置条件文本框中的内容为“C3”或输入“男”，单击“确定”按钮，统计班中男生人数。任务4.2制作学生成绩统计表4.2.3使用函数

将鼠标移动到L13单元格的右下角，拖动“填充柄”填充L14单元格，选中L14单元格，将编辑栏中的“男”改为“女”，完成女生人数的统计。

选中M21单元格，单击编辑栏左侧的插入函数按钮，打开“插入函数”对话框，将选择类别切换到“统计”，在“选择函数”列表中选择“COUNTIFS”函数，单击“确定”按钮，弹出“函数参数”对话框，如图4-38所示。设置区域1文本框中的数据范围为“$G$3:$G$22”，在条件1文本框中输入“K21”，设置区域2的数据范围为“$C$3:$C$22”，在条件2文本框中输入“男”，单击“确定”按钮，统计班中男生总成绩大于等于270的人数。任务4.2制作学生成绩统计表4.2.3使用函数图4-38

“COUNTIFS函数参数”对话框⑩

将鼠标移动到M21单元格的右下角，拖动“填充柄”填充M22单元格，选中M22单元格，将编辑栏中的“男”改为“女”，统计班中女生总成绩大于等于270的人数。⑪选中M13单元格，单击编辑栏左侧的插入函数按钮，打开“插入函数”对话框，将选择类别切换到“统计”，在“选择函数”列表中选择“AVERAGEIF”函数，单击“确定”按钮，弹出“函数参数”对话框。设置区域文本框中的数据范围为“$C$3:$C$22”，设置条件文本框中的内容为“男”，设置求平均值区域文本框中的内容为“$G$3:$G$22”，单击“确定”按钮，计算班中所有男生总成绩的平均分。⑫将鼠标移动到M4单元格的右下角，拖动“填充柄”填充M5单元格，选中M5单元格，将编辑栏中的“男”改为“女”，计算班中所有女生总成绩的平均分。任务4.2制作学生成绩统计表4.2.3使用函数（2）学生成绩等级划分。根据总成绩进行等级总评，将总成绩大于等于240的划分为“优秀”，总成绩小于240且大于等于180的划分为“合格”，总成绩小于180的划分为“不合格”。等级划分需用到IF函数来解决。

选中H3单元格，单击编辑栏左侧的插入函数按钮，弹出“插入函数”对话框，选择“IF”函数，单击“确定”按钮，弹出“函数参数”对话框，在测试条件文本框中输入条件“G3>=240”，真值文本框中输入“优秀”，如图4-40所示。任务4.2制作学生成绩统计表4.2.3使用函数图4-40

“IF函数参数”对话框

当G3单元格的数值小于240时，总成绩有“合格”、“不及格”两种情况，因此，需要在假值文本框中输入IF函数嵌套。将光标定位在假值文本框中，单击名称框中的IF函数，如图4-40框选位置，弹出嵌套函数参数的对话框，在函数参数对话框中输入参数，如图4-41所示。如果G3单元格的值大于等于180，则H3单元格的值为“合格”，否则H3单元格的值为“不合格”。

选中H3单元格，拖动“填充柄”向下填充，完成所有学生总成绩等级的评定划分。任务4.2制作学生成绩统计表4.2.3使用函数图4-41

“IF函数嵌套参数”对话框（3）计算学生排名为方便了解班中学生个人学习情况，可根据学生总成绩按从高到低进行排名，为评优评先做综合考虑。

选中I3单元格，选择“公式”选项卡，单击“插入函数”按钮，打开“插入函数”对话框，将“选择类别”切换到“全部”，在“选择函数”列表中选择“RANK”函数，单击“确定”按钮，弹出“函数参数”对话框。

在“函数参数”对话框中的Number文本框中输入“G3”，即指定参与排名的是G3单元格中的学生总成绩；在Ref文本框中输入“$G$3:$G$22”，即绝对引用G3到G22单元格区域的数据；Order文本框不输入任何数据，按默认的降序进行排名，如图4-42所示。单击“确定”按钮后，在I3单元格中可以显示出第一个学生的排名为“2”。任务4.2制作学生成绩统计表4.2.3使用函数图4-42

“函数参数”对话框

选中I3单元格，按住鼠标左键向下拖动填充柄到数据的最后一行，计算出所有学生的排名情况，效果如图4-43所示。选择“文件”选项卡中“保存”命令，保存“学生成绩统计表.xlsx”工作簿。图4-43“学生成绩统计表”效果图任务4.2制作学生成绩统计表4.2.3使用函数3．函数出错信息解决技巧（1）####错误原因：单元格中的数据太长，单元格公式所产生的结果太大，或是日期和时间格式的单元格做减法，出现了负值。解决方法：调整列宽，使结果能够完全显示。（2）#VALUE!错误原因：使用错误的参数或运算对象类型，或者当公式自动更正功能不能更正公式时，产生错误值。解决方法：确认公式或函数所需的运算符或参数是否正确，并且公式引用的单元格中包含有效的数值；确认数组常量不是单元格引用、公式或函数；将数值区域改为单一数值。修改数值区域，使其包含公式所在的数据行或列。（3）#DIV/0!错误原因：在公式中，除数使用了空单元格或是包含零值单元格的单元格引用。解决方法：修改单元格引用，或将单元格公式中除数设为非零的数值。（4）#NUM!错误原因：提供了无效的参数给工作表函数；公式的结果太大或太小而无法在工作表中显示。解决方法：确认函数中使用的参数类型正确；如果是公式结果太大或太小，就要修改公式。任务4.2制作学生成绩统计表4.2.3使用函数（5）#REF!错误原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到了由其他公式引用的单元格中。解决方法：更改公式或者在删除或粘贴单元格之后，立即单击“撤消”按钮，以恢复工作表中的单元格。（6）#NULL!错误原因：在公式中的两个范围之间插入了一个空格以表示交叉点，但这两个范围没有公共单元格。解决方法：取消两个范围之间的空格。（7）#N/A错误原因：在函数或公式中没有可用数值，从而产生了错误值。解决方法：如果工作表中某些单元格暂时没有数值，在这些单元格中输入“"#N/A"”，这样一来公式在引用这些单元格时将不进行数值计算，而是返回#N/A。任务4.2制作学生成绩统计表4.2.3使用函数【能力拓展】实训1

职工工资统计表【能力拓展】实训2学生竞赛成绩统计目录任务4.1

制作学生信息登记表制作学生成绩统计表任务4.2图书销售数据管理任务4.3制作职称结构统计图任务4.4销售数据分析任务4.5任务4.3

图书销售数据管理任务4.3图书销售数据管理4.3.1记录单的使用WPS2019表格中的记录单使用对话框的形式，将表格中的记录一条一条地显示，用户可通过记录单对表格中的记录进行添加、删除、查看或修改等操作。①打开“图书销售情况表.xlsx”工作簿，选中A1:F1单元格合并后居中，将“图书销售情况表”字体设置为“宋体、加粗、20磅”。②单击“快速访问”工具栏扩展按钮，在弹出的下拉菜单中选择“其他命令”，打开“选项”对话框；或单击“文件”菜单，选择“选项”命令，打开“选项”对话框，在左侧列表中选择“快速访问工具栏”。③在搜索框中输入“记录单”，选中列表中的记录单，单击添加按钮，将“记录单”命令添加到快速访问工具栏中，如图4-47所示；单击确定按钮完成添加。④单击“快速访问”工具栏中“记录单”按钮，打开“图书销售情况表”新建记录对话框。在该对话框中，用户可以对工作表中的数据记录进行添加、删除、查询等操作。单击“新建”按钮，在“新建记录”对话框中输入记录值“第2分店，图像处理，1，279，45.9”，将添加一条新记录。①选中F3单元格，在编缉栏中输入“=D3*E3”，按下“Enter”键，计算出第1分店销售图像处理图书的销售额。②选中F3单元格，将鼠标移动到单元格的右下方，拖动填充柄，计算出所有图书的销售额。③选中区域F3:F22，选择“开始”选项卡，将数字格式设置为“货币”，保留一位小数。④将鼠标移动至“图书销售情况表”工作表标签，单击鼠标右键，在弹出的快捷菜单中选择“创建副本”菜单命令，得到一张新表“图书销售情况表（2）”；将该工作表重命名为“分类汇总”。⑤重复步骤4的操作，在“图书销售情况表.xlsx”工作簿中，复制两次“图书销售情况表”工作表，分别将工作表重命名为“自动筛选”、“高级筛选”、“筛选满足条件的前几项”、“多级分类汇总”。任务4.3图书销售数据管理4.3.2计算销售额①选中“图书销售情况表”工作表中的数据区域A2:F22，选择“数据”选项卡，单击“排序”按钮的扩展按钮，选择“自定义排序”命令，打开“排序”对话框。在“主要关键字”下拉列表中选择“季度”，在“次序”下拉列表中选择“升序”。②单击“添加条件”按钮，在“次要关键字”下拉列表中选择“图书名称”，在“次序”下拉列表中选择“降序”。③单击“选项”按钮，打开“排序选项”对话框，选择“笔划排序”单选钮。单击“确定”按钮，返回“排序”对话框。④在“排序”对话框中，单击“确定”按钮，完成数据排序操作，排序结果如右图所示。

任务4.3图书销售数据管理4.3.3数据排序①

选择“分类汇总”工作表，按主要关键字“图书名称”进行排序。②选中数据区域A2:F22，选择“数据”选项卡，单击“分级显示”选项组中“分类汇总”按钮，打开“分类汇总”对话框，在“分类字段”下拉列表框中选择“图书名称”；“汇总方式”下拉列表框中选择“求和”；选择“选定汇总项”列表框中的“销售额”复选框，其他设置参数如图4-52所示。③单击“确定”按钮，完成分类汇总操作，结果如图4-53所示。此时表格中的数据自动分成了三级，在编辑区左侧单击数字按钮“1”、“2”、“3”，可以折叠或展开相应级别。任务4.3图书销售数据管理4.3.4分类汇总图4-52

“分类汇总”对话框任务4.3图书销售数据管理4.3.4分类汇总图4-53“分类汇总”结果图1.自动筛选自动筛选是对各个字段建立筛选，在“自动筛选”工作表中筛选出“计算机应用基础”图书销售数量大于等于200、小于300的记录。操作步骤如下。①选择“自动筛选”工作表，将光标定位在A2:F22数据区域中的任意一个单元格中，选择“数据”选项卡，单击“筛选”按钮，A2:F2区域内每个单元格右上角会出现一个筛选箭头，如图4-54所示。任务4.3图书销售数据管理4.3.5数据筛选图4-54

“自动筛选”图②单击“图书名称”字段右上角的筛选箭头，打开筛选条件对话框，选中“计算机应用基础”和“图书名称”复选框，单击“确定”按钮，筛选出所有“计算机应用基础”的图书销售记录。1.自动筛选③单击“数量”字段右上角的筛选箭头，打开筛选条件对话框，单击“数字筛选”菜单，选择“自定义筛选”命令。④在弹出的“自定义自动筛选方式”对话框中，设置数量的筛选条件为“大于或等于200与小于300”。⑤单击“确定”按钮，筛选出“计算机应用基础”图书销售数量大于等于200、小于300的记录，筛选结果如图4-58所示。任务4.3图书销售数据管理4.3.5数据筛选图4-58自动筛选结果图2.高级筛选自动筛选对各字段的筛选是“逻辑与”的关系，即同时满足多个条件。但若要实现字段间“逻辑或”的关系，即满足任一条件，则必须借助于高级筛选。在“高级筛选”工作表中，筛选出经售部门为“第1分店”或销售图书数量大于且等于230的销售记录，操作步骤如下。①选择“高级筛选”工作表，在H2:I4区域中输入筛选条件内容，如图4-59所示。②

选中数据区域A2:F22，选择“数据”选项卡，单击“筛选”按钮的扩展按钮，选择“高级筛选”命令，打开“高级筛选”对话框，在“方式”区域中选择“在原有区域显示筛选结果”，单击“条件区域”右侧的折叠按钮，拖动鼠标选择H2:I4区域，按回车键或单击“还原”按钮返回“高级筛选”对话框，如图4-60所示。任务4.3图书销售数据管理4.3.5数据筛选图4-59

高级筛选条件图4-60“高级筛选”对话框2.高级筛选③单击“确定”按钮，完成高级筛选，结果如图4-61所示。任务4.3图书销售数据管理4.3.5数据筛选图4-61“高级筛选”结果①

选择“筛选满足条件的前几项”工作表，将光标定位在数据区域A2:F22中的任意一个单元格中，选择“数据”选项卡，单击“筛选”按钮。②单击“销售额（元）”字段右上角的筛选箭头，选择“数字筛选”下拉菜单中的“前10项”命令。③在打开的“自动筛选前10个”对话框中，设置显示区域为“最大”“8”“项”，单击“确定”按钮，筛选出销售额排在前8位的记录，结果如图所示。任务4.3图书销售数据管理4.3.6筛选满足条件的前几项记录清单在进行分类汇总操作的过程中，用户可对同一字段进行多种方式的汇总。下面对“图书销售情况表”按各季度的销售额求和汇总，同时对各季度的销售数量进行统计，操作步骤如下。

选择“多级分类汇总”工作表，选中数据区域A2:F22。选择“数据”选项卡，单击“排序”按钮的扩展按钮，选择“自定义排序”选项，打开“排序”对话框，按照主要关键字“季度”升序和次要关键字“图书名称”的笔划降序进行排序。选择“数据”选项卡，单击“分类汇总”按钮，打开“分类汇总”对话框。在“分类字段”下拉列表中选择“季度”选项，“汇总方式”下拉列表中选择“求和”选项，在“选定汇总项”列表中选择“销售额”复选框。单击“确定”按钮，完成各季度销售额求和分类汇总操作。

选择“数据”选项卡，单击“分级显示”选项组中的“分类汇总”按钮，打开“分类汇总”对话框。在“分类字段”下拉列表框中选择“季度”，在“汇总方式”下拉列表框中选择“计数”，选中“选定汇总项”列表框中的“数量”复选框，取消选中“替换当前分类汇总”复选框。任务4.3图书销售数据管理4.3.7创建多级分类汇总清单单击“确定”按钮。即在各季度销售额求和分类汇总的基础上，完成了各季度销售数量的计数分类汇总，结果如图4-66所示。图4-66多级分类汇总任务4.3图书销售数据管理4.3.7创建多级分类汇总清单【能力拓展】实训学生成绩数据汇总1.实训目的①掌握工作表的格式设置。②掌握排序、分类汇总数据管理的方法。③掌握各种数据筛选的设置方法。【能力拓展】实训学生成绩数据汇总2.实训要求①打开“学生成绩表.xlsx”工作簿，选中Sheet1工作表中E3:E30区域，单击“条件格式”按钮的扩展按钮，选择“图标集”菜单项中的“其他规则(M)...”选项，在打开的“新建格式规则”对话框中，图标样式选择三色旗，当值大于等于90（类型数字）时图标为小红旗，当值小于90且大于等于60（类型数字）时图标为小黄旗，当值小于60时图标为红色❌。②将Sheet1工作表重命名为“学生成绩表”并创建副本，将“学生成绩表(2)”工作表重命名为“分类汇总”，对工作表内数据清单的内容按主要关键字“课程名称”笔划的递增次序、次要关键字“成绩”的递减次序进行排序。③对排序后的数据进行分类汇总，分类字段为“课程名称”，汇总方式为“平均值”，选定汇总项为“成绩”，汇总结果显示在数据下方。④再次进行分类汇总，汇总出每门课程的考试人数，汇总结果与平均值一同显示在数据下方，并且只显示到3级；将所有汇总成绩数字格式设置为“数值”，保留1位小数；将所有汇总的计数单元格数字格式设置为“数值”，无小数位，并清除条件格式。⑤为“学生成绩表”创建副本，并重命名为“自动筛选”，在“自动筛选”工作表中筛选出系别为“计算机”且课程名称为“人工智能”的记录。⑥为“学生成绩表”创建副本，并重命名为“自定义筛选”，选择“自定义筛选”工作表，筛选出系别为“自动控制”，且成绩大于90或小于等于60的记录。⑦为“学生成绩表”创建副本，并重命名为“高级筛选”，选择“高级筛选”工作表，筛选出系别为“计算机”或“数学”，且成绩大于75的记录，筛选条件写在G2开始的区域内，筛选结果显示在当前工作表G9开始的区域内。⑧以原文件名进行保存。【能力拓展】实训学生成绩数据汇总图4-71“高级筛选”结果目录任务4.1

制作学生信息登记表制作学生成绩统计表任务4.2图书销售数据管理任务4.3制作职称结构统计图任务4.4销售数据分析任务4.5任务4.4制作职工工资统计图图4-72

“职工工资统计图”结果任务4.4制作职工工资统计图4.4.1创建图表1.

图表说明表格文档中常见的图表的类型包括柱形图、折线图、饼图、条形图、面积图、组合图等。插入图表可通过“插入”选项卡中的“图表”选项组按钮实现，如“全部图表”、“插入柱形图”等。编辑图表可选中图表后，通过“绘图工具”、“文本工具”、“图表工具”选项卡进行操作。在“图表工具”选项卡中，我们可以进行“添加元素”、“快速布局”、“更改颜色”、“设置样式”、“更改类型”等操作。也可以通过图表区右上角的五个按钮快速完成图表编辑。任务4.4制作职工工资统计图4.4.1创建图表2.创建图表以“职工工资统计表”中的“职称”为横轴，以“所占比例”为纵轴，制作“职称结构统计图”三维饼图，并显示各职称所占比例。操作步骤如下。①打开“职工工资统计表.xlsx”工作簿，在“职工工资表”工作表中选中J3单元格，输入“=I3/”，单击插入函数按钮，选择COUNT函数，设置值1为“$D$3:$D$22”，综合运用公式和COUNT函数计算出“高工”占职工总人数的比例；设置数字格式为“百分比”，保留一位小数；将鼠标移动到J3单元格右下角，拖动填充柄填充至J5单元格，计算出各种职称占职工总人数的比例。②选中H2:H5区域，按住Ctrl键同时选中J2:J5区域；选择“插入”选项卡，单击“全部图表”按钮，打开“图表”对话框，如图所示；在左侧列表中选择“饼图”，右侧分类中选择“三维饼图”，预设图表中选择第一个，此时，编辑区将会生成一个三维饼图。任务4.4制作职工工资统计图4.4.1创建图表2.创建图表③将三维饼图插入到H7:L18区域内，拖动图表四角的圆形定位符可修改图表的大小；此时，图表的构成包括图表区、绘图区、图表标题、图例项，如图4-74所示。选中图表标题，将“所占比例”修改为“职称结构统计图”。④为“职工工资表”工作表创建副本，重命名为“组合图”，删除“职称结构统计图”。⑤

同时选中H2:H5、I2:I5和K2:K5数据区域，即“职称”列、“人数”列、“基本工资平均值（元）”列，单击“插入”选项卡中的“全部图表”按钮，打开“图表”对话框，在左侧列表中选择“组合图”，在自定义组合右侧设置“人数”系列图表类型为“折线图”，并勾选“次坐标轴”选项；设置“基本工资平均值”系列图表类型为“簇状柱形图”。点击“插入预设图表”按钮，将自定义组合图插入数据表中。图4-74

“职称结构统计图”图表效果任务4.4制作职工工资统计图4.4.1创建图表2.创建图表⑥将生成的自定义组合图插入到H7:L20区域内；将图表标题修改为“职称工资统计图”，效果如图所示。图4-76“职称工资统计图”图表效果任务4.4制作职工工资统计图4.4.2图表编辑1.编辑图表区内容①在“组合图”工作表中，选中“职称工资统计图”图表，单击图表区域右上角的“图表元素”按钮，选择“数据标签”，单击其右侧扩展按钮，选择“数据标签外”，在图表内添加数据标签，如图所示；添加数据标签操作也可单击“图表工具”选项卡中的“添加元素”按钮实现。任务4.4制作职工工资统计图4.4.2图表编辑1.编辑图表区内容②选中工程师工资的数据标签“6722.22”，将其移动到合适位置；选中工程师人数的数据标签“9”，将其移动到合适位置；单击“图表工具”选项卡中的“添加元素”按钮，在扩展菜单列表中选择“数据表”选项中的“显示图例项标示”，效果如图4-78所示。③单击“图表工具”选项卡中的“添加元素”按钮，在扩展菜单列表中选择“坐标轴”中的“更多选项...(M)”，会在工作表编辑区右侧显示出“属性”窗格，此时默认显示的是水平轴的相关选项。④单击“属性”窗格中“坐标轴选项”选项卡扩展按钮，在下拉菜单中选择“垂直（值）轴”，窗格中将显示出边界和单位等信息；将“边界”中的“最小值”设置为2000，“显示单位”设置为“千”。结果如图4-81所示。图4-81

编辑图表区内容结果图4-78显示图例项标示任务4.4制作职工工资统计图4.4.2图表编辑2.设置图表样式①选中“职工工资统计图”图表，选择“图表工具”选项卡，单击“更改颜色”按钮，选择彩色列表中的第三个。②单击图表区右上角的“设置图表区域格式”按钮，在右侧打开的属性窗格中，填充效果设置为“渐变填充”，并在色标卡尺中删除停止点2和3，将“亮度(B)”设置为100%，如图所示。任务4.4制作职工工资统计图4.4.2图表编辑2.设置图表样式③打开“开始”选项卡，将字体设置为“加粗”；在图表中选中图表标题，将字体设置为“楷体，18号，黑色”。④选中“绘图区”，通过拖动定位符修改绘图区大小；在右侧“属性”窗格中，设置填充效果为“纯色填充”，颜色为“白色，背景1”。效果如图所示。任务4.4制作职工工资统计图4.4.2图表编辑2.设置图表样式⑤打开“职工工资表”工作表，选中“职工工资统计图”图表，单击图表区域右上角的“图表样式”按钮，在样式分类中选择“样式2”，效果如图4-85所示。⑥单击“图表工具”选项卡中的“更改类型”按钮，在打开的“更改图表类型”对话框中选择“条形图”中的“簇状条形图”；单击“绘图工具”选项卡，将图表区高度设置为10厘米，宽度设置为15厘米；单击“文本工具”选项卡，将文本字号设置为11，加粗显示。完成效果如图4-86所示。保存工作簿。图4-85

使用预设样式设置图表效果图4-86

更改图表类型任务4.4制作职工工资统计图4.4.3修改图表数据源将“职称结构统计图”修改为“职称人数分布图”，操作步骤如下。①为“职工工资表”工作表创建副本，重命名为“职称人数分布图”，选中“职称结构统计图”图表，将图表标题改为“职称人数分布图”；单击“图表工具”选项卡，在预设样式列表中选择“样式5”。②单击“图表工具”选项卡中的“选择数据”按钮，弹出“编辑数据源”对话框；单击“图表数据区域”右侧折叠按钮，选择数据区域“H2:H5,I2:I5”，绝对引用，如图4-87所示。③单击“图例项(系列)”中的添加按钮，打开图4-88所示的“编辑数据系列”对话框，单击“系列名称”文本框右侧的折叠按钮，在工作簿中选中J2单元格（绝对引用）；单击“系列值”文本框，删除原内容，单击右侧的折叠按钮，在工作簿中选中J3:J5区域（绝对引用）；单击“确定”按钮，可为图表增加名为“所占比例”的系列。图4-88“编辑数据系列”对话框图4-87“编辑数据源”对话框任务4.4制作职工工资统计图4.4.3修改图表数据源④选中系列“所占比例”数据标签，单击绘图区右上角“图标元素”按钮，选择“数据标签”的“更多选项”命令，在右侧“属性”窗格“标签”选项卡中，取消勾选“类别名称(G)”，选择“值(V)”；选中系列“人数”数据标签，在右侧“属性”窗格中打开“标签”选项卡，取消勾选“类别名称(G)”，选择“值(V)”，“标签位置”选择“数据标签外(O)”。⑤单击绘图区右上角“图标元素”按钮，选中“数据表”选项，完成效果如图所示。将工作簿另存为“职工工资统计图.xlsx”【能力拓展】实训1“汽车销售统计图”制作【能力拓展】实训2“设备购置情况图”制作目录任务4.1

制作学生信息登记表制作学生成绩统计表任务4.2图书销售数据管理任务4.3制作职称结构统计图任务4.4销售数据分析任务4.5任务4.5