excel29

题目

2.利用“省市代码”、“各省市选票数”和“各省市抽样数”工作表中的数据信息，在“各省市选票抽样率”工作表中完成统计工作，其中：

①不要改变“地区”列的数据顺序；

②各省市的选票数为各对应在“各省市选票数”工作表中的4批选票之和；

 ----------sum函数

示例：=SUM(各省市选票数!B2:E2)（注意：不要引用）

--输入=sum函数：表“各省市选票抽样率”

--sum函数要用到的数据来源：表“各省市选票数”B2:E2列

③各省市的抽样数为各对应在“各省市抽样数”工作表3个阶段分配样本数之和； ----sum函数

示例：=SUM(各省市抽样数!B2:D2)（注意：不要引用）

--输入=sum函数：表“各省市选票抽样率”

--sum函数要用到的数据来源：表“各省市抽样数”B2:D2列

④各省市的抽样率为各对应抽样数与选票数之比，数字格式设置为百分比样式，并保留2位小数。

当行：抽样数/选票数（不要除反了！）；再更改数字格式（百分比2位），然后再双击填充整列

3.为“各省市选票抽样率”工作表的数据区域设置一个美观的表样式，并以三种不同的字体颜色和单元格底纹在“抽样率”列分别标记出最高值、最低值和高于平均抽样率值的单元格。

在“各省市选票抽样率”工作表的D2单元格，ctrl+shift+↓选中整列（不要第一行）

→开始→样式→条件格式→项目选取规则→高于平均值→进行颜色设置

还是这一列→样式→条件格式→项目选取规则→值最大的十项→10改为1，进行颜色设置

还是这一列→样式→条件格式→项目选取规则→值最小的十项→10改为1，进行颜色设置

注意；要设置颜色，包括字体颜色和填充颜色❗❗

4.利用“省市代码”、“候选人编号”、“第一阶段结果”、“第二阶段结果”和“第三阶段结果”工作表中的数据信息，在“候选人得票情况”工作表中完成计票工作，其中：

①不要改变该工作表中各行、列的数据顺序；

②通过公式填写候选人编码所对应的候选人姓名；

运用vlookup函数（在“候选人得票情况”工作表）

示例：=VLOOKUP(A3,候选人编号!$A$2:$B$65,2,0)

第一行：当行的A3候选人编号

第二行：表“候选人编号”选择2列数据（不包括第一行标题，并且记得绝对引用）

③计算各候选人在每个省市的得票情况及总票数；

示例：=SUMIF(第一阶段结果!$B$1:$AF$1,INDEX(省市代码!$A$2:$A$32,MATCH($C$2,省市代码!$B$2:$B$32,0),1),第一阶段结果!$B2:$AF2)+SUMIF(第二阶段结果!$B$1:$AF$1,INDEX(省市代码!$A$2:$A$32,MATCH($C$2,省市代码!$B$2:$B$32,0),1),第二阶段结果!$B2:$AF2)+SUMIF(第三阶段结果!$B$1:$AF$1,INDEX(省市代码!$A$2:$A$32,MATCH($C$2,省市代码!$B$2:$B$32,0),1),第三阶段结果!$B2:$AF2)

（在“候选人得票情况”工作表）

输入MATCH函数

示例：MATCH(C$2,省市代码!$B$2:$B$32,0)

=MATCH($C$2,省市代码!$B$2:$B$32,0)

第一行：C$2（C2是地区安徽）(因为要下拉，第2行固定不能变，所以需要绝对引用) 【❗注意是C$2，绝对引用一个，不要2个！！！后面截图这个注意一下有点小错误】

第二行：“省市代码”表的第二列地区（记得绝对引用）（选中列除第一个）记得选中一列即地区列！！

第三行：0（精确匹配）

match函数完后得12-----即安徽是在“省市代码”表选中区域的第12行

---

再用index函数

示例：=INDEX(省市代码!$A$2:$A$32,MATCH(C$2,省市代码!$B$2:$B$32,0),1)----------所求的34，即是安徽的代码34

将自动第一行的数据，剪切到第二行

第一行：“省市代码”A列（绝对引用）

第二行：剪切第一行的数据，并粘贴

第三行：1（要的是第一列的省市代码）

Index函数完后得34-----即在“省市代码”表选中区域的第12行安徽代码是34

---

再用条件求和sumif函数求得候选人在该省份的得票情况

示例：=SUMIF(第一阶段结果!$B$1:$AF$1,INDEX(省市代码!$A$2:$A$32,MATCH(C$2,省市代码!$B$2:$B$32,0),1),第一阶段结果!$B2:$AF2)

将自动第一行的数据，剪切到第二行

第一行：“第一阶段结果”表B1:AF1(绝对引用)-------选中的是“第一阶段结果”表第一行即地区所代表的编号

第二行：剪切第一行的数据，并粘贴

第三行：“第一阶段结果”表中$B2:$AF2(绝对引用；但是因为要向下填充，所以行号不能绝对引用，对列标进行绝对引用。因为B2:AF2改为$B2:$AF2，即在字母前绝对引用）-----示例，第一阶段第一行数据各个地区之和

但是还要求第二、第三阶段。复制（）不要复制前面的=，从sumif开始复制即可往后面加即可。即可求得这一个，再向右填充，双击填充下面的

④在数据区域最右侧增加名为“排名”的列，利用公式计算各候选人的总票数排名；

第一问：增加“排名”二字也要同“总票数”一样，占2格子，并且合并

第二问：rank函数（记得绝对引用）

⑤锁定工作表的前两行和前两列，确保在浏览过程中始终保持表头和候选人信息可见。

在C3单元格→视图→冻结窗格→冻结拆分窗格

5.将“候选人得票情况”工作表复制为当前工作簿的一个新工作表，新工作表名称为“候选人得票率”。在新工作表中，将表头文字“候选人在各地区的得票情况”更改为“候选人在各地区的得票率”。 -----复制，勾选建立副本，移到最后，重命名

6.利用“候选人得票情况”、“各省市选票抽样率”工作表中的数据信息，在“候选人得票率”工作表中完成统计工作

①利用公式计算各候选人在不同地区的得票率（得票率指该候选人在该地区的得票数与该地区选票抽样数的比值），数字格式设置为百分比样式，并保留2位小数；

分析：在所复制的表中，点C3，C3其实就是候选人在该地区的得票数，再求得该地区选票抽样数（需要用到vlookup函数），再二者相除即可。

做法：将C3单元格的函数再加个括号（括号加在=和sumif之间）/用vlookup函数求得的该地区选票抽样数

示例：=(SUMIF(第一阶段结果!$B$1:$AF$1,INDEX(省市代码!$A$2:$A$32,MATCH(C$2,省市代码!$B$2:$B$32,0),1),第一阶段结果!$B2:$AF2)+SUMIF(第二阶段结果!$B$1:$AF$1,INDEX(省市代码!$A$2:$A$32,MATCH(C$2,省市代码!$B$2:$B$32,0),1),第二阶段结果!$B2:$AF2)+SUMIF(第三阶段结果!$B$1:$AF$1,INDEX(省市代码!$A$2:$A$32,MATCH(C$2,省市代码!$B$2:$B$32,0),1),第三阶段结果!$B2:$AF2))/VLOOKUP(C$2,表1[[地区]:[抽样数]],3,0)

其中vlookup函数

第一行：“候选人得票率”中的C$2------地区安徽

第二行：“各省市选票抽样率”工作表中前三列（除第一行)

第三行：3（因为抽样率在选中3列的第3列）

第四行：0

记得改数字格式：百分比，2位数；再右拉，下拉

②将“总票数”列标题修改为“总得票率”，并完成该列数据的计算（总得票率指该候选人的总得票数与所有地区选票抽样总数的比值），数字格式设置为百分比样式，并保留2位小数；

--改名：列标题“总票数”改为“总得票率”

将AH3清除

----------sum函数

示例：=SUM(第一阶段结果!$B2:$AF2)    ❗注意前面要引用，后面不需要

--输入=sum函数：在“候选人得票率”表中的AH3

--sum函数要用到的数据来源：表“第一阶段结果”B2:AF2,注意B2:AF2绝对引用成$B2:$AF2

但是还要求第二、第三阶段。复制（）不要复制前面的=，从sumif开始复制即可往后面加即可。

示例：=SUM(第一阶段结果!$B2:$AF2)+SUM(第二阶段结果!$B2:$AF2)+SUM(第三阶段结果!$B2:$AF2)

注意题目是求之比，还要求除数即所有地区选票抽样总数用（记得被除数即3阶段之和，加个括号鸭）

----------sum函数

示例：SUM(表1[抽样数])

--输入=sum函数：在“候选人得票率”表中的AH3

--sum函数要用到的数据来源：“各省市选票抽样率”工作表中的抽样数列（即第3列，除第一行）

示例：=(SUM(第一阶段结果!$B2:$AF2)+SUM(第二阶段结果!$B2:$AF2)+SUM(第三阶段结果!$B2:$AF2))/SUM(表1[抽样数])

修改数字格式：百分比2位

③将“排名”列标题修改为“得票率最高的地区”，并根据之前的计算结果将得票率最高的地区统计至相对应单元格；

--改名：“排名”列标题修改为“得票率最高的地区”

--将AI清空，运用MAX函数

示例：=MAX(C3:AG3)

第一行：所在表“候选人得票率”的C3:AG3

---

再运用MATCH函数

示例：=MATCH(MAX(C3:AG3),C3:AG3,0)

第一行：已填过的MAX函数

第二行：所在表“候选人得票率”的C3:AG3

第三行：0

---

再运用index函数

第一行：所在表“候选人得票率”的$C$2:$AG$2

第二行：1

第三行：剪切第一行的数据MATCH函数，并粘贴

④在统计完成的得票率数据区域内，利用条件格式突出显示每个候选人得票率最高的两个地区，并将这些单元格设置为标准黄色字体、标准红色背景色填充。

选中表“候选人得票率”C3:AG66→开始→样式→条件格式→新建规则→运用公式→=RAMK(C3,$C3:$AG3,0)<3→格式：字体颜色：黄色，填充颜色：红色

注意：rank函数里面不能手打，必须选中表中的C3等，手打无效

7.在“候选人得票率”工作表的所有数据区域下方，根据候选人“姓名”和“总得票率”生成一个簇状柱形图图表，用以显示各候选人的总得票率统计分析。其中，图表数据系列名称为“总得票率”，数据标签仅含值，并显示在柱状上方。

在“候选人得票率”工作表下方空白处随点一个空白格→插入→柱形图→簇状（第1个）

设计→选择数据→添加→系列名称：AH1；系列值：清除原有的，输入AH3:AH66

编辑→选中当前表“候选人得票率”B3:B66（候选人姓名，除第一行）

要显示数据标签：布局→标签→数据标签加投标签外→将图标拉长一些（因为数据太密集了）

完

-2020.1.23