经济数学（第三版） 教案全套 刘洪宇 实训1-10 利用excel数学函数处理学生成绩表-利用mathstudio求微分和积分

实训一利用Excel处理成绩表【实训目的】Excel表格的建立掌握Excel常用公式和函数【实训内容】1、单元格引用相对引用:在复制公式时,按一定的规律复制,相对于公式复制前的地址而言,横向或纵向移动一定的单元格。绝对引用:在行号和列标前加$,在复制公式时,整个地址不变。混合引用：在行号或列标前加$,在复制公式时,行号或列标不变。2、公式运算符：+，-，*，/，^，%，=，<>，<，<=，>，>=，&文本连结公式：以等号开头，包含一系列数值、函数、运算符、单元格引用使用：选择单元格，从编辑栏输入公式，√表示确认,×表示取消函数格式：函数名(参数序列)，参数大多以,分隔,也可无参数求和函数sum（）：返回某一单元格区域中所有数字之和；条件求和函数sumif（条件区域，条件，单元格区域）：根据指定条件对若干单元格求和；求平均值函数average（）：返回某一单元格区域中所有数字的平均值；条件求平均值函数averageif(条件区域，条件，单元格区域)：根据指定条件对若干单元格求平均数；（5）求最大值、最小值函数max()、min()（6）计数函数count(单元格列表）：计算区域内数值型数据的个数；countif(单元格列表，条件)：统计区域内满足条件的单元格的数目；（7）排名函数rank(排名的数字，排名的数字所在的区域，排序方式）排序方式有两种：若是忽略或者为0，则为降序；若是非零值则是升序。二、实训案例下表是20个同学的三门课程大学语文、高等数学B，大学英语的成绩，试求每门课程的最高分、最低分、平均分、男生人数、女生人数、男生各科平均分、女生各科平均分、男生英语总分、每个同学的总分以及排名。成绩表学号性别大学语文高等数学B大学英语1男8687782女8087733男8887854女8286845女8582856男9092867男7584808女8793869男80918910女77897911男86948312男83887613男91858014女76867415女74828016男87827117男78968118男80898219女81957620男879581Excel演算步骤1.每门课程的最高分、最低分和平均分第一步：在C23栏输入公式“=MAX(C3:C22)”,得大学语文的最高分为91，将鼠标置于C23栏右下角，按住“+”型符号往右拖（Excel的句柄填充功能)，直至E23栏，如图1-1所示。图1-1各科最高分图1-2各科最低分第二步：在C24栏输入公式“=Min(C3:C22)”,得大学语文的最低分为74，将鼠标置于C24栏右下角，按住“+”型符号往右拖（Excel的句柄填充功能)，直至E24栏，如图1-2所示。第三步：在C25栏输入公式“=AVERAGE(C3:C22)”,得大学语文的平均分为82.65，将鼠标置于C25栏右下角，按住“+”型符号往右拖，直至E25栏，如图1-3所示。图1-3各科平均分图1-4男生、女生人数2.男生人数、女生人数第三步：在C26栏输入公式“=COUNTIF(B3:B22,"男")”,得男生人数为12，在C27栏输入公式“=COUNTIF(B3:B22,"女")”,得女生人数为8，如图1-4所示。3.男生各科平均分、女生各科平均分、男生英语总分第四步：在C28栏输入公式“=AVERAGEIF($B$3:$B$22,"男",C3:C22)”,得男生大学语文平均分为84.25，将鼠标置于C28栏右下角，按住“+”型符号往右拖，直至E28栏，如图1-5所示。图1-5男生各科平均分、女生各科平均分图1-6男生英语总分第五步：同理，在C29栏输入公式“=AVERAGEIF($B$3:$B$22,"女",C3:C22)”,得女生大学语文平均分为80.25，将鼠标置于C29栏右下角，按住“+”型符号往右拖，直至E29栏，如图1-5所示。第六步：在C31栏输入公式“=SUMIF(B3:B22,"男",E3:E22)”,得男生英语总分为972，如图1-6所示。每个同学的总分以及排名第七步：在F3栏输入公式“=SUM(C3:E3)”,得学号1同学的总分为251，将鼠标置于F3栏右下角，按住“+”型符号往下拖，直至F22栏，如图1-7所示。第八步：在G3栏输入公式“=RANK(F3,$F$3:$F$22,0)”,得学号1同学的总分为12，将鼠标置于G3栏右下角，按住“+”型符号往下拖，直至G22栏，如图1-8所示图1-7每个学生的总分图1-8每个学生的排名【实训练习】实训1：完成Excel文档“实训一学生成绩表”中的任务。实训2：在Excel文档“实训一学生成绩表”sheet2中快速输入以下数据，并将此工作簿命名为“快速输入”数学实验报告实验序号：日期：_____年_____月_____日班级姓名学号实验名称问题背景简述：实验目的：实验原理与使用软件实验过程记录（含：基本步骤、程序的文件名及异常情况记录等）：实验结果报告及实验总结：教师评语：实训二利用EXCEL计算增长率【实训目的】1.掌握增长率和复合增长率的计算会利用EXCEL绘制柱形图、折线图和等相关图形。【实训内容】一、基本概念概念1：增长率是指事物增长的数量与原来的数量的百分比值。假设一项投资基期的本金为，末期的价值为，则该项投资的价值增长量为,于是,该项投资的增长率为.概念2：复合增长率是反映事物从某一年的数量开始，按照某个固定指数经过若干年增长后达到预期数量的百分比值。已知一项投资的基期本金为,投资期数为期,末期价值为,则由可得Excel中复合增长率r可表示为或二、实训案例1、根据国际货币基金组织WEO数据，试比较中国与美国、日本、俄罗斯、英国、法国、德国、印度、韩国等国2008年相对于1990、2000年经济增长率和年平均增长率，以及2005年到2008年之间每年的经济增长率和年均增长率。表2-1：中、美、日、俄、英、法、德、印、韩GDP数据一览表国家1990年2000年2005年2006年2007年2008年中国1866899215183218211924257306300670美国5803199515126384133989140777144414日本437022850298995017345507364751580675075666俄罗斯73056216254269035331114416680英国5703976512541132581398914461法国103371443017241180811895319480德国127492062522422232512428224958印度54914207591345855396628455380525253韩国1866909603236086524099087438975013010239377资料来源：国际货币基金组织WEO数据库，单位：亿本币（均为2008年流通货币），见国家统计局网站《统计数据——国际数据》Excel演算步骤1.9国18年、8年的GDP增长率计算与比较第一步:在H5栏输入公式“=(G5/B5-1)*100%”,得出中国2008年相对于1990年的GDP增长率为1510.6%,如图2-1所示.图2-1完成中国18年GDP增长率计算第二步:将鼠标置于H5栏右下角,按住“+”型符号往下拖(即使用Excel的句柄填充功能),直至H13栏,出现美国、日本、俄罗斯、英国、法国、德国、印度、韩国2008年相对于1990年的经济增长率,如图2-2所示.图2-2完成各国18年GDP增长率计算第三步:类似于18年GDP增长率计算方法,可求各国2008年相对于2000年的GDP增长率,如图2-3所示.图2-3完成各国8年GDP增长率计算第四步:选定各国8年和18年GDP增长率(包括9国国名),双击图表窗口出现图表导向,选择柱形图,顺序点击至“完成”,做适当修饰后(使图更清晰),即完成9国8年和18年GDP增长率的比较柱形图,如图2-4所示.可以看出,中国18年GDP增长率最高,俄罗斯8年GDP增长率最高,印度和韩国排在中、俄之后和西方发达国家之前.图2-4各国GDP增长率比较图2.2005-2008年的GDP年度增长率计算与比较第一步:在J5栏输入公式“=(E5/D5-1)*100%”,选定J5栏,按住“+”型符号,然后利用Excel的句柄填充功能,先从J5拖至L5,然后选定J5至L5栏,按住“+”型符号再拖至L13,得到各国2006-2008年每年相对于上一年的GDP增长率结果,如图2-5所示.图2-5完成各国GDP年度增长率计算第二步:选定中国、美国、日本、俄罗斯、英国、法国、德国、印度、韩国9国2006-2008年每年GDP增长率(包括9国国名),双击图表窗口出现图表导向,选择柱形图,顺序点击至“完成”,即完成9国2006-2008年每年GDP增长率的比较柱形图,如图2-6所示.可以看出,这三年俄罗斯每年GDP增长率最高,中国第二,印度第三.图2-6各国GDP年度增长率比较图3.各国18年、8年的GDP复合增长率计算与比较第一步:建立计算公式.第二步:计算各国18年GDP年均增长率.在M5栏输入公式“=POWER(G5/B5，1/18)-1”,计算出中国2008年相对于1990年18年的GDP复合增长率为16.7%,选定M5栏拖至M13栏,得到美国、日本、俄罗斯、英国、法国、德国、印度、韩国的1990-2008年的GDP复合增长率,如图2-7所示.图2-7完成各国18年GDP复合增长率计算第三步:仿第二步,计算8年GDP复合增长率,结果如图2-8所示.图2-8完成各国8年GDP复合增长率计算第四步:作图比较9国1990-2008年和2000-2008年的GDP复合增长率.如图2-9所示,从图上比较可以看出,1990年以来,中国经济增长最快,印度紧追其后;2000年以来,俄罗斯经济增长最快,中国紧追其后.但2000年以来,除法国、俄罗斯GDP增长加快之外,其他国家均有所放慢.图2-9各国8年、18年GDP复合增长率比较图二、高技术产业销售收入增长分析问题2表2-2给出了我国高技术产业2002-2006年的销售收入数据,试通过年度增长率和复合增长率分析比较各产业的增长情况.表2-2我国高技术产业2002-2006年的销售收入数据单位:亿元产业2002年2003年2004年2005年2006年医药制造业2279.982750.7330334019.834718.82航空航天器制造业499.9547.2498.4781.37798.88电子及通信设备制造业7658.679927.1413819.116646.2521068.86电子计算机及办公设备制造业3441.676305.979192.710722.1512634.18医疗设备及仪器仪表制造业734.04880.4813031752.182363.82合计14614.2620411.5227846.233921.7841584.56第一步:借助Excel软件，根据公式分别计算每一个产业2003-2006年每年相对于上一年的销售收入年度增长率，根据公式分别计算每一个产业2002-2006年的销售收入复合增长率，如表2-3所示.可以看出，电子计算机及办公设备制造业的销售收入复合增长率为0.3842，高于其他四个产业，该产业2003年销售收入增长率为0.8322，呈现出爆发式增长的势头，但是该产业的销售收入增长速度从2004年开始下降，2005年、2006年该产业的销售收入增长率均保持在0.17左右；航空航天器制造业2004年销售收入增长率为-0.0892，呈现出负增长，2003年、2006年该产业的销售收入增长率均小于0.1，而2005年该产业的销售收入出现大幅增长，增长率高达0.5678.表2-3我国高技术产业2003-2006年年度增长率和复合增长率产业年度增长率复合增长率2003年2004年2005年2006年医药制造业0.20650.10260.32540.17390.1994航空航天器制造业0.0946-0.08920.56780.02240.1243电子及通信设备制造业0.29620.39210.20460.26570.2879电子计算机及办公设备制造业0.83220.45780.16640.17830.3842医疗设备及仪器仪表制造业0.19950.47990.34470.34910.3396第二步:作出增长率趋势比较图,如图2-10所示.可以看出，电子计算机及办公设备制造业的销售收入增长率从2003年的0.8322急剧下降到2005年的0.1664，从2005年开始增长率趋于平稳；航空航天器制造业的销售收入增长率波动较大，而电子及通信设备制造业、医疗设备及仪器仪表制造业的销售收入增长率波动比较平稳.图2-10我国高技术产业2002-2006年年度增长率趋势比较三、中美两国GDP差距发展趋势分析根据表2-4提供的中国和美国1978-2010年GDP数据,分析两国之间GDP差距的发展趋势,并采用2010年相对于1978年的复合增长率讨论未来差距的发展趋势.表2-4中国和美国1978-2010年GDP数据单位:亿美元年份美国中国年份美国中国1978年229472164.6080471995年739777279.8107081979年256332612.6045641996年781698561.0538321980年278953034.4459931997年830439525.1477511981年312842868.9736641998年8747010194.745371982年325502812.1499751999年9268010833.184531983年353673017.5608282000年9817011983.887131984年393323105.6010762001年10128013248.182091985年422033069.7991152002年10470014538.202631986年446282975.7313052003年10961016409.664961987年473953239.8171982004年11686019315.971591988年510384041.590492005年12422022343.531021989年548444513.2272992006年133989.326801.438021990年580313902.9481042007542621991年599594091.9594212008年143690.843025.812611992年633774880.9825962009年141190.549963.812661993年665746132.2281222010年146241.859847.067671994年707225592.052261第一步:借助Excel软件,根据公式计算中国和美国在1978-2010期间的GDP复合增长率分别为11.1%、6%，说明两国的经济在这32年时间里整体上都呈现出增长.然后根据公式分别求得中国和美国1979-2010年GDP的年度增长率，如图2-11所示.图2-11美国、中国1979-2010年年度增长率第二步:计算中国和美国GDP差距走势,如图2-12所示，对应的趋势图如图2-13所示.图2-12中国和美国1978-2010年GDP及其差距数据图2-13中国和美国1978-2010年GDP及其差距走势可以看出，1978年美国的GDP是中国的10.6倍，在1978-1994年美国GDP每年都能保持0.05左右的增长速度，而中国在此期间GDP增长不稳定，经常出现负增长，所以中美两国GDP差距越来越大.1995年以后，虽然中国GDP呈现增长趋势，但由于美国本身的GDP基数大，1995-2006年中美两国GDP差距仍较大.2007-2010年美国的经济呈现出经济衰退的趋势，而中国GDP仍能保持较高的增长率，因而中美两国GDP差距从2007年开始缩小.第三步:根据Excel提供的趋势分析工具,可得中国和美国2010年以后GDP及其差距走势预测趋势图,如图2-14所示.可以看出,大约到2025年,中国的GDP会赶超美国.图2-14中国和美国2010年以后GDP及其差距趋势预测图【实训练习】表2-5是我国2009年至2018年10年间的国民总收入等6个指标的相关数据，请根据数据分析各项指标的变化情况,并用图表表示.表2-52009年至2018年国民总收入等6项指标数据指标2009年2010年2011年2012年2013年国民总收入(亿元)347934.9410354.1483392.8537329588141.2国内生产总值(亿元)348517.7412119.3487940.2538580592963.2第一产业增加值(亿元)33583.838430.844781.449084.553028.1第二产业增加值(亿元)160171.7191629.8227038.8244643.3261956.1第三产业增加值(亿元)154762.2182058.6216120244852.2277979.1人均国内生产总值(元)2618030808363023987443684指标2014年2015年2016年2017年2018年国民总收入(亿元)642097.6683390.5737074818461896915国内生产总值(亿元)641280.6685992.9740060.8820754.3900309第一产业增加值(亿元)55626.357774.660139.262099.564734第二产业增加值(亿元)277571.8282040.3296547.7332742.7366001第三产业增加值(亿元)308082.5346178383373.9425912.1469575人均国内生产总值(元)4700550028536805920164644任务1:绘制我国国民总收入等6个指标的直方图；任务2:计算我国2018年相对于2009年各指标的增长率;任务3:计算我国2015年到2018年之间每年的增长率;任务4:计算我国2018年相对于2009年各指标的复合增长率;任务5:比较我国2015年到2018年之间每年的增长率和复合增长率的差异;任务6:选择合适的图表表示上述计算结果;任务7:根据你们的数据分析总结国民总收入等6个指标的变化情况,撰写实训报告.数学实验报告实验序号：日期：_____年_____月_____日班级姓名学号实验名称问题背景简述：实验目的：实验原理与使用软件实验过程记录（含：基本步骤、程序的文件名及异常情况记录等）：实验结果报告及实验总结：教师评语：实训三利用EXCEL求解投入产出问题【实训目的】掌握利用EXCEL进行矩阵的线性运算，矩阵的乘法，逆矩阵以及矩阵的转置；掌握利用EXCEL求解线性方程组。【实训内容】1.mmult(array1,array2)函数：返回两个矩阵array1,array2的乘积，结果矩阵的行数与array1的行数相同，矩阵的列数与array2的列数相同。注意：array1的列数必须与array2的行数相同，而且两个数组中都只能包含数值。按Ctrl+Shift+Enter组合键得到两个矩阵的乘积。2.minverse函数：返回矩阵的逆距阵。语法:minverse(array)Array必需行数和列数相等的数值矩阵。按Ctrl+Shift+Enter组合键得到所求矩阵的逆矩阵。3.transpose(array)函数：返回矩阵的转置距阵。按Ctrl+Shift+Enter组合键得到矩阵的转置距阵.4.线性方程组的有关概念线性方程组就是指n元一次方程组,设含有n个未知数m个方程的线性方程组为(6.1)记,,，则方程组(6.1)可表示为.其中,称为方程组(6.1)的系数矩阵,B称为右端常数矩阵,X称为未知数矩阵.如果矩阵A可逆,则用左乘上式两端,得到方程组的解为.二、实训操作实训1设矩阵，，求（1）；（2）；（3）（4）;（5）;（6）;解决方案:第一步:在EXCEL中输入矩阵A,B，如图所示：第二步：在A9栏输入“=A3+E3”，然后句柄填充，可得A+B第三步：在E9栏输入“=A3-E3”，然后句柄填充，可得A-B第四步：在A15栏输入“=4*A3”，然后句柄填充，可得4A第五步：选中E15：G17单元格，在E15单元格中输入“=MMULT(A3:C5,E3:G5)”，同时按下CTRL+SHIFT+ENTER键，可得A*B第六步：选中A21：C23单元格，在A21单元格中输入“=MINVERSE(A3:C5)”，同时按下CTRL+SHIFT+ENTER键，可得A的逆矩阵第七步：选中A25：C27单元格，在A25单元格中输入“=TRANSPOSE(A3:C5)”，同时按下CTRL+SHIFT+ENTER键，可得A的转置矩阵实训2解线性方程组解决方案:第一步:在EXCEL中输入系数矩阵A和常数矩阵B，如图所示：第二步：选中A8：C10单元格，在A8单元格中输入“=MINVERSE(A2:C4)”，同时按下CTRL+SHIFT+ENTER键，可得系数矩阵A的逆矩阵第三步：选中B13：B15单元格，在B13单元格中输入“=MMULT(A8:C10,E2:E4)”，同时按下CTRL+SHIFT+ENTER键，可得。【实训练习】1、设矩阵，，求（1）；（2）；（3）（4）;（5）2、3、设，求4、利用逆矩阵解下列方程组：5、设A=11111-11-11,B=数学实验报告实验序号：日期：_____年月日班级姓名学号实验名称问题背景简述：实验目的：实验原理与使用软件实验过程记录（含：基本步骤、程序的文件名及异常情况记录等）：实验结果报告及实验总结：教师评语：实训四利用EXCEL求解投入产出问题【实训目的】掌握利用EXCEL求直接消耗系数；掌握利用EXCEL求解消耗平衡(投入产出)方程组。【实训内容】一、投入产出的平衡关系①从纵向看，中间投入+最初投入=总投入。②从横向看，中间使用+最终需求=总产出。③每一个部门的总投入等于该部门的总产出。概念1：计算每个部门总产出1元价值的产品时，将相应各部门向该部门的直接输出所占的比例称为直接消耗系数，直接消耗系数是常数。二、如果消耗平衡方程组可以表示为因为,所以(I－A)X=Y.如果矩阵I－A可逆,则在方程(I－A)X=Y两边同时左乘矩阵(I－A)-1,可得解的矩阵表示为X=(I－A)-1Y其中,(I－A)-1为矩阵I－A的逆矩阵,称为里昂惕夫逆矩阵.【实训操作】一、利用Excel求直接消耗系数矩阵典型问题1利用Excel求解本章第一节表6-1的直接消耗系数矩阵表6-1投入产出表单位:万亿元产出投入中间使用最终需求总产出工业农业服务业中间投入工业农业服务业1.80.612415610最初投入(增值)933总投入15610解决方案:第一步:在H4栏输入“=C4/C$8”,得出直接消耗系数a11,即单位价值工业部门产品直接消耗0.2单位的工业部门自身产品.第二步:在H5栏输入“=D4/$D$8”,得出直接消耗系数a12,即单位价值工业部门产品直接消耗0.1单位的农业部门产品.第三步:在H6栏输入“=E4/$E$8”,得出直接消耗系数a13,即单位价值工业部门产品直接消耗0.1单位的服务业部门产品，结果如图6-1所示.图6-1直接消耗系数矩阵A二、利用Excel解线性方程组典型问题2利用Excel求解投入产出方程组(6.1).0.2x1解决方案:第一步:在工作表的E2至G4区域建立一个单位矩阵I,在I2至I4区域依次输入33,8,16.第二步:计算I－A.在A6栏输入“=E2－B2”,利用拖曳的方法将A6栏公式复制到A6至C8的区域,结果如图6-2所示.第三步:计算(I－A)-1.选中E6至G8区域,输入公式“=MINVERSE(A6∶C8)”,按下【Ctrl】+【Shift】+【Enter】组合键,结果如图6-3所示.图6-2方程组6.2)的系数矩阵图6-3里昂惕夫逆矩阵(I-A)-1第四步:利用公式X=(I-A)-1Y求方程组(6.2)的解.选中I6至I8区域,输入公式“=MMULT(E6∶G8,I2∶I4)”,按下【Ctrl】+【Shift】+【Enter】组合键,得方程组的解为x1=50,x2=30,x3=40,如图6-4所示.图6-4线性方程组(6.2)的解三、煤电系统的投入产出模型典型问题3某地区的煤矿、电厂和铁路三个企业是一个相互关联的经济系统,三者之间的直接消耗系数如表6-4所示.假设三个企业除了用于满足系统内部需求外,还需要满足外部的订单需求,已知现阶段的外部需求分别为60万元、25万元和18万元.问:现阶段各企业的总产出为多少?因为未来一个阶段是需求旺季,估计外部需求将会分别增加15万元、5万元和7万元,各企业又该如何安排生产?表6-4直接消耗系数表单位:万元产出投入中间使用外部订单需求煤矿电厂铁路中间投入煤矿电厂铁路00.150.300.450.050.050.400.150.05602518解决方案:设x1,x2和x3分别表示3个企业现阶段的总产出,记，，则即利用Excel求解上述方程组,得，即三个企业现阶段的总产出分别为106.16万元、51.58万元和54.87万元.具体结果如图6-5所示.图6-5现阶段三个企业总产出如果外部需求分别增加15万元、5万元和7万元,记ΔX=Δx1Δx则相应地有A·ΔX+ΔY=ΔX或(I-A)·ΔX=ΔY利用Excel求解该方程组,得Δx1=27.10,Δx2=12.16,Δx3=16.57.所以在未来的一个阶段三个企业的总产出应分别增加27.10万元、12.16万元和16.57万元,即三个企业未来一个阶段的总产出分别为132.26万元、63.74万元和71.44万元.具体结果如图6-7所示.图6-6未来一个阶段三个企业产出增加量【实训练习】实训1设某厂生产甲、乙、丙3种产品，其中第一季度和第二季度的产量用矩阵表示，其单位成本和销售单价用矩阵表示,且甲乙丙单位成本销售单价请使用Excel的MMULT函数求两个季度的成本总额和销售总额.实训2已知，使用Excel的MINVERSE函数求矩阵A的逆矩阵.实训3已知矩阵方程，请使用Excel求解该矩阵方程.实训4表6-7是某经济体系中A、B两个部门的投入产出资料,如果最终需求变成对A为210百万元,对B为147百万元,那么这个经济体系中各部门的总产出是多少?表6-7投入产出表单位:百万元产出投入使用方最终需求总产出AB生产方AB120604040140100300200实训5对由M(制造)、L(劳动)和A(农业)三个部门组成的经济体系进行调查,它们的产品投入产出分析资料如表6-8所示.表6-8投入产出表单位:亿元产出投入买方最终需求总产出MLA卖方MLA12030667.5112.59201030392.5297.5155600450200当对M、L和A的最终需求变为550亿元、330亿元和67亿元时,列出相应的表格,建立方程组,然后求出M、L和A新的总产出是多少.数学实验报告实验序号：日期：_____年_____月_____日班级姓名学号实验名称问题背景简述：实验目的：实验原理与使用软件实验过程记录（含：基本步骤、程序的文件名及异常情况记录等）：实验结果报告及实验总结：教师评语：实训五利用EXCEL求解线性规划问题注意：如果在【数据】菜单中没有见到【规划求解】命令，则要单击【文件】【选项】【加载项】【转到】命令，在弹出的对话框中的列表框中，选定【规划求解加载项】选项前的复选框．【实训目的】掌握使用EXCEL自定义函数进行数据处理掌握线性规划问题模型建立掌握EXCEL求解线性规划问题【实训内容】一、线性规划问题的三要素．1.有一组决策变量；2.有一个线性目标函数;3.有一组线性约束条件。线性规划问题的数学模型可表示如下线性规划问题的求解有很多方法，也有很多工具，比如常用的Matlab、Lingo、Excel等.Excel具有强大的规划求解功能，可以解决最多有200个变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的线性规划与非线性规划问题，本节主要讲解如何使用Excel求解线性规划问题.使用Excel求解线性规划规划求解加载宏是一组命令构成的一个子程序，其功能是可以求出线性和非线性数学规划问题的最优解和最优值.使用规划求解加载宏求解数学规划的步骤：第一步：在Excel工作表中输入目标函数的系数、决策变量、约束条件的系数、约束条件左端的值和约束条件右端的值（每一个单元格输入一个数据）；第二步：选定一个单元格存储目标函数，用定义公式的方式在这个目标单元格内定义目标函数；第三步：选定与决策变量个数相同的单元格（称为可变单元格），用以存储决策变量；再选择与约束条件个数相同的单元格，用定义公式的方式在每一个单元格内计算出相应的约束函数（称为约束函数单元格）；第四步：点击规划求解按钮，打开规划求解参数设定对话框，添加约束条件，完成规划模型的设定.【实训案例】案例1求解线性规划问题：Excel演算步骤第一步：启动Excel，在工作表中的A1，A2，A3，A10，E3，F3单元格中分别输入文字“目标函数系数”，“决策变量”，“约束条件”，“目标函数值”，“约束条件左端的值”，“约束条件右端的值”；在B1，C1，D1单元格中输入目标函数的系数1，-2，1，在B4，C4，D4单元格中输入第一个约束条件的系数1，1，1；同理，在相应单元格中输入其他约束条件的系数与约束条件右端的值，如下图4-1所示：图4-1数据输入第二步：计算约束条件左端的值和目标函数值．因为约束条件左端的值等于约束条件的系数乘以相应的决策变量，所以在E4单元格中输入公式“=B4*B2+C4*C2+D4*D2”，在E5单元格中输入公式“=B5*B2+C5*C2+D5*D2”，依次类推在E9单元格中输入公式“=B9*B2+C9*C2+D9*D2”；目标函数的值等于目标函数系数乘以决策变量，从而在D10单元格中输入公式“=B1*B2+C1*C2+D1*D2”，如图4-2所示．图4-2计算约束条件左端的值和目标函数值事实上，在计算约束条件左端值时，只需要在E4单元格中输入公式“=B4*$B$2+C4*$C$2+D4*$D$2”，然后单击E4单元格，将鼠标至于E4单元格右下角，当光标变为小黑十字时拖曳至E9单元格即可．第三步：单击【工具】菜单中的【规划求解】命令，在弹出的规划求解对话框中输入各项参数．（1）设置目标单元格和可变单元格在“规划求解参数”对话框中选中“最大值”前的单选按钮，设置目标单元格为“$D$10”，可变单元格为“$B$2:$D$2”，如图4-3所示．图4-3“规划求解参数”对话框（2）添加约束条件单击【规划求解参数】对话框中的【添加】按钮，打开【添加约束】对话框，单击单元格引用位置文本框，然后选定工作表中的E4单元格，则在文本框中显示“$E$4”，选择“<=”约束条件；单击约束值文本框，然后选定工作表中的F4单元格，如图4-4所示．图4-4“添加约束”对话框图中所示约束条件表示，依此类推，把所有约束条件都添加到【规划求解参数】对话框的【约束】列表框中．第四步：在“选择求解方法中”点击“单纯线性规划”，单击【求解】按钮，弹出图4-5所示的【规划求解结果】对话框，选中【保存规划求解结果】单选按钮．图4-5“规划求解结果”对话框第五步：在【规划求解结果】对话框中，单击【确定】按钮，工作表中就显示出规划求解的结果，如图4-6所示．图4-6结果显示如果要生成运算结果报告，可在【规划求解】对话框中选择【报告】列表框中的【运算结果报告】．单击【确定】按钮，则产生如图4-7所示的运算结果报告表，在该表中对约束条件和结果作出了详细的说明．图4-7运算结果报告表从图4-6或图4-7可以很容易看出，当变量时，目标函数的最大值为．案例2某奶制品加工厂用牛奶生产两种奶制品，1桶牛奶可以在设备甲上用12小时加工成3公斤，或者在设备乙上用8小时加工成4公斤．根据市场需求，生产的全部能售出，且每公斤获利24元，每公斤获利16元．现在加工厂每天能得到50桶牛奶的供应，每天正式工人总的劳动时间为480小时，并且设备甲每天至多能加工100公斤，设备乙的加工能力没有限制．请为该厂制定一个生产计划，使得工厂每天获利最大．解决方案:1.模型建立第一步：根据实际问题，设置决策变量．设每天用桶牛奶生产，用桶牛奶生产.第二步：确定目标函数．设每天获利元．桶牛奶可生产3公斤，获利，桶牛奶可生产4公斤，获利，故.第三步：分析各种资源限制，列出约束条件．原料供应：生产的原料(牛奶)总量不超过每天的供应，即桶；劳动时间：生产的总加工时间不超过总的劳动时间，即；设备能力：的产量不得超过设备甲每天的加工能力，即；非负约束：均不能为负值，即.第四步：综合目标函数和各个约束条件，写出整个线性规划模型如下：2.Excel求解第一步：在工作表中的A14，A15，A16，E15，E16，A22单元格中分别输入“目标函数系数”，“决策变量”，“约束条件系数”，“约束条件左端的值”，“约束条件右端的值”,“目标函数值”；在B14，C14单元格中输入目标函数的系数72，64，在B16，C16单元格中输入第一个约束条件的系数1，1；同理，在相应单元格中输入其他约束条件的系数与约束条件右端的值，如下图4-8所示：图4-8数据输入第二步：计算约束条件左端的值和目标函数值．在E16单元格中输入公式“=B16*$B$15+C16*$C$15”，并使用句柄填充拖曳至E20单元格.在B22单元格中输入公式“=B14*B15+C14*C15”，如图4-9所示．图4-9计算约束条件左端的值和目标函数值第三步：单击【数据】菜单中的【规划求解】命令，在弹出的规划求解对话框中输入各项参数．设置目标单元格和可变单元格在“规划求解参数”对话框中选中“最大值”前的单选按钮，设置目标单元格为“$B$22”，可变单元格为“$B$15:$C$15”，如图4-10所示．图4-10“规划求解参数”对话框添加约束条件单击【添加】按钮，打开【添加约束】对话框，单击单元格引用位置文本框，然后选定工作表中的E16至E18单元格，则在文本框中显示“$E$16:$E$18”，选择“<=”约束条件；单击约束值文本框，然后选定工作表中的F16至F18单元格，如图4-11所示．图4-11“添加约束”对话框依此类推，把非负约束条件添加到约束列表框中．第四步：在【规划求解结果】对话框中，单击【确定】按钮，工作表中就显示出规划求解的结果，如图4-12所示．图4-12“规划求解结果”对话框从图4-12可以很容易看出，当变量时，目标函数的最大值为．【实训练习】实训1某单位一次性购买了10本图书，书名、数量、单价数据见表7-11：表7-11图书单价及数量表书名单价数量书名单价数量大学语文18.320政治经济学2813高等数学3420文学5.512化工与化学23.58自然地理197大学英语2015计算机3840写作1830英语写作13.524任务1：请利用EXCEL自定义函数计算出每本书的购买总价.任务2：请利用EXCEL自定义函数计算出购买平均价格、平均数量.实训2某工厂用A、B两种配件生产甲、乙两种产品，每生产1件甲产品使用4个A配件耗时1h，每生产1件乙产品使用4个B配件耗时2h.该厂每天最多可从配件厂获得16个A配件和12个B配件，按每天工作8h计算，若生产1件甲产品获利2万元，生产1件乙产品获利3万元，问工厂应如何安排生产，以使得总利润最大？任务1：建立使总利润最大的线性规划模型.任务2：使用EXCEL求解该线性规划模型.实训3某农场每天需要使用一种特殊饲料800千克，这种特殊饲料由玉米和大豆粉配制而成，营养要求是至少含有30%的蛋白质和至多5%的纤维，具体成份见表7-12.表7-12饲料每千克饲料中含有成份总量（千克）费用（元/千克）蛋白质纤维玉米0.090.020.3大豆粉0.60.060.9请问农场应该如何配制这种特殊饲料，使得每天的饲料成本最小？任务1：建立使总成本最小的线性规划模型.任务2：使用EXCEL求解该线性规划模型.数学实验报告实验序号：日期：_____年_____月_____日班级姓名学号实验名称实验目的：实验原理与使用软件实验过程记录（含：基本步骤、程序的文件名及异常情况记录等）：实验结果报告及实验总结：教师评语：实训六利用EXCEL进行统计分析注意：如果在【数据】菜单中没有见到【数据分析】命令，则要单击【文件】【选项】【加载项】【转到】命令，在弹出的对话框中的列表框中，选定【分析工具库】选项前的复选框．【实训目的】掌握利用EXCEL求基本统计量；掌握频数分布直方图和频率分布直方图的做法【实训内容】一、基本统计量的计算通过调查或观察，采集到样本以后，常用一些统计量描述这些数据的分布状态，并通过这种认识，对数据的总体特征进行总结和归纳。数据的分布状态常通过数据的进行描写。描述性统计分析，主要包括集中趋势和离散趋势分析，其主要统计量如下：（1）描述集中趋势的统计◆算术平均值：样本数据的总和除以样本数据的个数即是算术平均值。EXCEL调用格式：average(X)，其中X表示一组数据.◆中位数：首先将样本数据(假设有n个数)按升序或降序排列，如果n为奇数，则数列中间的数值为中位数；如果n为偶数，则中位数为其中两数值的均值。EXCEL调用格式：median(X)，其中X表示一组数据.◆众数：样本数据中出现频数(次数)最多的那个数称为众数。众数是一组数据分布的峰值，是一种位置的代表，当数据的分布具有明显的集中趋势时，尤其对于偏态分布，众数的代表性比均值好。EXCEL调用格式：mode(X)，其中X表示一组数据.（2）描述离散趋势的统计量平均指标是一个代表性数值，它反映总体各单位某一数量标志的一般水平，而把总体各单位之间的差异抽象化了。要全面反映一个总体的特征，还必须测定总体各单位之间差异程度。◆极差：样本数据中最大值与最小值的差值。作为样本观测数据离散程度大小的一个简单度量。EXCEL调用格式：max(X)-min(X)，其中X表示一组数据.◆方差:每个样本值与全体样本值的平均数之差的平方和除以数据个数减一.当数据分布比较分散（即数据在平均数附近波动较大）时，各个数据与平均数的差的平方和较大，方差就较大；当数据分布比较集中时，各个数据与平均数的差的平方和较小。因此方差越大，数据的波动越大；方差越小，数据的波动就越小。EXCEL调用格式：var(X)，其中X表示一组数据.◆标准差：样本方差的算术平方根叫做样本标准差。样本标准差越大，样本数据的波动就越大。EXCEL调用格式：stdev(X)，其中X表示一组数据.（3）

频数分布直方图和频率分布直方图直方图是最常用的图示法，又可以分为频数分布直方图和频率分布直方图两种。绘制频数分布和频率分布直方图一般分为以下七个步骤:第一步确定全部数据所在的范围，找出数据的最大值和最小值；第二步确定组距和组数(一般建议确定5~20组，当样本容量n较大时可分10~20组，样本容量较小时可分5~10组)；第三步数据分组；注意：统计落入各组的数据个数，若数据正好落在界线上，则规定该数据归到下一组。第四步求出各组的频数（落入各组的数据个数）第五步求出各组的频率(各组频数除以数据总个数)第六步列频数分布和频率分布表；第七步绘制频数分布直方图（纵坐标是频数/组距）和频率分布直方图（纵坐标是频率/组距）。二、实训操作实训1表6-1是1980年-1998年某市城镇居民年人均可支配收入的数据。表6-1人均可支配收入与人均消费支出数据(单位：元)年份城镇居民年人均可支配收入年份城镇居民年人均可支配收入1980526.921990884.211981532.721991903.661982566.811992984.091983591.1819931035.261984699.9619941200.91985744.0619951289.771986851.219961432.931987884.2119971538.971988847.2619981663.631989820.99试求城镇居民人均可支配收入的平均数、中位数、众数、极差、方差、标准差、峰度和偏度。操作步骤EXCEL求解步骤：第一步：打开待分析数据的EXCEL表，单击【数据】菜单下【数据分析】选项，进入“数据分析”对话框，鼠标双击“数据分析”中的【描述统计】选项，出现“描述统计”对话框，如图6-1所示。图6-1“描述统计”对话框第二步：在“输入区域”输入“$B$2:$B$20”，在“输出选项”中选择“输出区域”选项，并输入“$H$2”(不是唯一的，也可以是其它单元格地址)，同时选中“汇总统计”和“平均数置信度”复选框，如图6-2所示。图6-2“描述统计”参数设计第三步：设计完“描述统计”参数后，按【确定】按扭即可，如图6-3所示。图6-3“描述统计”计算结果描述统计工具可生成以下统计指标，按从上到下的顺序其中包括样本的平均值、标准误差、中位数、众数、样本标准差、样本方差、峰度值、偏度值、极差、最小值、最大值、样本总和、样本个数和一定显著水平下总体均值的置信区间。实训2、绘制员工年薪工资频率分布直方图某公司为了了解该公司市场部员工工资的情况，随机调查了50名市场部的员工的年薪工资，具体的数据如表6-2所示(单位：千元).表6-250名市场部的员工的年薪工资1459514811213212714313413613714016211817014415584102154142145127148165138134165123124124173113104141142138160157138131116178123141138114135151138157试列出这50个数据的频数直方图和频率直方图.解决方案：第一步：首先确定全部数据所在的范围，找出数据的最大值和最小值.在B7栏输入函数“=MAX(A1：J5)”,在B8栏输入函数“=MIN(A1：J5)”,得到最大值是178，最小值是84.再取两个特殊值与，使略小于最小值84，略大于178，不妨取，，这样所有的数据都落入区间内.第二步：确定组数和组距：一般建议确定5~20组，当样本容量较大时可分10~20组，样本容量较小时可分5~10组，本题有50个数据，我们作等距分组，分为5组，每组的组距为.第三步：数据分组：在D7-D11栏中分别输入数组[80,100],[100,120],[120,140],[140,160],[160,180].第四步：列出频数：统计落入各组限的数据个数，若数据正好落在界线上，则规定该数据归到下一组，如120就归到[120,140]组内.现对落在各小组内的数据进行统计，在F8栏中输入函数“=COUNTIF(A1:J5,＂<100＂)－COUNTIF(A1:J5,＂<80＂)”,得到组限[80,100]中的数据个数是2,依次类推,所得的频数分布表如图6-4.第五步：求频率,即各组频数除以数据总个数,.第六步：列出频率分布：在G8栏中输入函数“=F8/50”得到组限[80,100]中的数据频率为0.04,将鼠标置于G8栏右下角，按住“＋”型符号往下拖,直至G12栏，所得的频率分布表如图6-4所示.图6-4频数、频率分布表第七步：绘制频数分布直方图和频率分布直方图.选定E8:E12的“频数/组距”的数据，点击【插入】菜单下【柱形图】，然后【选择数据】修改【水平（分类）轴标签】，然后点击【插入】菜单下【文本框】添加竖直轴“频数/组距”，可得到频数分布条形图，类似可得到频率分布条形图如图6-5所示。在频数分布条形图中点击【设置数据系列格式】，把分类间距调到0%，并设置边框颜色，即可得到频数直方图，类似可得到频率分布直方图，如图6-6所示。图6-5频数分布直方图图6-6频率分布直方图【实训练习】一名射击运动员在在十米气步枪决赛中的成绩(环数)为10.6,10.6,9.3,10.7,10.4,10.4,10.8,9.2,10.4,10.0.试以这些数据作为一个样本，使用Excel计算平均值、众数、中位数、极差、方差和标准差.(10)2.某地举行了一次语文、数学、外语三科竞赛，表6-3是某校的竞赛成绩：运用所学的知识，将表格填充完整，并画出频数直方图和频率直方图.(40)表6-3三门学科竞赛总成绩统计表分数段频数频率280~300分0.1260~279分7240~259分10220~239分9200~219分8180~199分70~179分合计503.某班级50名同学的经济数学成绩如表6-4所示（：分）表6-4经济数学成绩表55767269667257867192828082100697364745868667755677675755365795067757573956669796470787267725888706173（1）试求该班经济数学成绩的平均数、中位数、众数、极差、标准差和偏度；(10)（2）画出频数直方图和频率直方图；(30)（3）及格率是多少（分数≥60分）?优秀率是多少（分数≥90分）？(10)实验报告实验序号：06日期：_____年_____月_____日班级姓名学号实验名称问题背景简述：实验目的：实验原理与使用软件实验过程记录（含：基本步骤、程序的文件名及异常情况记录等）：实验结果报告及实验总结：教师评语：实训七利用EXCEL进行统计分析注意：如果在【数据】菜单中没有见到【数据分析】命令，则要单击【文件】【选项】【加载项】【转到】命令，在弹出的对话框中的列表框中，选定【分析工具库】选项前的复选框．【实训目的】掌握利用EXCEL进行一元线性回归分析；掌握利用EXCEL进行简单的时间序列分析。【实训内容】一、线性回归分析步骤：1.确定自变量、因变量；2.绘制散点图或求相关系数（用CORREL函数或数据分析中的“相关系数”）3.应用数据分析工具进行回归分析或应用趋势线进行回归分析4.利用回归分析进行预测。设因变量为，其预测值为，则的预测区间为（置信度为95%）其中二、时间序列趋势分析1.简单平均法根据过去已有的t期观察值，通过简单平均来预测下一期数值的一种预测方法，称为简单平均法.设时间序列已有的期观察值为，则第期的预测值为,简单平均法计算简单，只适合对波动不大的客观现象使用.2.移动平均法通过对时间序列逐期递推移动求得平均数作为趋势值或预测值的一种预测方法，称为移动平均法(movingaverage)，简单移动平均是将最近的期数据加以平均，作为下一期的预测值.第期的简单移动平均预测值为3.指数平滑法指数平滑法(exponentialsmarthing)是对过去的观察值加权平均进行预测的一种方法，该方法使得第期的预测值等于第期的实际观察值与第期预测值的加权平均值.其预测模型为，为第期的实际观察值，为第期的预测值，为平滑系数，为阻尼系数.一般来说,如果数据波动较大,值应取大一些,可以增加近期数据对预测结果的影响；如果数据波动平稳,值应取小一些.【实训操作】实训1已有研究表明,一种股票的收益率同市场(market)的收益率相关.表7-1中给出了2004年6月的22个交易日中长江电力(代码600900)的收益率和整个市场的收益率数据,试根据数据得到长江电力收益率关于市场收益率的线性方程,并给出线性拟合图.表7-12004年6月的22个交易日中长江电力的收益率与市场的收益率数据日期market600900日期market600900200406010.0171490.018994200406160.0048210.0069220040602-0.0062-0.0121820040617-0.02063-0.0183320040603-0.01647-0.0067320040618-0.00931-0.02917200406040.00009-0.00113200406210.008833-0.001220040607-0.01474-0.01017200406220.0108410.02286420040608-0.01237-0.0114220040623-0.008960.00235320040609-0.023170.00230920040624-0.00772-0.0023520040610-0.00185-0.0080720040625-0.02208-0.02235200406110.0022380.00464620040628-0.016770.00601720040614-0.02503-0.01619200406290.0126020.021531200406150.0182780.01880120040630-0.00972-0.00703解决方案:根据表7-1所提供的数据,按照统计及回归分析的有关理论知识,建立长江电力收益率受市场收益率影响的线性回归方程.可通过利用Excel提供的数据分析工具或趋势线得到回归方程.1.应用数据分析工具进行回归分析第一步:新建工作表,输入表头“应用回归分析工具进行回归分析”,输入表7-1中的22个交易日的日期及收益率数据.第二步:单击【数据】→【数据分析】,在出现的【数据分析】对话框中选择“回归”,如图7-1所示,单击【确定】.图7-1【数据分析】对话框第三步:在出现的【回归】对话框中,单击“Y值输入区域”后的折叠按钮,选择C3∶C25单元格;单击“X值输入区域”后的折叠按钮,选择B3∶B25单元格.选中“标志”复选框、“新工作表组”单选框和“线性拟合图”复选框,如图7-2所示,点击【确定】.图7-2【回归】对话框第四步:得到回归分析结果的汇总输出(SUMMARYOUTPUT),如图7-3所示.图7-3回归分析结果汇总输出结果分析：结果可以分为四个部分第一部分是回归统计的结果，包括相关系数、决定系数、调整之后的相关系数、回归标准误差以及样本观测值个数。第二部分是方差分析的结果，包括可解释的自由度、离差、残差和它们总离差的以及由此计算出的F统计量和相应的显著水平。第三部分是回归方程的截距和斜率的估计值以及它们的估计标准误差、t统计量大小双边拖尾概率值、以及估计值的上下界。从图7-3的回归汇总输出可以看出,对应的回归方程为长江电力收益率=0.0024+0.7923×市场收益率..第四部分是样本散点图，其中蓝色的点是样本的真实散点图，红色的点是根据回归方程进行样本历史模拟的散点。如果觉得散点图不够清晰可以用鼠标拖动图形的边界达到控制图形大小的目的。第五步:得到回归分析结果中的线性拟合图(LineFitPlot),如图7-4所示.图7-4线性拟合图2.应用趋势线进行回归分析第一步：新建工作表，输入表头“应用散点图和趋势线进行回归分析”，输入表7-1中22个交易日的日期及收益率数据；第二步:选择B4∶C25单元格数据，单击【插入】→【散点图】.第三步:在得到的散点图中选中网格线，右键选择“删除”即可删除网格线.第三步:点击散点图，单击【布局】→【坐标轴标题】→【主要横坐标标题】→【坐标轴下方标题】，在出现的文本框中输入“市场收益率”.然后点击【坐标轴标题】→【主要纵坐标标题】→【竖排标题】，在出现的文本框中输入“长江电力收益率”.生成的散点图如图7-5所示.图7-5长江电力收益率同市场收益率散点图第四步:右击散点图中的蓝色散点,选择“添加趋势线”,在“趋势预测/回归分析类型”选项区域中单击“线性”选项图标.选中【显示公式】复选框,单击【关闭】按钮.最终回归分析结果如图7-5所示.图7-5回归分析函数输出图从图7-5中可以看出,长江电力收益率同市场收益率的回归方程为:y=0.7923x+0.0024.其中,x表示市场收益率,y表示长江电力收益率.实训2表7-2给出了我国1990-2004年人均国内生产总值(GDP)和居民消费价格指数的时间序列，要求用3期移动平均预测2005年的居民消费价格指数以及平滑系数为0.7预测2005年的居民消费价格指数).表7-2人均国内生产总值(GDP)和居民消费价格指数的时间序列年份人均GDP(元)居民消费价格指数(%)(上年=100)年份人均GDP(元)居民消费价格指数(%)(上年=100)19901634103.11998630899.219911879103.41999655198.619922287106.420007086100.419932939114.720017651100.719943923124.12002821499.219954854117.120039111101.219965576108.3200410561103.919976054102.8注：资料来源—国家统计局网站1.问题分析:为判断这两个数列的变化形态及随时间的变化趋势，下面给出了2个序列的图形.从图7-6及图7-7可以看出，人均GDP序列呈现一定的线性趋势，居民消费价格指数序列图则没有任何趋势，呈现出一定的随机波动.通过图形的观察和分析有助于作进一步的描述，并为选择预测模型提供基本依据.图7-6人均GDP序列图图7-7居民消费价格指数序列图2.解决方案:=1\*GB2⑴用移动平均法预测居民消费价格指数第一步：单击【数据】→【数据分析】，在出现的“数据分析”对话框中选择“移动平均”，单击【确定】按钮.这时弹出移动平均对话框，如图7-8.图7-8移动平均对话框第二步:在“输入区域”框中指定统计数据所在区域为B1∶B16选中“标志位于第一行”复选框,在“间隔”框内输入移动平均的项数为3,在“输出区域”框中指定输出数据所在区域为C2∶C16,选中“图表输出”复选框,如图7-8所示.第三步：单击【确定】，得计算结果及实际值与移动平均值的曲线图如图7-9所示.图7-9移动平均的计算结果及实际值与移动平均值的曲线图从图7-9可以看出，2005年居民消费价格指数的预测值为101.4333.=2\*GB2⑵用指数平滑法预测居民消费价格指数第一步：单击【数据】→【数据分析】，在出现的“数据分析”对话框中选择“指数平滑”，单击【确定】按钮.这时出现指数平滑对话框，如图7-10.图7-10指数平滑对话框第二步：在输入框中指定输入参数，如图7-10所示.第三步：单击【确定】，得一次指数平滑值，如图7-11所示图7-11指数平滑结果输出图从图7-11可以看出，2005年居民消费价格指数的预测值为102.9455=3\*GB2⑶利用线性趋势方程来预测人均GDP第一步：单击【数据】→【数据分析】，在出现的“数据分析”对话框中选择“回归”，单击【确定】按钮.这时出现回归对话框，如图7-12所示.图7-12回归工具对话框第二步：在输入及输出区域填入相应的值，如图7-12所示；得到的输出结果如表7-3、7-4和图7-13所示.表7-3人均GDP线性趋势预测结果年份t人均GDP(元)预测人均GDP(元)残差标准残差1990年116341442.39191.610.511991年218792042.32-163.32-0.431992年322872642.24-355.24-0.941993年429393242.17-303.17-0.801994年539233842.0980.910.211995年648544442.02411.981.091996年755765041.94534.061.411997年860545641.87412.131.091998年963086241.7966.210.181999年1065516841.72-290.72-0.772000年1170867441.64-355.64-0.942001年1276518041.57-390.57-1.032002年1382148641.49-427.49-1.132003年1491119241.42-130.42-0.35200434719.661.90表7-4人均GDP线性回归计算结果Coefficients标准误差tStatP-valueLower95%Upper95%下限95.0%上限95.0%Intercept842.47213.043.950.00382.221302.71382.221302.71t599.9323.4325.600.00549.31650.54549.31650.54图7-13人均GDP线性拟合图所以，线性趋势回归方程为：，将代入线性趋势回归方程可得2005年人均GDP的预测值，即（元）..【实训练习】1．一家电气销售公司的管理人员认为，每月的销售额是广告费用的函数，并想通过广告费用对月销售额做出估计。表7-4是近8个月的销售额与广告费用数据：表7-4近8个月的销售额与广告费用数据电视广告费用/万元报纸广告费用/万元月销售收入y/万元51.596229041.5952.52.59233.3953.52.3942.54.29432.594任务1：请利用Excel求月销售收入的众数、中位数、平均数.任务2：请利用Excel求电视广告费用的极差.任务3：请利用Excel求电视广告费用、报纸广告费用和月销售收入的方差、标准差.任务4：请利用3期移动平均法预测下个月的月销售收入为多少万元？任务5：采用指数平滑法，分别用阻尼系数α=0.3和α=0.5预测各月的月销售收入，分析预测误差，说明用哪一个平滑系数预测更合适？任务6：请利用Excel绘制绘制月销售收入关于电视广告费用的散点图任务7:请分别利用Excel数据分析工具箱中的相关系数功能和CORREL函数计算月销售收入与电视广告费用的相关系数.任务8:请利用Excel进行回归分析，建立月销售额关于电视广告费的回归方程，并预测电视广告费用为3.6万元的月销售收入。任务9:建立月销售额关于电视广告费用和报纸广告费用的回归方程。并预测电视广告费用和报纸广告费用分别为3.6万元和4.5万元时，估计月销售额。任务10：撰写实训1~实训9的实训报告.数学实验报告实验序号：07日期：_____年_____月_____日班级姓名学号实验名称问题背景简述：实验目的：实验原理与使用软件实验过程记录（含：基本步骤、程序的文件名及异常情况记录等）：实验结果报告及实验总结：教师评语：实训八使用Excel讨论概率计算问题【实训目的】熟悉超几何分布的概率计算；掌握使用Excel计算二项分布、正态分布的概率以及累积概率.【实训内容】1.超几何分布HYPGEOMDIST函数：给定样本容量、样本总体容量和样本总体中成功的次数，HYPGEOMDIST函数返回样本取得给定成功次数的概率.其语法为：HYPGEOMDIST(sample_s,number_sample,population_s,number_population)，其中sample_s为样本中成功的次数，number_sample为样本容量，population_s为样本总体中成功的次数，number_population为样本总体的容量.2.二项分布BINOMDIST函数：返回二项式分布的概率值.其语法为：BINOMDIST(number_s,trials,probability_s,cumulative)其中为number_s为试验成功的次数，trials为独立试验的次数，probability_s为每次试脸中成功的概率，cumulative取0或1..3.正态分布NORMDIST函数：返回正态分布的概率值.其语法为：NORMDIST(x，mean，standard_dev,cumulative）其中x为需要计算其分布的数值，mean为正态分布的算术平均值，standard_dev为正态分布的标准偏差，cumulative取0或1注意：cumulative取0返回概率密度函数，cumulative取1返回累积分布函数若，则X对应的概率问题一般可借助EXCEL的NORM.DIST函数求解，基本使用格式为：；；4.NORMINV函数：返回指定平均值和标准差的正态累积分布的反函数.其语法为:NORMINV（probability，mean，standard_dev）其中probability为正态分布的概率值，mean为正态分布的算术平均值，standard_dev为正态分布的标准偏差.【实训操作】一、彩票中奖概率问题典型问题1某地发行福利彩票，每张彩票的号码是7个数字的无序数组，开奖时，用一个摇奖机，里面装有分别写上01,02,…，35的35个小球.充分搅拌这些小球一分钟，从出口处掉出一个小球，记下小球上的数字.摇出的小球不放回摇奖机中，重复刚才的做法，一直到产生一个7个数字的无序数组，记作a，设有一、二、三等奖.规定：彩票号码与a完全一样时，得一等奖；彩票号码与a有6个数字一样时，得二等奖；有5个数字一样时，得三等奖.试问：买一张彩票，中一、二、三等奖的概率各是多少?1.问题分析根据题意，将问题转化为一个袋子中有35个彩球，其中红球7个，白球28个，每次随机的取出一只，第一次取到的球不放回袋中，第二次从剩余的球中再取一球，共取7次，求取到7球中全是红球、有6个红球和有5个红球的概率.经过转换，问题变为无放回的随机抽样(超几何分布)，根据其概率分布（详见本章第三节）即可计算出相应的概率值.2.解决方案利用Excel中的超几何分布函数(HYPGEOMDIST函数)可计算出相应参数下超几何分布的概率，具体求解步骤如下.第一步：新建一个工作表，输入表头“应用超几何分布函数HYPGEOMDIST求概率”.第二步：分别单击C2、E2、C3和E3单元格，输入已知参数:N=35，M=7，n=7，x=8.第三步：运用HYPGEOMDIST求7个球中全为红球的概率，在B5单元格输入“=HYPGEOMDIST(E3，C3，E2，C2)”，结果如图8-1所示.图8-1应用超几何分布求概率利用相同的原理可求得x=6及x=5的概率值.第一步：新建Excel工作表，输入“超几何分布函数概率分布图”.第二步：分别单击C2、E2和C3单元格，输入己知参数N=35，M=7，n=8.第三步：设定样本中中奖的号码个数x序列.在B6—B13单元格输入x为0，1，…，7的取值.第四步：求不同的x对应的概率.单击C6单元格，输入"=HYPGEOMDIST（B6，$C$3,$E$2，$C$2）”，再次单击C6单元格，将鼠标至于C6单元格右下角，当光标变为小黑十字时拖曳