视频讲解
真题练习31-Excel电子表格_哔哩哔哩_bilibili
题库下载
题目要求
第31套
在某评选投票工作中，小刘需要在Excel中根据计票数据采集情况完成相关统计分析。具体要求如下：
1．在考生文件夹下，将“Excel素材.xlsx”文件另存为“Excel.xlsx”（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。
2．利用“省市代码”、“各省市选票数”和“各省市抽样数”工作表中的数据信息，在“各省市选票抽样率”工作表中完成统计工作，其中：
①不要改变“地区”列的数据顺序；
②各省市的选票数为各对应在“各省市选票数”工作表中的4批选票之和；
③各省市的抽样数为各对应在“各省市抽样数”工作表3个阶段分配样本数之和；
④各省市的抽样率为各对应抽样数与选票数之比，数字格式设置为百分比样式，并保留2位小数。
3．为“各省市选票抽样率”工作表的数据区域设置一个美观的表样式，并以三种不同的字体颜色和单元格底纹在“抽样率”列分别标记出最高值、最低值和高于平均抽样率值的单元格。
4．利用“省市代码”、“候选人编号”、“第一阶段结果”、“第二阶段结果”和“第三阶段结果”工作表中的数据信息，在“候选人得票情况”工作表中完成计票工作，其中：
①不要改变该工作表中各行、列的数据顺序；
②通过公式填写候选人编码所对应的候选人姓名；
③计算各候选人在每个省市的得票情况及总票数；
④在数据区域最右侧增加名为“排名”的列，利用公式计算各候选人的总票数排名；
⑤锁定工作表的前两行和前两列，确保在浏览过程中始终保持表头和候选人信息可见。
5．将“候选人得票情况”工作表复制为当前工作簿的一个新工作表，新工作表名称为“候选人得票率”。在新工作表中，将表头文字“候选人在各地区的得票情况”更改为“候选人在各地区的得票率”。
6．利用“候选人得票情况”、“各省市选票抽样率”工作表中的数据信息，在“候选人得票率”工作表中完成统计工作，其中：
①利用公式计算各候选人在不同地区的得票率（得票率指该候选人在该地区的得票数与该地区选票抽样数的比值），数字格式设置为百分比样式，并保留2位小数；
②将“总票数”列标题修改为“总得票率”，并完成该列数据的计算（总得票率指该候选人的总得票数与所有地区选票抽样总数的比值），数字格式设置为百分比样式，并保留2位小数；
③将“排名”列标题修改为“得票率最高的地区”，并根据之前的计算结果将得票率最高的地区统计至相对应单元格；
④在统计完成的得票率数据区域内，利用条件格式突出显示每个候选人得票率最高的两个地区，并将这些单元格设置为标准黄色字体、标准红色背景色填充。
7．在“候选人得票率”工作表的所有数据区域下方，根据候选人“姓名”和“总得票率”生成一个簇状柱形图图表，用以显示各候选人的总得票率统计分析。其中，图表数据系列名称为“总得票率”，数据标签仅包含值，并显示在柱状上方。
文字解析
第31组
1．【解析】在考生文件夹中选中“Excel素材.xlsx”文件，选择文件菜单，在弹出的菜单中选中“另存为”命令，然后，将文件名称中的“Excel素材”改成“Excel”，在保存类型中选中“Excel工作簿(*.xlsx)”，单击“保存”按钮。
2．【解析】②步骤1：在“各省市选票数”工作表的F1单元格中输入内容“地区”，选中F2单元格，单击“插入函数”按钮，打开“插入函数”对话框；
步骤2：找到并选中“VLOOKUP”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤3：第一个参数是需要在数据表首列进行搜索的值，这里填“A2”，第二个参数是需要在其中搜索数据的数据表，这里填“省市代码!$A$1:$B$32”，第三个参数是满足条件的单元格在数据表中的列数，这里填“2”，第四个参数是指定是否精确查找，这里填“0”，单击“确定”按钮；
步骤4：双击F2单元格智能填充柄，完成其他地区查找。
步骤5：选中“各省市选票抽样率”工作表中的B2单元格，单击“插入函数”按钮，打开“插入函数”对话框；找到并选中“SUM”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤6：将光标定位于第一个参数输入框中，选中“各省市选票数”工作表中的B2:E2单元格区域，单击“确定”按钮（注：稍微比较下就能发现，地区顺序是相同的）；
步骤7：双击B2单元格智能填充柄，完成其他地区选票数统计。
③步骤1：在“各省市抽样数”工作表的E1单元格中输入内容“地区”，选中E2单元格，单击“插入函数”按钮，打开“插入函数”对话框；
步骤2：找到并选中“VLOOKUP”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤3：第一个参数是需要在数据表首列进行搜索的值，这里填“A2”，第二个参数是需要在其中搜索数据的数据表，这里填“省市代码!$A$1:$B$32”，第三个参数是满足条件的单元格在数据表中的列数，这里填“2”，第四个参数是指定是否精确查找，这里填“0”，单击“确定”按钮；
步骤4：双击E2单元格智能填充柄，完成其他地区查找。
步骤5：选中“各省市选票抽样率”工作表中的C2单元格，单击“插入函数”按钮，打开“插入函数”对话框；找到并选中“SUM”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤6：将光标定位于第一个参数输入框中，选中“各省市抽样数”工作表中的B2:D2单元格区域，单击“确定”按钮（注：稍微比较下就能发现，地区顺序是相同的）；
步骤7：双击C2单元格智能填充柄，完成其他地区选票数统计。
④步骤1：选中“各省市选票抽样率”工作表中的D2单元格，在地址栏中输入“=C2/B2”，双击D2单元格智能填充柄，完成其他地区选票数统计。
步骤2：选中D2:D32单元格区域，单击“开始”|“数字”分组中的“
”百分号按钮，连续单击两次“增加小数位数”按钮。
3．【解析】步骤1：选中“各省市选票抽样率”工作表数据区域，单击“开始”|“样式”分组中的“套用表格格式”下拉按钮，选中其中一种样式（例如：表格样式浅色10），选中“表包含标题”复选按钮，单击“确定”按钮；
步骤2：单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中选中“最前/最后规则”级联菜单中的“最后10项”命令，打开“最后10项”对话框，设置项数为“1”，设置为中选“黄填充色深黄色文本”，单击“确定”按钮；
步骤3：单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中选中“最前/最后规则”级联菜单中的“高于平均值”命令，打开“高于平均值”对话框，设置为中选“绿填充色深绿色文本”，单击“确定”按钮。
步骤4：选中D2:D32单元格区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中选中“最前/最后规则”级联菜单中的“前10项”命令，打开“前10项”对话框，设置项数为“1”，设置为中选“浅红填充色深红色文本”，单击“确定”按钮；
4．【解析】②步骤1：选中“候选人得票情况”工作表中的B3单元格，单击“插入函数”按钮，打开“插入函数”对话框；
步骤2：找到并选中“VLOOKUP”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤3：第一个参数是需要在数据表首列进行搜索的值，这里填“A3”，第二个参数是需要在其中搜索数据的数据表，这里填“候选人编号!A:B”，第三个参数是满足条件的单元格在数据表中的列数，这里填“2”，第四个参数是指定是否精确查找，这里填“FALSE”，单击“确定”按钮；
步骤4：双击B3单元格智能填充柄，完成其他地区查找。
③步骤1：各候选人在每个省市的得票情况，需要将三个阶段的得票数相加，这里我们使用<INDEX+MATCH+VLOOKUP>组合函数来获取各候选人的各个阶段得票数；
步骤2：首先通过VLOOKUP函数，来获取地区代码（为了方便使用VLOOKUP函数，我们需要将“省市代码”工作表中的A列复制到C列），公式写成：VLOOKUP(C$2,省市代码!$B$2:$C$32,2,FALSE)；
步骤3：然后通过MATCH函数来确定要查找的省市代码在“第一阶段结果”工作表中所在的列数，公式写成：MATCH(VLOOKUP(C$2,省市代码!$B$2:$C$32,2,FALSE),第一阶段结果!$B$1:$AF$1,0)；
注意：第一个参数是要在数组中查找的匹配值，这里是VLOOKUP函数返回到省市代码；第二个参数是要查找的值所在的连续单元格区域，这里是要在“第一阶段结果”工作表中的B1：AF1单元格区域，因此是：第一阶段结果!$B$1:$AF$1；第三个参数是指定匹配方式（为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置；为0时，查找等于lookup_value的第一个数值；为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列）。
步骤4：通过MATCH函数来确定要查找的候选人在“第一阶段结果”工作表中所在的行数，公式写成：MATCH($A3,第一阶段结果!$A$2:$A$65,0)；
注：第一个参数“$A3”是要查找的候选人编号；第二个参数“第一阶段结果!$A$2:$A$65”是要查找的值所在的连续单元格区域。
步骤5：通过INDEX函数来获取指定行和列的单元格数值，公式写成：INDEX(第一阶段结果!$B$2:$AF$65,MATCH($A3,第一阶段结果!$A$2:$A$65,0),MATCH(VLOOKUP(C$2,省市代码!$B$2:$C$32,2,FALSE),第一阶段结果!$B$1:$AF$1,0))
注：函数功能是：在给定的单元格区域找行数和列数交叉处单元格的值。第一个参数是在给定的单元格区域；第二个参数是所要查找单元格所在的行数；第三个参数是所要查找单元格所在的列数。
步骤6：经过以上5步计算出候选人在第一阶段结果的得票数，使用同样的方法可以计算出其他两阶段的得票数，再将三次的相加，就得出该候选人总得票数；
步骤7：最后公式为：=INDEX(第一阶段结果!$B$2:$AF$65,MATCH($A3,第一阶段结果!$A$2:$A$65,0),MATCH(VLOOKUP(C$2,省市代码!$B$2:$C$32,2,FALSE),第一阶段结果!$B$1:$AF$1,0))+INDEX(第二阶段结果!$B$2:$AF$65,MATCH($A3,第二阶段结果!$A$2:$A$65,0),MATCH(VLOOKUP(C$2,省市代码!$B$2:$C$32,2,FALSE),第二阶段结果!$B$1:$AF$1,0))+INDEX(第三阶段结果!$B$2:$AF$65,MATCH($A3,第三阶段结果!$A$2:$A$65,0),MATCH(VLOOKUP(C$2,省市代码!$B$2:$C$32,2,FALSE),第三阶段结果!$B$1:$AF$1,0))
步骤8：拖动C3智能填充柄至AG单元格，填充该候选人在各个省份的得票数。选中“C3:AG3”单元格区域，双击智能填充柄完成其他候选人选票统计；
步骤9：选中AH3单元格，单击“开始”|“编辑”分组中的“求和”按钮，然后按回车键，完成第一个候选人总票数统计，双击智能填充柄完成其他候选人总票数统计。
说明：使用绝对引用是为方便后面自动填充
④步骤1：在AI2单元格中输入内容“排名”；
步骤2：选定AI3单元格，单击“插入函数”按钮，打开“插入函数”对话框；找到并选中“RANK.EQ”函数，单击“确定”按钮，打开“函数参数”对话框；
步骤3：第一个参数是指定要排序的数字，这里选“AH3”，第二个参数是要排序的数组或数据列表，这里是总票数列，写成“$AH$3:$AH$66”，第三个参数是指定排名方式，“0”或不写表示降序，非“0”值表示升序，题目是要从高到低（降序），因此输入“0”或不输入，单击“确定”按钮；双击智能填充柄完成其他候选人排名计算。
⑤选中C3单元格，单击“视图”|“窗口”分组中的“冻结窗口”下拉按钮，在下拉列表中单击“冻结拆分窗格”命令。
5．【解析】步骤1：在“候选人得票情况”工作表标签上单击鼠标右键，在弹出菜单中选择“移动或复制”命令，打开“移动或复制工作表”对话框，选中“候选人得票情况”，选中“建立副本”复选框，单击“确定”按钮；
步骤2：将新复制出来的工作表名称改为“候选人得票率”，删除C3:AI66单元格区域的数据；
步骤3：选中表头文字所在单元格，将内容改为“候选人在各地区的得票率”。
6．【解析】①步骤1：我们首选使用VLOOKUP函数计算出候选人所在省份的选票抽样数，公式写成：VLOOKUP(C$2,表1[#全部],3,0)；
注：第一个参数“C$2”是要查找候选人所在的省份名称；第二个参数“表1[#全部]”是“各省市选票抽样率”工作表中的数据区域；第三个参数“3”是所要查找的抽样数所在列数，第四个参数“0”表示大致查找。
步骤2：候选人得票情况直接从“候选人得票情况”工作表中获取“候选人得票情况!C3”，然后用得票情况除以抽样数就得出抽样率了，公式写成：候选人得票情况!C3/VLOOKUP(C$2,表1[#全部],3,0)；
步骤3：往后拖动C3单元格智能填充柄至AG单元格完成其他地区填充，选中C3:AG3单元格，双击智能填充柄完成其他候选人抽样率统计；
步骤4：选中C3:AG66单元格区域，单击“开始”|“数字”分组中的“百分号”按钮，连续两次单击“增加小数位数”按钮。
②步骤1：选中“总票数”单元格，将内容改成“总得票率”；
步骤2：选定AH3单元格，根据题目说明，使用候选人总得票数除以所有地区总抽样数，所有地区总抽样数计算公式为：SUM(表1[抽样数])；
步骤3：候选人总得票数为：候选人得票情况!AH3，总得票率公式为：候选人得票情况!AH3/SUM(表1[抽样数])；
步骤4：双击“AH3”单元格智能填充柄，完成其他候选人总得票率计算；
步骤5：选中AH3: AH66单元格区域，单击“开始”|“数字”分组中的“百分号”按钮，连续两次单击“增加小数位数”按钮。
③步骤1：选中AI2单元格，将其中内容修改为“得票率最高的地区”；
步骤2：先通过MAX函数找出候选人在所有地区中的最大得票率，公式写成：MAX(C3:AG3)，其中参数“C3:AG3”表示要查找最大值的区域；
步骤3：通过MATCH函数找出这个最大得票率所在的列数，公式写成：MATCH(MAX(C3:AG3),C3:AG3,0)；
步骤4：通过INDEX函数获取最大得票率所在列的标题内容，公式写成：INDEX($C$2:$AG$2,1,MATCH(MAX(C3:AG3),C3:AG3,0))；
步骤5：双击AI3单元格智能填充柄，完成其他候选人得票率最高的地区计算。
④步骤1：选中C3:AG3单元格区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中选中“新建规则”命令，打开“新建格式规则”对话框；
步骤2：选中“仅对排名靠前或靠后的数据设置格式”，选中“最高”、设置数值为“2”，单击“格式”按钮，打开“设置单元格格式”对话框；
步骤3：单击“字体”选项卡，设置字体颜色为标准色中的“黄色”，单击“填充”选项卡，设置背景色为标准色中的“红色”，单击“确定”按钮，再次单击“确定”按钮；
步骤4：选中C3:AG3单元格区域，双击格式刷按钮，然后依次点第4至66行的行标题，完成其他候选人最高得票区域的突出显示设置。
7．【解析】步骤1：插入图表前，要先将总得票率单元格拆分，并将“总得票率”文字放置在AH2单元格中；
步骤2：选中B2:B66和AH2:AH66单元格区域，单击“插入”|“图表”分组中的“柱形图”下拉按钮，选中下拉列表中的“簇状柱形图”图表类型；
步骤3：单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在下拉列表中选中“数据标签”级联菜单中的“其他数据标签选项”命令，打开“设置数据标签格式”对话框；
步骤4：选中“标签选项”中的“值”，“系列名称”和“类别名称”复选框不要选中，选中标签位置中的“数据标签外”按钮，单击“关闭”按钮；
步骤5：拖动图表到整个数据表下方，并适当拖放图表大小，使之适当显示。
保存并关闭Excel文档。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
