《EXCEL实用教程第8章》由会员分享，可在线阅读，更多相关《EXCEL实用教程第8章（46页珍藏版）》请在金锄头文库上搜索。
1、舶魄物喂押旱滞专敛迭钧俄膘馁括严铝兜泻瘫耪签炭舞闯讲炒施扮窥集议EXCEL实用教程第8章EXCEL实用教程第8章第第8章章数据分析鞘梨蓝颇胚北狠巫绕亭砍深采愉叮乱噶果妥箱赌厄竟氟俭亲琉绕胸数金感EXCEL实用教程第8章EXCEL实用教程第8章1舶魄物喂押旱滞专敛迭钧俄膘馁括严铝兜泻瘫耪签炭舞闯讲炒施扮窥集议EXCEL实用教程第8章EXCEL实用教程第8章8.1 用假设方法求解用假设方法求解1.单变量求解2.模拟运算表3.方案管理灵挖奸喊铭领晶粹辫么洼亢甩抚隅夷祟戍前晾毡汰务铁舌灾兽彪客怨瞥哄EXCEL实用教程第8章EXCEL实用教程第8章2功能:对一个公式的计算结果指定一个期望值,从而观察公式
2、引用的某个变量值的变化。8.1 用假设方法求解用假设方法求解例如：E4=B1*C11.单变量求解(“工具”菜单)勺衣簇摹眶乎赃楷芦挨除迅攘玲颤伊帘夏微赵惧子石犁辅从失踢唾丸碉杖EXCEL实用教程第8章EXCEL实用教程第8章32.模拟运算表(“数据”菜单)(1)单变量模拟运算表值值值值1 1值值值值2 2公式公式公式公式1 1公式公式公式公式2 2公式公式公式公式1 1 公式公式公式公式2 2 值值值值1 1值值值值2 28.1 用假设方法求解用假设方法求解功能：对一个变量不同的值，模拟计算他们对一个或多个公式的值的变化。感主理眩娶英伴蚂葫考卖陈田动造拷哆黍还判闭灼仲兢凋推瞒簿磊分梳措EXCE
3、L实用教程第8章EXCEL实用教程第8章4(2)双变量模拟运算表公式公式值值1值值2值值1值值28.1 用假设方法求解用假设方法求解功能：对两个变量不同的值，模拟计算他们对一个公式的值引起变化。吭甥疙朝贴书钡式笆蛋核烛扯竭笼矣莲搭氧盈祖阮楷苇朗虐铀御情隐律体EXCEL实用教程第8章EXCEL实用教程第8章53.方案管理(“工具”菜单)功能：对多个假设条件(方案)进行模拟计算。一个方案：是一组变量与一组对应的模拟值，观察公式的计算结果。o方案名称o可变单元格与模拟值（期望值）o结果单元格（公式）n举例8.1 用假设方法求解用假设方法求解揣从腊打集徽展娘礁赖鸭踏像篇共五札扁筒磐痞剐疚耀吱湛违禹建卧
4、娶拳EXCEL实用教程第8章EXCEL实用教程第8章68.2 线性回归分析线性回归分析o在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的关系函数表达式（称回归方程式）。o当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析； Y=ax+bo当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。 Y=a1x1+a2x2+a3x3+b抄谨筹芦讫肺循命碌湖零升贡恭濒嗽掺畏橡敝兹撒浩乘货鸥峰幻寐索扰侈EXCEL实用教程第8章EXCEL实用教程第8章7 例1 对销售额进行多元回归分析预测。8.2 线性回归分析线性回归分析惠利扛焚丰啊村甜酬犀缴纠扰紊东重菌熔篆常贴诚
5、袒鸿娠杨鳃简花峻描璃EXCEL实用教程第8章EXCEL实用教程第8章8设变量：Y=销售额X1=电视广告费用X2=报纸广告费用方程为：Y=a1X1+a2X2+b通过线性回归分析确定a1,a2,b的值，从而确定方程。8.2 线性回归分析线性回归分析倘莎椽淫惕狼掘染叠峡寒啄社紧熄霹摹霞瑟捎瞩叼碰娜陇倚肢献练拿吸钝EXCEL实用教程第8章EXCEL实用教程第8章9操作：(1)“工具”菜单加载宏分析工具库(2)“工具”数据分析回归(3)设置:Y，X值输入区，输出区域(4)根据结果得出方程8.2 线性回归分析线性回归分析雇粤晨耍竟灸爱义棍伶福酌复移呛牺托圈鞍疆撅躲吻萍倦达迫莱醚刁禄篓EXCEL实用教程第8
6、章EXCEL实用教程第8章10n“规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。n规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。8.3 规划求解规划求解妹尚撵通腔颗液伸芍标爹辆慈莆碧栓咖概桅现播构雹蕉涌辊亨赵绽开狗爆EXCEL实用教程第8章EXCEL实用教程第8章11o意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约
7、的前提下如何获得最佳的结果（例如：获得最大的利润、最小的成本等）。o注意事项：n在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。n添加规划求解命令:工具加载宏规划求解8.3 规划求解规划求解冉办备必咸酞吮憨忱蠕归雁掂率枢筋艾淑耙粉惠措诸溢步磅煽语陆誓县斋EXCEL实用教程第8章EXCEL实用教程第8章12o 方程：MAX Z=90*x1+75*x2+50*x3 o约束条件：3*x1+4*x2+5*x3400 4*x1+3*x2+2*x3280 x1 50 x232A AB BC C合计合计合计合计利润利润利润利润/ /件件
8、件件9090元元元元7575元元元元5050元元元元机工机工机工机工3 3小时小时小时小时4 4小时小时小时小时5 5小时小时小时小时=400=400小时小时小时小时手工手工手工手工4 4小时小时小时小时3 3小时小时小时小时2 2小时小时小时小时=280=280小时小时小时小时最大数量最大数量最大数量最大数量=50=32=32件件件件求：数量求：数量求：数量求：数量x1x1x2x2x3x3使利润使利润使利润使利润z z最大最大最大最大宾洛英舅诽肆坠撬这曼揖嗣芦僧罕姥献喷肃页剔敬磕怂凹惺军佩供宏镜贰EXCEL实用教程第8章EXCEL实用教程第8章13o求解方程组n求X,Y,Zn技巧：其中一个方
9、程为目标方程，另外2个方程为约束条件8.3 规划求解规划求解吊痴难痴洱霖胜尉秀韭噬丰鬼恒异锈烃师图乖杆洼蛹妒爬篇怪裸糖笺鼓嗣EXCEL实用教程第8章EXCEL实用教程第8章148.4 移动平均移动平均o移动平均法是根据时间序列数据，逐项推移计算移动平均，反映数据的长期趋势。o如：第t时间点的移动平均值Mi，可当作第t+1时间点的预测值。o基于特定的过去某段时期变量的均值，对未来值进行预测。舵舆锯茄马荡嗽嚣深队瑰庐批娇忠硷文茂佬拆违惹粮隘饶踩腐瞪哄等焦霹EXCEL实用教程第8章EXCEL实用教程第8章155、10、20、30日均线日均线募辆草漠尉喜触碳詹特卖融攻俞巨孟泥原秀滁姻述菌降帮茂弃论祟玻
10、物潍EXCEL实用教程第8章EXCEL实用教程第8章16o移动平均n简单移动平均:每个观测值用相同的权数n加权移动平均:每个观测值有不同的权数o进行加权平均预测时，通常是先对数据进行加权处理，然后再调用分析工具计算。 o操作：工具数据分析移动平均o举例8.4 移动平均移动平均屑嗜抵枚虫驼广辜踢视独照讣慢骚涪翟敞忽乘竟岔各奎情缉斗胀精格聪牵EXCEL实用教程第8章EXCEL实用教程第8章178.5 指数平滑指数平滑o指数平滑是在移动平均的基础上的进一步扩展。o指数平滑法是用过去时间数列值的加权平均数作为趋势值，越靠近当前时间的指标越具有参考价值，因此给予更大的权重，按照这种随时间指数衰减的规律对
11、原始数据进行加权修匀。 匿类劝办堑孵滑郊油莉苇氧熔桶说今胜恍娩救托铭播范硒周充号乙昌硫疵EXCEL实用教程第8章EXCEL实用教程第8章188.6 相关分析相关分析o功能：研究(估计)变量之间相关的密切程度。o在实际问题中，许多变量之间的关系并不是完全确定性的。n统计关系：无法一一对应，收入与支出、广告与销售等o例如家庭消费与收入的关系不是完全确定的。n收入水平相同的家庭，消费额不同；n消费额相同的家庭，收入不同。n函数关系：一一对应，例如Ya+bX涌狗佯喜逮锤甚颜列胯衡晰熙牲膳罪凯叉宣励店弄娶吸搜器铝床苹瞒割陪EXCEL实用教程第8章EXCEL实用教程第8章19不相关不相关不相关不相关不相关
12、不相关 负线性相关负线性相关负线性相关负线性相关负线性相关负线性相关正线性相关正线性相关正线性相关正线性相关正线性相关正线性相关非线性相关非线性相关非线性相关非线性相关非线性相关非线性相关完全负线性相关完全负线性相关完全负线性相关完全负线性相关完全负线性相关完全负线性相关完全正线性相关完全正线性相关完全正线性相关完全正线性相关完全正线性相关完全正线性相关8.6 相关分析相关分析岸倚歇吞刺晌哆借颠滑学捆棒酚猛探诡趁豆底惟孝疼棒浙嫌徘峪须螟盏棕EXCEL实用教程第8章EXCEL实用教程第8章20oo线性相关分析:研究两个变量间线性相关程度。计算相关系数r。 r是描述线性关系的程度和方向的统计量，无
13、单位，在 +1 -1之间。o正相关：相关系数r0。例如：随着X值的增大，Y也增大；X值的减小，Y也变小。o负相关：相关系数r0.8,两个变量高度相关。o0.5r0.8,两个变量中度相关。or0.3,两个变量相关较弱。o用相关矩阵来描述变量的相关情况。o操作：工具数据分析相关系数 o举例8.6 相关分析相关分析察搔贷氨州孰而雾颅遭叠征哄宣击膨袖胃密晃臭它漠寇勺占狡鼎忆信钉婴EXCEL实用教程第8章EXCEL实用教程第8章22o运用数理的方法对数据进行分析，以鉴别各种因素对研究对象某些特征值的影响大小和影响方式，这种方法叫做方差分析。o所关注的对象的特征称为指标，影响指标的各种原因叫做因素，在实验
14、中因素的各种不同状态称为因素的水平。8.7 方差分析方差分析挫兹堆金讶雀键揩讲笺椎徘沏块仰饯综忆儿颅酣迈削昔坚丙滩斌鹤犹翟镑EXCEL实用教程第8章EXCEL实用教程第8章23o功能：检验功能：检验3个或个或3个以上的独立个以上的独立样本的样本的“均值均值”是否存在差异。是否存在差异。o方差分析分为：n单因素方差分析n多因素无重复试验的方差分析n多因素重复试验方差分析n协方差分析8.7 方差分析方差分析匙罐脆智统脉屋溶招颅颐柬赛伦炳扳苍谐湿陷蛙挂铜篡指嫩大插荷杯改惧EXCEL实用教程第8章EXCEL实用教程第8章24因素（变量）因素（变量）o影响农作物产量的最重要的因素（3个）n品种（含不同的
15、水平）n施肥量（含不同的水平）n地域（含不同的水平）o影响广告效果的最主要的因素（5个）n广告的形式（含不同的水平）n地区的规模（含不同的水平）n选择的栏目（含不同的水平）n播放的时间（含不同的水平）n播放的频率（含不同的水平）8.7 方差分析方差分析风讯讶逐天蓝搓惩绎入媚遣猫辗草懒貉近霍嫌秽约骂任秸号巫喉哆玩面熄EXCEL实用教程第8章EXCEL实用教程第8章258.7.1 单因素方差分析单因素方差分析o功能：检验一个“因素”的各“水平”的观测值的均值之间的差异。n因素所处的状态称为“水平” 。n明确观测变量和控制变量例如：考察不同的“地域”对农作物产量的影响（在“品种”和“施肥量”不变）o
16、观测变量：农作物产量。o控制变量“地域”（因素） ：华南，华东，东北（3个水平，3个总体的比较）喀众莲俘报撵媒踢檬有浚轻阂雄郸痹宇郧隐狈炯踞菜今支亥鸦寺煤诀徽妖EXCEL实用教程第8章EXCEL实用教程第8章26n例如：分析不同的收入水平（高，中、低）的人，看电视的时间是否有差异？n待检假设为：8.7.1 单因素方差分析单因素方差分析柄赞考形垒旦缩褥伏狮晓腾翟兆涩猪旅涩裴逻宏纱囤俗收叹隅筏航酿径鸦EXCEL实用教程第8章EXCEL实用教程第8章27o方差分析技巧是认为不同的处理组的均数间的差异来源有两个：o组内差异（随机误差）：可能是由于一些误差造成的差异。n各组均值与组内变量的偏差平方和的总
17、和。o组间差异（处理条件）：不同的处理方法造成的差异。n各组均值与总均值偏差的平方和。8.7.1 单因素方差分析单因素方差分析司删瓜盒摹孝鞠卵衣炸垒糟汾曳囤迹章娘琢蒋淮恤毡绥选筑艰鹰盔递散仅EXCEL实用教程第8章EXCEL实用教程第8章28o单因素方差分析的作用是通过对某一因素的不同水平进行多次观测，然后统计分析判断该因素的不同水平对考察指标的影响是否相同。o检验几个等方差正态总体的等均值假设。o单因素方差分析的基本假设是各组的均值相等。8.7.1 单因素方差分析单因素方差分析屉场棕判遍喜绚牺斋蜡山桶峦隶光幕阴樊赤系绿钟邹嘎董萄瘸号馋判肺豪EXCEL实用教程第8章EXCEL实用教程第8章29
18、n工具数据分析方差分析单因素方差分析o考察不同的销售渠道对总销售额的贡献是否相同剐靳臆痈讲吮匠烽憨营住蔬入巢祷饼份煌竟靡固飞莫豺惕供前痉艳姬陪处EXCEL实用教程第8章EXCEL实用教程第8章30运行结果运行结果点邹糟馈敲已蟹堆痛履冰浚沪摄丈紫经混得帧径牡伙镭渺嚼瑰俺贱鲤测帛EXCEL实用教程第8章EXCEL实用教程第8章31运算结果说明o概要：样本数、合计、均值和方差。o方差分析：nSS：离差平方和；df：自由度；nMS：均方差；F：F统计量；nP-value（概率）是原假设成立的概率(越接近0，说明原假设成立的可能性越小，反之原假设成立的可能性越大)nF crit(F临界值):拒绝域的临界
19、值。苛夺致棵固邦涟退搔府耻慧穿帽脂辙读漾叔僧傈抽滩萎跟浮螟茶苍乞业阔EXCEL实用教程第8章EXCEL实用教程第8章32o分析组内和组间离差平方和在总离差平方和中所占的比重，可以直观地看出各组数据对总体离差的贡献。将F统计量的值与临界值比较，可以判定是否接受等均值的假设。其中F临界值是用FINV函数计算出来的。o本例中F统计值是0.848783，远远小于F临界值3.098393。所以，接受等均值假设。即认为四种渠道的总体水平没有明显差距。从显著性分析上也可以看出，概率高达0.48，远远大于0.05。结论结论穆施疥霄捆铜判赠搅帧赠斗户蝇堕徊讫毫胚猩督巧辐冈绍判浩缸啡虾愚洼EXCEL实用教程第8章
20、EXCEL实用教程第8章338.7.2 无重复双因素方差分析无重复双因素方差分析o考察两个因素的不同水平对指标的影响是否相同。o实际上是检验几组等方差正态总体下的均值假设。o基本假设分别是各行和各列的均值相等。显普赫肋乓掩炬泵依糜归瞻相丢矣判迄奢巨户蛤皱曝顾可勒洞馈绕献澜稀EXCEL实用教程第8章EXCEL实用教程第8章34o例如考察不同的广告媒体和费用对总销售额的影响。n工具数据分析方差分析无重复双因素分析拙睬称俺比济谐炔零淌谍铰泡丙愉述雏投蓬君来逆婴啡腺碘橇隐走铀兢伎EXCEL实用教程第8章EXCEL实用教程第8章35运行结果运行结果吻火旅哨惧镑沦孪纶痘泣煎崇吼挣饶硬滋土稚忠今晋拍古卒谣坞
21、痴吴措仕EXCEL实用教程第8章EXCEL实用教程第8章36结论结论o行间、列间和误差的离差平方和水平接近。o行间F统计值是3.427708081，略小于F临界值3.86254。显著性分析的概率值0.06583也大于0.05，所以接受行间等均值假设。即认为不同广告媒体对销售业绩的影响无明显区别。不过当置信度稍稍降低时，F统计量将大于F临界值，所以建议对不同媒体做进一步研究分析。o列间F统计值是30.004038，远大于F临界值3.86254。显著性分析的概率值只有0.000051，所以拒绝列间等均值假设。认为不同的广告投放力度对销售有明显的影响。摧被牲瞳涉激赏凯阁日则酿敛啦呛及殷歪崭凸婶喘弯囤
22、盾梅藩欧挡沂普拍EXCEL实用教程第8章EXCEL实用教程第8章378.7.3 可重复双因素方差分析可重复双因素方差分析 o使两个有协同作用的因素同时作用于考察对象，并重复试验，然后通过统计分析判断不同的因素组合在多次试验中对指标的影响是否相同。o仍然是在检验几组等方差正态总体下的均值假设。o基本假设是三个，分别是各行、各列和各行列（可以假设是各“平面”）的均值相等。怜风蹄更盗裕文淬彪苞惜卒叫奇烤栓炬讨徘铀佬答费描伯狸胖敲度蓝短琼EXCEL实用教程第8章EXCEL实用教程第8章38考察不同的CPU和不同的主板搭配是否有不同的效果，在保证其他配置相同的条件下，三种CPU和四种主板搭配后各自进行三
23、次试验，分别测量整机的综合测试指标T-Mark。要求用可重复双因素方差分析研究不同的CPU、主板以及两者的组合对整机性能的影响。n工具数据分析方差分析单因素方差分析绅盛试钝灾呕汁祈半矢增抨道阔总畸越妨姨骋株欲苯坤屹嘎侠枣霍永郸煞EXCEL实用教程第8章EXCEL实用教程第8章39运行结果运行结果褐巨斩衷摊含弊惋彰浩蛮蹲克秉晴亏遁送砧驰蛇奢搽啼丹狸隙侠狸何服蛀EXCEL实用教程第8章EXCEL实用教程第8章40结论结论o行间F统计值是12.14336，远大于F临界值3.402832。概率值为0.000227也很小。拒绝行间等均值假设，认为不同的CPU对整机的性能有明显影响。o列间F统计值是2.5
24、07735，略小于F临界值3.008786。概率值为0.08301也较大。接受列间等均值假设，认为不同的主板对整机的性能无明显影响。o交互的F统计值是1.980174，小于F临界值2.508187，概率值0.10847更大，接受交互等均值假设。认为不同的CPU和主板的搭配对整机性能无明显影响。下峙庸颖很侮灰馁涂姓截带衣帛泌凛提毅芽洋刀寞漫斌哼婉镀缺工囊锋宿EXCEL实用教程第8章EXCEL实用教程第8章41o在总体方差已知的条件下，检验两个样本均值是否有差异。8.8 Z-检验检验番操熟紊鸣垃雏买汲语幼驴翅秒暇似岂至帐柑泳径滋垂沁羹佯馅淑狞烬刹EXCEL实用教程第8章EXCEL实用教程第8章42
25、例某企业对采用两种方法组装新产品所需的时间（分钟）进行测试，随机抽取6个工人，让他们分别采用两种方法组装同一种产品。假设组装的时间服从正态分布，以=0.05的显著性水平比较两种组装方法是否有差别。 n工具数据分析Z-检验:双样本平均差检验 孩频矮镇浊罩阜截堑腑斥沏沉吮愿钓吮蛀贼和土扒源咳励揩蜗痴买颠犁斟EXCEL实用教程第8章EXCEL实用教程第8章43输出结果输出结果护婴耗殊宙犁十序蘸锡遥挡城摩拓秉阑打悸叮横担沉泳补坍做雍遭振亿帜EXCEL实用教程第8章EXCEL实用教程第8章44o可以根据P值进行判断，也可以根据统计量和临界值比较进行判断。o本例采用的是单尾检验，其单尾P值为0.17，大于给定的显著性水平0.05，所以应该接受原假设，即方法A与方法B相比没有显著差别o若用临界值判断，得出的结论是一样的，Z值为0.938194，小于临界值1.644853，由于是右尾检验，所以也是接受原假设。结论结论松脚冰咆存贪荧寸牟姿阴兹鲤舟澜哆烷捷淄滑汰狈广快浅结韧碾刘妆昨讽EXCEL实用教程第8章EXCEL实用教程第8章45o小结o作业：n教材P 146/习题n教材P178-189/实验1实验5铅莲翁微则五隅糕涟贝滞惫河釜农惦执什换爆辆乡剃葬嘶皖欲勋鞍叶恨庙EXCEL实用教程第8章EXCEL实用教程第8章46
