教科版（2019）高中信息技术 基础巩固9-Excel表格处理

基础巩固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相对引用与绝对应用
9.数据排序：将对应数据区域安照相关条件进行排序，注意主要关键字和次要关键字。
10.数据筛选：分自动筛选和高级筛选。筛选功能是将满足一定条件的部分数据保留下来，不满足条件的数据隐藏起来。注意相关关键词：不足、超过、超出、不够以及10个最大的值的使用。
11.数据分类汇总：对数据分析研究的方法，可使数据按照不同的类别进行求和、求平均值及个数等。
12.图表的创建及应用：数据区域、数据分析及图表编辑和修改。常见图表类型：柱形图、折线图、条形图、饼图、散点图、面积图等。
13.图表和数据区域中内容
改变图表区域中某个单元格内容，图表对应内容也会自动更新。图表数据区域内的数据排序后，图表中也会跟着变化。筛选后，不符合筛选条件的信息图表上不会显示。图表数据区域外插入整行或整列，图表不会变化。
【注意】
公式必须以等号开头；
（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 分）
【答案】
=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
【答案】
= D3/(C3 * C3) * 10000 或 =D3/C3^2 * 10000或等价表达式 （2）AC （3）A2:A6,D2:E6
【解析】
直接根据提示信息书写公式，书写方式比较多，等价表达式均可。特别注意单位换算（厘米、米）。
（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
为了获得名称包含“制造”的主要行业中，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所示，则创建该图表的数据区域是 。
第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 单元格计算男同学的各科平均分，E50:J50 的值是通过D50 单元格自动填充功能得到，可知D50单元格公式为___________。
(提示: AVERAGEIF函数返回满足条件的单元格的平均值。例如:=AVERAGEIF(C3:C48,"女",F3:F48)，返回所有女生的英语平均分）
(2)根据第3题图a中的数据，制作了一张部分学生总分图表，如第13题图b所示，则制作该图表的数据区域是___________________。
第3题图b
(3)下列有关对图表和数据表操作描述正确的是________（多选）。
A.删除第13题图a表中“学号”所在的A列，不影响第13图b所示图表效果
B.I49单元格中是全班地理平均分，可以用 =AVERAGE（I3:I48）公式计算得到
C.要对全班成绩按总分排序，可以仅选择K1:K48区域进行排序
D.要筛选出全班总分最高的女同学，可以先以“性别”等于“女”筛选，再以“总分”最大的1项筛选得到
4.小林老师收集了全国各地区近5年普通高中毕业生数的数据，并使用Excel软件对数据进行处理，如图a所示。
图a
请回答下列问题：
（1）区域I3:I7的数据来自区域B7:F7，首先选择区域B7:F7复制，再选择I3单元格单击右键，出现如图b所示选项，要实现以上效果，应选择粘贴选项的序号是 。
图b 图c
（2）区域J3:J6的数据是通过公式计算得到的，在J3单元格中输入公式，并使用自动填充功能完成区域J4:J6的计算，则J3单元格中的公式是 。（提示：增长率=（当年毕业生数 - 去年毕业生数）/ 去年毕业生数）
（3）利用SUM函数在第34行计算每年全国普通高中的毕业生总数，进行了某种排序操作后，创建了如图c所示图表。结合图a和图c，下列说法正确的有 （多选，填字母）。
A.由图c可知毕业生总数最多的是2015年，最少的是2017年
B.若不小心删除了一个省的毕业生人数，图表中对应的数据条将变低
C.选择区域A2:F33，以“2019年”为关键字进行降序排序，图表会发生改变
D.选择区域A2:F33，按“地区”筛选值包含“自治区”，不会影响图表内容
E.创建图表前的排序操作可能是选择B2:F34区域，以“行34”为关键字按行进行排序
（注：全部选对的得 2 分，选对但不全的得 1 分，不选或有选错的得 0 分）
5.小张收集了某校图书馆借阅排行TOP300的相关数据，并用Excel软件进行处理，如图a所示：
图a
（1）为计算图a中的“流通次数”和“预约次数”，小张已利用公式正确计算了“流通次数”，他希望通过修改K3单元格的公式，利用自动填充得到“预约次数”L3:L7区域中正确的数据，则单元格K3中的公式应修改为 。（提示:SUMIF函数用于求满足条件的单元格之和，例如=SUMIF(E3:E302,J3,G3:G302)用于求单元格区域E3:E302中与单元格J3中值相等的对应单元格区域G3:G302中的值之和。）
（2）根据第13题图a中数据制作了图表如图b所示，则创建图表的数据区域 。
图b
（3）根据第13题图a所示，下列说法正确是 （多选题）
A.若表格已排序，则主要关键字是“流通人次”，次要关键字是“预约人次”
B.若以“类型”为文学，以“流通人次”最大的5项进行筛选，得到的记录数可能大于等于5条
C.因误操作将“价格”所在列删除，则“流通次数”和“预约次数”的数据将不能正确计算
D.若要找出文学类流通人次大于10次的书籍，则可以利用“类型”为文学，“流通人次”大于10进行筛选
（注：全部选对的得2分，选对但不全的得1分，不选或有选错的得0分）
6．用 Excel软件对“某公司销售业绩”的相关数据进行处理，如图a所示。其中货物编号的规则：型号（第1、2个字符）+ “-”（第3个字符）+年份（4~7个字符）+月份（第8、9个字符）+7位货品编号。
图b
图a 图c
请回答如下问题：
区域C3:G14的数据是通过公式计算得到的，在C3单元格中输入公式 ，先自动填充至G3，再使用自动填充功能完成区域C4:G18的计算（提示：销售金额= 目标数量*单价）
若要筛选出2020年10月所有型号的销售数据，则第13题图b中加框处应输入 。
对第13题图a所示工作表中的数据进行操作处理，下列说法正确的有 （多选，填字母）。
A.正确完成（1）的计算后，不小心删除了A列，则区域C3:G18的数据不会发生变化。
B.因为有合并单元格，无法对任何数据进行排序操作
C.要完成（2）的操作，还可以设置筛选条件为“包含：202010”
D.要完成第13题图c所示图表，选择的数据区域B2:G2, B19:G19
（注：全部选对的得2分，选对但不全的得1分，不选或有选错的得0分）
7.某校一次考试的成绩数据用Excel软件进行处理，如图所示。
图a
请回答下列问题：
（1）区城M3:M172的数据是通过公式计算得到的，在M3单元格中输入公式 ，再使用自动填充功能对区域M4:M172进行填充。
（2）为了实现1班的总分从高到低排序，下列对区域A2:M172的操作可行的是 （多选，填字母）。
A.先按“班级”为关键字升序排序，再按“总分”为关键字降序排序
B.以“班级”为主要关键字升序、“总分”为次要关键字降序进行排序
C.以“总分”为主要关键字降序、 “班级”为次要关键字升序进行排序
D.先按“班级”筛选。选择值为“1班”，再按“总分”进行降序排序
（注：全部选对的得2分，选时但不全的得1分，不选或有选错的得0分）
（3）若表格数据已按班级升序排序，要找出2班总分最高分，可在指定单元格用公式“=max( )”实现。
8.使用Excel软件处理某超市进销存数据，如图a所示。
图a
请回答下列问题：
（1）区域H3:H357的数据通过公式计算得到：在H3单元格中输入公式 ，再使用自动填充功能 完成区域H4:H357的计算。（提示：利润 =（商品售价-商品进价）× 销量）
（2）图a中“粮油类”商品编码数据“101717”误输入为“10177”，“粮油类”商品按“商品编码”升序 排序后，发现“10177”位于“101720”之后，其原因是B355、B356单元格中的数据是 类型。
图b 图c
（3）完成图a中的各类商品总利润计算之后，制作了如图b所示的图表。下列说法正确的有 (多选，填字母)。
A.总利润最高的商品是水果类
B.对区域E3:G357数据格式设置为“保留1位小数”，“利润”列数据保持不变
C.在图a所示工作表中按“商品名称”筛选所有花生油，图c中①处可选择“包含”
D.对蔬菜类以“销量”为关键字进行升序排序，则选择的排序数据区域为A2:H130
(注：全部选对的得2分，选对但不全的得1分，不选或有选错的得0分)
9.小俞用Excel软件处理查询得到的“近几年全国居民人均收入支出统计”数据，如第9题图a所示：
第9题图a
请回答下列问题：
（1）参照J3单元格的公式计算J7单元格的值，并用自动填充功能完成区域J8:J14的计算，则J7单元格中的公式为 。
（2）若要对类别为“支出”的数据按“2020年”进行降序排序，则排序的数据区域是 。
（3）根据图a中数据制作的图表如图b所示，以下说法正确的是： （多选，填字母）。
第9题图b
A. 近几年，衣、食、住三项支出中，居住消费支出的增长是最缓慢的
B. 选中A1:J1单元格，执行“合并后居中”操作后，该单元格名称为A1
C. 若在图a所示工作表中，以“类别”为支出、“2020年”为最大3项进行筛选，则筛选出的记录有3条
D. 若在图a所示工作表的C列前插入一列，并输入2013年的相关数据，J列的数值不会发生改变
（注：全部选对的得2 分，选对但不全的得1 分，不选或有选错的得0 分）
10.某中学用Excel软件对高一部分学生体测数据进行统计分析，如第10题图a所示：
第10题图a
（1）第10题图a中，为计算各班级各项目平均分，首先在H4单元格输入公式,再向下并向右填充完成各班各项目平均分的计算，发现结果不正确，公式应修改为 。
（提示：AVERAGEIFS函数用于对一定区域中满足指定条件的单元格求平均值。以第10题图a中的数据为例，=AVERAGEIFS(C3:C88,A3:A88,G4)表示计算A3:A88区域中班级为1901的跳绳平均分）
（2）若要根据立定跳远得分对整个年级进行降序排序，应选择的排序区域为 。
第10题图b
（3）小李根据第13题图a制作了1901班仰卧起坐得分对比如13题图b所示。下列说法正确的是 （多选,填字母）。
A.创建该图表的区域为：B2:B9，E2:E9
B.若要筛选出1902班跳绳得分最高的三个同学，可以先对“班级”按“1902”进行筛选，再对“跳绳得分”按“10个最大的值”中最大3项进行筛选
C.若在C列前插入一列，图表不会发生变化
D.若修改C、D、E列数值格式保留1位小数，则图表会发生变化
（注：全部选对的得2分，选对但不全的得1分，不选或有选错的得0分）
11.为了响应全民健身计划,某高中对学生进行了体质健康测评,并用Excel软件进行数据处理，如第13题图a所示。
图a
请回答下列问题：
区域M2:P15 的数据是通过公式计算得到的，在M2单元格中输入公式后，再使用自动填充功能完成该区域的计算，则N3单元格中的公式是“=SUMPRODUCT((______________________)*($I$3:$I$553=N$1))”。
（提示：M2单元格输入公式=SUMPRODUCT((A1:A100=”2”)*(I1:I100=”优秀”))，表示同时满足A1:A100是2班和I1:I100是优秀这两个条件的情况有几个，也就是进行条件计数。）
（2）根据第13题图a中的数据制作的图表如第13题图b所示。创建该图表的数据区域是_______________ 图b
（3）对图a工作表进行相关数据分析，下列说法正确的是_________（多选，填字母）。
A.以“总分等级”为依据进行升序排序，选择的区域是A3：I553
B.选择“A3：I553”按照“总分等级”升序排序后，图 b 的图表不会发生改变
C.现需要选出总分等级为“不及格”的学生，可选择区域A3：I553，以“总分等级”为“不及格”进行筛选并保留结果
D.为了显示男生立定跳远得分最高的3位学生数据，可先对“性别”为“男”进行筛选，再对“立定跳远”进行筛选，选择最大的3项
12.某品牌销售部员工收集了2021年第一季度各销售网点销售数据，并使用Excel软件处理数据，如图a所示。请回答下列问题：
第2题图a
（1）区域G3:G16的数据是在G3的单元格中输入公式，然后利用自动填充功能得到的，则G3单元格中
的公式是 。（提示：2021年第二季度比2020年同期增长率=（2021年第二季度销售额－2020年第二季度销售额）/ 2020年第二季度销售额*100）
（2）为了更加直观地分析2021年第二季度东北地区销售情况走势，建立如图b所示的图表。则生成该图表的数据区域是 。
第2题图b
（3）结合图a，下列说法正确的是 （多选，填字母）
A.对列F进行筛选，筛选条件设置为“最大3项”，图b中的图表不会发生变化
B.若要选出华中地区5月销售冠军，应选B7:G10为排序区域，并以“五月销售额”为关键字
C.图a中标题单元格是选中A1:G1设置“合并后居中”得到
D.若将C3: F16域的数据设置为0位小数，会影响G列数据的值
E.若不慎将F列单元格数据清除，则G列单元格中将显示#DIV/0!
（注：全部选对的得2分，选对但不全对的得1分，不选或有选错的得0分）
答案
1.【答案】（1）=RANK(B4, B$4:B$18) （2）D3:D5,F3:F5 （3）①⑤ 或 ⑤① 或1 5 或 5 1
2.【答案】（1）= H14 / H$16 （2）L2:L4,N2:O4 （3）BD
3.【答案】（1）=AVERAGEIF($C3:$C48,"男",D3:D48) （2）B39:B48,K39:K48 （3）AB
4.【答案】（1）④ （2）=（I3-I4）/I4 （3）ABE
5.【答案】（1）=SUMIF($E$3:$E$302,$J3,G$3:G$302) （2）J4:K7 （3）BD
6.【答案】（1）=$B3*C$19 或 =C$19*$B3 （2） 202010 或 T -202010 或 T 202010 （3）AD
7.【答案】（1） =sum(C3:L3) （2） BD （3） M31:M60
8.【答案】（1）=(E3-F3)*G3 （2）文本 （3）BC
9.【答案】（1）=I7/SUM(I$7:I$14)*100 或 =I7/SUM($I$7:$I$14)*100 （2）A7:J14 或 A7:I14（3）BD
10.【答案】（1）=AVERAGEIFS(C$3:C$88,$A$3:$A$88,$G4) （2）A2:E88 或 A3:E88 （3）AC
11.【答案】（1）$A$3:$A$553=$K3 （2）M1:P1,M16:P16 （3）AB
12.【答案】（1）=(SUM(C3:E3)－F3)/F3* 100 （2）B2:E2,B11:E13 （3）BCE