Excel与数据处理第七章数据分析工具及应用.ppt

Excel与数据处理,第七章 数据分析工具及应用,本章教学目的与要求,1、掌握宏的加载方法2、掌握追踪从属或引用单元格的方法3、掌握限定单元格数据的范围及圈释无效数据的 应用方法4、掌握模拟运算表及变量求解的应用5、掌握方案的建立和应用6、掌握规划求解工具的应用7、了解假设检验和回归分析等工具的应用,本章重点、难点及学时数,重点：掌握数据审核的方法掌握模拟运算表的应用掌握单变量求解的应用掌握方案的应用掌握规划求解的应用难点：掌握规划求解的应用学时数：12学时（上机6学时）,本章目录,7.1 分析工具的安装7.2 数据审核及跟踪分析7.3 模拟运算表7.4 单变量求解7.5 方案分析7.6 线性规划求解7.7 数据分析工具库小结思考与练习,7.1 分析工具的安装,1、加载宏的概念加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装。其作用是为 Excel 添加命令和函数，扩充Excel的功能。Excel加载宏的扩展名是.xla或.xll。在默认情况下，Excel将下表列出的加载宏程序安装在如下某一磁盘位置：“Microsoft OfficeOffice”文件夹下的“Library”文件夹或其子文件夹，或 Windows 所在文件夹下的“Profiles用户名Application DataMicrosoftAddIns”文件夹下。网络管理员也可将加载宏程序安装到其他位置。,7.1 分析工具的安装,2、Excel内置加载宏,7.1 分析工具的安装,7.1 分析工具的安装,3、安装分析工具选择“工具”|“加载宏”菜单在对话框中选择所需工具，按确定 注：若在安装EXCEL系统时没有安装加载宏，则必须重新启动EXCEL的安装程序，选择其中的“添加/删除”命令，安装EXCEL的加载宏。,目录,7.2 数据审核及跟踪分析,1、概念数据审核是一种查找单元格数据错误来源的工具，快速地找出具有引用关系的单元格，借此分析造成错误的单元格。数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。2、数据审核的方式追踪引用单元格见ch7.xls追踪引用单元格 操作方法：选定菜单“工具”“审核”显示审核工具栏选择要追踪引用的含公式单元格“审核”工具栏中“追踪引用单元格”按钮再次单击“追踪引用单元格”按钮提供数据的下一级单元格移去引用单元格追踪箭头：操作方法：选择“审核”工具栏中“移去引用单元格中追踪箭头”,7.2 数据审核及跟踪分析,追踪从属单元格见ch7.xls追踪从属单元格某单元格公式引用了其它单元格，则该单元格为从属单元格。操作方法：选定菜单“工具”“审核”显示审核工具栏选择要追踪从属单元格的单元格“审核”工具栏中“追踪从属单元格”按钮再次单击“追踪从属单元格”按钮提供从属的的单元格移去引用单元格追踪箭头：操作方法：选择“审核”工具栏中“移去从属单元格中追踪箭头”,7.2 数据审核及跟踪分析,3、数据有效性数据有效性：对数据进行检验和检查的有效方法，把错误限制在数据输入阶段。限定数据类型和有效范围：如：限定数据大小范围、日期的范围、输入字符的个数、单元格的公式,7.2 数据审核及跟踪分析,数据限制的操作方法：选择“数据”“有效性”在对话 框中操作：限定文本长度：“设置”选项卡中“允许”下拉列表中选择文本长度。限定数据的有效范围：“设置”选项卡中“允许”下拉列表中选择整数/小数-确定最大/小值设置单元格有效范围：“设置”选项卡中“允许”下拉列表中选择序列输入序列值设置输入提示信息：“输入信息”选项卡中输入要显示的信息,7.2 数据审核及跟踪分析,例：见ch7.xls限定数据范围 某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表中数据的输入类型及范围进行限制。限制学号为8位字符，不能小于8位，也不能多于8位。限制所有学科成绩为0100之间的整数。限制科目列标题的取值范围，如“高数”不能输入为“高等数学”。,7.2 数据审核及跟踪分析,4、圈释无效数据 使用数据有效性规则可限制单元格可接收的数据，但对已输入数据的区域，不能显示出有误的数据。采用圈释无效数据的方法，可以显示不满足有效性规则的错误单元格。操作方法：（选择数据区域设置数据有效性规则）选择“工具”菜单“审核”选择“显示审核工具栏”选中有效性检测的数据区域单击“审核”工具栏的“圈释无效数据”按钮 注：要先设置数据的有效范围，然后再圈释无效数据例：见ch7.xls圈释无效数据 某班要建立一个成绩登记表，已经对成绩表中数据的输入类型及范围进行限制，找出其中不符合规定的数据。,目录,7.3 模拟运算表,1、概念模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。2、模拟运算表的类型基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；单模拟运算表基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响双模拟运算表,7.3 模拟运算表,3、单变量模拟运算表概念在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格”。输入单元格，就是被替换的含有输入数据的单元格 操作步骤：1、在工作表中建立模拟运算表的结构；2、输入模拟运算表要用到的公式；3、选择包括公式、引用单元格和运算结果单元格区域（3部分）；4、选择“数据”菜单“模拟运算表”选项；5、在“模拟运算表”对话框中输入引用单用格（行或列一种）确定,7.3 模拟运算表,例：见ch7.xls单变量模拟运算表 假设某人正考虑购买一套住房，要承担一笔250 000元的贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。若贷款额分别为400 000，550 000，800 000元，每月的应还贷金额又是多少？,7.3 模拟运算表,4、双变量模拟运算表概念：单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响，要查看两个变量对公式计算结果的影响，就要用到双变量模拟运算表。所谓双模拟变量，就是指公式中有两个变量。公式中两个变量所在的单元格是任取的。可以是工作表中任意空白单元格。,7.3 模拟运算表,操作步骤：1、在工作表中建立模拟运算表的结构；2、在行列交叉处输入模拟运算表要用到的公式；3、选择包括公式,引用单元格和运算结果单元格区域（3部分）；4、选择“数据”菜单“模拟运算表”选项；5、在“模拟运算表”对话框中输入公式中行和列引用的单用格确定例：见ch7.xls双变量模拟运算表 假设某人想贷款45万元购买一部车，要查看在不同的利率和不同的偿还年限下，每个月应还的贷款金额。假设要查看贷款利率为5%、5.5%、6.5%、7%、7.5%、8%，偿还期限为10年、15年、20年、30年、35年时，每月应归还的贷款金额是多少？,目录,7.4 单变量求解,1、概念所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值.2、单变量求解方法在工作表中输入原始数据；建立可变数公式；设置求解公式：菜单“工具”单变量求解对话框中输入：目标单元格、目标值、可变单元格例：见ch7.xls单变量求解 某公司想向银行贷款900万元人民币，贷款利率是8.7%，贷款限期为8年，每年应偿还多少金额？如果公司每年可偿还120万元，该公司最多可贷款多少金额？前一问题可用PMT函数,后一问题可用单变量求解。,目录,7.5 方案分析,1、概念方案是已命名的一组输入值，是 Excel 保存在工作表中并可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。例:已知某茶叶公司2004年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划。由于市场竞争的不断变化，所以只能对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是总销售额增长率高，各茶叶的销售成本增长率低。最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、红茶的销售成本分别增长10%、6%、10%、7%。见ch7.xls方案,7.5 方案分析,建立方案解决工作表,建立方法如下，输入下表A列、B列及第3行的所有数据；在C4单元格中输入公式“=B4*(1+$B$16)”，然后将其复制到D4F4；在C7中输入公式“=B7*(1+$B$17)”,并将其复制到D7F7；在C8中输入公式“=B8*(1+$B$18)”，并将其复制到D8和F8；在C9中输入公式“=B9*(1+$B$19)”，并将其复制到D9F9；在C10中输入公式“=B10*(1+$B$20)”，并将其复制到D10F10；第11行数据是第7，8，9，10行数据对应列之和；净收入是相应的总销售额和销售成本之差，E19的总净收入是第13行数据之和。,7.5 方案分析,输入方案变量值如下图所示：,7.5 方案分析,2、显示方案 选择“工具”“方案”菜单选择“方案管理器”对话框中的某一方案单击“显示”按钮3、建立方案报告见ch7.xls方案摘要 选择“工具”“方案”菜单选择“方案管理器”对话框中的某一方案单击“总结”按钮在“方案总结”对话框中结果类型中选择“方案总结”4、建立方案透视图见ch7.xls方案数据透视图 选择“工具”“方案”菜单选择“方案管理器”对话框中的某一方案单击“总结”按钮在“方案总结”对话框中结果类型中选择“方案数据透视表”,目录,7.6 线性规划求解,1、概述 EXCEL提供的规划求解工具，可求解出线性与非线性两种规划求解问题，规划求解问题常用于解决产品比例、人员调度、优化路线、调配材料等方面问题。2、规划求解问题的特点：问题有单一的目标，如求运输的最佳路线、求生产的最低成本、求产品的最大盈利，求产品周期的最短时间等。问题有明确的不等式约束条件，例如生产材料不能超过库存，生产周期不能超过一个星期等。问题有直接或间接影响约束条件的一组输入值。,7.6 线性规划求解,3、Excel规划求解问题的组成部分（1）一个或一组可变单元格 可变单元格称为决策变量，一组决策变量代表一个规划求解的方案（2）目标函数目标函数表示规划求解要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数（3）约束条件约束条件是实现目标的限制条件。意义：通过规划求解，用户可为工作表的目标单元格中的公式找到一个优化值，规划求解将直接或间接与目标单元格公式相联系的一组单元格数值进行调整，最终在目标单元格公式中求得期望的结果。,7.6 线性规划求解,例：见ch7.xls规划求解某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料4种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下页各表所示。问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？分析:所求是在现有的原材料情况下,应如何合理搭配,才能获取生产产品的最大利润.,7.6 线性规划求解,表2 生产肥料的库存原材料,表1 各肥料成品用料及其价格表表的意思是生产一个单位的肥料需要多少各种原材料多少单位,表3单位原材料成本单价,7.6 线性规划求解,建立规划求解模型步骤：规划求解第一步建立求解工作表(输入原始数据及相应的各公式),7.6 线性规划求解,规划求解第二步设置求解参数选择“工具”“规划求解”菜单，设置以下求解的各项参数：设置目标单元格：输入目标函数所在单元格(为总余额单元格)设置目标：最大值、最小值或值的数值(最大利润,即最大值)设置可变单元格：它的确定决定结果（为生产数量）设置约束条件：单击“添加”按钮输入约束条件按添加依次输入所有约束条件确定,7.6 线性规划求解,规划求解第3步保存求解结果在规划求解对话框中按“求解”在规划求解结果对话框中按“保存规划求解结果”,7.6 线性规划求解,4、修改资源 例1：见ch7.xls规划求解肥料厂接到一个电话：只要公司肯花10元的运费就能得到150个单位的矿物。这笔交易稍稍降低了矿物质的平均价格，但这些矿物质值10元吗？解决该问题的方法是，将库存矿物3500改为3650，用规划求解重新计算最大盈余。看除去￥10的成本后，盈余是否增加 操作:将库存矿物3500改为3650,其它所有公式不变,再次进行求解,求得盈余额为4483.41,原盈余额为4425.89.可知盈利为57.52.扣除10元成本后仍有47.52.因此该矿物还是要的.,7.6 线性规划求解,5、修改约束条件 见ch7.xls规划求解肥料厂接到一个电话，一个老顾客急需25个单位的上层肥料，公司经理在检查打印结果后，发现没有安排生产上层肥料。数量为0。决定增加约束条件，为他生产25个单位的上层肥料。结果可发现:盈余额仅3246.51,比原来4483.41少了1236.9。显然不值得。但如该顾客为长期顾客，则短期内将损失一些钱，但得到了顾客的信任。,增加的约束条件,7.6 线性规划求解,6、规划求解的结果报告运算结果报告：列出目标单元格、可变单元格及它们的初始值、最终结果、约束条件和有关约束条件的信息。见ch7.xls运算结果报告,7.6 线性规划求解,敏感性报告：见ch7.xls敏感性报告,7.6 线性规划求解,极限报告：列出目标单元格、可变单元格及它们的数值、上下限和目标值。下限为在满足约束条件和保持其它可变单元格数值不变的情况下，某个可变单元格可以取得的最小值，上限则为在这种情况下可以取到的最大值。见ch7.xls极限值报告,7.6 线性规划求解,7、求解精度及求解模型设置 Excel采用迭代的方式进行规划求解，当求解到一定精度时就结束求解，但有时要修改求解的精度、计算时间、规划模型和迭代次数。修改上述设置的方法如下：在“规划求解参数”对话框中设置好各项求解参数；单击“选项”按钮，在“规划求解选项”对话框中设置各项求解参数。,7.6 线性规划求解,例2：求解不等式：见ch7.xls规划求解不等式某工厂生产甲、乙两种产品，假设生产甲产品1吨，要消耗9吨煤，4千瓦电力，3吨钢材，获利0.7万元；生产乙产品1吨，要消耗4吨煤，5千瓦电力，10吨钢材，获利1.2万元。按计划国家能提供给该厂的煤为360吨，电力200千瓦，钢材300吨，问应该生产多少吨甲种产品和乙种产品，才能获得最大利润？假设生产甲种产品X1吨，生产乙种产品x2吨，其最大利润是求=0.7x1+1.2x2的最大值。这个问题可用数学建模如下：,7.6 线性规划求解,规划求解如下：B3和C3分别用于保存甲和乙产品的生产量。目标单元格为B8；可变单元格为$B$3:$C$3；约束条件为：$B$3=0$C$3=0$B$4=360$B$5=200$B$6=300,7.6 线性规划求解,例3：见ch7.xls线形规划求解某公司在A地有一个生产基地,其生产能力为400,随着市场需求的增长及该公司业务量的增大,现有3个配送中心的需求都在增长,预计分别为200,400,300。公司正考虑再建立一个生产能力为500的工厂，准备建在B地。从A地的工厂向3个配送中心的单位运输成本分别为5.0元，6.0元，5.4元，从B地的工厂向3个配送中心的单位运输成本是7.0元，4.6元，6.6元。应怎样分配A、B两地到3个配送中心的产品量，才能使运输成本最小？规划模型解释如下：,7.6 线性规划求解,D5、E5、F5表示从A厂将1个单位产品分别送到配送中心1，2，3的费用；D7，E7，F7表示从B厂将1个单位产品分别送到配送中心1，2，3的费用；D6，E6，F6为可变单元格，保存从A厂运到3个配送中心的最佳产品量；D8，E8，F8为可变单元格，保存从B厂运到3个配送中心的最佳产品量；D10，E10，F10表示配送中心1，2，3的最大负荷能力；H5，H7分别是A厂、B厂的生产能力，H9是A，B两厂的总生产能力,7.6 线性规划求解,G6，G8分别为A，B两厂各自的产品总量。G6公式：=D6+E6+F6；G8公式：=G8+E8+F8；D9，E9，F9分别为两厂送到各配送中心的产品总量。D9公式：=D6+D8；E9公式：=E6+E8；F9公式：=F6+F8；本模型的约束条件分析如下：A，B两厂送到各配送中心的产品总量不能超过各配送中心的负荷能力：D9：F9=0，D8:F8=0,本模型的目标函数，求下列公式的最大值：S=D5*D6+E5*E6+F5*F6+D7*D8+E7*E8+F7*F8,目录,7.7 数据分析工具库,1、概述Excel提供了一组数据分析工具，称为分析工具库。其中提供的分析工具在工程分析、数理统计、经济计量分析等学科中有较强的实用价值。分析工具库由Excel自带的加载宏提供。如果启动Excel后，在Excel的“工具”菜单中没有“数据分析”菜单项，就需启动“工具”中的“加载宏”菜单项，将“分析工具库”加载到Excel系统中。如果加载宏对话框中没有分析工具库，则单击加载宏对话框中“浏览”按钮，定位到分析工具库加载宏文件“Analy32.dll”所在的驱动器和文件夹，通常位于“Microsoft OfficeOfficeLibraryAnalysis”中,否则需运行Office系统的安装程序。Excel的“分析工具库”加载宏提供的一些统计函数、财务函数和工程函数。这些函数只有在安装了“分析工具库”后才能使用。,7.7 数据分析工具库,2、Excel分析工具库中的工具,7.7 数据分析工具库,7.7 数据分析工具库,3、统计分析 Excel的分析工具库提供了3种统计观测分析工具：指数平滑分析、移动平均分析和回归分析 三种工具用法相同，下面以指数平滑分析为例。见ch7.xls 指数平滑分析（1）在工作表的一列上输入各时间点上的观察值，如下图A列所示。,7.7 数据分析工具库,（2）选择“工具”菜单“数据分析”选项，对话框中选择“指数平滑”按确定。（3）在“指数平滑”对话框中设置“输入区域”、“阻尼系数”、“输出区域”选项。（4）选定对话框中“图表输出”和“标准误差”复选框标志。分析结果：B列为分析之后输出的预测数据；C列是分析工具输出的标准误差。,7.7 数据分析工具库,4、假设检验 假设检验是根据对事物进行抽样所得的少量样本信息，判断总体分布的某个假设是否成立的一种数理统计方法。假设分析工具有三种：t-检验、z-检验、F-检验。运用这些检验工具可以完成均值、方差的假设检验。方法见下例,7.7 数据分析工具库,例：见ch7.xlst检验（双样本等方差t-检验，以确定两个样本均值实际上是否相等）某种子公司为比较两个稻种的产量，选择了25块条件相似的试验田，采用相同的耕种方法进行耕种试验，结果播种甲稻种的13块田的亩产量（单位：市斤）分别是：880、1 120、980、885、828、927、924、942、766、1 180、780、1 068、650；播种乙稻种的12块试验田的亩产量分别是：940、1 142、1 020、785、645、780、1 180、680、810、824、846、780。问这两个稻种的产量有没有明显的高低之分。说明：要判断两稻种有无显著差别，用t-检验方法，需先计算各样本的平均值和方差，才能作进一步的检验分析。,7.7 数据分析工具库,t-检验操作过程：(1)输入A、B两列样本数据（下表中右边数据全为产生的分析结果）,7.7 数据分析工具库,(2)选择“工具”菜单中“数据分析”选项，对话框中选择t检验双样本等方差假设；（3）在“双样本等方差假设分析”对话框中设置t检验的各项参数按确定,7.7 数据分析工具库,5、回归分析回归分析主要用于分析单个因变量是如何受一个或几个自变量影响的。如观察某个运动员的运动成绩与一系列统计因素的关系。如年龄、体重、身高等。回归分析分为线性回归和非线性回归两种。线性回归的数学模型为：Excel通过对一组观察值使用“最小二乘法”直线拟合，进行线性回归分析，该回归分析可同时解决一元回归与多元回归问题。,7.7 数据分析工具库,例：（用一个多元回归线性分析例子来说明回归分析工具的使用方法）ch7.xls回归分析数据表中，列出了美国19561970年间历年的人均可支配收入xi和人均可消费支出yi的数据。试用图中的数据拟合模型。模型中的趋势变量t，用于反映除人均收入之外的所有其他因素对人均消费的影响,7.7 数据分析工具库,利用回归分析工具求解此模型的方法输入原始数据表；选择“工具”菜单中“数据分析”选项，在“数据分析”对话框中选择“回归”列表。系统弹出如下对话框；在“回归”对话框中输入因变量y和自变量x的数据区域；若需要线性拟合的“残差图”和“线性拟合图”等，则需选择相应复选框本题结果见见ch7.xls回归分析的输出结果,目录,综合实例,1.单变量模拟运算表实例超市要进行一些改革，如对某些产品采取分期付款的方式进行销售。假设有一液晶电视，每台售价为98,000，采用分期付款的方式进行销售，初步确定分期付款的方式为零首付，月分期手续费率为0.7，求在不同的分期付款期数（月）下，每期（月）消费者需要付款的金额,综合实例,【实例操作步骤】具体操作步骤如下：步骤1：创建工作表，建立基本的模拟运算工作表，输入必要的数据及要测试的工作表中的数据，即不同的分期付款期数，如图所示。,综合实例,步骤2：创建运算公式在单元格D3中输入公式“=PMT（B5,C3,B2）”，得出当分期付款期数为C3单元格中的数值时，消费者每月的付款额。如图7-2所示。公式中B5为月分期手续费率，C3为分期付款期数，以月为单位，B2为付款本金。,综合实例,步骤3：建立单变量模拟运算表首先要选定公式、数值序列和模拟运算结果所在的单元格区域，即C3:D12，以定义这个模拟运算表，然后选择“数据”菜单中的“模拟运算表”命令，打开“模拟运算表”对话框，在“模拟运算表”对话框的“输入引用列的单元格”文本框中输入第一个变量所在的单元格地址“$C$3”，如图所示。,综合实例,【实例操作结果】最终的计算结果如图所示。,综合实例,双变量模拟运算表实例在前面的例子中，仅仅把分期付款期数作为单变量进行模拟运算，但在现实生活中，月分期付款手续费率也是经常要发生变化的。假设想查看在不同的分期付款期数和不同的月分期付款手续费率下消费者每月付款金额的变化情况，就必须建立双变量模拟运算表了。,综合实例,【实例操作步骤】步骤1：创建工作表建立基本的运算工作表，输入必要的数据及要测试的工作表中的数据，即不同的月分期付款手续费率和分期付款期数，其中，月分期付款手续费率属于行变量，分期付款期数属于列变量。如图所示。,综合实例,步骤2：创建运算公式双变量模拟运算中首先要在行、列交叉处所在的单元格输入运算公式。在本例中，在分期付款期数和月分期付款手续费率的交叉处，即D3单元格中输入公式“=PMT(B5,B7,B2)”，运算结果如图所示。,综合实例,步骤3：建立双变量模拟运算表首先要选定公式、数值序列和模拟运算结果所在的单元格区域，即D3:J13，以定义这个模拟运算表，然后选择“数据”菜单中的“模拟运算表”命令，打开“模拟运算表”对话框，在“输入引用行的单元格”中输入月分期手续费率所在的单元格“$B$5”，在“输入引用列的单元格”中输入分期付款期数所在的单元格“$B$7”，如图所示。最后单击“模拟运算表”对话框的“确定”按钮。,综合实例,【实例操作结果】最终的计算结果如图所示。,综合实例,利用单变量求解计算付款期数案例在上面模拟运算的例子中，主要是求出消费者在固定的分期手续费率和分期付款期数下每月应付的金额。假设现在有一消费者可负担的每月的付款金额为￥5000，想知道在固定的月分期手续费率下，该消费者可承受的付款期数是多少？,综合实例,【案例操作步骤】步骤1：在单变量求解工作表中输入原始数据。如图所示,综合实例,步骤2：在月付款金额对应的单元格B4中输入公式“=PMT(B3,B5,B2)”，如图所示。,综合实例,步骤3：选择“工具”菜单的“单变量求解”命令，打开“单变量求解”对话框，在“目标单元格”中输入“$B$4”，在“目标值”中输入“-5000”，表示经过求解之后，单元格B4的值应是-5000，目标单元格必须包含公式，在“可变单元格”中输入“$B$5”，即最后分期付款期数所在的单元格，如左图所示。接着在单击“确定”按钮后，屏幕上可能出现出错提示，表示无法进行求解，如右图所示。,综合实例,在这里可以先给可变单元格设置一个值，比如5，因为其中的数值也要根据目标单元格中的结果而发生变化，所以事先给的值并不会影响到最终的计算结果。具体设置如左图所示。步骤4：再打开“单变量求解”对话框，按上面同样的设置，可得到“单变量求解状态”对话框，表明求得一个解，如右图所示。,综合实例,【案例操作结果】求解结果如图所示。,综合实例,超市收入预测方案实例假设已知超市在2007年的各部门的收入情况，现在想对2008年的收入情况做一个估计，分析在不同增长方案下的收入情况，这些增长方案包括经营情况一般、经营情况良好和经营情况较差三种方案。,综合实例,【案例操作步骤】具体操作步骤如下：步骤1：制作如图所示的工作表，包括2007年度的收入情况，以及2008年度根据经营情况（一般）设置的收入增长率。其中在单元格D3输入公式“=C3-B3”，然后将其复制到D4、D5、D6；在单元格D11中输入公式“=C3*(1+C11)-B3*(1+B11)”，并将其复制到D12、D13、D14；在单元格D15中输入公式“=SUM(D11:D14)”。从图中可以看出，在经营情况一般这种方案下，2008年企业的总利润为5052715。,综合实例,步骤2：选择“工具”菜单的“方案”命令，弹出“方案管理器”对话框，如图所示。,综合实例,步骤3：在“方案管理器”中单击“添加”按钮，弹出“编辑方案”对话框，在“方案名”中输入方案名称“经营情况一般”；在“可变单元格”中输入需要更改的单元格的引用“$B$11:$C$14”，选中“防止更改”复选项。如图所示。,综合实例,步骤4：在上图所示的对话框中单击“确定”按钮，将打开“方案变量值”对话框。在“方案变量值”对话框中分别输入可变单元格所对应的单元格，如图所示。,综合实例,步骤5：在上图所示的对话框中单击“确定”按钮，则该方案创建完成，返回“方案管理器”对话框，将显示出所定义方案的名称。如图所示。,综合实例,步骤6：在“方案管理器”对话框中单击“添加”按钮，使用类似的步骤再创建两个方案。【实例操作结果】最终结果如图所示,综合实例,利用规划求解制定销售决策实例假设超市某一部门要销售两种商品，其中A商品的采购价为900元，销售价为2400元，B商品的采购价位800元，销售价为1800元，而且两种商品的物流和仓储成本不同，A商品的物流成本和仓储成本分别为30元和19.5元，B商品物流成本和仓储成本分别为25元和6.5元，现在的问题是在每月的物流和仓储费用固定的情况下（物流和仓储总费用分别为2800元和1500元）。该如何分配A、B两种商品的进货数量，才能得到最大的销售利润？,综合实例,【实例操作步骤】具体操作步骤如下：步骤 1：建立商品数据表，如图所示。,综合实例,步骤 2：输入公式，在B12单元格中输入公式“=(B6-B5-B4-B3)*B7+(C6-C5-C4-C3)*C7”，在单元格B13和B14中分别输入“=B4*B7+C4*C7”和“=B5*B7+C5*C7”。如图所示。,综合实例,步骤 3：建立好上面表格后，就可以利用规划求解工具对表格的问题进行求解了。选择“工具”菜单中的“规划求解”命令，弹出“规划求解参数”对话框，如图所示。在“设置目标单元格”文本框中输入B12单元格，即要求的销售最大利润。在“等于”单选项中选择“最大值”，表示所要求的目标函数的最大值。在“可变单元格”文本框中输入B7:C7，就是目标函数的两个变量，也就是两种商品各自的进货量。这里的引用均为绝对引用。,综合实例,步骤4：设置规划求解约束条件。选择“规划求解参数”对话框中的“添加”按钮，弹出“添加约束”对话框，在对话框中添加限制条件。对于物流总成本的限制，应该是物流总成本小于每月的物流费用控制，即B13B9。添加完该约束后，单击对话框中的“添加”按钮，接着添加其它的约束条件。最终添加完约束条件的结果如图所示。,综合实例,步骤5：进行规划求解。选择“规划求解参数”对话框中的“求解”按钮，弹出“规划求解结果”对话框。接着单击“确定”按钮，就可以看到规划求解的求解结果，如图所示。,综合实例,利用数据分析工具进行销售预测实例超市有关部门要作如下的销售预测。根据超市近7年来的销售收入数据（如图所示），预测下一年（第8年）的销售量。,综合实例,【实例操作步骤】步骤1：选择“工具”菜单中的“数据分析”命令，弹出“数据分析”对话框，从“分析工具列表”中选择“回归”选项，然后单击“确定”按钮，打开“回归”对话框，如图所示。,综合实例,步骤2：在“Y值输入框”中输入“$B$2:$B$8”，在“X值输入框”中输入“$A$2:$A$8”，在“输出选项”中选“输出区域”，并填入“$D$1”，然后根据实际需要，勾选其他需要的选项，如图所示。单击“确定”按钮，回归分析的摘要就输出在本工作表上。,综合实例,【实例操作结果】最终求解结果如图所示。,综合实例,利用数据分析工具进行数据统计实例超市有关部门要作如下的数据统计。根据当月的员工加班情况（如图所示），统计出各加班段的人数。,综合实例,【实例操作步骤】对于实例所提出的问题，可以通过直方图分析工具来解决。具体操作步骤如下：步骤1：建立如图所示的统计表,综合实例,步骤2：选择“工具”菜单中的“数据分析”命令，弹出“数据分析”对话框，从“分析工具列表”中选择“直方图”选项，然后单击“确定”按钮，打开“直方图”对话框，如图所示。,综合实例,步骤3：在“输入区域”中输入“$C$2:$C$49”，在“接受区域”中输入“$E$2:$E$7”，在“输出选项”中选“输出区域”，并填入“$E$11”，然后根据实际需要，勾选其他需要的选项，如图所示。最后单击“确定”按钮，系统将生成频率数和直方图。,综合实例,【实例操作结果】最终求解结果如图所示。,本章小结,Excel的分析工具库中提供了大量非常实用的分析工具。数据审核和跟踪分析可以查找单元格或公式中的数据来源，并可由此分析产生错误的根源。数据的有效性检验工具可以把单元格中的数据输入或数据值限制在一个有效的范围内，以减少错误数据的产生。要分析两个变量之间的因果关系，模拟运算和单变量求解是一个较佳的工具。线形规划工具可用于求解人员分配、生产计划、公路运输及生产、投资等的最佳方案。统计分析、假设检验及回归分析在数理统计、经济分析等领域有着较强的应用价值，它们为许多复杂问题的解决提供了一些有用的模型和解决方案。,目录,本章思考与练习,1、分析工具如何安装？2、追踪引用单元格和追踪从属单元格有什么区别？3、什么前提下适合用模拟运算表解决问题？4、什么前提下适合用变量求解解决问题？5、什么前提下适合用方案分析解决问题？6、什么前提下适合用规划求解解决问题？规划求解的数学模型如何创建？7、了解数据分析工具库中的其他分析工具。8、某人欲贷款100万元买一幢别墅，此人每月有还1万元的能力，基于年利率7%每月末还款至少多久能还清？思考用函数和用单变量求解两种方法。,目录,