Excel模拟运算表.ppt

1、EXCEL基础及应用,湖北生物科技职业学院 刘英,Excel数据分析工具及应用,数据分析工具的安装与卸载,模拟运算表,方案分析,1,2,3,4,规划求解,1 数据分析工具的安装与卸载,1.1 安装分析工具库 “分析工具库”是一个加载宏文件，文件名为Analys32.xll（加载宏文件是可以自动执行一系列复杂任务的操作命令的组合）。但这一工作在安装Microsoft Office组件时已经加载。 安装步骤如下 打开Excel工作簿，选择菜单栏中【工具】菜单中的【加载宏】，如图5-1所示。,图1加载宏,在打开“加载宏”对话框，对话框如图5-2所示，单击【分析工具库】复选框，框前即出现号，表明已被选

2、定，选择【确定】。,图2选择分析工具库,1.2 卸载分析工具库,当不需要使用分析工具库时，可以通过设置来卸载分析工具库。 操作步骤： 打开Excel工作簿，选择菜单栏中【工具】菜单中的【加载宏】。 在打开“加载宏”对话框，对话框如图4所示，单击【分析工具库】复选框，将框前出现号取消。 选择取消后，单击【确定】按钮，即可自动卸载分析工具库。此时在“工具”菜单中的“数据分析”选项自动隐藏。,图5-4卸载分析工具库,5.2 模拟运算表,什么是模拟运算表？ 是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。 模拟运算表的类型 基于一个输入变量的表，用这

3、个输入变量测试它对多个公式的影响；单变量模拟运算表 基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响双变量模拟运算表,2.1 单变量模拟运算表,【例5-1】某人想买房，向银行贷款30万，还款年限为10年，目前年利率假设为4%，采取每月等额还款的方式。 请帮助该买房人计算在现有利率下每月的偿还额。 请帮助该买房人计算不同利率条件下的每月偿还额。 请帮助该买房人计算出还款期限是5年、10年、15年、20年、25年和30年时，每月等额还款的数额。,单变量模拟运算表主要是用来分析当其他因素不变时，一个参数的变化对目标结果的影响。,问题求解：可利用PMT函数计算。 (1) 建立一新工作簿，并

4、选择一张工作表，将住房贷款有关的基本数据输入该工作表（B4:D8）单元格区域所示， (2) 在D9单元格中输入公式“=PMT(D5/12,D8,D4)”，按下【Enter】键，计算出所要的结果。,图5-5住房贷款还款的Excel分析图,第一个参数是月利率 第二个参数是还款期 第三个参数是贷款金额,问题求解：可利用模拟运算表计算不同利率下的月偿还额。 输入贷款期各种可能的不同利率数据，如图5-6（F4:F16）单元格区域所示。,图5-6住房贷款还款的Excel模拟运算表分析图,选择包含公式和需要进行模拟运算目标单元区域（F3:G16），如图5-7所示。,图5-7住房贷款还款的Excel模拟运算表

5、分析图,单击【数据】【模拟运算表】命令，弹出如图5-8所示的对话框。在“输入引用列的单元格”中输入$D$5，再单击【确定】按钮，,图5-8住房贷款还款的Excel模拟运算表分析图,图5-9模拟运算表工具使用示意图,各分析值自动填入分析表中，如图5-9（ G4：G16 ）单元格区域所示。,2.2 双变量模拟运算表,对于问题分析不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。 问题求解： 选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据；在该区域的第一行输入可能的贷款年限数据。如图5-10（B11:G11）单元格区域所示。,图5-1

6、0模拟运算表工具使用示意图,在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。选定整个模拟运算表区域。如图5-11（A11:G24）单元格区域所示。,图5-11模拟运算表工具使用示意图,单击数据菜单中的模拟运算表命令。在模拟运算表对话框（如图5-12）的输入引用行的单元格框中输入“$C$6”（年份）；在输入引用列的单元格框中输入“$C$5”（年利率）,单击【确定】。,双变量模拟运算表的计算结果如图513所示。其中B12：G24单元格区域的计算公式为“=表(C6,C5) ”，表示其是一个以C6为行变量，C5为列变量的模拟运算表。,单变量模拟运算表,假设有一个制衣厂，生产的裤子单价100元

7、，其中单位人工费10元， 单位材料费15元， 单位产品制造过程中所耗费的水电费10元,可做表如下：,单变量模拟运算表,B2=B3+B4+B5, B7=B1B2 B8=B9+B10+B11,B14=B13B7 B15=B13B1 B16=B8+B13B2 B17=B15B16 边际贡献总值=单位边际贡献 销量，盈亏平衡量=固定成本/单位边际贡献， 即B19=B8B7,单变量模拟运算表,其中： C1到F1的标题手工填入， 输入C2=0 和C3=1500时的值,选择C1到F3，然后按工具栏上的 图表向导。 选择XY散点图， 子图表类型选择 无数据点折线散点图， 然后按一步,2.3 单变量求解,什么是

8、单变量求解？ 单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。,【例5-2】某公司想向银行贷款900万元人民币，贷款利率是8%，贷款限期为5年，如果公司每年可偿还100万元，该公司最多可贷款多少金额？ (1) 在工作表中输入原始数据，如图5-15；,图5-15贷款总额计算,(2) 建立可变数公式； 在单元格B2中输入公式=PMT(B1,B3,B4)”； (3) 设置求解公式：菜单【工具】单变量求解对话框中输入：目标单元格、目标值、可变单元格，如图5-16所示。,图5-16【单变量求解】对话框,3 方案分析,方案，就是已命名的一组输入值，这组输入值保存在工作表中

9、，并可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。对于同一解题方案的模型参数，可以创建多组不同的参数值，得到各组不同的结论，每组参数和结论都是一个方案。,【例5-3】某化工企业生产产品乙烯、丙烯、丁二烯，在2008年的销售额分别为400万元、600万元和300万元，销售成本分别为300万元、380万元和220万元。根据市场情况推测，2009年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如图5-17所示。,图5-17产品销售资料及预计增长率,3.1 建立方案,（1）设计方案计算分析，如图5-17所示，选择单元格H7并输入公式“=SUMPRODUCT(B

10、3:B5,1+H4:H6)-SUMPRODUCT(C3:C5,1+I4:I6)”。 （2）对不同的产品进行命名，以便分析。分别将单元格H4的名字为“乙烯销售额增长率”，单元格I4的名字为“乙烯销售成本增长率”，单元格H5的名字为“丙烯销售额增长率”，单元格I5的名字为“丙烯销售成本增长率”，单元格H6的名字为“丁二烯销售额增长率”，单元格I6的名字为“丁二烯销售成本增长率”，单元格H7的名字为“总销售利润”。,（3）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图5-18所示，单击【添加】按钮，系统弹出【添加方案】对话框，如图5-19所示。,图5-18【方案管理器】对话框

11、图5-19【添加方案】对话框,（4）在【添加方案】对话框中，【方案名】编辑框中输入“方案1好”，【可变单元格】编辑框中输入“$H$4:$I$6”，单击【确定】按钮，系统弹出【方案变量值】对话框，如图5-20所示。,图5-20【方案变量值】对话框,（5）在【方案变量值】对话框中输入每个可变单元格的值（这里要按行输入），完毕后单击【添加】按钮，系统会弹出如图5-19所示的【添加方案】对话框，对第2个方案进行输入；待所有方案输入完毕后，单击【方案变量值】对话框中的【确定】按钮，系统返回到【方案管理器】对话框，如图5-21所示。此时，可单击【关闭】按钮，回到工作表。,图5-21【方案管理器】对话框,3

12、.2 浏览方案,（1）打开原工作表，并激活工作表。 （2）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图5-21所示，选择要想查看的方案，单击【显示】按钮，则系统就自动显示出该方案的执行结果，如图5-17所示。,5.3.3 编辑方案,对做好的方案进行修改，只需在图5-21所示的【方案管理器】对话框中选中需要修改的方案，单击【编辑】按钮，系统弹出如图5-20所示的对话框，进行相应的修改即可。 若要删除某一方案，则在图5-21所示的【方案管理器】对话框中选中需要删除的方案，单击【删除】按钮。 若要增加方案，则在图5-21所示的【方案管理器】对话框中单击【添加】按钮，然后在图5

13、-19所示的对话框填写相关的项目。,3.4 建立方案摘要,（1）在图5-21所示的【方案管理器】对话框中单击【摘要】按钮，弹出【方案摘要】对话框，如图5-22所示，在【结果类型】中选择“方案摘要”项，在【结果单元格】中输入“H7”，然后单击【确定】按钮，则系统在当前工作簿中自动建立一个名为“方案总结”的工作表，如图5-23所示。,图5-22【方案总结】对话框,图5-23方案摘要,4 规划求解,在经济管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那就是：如何合理利用各种有限的资源实现最佳的经济效益，

14、也就是达到利润最大、成本最低、费用最省等目标。,规划求解具有如下三个特点：,（1）所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。 （2）总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。 （3）问题都有直接或间接影响约束条件的一组输入值。,4.1 建立规划求解模型,Excel规划求解问题的基本构成 （1）决策变量（variable） 一个或一组可变单元格，可变单元格称为决策变量，一组决策变量代表一个规划求解的方案 （2）目标函数

15、目标函数表示规划求解要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数 （3）约束条件 约束条件是实现目标的限制条件。,建立规划求解模型：,第1步建立求解工作表(输入原始数据及相应的各公式) 第2步设置求解参数 选择“工具” “规划求解”菜单，设置以下求解的各项参数： 设置目标单元格：输入目标函数所在单元格(为总余额单元格) 设置目标：最大值、最小值或值的数值(最大利润,即最大值) 设置可变单元格：它的确定决定结果（为生产数量） 设置约束条件：单击【添加】按钮输入约束条件按添加依次输入所有约束条件确定 第3步保存求解结果 在规划求解对话框中按“求解”在规划求解结果对话框中按“保存规

16、划求解结果”,4.2求解优化问题,【例5-4】某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成，含有以下成份：,特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。,求解过程： 步骤1：根据问题建立数学模型 因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为： x=每天混合饲料中玉米的重量（磅）y=每天混合饲料中大豆粉的重量（磅） 目标函数是使配制这种饲料的每天总成本最小，因此表示为： z=0.3x+0.9y,模型的约束条件是饲料的日需求量和对营养成份的需求量，具体表示为： x+y800 /每天所需饲料 0.09x+0.6y

17、0.3(x+y) /蛋白质 0.02x+0.06y0.05(x+y) /纤维 将上述不等式化简后，完整的模型为： min z=0.31+0.92 s.t. x+y800 0.21x-0.3y0 0.03x-0.01y0 x,y0,步骤2 建立工作表工作表设计如图5-23所示：,图5-23建立营养配方模型工作表,其中，输入数据的单元格使用了阴影格式，即B4:C7和F5:F7；变量和目标函数单元格为B12:D12；D4:D7中输入了约束公式到 D4=SUMPRODUCT(B4:C4,$B$12:$C$12) D5=SUMPRODUCT(B5:C5,$B$12:$C$12) D6= SUMPRODUCT(B6:C6,$B$12:$C$12) D7= SUMPRODUCT(B7:C7,$B$12:$C$12),步骤3 应用规划求解工具,单击【工具规划求解】，出现如图5-24所示的“规划求解参数”对话框，设计相应的参数。,图5-24规划求解参数设置,并且单击