Excel2007单变量模拟运算表

格式：pdf
大小：606.62 KB
文档页数：4

下载文档原格式

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 创建单变量模拟运算表

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单变量与规划求解模拟运算表

每吨单价350元，含四种营养成份分别为A20％、B8％、C1％、D0.5％；丁原料（玉米），每吨单价为450元，含四种营养成份分别为A7％、B5％、C40％、D6％。
求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用
最低。
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

第12讲 Excel模拟分析工具.ppt

单变量求解
计
算已知公式计算的结果，计算出引用单元格的值。
机例如：希望月还款额的值为2000元，“年利率” 高和“年限”不变，那么“贷款总额”应该是多少？
级办
选中D2，工具→单变量求解
公
自
动
化
贷款总额从40万变为约30万
2020/10/13
大学计算机信息科技教程(第二版)
5
上机实验
计
算 ➢ 实验指导“实验9”：
机高
5. 函数
级
6. 单变量求解
办
7. 模拟运算表
公
8. 页面设置和打印预览
自动
➢ 说明：
化
实验结果必须保存到虚拟磁盘OA中
2020/10/13
大学计算机信息科技教程(第二版)
6
级
办
选中A4:B9，
公
数据→模拟运算表
自
动年化限
=D2
不同年限月还款额
2020/10/13
大学计算机信息科技教程(第二版)
3
1. 模拟运算表-双变量
计
算
机
高级
=D2 贷款金额
办
年
公
限
自
动选中D4:H9，化数据→模拟运算表
不同年限、不同贷款总额的月还款额
2020/10/13
大学计算机信息科技教程(第二版)
第12讲 Excel模拟分析工具
计
算 1. 模拟运算表
机
单变量模拟分析、双变量模拟分析
高级
2. 单变量求解
办
公
自
动
化
上机实验
2020/10/13
大学计算机信息科技教程(第二版)

excel模拟运算表的使用方法

excel模拟运算表的使用方法Excel是一款强大的电子表格软件，它可以模拟运算表的使用方法。

通过Excel，我们可以进行各种数学运算、统计分析、数据展示和图表绘制等操作。

下面将为大家介绍一些Excel的基本功能和使用技巧，帮助大家更好地掌握和应用Excel。

我们需要了解Excel的界面和基本操作。

打开Excel后，我们可以看到一个由行和列组成的表格，每个单元格都可以输入数据。

在Excel的工具栏上，有许多常用的功能按钮，如新建、打开、保存、复制、粘贴等，这些按钮可以帮助我们快速完成一些操作。

另外，还有一些常用的快捷键，比如Ctrl+C用于复制选中的单元格，Ctrl+V用于粘贴复制的内容。

在Excel中，我们可以进行各种数学运算。

例如，我们可以在一个单元格中输入一个数值，然后在另一个单元格中输入一个运算符（如+、-、*、/），再在另一个单元格中输入另一个数值，按下回车键，Excel就会自动计算出结果。

这样，我们就可以方便地进行加减乘除等基本运算。

另外，Excel还支持各种复杂的数学函数，如求平均值、求和、求最大值、求最小值等，这些函数可以帮助我们更好地分析和处理数据。

除了数学运算，Excel还支持各种统计分析。

例如，我们可以使用Excel的排序功能，将一列数据按照升序或降序排列，以便更好地查看和比较数据。

另外，我们还可以使用Excel的筛选功能，根据某个条件来筛选数据，以便更好地分析和展示数据。

此外，Excel还支持各种统计图表的绘制，如柱状图、折线图、饼图等，这些图表可以直观地展示数据的分布和趋势。

除了基本的数学运算和统计分析，Excel还支持各种数据处理和格式化操作。

例如，我们可以使用Excel的查找和替换功能，快速找到某个关键字并进行替换。

另外，我们还可以使用Excel的条件格式功能，根据某个条件来设置单元格的颜色、字体等样式，以便更好地展示和比较数据。

此外，Excel还支持各种数据导入和导出操作，可以方便地与其他软件进行数据交互。

Excel_模拟运算表使用方法

Excel 模拟运算表使用方法大家平常都只用函数公式做运算,相信很少人用过模拟运算.现对模拟运做一实例分析,让大家对之有初步认识.在工作表中输入公式后，可进行假设分析.查看当改变公式中的某些值时怎样影响其结果，模拟运算表提供了一个操作所有变化的捷径。

模拟运算表是一个单元格区域，它可显示一个或多个公式中替换不同值时的结果。

有两种类型的模拟运算表：单输入模拟运算表和双输入模拟运算表。

单输入模拟运算表中，用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。

双输入模拟运算表中，用户对两个变量输入不同值，而查看它对一个公式的影响。

1 单输入模拟运算表当对公式中的一个变量以不同值替换时,这一过程将生成一个显示其结果的数据表格。

我们既可使用面向列的模拟运算表，也可使用面向行的模拟运算表。

面向列的模拟运算表例如我们对图中的模型进行模拟运算，假设可变成本分别为固定成本的10%、15%、20%、25%和30%，而其他条件不改变时整个公司的利润会怎样变动？其操作步骤如下：（1）在单一列的输入单元格内，输入要Excel替换的值的序列，我们在“A6”单元格中向下输入上述的序列。

在第一个值的上面一行和值列右边的单元格中，键入引用输入单元格的公式，输入单元格可以是工作表上的任一空单元格，我们指定“A5”单元格为输入单元格。

输入附加的公式到同一行中第一个公式的右边，即输入“=A2+A3-B2*A5-B2”。

如图所示。

（2）选定包含公式和替换值序列的矩形区域，如图所示。

（3）执行“数据”菜单中“模拟运算表”命令，出现如图对话框。

（4）在“输入引用列的单元格”框中，输入可变单元格地址，在这里我们输入“A5”单元格。

按下“确定”按钮。

之后，Excel就会替换输入单元格中的所有值，且把结果显示在每一个输入值的右侧，如图所示。

还可以提供新值来替换工作表上原来输入的值，这样Excel将使用新值重新进行计算。

使用基于行的模拟运算表的过程和列类似，大家可以自己练习一下如果要观察一个输入值的变化对多个公式的影响，可以在已存在的单输入数据表格中增加一个或多个公式。

Excel数据分析

实例1：单变量模拟运算表
例：计算商场出售商品的获利情况。公式为毛利=进货成本*加价百分比*销售数量-销售费用
操作步骤：
① 参数写在同一列A10:A14上，因此选择B9单元格输入公式：=B1*B2*B3-B4；
② 选择A9:B14区域，在“模拟运算表”对话框中，选择 “输入引用列的单元格”，单击B2单元格，则产生结果，此结果区域为数组；
行引用和列引用
模拟运算表中，输入值要排在一行或一列。如果输入值排在同一列，则在“输入引用列的单元格”框中，键入可变单元格。如果输入值排在同一行上，则在“输入引用行的单元格”框中，键入可变单元格。
“单变量模拟运算表”中公式位置
当输入值排成一列时，在第一个数值的上一行且处于数值列右侧的单元格中键入所需公式。（可以在公式右边的单元格中键入其它公式）当输入值排成一行时，在第一个数值左边一列且数值行下方的单元格内键入所需公式。（可以为一个以上的公式）
② 在B6单元格里输入公式： =SUMPRODUCT(F2:F3,G2:G3)
③ 在“规划求解参数”对话框设置参数，参考图片，“选项” 中选择“采用线性模型”和“假定非负”；
分析工具库
在“Excel选项”中，选“加载项”，单击“转到”，在打开的“加载宏”对话框中选择“分析工具库”。
§4. 方案分析
模拟运算表计算结果
模拟运算表的计算结果存放在数组中，不能单独修改其中的某一个值。要清除计算结果，选中模拟运算表中所有结果，按Delete键即可。
单变量模拟运算表操作步骤：
① 根据输入值的方向，选择公式的所在位置，输入公式； ② “数据”|“数据工具”|“假设分析”|“模拟运算表”，在“模拟运算表”对话框中键入引用的单元格；

Excel怎样运用模拟运算表

Excel怎样运用模拟运算表Excel的模拟运算表是利用模拟运算设定，利用EXCEL公式，计算显示运算结果。

一般采用单变量模拟运算，最多可以容纳2个变量即“输入引用行的单元格”和“输入引用列的单元格”。

常见的用来假设分析定额存款模拟试算和贷款月还款模拟试算。

下面用举例的方式来谈谈模拟运算表的使用方法。

案例一、用模拟运算试算定额存款最终存款总额1、启动Excel2010，要进行“模拟运算”的数据表如下图所示。

2、运用FV函数，计算存款总额。

先选中B5单元格，点击“公式”＝〉“插入函数”。

在打开“插入函数”对话框中，在“或选择类别”下拉列表中选择“财务”，在“选择函数”列表中选择“FV”，单击“确定”，如下图所示：3、弹出“函数参数”对话框，在“Rate”文本框中输入“B2/12”即为月利率；在“Nper”文本框中输入“B3”即存款期限（月）；在“Pmt”文本框中输入“A5”即每月存款额。

单击“确定”，如下图所示：4、FV函数计算结果如下图所示。

5、选中“A5：B9”单元格，点击“数据”＝〉“模拟分析”＝〉“模拟运算表”，如下图所示：6、弹出“模拟运算表”对话框，在“输入引用列的单元格”文本框中点击“A5”，如下图所示：7、模拟运算完成，结果如下图所示。

案例二、用模拟运算试算贷款月还款金额1、启动Excel2010，要进行“模拟运算”的数据表如下图所示。

2、运用PMT函数，计算月还款总额。

先选中B5单元格，点击“公式”＝〉“插入函数”。

在打开“插入函数”对话框中，在“或选择类别”下拉列表中选择“财务”，在“选择函数”列表中选择“PMT”，单击“确定”，如下图所示：3、弹出“函数参数”对话框，在“Rate”文本框中输入“A5/12”即为月利率；在“Nper”文本框中输入“B3”即贷款期限（月）；在“Pv”文本框中输入“B2”即贷款金额。

单击“确定”，如下图所示：4、PMT函数计算结果如下图所示。

Excel 2007

【例9.2】某班要建立一个成绩登记表，为了减少成绩输入错误，】某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表中数据的输入类型及范围进行限制。可对成绩表中数据的输入类型及范围进行限制。限制学号为8位字符，不能小于8位，也不能多于8位。限制学号为位字符，不能小于位也不能多于位位字符限制所有学科成绩为0~100之间的整数。之间的整数。限制所有学科成绩为之间的整数限制科目列标题的取值范围，高数”不能输入为“ 限制科目列标题的取值范围，如“高数”不能输入为“高等数学”。
从属单元格
如果某个单元格中的公式引用了其它单元格，如果某个单元格中的公式引用了其它单元格，那么此单元格就称为从属单元格。那么此单元格就称为从属单元格。当公式中所引用单元格的值发生变化时，当公式中所引用单元格的值发生变化时，公式所在单元格（从属单元格）所在单元格（从属单元格）中的值也会随之变例如，化。例如，若B3单元格中包含公式单元格中包含公式就是A3和的从属单元 “=A3+A4”，则B3就是和A4的从属单元，就是单元格的值发生变化时，单格。当A3或A4单元格的值发生变化时，B3单或单元格的值发生变化时元格的值就会发生变化。元格的值就会发生变化。
2、单击“数据有效性” 右边的下箭头，选择 “圈释无效数据” 圈释无效数据”
Excel就会将不就会将不符合有效性规则的数据圈释出来
9.1.3 数据有效性检验数据有效性检验
设置输入提示信息和错误警告信息
这些信息都需要通过“数据有效性” 这些信息都需要通过数据有效性”对话框进行设置。行设置。
9.1 数据审核及跟踪分析数据审核及跟踪分析
1、概念、
数据审核是一种查找单元格数据错误来源的工数据审核是一种查找单元格数据错误来源的工具，通过它可以快速地找出具有引用关系的单元格，借此分析造成错误的单元格。元格，借此分析造成错误的单元格。数据审核使用追踪箭头追踪箭头，数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。或追踪单元格与公式之间的关系。

EXCEL数据分析工具的应用

EXCEL数据分析工具的应用（模拟运算等）2008年10月31日星期五 17:12Excel提供了非常实用的数据分析工具，利用这些分析工具，可解决财务管理中的许多问题，例如财务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等等。

下面介绍财务管理与分析中常用的一些数据分析工具。

2.4.1 模拟运算表模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。

在Excel中，可以构造两种模拟运算表：单变量模拟运算表和多变量模拟运算表。

2.4.1.1 单变量模拟运算表单变量模拟运算表就是基于一个输入变量，用它来测试对公式计算结果的影响。

【例2-13】企业向银行贷款10000元，期限5年，则可以使用【模拟运算表】工具来测试不同的利率对月还款额的影响，步骤如下：（1）设计模拟运算表结构，如图2-62所示。

图2-62 单变量模拟运算表。

（2）在单元格B4中输入公式“=PMT(A4/12,5*12,B1)”（3）选取包括公式和需要进行模拟运算的单元格区域A4:B13。

（4）单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，如图2-63。

图2-63 【模拟运算表】对话框。

单击【确（5）由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$A$4”定】按钮，即得到单变量的模拟运算表，如图2-62所示。

2.4.1.2 双变量模拟运算表双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响，在财务管理中应用最多的是长期借款双变量分析模型，有关详细内容可参阅第3章的有关章节。

2.4.2 单变量求解单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。

单变量求解工具可以解决许多财务管理中涉及到一个变量的求解问题。

【例2-14】某企业拟向银行以7%的年利率借入期限为5年的长期借款，企业每年的偿还能力为100万元，那么企业最多总共可贷款多少？，单击【工具】设计如图2-64所示的计算表格，在单元格B2中输入公式“=PMT(B1,B3,B4)”菜单，选择【单变量求解】项，则弹出【单变量求解】对话框，如图2-65所示，在【目标，然后单元格】中输入“B2”，在【可变单元格】中输入“$B$4”，在【目标值】中输入“100”单击【确定】按钮，则系统立即计算出结果，如图2-64所示，即企业最多总共可贷款410.02万元。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

1.1.1 单变量模拟运算表
除了使用公式，Excel模拟运算表也是用于模拟试算的很好的工具。

模拟运算表实际上是一个单元格区域，它可以用列表的形式显示计算模型中某些参数的变化对计算结果的影响。

在这个区域中，生成的值所需要的若干个相同公式被简化成一个公式，从而简化了公式的输入。

模拟运算根据行、列变量的个数，可分为单变量模拟运算表和双变量模拟运算表。

以下步骤将演示借助模拟运算表工具完成与1.2.1相同的试算表格：
步骤1 先在D4:D11单元格区域中输入可能的美元汇率值，从6.98开始以0.1为步长进行递减，然后在E3单元格中输入公式“=B10”。

步骤2 选定单元格区域D3:E11，单击【数据】选项卡中的【假设分析】按钮，在其下拉列表中单击【数据表】按钮。

步骤3 在弹出的【数据表】对话框中，在【输入引用列的单元格】框中单击，鼠标指向B6单元格并单击，【输入引用列的单元格】框中将自动输入“$B$6”，最后单击【确定】按钮，如图1-3所示。

图1-3 借助模拟运算表工具创建试算表格
创建完成的试算表格如图1-4所示。

选定E4:E11中任意一个单元格，编辑栏均显示单元格内容为“{=TABLE(,B6)}”。

利用此表格，用户可以快速查看不同汇率水平下的交易额情况。

图1-4 模拟运算表对汇率影响的分析
“{=TABLE(,B6)}”是一个比较特殊的数组公式，有关数组公式的更多内容，请参阅：第20章。

在已经生成结果的模拟运算表中，原有的数值和公式引用都可以被修改，在本例中是D4:D11和E3。

而结果区域不能被修改，在本例中是E4:E11。

如果原有的数值和公式引用有变化，结果区域会自动更新。

深入了解：模拟运算表的计算过程
初次接触Excel模拟运算表的用户在被这个强大工具所吸引的同时，也很难理解刚才所讲的每一步骤对最终结果产生的作用，下面来详细剖析一下：
步骤1和步骤2向Excel告知了两件事情：一是模拟运算表的表格区域是D3:E11；二是本次计算要生成的结果是月交易额，以及月交易额是如何计算得到的。

步骤3的设置告诉Excel本次计算只有一个变量，即美元汇率，而且这个变量可能出现的数值都存放于D列中。

当然，也正因为这些美元汇率值已经存放于D列中，所以在步骤3中，“B6”是“引用列的单元格”，而不是“引用行的单元格”，而且将“引用行的单元格”留空。

不必去关心D3为什么为空，Excel现在已经得到了足够的信息来生成用户需要的结果。

而如果觉得E3的数值破坏了表格的可读性，可以将此单元格设置为白色字体。

为了让用户更加明白这个计算过程，下面将用另一种形式生成模拟运算表：
步骤1 先在E3:L3单元格区域中输入可能遇到的美元汇率值，从6.98开始以0.1为步长进行递减，然后在D4单元格中输入公式“=B10”。

步骤2 选定单元格区域D3:L4，单击【数据】选项卡中的【假设分析】按钮，在其下拉
列表中单击【数据表】。

在弹出的【数据表】对话框中，在【输入引用行的单元格】框中单击，鼠标指向B6单元格并单击，【输入引用行的单元格】框中将自动输入“$B$6”，如图1-5所示。

图1-5 创建用于横向的模拟运算表格区域
单击【确定】按钮后，生成计算结果如图1-6所示。

图1-6 横向的模拟运算表结果
在进行单变量模拟运算时，运算结果可以是一个公式，也可以是多个公式。

在本例中，如果在F3单元格中输入公式“=B11”，然后选定单元格区域D3:F11，再创建模拟运算表，则结果如图1-7所示（为了阅读需要，在E2:F2加入了标题）。

图1-7 单变量模拟运算多个公式结果
本篇文章节选自《Excel 2007应用大全》ISBN：9787115272904 人民邮电出版社。