Excel 创建单变量模拟运算表
Excel 创建单变量模拟运算表
单变量模拟运算主要用于银行信贷方面，根据年利率的不同，来计算每期偿还付款金额。
单变量模拟运算表的结构特点是，所有的输入数值被排列在一列中（称为“列引用”）或一行中（称为“行引用”）。
虽然输入单元格不必是模拟运算表的一部分，但模拟运算表中用到的公式必须引用输入单元格。
单变量模拟运算表可以对一个变量输入不同的值来查看其对一个或者多个公式的影响。
如果已知公式的预期结果，而未知使公式返回结果的某个变量的数值，就可以使用单变量求解功能。
下面以分期付款为例进行介绍，其中，相关的公式介绍如下：
首付款＝付款总数×首付比例
月供款＝（付款总数－首付款）×（1＋贷款利率）÷月数
在Excel 2007中用户可以通过PMT 函数来完成每期付款的金额计算。
其中，PMT 函数的格式为：PMT(rate,nper,pv,fv,type)。
有关函数 PMT 中参数的详细说明如下。
● Rate 贷款利率。
● Nper 该项贷款的付款总数。
● Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。
● Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv ，则假设其值为零，也就是一笔贷款的未来值为零。
● Type 用数字0或1表示，指定各期的付款时间是在期初还是期末。
例如，在工作表中输入相应的贷款数据信息，并在C8单元格中输入“=PMT(C5/12,C4*12,C3)”函数公式。
然后，选择B8至C15单元格区域，并选择【数据】选项卡，在【数据工具】组中单击【假设分析】下拉按钮，执行【数据表】命令，如图8-1所示。
图8-1 执行【数据表】命令 图8-Excel 单变量模拟运算表
在弹出的【数据表】对话框的【输入引用列的单元格】文本框内输入“$C$5”单元格，即可得到不同利率情况下，每期付款的金额，如图8-2所示。
得到单变量模拟运算的结果后，用户可以从中发现该模拟运算的特点是：输入的数值被排列在一列中或者一行中，而且运算表中使用的公式必须使用输入单元格。
其中，输入单元格是指存放在该单元格中的输入数据清单将被替换的单元格。
在【数据表】对话框中，包含两种类型的引用单元格，其功能如下。
● 输入引用行的单元格
如果数据表是行方向的，需要在【数据表】对话框的【输入引用行方向的单元格】文本框内输入引用单元格地址。
● 输入引用列的单元格 如果数据表是列方向的，需要在【数据表】对话框的【输入引用列的单元格】文本框内输入引用单元输入 执行 执行
每期付
款金额
格地址。
如何在excel表格中制作单变量模拟运算表
如何在excel表格中制作单变量模拟运算表
模拟分析是更改单元格中的值以查看这些更改对⼯作表上的公式结果有何影响的过程。
例如，您可以使⽤数据表改变贷款的利率和术语长度，以评估潜在的每⽉付款⾦额。
⽐如当你想在不同利率的情况下，所需付的不同款。
就可以将利率设置为变量值，来观察其对应的付款。
1.单变量模拟运算
1.1 以c47-c51的列表为变量值时
如上图，选中上图的灰显部分。
点击数据-模拟分析-模拟运算表
如果选中的列表值是竖型的⼀列，则在输⼊引⽤列的单元值⾥填写变量1所在的单元格位置。
D46:D51则就是输出后的值
1.2 以c47-c51的⾏表为变量值时
选中以下值，点击数据-模拟分析-模拟运算表，
如果选中的表值是横型的⼀列，则在输⼊引⽤⾏的单元值⾥填写变量1所在的单元格位置。
点确定后，就会在DE:DH输出计算后的值。
2.双变量模拟运算
在公式所在单元格的 x,y轴分别为变量1和变量2的值。
Excel中如何运用模拟运算表
模拟运算表模拟运算表是假设公式中的变量有一组替换值，代入公式取得一组结果值时使用的。
这组结果值可以构成一个模拟运算表。
模拟运算表有两种类型：●单变量模拟运算表：输入一个变量的不同替换值，并显示此变量对一个或多个公式的影响。
●双变量模拟运算表：输入两个变量的不同替换值，并显示这两个变量对一个公式的影响。
一、单变量模拟运算表当对公式中的一个变量以不同值替换时，该过程将生成一个显示其结果的数据表格。
我们既可使用面向列的模拟运算表，也可使用面向行的模拟运算表。
以下是单变量模拟运算表的排列方式之一：例如，用户想购买一套房子，要承担一笔30万元的抵押贷款，在这之前想看看不同利率下每月应偿还的贷款金额。
其中，在单元格E2中输入以下公式（其中，在B1前面输入一个负号，是为了在单元格得到正值）：=PMT（B3/12，B2*12，-B1）其结果为“3483.25，当贷款30万元进，如果年利率为7%，贷款年数为10年，每个月应偿还3483.25元。
如果要建立单变量模拟运算表，可以按照下述步骤进行：1、在一行或一列中，输入要替换工作表上的输入单元格的数值序列。
例如，在D3：D9分别输入了不同的利率，如图1所示。
图1 建立需要分析的的工作表2、如果输入的数值被排成一列，请在第一个数值的上一行且处于数值右侧的单元格中输入要用的公式，在同一行中，第一个公式的右边分别输入其他公式。
3、如果输入的数值被排成一行，请在第一个数值左边一列且处于数值列下方的单元格输入所需要的公式，在同一列中，在第一个公式的下方分别输入其他公式。
例如，本例在单元格D2中输入公式“=PMT（B3/12，B2*12，-B1）”。
4、选定包含公式和被替换数值的单元格区域。
例如，选定区域D2：E9。
5、选择“数据”菜单中的“模拟运算表”命令，出现如图2所示的“模拟运算表”对话框。
图2 “模拟运算表”对话框6、如果模拟运算表是列方向的，则单击“输入引用列的单元格”编辑框；如果模拟运算表是行方向的，则单击“输入引用行的单元格”编辑框，然后在工作中选定单元格。
单变量模拟运算表
规划求解
• 单击工具－加载宏，弹出下面的对话框，把规划求解选 项钩上，单击确定。（这一步只是在第一次使用的时候 需要，目的是把规划求解的模块加进来。） • 单击工具－规划求解…，弹出对话框，把目标函数、最 大或者最小、约束条件设置好，就可以求解了。
精度
允许误差
收敛度
设置“规划求解”选项（续1）
选 项 搜索 牛顿法 共轭法 说 明 指定每次的迭代算法，以确定搜索方向。 用准牛顿法迭代需要的内存比共轭法多，但所需的迭 代次数少。 比牛顿法需要的内存少，但要达到指定精度需要较多 次的迭代运算。当问题较大或内存有限，或单步迭 代进程序缓慢时，用此选项。 显示“装入模型”对话框，输入对所要调入模型的引 用。
规划求解
Max(min)Z=C1X1+ C2X2+…+CnXn
a11X1+ a12X2+…+ a1nXn (=, )b1 a21X1+ a22X2+…+ a2nXn (=, )b2 … … …
am1X1+ am2X2+…+ amnXn (=, )bm
Xj 0(j=1,…,n)
规划求解
• • • • • • 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）
模拟运算表数字格式
模拟运算表数字格式
模拟运算表是一种用于显示一个或两个变量如何改变另一个变量的影响的工具。
在Excel中，模拟运算表可以通过“数据”菜单中的“模拟运算表”命令来创建。
模拟运算表的数字格式可以根据需要进行设置。
在Excel中，可以使用“单元格格式”对话框来设置数字格式。
具体步骤如下：
1. 选择要设置格式的单元格或列。
2. 点击鼠标右键，选择“单元格格式”选项。
3. 在“单元格格式”对话框中，选择“数字”选项卡。
4. 在“分类”列表中选择所需的数字格式，例如“常规”、“数值”、“货币”、“日期”等。
5. 单击“确定”按钮，应用数字格式。
此外，还可以使用自定义数字格式来设置模拟运算表的数字格式。
自定义数字格式可以使用Excel内置的数字格式代码和一些自定义代码来创建。
例如，以下是一个自定义数字格式代码示例：
`%`
这将使数字显示为百分比格式，并保留两位小数。
总之，模拟运算表的数字格式可以根据需要进行设置，可以使用Excel内置的数字格式选项或自定义数字格式代码来创建所需的格式。
excel如何创建单变量模拟运算表
excel如何创建单变量模拟运算表
模拟运算表分为单变量模拟运算表和双变量模拟原算表两种类型，创建模拟运算表后在一些数据的统计上就比较简单了。
这里先介绍单变量模拟运算表的创建，具体的操作步骤如下。
①创建如下图所示的新的工作表，其中在B3和D6单元格中输入公式“＝$B$1*$B$2”。
②选中要进行模拟运算的区域C6:D17。
③单击【数据】|【模拟运算表】菜单项打开【模拟运算表】对话框，单击【输入引用列的单元格】右边【拾取器】按钮，在工作表中选中单元格B2，再次单击【拾取器】按钮返回到【模拟运算表】对话框中。
④单击确定按钮，则工作表中的模拟区域内根据所模拟的运算进行了填充。
EXCEL模拟运算
• 5.结果：
二、双变量模拟运算
• 某人贷款300000，想了解在不同的利率和 不同的偿还年限下每个应还款情况。假设 利率分别为： 6.84%、6.04%、7.11%、 7.20%、7.38%、7.56%、7.83% 。还款年 限分别10年、15年、20年、25年、30年。
第一步：在B4中输入PMT函数。 第二步：选中整个数据区域B4:G11 第三步：使用模拟求解，在行区域中输入E1,列 区域中输入C1.
模拟运算
根据输入求出输出。模拟运算可以有 一个输入变量也可以有多个。如： y=f(x),x作为变量，求y的值，是单变 量输入。z=f(x,y),x,y作为变量，z为求 的值，是双变量输入。
一、单变量模拟运算
• 假定某人为购房贷款30万元，分20年还清； 现想了解在不同利率下每月应还贷款。
PMT函数：分期付款函数 • 格式：PMT(利率，还款期数，贷款额 [， 未来值，付款时间 ]） • 说明：
– 未来值：指最后一次付款后希望得到的现金余 额 ，缺省为0。 – 付款时间用0或1表示，0表能：基于固定利率及等额分期付款方式， 返回贷款的每期付款额。
• 假定贷款年利率为7.83%，贷款总额30万， 分20年还清。则： • PMT(7.83% / 12, 20*12，300000） • 结果为：￥-2477.66
• 根据题意，要求在不同利率下求每月还款 额，则利率是变化的。假定利率分别是： 6.84%、6.04%、7.11%、7.20%、7.38%、 7.56%、7.83% 。 • 步骤： • 1.建立框架：
• 2.输入公式：
• 3.选择包括公式和需要保存计算结果的单元格区 域。
• 4. [数据] →[模拟运算表] ，弹出模拟运算表 对话框：“输入引用行的单元格”和“输 入引用行的单元格”分别表示引用单元格 是行还是列，只能选择一个。则在列单元 格中输入$a$5
Excel单变量与规划求解模拟运算表
求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用
最低。
3
数学模型
根据以上资料，设四种原料的配比比例分别为X1、X2、X3、X4， 可列线性规划模型如下： 25%*X1＋8%*X2＋20%*X3＋7%*X4≥15%（蛋白质约束条件） 2%*X1＋1%*X2＋8%*X3＋5%*X4≥4.5%(脂肪约束条件) 10%*X1＋5%*X2＋1%*X3＋40%*X4≥30%（淀粉约束条件） 2%*X1＋40%*X2＋0.5%*X3＋6%*X4≤10%（纤维素约束条 件） X1≥0，X2≥0，X3≥0，X4≥0（非负条件） Z=500X1＋50X2＋350X3＋450X4（目标函数值最小） EXCEL操作演示
6
四、模拟运算表
Excel作为一个电子表格其作用不仅仅是数据的电子化存储及排序和检 索，它还有另外一项很重要的功能，那就是数据分析功能，这里用 得最多的就是模拟运算表。 例如：要制作一个数据分析表，要求显示公式中某个值的变化将如何影 响公式的结果。模拟运算表提供了一种快捷手段，它可以通过一步操作 计算出多种情况下的值；同时它还是一种有效的方法，可以查看和比 较由工作表中不同变化所引起的各种结果。 例.银行存款年利率为5%,存入10000元,存10年后本息合计多少元?
有15％，B（脂肪）至少含有4.5％，C（淀粉）至少含有30％，D（纤维素）不得
超过10％。所能提供的原料有四种，甲（花生饼），每吨单价500元，含有各种 营养成份分别为A25％、B2％、C10％、D2％；乙原料（花生秧），每吨单价为50
Excel数据处理与分析实例教程(微课版第3版第11章(分析数据)
注意： 如果方案比较简单，一般情况下可以选择“方案摘要”类型；如果方
案比较复杂多样，或者需要对方案报告的结果做进一步分析，可以选 择“方案数据透视表”
规划求解
11.4 规划求解
• 规划求解的特征 • 决策变量 • 约束条件 • 目标函数
11.4 规划求解
• 规划求解的特征 • 决策变量 • 约束条件 • 目标函数
11.4.1 安装规划求解工具
• 规划求解是Excel的一个加载项，一般安装时默认不加载规划求 解工具
• 如果需要使用规划求解工具，必须手工先进行加载
注意： 在录制宏的过程中，如果出现操作错误，那么对错误的修改操作也将
记录在宏中。因此在记录或编写宏之前，应事先制订计划，确定宏所 要执行的步骤和命令
注意： 在创建方案前先将相关的单元格定义为易于理解的名称，可以在后续
的创建方案过程中简化操作，也可以让将来生成的方案摘要更具可读 性。这一步不是必需的，但却是非常有意义的
11.3.2 创建方案
• 创建方案是方案分析的关键，应根据实际问题的需要和可行性 来创建各个方案
• 创建方案后，可以在“方案管理器”对话框的“方案”列表中， 选定某一方案，单击“显示”按钮，来查看这个方案对利润总 额的影响
每个规划问题都有一组需要求解的未知数 （x1,x2,…,xn），称为“决策变量”
这组决策变量的一组确定值就代表一个具体 的规划方案
11.4 规划求解
• 规划求解的特征 • 决策变量 • 约束条件 • 目标函数
对于规划问题的决策变量通常都有一定的限 制条件，称为“约束条件”
约束条件通常用包含决策变量的不等式或等 式来表示
11.4 规划求解
• 规划求解的特征 • 决策变量 • 约束条件 • 目标函数
EXCEL模拟运算表
EXCEL模拟运算表例某人贷款购车，车价20万元，规定年利率为5.5%，24个月还清。
计算购车人的月还款额。
首先在工作表中建立如图7-37所示的计算模型。
其中前三项数据都是常数。
月付款额用公式：“PMT（利率，期数，-车价）”计算，结果为月付￥8 819.13元。
注意其中年利率与月利率的区别。
现在，如果购车人或银行希望了解不同的利率变化时月付金额的相应变化情况，就可以使用Excel 2000中的一个很有用的分析工具：模拟运算表。
1. 单变量模拟运算表在工作表的一块空白区输入两个利率值4.5%，4.6%，选择这两个单元格后，拖住选择区右下角的复制柄向下拖曳（至利率6.0%为止）。
在紧邻利率的右侧一列，起始利率的上面一行（图中的E2单元格）输入：“=B5”，于是E2中也出现了B5中的值，并且将会随着B5的变化而改变。
1）选择单元格区域D2:E18，把利率系列及上方的公式单元格（E2）全部包括在内。
2）打开“数据”菜单，选择“模拟运算”命令。
3）在弹出的对话框中单击“输入引用列的单元格”文本框，使文本插入符出现在该文本框中。
输入$B（或直接点选B3单元格）后退出（这一步的含义是指出将要引用“利率”列的单元格是$B）。
这时，在利率的右侧一列上已自动计算出对应所有不同利率的月付金额了。
可以看到，随着利率的升高，月付金额值也会有所提高（见图7-38中的单变量模拟运算表）。
2. 双变量模拟运算表上例是单变量的模拟运算。
还可以做双变量的模拟运算。
假设大家所关心的不仅是利率变化带来的影响，还关心还贷期限长短对月付金额大小产生的影响，那么可以把上面的模拟运算表改造一下。
如图7-39所示。
1）12，24，36，48等不同的期数放在表上方的行中。
2）最左上角单元格D2中引入计算公式（或将公式简化为“=B5”也可）。
3）选中整个区域后，打开“数据”菜单，选择“模拟运算”命令。
4）在“输入引用行的单元格”文本框中添入单元格地址B4（期数），在“输入引用列的单元格”文本框中添入单元格地址B3（利率）。
Excel_模拟运算表使用方法
Excel 模拟运算表使用方法大家平常都只用函数公式做运算,相信很少人用过模拟运算.现对模拟运做一实例分析,让大家对之有初步认识.在工作表中输入公式后，可进行假设分析.查看当改变公式中的某些值时怎样影响其结果，模拟运算表提供了一个操作所有变化的捷径。
模拟运算表是一个单元格区域，它可显示一个或多个公式中替换不同值时的结果。
有两种类型的模拟运算表：单输入模拟运算表和双输入模拟运算表。
单输入模拟运算表中，用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。
双输入模拟运算表中，用户对两个变量输入不同值，而查看它对一个公式的影响。
1 单输入模拟运算表当对公式中的一个变量以不同值替换时,这一过程将生成一个显示其结果的数据表格。
我们既可使用面向列的模拟运算表，也可使用面向行的模拟运算表。
面向列的模拟运算表例如我们对图中的模型进行模拟运算，假设可变成本分别为固定成本的10%、15%、20%、25%和30%，而其他条件不改变时整个公司的利润会怎样变动？其操作步骤如下：（1）在单一列的输入单元格内，输入要Excel替换的值的序列，我们在“A6”单元格中向下输入上述的序列。
在第一个值的上面一行和值列右边的单元格中，键入引用输入单元格的公式，输入单元格可以是工作表上的任一空单元格，我们指定“A5”单元格为输入单元格。
输入附加的公式到同一行中第一个公式的右边，即输入“=A2+A3-B2*A5-B2”。
如图所示。
（2）选定包含公式和替换值序列的矩形区域，如图所示。
（3）执行“数据”菜单中“模拟运算表”命令，出现如图对话框。
（4）在“输入引用列的单元格”框中，输入可变单元格地址，在这里我们输入“A5”单元格。
按下“确定”按钮。
之后，Excel就会替换输入单元格中的所有值，且把结果显示在每一个输入值的右侧，如图所示。
还可以提供新值来替换工作表上原来输入的值，这样Excel将使用新值重新进行计算。
使用基于行的模拟运算表的过程和列类似，大家可以自己练习一下如果要观察一个输入值的变化对多个公式的影响，可以在已存在的单输入数据表格中增加一个或多个公式。