用Excel统计的方法

Excel统计方法一、计量资料的常用统计描述指标1.平均数工平均数表示的是一组观察值（变量值）的平均水平或集中趋势。平均数计算公式：式中：NX为变量值、Z为总和，N为观察值的个数。标准差（S）标准差表示的是一组个体变量间的变异（离散）程度的大小。S愈小，表示观察值的变异程度愈小，反之亦然，常写成 夏±£。标准差计算公式：*MV27-1式中：EX2为各变量值的平方和，（EX）2为各变量和的平方，N-1为自由度标准误（S又）标准误表示的是样本均数的标准差，用以说明样本均数的分布情况，表示x和估量群体之间的差异，即各次重复抽样结果之间的差异。S^愈小，表示抽样误差愈小，样本均x数与总体均数愈接近，样本均数的可靠性也愈大，反之亦然，常写作 工士获。标准误计算公式：L_S牍= 二、计数资料的常用统计描述指标率和比率是一种表示在一定条件下某种现象实际发生例数与可能发生该现象的总数比，用来说明某种现象发生的频率。比是表示事物或现象内部各构成部分的比重。率和比计算公式：率=书+—颈。%顼+）+顼-）比= X100%A+B+C+U+"-率和比的标准误率和比的标准误是抽样造成的误差，表示样本百分率和比与总体百分率和比之间的差异，标准误小，说明抽样误差小，可靠性大，反之亦然。（p为率的标准误，P为样本率，当样本可靠且有一定数量的观察单位时可代替总体率。N为样本观察例数）三、显著性检验抽样实验会产生抽样误差，对实验资料进行比较分析时，不能仅凭两个结果（平均数或率）的不同就作出结论，而是要进行统计学分析，鉴别出两者差异是抽样误差引起的，还是由特定的实验处理引起的。显著性检验的含义和原理显著性检验即用于实验处理组与对照组或两种不同处理的效应之间是否有差异，以及这种差异是否显著的方法。无效假设显著性检验的基本原理是提出“无效假设”和检验“无效假设”成立的机率（P）水平的选择。所谓“无效假设”，就是当比较实验处理组与对照组的结果时，假设两组结果间差异不显著，即实验处理对结果没有影响或无效。经统计学分析后，如发现两组间差异系抽样引起的，则“无效假设”成立，可认为这种差异为不显著（即实验处理无效）。若两组间差异不是由抽样引起的，则“无效假设”不成立，可认为这种差异是显著的（即实验处理有效）。“无效假设”成立的机率水平检验“无效假设”成立的机率水平一般定为5%（常写为PW0.05），其含义是将同一实验重复100次，两者结果间的差异有5次以上是由抽样误差造成的，则“无效假设”成立，可认为两组间的差异为不显著，常记为P>0.05。若两者结果间的差异5次以下是由抽样误差造成的，则“无效假设”不成立，可认为两组间的差异为显著，常记为PW0.05。如果PW0.01，则认为两组间的差异为非常显著。（一）计量资料的显著性检验t检验（1）配对资料（实验前后）的比较假设配对资料差数的总体平均数为零。其计算公式：*（2）两样本均数的比较计算公式：£71-X2式中R-R为两数均数之差； 湍一前为两组合并标准误，计算公式：境1F=Sc2勺竺VlL勺说1） 自由度计算：两组例数相等，自由度f=2n-2；两组例数不等，自由度 "处3跖+&J2） 概率的确定：七<七，p>0.05；；七>七>七，0.01<p<0.05，七>七，p<0.01。0.05 0.01 0.05 0.01t0.05和^0.01是对应某一自由度，概率分别为p=0.05和P-0.01时的t界值，可从t值表上查得。（二）计数资料的显著性检验X2检验适用于二组或二组以上的计数资料的显著性检验。X2的计算公式：拱_寸皿-7^其中A为实测值，T为理论值。用四格表计算x两组计数资料可用四格表格表示。如A、B两组，A组阳性和阴性反应例数分别为a、b，B组阳性和阴性反应例数分别为c、d，其四格表如表5-10。表5-10四格表组别阳性例数阴性例数合计阳性百分率(%)Aa(Ta)b(Tb)a+ba/(a+b)X100Bc(Tc)d(Td)c+dc/(c+d)X100合计a+cb+da+b+c+d(a+c)/(a+b+c+d)X100腥-bc\-N!^N[a+b)(c+d)(a+c)(b+d)其中N=a+b+c+d。自由度计算：n‘=(R-1)(C-1) (R代表行数，C代表列数，本例R=2,C=2)。理论值计算：Ta=(a+c)(a+b)/N，Tb=(b+d)(a+b)/N，Tc=(a+c)(c+d)/N，Td=(b+d)(c+d)/N。概率的确定：X2<X2005，p>0.05；X2001>X2>X2005，0.01<p<0.05；X2>X2001，P<0.01。X20.05和X20.01是对应某一自由度，概率分别为p=0.05和p=0.01时的X2界值，可从X2界值表上查得。四、Excel中常用的数据统计分析功能实验数据的统计分析可以应用专业化统计分析软件进行，如SPSS、SigmaStat、Prism等优秀软件，这些软件功能齐全、使用十分方便，并可在统计后或同时作图，结果图标准、直接可用于论文发表。本小节主要介绍MSOffice中的Excel的常用数据统计功能。Excel提供了一些常用的统计工具，如均数、方差、t检验等。首次使用Excel的“数据分析”工具进行统计时，需加载数据分析工具库。加载方法：选取菜单栏的“工具”，在弹出的下拉菜单中单击“加载宏”，在弹出的对话框的“分析工具库”选项前方的小方框(复选框)打上勾，再单击“确定”按钮结束加载。经过上述操作，在菜单栏的“工具”中就会出现“数据分析”选项，以后使用统计功能时单击该选项就可调出数据分析工具对话框。当用户要进行某项统计时，选取菜单栏“工具”下拉菜单中的“数据分析”项并单击鼠标，就会弹出数据分析工具箱对话框(如图5-4-1)，再在对话框中选取所需的统计工具并单击就可进入相应统计工具对话框。

（一）用Excel数据分析工具进行统计生理科学实验中常用的统计方法有描述统计（均数、标准差）、方差分析、t检验、回归、相关系数等。下面简单介绍在Excel中如何进行这几项统计。图5-4-1数据分析工具箱对话框图5-4-2区域选择对话框1.描述统计（均数、标准差）（1）数据输入将需要统计的数据按列输入Excel表格中（如图5-4-2）。（2） 选择工具选取菜单栏“工具”下拉菜单中的“数据分析”项，在弹出数据分析工具箱对话框（如图5-4-1），选择“描述统计”项，“描述统计”对话框弹出。（3） 将分组方式设为“逐列”，选中汇总统计（如图5-4-3）。“第K大值”和“第K小值”是用于排除最大值和最小值的，可根据需要选择。（4）对话框“输入区域”右边的有红色箭头的小按钮（如图5-4-3），弹出区域选择对话框（如图5-4-2），在工作簿内拖动鼠标选择要统计的数据区域后关闭该对话框。（5）单击“输出区域”前面的小圆点（如图5-4-3），将统计结果输出到同一工作簿。再单击“输出区域”右方有红色箭头的小按钮，执行类似第（2）步的操作以选择统计结果的输出区域。（6）单击“确定”，描述统计的结果即出现在用户指定的区域中（如图5-4-3）。描述统计共产生14个统计量值，他们分别是：平均值、标准误差、中值（中数，Median）、模式（众数，Mode）、标准偏差、样本方差、峰值、偏斜度、区域（全距，rang）、最小值、最大值、求和、计数和置换度。如图5-4-3显示描述统计对话框及统计结果。图5-4-3描述统计对话框及统计结果t-检验在Excel中提供了三种t-检验方法，分述如下:（1） t-检验成对双样本平均差检验。比较两套数据的平均值。但数据必须是自然成对出现的，比如同一实验的两次数据，且必须有相同的数据点个数。两套数据的方差假设不相等。（2） t-检验双样本等方差假设。假设两个样本的方差相等来确定两样本的平均值是否相等。（3） t-检验双样本异方差假设。假设两个样本的方差不相等来确定两样本的平均值是否相等。（4） t-检验方法的操作方法以上三种t-检验方法的操作方法相同，介绍如下（图5-4-4）：打开“t-检验”对话框指定“变量1”和“变量2”的输入范围。选择输出区域。

单击“确定”取得统计结果。方差分析方差分析一般检验多套数据的平均值来确定这些数据集合中提供的样本的平均值是否也相等oExcel有三种方差分析工具，即：单因素方差分析通过简单的方差分析，对两个以上样本进行相等性假设检验。此方法是对双均值检验的扩充。可重复双因素方差分析该分析是对单因素分析的扩展，要求对分析的每组数据有一个以上样本，且数据集合必须大小相同。|[2奶时e威H-蜘diffto«&ejn国也也Ke稽而做iAdfedhu|[2奶时e威H-蜘diffto«&ejn国也也Ke稽而做iAdfedhu■口（用-1*1»1】*CI翩螺淄H|日-]矿"说|.E利叫 ♦U-1B111 贸耕&寻Bi3-1 ■FD E】姓觥：旗荷煎祥司咽佰分折23g&A7_JU101112.一也1415162?357&*y55s67s2s4平旃方轮观测值特松相关系戳保波平均箜dftSlQ!P<T<=tJ单尾„—, 1单用临界I Pgt〕双尾t芝尾临弊625懈.岫51,^51310.83SSS3。一4瘀14jtMirrmiL相瞥;平构01的成制MFT本分薜0.21276?0.119277-0.T75TT0慵出壕审卷械工Mfefi必:rfjjsfif®)HIHIILfMJ<i»iHKSh*rtl/Sh^rtBZShfrm/图5-4-4t-检验无重复双因素方差分析通过双因素方差分析(但每组数据只包含一个样本)，对两个以上样本进行相等性假设检验。单因素方差分析和无重复双因素方差分析操作方法单因素方差分析和无重复双因素方差分析方法一致，如图5-4-5o打开“单因素方差分析”对话框。定义输入区域，选分组方式为“逐列”，并选中“标志位于第1行”复选框。定义输出区域和显著水平a，Excel默认a为0.05。单击“确定”按钮即得统计结果。可重复双因素方差分析方法如图5-4-6，操作方法如下：打开“可重复双因素方差分析”对话框。

定义输入区域。该工具对输入区域内的数据排放格式有两点特殊规定：I数据组以列方式排放。II数据域的第一列和第一行必须是因素的标志。定义输出区域和显著水平a，Excel默认a为0.05。单击“确定”按钮即得统计结果。图5-4-5单因素方差分析和无重复双因素方差分析公文件归偶辑【曰祝EK丹苞格式《史TAiD敏君口〕案口(也带曲(由[:_少[:_少H-： |路/•d=NdACDEFGHIJ:J13c方差分析：可重夏双因素分析2aa52363bbS722SUHHIAP.b总计4cc63:V43aa5dd7S67计数2&ee9?45求和1395819772837平翻泓52949.258fig3985云)差6139S7S3.69101114戴和麴差

计不平万2138.6972,151&eej_t_L计薮2.18 求和125万差会折：可重教掌欧分曲演翰出顼仍： 房厂 Mr新工作汞期:皿：l9101114戴和麴差

计不平万2138.6972,151&eej_t_L计薮2.18 求和125万差会折：可重教掌欧分曲演翰出顼仍： 房厂 Mr新工作汞期:皿：l新工作荷⑦?X：图5-4-6可重复双因素方差分析回归分析回归是求出锯齿状分布数据的平滑线，一般用图形表示，以直线或平滑线来拟合散布的数据。回归分析使得原始数据的不明显趋势变得清晰可见。求回归的方法如图5-4-7所示：打开“回归”对话框指定“X区域”和“Y区域”的输入范围。回归采用一系列X—Y值，即每个数据点的座标来计算结果，因此上述两个框都必须填入数值。选择输出区域。单击“确定”取得统计结果。在回归对话框中将线性拟合图前方的复选框勾上即可生成线性拟合图回归公式Y=a+bX中的a等于intercept的Coefficients值，b等于XVariable1的Coefficients值。

统计结果的回归统计项中的“MultipleR”值即为两组数据的相关系数。(8)图5-4-8示回归结果及线形拟合图，虚线框内为a和b的取值。图5-4-7回归分析图5-4-8回归结果及线性拟合图相关系数相关系数表明某个数据集合是否与另一个数据集合有因果关系。相关系数工具检查每个数据点与另一个数据集合对应数据点的关系。如果两个数据集合变化方向相同(同时为正或同时为负)，就返回一个正数，否则返回负数。两个数据集合变化越接近，他们的相关性就越高。相关值为“1”表明两组数据的变化情况一模一样，为“-1”表明值的变化情况刚好相反。相关系数的操作步骤如下：（1） 打开“相关系数”对话框见图5-4-9（2） 指定输入区域。（3） 选择输出区域。（4） 单击“确定”取得统计结果。（5） 如回归统计中所述，相关系数也可用回归求得。图5-4-9相关系数对话框及统计结果（二）用EXCEL的统计函数进行统计用统计函数进行统计的优点是简便，可以通过复制、粘贴命令对几十、乃至数百组数据进行计算、观察他们的变化趋势及差异等。1.平均数计算（2） 将一组数据按行或列输入表格。如图5-4-10所示，将datal和data2两组数据按列输入表格。选择表的一空白单元格，存放平均数的计算结果，如图5-4-10所示的黑线框，将鼠标器移至工具栏的“fx”处，鼠标器左键点击工具栏的“fx”快捷键，打开函数选择框。（2）在函数选择框的“函数分类”栏选择“统计”项，然后在“函数名”栏内选择“AVERAGE”函数，选择上二项后，用鼠标器点击“确定”按钮，打开数据输入框。（3） 在数据输入处“Numberl”项的输入框内输入数据的起始单元格和结束单元格的行列号，起始单格元和结束单元格的行列号之间用“：”分隔（图5-4-12）。用鼠标器点击“确定”按钮，观察计算结果：在表存放平均数的空白单元格处显示平均数的计算结果。在“编辑”栏处显示平

均数计算的函数“AVERAGE”及数据的起始与结束单元格的行列号（图5-4-13）。2.标准差计算（1） 将一组数据按行或列输入表格。如图5-4-10所示，将datal和data2两组数据按列输入表格。选择表的一空白单元格，存放标准差的计算结果，将鼠标器移至工具栏的“fx”处，鼠标器左键点击工具栏的“fx”快捷键，打开函数选择框。（2） 先在函数选择框的“函数分类”栏选择“统计”项，然后在“函数名”栏内选择“STDEV”函数，用鼠标器点击“确定”按钮，打开数据输入框。（3） 在数据输入处“Number1”项的输入框内输入数据的起始单元格和结束单元格的行列号，起始单格元和结束单元格的行列号之间用“：”分隔。用鼠标器点击“确定”按钮，观察计算结果。（4） 在表存放标准差的空白单元格处显示平均数的计算结果。在“编辑”栏处显示平均数计算的函数“STDEV”及数据的起始与结束单元格的行列号。I翊装乏60「3.2-4■UUJ1.9®1-67L9S3,734.32A332.34*标准是P恒4,574.o25-I翊装乏60「3.2-4■UUJ1.9®1-67L9S3,734.32A332.34*标准是P恒4,574.o25-78图5-4-10数据输入•皿瞄3队】，3*,）冏!WF峪bZTADtJrtnidwELIOTDISTOUMT口现CHI1BTOCirrCDEHTECD盹LZJ把fl或刊目手芳的耳木+■均乾：于救司II.是北，互可包苫数酒吨名1札引用・图5-4-11函数选择框二AVERAGE（E2:E12）C11E1f_FG 二AVERAGE（E2:E12）C11E1f_FG nr-- dstaldata2AVEEA.GENumberl|E2：E12 ={2.6;3.Z1;3.73;3.Mu*ber2| =兰4.T10MQO91计宜参毂的苴术平均教；秦敬甫以是毂信或包含数值的咯福、数沮戒引用口JTwnb»rt:nnrt^rljnwn?rj,.用于许宜平均数的6.53F.574.S2E.7&垮数 |E£E⑵ 十—1到50个戮值。计耳渤=5MO蹒1 |强弄公|卑清邕图5-4-12计算选择框«£A虹型，b/a三三三M鼠£|=^VERAGE（E：2：E12）C DL__Fdata]2.6001-i-fO3.2401.9S0L9SQ3.730g330i3702.$404.V302.50D<1303-6005.58'）3.7305.?304.1406.如。土顷）6一5304.S2DE.730娜Ipii图5-4-13平均数计算结果用统计函数进行统计的优点是简便，可以通过复制、粘贴命令对几十、乃至数百组数据进行计算、观察他们的变化趋势及差异等。t检验（1） t检验时将需要进行比较的两组数据按行或列输入表格。如图5-4-14所示，将datal和data2两组数据按列输入表格。选择表的一空白单元格，存放概率P值的计算结果，将鼠标器移至工具栏的“fx”处，鼠标器左键点击工具栏的“fx”快捷键，打开函数选择框。（2） 在函数选择框的“函数分类”栏选择“统计”项，然后在“函数名”栏内选择“TTEST”函数（图5-4-15），选择上二项后，用鼠标器点击“确定”按钮，打开数据输入框。（3） 如图5-4-16在数据输入处“Arrayl”项的输入框内输入第一组数据（如datal）的起始单元格和结束单元格的行列号，在“Array2”项的输入框内输入第二组数据（如data2）的起始单元格和结束单元格的行列号，起始单元格和结束单元格的行列号之间用“：”分隔。根据检验的要求在“Tails”项的输入框内输入“1”（用于单侧检验，也称单尾）或“2”（用于双侧检验，也称双尾）。根据数据的性质在“Type”项的输入框内输入“1”、“2”或“3”。在各项数据输入完毕后，P值的计算结果立即显示。用鼠标器点击“确定”按钮，观察计算结果。（2） 在函数选择框的“函数分类”栏选择“统计”项，然后在“函数名”栏内选择“TTEST”函数5-4-15），选择上二项后，用鼠标器点击“确定”按钮，打开数据输入框。（3） 如图5-4-16在数据输入处“Arrayl”项的输入框内输入第一组数据（如data1）的起始单元结束单元格的行列号，在“Array2”项的输入框内输入第二组数据（如data2）的起始单元格和结束单的行列号，起始单元格和结束单元格的行列号之间用“：”分隔。

根据检验的要求在“Tails”项的输入框内输入“1”（用于单侧检验，也称单尾）或“2”（用于检验，也称双尾）。根据数据的性质在“Type”项的输入框内输入“1”、“2”或“3”。在各项数据完毕后，P值的计算结果立即显示。用鼠标器点击“确定”按钮，观察计算结果。（4）在存放概率p值的空白单元格处显示P值的计算结果。在“编辑”栏处显示t检验的函数“TTEST”及两组比较数据的起始与结束单元格的行列号、分布的尾数和 t检验的类型（图5-4-17）。用函数计算平均数（x）、标准差（S）和概率（p）时，仅需计算一列或一行，其它结果列或行可通过复制的方法得到结果，只需修改数据起始与结束单元格的行列号。%:%:qh*s.=国B/U云云壬困-5-EFdM快12.M01.670.丸-5-EFdM快12.M01.670.丸2401-9503.T3O1.甄）3.7302.3304.3202.3404.7302.5005.LE0氏6J05.5803.7305.7804.1406.4004.17'：)6-S30■J.5T04,湖5.7E'：'均数 4.门。9标箍差P值!j图5-4-14数据输入~~e I~~idataldatai2=TT皑卜心ML有幻虹麒！£一一」爨datal2.60«) 1.1>70姓敝4.71：|9标准苴L3口2卯丁.F值 回暴！¥R?IS摩g-氏szlaI11二洒■迅盟，携・=nZ.ST(Z.2:E12,F2:F14,2,3)cILDE?■dataldara22.EXLgm,2401.獭3.730L33fl3,02.330土3网2.3^：'17302.5005.1303.?■；：■5.53C3.73fi&.TEO4.1-3：'&.doo4.170B.5304.570LE境R78fi南数i."X标建差1一我抄置傩10.-il342^图5-4-15函数选择框图5-4-17p值计算结果图图5-4-17p值计算结果卡方检验（X2）卡方（X2）常用以检验两个或两个以上样本率或构成比之间差别的显著性分析，用以说明两类属性现象之间是否存在一定的关系。卡方检验常采用四格表，如图5-4-18所示，比较的A、B两组数据分别用a、b、c、d表示，a为A组的阳性例数，b为A组的阴性例数，c为B组的阳性例数，d为B组的阴性例数。用EXCEL进行卡方检验时，数据的输入方式按实际值和理论值分别输入四个单元格，如图5-4-18所示。（1） 比较的A、B两组数据分别用a、b、c、d表示。a=52,为A组的阳性例数；b=19,为A组的阴性例数；c=39,为B组的阳性例数；d=3,为B组的阴性例数。根据公式计算理论值T11、T12、、T21和T22。将实际值和理论值分别输入如图所示的四个单元格（图5-4-19）。选择表的一空白单元格，存放概率p值的计算结果，将鼠标器移至工具栏的“fx”处，鼠标器左键点击工具栏的“fx”快捷键，打开函数选择框。（2）在函数选择框的“函数分类”栏选择“统计”项，然后在“函数名” 栏内选择“CHITEST”函数，用鼠标器点击“确定”按钮，打开数据输入框（图5-4-20）。（3） 在“Actual_range”项的输入框内输入实际值（a、b、c、d）的起始单元格和结束单元格的行列号，在“Expected_range”项的输入框内输入理论值（T11、T12、T21、T22）的起始单元格和结束单元格的行列号，起始单元格和结束单元格的行列号之间用“：”分隔（图5-4-20）。在数据输入完毕后，P值的计算结果立即显示。用鼠标器点击“确定”按钮，观察计算结果。X电CB。，二f%a耳检该设-BJIE=M寻国诲1%j哭*共!=SUfl(〔成79)+I52H9)/iS2卜13+39i3))EFGC DEG阳性伊朋i阳1生例数合计阿性例数原性例数合计直组aba+bA组absrH)B组Cdc+d涸Ed合计&4-Cfc+da+b+c-d合计a+cb+4壮b+c+d实际值ab洌如Ecd阳也倒数阴性例数合计理论蔺thTP5219T21T22m组台th约322聘117注：T11=(a+v)(a+b口叫 T1Z^(b-M)(a+b)/(a-b+c+tJ)夹际值5219T21=(3+c)(c+d)/(a+b4c+d) j39 ] 3-.7■rjrQ-jln眼勺"TlN=(b十＜0([十d)饱十trbc十d)33.S2301幻花的1P值图5-4-19图5-4-18四格表图5-4-19四格表数据输入

二DT—E F］「矿二DT—E F］「矿ij阳性例数胴性种.数含i—-J1~1 . „ 弟3理论值T7699宜"食河,BZUW兰兰圉骤#,抽盘1：■1=CHITEST(£12:FL3,E14:F15JCDEF皿.阳性例敝皙1怪例披A组由ba+bE组dc+d舍UsH-cb+da+b+c+d例如，阳性例救阴性例散A组532nB组59342舍什91221-3实际值523$3建论值57.1769913.3230133.E2301S.176391LL01捕殂L 图5-4-21〃值计算结果图5-4-20计算选择框4.在表存放概率p值的空白单元格处显示p值的计算结果。在“编辑”栏处显示％2检验的函数“CHITEST”及两组比较数据的起始与结束单元格的行列号（图5-4-21）。直线回归参数计算（1）截距（a）计算将计算截距a的数据按X列和Y列输入，如图5-4-22所示。选择表的一空白单元格，存放截距a计算结果，如图5-4-22所示的黑线框，