商务数据分析课件

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,.,#,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2020/4/10,#,第,7,章,商务,数据处理分析,1,.,Excel,提供了非常实用的数据分析工具，利用这些分析工具，可解决数据管理中的许多问题，例如财务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等等。下面主要介绍财务管理与统计分析中常用的一些数据分析工具,。,2,.,7.1,用模拟分析方法求解,1.,单变量求解,单,变量求解是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。单变量求解工具可以解决许多数据管理中涉及到一个变量的求解问题。,例,1,某企业拟向银行以,7%,的年利率借入期限为,5,年的长期借款，企业每年的偿还能力为,100,万元，那么企业最多总共可贷款多少,？,图,7. 1,贷款总额计算,图,7. 2,“单变量求解”对话框,3,.,2.,模拟运算表,模拟运算表是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。在,Excel,中，可以构造两种模拟运算表：单变量模拟运算表和多变量模拟运算表。,（,1,）单变量模拟运算表,单变量模拟运算表是基于一个输入变量，用它来模拟对一个或多个公式计算结果的影响。,例,1,企业向银行贷款,10000,元，期限,5,年，使用“模拟运算表”模拟计算不同的利率对月还款额的影响，,4,.,图,7. 3,单变量模拟运算表,5,.,图,7. 4,“模拟运算表”对话框,图,7. 5,单变量的模拟运算表,6,.,3,方案管理器,在企业的生产经营活动中，由于市场的不断变化，企业的生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。,Excel,提供了称为方案的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种,方案进行,模拟分析。例如，不同的市场状况、不同的定价策略等,所可能产生的结果，也即利润会怎样变化。,例,1,某企业生产光盘，现使用方案管理器，假设生产不同数量的光盘（例如,3000,，,5000,，,10000,），对利润的影响。,7,.,已知：在该例中有,4,个可变量：单价、数量、推销费率和单片成本。,利润,=,销售金额成本费用,*,（,1+,推销费率）,销售,=,单价,*,数量,费用,=20000,成本,=,固定成本单价,*,单片成本,固定成本,=70000,8,.,（,1,）建立方案,步骤如下：,建立模型：将数据、变量及公式输入在工作表中，如图,7. 9,所示,。,在,单元格,“B7:B10”,中保存着要进行模拟的,4,个变量,分别是：单价、数量、单片成本和推销费率,。,图,7. 9,建立模型,9,.,图,7. 10,“方案管理器”对话框,图,7. 11,“编辑方案”对话框,10,.,图,7. 12,“,方案量值,”,对话框,11,.,（,2,）显示方案,设定了各种模拟方案后，任何时候都可以执行方案，察看模拟的结果,。,图,7. 13,显示运算结果,（,按钮。,图,7. 13,显示运算结果,12,.,（,3,）修改、删除或增加方案,（,4,）建立方案,报告,当需要将所有的方案执行结果都显示出来时，可建立方案报告,。,图,7. 14,（,a,） “方案总结”对话框,（,b,） “方案总结”工作表,13,.,7.2,线性回归,分析,回归分析法，是在掌握大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式（称回归方程式）。回归分析中，当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析,。,回归分析,中，又依据描述自变量与因变量之间因果关系的函数表达式是线性的还是非线性的，分为线性回归分析和非线性回归分析。通常线性回归分析法是最基本的分析方法，遇到非线性回归问题可以借助数学手段化为线性回归问题处理,。,14,.,例,1,对销售额进行多元回归分析预测，数据见图,。,15,.,解：本题可用二元线性回归分析来求解：,设定变量：,Y=,销售额，,X1=,电视广告费用，,X2=,报纸广告费用,方程为：,Y=a,1,X,1,+a,2,X,2,+b,通过线性回归分析确定,a,1,a,2,b,的值，从而确定方程。,16,.,1,操作方法与步骤,(1),建立数据模型,(2),工具加载宏,/,“,分析工具库,”,复选框,(3),工具数据分析,/,“,回归,”,(4,）,设置“回归”对话框中参数,:,“,Y,值输入区,”,，,“,X,值输入区,”,，,“,输出区域,”,(5),根据结果得出方程,17,.,图,7. 15,在,EXCEL,工作表中建立数据模型,图,7. 16,“回归”,对话框,中,参数的设置,18,.,由回归分析结果可见：回归方程,y=a,1,*x1+a,2,*x2+b,中，,a,1,=2.2901836209178,；,a,2,=1.30098909825998,；,b=832.300916901311,19,.,表,7.1,回归,结果,整理,4.,预测未来的销售额,假设,2002,年的电视广告费用预算为,35,万元，报纸广告费用预算为,18,万元，则根据多元线性回归方程,y=2.290183621*x1+1.300989098*x2+832.3009169,可计算出,2002,年的销售额为,2.290183621*35+1.300989098*18+832.3009169,即,913.7583,万元。,多元回归方程：,y=2.290183621*x,1,+1.300989098*x,2,+832.3009169,标准差：,a,1,=0.304064556,a,2,=0.320701597,b=15.73868952,判定系数,=0.9191356,y,估计值的标准误差,=6.425873026,F,统计值,=28.37776839,自由度,=5,回归平方和,=2343.540779,残差平方和,=206.4592208,20,.,7.3,规划求解,“规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。,规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值。（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果）,21,.,意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果。如获得最大的利润、最小的成本等。,注意事项：,在创建模型过程中，对,“,规划求解,”,模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。,添加规划求解命令,:,单击文件,选项卡选项加载项规划求解加载项转到，在“加载宏“对话框中选定“规划求解”复选框，然后单击“确定”,按钮。,22,.,1,、,求解线性规划问题,118,例：,工厂生产,3,种玩具：狗、娃娃、大熊猫；,3,种玩具的净利润（元）：,90,、,75,、,50,3,种玩具各用机工（小时）：,3,、,4,、,5,3,种玩具各用手工（小时）：,4,、,3,、,2,23,.,工厂极限生产能力：机工：,=400 h,手工,：,= 280 h,要求：,生产玩具狗的数量,= 32,件,求满足以上条件时，如何安排各玩具的生产数量以获得最大利润。,24,.,1,建立,数据模型,:,图,7.,21,建立数据模型,25,.,（,2,）进行求解,单击数据选项卡规划求解，弹出“规划求解参数”对话框，,如,图,7. 22,所示,26,.,在建立好所有的规划求解参数后，单击“求解”，则系统将显示,如,图,7. 25,所示的“规划求解结果”对话框，选择“保存规划求解结果”项，单击“确定”，则求解结果显示在工作表上，,如,图,7. 25,所示,。,在建立好所有的规划求解参数后，单击“求解”，则系统将显示,如,图,7. 25,所示的“规划求解结果”对话框，选择“保存规划求解结果”项，单击“确定”,，求解结果,如,图,7.,25,。,图,7. 25,“规划求解结果”对话框,图,7. 26,运算结果,27,.,求解的结果可形成：,运算结果报告,敏感性报告,极限值报告,28,.,2,、 求解方程组的解,（,1,）建立计算模型：在工作表中输入数据及公式，,如,图,7. 27,所示；,图,7. 27,利用“规划求解”工具求解方程组,29,.,（,2,）单击数据选项卡规划求解，弹出“规划求解参数”对话框，设置,“,规划求解参数,”,对话框中的,参数,（,3,）单击“求解”，即可得到方程组的解，,如,图,7. 28,所示。,图,7. 28,求解结果,30,.,7.4,移动平均,移动平均法是根据时间序列资料，逐项推移，依次计算移动平均，来反映现象的长期趋势。特别是现象的变量值受周期变动和不规则变动的影响，起伏较大，不能明显地反映现象的变动趋势时，运用移动平均法，消除这些因素的影响，进行动态数据的修匀，以利于进行长期趋势的分析和预测。,31,.,简单移动平均的计算公式：,设,xi,为时间序列中的某时间点的观测值，其样本数为,N,；每次移动地求算术平均值所采用的观测值的个数为,n,（,n,的取值范围：,2n0,为正相关，,r=0.8,，高度相关；,0.5=r0.8,，视为中度相关；,0.3=r0.5,，视为低度相关；,|r|0.3,时，视为不相关。,41,.,例,根据下图的数据，对家庭月消费支出与家庭月收入的数据进行相关分析。,42,.,操作步骤：,建立数据模型；,工具数据分析,/,相关系数,设置,“,相关系数,”,对话框内容,43,.,“,确定”，输出结果如图所示。,分析结果表明：相关系数,r=0.979747601,，表示家庭月消费支出与家庭月收入之间,存在,高度正相关关系。,44,.,分析结果表明：相关系数,r=0.979747601,，表示家庭月消费支出与家庭月收入之间存在高度正相关关系。,操作步骤,:,（,1,）建立数据模型：将数据输入到工作表区域,A1:E16,中，如图,8.38,所示；,45,.,7,.7,方差分析,方差分析,(Analysis of Variance,缩写为,ANOVA),是数理统计学中常用的数据处理方法之一，是经济和科学研究中分析试验数据的一种有效的工具。运用数理的方法对数据进行分析，以鉴别各种因素对研究对象某些特征值的影响大小和影响方式，这种方法就叫做方差分析。,所关注的对象的特征称为指标,，,影响指标的各种原因叫做因素，,在实验中,因素的各种不同状态称为因素的水平。,46,.,7,.7.1,单因素方差分析,单因素方差分析的作用是通过对某一因素的不同水平进行多次观测，然后通过统计分析判断该因素的不同水平对考察指标的影响是否相同。从理论上讲，实际上是在检验几个等方差正态总体的等均值假设。单因素方差分析的基本假设是各组的均值相等。,例,1,为了考察不同的销售渠道对总销售额的贡献，连续半年对不同渠道的业绩进行观测，得到一组数据如图所示，要求用方差分析判断各渠道的作用是否相同。,本题是一个典型的单因素方差分析问题，渠道作为营业业绩这个指标的一个主要因素，而不同的渠道可以视做该因素的不同水平。,47,.,操作步骤,:,建立数据模型,48,.,单击工具数据分析,/,方差分析：单因素方差分析”，,设置对话框的内容,，,“确定”。,49,.,50,.,运算结果说明：,运算结果分为,概要,和,方差分析,两部分。,概要：返回每组数据（代表因素的一个水平）样本数、合计、均值和方差。,方差分析：返回标准的单因素方差分析表，包括离差平方和、自由度、均方、,F,统计量、概率值、,F,临界值。,51,.,其中的“组间”就是影响销售额的因素,(,不同的销售渠道,),，“组内”就是误差，“总计”就是总和，“差异源”则是方差来源，,“,SS”-,平方和，,“,df”-,自由度，,“,MS”-,均方，,“,F”-F,比（,F,统计量），,“,P-value”,则是原假设,(,结论,),成立的概率,(,这个数值越接近,0,，说明原假设成立的可能性越小，反之原假设成立的可能性越大，“,F crit”,为拒绝域的临界值。,52,.,分析组内和组间离差平方和在总离差平方和中所占的比重，可以直观的看出各组数据对总体离差的贡献。将,F,统计量的值与临界值比较，可以判定是否接受等均值的假设。其中,F,临界值是用,FINV,函数计算出来的。,本例中,F,统计值是,0.848783,，,远远小于,F,临界值,3.098393,。所以，接受等均值假设。即认为四种渠道的总体水平没有明显差距。从显著性分析上也可以看出，,概率高达,0.48,，远远大于,0.05,。,53,.,7,.8,Z-,检验,Z,检验：是在总体方差已知的条件下两个样本均值之差的检验。,例,1,某企业管理人员对采用两种方法组装新产品所需的时间（分钟）进行测试，随机抽取,6,个工人，让他们分别采用两种方法组装同一种产品，采用方法,A,组装所需的时间和采用方法,B,组装所需的时间如,图,8.54,所示。假设组装的时间服从正态分布，以,=0.05,的显著性水平比较两种组装方法是否有差别。,54,.,操作步骤：,建立数据模型,工具数据分析,/Z-,检验：双样本平均差检验,”,设置对话框，确定。,55,.,56,.,结果：,57,.,结果分析：,可以根据,P,值进行判断，也可以根据统计量和临界值比较进行判断。,本例采用的是单尾检验，其单尾,P,值为,0.17,，大于给定的显著性水平,0.05,，所以应该接受原假设，即方法,A,与方法,B,相比没有显著差别,若用临界值判断，得出的结论是一样的，,Z,值为,0.938194,，小于临界值,1.644853,，由于是右尾检验，所以也是接受原假设。,58,.,谢谢观看！,