Excel财务应用教程.doc

实例十一 投资风险分析在市场经济的今天，投资活动愈发显得频繁和重要。由于投资活动充满不确定性，所以任何投资总要承担一定的风险。如果决策面临的不确定性比较大，足以影响投资方案的选择，就应该对不同的方案进行计量，例如计算比较各种方案的期望净现值，作为投资决策的依据。Excel中大量的财务、统计等各种函数及其强大的表格功能，加上简单易行的操作，使其成为辅助投资风险分析的良好助手。其中的“方案管理器”更有助于如投资决策这种多方案问题的分析。本例中，某企业现在面临两种投资方案：新建厂房生产新产品和扩建厂房生产现有产品。新建厂房须投资300万元，扩建厂房须投资100万元。产品的市场前景不能确定。究竟使用那种方案，须考虑多种因素，而两种方案的预计净现值比较是必须考虑的重要依据。本例目标：l 学习使用IF函数l 学习设置单元格的有效数据范围l 学习使用NPV函数计算净现值l 学习在工作表中进行方案管理l 学习设置共享工作簿步骤一：建立工作表该企业目前面临5种可能的市场前景，各前景的说明及预计发生概率如表11-1。已知基本折现率为15%，厂房使用年限为4年。表11-1 各前景的说明及发生概率前 景说 明概 率前景1新产品畅销，现有产品滞销25%前景2第一年现有产品畅销，一年后新产品畅销35%前景3前两年现有产品畅销，两年后新产品畅销20%前景4前三年现有产品畅销，三年后新产品畅销10%前景5现有产品畅销，新产品滞销10%首先新建名为“投资风险分析”的工作簿，并在其中建立计算净现值的表格（如图11-1所示）。拟在工作表中先由各种前景的概率计算出各年的期望年净收益，再用函数计算净现值。图11-1 建立净现值计算表格步骤二：输入逻辑公式在本例中，要计算两种不同的方案的预计净现值，并加以比较。为在一张表格中计算两种不同的方案的预计净现值，使用逻辑函数IF来计算各种前景下的各年净收益。一、IF函数简介IF函数用于执行真假值判断，根据逻辑测试的真假值，返回不同的结果。可以使用函数IF对数值和公式进行条件检测。语法：IF(logical_test,value_if_true,value_if_false)参数：Logical_test可以是计算结果为TRUE或FALSE的任何数值或表达式。Value_if_true是Logical_test为TRUE时函数的返回值。如果logical_test为TRUE并且省略value_if_true，则返回TRUE。Value_if_true可以为某一个公式。Value_if_false是Logical_test为FALSE时函数的返回值。如果logical_test为FALSE并且省略value_if_false，则返回FALSE。Value_if_false可以为某一个公式。说明：函数IF可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。如果函数IF的参数包含数组，则在执行IF语句时，数组中的每一个元素都将计算。如果某些value_if_true和value_if_false参数为操作提取函数，则执行所有的操作。二、使用IF函数下面使用IF函数计算各年净现值。已知如果产品畅销，预计年净收益为180万元。如果产品滞销，预计年净收益为20万元。操作步骤如下：1. 将单元格B4命名为“投资”，将单元格B5命名为“产品”。2. 单击选中B10单元格。由前景说明可知该单元格对应的情况下，新产品畅销，现有产品滞销。也就是说如果企业生产的产品为新产品，则年净收益为180万元，如果企业生产的产品是现有产品，则年净收益为20万元。3. 单击“粘贴函数”按钮，弹出“粘贴函数”对话框（如图11-2所示）。图11-2 粘贴IF函数4. 在“函数分类”列表框中单击选中“逻辑”，在“函数名”列表框中单击选中“IF”，单击“确定”，弹出“IF函数”框（如图11-3所示）。图11-3 使用IF函数5. 在“Logical_test”编辑框中键入“产品=新产品”。6. 在“Value_if_true”编辑框中键入“180”，在“Value_if_false”编辑框中键入“20”，单击“确定”按钮。由于“产品”单元格中还没有数值，即不为“新产品”，所以B10单元格中显示数值“20”（如图11-4所示）。图11-4 逻辑函数计算结果将B10单元格中的公式复制到所有对应新产品畅销的单元格中。然后在对应现有产品畅销的单元格中输入逻辑计算公式。在熟悉IF函数以后，也可以直接在编辑栏中键入引用IF函数的公式，而不必使用“粘贴函数”按钮。操作步骤如下：1. 单击B11单元格。2. 在编辑栏中键入“=IF（产品=现有产品，180，20）”。3. 单击“输入”按钮。4. 将B11单元格中的公式复制到所有对应现有产品畅销的单元格中。由于“产品”单元格中没有数值，即既不为“新产品”，也不为“现有产品”，所以所有的年净收益单元格中都显示数值“20”。按照生产新产品的方案在“投资”单元格和“产品”单元格中键入数据，计算表格中显示对应的年净收益数值，如图11-5所示。图11-5 生产新产品时的年净收益步骤三：设置单元格的有效数据范围在上个步骤中，用于计算年净收益的逻辑函数引用了“产品”单元格。计算结果由“产品”单元格中的数据决定。如果在向该单元格中输入数据时稍出差错，例如，不小心多键入了一个空格，将会造成年净收益的计算错误。为了避免这种情况的发生，为该单元格设置有效的数据范围，使该单元格的数据输入只能从下拉列表中选择。操作步骤如下：1. 选定“产品”单元格（B5）。2. 单击“数据”菜单中的“有效数据”命令，弹出“有效数据”对话框（如图11-6所示）。图11-6 设置单元格的有效数据范围3. 在“许可”下拉列表中选择“序列”。4. 在“来源”编辑框中键入“新产品,现有产品”。注意：在“来源”编辑框中键入的可选单元格数据系列中，必须用英文输入法下的逗号来分隔。5. 选中“提供下拉箭头”复选框。6. 单击“错误警告”选项卡（如图11-7所示）。图11-7 设置错误警告信息7. 确定选中“输入无效数据时显示出错警告”复选框。8. 在“图案样式”下拉列表框中选择“信息”。9. 在“错误信息”编辑框中键入“请在下拉列表中选择输入选项”。10. 单击“确定”按钮。经过上述步骤，Excel为“产品”单元格设置下拉列表。当单击该单元格时，将在单元格右侧显示下拉箭头按钮，单击下拉箭头按钮，显示在“有效数据”对话框中设置的列表选项，如图11-8所示。图11-8 为单元格设置下拉列表如果在单元格中输入了错误的数据，例如在“新”字与“产”字之间多输入了一个空格，完成输入时，Excel将显示出错信息，如图11-9所示。图11-9 显示出错信息步骤四：计算净现值下面计算投资的净现值。所谓净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。它是用来比较方案优劣的重要指标。一、NPV函数简介NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。语法：NPV(rate、value1、value2)参数：rate为各期贴现率，是一固定值。value1，value2代表1到29笔支出及收入的参数值。value1、value2所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。NPV按次序使用value1、value2来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略。如果参数是一个数组或引用，只有其中的数值部分计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。说明：函数NPV假定投资开始于value1现金流所在日期的前一期，并结束于最后一笔现金流的当期。函数NPV依据未来的现金流计算。如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须加入到函数NPV的结果中，而不应包含在values参数中。如果n是values参数表中的现金流的次数，则NPV的公式为：二、计算年净收益期望值在用NPV函数计算净现值时，需要用到各期的净收益值。在计算净现值之前首先计算年净收益期望值。在B15单元格中键入计算第一年期望净现值的计算公式“=B10*$F10+B11*$F11+B12*$F12+B13*$F13+B14*$F14”选中该单元格，用鼠标拖拉填充柄，将公式复制到其他各年的期望年净收益单元格中。计算表格中显示各期的年净收益期望值，如图11-10所示。图11-10 计算年净收益期望值三、使用NPV函数计算净现值下面用NPV函数计算净现值，操作步骤如下：1. 单击B16单元格。提示：在对单元格进行合并及居中操作后，合并的单元格的引用采用合并区域左上角的单元格的引用。2. 单击“粘贴函数”按钮。3. 在“粘贴函数”对话框中，在“函数分类”列表框中选择“财务”，在“函数名”列表框中选择“NPV”。4. 单击“确定”，弹出“NPV函数”框（如图11-11所示）。5. 在“Rate”编辑框中键入“基准折现率”单元格的引用。6. 在各“Value”编辑框中键入各期年净收益期望值的单元格引用。图11-11 设置NPV函数的参数7. 单击“确定”。8. 由于NPV函数没有计算本年度的现金流，还应在原来的基础上减去投资额。在编辑框中原公式的后面加上“-投资”，公式成为“=NPV(B3,B15,C15,D15,E15)-投资”。经过上述步骤，计算表格中显示出净现值的计算结果（如图11-12所示）。图11-12 计算净现值步骤五：多方案求解在图11-12所示的计算表格中，只显示出一种方案的计算结果。在基本数据表格中，输入另一种方案的数据，可得出另一方案的净现值。对于这种多方案的问题，使用“方案管理器”可以更好地管理数据和信息。还可创建方案总结报告和方案数据透视表，便于对各方案进行分析比较。一、创建方案创建方案的步骤如下：1. 单击“工具”菜单中的“方案”命令，弹出“方案管理器”对话框（如图11-13所示）。图11-13 “方案管理器”对话框2. 单击“添加”按钮，弹出“添加方案”对话框（如图11-14所示）。图11-14 添加方案3. 在“方案名”编辑框中，键入方案名称“扩建厂”。4. 在“可变单元格”编辑框中，键入“投资，产品”。提示：对话框中“保护”标题下的复选框用于设置对方案的保护。选中“防止更改”复选框可禁止对方案的更改，选中“隐藏”复选框，可隐藏方案。在这里设置了对方案的保护后，还应对工作表进行保护，方法是：将鼠标指向“工具”菜单中的“保护”选项，然后单击子菜单中的“保护工作表”命令。在编辑或删除方案之前，必须清除“防止更改”复选框。5. 单击“确定”按钮，弹出“方案变量值”对话框（如图11-15所示）。图11-15 输入方案变量值6. 在“方案变量值”对话框中，键入投资额为“100”，产品为“现有产品”。7. 单击“添加”按钮，重复步骤3到步骤6，创建名为“新建厂”的方案，投资额为“300”，产品为“新产品”。8. 单击“确定”按钮，在方案管理器中显示已有的方案（如图11-16所示）。图11-16 在“方案管理器”中显示已有的方案列表此时在“方案”列表框中单击选中一种方案，单击“删除”按钮可将其删除，单击“编辑”按钮，进入“编辑方案”对话框，可对其进行编辑。9. 单击“关闭”按钮。经过上述步骤，方案的创建完成。二、显示方案目前，计算表格中显示的是“新建厂”方案的数据，下面在表格中显示“扩建厂”方案的计算结果。操作步骤如下：1. 单击“工具”菜单中的“方案”命令。2. 在“方案管理器”对话框中，单击选中“方案”列表框中的“扩建厂”选项。3. 单击“显示”按钮。4. 单击“关闭”按钮。在计算表格中显示出对“扩建厂”方案的计算结果（如图11-17所示）。图11-17 “扩建厂”方案的计算结果三、创建方案总结报告为了对方案进行比较分析，可创建方案总结报告。为明晰数据，首先将显示净现值的合并单元格命名为“净现值”。创建方案总结报告的操作步骤如下：1. 单击“工具”菜单中的“方案”命令。2. 单击“总结”按钮，弹出“方案总结”对话框（如图11-18所示）。图11-18 “方案总结”对话框3. 单击“方案总结”选项按钮。4. 在“结果单元格”编辑框中，键入“净现值”。提示：在“结果单元格”中，可输入多个单元格的引用或名称，各引用需用逗号分隔。在生成方案总结报告时不一定需要结果单元格，而在生成方案数据透视表报告时则一定需要。5. 单击“确定”。Excel在当前工作表之前插入一张名为“方案总结”的工作表，在其中显示各方案及表格当前值的计算结果，如图11-19所示。图11-19 方案总结报告步骤六：共享工作簿投资决策的指定决不是简单的比较各方案的预计净现值就可以决定的，毕竟净现值只是需要考虑的一个重要的方面，而且计算得出的结果只是预测结果。投资决策的指定，还需要考虑其他方面的各种因素。在企业管理中进行科学的决策，应避免一人说了算。众人讨论的结果要更加科学、可靠。电脑网络使得人们不必坐在一起，就可以进行讨论。在用连网的电脑进行决策讨论时，应共享所有的决策参考数据。将这个辅助投资风险分析的工作簿设置为共享工作簿，可使多人同时参阅该工作簿或对该工作簿进行操作，例如在工作簿中添加批注。一、共享工作簿简介通过建立共享工作簿，可以与其他人同时审阅和编辑同一份工作，还可以查看各自所做的改动。当多人一起在共享工作簿上工作时，Excel会保持信息不断被更新。在一个共享工作簿中，各个用户可以输入数据，插入行和列，添加和更改公式，还可以更改格式。每个用户能够独立地筛选工作表以显示感兴趣的数据行。Excel可以为每一位用户保留各自的视面，其中包含用户的各种筛选设置。各用户都可通过单击“保存”按钮，以按自己所做的更改更新共享工作簿。还可得到所有其他用户保存的更改内容。Excel能够自动按指定的时间间隔对更改进行更新。在保存一个共享工作簿时，可以用自己所做的更改替换他人作出的相冲突的更改，也可以审查每一种更改以决定是否接受。通过保存冲突日志，可以保留被接受更改的记录，并可以查看一个记录着详细更改情况的日志工作表，其中包含用户间互相冲突的更改内容。保留冲突日志还使用户能够合并共享工作簿的各种副本。在共享工作簿时，既可以给每个人相同的权限，也可以通过保护共享工作簿来限制权限。二、设置共享工作簿下面将这个辅助投资风险分析的工作簿设置为共享工作簿，操作步骤如下：1. 单击“工具”菜单中的“共享工作簿”命令，弹出“共享工作簿”对话框（如图11-20所示）。图11-20 设置共享工作簿2. 单击“编辑”选项卡，选中“允许多用户同时编辑，同时允许工作簿合并”复选框，然后单击“确定”。3. 单击“确定”，保存工作簿。4. 在“文件”菜单中单击“另存为”命令，然后将共享工作簿保存在其他用户可以访问到的一个网络资源上。注意：如果要将共享工作簿复制到一个网络资源上，应确保该工作簿与其他工作簿或文档的任何链接都保持完整。可以使用“编辑”菜单中的“链接”命令对链接定义进行修正。这一步骤同时也启用了冲突日志，使用它可以查看对共享工作簿的更改信息，以及在有冲突时修改的取舍情况。三、撤消工作簿的共享状态如果不再需要其他人对共享工作簿进行更改，可以将自己作为唯一用户打开并操作该工作簿。撤消工作簿的共享状态的操作如下：1. 单击“工具”菜单中的“共享工作簿”命令，然后单击“编辑”选项卡。2. 确认自己是在“正在使用本工作簿的用户”框中的唯一一位用户，如果还有其他用户，他们都将丢失未保存的工作内容。3. 清除“允许多用户同时编辑，同时允许工作簿合并”复选框，然后单击“确定”按钮。4. 当提示到对其他用户的影响时，单击“是”按钮。注意：一旦撤消了工作簿的共享状态，将中断所有其他用户与共享工作簿的联系、关闭冲突日志，并清除已存储的冲突日志，此后就不能再查看冲突日志，或是将共享工作簿的此备份与其他备份合并。为了确保其他用户不会丢失工作进度，应在撤消工作簿共享之前确认所有其他用户都已得到通知，这样，他们就能事先保存并关闭共享工作簿。四、保护共享工作簿能够访问保存有共享工作簿的网络资源的所有用户，都可以访问共享工作簿。如果希望防止对共享工作簿的某些访问，可以通过保护共享工作簿和冲突日志来实现。与一般工作簿一样，也可以为共享工作簿指定一个打开时输入的密码，且方法相同，详细内容参阅本书实例七。防止他人对共享工作簿进行更改的操作步骤如下：1. 为设置共享工作簿的改动密码，先撤消对工作簿的共享。2. 隐藏不希望其他用户看到的某些行和列，取消允许其他人进行更改的指定区域的锁定。3. 在“工具”菜单中将鼠标指向“保护”子菜单，然后单击“保护并共享工作簿”命令，弹出“保护共享工作簿”对话框（如图11-21所示）。4. 选中“以追踪修订方式共享”复选框。5. 设置其他用户在关闭冲突日志或撤消工作簿共享状态时须输入的密码，在“密码”框中键入密码，单击“确定”。6. 在“确认密码”对话框中再输入一遍密码，单击“确定”。7. 在出现提示时，单击“确定”保存工作簿，这样可以共享此工作簿并且启用冲突日志。图11-21 “保护共享工作簿”对话框注意：为工作簿提供共享保护以后，其他用户就不能撤消工作簿共享状态或者关闭冲突日志。在一个已经共享的工作簿中，可以启用对共享和冲突日志的保护，但是不能为这种保护指定密码。如果需指定密码，必须首先撤消工作簿的共享状态。本例中重要讲述了逻辑公式的建立，设置单元格的有效数据范围，财务函数NPV的使用，多方案求解问题以及工作簿的共享。IF函数用于执行真假值判断，根据逻辑测试的真假值，返回不同的结果。可以使用函数IF对数值和公式进行条件检测。通过对单元格有效数据范围的设置，可以防止由于数据输入错误造成的计算错误。本例中，为单元格设置了有效数据的下拉列表。对于多方案的问题，使用“方案管理器”可以更好地管理数据和信息。还可创建方案总结报告和方案数据透视表，便于对各方案进行分析比较。通过建立共享工作簿，可以与其他人同时审阅和编辑同一份工作，还可以查看各自所作的改动。问题一：有效数据有哪些类型答：“有效数据”对话框中显示的选项根据“许可”和“数据”下拉列表框中设置的不同而不同。可选的有效数据的类型如下：任何数值对输入数据不作任何限制。如果希望不检查输入的正确性而只显示输入信息，可使用此设置。自定义这种设置允许输入公式、使用表达式或者引用其他单元格中的计算值来判定输入数值的正确性。公式必须以等号“”开始，且得出的必须是True或False。日期指定输入的数值必须为日期。在“数据”下拉列表框中单击选定一个操作符，接着填充下面的编辑框，如“起始日期”、“终止日期”，可指定输入日期的范围。小数指定输入的数值必须为数字或小数。在“数据”下拉列表框中单击选定一个操作符，接着填充下面的编辑框，如“最小值”、“最大值”，可指定输入小数的范围。序列为有效数据指定序列。在“来源”编辑框中此输入工作簿上包含有效数据的区域的引用或名称或者以逗号为间隔符直接键入有效数据（例如：销售部、生产部、技术部、公关部）。选中“提供下拉箭头”复选框，将在用户单击单元格时显示一下拉箭头，让用户在有效数据序列中选择。文本指定有效数据的字符数。在“数据”下拉列表框中单击选定一个操作符，接着填充下面的编辑框，如“最小值”、“最大值”，可指定输入数据字符数的范围。时间指定输入的数值必须为时间。在“数据”下拉列表框中单击选定一个操作符，接着填充下面的编辑框，如“起始时间”、“ 终止时间”，可指定输入时间的范围。整数指定输入的数值必须为整数。在“数据”下拉列表框中单击选定一个操作符，接着填充下面的编辑框，如“最小值”、“最大值”，可指定输入整数的范围。问题二：为何“数据”菜单中的“有效数据”命令不能使用答：由于下列原因，“有效数据”命令将不能使用：正在输入数据。当设置了有效数据范围或信息的单元格中正在进行编辑时，“有效数据”命令不能使用。工作表内容可能处于保护状态。如果要取消保护，指向“工具”菜单中的“保护”子菜单，再单击“撤消工作表保护”命令。工作簿可能是共享工作簿的。尽管仍可以继续输入数据，并且输入信息和错误信息也照常显示，但是在工作簿被共享时，“有效数据”命令不能使用。问题三：使用共享工作簿有哪些限制答：使用共享工作簿时，Excel的某些功能无效，如果需要使用这些功能，应在将工作簿共享之前进行操作，或撤消工作簿的共享状态。在共享工作簿中，不能完成下列操作：删除工作表合并单元格可以在将工作簿共享之前查看合并单元格的单元格。定义或使用条件格式可以在工作簿共享之前查看条件格式的使用效果。设置或更改数据有效性的限制和消息可以在工作簿共享之前查看所设置的限制和消息的效果。成块插入或删除单元格可以插入或删除整个行和列。插入或更改图表、图片、对象或超级链接使用绘图工具设置密码来保护单独的工作表或整个工作簿在工作簿共享之前使用的保护措施，在工作簿共享之后依然有效。更改或删除密码在工作簿共享之前设置的密码，在工作簿共享之后依然有效。保存、查看或更改方案创建组或分级显示数据插入自动分类汇总创建模拟运算表创建数据透视表或更改已存在的数据透视表布局写入、更改、查看、记录或分配宏可以将共享工作簿中所录制的宏保存到另一个未共享的工作簿中。在共享工作簿中，还可以使用工作簿共享之前创建的宏，但在这种情况下，如果所使用的宏中包含某个此时无效的操作，宏将在运行到此无效操作时停止运行。问题四：如何为工作簿保存冲突日志答：如果为工作簿保存冲突日志，Excel会同时开启工作簿共享。保存冲突日志的操作步骤如下：1. 单击“工具”菜单中的“共享工作簿”命令。2. 单击“编辑”选项卡。3. 选定“允许多用户同时编辑，同时允许工作簿合并”复选框。4. 单击“高级”选项卡（如图11-22所示）。图11-22 保存冲突日志5. 在“修订”标题下，单击“保存修订记录”，接着在“天”微调编辑框中键入希望保留冲突日志的天数。6. 单击“确定”按钮。当弹出对话框提示保存工作簿时，再次单击“确定”按钮。问题五：如何查看共享工作簿中有关相互冲突更改的信息答：在创建共享工作簿以后，冲突日志就被启用，可以查看以前有关相互冲突更改的信息。如果关闭冲突日志，Excel将不再保留有关相互冲突更改的信息。查看冲突日志的操作步骤如下：1. 指向“工具”菜单中的“修订”子菜单，然后单击“突出显示修订”命令，弹出“突出显示修订”对话框（如图11-23所示）。图11-23 查看冲突日志2. 选中“时间”选框，然后单击“时间”框中的“全部”。3. 确认“修订者”和“位置”复选框已被清除。4. 选中“在新工作表上显示修订”复选框，然后单击“确定”按钮。Excel在工作簿中插入“冲突日志”工作表（如图11-24所示），可在其中查看工作表的更改记录。图11-24 查看冲突日志被保留的相互冲突的更改在“操作类型”列中显示为“成功”，用于在冲突日志工作表中说明被舍弃更改的数据行，将在“操作失败”列中显示行号。第十章 完成复杂计算 公式是对单元格中数值进行计算的等式，使用公式可以进行数据计算。函数是Excel2000中预定的内置公式，使用函数可以提高公式计算的效率。数组是一种计算工具，可用来建立产生多个数值或对一组数据进行操作的公式。综合使用公式、函数和数组可以在Excel中完成复杂计算。第一节 创建与编辑公式 使用公式可进行例如加、减、乘、除等简单的计算，也可以完成很复杂的财务、统计及科学计算，还可以用公式进行比较或操作文本。公式是工作表的核心，如果没有公式，Excel2000这样的电子表格软件就失去了其存在的意义。下面是几个公式的例子： 73十27 sum(A1：B7) 收入支出 上面的例子体现了Excel公式的语法，即公式以等号开头，后面紧接着运算数和运算符，运算数可以是常数、单元格引用、单元格名称和工作表函数。 一、创建公式1 公式中的运算符Excel的运算符有以下4类：算术运算符：完成基本数学运算，如加、减、乘、除等，它们连接数字并产生计算结果。比较运算符：用来比较两个数值大小关系的运算符，它们返回逻辑值TRUE或FALSE。文本运算符：用来将多个文本连接成组合文本。引用运算符：可以将单元格区域合并运算。各种运算符的含义及示例请见表101。表10.1 Excel公式中的运算符算术运算符含 义示例+（加号）加1+2（减号）减21（负号）负数1*（星号）乘2*2/（斜杠）除4/2%（百分比）百分比12%（脱字符）乘幂32 （续表）比较运算符含 义示例=（等号）等于A1=A2（大于号）大于A1A2（小于号）小于A1=（大于等于号）大于等于A1=A2=（小于等于号）小于等于A1=A2（不等号）不等于A1A2文本运算符含义示例&（连字符）将两个文本连接起来产生连续的文本“学会”&“求知”产生“学会求和”引用运算符含义示例：（冒号）区域运算符，对两个引用之间包括这两个引用在内的所有单元格进行引用A1：D1（引用A1到D1范围内的所有单元格），（逗号）联合运算符，将多个引用合并为一个引用SUM（A1：D1，A2：C2）将A1：D2和A2：C2两个区域合并为一个（空格）交叉运算符，产生同时属于两个引用的单元格区域的引用SUM（A1：F1 B1：B4）（B1同时属于两个引用A1：F1，B1：B4） 2公式的运算顺序 运算符（优先级从高到低）说明 ：（冒号） ，（逗号） （空格） （负号） %（百分号） （脱字符） *和/ +和 & =、=、=、 区域运算符 联合运算符 交叉运算符 5 百分比 乘幂 乘和除 加和减 文本运算符 比较运算符每个运算符都有自己的运算优先级，表10.2列出了各种运算符的优先级，对于不同优先级的运算，按照优先级从高到低的顺序进行。对于同一优先级的运算，按照从左到右的顺序进行。使用括号把公式中优先级低的运算括起来，可以改变运算的顺序。表10.2 各种运算符的优先级 二、公式的输入 1在编辑栏中输入公式 像输入数字或文本一样键入公式后，再按Enter键或单击“输入”按钮。 2在单元格里直接输入公式 双击要输入公式的单元格，或者先选中单元格再按F2键后，在单元格中输入公式，最后按Enter键。 3.举例说明输入公式的具体步骤 (1)建立一个如图10.1所示的工作表。 (2)选定单元格D2。 (3)在编辑栏中输入“B2+C2”。 (4)按Enter键，D2中将显示公式的计算结果158。 例101创建并输入公式，将图101工作表中B4和C4单元格中的两个文本连接为一个文本。 (1)选定单元格E4。 (2)在编辑栏中输入“”，然后用鼠标单击B4单元格，发现编辑栏中显示“B2”，在编辑栏中输入“”，再用鼠标单击C4单元格，编辑栏中显示“B4C4”。 (3)按Enter键，E4单元格中显示“学会求知”，如图102所示，这是公式“B4&C4”的计算结果。图101工作表示例图 102文本连接的计算结果 实用技巧 当要在一个单元格区域中输入同一个公式时，先选定该区域，如图103所示，输入公式(“B6十C6”或“B6:B8十C6：C8”)后，按“Ctrl十Enter”键，执行以上操作就不必在每个单元格中逐一输入公式。图103选定单元格区域示例 在按Enter键确认输人的公式之前，公式实际上并没有被存储在单元格中，可以单击编辑栏左边的“取消”按钮或按Esc键来取消输入的公式。 三、公式的编辑 单元格中的公式也可以像单元格中的其他数据一样进行编辑，例如修改、复制、移动等。 1修改公式 修改公式同修改单元格中数据的方法一样。先单击包含要修改公式的单元格，如果要删除公式中的某些项，在编辑栏中用鼠标选中要删除的部分后，再按Backspace或者Delete。键。如要替换公式中的某些部分，须先选中被替换的部分，然后再进行修改。在未确认之前单击“取消”按钮或按Esc键放弃本次修改。如果已确认修改但还未进行其他命令，单击“编辑”菜单中的“撤消”命令或按“Ctrl十Z”键仍可放弃本次修改。 2复制公式 以将图103单元格D2中的公式复制到单元格D6中为例，操作步骤如下： (1)选定单元格D2。 (2)单击“编辑”菜单中的“复制”命令，或按“Ctrl十C”快捷键。 (3)单击D6单元格。 (4)单击“编辑”菜单中的“选择性粘贴”选项，弹出如图104所示的“选择性粘贴”对话框。图10.4“选择性粘贴”对话框 (5)在“选择性粘贴”对话框中选择“公式”单选按钮。 (6)单击“确定”按钮，D6中显示26，即已将D2中的公式复制过来。 3移动公式 以将图10.3单元格D2中的公式复制到单元格G2中为例，操作步骤如下： 图10.4“选择性粘贴”对话框 (1)选定D2单元格。 (2)将鼠标移到D2单元格的边框上，当鼠标变为白色箭头时按下左键。 (3)拖动鼠标到G2单元格。 (4)释放左键。 也可以用菜单命令或“常用”工具栏上的工具按钮像移动单元格一样来移动公式。 四、使用复杂公式 1.公式中的数值转换在Excel中数据是分类型的，例如数字型、文本型、逻辑型等。在公式中，每个运算符都只能连接特定类型的数据。如果运算符连接的数值与所需的类型不同，Excel能自动转换数值类型。表10.3给出了几个数值转换的例子。 表10.3 公式中数值转换示例公 式运算结果说 明=“4”*“7”28当使用+、*、/等运算符时，Excel认为运算数是数字。Excel自动将字符型数据“4”和“7”转换为数字=“99/7/20” “98/7/20”365Excel将具有yy/mm/dd格式的文本当作日期，将日期转换成序列数之后，再进行计算SUM（“3+2”，5）3&“Word”#VALUE！3Word返回出错值，因为Excel不能将文本（3+2）转换成数字，而SUM（“5”，5）可以返回10当公式中需要文本型数值时，Excel自动将数字转换成文本 2日期和时间的使用 在Excel2000中不仅可以对数字和字符进行计算，也可以对日期和时间进行计算。 Excel中显示的时间和日期数字(例如34412.25)，是以1990年1月1日星期日为日期起点，数值设定为1；以午夜零时(0：00：00)为时间起点，数值设定为0.0，其范围是24小时。 日期的计算中经常用到两个日期之差，例如公式“981020”“98105”，计算结果为15。也可以进行其他计算，例如公式“99720”十“9975”，计算结果为72707。在Excel2000中输入日期时如果以短格式输入年份(年份输入两位数)，Excel将做如下处理： (1)如果年份在00至29之间，Excel将作为2000至2029年处理，例如输入101020，Excel认为这个日期是2010年10月20日。 (2)如果年份在30至99之间，Excel将其作为1930至1999年处理，例如输入73323，Excel认为这个日期是1973年3月23日。 五、公式返回的错误值和产生原因 在使用公式进行计算时，有时会在单元格中看到“#NAME?”、“#VALUE?”等信息。这些都是使用公式时出现错误后返回的错误值，产生原因请见表104。表10.4 公式返回的错误值及其产生原因返回的错误值产生的原因# # # # #！公式计算的结果太长，单元格容纳不下，增加单元格的列宽可以解决这个问题# DIV/0除数为零# N/A公式中无可用的数值或缺少函数参数# NAME？使用了Excel不能识别的名称# NULL！使用了不正确的区域运算或不正确的单元格引用# NUM！在需要数字参数的函数中使用了不能接受的参数，或者公式计算结果的数字太大或太小，Excel无法表示# REF！公式中引用了无效单元格# VALUE！需要数字或逻辑值时输入了文本第二节 单元格的引用 单元格的引用就是指单元格的地址，单元格的引用把单元格中的数据和公式联系起来。在创建和使用复杂公式时，单元格的引用是非常有用的。Excel2000通过单元格引用来指定工作薄中的单元格或单元格区域。 一、单元格引用及引用样式 单元格引用的作用在于标识工作表上的单元格和单元格区域，并指明使用数据的位置。通过引用可以在公式中使用单元格中的数据。单元格引用有不同的表示方法，即可以直接用相应的地址表示，也可以用单元格的名字表示。 用地址来表示单元格引用有两种样式： A1引用样式：这是默认样式。这种引用是用字母来表示列(从A到IV共256列)，用数字来表示行(从1到65536)。引用的时候，先写列字母再写行数字，如B2。 R1C1样式，R代表Row，是行的意思；C代表Column，是列的意思。在R1C1引用样式中，用R加行数字和C加列数字来表示单元格的位置，如R3C2指位于第3行第2列上的单元格。 在A1引用样式中又包括绝对引用、相对引用和混合引用三种样式。 相对引用 相对引用的意义是指单元格引用会随公式所在单元格的位置变更而改变。也就是说，相对引用在被复制到其他单元格时，其单元格引用地址发生改变。相对引用的样式是用字母表示列，用数字表示行，例如A1、B2等，但是只使用相对引用是无法满足使用需要的。 绝对引用 绝对引用是指引用特定位置的单元格。如果公式中的引用是绝对引用，那么复制后的公式引用不会改变。绝对引用的样式是在列字母和行数字之前加上美元符$，例如由$A$2、$B$5都是绝对引用。 混合引用 除了相对引用和绝对引用之外，还有混合引用。当需要固定某行引用而改变列引用，或者需要固定某列引用而改变行引用时，就要用到混合引用，例如$B5、B$5都是混合引用。 在Excel2000中，使用F4键可以快速改变单元格引用的类型。示例如下： (1)选择单元格A1然后链入：“$B$2”。 (2)按F4键将引用变为绝对引用，该公式变为：“$B$2”。 (3)再按F4健将引用变为混合引用(绝对行，相对列)，公式变为：“B$2”。 (4)再按F4键将引用变为另一种混合形式(绝对列，相对行)，公式变为：“$B2”。 (5)再按P4键返回到原来的相对引用形式。 二、输入单元格引用 在Excel中使用鼠标输入单元格引用比用键盘节省时间而且准确率高。 例10.2用鼠标在单元格A3中输入对A1和A2的引用。 (1)选择A3，然后键人一个等号“”。 (2)单击A1并键人一个加号“十”。 (3)单击A2后按Enter键。 当单击某单元格时，闪烁的边框环绕着该单元格，同时在A3中插入了对该单元格的引用。在结束公式的输入时，必须按Enter键。若没按Enter键并且选择了别的单元格，Excel便认为要在公式中包括该单元格的引用，而不仅仅是以前指定的单元格。 在向活动单元格输入数值或其他单元格的引用时，活动单元格不必出现在当前窗口中创建公式时，可以通过滚动条波动工作表来选择工作表中距离公式单元格较远的单元格。不管活动单元格位于工作表的什么位置，编辑栏总是显示活动单元格中的内容。 实用技巧 若滚动工作表后活动单元格不再可见，按“CtrlBackspace”键可快速重新显示活动单元格。 在编辑公式时，被该公式所引用的所有单元格及单元格区域都将以彩色显示在存放公式的单元格中，并在相应单元格及单元格区域的周围显示具有相同颜色的边框。 三、引用其他工作表中的单元格 在Excel中，不仅可以引用当前工作表的单元格，还可以引用工作簿中其他工作表，其方法是：在公式中同时包括工作表引用和单元格引用。例如，要引用工作表Sheet9中的B2单元格，应在公式中输入Sheet9!B2。感叹号将工作表引用和单元格引用分开。如果工作表已命名，只需使用工作表名字再加上单元格引用。但是如果工作表名字中包含空格，必须用单引号括住工作表引用。 使用鼠标也可以引用工作簿中另一张工作表的单元格或单元格范围，其方法是：进入输入公式的状态，然后单击需要引用的单元格所在的工作表标签，选中需要引用的单元格，则该单元格引用会显示在编辑栏中。如果工作表名字包括空格，Excel2000会自动用单引号括住工作表引用，最后按Enter键完成公式的输入。 四、引用其他工作薄中的单元格 在Excel中，不但可以引用同一工作薄中不同工作表的单元格，还能引用不同工作薄中的单元格。其方法是：在公式中同时包括工作薄引用、工作表引用和单元格引用。例如： Book1Sheetl!$A$1一Book2Sheet2!$B$1 在上面的公式中，Book1和Book2是两个不同工作簿的名称，Sheet1和Sheet2是分别属于两个工作簿的工作表的名称。$A$1和$B$1表示单元格的绝对引用。若引用的工作簿已关闭，那么在引用中将出现该工作簿存放位置的全部路径，例如： Sheet1!$A$1C:MY DOCUMENTSBook2.XLSSheet2!$B$1第三节函数 函数是一些已经定义好的公式，Excel2000中的大多数函数是常用公式的简写形式。函数通过参数接收数据，输入的参数应放到函数名后并且用括号括起来。各函数使用特定类型的参数，例如：数字、引用、文本或编辑值等。函数大多数情况下返回的是计算的结果，也可以返回文本、引用、逻辑值、数组或者工作表的信息。 在Excel2000中，不仅提供了大量的内置函数，还可以根据特定的需要使用Visual Basic自定义函数。使用公式时尽可能地使用内置函数，它可以节省输入时间，减少错误发生。 一、Excel内置函数Excel提供了大量的内置函数，按照功能进行分类，如表105所示。 表10.5 内置函数分类分 类功 能 简 介数据库工作表函数分析数据清单中的数值是否符合特定条件日期与时间函数在公式中分析和处理日期值和时间值工程函数用于工作分析信息函数确定存储在单元格中数据的类型财务函数进行一般的财务计算逻辑函数进行逻辑判断或者进行复合检验统计函数对数据区域进行统计分析查找和引用函数在数据清单中查找特定数据或者查找一个单元格的引用文本函数在公式中处理字符串数学和三角函数进行数学计算 二、常用函数 Excel2000提供了几百个内置函数，下面只介绍常用的函数，有关其他函数的用法，可以使用Excel2000的帮助进行学习。 1SUM函数 功能：SUM函数用于计算单个或多个参数之和。 语法：SUM(number1,number2，) number1,n