Transcript 单变量模拟运算表
Excel高级教程 －Excel分析工具 Excel分析工具 • 假设分析是指观察改变单元格的数值对工作表 中公式所产生结果的影响。例如：当偿还期限 改变时对每月按揭额的影响。 • 利用Excel提供的假设分析功能（包括单变量 求解、模拟运算表，规划求解及方案等），并 结合Excel强大的函数库，可以方便轻松地完 成各种财务统计等工作。 Excel分析工具 • 单变量求解 • 模拟运算表 – 单变量模拟运算表 – 双变量模拟运算表 • 规划求解 – – – – 线形规划 整数规划 0-1规划 非线形规划 • 方案管理 单变量求解 • 如果已知公式的结果，而不知道公式的 变量值，那么可以用单变量求解来寻求 公式的特定解。进行单变量求解时， Excel通过一定的算法进行试算，直到找 到合适的解。 • 说白了，就是解单变量的方程。 单变量求解 • 单变量求解的具体操作如下： – 公式编辑。 – 单击“工具”菜单中的“单变量求解”命令。这时 将出现“单变量求解”对话框。 – 在“目标单元格”编辑框中，输入待求解的公式所 在的单元格的引用。 – 在“目标值”编辑框中，键入所需的结果。 – 在“可变单元格”编辑框中，输入待调整数值（变 量）所在单元格的引用。 – 单击确定可得到结果。 单变量求解 • 示例 解方程 8 x 44 100 • 在单元格A2中输入公式“＝A1*8+44”， A1作为存放变量x的单元格。 单变量求解 • 单击“工具”菜单中的“单变量求解”命令。 这时将出现“单变量求解”对话框。 • 在“目标单元格”编辑框中，输入A2。 • 在“目标值”编辑框中，键入100。 • 在“可变单元格”编辑框中，输入A1。 单变量求解 • 单击确定可得到结果。 单变量求解 • 这时，有同学就要问，这么简单的问题， 何必杀鸡用牛刀？ • 事实上，很多方程根本就无法人工解出来， 必须依靠计算机来解决。这时Excel就是 一个很好的工具。 • 比如 就够你受的了。更 别说更复杂的了。利用Excel很快就可以 计算出x＝5.739552102 模拟运算表 • 模拟运算表是工作表中的一个单元格区域， 它可以显示公式中某些值的变化对计算结果 的影响。模拟运算表为同时求解某一运算中 所有可能的变化值提供了捷径，并且，它还 可以将所有不同的计算结果同时显示在工作 表中，便于查看和比较。 模拟运算表 • 单变量模拟运算表 • 单变量模拟运算表的结构特点是，其输入数值 被排列在一列中（列引用）或一行中（行引 用）。单变量模拟运算表中使用的公式必须引 用输入单元格。存放在输入单元格中的输入数 据清单将被替换。工作表中的任何单元格都可 以用作输入单元格。虽然输入单元格不必是模 拟运算表的一部分，但模拟运算表中的公式必 须引用输入单元格。 模拟运算表 • 单变量模拟运算表 • 创建单变量模拟运算表，具体操作如下： – 在一行或者一列中输入要替换工作表上的输入单元 格的数值序列； – 如果输入数值被排成一列（行），则在第一个数值 的上一行（左边一列）且处于数值序列右侧（下方） 的单元格中输入所需的公式。 – 选定包含公式和需要被替换的数值的单元格区域。 – 在“数据”菜单中，单击“模拟运算表”命令； – 如果模拟运算表是列方向的，请在“输入引用列的 单元格”编辑框中，为输入单元格键入引用。如果 是行，相应做即可。 模拟运算表 • 单变量模拟运算表 下面我们通过绘制y＝sin x在[0,2Pi]的曲 线来说明单变量模拟运算表的操作。 绘制曲线，首先要算出函数在各个点的取 值，然后画出各个点的散点图。 模拟运算表 • 单变量模拟运算表 1、如下图准备数据 2、选中区域C3：D20 模拟运算表 • 单变量模拟运算表 4、单击“确定“，得到如下结 3、单击“数据”－ 果。 “模拟运算表”，弹 出如下对话框，如图 示设置列引用。 模拟运算表 • 单变量模拟运算表 5、用图表向导，作出散点图。 模拟运算表 • 双变量模拟运算表 • 双变量模拟运算表与单变量模拟运算表 的区别在于前者使用两个可变单元格。 双变量模拟运算表中的两组输入数值使 用同一个公式。这个公式必须使用两个 不同的输入单元格。 模拟运算表 • 双变量模拟运算表 • 创建双变量模拟运算表的具体操作如下： – 在工作表的某个单元格中，输入所需的至少引用两 个单元格的公式； – 在公式的下面同一列中键入一组待替换的变量序列， 在公式右边同一行键入待替换的变量序列； – 选中包含公式以及数据行和列的单元格区域； – 在“数据”菜单中，单击“模拟运算表…”命令； – 在“输入引用行的单元格”编辑框中，输入要由行 变量序列替换的输入单元格的引用； – 在“输入引用列的单元格”编辑框中，输入要由列 变量序列替换的输入单元格的引用； – 单击“确定”得到所需的结果。 模拟运算表 • 双变量模拟运算表 • 下面，我们通过制作一个乘法口诀表来说 明双变量模拟运算表的操作过程。 1、按照如下格式准备数据 模拟运算表 • 双变量模拟运算表 2、在单元格C3中输入“=B4 & "×" & B5 & "=" & B4*B5” 模拟运算表 • 双变量模拟运算表 3、选中区域C3：L12 模拟运算表 • 双变量模拟运算表 4、单击“数据”－“模 拟运算表”，弹出如 下对话框，如图示设 置行引用和列引用。 注意：这里行引用和列引用 不能是上一步选择的区域 中的任何一个单元格，否 则将出错。单变量模拟运 算表则无此限。 模拟运算表 • 双变量模拟运算表 5、单击“确定”，得到一种漂亮的乘法口诀 表。 模拟运算表 • 模拟运算表的应用实例 分期付款（按揭）是当代比较流行的支 付方式，下面我们将同过两个实例来说 明单变量模拟运算表和双变量模拟运算 表的应用。 模拟运算表 • 示例一 某人贷款10万元，欲分4年（48月）偿 还，试求贷款按揭利率在8％～13％之 间变化时，每月应等额偿还多少钱？ 模拟运算表 • • • • • • PMT(rate,nper,pv,fv,type) 有关函数 PMT 中参数的详细说明，请参阅函数 PV。 Rate 贷款利率。 Nper 该项贷款的付款总数。 Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。 Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果 省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。 • Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。 • 如，贷款10万，分48月还，年利率为8％，每月月初应还的数额 为pmt（0.08/12，48，100000，0，0） 模拟运算表 • 从该表我们可以看出， 当年利率8％～13％之 间变化时，每月的偿还 额从￥2411上升到 ￥2682。这就为想在4 年内支付完一辆现在价 格为10万元的汽车的消 费者提供一个支付能力 的参考。 模拟运算表 • 示例二 贷款10万元，当贷款年利率在8％～13 ％之间变化时，在各种利率下，还款期 限为5、10、15、20年时所对应的每月 偿还额是多少？ 模拟运算表 • 运算结果表明，每 月的偿还额从2500 到6825之间变化， 年利率越高，月偿 还额越大，期限越 长，月偿还额月小。 如果预期的月薪不 到2500，就别奢望 拥有现价为30万的 房子了! 规划求解 • 运筹学是经济管理过程中常用的工具之一。经 济管理往往被一定的运筹模型来表达，但是人 工求解比较困难，特别对于变量个数比较多的 规划模型，人工求解简直就是痴心妄想!于是， 人们就开始寻求机械的解法，特别是运用计算 机来求解。现在已经有很多软件可以实现规划 求解，如SAS、Malab、Excel。今天，我们 介绍一下如何用Excel来求解运筹学中的规划 问题。能够解决的规划问题包括：线性规划、 目标规划、整数规划、非线性规划。 规划求解 Max(min)Z=C1X1+ C2X2+…+CnXn a11X1+ a12X2+…+ a1nXn (=, )b1 a21X1+ a22X2+…+ a2nXn (=, )b2 … … … am1X1+ am2X2+…+ amnXn (=, )bm Xj 0(j=1,…,n) 规划求解 • 第一步，按照下 面的格式，把目 标函数、约束条 件的两边分别用 n个变量的表达 式或以这些变量 为参数的函数表 示。 规划求解 • 单击工具－加载宏，弹出下面的对话框，把规划求解选 项钩上，单击确定。（这一步只是在第一次使用的时候 需要，目的是把规划求解的模块加进来。） • 单击工具－规划求解…，弹出对话框，把目标函数、最 大或者最小、约束条件设置好，就可以求解了。 “规划求解参数”对话框上各选项说 明 选项 说明 目标单元格 在此指定目标单元格，经求解后获得最大值、最小值或者某一特 定数值。这个单元格必须包含公式 等于 在此指定是否需要对目标单元格求取最大值、最小值或者某一特 定数值。如果需要指定数值，请在右侧编辑框中键入 可变单元格 在此指定可变单元格。求解时其中的值不断调整，直到满足约束 条件，并且“目标单元格”编辑框中指定的单元格达到目标 值。可变单元格必须直接或间接与目标单元格相联系 推测 单击此钮，自动定位“目标单元格”编辑框中公式引用的所有非 公式单元格，并在“可变单元格”编辑框中输入其引用 约束 在此列出当前的所有约束条件 添加 显示“添加约束”对话框 更改 显示“改变约束”对话框 “规划求解参数”对话框上各选项说明（续） 选项 说明 删除 删除选定的约束条件 求解 对定义好的问题进行求解 关闭 关闭对话框，不进行规划求解。但保留通过“选项”、 “添加”、“更改”或“删除”按钮所作的修改 选项 显示“规划求解选项”对话框。在其中装入或保留规划求 解模型，并对求解运算的高级属性进行设定 全部重设 清除规划求解中的当前设置，将所有的设置恢复为初始值 设置“规划求解”选项 设置“规划求解”选项 选 项 说 明 最长运算时间 在此设定求解过程的时间，可输入的最大值为32767秒，默认值 为100秒可以满足大多数小型规划求解要求 迭代次数 在此设定求解过程中迭代运算的次数，限制求解过程的时间。可 输入的最大时间为32767，默认值为100次可满足大多数小型 规划求解要求 精度 在此输入用于控制求解精度的数字，以确定约束条件单元格中的 数值是否满足目标值或上下限。精度值必须为小数（0-1之 间），输入数字的小数位越少，精度越低。精度越高，求解 时间越长。 允许误差 在此输入满足整数约束条件的目标单元格求解结果与最佳结果间 的允许百分偏差。这个选项只应用于具有整数约束条件的问 题。设置的允许误差值越大，求解过程就越快。 收敛度 在此输入收敛度数值，当最近五次迭代时，目标单元格中数值的 变化小于“收敛度”编辑框中设置的数值时，“规划求解” 停止运行。收敛度只应用于非线性规划问题，并且必须由一 个在 0（零） 和 1 之间的小数表示。设置的数值越小，收 敛度就越高。例如，0.0001 表示比 0.01 更小的相对差别。 收敛度越小，“规划求解”得到结果所需的时间就越长。 设置“规划求解”选项（续1） 选 项 搜索 说 明 指定每次的迭代算法，以确定搜索方向。 牛顿法 用准牛顿法迭代需要的内存比共轭法多，但所需的迭 代次数少。 共轭法 比牛顿法需要的内存少，但要达到指定精度需要较多 次的迭代运算。当问题较大或内存有限，或单步迭 代进程序缓慢时，用此选项。 装入模型 显示“装入模型”对话框，输入对所要调入模型的引 用。 保存模型 显示“保存模型”对话框，输入模型的保存位置。只 有当需要在工作表上保存多个模型时，单击此命令。 第一个模型会自动存储。 设置“规划求解”选项（续2） 选 项 正切函数 说 使用正切向量线性外推。 明 二次方程 用二次函数外推法，提高非线性规划问题的计算精 度。 导数 指定用于估计目标函数和约束函数偏导数的差分方 案。 向前差分 用于大多数约束条件数值变化相对缓慢的问题。 中心差分 用于约束条件变化迅速，特别是接近限定值的问题。 虽然此选项要求更多的计算，但在“规划求解” 不能返回有效解时也许会有帮助。 设置“规划求解”选项（续3） 选 项 说 明 采用线性模型 当模型中的所有关系都是线性的，并且希望解决线 性优化问题时，选中此复选框可加速求解进程 显示迭代结果 如果选中此复选框，每进行一次迭代后都将中断 “规划求解”，并显示当前的迭代结果。 自动按比例缩 当输入和输出值数量差别很大时，可以使用此功能。 放 例如，对一项百万美元投资的盈利百分比进行放 大。 假定非负 对于在“添加约束”对话框的“约束值”编辑框中 没有设置下限的可变单元格，假定其下限为 0 （零）。 估计 指定在每个一维搜索中用来得到基本变量初始估值 的逼近方案。 “规划求解”完成信息 • 当“规划求解”得到答案时，它将在“规划 求解结果”对话框中显示下述两条信息之一： – “规化求解”找到一个解，可满足所有的约束及 最优化要求。 这表明按照“规划求解选项”对话框中设置的 精度，所有约束条件都已满足，并且只要有可能， 目标单元格中将得到极大值或极小值。 – “规划求解”收敛于当前结果，并满足全部约束 条件。 这表明目标单元格中的数值在最近五次求解过 程中的变化量小于“规划求解选项”对话框中 “收敛度”设置的值。“收敛度”中设置的值越 小，“规划求解”在计算时就会越精细，但求解 过程将花费更多的时间。 “规划求解”完成信息 • 当规划求解不能得到最佳结果时，在“规划求 解结果”对话框中显示下述信息之一： – 满足所有约束条件。“规划求解”不能进一步优化 结果。 这表明仅得到近似值，迭代过程无法得到比显示结 果更精确的数值：或是无法进一步提高精度，或是 精度值设置得太小，请在“规划求解选项”对话框 中试着设置较大的精度值，再运行一次。 “规划求解”完成信息 – 求解达到最长运算时间后停止。 这表明在达到最长运算时间限制时，没有 得到满意的结果。如果要保存当前结果并节 省下次计算的时间，请单击“保存规划求解” 或“保存方案”选项。 “规划求解”完成信息 – 求解达到最大迭代次数后停止。 这表明在达到最大迭代次数时，没有得到满意的结 果。增加迭代次数也许有用，但是应该先检查结果 数值来确定问题的原因。如果要保存当前值并节省 下次计算的时间，请单击“保存规划求解”或“保 存方案”选项。 – 目标单元格中数值不收敛。 这表明即使满足全部约束条件，目标单元格数值也 只是有增有减但不收敛。这可能是在设置问题时忽 略了一项或多项约束条件。请检查工作表中的当前 值，确定数值发散的原因，并且检查约束条件，然 后再次求解。 “规划求解”完成信息 – “规划求解”未找到合适结果。 这表明在满足约束条件和精度要求的条件 下，“规划求解”无法得到合理的结果，这 可能是约束条件不一致所致。请检查约束条 件公式或类型选择是否有误。 “规划求解”完成信息 – “规划求解”应用户要求而中止。 这表明在暂停求解过程之后，或在单步执行规划 求解时，单击了“显示中间结果”对话框中的“停 止”按钮。 – 无法满足设定的“采用线性模型”条件。 这表明求解时选中了“采用线性模型”复选框， 但是最后计算结果并不满足线性模型。计算结果对 工作表中的公式无效。如果要验证问题是否为非线 性的，请选定“自动按比例缩放”复选框，然后再 运行一次。如果又一次出现同样信息，请清除“采 用线性模型”复选框，再运行一次。 “规划求解”完成信息 – “规划求解”在目标或约束条件单元格中发现错误 值。 这表明在最近的一次运算中，一个或多个公式的 运算结果有误。请找到包含错误值的目标单元格或 约束条件单元格，修改其中的公式或内容，以得到 合理的运算结果。 还有可能是在“添加约束”或“改变约束”对话 框中键入了无效的名称或公式，或者在“约束”编 辑框中直接键入了“integer”或“binary”。如果要将 数值约束为整数，请在比较操作符列表中单击 “Int”。如果要将数值约束为二进制数，请单击 “Bin”。 “规划求解”完成信息 – 内存不够。 Microsoft Excel 无法获得规划求解所需的内 存。请关闭一些文件或应用程序再试一次。 – 其它的 Excel 例程正在使用 SOLVER.DLL。 请表明有多个 Microsoft Excel 任务在运行， 其中一个任务正在使用 SOLVER.DLL。 SOLVER.DLL 同时只能供一个任务使用。 关于报告 • 创建指定类型的报告，并将每份报告存放到工作簿中单独的一张 工作表上。 • 运算结果报告 列出目标单元格和可变单元格以及它们的初始值、 最终结果、约束条件和有关约束条件的信息。 • 敏感性报告 在“规划求解参数”对话框的“目标单元格”编辑 框中所指定的公式的微小变化，以及约束条件的微小变化对求解 结果都会有一定的影响。此报告提供关于求解结果对这些微小变 化的敏感性的信息。含有整数约束条件的模型不能生成本报告。 对于非线性模型，此报告提供缩减梯度和拉格朗日乘数；对于线 性模型，此报告中将包含缩减成本、影子价格（机会成本）、目 标系数(允许有小量增减额)以及右侧约束区域。 • 限制区域报告 列出目标单元格和可变单元格以及它们的数值、 上下限和目标值。含有整数约束条件的模型不能生成本报告。下 限是在满足约束条件和保持其它可变单元格数值不变的情况下， 某个可变单元格可以取到的最小值。上限是在这种情况下可以取 到的最大值。 规划求解实例 • 下面，我们通过实例来演示规划求解的 强大求解功能，我们将通过4个例子来说 明如何利用规划求解功能求解线性规划、 整数规划、0-1规划、非线性规划问题。 规划求解 • 一个副产品 思考，能否用规划求解功能来解方程 组呢？ 规划求解 • 答案是可以的。 • 因为所谓方程组有解，必须是n个未知数n个方 程，而这n个方程又构成了线性规划的n个约束 条件，而且他们必有唯一解。此时，可随便定 义一个目标函数，而在变量唯一的情况下，目 标函数的最大与最小都是一样的，因为变量值 别无选择。 • 下面通过一例子以明之。 方案 • 方案是用于预测工作表模型结果的一组 数值。可以在工作表中创建并保存多组 不同的数值，并且可以在这些新方案之 间任意切换，查看不同的方案结果。例 如：如果需要建立一份预算报表，但尚 不能确定具体的产品成本，可以先假定 几组不同的成本值，然后通过在方案之 间切换进行假设分析。 方案 创建方案 1.在“工具”菜单中，单击“方案”命令。 2.单击“添加”按钮。 3.在“方案名”编辑框中，键入方案名称。 4.在“可变单元格”编辑框中，输入对需要反复修改的单元格的引 用。 5.在“保护”下，选择所需选项。 6.单击“确定”按钮。 7.在“方案变量值”对话框中，键入可变单元格所需的数值。 8.如果要创建方案，请单击“确定”按钮。 9.如果要创建多个方案，请单击“添加”按钮，然后重复步骤 3 到 步骤 7。完成方案创建后，请单击“确定”按钮，接着再单击 “方案管理器”对话框中的“关闭”按钮。 方案 • 提示 要想在更改的单元格中保留原始 值，请在创建要修改原始值的方案前先 创建使用原始单元格值的方案。 方案 • 实例 某产品G的原材料为A， 从A到G要经过三次加工， 其可能的工序如下图所示。 每道工序都需要一定的加 工费，加工费 为 时长 单价。已知一次 加工的单价为2.5，二次 加工的时长都为3，三次 加工的单价为5。 现在想从这些可能的工序中 选择最优的方案。 3 i 1 i 方案 • 准备数据 方案 • 在“工具” 菜单中，单 击“方案” 命令，弹出 如下的“方 案管理器” 对话框； 方案 • 单击“添加”按 钮，在“方案名” 编辑框中，键入 方案名称，在 “可变单元格” 编辑框中，输入 对需要反复修改 的单元格的引用， 在“保护”下， 选择所需选项； 方案 • 单击“确定”按 钮，在“方案变 量值”对话框中， 键入可变单元格 所需的数值；如 果要总结方案， 请单击“确定”， 否则一直重复本 步操作。 方案 • 所有方案都输入完，就可以进行总结了。 方案 • 选择好目标 结果单元格， 单击“确定” 就可以得到 如下的方案 总结报告。 从该总结报告中，我们可以看到，方案“ADFG”为 加工费最低的方案。故工艺流程应为A->D->F->G。 方案 • 编辑方案 如果在对方案进行修改后，没有改变它原来的名称。 则修改后的可变单元格中的新值将替换原来方案中 的值。 – 在“工具”菜单中，单击“方案”命令。 – 单击待编辑方案的名称，然后单击“编辑”按钮。 – 进行相应的修改。 – 在“方案变量值”对话框中，键入可变单元格所需 的数值。 – 如果要保存所做的修改，可单击“确定”按钮。 – 如果要返回“方案管理器”对话框而不改变当前方 案，可单击“取消”按钮。 方案 • 显示方案 当显示方案时，被存为方案的一部分的单元 格的值将被修改。 – 在“工具”菜单中，单击“方案”命令。 – 单击待显示方案的名称。 – 单击“显示”按钮。 方案 • • – – – – 创建方案汇总报告 在“工具”菜单中，单击“方案”命令。 单击“总结”按钮。 单击“方案总结”或“方案数据透视表”选项。 在“结果单元格”编辑框中，输入单元格的引用， 该单元格引用包含方案修改结果的单元格。多个 引用需用逗号分隔开。 注意 在生成方案总结报告时不一定需要结 果单元格，而在生成方案数据透视表报表时 则一定需要。 方案 • 保护方案 – – – – – – – – – 在“工具”菜单中，单击“方案”命令。 在“方案”框中单击方案的名称。 单击“编辑”按钮。 如果要防止他人修改您的方案，可选中“防止更改”复选 框。 如果要从“方案管理器”对话框的列表中取消方案，可选 中“隐藏”复选框。 单击“确定”按钮。 在“方案变量值”对话框中，单击“确定”按钮，再单击 “关闭”按钮。 在“工具”菜单中，指向“保护”子菜单，然后单击“保 护工作表”命令。 确认选中了“方案”复选框。 方案 • 注意 可以在工作表受保护时添加方案， 但是在清除“编辑方案”对话框中的 “防止更改”复选框之前，不能编辑或 删除已有方案。如果单元格未被锁定， 仍可以直接在工作表中编辑可变单元格 中的数值。 方案 • 撤消保护 – 请切换到需要恢复为可以完全访问的工作表。 – 用鼠标指向“工具”菜单中的“保护”，然后单击 其中的“撤消工作表保护”命令。 – 如果需要输入密码，请键入工作表的保护密码。请 注意：密码是区分大小写的，因此，一定要严格按 照当初创建的格式输入密码，包括其中的字母大小 写格式。 方案 • 删除方案 • 在“工具”菜单中，单击“方案”命令。 • 单击待删除方案的名称，然后单击“删 除”按钮。