第3章 电子表格处理 实验31工作表的编辑与格式化 1. 实验目的 (1) 掌握工作簿的创建和保存方法。 (2) 掌握插入、删除和移动工作表的方法。 (3) 掌握重命名工作表和工作表标签颜色设置。 (4) 掌握工作表单元格的格式设置。 2. 实验示例 【任务1】创建工作簿，插入工作表，重命名工作表，设置工作表标签颜色。 【要求】 (1) 新建空白工作簿文件，将文档以“学号姓名”格式命名，如2023101010张三.xlsx。 (2) 在工作簿的最左侧插入一个空白工作表。 (3) 将新插入的工作表重命名为“学生信息表”。 (4) 设置“学生信息表”工作表标签颜色为“紫色(标准色)”。 【操作步骤】 1） 新建工作表 在桌面空白处右击，在弹出的快捷菜单中选择“新建”→“Microsoft Excel工作表”命令，即可创建空白工作簿，按题目要求输入文件名称，按Enter键确认。 2) 插入工作表 （1） 双击已创建的工作簿图标，打开工作簿。 （2） 右击工作表标签“Sheet1”，在弹出的快捷菜单中选择“插入”命令，弹出“插入”对话框，在“常用”选项卡中选择“工作表”，如图31所示，单击“确定”按钮。在工作表“Sheet1”的左侧出现插入的新工作表，工作表名称默认为“Sheet2”。 图31插入工作表 3) 重命名工作表 （1） 右击“Sheet2”工作表标签，在弹出的快捷菜单中选择“重命名”命令(图32)，或者双击“Sheet2”工作表标签，即可进入工作表标签编辑状态。 图32重命名工作表 （2） 输入工作表名称“学生信息表”，按Enter键，即可完成工作表的重命名。 4) 设置工作表标签颜色 右击“学生信息表”工作表标签，在弹出的快捷菜单中选择“工作表标签颜色”→“标准色”中的“紫色”命令，如图33所示。 图33设置工作表标签颜色 提示： 在设置工作表标签颜色过程中，如果“工作表标签颜色”级联菜单中没有合适的颜色，也可以通过“其他颜色”命令进行自定义设置。 【任务2】新建、删除和移动工作表。 【要求】 (1) 在工作表“Sheet1”的右侧插入一个空白工作表，重命名为“个人信息表”。 (2) 删除工作表“Sheet1”，并将“个人信息表”移动到“学生信息表”前。 (3) 将“学生信息表”作为副本移动到一个新的工作簿中，并将工作簿以“工作表的基本操作.xlsx”名称保存在桌面。 【操作步骤】 1） 插入工作表 选中工作表标签“Sheet1”，单击工作列表区右侧的“新工作表”按钮(图34)，在工作表“Sheet1”右侧插入一张新的工作表“Sheet3”，将工作表重命名为“个人信息表”。 2) 删除工作表 （1） 右击需要删除的工作表标签“Sheet1”，在弹出的快捷菜单中选择“删除”命令，如图35所示。 图34插入新工作表 图35删除工作表 （2） 同一工作簿中移动和复制工作表。 右击“个人信息表”工作表标签，在弹出的快捷菜单中选择“移动或复制”命令，弹出的“移动或复制工作表”对话框(图36)，选择“将选定工作表移至工作簿”下拉列表中的当前工作簿“2022101010张三.xlsx”，选择“下列选定工作表之前”列表框中的“学生信息表”，单击“确定”按钮，“个人信息表”工作表即可移动到“学生信息表”工作表之前。 提示： 在移动工作表过程中，选中图36中的“建立副本”复选框，则可实现工作表副本的复制，原始工作表不变。 3) 不同工作簿中移动和复制工作表 （1） 右击“学生信息表”工作表标签，在弹出的快捷菜单中选择“移动或复制”命令，弹出“移动或复制工作表”对话框，选择“将选定工作表移至工作簿”下拉列表中的“(新工作簿)”选项，选中“建立副本”复选框，如图37所示，单击“确定”按钮。打开一个新的工作簿，默认名称为“工作簿1.xlsx”，“学生信息表”已复制到该工作簿中。 图36同一工作簿中移动工作表 图37不同工作簿中复制工作表 （2） 选择“文件”→“另存为”→“浏览”。在弹出的“另存为”对话框中选择文件的保存位置，输入文件名，选择保存类型，单击“保存”按钮。 提示： 在移动工作表过程中如取消选中“建立副本”复选框，单击“确定”按钮，则可将“学生信息表”移动到一个新的工作簿中，原始工作簿中该表消失。 以下任务需使用素材“实验31.xlsx”，其中包括“各分公司销售统计表”和“图书销售情况统计表”两个工作表，如图38所示。 图38原始素材工作簿 【任务3】设置单元格格式。 【要求】 (1) 在“各分公司销售统计表”工作表第一行前插入新行，在A1单元格中输入“某公司产品销售额统计表”。 (2) 合并A1:E1单元格区域，并将单元格填充背景色设置为“灰色-25%,背景2”。 (3) 将表中全部数据字体设置为黑体、14号，对齐方式设置为水平居中和垂直居中； 为表格添加粗实线外边框，双实线内边框； 将表中全部数值内容设置为保留小数点后两位，添加人民币符号。 (4) 将A1:E6单元格区域设置为“自动调整列宽”。 【操作步骤】 1) 插入新行并输入表格标题 在第一行任意单元格上右击，在弹出的快捷菜单中选择“插入”命令，弹出“插入”对话框，选中“整行”单选按钮(图39)，单击“确定”按钮。在A1单元格中输入标题文字“某公司产品销售额统计表”。 提示： 插入新行的另一种方法是在行号1上右击，在弹出的快捷菜单中选择“插入”命令。 2) 合并单元格并设置填充背景色 （1） 选中A1:E1单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，在“对齐”选项卡的“文本控制”选项组中选中“合并单元格”复选框，如图310所示。 （2） 在“填充”选项卡的“背景色”中选择“灰色25%,背景2”，如图311所示。 图39“插入”对话框 图310“合并单元格”复选框 图311设置填充色菜单 提示： 在设置背景色时，当鼠标指针悬浮在“背景色”色块上时，并不显示该色块对应的颜色名称，只能通过“图案颜色”下拉列表中的色块来查看对应位置的色块颜色名称。 3) 设置单元格格式 （1） 选中A1:E6单元格区域，在“开始”选项卡→“字体”选项组将表中全部数据字体设置为黑体、14号。 （2） 选中A2:E6单元格区域，单击“开始”选项卡→“对齐方式”选项组右下角的对话框启动器按钮，弹出“设置单元格格式”对话框，在“对齐”选项卡中将对齐方式设置为水平居中和垂直居中； 在“边框”选项卡中设置粗实线外边框，双实线内边框，单击“确定”按钮。 （3） 选中B3:E6单元格区域，单击“开始”选项卡→“对齐方式”选项组右下角的对话框启动器按钮，弹出“设置单元格格式”对话框，选择“数字”选项卡，在“分类”列表框中选择“货币”，设置小数位数为2，在“货币符号(国家/地区)”下拉列表中选择货币符号“￥”，如图312所示。 图312设置数值内容 4) 设置“自动调整列宽” 选中A1:E6单元格区域，单击“开始”选项卡→“单元格”选项组中的“格式”下拉按钮，在弹出的下拉列表中选择“自动调整列宽”命令，如图313所示。 图313自动调整列宽 设置条件格式 【任务4】设置条件格式。 【要求】将低于100000元销售额的单元格设置为“浅红色填充”； 将高于400000元销售额的单元格的字体颜色设置为“蓝色”，填充背景色设置为“黄色”。 【操作步骤】 (1) 选中B3:E6单元格区域，单击“开始”选项卡→“样式”选项组中的“条件格式”下拉按钮，在弹出的下拉列表中选择“突出显示单元格规则”→“小于”命令，弹出“小于”对话框，设置数值低于100000元的单元格格式为“浅红色填充”，如图314所示，单击“确定”按钮。 图314设置条件格式 (2) 重复上述操作步骤，在“大于”对话框中将高于400000元的单元格设置“自定义格式”，在弹出的“设置单元格格式”对话框中设置字体颜色为“蓝色”，填充背景色为“黄色”。条件格式设置完成效果如图315所示。 图315“各分公司销售统计表”条件格式设置完成效果 【任务5】自定义主题单元格样式、自动填充及套用表格样式。 【要求】 (1) 设置“图书销售情况统计表”中标题“12月份计算机图书销售情况统计表”的单元格为“20% 着色3”的主题单元格样式。 (2) “序号”列自动添加“1 2 3 4…”的连续编号。 (3) 对“图书销售情况统计表”中数据内容套用表格格式“表样式浅色9”。 【操作步骤】 (1) 选中标题单元格，单击“开始”选项卡→“样式”选项组中的“单元格样式”下拉按钮，在弹出的下拉列表中设置主题单元格样式为“20% 着色3”，如图316所示。 (2) 选中A3单元格，输入数字“1”，鼠标指针移动到该单元格右下角，当光标图案变为填充柄“+”时，按住鼠标左键和Ctrl键，向下拖动鼠标指针至A15单元格，即可完成序号的自动填充。 (3) 选中A2:E15单元格区域，单击“开始”选项卡→“样式”选项组中的“套用表格格式”下拉按钮，在弹出的下拉列表中选择“浅色”组中的“表样式浅色9”，设置完成后的效果如图317所示。 图316设置主题单元格样式 图317“图书销售情况统计表”完成效果 3. 实验作业 本实验作业在素材“作业文档31.xlsx”中完成。 (1) 在第一行前插入一个新行，在A1单元格中输入表标题名称“某公司员工调薪工资表”。 (2) 将A1:D1单元格区域合并为一个单元格，内容水平居中，并设置标题文字为宋体、14号、加粗。 (3) 为数据区域添加单实线边框。 (4) 设置所有列为“自动调整列宽”。 (5) 设置调薪后工资格式为数值类型、保留小数点后1位、带千位分隔符。 (6) 设置调薪后工资大于3500元的为红色字体、浅绿色填充。 (7) 将A2:D17单元格区域自动套用表格格式“表样式浅色10”，包含标题。 (8) 将工作表命名为“工资表”，并将工作表标签颜色设置为标准红色。 (9) 修改工作簿名称，格式为“作业31+姓名+学号.xlsx”。 实验32公式与函数 1. 实验目的 (1) 掌握公式的使用方法。 (2) 掌握函数的使用方法。 (3) 掌握单元格地址的相对引用、绝对引用和混合引用。 2. 实验示例 以下任务需使用素材“实验32简单计算.xlsx”。 【任务1】公式计算。 【要求】 (1) 计算“竞赛成绩统计表”中的“总成绩”。 (2) 计算“销售情况统计表”中的图书“销售额”。 【操作步骤】 1) 计算“总成绩” (1) 选择“竞赛成绩统计表”工作表，单击E2单元格，输入“=B2*10%+C2*20%+D2*70%”或者“=B2*0.1+C2*0.2+D2*0.7”，按Enter键确认。 (2) 选中E2单元格，鼠标指针移动至本单元格右下角，当其变为填充柄“+”时，按住鼠标左键，将公式向下填充至E16单元格，或者双击“+”，自动向下填充至E16单元格，完成效果如图318所示。 图318“竞赛成绩统计表”完成效果 2) 计算“销售额” (1) 选择“销售情况统计表”工作表，单击F2单元格，输入“=D2*E2”，按Enter键确认。 (2) 双击F2单元格填充柄，完成“销售额”列数据的计算，完成效果如图319所示。 图319“销售情况统计表”完成效果 以下任务需使用素材“实验32函数.xlsx”。 【任务2】学生成绩分析。 【需求】利用函数计算“学生成绩分析表”中的“总分”“平均分”“最高分”“最低分”，同时将前5名的总分成绩用红色填充。“学生成绩分析表”原始素材如图320所示。 图320“学生成绩分析表”原始素材 【操作步骤】 1) 计算“总分”和“平均分” (1) 选中F3单元格，单击编辑栏左侧的“插入函数”按钮 (图321)，在弹出的“插入函数”对话框中选择函数“SUM”，单击“确定”按钮，弹出“函数参数”对话框，设置函数参数“Number1”为B3:E3，单击“确定”按钮，如图322所示。上述步骤完成后，F3单元格编辑栏中自动出现函数计算表达式，如图323所示。 图321“插入函数”按钮 图322插入函数操作流程 提示： 也可以单击“公式”选项卡→“函数库”选项组中的“插入函数”按钮，弹出“插入函数”对话框。 提示： 设置函数参数的方式有两种，一是通过鼠标拖动选中连续单元格区域，二是手动输入连续单元格区域。 (2) 将光标置于F3单元格右下角，当光标图案变为填充柄“+”时按住鼠标左键，将函数向下填充至F18单元格。 (3) 使用AVERAGE函数计算“平均分”，参照“总分”操作步骤完成全部学生平均分的计算。 2) 计算“最高分”和“最低分” (1) 使用MAX函数和MIN函数，分别计算B19单元格和B20单元格。 (2) 同时选中B19和B20单元格，按住填充柄，将公式向右填充至E20单元格。 提示： 任务2也可以通过单击“开始”选项卡→“编辑”选项组中的“自动求和”按钮完成4个函数的计算，如图324所示。 图323F3单元格函数计算表达式 图324自动求和按钮功能 3) 设置总分前5名的单元格为红色填充 (1) 选中所有总分成绩，单击“开始”选项卡→“样式”选项组中的“条件格式”下拉按钮，在弹出的下拉列表中选择“新建规则”命令。 (2) 弹出“新建格式规则”对话框，在“选择规则类型”列表框中选择“仅对排名靠前或靠后的数值设置格式”，“为以下排名内的值设置格式”设置为“前”和“5”(图325)。单击“格式”按钮，弹出“设置单元格格式”对话框，在“填充”选项卡中选择“背景色”中的标准红色，单击“确定”按钮，再单击“新建格式规则”对话框中的“确定”按钮，完成设置。 图325设置条件格式新建规则 提示： 本任务也可以单击“开始”选项卡→“样式”选项组中的“条件格式”下拉按钮，在弹出的下拉列表中选择“项目选取规则”→“前10项”，在弹出的对话框中进行修改设置。 【任务3】使用函数计算“名次”和“等级”。 【要求】 (1) 利用RANK函数，按总分的递减顺序在H列排出名次，注意单元格地址的绝对引用方法。 (2) 根据总分，使用IF函数计算等级，成绩等级对照如表31所示，注意IF函数的嵌套用法。 表31成绩等级对照 分数段等级 总分≥350优秀 280≤总分<350合格 总分<280不合格 (3) 使用公式和函数计算程序设计课程各分数段的“人数”“比例”“总分”，注意函数和公式的混合使用方法。 【操作步骤】 1) 计算“名次” 在H3单元格内输入图326所示函数计算表达式，按Enter键确认。使用H3单元格右下角填充柄将函数填充至H18单元格。 图326“名次”函数计算表达式 注意： “$”符号既可以手动输入，也可以按F4键切换，具体操作方法参照配套教材5.3.3节，此处不再赘述。 提示： 向下填充函数时，函数参数中相对地址的“行号”会自动递增。用来统计名次的总分地址范围是固定的，属于绝对地址引用，所以总分“行号”地址前必须添加“$”符号。 2) 计算“等级” 在I3单元格内输入图327所示函数计算表达式，按Enter键确认。按住I3单元格填充柄，将函数填充至I18单元格。 图327J3“等级”函数计算表达式 3) 计算程序设计课程各分数段的“人数”“比例”“总分” (1) 在L2单元格内输入函数“=COUNTIF(E3:E18,">=80")”，或者使用“插入函数”对话框完成计算。 (2) 在N2单元格内输入函数“=COUNTIF(E3:E18,"<60")”。 (3) 在M2单元格内输入函数“=COUNTIFS(E3:E18,"<80",E3:E18,">=60")”。 (4) 在L3单元格内输入函数“=L2/SUM($L$2:$N$2)”，将函数向右填充至N3单元格。 提示： 向右填充函数时，函数参数中的“列标”相对地址会自动递增。用来计算人数、比例的总人数是固定内容，属于绝对引用，所以总人数单元格“行标”地址和“列标”地址前必须添加“$”符号。 (5) 在L4单元格内输入函数“=SUMIF(E3:E18,">=80",E3:E18)”。 (6) 在N4单元格内输入函数“=SUMIF(E3:E18,"<60",E3:E18)”。 (7) 在M4单元格内输入函数“=SUMIFS(E3:E18,E3:E18,"<80",E3:E18,">=60")”。 任务3完成效果如图328所示。 图328“学生成绩分析表”完成效果 提示： 在函数计算表达式中，所有标点符号均为英文半角。 VLOOKUP函数 使用 【任务4】根据学号查找对应的姓名。 【要求】在“学号”列右侧插入一新列，列标题命名为“姓名”； 利用VLOOKUP函数，按照“姓名对照表”查找相应学号对应的姓名，将其填入“姓名”列中。 【操作步骤】 (1) 单击列编号“B”，显示黑色向下箭头，右击，在弹出的快捷菜单中选择“插入”命令，设置新插入列标题为“姓名”。 提示： 也可以选中需要插入列后面一列中的任意一个单元格，右击，在弹出的快捷菜单中选择“插入”命令，弹出“插入”对话框，选中“整列”单选按钮，完成插入新列。 (2) 选中B3单元格，在编辑栏中输入函数“=VLOOKUP(A3,姓名对照表!$A$2:$B$18,2,FALSE)”，或单击编辑栏左侧的“插入函数”按钮，在弹出的“插入函数”对话框中选择VLOOKUP函数，弹出“函数参数”对话框，填写对应参数框内容，如图329所示。 图329设置VLOOKUP函数参数 (3) 利用B3单元格填充柄，将函数向下填充至B18单元格，效果如图330所示。 图330填充“姓名”列 MID函数计算 【任务5】根据学号计算学生所在班级。 【要求】在“高等数学”列左侧插入一新列，列标题命名为“班级”(“学号”中第8个字符即是班级)，利用MID函数计算学生所在班级，显示形式如“1班”。 【操作步骤】 (1) 单击列编号“C”，显示黑色向下箭头，右击，在弹出的快捷菜单中选择“插入”命令，设置新插入列标题为“班级”。 (2) 选中C3单元格，在编辑栏中输入函数“=MID(A3,8,1)&"班"”，如图331所示。 图331 MID函数计算表达式 (3) 利用C3单元格填充柄，将函数向下填充至C18单元格，最终效果如图331所示。 提示： 在根据学号计算学生所在班级时，如果“学号”中的第7和第8两个字符为班级，则需要利用MID函数对“学号”进行截取。其方法如下： 选中C3单元格，在编辑栏中输入函数“=MID(A3,7,2)&"班"”。 3. 实验作业 本实验作业在素材“作业文档32.xlsx”中完成。 (1) 在工作表“运动会成绩统计表”中利用公式计算“总积分”列的内容(公式： “总积分=第一名项数×8+第二名项数×5+第三名项数×3”)。 (2) 按总积分的降序次序计算“积分排名”列的内容(利用RANK函数)。 (3) 在工作表“课程表”中，利用VLOOKUP函数填充“课程名”列信息，课程编号与课程名对照信息放置在“课程对照表”工作表中。 (4) 计算所有课程的总课时数和总学分，结果分别显示在C46和E46单元格中。 (5) 利用COUNTIF函数计算学分不小于4的课程门数，结果显示在F2单元格中。 (6) 根据课程编号，利用IF函数填充“课程类型”列，课程类型与课程编号的对应关系是： 课程编号的第一位(要求利用MID函数得到)表示课程类型，“6”表示“通修课程”，“4”表示“选修课程”，“9”表示“专业核心课程”，结果显示在D2:D45单元格区域中。 实验33数据图表 1. 实验目的 (1) 掌握图表的创建方法。 (2) 掌握图表的编辑方法。 2. 实验示例 以下任务需使用实验33素材“优秀支持率统计表”工作表，素材初始数据如图332所示。 【任务】根据“学生”和“优秀支持率”两列数据区域的内容建立三维饼图，完成效果如图333所示。 图332实验33素材初始数据 图333实验33任务完成效果 【要求】图表标题为“优秀支持率统计图”，图例位于左侧，为饼图添加数据标签并居中，将图表移动到工作表A12:E28单元格区域。 【操作步骤】 (1) 建立三维饼图。 选中A2:A8单元格区域，按Ctrl键的同时选中C2:C8单元格区域，单击“插入”选项卡→“图表”选项组中的“其他图表”下拉按钮，在弹出的下拉列表中选择“所有图表类型”命令，弹出“插入图表”对话框，在“饼图”选项卡中选择“三维饼图”，单击“确定”按钮。 (2) 修改图表标题。 单击图表标题区，将默认的图表标题“优秀支持率”修改为“优秀支持率统计图”。 图334修改图例位置 (3) 修改图例位置。 单击图表，单击图表区右上角的“图表元素”按钮，选择“图例”→“左”命令，设置图例位于左侧，如图334所示。 (4) 添加数据标签。 选中图表对象，单击“图表工具|设计”选项卡→“图表布局”选项组中的“添加图表元素”下拉按钮，在弹出的下拉列表中选择“数据标签”→“其他数据标签选项”，如图335所示，在右侧弹出的“设置数据标签格式”窗格的“标签选项”中设置“标签位置”为“居中”，如图336所示。 图335添加数据标签 图336设置数据标签格式 说明： 也可在图335中直接选择“居中”命令完成设置。 (5) 移动图表。 选中图表对象，先将图表对象左上角移动至A12单元格处，然后将鼠标指针放到图表对象右下角，当鼠标指针变成双向箭头时，按住鼠标左键的同时拖动鼠标至E28单元格处。 3. 实验作业 本实验在“作业文档33.xlsx”工作簿中的“所占比例”工作表中完成。 (1) 选取“所占比例”工作表中的“年份”和“所占比例”两列建立簇状柱形图，图表标题为“汽车销售统计图”，将图例置于底部。 (2) 设置图表区格式边框样式为“圆角”，图表区域图案填充5%的“蓝灰、文字2、淡色80%”的背景色； 将图表置于A15:F30单元格区域中。 实验34数据管理 1. 实验目的 (1) 掌握数据的排序方法。 (2) 掌握数据的筛选方法。 (3) 掌握数据的分类汇总方法。 (4) 掌握数据透视表的使用方法。 (5) 掌握数据表的合并计算方法。 (6) 掌握外部数据导入方法。 2. 实验示例 以下任务需使用素材“实验34.xlsx”和文本文件“蔬菜主要品种目录.txt”完成。 数据排序和 分类汇总 【任务1】数据排序和分类汇总。 【要求】对工作表“分类汇总”的数据清单内容(图337)进行分类汇总，分类字段为“系别”，汇总方式为“平均值”，汇总项为“考试成绩”，汇总结果显示在数据下方。 图337学生成绩表初始数据 图338“分类汇总”对话框 【操作步骤】 (1) 选中A2:A20单元格区域的任一单元格，单击“开始”选项卡→“编辑”选项组中的“排序和筛选”下拉按钮，在弹出的下拉列表中选择“升序”命令。 提示： 也可以单击“数据”选项卡→“排序和筛选”选项组中的“升序”按钮。 (2) 选中A1:F20单元格区域或单击数据区域的任意一个单元格，单击“数据”选项卡→“分级显示”选项组中的“分类汇总”按钮，在弹出的“分类汇总”对话框中设置分类字段为“系别”，汇总方式为“平均值”，仅选中汇总项“考试成绩”，单击“确定”按钮，如图338所示。分类汇总效果如图339所示。 提示： 如果需要隐藏明细项，查看各类别的汇总值，可以单击图339中左上角的级别按钮，或单击级别下方对应的展开(+)或折叠(-)按钮，如图340所示。 图339分类汇总效果 图340分类汇总隐藏部分明细项效果 【任务2】数据自动筛选和多关键字排序。 【要求】 (1) 对“筛选”工作表内数据清单的内容进行筛选，条件为数据库原理、操作系统、体系结构3门成绩均不小于60且平均成绩不小于75。 (2) 对筛选后的内容按主要关键字“平均成绩”降序和次要关键字“班级”升序进行排序。 【操作步骤】 1) 针对各科成绩的多字段自动筛选 (1) 选中数据区任意一个单元格，单击“数据”选项卡→“排序和筛选”选项组中的“筛选”按钮，各列数据标题单元格右侧自动出现下拉筛选按钮。 (2) 单击“数据库原理”列筛选按钮，在弹出的下拉列表中选择“数字筛选”→“大于或等于”，弹出“自定义自动筛选方式”对话框，在“数据库原理”“大于或等于”中输入“60”，单击“确定”按钮，如图341所示。 图341“自定义自动筛选方式”对话框 (3) 分别单击“操作系统”“体系结构”“平均成绩”列标题右侧筛选按钮，参考步骤(2)进行筛选操作。 2) 对筛选结果进行多关键字排序 (1) 选中数据区任意一个单元格，单击“数据”选项卡→“排序和筛选”选项组中的“排序”选项卡，弹出“排序”对话框(图342)，设置“主要关键字”为“平均成绩”，“排序依据”为“数值”，“次序”为“降序”。 图342“排序”设置 (2) 单击“添加条件”按钮，设置“次要关键字”为“班级”，“排序依据”为“数值”，“次序”为“升序”。单击“确定”按钮，效果如图343所示。 图343各科目筛选及排序后结果 提示： 进行排序的数据区域中不能包括已合并的单元格。 高级筛选 【任务3】高级筛选。 【要求】先在“高级筛选”工作表表头插入4行，筛选出系别为信息或者成绩大于80的记录，并将筛选结果保存在A37开始的单元格。(注： 筛选条件放在表头3行中) 【操作步骤】 1) 插入4行空行 （1） 选中第一行任意一个单元格，单击“开始”选项卡→“单元格”选项组中的“插入”下拉按钮，在弹出的下拉列表中选择“插入工作表行”命令。 提示： 也可以选择右键快捷菜单中的“插入”命令，在弹出的“插入”对话框中选中“整行”单选按钮，如图344所示，或者选中整行，右击，在弹出的快捷菜单中选择“插入”命令。 （2） 重复步骤（1）3次，完成表头前插入4个空白行。 提示： 也可以在完成步骤（1）后按3次F4键，完成表头前插入4个空白行。 2) 制作高级筛选条件区域 （1） 选中表头行A5:E5单元格区域，将其复制到A1:E1单元格区域。 （2） 在A2单元格内输入“信息”，在E3单元格内输入“>80”，完成高级筛选条件区域制作。如图345所示。 图344插入工作表行 图345高级筛选条件区域 3） 将筛选结果保存到A37开始的单元格 选中数据区域A5:E34，单击“数据”选项卡→“排序和筛选”选项组中的“高级”按钮，弹出“高级筛选”对话框，选中“将筛选结果复制到其他位置”单选按钮，单击“条件区域”文本框右侧折叠按钮，在工作表选择A1:E3单元格区域，单击“复制到”文本框右侧折叠按钮，选择工作表的A37单元格，如图346所示，单击“确定”按钮，高级筛选后的数据如图347所示。 图346“高级筛选”对话框 图347高级筛选后的结果数据 【任务4】数据透视表的使用。 【要求】为“数据透视表”工作表中的图书销售情况建立数据透视表，按行为“经销部门”，列为“图书类别”，数据为“数量(册)”求和布局，并置于现工作表的H2:L7单元格区域。 提示： 分类汇总只能针对一个字段进行分类和汇总，若要对两个字段同时进行分类汇总，则必须使用数据透视表(或数据透视图)功能。 【操作步骤】 (1) 插入数据透视表。 选中A2:F44单元格区域，单击“插入”选项卡→“表格”选项组中的“数据透视表”按钮，弹出“创建数据透视表”对话框，设置放置数据透视表的位置为“现有工作表”，并设置位置为H2:L7单元格区域，如图348所示，单击“确定”按钮。 图348“创建数据透视表”对话框 (2) 在“数据透视表字段”窗格(图349)中进行设置，拖曳字段区域的“经销部门”到行区域，拖曳“图书类别”字段到列区域，拖曳“数量(册)”字段到值区域，数据透视表完成效果如图350所示。 图349“数据透视表字段列表”窗格 图350数据透视表完成效果 注意： 默认数值汇总方式为“求和项”，如有需要，可以通过单击图349值区域右侧的下拉三角按钮，在弹出的下拉列表中选择“值字段设置”，在弹出的“值字段设置”对话框中可以修改值汇总方式，如图351所示。 图351“值字段设置”对话框 合并表格 【任务5】合并计算。 以下任务需使用素材“实验34.xlsx”中的4个工作表“第1周”~“第4周”中的原始数据进行完善后实现。 【要求】 (1) 在每个工作表的C、D、E、F 4个销量列的空白单元格中输入数字0。 (2) 分别计算每个工作表中4个销售部的周销量合计值，填入G列； 分别计算每个工作表中的周销售总额，填入H列。 (3) 分别将每个工作表中的数据区域定义为与工作表名相同的名称。 (4) 将4个工作表中的数据以求和方式合并到新工作表“月销售合计”中，合并数据自工作表“月销售合计”的A1单元格开始。 【操作步骤】 (1) 切换到工作表“第1周”，选中C2:F106单元格区域，单击“开始”选项卡→“编辑”选项组中的“查找和选择”下拉按钮，从弹出的下拉列表中选择“定位条件”命令，弹出“定位条件”对话框，选中“空值”单选按钮，单击“确定”按钮，即可选中所有空白单元格。在编辑栏中输入“0”，按Ctrl+Enter组合键确认，使所有选中的空白单元格均被填充数字0。采用同样方法，为“第2周”“第3周”“第4周”工作表的空白单元格填充数字0。 (2) 计算每个工作表中4个销售部的周销量合计值。 ① 单击“第1周”工作表标签，按住Shift键，再单击“第4周”工作表标签，同时选中这4张工作表(组成工作表组)。在G2单元格中输入或通过“插入公式”对话框构造公式“=SUM(C2:F2)”，按Enter键确认输入。在H2单元格中输入或通过“插入公式”对话框构造公式“=B2*G2”，按Enter键确认输入。 ② 右击“第1周”~“第4周”任一工作表标签，从弹出的快捷菜单中选择“取消组合工作表”命令。切换到“第1周”工作表，选中G2:H2单元格区域，双击它们的填充柄，填充本工作表G、H列后续各行单元格； 分别对“第2周”~“第4周”的工作表做同样操作。 (3) 切换到“第1周”工作表，选中A1:H106单元格区域，删除编辑栏左侧名称框中的任何内容，并输入“第1周”，按Enter键确认。按照同样方法，为其他3周的工作表的数据区域定义名称“第2周”“第3周”“第4周”。 提示： 也可选中A1:H106单元格区域，右击，在弹出的快捷菜单中选择“定义名称”命令，在弹出的“新建名称”对话框中设置区域名称为“第1周”，如图352所示。 (4) 合并表格数据。 ① 单击工作表标签右侧的“新工作表”按钮，新建一张工作表。双击新工作表标签，将其重命名为“月销售合计”。 ② 选中“月销售合计”工作表的A1单元格，单击“数据”选项卡→“数据工具”选项组中的“合并计算”按钮，在弹出的“合并计算”对话框中设置“函数”为“求和”。将插入点定位到“引用位置”框，选择“第1周”工作表的数据区域A1:H106，单击“添加”按钮。采用同样方法，添加其他3周工作表的数据区域到“所有引用位置”列表框中，选中“首行”和“最左列”复选框，单击“确定”按钮，如图353所示。 图352“新建名称”对话框 图353“合并计算”对话框 提示： 也可以在图353的“引用位置”框输入已命名好的区域名称“第1周”，单击“添加”按钮。同理，将其他3个命名区域添加到“所有引入位置”框。 ③ 适当调整各列列宽，使其完整显示数值。4张表合并后的效果如图354所示。 图3544张表合并后的效果(部分) 【任务6】获取外部数据。 【要求】在工作簿最右侧创建一个名为“品种目录”的工作表。将以制表符分隔的素材文本文件“蔬菜主要品种目录.txt”自A1单元格开始导入“品种目录”工作表中，要求“编号”列保持原格式。 【操作步骤】 (1) 单击工作表标签区最右侧的“新工作表”按钮，新建一张工作表。双击新工作表标签，输入名称“品种目录”，按Enter键确认。拖曳该工作表标签，使其位于所有工作表的右侧。 (2) 选中“品种目录”工作表的A1单元格，单击“数据”选项卡→“获取外部数据”选项组中的“自文本”按钮，弹出“导入文本文件”对话框，选择素材文本文件“蔬菜主要品种目录.txt”，单击“导入”按钮。 (3) 弹出“文本导入向导”对话框，第1步，选中“分隔符号”单选按钮，单击“下一步”按钮； 第2步，选中“Tab键”复选框，单击“下一步”按钮； 第3步，在下方“数据预览”中选择“编号”列，在上方选中“文本”单选按钮，单击“完成”按钮，如图355~图357所示。 (4) 在弹出的“导入数据”对话框中设置数据的放置位置为“现有工作表”“=$A$1”，单击“确定”按钮，如图358所示。 图355“文本导入向导”对话框第1步 图356“文本导入向导”对话框第2步 图357“文本导入向导”对话框第3步 图358“导入数据”对话框 3. 实验作业 本实验作业在素材“作业文档34.xlsx”中完成。 (1) 将Sheet1工作表复制4份，并将这些工作表依次命名为“分类汇总”“筛选”“高级筛选”“数据透视表”。 (2) 在“分类汇总”工作表中利用“总成绩”进行降序排序，利用“专业”分类汇总出实验成绩和总成绩的最低分。 (3) 在“筛选”工作表中筛选出实验成绩大于15分、总成绩大于90分的数据。 (4) 在“高级筛选”工作表中筛选出考试实验成绩不小于80分或实验成绩不小于18分的数据。 (5) 为“数据透视表”工作表中的数据建立数据透视表，数据透视表放置到一个名为“成绩透视表”的新工作表中，透视表行标签为“系别”，对考试成绩求最小值、实验成绩求最大值、总成绩求平均分。 实验35综合应用——Excel综合实训 使用给定素材完成以下任务，具体要求如下。 (1) 将“Sheet1”工作表的A1:G1单元格区域合并为一个单元格，内容水平居中； 计算“总计”列和“专业总人数所占比例”列(百分比型，保留小数点后2位)的内容； 利用条件格式的“绿、黄、红”色阶修饰G3:G10单元格区域。 (2) 参看素材中的样张1图，选择Sheet1工作表“专业”和“专业总人数所占比例”两列数据区域的内容建立三维饼图，图表标题为“专业总人数所占比例统计图”，图例位置靠左，数据标签内。将图表移动到工作表A12:G28单元格区域，将工作表命名为“在校生专业情况统计表”。 (3) 在“误差测量表”工作表中计算实测值与预测值之间误差的绝对值，并放置于“误差(绝对值)”列。评估“预测准确度”列，评估规则为： “误差”低于或等于“实测值”10%的，“预测准确度”为“高”； “误差”大于“实测值”10%的，“预测准确度”为“低”(使用IF函数)。设置“预测准确度”列的单元格样式为“玫瑰红40%着色2”。 (4) 在“销售情况表”工作表中计算“已销售出数量”(已销售出数量=进货数量-库存数量)，计算“销售额(元)”，利用RANK函数计算“销售额排名”(按销售额降序排列)； 利用单元格样式的“标题2”修饰表标题，利用“输出”修饰A2:G14单元格区域； 利用条件格式将“销售排名”列内容中数值不大于5的数字颜色设置为红色。 (5) 在“成绩表”工作表中计算“平均成绩”列的内容(数值型，保留小数点后2位)，计算一组学生人数(置于G3单元格内，利用COUNTIF函数)和一组学生平均成绩(置于G5单元格内，利用AVERAGEIF函数)。 (6) 对“计算机动画技术成绩单”工作表内数据清单的内容进行筛选，条件如下： 实验成绩15分(含15分)以上，总成绩在80~100分(含80分和100分)的数据。 (7) 参考素材中的样张2图，对“保险”工作表中的险种进行分类汇总，分类字段为“险种”，汇总方式为“求和”，汇总项为“保费收入”。 (8) 在“高级筛选”工作表的数据清单前插入4行，条件区域设在A1:F3单元格区域，条件如下： 高等数学大于80分，或者英语大于80分的记录，在原有区域显示筛选结果，工作表名不变。 (9) 对“图书销售表”工作表的内容建立数据透视表，行标签为“经销部门”，列标签为“图书类别”，求和项为“销售额(元)”，并置于现工作表的H2:L7单元格区域，参看素材中的样张3图。 (10) 原名保存文档后，将保存后的文档另存为“结果.xlsx”。