加载中…
个人资料
  • 博客等级:
  • 博客积分:
  • 博客访问:
  • 关注人气:
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

计算机二级考试:国家统计局每10年进行一次人口普查  EXCEL题目难题详解

(2017-03-26 18:42:12)
标签:

excel

计算机二级

index()

match()

if()

分类: 计算机基础

中国的人口发展形势非常严峻,为此国家统计局每10年进行一次人口普查,以掌握全国人口的增长速度及规模。按照下列要求完成对第五次、第六次人口普查数据的统计分析:

  1)新建一个空白Excel文档,将工作表sheet1更名为“第五次普查数据”,将sheet2更名为“第六次普查数据”,将该文档以“全国人口普查数据分析.xlsx”为文件名进行保存。

2)浏览网页“第五次全国人口普查公报.htm”,将其中的“2000年第五次全国人口普查主要数据”表格导入到工作表“第五次普查数据”中;浏览网页“第六次全国人口普查公报.htm”,将其中的“2010年第六次全国人口普查主要数据”表格导入到工作表“第六次普查数据”中(要求均从A1单元格开始导入,不得对两个工作表中的数据进行排序)。

3)对两个工作表中的数据区域套用合适的表格样式,要求至少四周有边框、且偶数行有底纹,并将所有人口数列的数字格式设为带千分位分隔符的整数。

 4)将两个工作表内容合并,合并后的工作表放置在新工作表"比较数据 "中(自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函数输出最终的值。

本人觉得其他都很简单,所以其他解题过程,不在讲述。 如果疑问可以留言,本人看到时必定协助解答。



0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有