1、73第十三章 计算机在概率数理统计中应用13.1 Excel 的主要统计功能与数据引用13.2 常用的统计函数1、学时：2 学时2、过程与方法：通过实际操作介绍 Excel 的主要统计功能与数据引用，以及常用的统计函数3、教学要求：（1）掌握 Excel 的主要统计功能与数据引用（2）掌握常用的统计函数教学重点：Excel 的主要统计功能与数据引用，常用的统计函数教学形式：多媒体讲授教学过程：一、新课教学内容13.1 Excel 的主要统计功能与数据引用13.1.1 Excel 的主要统计功能（1）统计绘图，Excel 有多种图表类型如 xy 散点图、柱形图、拆线图、饼图、圆环图、曲面图等等（
2、2）概率分布提供了 20 种概率分布类型，其中包括连续分布和离散分布，而且每种分布类型均给出 5 个有用的函数，即概率密度函数，累积分布函数、逆累积分布函数、随机数产生器、均值和方差计算函数（3）参数估计依据特定分布的原始数据，可以计算分布参数的估计值及其置信区间（4）描述性统计提供描述数据样本特征的函数包括位置和散布的度量，分位数估计和处理数74据缺失情况的函数等（5）线性模型针对线性模型、工具箱提供的函数涉及单因素方差分析，双因素方差分析，一元线性回归，多元线性回归等（6）非线性模型为非线性模型提供的函数涉及参数估计，多元非线性拟合的预测以及参数和预计值的置信区间计算等（7）假设检验提供最
3、通用的假设检验的函数：t 检验和 z 检验（8）分位数和分位数图等（9）多元统计关于多元统计的函数有主成分分析和线性判别分析（10）统计工序管理可绘制通用的管理图和进行工序性能的研究（11）试验设计支持因子设计和 D 优化设计13.1.2. 数据的引用（1）正确定义输入的统计数据当我们要使用 Excel 工作表计算统计数据时，通常会将每一个统计的样本数值输入工作表的单元格当中，以便进一步地利用这些数据但在输入这些数据时，必须注意所输入的数据是否符合单元格中的数据类型规范如果输入的数据是属于文本类型，要在数据前加注“ ”单引号，即可将数据表示为文字类型的数据在我们将单元格中的数据定义为文本类型时
4、，我们会看到单元格中的数据会以默认的状态往左对齐如果输入的数据不是属于文本类型即数值型，就直接输入，此时单元格中数据就往右对齐例如在一般问卷统计数据当中，常以编号方式代表某项答案如图 13-175图 13-1（2）正确定义单元格的数据范围通常在 Excel 工作表中，想引用连续性单元格范围数据见下表 13-1表 13-1引用单元格 输入格式列 A 和行 10 到行 20 A10:A20行 15 和列 B 到列 E B15:E15行 5 5:5行 5 到行 10 5:10列 H H:H列 H 到列 J H:JA 列 10 行到 E 列 20 行 A10:E20 *是一矩阵范围13.2 常用的统计
5、函数常用的统计函数：（1）SUM功能：计算单元格区域中所有数值之和格式：SUM （number1 ，number2）76参数：number1 ，number2 为指定数值实例 1：若 A1:A4 区域内数值为： 80，70，60 和 90，求此四个数总和，则公式“= SUM (A1:A4)”返回和为 300（2）AVERAGE功能：返回其参数的算术平均值格式：AVERAGE（number1 ，number2）参数：同上例 2：如果 A1:A5 区域内有数值分别为 100，70，92，47 和 82，则公式“=AVERAGE(A1:A5)”返回平均值为 78.2（3）MAX功能：返回一组数值中最
6、大值格式：MAX（number1 ，number2）参数：同上例 3：现有数值 50，60，70，60，56，80 分别输入在单元格 B2:B7，则公式“= MAX(B2:B7)”返回最大值为 80（4）MIN功能：返回一组数值中最小值格式：MIN（number1 ，number2）参数：同上例 4、以上例为代表，则公式“=MIN(B2:B7)”返回最小值为 50（5）MEDIAN功能：返回一组数值的中位数格式：MEDIAN （number1，number2）参数：同上例 5：求单元格 A1:A7 的中位数， A1:A7 内数分别为 50， 60，70，56，80，90，则公式“= MEDIA
7、N (A1:A7)”返回中位数为 60（6）MODE功能：返回一组数值或数据区域中众数格式：MODE（number1 ，number2）参数：同上例 6：求上例中众数，则公式“=mode（A1:A7） ”则返回众数为 6077（7）GEOMEAN功能：返回一正数数组或数值区域的几何平均数格式：GEOMEAN （number1，number2）参数：同上例 7：求数值 4，5，8，7，11，4，3 的几何平均数即求 则公式7458143“GEOMEAN(4,5,8,7,11,4,3)”则返回几何平均数为 5.476987（8）VAR功能：计算给定样本的方差，(忽略样本中的逻辑值及文本) 计算公式
8、为 21()nix格式：VAR（number1，number2）参数：同上例 8：计算某米酒工厂所生产产品重量的方差，抽查 12 桶米酒重量如下：100，120，110，90，98，85，98，95，96，99，120，107，将上列数据输入单元格 B2:B13 内，用公式“=VAR(B2:B13)”则返回样本方差数为 117.9091（9）STDEV功能：计算给定样本标准差，(忽略样本中的逻辑值及文本) 计算公式为21ixn格式：STDEV（number1，number2）参数：同上例 9：如上例用公式“=STDEV(B2:B13)”则返回样本标准差为 10.8586（10）VARP功能：计
9、算给定样本的总体方差，(忽略样本中的逻辑值及文本) 其计算公式为 21()ixn格式：VARP （number1 ，number2）参数：同上例 10：计算例 8 米酒厂产品重量总体方差，用公式“=VARP(B2:B13)”则返回给定样本的总体方差为 108.0833（11）STDEVP功能：计算给定样本的总体标准差，(忽略样本中的逻辑值及文本) 公式为 21()ixn格式：STDEVP（number1，number2）78参数：同上例 11：计算例 8 米酒厂产品重量总体标准差，则公式“=STDEVP(B2:B13)”则返回给定样本的总体标准差为 10.3963（12）BINOMDIST功能
10、：返回二项分布的概率格式：BINOMDIST(number_s,trials,probability_s,cumulative)参数：number_s：实验成功次数，trials：独立实验总次数，probability_s：每一次独立实验成功的概率，cumulative：为逻辑值，累积分布函数用 TRUE；概率密度函数用 FALSE例 12：一次实验成功概率为 0.4，实验 15 次，成功次数 5 次，公式“=BINOMDIST(5,15,0.4,FALSE)”则返回其概率为 0.185938，即 P(x=5)=0.185938 公式“=BINOMDIST(5,15,0.4,TRUE)”则返回
11、累积分布函数 P（x5）=0.403216（13）NORMDIST功能：返回正态分布函数值格式：NORMDIST(x,mean,standard_dev,cumulative)参数：x：用于计算正态分布函数值的点，mean：正态分布均值，standard_dev：正态分布的标准差，cumulative：为逻辑值，累积分布函数用 TRUE；概率密度函数用 FALSE例 13：xN(40,1.5 2) 求 P（x42） ，公式“=NORMDIST(42,40,1.5,TRUE) ”则返回概率值为0.908789（14）POISSON功能：返回泊松分布的概率格式：POISSON（x,mean,cum
12、ulative ）参数：x：事件出现次数，mean：分布的期望值，cumulative：为逻辑值，累积分布函数用 TRUE 公式为 ；概率密度函数用 FALSE 公式为0!kke!xe例 14：公式“=POISSON(5,10,TRUE) ”表示求 ，返回值为 0.067086； 105()!kkepx公式“=POISSON(5,10,FALSE) ”表示求 返回值为 0.037833105()!ePx（15）EXPONDIST功能：返回指数分布概率值格式：EXPONDIST(x,Lambda,cumulative)79参数：x：指数分布函数计算的点，非负，Lambda ：指数函数的参数 ，c
13、umulative：为逻辑值，累积分布函数为 TRUE，公式为 ；概率密度函数用 FALSE，其公式为(,)1xFxe (,)xfe实例 15：公式“=EXPONDIST(0.2,10,TRUE)”返回 0.864665若公式“=EXPONDIST(0.2,10,FALSE)”返回 1.353353（16）CORREL功能：返回 array1 和 array2 两组数值的相关系数格式：CORREL （array1,array2）参数：array1：第一组数值单元格区域array2：第二组数值单元格区域例 16：检测学生物理与数学学习成绩之间是否关联、现抽查 5 个学生，物理成绩输入 A 列：A
14、1=90,A2=86,A3=65,A4=54,A5=36，数学成绩输入 B 列：B1=89,B2=83,B3=60,B4=50,B5=32，则公式“=CORREL(A1:A5,B1:B5)”返回 0.998876，可看出两科目成绩具有较高的相关性（17）PEARSON功能：求波尔生积矩法的相关系数 r，也是反映两个数据组之间线性关系程度格式：PEARSON（array1,array2 ）参数：array1：一组自变量array2：一组因变量例 17：上例公式：“=PEARSON(A1:A5,B1:B5) ”返回值为 0.998876（18）INTERCEPT功能：求线性回归拟合线方程的截距格式
15、：INTERCEPT(known_ys, known_xs)参数：known-ys 因变量 y 数据点known_xs 自变量 x 数据点例 18：如自变量 x 值输入 B 列：B1=59,B2=70,B3=80,B4=90,B5=89,B6=84,B7=92 因变量 y 输入 A列：A1=71,A2=83,A3=76,A4=49,A5=92,A6=88,A7=96，则公式“=INTERCEPT(A1:A7,B1:B7)”返回截距为 62.44657419、LINEST功能：使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组格式：LINEST(known_ys,known_xs,
16、const,stats)参数：known_ys：为因变量 y 值数据，即表达式 y=a+bxknown_xs：为自变量 x 值数据80const：为逻辑值，若 const 为 TRUE 或省略将参与正常计算；若为 FALSE 将 a 设为 O，则公式为 y=bxstats：为逻辑值，若为 TRUE 表返回附加回归统计值；若为 FALSE 或省略返回系数 b例 19：如上例，则公式“=LINEST(A1:A7,B1:B7)”则返回 0.208996427，即返回 y=a+bx 中b=0.20899642720、SLOPE功能：返回经过给定数据点的线性回归拟合线方程的斜率格式：SLOPE（know
17、n_ys, known_xs）参数：known_ys：因变量 y 数组或单元格区域known_xs：自变量 x 数组或单元格区域例 20：如上例则公式：“=SLOPE(A1:A7, B1:B7) ”则返回斜率为 0.20899642721、LOGEST功能：在回归分析中，计算符合观测数据组的指数回归拟合曲线，并返回描述该曲线的的数组即 m 与 b 值ybx格式：LOGEST(known_ys, known_xs, const, stats)参数：known_ys：为一组符合 的 y 值ymxknown_xs：为一组符合 的 x 值bconst：为逻辑值，若为 TRUE 或省略，可按常规计算 b
18、；若为 FALSE 则将 b 设置为 1，关系式则为 ymxstats：为逻辑值若为 TRUE 则返回额外回归统计数据返回数组为 ；112,;,nnybsesreFdfsregsid 若为 FALSE 或省略，则返回系数 m 和常数项 b例 21：某公司在经历 10 个月的不景气后将新产品投入市场，其后销售额成指数增长在投入后 6 个月中，每月销售量增加为 33100，47300，69000，102000，150000 和 220000 单位，假设将此值放入A1A 6 单元格中，x 值取 10 个月后为 11，12，13，14， 15，16 放入 B1B6 单元格内，则以公式：“=LOGEST
19、(A1:A6, B1:B6, TRUE,TRUE)”则返回结果为1.46327563， 495.30477；0.0026334, 0.03583428；0.99980862，0.01101631；20896.8011，4；2.53601883，0.000485443y=495.31.4633 x8122、TREND功能：返回一条线性回归拟合线的值，用最小二乘法找到最适合 known_ ys 和 known_ xs 数组的线性拟合公式，然后代入所指定的 new _xs 数组，返回估计的 y 值格式：TREND（known_ ys，known_ xs ，new _ xs，const）参数：know
20、n_ ys：为一组符合 y=mx+b 关系的已知 y 值known_ xs：为一组符合 y=mx+b 关系的已知 x 值，这是可省略自变量new_ xs：为一组需要 TREND 函数计算出对应 y 值的新 x 值const：为逻辑值，若为 TRUE 或省略，b 由计算取得；若为 FALSE 常数项 b 设置为 0，公式为 ymx例 22：3 月初某公司计划在 7 月购买一块土地，该公司收集了最近 12 个月里该地区相同面积土地的交易价格信息：12 个月交易价格为133890，135000，135790，137300，138130，139100，139900,141120，141890，1432
21、30，144000，145290将其放单元格区域 B2:B13 为 known_ ys，x 为 112，输入到 A2:A13 为 known_xs 要预测 3 月到 7 月的交易价格设 new_xs 为 13， 14，15，16，17，则公式 TREND（B2:B13，A2:A13 ，17） ，则得到计算结果为 150244，由此计算结果，该公司预测等到 7 月份购买土地其价格可能会涨到 150244 元以上函数在 Excel 中可直接调用，调用步骤如下：（1）单击需返回结果的单元格（2）单击工具栏的 “插入（I） ” 按钮下拉选 fx 函数（ F） 按下弹出 插入函数 对话框（3）单击 或选
22、择类别（C） 的下拉式按纽，选 统计 （4）在 选择函数（N） 下面选择对应函数名，单击 确定 按钮，弹出 函数参数 对话框就按计算机提示填入数值或数值所在单元格区域或逻辑值即可我们以调用中位数函数“MEDIAN”为例实例：某一年甲班学生有 60 个学生其学习成绩输入在单元格范围为“C3:C62”见表 13-2，求其中位数步骤如下：使用鼠标选取我们要放置甲班中位数的数据的单元格“H8” ，并选取“插入”“函数”命令，打开“插入函数”对话框，如图 13-2 所示在“插入函数”对话框的“选择函数”列表中，选取“MEDIAN”函数，并单击“确定”按钮表 13282图 13-2 单击“确定”按钮后，在所打开“函数参数”对话框的“Number”栏中输入甲班全部同学的成绩单元格范围“C3:C62 ”后，单击 “确定”按钮，您即会在 H8 单元格字段中看到所求得的甲班同学成绩的中位数为 85，如图 13-3 所示图 13-3二、课堂小结本节学习了 Excel 的主要统计功能与数据引用，以及常用的统计函数.
