Excel统计分析实例精讲 上证180指数按收益率分组 何隔 黄数日 6 0.12 0 6 返回数组 0 9 0.0M 10 o.ce 运算结果 012 1 合计 1 图1-15各组区间观测值 说明：在Excel中，数组运算是比较特殊的一类运算形式，如果某函数采用数组运 算，则该输入参数应采用数组的形式，返回的结果也采用数组的形式。 执行数组运算时，首先在返回结果的区域中的第一个单元格中输入数组函数公式， 然后选中数组返回区域，先按F2键，再同时按下Ctrl+Shift+Enter键，即可执行 数组运算，数组运算返回的数组为一整体，不能改变或删除其中的某一部分，否 则会出现警告，只能将其整体删除。 (4)验证总的观测值数目，单击C13单元格，在编辑栏输入“=SUM(C5:C12)”。 最终结果见图1-15。 从图1-12和图1-15的结果中可以看出，将非组数据转化为组数据后，数据更直观明了， 对应不同指数值区间段观测值数目和比例清晰可见，同未分组前的数据相比，可更直接地 观察出指数和收益率的变动状况。 1.3数据描述 在统计中，恰当地运用统计图形往往能更直观地反映问题，尤其在商业演示中，直观 的统计图表往往更容易打动客户。数据描述主要通过把原始数据转化成对应的统计图表， 通过图表直观地描述数据的分布。数据描述的方法较多，本书主要介绍4种数据描述的方式： 频率分布图、相对频率分布图、累积频率分布图（或累积曲线）和直方图。 在例1.2中，通过将非组数据转化成组数据，把原始数据概括成了频率信息，将指数（或 收益率)的观测值转化成了指数（或收益率）对应区间的观测值的频率，本节将在分组的 基础上，通过举例说明如何在Excel中绘出数据对应的不同数据描述图。 1.3.1频率分布图 绘制频率分布图时，以等级区间（指数值）作为横轴，以观测值频率作为纵轴，根据 每个区间的频率值进而绘制出频率表，频率分布图中每个条格的高度代表对应等级区间的 观测值频率。 i0 ·12·
Excel 统计分析实例精讲 · 12 · 图 1-15 各组区间观测值 说明：在Excel中，数组运算是比较特殊的一类运算形式，如果某函数采用数组运 算，则该输入参数应采用数组的形式，返回的结果也采用数组的形式。 执行数组运算时，首先在返回结果的区域中的第一个单元格中输入数组函数公式， 然后选中数组返回区域，先按F2键，再同时按下Ctrl+Shift+Enter键，即可执行 数组运算，数组运算返回的数组为一整体，不能改变或删除其中的某一部分，否 则会出现警告，只能将其整体删除。 （4）验证总的观测值数目，单击C13单元格，在编辑栏输入“=SUM(C5:C12)”。 最终结果见图1-15。 从图1-12和图1-15的结果中可以看出，将非组数据转化为组数据后，数据更直观明了， 对应不同指数值区间段观测值数目和比例清晰可见，同未分组前的数据相比，可更直接地 观察出指数和收益率的变动状况。 1.3 数 据 描 述 在统计中，恰当地运用统计图形往往能更直观地反映问题，尤其在商业演示中，直观 的统计图表往往更容易打动客户。数据描述主要通过把原始数据转化成对应的统计图表， 通过图表直观地描述数据的分布。数据描述的方法较多，本书主要介绍4种数据描述的方式： 频率分布图、相对频率分布图、累积频率分布图（或累积曲线）和直方图。 在例1.2中，通过将非组数据转化成组数据，把原始数据概括成了频率信息，将指数（或 收益率）的观测值转化成了指数（或收益率）对应区间的观测值的频率，本节将在分组的 基础上，通过举例说明如何在Excel中绘出数据对应的不同数据描述图。 1.3.1 频率分布图 绘制频率分布图时，以等级区间（指数值）作为横轴，以观测值频率作为纵轴，根据 每个区间的频率值进而绘制出频率表，频率分布图中每个条格的高度代表对应等级区间的 观测值频率。 返回数组 运算结果
■■■■■ 第1章统计基础与数据描述 下面通过举例说明如何在分组数据的基础上绘制频率分布图。 例1.4绘制频率分布图 在例1.2结果的基础上（见图1-12），根据指数值组数据绘制出指数的频率分布图。 按指数分组值绘制2002到2004三年间的指数值频率分布图，具体操作步骤如下： (1)选择【插入】菜单中的【工作表】命令，双击新工作表的表名“sheet4”,将其 改为“例1.4”。 (2)设置表头“上证180按指数分组”和列名称“组号”、“上证180”和“观测值数 目”。 (3)从工作表例1.2中复制组号和分组区间。打开工作表例1.2，选择A3:A12对应的区 域，右击选中区域，在菜单中选择【复制】（或者直接用快捷键Ctr1+C执行复制命令）， 返回工作表例1.4，单击A3单元格，右击选中单元格，在菜单中单击【粘贴】（或者直接用 快捷键Ctr1+V执行粘贴命令)，即可完成对组号的分组区间的复制。 (4)从工作表例1.2中复制对应区间的观测值数目。打开工作表例1.2，选择D3:D12 对应的区域，右击选中区域，在菜单中选择【复制】，返回工作表例1.4，单击C3单元格， 右击选中单元格，在菜单中单击【粘贴】，发现对应的观测值并没有被复制过来，而是出 现了提示错误的#VALUE!,如图1-16所示。 上证180按指效分组 2 组号 上证180 花忍值数且 1≤24000 ≤24000 出错提示 4 22400.0-2500.0 HVALUEI 32500.-2600.0 HVALUEY 116000.17000 HVALUE! 7 527000-2800.0 8 628000-2900.0 华UE! 9 739000-3000.0 HVALUE! 10 8000-31000 WA礼E 93100.0-35200.0 W呢I 12 10.3200.0-3500.0 HYALUE! 13 14 图1-16复制出错 说明：出现VALE!,说明运算过程中出现了错误，当使用的参数或操作数类型 错误时，会出现这种错误。 (5)问题在于需要复制的仅是观测值数值，而直接用【复制】命令，Exce1会默认将 所要复制单元格的公式一同复制过去，此时可以改用Exce1的选择性粘贴功能，在复制过程 中，右击选中单元格，在菜单中选择【选择性粘贴】命令，出现【选择性粘贴】对话框， 如图1-17所示。 说明：如不采用选择性粘贴功能，则Excl会默认为采用全部粘贴方式，复制后的 单元格会根据新的工作表的单元格的值按公式重新计算。 ·13· 10
第 1 章 统计基础与数据描述 · 13 · 下面通过举例说明如何在分组数据的基础上绘制频率分布图。 例 1.4 绘制频率分布图 在例1.2结果的基础上（见图1-12），根据指数值组数据绘制出指数的频率分布图。 按指数分组值绘制2002到2004三年间的指数值频率分布图，具体操作步骤如下： （1）选择【插入】菜单中的【工作表】命令，双击新工作表的表名“sheet4”，将其 改为“例1.4”。 （2）设置表头“上证180按指数分组”和列名称“组号”、“上证180”和“观测值数 目”。 （3）从工作表例1.2中复制组号和分组区间。打开工作表例1.2，选择A3:A12对应的区 域，右击选中区域，在菜单中选择【复制】（或者直接用快捷键Ctrl+C执行复制命令）， 返回工作表例1.4，单击A3单元格，右击选中单元格，在菜单中单击【粘贴】（或者直接用 快捷键Ctrl+V执行粘贴命令），即可完成对组号的分组区间的复制。 （4）从工作表例1.2中复制对应区间的观测值数目。打开工作表例1.2，选择D3:D12 对应的区域，右击选中区域，在菜单中选择【复制】，返回工作表例1.4，单击C3单元格， 右击选中单元格，在菜单中单击【粘贴】，发现对应的观测值并没有被复制过来，而是出 现了提示错误的#VALUE!，如图1-16所示。 图 1-16 复制出错 说明：出现 #VALUE!，说明运算过程中出现了错误，当使用的参数或操作数类型 错误时，会出现这种错误。 （5）问题在于需要复制的仅是观测值数值，而直接用【复制】命令，Excel会默认将 所要复制单元格的公式一同复制过去，此时可以改用Excel的选择性粘贴功能，在复制过程 中，右击选中单元格，在菜单中选择【选择性粘贴】命令，出现【选择性粘贴】对话框， 如图1-17所示。 说明：如不采用选择性粘贴功能，则Excel会默认为采用全部粘贴方式，复制后的 单元格会根据新的工作表的单元格的值按公式重新计算。 出错提示
Excel统计分析实例精讲 选中数值 D式) 。边恒外) 都D O则高D 。结或中 0公其和数信其D O想生G 。信和款字持其包) a无心 0) ●关D) o轴@ O减D 回肤过学单元D 国转置 ☐家确 图1-17 【选择性粘贴】对话框 (6)单击选中【数值】单选按钮，完成后单击【确定】按钮，即可完成对观测值数目 的复制，如图1-18所示。 上证180按指数分组 123 组号 上证1S0现值数日 1524000 224000-25000 5 32500.0-2600.0 选择性粘贴 126000.27000 7 517000-28000 4 结果 62800.0-2900.0 9 729000-30000 83000.0-31000 931000-32000 12 1032000-33000 13 图1-18观测值数据 下面开始绘制按指数值分组的频率分布图。 (7)单击工具栏【插入】菜单中的【图表】命令，出现【图表向导-4步骤之1-图表类 型】对话框，如图1-19所示。 雷表。4球之1一自有天型 型有定火型 选择柱形图 区州 打数6图 益达面 选择簇状柱形图 可专钢可 理克属) 图1-19选择图表类型 说明：插入图表也可直接单击工具栏中的插入图表按钮国，同样会出现【图表向 导】对话框。 14·
Excel 统计分析实例精讲 · 14 · 图 1-17 【选择性粘贴】对话框 （6）单击选中【数值】单选按钮，完成后单击【确定】按钮，即可完成对观测值数目 的复制，如图1-18所示。 图 1-18 观测值数据 下面开始绘制按指数值分组的频率分布图。 （7）单击工具栏【插入】菜单中的【图表】命令，出现【图表向导-4步骤之1-图表类 型】对话框，如图1-19所示。 图 1-19 选择图表类型 说明：插入图表也可直接单击工具栏中的插入图表按钮 ，同样会出现【图表向 导】对话框。 选择性粘贴 结果 选中数值 选择柱形图 选择簇状柱形图
■■■■■■ 第1章统计基础与数据描述 (8)从【图表类型】列表中选择【柱形图】，从【子图表类型】选择区域中单击【簇 状柱形图】对应的图例，完成后单击【下一步】按钮，出现【图表向导-4步骤之2-图表源 数据】对话框，如图1-20所示。 图木年行一售之？二香声有黄卡 城上 c州：中中： O样) 所选列区域 准■卫习■ 图1-20【数据区域】选项卡 (9)在【数据区域】选项卡中，单击选中【系列产生在】选择区域中的【列】单选按 钮，单击【数据区域】文本框后的折叠按钮，将对话框折叠，选择C3:C12对应的单元格 区域，单击打开折叠按钮回，返回【图表向导-4步骤之2-图表源数据】对话框。 (10)单击【图表向导-4步骤之2-图表源数据】对话框的【系列】标签，进入【系列】 选项卡，在【名称】文本框中输入“上证180按指数分组观测值数目”，单击【分类(X)轴 标志】后的折叠按钮，选择B3:B12对应的单元格区域，单击打开折叠按钮▣，返回【图 表向导-4步骤之2-图表源数据】对话框，如图1-21所示。 就就就航祖祖饼前销 设置系列名称 新N甲 p回aC 。上证：使程与见酬a数日等 14亚 卫理 满a板洁D1 -料1i11日 选择X轴区域 图1-21【系列】选项卡 (11)单击【下一步】按钮，出现【图表向导-4步骤之3-图表选项】对话框。在【标 题】选项卡的【图表标题】文本框中输入“上证180按指数分组频率图”，在【分类(X) 轴】文本框中输入“指数”，在【数值(Y)轴】文本框中输入“频率”，如图1-22所示。 1·15· 1i
第 1 章 统计基础与数据描述 · 15 · （8）从【图表类型】列表中选择【柱形图】，从【子图表类型】选择区域中单击【簇 状柱形图】对应的图例，完成后单击【下一步】按钮，出现【图表向导-4步骤之2-图表源 数据】对话框，如图1-20所示。 图 1-20 【数据区域】选项卡 （9）在【数据区域】选项卡中，单击选中【系列产生在】选择区域中的【列】单选按 钮，单击【数据区域】文本框后的折叠按钮 ，将对话框折叠，选择C3:C12对应的单元格 区域，单击打开折叠按钮 ，返回【图表向导-4步骤之2-图表源数据】对话框。 （10）单击【图表向导-4步骤之2-图表源数据】对话框的【系列】标签，进入【系列】 选项卡，在【名称】文本框中输入“上证180按指数分组观测值数目”，单击【分类(X)轴 标志】后的折叠按钮 ，选择B3:B12对应的单元格区域，单击打开折叠按钮 ，返回【图 表向导-4步骤之2-图表源数据】对话框，如图1-21所示。 图 1-21 【系列】选项卡 （11）单击【下一步】按钮，出现【图表向导-4步骤之3-图表选项】对话框。在【标 题】选项卡的【图表标题】文本框中输入“上证180按指数分组频率图”，在【分类（X） 轴】文本框中输入“指数”，在【数值（Y）轴】文本框中输入“频率”，如图1-22所示。 所选列区域 选择X轴区域 设置系列名称
Excel统计分析实例精讲 上以小样电 上T4 图1-22【标题】选项卡 (12)选择【图表向导-4步骤之3-图表选项】对话框的【网格线】选项卡，撤选【数 值(Y)轴】选项组中的【主要网格线】复选框，取消网格线的显示，如图1-23所示。 有有94小之有不国 W 取消选中主要网格线 图1-23【网格线】选项卡 (13)选择【图表向导-4步骤之3-图表选项】对话框的【图例】选项卡，撤选【显示 图例】复选框，取消图例的显示，如图1-24所示。 4之)二有利项 取消选中显示图例 图1-24【图例】选项卡 (14)完成后单击【下一步】按钮，出现【图表位置】对话框，如图1-25所示，此处 采用作为工作表例1.4中的一个对象插入（也可作为单独一个新的工作表插入），单击【确 定】按钮。 丽末自置 作为对象插入 图1-25 【图表位置】对话框 ·16·
Excel 统计分析实例精讲 · 16 · 图 1-22 【标题】选项卡 （12）选择【图表向导-4步骤之3-图表选项】对话框的【网格线】选项卡，撤选【数 值（Y）轴】选项组中的【主要网格线】复选框，取消网格线的显示，如图1-23所示。 图 1-23 【网格线】选项卡 （13）选择【图表向导-4步骤之3-图表选项】对话框的【图例】选项卡，撤选【显示 图例】复选框，取消图例的显示，如图1-24所示。 图 1-24 【图例】选项卡 （14）完成后单击【下一步】按钮，出现【图表位置】对话框，如图1-25所示，此处 采用作为工作表例1.4中的一个对象插入（也可作为单独一个新的工作表插入），单击【确 定】按钮。 图 1-25 【图表位置】对话框 取消选中主要网格线 取消选中显示图例 作为对象插入