Excel统计功能.doc

《Excel统计功能.doc》由会员分享，可在线阅读，更多相关《Excel统计功能.doc（4页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel的统计功能一个Excel工作簿（book）可显示255个工作表（sheet），每一工作表由65536行256列的表格组成（见图1）。行号（如1、2、3，范围：165536）和列标（如A、B、C，范围：AIV）分别用数字和字母表示。每张工作表的最多可容纳65536个观察个体、256个变量。图1 Excel界面1. 数据编辑整理功能Excel具有卓越的自动填充、数据编辑、数据查询等功能，对查询结果可迅速建立子数据清单。SPSS、SAS等统计学软件可以十分方便地读取Excel文件数据，因此对于样本含量不是特别大的研究，可以采用Excel作为数据建库软件。2. 数据统计分析工具Excel有一

2、个分析工具叫做“数据分析（data analysis）”，对于统计学工作者及其有关人员十分有用。在安装好微软Excel后，一般在Excel工具菜单中找不到“数据分析”，需要“加载宏”，进行继续安装。安装“数据分析”工具的方法是： 单击“工具加载宏”，弹出加载宏界面。 选取“分析工具库”等，按确定键，根据屏幕提示进行安装。如果以前的Excel不是完全安装（特别推荐采用完全安装格式进行安装），还需按照安装提示插入相同版本的Office盘进行安装。安装完“加载宏”后，打开“工具”菜单，即可发现“数据分析”选项。单击菜单中的“工具数据分析”，可弹出数据分析菜单界面（见图2）。图2 Excel “数据分

3、析”分析工具对话框利用数据分析工具可简单地完成常用的统计学分析，如数据的统计学描述、制作直方图、进行t检验、方差分析、相关与回归分析等等。此外，Excel的数据透视功能可帮助我们进行统计数据的探索性分析。其调用方式是：将鼠标放在数据区内，点击菜单中的“数据数据透视表和数据透视图”，按提示完成数据的整理与分析。3. Excel的函数与公式表1 常见的统计函数编号函数举例说明（结果）1AVERAGE(number1,number2, .)=AVERAGE(A2:A6)获得一组数据（或单元格引用）的均数公式：（）/n2BINOMDIST(number_s, trials,probability_s,

4、 cumulative)=BINOMDIST(6,10, 0.3, 0)总体率为0.3时10 次试验中成功 6 次的概率(参见第2章)；Cumulative=0或FALSE计算概率密度函数;Cumulative=1或TRUE计算累积分布函数3CHIDIST(x, degrees_freedom)=CHIDIST(18.307, 10)卡方值18.307在自由度为10的卡方分布中的P值（0.05）； 参见第8章4CHIINV(probability, degrees_freedom)=CHIINV(0.05,10)自由度为10的卡方分布中概率为P时的界值(18.30703)；参见第8章5CONF

5、IDENCE(alpha,standard_dev,size)=CONFIDENCE(0.05,2.5,50)由检验水准、标准差S、样本例数n获得总体平均值的置信区间宽度的一半(0.693) 公式：Z/2 S/；参见第4章6CORREL(array1,array2)=CORREL(A2:A6,B2:B6)计算区域A2:A6与B2:B6内数据间的Pearson相关系数7COUNT(value1,value2,.)=COUNT(A2:A8)清点区域中数值单元格的个数（不包括空白单元格、文本、逻辑值）8COUNTIF(range,criteria)=COUNTIF(B2:B5,55)计算区域B2:B

6、5中的数值大于 55 的单元格个数9COVAR(array1,array2)=COVAR(A2:A6, B2:B6)计算协方差，即每对数据点的离均差乘积的平均数；公式：()()/n10DEVSQ(number1,number2,.)=DEVSQ(A2:A8)计算离均差平方和公式：()211FDIST(x,degrees_freedom1,degrees_freedom2)=FDIST(15.21,6,4)计算15.21在分子自由度为6、分母自由度为4的F分布中的P值 （0.01）；参见第7章12FINV(probability,degrees_freedom1,degrees_freedom2

7、)=FINV(0.01,6,4)获得分子自由度为6、分母自由度为4的 F分布的0.01界值(15.20675)，FDIST的逆函数；参见第7章13FISHER(x)=FISHER(0.75)相关系数为r = 0.75的正态变换值(0.972955)；可采用变换后的值按正态方法求相关系数置信区间。公式：ZATANH(r)=0.5ln(1+r)/(1-r)14FISHERINV(y)=FISHERINV (0.972955)将变换值Z=0.972955 还原为相关系数 (0.75)，FISHER的逆函数；公式：r =TANH(Z)=15FREQUENCY(data_array, bins_arra

8、y)=FREQUENCY(A2:A10,B2:B5)获得连续型变量的频数表； A2:A10为原数据，B2:B5为频数表各个组段的上限值；参见第2章16GEOMEAN(number1,number2,.)=GEOMEAN(4,5,8,7,11,4,3)计算数据的几何均数 (5.476987)；参见第2章17HARMEAN(number1,number2,.)=HARMEAN(A2:A8)计算数据区域A2:A8的调和平均值 (5.028376)；参见第2章18MAX(number1,number2,.)=MAX(A2:A6)求区域A2:A6中最大值；参见第2章19MEDIAN(number1,nu

9、mber2, .)=MEDIAN(A2:A7)求数据A2:A7的中位数，参见第2章20MIN(number1,number2,.)=MIN(A2:A6)求数据A2:A6的最小值；参见第2章21MODE(number1,number2,.)=MODE(A2:A7)求数据A2:A7的众数，即出现频率最多的数22NORMDIST(x,mean,standard_dev,cumulative)=NORMDIST(42,40, 1.5,TRUE)计算42在正态分布（40，1.52）中的累积函数(0.908789)；参见第3章；Cumulative=0或FALSE计算概率密度函数;Cumulative=1

10、或TRUE计算累积分布函数23NORMINV(probability,mean,standard_dev)=NORMINV(0.908789,40,1.5)获得正态分布累积函数的逆函数值（或分位数）（42）；参见第3章24NORMSDIST(z)=NORMSDIST(1.645)获得1.645 的标准正态分布累积函数值；参见第3章25NORMSINV(probability)=NORMSINV(0.95)获得标准正态累积分布函数的逆函数值；即由P值获得标准正态分布临界值 (1.645)；参见第3章26PEARSON(array1,array2)=PEARSON(A2:A6, B2:B6)获得P

11、earson相关系数，等价于CORREL函数27PERCENTILE(array,k)=PERCENTILE(A2:A5,0.3)求单元格区域A2:A5数据的第 30 百分位数值 28POISSON(x,mean, cumulative)=POISSON(2,5, TRUE)均值5的泊松分布中发生2次的概率 (0.124652)；Cumulative=0或FALSE计算概率密度函数;Cumulative=1或TRUE计算累积分布函数；参见第3章29RSQ(known_ys,known_xs)=RSQ(A2:A8,B2:B8)计算Pearson相关系数的平方，即决定系数30STANDARDIZE

12、(x,mean, standard_dev)= STANDARDIZE(42,40,1.5)将一般正态分布（，2）转化为标准正态分布（0，12），(1.333333)公式：z（X）/31STDEV(number1,number2,.)=STDEV(A2:A11)无偏估计单元格区域A2:A11中数据的标准差；参见第3章32TDIST(x,degrees_freedom, tails)=TDIST(1.96,60,2)返回1.96在自由度为60的t分布中的双侧P值（0.054644927）；tails=2，双侧；tails=1，单侧；参见5章33TINV(probability, degrees_

13、freedom)=TINV(0.054645, 60)返回自由度为60的t 分布中P0.054645双尾界值(1.959997462) 34VAR(number1,number2,.)=VAR(A2:A11)估计区域A2:A11的样本方差（即标准差的平方）；参见第2章； 公式：35VARP(number1,number2,.)=VARP(A2:A11)估计区域A2:A11的总体方差；公式：Excel内置函数有近400个，包括数学和三角函数、统计函数、日期与时间函数、文本函数、逻辑函数、查询和引用函数、数据库函数、信息函数、工程函数、财务函数、用户定义函数等11类。其中常见的统计函数见表1所示。

14、利用函数和运算符【如算术运算符（+、-、*、/、%）、比较运算符（=、=、=、）、文本运算符（&）、引用运算符（“：（冒号）”、“，（逗号）”、“ （空格）”）】，可建立各种Excel公式，完成各种纷繁复杂的统计计算。为了建立公式，首先必须键入“=”，键入“=”表示编辑公式即将开始。一般可使用下列操作步骤建立公式：（1）选择要建立公式的单元格。（2）键入“=”。一般先将鼠标指针指向编辑栏，并单击，然后输入等号“=”；如果要利用Excel函数建立公式，可在编辑栏左侧点击插入函数“”按钮。（3）输入公式的内容，比如“=SUM(B4:G4)”。如果输入的是内置函数，如求和函数SUM，Excel会自动

15、弹出函数的格式文本框，提示用户正确的函数输入格式（见图3）（注意Excel 2002以上版本才有此功能）。图3 编辑栏输入函数图示（4）为了完成公式的编辑，确认输入的公式，可按Enter键或用鼠标单击编辑栏左侧的“”按钮；如果要取消编辑的公式，可单击编辑栏的“”按钮。矩阵运算时需要在多个单元格（即一个单元格区域）中建立一个公式，为了完成公式的编辑，确认输入的公式必须按Ctrl + Shift + Enter组合键，而不能只按Enter键。4. 电脑实验的制作原理Excel绘图功能非常强大，修饰加工后的Excel图形美观好看；单元格数据与Excel分析结果之间具有“联动”关系，改变其中一个单元格数据，与之相关的Excel公式或图表就会发生相应的改变，具有“即改即见”的效果，这一特点有利于制作生动的统计学电脑实验。本书的大多数电脑试验就是根据这一原理制成的。