EXCEL数据分析.ppt

1 1数据1 2Excel基础1 3属性数据分析1 4数据展示图1 5数值型数据分析 第1章数据和数据分析 根据数据的属性 数值型 123 1万元 23 5 4700点 连续型数据 温度变化 如23 5 离散型数据 5个篮板 120个集装箱 属性型 用文字 判断等表示的数据天气 晴 阴 雨 股市 升 降 CPI 高 中 低 质量 好 中 差 属性型数据都是离散的 1 1数据 第1章数据和数据分析 商务数据的分类 根据数据的变化不随状态变化 相对稳定的数据称为静态数据 随状态变化的数据称为动态数据 1 1数据 商务数据的分类 时间序列数据截面数据 1996年 2004年农村与城镇家庭恩格尔系数 食品支出占家庭总支出的百分比 单位 按照时间前后发生的数据称为时间序列数据 1 1数据 2002 2006年某市旅游接待与收入情况 按时间划分 陕南2007年经济指标 同一时间段发生的数据称为截面数据 1 1数据 截面数据 定类数据 Categorical NominalData 由不同类别组成的数据称为定类数据 各类数据的类型和单位一般都不相同 不同类的数据之间有些可以运算 有些则不能运算 1 1数据 按相互关系划分 定序数据 OrdinalData 仅表明对象的排列次序的数据称为定序数据 定序数据之间的差或比值一般没有意义 1 1数据 定距数据 IntervalData 数据没有绝对的零值 没有倍数 比例关系 只有相互之间的差距有实际意义 1 1数据 定比数据 RatioData 定比数据具有明确的零点 可以计算数据的比例 例如2008年美国的GDP是中国的4 15倍 1 1数据 根据数据的连续性离散数据数据是整数或者若干可能的结果之一 例如顾客投诉的次数 产品中不合格的个数 天气是晴天 阴天或下雨 连续数据数据是连续变量 例如产品的成本 利润 交货时间等 1 1数据 1 1数据 EXCEL软件简介 MicrosoftExcel是一个功能强大 使用灵活方便的电子表格软件 也是最为流行的办公自动化软件 本课程主要利用EXCEL的统计分析功能和丰富的统计图表 1 2Excel基础 工作表和单元格单元格的格式数据输入方法条件格式工具数据分列工具 1 2Excel基础 主要内容 工作表和单元格 1 2Excel基础 默认的Excel工作薄由三张工作表组成 最多可以包括255张工作表 最多可以输入256列 工作表和单元格 1 2Excel基础 单元格是Excel工作表的基本元素 单元格可分为数据单元格和公式单元格 数据单元格 SUM B 8 B 11 SUM C 8 C 11 SUM D 8 D 11 B 5 B8 C 5 C8 D 5 D8 B 5 B9 C 5 C9 D 5 D9 B 5 B10 C 5 C10 D 5 D10 B 5 B11 C 5 C11 D 5 D11 SUM F8 F11 工作表和单元格 1 2Excel基础 公式单元格 例1 1 单元格的格式 Excel单元格有多种格式 包括 常规 数值 货币 日期 时间 百分比 分数 科学计数 文本 特殊 自定义 等11种格式 1 2Excel基础 选定需要定义格式的单元格 打开Excel菜单 格式 单元格 弹出单元格格式对话窗口 其中 常规 和 文本 两种格式没有格式选项 其他格式都有不同的选项 例如 数值 格式有小数位数 是否选用千分位分隔符 负数的显示方式等选项 1 2Excel基础 单元格的格式 日期格式 可以把日期系列数显示为定义的日期格式 日期系列数是从1开始的整数 数字1表示1900年1月1日 2表示1900年1月2日等等 1 2Excel基础 单元格的格式 日期系列数和日期格式的转换如下图所示 A2 A3 A20 1 2Excel基础 单元格的格式 不同的时间格式如下 1 2Excel基础 单元格的格式 数据输入是数据分析和建模的第一步 采用正确的数据输入方法可以提高数据输入的效率 减少数据输入的错误 数据输入方法 1 2Excel基础 Excel数据输入的方法有 直接键盘输入用自定义格式输入输入序列用 有效性 工具输入用条件函数IF输入用字符提取函数RIGHT LEFT MID输入用查找函数VLOOKUP输入 直接键盘输入键盘输入是Excel数据输入最基本 最常用的输入方法 键盘输入遇到的常见问题有 数值格式输入的数字最大为15位 多于15位的数字将自动转换成科学计数格式 例如 常规或数值格式的单元格中输入18位身份证号码按回车确认后 显示为 3 3106E 17 因此 如果需要输入15位以上的数字 需要将单元格定义为文本格式 或者在常规或数值格式单元格中 先输入撇号 然后输入数字 数据输入方法 自定义格式输入 如果某些单元格中输入的数据有相同的字符 例如 出生于20世纪 出生地为杭州市的身份证号码前8个字符 33010619 可以将需要输入身份证号码的单元格定义为 自定义 格式 如下 33010619 这样 输入身份证号码时 只需要输入后面10个字符 即可完整显示身份证号码 数据输入方法 以上 自定义 格式 的对话窗口如下 数据输入方法 用自定义格式 输入后的效果如下图 其中黄色的单元格是用 33010619 自定义格式 数据输入方法 输入序列1 2 3 4 一 二 三 四 A B C D 甲 乙 丙 丁 子 丑 寅 卯 等都是序列 数字序列的输入方法 输入数字序列的前两个数字 拖动单元格右下十字钮 或用Excel菜单 编辑 填充 序列 工具生成数据序列 数据输入方法 1 2Excel基础 其他序列的方法 打开Excel菜单 工具 选项 自定义序列 在对话框中输入一个序列 单击 添加 定义序列以后 就可以用数字序列一样的方法输入 具体过程如下 Excel菜单 工具 选项 自定义系列 对话窗口如下 数据输入方法 序列可以在对话窗口中直接输入 单击 添加 后生成 如下图 数据输入方法 也可以事先在Excel表中输入序列 然后在对话窗口中选用 从单元格中导入序列 选定 A 1 A 7 单击 导入 生成序列 这种方法比较适合生成较长的序列 数据输入方法 1 2Excel基础 自定义序列后 只要输入序列第一个值 拖动单元格右下十字钮 就可以输入整个序列 如图 数据输入方法 用 有效性 工具输入Excel有效性工具是数据输入的常用方法 它可以提高输入效率 减少输入错误 打开Excel菜单 数据 有效性 出现以下 数据有效性 对话窗口 其中有四个卡片 设置 输入信息 出错警告 和 输入法模式 数据输入方法 其中 设置 卡片用来设置数据的有效性范围 例如 数值型数据可以设置最小值和最大值 字符型数据可以设置字符长度等 一旦输入的数据在有效范围以外 将会出现出错警告 例如 职工 出生年月 的值应该在1949年 60岁 到1991年 18岁 之间 手机号码应该为11位 固话号码应该为8位 等等 数据输入方法 如果输入的数据是职工出生年月 在 允许 下拉菜单中选定 日期 输入 开始日期 和 结束 日期 数据输入方法 如果输入的数据是职工身份证号码 在 允许 下拉中选定 文本长度 数据 下拉选定 等于 长度 输入18 数据输入方法 输入信息 卡片用于用户定制选定输入单元格时出现的提示信息 例如 对于输入身份证号码的单元格 提示信息如下 数据输入方法 当选定输入单元格时 出现提示信息的效果如下 数据输入方法 数据有效性工具还可以定制输入出错警告 定制输入职工出生日期的 出错警告 如下图 数据输入方法 定制输入职工身份证号码的 出错警告 如下图 数据输入方法 输入的身份证号码不足18位时出现的错误警告 数据输入方法 有效性工具中 可以建立输入项目的下拉菜单 特别适合输入定制的项目 先选定单元格C2 C17 然后打开 允许 下拉菜单 选定 序列 数据输入方法 在 来源 中 输入定制项目单元格范围E2 E13 单击确定 将光标置于选定的C2 C17的每一个单元格 都将出现下拉箭头 数据输入方法 选定下拉菜单中的院校名称 就可以方便地输入所选内容 数据输入方法 如果出现的条件有两个以上 可用嵌套的IF函数实现 例如 如果奖金发放金额由考核等级确定 设考核等级单元格为A2 计算奖金额度的函数为 IF A2 A 2000 IF A2 B 1000 0 IF函数嵌套的层数最多为7层 由于多层嵌套的IF函数构造比较复杂 容易出错 多个条件的情况 还是推荐用函数VLOOKUP来实现 数据输入方法 用字符提取函数RIGHT LEFT MID输入 选读 许多数据包含不同的信息 例如18位身份证号码中就包含了出生地 出生日期 性别等信息 身份证中不同位置的数字对应的信息如下 要从身份证号码中提取相应的信息 就需要用以上三个函数 数据输入方法 设身份证号码单元格为A2 提取出生省份 LEFT A2 2 10000提取出生市 MID A2 3 2 100提取出生县 MID A2 3 4 提取出生日期 MID A2 7 4 MID A2 11 2 MID A2 13 2 提取序号 RIGHT A2 4 提取性别 MOD MID A2 17 1 2 返回值1为男性 0为女性 数据输入方法 用查找函数VLOOKUP输入 选读 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 VLOOKUP中的 V 代表垂直 VLOOKUP lookup value table array col index num range lookup lookup value为需要在数组第一列中查找的数值 col index num为table array中待返回的匹配值的列序号 range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE 1 或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 0 函数VLOOKUP将返回精确匹配值 table array为需要在其中查找数据的数据表 对于非精确匹配查找 table array的第一列必须排序 对于精确匹配查找 则不必排序 数据输入方法 VLOOKUP B3 G 2 G 6 2 0 VLOOKUP B2 G 2 G 6 2 0 VLOOKUP B13 G 2 G 6 2 0 用VLOOKUP函数可以实现多个条件数据的查找和输入 数据输入方法 VLOOKUP在计算个人所得税中的应用 B13 B14 VLOOKUP B13 B14 A2 E10 4 1 VLOOKUP B13 B14 A2 E10 5 1 数据输入方法 条件格式 工具是将符合一定的条件的单元格的格式 边框 底色 文本格式 文本颜色等 改变为用户定制的形式 条件格式常用于突出显示某些单元格的内容 也可以用于美化Excel表格 例如 在根据考核等级评定奖金的Excel表中 将考核等级为A用红色表示 选定 考核等级 B2 B13 打开Excel菜单 格式 条件格式 条件格式工具 1 2Excel基础 在 条件1 下拉菜单中选择 单元格数值 中间下拉菜单选择 等于 右侧下拉菜单输入 A 然后单击 格式 选定文本字体和颜色 单击 确定 条件格式工具 1 2Excel基础 得到效果如下 条件格式工具 1 2Excel基础 在 条件格式 对话窗口中单击 添加 可以增加第二个条件 条件格式 最多可以有三个条件 条件格式工具 1 2Excel基础 设置两个条件的 条件格式 产生的效果如下图 如果设置的多个条件是互相冲突的 不会出现任何提示 所定义的格式也不会起作用 条件格式工具 1 2Excel基础 并不是所有的Excel的数据都需要用键盘输入 很多数据来自网络或其他信息系统 Excel读入这些数据后 往往需要根据数据不同的属性 将这些数据分为不同的列 Excel菜单 数据 分列 的数据分列工具就可以完成这一工作 Excel读入的数据有三种情况 字段之间有空格 逗号 制表符等分割符 字段没有任何分隔符 但每个字段的长度是确定的 字段之间既没有分隔符 字段的长度也各不相同 对于前两种情况 Excel分列工具可以方便地实现分列 第三种情况则需要用户分析数据的特点 在字段之间插入分隔符 然后再实现数据分列 数据分列工具 1 2Excel基础 工作表和单元格单元格的格式数据输入方法条件格式工具数据分列工具 从网上搜素并下载 身份证号码和地址对照表 如下 这个表只有一列 其中包括身份证号的前六位和相应的地址 两个字段之间有空格分隔 需要把身份证号和地址分为两列 数据分列工具 1 2Excel基础 打开Excel菜单 数据 分列 弹出 文本分列向导 选择 分隔符号 单击 下一步 数据分列工具 在 分隔符号 中选定 空格 预览中出现分列的演示 单击 下一步 数据分列工具 设置数据的类型为 常规 单击 完成 数据分列工具 这样就完成了数据分列 数据分列工具 注意到这个数据的第一个字段 身份证号 是六个字符 因此也可以用 固定宽度 分列 选定 固定宽度 单击 下一步 数据分列工具 用户可以用 拖动分列线 自定义列宽 单击 下一步 数据分列工具 同样可以完成分列 数据分列工具 实际应用中 所要分析的对象 公司 政府部门 人等 通常具有多个不同的属性 例如 部门 姓名 性别 年龄 职务 职称 专业等 在数据统计表中 这些数据称为 属性数据 1 3属性数据分析 第1章数据和数据分析 1 1数据1 2Excel基础1 3属性数据分析1 4数据展示图1 5数值型数据分析 实际应用中 所要分析的对象 公司 政府部门 人等 通常具有多个不同的属性 例如 部门 姓名 性别 年龄 职务 职称 专业等 在数据统计表中 这些数据称为 属性数据 1 3属性数据分析 运用数据透视表可以对属性数据进行统计分析 1 3 1数据透视表1 3 2单变量属性数据分析1 3 3双变量属性数据分析1 3 4数据透视表在问卷调查中的应用 数据透视表是Excel中的功能强大的工具 运用数据透视表可以对大量数据进行快速汇总 创建相应的多维统计表格 1 3属性数据分析 1 3 1数据透视表 1 3属性数据分析 1 3 1数据透视表 例如 创建汇总统计表格 1 3属性数据分析1 3 2单变量属性数据分析 用途 对性别 职称 品牌等单列属性数据进行分布统计 1 分析该研究所 男 女职工 的人数 步骤 1 打开 数据 菜单 2 选择 数据透视表 按下一步 3 选择 布局 4 单击变量 性别 拖到 行 区域中 5 单击变量 性别 拖到 数据 区域中 6 双击 求和项 在 汇总方式 中选择 计数 7 单击 确定 1 3 2单变量属性数据分析 1 3属性数据分析 示例 对济南市饭店业进行前景调查 问卷数据如下表所示 要求统计出业主类型的分类人数 1 3属性数据分析 1 3 2单变量属性数据分析 分析研究所的 职称结构 1 3属性数据分析 例1 2 打开教学光盘中的 研究所员工资料 1 3 2单变量属性数据分析 分析各部门中 男 女职工 的人数 1 3属性数据分析 例1 2 打开教学光盘中的 研究所员工资料 1 3 3双变量属性数据分析 两维表格 列联表 用途 对性别 职称等属性数据进行相关关系描述 1 3 1数据透视表1 3 2单变量属性数据分析1 3 3双变量属性数据分析1 3 4数据透视表在问卷调查中的应用 数据透视表 二维列联表 1 3属性数据分析 1 3 3双变量属性数据分析 两维表格 列联表 步骤 1 打开 数据 菜单 2 选择 数据透视表 按下一步 3 选择 版式 4 单击变量 部门 拖到 行 区域中 5 单击变量 性别 拖到 列 区域中 5 单击变量 姓名 拖到 数据 区域中 6 双击 求和项 在 汇总方式 中选择 计数 7 单击 确定 打开菜单 数据 数据透视表和数据透视图 1 3属性数据分析 1 3 3双变量属性数据分析 选择图表类型 选择数据区域 1 3属性数据分析 确定数据透视表生成的位置 1 3属性数据分析 接着 选择 布局 1 3 3双变量属性数据分析 分别将 部门 拉到行区域 性别 拉到列区域 姓名 拉到数据区域 1 3属性数据分析 1 3 3双变量属性数据分析 得到数据透视表 1 3属性数据分析 1 3 3双变量属性数据分析 继续 单击 部门 单元格的下拉菜单 可以选择统计的范围 例如 部门 中不包括 办公室 1 3属性数据分析 1 3 3双变量属性数据分析 得到不包括部门 办公室 的人数统计 1 3属性数据分析 1 3 3双变量属性数据分析 为什么拉到数据区域的是 姓名 数据区域出现的数字是 人数 右键单击数据区域 出现右键菜单 选定 字段属性 1 3属性数据分析 1 3 3双变量属性数据分析 可以看到默认的字段属性为 计数 因此数据字段出现的数字是人数 1 3属性数据分析 1 3 3双变量属性数据分析 2 分析 部门 职称 性别 学历 平均工资 1 3属性数据分析 1 3 3双变量属性数据分析 生成三维表格 在二维列联表上 把 学历 拉到 性别 下面 就可以产生三维列联表 1 3属性数据分析 1 3 3双变量属性数据分析 生成三维表格 1 3属性数据分析 1 3 3双变量属性数据分析 生成三维表格 或者 在二维列联表上 把 职称 拉到 部门 右边 也可以产生三维列联表 1 3属性数据分析 1 3 3双变量属性数据分析 生成三维表格 或者 在二维列联表上 把 职称 拉到 部门 右边 也可以产生三维列联表 1 3属性数据分析 1 3 3双变量属性数据分析 生成三维表格 把 职称 拉到 部门 右边 把 学历 拉到 性别 下面 就可以产生四维列联表 1 3属性数据分析 1 3 3双变量属性数据分析 生成四维表格 1 3属性数据分析 1 3 3双变量属性数据分析 生成四维表格 把 职称 拉到 部门 右边 把 学历 拉到 性别 下面 把 姓名 拖出数据透视表外 把 月薪 拉进数据区域 就可以产生数据区域为 月薪 的四维列联表 1 3属性数据分析 1 3 3双变量属性数据分析 生成四维表格 且数据区域为 月薪 把 职称 拉到 部门 右边 把 学历 拉到 性别 下面 把 月薪 拉到数据区域 就可以产生数据区域为 月薪 的四维列联表 1 3属性数据分析 1 3 3双变量属性数据分析 生成四维表格 且数据区域为 月薪 右键单击数据区域 选择 字段设置 将数据区域的属性定义为 平均工资 则数据区域的数字是 合计工资 同样的方法 也可以求 最高工资 最低工资 或 求和 1 3属性数据分析 1 3 3双变量属性数据分析 生成四维表格 且数据区域为 月薪 四维列联表如下 1 3属性数据分析 1 3 3双变量属性数据分析 生成四维表格 且数据区域为 月薪 1 4数据展示图 1 1数据1 2Excel基础1 3属性数据分析1 4数据展示图1 5数值型数据分析 Excel数据展示图形有13种 柱形图垂直柱状组成的图形称为柱形图 水平条状组成的图形称为条形图 1 4数据展示图 例1 7创建我国2001 2004年第一 第二和第三产业产值数量的变化的柱形图 1 打开Excel表 单击 插入图标 工具图标 选择图表类型 柱形图和子图标类型 点击 下一步 用图表和曲线图展示数据 1 4数据展示图 图表向导 2 从 数据区域 选择目标数据 用鼠标选定B4 D8 单击 系列 卡片 点击 下一步 用图表和曲线图展示数据 1 4数据展示图 3 在 系列 中 选择输入系列名称 用鼠标选定 分类 X 轴标志 A5 A8 单击 下一步 用图表和曲线图展示数据 1 4数据展示图 4 输入或修改 标题 坐标轴 网格线 图例 数据标志 数据表 等属性 单击 下一步 用图表和曲线图展示数据 1 4数据展示图 5 选择图表位置 单击 完成 用图表和曲线图展示数据 1 4数据展示图 6 图表完成 如果需要 可以双击图表中任何一部分进行修改 用图表和曲线图展示数据 图表区 分类轴 分类轴标题 数值轴 图例 图表标题 系列 第一产业 绘图区 数值轴主要网格线 数值轴标题 系列 第二产业 系列 第三产业 1 4数据展示图 条形图的制作是类似柱形图 垂直柱状组成的图形称为柱形图 水平条状组成的图形称为条形图 例1 8 我国2001 2004年三次产业的条形图如下 条形图 1 4数据展示图 例1 92004年我国三类产业产值 饼图 饼图常用来表示一项数据中各部分的比例 1 4数据展示图 折线图 常用来表示数据随时间或项目不同的变化 例1 101978年 2004年我国能源消费总量及组成 单位 万吨标煤 1 4数据展示图 试创建 1978 2004年中国能源消费 xls 中的 总量 煤炭 石油 天然气 水电 消费量的折线图 1 4数据展示图 折线图 例1 11原油消费量和天然气消费量的散点图 散点图 用于表示两个变量数值之间的相关关系 1 4数据展示图 例1 12有两家企业A和B 它们的资产属性评估结果如下表 雷达图 用来表示两组变量相应数值的比较 1 4数据展示图 例1 132004年10省市的GDP和人均GDP数据 气泡图 可以对一组数据的三个指标进行展示和比较 1 4数据展示图 例1 132004年10省市的GDP和人均GDP数据 1 4数据展示图 气泡的高度表示GDP总量的大小 气泡的大小表示人均GDP的高低 数值型数据分析的主要内容 包括 1 5 1 数值型单变量数据分析1 5 2 数值型双变量数据分析 1 5数值型数据分析 Excel中的数值型数据统计功能有以下二种实现方法使用Excel菜单的数据分析工具使用Excel统计函数 1 1数据1 2Excel基础1 3属性数据分析1 4数据展示图1 5数值型数据分析 1 5数值型数据分析 1 5 1 数值型单变量数据分析例某班 DMD 考试成绩如下 808576786793889075666577748381708364966079868071试用相关统计量对成绩进行分析 Excel有3种分析工具用来描述数值型单变量数据 1 描述性统计2 直方图3 排位和百分比排位 1 5 1 数值型单变量数据分析 描述性统计 数据的分布属性 数据的分布属性包括数据的集中属性 数据的分散属性以及数据频数的形态三大类 数据集中属性的指标均值Average中位数Median众数Mode数据的分散属性的指标极差Range方差Variance标准差StandardDeviation标准误StandardError数据频数的分布形态指标偏度Skewness峰度Kurtosis 1 5 1 数值型单变量数据分析 描述性统计 数据的分布属性 数据的分布属性包括数据的集中属性 数据的分散属性以及数据频数的形态三大类 数据集中属性的指标均值Average中位数Median众数Mode数据的分散属性的指标极差Range方差Variance标准差StandardDeviation标准误StandardError数据频数的分布形态指标偏度Skewness峰度Kurtosis 1 5 1 数值型单变量数据分析 Excel中的值型单变量数据统计功能有以下三种实现方法1 描述性统计2 直方图3 排位和百分比排位 步骤 1 打开 工具 菜单 2 选择 数据分析 3 选择 描述统计 4 根据对话框 选择或输入相关数据 C MyDocuments 模拟成绩 xls 描述性统计 数据的分布属性 1 5 1 数值型单变量数据分析 步骤 单击 工具 数据分析 描述性统计 数据的分布属性 例某班 DMD 考试成绩统计 打开Excel菜单 工具 数据分析 描述统计 选择 描述统计 单击 确定 描述性统计 数据的分布属性 1 5 1 数值型单变量数据分析 步骤 选择 输入区域 分组方式 在 输出选项 中选择 新工作表组 和新工作表名称 选定 汇总统计 单击 确定 DMD 描述性统计 数据的分布属性 步骤 得到 DMD 考试成绩的汇总统计结果 注 Excel2003中文版描述统计工具输出结果中 区域 是 Range 的误译 应该译为 全距 描述性统计 数据的分布属性 算术平均数 Average 设总体包括N个观察值x1 x2 xN 则总体平均值 均值 为 设n个样本为x1 x2 xn 样本平均值 均值 为 描述性统计 数据的分布属性 有关指标说明 1 5 1 数值型单变量数据分析 AVERAGE B2 B24 一组数据按大小顺序排列以后 处于中间位置的数据 对于奇数组 中位数是中间的一个 对于偶数组 中位数是中间的两个的平均值 中位数 Median MEDIAN B2 B24 1 5 1 数值型单变量数据分析 众数是一组数据出现次数最多的数值 如果一组数据各不相同 则这组数据不存在众数 众数 Mode MODE B2 B24 1 5 1 数值型单变量数据分析 全距是一组数据x1 x2 xn中最大值和最小值之差 即 max x1 x2 xn min x1 x2 xn 全距可以表示数据的离散程度 全距越大离散程度越大 全距只和数据中两个数值有关 容易受到奇异数据的影响 数据A的全距 18 11 7 数据B的全距 39 3 36 数据B的离散程度大于数据A 全距 Range 离散属性的测度 1 5 1 数值型单变量数据分析 方差分为总体方差和样本方差 总体方差的计算公式为 其中xi为第i个数据 N为总体数据的个数 为总体平均数 方差是数据离散程度的指标 方差越大 离散程度越大 方差的单位是数据单位的平方 Excel计算总体方差的函数是VARP 方差 Variance 1 5 1 数值型单变量数据分析 方差 Variance 样本方差的计算公式为 由于实际处理的数据大多数都是样本数据 因此样本方差应用更多 Excel计算样本方差的函数是VAR 1 5 1 数值型单变量数据分析 标准差 Standarddeviation 标准差是方差的平方根 计算总体标准差的公式为 计算样本标准差的公式为 标准差的单位和数据的单位相同 因此比方差更容易理解 应用也更多 1 5 1 数值型单变量数据分析 在工业生产中 产品的指标总会出现波动 一般会把产品指标的变动控制在指标平均值加减3个标准差的范围内 认为这是生产正常的标志 如果产品指标波动超出这个范围 说明生产系统不正常 标准差的重要性 1 5 1 数值型单变量数据分析 在投资风险分析中 评价投资收益有两个指标 一个是收益期望值 平均值 另一个是收益的标准差 投资收益的标准差表示投资风险的大小 标准差越大 风险越大 收益期望值 收益标准差 低收益低风险 低收益高风险 高收益低风险 高收益高风险 1 5 1 数值型单变量数据分析 在经济领域中 标准差常用来反映未来数据的不确定性 有主要的理论和应用价值 标准差是测定数据离散程度的标志 但由于不同的数据数值大小不同 相同的离散程度 数值比较大的标准差也会比较大 以下是不同年龄组每周上网时间的统计数据 为了比较大小不同的几组数据的离散程度 定义如下的变异系数 CoefficientofVariation 变异系数 1 5 1 数值型单变量数据分析 正态CS 0 57 左偏CS 0 92 右偏CS 0 27 偏态系数 CoefficientofSkewness 平均数 中位数 众数对称分布平均数 中位数 众数右偏众数 中位数 平均数左偏 分布形态的测度 1 5 1 数值型单变量数据分析 Excel中有计算数据偏度的函数 SKEW 数据范围 Excel的偏度公式为 有的文献计算数据偏度还有其他的公式 例如SPSS计算偏度的公式为 1 5 1 数值型单变量数据分析 分布形态的测度 峰态系数 测定分布形态的峰态系数 CoefficientofKurtosis CK0尖峰分布 CK 1 13 CK 18 24 1 5 1 数值型单变量数据分析 Excel中有计算数据峰度的函数KURT Excel的偏度公式为 有的文献计算数据峰度度还有其他的公式 例如SPSS计算偏度的公式为 1 5 1 数值型单变量数据分析 Excel以上对数据的描述统计的每一项内容 也可以单独用相应的Excel函数计算 这些函数是 均值AVERAGE中位数MEDIAN众数MODE标准差STDEVP 总体 STDEV 样本 方差VARP 总体 VAR 样本 偏度SKEW峰度KURT最大值MAX最小值MIN求和SUM计数COUNT 1 5 1 数值型单变量数据分析 直方图 Excel中的数值型单变量数据统计功能有以下三种实现方法1 描述性统计2 直方图3 排位和百分比排位 该统计工具提供一张频数分布表和一张直方图 步骤 1 确定直方图的区间个数 填入每个组界值 2 打开 工具 菜单 3 选择 数据分析 4 选择 直方图 5 在 直方图 对话框中填入数据 C MyDocuments 模拟成绩 xls 1 5 1 数值型单变量数据分析 例绘制 某班 DMD 考试成绩 频数直方图 首先 计算 DMD 考试成绩的最小值和最大值 其次 确定频数统计的分布区间 直方图 Excel中的统计功能有以下三种实现方法1 描述性统计2 直方图3 排位和百分比排位 1 5 1 数值型单变量数据分析 打开菜单 工具 数据分析 出现以下窗口 选择 直方图 单击 确定 Excel 工具 数据分析 直方图 1 5 1 数值型单变量数据分析 直方图 输入区域 为B2 B25 接收区域 为G2 G7 输出区域 为I17 选定 图表输出 单击 确定 1 5 1 数值型单变量数据分析 直方图 Excel 工具 数据分析 直方图 对话框 得到某班 DMD 考试成绩的频数和频数直方图 J 2 1 5 1 数值型单变量数据分析 直方图 即有 在指定位置I17生成发生频数统计表和频数直方图 注 Excel2003中文版直方图工具输出结果中 frequency误译为 频率 在统计学中应译为 频数 1 5 1 数值型单变量数据分析 直方图 书中例1 15 绘制 饮料全年日销售量 频数直方图 首先 计算365天销售量的最小值和最大值 其次 确定频数统计的分布区间 直方图 Excel中的统计功能有以下三种实现方法1 描述性统计2 直方图3 排位和百分比排位 1 5 1 数值型单变量数据分析 打开菜单 工具 数据分析 出现以下窗口 选择 直方图 单击 确定 Excel 工具 数据分析 直方图 输入区域 为B2 B366 接收区域 为H2 H15 输出区域 为J2 选定 累积百分率 和 图表输出 单击 确定 得到饮料销售量356个数据的频数和频数直方图 J 2 直方图 即有 在指定位置J2生成发生频数统计表和频数直方图 用Excel函数FREQUENCY生成数据频数图 先用鼠标选择准备好的频数区域I2 I15 然后在函数栏中输入函数 FREQUENCY 从提示可以看出 这个函数有两个参数 第一个参数data array为销售量B2 B366 第二个参数bin array为区间H2 H15 直方图 输入完毕后 按组合键 CTRL SHIFT ENTER 频数就出现在I2 I15区域中 Excel有一些函数 它们的输出结果不是产生的一个单元格中 而是同时产生的一个区域中 这样的函数称为 向量函数 FREQUENCY就是一个向量函数 用Excel函数FREQUENCY生成数据频数图 直方图 绘制I2 I15频数柱形图如图1 54 得到和 工具 数据分析 直方图 同样的结果 用Excel函数FREQUENCY生成数据频数图 直方图 该统计工具提供一张含有升序排列的数据表格以及其排列后的序号 百分位号 步骤 1 打开 工具 菜单 2 选择 数据分析 3 选择 排位与百分比排位 4 根据对话框 选择或输入相关数据 排位和百分比排位 1 5 1 数值型单变量数据分析 Excel中的统计功能有以下三种实现方法1 描述性统计2 直方图3 排位和百分比排位 排位和百分比排位 1 5 1 数值型单变量数据分析 Excel中的统计功能有以下三种实现方法1 描述性统计2 直方图3 排位和百分比排位 得到某班 DMD 考试成绩的数据排序表格 1 5 2 数值型双变量数据分析 对两组数值型数据分析它们之间的相关关系 有两种分析工具 1 散点图2 相关系数 变量之间的因果关系固定资产投资增加和GDP增长二氧化碳排放量和平均气温上升吸烟数量和年限与肺癌的发病率 变量之间的相关关系 变量之间的相关关系 海水平均温度与灾害性天气天数 厄尔尼诺现象 公司高尔夫球水平与公司股票市场表现 超市啤酒销售量和纸尿布销售量 1 5 2 数值型双变量数据分析 散点图 该统计工具用来描述两个变量之间的关系 通常用在直观判断基本关系或用在回归模型使用前 步骤 1 打开Excel表 输入变量X和Y的数值 2 选中数据 3 单击 图表向导 4 选择图表类型 XY散点图5 对散点图进行编辑 1 5 2 数值型双变量数据分析 有两种分析工具描述数据之间的相关关系 1 散点图2 相关系数 例 某公司欲调查办公楼的租金和空置率间的关系 工作人员在10个不同的城市里 对办公楼中每平方米的月租金和空置率的数据记录如下 试作散点图 并判断两变量间的相关性 散点图 散点图 相关系数用来说明两个变量之间的相关密切程度 步骤 1 打开 工具 菜单 2 选择 数据分析 3 选择 相关系数 4 根据对话框 选择或输入相关数据 相关系数 1 5 2 数值型双变量数据分析 有两种分析工具描述数据之间的相关关系 1 散点图2 相关系数 选择 相关系数 相关系数 步骤 选择变量范围 输出选项 选择 新工作表组 单击 确定 步骤 相关系数 相关系数 相关系数的基本计算公式 两个数列的协方差 自变量数列的标准差 因变量数列的标准差 相关程度判断标准 1 当R 1时 X与Y为线性相关 2 当 R 0 3时 X与Y为微弱相关 3 当0 3 R 0 5时 X与Y为低度相关 4 当0 5 R 0 8时 X与Y为显著相关 5 当0 8 R 1时 X与Y为高度相关 R越接近1 变量的相关关系越明显 相关系数 5 1一元线性回归5 2多元线性回归5 3非线性回归 第5章回归分析 一 数据与统计第1章数据和数据分析第5章回归分析第6章统计预测 5 1一元线性回归 回归分析是一种分析随机变量之间相关关系的统计理论和统计方法 可以根据自变量的取值 预测相应的因变量的取值 在工业 农业 商业以及经济学 社会学 医学等领域有着非常广泛的应用 一些变量之间存在相关关系 例如一个城市的空调产品的销售量和夏季平均气温或冬季平均气温有相关关系 股票市场的价格和银行存贷款利率水平有相关关系 如果能够建立这些相关关系的数量表达式 就可以根据一个变量的值来预测另一个变量的变化 回归分析的目的就是构建变量之间数量关系的模型 回归分析包括只有一个自变量的一元回归和包括多个自变量的多元回归 只有一个自变量的