EXCEL 在 统计 工作中的 应 用 —— 基于 Excel2010 主 讲 西安 统计 研究院 亢大麟.

Presentation on theme: "EXCEL 在 统计 工作中的 应 用 —— 基于 Excel2010 主 讲 西安 统计 研究院 亢大麟."— Presentation transcript:

1 EXCEL 在 统计 工作中的 应 用 —— 基于 Excel2010 主 讲 西安 统计 研究院 亢大麟

2 统计 工作的特征和基本 过 程  统计 工作的特征  统计 工作的 对 象 —— 大量 现 象 总 体数量特征  统计 工作 —— 在相关理 论 指 导 下 ，发现 并描述大量 现 象 总 体数量特征 的工作  特点 ——  关注所研究 现 象的数量特征 ， 以数字 说话；  面 对 的是大量表象的数字 ， 需从中找出本 质 的特征  数字后面都有特定的内涵  统计 工作 过 程  提出 问题  拟 定研究方案  搜集数据 （现 象个体的表面数量特征 ）  整理并分析数据  得到 认识（结论 —— 现 象 总 体的本 质 特征 ） 需要搜集、存储、整 理、分析数据的工具

3 讲 座基本内容  第一部分 EXCEL 简 介  第二部分 利用 EXCEL 进 行 统计设计  第三部分 利用 EXCEL 进 行数据整理  第四部分 利用 EXCEL 进 行数据分析 调查设计 数据整理数据分析得到结论

4 第一部分 EXCEL 简 介

5 MICROSOFT OFFICE 电 子 办 公套件 组 成  目前的版本  Office 2003 Windows XP  Office 2007 Windows Vista  Office 2010 Windows 7  Office 2013 Windows 8 （ 本 课 程基于 2010 版介 绍， Office 套件可通 过 校园先 锋计 划 优 惠 购买 )  Microsoft office 电 子 办 公套件主要构成  Microsoft office word 文本 处 理  Microsoft office Excel 电 子表格  Microsoft office Powerpoint 幻灯演示  Microsoft office Access 数据 库  Microsoft office Outlook 事 务处 理  Microsoft office Publisher 排版 设计  Microsoft office Visio 图 形 绘 制  Microsoft office Project 项 目管理

6 EXCEL2010 工作簿的基本界面 文件名快速工具栏 菜单栏 菜单工具栏（随菜单改 变） 单元格内容编辑栏 工作表名称 命名框 工作表控制按钮 状态栏 垂直拆分 水平拆分

7 各类报表设计 EXCEL 的基本功能  功能一 ：设计电 子表格  电 子表格 设计  数据 （ 含文字 ）录 入与 编辑  功能二 ： 一般数据 计 算  利用公式 进 行常 规 运算  利用函数 进 行复 杂 运算  功能三 ：图 形 图 表 显 示  计 算 结 果的 显 示  数据 （ 含文字 、图 形 ）显 示  功能四 ：专业 数据分析和 显 示  利用数据透 视 表 进 行描述性 统计 分析  利用函数 进 行数据分析  利用数据分析工具包 进 行 专业 数据分析 公式函数运算 图形图表显示 数据搜集整理 分析

8 统计 中常用的 EXCEL 操作技巧  关于工作区界面  行列的 隐 藏与 显 示  行列的 冻结  窗口分割  各种 视图 的使用  关于数据格式  格式刷的使用  序列数据的 输 入  定 义 数据有效性  条件格式的 设 定  多 级 下拉菜 单 的建立  工作簿和工作表保 护  关于数据引用  导 入 导 出数据  公式的使用  单 元格引用  选择 性粘 贴  关于函数的使用  SUM 加 总 函数  平均函数  VLOOKUP  HLOOKUP  IF 条件函数  加 载项 的使用  加 载记录单 命令  加 载 分析工具 库  打印 设 置  打印区域 设 置  网格 线隐 藏  自定 义页 眉 页 脚  标题 行重复  显 示 视图  控制打印范 围  双面打印

9 关于工作区界面  行列的 隐 藏与 显 示  选 中需 隐 藏的行 ，视图 - 隐 藏  选 中需 隐 藏的列 ，视图 - 隐 藏  首行 、 首列的 隐 藏与 显 示 ， 点 击 工作区左上角 选 中全部 单 元 格 ， 右 键 —— 取消 隐 藏  行列的 冻结  将活 动单 元格定位在需 冻结 行 （ 列 ） 的右下  冻结 行 ：视图 —— 冻结 窗格 —— 冻结 首行  冻结 列 ：视图 —— 冻结 窗格 —— 冻结 首列  全部 冻结：视图 —— 冻结 窗格 —— 冻结 拆分  工作窗口的分割  横向分割 ， 双 击 横向拆分按 钮  纵 向分割 ， 双 击纵 向拆分按 钮 1 2 3

10 关于数据的引用  录 入数据  直接 录 入  利用 记录单录 入  导 入数据  导 入文本数据  导 入数据 库 数据  导 入网 页 数据  导 入 word\excel 中的数据  单 元格引用  相 对 引用  绝对 引用  混合引用  复制粘 贴  简单 直接粘 贴  选择 性粘 贴

11 关于数据格式  格式刷的使用  对单 元格使用相同的格式 ， 包括字体 、 字 号 、颜 色及背景 、 框 线、 条件格式等  单击 使用一次 、 双 击 格式刷可多次使用  条件格式的 设 定  为 数据 应 用条件格式 ， 可立即突出 显 示一系 列数 值 中复合 设 定条件的数据  方法 ：选择 要 设 置条件格式的数据列或区 域 —— 开始 —— 式 样 —— 条件格式  根据需要 选择 条件类型 、 条件和复合 设 定条 件后的 显 示格式

12 关于数据格式 2  自定 义 序列数据的 输 入  一次性使用的序列数据  在 连续 两个 单 元格中 输 入序列的前两个 值  选 中 这 两个 单 元格 ， 点拖拽柄拖 动  多次使用的序列数据  文件 —— 选项 —— 高 级 —— 常 规 —— 编辑 自定 义 列 表  输 入或从其他位置 导 入新的序列 —— 添加 —— 确定  单 元格中 输 入新序列的第一个 ， 拖 动 拖拽柄拖 动 复 制

13 关于数据有效性  定 义 数据有效性作用  标 准化和 规 范化 单 元格数据格式及 输 入的 内容  步 骤  数据 —— 数据有效性 —— 设 置 —— 有效性 条件 —— 选择 允 许 的类型 。  定 义 整数 、 小数 、 日期 、时间、 文本数 据 ：选择 条件 - 上下限  定 义 序列数据 ： 建立序列字段列表 ， 数 据 —— 数据有效性 —— 序列 —— 选择 可 输 入的序列列表 ，选择 提供下拉箭 头  定 义 公式 （ 自定 义）： 根据数据要求建立 公式条件  输 入提示信息

14 ECXEL 数据分析中常用函数的使用  数据 查询 类  VLOOKUP  VLOOKUP( 参数 1 ： 需要在表格区域 查 找的代 码 数 值（ 可引用 单 元格 ）, 参数 2 ： 要在其中 查 找代 码 及数 值 的表区域 （ 其中 第一列是排序后的代 码， 采用 绝对 引用 ）, 参数 3 ： 返回的 值 在被 查询 表中的列数, 参数 4 ： FALSE ）  HLOOKUP  HLOOKUP( 参数 1 ： 需要在表格区域 查 找的代 码 数 值（ 可引用 单 元格 ）, 参数 2 ： 要在其中 查 找代 码 及数 值 的表区域 （ 其中 第一行是排序后的字段名 ， 采用 绝对 引用 ）, 参数 3 ： 返回的 值 在被 查询 表中的行数, 参数 4 ： FALSE ）  SUM 加 总 函数  AVERAGE 平均函数  判断函数 IF ：  IF ( 参数 1 ： 判断的条件, 参数 2 ：满 足条件的 结 果 ， 参数 3 ： 不 满 足条件的 结 果 ) ）  例 ： IF （ A1>=60,” 及格 ”,” 不及格 ” ） 含 义： 如果 A1 单 元格的数 值 大于或等于 60 则显 示 “ 及格 ” ， 否 则显 示 “ 不及格 ” 。  IF 函数允 许 多 级 嵌套使用 —— 即可以 进 行多 级 判断

15 打印 设 定  打印 设 置  打印区域  网格 线隐 藏  自定 义页 眉 页 脚  标题 行重复  显 示 视图  控制打印范 围  双面打印

16 第二部分 利用 EXCEL 进 行 统计设计

17 （ 一 ） 使用 XECEL 设计 各类 统计报 表 （调查 表 ） 模板  1 、报 表格式 设计  报 表格式 设计  报 表 总 体格局 设计（宽、 高 、单 元格合并 、单 元格 宽、 高 设计）  字体 设计（ 字体 、 字号 、 排列 ）  报 表 边 框 设计（线 型 、线宽、 表 头 斜 线设计）  报 表打印 设计  打印区域 设 置  打印 页 眉 页 脚 设计 [ 自定 义页 眉 （ 左中右 ）、页 脚 （ 左中右 ） ]  表格列数 较 多需多 页 打印 时 上方 宾栏标题 行在每 页 重复出 现  表格行数 较 多需多 页 打印 时 左 侧 主 栏标题 列在每 页 重复出 现  2 、报 表功能 设计  数据有效性 设计  自 动计 算字段 设计  当前年月日 、 自 动调 用 标题、 自 动 使用文件名  自 动计 算字段 （ 公式 ）  单 元格内容限制的 设 定  数据有效性的灵活使用  下拉菜 单选择设计  一 级 下拉菜 单  二 级 下拉菜 单  3 、 多 级级联 下拉菜 单 的 设 定  作用 ： 限定在一 级 分类下的二 级 分类 的内容  步 骤：  建立各 级 分类内容  命名区域 ：选 定分类内容 —— 公式 — — 定 义 的名称 —— 根据所 选 内容确定  对 一 级 分类列定 义 数据有效性 ， 序列 —— 来源 = 一 级 分类命名区域  对 二 级 分类列定 义 数据有效性 ， 序列 —— 来源 =INDIRECT($ A2 ) ， 其中 A 为 一 级 分类所在列, 2 为 一 级 分类所在的 行  三 级 分类以此类推

18 （ 一 ） 使用 XECEL 设计 各类 统计报 表模板  4 、 常用控件的使用  单选 按 钮  复 选 框  数 值调节 按 钮  其他功能按 钮  5 、 数据 计 算公式 设计  在 报 表中 设计 公式的目的 ：  简 化数据 输 入  验证输 入的数据  建立模型  输 入公式  6 、 工作表 、 工作薄保 护  保 护 工作表  隐 藏 计 算公式

19 （ 二 ） 利用 EXCEL 进 行抽 样设计  1 、 影响 样 本量的因素  总 体方差 （ 没有 时 可用相似指 标样 本方差代替 ）  抽 样误 差 （ 平均 误 差与极限 误 差 ）  抽 样 估 计 的概率度  抽 样 方式 （ 以 简单 随机抽 样为 例 ）  2 、计 算所需的 样 本量  重复抽 样  不重复抽 样

20 （ 二 ） 利用 EXCEL 进 行抽 样设计  3 、 重复抽 样样 本 单 元的抽取  全局 简单 随机 样 本 【 数据 - 数据分析 - 抽 样 - 输 入区域 （样 本框 ） - 随机 （ 所需 样 本数 ） - 选择输 出区域 - 确定  区 间简单 随机抽 样  将 总 体分 组， 在各 组 内按上述 办 法抽取 。  4 、 利用随机数 发 生器抽取 样 本 （ 重复抽 样）  使用随机函数 RAND() 产 生 0-1 之 间 的随机数  使用函数 ：【 =RANDBETWEEN(MIN,MAX) 】产 生介于最小最大数之 间 的一个随机数 。  按需要的个数复制  5 、 不重复抽 样样 本 单 元的抽取  等距抽 样 【 数据 - 数据分析 - 抽 样 - 输 入区域 （样 本框 ） - 周期 （样 本 间 距 ） - 选择输 出区域 - 确定  随机抽 样 A 列 为样 本框 编 号 1 、 先使用函数 ：【 =RAND() 】 生成一列随机数 （ 如在 A1-A100) 2 、 再使用函数 ：【 =MATCH(SMALL(A$1:A$100,ROW()),A$1:A$100,) 】（ 如在 B1) 3 、 按需要的 产 生随机数个数向下复制所需的行数 。 即得到不重复的随机整数 。（ 如从 B1-B20) 4 、查 找与 A 列 编 号 对应 的 标 志 值。

21 第三部分 利用 EXCEL 进 行数据整理

22 （ 一 ） 数据的 简单 整理 —— 合并 计 算  1 、 多表数据合并 （报 表 汇总、计 算平均 ）  将多个数据表中的数据 计 算 汇总 数 、 平均数等  前提 ： 各表格式相同 、 数据 单 位相同  多表 间 数据 汇总  合并 计 算 合并 计 算

23 数据列表是将工作表中的数据作为数 据库处理，按照行、列方式组织数据。每 一行称为记录，每一列称为字段。数据列 表的第一行称为标题行，由字段名组成。 从统计的角度看，数据列表可以看成 是统计的原始数据的过录表。字段名即总 体单位的标志名称，字段的值即为总体单 位的标志值。每一行为一个总体单位的全 部标志值。 建立数据列表是利用 EXCEL 进行数 据分析的基础。 （ 二 ） 数据列表 （ 清 单） —— 数据分析的基 础

24 （ 二 ） 数据 导 入 - 建立数据清 单  2 、 数据 导 入 、 建立数据清 单  数据清 单设计  建立数据清 单  数据 录 入  数据 导 入  表格数据 导 入  文本数据 导 入  网 页 数据表 导 入  数据 库 数据 导 入  报 表合并  对记录补 充缺失字段  （ HLOOKUP 函数使用 - 横向 补 充 ）  对 字段 补 充缺失 记录  （ VLOOKUP 函数使用 - 纵 向 补 充 ） 数据清 单 1 、 数据分析的基 础 2 、 第一行的每列 为 指 标（变 量 ） 名 ， 每行 为 一 个个案 （样 本 ） 的各 变 量 值； 3 、 清 单 中没有完整的空行和空列 数据清 单 1 、 数据分析的基 础 2 、 第一行的每列 为 指 标（变 量 ） 名 ， 每行 为 一 个个案 （样 本 ） 的各 变 量 值； 3 、 清 单 中没有完整的空行和空列

25 （ 三 ） 数据 检验  1 、 异常 值检验  利用 图 表 进 行 检验  散点 图  折 线图  利用条件格式 进 行 检验  同列数据 验证  列 间 数据 验证  2 、 异常 值 修正与缺失 值 插 补  同字段缺失 值 插 补  简单 平均插 补  分类平均插 补  时间 序列缺失 值 插 补  均 值 插 补  趋势 插 补  季 节 指数插 补（ 月度及以下数据 ）

26 第四部分 利用 EXCEL 进 行数据分析 一 、 描述性 统计 分析 二 、 推断性 统计 分析

27 数据透视表基于数据清单建立，是一种强大的数据组织表，它提供了一种在 不破坏原数据清单的前提下灵活进行数据分类汇总和简单计算的方法，用户可以 随时按照不同的需要，依不同的关系提取和组织数据。 数据透视表可以从多个维度组织数据以满足分析的需要。数据透视表可以直 接显示分组数据的多种参数。因此，利用数据透视表可以得到填报表时所需的各 类汇总数据和分类构成数据。 （ 一 ） 数据透 视 表 —— 基本 统计 分析的利器

28 创建数据透视表对话框  单击数据清单中的任一单元格；  调用菜单命令 “ 插入 —— 数据透视表 ” ； 1 、创 建数据透 视 表

29 分页字段区列字段区域行字段区域 值显示区域所有字段选择区 2 、 数据透 视 表界面

30 将是否在校拖入分页区， 并在字段值中选择 “ 是 ” 将 “ 学历 ” 拖入行标题区， 并在字段值中选择 “ 全部 ” 将 “ 学科 ” 拖入列标题区， 并在字段值中选择 “ 全部 ” 将 “ 学生代码 ” 拖入数值显 示区。显示 “ 计数 ” 显示各行、列的计数分布 例：按学历分类的各学科在校生人数 3 、 数据透 视 表基本使用 通过以下三步，使用数据透视表： 第 1 步：根据分析的需要选择字段，根据显示的需要决定放置字段的位置，将所需字段从字 段选择区拖入到相应区域

31 按学校类型中类、小类两 个字段的复合分组 对于已经拖入显示区域的字段，可以根 据需要进行筛选，以满足分析的需要。 可以将多个字段拖入行、列标签区 域，以实现复合分组 点击这里选择筛选的字段 并选择筛选的条件。

32 第 2 步：使用右键菜单中的 “ 值字 段设置 ” 打开对话框，选择值计算 字段的汇总方式。 计算字段为文本型字段时，只能 计数； 计算字段为数值型字段时，可以 选择字段值求和、计算最大值、最 小值、平均数、标准差、方差等相 应的参数。 调 整数据透 视 表 值汇总 的方式

33 不同 汇总 方式的 显 示 注意这里！ 显示的汇总方式和 汇总的字段 各类院校本科招生 总人数 各类学校中有本科 毕业生的院校数

34 第 3 步：使用右键菜单中的 “ 值 字段设置 ” 打开对话框，根据需要 选择数据透视表中数据的显示方 式。 可以显示分组后的各部分占横 行汇总、纵列汇总、全部汇总的 百分比等。 调 整数据透 视 表内容的 显 示方式

35 例：按学历分类 的各学科在校生 人数占各学科总 人数的比例 例：按学历分类 的各学科在校生 人数占各学历总 人数的比例 例：按学历分类的各学科在校生人数

36 数据 、 日期字段的分 组显 示 1 2 3

37 按数 值、 日期字段分 组显 示注意的 问题 1 、自定义分组的字段数据类型为数值型（含数值型日期： yyyy/mm/dd ） 2 、字段数据类型必须一致（不能有空白单元格） 3 、该字段不能已经分组 4 、分组起始位置可以不是该字段的最小值，分组终止位置也可以不是 该字段的最大值，超出部分按大于、小于分组 5 、步长：即组距可自定义，分组结果为等距分组 6 、 Excel 中对连续变量分组的原则是 “ 上限在内 ”

38 方法一 : 先选定数据透视表中的任一单元格, 右键菜单中单击 “ 刷新 ” 命令 ; 方法二 : 先选定数据透视表中的任一单元格, 单击 “ 数据透视表工具 ” 菜单栏上的 “ 刷新 ” 工 具按钮 方法三：先选定数据透视表中的任一单元格，单击 “ 数据 ” 菜单的 “ 全部刷新 ” 工具按钮。 方法一 : 先选定数据透视表中的任一单元格, 右键菜单中单击 “ 刷新 ” 命令 ; 方法二 : 先选定数据透视表中的任一单元格, 单击 “ 数据透视表工具 ” 菜单栏上的 “ 刷新 ” 工 具按钮 方法三：先选定数据透视表中的任一单元格，单击 “ 数据 ” 菜单的 “ 全部刷新 ” 工具按钮。 更新数据透 视 表中的数据 已建立数据透视表后原始数据清单发生变化，数据透视表中的数据不 会同步自动变化，需要对数据透视表数据进行更新。 若记录没有增减，按以下办法刷新数据 若记录有增减，则需重新定义数据区域。方法为： 点击数据透视表工具菜单的 “ 选项 ” 工具，选择 “ 更改数据源 ”

39 （ 二 ） 利用数据分析工具包 进 行数据分析  数据分析工具包的加 载  EXCEL 中的一些潜在功能默 认 不 显 示 ， 需要通 过 加 载项 加 载 后使用  1 、 加 载 与 调 用数据分析工具  文件 —— 选项 —— 加 载项 —— 转 到 —— 打开加 载 项对话 框 —— 勾 选 分析工具 库  分析工具的 调 用 ： 数据 —— 数据分析  2 、 加 载 与使用 记录单  录 入 （ 或 检 索 ） 字段比 较 多的数据清 单时， 可采 用 记录单  记录单 加 载： 文件 —— 选项 —— 自定 义 功能区 — — 不在功能区中的命令 —— 记录单 ——

40 （ 1 ） 描述性 统计 分析  数 值 型数据的不等距分 组 —— 直方 图 工具  数据分布特征的描述 —— 描述 统计 分析工具 包  排位与百分比排位  简单趋势预测 —— 线 性回 归趋势

41 （ 1 ） 描述性 统计 分析  2 、 利用函数 进 行 统计 分析  EXCEL 中函数的使用  函数分类  主要 统计 函数的使用

42 （ 1 ） 描述 统计 分析 设：各地区第一产业增加值为 x 平 均： x 的平均值。 标准误差： x 的标准误差（抽样平均误差） 中位数： x 的中位数 众 数： x 的众数 标准差： x 的样本标准差 方 差： x 的样本方差 峰 度： x 分布的陡峭程度（以 3 为中心判断） 偏 度： x 分布的偏斜程度（以 0 为中心判断） 区 域： x 的极差 =x 最大值 -x 最小值 最大值： x 中的最大值 最小值： x 中的最小值 求 和： x 的合计数 观测数： x 的个数、单位数 n 最大值 2 ： x 的次大值 最小值 2 ： x 的次小值 置信度（ 95% ）：在 95% 的置信水平下的抽样极 限误差 =1.96x 平均误差 注意：描述统计分析 工具默认是将数据作 为样本数据来分析的 3 、分析结果的判读

43 （ 2 ） 推断性 统计 分析  相关分析  定量数据的相关性分析 —— 线 性相关系数  两个 变 量  多个 变 量  相关系数判 读  大小  方向  定类数据的相关性分析 —— 品 质 相关系 数 、 列 联 表分析  回 归 分析  回 归 分析建立一元 线 性回 归 方程  利用数据 图 表的 趋势线 建立一元非 线 性回 归 方程  抽 样 估 计  总 体均 值 的区 间 估 计  总 体比例的区 间 估 计

44 列 联 表分析 - 品 质 数据的相关分析 城市 武汉武汉南京上海合计(h) 态度态度 喜欢喜欢 40302090 无所谓 2030 80 不喜欢 40201070 合计(l) 1008060240 期望频数(f) 37.53022.5 33.33326.6720 29.16723.3317.5 卡方统计量 喜欢喜欢 0.170.000.28 无所谓 5.330.425.00 不喜欢 4.020.483.21 合计合计 18.91 1 、建立假设： 原假设：态度与城市无关 备择假设：态度与城市有关 若卡方值大于临界值，拒绝原假设 卡方值小于临界值，不能拒绝原假设 2 、计算卡方值 3 、查卡方分布临界值表 在（ c-1 ） * （ r-1 ）自由度，显著水平 95% 时，卡方分布的临界值 =9.48 4 、做出判断 因本例卡方值（ 18.9087 ）大于临界值 （ 9.48 ），所以，拒绝原假设。即：态度与城市 有关。这一判断的的可靠程度为 95% 。

45 回 归 分析 结 果 的判 读

46 （ 三 ） EXCEL 中 图 表的 创 建和 设 置  1 、 双坐 标图 的 设计 与使用 ——( 总 量坐 标 + 百分比坐 标 )  2 、单 坐 标 双 图 形的 设计 与使用 —— 柱形 图 + 线图  3 、 含有二 级 构成的 饼图设计 与的使用  4 、阶 梯 图 的 设计 与使用  5 、 雷达 图 的 设计 与使用

47 结语 轻 松快 乐 地学 习 EXCEL  Excel 在 线 培 训  http://office.microsoft.com/zh- cn/support/FX010056500.aspx http://office.microsoft.com/zh- cn/support/FX010056500.aspx  微信公众号 ： excel 精英培 训  http://www.excelpx.com/ http://www.excelpx.com/  推荐两本 书  《统计 的 谎 言 》 （ 美 ） 达莱尔 · 哈 夫 著 ； 廖 颖 林 译 达莱尔 · 哈 夫 廖 颖 林  《谁说 菜 鸟 不会数据分析 》 张 文霖, 刘夏璐, 狄松 编 著  希望大家 轻 松快 乐 地学 习统计、 玩 转 Excel