Excel在统计中的应用技巧

发布人：圣才学习网 发布日期：2009-12-21 14:19 共3人浏览[大] [中] [小]

1、“数据”菜单，再选“排序”，主要关键字选A18，按“确定”按钮（没有运行此步，有时归类不集中）；

2、选“分类汇总”，分类字段选A18，汇总方式选求和，汇总项选A201，A211，A2211，统计上习惯一般汇总数在上方，因此请不要选“汇总结果显示在数据下方”，再按确定则可。

下图反映分类汇总的第二层次数据，实际是我们通常所说的简单分组汇总。右边有很多“+”按钮，按一下此按钮，显示出参加这类汇总的记录，并“+”按钮变成“-”按钮。

值得注意的是，分类汇总的数据是在原工作表上，如果要调回原工作表，请选“分类汇总”子菜单，按“全部删除”按钮则可。

“分类汇总”一次汇总只能用一个字段，不如“数据透视表”功能强，我们以上面的工作表为例用“数据透视表”功能汇总一张表，主栏用a18（注册经济类型），a12（营业状态）复合分组，宾栏用a09（隶属关系）分组，汇总出分组的单位个数，具体操作：

1、选定工作表的所有区域；

2、选“数据透视表和图表报告”子菜单，弹出一个该功能的向导，按“完成”按钮，形成一个新工作表。

3、拖动a18，a12到“行字段”（表中有提示，下同）处，a09到“列字段”处，a01到“数据项”处（注：汇总单位个数应拖入非数值字段）则完成，结果见下图。

为较全面的显示汇总结果，图中14-35行隐藏起来，从表中可看出共有132个单位，其中国有（110）41个，集体（120）20个，地市属（40）17个，县（市、区）属29个，国有中营业、停业和筹建的分别为12个、24个和5个。 四、数据的保护

我们有时不想让其他同事打开某Excel电子表格，可对工作簿进行加密，具体操作是：先选“文件”菜单的子菜单“另存为”，对Excel 2000版本再选“工具”栏的“常规选项”，对其他版本在“存盘”窗口点“选项”按钮，分别输入打开和修改该工作簿的密码，按“确定”按钮，系统要求再输一次密码，验正相同后，按“存盘”按钮则可。正常打开保密文件后，可用同样办法去掉密码。 Excel还可对工作簿或工作表的整表、某一区域进行保护。就统计而言，通常要保护表格的主宾栏文字、计算公式和审核关系等，特别是当你构造的电子表格要提供给别人使用时，更要如此。对整个工作簿、工作表保护比较简单，只要按“工具”菜单的“保护”子菜单，选“工作簿”或“工作表”，输入保护密码，点“确定”按钮，重输该密码则可。对工作表的局部进行保护，可以先定议不予保护的区域，然后再按“工具”菜单的“保护”子菜单，选“工作簿”或“工作表”，输入保护密码，点“确定”按钮，重输该密码可。对不予保护的区域，可输入各式数据。

定义不予保护的区域方法是：1、选定区域；2、按鼠标右键，点“设置单元格格式”菜单，选“保护”项，“锁定”选项的前面方框如为“√”，则点一下该方框使其为空，按“确定”则可。

五、数据审核

对某一单元格可以利用“设置单元格格式”中的“数字”设置功能进行数据类型的定义，包括常规、数值（小数、分数、科学记数等）、日期、时间、文本等，这也是单元格数据审核的一个手段。对定义为“常规”的单元格可接受任何类型的数据，若其为数值则不限小数点位数或表示方法。一旦某单元格已输入某种类型的数据，如日期型（输入方法：2002-10-24），则你而后输入任何一个信息，系统将作为日期型数据处理。要改变单元格的数据类型，可重新设置其数据格式。

对单元格的数据有效性审核，可用“数据”菜单的“有效性”功能进行定义，主要有“设置”和“出错警告”两部分，设置包括整数、小数、文本长度等。整数、小数和文本长度可设定一个连续的数值范围。如性别编码取值为1~2，设应设置为整数，关系符选择“介于”、“最小值”输入1，最大值输入2。“出错警告”的标题可以是单元格地址，也可是文字，如输入“性别”，“警告信息”栏可输入“取值为1或2，超出取值范围的可选择“中止”、“警告”和“信息”三种，“中止”要求无条件重输，“警告”可强行通过也可重输，而“信息”只显示出错信息，不需重输，但可取消该输入。

统计上有很多逻辑平衡关系，在电子表格中要进行审核，并提示审核结果。下面表格中是一个很典型的逻辑关系审核。显然有： B1=B2+B3+B4 且B1>0 B20 B30 B40

注意Excel没有提供逻辑关系运行符，因此表达相对比较复杂。通常如果不返回详细错误信息，则可在C1．．c4分别直接描述为： =b1=b2+b3+b4 =b2在D1．．D4分别输入： =b1>0 =b2>0 =b3>0 =B4>0

对应单位格返回“TRUE”（真）时则审核通过，返回“FALSE”（假）则不符合审核要求。这种审核方法简单，但返回结果不直观。事实可以用IF函数进行审核效果更好。我们在E1．．E4分别输入：

=IF（B20，“OK”，“应大于0”），“应小于合计数”） =IF（B20，“OK”，“应大于0”），“应小于合计数”） =IF（B30，“OK”，“应大于0”），“应小于合计数”） =IF（B40，“OK”，“应大于0”），“应小于合计数”） IF函数括号内由用逗号隔开的三部分组成，第一部分是逻辑关系式，第二部分是满足逻辑关系式条件的返回信息，第三部分是不满足条件的返回信息。IF函数允许多层嵌套，本例中实际是两层嵌套。 六、统计计算和分析上的应用

Excel提供了不少统计计算和分析使用的函数，主要包括： AVEDEV、DEVSQ、STDEV，分别为平均差、方差和标准差函数。

AVERAGE、GEOMEAN、GEOMEAN，分别计算算术平均数、几何平均数以及调和平均数。

CORREL（array1，array2），计算数组1和数组2的相关系数。 COVAR（array1，array2），计算数组1和数组2的协方差。 SUM、MAX、MIN，计算合计数、最大值和最小值。 MINVERSE（ARRAY），求数组的逆矩阵。

另外Excel还提供不少数理统计上复杂计算的函数，如正态分布、二项式分布、T分布、帕松分布、β分布、γ分布、指数分布和F概率分布，F检验、T检验和x2检验等。 在投入产出分析中，计算完全消耗系数需要求逆矩阵，用人工计算非常麻烦，用MINVERSE函数就简单多了。如单元矩阵I和直接消耗系数矩阵A相减矩阵I—A为：

0．8395 -0．0655 -0．0177 -0．18 0．4600 -0．23 -0．0531 -0．0961 0．8101

把该矩阵数值输入A1．．C3单元，求逆矩阵的公式放在E1．．G3，见下表： MINVERSE（A1．．C3）为求逆矩阵公式，其结果是一个数组，即为一串数字。INDEX（MINVERSE（A1．．C3），2，3）代表取数组（本例组为三行三列）的第二行第三列元素。依表中公式可得到逆矩阵（I—A）-1为： 1．2417 0．1972 0．0976 0．6063 2．4455 0．8866 0．1533 0．3030 1．3460

（I—A）-1为完全消耗系数矩阵，即所求矩阵的对角线（“\\”方向）上三个元素减去1，其他元素不变。

有些计算用固有函数不一定能算出来，如加权算术平均数、加权资料的标准差等，这时可利用公式及公式复制等实现。设有100个变量值放在A1．．A100单元格，其变量的对应权数放在B1．．B100单元格，求加权算术平均数步骤： 在C1单元格输入公式：=A1*B1 将C1公式复制到C2．．C100；

在C101单元格输入公式：=SUM（C1．．C100）； 在B101单元格输入公式：=SUM（B1．．B100）； 在C102单元格输入公式：=C101/B101。

C102单元格的结果就是加权算术平均数。其标准差的计算步骤为： 在D1单元格输入公式：=（A1-$C$102）ˇ2*B1； 复制D1到D2．．D100； 在D101单元格输入公式：

=SQRT（SUM（D1．．D100）/B101）。 D101的输出结果就是加权资料的标准差。