论文部分内容阅读
[提 要] 在传统的统计分析中,经常用到一些专门的统计软件,但这些专门软件往往价格不扉,难于推广｡而在目前应用广泛的Excel中有专门用于描述统计方面的函数,可以直接利用这些函数计算一些统计指标｡Excel有功能强大的VBA,用它定义一些函数,不但使用方便,可针对不同的数据进行统计计算,还可处理一些Excel内部函数不能处理的统计数据｡“分析工具库”是Excel中一个专门用于统计的工具,其中的“描述统计”可以一次轻松获得多个统计指标,从而可利用Excel达到统计分析的目的｡
[关键词] Excel;描述统计;函数;宏
[作者简介]张占军(1969-),男,华北电力大学研究生院在读研究生｡(河北保定 071003)
Excel是大家熟知的Office组件,拥有丰富的函数,其中专门用于统计方面的函数就有近百个｡Ex-cel强大的数据计算功能,不仅能处理普通的数据运算,而且也能胜任处理专门的统计业务，如统计学中的数据描述､概率分布､参数估计､假设检验､方差分析等等,都可以用Excel来完成｡因此,对于绝大多数统计分析来说,用Excel替代昂贵的专门统计软件,就可以轻松完成统计分析的任务｡本文中,笔者以描述统计数据分析为例,来说明利用Excel处理统计专业数据的途径和步骤｡
一､统计函数
在描述统计中,描述数据集中趋势的统计指标有:众数､中位数､均值､调和平均数､几何平均数;描述数据离散程度的统计指标有:极值､全距､四分位差､方差､标准差等｡在未分组资料中,可直接用Excel函数计算这些统计指标｡如根据表1中的数据,计算某企业30名职工工资的众数,可在存放结果的单元格中输入= MODE(A2：F6)，按确认键,结果立刻出现在当前单元格中,比起手工计算查找来说又快又准确｡同样,计算其他统计指标时,在“插入函数”窗口的“函数类别”下拉文本框中选择“统计”类,然后在函数文本框中选择相关函数,使用即可,和使用Excel常用函数没有区别｡当选取某个函数时,在“插入函数”窗口的下面有帮助信息,简要说明该函数的功能及如何使用该函数｡
二､用VBA处理统计数据
由于函数只能应用于处理未分组资料,但对于分组的统计数据,不能直接使用Excel内部函数求得,需要多步计算,过程较繁琐｡但可以用VAB定义一个函数,使用这个函数,就像使用Excel内部函数一样,输入参数,点击确定,顷刻就可得到结果;再者可以在不同的地方､针对不同的数据进行计算,可谓一劳永逸｡定义这样一个函数需要一些简单的VBA编程知识,但并不很难｡如表2中所示资料,是对表1数据的分组(“组距上限”列各数值是各组组距的上限值,一定要这样输入,也是为了更好的使用FREQUENCY()函数),输入频数或用FREQUENCY()计算频数(计算后,通过“复制”“选择性粘贴”粘贴成数值)｡顺次执行菜单“工具”“宏”“Visual Basic编辑器”,打开Visual Basic编辑器,选择菜单“插入”“模块”,然后再选择菜单“插入”“过程”,在对话框中选择“函数”单选按钮,输入“函数名称”,如:get-Mode,点击确定,然后在弹出的窗口中输入如下代码:
Public Function get Mode (your Range As Range)
Dim max Frequency， lower Limit， class Interval， interval1， intervall2 As Single
Dim row，col As Integer＇定义变量:行号,列号
Set interval Range = your Range .Find (Application .Worksheet Function. Max (your Range))
row = interval Range. row ＇众数组所在行
col = interval Range. Column ＇众数组所在列
max Frequency = interval Range. Value ＇最大频数
lower Limit = Application. Cells(row - 1， col - 1).Value + 1 ＇组下限
class Interval = Application. Cells(row， col - 1).Value - Application. Cells(row - 1， col - 1).Value ＇组距
interval1 = max Frequency - Application. Cells(row - 1， col).Value
interval2 = max Frequency - Application. Cells(row + 1， col).Value
get Mode = lower Limit + (interval1 / (interval1 + interval2)) * class Interval ＇应用众数公式
End Function
最后保存退出,这样计算分组数据众数的函数“get Mode”就完成了｡使用方法和使用Excel内部函数一样,选择“用户定义”函数,就会看到get Mode函数,选择它并在参数框中输入频数的单元格区域,如表2中的“C3:C7”点击“确定”,众数就计算出来了｡将计算结果和手工计算的结果比较,结果是一致的｡使用这个函数同样可以直接计算其他分组数据的众数｡
上面是一个应用VBA计算集中趋势描述指标的例子,下面看一个如何用VBA计算离散程度指标的例子｡
计算标准差,未分组数据可直接用函数STDEV()或STDEVP()求得,若要求分组数据的标准差或带有权数的标准差,就不太容易了,但同样可以用自定义函数的方法解决之｡如表3是对表1数据的分组,求职工收入的标准差｡按照前面自定义函数的制作过程,定义如下函数:
Public Function get Stdev (interval Range As Range， avg As Single)
Dim i， row， col As Integer ＇定义变量:计数,行号,列号
row = interval Range. row
col = interval Range. Column
For i = row To row + interval Range. Rows. Count - 1
g = g + Cells(i， col)
h = h + (Application. Cells(i， col - 1) - avg) * (Application. Cells(i， col - 1) - avg) * Cells(i， col)
Next i
Get Stdev = Sqr(h / g)
End Function
简单的几行代码,函数就定义好了｡使用时输入参数为权数范围“C3：C7”和平均值“B9”,点击确定,就得出职工收入的标准差｡
计算分组数据的其他统计指标,同样可以自己定义函数,定义时虽然有些费事,但使用方便,计算快速｡
三､描述统计工具
前面介绍的都是利用函数计算单个统计指标,使用Excel的“分析工具库”中的“描述统计”,可以一次计算若干个统计指标,更方便快捷,还可以同时产生图表｡“分析工具库”在工具菜单下,如果没有,则要运行“安装”程序来加载“分析工具库”,安装完毕之后,通过“工具”菜单中的“加载宏”命令,在“加载宏”对话框中选择并启动它｡下面来看如何用它做描述统计｡
仍以前面职工工资收入为例,将数据排成一列,如表4中的A列(图中未显示全部数据),然后选择菜单“工具”“数据分析库”“描述统计”,弹出描述统计对话框｡在输入区域文本框中输入数据所在单元格,如“A2:A32”;在分组方式中,依据输入区域中的数据是按行还是按列排列,单击“行”或“列”,在这儿选择“列”;如果输入区域的第一行中包含标志项(变量名),则选中“标志位于第一行”复选框;如果输入区域的第一列中包含标志项,则选中“标志位于第一列”复选框;如果输入区域没有标志项,则不选任何复选框,Excel将在输出表中生成适宜的数据标志,这里选中“标志位于第一行”;“输出选项”,前三项依据输出结果存放的位置不同,来选择以本表中某个单元格开始的区域,或输出到新工作表或新工作簿;“汇总统计”,指定输出表中生成“平均值､标准误差､中值､众数､标准偏差､方差､峰值､偏斜度､极差(全距)最小值､最大值､总和､样本个数”统计结果,则选中此复选框;“均值置信度”:若需要输出由样本均值推断总体均值的置信区间,则选中此复选框,然后在右侧的编辑框中,输入所要使用的置信度;“第K个最大/小值”:在输出表中包含输入区域数据的第k个最大/小值,若选中此复选框,在右侧的编辑框中,输入k的数值｡所有这些都设置好后,点击确定,结果即可出来,如表4中的C列与D列｡
使用Excel内部函数和分析工具库,处理统计数据,简单的使用就可轻松获得计算结果;使用VBA来自定义函数,处理一些Excel内部函数无法直接处理的数据,更是灵活､便捷｡
[参考文献]
[1]安维默.统计电算化[M].北京:中国统计出版社,2000.
[责任编辑:李丽娜]
[关键词] Excel;描述统计;函数;宏
[作者简介]张占军(1969-),男,华北电力大学研究生院在读研究生｡(河北保定 071003)
Excel是大家熟知的Office组件,拥有丰富的函数,其中专门用于统计方面的函数就有近百个｡Ex-cel强大的数据计算功能,不仅能处理普通的数据运算,而且也能胜任处理专门的统计业务，如统计学中的数据描述､概率分布､参数估计､假设检验､方差分析等等,都可以用Excel来完成｡因此,对于绝大多数统计分析来说,用Excel替代昂贵的专门统计软件,就可以轻松完成统计分析的任务｡本文中,笔者以描述统计数据分析为例,来说明利用Excel处理统计专业数据的途径和步骤｡
一､统计函数
在描述统计中,描述数据集中趋势的统计指标有:众数､中位数､均值､调和平均数､几何平均数;描述数据离散程度的统计指标有:极值､全距､四分位差､方差､标准差等｡在未分组资料中,可直接用Excel函数计算这些统计指标｡如根据表1中的数据,计算某企业30名职工工资的众数,可在存放结果的单元格中输入= MODE(A2：F6)，按确认键,结果立刻出现在当前单元格中,比起手工计算查找来说又快又准确｡同样,计算其他统计指标时,在“插入函数”窗口的“函数类别”下拉文本框中选择“统计”类,然后在函数文本框中选择相关函数,使用即可,和使用Excel常用函数没有区别｡当选取某个函数时,在“插入函数”窗口的下面有帮助信息,简要说明该函数的功能及如何使用该函数｡
二､用VBA处理统计数据
由于函数只能应用于处理未分组资料,但对于分组的统计数据,不能直接使用Excel内部函数求得,需要多步计算,过程较繁琐｡但可以用VAB定义一个函数,使用这个函数,就像使用Excel内部函数一样,输入参数,点击确定,顷刻就可得到结果;再者可以在不同的地方､针对不同的数据进行计算,可谓一劳永逸｡定义这样一个函数需要一些简单的VBA编程知识,但并不很难｡如表2中所示资料,是对表1数据的分组(“组距上限”列各数值是各组组距的上限值,一定要这样输入,也是为了更好的使用FREQUENCY()函数),输入频数或用FREQUENCY()计算频数(计算后,通过“复制”“选择性粘贴”粘贴成数值)｡顺次执行菜单“工具”“宏”“Visual Basic编辑器”,打开Visual Basic编辑器,选择菜单“插入”“模块”,然后再选择菜单“插入”“过程”,在对话框中选择“函数”单选按钮,输入“函数名称”,如:get-Mode,点击确定,然后在弹出的窗口中输入如下代码:
Public Function get Mode (your Range As Range)
Dim max Frequency， lower Limit， class Interval， interval1， intervall2 As Single
Dim row，col As Integer＇定义变量:行号,列号
Set interval Range = your Range .Find (Application .Worksheet Function. Max (your Range))
row = interval Range. row ＇众数组所在行
col = interval Range. Column ＇众数组所在列
max Frequency = interval Range. Value ＇最大频数
lower Limit = Application. Cells(row - 1， col - 1).Value + 1 ＇组下限
class Interval = Application. Cells(row， col - 1).Value - Application. Cells(row - 1， col - 1).Value ＇组距
interval1 = max Frequency - Application. Cells(row - 1， col).Value
interval2 = max Frequency - Application. Cells(row + 1， col).Value
get Mode = lower Limit + (interval1 / (interval1 + interval2)) * class Interval ＇应用众数公式
End Function
最后保存退出,这样计算分组数据众数的函数“get Mode”就完成了｡使用方法和使用Excel内部函数一样,选择“用户定义”函数,就会看到get Mode函数,选择它并在参数框中输入频数的单元格区域,如表2中的“C3:C7”点击“确定”,众数就计算出来了｡将计算结果和手工计算的结果比较,结果是一致的｡使用这个函数同样可以直接计算其他分组数据的众数｡
上面是一个应用VBA计算集中趋势描述指标的例子,下面看一个如何用VBA计算离散程度指标的例子｡
计算标准差,未分组数据可直接用函数STDEV()或STDEVP()求得,若要求分组数据的标准差或带有权数的标准差,就不太容易了,但同样可以用自定义函数的方法解决之｡如表3是对表1数据的分组,求职工收入的标准差｡按照前面自定义函数的制作过程,定义如下函数:
Public Function get Stdev (interval Range As Range， avg As Single)
Dim i， row， col As Integer ＇定义变量:计数,行号,列号
row = interval Range. row
col = interval Range. Column
For i = row To row + interval Range. Rows. Count - 1
g = g + Cells(i， col)
h = h + (Application. Cells(i， col - 1) - avg) * (Application. Cells(i， col - 1) - avg) * Cells(i， col)
Next i
Get Stdev = Sqr(h / g)
End Function
简单的几行代码,函数就定义好了｡使用时输入参数为权数范围“C3：C7”和平均值“B9”,点击确定,就得出职工收入的标准差｡
计算分组数据的其他统计指标,同样可以自己定义函数,定义时虽然有些费事,但使用方便,计算快速｡
三､描述统计工具
前面介绍的都是利用函数计算单个统计指标,使用Excel的“分析工具库”中的“描述统计”,可以一次计算若干个统计指标,更方便快捷,还可以同时产生图表｡“分析工具库”在工具菜单下,如果没有,则要运行“安装”程序来加载“分析工具库”,安装完毕之后,通过“工具”菜单中的“加载宏”命令,在“加载宏”对话框中选择并启动它｡下面来看如何用它做描述统计｡
仍以前面职工工资收入为例,将数据排成一列,如表4中的A列(图中未显示全部数据),然后选择菜单“工具”“数据分析库”“描述统计”,弹出描述统计对话框｡在输入区域文本框中输入数据所在单元格,如“A2:A32”;在分组方式中,依据输入区域中的数据是按行还是按列排列,单击“行”或“列”,在这儿选择“列”;如果输入区域的第一行中包含标志项(变量名),则选中“标志位于第一行”复选框;如果输入区域的第一列中包含标志项,则选中“标志位于第一列”复选框;如果输入区域没有标志项,则不选任何复选框,Excel将在输出表中生成适宜的数据标志,这里选中“标志位于第一行”;“输出选项”,前三项依据输出结果存放的位置不同,来选择以本表中某个单元格开始的区域,或输出到新工作表或新工作簿;“汇总统计”,指定输出表中生成“平均值､标准误差､中值､众数､标准偏差､方差､峰值､偏斜度､极差(全距)最小值､最大值､总和､样本个数”统计结果,则选中此复选框;“均值置信度”:若需要输出由样本均值推断总体均值的置信区间,则选中此复选框,然后在右侧的编辑框中,输入所要使用的置信度;“第K个最大/小值”:在输出表中包含输入区域数据的第k个最大/小值,若选中此复选框,在右侧的编辑框中,输入k的数值｡所有这些都设置好后,点击确定,结果即可出来,如表4中的C列与D列｡
使用Excel内部函数和分析工具库,处理统计数据,简单的使用就可轻松获得计算结果;使用VBA来自定义函数,处理一些Excel内部函数无法直接处理的数据,更是灵活､便捷｡
[参考文献]
[1]安维默.统计电算化[M].北京:中国统计出版社,2000.
[责任编辑:李丽娜]