计算机二级考试：国家统计局每10年进行一次人口普查 EXCEL题目难题详解
2017-03-26 18:42阅读：
中国的人口发展形势非常严峻，为此国家统计局每10年进行一次人口普查，以掌握全国人口的增长速度及规模。按照下列要求完成对第五次、第六次人口普查数据的统计分析：
（1）新建一个空白Excel文档，将工作表sheet1更名为“第五次普查数据”，将sheet2更名为“第六次普查数据”，将该文档以“全国人口普查数据分析.xlsx”为文件名进行保存。
（2）浏览网页“第五次全国人口普查公报.htm”，将其中的“2000年第五次全国人口普查主要数据”表格导入到工作表“第五次普查数据”中；浏览网页“第六次全国人口普查公报.htm”，将其中的“2010年第六次全国人口普查主要数据”表格导入到工作表“第六次普查数据”中（要求均从A1单元格开始导入，不得对两个工作表中的数据进行排序）。
（3）对两个工作表中的数据区域套用合适的表格样式，要求至少四周有边框、且偶数行有底纹，并将所有人口数列的数字格式设为带千分位分隔符的整数。
（4
SPAN>
'比较数据
'中（自A1单元格开始），且保持最左列仍为地区名称、A1单元格中的列标题为'地区'，对合并后的工作表适当的调整行高列宽、字体字号、边框底纹等，使其便于阅读。以'地区'为关键字对工作表'比较数据'进行升序排列。
（5）在合并后的工作表'比较数据'中的数据区域最右边依次增加'人口增长数'和'比重变化'两列，计算这两列的值，并设置合适的格式。其中：人口增长数=2010年人口数-2000年人口数；比重变化=2010年比重-2000年比重。
（6）打开工作簿'统计指标.xlsx'，将工作表'统计数据'插入到正在编辑的文档'全国人口普查数据分析.xlsx'中工作表'比较数据'的右侧。
（7）在工作簿'全国人口普查数据分析.xlsx'的工作表'比较数据'中的相应单元格内填入统计结果。
（8）基于工作表'比较数据'创建一个数据透视表，将其单独存放在一个名为'透视分析'的工作表中。透视表中要求筛选出2010年人口数超过5000万的地区及其人口数、2010年所占比重、人口增长数，并按人口数从多到少排序。最后适当调整透视表中的数字格式。
（提示：行标签为'地区'，数值项依次为2010年人口数、2010年比重、人口增长数）。
统计字段如下图：
| 统计项目 |
2000年 |
2010年 |
|
|
|
|
|
| 总人数(万人） |
|
|
|
|
|
|
|
| 总增长数(万人） |
- |
|
|
|
|
|
|
| 人口最多的地区 |
|
|
|
|
|
|
|
| 人口最少的地区 |
|
|
|
|
|
|
|
| 人口增长最多的地区 |
- |
|
|
|
|
|
|
| 人口增长最少的地区 |
- |
|
|
|
|
|
|
| 人口为负增长的地区数 |
- |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
注意：进行地区统计时，统计范围不包含“中国人民解放军现役军人”及“难以确定常住地”两类地区
这是这道题的难点，很多人都不知道如何解决，再次贴下一个公式共大家参考。
在统计人口最多或最少的地区时必须排除上述两个地区，这就加大了难度，而且很难，几乎没有看到有学生使用公式做对过。
本人使用的解决方法涉及到几个函数和数组的应用。比如现要求2010年人口最少的地区为例。其他几项参考此解法。
用到的函数：if(), match(),max(),min(),index()和数组应用
在2010年列对应的人口最多的地区行单元格中输入如下公式：
=INDEX(比较数据!$A$2:$A$34,MATCH(MAX(IF((比较数据!$A$2:$A$34<>'中国人民解放军现役军人')*(比较数据!$A$2:$A$34<>'难以确定常住地'),比较数据!D$2:D$34,FALSE)),IF((比较数据!$A$2:$A$34<>'中国人民解放军现役军人')*(比较数据!$A$2:$A$34<>'难以确定常住地'),比较数据!D$2:D$34,FALSE),0))
（说明： 上述公式并没有排除“难以确定的常驻地和中国人民解放军现役军人”）
然后在公式编辑栏中选中上述公式按 ctrl+shift+enter 3个组合键
得到如下公式样子（多了一个大括号）
{=INDEX(比较数据!$A$2:$A$34,MATCH(MAX(IF((比较数据!$A$2:$A$34<>'中国人民解放军现役军人')*(比较数据!$A$2:$A$34<>'难以确定常住地'),比较数据!D$2:D$34,FALSE)),IF((比较数据!$A$2:$A$34<>'中国人民解放军现役军人')*(比较数据!$A$2:$A$34<>'难以确定常住地'),比较数据!D$2:D$34,FALSE),0))
}
执行上述操作后：就排除了不得统计的两个地区，这个公式中涉及了多个函数的嵌套，
可以说是excel的高难度应用了
公式中涉及的$符号是绝对引用符号，本人只是为了方便公式的引用而加上的，可以去掉。
公式中2次出现了
IF((比较数据!$A$2:$A$34<>'中国人民解放军现役军人')*(比较数据!$A$2:$A$34<>'难以确定常住地'),
比较数据!D$2:D$34 , FALSE) 这个if函数目的就是为了排除
这两个地区不纳入在统计范围之内，在max函数内用一次是为了计算最大值时排除这两个地区，在match函数内是为了排除这个两个地区获取最大值的行号，然后为index函数提供行号。最终靠index函数输出最终的值。
本人觉得其他都很简单，所以其他解题过程，不在讲述。 如果疑问可以留言，本人看到时必定协助解答。
|
