Excel高级应用讲稿Word文件下载.docx

Excel高级应用讲稿Word文件下载.docx

《Excel高级应用讲稿Word文件下载.docx》由会员分享，可在线阅读，更多相关《Excel高级应用讲稿Word文件下载.docx（24页珍藏版）》请在冰豆网上搜索。

其中Rate为各期利率，是一固定值。

Nper为付款期总期数。

pv为投资现值，也称为本金。

fv为最后一次付款后的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

Type为0或1，为0（或省略参数type）时是指各期的付款时间是在期末，为1时是指期初付款。

例如，某人以按揭方式购买房屋，房屋价格为50万元，首期需交付30%，若要求余款（即贷款部分）按年息6%在10年内按月还清，则每月还款额应为多少？

本问题可用如下公式计算：

=PMT（6%/12，12*10，500000*（1–30%），0，0）

结果为￥–3885.72。

负号可理解为是向外支付。

如不希望出现负号，可令参数中的本金为负。

此为每月末付款的结果，若希望每月初付款则函数应写成=PMT（6%/12，12*10，500000*（1–30%），0，1），结果为￥–3866.39。

注意在函数中有默认参数时需要保留其位置以便系统能分辨随后出现的参数的含义，但最末的参数省略时不必特意标识。

（3.）查找与引用函数

VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

其中，lookup_value为需要在数据表第一列中查找的数据值。

lookup_value可以为数值、引用或文字串。

Table_array为需要在其中查找数据的整个数据表，可以使用对区域或区域名称的引用，例如数据库或数据表单。

col_index_num为table_array中待返回的匹配值的列序号。

col_index_num为1时，返回table_array第一列中的数值；

col_index_num为2，返回table_array第二列中的数值，以此类推。

range_lookup为一逻辑值，为TRUE或省略时返回近似匹配值，为FALSE时，将返回精确匹配值。

功能：

在表格或数值数组的首列查找指定的数值，并返回该行中指定列处的单元格中的值。

例如，在前面提到的电话号码表中，若要直接返回张山的电话号码，可以使用函数VLOOKUP（“张山”，C2:

D101，2，FALSE），执行结果如图7-34所示。

图7-34 

VLOOKUP函数的应用

如果数据表是横向的，查找值位于首行时，可以使用HLOOKUP函数。

此时第三个参数的含义为需要返回查找值的行序号，即，与找到的值处于同列，并由此向下的第n行（该行上的那个数据）。

上述查找与引用类函数在实际工作中非常有用，特别是它们的嵌套使用可以灵活地实现许多复杂的查找引用功能。

5.日期与时间函数

（1）TODAY（）

返回当前日期。

另有函数NOW（），可返回当前日期和时间。

（2）DATE（year，month，day）

将所给参数转换成日期。

例如c1，d1，e1中分别是76，5和9，则DATE（c1，d1，e1）的结果为1976-5-9。

（3）TIME（hour，minute，second）

将所给参数转换成时间。

例如c1，d1，e1中分别是8，15和0，则TIME（c1，d1，e1）的结果为8:

15AM。

（4）WEEKDAY（serial_number，return_type）

其中serial_number代表要查找的日期，以了解该日期为星期几。

return_type用来确定返回值的类型，具体规定如表7-5所示。

表7-5 

return_type返回值的规定

代码（return_type）值

返 

回 

值

1或省略

数字1~7，对应于星期天到星期六

2

数字1~7，对应于星期一到星期天

3

数字0~6，对应于星期一到星期天

结果指出参数给出的某日期为星期几。

例如，“=WEEKDAY（“2002/05/01”，2）”的结果为3（星期三）。

另外还有YEAR、MONTH、DAY等函数可分别从日期字符串中抽取出年份、月份及某月中的“多少号”。

6.逻辑函数

（1）AND（logical1，logical2，…，logical30）

logical1，logical2，…，logical30表示待检测的1到30个条件值（参数），各条件值必须是逻辑值（或为TRUE，或为FALSE）。

所有参数的逻辑值为真时函数结果为TRUE；

只要一个参数的逻辑值为假结果即为FALSE。

AND（TRUE，FALSE）等于FALSE，AND（2+2=4，2+3=5）等于TRUE。

（2）OR（logical1，logical2，…，logical30）

logical1，logical2，…，logical30为需要进行检测的1到最多30个条件，各条件值必须是逻辑值（或为TRUE，或为FALSE）。

任何一个参数的逻辑值为TRUE，函数即返回TRUE。

例如，如果B1:

B3单元格中的值为FALSE、FALSE、TRUE，那么：

OR（B1:

B3）等于TRUE。

（3）NOT（logical）

logical为一个逻辑值或是可以计算出TRUE或FALSE的逻辑表达式。

如果逻辑值为FALSE，函数NOT返回TRUE；

如果逻辑值为TRUE，函数NOT返回FALSE。

例如，NOT（FALSE）等于TRUE，NOT（1+1=2）等于FALSE。

（4）IF（logical_test，value_if_true，value_if_false）

Logical_test是计算结果为TRUE或FALSE的任意条件表达式。

Value_if_true表示当logical_test为TRUE时要求返回的值；

Value_if_false表示当logical_test为FALSE时要求返回的值。

执行真假值判断，并根据逻辑测试的真假值返回不同的结果。

例如，单元格A10中有值3，执行“IF（A10=3，“单元格中是3”，“单元格中不是3”）”结果返回“单元格中是3”。

如果A10中为5，IF函数中的条件不成立（为FALSE），则返回的将是“单元格中不是3”这句话。

IF函数是个非常重要的函数，它在进行自动判断时是很有用的。

IF函数可以嵌套使用（最多7层）。

图7-35表示学生能否被录取的政策规定（三门课成绩中至少有两门大于等于90分，或者三门总分达到240分）。

无论是怎样的成绩数据，用嵌套的IF函数总能得出正确的是否录取的结论。

图7-35 

IF函数及其嵌套使用

函数公式中常用连接运算符号“&

”组合运算结果，它的作用是将两个文本值串接起来从而产生一个连续的文本值（参见图7-56中单元格B10的计算公式）。

7.单元格批注

有时在单元格中使用的函数或公式比较复杂，甚至有时设计这些公式的人自己过后也感到难以理解它们了。

可以为这样的单元格添加“批注”，方法如下。

1）单击需要添加批注的单元格。

2）在“插入”菜单中，单击“批注”命令。

3）在弹出的批注框中键入批注文本。

4）完成文本键入后，请单击批注框外部的工作表区域。

也可用鼠标右键单击要添加批注的单元格，在弹出式菜单中选“插入批注”命令，完成插入批注工作。

〖实例教学〗

〖教学内容〗

用函数求解如下问题：

1.打开“公式与函数.xls”，按要求计算。

2.打开“计税.xls”,按要求计算。

3.某人以按揭方式购买房屋，房屋价格为30万元，首期需交付30%，若要求余款（即贷款部分）按年息6%在10年内按月还清，则每月还款额应为多少？

〖问题思考〗

可以从哪些地方了解到函数的功能与具体格式等方面的详尽信息。

8 

模拟运算表

例:

某人贷款购车，车价20万元，规定年利率为5.5%，24个月还清。

计算购车人的月还款额。

首先在工作表中建立如图7-37所示的计算模型。

图7-37 

月还贷额计算模型

其中前三项数据都是常数。

月付款额用公式：

“PMT（利率，期数，-车价）”计算，结果为月付￥8819.13元。

注意其中年利率与月利率的区别。

现在，如果购车人或银行希望了解不同的利率变化时月付金额的相应变化情况，就可以使用Excel2000中的一个很有用的分析工具：

模拟运算表。

1.单变量模拟运算表

在工作表的一块空白区输入两个利率值4.5%，4.6%，选择这两个单元格后，拖住选择区右下角的复制柄向下拖曳（至利率6.0%为止）。

在紧邻利率的右侧一列，起始利率的上面一行（图中的E2单元格）输入：

“=B5”，于是E2中也出现了B5中的值，并且将会随着B5的变化而改变。

1）选择单元格区域D2:

E18，把利率系列及上方的公式单元格（E2）全部包括在内。

2）打开“数据”菜单，选择“模拟运算”命令。

3）在弹出的对话框中单击“输入引用列的单元格”文本框，使文本插入符出现在该文本框中。

输入$B$3（或直接点选B3单元格）后退出（这一步的含义是指出将要引用“利率”列的单元格是$B$3）。

这时，在利率的右侧一列上已自动计算出对应所有不同利率的月付金额了。

可以看到，随着利率的升高，月付金额值也会有所提高（见图7-38中的单变量模拟运算表）。

图7-38 

单变量模拟运算表

2.双变量模拟运算表

上例是单变量的模拟运算。

还可以做双变量的模拟运算。

假设大家所关心的不仅是利率变化带来的影响，还关心还贷期限长短对月付金额大小产生的影响，那么可以把上面的模拟运算表改造一下。

如图7-39所示。

图7-39 

双变量模拟运算表

1）12，24，36，48等不同的期数放在表上方的行中。

2）最左上角单元格D2中引入计算公式（或将公式简化为“=B5”也可）。

3）选中整个区域后，打开“数据”菜单，选择“模拟运算”命令。

4）在“输入引用行的单元格”文本框中添入单元格地址B4（期数），在“输入引用列的单元格”文本框中添入单元格地址B3（利率）。

5）按“确定”退出时，基于两个变量而进行的模拟运算也就完成了。

某公司月生产利润的计算关系如表7-6所示。

若每件产品的工时费在2，2.5，3，…，7之间变动，对应的月利润各是多少。

若每件材料费也同时在4，5，6，…，8范围内变动，对应的月利润各是多少。

表7-6 

生产利润计算关系

每件产品成本

13.7 

=每件固定成本+每件工时费+每件材料费

每件固定成本

4.2

每件工时费

3.5

每件材料费

6

每件售价

19.99

每件利润

6.29 

=每件售价-每件产品成本

月产量

10000

月利润

62900 

=每件利润*月产量

〖操作指导〗

分别用一维与二维模拟运算表完成。

如果表7-6中给出的全部是常数，模拟运算会出现什么情况，为什么？

7.3.5 

方案管理器

模拟运算表是一种用做What-if分析的简便工具。

但是，模拟运算表最多只能分析两个变量发生变化时对结果产生的影响。

如果要考察更多变量变化对计算结果产生的影响，可以用Excel提供的“方案管理器”来完成。

例如，为获得最大利润，某企业在产品推销成本策略上制定了：

紧缩、正常和扩张三种方案。

在不同的方案中，推销每件产品的销售佣金、广告费分摊、网点建设费分摊都有不同。

有关数据见图7-40。

图7-40 

产品推销策略三种方案有关数据

在工作表中建立一个计算模型如图7-41。

结果单元格（利润值C9）中的公式表明，全部利润等于每件产品的利润乘上预计销售量，而每件产品的利润等于销售价格减去制造成本和各种销售成本。

图7-41 

求推销成本的计算模型

1.方案的建立

1）打开“工具”菜单，启动“方案”命令，弹出一个“方案管理器”对话框如图7-42所示。

2）对话框中指出“未定义方案”，可选择“添加”按钮以添加方案。

3）单击“添加”按钮，出现“添加方案”对话框，如图7-43所示。

4）首先在第一个文本框中键入第一个方案的名称“紧缩”（在本例中，假定三个方案分别称为：

紧缩、正常和扩张。

当然也可以叫成别的名字）。

图7-42 

“方案管理器”对话框 

图7-43 

“添加方案”对话框

5）第二个文本框要求输入“可变单元格”。

它是指刚才建立的计算模型中哪些单元格中的数据是变动的（从而构成不同的方案）。

分析原始数据可知，销售价格与制造成本在各方案中均保持不变，而利润是计算出来的，除此之外的预计销售量、销售佣金、广告分摊、网点建设费分摊等4项在不同方案中将取不同的值。

体现在图7-41的计算模型中，可变单元格应是C5:

C8。

6）下面的文本框中可以加入方案注释，如不需要，按“确定”退出。

7）操作转入“方案变量值”对话框（见图7-44），要求为刚刚定义的“紧缩”方案输入各可变单元格的值。

该对话框以序号和各可变单元格地址标识出一系列输入文本框，例如“1$C$5”后面的文本框内应输入的是第一个方案（紧缩）中的“预计销售量”（150000），“2 

$C$6”后的文本框应输入同一方案的“销售佣金”数，以此类推。

图7-44 

“方案变量值”对话框

8）变动数据全部输入后按“确定”退出，回到“方案管理器”对话框。

此时，对话框中显示出已经建立了一个名为“紧缩”的方案。

9）在“方案管理器”对话框中再次按“添加”按钮，可添加第二个方案，其过程与建立第一个方案是完全一样的，只不过要把方案名换成“正常”，可变单元格C5:

C8中的值换成第二套方案的变更值而已。

当三个方案都建好之后，“方案管理器”中会看到三个方案的名字。

10）移动“方案管理器”（用鼠标左键点住该对话框的蓝色标题条并拖曳），使之靠近计算模型（目的是观察方便一些）。

在对话框中任意选择一个方案，单击“显示”按钮，就可以看到计算模型中出现该方案的数据与计算结果。

2.方案总结

如果想生成一个包含有全部三个方案的规范的报表，可以单击“方案管理器”对话框中的“总结”按钮，“方案管理器”会自动生成一份“方案总结”，如图7-45所示。

该“方案总结”被建立在本工作簿的一张新的称为“方案总结”的工作表上。

图7-45 

用方案管理器生成的方案总结

3.方案的保管

把本工作簿保存起来后（建议用“文件”菜单中的“另存为”定义一个有助于记忆的名字），建立在该工作簿中的方案管理器，连同各方案中的数据就一起被保存起来了。

今后，只要打开该工作簿，使建有方案计算模型的工作表成为当前工作表，启动“工具”菜单中的“方案”命令，就可以用“方案管理器”观察这些方案了。

可以用“方案管理器”建立和保存多种解决方案，以供决策时参考。

在模拟运算表部分的“实例教学”所举的计算月生产利润问题中，由于工时费、材料费调高，为保证一定的利润，售价也应调整。

用“方案管理器”做出4种比较合理的方案，并保存起来。

〖操作指导〗（略）

〖问题思考〗（略）

10 

单变量求解

有时我们希望先设定一个目标值，反过来看一下，为达到该目标值，相关变量的值应是多少。

例如，在讲解模拟运算表时曾经举过贷款购车，计算月付金额的例子。

现在假设顾客提出他每月最大还贷能力只有5000元，希望银行帮他选择一下还贷期限。

这相当于先提出对结果的要求，再去调整变量值以满足该目标。

类似这样的问题可以用单变量求解命令来完成。

在工作表中输入原始模型数据（原例中用到的数据参见图7-37），并且照样输入计算月付金额的公式：

“=PMT（年利率/12，期数，－贷款额）”。

启动“工具”菜单中的“单变量求解”命令，弹出如图7-46所示的“单变量求解”对话框。

在“目标单元格”和“目标值”两个文本框中分别键入B5和5000，表明目标单元格是月付金额，目标值是5000元。

对话框中的最后一个文本框要求确定让那个变量变动（以便能够使计算结果满足设定的目标值），就本例要求而言，应指定为B4（还贷的期限）。

按“确定”退出后可见目标单元格中已变成5000，而可变单元格B4中的期数已变成44.288。

即，为使月付金额达到5000元的目标，必须使还贷期数延长至44个月以上（将近4年）。

执行“单变量求解”命令后会返回一个“单变量求解状态”对话框，报告是否已求得解。

“单变量求解”命令通常使用迭代方法（不断调整可变单元格的值）来使公式计算结果逼近目标值，因此有些问题的求解时间会长一些，而且也会有些问题是不可解的。

显然，“单变量求解”命令所利用的公式中必须包含对可变单元格的引用。

如果目标单元格值的计算根本不依赖可变单元格，那也就不可能求得解。

正因为如此，我们还可以利用建立的计算模型推算其他变量的值，只要这些变量参与对结果的计算。

1.在7.3.4小节模拟运算表部分的“实例教学”中所举的计算月生产利润问题，若要求月利润达到10万元，月产量应达到多少。

或者售价应调至多高。

为什么说“单变量求解”命令所利用的公式中必须包含对可变单元格的引用？

11 

规划求解

在经济、管理领域中经常会遇到求“最佳解”的问题。

通常这种最佳解的求解过程都比较麻烦，因为求解过程中还要满足各种各样的约束条件。

这种在满足给定约束条件的前提下求“最佳解”（如利润最大值或成本最小值）的问题称为规划问题。

Excel2000提供了“规划求解”命令帮助解决此类问题。

1.最大利润问题

例如，某企业生产3种产品（A、B和C），每种产品有各自的日生产数量和单位利润，如图7-47所示。

企业自然要追求利润的最大化，但是又不可能无限制地扩大产量。

具体说来，各种产品的日产量受到如下一些约束。

图7-47 

某企业三种产品日生产数量与单位利润数据表

● 

日生产能力最多达3500件（无论A、B还是C）。

成品库中的专用货架每天只能存放700件A产品。

每天至少要生产500件B产品（有长期订单）。

由于C产品的市场容量有限，每天生产数量不能超过100件。

要解决本规划问题，首先要建立好计算模型。

例如日产量合计单元C7=SUM（C4:

C6），产品A的利润E4=C4*D4，总利润E7=SUM（E4:

E6）等。

产品B、C的利润计算公式可通过复制产品A的利润计算公式得到。

1）启动“工具”菜单中的“规划求解”命令，出现如图7-48所示的“规划求解参数”对话框。

2）在“设置目标单元格”文本框中键入总利润单元格E7（或单击该文本框，出现插入点光标后，用鼠标点选E7单元格）。

3）在“等于”单选框处，选“最大值”，因为我们是想求总利润这个目标单元格的最大值。

图7-48 

“规划求解参数”对话框

4）在“可变单元格”文本框内用鼠标拖曳法输入C4:

C6，这三个单元格中分别是三种产品的日产量，在计算过程中它们的值将不断变化组合以使得总利润达到最大。

5）接下来开始建立约束条件。

单击“添加”按钮，出现“添加约束”对话框（见图7-49）。

第一个约束条件是日合计产量C7的值不能超过（即小于等于）3500。

在“单元格引用位置”文本框内输入C7，中间的比较符选用框中选“<

=”，右边的“约束值”文本框中输入“3500”，单击“确定”按钮退出，回到“规划求解参数”对话框，此时可看到“约束”文本框中已经建立起第一个约束条件。

图7-49 

“添加约束”对话框

6）再次按“添加”按钮，开始建立第二个约束条件：

C4<

=700。

每次回到“规划求解参数”对话框后都可用“添加”按钮建立下一个约束条件（本例中第三和第四个约束条件分别是：

C5>

=500及C6<

=100）。

7）全部约束条件建立好，并返回至“规划求解参数”对话框。

8）单击对话框中的“求解”按钮，此时会出现一个“规划求解结果”对话框，见图7-50。

图7-50 

“规划求解结果”对话框

该对话框报告是否找到了满足所有约束条件，并且是最优的解。

如果找到了，用户还可以选择是保存这次规划求解结果，还是取消此次求解使数据表恢复原值。

另外，该对话框的右侧还有一个列表框供选择。

用户可以决定是否要产生一个运算结果报告（或者是敏感性报告及极限值报告）。

为了了解这些报告的作用，我们以选择“运算结果报告”为例。

单击“运算结果报告”后，按“确定”钮退出，这时在工作表标签处可以发现多出一张“运算结果报告”工作表，打开该工作表，可见如图7-51所示的内容。

表中报告了目标单元格所求出的最大值（￥62500），各可变单元格的最终取值，以及它们满足约束条件的情况。

9）请重新回到计算模型所在的工作表并再次启动“规划求解”命令。

在出现的“规划求解参数”对话框上还可以使目标单元格取某个特定的值。

方法是，在“等于”单选框处选择“值为”，并在其后的文本框中输入所希望的值，这样就可以使运算过程朝着这个指定的目标值进行。

图7-51 

规划求解的运算结果报告

10）“可变单元格”旁边有个“推测”按钮。

其作用是，如果单击“推测”按钮后，出现