■■■■----- 第1章统计基础与数据描述 说明：Excl的隐藏功能也是常常用到的功能之一，可以隐藏选中的行和列。选中 需要隐藏的行（或列），右击，选择【隐藏】，即可将选中的行（或列）隐藏。当 选中包含隐藏行（或列）的区域后，右击，选择【取消隐藏】，即可令隐藏的行（或 列)重新显示。 从图1-6中可以看出，虽然求得了三年中每个月的指数收益率，但由于数据太多，无法 直观地观察出其中的规律，甚至无法对这一系列的数据进行说明，因此有必要对其加工汇 总，将非组数据转化为组数据。 对于例1.1中如此多的数据，可以将其分为不同的组区间或等级，并采用不同区间的观 测值数目（或称频率）来描述，并给出对应的频率表。非组数据的分组可以通过COUNTIF 计算不同组区间内满足条件的单元格的数目间接求解，也可以运用频率分布函数FREQUENCY 直接返回频率数组，下面对这两种方法分别举例说明。 对数据进行分组时，应首先判断数据大体所在的区域，找出最大值和最小值，然后合 理地确定出对应的分组区间（又称组距），且区间不能有重叠部分。 例1.2运用COUNTIF函数将非组数据转化为组数据 指数的数据如表1-4所示，试按指数值进行分组，并求得每组中对应的观测数目和累积 频率。 本例在指数为2300点到3300点对应的区间内运行，因此分组时将从≤2400为第一组， 组距设为100点，直至3200点到3300点对应的区间。采用C0 UNTIF函数来实现非组数据转化 为组数据，具体操作步骤如下： (1)新建一空白工作表“例1.2”，选择A1:E1单元格，右击选择【设置单元格格式】， 在出现的【单元格格式】对话框中的【对齐】选项卡中，单击选中【合并单元格】前的复 选框，将单元格合并，输入表头“上证180按指数分组”。 (2)分别在A2、B2、C2、D2、E2单元格设定列名：“组号”、“上证180”、“累积 频率”、“观测值数目”和“观测值比例”。 (3)设定分组区间，单击单元格B3,在编辑栏输入“≤2400.0”，分别在B4、B5、B6、 B7、B8、B9、B10、B11、B12单元格中输入“2400.0-2500.0”、“2500.0-2600.0”、 “2600.0-2700.0”、“2700.0-2800.0”、“2800.0-2900.0”、“2900.0-3000.0”、 “3000.0-3100.0”、“3100.0-3200.0”和“3200.0-3300.0”。 (4)运用自动填充功能设定组号，单击A3单元格，输入“1”，单击A4单元格，在编 辑栏输入“=A3+1”,完成后按回车键。单击A4单元格，拖动鼠标至A12单元格，即可完 成组号的自动填充。 (5)确定分组中“≤2400.0”的累积频率。单击C3单元格，选择【插入】/【函数】 命令，出现【插入函数】对话框，从【或选择类别】下拉列表中选择【统计】，如图1-7 所示。 1i
第 1 章 统计基础与数据描述 · 7 · 说明：Excel的隐藏功能也是常常用到的功能之一，可以隐藏选中的行和列。选中 需要隐藏的行（或列），右击，选择【隐藏】，即可将选中的行（或列）隐藏。当 选中包含隐藏行（或列）的区域后，右击，选择【取消隐藏】，即可令隐藏的行（或 列）重新显示。 从图1-6中可以看出，虽然求得了三年中每个月的指数收益率，但由于数据太多，无法 直观地观察出其中的规律，甚至无法对这一系列的数据进行说明，因此有必要对其加工汇 总，将非组数据转化为组数据。 对于例1.1中如此多的数据，可以将其分为不同的组区间或等级，并采用不同区间的观 测值数目（或称频率）来描述，并给出对应的频率表。非组数据的分组可以通过COUNTIF 计算不同组区间内满足条件的单元格的数目间接求解，也可以运用频率分布函数FREQUENCY 直接返回频率数组，下面对这两种方法分别举例说明。 对数据进行分组时，应首先判断数据大体所在的区域，找出最大值和最小值，然后合 理地确定出对应的分组区间（又称组距），且区间不能有重叠部分。 例 1.2 运用 COUNTIF 函数将非组数据转化为组数据 指数的数据如表1-4所示，试按指数值进行分组，并求得每组中对应的观测数目和累积 频率。 本例在指数为2300点到3300点对应的区间内运行，因此分组时将从≤2400为第一组， 组距设为100点，直至3200点到3300点对应的区间。采用COUNTIF函数来实现非组数据转化 为组数据，具体操作步骤如下： （1）新建一空白工作表“例1.2”，选择A1:E1单元格，右击选择【设置单元格格式】， 在出现的【单元格格式】对话框中的【对齐】选项卡中，单击选中【合并单元格】前的复 选框，将单元格合并，输入表头“上证180按指数分组”。 （2）分别在A2、B2、C2、D2、E2单元格设定列名：“组号”、“上证180”、“累积 频率”、“观测值数目”和“观测值比例”。 （3）设定分组区间，单击单元格B3，在编辑栏输入“≤2400.0”，分别在B4、B5、B6、 B7、B8、B9、B10、B11、B12单元格中输入“2400.0-2500.0”、“2500.0-2600.0”、 “2600.0-2700.0”、“2700.0-2800.0”、“2800.0-2900.0”、“2900.0-3000.0”、 “3000.0-3100.0”、“3100.0-3200.0”和“3200.0-3300.0”。 （4）运用自动填充功能设定组号，单击A3单元格，输入“1”，单击A4单元格，在编 辑栏输入“= A3 +1”，完成后按回车键。单击A4单元格，拖动鼠标至A12单元格，即可完 成组号的自动填充。 （5）确定分组中“≤2400.0”的累积频率。单击C3单元格，选择【插入】/【函数】 命令，出现【插入函数】对话框，从【或选择类别】下拉列表中选择【统计】，如图1-7 所示
Excel统计分析实例精讲 速载1 选择统计函数 我龙的鞋食 国神 图1-7【插入函数】对话框 (6)在出现的统计函数列表中，单击【选择函数】中的【COUNTIF】,如图1-8所示， 单击【确定】按钮。 能入函数 ?☒ 使要面数D1 编入一馆地八说时末有达西想加什。物酒单看“林有 转到■ 就速样共制口：镜计 速样函数们： 选择COUNTIF函数 FIP0ND1ST Cotf时it.aiterl) 计煤某区装中见编定条件的单无特教目 应的该通数的酸能 图1-8统计函数列表 说明：COUNTIF函数用于计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(Range,Criteria),其中Range为需要计算其中满足条件的单元格 数目的单元格区域：Criteria为确定哪些单元格将被计算在内的条件，其形式可 以为数字、表达式或文本。 (7)在出现的COUNTIF的【函数参数】对话框中，如图1-9所示，单击【Range】框后 的折叠按钮，将对话框折叠。 不长然青 中 什属装士信中风现处宝年得的单光号鞋目 折叠按钮 计有健第、 瓶通五y危 图1-9【函数参数】对话框 (8)单击数据所在的工作表“例1.1”，拖动鼠标选择B3:B38对应的区域，如图1-10 ·8·■
Excel 统计分析实例精讲 · 8 · 图 1-7 【插入函数】对话框 （6）在出现的统计函数列表中，单击【选择函数】中的【COUNTIF】，如图1-8所示， 单击【确定】按钮。 图 1-8 统计函数列表 说明：COUNTIF函数用于计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(Range,Criteria)，其中Range为需要计算其中满足条件的单元格 数目的单元格区域；Criteria为确定哪些单元格将被计算在内的条件，其形式可 以为数字、表达式或文本。 （7）在出现的COUNTIF的【函数参数】对话框中，如图1-9所示，单击【Range】框后 的折叠按钮 ，将对话框折叠。 图 1-9 【函数参数】对话框 （8）单击数据所在的工作表“例1.1”，拖动鼠标选择B3:B38对应的区域，如图1-10 选择统计函数 选择COUNTIF函数 折叠按钮
■■■■■■ 第1章统计基础与数据描述 所示。 上正10指数 时 唇数夜拉率 2002-238696900145丁 跨工作表选 002-19414300514 择引用参数 2032-40149008 ,生”4传0球0” 10 12902-1011764-0●4610 2002-1116$-00565 14 2002-1216145 0604 200413.819●t006元51 2004442144B0023229 0,81每1曹重1-后分车 09149 2004-61线44-01020 2001-514916 00272 3004-92501000845111 200-02427 2908-111492推001509 图1-10选择数据区域 (9)单击打开折叠按钮回，返回【函数参数】对话框，在【Criteria】对应的文本 框中输入“<=2400.0”，如图1-11所示。完成后单击【确定】按钮，即可求出指数满足 “≤2400.0”的累积频率数。 函鞋多教 COMMTI 1mg4NL,II时E3m 时算某十区装中河呢位宜器件的单光特积销 静额计其其中事定单元材数目的区城 输入条件 计常储京， 智线过流太拉桂应国 图1-11【函数参数】对话框 (10)确定分组中“2400.0-2500.0”区间的累积频率，单击C4单元格，输入“=C0 UNTIF(例 1.1!B3:B38,"<=2500")”。同样的方法可以求出第三组到第十组对应的累积频率数，由于 方法同上相同，在此不再详述，而第十组的累积频率数恰为样本总数目36。 (11)求解每组对应的观测值数目，第一组的累积频率数即为观测值数目，而对应其 他各组，本组的观测值数目=本组的累积频率数-上一组的累积频率数。 第一组的观测值数，单击D3单元格，在编辑栏输入“=C3”。第二组的观测值数目，单 击D4单元格，在编辑栏输入“=C4-C3”。以后各组的观测值数目，单击D4单元格，将鼠标 放至D4单元格右下角，出现小黑十字光标后拖动至D12单元格，即可完成其他各组的观测值 数目自动填充。 (12)求解每组对应的观测值比例，本组的观测值比例=本组的观测值数目/累积频率 1i
第 1 章 统计基础与数据描述 · 9 · 所示。 图 1-10 选择数据区域 （9）单击打开折叠按钮 ，返回【函数参数】对话框，在【Criteria】对应的文本 框中输入“<=2400.0”，如图1-11所示。完成后单击【确定】按钮，即可求出指数满足 “≤2400.0”的累积频率数。 图 1-11 【函数参数】对话框 （10）确定分组中“2400.0-2500.0”区间的累积频率，单击C4单元格，输入“=COUNTIF(例 1.1!B3:B38,"<=2500")”。同样的方法可以求出第三组到第十组对应的累积频率数，由于 方法同上相同，在此不再详述，而第十组的累积频率数恰为样本总数目36。 （11）求解每组对应的观测值数目，第一组的累积频率数即为观测值数目，而对应其 他各组，本组的观测值数目=本组的累积频率数-上一组的累积频率数。 第一组的观测值数，单击D3单元格，在编辑栏输入“=C3”。第二组的观测值数目，单 击D4单元格，在编辑栏输入“=C4-C3”。以后各组的观测值数目，单击D4单元格，将鼠标 放至D4单元格右下角，出现小黑十字光标后拖动至D12单元格，即可完成其他各组的观测值 数目自动填充。 （12）求解每组对应的观测值比例，本组的观测值比例=本组的观测值数目/累积频率 跨工作表选 择引用参数 输入条件
Excel统计分析实例精讲 总数。 第一组的观测值比例，单击E3单元格，在编辑栏输入“=D3/C12”,在编辑栏中的C12 的C前后各输入一个“$”，表示绝对引用C12单元格，完成后按回车键。单击E3单元格，将 鼠标放至E3单元格右下角，出现小黑十字光标后拖动至E12单元格，即可完成自动填充，求 出其他各组的观测值数目。 说明：从上例中可以看出，在求观测值比例时，由于需要C12作为总数保持不变， 因此在运用自动填充单元格功能时，采用绝对引用，即在C12的行和列前均输入一 “$”,变为“$C$12”,此时再用自动填充单元格时，对应的分母项“$C$12”不再 变化，仅分子为对应各项的观测值数目，因此可以快速地求出以后各组的观测值 比例。 恰当选用Excel的引用方式功能将极大地减少工作量，因此有必要介绍一下Excel 中的三种引用方式：相对引用、绝对引用与混合引用。 ·相对引用：单元格位置中列或行的坐标中均不含$符号，如C2,此时运用自动 填充时，行列位置均发生变化。 ·绝对引用：单元格位置中行或列的坐标中均含$符号，如$C$2,此时运用自动 填充单元格命令时，行列位置均不变化。 混合引用：位置中行或列的某项中含有$符号，如$C2,此时运用自动填充单 元格命令时，列对应的C不发生变化，而仅行的位置2发生变化：或C$2,此时 运用自动填充单元格命令时，行对应的2不发生变化，而仅列的位置C发生变 化。 最终按指数分类的结果如图1-12所示。 面号 上 馨积事花房植靠时发画值比例 12400.0 7 12. 340 15 图1-12按指数分组后的结果 例1.3运用FREQUENCY函数转化为组数据 指数的月收益率数据如图1-6所示，试按收益率进行分组，并求得每组中对应观测数目 和累积频率。 本例中指数收益率大部分在-0.12到0.12对应的区间内运行，仅有一个值大于0.12，因 此分组时将从≤-0.12设为第一组，组距设为0.04。采用FREQUENCY函数来实现非组数据转 化为组数据，具体操作步骤如下： (1)新建一空白工作表“例1.3”，输入表头“上证180指数按收益率分组”，设定列 名：“间隔”和“观测值数目”。 i0 ·10·0
Excel 统计分析实例精讲 · 10 · 总数。 第一组的观测值比例，单击E3单元格，在编辑栏输入“= D3/C12”，在编辑栏中的C12 的C前后各输入一个“$”，表示绝对引用C12单元格，完成后按回车键。单击E3单元格，将 鼠标放至E3单元格右下角，出现小黑十字光标后拖动至E12单元格，即可完成自动填充，求 出其他各组的观测值数目。 说明：从上例中可以看出，在求观测值比例时，由于需要C12作为总数保持不变， 因此在运用自动填充单元格功能时，采用绝对引用，即在C12的行和列前均输入一 “$”，变为“$C$12”，此时再用自动填充单元格时，对应的分母项“$C$12”不再 变化，仅分子为对应各项的观测值数目，因此可以快速地求出以后各组的观测值 比例。 恰当选用Excel的引用方式功能将极大地减少工作量，因此有必要介绍一下Excel 中的三种引用方式：相对引用、绝对引用与混合引用。 · 相对引用：单元格位置中列或行的坐标中均不含$符号，如C2，此时运用自动 填充时，行列位置均发生变化。 · 绝对引用：单元格位置中行或列的坐标中均含$符号，如$C$2，此时运用自动 填充单元格命令时，行列位置均不变化。 · 混合引用：位置中行或列的某项中含有$符号，如$C2，此时运用自动填充单 元格命令时，列对应的C不发生变化，而仅行的位置2发生变化；或C$2，此时 运用自动填充单元格命令时，行对应的2不发生变化，而仅列的位置C发生变 化。 最终按指数分类的结果如图1-12所示。 图 1-12 按指数分组后的结果 例 1.3 运用 FREQUENCY 函数转化为组数据 指数的月收益率数据如图1-6所示，试按收益率进行分组，并求得每组中对应观测数目 和累积频率。 本例中指数收益率大部分在-0.12到0.12对应的区间内运行，仅有一个值大于0.12，因 此分组时将从≤-0.12设为第一组，组距设为0.04。采用FREQUENCY函数来实现非组数据转 化为组数据，具体操作步骤如下： （1）新建一空白工作表“例1.3”，输入表头“上证180指数按收益率分组”，设定列 名：“间隔”和“观测值数目
■■■■■ 第1章统计基础与数据描述 (2)设定分组区间，单击B5单元格，在编辑栏输入“-0.1”，再次单击B5单元格，单 击【编辑】/【填充】/【序列】，在出现的【序列】对话框中，在【序列产生在】选项组 中单击选中【列】单选按钮，在【类型】选项组单击选中【等差序列】单选按钮，在【步 长值】文本框中输入“0.04”（组距值），在【终止值】文本框中输入“0.12”，如图1-13 所示，单击【确定】按钮。 作剂 选择列 。稀据年有值) 。日期) 口裤地四 ○自销填克包 输入步长0.04 有止重但年 输入终止值0.12 图1-13【序列】对话框 (3)用FREQUENCY函数求区间内的观测值数目。 说明：FREQUENCY,以一列垂直数组返回某个区域中数据的频率分布。例如，使用 函数FREQUENCY可以计算在给定的分数范围内测验分数的个数。由于函数 FREQUENCY返回一个数组，所以必须以数组公式的形式输入。 语法：FREQUENCY(data_array,bins_array),其中data_array为一数组或对一组 数值的引用，用来计算频率：如果data array中不包含任何数值，函数FREQUENCY 返回零数组。bins array为间隔的数组或对间隔的引用，该间隔用于对data_array 中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回 data_array中元素的个数。 说明：在选定相邻单元格区域（该区域用于显示返回的分布结果）后，函数 FREQUENCY应以数组公式的形式输入。函数FREQUENCY将忽略空白单元格和文本。 对于返回结果为数组的公式，必须以数组公式的形式输入。 返回的数组中的元素个数比bins_array(数组)中的元素个数多1。返回的数组中 所多出来的元素表示超出最高间隔的数值个数。 单击C5单元格，在编辑栏输入“=FREQUENCY(例1.1！C4:C38,B5:B11)”。选择C5到C12 对应的单元格区域（用于显示返回结果），按F2键，如图1-14所示，同时按下 Ctrl+Shift+Enter键，执行数组运算，运算结果如图l-l5所示。 上证180增数按收益率分组 3 座用E了通常 何隔成测值数且 Q1PUEY例1,1C4:鸡，5：B11l 00日 -004 00w 选择数组 012 输出区域 合计 14 图1-14 选中区域按F2键 ·11· 1i
第 1 章 统计基础与数据描述 · 11 · （2）设定分组区间，单击B5单元格，在编辑栏输入“-0.1”，再次单击B5单元格，单 击【编辑】/【填充】/【序列】，在出现的【序列】对话框中，在【序列产生在】选项组 中单击选中【列】单选按钮，在【类型】选项组单击选中【等差序列】单选按钮，在【步 长值】文本框中输入“0.04”（组距值），在【终止值】文本框中输入“0.12”，如图1-13 所示，单击【确定】按钮。 图 1-13 【序列】对话框 （3）用FREQUENCY函数求区间内的观测值数目。 说明：FREQUENCY，以一列垂直数组返回某个区域中数据的频率分布。例如，使用 函数 FREQUENCY可以计算在给定的分数范围内测验分数的个数。由于函数 FREQUENCY返回一个数组，所以必须以数组公式的形式输入。 语法：FREQUENCY(data_array,bins_array)，其中data_array为一数组或对一组 数值的引用，用来计算频率；如果data_array中不包含任何数值，函数FREQUENCY 返回零数组。bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array 中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回 data_array中元素的个数。 说明：在选定相邻单元格区域（该区域用于显示返回的分布结果）后，函数 FREQUENCY应以数组公式的形式输入。函数FREQUENCY将忽略空白单元格和文本。 对于返回结果为数组的公式，必须以数组公式的形式输入。 返回的数组中的元素个数比bins_array（数组）中的元素个数多1。返回的数组中 所多出来的元素表示超出最高间隔的数值个数。 单击C5单元格，在编辑栏输入“=FREQUENCY(例1.1!C4:C38,B5:B11)”。选择C5到C12 对应的单元 格区域（用 于显示返回 结果），按 F2键，如图 1-14所示，同时按下 Ctrl+Shift+Enter键，执行数组运算，运算结果如图1-15所示。 图 1-14 选中区域按 F2 键 选择数组 输出区域 选择列 输入步长0.04 输入终止值0.12