基础巩固 9（Excel 表格处理） 一、考点梳理 1.Excel 表格界面 2.电子表格软件 Excel 的主要功能是存储和处理数据。根据应用需求，可通过计算、排序 、筛选、图表等 方法对数据进行分析。Excel 文件格式（扩展名）为 xlsx。 Excel 常用快捷工具图标含义： 3.公式的编辑：必须等号“=”开头，乘号为*，除号为/，括号（）。乘号不能省略，不能用中括号[]。比如 公式“=（（A2+B2）/2）-5”，不能写成“=[（A2+B2）/2]-5”。如果单元格格式被设置成“文本”，输入 公式会无效（被当做文本字符）。 4.Excel 常用的函数有 Sum 、 Average 、Max、Min 等。多个不连续区域中间用逗号分隔，比如 “=Sum(B2:D2,F2)” 5.编辑的公式引用的空白单元格，空白单元格当做 0 处理。比如公式“=A2/B2”，如果 B2 为空白单元格， 此时除数为 0，会出现“#DIV/O!”错误提示；而函数计算时会自动忽略非数字类型单元格和空白单元格。 如下图，在 E2 输入公式“= Average(B2:D2)”计算结果为 33。 6.选择性粘贴 ① 粘贴（公式和格式）②粘贴值（计算结果）③粘贴公式④行列置换⑤粘贴格式⑥粘贴链接 7.Excel 常见错误 8.Excel 相对引用与绝对应用 B$1 -> B$1 B$1 -> C$1 9.数据排序：将对应数据区域安照相关条件进行排序，注意主要关键字和次要关键字。 10.数据筛选：分自动筛选和高级筛选。筛选功能是将满足一定条件的部分数据保留下来，不满足条件的 数据隐藏起来。注意相关关键词：不足、超过、超出、不够以及 10 个最大的值的使用。 11.数据分类汇总：对数据分析研究的方法，可使数据按照不同的类别进行求和、求平均值及个数等。 12.图表的创建及应用：数据区域、数据分析及图表编辑和修改。常见图表类型：柱形图、折线图、条形 图、饼图、散点图、面积图等。 13.图表和数据区域中内容 改变图表区域中某个单元格内容，图表对应内容也会自动更新。图表数据区域内的数据排序后， 图表中也会跟着变化。筛选后，不符合筛选条件的信息图表上不会显示。图表数据区域外插入整行或 整列，图表不会变化。 【注意】 （1）公式必须以等号开头； （2）使用自动填充的操作要注意是否需要使用绝对引用； （3）排序区域必须是单一连续区域，不能选择合并单元格 （4）数据筛选时不同列之间的条件没有关联，也不分先后顺序，最终结构为各列条件筛选后的交集； （5）图表数据源要注意根据图例确定是否包含标题区域，且最终的图表数据源应该是一个左右、上下对 称的区域。如 A2:B7,E2:F7，避免出现形如 A3:B7,E2:F7 的答案。 二、常见错题 例 1.小俞用 Excel 软件处理查询得到的“近几年全国居民人均收入支出统计”数据，如图 a 所示： 图a 请回答下列问题： （1）参照 J3 单元格的公式计算 J7 单元格的值，并用自动填充功能完成区域 J8:J14 的计算，则 J7 单元格 中的公式为 。 （2）若要对类别为“支出”的数据按“2020 年”进行降序排序，则排序的数据区域是 （3）根据图 a 中数据制作的图表如图 b 所示，以下说法正确的是： 。 （多选，填字母）。 图b A. 近几年，衣、食、住三项支出中，居住消费支出的增长是最缓慢的 B. 选中 A1:J1 单元格，执行“合并后居中”操作后，该单元格名称为 A1 C. 若在图 a 所示工作表中，以“类别”为支出、“2020 年”为最大 3 项进行筛选，则筛选出的记录有 3 条 D. 若在图 a 所示工作表的 C 列前插入一列，并输入 2013 年的相关数据，J 列的数值不会发生改变 （注：全部选对的得 2 分，选对但不全的得 1 分，不选或有选错的得 0 分） 【答案】 （1）=I7/SUM(I$7:I$14)*100 或 =I7/SUM($I$7:$I$14)*100 （2）A7:J14 或 A7:I14 （3）BD 【解析】（1）本题考查单元格绝对应用和单元格计算。从编辑栏可以看出是计算 2020 收入占各指标占 比，从 I7：I14 开始计算支出占比，因此需要对支出和进行绝对应用，=I7/SUM($I$7:$I$14)*100，由 于按列填充，因此 I 列可 以不加$。（2）本题考查数据排序范围选择。在选择排序范围是，范围尽可能的大，但是要去掉不需要的 区间和合并单元格的部分。本题针对支出部分数据进行排序，因此只要选择支出部分数据区域 A7:J14。 （3）A 项：从图表可以分析得到食品烟酒消费支出增长缓慢； B 项：合并单元格后的名称是合并区域的 第一个单元名称，所以对的； C 项：先以“类别”为支出以“类别”为支出、“2020 年”为最大 3 项进行筛选， 则筛选出的记录有 0 条，C 项是错误；D 项：在 c 列前插入一列，C 列后面的列好会自动变化，公式也是 发生变化，不影响计算结果。 例 2.收集某中学两个班级体质健康测试数据，并对部分项目用 Excel 软件进行数据处理，如图 a 所示。 图a 请回答下列问题： （1）区域 G3：G82 的数据是通过公式计算得到的，在 G3 单元格中输入公式 ，再使用自动填 充功能完成区域 G4:G82 的计算。（提示：体重指数=体重/身高 2，单位：千克/米 2）。 （2）对区域 A2:G82 进行相关数据分析，下列说法正确的是 （多选，填字母）。 A.更新 C 列身高数据，G 列体重指数的值会随之改变 B.为了将女生身高最高的数据显示在当前工作表第 3 行，可选择身高为主要关键字、性别为次要关 键字均按降序排序 C.为了显示男生 1000/800 米得分大于 85 的数据，可先对“性别”进行筛选，选择的值为“男”，再 对“1000/800 米得分”进行筛选，条件为大于 85 D.为了显示女生肺活量得分最高的 3 位学生数据，可先对“性别”进行筛选，选择的值为“女”，再 对“肺活量得分”进行筛选，选择“10 个最大的值”并显示最大的 3 项 （注：全部选对的得 2 分，选对但不全的得 1 分，不选或有选错的得 0 分） （3）对图 a 中的数据进行相关统计，结果放在新工作表中，如图 b 所示。根据图 b 中的数据制作的图表 如图 c 所示。创建该图表的数据区域是 图b 。 图c 【答案】 （1） = D3/(C3 * C3) * 10000 或 =D3/C3^2 * 10000 或 等 价 表 达 式 （ 2 ） AC （3）A2:A6,D2:E6 【解析】 （1）直接根据提示信息书写公式，书写方式比较多，等价表达式均可。特别注意单位换算（厘米、 米）。 （2）A.G 列数据由包含 C 列单元格的公式计算得到，故更新 C 列数据，G 列值会随之改变。B.身高为主 要关键字降序，则身高最高的同学（不管男女）会被排在最前面，只有身高相同才会按次要关键字性别降 序。主次要关键字交换，才能实现题目要求。C.多种筛选条件，分步对每个条件进行单独筛选，各自筛选 结果的交集，即是筛选最终结果。D.分析同 C。需要注意的是，后续筛选仍然是对整列而非上一筛选结果 进行的。该选项的操作结果是“既是女生、肺活量又排在全体前三的”，而非“女生中肺活量排名前三的”。如 果肺活量得分最高的 3 项全是男生，则该操作筛选结果为 0 条数据。 （3）该题比较常规，根据图表横坐标和图例即可写出数据区域。特别注意每列单元格数量要一致，不要 漏掉 A2。 三、精选练习 1.小吴收集了近三年全国工业产能利用率的数据，并用 Excel 软件进行数据处理，如第 1 题图 a 所示。 第 1 题图 a （1）如第 1 题图 a 所示，区域 C5:C18 的数据由 C4 单元格公式自动填充得到，且结果正确。为了计 算 2019 和 2020 年主要行业的产能利用率排名，小吴将 C4:C18 区域复制到 E4:E18 和 G4:G18 区域，但发现计算结果有误，则 C4 单元格的公式应修改为 ，并重新进行自动填充 和复制操作。 （2）根据第 1 题图 a 中数据制作的图表如第 1 题图 b 所示，则创建该图表的数据区域为 A3:B5, 。 第 1 题图 b （3）为了获得名称包含“制造”的主要行业中，2018 年“产能利用率(%)”最大的 5 项数据，并显示在操 作结果的最前面（紧邻第 3 行），一种可行的操作是：选择数据区域 A3:G18 后，对该区域 （选填 2 个序号，按操作顺序）。 ① 按“主要行业”进行文本筛选，条件为：包含“制造”②按“主要行业”进行文本筛选，条件为：等于“*制造” ③ 按 2018 年的“产能利用率(%)” 进行筛选，选择“10 个最大的值”并显示最大的 5 项 ④ 以 2018 年的“排名”为关键字进行降序排序 ⑤以 2018 年的“产能利用率(%)”为关键字进行降序排 序 （注：全部选对的得 2 分，选对但不全的得 1 分，不选或有选错的得 0 分） 2.用 Excel 软件对“浙江省新高考体艺类招生计划表”的相关数据进行处理，如第 2 题图 a 所示。 第 2 题图 a 请回答下列问题： （1）区域 M3:O4 的数据是通过公式计算得到的，在 M3 单元格（计算 2020 年艺术类计划人数占总计 的百分比）中输入公式 ，先自动填充至 M4，再使用自动填充功能完成区域 N3:O4 的计算（提示： 各类占比=该类合计人数/总计）。 （2）根据第 2 题图 a 中数据制作的图表如第 2 题图 b 所示，则创建该图表的数据区域是 2020 年体艺类本科、专科计划招生人数占比 。 第 2 题图 b （3）对第 13 题图 a 所示工作表中的数据进行操作处理，下列说法正确的有 （多选，填字母）。 A.设置了 M3 单元格的格式为“数值”、“两位小数” B.在正确完成 M3:O4 计算后，若不小心将 B 列除，则原 M3:04 单元格中的数据保持不变 C.对表中艺术类第二批各类统考数据（数据区域为 A6:J13）按“列 H”为主要关键字降序排序，则图 b 所示 的图表也会随之发生变化 D.若要筛选出艺术类第二批的数据，可以选择 A2:J3 区域，对“批次名称”设置筛选条件为“包含：第二批”进 行筛选（注：全部选对的得 2 分，选对但不全的得 1 分，不选或有选错的得 0 分） 3.某校一个班学生成绩的统计数据，用 Excel 软件进行数据处理，如第 3 题图 a 所示。请回答下列问题： 第 3 题图 a 请回答下列问题： (1)第 3 题图 a 中 D50:J50 单元格计算男同学的各科