Excel统计分析实例精讲 ■■■■■ (7)完成后单击【下一步】按钮，出现【图表向导-4步骤之4-图表位置】对话框，默 认将工作表例1.6中的一个对象插入，单击【完成】按钮。 工作表中即插入一个图表，可以直接对图表进行拖动将其放至合适的位置，或单击图 表区域，通过拖动图表区四周出现6个小黑正方形来调整图表的大小。 最终按指数值分组的累积曲线如图1-34所示。 上在和店指台针信 图表工具栏 14 图1-34指数值的累积曲线 (8)为使图形更清晰，可以去掉网格线和绘图区背景，单击任意一网格线，按Delete 键删除网格线，单击绘图区域的背景部分，按Delete键删除背景，最终结果如图1-35所示。 上廷180技指数分组素积曲线 累积曲线 2409 2400.0-2599. 2500.0-2490. 2800.8-2700.0 20.0-300.0 2800.0-290. 27.0-2000 3108.0-3209 30.0-3100 3290.9-300. 罗数 图1-35 调整后的指数值的累积曲线 1.3.4直方图 直方图(histogram)也是一种重要的数据描述方法，直方图由满足以下条件的矩形构 成：(1)以水平轴为底：(2)对应的宽度为组距：(3)面积的大小与组的频数成比例。 直方图在Excel中是一种常用的分析图表，而且Excell的数据分析工具中即专门包含直方图 命令。运用数据分析中的直方图命令，可快速计算数据区域内的单个和累积频率，也可用 此工具统计数据集中某个数值出现的次数。 数据分析是Excel附带的众多宏中的一种，宏是一系列命令和函数，存储于Visual Basic模块中，并且在需要执行该项任务时可随时运行，Excel的宏命令使得一系列常用而 ·22·■
Excel 统计分析实例精讲 · 22 · （7）完成后单击【下一步】按钮，出现【图表向导-4步骤之4-图表位置】对话框，默 认将工作表例1.6中的一个对象插入，单击【完成】按钮。 工作表中即插入一个图表，可以直接对图表进行拖动将其放至合适的位置，或单击图 表区域，通过拖动图表区四周出现6个小黑正方形来调整图表的大小。 最终按指数值分组的累积曲线如图1-34所示。 图 1-34 指数值的累积曲线 （8）为使图形更清晰，可以去掉网格线和绘图区背景，单击任意一网格线，按Delete 键删除网格线，单击绘图区域的背景部分，按Delete键删除背景，最终结果如图1-35所示。 图 1-35 调整后的指数值的累积曲线 1.3.4 直方图 直方图（histogram）也是一种重要的数据描述方法，直方图由满足以下条件的矩形构 成：（1）以水平轴为底；（2）对应的宽度为组距；（3）面积的大小与组的频数成比例。 直方图在Excel中是一种常用的分析图表，而且Excel的数据分析工具中即专门包含直方图 命令。运用数据分析中的直方图命令，可快速计算数据区域内的单个和累积频率，也可用 此工具统计数据集中某个数值出现的次数。 数据分析是Excel附带的众多宏中的一种，宏是一系列命令和函数，存储于Visual Basic 模块中，并且在需要执行该项任务时可随时运行，Excel的宏命令使得一系列常用而 图表工具栏 累积曲线
第1章统计基础与数据描述 复杂的操作仅通过运行宏就可以一步到位，因此极大地提高了数据处理的效率。 下面举例说明如何在Excel中运用分析工具中的直方图宏命令绘制直方图。 例1.7采用分析工具绘制直方图 表1-7给出了上证180指数2002年`2004年月收益率，试采用分析工具库中的直方图命令 生成收益率的直方图和累积曲线。 表1-7上证180指数月收益率 上证180指数收益率 时间 收益率 时间 收益率 时间 收益率 2002-1 2003-1 0.0912 2004-1 0.0673 2002-2 0.0144 2003-2 -0.0006 2004-2 0.0402 2002-3 0.0250 2003-3 0.0062 2004-3 0.0232 2002-4 0.0296 2003-4 0.0256 2004-4 -0.0936 2002-5 -0.0841 2003-5 0.0442 2004-5 -0.0320 2002-6 0.1893 2003-6 -0.0543 2004-6 -0.1032 2002-7 -0.0485 2003-7 0.0052 2004-7 0.0079 2002-8 -0.0045 2003-8 -0.0420 2004-8 -0.0272 2002-9 -0.0547 2003-9 -0.0446 2004-9 0.0451 2002-10 -0.0461 2003-10 -0.0103 2004-10 -0.0534 2002-11 -0.0563 2003-11 0.0346 2004-11 0.0153 2002-12 -0.0366 2003-12 0.0548 2004-12 -0.0515 具体操作步骤如下： (1)新建一工作表“例1.7”，设置表头“上证180指数收益率直方图”，输入表1-7 中已知的时间段及对应收益率。 (2)设定分组临界点，此处设定从-0.12开始至0.20，每0.04个单位为一区间，对应9 个临界点内的8个区间。单击E4单元格，输入“-0.12”，单击E5单元格，在编辑栏输入“= E4+0.04”,拖动鼠标至E12单元格，运用自动填充单元格命令完成其他临界点的确定，如 图1-36所示。 上证10指数收益率直方图 收查事 1002=1 接收区成 设定接收区域 42002-20.0144 0.12 52002-3 0.0250 0,08 1002-4 00296 -0.04 72002.. 0.0841 0 2002-60.1893 2002- 0.08 102002-8-00045 0.12 112002-00.0511 016 22002-1000461 02 图1-36设定接收区域临界点 (3)单击【工具】/【加载宏】，出现【加载宏】对话框，单击选中【分析工具库】 1·23·= 1i
第 1 章 统计基础与数据描述 · 23 · 复杂的操作仅通过运行宏就可以一步到位，因此极大地提高了数据处理的效率。 下面举例说明如何在Excel中运用分析工具中的直方图宏命令绘制直方图。 例 1.7 采用分析工具绘制直方图 表1-7给出了上证180指数2002年~2004年月收益率，试采用分析工具库中的直方图命令 生成收益率的直方图和累积曲线。 表1-7 上证180指数月收益率 上证180指数收益率 时间 收益率 时间 收益率 时间 收益率 2002-1 2003-1 0.0912 2004-1 0.0673 2002-2 0.0144 2003-2 -0.0006 2004-2 0.0402 2002-3 0.0250 2003-3 0.0062 2004-3 0.0232 2002-4 0.0296 2003-4 0.0256 2004-4 -0.0936 2002-5 -0.0841 2003-5 0.0442 2004-5 -0.0320 2002-6 0.1893 2003-6 -0.0543 2004-6 -0.1032 2002-7 -0.0485 2003-7 0.0052 2004-7 0.0079 2002-8 -0.0045 2003-8 -0.0420 2004-8 -0.0272 2002-9 -0.0547 2003-9 -0.0446 2004-9 0.0451 2002-10 -0.0461 2003-10 -0.0103 2004-10 -0.0534 2002-11 -0.0563 2003-11 0.0346 2004-11 0.0153 2002-12 -0.0366 2003-12 0.0548 2004-12 -0.0515 具体操作步骤如下： （1）新建一工作表“例1.7”，设置表头“上证180指数收益率直方图”，输入表1-7 中已知的时间段及对应收益率。 （2）设定分组临界点，此处设定从-0.12开始至0.20，每0.04个单位为一区间，对应9 个临界点内的8个区间。单击E4单元格，输入“-0.12”，单击E5单元格，在编辑栏输入“= E4 +0.04”，拖动鼠标至E12单元格，运用自动填充单元格命令完成其他临界点的确定，如 图1-36所示。 图 1-36 设定接收区域临界点 （3）单击【工具】/【加载宏】，出现【加载宏】对话框，单击选中【分析工具库】 设定接收区域
Excel统计分析实例精讲 ■■■■■ 和【分析工具库-VBA函数】复选框，如图1-37所示，完成后单击【确定】按钮。 加量室 选择分析工具库 可用如软虫a): 家消 克)： 直精化).· 分斯工具库一EA属数 为分新工其库提供的理函数， 图1-37 【加载宏】对话框 说明：在典型或简单安装Excl中，未包含宏的安装，此时需要单击【加载宏】进 行加载，Excel会自动要求插入安装盘进行安装并加载；但如果Excel采用的是完 全安装，则系统会直接从己安装的程序中自动加载。 (4)选择【工具】菜单中的【数据分析】命令，出现【数据分析】对话框，在【分析 工具】列表中单击【直方图】，如图1-38所示。 数分新 身折工其少 工排本方莲 子灯信的底叶二丰升桥 选择直方图工具 图1-38【数据分析】对话框 (5)单击【确定】按钮，出现【直方图】对话框（见图1-39），单击【输入区域】后 的折叠按钮，将对话框折叠，选择B4:B38单元格区域，单击打开折叠按钮回，返回【直 方图】对话框，单击【接收区域】后的折叠按钮，选择接收区域对应的E4:E12单元格， 单击打开折叠按钮同，返回【直方图】对话框。 方 选择输入区域 健信室减☒ 铁结 @出道莱的 。根工作地包 选择接收区域 0数工作满D 24·
Excel 统计分析实例精讲 · 24 · 和【分析工具库-VBA函数】复选框，如图1-37所示，完成后单击【确定】按钮。 图 1-37 【加载宏】对话框 说明：在典型或简单安装Excel中，未包含宏的安装，此时需要单击【加载宏】进 行加载，Excel会自动要求插入安装盘进行安装并加载；但如果Excel采用的是完 全安装，则系统会直接从已安装的程序中自动加载。 （4）选择【工具】菜单中的【数据分析】命令，出现【数据分析】对话框，在【分析 工具】列表中单击【直方图】，如图1-38所示。 图 1-38 【数据分析】对话框 （5）单击【确定】按钮，出现【直方图】对话框（见图1-39），单击【输入区域】后 的折叠按钮 ，将对话框折叠，选择B4:B38单元格区域，单击打开折叠按钮 ，返回【直 方图】对话框，单击【接收区域】后的折叠按钮 ，选择接收区域对应的E4:E12单元格， 单击打开折叠按钮 ，返回【直方图】对话框。 选择直方图工具 选择输入区域 选择接收区域 选择分析工具库
■ 第1章统计基础与数据描述 图1-39【直方图】对话框 (6)单击选中【输出区域】单选按钮，单击【输出区域】后的折叠按钮，单击E14 单元格，单击打开折叠按钮回，返回【直方图】对话框。分别单击选中【累积百分率】和 【图表输出】复选框，如图1-39所示。 说明：【直方图】对话框。 【输入区域】：在此输入待分析数据区域的单元格引用。 【接收区域】：可选，在此输入接收区域的单元格引用，该区域包含一组可选的用 来定义接收区域的边界值。这些值应当按升序排列。Excl将统计在当前边界值和 相邻的较高边界值之间的数据点个数（如果存在）。如果数值等于或小于边界值， 侧该值将被归到以该边界值为上限的区域中进行计数。所有小于第一个边界值的 数值将一同计数，同样所有大于最后一个边界值的数值也将一同计数。如果省略 此处的接收区域，Excl将在数据的最小值和最大值之间创建一组均匀分布的接收 区间。 【标志】：如果输入区域的第一行或第一列中包含标志项，请选中此复选框。如果 输入区域没有标志项，请清除此复选框，Excel将在输出表中生成适宜的数据标志。 【输出区域】：在此输入对输出表左上角单元格的引用。如果输出表将覆盖己有的 数据，Excel会自动确定输出区域的大小并显示一则消息。 【新工作表组】：单击此选项可在当前工作簿中插入新工作表，并由新工作表的A1 单元格开始粘贴计算结果。若要为新工作表命名，请在右侧的框中输入名称。 【新工作簿】：单击此选项可创建一新工作簿，并在新工作簿的新工作表中粘贴计 算结果。 【柏拉图】：选中此复选框可在输出表中按降序来显示数据。如果此复选框被清除， Exce1将只按升序来显示数据并省略最右边包含排序数据的三列数据。 【累积百分率】：选中此复选框可在输出表中生成一列累积百分比值，并在直方图 中包含一条累积百分比线。如果清除此选项，则会省略累积百分比。 【图表输出】：选中此复选框可在输出表中生成一个嵌入直方图。 (7)完成后单击【确定】按钮，即可得到对应的直方图和频率表，结果如图1-40所示。 ■204+0043 56- 直方 10.0g% ·-君份， 00 ■·25 1i
第 1 章 统计基础与数据描述 · 25 · 图 1-39 【直方图】对话框 （6）单击选中【输出区域】单选按钮，单击【输出区域】后的折叠按钮 ，单击E14 单元格，单击打开折叠按钮 ，返回【直方图】对话框。分别单击选中【累积百分率】和 【图表输出】复选框，如图1-39所示。 说明：【直方图】对话框。 【输入区域】：在此输入待分析数据区域的单元格引用。 【接收区域】：可选，在此输入接收区域的单元格引用，该区域包含一组可选的用 来定义接收区域的边界值。这些值应当按升序排列。Excel将统计在当前边界值和 相邻的较高边界值之间的数据点个数（如果存在）。如果数值等于或小于边界值， 则该值将被归到以该边界值为上限的区域中进行计数。所有小于第一个边界值的 数值将一同计数，同样所有大于最后一个边界值的数值也将一同计数。如果省略 此处的接收区域，Excel将在数据的最小值和最大值之间创建一组均匀分布的接收 区间。 【标志】：如果输入区域的第一行或第一列中包含标志项，请选中此复选框。如果 输入区域没有标志项，请清除此复选框，Excel将在输出表中生成适宜的数据标志。 【输出区域】：在此输入对输出表左上角单元格的引用。如果输出表将覆盖已有的 数据，Excel会自动确定输出区域的大小并显示一则消息。 【新工作表组】：单击此选项可在当前工作簿中插入新工作表，并由新工作表的A1 单元格开始粘贴计算结果。若要为新工作表命名，请在右侧的框中输入名称。 【新工作簿】：单击此选项可创建一新工作簿，并在新工作簿的新工作表中粘贴计 算结果。 【柏拉图】：选中此复选框可在输出表中按降序来显示数据。如果此复选框被清除， Excel将只按升序来显示数据并省略最右边包含排序数据的三列数据。 【累积百分率】：选中此复选框可在输出表中生成一列累积百分比值，并在直方图 中包含一条累积百分比线。如果清除此选项，则会省略累积百分比。 【图表输出】：选中此复选框可在输出表中生成一个嵌入直方图。 （7）完成后单击【确定】按钮，即可得到对应的直方图和频率表，结果如图1-40所示
Excel统计分析实例精讲 图1-40生成的频率表及直方图 1.4小 结 本章在首先介绍统计学定义的基础上，明确了统计中总体与样本、连续数据与离散数 据、横截面数据和时间序列数据等基本概念，并着重介绍了组数据和非组数据的概念以及 如何将非组数据转化为组数据。其次，介绍了数据描述的4种方法：频率分布图、相对频率 分布图、累计频率分布图（或累积曲线）和直方图，以及如何通过Exce1的图表功能进行统 计图表的绘制。本章作为本书的开始，对具体操作步骤作了详细的说明，以使你在明确统 计概念的同时尽快掌握Exce1的基本操作，为以后的复杂统计分析打下良好的基础。 1.5习题 1.填空题 (1)数据按照是否连续可分为和 (2)对组中的每一个元素进行观察，则得到的数据即为一，而仅从组中观测一部 分数据，组中的这部分即为 (3)描述某一特定时间的一组变量的状况的数据为，而反映某一特定变量随时 间的变化的数据为 (4)当只有较少部分数据且未被加工或处于非组状态的数据称为 ,而将大量数 据进行加工汇总，将数据转化为一个频率表或其他分析人员能方便掌握的形式，转化后的 数据称为 (5)常用的描述数据的4种方式是 和 2.操作题 (1)试从上海交易所网站上(www.sse.com.cn)获取某种股票2005年3月和4月两个月 份的每日收盘价数据，根据收盘价数据计算对应的日收益率。 (2)在(1)的基础上将该股票日收益率数据转化成组数据，给出相应区间的频率值。 (3)利用分析工具中的直方图画出日收益率的直方图和累积频率曲线。 ·26·0
Excel 统计分析实例精讲 · 26 · 图 1-40 生成的频率表及直方图 1.4 小 结 本章在首先介绍统计学定义的基础上，明确了统计中总体与样本、连续数据与离散数 据、横截面数据和时间序列数据等基本概念，并着重介绍了组数据和非组数据的概念以及 如何将非组数据转化为组数据。其次，介绍了数据描述的4种方法：频率分布图、相对频率 分布图、累计频率分布图（或累积曲线）和直方图，以及如何通过Excel的图表功能进行统 计图表的绘制。本章作为本书的开始，对具体操作步骤作了详细的说明，以使你在明确统 计概念的同时尽快掌握Excel的基本操作，为以后的复杂统计分析打下良好的基础。 1.5 习 题 1. 填空题 （1）数据按照是否连续可分为＿＿＿和＿＿＿。 （2）对组中的每一个元素进行观察，则得到的数据即为＿＿＿，而仅从组中观测一部 分数据，组中的这部分即为＿＿＿。 （3）描述某一特定时间的一组变量的状况的数据为＿＿＿，而反映某一特定变量随时 间的变化的数据为＿＿＿。 （4）当只有较少部分数据且未被加工或处于非组状态的数据称为＿＿＿，而将大量数 据进行加工汇总，将数据转化为一个频率表或其他分析人员能方便掌握的形式，转化后的 数据称为＿＿＿。 （5）常用的描述数据的4种方式是＿＿＿＿、＿＿＿＿、＿＿＿＿和＿＿＿＿。 2. 操作题 （1）试从上海交易所网站上（www.sse.com.cn）获取某种股票2005年3月和4月两个月 份的每日收盘价数据，根据收盘价数据计算对应的日收益率。 （2）在（1）的基础上将该股票日收益率数据转化成组数据，给出相应区间的频率值。 （3）利用分析工具中的直方图画出日收益率的直方图和累积频率曲线