用Excel作计算与数据分析02-04.doc

用Excel作计算与数据分析02-04准备数据集；计算描述统计量，如均值和方差等；作图，如直方图等；随机数发生器，Rand()的应用;相关系数、协方差；回归。有关学习网站推荐：/school/office/office_more.php3?type=Excel&begin_num=20Char02例2.1-纸牌游戏中的条件概率计算新建Excel表-选择要输出的单元格-键入“=(4/52)/(12/52)”-按回车0.333333例2.2-在班级里随机地选学生选定单元格-输入”=0.25/0.40” -按回车0.625使用概率和概率表格本部分内容的目的不是计算，而是分析和解决问题。所以不需用Excel来计算。例2.3-使用概率表格来计算决策树中的概率例2.4 制造业中的质量控制例2.5 - 一种新消费服务成功的概率例2.6 谋杀案审判中的DNA检验例2.7 一个灵活制造系统的可靠性例2.8 生产过程中的质量问题假设一个生产过程生产出的产品是优质产品的概率是0.92，是劣质产品的概率是0.08。假设此生产过程每次生产140单位的产品。用X表示每次生产的优质产品数。则X服从二项分布，样本容量n=140，发生概率p=0.92。注意X可以取0,1,2,3,140中的任何值，取到不同的值的概率也不同。Excel计算概率公式：P(X=x)= BINOMDIST(x,140,0.92,FALSE)于是计算例中X=130概率如下，方法一：选定单元格-输入“= BINOMDIST(130,140,0.92,FALSE)“-回车确认;0.120773381方法二：用函数工具图标。选定单元格-点击函数图标-在常用函数中选BINOMDIST-在Number_s输入130-Trails输入140-probability_s输入0.92-Cumulative 输入false-回车确认;例2.9 掷硬币10, 求正面出现6次的概率。正面出现次数H服从二项分布，样本容量n=10，发生概率p=0.5。Excel计算概率公式：P(H=6) =BINOMDIST(6,10,0.5,FALSE)方法一：选定单元格-输入“= BINOMDIST(6,10,0.5,FALSE)“-回车确认;0.205078125方法二：用函数工具图标。 表2.14 n=8, p=0.7的二项分布概率X概率00.0000710.0012220.0100030.0466840.1361450.2541260.2964870.1976580.05765Excel产生表2.14的操作过程：方法一：新建Excel表-在A1:A9写入0至8-在B1中写入公式“=BINOMDIST(A1,8,0.7,FALSE)”-回车-复制粘贴方法二：新建Excel表-在A1:A9写入0至8-选单元格B1-在常用函数中选BINOMDIST-在Number_s后面的图标-选择单元格A1-Trails输入8-probability_s输入0.7-Cumulative 输入false-回车确认;例2.10 办公室里的计算机假设办公室里有8台计算机。在午饭时间任何一台计算机使用的概率是0.70。用X表示午饭时间正在使用的计算机的个数。则X服从二项分布，样本容量n=8，成功概率p=0.70。Excel计算概率公式：P(X=x)= BINOMDIST(x,8,0.7,FALSE)例2.11 生产过程中的质量控制假设一个生产过程生产出的产品是优质产品的概率是0.83，是劣质产品的概率是0.17。假设此生产过程每次生产5单位的产品。用X表示每次生产出的劣质产品的单位数。正好生产出2个劣质产品的概率为P(X=2)：P(X=2)= BINOMDIST(2,5,0.17,FALSE)选定单元格-输入“= BINOMDIST(2,5,0.17,FALSE)-回车.0.165246每次至少生产出1单位劣质产品的概率为：P(X1)= 1-BINOMDIST(0,5,0.17,TRUE)选定单元格-输入“= 1-BINOMDIST(0,5,0.17,TRUE)-回车.0.606095936注意上面公式中TRUE表示返回累积概率值，FALSE表示返回等于某值的概率值。例2.12 Netway Computers公司服务中心的人员安排问题用Excel计算方法同前。P(X2)= 1-BINOMDIST(1,50,0.02,TRUE)0.264228606P(X3)= 1-BINOMDIST(2,50,0.02,TRUE)0.078427748P(X4)= 1-BINOMDIST(3,50,0.02,TRUE)0.017758081或计算“=CRITBINOM(50,0.02,0.95)“ ，计算结果为3。函数CRITBINOM(trails, probability_s, alpha)返回一个数值，该值为使累积二项分布的函数值大于等于临界值alpha的最小整数。例2.13 掷骰子-求期望11/6 21/6 31/6 41/651/661/6选定单元格A1-键入1-在第二个键入=1+A1，回车-复制粘贴公式选定存放均值的单元格-键入“=SUM(A1:A6*B1:B6) “按Ctrl+Shift+Enter3.5例2.14 General Avionics 636飞机的订单数-求期望表2.13 Avionics飞机订单数的概率分布Avionics飞机订单数xi概率pi420.05430.10440.15450.20460.25470.15480.10复制表2.13-新建Excel表-粘贴方法一：选定要计算均值的单元格-键入“=SUM(A2:A8* b2:b8)”-按Ctrl+Shift+Enter45.35方法二：先计算各单项乘积，后求和;例2.15 两个地区的销售情况-求均值。表2.16 东部地区销售情况销售额X（$million）概率p30.0540.2050.3560.3070.10表2.17 西部地区销售情况销售额X（$million）概率p30.1540.2050.2560.1570.1580.10 首先用Excel作柱状分布图： 复制表2.16到Excel-插入-图表-选柱状图类型1-点下一步-选系列产生中的列-在数据区域选Y变量数据列-选系列-在分类（X）轴标志下选X变量数据列-点完成。如果不点完成-还可以继续选“标题”-图表标题栏输入“柱状图”-分类（X）轴输入“销售额（$million）”-数值轴（Y）输入“概率”。作法同上例：“=SUM(A2:A6* B2:B6) “-按Ctrl+Shift+Enter5.2“=SUM(A2:A7* B2:B7) “-按Ctrl+Shift+Enter5.25例2.16 掷骰子-求方差首先计算期望（比如在B7中）-选择要存放方差的单元格-键入“=SUM(A1:A6-B7)2*B1:B6) “-按Ctrl+Shift+Enter2.916667例2.17 General Avionics 636飞机的订单数-求方差表2.13 Avionics飞机订单数的概率分布Avionics飞机订单数xi概率pi420.05430.10440.15450.20460.25470.15480.10首先如例2.14计算期望（比如在A10中 ）“=SUM(A2:A8*B2:B8)”-选择要计算方差的单元格，键入“=SUM(A2:A8-A10)2*B2:B8) “-按Ctrl+Shift+Enter2.6275例2.18 两个地区的销售情况-求方差表2.16 东部地区销售情况销售额X（$million）概率p30.0540.2050.3560.3070.10在单元格B8输入 “=SUM(A2:A6* B2:B6) “-按Ctrl+Shift+Enter ，这样求得的期望(5.2)存于B8中。在存放方差的单元格中输入“=SUM( (A2:A7-B8)2* B2:B7) “-按Ctrl+Shift+Enter1.06表2.17 西部地区销售情况销售额X（$million）概率p30.1540.2050.2560.1570.1580.10在单元格B8输入 “=SUM(A2:A7* B2:B7) “-按Ctrl+Shift+Enter ，这样求得的期望(5.25)存于B8中。在存放方差的单元格中输入“=SUM( (A2:A7-B8)2* B2:B7) “-按Ctrl+Shift+Enter2.3875例2.19 二项分布的均值和标准差公式。例2.20 Netway Computers公司服务中心的人员安排问题按公式直接求;在选定的单元格直接输入：=50*0.02=50*0.02*(1-0.02)=SQRT(50*0.02*(1-0.02)计算结果：10.980.989949例2.21 概率分布和决策树留作练习;例2.22 一名经理的报酬表2.22 工资X ($)概率p 95,0000.15100,0000.20103,0000.25107,0000.20109,0000.20用X表示Karen现在公司明年给她的工资。则可计算出E(X)，VAR(X)和X。而如果Karen加入竞争对手公司，则她的工资为Y=1.20X+12,000。然后可以计算出E(Y)，VAR(Y)和Y。直接将表2.22复制到Excel表中-选定要存放E(X)的单元格（设为C2）-键入“=SUM(A2:A6*B2:B6)”-按Ctrl+Shift+Enter103200选定要存放Var(X)的单元格（设为C3）-键入“=SUM(A2:A6-C2)2*B2:B6)”-按Ctrl+Shift+Enter21760000选定要存放X的单元格（设为C4）-键入“sqrt(C3)”-按回车4664.761516选定要存放E(Y)的单元格（设为D2）-键入“=1.20*C2+12000”-按回车135840选定要存放Var(Y)的单元格（设为D3）-键入“=1.22*C3”-按回车31334400选定要存放Y的单元格（设为D4）-键入“=sqrt(D3)”-按回车5597.714例2.23 股票市场的一个简单模型例2.24 共同基金（Mutual Funds）降低投资风险例2.25 生日问题例2.26 信息在股票市场上投资的作用zExercise 2.3（计算概率）zExercise 2.9（全概率公式，条件概率）zExercise 2.14（期望、方差）zExercise 2.24（相关性度量）zExercise 2.27（Excel，二项分布）Char03例3.1 汽车排气系统的寿命例3.2 钢板的宽例3.3 -汽车排气系统的寿命正态分布- Normal用Excel画正态密度函数图：新建Excel表-在A列键入-3，-2.8，3-在单元格B1输出公式“=EXP(-1*(A12/2)/SQRT(2*PI()“-复制粘贴公式（即用鼠标点单元格B1,下拉）选择数据区域-插入-图表-XY散点图-无数据点平滑线散点图-各种表格属性-完成分布函数图形新建Excel表-在A列键入-3，-2.8，3-在C列建立公式“=NORMDIST(A1,0,1,TRUE)“-复制粘贴公式选择数据区域-插入-图表-XY散点图-无数据点平滑线散点图-各种表格属性-完成例3.4 Valley纺织品公司月收益率的分布表3.1MonthRate ofReturn(%)MonthRate ofReturn(%)MonthRate ofReturn(%)MonthRate ofReturn(%)Jan-883.58 May-90-4.77 Sep-925.26 Jan-951.74 Feb-882.41 Jun-906.43 Oct-920.86 Feb-953.99 Mar-882.47 Jul-90-4.07 Nov-922.30 Mar-952.74 Apr-881.99 Aug-901.30 Dec-92-2.10 Apr-954.56 May-88-1.40 Sep-900.24 Jan-93-2.38 May-95-0.07 Jun-887.50 Oct-90-1.46 Feb-931.51 Jun-95-1.39 Jul-884.60 Nov-901.85 Mar-931.54 Jul-955.69 Aug-884.18 Dec-902.77 Apr-932.02 Aug-953.82 Sep-88-2.13 Jan-915.72 May-933.35 Sep-952.89 Oct-884.84 Feb-915.94 Jun-93-0.72 Oct-95-2.64 Nov-885.00 Mar-91-1.11 Jul-93-0.44 Nov-95-1.06 Dec-883.53 Apr-912.76 Aug-93-1.56 Dec-953.10 Jan-893.22 May-912.28 Sep-933.69 Jan-961.46 Feb-895.32 Jun-91-2.19 Oct-932.28 Feb-960.72 Mar-89-2.18 Jul-913.33 Nov-93-3.48 Mar-967.24 Apr-89-1.47 Aug-91-0.46 Dec-933.68 Apr-96-1.65 May-89-5.69 Sep-910.65 Jan-94-1.78 May-962.66 Jun-896.65 Oct-914.80 Mar-943.66 Jun-967.87 Jul-898.76 Nov-910.46 Mar-941.12 Jul-967.00 Aug-89-0.24 Dec-912.03 Apr-940.03 Aug-965.93 Sep-893.00 Jan-92-0.07 May-942.99 Sep-960.89 Oct-89-1.19 Feb-925.11 Jun-944.64 Oct-966.52 Nov-89-1.12 Mar-92-7.68 Jul-94-2.69 Nov-960.76 Dec-89-1.63 Apr-924.16 Aug-94-0.75 Dec-96-1.43 Jan-902.16 May-921.89 Sep-94-4.83 Jan-970.43 Feb-901.73 Jun-922.12 Oct-940.23 Feb-970.56 Mar-906.40 Jul-92-3.51 Nov-945.06 Apr-90-5.59 Aug-922.07 Dec-94-0.90 选定本章表格表3.1-复制-建立新Excel表-粘贴；将4列的Rate of Return合并到1列上（如B列）, 对该进行排序，得到最小、最大的Rate of Return值：最小值：-7.68最大值：8.76共有112个数值，于是大约可以分成11个区间组，根据最小、最大值，将-7.5到7.5等分为11个区间组，每个区间长度为1.5选定单元格D33-输入“-7.5“- 在单元格D34输入”=D33+1.5“-下拉开成如下区间组：-7.5-6-4.5-3-1.501.534.567.59插入函数-选FREQUENCY-在data-array中选择要计算频率的变量值-在Bins-array选择刚计算好的区间组-确定-下拉全选与区间组对应的空白区域-按F2-再按 Ctrl+Shift+Enter再将频率转换为百分比。插入-图表-选柱状图类型1-点下一步-选系列产生中的列-在数据区域选Y变量数据列-选系列-在分类（X）轴标志下选X变量数据列-点完成。例3.5 Simco食品公司在各销售区域的收入分布表3.2DistrictNet Revenues ($Million)DistrictNet Revenues ($Million)DistrictNet Revenues ($Million)DistrictNet Revenues ($Million)1-6.834815.779516.981423.55213.644915.949610.041439.48316.7250-3.659712.3014411.42414.235127.37983.821458.01516.655211.039917.3014613.83612.375314.381008.43147-9.82712.875415.1210114.1414812.8987.80554.9610213.651495.1096.415616.3910310.1015019.32108.065715.481048.0215131.181126.56587.5810522.681526.7712-3.235919.16106-4.4015312.521310.10607.2410718.531546.151416.17618.5710810.7815514.661514.08628.971097.6315616.891610.316314.7911019.591579.25179.106423.3611118.081586.20189.82657.44112-6.8215911.571913.00663.3811315.8616023.272013.0667-0.9411426.88161-0.592114.606819.2811516.5516226.31226.2469-5.1711613.4416316.47234.82703.061179.3516416.842411.317110.7911814.021657.18254.03726.1511916.6716611.63269.92731.671200.5816712.64276.62746.981219.1216812.532822.29753.70122-0.041699.2329-3.717612.1512313.1717019.39306.50779.541245.3517117.9331-4.187814.3412514.2817214.88328.21793.6812626.831731.143312.13803.8512731.851742.2234-5.9081-1.951281.4917511.843510.32829.0412917.4417612.093613.97838.2213012.16177-3.03374.458413.7113126.4217810.90383.468525.99132-4.621796.713919.53866.6413322.7318015.314020.978711.4713414.4318115.014115.51882.3013520.97182-0.954214.208917.721368028.001831.074311.41904.8113717.291845.104411.13917.2813810.1918513.614517.499212.9813916.744610.729321.361407.984715.879415.701419.80同上，先算频率后作图。留为作业。例3.6 一家快餐店在午餐时间的收入分布表3.3Range of RevenueNumber of DaysFraction of Days$1000 to $120000.000$1200 to $140030.014$1400 to $160050.024$1600 to $1800160.076$1800 to $2000210.100$2000 to $2200320.152$2200 to $2400490.233$2400 to $2600480.229$2600 to $2800210.100$2800 to $300080.038同上，百分比频率已经给出，直接作图，需要注意系列名称的选取。留为作业。例3.7 全职女工的收入例3.8 处理一个保单的时间(a) NORMDIST(120,150,30,TRUE)0.158655(b) 1-NORMDIST(25,75,25,TRUE)0.97725(c) NORMINV(0.95,150,30)199.3456例3.9 处理一个保单的时间首先要计算均值和标准差，之后算法同上(a) NORMDIST(180,sum(150,75),sqrt(302+252+2*30*25*0.37),TRUE)(b) NORMINV(0.95,sum(150,75),sqrt(302+252+2*30*25*0.37)(c) NORMDIST(60*8,3*sum(150,75),3*sqrt(302+252+2*30*25*0.37),TRUE)例3.10 中心极限定理举例1例3.11 中心极限定理举例2P137页图模拟：首先通过随机数生成器生成随机数，然后使用例3.1的方法作出频数统计柱状图.N=1时，随机数INT(RAND()*6)+1 或RANDBETWEEN(1,6)N=2时，随机数INT(RAND()*6)+ INT(RAND()*6)+2 或RANDBETWEEN(1,6)+ RANDBETWEEN(1,6)N=3时，随机数INT(RAND()*6)+ INT(RAND()*6)+ INT(RAND()*6)+3 或RANDBETWEEN(1,6)+ RANDBETWEEN(1,6)+ RANDBETWEEN(1,6)例3.12 正态分布作为二项分布的近似例3.13 生产过程的随机测试首先验证，计算再计算1-NORMDIST(10,6000*0.001,sqrt(6000*0.001*(1-0.001),TRUE)P138页图，Excel中二项分布的随机数生成器zExercise3.2 zExercise3.3 zExercise3.9 zExercise3.16 zExercise3.18 Char04例4.1NEXNet公司表4.1Respondent NumberOctober Phone BillRespondent NumberOctober Phone BillRespondent NumberOctober Phone Bill195.672579.324990.02282.692689.125061.06375.272763.125151.004145.2028145.625297.715155.202937.535395.44680.533097.065431.89780.813186.335582.35860.933269.835660.20986.673377.265792.281056.313464.9958120.8911151.273557.785935.091296.933661.826069.531365.603774.076149.851453.4338141.176242.331563.033948.576350.0916139.454076.776462.691758.514178.786558.691881.224262.2066127.821998.144380.786762.472079.754484.516879.252172.744593.386976.532275.9946139.237074.132380.354748.062449.424844.51选定本章表格表4.1-复制-建立新Excel表-粘贴；运用FREQUENCY函数，计算不同区间上数据的分布(需要指定各区间分隔值，返回的是“个数”)。选择表格-插入图表-柱状图-分类轴标志-系列名称-系列值-其他图标选项-完成方法一：样本均值函数=AVERAGE(B2:B25,D2:D25,F2:F23)79.40329公式后面数据区域会虽粘贴位置不同而不同样本方差函数=VAR(B2:B25,D2:D25,F2:F23)829.089公式后面数据区域会虽粘贴位置不同而不同方法二：直接用函数例4.2NEXNet公司续中位数函数MEDIAN=MEDIAN(B2:B25,D2:D25,F2:F23)76.65例4.3MBA学生工作经历的年数表4.3.学生工作年数John3Alice5Manuel5Sarah4Lee3Vivek2Tom5Elaine4Vien9样本均值函数=AVERAGE(B2:B10)4.444444444样本中位数函数=MEDIAN(B2:B10)4样本方差函数=VAR(B2:B10)4.027777778样本标准差函数=STDEV(B2:B10)2.00693243例4.4-Berbshire电力公司表4.4.usageHN2424142422180332286424755323461901737418430393622102907113677122153132892141878153014163284172695182870196321202947212009222320231885243557252803263933271901283071293837301806312153322801333192342137353494362950373282382748394007403797412980422390433274443785452497463018472051484398493053503487513797524052533638544170552088562360572170584002594399602197613731623796633076641807654395663370673363684119693217702510713570722126733207741958753896761964772133782774792225802865813630823021832096843481853711863525872225883332892841902911914090923313932776942314953798962772973644982461992165100样本均值函数=AVERAGE