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