第10章 Excel软件在数理统计中的应用

第10章Excel软件在概率统计中的应用10.1中文Excel的基本介绍10.1.1中文Excel的概述Excel是一个功能多、技术先进、使用方便的表格式数据综合管理和分析系统，它采用电子表格的形式进行数据处理，工作简单明了，提供了丰富的函数，可以进行数据处理、统计分析和决策，同时，还具有较好的制图功能。工作表区由单元格组成，每个单元格由列标和行号识别。工作表区的最上面一行为列标，用A,B,…,Z,AA,AB,…,AZ,BA,BB,…,BZ,…,IA,…,IV等表示，工作表区左边一列为行号，用1,2,3，…表示，Excel2007及Excel2010增大了数据容量，列数最多可为16384，行数最多可为1048576。单元格“A1”表示单元格位于A列第1行。单元格区域则规定为矩形，例如“A1:E4”表示一矩形区域，如图10-1，A1和E4分别为该区域的主对角线两端的单元格。每张工作表有一个标签与之对应，例如：“sheet1”,“sheet2”,“sheet3”.工作表隶属于工作簿，一个工作簿最多可由255个不同的工作表组成。图10-110.1.2．Excel函数的调用方法1）首先选择函数值存放的单元格,若是Excel2003版，使用“插入”菜单—“函数”选项，如图10-2所示，或使用“常用”工具栏中的“粘贴函数”按钮进入“粘贴函数”对话框；若是Excel2010版，使用“公式”菜单—“插入函数”选项。图10-22）点击“函数”选项后，将出现如图10-3所示的对话框。图10-33）在“选择类别”列表中选择“统计”，如图10-4，然后在“函数名”列表中选择相应的函数，如选中函数“AVERAGE”,点击“确定”按钮，出现输入数据或单元格范围的对话框；如图10-5图10-4图10-54)输入数据或单元格范围,点击“确定”按钮，在函数值存放的单元格即计算出（返回）函数值。注：若对某函数的使用不太熟悉，可以点击图10-5左下方的“有关该函数的帮助”按钮，即可获得帮助。10.1.3.Excel中加载数据分析的方法若为Excel2003版本：1）首先单击“工具”菜单中“加载宏”选项，出现加载宏页面对话框。2）在对话框中选定“分析工具库”（在前面打“√”），如图10-6：图10-63）点击“确定”按钮；再次打开“工具”菜单，下拉菜单中将出现“数据分析”，如图10-7，本功能加载成功。图10-74）点击“数据分析”菜单，将出现如图10-8所示的对话框.图10-8若为Excel2010版本：1）首先点击自定义快速访问工具栏中的其他命令（M）…，如图10-9所示：图10-92）当出现如图10-10所示的Excel选项后，选择“加载项”。图10-103）当出现如图10-11所示的窗口后，选择“分析工具库”，然后点击下方的按钮“转到（G）…”,则将出现与图10-6一样的窗口，其他就和2003版一致了。当加载完成后，就会在“数据”菜单中看见“数据分析”工具条了。图10-11在图10-8的对话框中共有19个模块，它们分别属于5大类：1.基础分析：（1）随机数发生器；（2）抽样；（3）描述统计；（4）直方图；（5）排位与百分比排位。2.检验分析：（6）t检验：平均值的成对二样本分析；（7）t检验：双样本等方差假设；（8）t检验：双样本异方差假设；（9）Z检验：双样本平均差检验；（10）F检验：双样本方差。3.相关、回归：（11）相关系数；（12）协方差；（13）回归。4．方差分析：（14）方差分析：单因素方差分析；（15）方差分析：可重复双因素分析；（16）方差分析：无重复双因素分析。5.其它分析工具：（17）移动平均；（18）指数平滑；（19）傅立叶分析。10.2Excel数据计算的基本操作10.2.1、单组数据加减乘除运算（1）单组数据求和公式：=（A1+B1）举例：单元格A1：B1区域依次输入了数据20和10，计算：在C1中输入=A1+B1后点击键盘“Enter（确定）”键后，该单元格就自动显示20与10的和30。（2）单组数据求差公式：=（A1-B1）举例：在C1中输入=A1-B1即求20与10的差值10，电脑操作方法同上；（3）单组数据求乘法公式：=（A1*B1）举例：在C1中输入=A1*B1即求20与10的积值200，电脑操作方法同上；（4）单组数据求除法公式：=（A1/B1）举例：在C1中输入=A1/B1即求20与10的商值2，电脑操作方法同上；（5）其它应用：在D1中输入=A1^3即求20的立方（三次方）；在E1中输入=B1^（1/3）即求20的立方根注：（1）在单元格输入的含等号的运算式，Excel中称之为公式，公式前一定要加等号，否则将当成文本字符处理了。（2）在计算机上有的运算符号发生了改变：“×”与“*”同、“÷”与“/”同、“^”与“乘方”相同，开方作为乘方的逆运算，只需把乘方中的指数a变成其倒数1/a就成了数的开方运算。（3）如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元格，将鼠标移至该单元格的右下角，当出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公式自动复制，自动计算。10.2.2、多组数据加减乘除运算（1）多组数据求和公式：(常用)举例说明：=SUM（A1:A10）,表示同一列纵向从A1到A10的所有数据相加；=SUM（A1:J1）,表示不同列横向从A1到J1的所有第一行数据相加；（2）多组数据求乘积公式：（较常用）举例说明：=PRODUCT（A1:J1）表示不同列从A1到J1的所有第一行数据相乘；=PRODUCT（A1:A10）表示同列从A1到A10的所有的该列数据相乘；（3）多组数据求相减公式：(很少用)举例说明：=A1-SUM（A2:A10）表示同一列纵向从A1到A10的所有该列数据相减；=A1-SUM（B1:J1）表示不同列横向从A1到J1的所有第一行数据相减；（4）多组数据求除商公式：(极少用)举例说明：=A1/PRODUCT（B1:J1）表示不同列从A1到J1的所有第一行数据相除；=A1/PRODUCT（A2:A10）表示同列从A1到A10的所有的该列数据相除；10.2.3绝对地址与相对地址的区别相对引用：随着公式的位置变化，所引用单元格位置也是在变化的就是相对引用；绝对引用：随着公式位置的变化所引用单元格位置不变化的就是绝对引用。下面讲一下“C4”、“$C4”、“C$4”和“$C$4”之间的区别。在一个工作表中，在C4、C5中的数据分别是60、50。如果在D4单元格中输入“=C4”，那么将D4向下拖动到D5时，D5中的内容就变成了50，里面的公式是“=C5”，将D4向右拖动到E4，E4中的内容是60，里面的公式变成了“=D4”。如图10-12图10-12现在在D4单元格中输入“=$C4”，将D4向右拖动到E4，E4中的公式还是“=$C4”，而向下拖动到D5时，D5中的公式就成了“=$C5”。如图10-13图10-13如果在D4单元格中输入“=C$4”，那么将D4向右拖动到E4时，E4中的公式变为“=D$4”，而将D4向下拖动到D5时，D5中的公式还是“=C$4”。如图10-14图10-14如果在D4单元格中输入“=$C$4”，那么不论你将D4向哪个方向拖动，自动填充的公式都是“=$C$4”。原来谁前面带上了“$”号，在进行拖动时谁就不变。如果都带上了“$”，在拖动时两个位置都不能变。如图10-15图10-1510.2.4统计函数基本介绍1.AVEDEV用途：返回一组数据与其平均值的绝对偏差的平均值，该函数可以评测数据(例如学生的某科考试成绩)的离散度。语法：AVEDEV(number1，number2，...)参数：Number1、number2、...是用来计算绝对偏差平均值的一组参数，其个数可以在1～30个之间。参数必须是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。实例：如果A1=80、A2=60、A3=50、A4=90、A5=30，则公式“=AVEDEV(A1:A5)”返回18.4。2.AVERAGE用途：计算所有参数的算术平均值。语法：AVERAGE(number1，number2，...)。参数：Number1、number2、...是要计算平均值的1～30个参数。实例：如果A1:A5区域中的数值分别为100、80、95、48和85，则公式“=AVERAGE(A1:A5)”返回81.6。3.AVERAGEA用途：计算参数清单中数值的平均值。它与AVERAGE函数的区别在于不仅数字，而且文本和逻辑值(如TRUE和FALSE)也参与计算。语法：AVERAGEA(value1，value2，...)参数：value1、value2、...为需要计算平均值的1至30个单元格、单元格区域或数值。包含文本的数组或引用参数将作为0（零）计算。空文本()也作为0（零）计算。如果在平均值的计算中不能包含文本值，请使用函数AVERAGE。包含TRUE的参数作为1计算；包含FALSE的参数作为0计算。实例：如果A1=80、A2=90、A3=TRUE，则公式“=AVERAGEA(A1:A3)”返回57(即（76+85+1）/3=54)。4.BETA.DIST用途：返回Beta分布累积函数的函数值。Beta分布累积函数通常用于研究样本集合中某些事物的发生和变化情况。例如，人们一天中看电视的时间比率。语法：BETA.DIST(x，alpha，beta，A，B)参数：X用来进行函数计算的值，须居于可选性上下界(A和B)之间。Alpha，Beta均为分布的参数。A是数值x所属区间的可选下界，B是数值x所属区间的可选上界。如果省略A或B值，函数BETA.DIST使用标准beta分布的累积函数，即A=0，B=1实例：公式“=BETA.DIST(3，8，10，1，4)”返回0.972716。5.BETA.INV用途：返回beta分布累积函数的逆函数值。即，如果probability=BETA.DIST(x，…TRUE)，则BETA.INV(probability，...)=x。beta分布累积函数可用于项目设计，在给出期望的完成时间和变化参数后，模拟可能的完成时间。语法：BETAI.NV(probability，alpha，beta，A，B)参数：Probability为Beta分布的概率值，Alpha，Beta为分布的参数，A为数值x所属区间的可选下界，B为数值x所属区间的可选上界。实例：公式“=BETA.INV(0.972716，8，10，1，4)”返回3。6.BINOM.DIST用途：返回一元二项式分布的概率值。BINOM.DIST函数适用于固定次数的独立实验，实验的结果只包含成功或失败二种情况，且成功的概率在实验期间固定不变。例如，它可以计算掷10次硬币时正面朝上5次的概率。语法：BINOM.DIST(number_s，trials，probability_s，cumulative)参数：Number_s为实验成功的次数，Trials为独立实验的次数，Probability_s为一次实验中成功的概率，Cumulative是一个逻辑值，用于确定函数的形式。如果cumulative为TRUE，则BINOM.DIST函数返回累积分布函数，即至多number_s次成功的概率;如果为FALSE，返回概率密度函数，即number_s次成功的概率。实例：抛硬币的结果不是正面就是反面，第一次抛硬币为正面的概率是0.5。则掷硬币10次中5次的计算公式为“=BINOM.DIST(5，10，0.5，FALSE)”，计算的结果等于0.246093757.CHISQ.DIST用途：返回2分布的左尾概率。2分布与2检验相关。使用2检验可以比较观察值和期望值。例如，某项遗传学实验假设下一代植物将呈现出某一组颜色。使用此函数比较观测结果和期望