Excel | 操 作 题 第 26 套 题 目 

陈颖是某环境科学院的研究人员，现在需要使用Excel来分析我国主要城市的降水量。根据下列要求，帮助她完成这项工作。

1. 在考生文件夹下，将“Excel_素材.xlsx”文件另存为“Excel.xlsx”（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。

2. 在“主要城市降水量”工作表中，将A列数据中城市名称的汉语拼音删除，并在城市名后面添加文本“市”，如“北京市”。

3. 将单元格区域A1:P32转换为表，为其套用一种恰当的表格格式，取消筛选和镶边行，将表的名称修改为“降水量统计”。

4. 将单元格区域B2:M32中所有的空单元格都填入数值0；然后修改该区域的单元格数字格式，使得值小于15的单元格仅显示文本“干旱”；再为这一区域应用条件格式，将值小于15的单元格设置为“黄色填充深黄色文本”。（注意：不要修改单元格中的数值本身）

5. 在单元格区域N2:N32中计算各城市全年的合计降水量，对其应用实心填充的数据条条件格式，并且不显示数值本身。

6. 在单元格区域O2:O32中，根据“合计降水量”列中的数值进行降序排名。

7. 在单元格区域P2:P32中，插入迷你柱形图，数据范围为B2:M32中的数值，并将高点设置为标准红色。

8. 在R3单元格中建立数据有效性，仅允许在该单元格中填入单元格区域A2:A32中的城市名称；在S2单元格中建立数据有效性，仅允许在该单元格中填入单元格区域B1:M1中的月份名称；在S3单元格中建立公式，使用Index函数和Match函数，根据R3单元格中的城市名称和S2单元格中的月份名称，查询对应的降水量；以上三个单元格最终显示的结果为广州市7月份的降水量。

9. 按照如下要求统计每个城市各月降水量以及在全年中的比重，并为其创建单独报告，报告的标题和结构等完成效果可参考考生文件夹下的图片“城市报告.png”。

① 每个城市的数据位于一张独立的工作表中，工作表标签名为城市名称，如“北京市”。

② 如参考图片“城市报告.png”所示，各月份降水量数据位于单元格区域A3:C16中，A列中的月份按照1-12月顺序排列，B列中为对应城市和月份的降水量，C列为该月降水量占该城市全年降水量的比重。

③ 不限制计算方法，可使用中间表格辅助计算，中间表格可保留在最终完成的文档中。

10. 在“主要城市降水量”工作表中，将纸张方向设置为横向，并适当调整其中数据的列宽，以便可以将所有数据都打印在一页A4纸内。

11. 为文档添加名称为“类别”，类型为文本，值为“水资源”的自定义属性。

 

  对 应 步 骤  

1、选择Excel素材文件复制，新文件命名为“Excel”

2、选择A列数据复制，然后打开word → 在word里粘贴 → 只保留文本

替换

光标定位查找内容→点击更多→特殊格式→选择任意字母

输入后如图所示，点击全部替换→此时所有字母都被删除了

再次单击替换→在查找内容输入^p 替换为那里输入 市^p → 全部替换，此时后面全部都有了市

然后粘贴回Excel即可

3、选择A1:P32，开始→随便套用一种表格格式→表包含标题→确定

点击表格工具→设计→取消勾选镶边行，表名称改为“降水量统计”

数据选项卡→单击筛选即可取消

4、选择B2:M32区域→开始→查找和选择→定位条件

选择空值→确定

单击确定之后，鼠标不要点任何地方，直接按键盘上的0，然后按ctrl+enter →此时所有空单元格都输入了0

继续选择这部分区域→条件格式→突出显示单元格规则→小于

输入15→选择自定义格式

切换到数字→自定义→输入“干旱”→确定

再次选择这部分区域→条件格式→突出显示单元格规则→小于→同样输入15→格式选择黄色填充，深黄色文本

5、光标定位N2单元格→单击自动求和

选择N2:N32区域→条件格式→数据条→其他规则

勾选“仅显示数据条”，选择实心填充，确定

6、在O2单元格输入排名函数RANK.EQ （函数的参数）

RANK.EQ参数（排名的数据，排名的区域，0表示降序/1表示升序）

排名的数据即N2单元格，排名的区域为N2:N32，0表示降序

7、光标定位P2单元格→插入→迷你图下的柱形图

数据范围选择B2:M2单元格 

选中P2单元格→迷你图工具选项卡→标记颜色→高点→红色，剩下的自动填充

8、选择R3单元格→数据验证

允许条件选择序列，来源选择城市的区域→确定

同理选择S2单元格，数据验证→允许选择序列，来源选择B1:M1区域

然后在下拉菜单分别选择“广州市”和“7月”

题中要求在S3引用index函数和match函数。先介绍一下这两个函数。

index函数：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。

index参数（引用的区域，引用的行数，引用的列数）

最终的结果就是这个区域中第几行和第几列交叉处的值。

题中要求返回广州市和7月的降水量，所以我们的区域就是B2:M32

而广州市和7月的所在行数和列数要用match函数返回。

MATCH函数：返回指定数值在指数据区域中的位置

MATCH参数（数值，区域，-1/1/0）

-1 ：数据必须按升序排列

1：数据必须按降序排列

0,：数据可以按任何顺序排列

所以我们只需要查找广州市和7月再对应区域的行数 和列数。

广州市的在A2:A32的第几行，即=MATCH(R3,A2:A32,0)

7月在B1:M1的第几列，即=MATCH(S2,B1:M1,0)

所以最后的函数公式为：

9、①选择A1:M32区域→插入→数据透视表 

把城市拖动到筛选器上

分析选项卡→数据透视表的选项→显示报表筛选页

然后随便选择一个城市→在表格中输入下图内容

光标定位B3单元格→利用vlookup函数查找重庆市的降水量，并返回1月的值

vlookup函数（查找的数据，查找的范围，返回的值在范围的第几列，0）

即=vlookup（重庆市，主要城市降水量的范围，1月在范围的第2列，0）

此时1月的数据就求出来了，那么2月返回的列数是在范围的第3列，我们把2改成3即可

所以剩下的我们自动填充，然后把第3个参数改成对应的月份在范围的第几列即可。最后结果如下图所示

总计用sum函数求和即可

全年占比=每月降水量/总计单元格。 总计单元格需要锁定。剩下的自动填充到C16单元格。

选择C4:C16单元格→右击设置单元格格式→2位小数→确定
（图中的底纹颜色可以不设置，考试不扣分）

选择我们做好的数据区域，复制

然后按住shift键选择底部各城市的工作表，在A3单元格中粘贴，此时每个表就都有了数据。

粘贴好之后，右击工作表标签，取消组合工作表

10、在页面布局选项卡→页面对话框启动器→选择纸张方向为横向→选择调整为1页高1页宽→纸张大小选择A4→确定，最后适当缩小列宽。

11、点击文件→信息→属性→高级属性

点击自定→名称输入“类别”，类型为“文本”，取值输入“水资源”→确定。

最后单击保存即可。