3.2-Excel高级应用讲稿.doc

3.2 Excel高级应用3.2.3 应用公式与函数1. 公式引用在单元格中使用公式计算的时候，必须输入以等于号“=”后跟具体计算公式/函数的数学表达式，按回车键Enter后，在活动单元格中得到公式/函数计算的结果。由于在Excel中可以对单元格的公式进行复制（/移动），这就使得在设置单元格公式的时候要考虑到公式被复制给（/移动到）其它单元格的情况，也即公式中引用的值的地址表示方式，包括相对引用、绝对引用和混合引用三种。相对引用指在公式移动或复制时，值地址相对目的单元格发生变化。由列名行号来表示，如B4。绝对引用指在公式移动或复制时，值地址不随复制或移动的目的单元格的变化而变化。在列名行号前都加上$符号来表示，如$A$2。混合引用指在公式移动或复制时，值地址的一部分为相对引用，一部分为绝对引用。如A$3,$B5。2. 函数与高级算法在Excel中，录入单元格内容时输入“=”号，在“名字框”中就会列出函数供用户选择，可以单击函数右边的向下箭头在函数列表中选择不同的函数，对于一些不常用较复杂的函数可以通过单击函数列表中的“其他函数”命令，打开“粘贴函数”对话框（图3.55）选择更多的函数。在对话框中可以看到Excel为用户提供了不同类型的函数，在选择好函数名后，在列表框下面还有对函数的简单说明，如果对函数用法不太了解，可以单击对话框中的帮助按钮。图3.55“粘贴函数”对话框3、常用函数用法（1）SUMIF（range，criteria，sum_range）其中参数range指出施加条件判断的单元格区域，criteria指出确定单元格被相加求和的条件，sum_range为需要求和的实际单元格区域（只有当 range中有满足条件的单元格时，才对 sum_range 中的对应单元格求和。如果省略sum_range，则直接对range中的单元格求和）。例如，假设A2:A5的内容分别为4套房子的销售标价：$150 000，$200 000，$250 000，$300 000。B2:B5的内容$7 000，$9 000，$11 500，$14 000为与每个销售价格相对应的销售佣金。则SUMIF（A2:A5，“150000”，B2:B5）表示房价大于150 000的房屋销售佣金合计，其结果等于 $34500（见图7-31）。图7-31 SUMIF函数的使用其他数学函数包括三角函数就不再介绍了。（2.）金融、财务类函数PMT（rate，nper，pv，fv，type）其中Rate为各期利率，是一固定值。Nper为付款期总期数。pv为投资现值，也称为本金。fv为最后一次付款后的现金余额，如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。Type为0或1，为0（或省略参数type）时是指各期的付款时间是在期末，为1时是指期初付款。例如，某人以按揭方式购买房屋，房屋价格为50万元，首期需交付30%，若要求余款（即贷款部分）按年息6%在10年内按月还清，则每月还款额应为多少？本问题可用如下公式计算：=PMT（6%/12，12*10，500000*（130%），0，0）结果为￥3885.72。负号可理解为是向外支付。如不希望出现负号，可令参数中的本金为负。此为每月末付款的结果，若希望每月初付款则函数应写成=PMT（6%/12，12*10，500 000*（130%），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:15 AM。（4）WEEKDAY（serial_number，return_type）其中serial_number代表要查找的日期，以了解该日期为星期几。return_type用来确定返回值的类型，具体规定如表7-5所示。表7-5 return_type返回值的规定代码（return_type）值返 回 值1或省略数字17，对应于星期天到星期六2数字17，对应于星期一到星期天3数字06，对应于星期一到星期天功能：结果指出参数给出的某日期为星期几。例如，“=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（利率，期数，-车价）”计算，结果为月付￥8 819.13元。注意其中年利率与月利率的区别。现在，如果购车人或银行希望了解不同的利率变化时月付金额的相应变化情况，就可以使用Excel 2000中的一个很有用的分析工具：模拟运算表。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”后面的文本框内应输入的是第一个方案（紧缩）中的“预计销售量”（150 000），“2 $C$6”后的文本框应输入同一方案的“销售佣金”数，以此类推。图7-44 “方案变量值”对话框8）变动数据全部输入后按“确定”退出，回到“方案管理器”对话框。此时，对话框中显示出已经建立了一个名为“紧缩”的方案。9）在“方案管理器”对话框中再次按“添加”按钮，可添加第二个方案，其过程与建立第一个方案是完全一样的，只不过要把方案名换成“正常”，可变单元格C5:C8中的值换成第二套方案的变更值而已。当三个方案都建好之后，“方案管理器”中会看到三个方案的名字。10）移动“方案管理器”（用鼠标左键点住该对话框的蓝色标题条并拖曳），使之靠近计算模型（目的是观察方便一些）。在对话框中任意选择一个方案，单击“显示”按钮，就可以看到计算模型中出现该方案的数据与计算结果。2. 方案总结如果想生成一个包含有全部三个方案的规范的报表，可以单击“方案管理器”对话框中的“总结”按钮，“方案管理器”会自动生成一份“方案总结”，如图7-45所示。该“方案总结”被建立在本工作簿的一张新的称为“方案总结”的工作表上。图7-45 用方案管理器生成的方案总结3. 方案的保管把本工作簿保存起来后（建议用“文件”菜单中的“另存为”定义一个有助于记忆的名字），建立在该工作簿中的方案管理器，连同各方案中的数据就一起被保存起来了。今后，只要打开该工作簿，使建有方案计算模型的工作表成为当前工作表，启动“工具”菜单中的“方案”命令，就可以用“方案管理器”观察这些方案了。可以用“方案管理器”建立和保存多种解决方案，以供决策时参考。实例教学教学内容在模拟运算表部分的“实例教学”所举的计算月生产利润问题中，由于工时费、材料费调高，为保证一定的利润，售价也应调整。用“方案管理器”做出4种比较合理的方案，并保存起来。操作指导（略）问题思考（略）10 单变量求解有时我们希望先设定一个目标值，反过来看一下，为达到该目标值，相关变量的值应是多少。例如，在讲解模拟运算表时曾经举过贷款购车，计算月付金额的例子。现在假设顾客提出他每月最大还贷能力只有5 000元，希望银行帮他选择一下还贷期限。这相当于先提出对结果的要求，再去调整变量值以满足该目标。类似这样的问题可以用单变量求解命令来完成。在工作表中输入原始模型数据（原例中用到的数据参见图7-37），并且照样输入计算月付金额的公式：“=PMT（年利率/12，期数，贷款额）”。启动“工具”菜单中的“单变量求解”命令，弹出如图7-46所示的“单变量求解”对话框。在“目标单元格”和“目标值”两个文本框中分别键入B5和5 000，表明目标单元格是月付金额，目标值是5 000元。对话框中的最后一个文本框要求确定让那个变量变动（以便能够使计算结果满足设定的目标值），就本例要求而言，应指定为B4（还贷的期限）。按“确定”退出后可见目标单元格中已变成5 000，而可变单元格B4中的期数已变成44.288。即，为使月付金额达到5 000元的目标，必须使还贷期数延长至44个月以上（将近4年）。执行“单变量求解”命令后会返回一个“单变量求解状态”对话框，报告是否已求得解。“单变量求解”命令通常使用迭代方法（不断调整可变单元格的值）来使公式计算结果逼近目标值，因此有些问题的求解时间会长一些，而且也会有些问题是不可解的。显然，“单变量求解”命令所利用的公式中必须包含对可变单元格的引用。如果目标单元格值的计算根本不依赖可变单元格，那也就不可能求得解。正因为如此，我们还可以利用建立的计算模型推算其他变量的值，只要这些变量参与对结果的计算。实例教学教学内容1. 在7.3.4小节模拟运算表部分的“实例教学”中所举的计算月生产利润问题，若要求月利润达到10万元，月产量应达到多少。或者售价应调至多高。操作指导（略）问题思考为什么说“单变量求解”命令所利用的公式中必须包含对可变单元格的引用？11 规划求解在经济、管理领域中经常会遇到求“最佳解”的问题。通常这种最佳解的求解过程都比较麻烦，因为求解过程中还要满足各种各样的约束条件。这种在满足给定约束条件的前提下求“最佳解”（ 如利润最大值或成本最小值）的问题称为规划问题。Excel 2000提供了“规划求解”命令帮助解决此类问题。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=500及C6=0。本例的规划求解过程不再赘述，请大家自己完成它。这里只给出计算后得到的一份运算结果报告（见图7-54）。3. 关于数组公式在上面计算最小运费的例子里碰到了成组数据参与运算的情况。在SUMPRODUCT函数中，上海仓库发生的运输成本（C24）等于（C4:C9）与（C13:C18）两组数据中对应元素的乘积之和，这实际上已经是在执行数组运算了。一般，涉及数组运算时最好采用数组公式，其好处是，减少公式的复制或中间公式的数量，计算更简捷，效率更高。数组是一些单元格的集合（或成组操作的数值）。数组公式就是直接使用或者作为函数参数使用了一或多个数组的公式。建立数组公式的方法与建立普通公式基本一样，但重要的区别是，普通公式的建立用Enter键结束，而数组公式用Ctrl+Shift+Enter键结束。在编辑框中可以看到，建立起来后的数组公式总被一对大括号括住。例如，制作如图7-55所示的平方根表。图7-54 最小运费问题结果报告图7-55 用数组公式制作平方根表建立数组公式的步骤如下。1）首先选择存放结果的单元格区域（B2:B11，这实际上也是选择了数组公式的作用范围）。2）在B2单元格中输入SQRT函数，参数选择区域A2:A11。3）用Ctrl+Shift+Enter键结束函数输入，退出。可以看到，在单元格区域B2:B11中已出现了计算结果，选中任何一个结果单元格都会看到一个完全相同的，由大括号括住的数组公式，这说明同一个数组公式作用到这一组结果单元格中了。用数组公式计算出的结果也可以只占用一个单元格。再次以前面的最低运输成本问题为例，我们可以直接令规划求解的总成本合计单元（G24）中的公式为：“=SUMPRODUCT（C4：E9，C13:E18）”，所得结果与原来一样。请注意理解该函数在两个数组C4:E9与C13:E18上施加的运算是让两组数据中的对应元素相乘后，累加所有的积。显然，在做这样的运算时两个数组的行数与列数应该对应相等。实例教学教学内容1. 自己完成“最小运费”的规划求解问题。