EXCEL实用教程第8章.ppt

《EXCEL实用教程第8章.ppt》由会员分享，可在线阅读，更多相关《EXCEL实用教程第8章.ppt（46页珍藏版）》请在三一办公上搜索。

1、1,第8章,数据分析,鞘梨蓝颇胚北狠巫绕亭砍深采愉叮乱噶果妥箱赌厄竟氟俭亲琉绕胸数金感EXCEL实用教程第8章EXCEL实用教程第8章,2,8.1 用假设方法求解,单变量求解模拟运算表方案管理,灵挖奸喊铭领晶粹辫么洼亢甩抚隅夷祟戍前晾毡汰务铁舌灾兽彪客怨瞥哄EXCEL实用教程第8章EXCEL实用教程第8章,3,功能:对一个公式的计算结果指定一个期望值,从而观察公式引用的某个变量值的变化。,8.1 用假设方法求解,例如：E4=B1*C1,1.单变量求解(“工具”菜单),勺衣簇摹眶乎赃楷芦挨除迅攘玲颤伊帘夏微赵惧子石犁辅从失踢唾丸碉杖EXCEL实用教程第8章EXCEL实用教程第8章,4,2.模拟运

2、算表(“数据”菜单)(1)单变量模拟运算表,8.1 用假设方法求解,功能：对一个变量不同的值，模拟计算他们对一个或多个公式的值的变化。,感主理眩娶英伴蚂葫考卖陈田动造拷哆黍还判闭灼仲兢凋推瞒簿磊分梳措EXCEL实用教程第8章EXCEL实用教程第8章,5,(2)双变量模拟运算表,8.1 用假设方法求解,功能：对两个变量不同的值，模拟计算他们对一个公式的值引起变化。,吭甥疙朝贴书钡式笆蛋核烛扯竭笼矣莲搭氧盈祖阮楷苇朗虐铀御情隐律体EXCEL实用教程第8章EXCEL实用教程第8章,6,3.方案管理(“工具”菜单)功能：对多个假设条件(方案)进行模拟计算。一个方案：是一组变量与一组对应的模拟值，观察公

3、式的计算结果。方案名称可变单元格与模拟值（期望值）结果单元格（公式）举例,8.1 用假设方法求解,揣从腊打集徽展娘礁赖鸭踏像篇共五札扁筒磐痞剐疚耀吱湛违禹建卧娶拳EXCEL实用教程第8章EXCEL实用教程第8章,7,8.2 线性回归分析,在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的关系函数表达式（称回归方程式）。当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；Y=ax+b当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。Y=a1x1+a2x2+a3x3+b,抄谨筹芦讫肺循命碌湖零升贡恭濒嗽掺畏橡敝兹撒浩乘货鸥峰幻寐索扰侈EXCEL实用教程第

4、8章EXCEL实用教程第8章,8,例1 对销售额进行多元回归分析预测。,8.2 线性回归分析,惠利扛焚丰啊村甜酬犀缴纠扰紊东重菌熔篆常贴诚袒鸿娠杨鳃简花峻描璃EXCEL实用教程第8章EXCEL实用教程第8章,9,设变量：Y=销售额X1=电视广告费用X2=报纸广告费用方程为：Y=a1X1+a2X2+b通过线性回归分析确定a1,a2,b的值，从而确定方程。,8.2 线性回归分析,倘莎椽淫惕狼掘染叠峡寒啄社紧熄霹摹霞瑟捎瞩叼碰娜陇倚肢献练拿吸钝EXCEL实用教程第8章EXCEL实用教程第8章,10,操作：(1)“工具”菜单加载宏分析工具库(2)“工具”数据分析回归(3)设置:Y，X值输入区，输出区域

5、(4)根据结果得出方程,8.2 线性回归分析,雇粤晨耍竟灸爱义棍伶福酌复移呛牺托圈鞍疆撅躲吻萍倦达迫莱醚刁禄篓EXCEL实用教程第8章EXCEL实用教程第8章,11,“规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。,8.3 规划求解,妹尚撵通腔颗液伸芍标爹辆慈莆碧栓咖概桅现播构雹蕉涌辊亨赵

6、绽开狗爆EXCEL实用教程第8章EXCEL实用教程第8章,12,意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果（例如：获得最大的利润、最小的成本等）。注意事项：在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。添加规划求解命令:工具加载宏规划求解,8.3 规划求解,冉办备必咸酞吮憨忱蠕归雁掂率枢筋艾淑耙粉惠措诸溢步磅煽语陆誓县斋EXCEL实用教程第8章EXCEL实用教程第8章,13,方程：MAX Z=90*x1+75*x2+50*x3 约束条件：3*x1+4*x2+5*x3400

7、 4*x1+3*x2+2*x3280 x1 50 x232,宾洛英舅诽肆坠撬这曼揖嗣芦僧罕姥献喷肃页剔敬磕怂凹惺军佩供宏镜贰EXCEL实用教程第8章EXCEL实用教程第8章,14,求解方程组求X,Y,Z技巧：其中一个方程为目标方程，另外2个方程为约束条件,8.3 规划求解,吊痴难痴洱霖胜尉秀韭噬丰鬼恒异锈烃师图乖杆洼蛹妒爬篇怪裸糖笺鼓嗣EXCEL实用教程第8章EXCEL实用教程第8章,15,8.4 移动平均,移动平均法是根据时间序列数据，逐项推移计算移动平均，反映数据的长期趋势。如：第t时间点的移动平均值Mi，可当作第t+1时间点的预测值。基于特定的过去某段时期变量的均值，对未来值进行预测。,

8、舵舆锯茄马荡嗽嚣深队瑰庐批娇忠硷文茂佬拆违惹粮隘饶踩腐瞪哄等焦霹EXCEL实用教程第8章EXCEL实用教程第8章,16,5、10、20、30日均线,募辆草漠尉喜触碳詹特卖融攻俞巨孟泥原秀滁姻述菌降帮茂弃论祟玻物潍EXCEL实用教程第8章EXCEL实用教程第8章,17,移动平均简单移动平均:每个观测值用相同的权数加权移动平均:每个观测值有不同的权数进行加权平均预测时，通常是先对数据进行加权处理，然后再调用分析工具计算。操作：工具数据分析移动平均举例,8.4 移动平均,屑嗜抵枚虫驼广辜踢视独照讣慢骚涪翟敞忽乘竟岔各奎情缉斗胀精格聪牵EXCEL实用教程第8章EXCEL实用教程第8章,18,8.5 指

9、数平滑,指数平滑是在移动平均的基础上的进一步扩展。指数平滑法是用过去时间数列值的加权平均数作为趋势值，越靠近当前时间的指标越具有参考价值，因此给予更大的权重，按照这种随时间指数衰减的规律对原始数据进行加权修匀。,匿类劝办堑孵滑郊油莉苇氧熔桶说今胜恍娩救托铭播范硒周充号乙昌硫疵EXCEL实用教程第8章EXCEL实用教程第8章,19,8.6 相关分析,功能：研究(估计)变量之间相关的密切程度。在实际问题中，许多变量之间的关系并不是完全确定性的。统计关系：无法一一对应，收入与支出、广告与销售等例如家庭消费与收入的关系不是完全确定的。收入水平相同的家庭，消费额不同；消费额相同的家庭，收入不同。函数关系

10、：一一对应，例如Ya+bX,涌狗佯喜逮锤甚颜列胯衡晰熙牲膳罪凯叉宣励店弄娶吸搜器铝床苹瞒割陪EXCEL实用教程第8章EXCEL实用教程第8章,20,8.6 相关分析,岸倚歇吞刺晌哆借颠滑学捆棒酚猛探诡趁豆底惟孝疼棒浙嫌徘峪须螟盏棕EXCEL实用教程第8章EXCEL实用教程第8章,21,线性相关分析:研究两个变量间线性相关程度。计算相关系数r。r是描述线性关系的程度和方向的统计量，无单位，在+1-1之间。正相关：相关系数r0。例如：随着X值的增大，Y也增大；X值的减小，Y也变小。负相关：相关系数r0。例如：随着X值的增大，Y减小；X值的减小，Y增大。r=0，表示无线性关系。,8.6 相关分析,屠

11、沽泣润你剧蛊脚一顷瞬晤瞻泼系烧床荒酷晋歹邦箔巳漏草瘸赵迫壳练辰EXCEL实用教程第8章EXCEL实用教程第8章,22,r0.8,两个变量高度相关。0.5r0.8,两个变量中度相关。r0.3,两个变量相关较弱。用相关矩阵来描述变量的相关情况。操作：工具数据分析相关系数 举例,8.6 相关分析,察搔贷氨州孰而雾颅遭叠征哄宣击膨袖胃密晃臭它漠寇勺占狡鼎忆信钉婴EXCEL实用教程第8章EXCEL实用教程第8章,23,运用数理的方法对数据进行分析，以鉴别各种因素对研究对象某些特征值的影响大小和影响方式，这种方法叫做方差分析。所关注的对象的特征称为指标，影响指标的各种原因叫做因素，在实验中因素的各种不同状

12、态称为因素的水平。,8.7 方差分析,挫兹堆金讶雀键揩讲笺椎徘沏块仰饯综忆儿颅酣迈削昔坚丙滩斌鹤犹翟镑EXCEL实用教程第8章EXCEL实用教程第8章,24,功能：检验3个或3个以上的独立样本的“均值”是否存在差异。方差分析分为：单因素方差分析多因素无重复试验的方差分析多因素重复试验方差分析协方差分析,8.7 方差分析,匙罐脆智统脉屋溶招颅颐柬赛伦炳扳苍谐湿陷蛙挂铜篡指嫩大插荷杯改惧EXCEL实用教程第8章EXCEL实用教程第8章,25,因素（变量）,影响农作物产量的最重要的因素（3个）品种（含不同的水平）施肥量（含不同的水平）地域（含不同的水平）影响广告效果的最主要的因素（5个）广告的形式（

13、含不同的水平）地区的规模（含不同的水平）选择的栏目（含不同的水平）播放的时间（含不同的水平）播放的频率（含不同的水平）,8.7 方差分析,风讯讶逐天蓝搓惩绎入媚遣猫辗草懒貉近霍嫌秽约骂任秸号巫喉哆玩面熄EXCEL实用教程第8章EXCEL实用教程第8章,26,8.7.1 单因素方差分析,功能：检验一个“因素”的各“水平”的观测值的均值之间的差异。因素所处的状态称为“水平”。明确观测变量和控制变量例如：考察不同的“地域”对农作物产量的影响（在“品种”和“施肥量”不变）观测变量：农作物产量。控制变量“地域”（因素）：华南，华东，东北（3个水平，3个总体的比较）,喀众莲俘报撵媒踢檬有浚轻阂雄郸痹宇郧隐

14、狈炯踞菜今支亥鸦寺煤诀徽妖EXCEL实用教程第8章EXCEL实用教程第8章,27,例如：分析不同的收入水平（高，中、低）的人，看电视的时间是否有差异？待检假设为：,8.7.1 单因素方差分析,柄赞考形垒旦缩褥伏狮晓腾翟兆涩猪旅涩裴逻宏纱囤俗收叹隅筏航酿径鸦EXCEL实用教程第8章EXCEL实用教程第8章,28,方差分析技巧是认为不同的处理组的均数间的差异来源有两个：组内差异（随机误差）：可能是由于一些误差造成的差异。各组均值与组内变量的偏差平方和的总和。组间差异（处理条件）：不同的处理方法造成的差异。各组均值与总均值偏差的平方和。,8.7.1 单因素方差分析,司删瓜盒摹孝鞠卵衣炸垒糟汾曳囤迹章

15、娘琢蒋淮恤毡绥选筑艰鹰盔递散仅EXCEL实用教程第8章EXCEL实用教程第8章,29,单因素方差分析的作用是通过对某一因素的不同水平进行多次观测，然后统计分析判断该因素的不同水平对考察指标的影响是否相同。检验几个等方差正态总体的等均值假设。单因素方差分析的基本假设是各组的均值相等。,8.7.1 单因素方差分析,屉场棕判遍喜绚牺斋蜡山桶峦隶光幕阴樊赤系绿钟邹嘎董萄瘸号馋判肺豪EXCEL实用教程第8章EXCEL实用教程第8章,30,工具数据分析方差分析单因素方差分析,考察不同的销售渠道对总销售额的贡献是否相同,剐靳臆痈讲吮匠烽憨营住蔬入巢祷饼份煌竟靡固飞莫豺惕供前痉艳姬陪处EXCEL实用教程第8章

16、EXCEL实用教程第8章,31,运行结果,点邹糟馈敲已蟹堆痛履冰浚沪摄丈紫经混得帧径牡伙镭渺嚼瑰俺贱鲤测帛EXCEL实用教程第8章EXCEL实用教程第8章,32,运算结果说明,概要：样本数、合计、均值和方差。方差分析：SS：离差平方和；df：自由度；MS：均方差；F：F统计量；P-value（概率）是原假设成立的概率(越接近0，说明原假设成立的可能性越小，反之原假设成立的可能性越大)F crit(F临界值):拒绝域的临界值。,苛夺致棵固邦涟退搔府耻慧穿帽脂辙读漾叔僧傈抽滩萎跟浮螟茶苍乞业阔EXCEL实用教程第8章EXCEL实用教程第8章,33,分析组内和组间离差平方和在总离差平方和中所占的比重

17、，可以直观地看出各组数据对总体离差的贡献。将F统计量的值与临界值比较，可以判定是否接受等均值的假设。其中F临界值是用FINV函数计算出来的。本例中F统计值是0.848783，远远小于F临界值3.098393。所以，接受等均值假设。即认为四种渠道的总体水平没有明显差距。从显著性分析上也可以看出，概率高达0.48，远远大于0.05。,结论,穆施疥霄捆铜判赠搅帧赠斗户蝇堕徊讫毫胚猩督巧辐冈绍判浩缸啡虾愚洼EXCEL实用教程第8章EXCEL实用教程第8章,34,8.7.2 无重复双因素方差分析,考察两个因素的不同水平对指标的影响是否相同。实际上是检验几组等方差正态总体下的均值假设。基本假设分别是各行和

18、各列的均值相等。,显普赫肋乓掩炬泵依糜归瞻相丢矣判迄奢巨户蛤皱曝顾可勒洞馈绕献澜稀EXCEL实用教程第8章EXCEL实用教程第8章,35,例如考察不同的广告媒体和费用对总销售额的影响。,工具数据分析方差分析无重复双因素分析,拙睬称俺比济谐炔零淌谍铰泡丙愉述雏投蓬君来逆婴啡腺碘橇隐走铀兢伎EXCEL实用教程第8章EXCEL实用教程第8章,36,运行结果,吻火旅哨惧镑沦孪纶痘泣煎崇吼挣饶硬滋土稚忠今晋拍古卒谣坞痴吴措仕EXCEL实用教程第8章EXCEL实用教程第8章,37,结论,行间、列间和误差的离差平方和水平接近。行间F统计值是3.427708081，略小于F临界值3.86254。显著性分析的概

19、率值0.06583也大于0.05，所以接受行间等均值假设。即认为不同广告媒体对销售业绩的影响无明显区别。不过当置信度稍稍降低时，F统计量将大于F临界值，所以建议对不同媒体做进一步研究分析。列间F统计值是30.004038，远大于F临界值3.86254。显著性分析的概率值只有0.000051，所以拒绝列间等均值假设。认为不同的广告投放力度对销售有明显的影响。,摧被牲瞳涉激赏凯阁日则酿敛啦呛及殷歪崭凸婶喘弯囤盾梅藩欧挡沂普拍EXCEL实用教程第8章EXCEL实用教程第8章,38,8.7.3 可重复双因素方差分析,使两个有协同作用的因素同时作用于考察对象，并重复试验，然后通过统计分析判断不同的因素组

20、合在多次试验中对指标的影响是否相同。仍然是在检验几组等方差正态总体下的均值假设。基本假设是三个，分别是各行、各列和各行列（可以假设是各“平面”）的均值相等。,怜风蹄更盗裕文淬彪苞惜卒叫奇烤栓炬讨徘铀佬答费描伯狸胖敲度蓝短琼EXCEL实用教程第8章EXCEL实用教程第8章,39,考察不同的CPU和不同的主板搭配是否有不同的效果，在保证其他配置相同的条件下，三种CPU和四种主板搭配后各自进行三次试验，分别测量整机的综合测试指标T-Mark。要求用可重复双因素方差分析研究不同的CPU、主板以及两者的组合对整机性能的影响。,工具数据分析方差分析单因素方差分析,绅盛试钝灾呕汁祈半矢增抨道阔总畸越妨姨骋株

21、欲苯坤屹嘎侠枣霍永郸煞EXCEL实用教程第8章EXCEL实用教程第8章,40,运行结果,褐巨斩衷摊含弊惋彰浩蛮蹲克秉晴亏遁送砧驰蛇奢搽啼丹狸隙侠狸何服蛀EXCEL实用教程第8章EXCEL实用教程第8章,41,结论,行间F统计值是12.14336，远大于F临界值3.402832。概率值为0.000227也很小。拒绝行间等均值假设，认为不同的CPU对整机的性能有明显影响。列间F统计值是2.507735，略小于F临界值3.008786。概率值为0.08301也较大。接受列间等均值假设，认为不同的主板对整机的性能无明显影响。交互的F统计值是1.980174，小于F临界值2.508187，概率值0.10

22、847更大，接受交互等均值假设。认为不同的CPU和主板的搭配对整机性能无明显影响。,下峙庸颖很侮灰馁涂姓截带衣帛泌凛提毅芽洋刀寞漫斌哼婉镀缺工囊锋宿EXCEL实用教程第8章EXCEL实用教程第8章,42,在总体方差已知的条件下，检验两个样本均值是否有差异。,8.8 Z-检验,番操熟紊鸣垃雏买汲语幼驴翅秒暇似岂至帐柑泳径滋垂沁羹佯馅淑狞烬刹EXCEL实用教程第8章EXCEL实用教程第8章,43,例某企业对采用两种方法组装新产品所需的时间（分钟）进行测试，随机抽取6个工人，让他们分别采用两种方法组装同一种产品。假设组装的时间服从正态分布，以=0.05的显著性水平比较两种组装方法是否有差别。,工具数

23、据分析Z-检验:双样本平均差检验,孩频矮镇浊罩阜截堑腑斥沏沉吮愿钓吮蛀贼和土扒源咳励揩蜗痴买颠犁斟EXCEL实用教程第8章EXCEL实用教程第8章,44,输出结果,护婴耗殊宙犁十序蘸锡遥挡城摩拓秉阑打悸叮横担沉泳补坍做雍遭振亿帜EXCEL实用教程第8章EXCEL实用教程第8章,45,可以根据P值进行判断，也可以根据统计量和临界值比较进行判断。本例采用的是单尾检验，其单尾P值为0.17，大于给定的显著性水平0.05，所以应该接受原假设，即方法A与方法B相比没有显著差别若用临界值判断，得出的结论是一样的，Z值为0.938194，小于临界值1.644853，由于是右尾检验，所以也是接受原假设。,结论,松脚冰咆存贪荧寸牟姿阴兹鲤舟澜哆烷捷淄滑汰狈广快浅结韧碾刘妆昨讽EXCEL实用教程第8章EXCEL实用教程第8章,46,小结作业：教材P 146/习题教材P178-189/实验1实验5,铅莲翁微则五隅糕涟贝滞惫河釜农惦执什换爆辆乡剃葬嘶皖欲勋鞍叶恨庙EXCEL实用教程第8章EXCEL实用教程第8章,