第六章成长期企业财务预算 3 内容简介 本书以 Excel 2007 为平台, 系统介绍了 Excel 软件在财务管理中的应用 全书分为八章, 主要内容包括 Excel 2007 软件基础 Excel 在财务管理基础观念中的应用 Excel 在筹资管理中的应用 Excel 在流动资产管理中的应用 E

Transcription

1 高等职业教育 十二五 规划教材 高职高专会计类精品教材系列 Excel 在财务管理中的应用 ( 第二版 ) 李慧主编 北 京

2 第六章成长期企业财务预算 3 内容简介 本书以 Excel 2007 为平台, 系统介绍了 Excel 软件在财务管理中的应用 全书分为八章, 主要内容包括 Excel 2007 软件基础 Excel 在财务管理基础观念中的应用 Excel 在筹资管理中的应用 Excel 在流动资产管理中的应用 Excel 在投资管理中的应用 成长期企业财务预算案例 创业企业财务预测案例及个人 / 家庭理财方案设计案例 本书可以作为高等职业技术学院 成人高等教育学校及民办高校经济管理类专业的教学用书, 也可以作为财会工作者及各类管理人员自学提高业务水平的读物 图书在版编目 (CIP) 数据 Excel 在财务管理中的应用 / 李慧主编. 2 版. 北京 : 科学出版社,2014 ( 高等职业教育 十二五 规划教材 高职高专会计类精品教材系列 ) ISBN Ⅰ.1E Ⅱ.1 李 Ⅲ. 表处理软件 - 应用 - 财务管理 - 高等职业教育 - 教材 Ⅳ.1F 中国版本图书馆 CIP 数据核字 (2014) 第 号 责任编辑 : 唐寅兴 / 责任校对 : 马英菊责任印制 : 吕春珉 / 封面设计 : 东方人华平面设计部 出版北京东黄城根北街 16 号邮政编码 : 印刷科学出版社发行各地新华书店经销 * 2007 年 9 月第一版开本 : / 年 8 月第二版印张 : 年 8 月第七次印刷字数 : 定价 : 元 ( 如有印装质量问题, 我社负责调换 世知 ) 销售部电话 编辑部电话 版权所有, 侵权必究举报电话 : ; ;

3 第二版前言 财务管理是会计学 工商管理等专业的主干课程之一, 具有实践性较强 计算量较大等特点 本书以 Excel 2007 版软件为平台, 系统介绍了 Excel 软件在筹资管理 投资管理 流动资产管理等财务管理活动中的应用, 使学生在掌握财务管理基本理论和方法的基础上, 能够熟练应用 Excel 2007 软件对企业 个人的财务活动进行有效管理 本书具有以下特点 : 1. 循序渐进, 能力导向针对财务管理的内容和特点, 本书首先系统阐述了 Excel 2007 软件在财务管理基础观念 筹资管理 投资管理及流动资产管理等财务活动中的应用要点, 夯实学生操作技能的基础, 然后引进成长期企业财务预算 创业企业财务预测和个人理财方案设计三个综合案例, 渐进培养学生利用 Excel 软件分析 解决财务管理实际问题的综合能力, 为学生参与企业财务管理实践 创业实践 创业计划大赛及个人理财规划等活动提供有力帮助 2. 图文并茂, 步骤详尽本书充分利用 Excel 2007 软件强大的数据处理和分析功能, 通过文字 图片及表格相结合的方式全方位介绍软件在财务管理实践活动中的具体应用, 思路清晰, 步骤详尽, 每章均附有习题, 易学易用, 既可以作为高校会计学 财务管理 工商管理等专业的教学用书, 也可作为财会工作者 企业管理者 创业者及银行 保险等相关领域专业技术人员的参考读物 3. 体系完整, 内容精练本书覆盖 Excel 软件在筹资管理 投资管理及流动资产管理等财务管理活动以及财务预测 财务预算 财务分析等财务管理环节应用的主要内容, 篇幅仅为同类教材的三分之二, 适合各级各类学校教学选用 本书由天津职业技术师范大学李慧撰写 在编写过程中, 天津职业技术师范大学林永春和山西经济管理干部学院刘智远提供了大力支持, 谢铭泉 姜志云 马从彦 李丹丹 陈婷婷 张骁等同学参加了案例收集 数据计算等工作 本书在撰写过程中参阅了大量的相关著作, 并得到科学出版社的大力支持, 成书之时, 作者表示衷心的感谢! 囿于作者的水平, 书中存在的缺憾, 恳请读者批评指正 李慧 2014 年 6 月

4 Excel 在财务管理中的应用 2 第一版前言 财务管理是以企业资金运动为对象, 利用价值形式对企业各种资源进行优化配置的管理活动 在现代企业中, 财务管理的重要地位是毋庸置疑的 Excel 2007 电子表格软件具有强大的数据分析 财务函数运算 图表分析等功能, 从而为企业的财务管理工作提供了直观便捷的工具, 可以有效地帮助财务管理人员分析和共享信息以做出更加明智的决策 因此, 本书以 Excel 2007 为平台, 系统介绍了 Excel 2007 软件在企业筹资管理 投资管理等财务管理活动中的应用, 使读者在掌握财务管理基本理论和方法的基础上, 能熟练应用计算机对企业财务活动进行管理 本书具有以下特点 : 1. 简明实用 把 Excel 2007 电子表格软件应用于企业的财务管理工作中, 可以高效 准确地帮助财务管理人员进行预测和决策 本书中所有案例的操作步骤均以简要说明结合图片形式给出, 易学易用 2. 平台新颖 本书以 Excel 2007 版为平台撰写 3. 体系完整 本教材覆盖财务管理基础观念 筹资管理 投资管理 流动资产管理等财务管理主要内容及财务分析 预测和预算等财务管理环节 本书按照财务管理的内容和环节共分为八章 其中, 第二 三 四 五 六章由李慧撰写, 第一 七 八章由刘智远撰写 李慧负责本书框架及结构设计 在撰写过程中, 我们参考并引用了有关专家学者的研究资料, 得到了科学出版社的大力支持, 在此深表感谢! 由于时间紧迫, 加之我们水平有限, 虽倾力投入, 书中难免仍存在疏漏之处, 恳请广大读者批评指正

5 目 录 第一章 Excel 2007 软件基础... 1 第一节 Excel 2007 软件简介... 1 一 Excel 2007 软件的特点... 1 二 Excel 2007 软件的启动与退出... 2 三 Excel 2007 软件的外观... 4 第二节 Excel 2007 软件的基本操作... 9 一 工作表的操作... 9 二 行 列及单元格操作 三 设置行高 列宽 四 合并 拆分单元格 五 数据 文本的输入 六 设置数据格式 七 设置公式函数 八 Excel 2007 软件相对前期版本增加的内容 习题一 术语积累 第二章 Excel 在财务管理基础观念中的应用 第一节财务管理基础观念及 Excel 软件要点 一 财务管理基础观念要点 二 Excel 软件要点 第二节财务管理基础观念中 Excel 的应用 一 单利终值和现值的计算 二 复利终值和现值的计算 三 年金终值和现值的计算 四 资金时间价值计算的特殊问题 五 风险的计量 习题二 术语积累 第三章 Excel 在筹资管理中的应用 第一节筹资管理及 Excel 软件要点 一 筹资管理要点... 85

6 vi Excel 在财务管理中的应用 ( 第二版 ) 二 Excel 软件要点 第二节筹资管理中 Excel 的应用 一 确定筹资规模 二 权益资金的筹集 三 负债资金的筹集 四 杠杆作用分析 五 资本结构的决策方法 习题三 术语积累 第四章 Excel 在流动资产管理中的应用 第一节流动资产管理及 Excel 软件要点 一 流动资产管理要点 二 Excel 软件要点 第二节流动资产管理中 Excel 的作用 一 现金的管理 二 应收账款的管理 三 存货的管理 习题四 术语积累 第五章 Excel 在投资管理中的应用 第一节投资管理及 Excel 软件要点 一 投资管理要点 二 Excel 软件要点 第二节投资管理中 Excel 的应用 一 项目投资的决策 二 项目投资决策的风险分析 三 债券投资的决策 四 股票投资的决策 五 证券投资组合的决策 习题五 术语积累 第六章成长期企业财务预算案例 一 案例目标 二 案例资料 三 Ecxel 软件要点

7 目 录 vii 四 案例分析 习题六 第七章创业企业财务预测案例 一 案例目标 二 案例资料 三 Excel 软件要点 四 案例分析 习题七 第八章个人 / 家庭理财方案设计案例 一 案例目标 二 案例资料 三 Excel 软件要点 四 案例分析 习题八 参考文献

8 Excel 2007 软件基础 第一章 第一节 Excel 2007 软件简介 Excel 2007 软件是 Microsoft Office 办公组件中的一件功能强大的工具, 它可以创建电子表格并为其设置格式, 分析和共享信息以帮助决策者做出更加明智的决策 通过使用面向结果的新界面 丰富的直观数据及数据透视表视图, 可以更加轻松地创建和使用专业水准的图表 Excel 2007 软件界面友好, 操作简单, 应用广泛, 是一个集表格处理 文档处理 数据库处理 绘图 统计 排序 分类等功能于一体的多功能通用电子表格软件 利用 Excel 软件的强大功能及其提供的大量函数, 可以很方便地进行统计分析 预测分析和财务管理等工作 本书着重介绍 Excel 2007 软件在财务管理中的应用 一 Excel 2007 软件的特点 1)Excel 2007 软件拥有的全新用户界面可以帮助用户在需要时找到强大的工具 使用 Excel 2007 软件面向结果的新界面可以帮助用户随时找到所需的工具 根据要完成的工作, 无论是创建表还是编写公式,Excel 2007 软件都可以在全新的用户界面中提供适当的命令 2) 在显著扩展的电子表格中导入 组织和浏览大量数据集 Excel 2007 软件可以处理大量数据, 它支持的电子表格可以多达 行和 列 现在, 在需要分析大量信息时, 用户将不再需要处理多个电子表格或使用其他应用程序 3) 使用重新设计的 Excel 2007 软件制图引擎, 可以帮助用户在具有专业外观的图表中共享用户的分析 使用新用户界面中的制图工具, 用户可以更快地创建出具有专业外观的图表, 而所需的单击动作更少 用户还可以在图表中应用丰富的视觉增强效果,

9 2 Excel 在财务管理中的应用 ( 第二版 ) 如三维 柔和阴影和透明效果 无论使用的是 Microsoft Word 2007 软件还是 Microsoft PowerPoint 2007 软件, 都能够以相同的方式创建图表并与之进行交互, 因为 Excel 2007 软件制图引擎与它们的引擎是一致的 4) 为用户使用表格提供了经过改进的有力支持 由于 Excel 2007 软件已经显著改 进了对表格的支持, 用户现在可以在公式中创建 扩展和引用表格, 还可以设置表格的格式 在分析大型表格中包含的数据时,Excel 2007 软件可以使用户在滚动表时仍然可以看到表标题 5) 轻松创建和使用数据透视表视图 通过数据透视表视图, 可以迅速重新定位数据以便帮助用户回答多个问题 由于 Excel 2007 软件将帮助用户更加方便地创建和使用数据透视表视图, 因此用户可以更快地找到所需答案 6) 查看重要趋势并发现数据中的异常 Excel 2007 软件可以更加轻松地将条件格式应用于信息, 随后可以使用丰富的可视化方案来研究数据的图形并突出显示数据的趋势 7) 使用 Excel 2007 软件和 Excel Services 有助于更加安全地与其他人共享电子表格 Excel Services 可以动态地将电子表格呈现为 HTML 格式, 这样其他人就可以在任何 Web 浏览器中访问存储在 Microsoft SharePoint Server 2007 软件上的电子表格 由于对 Excel 2007 软件客户端而言这种方式的保真度很高, 因此, 用户可以使用 Excel Services 来浏览 排序 筛选 输入参数, 以及与数据透视表视图进行交互, 这一切工作都可以在 Web 浏览器中进行 8) 有助于确保用户及其组织使用最新的业务信息 通过使用 Excel 2007 软件并将对业务非常关键的电子表格发布到 SharePoint Server 2007 软件, 可以防止组织内出现电子表格的多个副本或过期副本, 使用基于权限的访问可以控制哪些用户能够查看和修改服务器上的电子表格 9) 减小电子表格的大小和提高损坏文件的恢复能力 全新的 Microsoft Excel XML 压缩格式可使文件大小显著减小, 同时其体系结构可提高损坏文件的数据恢复能力 这种新格式可以大大节省存储和带宽需求 二 Excel 2007 软件的启动与退出 1.Excel 2007 软件的启动 Excel 2007 软件的启动有以下两种方法 : 1) 单击 1 开始 菜单 2 选择 所有程序 分菜单 3 选择 Microsoft Office 文件夹 4 选择 Microsoft Office Excel 2007 选项, 即可启动 Excel, 如图 1-1 所示 2) 双击任何一个文扩展名为.xls 或者.xlsx 的文件即可启动 Excel, 如图 1-2 所示

10 第一章 Excel 2007 软件基础 图 1-1 Excel 2007 软件的启动 2.Excel 2007 软件的退出 Excel 2007 软件的退出有以下四种方法 : 1) 单击 Excel 窗口右上角的控制按钮 即可退出 Excel 2) 双击 Excel 窗口左上角的 Office 控制按钮 即可退出 Excel 3) 单击 1Office 控制按钮 2 单击 退出 Excel 按钮, 图 1-2 双击启动 Excel 即可退出 Excel, 如图 1-3 所示 1 2 图 1-3 退出 Excel 4) 按 Alt+F4 组合键也可退出 Excel

11 4 Excel 在财务管理中的应用 ( 第二版 ) 三 Excel 2007 软件的外观 当 Excel 2007 软件正常启动后, 出现 Excel 2007 软件的工作窗口, 如图 1-4 所示 图 1-4 Excel 2007 软件工作窗口 下面我们分功能区域来了解 Excel 2007 软件的窗口 1. 标题栏 标题栏是浓缩的一个窗口, 其主要功能的提示控件如图 1-5 所示 标题栏位于软件 窗口的最上方, 其用最精练的语言说明该窗口的名称及主要功能 Excel 2007 软件的标 题栏不仅有该窗口的主要功能的提示, 还有方便用户操作的 快速访问工具栏 Office 控制按钮 (1)Office 控制按钮 快速访问工具栏窗口名称及功能提示控制按钮 图 1-5 标题栏 Excel 2007 软件中将 Excel 低版本的 文件 菜单变换成了 Office 按钮, 它不但拥 有与 Excel 低版本相同的 打开 保存 和 打印 等基本命令, 而且提供了更多其 他的命令, 如 准备 和 发布, 如图 1-6 所示 (2) 快速访问工具栏 使用快速访问工具栏可以快速打开日常频繁使用的工具 在默认情况下, 快速访问 工具栏位于 Excel 窗口的顶部 用户还可以向快速访问工具栏添加命令, 从而对其进行 自定义, 如图 1-7 所示

12 第一章 Excel 2007 软件基础 5 图 1-6 Office 控制按钮下的基本命令 图 1-7 自定义快速访问工具栏 右击快速访问工具栏, 弹出快捷菜单 ( 快捷菜单是显示与特定项目相关的一列命令的菜单 若显示快捷菜单, 则右击某一项目或按 Shift+F10 组合键 ), 从中单击 自定义快速访问工具栏 选项, 弹出 Excel 选项 对话框, 如图 1-8 所示 自定义的具体步骤如下 : 1) 在 从下列位置选择命令 下拉列表框中选择 所有命令 选项 2) 在 自定义快速访问工具栏 下拉列表框中, 选择 用于所有文档 ( 默认 ) 选项或某个特定文档 3) 选择要添加的命令, 然后单击 添加 按钮 对所有要添加的命令重复以上操作 4) 单击 上移 和 下移 按钮, 按照希望这些命令在快速访问工具栏上出现的顺序排列它们 最后单击 确定 按钮 如果不希望快速访问工具栏在其当前位置显示, 可以将其移到其他位置 如果感觉 Office 控制按钮旁边的默认位置距离工作区太远使用不方便, 可以将其移到靠近工作区的位置 如果该位置处于功能区下方, 则会超出工作区 因此, 如果要最大化工作区,

13 6 Excel 在财务管理中的应用 ( 第二版 ) 需将快速访问工具栏保留在其默认位置 单击 自定义快速访问工具栏 下拉按钮, 在列表中单击 在功能区下方显示 选项即可 当前快速访问工具栏 可以添加至快速访问包含的工具工具栏的工具 关闭 图 1-8 Excel 选项 对话框 (3) 窗口名称及功能提示 窗口名称及功能提示用于显示窗口及窗口功能的信息 (4) 控制按钮 控制按钮可以用来实现窗口的最小化 最大化和 功能, 也可以通过右击标题栏在弹出的窗口控制快 捷菜单中进行相应的操作, 如图 1-9 所示 图 1-9 窗口控制快捷菜单 2. 任务功能区 在 Excel 2007 软件中,Excel 低版本中的菜单 工具栏消失不见了, 取而代之的是 任务功能区, 如图 1-10 所示 任务功能区可以使用户直接 快速地找到完成某一任务所需的命令 命令被组织在任务功能区逻辑组工具栏中, 任务功能区逻辑组工具栏集中在任务功能选项卡下 每个

14 第一章 Excel 2007 软件基础 7 选项卡都与一种类型的活动 ( 如为公式输入内容或布局设计 ) 相关 为了减少混乱, 某些选项卡只在需要时才显示 例如, 仅当选择图片后, 才显示 图片工具 选项卡, 如图 1-11 所示 任务功能区选项卡 任务功能区逻辑组工具栏 图 1-10 任务功能区 图 1-11 图片工具 选项卡 (1) 使用菜单将任务功能区最小化和还原有时候我们操作的数据较多, 希望工作区域增大, 可以采用以下方法将任务功能区最小化和还原 1) 单击 自定义快速访问工具栏, 或者右击任务功能区选项卡 在弹出的快捷菜单中, 单击 功能区最小化 选项 还原功能区 : 单击 自定义快速访问工具栏 下拉按钮, 在列表中单击 功能区最小化 选项 2) 双击任务功能区选项卡, 任务功能区自动最小化 还原功能区 : 双击任务功能区选项卡, 任务功能区最小化状态消失, 恢复任务功能区 (2) 使用键盘快捷方式将任务功能区最小化和还原若要最小化, 按 Ctrl+F1 组合键即可 还原功能区 : 若要还原功能区, 则按 Ctrl+F1 组合键 若在功能区最小化的情况下使用功能区, 需单击要使用的选项卡, 然后单击要使用的选项或命令 例如, 在功能区最小化的情况下, 可以单击 开始 选项卡, 然后在 字体 组中, 单击所需的文本大小 单击所需的文本大小后, 功能区返回到最小化状态

15 8 Excel 在财务管理中的应用 ( 第二版 ) 3. 工作簿及工作表 Excel 工作簿是包含一个或多个工作表的文件, 可以用其中的工作表来组织各种相 关信息 工作表总是存储在工作簿中 Excel 工作簿模板是指创建后作为其他相似工作簿基础的工作簿 工作簿的默认模 板名为 Book.xlt, 工作表的默认模板名为 Sheet.xlt Excel 工作表是指在 Excel 软件中用于存储和处理数据的主要文档, 也称为电子表 格 工作表由排列成行或列的单元格组成 图 1-12 创建新工作簿 (1) 工作簿 要创建新工作簿, 可以打开一个空白工作簿, 也可以基于 现有工作簿 默认工作簿模板 ( 默认工作簿模板是指用户创建的 Book.xlt 模板可更改新建工作簿的默认格式 在启动 Excel 或没有指定模板而新建工作簿时,Excel 用该模板创建一个空白工作簿 ) 或任何其他模板创建新工作簿, 如图 1-12 所示 Excel 2007 软件工作簿增加的新文件类型如表 1-1 所示 表 1-1 工作簿文件类型及扩展名 文件类型扩展名文件类型扩展名 工作簿.xlsx 启用了宏的模板.xltm 启用了宏的工作簿.xlsm 非 XML 二进制工作簿.xlsb 模板.xltx 启用了宏的加载项.xlam (2) 工作表工作表是 Excel 软件的数据操作主体, 它主要包括名称框 编辑栏 列标 行标 单元格 工作表标签 工作表标签操作按扭 滚动条和状态栏等内容, 如图 1-13 所示 图 1-13 工作表 8 工作表各部分的功能如下 : 1 名称框 : 显示当前的引用区域, 即当前活动单元格的地址或单元格区域名 2 编辑栏 : 对在工作表中选定的单元格或单元格区域输入或修改数据

16 第一章 Excel 2007 软件基础 9 3 列标 : 说明工作表中各列的具体位置, 用英文字母 A B C D 表示 Excel 2007 软件支持每个工作表中最多有 列 (A,B,,XFD) 4 行标 : 说明工作表中各行的具体位置, 用阿拉伯数字 表示 Excel 2007 软件支持每个工作表中最多有 行 (1,2,, ) 5 单元格 : 工作表的基本单位, 其中可存放数字 字符串 公式等 单元格的名称由列标和行标组成, 如 A2 B3; 单元格区域的名称由选定单元格区域左上角的单元格名称和右下角的单元格名称组成, 如 A2:B3 C1:F4 目前正在使用的单元格称为活动单元格 6 工作表标签 : 标识一个工作簿中各张工作表的名称 默认情况下一个工作簿有三个工作表, 标签名称为 Sheet1 Sheet2 Sheet3 目前正在使用的工作表称为活动工作表, 可以单击工作表标签进行工作表的切换 7 工作表标签操作按扭 : 可以查看隐藏的工作表标签 8 滚动条 : 可显示工作区域看不到的单元格区域, 分横向滚动条和纵向滚动条 9 状态栏 : 显示当前所处状态相关信息, 其有缩放比例及页面显示工具 第二节 Excel 2007 软件的基本操作 一 工作表的操作 1. 插入工作表 1) 一次插入一个工作表 插入一个新工作表, 需执行下列操作之一 : 1 若在现有工作表的末尾快速插入新工作表, 单击 Excel 窗口下方的 插入工作表, 如图 1-14 所示, 或者按 Shift+F11 组合键 图 1-14 在现有工作表末尾插入新工作表 2 如果在现有工作表之前 ( 当前工作表左侧 ) 插入新工作表, 则选择该工作表, 在 开始 选项卡的 单元格 组中, 单击 插入 右侧的下拉箭头, 然后单击 插入工作表 选项, 如图 1-15 所示 也可以右击现有工作表的标签, 在弹出的快捷菜单中单击 插入 选项, 弹出 插入 对话框, 在 常用 选项卡中单击 工作表 选项, 然后单击 确定 按钮 图 1-15 在现有工作表前 2) 一次插入多个工作表 一次插入多个工作表可以按住插入新工作表 Shift 键, 然后在打开的工作簿中选择与要插入的工作表数目相同的现有工作表标签 ( 如要插入两个新工作表, 则选择两个现有工作表的工作表标签 ), 重复插入单个工作表的步骤

17 10 Excel 在财务管理中的应用 ( 第二版 ) 也可以右击所选的工作表标签, 在弹出的快捷菜单中单击 插入 选项, 弹出 插入 对话框, 在 常用 选项卡中单击 工作表 选项, 然后单击 确定 按钮 如果要求一次插入的工作表数量庞大, 可分批次进行插入 2. 重命名工作表 图 1-16 重命名工作表标签 在 工作表标签 栏中, 右击要重命名的工作表标签, 在弹出的快捷菜单中单击 重命名 选项, 选择当前的名称, 然后输入新名称, 如图 1-16 所示 也可以双击需要重命名的工作表标签, 然后输入新名称 3. 删除工作表在 开始 选项卡的 单元格 组中, 单击 删除 右侧的下拉箭头, 然后单击 删除工作表 选项, 如图 1-17 所示 也可以右击要删除的工作表的工作表标签, 在弹出的快捷菜单中单击 删除 选项, 如图 1-18 所示 图 1-17 开始 选项卡中删除工作表 二 行 列及单元格操作 图 1-18 右击删除工作表 1. 选择一个单元格选择一个单元格可以单击该单元格或按键盘方向键移至该单元格 2. 选择连续单元格区域选择单元格区域可以单击该区域中的第一个单元格, 然后拖动鼠标指针至最后一个单元格, 或者在按住 Shift 键的同时按键盘方向键以扩展选定区域 也可以选择该区域中的第一个单元格, 然后按 F8 键, 使用键盘方向键扩展选定区域 若停止扩展选定区

18 第一章 Excel 2007 软件基础 11 域, 则再次按 F8 键 选择较大的单元格区域可以单击该区域中的第一个单元格, 然后在按住 Shift 键的同时单击该区域中的最后一个单元格, 可以使用滚动功能显示最后一个单元格 3. 选择不相邻的单元格或单元格区域选择不相邻的单元格或单元格区域可以先选择第一个单元格或单元格区域, 然后在按住 Ctrl 键的同时选择其他单元格或区域 也可以先选择第一个单元格或单元格区域, 然后按 Shift+F8 组合键将另一个不相邻的单元格或区域添加到选定区域中 若停止向选定区域中添加单元格或区域, 则再次按 Shift+F8 组合键 4. 选择整行或整列选择整行或整列可以利用单击行标或列标来实现 也可以先选择行或列中的单元格, 方法是选择第一个单元格, 然后按 Ctrl+Shift+ 键盘方向键 ( 对于行, 使用向右键或向左键 ; 对于列, 使用向上键或向下键 ) 如果行或列包含数据, 那么按 Ctrl+Shift+ 键盘方向键可选择到行或列中最后一个已使用单元格之前的部分 按 Ctrl+Shift+ 键盘方向键 1 秒钟可选择整行或整列 5. 选择相邻的行或列选择相邻行或列可以在行标题或列标题间拖动鼠标指针, 或者先选择第一行或第一列, 然后在按住 Shift 键的同时选择最后一行或最后一列 6. 选择不相邻的行或列选择不相邻的行或列可以单击选定区域中第一行的行标或第一列的列标, 然后在按住 Ctrl 键的同时单击要添加到选定区域中的其他行的行标或其他列的列标 7. 选择行 列或工作表中的第一个或最后一个单元格选择行或列中的第一个或最后一个单元格可以选择行或列中的一个单元格, 然后按 Ctrl+ 键盘方向键 ( 对于行, 使用向右键或向左键 ; 对于列, 使用向上键或向下键 ) 选择工作表中第一个或最后一个单元格可以按 Ctrl+Home 组合键选择工作表或 Excel 列表中的第一个单元格 ; 然后按 Ctrl+End 组合键选择工作表或 Excel 列表中最后一个包含数据或格式设置的单元格 选择工作表中最后一个使用的单元格 ( 右下角 ) 之前的单元格区域可以选择第一个单元格, 然后按 Ctrl+Shift+End 组合键可将选定单元格区域扩展到工作表中最后一个使用的单元格 ( 右下角 ) 到工作表起始处的单元格区域可以选择第一个单元格, 然后按 Ctrl+Shift+Home 组合键可将单元格选定区域扩展到工作表的起始处 8. 增加或减少活动选定区域中的单元格增加或减少活动选定区域中的单元格, 可以在按住 Shift 键的同时单击要包含在新

19 12 Excel 在财务管理中的应用 ( 第二版 ) 选定区域中的最后一个单元格 活动单元格 ( 活动单元格就是选定单元格, 可以向其中输入数据 一次只能有一个活动单元格, 活动单元格四周的边框加粗显示 ) 和单击的单元格之间的矩形区域将成为新的选定区域 9. 全选选择工作表中的所有单元格可以单击 全选 按钮, 如图 1-19 所示 若选择整个工作表, 还可以按 Ctrl+A 组合键 如果工作表包含数据, 则按 Ctrl+A 组合键可选择当前区域 按住 Ctrl+A 组合键 1 秒可选择整个工作表 取消选择的单元格区域, 则单击工图 1-19 全选 按钮作表中的任意单元格即可实现 10. 在工作表中插入空白单元格在工作表中插入空白单元格的操作步骤如下 : 1) 选取要插入新空白单元格的单元格或单元格区域 选取的单元格数量应与要插入的单元格数量相同 例如, 要插入六个空白单元格, 则需要选取六个单元格 2) 在 开始 选项卡的 单元格 组中, 单击 插入 右侧的下拉箭头, 然后单击 插入单元格 选项, 如图 1-20 所示 还可以右击所选的单元格, 然后单击快捷菜单中的 插入 选项, 弹出 插入 对话框 3) 在 插入 对话框中, 单击要移动周围单元格的方向 当在工作表中插入单元格时, 受插入影响的所有引用都会 相应地做出调整, 不管它们是相对引用还是绝对引用 这同样适用于删除单元格, 当删 除的单元格由公式直接引用时除外 如果需要引用自动调整, 建议在公式中尽可能使用 区域引用, 而不是指定单个单元格 可以插入包含数据和公式的单元格, 方法是先对其进行复制或剪切, 右击要粘贴的 位置, 然后单击快捷菜单中的 插入复制单元格 或 插入剪切单元格 选项 若要快速重复插入单元格的操作, 则单击要插入单元格的位置, 然后按 Ctrl+Y 组 合键 如果要设置格式, 可以使用 插入选项, 利用格式刷 元格的格式 11. 在工作表中插入行在工作表中插入行的操作步骤如下 : 图 1-20 插入单元格 来选择如何设置插入单 1) 选择要插入的行 若插入单一行, 则选择要在其上方插入新行的行或该行中的一个单元格 例如, 在第五行上方插入一个新行, 则单击第五行中的一个单元格 若插入多行, 则选择要在其上方插入新行的那些行, 所选的行数应与要插入的行数相同 例如, 插入三个新行, 需要选择三行

20 第一章 Excel 2007 软件基础 13 若插入不相邻的行, 则按住 Ctrl 键的同时选择不相邻的行 2) 在 开始 选项卡的 单元格 组中, 单击 插入 右侧的下拉箭头, 然后单击 插入工作表行 选项, 如图 1-21 所示 还可以右击所选行, 然后单击快捷菜单中的 插入 选项 当在工作表中插入行时, 受插入影响的所有引用都会相图 1-21 插入工作表行应地做出调整, 不管它们是相对引用还是绝对引用 这同样适用于删除行, 当删除的单元格由公式直接引用时除外 如果需要引用自动调整, 建议在公式中尽可能使用区域引用, 而不是指定单个单元格 12. 在工作表中插入列在工作表中插入列的操作步骤如下 : 1) 选择要插入的列 若插入单一列, 可以选择要在紧靠其右侧插入新列的列或该列中的一个单元格 例如, 在 B 列左侧插入一列, 则单击 B 列中的一个单元格 若插入多列, 可以选择要紧靠其右侧插入列的那些列, 所选的列数应与要插入的列数相同 例如, 插入三个新列, 需要选择三列 若插入不相邻的列, 则按住 Ctrl 键的同时选择不相邻的列 2) 在 开始 选项卡的 单元格 组中, 单击 插入 右侧的下拉箭头, 然后单击 插入工作表列 选项, 如图 1-22 所示 还可以右击所选的单元格, 然后单击快捷菜单中的 插入 选项 当在工作表中插入列时, 受插入影响的所有引用都会相应图 1-22 插入工作表列地做出调整, 不管它们是相对引用还是绝对引用 这同样适用于删除列, 当删除的单元格由公式直接引用时除外 如果需要引用自动调整, 建议在公式中尽可能使用区域引用, 而不是指定单个单元格 13. 删除单元格 行或列在工作表中删除单元格 行或列, 在 开始 选项卡的 单元格 组中, 执行下列操作之一 : 1 若要删除所选的单元格, 则单击 删除 右侧的下拉箭头, 然后单击 删除单元格 选项 2 若要删除所选的行, 则单击 删除 右侧的下拉箭头, 然后单击 删除工作表行 选项 3 若要删除所选的列, 则单击 删除 右侧的下拉箭头, 然后单击 删除工作表列 选项, 如图 1-23 所示 还可以右击所选的单元格 行或列, 单击快捷菜单中的 删除 选项, 然后在弹出的 删除 对话框中单击所需的选项 图 1-23 删除单元格 行或列

21 14 Excel 在财务管理中的应用 ( 第二版 ) 图 1-24 删除单元格或单元格区域 如果要删除单元格或单元格区域, 则在 删除 对话框中, 点选 右侧单元格左移 下方单元格上移 整行 或 整列 单选按钮, 如图 1-24 所示 按 Delete 键只删除所选单元格的内容, 而不会删除单元格本身 Excel 通过调整移动单元格的引用以反映它们的新位置来使公式保持更新 但是, 如果公式中引用的单元格已被删除, 将显示错误值 #REF! 14. 冻结窗格 冻结窗格的操作步骤如下 : 1) 选择要冻结的行列 在工作表中, 执行下列操作之一 : 1 若锁定行, 则选择其下方要出现拆分的行 2 若锁定列, 则选择其右侧要出现拆分的列 3 若同时锁定行和列, 则单击其下方和右侧要出现拆分的单元格 2) 在 视图 选项卡的 窗口 组中, 单击 冻结窗格 选项, 然后单击所需的选项, 如图 1-25 冻结窗格图 1-25 所示 当冻结窗格时, 冻结窗格 选项更改为 取消冻结窗格, 以便可以取消对行或列的锁定 15. 拆分窗格 拆分窗格的操作步骤如下 : 1) 若拆分窗格, 则将鼠标指针指向垂直滚动条顶端或 拆分 按钮 水平滚动条右端的拆分框, 如图 1-26 所示 2) 当指针变为拆分指针 或 时, 将拆分框向下或 向左拖至所需的位置 图 1-26 拆分窗格 3) 若取消拆分, 则双击拆分窗格的拆分条的任何部分 16. 隐藏行或列 隐藏行或列的操作步骤如下 : 1) 选择要隐藏的行或列 若取消选择的单元格区域, 则单击工作表中的任意单元格 2) 在 开始 选项卡的 单元格 组中, 单击 格式 选项 3) 执行下列操作之一 : 1 将鼠标指针指向 可见性 下方的 隐藏和取消隐藏, 然后单击 隐藏行 或 隐藏列 选项, 如图 1-27 所示 2 在 单元格大小 下方, 单击 行高 或 列宽 选项, 然后在弹出的 行高

22 第一章 Excel 2007 软件基础 15 或 列宽 对话框中输入 0 图 1-27 隐藏行或列 3 也可以右击一行或一列 ( 或者选择的多行或多列 ), 然后单击 隐藏 选项 17. 取消隐藏行或列取消隐藏行或列的操作步骤如下 : 1) 在工作表中, 执行下列操作之一 : 1 若显示隐藏的行, 则选择要显示的行的上一行和下一行 2 若显示隐藏的列, 则选择要显示的列两边的相邻列 3 若显示工作表中一个隐藏的行或列, 在 名称框 中输入隐藏行或列的单元格 也可以使用 定位 对话框来选择它 在 开始 选项卡的 编辑 组中, 单击 查找和选择 选项, 然后单击 转动 选项, 在 引用位置 文本框中, 输入隐藏行 列的单元格, 再单击 确定 按钮 2) 在 开始 选项卡的 单元格 组中, 单击 格式 选项, 将鼠标指针指向 隐藏和取消隐藏, 再单击 取消隐藏行 或 取消隐藏列 选项如图 1-28 所示 图 1-28 取消隐藏行或列 三 设置行高 列宽在工作表中, 可以将列宽指定为 0~255, 此值表示可在用标准字体 ( 默认文本字体 ) 进行格式设置的单元格中显示的字符数 默认列宽为 8.43 个字符 如果列宽设置为 0, 则隐藏该列 可以将行高指定为 0~409, 此值以点数 (1 点约等于 1/72 英寸 ) 表示高度测量值 默认行高为 点 如果行高设置为 0, 则隐藏该行

23 16 Excel 在财务管理中的应用 ( 第二版 ) 1. 将列设置为特定宽度将列设置为特定宽度的操作步骤如下 : 1) 选择要更改的列 2) 在 开始 选项卡的 单元格 组中, 单击 格式 选项 3) 在 单元格大小 下单击 列宽 选项 4) 在 列宽 对话框中, 输入所需的值, 如图 1-29 所示 图 1-29 设置列宽 2. 更改列宽以适合内容更改列宽以适合内容的操作步骤如下 : 1) 选择要更改的列 2) 在 开始 选项卡的 单元格 组中, 单击 格式 选项 3) 在 单元格大小 下单击 自动调整列宽 选项, 如图 1-30 所示 若要快速自动调整工作表中的所有列, 则单击 全选 按钮, 然后双击两个列标题 之间的任意边界, 如图 1-31 所示 图 1-30 自动调整列宽 3. 将列宽与另一列匹配将列宽与另一列匹配的操作步骤如下 : 1) 在该列中选择一个单元格 全选 按钮 图 1-31 快速自动调整列宽 2) 在 开始 选项卡的 剪贴板 组中, 单击 复制 按钮, 然后选择目标列 3) 在 开始 选项卡的 剪贴板 组中, 单击 粘贴 下方的下拉箭头, 然后单击 选择性粘贴 选项

24 第一章 Excel 2007 软件基础 17 4) 在弹出的 选择性粘贴 对话框中的 粘贴 下, 点选 列宽 单选按钮, 如 图 1-32 所示 图 1-32 列宽与另一列匹配 4. 更改工作表或工作簿中所有列的默认宽度默认列宽的值只是适合单元格的标准字体的平均字符数 可以为工作表或工作簿的默认列宽指定其他数字 更改工作表或工作簿中所有列的默认宽度的操作步骤如下 : 1) 执行下列操作之一 : 1 若要更改工作表的默认列宽, 则单击其工作表标签 2 若要更改整个工作簿的默认列宽, 则右击工作表标签, 如图 1-33 所示, 然后单击快捷菜单中的 选定全部工作表 选项 2) 在 开始 选项卡的 单元格 组中, 单击 格式 选项 3) 在 单元格大小 下单击 默认宽度 选项 4) 在 默认列宽 对话框中, 输入新的度量值, 如图 1-34 所示 图 1-33 选定全部工作表 图 1-34 更改默认列宽 如果要为所有新的工作簿和工作表定义默认列宽, 可以创建一个工作簿模板或 工作表模板, 然后基于这些模板创建新的工作簿或工作表 有关详细信息, 请参阅创建模板

25 18 Excel 在财务管理中的应用 ( 第二版 ) 5. 使用鼠标指针更改列宽若要更改某一列的宽度, 可用鼠标指针拖动列标题的右侧边界, 直到达到所需列宽, 如图 1-35 所示 若要更改多列的宽度, 则选择要更改的列, 然后拖动所选列标题的右侧边界 若要更改列宽以适合内容, 则选择要更改的列, 然后双击所选列标题的右侧边界 若要更改工作表中所有列的宽度, 则单击 全选 按钮, 然后拖动任意列标题的边界, 如图 1-36 所示 全选 按钮 图 1-35 拖动鼠标指针更改列宽 图 1-36 更改所有列的宽度 6. 将行设置为指定高度 将行设置为指定高度的操作步骤如下 : 1) 选择要更改的行 2) 在 开始 选项卡的 单元格 组中, 单击 格式 选项 3) 在 单元格大小 下单击 行高 选项 4) 在 行高 对话框中, 输入所需的值, 如图 1-37 所示 图 1-38 自动调整行高 选项 图 1-37 设置行高 7. 更改行高以适合内容 更改行高以适合内容的操作步骤如下 : 1) 选择要更改的行 2) 在 开始 选项卡的 单元格 组中, 单击 格式 3) 在 单元格大小 下单击 自动调整行高 选项, 如 图 1-38 所示 若要快速自动调整工作表中的所有行, 则单击 全选 按钮, 然后双击行标题之一 下方的边界, 如图 1-39 所示

26 第一章 Excel 2007 软件基础 使用鼠标指针更改行高使用鼠标指针更改行高可以执行下列操作之一 : 1) 若要更改某一行的行高, 则拖动行标题下方的边界, 直到达到所需行高, 如图 1-40 所示 2) 若要更改多行的行高, 则选择要更改的行, 然后拖动所选行标题之一下方的边界 全选 按钮图 1-39 快速自动调整行高 3) 若要更改工作表中所有行的行高, 则单击 全选 按钮, 然后拖动任意行标题下方的边界, 如图 1-41 所示 全选 按钮 图 1-40 更改某一行的行高 图 1-41 更改所有行的行高 4) 若要更改行高以适合内容, 则双击行标题下方的边界 四 合并 拆分单元格 1. 合并相邻单元格 1) 选择两个或更多要合并的相邻单元格 确保在合并单元格中显示的数据位于所选区域的左上角单元格中 只有左上角单元格中的数据将保留在合并的单元格中, 所选区域中所有其他单元格中的数据都将被删除 2) 在 开始 选项卡的 对齐方式 组中, 单击 合并后居中 右侧的下拉箭头, 如图 1-42 所示 这些单元格将在一个行或列中合并, 并且单元格内容将在合并单元格中居中显示 若合并单元格不居中显示内容, 则单击 跨越合并 或 合并单元格 选项 图 1-42 合并相邻单元格如果 合并后居中 按钮不可用, 则所选单元格可能在编辑模式下 取消编辑模式, 按 Enter 键 3) 若更改合并单元格中的文本对齐方式, 可以选择该单元格, 在 开始 选项卡的 对齐方式 组中, 单击任一对齐方式按钮 2. 拆分合并的单元格拆分合并的单元格的操作步骤如下 : 1) 选择拆分合并的单元格 当选择合并的单元格时, 合并后居中 按钮在 开始 选项卡的 对齐方式 组中也显示为选中状态

27 20 Excel 在财务管理中的应用 ( 第二版 ) 2) 若拆分合并的单元格, 则单击 合并后居中 右侧的下拉箭头, 再单击 取消单元格合并 选项, 合并单元格的内容将出现在拆分单元格区域左上角的单元格中, 如图 1-43 所示 五 数据 文本的输入 1. 输入数字或文本 (1) 改变 Enter 键的移动方向图 1-43 拆分合并的单元格在工作表中, 单击一个单元格, 输入所需的数字或文本, 然后按 Enter 键或 Tab 键 如果在同一个单元格中另起一行输入数据, 则按 Alt+Enter 组合键输入一个换行符, 强制换行 默认情况下, 按 Enter 键会将所选内容向下移动一个单元格,Tab 键会将所选内容向右移动一个单元格 在 Excel 中我们不能更改 Tab 键移动的方向, 但是可以为 Enter 键指定不同的方向 单击 Office 控制按钮, 再单击 Excel 选项 按钮, 弹出 Excel 选项 对话框, 在 高级 类别中的 编辑选项 下, 勾选 按 Enter 键后移动所选内容 复选框, 然后在 方向 下拉列表框中选择所需的方向, 如图 1-44 所示 (2) 单元格内自动换行当单元格包含的数据的数字格式比其列宽 更宽时, 单元格可能显示 ##### 要查看所有文本, 必须增加列宽或者换行显示 单击要自动换行的单元格, 在 开始 选项卡的 对齐方式 组中, 单击 自动换 图 1-45 自动换行 行 按钮, 如图 1-45 所示 如果文本是一个长单词, 则这些字符不会换行 ; 此时可以 加大列宽或缩小字号来显示所有文本 如果在自动换行后并未 显示所有文本, 可能需要调整行高 在 开始 选项卡的 单 元格 组中, 单击 格式 选项, 然后单击 单元格大小 下 的 自动调整行高 选项 图 1-44 改变 Enter 键的移动方向 在 Excel 中, 单元格中数字的显示与该单元格中存储的数 字是分离的 当输入的数字四舍五入时, 大多数情况下, 只显示用户设置的四舍五入要求 计算时使用单元格中实际存储的数字 ( 没有四舍五入的数字 ), 而非显示的四舍五入后的数字 2. 输入具有自动设置小数点的数字在工作表中, 单击一个单元格, 然后输入所需的数字 单击 Office 控制按钮, 然后单击 Excel 选项 按钮, 弹出 Excel 选项 对话框, 在 高级 类别中的 编辑选

28 第一章 Excel 2007 软件基础 21 项 下, 勾选 自动插入小数点 复选框, 如图 1-46 所示 图 1-46 自动插入小数点 在 位数 列表框中, 输入一个正数表示小数点右边的位数, 或输入一个负数表示小数点左边的位数 例如, 如果在 位数 列表框中输入 2, 然后在单元格中输入 1234, 则值为 如果在 位数 列表框中输入 -2, 然后在单元格中输入 1234, 则值为 在勾选 自动插入小数点 复选框之前输入的数据不受影响, 也可在输入数字时直接输入小数点 3. 输入日期或时间在工作表中, 单击一个单元格 对于日期, 使用斜线或连字符分隔日期的各部分 例如, 输入 9/5/2013 或 5-Sep-2013 当在单元格中输入日期或时间时, 它的显示格式既可以是默认的日期或时间格式, 也可以是在输入日期或时间之前应用于该单元格的格式 默认系统日期或时间设置发生了更改, 则工作簿中未使用 设置单元格格式 命令设置格式的任何现有日期也会随之更改 若使用默认的日期或时间格式, 则单击包含日期或时间的单元格, 然后按 Ctrl+ Shift+# 或 组合键 ; 若要输入当前日期, 按 Ctrl+; 组合键 在重新打开工作表时仍保持当前的日期或时间, 可以使用 TODAY 和 NOW 函数 4. 编辑单元格内容在 Excel 中可以直接在单元格中编辑单元格内容 : 双击包含要编辑的数据的单元格 ; 也可以在编辑栏中编辑单元格内容, 如图 1-47 所示 图 1-47 编辑单元格内容单击包含要编辑的数据的单元格, 然后单击编辑栏中的任何位置 在编辑模式下, 许多功能区命令将处于非活动状态暂时停用 若删除字符, 则单击要删除字符的位置, 然后按 Backspace 键, 或者选择字符, 然后按 Delete 键 若插入字符, 则单击要插入字符的位置, 然后输入新字符 若替换特定字符, 则先选择它们, 然后输入新字符 若在单元格中特定的位置开始新的文本行, 则在希望断行的位置单击, 并按 Alt+ Enter 组合键 ( 强制换行 ) 确认更改, 则按 Enter 键 在按下 Enter 键之前, 可以通过按 Esc 键取消所做的任何

29 22 Excel 在财务管理中的应用 ( 第二版 ) 更改 按 Enter 键后, 可以通过单击快速访问工具栏中的 撤消 按钮取消所做的任何更改 5. 自动填充数据 (1) 使用填充柄填充数据填充柄是位于选定区域右下角的小黑方块 当用鼠标指针指向句柄时, 指针更改为黑十字, 如图 1-48 所示 填充柄功能强大, 我们可以使用 填充 命令将数据填充到工作表单元格中, 还可以让 Excel 图 1-48 黑十字指针根据建立的模式自动输入数字 数字和文本的组合 日期或时间段序列 然而, 若要快速填充几种类型的数据序列, 可以选择单元格并拖动填充柄, 如图 1-49 所示 拖动填充柄后, 会出现 自动填充选项 按钮, 以便选择如何填充所选内容, 如图 1-50 所示 图 1-49 快速填充数据序列的方法 图 1-50 自动填充选项 例如, 可以点选 仅填充格式 单选按钮只填充单元格格式, 也可以点选 不带格 式填充 单选按钮只填充单元格的内容 如果不希望每次拖动填充柄时都显示 自动填充选项 按钮, 可以将其关闭 单击 Office 控制按钮, 然后单击 Excel 选项, 弹出 Excel 选项 对话框 在 高级 类别中的 剪切 复制和粘贴 下, 取消勾选 显示粘贴选项按钮 复选框, 如图 1-51 所示 图 1-51 取消勾选 显示粘贴选项按钮 复选框

30 第一章 Excel 2007 软件基础 23 (2) 为相邻的单元格填充数据我们可以使用 填充 命令用相邻单元格或区域的内容填充活动单元格或选定区域, 也可以通过拖动填充柄快速填充相邻的单元格 1) 不使用填充柄填充 选择包含要填充的数据的单元格图 1-52 选择个空白单元格上方 下方 左侧或右侧的一个空白单元格, 如图 1-52 所示 在 开始 选项卡的 编辑 组中, 单击 填充 选项, 然后单击 向下 向右 向上 或 向左 选项, 完成填充, 如图 1-53 所示 若使当前单元格上方或左侧的单元格中的内容快速填充当前单元格, 可以按 Ctrl+D 或 Ctrl+R 组合键 2) 使用填充柄填充 选择包含要填充到相邻单元格中的数据的单元格, 将填充柄拖过要填充的单元图 1-53 填充 命令格, 单击 自动填充 选项选择填充所选内容的方式, 选择所需的选项, 如图 1-54 所示 原数据单元格 需要填充的数据单元格 默认填充方式 备选填充方式 图 1-54 使用填充柄填充 (3) 将公式填充到相邻的单元格中选择包含要填充到相邻单元格中的公式的单元格, 将填充柄拖过要填充的单元格, 如图 1-55 所示 还可以通过使用 开始 选项卡的 编辑 组中 填充 命令, 用相邻单元格的公式填充活动单元格, 或通过按 Ctrl+D 或 Ctrl+R 组合键填充包含公式的单元格下方或右侧的单元格 对于应用某公式的所有相邻单元格, 可以自动向下填充该公式, 方法是双击包含公式的第一个单元格的填充柄 例如, 在单元格 A1:A10 和 B1:B10 中含有数字, 在单元格 C1 中输入公式 =A1+B1 要将该公式复制到单元格 C2:C10 中, 则需选择单元格 C1 并双击填充柄

31 24 Excel 在财务管理中的应用 ( 第二版 ) 公式 C1=A1+B1 图 1-55 填充柄拖过要填充的单元格 注意 : 使用此方法时要求相邻两列行数相同 若行数不同 Excel 则采用就低原 则, 适应相邻两列最少行数, 如上例变为 A1:A10 和 B1:B5 相邻, 在单元格 C1 中输 入公式 =A1+B1, 选择单元格 C1 并双击填充柄,C1:C5 将自动填充公式,C6:C10 不做操作 图 1-56 填充数字 日期序列 (4) 填充数字 日期序列或其他内置序列项目 使用填充柄可以快速用数字或日期序列或者日 工作 日 月或年的内置序列填充某区域中的单元格, 如图 1-56 所示 1) 选择需要填充的区域中的第一个单元格 2) 输入序列的起始值 3) 在下一个单元格中输入值以建立模式 例如, 如果要使用序列 , 则在前两个单元格中输入 1 和 2 如果要使用序列 , 则输入 2 和 4 如果要使用序列 , 则可以保留第二个单元格为空 4) 选择包含初始值的单元格 5) 将填充柄拖过要填充的区域 按升序填充, 从上到下或从左到右拖动 ; 按降序填充, 从下到上或从右到左拖动 Excel 还内置了相当多的序列变化 ( 用逗号分隔的各项放置到相邻单元格中 ), 如 表 1-2 所示 表 1-2 Excel 中的序列 初始选择 扩展序列 1,2,3 4,5,6, 9:30 10:30,11:30,12:30, 周一 周二, 周三, 周四,

32 第一章 Excel 2007 软件基础 25 续表 初始选择星期一一月一月, 三月 扩展序列星期二, 星期三, 星期四, 二月, 三月, 四月, 五月, 七月, 九月, 1999 年 1 月,1999 年 4 月 1999 年 7 月,1999 年 10 月,2000 年 1 月, 1 月 1 日,4 月 1 日 7 月 1 日,10 月 1 日, 1999, ,2002,2003, 1 月 1 日,3 月 1 日 5 月 1 日,7 月 1 日,9 月 1 日, 文本 1, 文本 A 第 1 章 产品 1 文本 1, 文本 A, 文本 3, 文本 A, 第 2 章, 第 3 章, 产品 2, 产品 3, 也可以指定序列类型, 方法是先按住鼠标右键, 再拖动填充柄, 在到达填充区域之上时, 单击快捷菜单中的适当命令 例如, 如果初始值为 Jan-2014, 则单击 以月填充 将生成序列 Feb-14 Mar-14 等 ; 单击 以年填充 将生成序列 Jan-15 Jan-16 等, 如图 1-57 所示 如果选定区域包含数字, 则可以控制要创建的序列的类型 在 开始 选项卡的 编辑 组中, 单击 填充 选项, 然后在列表中单击 系列 选项, 弹出 序列 对话框, 如图 1-58 所示 在 类型 列表框中, 执行下列选项之一 : 1 点选 等差序列 单选按钮, 获得对每个单元格值依次添加 步长值 文本框中的值而计算出的序列 2 点选 等比序列 单选按钮, 获得将 步长值 文本图 1-57 指定序列类型框中的值依次与每个单元格值相乘而计算出的序列 3 点选 日期 单选按钮, 获得按照 步长值 文本框中的值以递增方式填充数据值的序列, 该序列采用在 日期单位 下指定的单位 4 点选 自动填充 单选按钮, 获得在拖动填充柄产生相同结果的序列 可以复制 自动填充, 方法是按住 Ctrl 键的同时拖动选择两个或更多单元格的填充柄 选择的值就复制到了相邻的单元格, 并且 Excel 不扩展序列 (5) 自定义填充序列填充为了更轻松快速地输入特定的数据序列 ( 如名称或销售区域的列表 ), 可以创建自定义填充序列 自定义填充序列可以基于工作表中已有项目的列表, 也可以从头开始输入列表 不能编辑或删除内置填充序列 ( 如月 日和星期的填充序列 ), 但是可以编辑或删除自定义填充序列

33 26 Excel 在财务管理中的应用 ( 第二版 ) 图 1-58 序列 对话框 自定义列表只可以包含文字或混合数字的文本 对于只包含数字的自定义列表, 如从 0~100, 必须将这些数字设为文本格式 1) 单击 Office 控制按钮, 然后单击 Excel 选项 按钮 2) 在弹出的 Excel 选项 对话框中单击 常用 选项, 然后在 使用 Excel 时采用的首选项 下单击 编辑自定义列表 按钮 确认所选项目列表的单元格引用显示在 从单元格中导入序列 文本框中, 然后单击 导入 按钮 所选的列表中的项目将添加到 自定义序列 列表框中, 如图 1-59 所示 图 1-59 编辑自定义列表 3) 单击 确定 按钮两次 在工作表中, 单击一个单元格, 然后在自定义填充序列中输入要用作列表初始值的项目 将填充柄拖过要填充的单元格

34 第一章 Excel 2007 软件基础 27 也可按下述步骤操作, 如图 1-60 所示 选择自定义新序列 输入自定义序列的内容 添加自定义序列到序列列表 图 1-60 自定义序列 单击 自定义序列 列表框中的 新序列, 然后在 输入序列 列表框中输入各个项, 从第一个项开始 输入每个项后, 按 Enter 键 当列表完成后, 单击 添加 按钮, 然后单击 确定 按钮两次或者用鼠标点击确定 如要删除或修改自定义序列, 可以在 自定义序列 列表框中先选择要修改或删除的列表, 然后执行下列操作之一 : 1 修改填充序列, 则在 输入序列 列表框中进行所需的更改, 然后单击 添加 按钮 2 删除填充序列, 则单击 删除 按钮 (6) 隐藏或显示填充柄 1) 单击 Office 控制按钮, 然后单击 Excel 选项 按钮 2) 在弹出的 Excel 选项 对话框中单击 高级 选项, 然后在 编辑选项 下, 取消勾选或勾选 启用填充柄和单元格拖放功能 复选框以隐藏或显示填充柄 3) 为了避免在拖动填充柄时替换现有数据, 勾选 覆盖单元格内容前发出警告 复选框, 如图 1-61 所示 图 1-61 隐藏或显示填充柄六 设置数据格式 1. 使用任务功能区常用的工具 (1)Excel 中文字对齐的操作 1) 左对齐 : 选择要设置格式的文字, 在 开始 选项卡的 对齐方式 组中, 单击 文本左对齐 2) 右对齐 : 选择要设置格式的文字, 在 开始 选项卡的 对齐方式 组中, 单击 文本右对齐, 若将所选文字右对齐, 也可按 Ctrl+R 组合键

35 28 Excel 在财务管理中的应用 ( 第二版 ) 3) 水平居中 : 选择要设置格式的文字, 在 开始 选项卡的 对齐方式 组中, 单击 居中, 若将所选文字居中, 也可按 Ctrl+E 组合键 4) 顶端对齐 : 选择要设置格式的文字, 在 开始 选项卡的 对齐方式 组中, 单击 顶端对齐 5) 底端对齐 : 选择要设置格式的文字, 在 开始 选项卡的 对齐方式 组中, 单击 底端对齐 6) 垂直居中 : 选择要设置格式的文字, 在 开始 选项卡的 对齐方式 组中, 单击 垂直居中 (2) 更改文字颜色选择要用不同文字颜色来设置格式的单元格 单元格区域 文本或字符 ( 取消选择的单元格区域, 则单击工作表中的任意单元格 ), 如图 1-62 所示 在 开始 选项卡的 字体 组中, 执行下列操作之一 : 1 若更改文本颜色, 则单击 字体颜色 右侧的下拉箭头, 然后在 主题颜色 或 标准色 下, 单击要使用的颜色, 如图 1-63 所示 图 1-62 字体颜色 按钮图 1-63 字体颜色设置 应用最近选择的文本颜色, 则直接单击 字体颜 色 按钮 2 若应用提供的主题颜色和标准色以外的颜色, 则单击 其他颜色, 然后在 颜色 对话框中的 标 准 选项卡或 自定义 选项卡中指定要使用的颜色, 如图 1-64 所示 (3) 更改文字的背景色选择要用不同背景色来设置格式的单元格 单元格区域 文本或字符 ( 取消选择的单元格区域, 则单击工作表中的任意单元格 ), 如图 1-65 所示 图 1-64 其他颜色在 开始 选项卡的 字体 组中, 执行下列操作之一 :

36 第一章 Excel 2007 软件基础 29 1 若更改背景色, 则单击 填充颜色 右侧的下 拉箭头, 然后在 主题颜色 或 标准色 下单击要使用 的背景色 2 若应用最近选择的背景色, 则直接单击 填充颜色 按钮 图 1-65 填充颜色 按钮 3 若应用提供的主题颜色和标准色以外的颜色, 则单击 其他颜色 ( 操作图形同 更改文字颜色 ), 然后在 颜色 对话框中的 标准 选项卡或 自定义 选项卡中 指定要使用的颜色 (4) 设置文字格式为加粗 倾斜或带下划线 选择要设置格式的单元格 单元格区域 文本或字符 ( 取消选择的单元格区域, 则 单击工作表中的任意单元格 ), 如图 1-66 所示 在 开始 选项卡的 字体 组中, 执行下列操 作之一 : 1 若使文本加粗, 则单击 加粗, 也可以按 图 1-66 加粗 倾斜 Ctrl+B 或 Ctrl+2 组合键 2 若使文本倾斜, 则单击 倾斜, 也可以按 下划线 按钮 Ctrl+I 或 Ctrl+3 组合键 3 若使文本带下划线, 则单击 下划线, 也可以按 Ctrl+U 或 Ctrl+4 组合键 若要应用其他类型的下划线, 则在 开始 选项卡的 字体 组中, 单击 字体 右侧的 设置单元格格式 : 字体 对话框启动器 ( 或者按 Ctrl+Shift+F 或 Ctrl+1 组合键 ), 弹出 设置单元格格式 对话框, 然后在 下划线 下拉列表框中选择所需 的样式, 如图 1-67 所示 图 1-67 选择其他类型的下划线 (5) 设置文字为带删除线格式选择要设置格式的单元格 单元格区域 文本或字符 ( 取消选择的单元格区域, 则单击工作表中的任意单元格 ) 在 开始 选项卡的 字体 组中, 单击 字体 右侧的 设置单元格格式 : 字体 对话框启动器, 也可以按键盘快捷方式 Ctrl+Shift+F 或 Ctrl+1 快速显示 设置单元格格式 对话框的 字体 选项卡 在 特殊效果 列表框中勾选 删除线 复选框, 如图 1-68 所示 若在不使用对话框的情况下快速应用或去掉删除线格式设置, 则按 Ctrl+5 组合键 (6) 设置文字为上标或下标格式选择要设置格式的单元格 单元格区域 文本或字符 ( 要取消选择的单元格区域, 则单击工作表中的任意单元格 )

37 30 Excel 在财务管理中的应用 ( 第二版 ) 图 1-68 勾选 删除线 复选框在 开始 选项卡的 字体 组中, 单击 字体 右侧的 设置单元格格式 : 字体 对话框启动器, 也可以按键盘快捷方式 Ctrl+Shift+F 或 Ctrl+1 快速显示 设置单元格格式 对话框的 字体 选项卡 在 特殊效果 列表框中勾选 上标 或 下标 复选框, 如图 1-69 所示 图 1-69 勾选 上标 或 下标 复选框 2. 使用 设置单元格格式 窗口设置格式 (1) 可用的数字格式 通过应用不同的数据格式, 可以更改数据的外观而不会更改数字 数据格式并不影 响 Excel 计算实际单元格值 开始 选项卡的 数字 组中可用数字格式如图 1-70 所示 若查看所有可用的数字格式, 则单击 数字 右侧的 设置单元格格式 : 数字 对 话框启动器, 如图 1-71 所示, 弹出 设置单元格格式 对话框, 如图 1-72 所示 图 1-70 数字 组中的可用数字格式 图 1-71 对话框启动器

38 第一章 Excel 2007 软件基础 31 图 1-72 设置单元格格式 : 数字 单元格分类格式的含义如表 1-3 所示 表 1-3 各分类格式的含义 格式说明 常规数值货币会计专用日期时间百分比分数科学记数文本特殊自定义 这是输入数字时 Excel 应用的默认数字格式 大多数情况下, 常规 格式的数字以输入的方式显示 然而, 如果单元格的宽度不够显示整个数字, 常规 格式会用小数点对数字进行四舍五入 常规 格式还对较大的数字 (12 位或更多位 ) 使用科学记数 ( 指数 ) 表示法 这种格式用于数字的一般表示, 可以指定要使用的小数位数 是否使用千位分隔符及如何显示负数 此格式用于一般货币值并显示带有数字的默认货币符号, 可以指定要使用的小数位数 是否使用千位分隔符及如何显示负数 这种格式也用于货币值, 但是它会在一列中对齐货币符号和数字的小数点 这种格式会根据指定的类型和区域设置 ( 国家 / 地区 ), 将日期和时间系列数值显示为日期值 以星号 (*) 开头的日期格式响应在 控制面板 中指定的区域日期和时间设置的更改, 不带星号的格式不受 控制面板 设置的影响 这种格式会根据指定的类型和区域设置 ( 国家 / 地区 ), 将日期和时间系列数显示为时间值 以星号开头的时间格式响应在 控制面板 中指定的区域日期和时间设置的更改, 不带星号的格式不受 控制面板 设置的影响 这种格式以百分数形式显示单元格的值, 可以指定要使用的小数位数 这种格式会根据指定的分数类型以分数形式显示数字 这种格式以指数表示法显示数字, 用 E+n 替代数字的一部分, 其中用 10 的 n 次幂乘以 E( 代表指数 ) 前面的数字 例如, 两位小数的 科学记数 格式将 显示为 1.23E+10, 即用 1.23 乘 10 的 10 次幂 其可以指定要使用的小数位数 这种格式将单元格的内容视为文本, 并在输入时准确显示内容, 即使是输入数字 这种格式将数字显示为邮政编码 电话号码或社会保险号码 这种格式允许修改现有数字格式代码的副本 这样会创建一个自定义数字格式并将其添加到数字格式代码的列表中 自定义 格式可以添加 200~250 个自定义数字格式

39 32 Excel 在财务管理中的应用 ( 第二版 ) (2) 百分比形式显示数字 1) 选择要设置格式的单元格 ( 取消选择的单元格区域, 则单击工作表中的任意单元格 ) 2) 在 开始 选项卡中, 单击 数字 右侧的 设置单元格格式 : 数字 对话框启动器 3) 在 数字 选项卡中单击 分类 列表框中的 百分比, 如图 1-73 所示 在 小数位数 下拉列表框中, 输入要显示的小数位数 工作表中选择的活动单元格中的数字出现在 示例 列表框中, 这样可以预览选择的数字格式选项 若要快速地以百分比形式显示数字, 则单击 设置单元格格式 对话框中的 百分比 或单击 开始 选项卡 数字 中的 百分比样式, 如图 1-74 所示 图 1-73 设置 百分比 图 1-74 百分比样式 在应用 百分比 格式之前单元格中的数字将乘以 100 来转换为百分比形式 在应 用 百分比 格式之后在单元格中输入的数字则有所不同 默认情况下, 大于和等于 1 的数字转换为百分比形式 ; 小于 1 的数字乘以 100 来转换为百分比形式 例如, 输入 5 的结果为 5%, 输入.36 的结果同样是 36% 若重新设置所选单元格的数字格式, 则在 数字 选项卡中单击 分类 列表框中 的 常规 选项 用 常规 格式设置的单元格没有特定的数字格式 (3) 以分数形式显示数字 1) 使用 分数 格式将以实际分数 ( 而不是小数 ) 的形式显示或输入数字 2) 在工作表中输入数字 , 选择该单元格 ( 取消选择的单元格区域, 单击工作表中的任意单元格 ) 3) 在 开始 选项卡中, 单击 数字 右侧的 设置单元格格式 : 数字 对话框启动器 4) 在 分类 列表框中, 单击 分数 选项, 在 类型 列表框中, 单击要使用的分数格式类型, 如图 1-75 所示

40 第一章 Excel 2007 软件基础 33 图 1-75 选择分数格式类型 分类 列表框中的 分数 各选项含义见如表 1-4 所示 表 1-4 分数 中的各选项含义 分数格式一位数分数两位数分数三位数分数 单元格内 的显示 123 1/2, 四舍五入为最接近的一位数分数值 /57, 四舍五入为最接近的两位数分数值 /125, 四舍五入为最接近的三位数分数值 以 2 为分母的分数 123 1/2 以 4 为分母的分数 123 1/2 以 8 为分母的分数 123 1/2 以 16 为分母的分数 123 7/16 以 10 为分母的分数 123 1/2 以 100 为分母的分数 /50 工作表中选定区域内活动单元格中的数字显示在 示例 列表框中, 以便可以预览选定的数字格式选项 在将 分数 格式应用于一个单元格后, 在该单元格中输入的小数和实际分数将显示为分数 例如, 当使用 分母为一位数 分数类型对单元格进行了格式设置时, 输入.5 或 1/2 的结果是 1/2 如果没有对单元格应用 分数 格式, 并且输入分数 ( 如 1/2), 它将采用日期格式 若将其显示为分数, 则应用 分数 格式, 然后重新输入分数 如果不需要对分数执行运算, 则可以在单元格中输入分数之前, 通过单击 分类

41 34 Excel 在财务管理中的应用 ( 第二版 ) 列表框中的 文本 选项将单元格设置为文本格式 这样, 输入的分数就不会减小或转换为小数 然而, 不能对以文本格式显示的分数执行算术运算 如果要重新设置数字格式, 则单击 常规 选项, 用 常规 格式设置的单元格没有特定的数字格式 (4) 显示数字为货币 1) 选择包含要用货币符号显示的数字的单元格 ( 取消选择的单元格区域, 单击工作表中的任意单元格 ) 2) 在 开始 选项卡中, 单击 数字 右侧的 设置单元格格式 : 数字 对话框启动器 3) 在 分类 列表框中, 单击 货币 或 会计专用, 在 货币符号 下拉列表框中, 单击所需的货币符号, 如图 1-76 所示 图 1-76 货币符号的选择 如果要显示没有货币符号的货币值, 可以单击 无 在 小数位数 下拉列表框中, 输入要显示的小数位数 在 负数 列表框中, 选择负数的显示样式, 负数 列表框对 会计专用 数字格式不可用 工作表中选择的活动单元格中的数字出现在 示例 列表框中, 这样可以预览选择的数字格式选项 要快速显示带有默认货币符号的数字, 则选择单元格或单元格区域, 然后单击 开始 选项卡的 数字 组中的 会计数字格式 选项 若使用另一种货币, 则单击 会计数字格式 右侧的下拉箭头, 然后单击所需的货币, 如图 1-77 所示 图 1-77 选择其他货币符号如果要更改 Excel 和其他 Office 程序的默认货币符号, 可以在控制面板中更改默认的区域货币设置 注意, 尽管 会计数字格式 按钮图像没有改变, 但在单击按钮时会应用所选的货币符号

42 第一章 Excel 2007 软件基础 35 如果重新设置数字格式, 则单击 常规 选项, 用 常规 格式设置的单元格没有特定的数字格式 (5) 将数字显示为日期或时间当在单元格中输入日期或时间时, 它会以默认的日期和时间格式显示 默认的日期和时间基于在 控制面板 中指定的区域日期和时间设置, 并会随着这些设置的更改而更改 可以用几种其他日期和时间格式 ( 其中大多数不受 控制面板 设置的影响 ) 来显示数字 1) 选择要设置格式的单元格 ( 取消选择的单元格区域, 单击工作表中的任意单元格 ) 2) 在 开始 选项卡中, 单击 数字 右侧的 设置单元格格式 : 数字 对话框启动器 3) 在 分类 列表框中, 单击 日期 或 时间 选项 在 类型 列表框中, 单击要使用的日期或时间格式 以星号开始的日期和时间格式响应在 控制面板 中指定的区域日期和时间设置的更改 不带星号的格式不受 控制面板 设置的影响 若以其他语言的格式显示日期和时间, 则在 区域设置 ( 国家 / 地区 ) 下拉列表框中单击所需的语言设置, 如图 1-78 所示 图 1-78 日期 格式的设置 工作表中选定区域内活动单元格中的数字显示在 示例 列表框中, 以便可以预览选择的数字格式选项 如果要快速设置日期或时间的格式, 则在 开始 选项卡的 数字 组中, 在弹出的 设置单元格格式 对话框中的 数字 选项卡中单击所需的日期或时间格式, 如图 1-78 所示 如果在 类型 列表框中找不到所需的格式, 可创建自定义数字格式, 方法是首先单击 分类 列表框中的 自定义, 然后对日期和时间使用格式代码

43 36 Excel 在财务管理中的应用 ( 第二版 ) 月 日 年日期的格式如表 1-5 所示 表 1-5 月 日 年日期的格式 显示日期将月显示为 1 12 将月显示为 将月显示为 Jan Dec 将月显示为 January December 将月显示为该月份的第一个字母将日显示为 1 31 将日显示为 将日显示为 Sun Sat 将日显示为 Sunday Saturday 将年显示为 将年显示为 使用的代码 m mm mmm mmmm mmmmm d dd ddd dddd yy yyyy 时 分 秒时间的格式如表 1-6 所示 表 1-6 时 分 秒时间的格式显示时间使用的代码 将小时显示为 0 23 将小时显示为 将分显示为 0 59 将分显示为 将秒显示为 0 59 将秒显示为 使小时显示类似于 4 AM H hh m mm S SS h AM/PM 使时间显示类似于 4:36 PM 使时间显示类似于 4:36:03 P 以小时为单位显示运行时间, 如 以分为单位显示运行时间, 如 63:46 以秒为单位显示运行时间秒的分数 h:mm AM/PM h:mm:ss A/P [h]:mm [mm]:ss [ss] h:mm:ss.00

44 第一章 Excel 2007 软件基础 37 AM 和 PM 格式如果包含 AM 或 PM, 则按照 12 小时制显示小时, AM 或 A 表示从午夜十二点到中午十二点之间的时间, PM 或 P 表示从中午十二点到午夜十二点之间的时间, 否则, 按 24 小时制显示小时 m 或 mm 代码必须紧跟在 h 或 hh 代码之后, 或后面紧接 ss 代码 ; 否则,Excel 将显示月而不是分 如果要使用默认的日期或时间格式, 则单击包含日期或时间的单元格, 然后按 Ctrl+ Shift+# 或 Ctrl+Shift+@ 组合键 当尝试通过选择 分类 列表框中的 常规 撤消日期或时间格式时,Excel 将显示一个数字代码 当再次输入日期或时间时,Excel 会显示默认日期或时间格式 要输入特定日期或时间格式, 如 2014 年 1 月, 可能要通过选择 分类 列表框中的 文本 将其设置为文本格式 (6) 设置千位分隔符 1) 选择要设置格式的单元格 ( 取消选择的单元格区域, 单击工作表中的任意单元格 ) 2) 在 开始 选项卡中, 单击 数字 右侧的 设置单元格格式 : 数字 对话框启动器 3) 在 数字 选项卡中, 单击 分类 列表框中的 数值 要显示或隐藏千位分隔符, 则勾选或取消勾选 使用千位分隔符 (,) 复选框, 如图 1-79 所示 要快速显示千位分隔符, 可以单击 开始 选项卡的 数字 组中的 千位分隔样式, 如图 1-80 所示 图 1-79 使用千位分隔符 复选框 图 1-80 千位分隔样式 在默认情况下 Excel 显示系统千位分隔符, 还可以通过更改 控制面板 中的区域 设置来指定不同的系统分隔符 七 设置公式函数 运算符 1. 认识公式公式是对工作表中的数值执行计算的等式 公式以等号 = 开头, 公式也可以包括函数 常量 运算符和引用, 如图 1-81 所示 函数 : 预先编写的程序 公式, 可以对一个或多个值执行运算, 并返回一个或多个值 函数可以 = sum ( ) * A1 ( 1 + B3 ) 函数变量常量图 1-81 公式包含的元素 简化和缩短工作表中的公式, 尤其在用公式执行很长或复杂的计算时 常量 : 不进行计算的值, 因此也不会发生变化 例如, 数字 1000 及文本 销售量

45 38 Excel 在财务管理中的应用 ( 第二版 ) 都是常量 表达式及表达式产生的值都不是常量 运算符 : 一个标记或符号, 指定表达式内执行的计算的类型 有数学 比较 逻辑和引用运算符等 (1) 常量常量是不用计算的值 例如, 日期 数字 1000 及文本 销售量 都是常量 表达式或由表达式得出的结果不是常量 如果在公式中使用常量而不是对单元格的引用 ( 如 ), 则只有在更改公式时其结果才会更改 (2) 运算符运算符用于指定对公式中的元素执行的计算类型 计算时有一个默认的次序, 但可以使用括号更改计算次序 计算运算符分为四种不同类型 : 算术 比较 文本连接和引用 1) 算术运算符 若要完成基本的数学运算 ( 加 减 乘 除等 ) 合并数字及生成数值结果, 则使用表 1-7 所示的算术运算符 表 1-7 算术运算符及其含义算术运算符含义示例 +( 加号 ) 加法 6+3 -( 减号 ) 减法 6-1 负数 -1 *( 星号 ) 乘法 6 *3 /( 正斜杠 ) 除法 6/3 6 月 3 日 %( 百分号 ) 百分比 60% ^( 脱字号 Shift+6) 乘方 6^2 2) 比较运算符 可以使用表 1-8 所示的运算符比较两个值 表 1-8 比较运算符及其含义 比较运算符含义示例 =( 等于号 ) 等于 A1=B1 >( 大于号 ) 大于 A1>B1 <( 小于号 ) 小于 A1<B1 >=( 大于等于号 ) 大于或等于 A1>=B1 <=( 小于等于号 ) 小于或等于 A1<=B1 <>( 不等号 ) 不等于 A1<>B1 当用这些运算符比较两个值时, 结果为逻辑值 TRUE 或 FALSE 3) 文本连接运算符 可以使用与号 & 联接或连接一个或多个文本字符串, 以生成一段文本, 如表 1-9 所示

46 第一章 Excel 2007 软件基础 39 表 1-9 文本运算符及其含义 文本运算符含义示例 &( 与号 ) 将两个值连接或串起来产生一个连续的文本值 张 & 三 4) 引用运算符 可以使用表 1-10 所示的运算符对单元格区域进行合并计算 表 1-10 引用运算符号及其含义 引用运算符含义示例 :( 冒号 ) 区域运算符, 生成对两个引用之间所有单元格的引用 ( 包括这两个引用 ) B5:B15,( 逗号 ) 联合运算符, 将多个引用合并为一个引用 SUM(B5:B15,D5:D15) ( 空格 ) 交集运算符, 生成对两个引用中共有的单元格的引用 A1:D2 B2:C8 (3)Excel 执行公式运算的次序公式按特定次序进行值的计算 Excel 中的公式始终以等号 = 开头, 该等号告知 Excel 随后的字符组成一个公式, 等号后面是要计算的元素 ( 即操作数 ), 各操作数之间由运算符分隔 Excel 按照公式中每个运算符的特定次序从左到右计算公式 (4) 运算符优先级如果一个公式中有若干个运算符,Excel 将按表 1-11 中的次序进行计算 如果一个公式中的若干个运算符具有相同的优先顺序 ( 如一个公式中既有乘号又有除号 ),Excel 将从左到右进行计算 表 1-11 运算符 运算符说明 :( 冒号 ) ( 单个空格 ) 引用运算符,( 逗号 ) - 负数 ( 如 -1) % 百分比 ^ 乘方 * 和 / 乘和除 + 和 - 加和减 & 连接两个文本字符串 ( 串连 ) = <> <= 比较运算符 >= <>

47 40 Excel 在财务管理中的应用 ( 第二版 ) (5) 使用括号 若更改求值的顺序, 则将公式中要先计算的部分用括号括起来 例如, 下面公式的 结果是 26, 因为 Excel 先进行乘法运算后进行加法运算 将 2 与 10 相乘, 然后再加上 6, 即得到结果 =6+2*10 但是, 如果用括号对该语法进行更改,Excel 将先求出 6 加 2 之和, 再用结果乘 以 10 得 80 =(6+2) *10 在以下示例中, 公式第一部分的括号强制 Excel 先计算 B4+1, 然后再除以单元 格 D5 E5 和 F5 中值的和 2. 在公式中使用函数和嵌套函数 =(B4+1)/SUM(D5:F5) 函数是预定义的程序或公式, 通过使用一些称为参数的特定数值以特定的顺序或结 构执行计算 函数可用于执行简单或复杂的计算 (1) 认识函数 下面的 SUM 函数示例说明了函数的语法, 求单元格 A1 B2 的和 =SUM(A1,B2) 1) 函数的结构 函数的结构以等号 = 开始, 后面紧跟函数名称和左括号, 然后 以逗号分隔输入该函数的参数, 最后是右括号 合键 2) 函数名称 如果要查看可用函数的列表, 可单击一个单元格并按 Shift+F3 组 参数是函数中用来执行操作或计算的值 参数的类型与函数有关, 函数中常用的参 数类型包括数字 文本 单元格引用和名称 指定的参数都必须为有效参数值 参数也可以是常量 公式或其他函数 参数工具提示, 在输入函数时, 会出现一个带有语法和参数的工具提示 例如, 输入 =sum 时, 工具提示就会出现 工具提示只在使用内置函数时出现, 如图 1-82 所示 (2) 输入函数如果创建带函数的公式, 插入函数 对话框将有助于输入工作表函数 在公式中输入函数时, 插入函数 对话框将显示函数的名称及其各个参数 函数及其各个参数的说明 函数的当前结果及整个公式的当前结果, 插入函数的操作步骤如图 1-82 参数工具提示图 1-83 所示 为了便于创建和编辑公式, 同时尽可能减少输入和语法错误, 可以使用公式记忆式输入 当输入 =( 等号 ) 和开头的几个字母或显示触发字符之后,Excel 会在单元格的下方显示一个动态下拉列表, 该列表中包含与这几个字母或该触发字符相匹配的有效函数 参数和名称 然后将该下拉列表中的一项插入公式中

48 操作步骤5) 输入公式后, 按 Enter 键 第一章 Excel 2007 软件基础 41 1) 选择需输入公式的单元格 2) 单击 编辑栏 上的 插入函数 按钮, 开始 引用函数 3) 选择要使用的函数 在 搜索函数 文本框中输入对需要解决 的问题的说明 ( 如输入 数值相加 将返回 SUM 函数 ), 或者浏 览 或选择类别 下拉列表框中的分类 4) 输入参数 若要将单元格引用作为参数输入, 单击 压缩对话 框 暂时隐藏该对话框, 在工作表中选择单元格, 然后按 展开对 话框 图 1-83 插入函数的操作步骤 (3) 嵌套函数在某些情况下, 可能需要将某函数作为另一函数的参数使用 例如, 下面的公式使用了嵌套的 AVERAGE 函数并将结果与值 100 进行了比较 =IF(AVERAGE(C1:D2)<100,SUM(F4:G5),5) AVERAGE 和 SUM 函数嵌套在 IF 函数中 1) 有效的返回值, 当嵌套函数作为参数使用时, 它返回的数值类型必须与参数使用的数值类型相同 例如, 如果参数返回一个 TRUE 或 FALSE 值, 那么嵌套函数也必须返回一个 TRUE 或 FALSE 值 否则 Excel 将显示 #VALUE! 错误值 2) 嵌套级别限制, 公式可包含多达七级的嵌套函数 当函数 B 在函数 A 中用作参数时, 函数 B 则为第二级函数 例如,AVERAGE 函数和 SUM 函数都是第二级函数, 因为它们都是 IF 函数的参数 在 AVERAGE 函数中嵌套的函数则为第三级函数, 以此类推 (4) 使用 引用 引用的作用在于标识工作表中的单元格或单元格区域, 并告知 Excel 在何处查找公式中所使用的数值或数据 通过引用可以在一个公式中使用工作表不同部分中包含的数据, 或者在多个公式中使用同一个单元格的数值 还可以引用同一个工作簿中其他工作表上的单元格和其他工作簿中的数据 1) 外部引用即链接, 对其他 Excel 工作簿中的工作表单元格或区域的引用, 或对其他工作簿中的定义名称的引用 默认引用样式, 默认情况下,Excel 使用 A1 引用样式, 此样式引用列标 ( 从 A~XFD, 共 列 ) 及数字标识行 ( 从 1~ ) 若要引用某个单元格, 输入后跟行号的列标 例如,B6 引用列 B 和行 6 交叉处的单元格 单元格示例如表 1-12 所示 表 1-12 单元格示例 要求输入 列 A 和行 12 交叉处的单元格 在列 A 和行 12~ 20 之间的单元格区域 在行 15 和列 D 到列 E 之间的单元格区域 A12 A12:A20 D15:E15

49 42 Excel 在财务管理中的应用 ( 第二版 ) 续表 要 求 输 入 行 5 中的全部单元格 5:05 行 5 ~ 20 之间的全部单元格 5:20 列 F 中的全部单元格 列 H ~ J 之间的全部单元格 列 A ~ E 和行 10 ~ 20 之间的单元格区域 F:F H:J A10:E20 引用其他工作表中的单元格 在以下示例中,SUM 函数将计算同一个工作簿中名 为 人员 的工作表的 A1:B10 区域内的平均值 =SUM(Sheet1!A1:B10) 2) 相对引用, 公式中的相对单元格引用 ( 如 A1) 是基于包含公式和单元格引用 的单元格的相对位置 如果公式所在单元格的位置改变, 引用也随之改变 如果多行或多列地复制或填充公式, 引用会自动调整 默认情况下, 新公式使用相对引用 例如, 如果将单元格 B2 中的相对引用复制或填充到单元格 B3, 将自动从 =A1 调整到 =A2, 如图 1-84 所示 3) 绝对引用, 公式中的绝对单元格引用 ( 如 $A$1) 总是在特定位置引用单元格 如果公式所在单元格的位置改变, 绝对引用将保持不变 如果多行或多列地复制或填充公式, 绝对引用将不做调整 默认情况下, 新公式使用相对引用, 可能需要将它们转换为绝对引用 例如, 如果将单元格 B2 中的绝对引用复制或填充到单元格 B3, 则在两个单元格中一样, 都是 $A$1, 如图 1-85 所示 4) 混合引用, 具有绝对列和相对行或绝对行和相对列 绝对引用列采用 $A1 $B1 等形式 绝对引用行采用 A$1 B$1 等形式 如果公式所在单元格的位置改变, 则相对引用将改变, 而绝对引用将不变 如果多行或多列地复制或填充公式, 相对引用将自动调整, 而绝对引用将不做调整 例如, 如果将一个混合引用从 A2 复制到 B3, 它将从 =A$1 调整到 =B$1, 如图 1-86 所示 图 1-84 相对引用示例图 1-85 绝时引用示例图 1-86 混合引用 5) 三维引用样式 如果分析同一工作簿中多个工作表上相同单元格或单元格区域中的数据, 则使用三维引用 三维引用包含单元格或区域引用, 前面加上工作表名称的范围 Excel 使用存储在引用开始名和结束名之间的任何工作表 例如,=SUM (Sheet2:Sheet13!E5) 将计算 E5 单元格内包含的所有值的和, 单元格取值范围是从工作表 2 到工作表 13 使用三维引用可以引用其他工作表中的单元格 定义名称, 还可以通过使用下列函数来创建公式 :SUM AVERAGE AVERAGEA COUNT COUNTA MAX MAXA

50 第一章 Excel 2007 软件基础 43 MIN MINA PRODUCT STDEV STDEVA STDEVP STDEVPA VAR VARA VARP 和 VARPA 等 三维引用不能用于数组公式中, 也不能与交叉引用运算符 ( 空格 ) 一起使用, 也不能用在使用了绝对交集的公式中 以下示例演示在移动 复制 插入或删除三维引用中包括的工作表时出现的情况 该示例使用公式 =SUM(Sheet2:Sheet6!A1:A3) 对从 Sheet2 到 Sheet6 的每个工作表中的 A1~A3 单元格求和 1) 插入或复制 如果在 Sheet2 和 Sheet6 之间插入或复制工作表,Excel 将在计算中包含所添加的工作表中从单元格 A1~A3 的所有数值 2) 删除 如果删除了 Sheet2 和 Sheet6 之间的工作表,Excel 将删除计算中相应的值 3) 移动 如果将 Sheet2 和 Sheet6 之间的工作表移动到引用工作表区域之外的位置, Excel 将删除计算中相应的值 4) 移动工作表 如果将 Sheet2 或 Sheet6 移到同一工作簿中的其他位置,Excel 将对计算进行调整以包含它们之间的新工作表区域 5) 删除工作表 如果删除了 Sheet2 或 Sheet6,Excel 将对计算进行调整以包含它们之间的工作表区域 3. 在公式中使用名称 1) 名称 : 代表单元格 单元格区域 公式或常量值的单词或字符串 名称更易于理解, 例如, 可将 A1:B2 定义为 产品 名称 可创建已定义名称来代表单元格 单元格区域 公式 常量值或 Excel 表 名称是一种有意义的简写形式, 它更便于我们了解单元格引用 常量 公式或表的用途 2) 创建和输入名称 : 使用 名称框 最适用于为选定的区域创建工作簿级别的名称 从选定区域创建名称, 可以使用工作表中选择的单元格根据现有的行和列标签方便地创建名称 使用 新建名称 对话框, 当希望更灵活地创建名称 ( 如指定本地工作表级别的范围或创建名称批注 ) 图 1-87 创建名称时, 此方法最适合, 如图 1-87 所示 也可以通过执行下列操作来输入名称 : 选择需要设置名称的单元格区域, 在编辑栏内输入该区域的名称, 按 Enter 键结束输入 4. 使用数组公式和数组常量数组公式对一组或多组值执行多重计算, 并返回一个或多个结果 数组公式括于大括号 { } 中 按 Ctrl+Shift+Enter 组合键可以输入数组公式 数组公式可以执行多项计算并返回一个或多个结果 数组公式对两组或多组名为数组参数的值执行运算 每个数组参数都必须具有相同数量的行和列 除了用快捷键 Ctrl+ Shift+Enter 输入公式外, 创建数组公式的方法与创建其他公式的方法相同 某些内置函数是数组公式, 并且必须作为数组输入才能获得正确的结果

51 44 Excel 在财务管理中的应用 ( 第二版 ) 如果不想在工作表的单个单元格中输入每个常量值, 则可用数组常量来代替引用 (1) 使用数组公式 在输入数组公式时,Excel 自动在大括号 { } 之间插入公式 图 1-88 数组公式示例一 些计算结果相加 1) 计算单个结果 此类数组公式通过用一个数组公式代 替多个公式的方式来简化工作表模式 例如, 求某公司当月总销售额, 如图 1-88 所示 当将公式 {=SUM(B2:C2*B3:C3)} 作为数组公式输入 时, 该公式将每个产品的 单价 和 销售量 相乘后再将这 2) 计算多个结果 一些工作表函数返回多组数值, 或需要将一组值作为一个参数 如果要使数组公式能计算出多个结果, 则必须将数组输入到与数组参数具有相同的列数和行数的单元格区域中 例如, 求某公司各产品销售额, 如图 1-89 所示 数组公式输入完成后切记按 Ctrl+Shift+Enter 组合键确认数组公式, 若需修改数组公式, 改完后需再次按 Ctrl+Shift+Enter 组合键确认数组公式 图 1-89 数组公式示例二 (2) 使用数组常量在普通公式中, 可输入包含数值的单元格引用或数值本身, 其中该数值与单元格引用被称为常量 同样, 在数组公式中也可输入数组引用, 或包含在单元格中的数值数组, 其中该数值数组和数组引用被称为数组常量 数组公式可以按与非数组公式相同的方式使用常量, 但是必须按特定格式输入数组常量 数组常量可包含数字 文本 逻辑值 ( 如 TRUE FALSE 或错误值 #N/A) 数组常量中可包含不同类型的数值 例如,{1,3,4;TRUE,FALSE,TRUE} 数组常量中的数字可以使用整数 小数或科学记数格式 文本必须包含在半角的双引号内, 如 产品 数组常量不包含单元格引用 长度不等的行或列 公式或特殊字符 $( 美元符号 ) 括弧或 %( 百分号 ) 在设置数组常量的格式时应确保用大括号 { } 将其括起 ; 不同列的数值用逗号, 分开, 例如, 若要表示数值 和 40, 必须输入 {10,20,30,40} 这个数组常量是一个 1 行 4 列数组, 相当于一个 1 行 4 列的引用 ; 不同行的值用分号 ; 隔开 例如, 在第一行中输入 , 第二行输入 , 则需要输入一个 2 行 4 列的数组常量 :{10,20,30,40;50,60,70,80} 5.Excel 执行公式运算的次序 在某些情况中, 执行计算的次序会影响公式的返回值, 因此, 了解如何确定计算次序及如何更改次序及获得所需结果非常重要 公式按特定次序进行值的计算 Excel 中的公式始终以等号 = 开头, 该等号告知 Excel 随后的字符组成一个公式, 等号后面是要计算的元素 ( 即操作数 ), 各操作数之间

52 对正复制的公$A1( 绝对列和相对行 ) $A3 式第一章 Excel 2007 软件基础 45 由运算符分隔 Excel 按照公式中每个运算符的特定次序从左到右计算公式 6. 复制和移动公式 (1) 复制公式 1) 选择包含需要复制的公式的单元格 2) 在 开始 选项卡的 剪贴板 组中, 单击 复制 按钮 3) 执行下列操作之一 : 1 如果粘贴公式和所有格式, 在 开始 选项卡的 剪贴板 组中单击 粘贴 按钮 2 如果只粘贴公式, 在 开始 选项卡的 剪贴板 组中, 单击 粘贴 下方的下拉箭头, 再单击 选择性粘贴 选项, 然后点选 公式 单选按钮 也可以只粘贴公式结果, 在 开始 选项卡的 剪贴板 组中, 单击 粘贴 下方的下拉箭头, 再单击 选择性粘贴 选项, 然后点选 数值 单选按钮, 再单击 确定 按钮 表 1-13 概述了当将包含引用的单元格向下和向右各复制两个单元格时, 引用类型更新的方式 表 1-13 引用类型更新方式 引用 要求 输 入 $A$1( 绝对列和绝对行 ) $A$1 A$1( 相对列和相对行 ) C$1 A1( 相对列和相对行 ) C3 (2) 移动公式 1) 选择包含要移动的公式的单元格 2) 在 开始 选项卡的 剪贴板 组中, 单击 剪切 按钮 3) 也可通过将所选单元格的边框拖动到粘贴区域左上角的单元格中来移动公式 这将替换现有的任何数据 1 如果粘贴公式和任何格式, 在 开始 选项卡的 剪贴板 组中, 直接单击 粘贴 按钮 2 如果仅粘贴公式, 在 开始 选项卡的 剪贴板 组中, 单击 粘贴 下方的下拉箭头, 再单击 选择性粘贴 选项, 然后点选 公式 单选按钮, 再单击 确定 按钮 7. 禁止公式在编辑栏中显示 1) 选定要隐藏的公式所在的单元格区域, 还可以根据需要选定非相邻区域 ( 该选定区域包含彼此互不相邻的两个或多个单元格或区域 在图表中绘制非相邻区域时, 确保组合的选定区域的形状为矩形 ) 或整个工作表 2) 在 开始 选项卡的 单元格 组中, 单击 格式 选项, 再单击 设置单元

53 46 Excel 在财务管理中的应用 ( 第二版 ) 格格式 选项, 然后单击 保护 选项卡, 如图 1-90 所示 3) 勾选 保护 选项卡中的 隐藏 复选框, 再单击 确定 按钮 4) 在 开始 选项卡的 单元格 组中, 单击 格式 选项, 然后单击 保护工作表 选项 在 保护工作表 对话框中, 必须勾选 保护工作表及锁定的单元格内容 复选框, 如图 1-91 所示 图 1-90 保护 选项卡 图 1-91 保护工作表 图 1-92 撤消工作表保护 9. 公式常用技巧 8. 通过取消保护显示隐藏的公式 1) 在 审阅 选项卡的 更改 组中, 单击 撤消工作表保护 选项, 如图 1-92 所示 2) 选取要取消隐藏其公式的单格区域, 还可以根据需要选定非相邻区域 1) 快速复制公式 可以在一系列单元格中快速输入同一个公式, 选择要计算的区域输入公式, 然后按 Ctrl+Enter 组合键 例如, 如果在区域 C1:C8 中输入 =SUM(A1:B1), 然后按 Ctrl+Enter 组合键, Excel 将在该区域内的每个单元格中输入该公式, 并将 A1 用作相对引用, 如图 1-93 所示 图 1-93 快速复制公式 2) 使用公式记忆式输入 为了便于创建和编辑公式, 同时尽可能减少输入和语法错误, 可以使用公式记忆式输入 当输入 =( 等号 ) 和开头的几个字母或显示触发字符之后,Excel 会在单元格的下方显示一个动态下拉列表, 该列表中包含与这几个字母或该触发字符相匹配的有效函数 参数和名称, 然后可以将该下拉列表中的一项插入公式中, 如图 1-94 所示 3) 使用函数工具提示 如果熟悉函数的参数, 则可以使用在输入函数名称和左括号后出现的函数工具提示 单击函数名称可查看该函数的帮助主题, 单击参数名称可以

54 第一章 Excel 2007 软件基础 47 在公式中选择相应的参数, 如图 1-95 所示 图 1-94 使用公式记忆式输入 图 1-95 使用函数工具提示 10. 在创建公式时避免出现常见的错误表 1-14 汇总了输入公式时常见的错误及纠正的方法 表 1-14 输入公式时常见的错误及纠正方法 要求说明 所有左括号和右括号匹配用冒号表示区域输入所有必需参数函数的嵌套不超过七层将其他工作表名称包含在单引号中包含外部工作簿的路径输入无格式的数字 确保所有括号都成对出现 创建公式时,Excel 在输入括号时将括号显示为彩色, 一组括号一种颜色 引用单元格区域时, 使用冒号 : 分隔对单元格区域中第一个单元格的引用和对最后一个单元格的引用 有些函数包含必需的参数 可以在函数中输入或嵌套七层以下的函数 如果公式中引用了其他工作表或工作簿中的值或单元格, 并且这些工作簿或工作表的名称中包含非字母字符, 那么必须用单引号 将其名称引起来 确保每个外部引用都包含工作簿的名称和路径 在公式中输入数字时, 不需为数字设置格式 例如, 即使要输入的值是 1,000, 也应在公式中输入 1000 八 Excel 2007 软件相对前期版本增加的内容 1. 用户界面面向结果新的用户界面面向结果, 使用户可以轻松地在 Excel 中工作 以往版本中的命令和功能常常深藏在复杂的菜单和工具栏中, 现在用户可以在包含命令和功能逻辑组的 面向任务的选项卡中更轻松地找到它们 新的用户界面利用显示有可用选项的下拉库替代了以往的许多对话框, 并且提供了描述性的工具提示或示例预览来帮助用户选择正确的选项 无论用户在新的用户界面中执行任何活动 ( 不管是格式化还是分析数据 ),Excel 都会显示成功完成该任务最合适的工具 Excel 2007 软件用户界面如图 1-96 所示 图 1-96 Excel 2007 软件用户界面

55 48 Excel 在财务管理中的应用 ( 第二版 ) 2. 更多行和列及其他新限制为了使用户能够在工作表中浏览大量数据,Excel 2007 软件支持每个工作表中最多有 行和 列 具体来说,Excel 2007 软件为 行乘以 列, 与 Excel 2003 相比, 它提供的可用行增加了 1500%, 可用列增加了 6300% 用户可能会惊奇地发现, 列现在以 XFD 而不是 IV 结束, 如图 1-97 所示 Excel 2007 最末列列标 XFD, 单表列标为 :A,B,, XFD Excel 2003 最末列列标 IV, 单表列标为 :A,B,, IV 图 1-97 Excel 2007 与 Excel 2003 最末列的对比 现在, 用户可以在同一个工作簿中使用无限多的格式类型, 而不再仅限于 4000 种 ; 每个单元格的单元格引用数量从 8000 增长到了任意数量, 唯一的限制就是用户的可用 内存 为了改进 Excel 的性能, 内存管理已从 Excel 2003 中的 1GB 内存增加到 Excel 2007 软件中的 2GB 由于 Excel 2007 软件支持双处理器和多线程芯片集, 用户将同时在包 含大量公式的大型工作表中体验到更快的运算速度 Excel 2007 软件还支持最多 种颜色 3. 主题和 Excel 样式 在 Excel 2007 软件中, 可以通过应用主题和使用特定样式在工作表中快速设置数据 格式 主题可以与其他 Microsoft Office 2007 软件发布版程序 ( 如 Microsoft Word 和 Microsoft PowerPoint) 共享, 而样式只用于更改特定于 Excel 的项目 ( 如 Excel 表格 图表 数据透视表 形状或图 ) 的格式 1) 主题应用 主题是一组预定义的颜色 字体 线条和填充效果, 可应用于整个 工作簿或特定项目, 如图表或表格 它们可以帮助用户创建外观精美的文档 用户可以使用本公司提供的公司主题, 也可以从 Excel 提供的预定义主题中选择 创建用户自己的具有统一 专业外观的主题并将其应用于用户所有的 Excel 工作簿和其他 Microsoft Office 2007 软件发布版文档, 也是很简单的事情 在创建主题时, 可以分别更改颜色 字体和填充效果, 以便用户对任一或所有这些选项进行更改, 如图 1-98 所示 2) 样式使用 样式是基于主题的预定义格式, 可应用它来更改 Excel 表格 图表

56 第一章 Excel 2007 软件基础 49 数据透视表 形状或图的外观 如果内置的预定义样式不符合用户的要求, 用户可以自定义样式 对于图表来说, 用户可以从多个预定义样式中进行选择, 但不能创建自己的图表样式, 如图 1-99 所示 图 1-98 主题应用 图 1-99 样式使用 Excel 2007 同 Excel 2003 中一样, 单元格样式用于设置所选单元格的格式 现在用户可以快速应用预定义单元格样式 大多数单元格样式都不基于应用到工作簿的主题, 而且用户可以轻松创建自己的单元格样式 4. 丰富的条件格式在 Microsoft Office 2007 软件中, 用户可以使用条件格式直观地注释数据以供分析和演示使用 若在数据中轻松地查找例外和发现重要趋势, 则可以实施和管理多个条件格式规则, 这些规则以渐变色 数据柱线和图标集的形式将可视性极强的格式应用到符合这些规则的数据 条件格式也很容易应用, 即只需进行几次单击操作, 即可看到可用于分析的数据中的关系, 如图 所示 5. 轻松编写公式下列改进使在 Excel 2007 软件中编写公式更为轻松 (1) 可调整的编辑栏图 条件格式编辑栏会自动调整以容纳长而复杂的公式, 从而防止公式覆盖工作表中的其他数据 与 Excel 早期版本相比, 用户可以编写的公式更长 使用的嵌套级别更多, 如图 所示 图 编辑栏 (2) 函数记忆式输入使用函数记忆式输入, 可以快速写入正确的公式语法 它不仅可以轻松检测到用户要使用的函数, 还可以获得完成公式参数的帮助, 从而使用户在第一次使用时及今后的每次使用中都能获得正确的公式 (3) 结构化引用除了单元格引用 ( 如 A2 和 R2C2),Excel 2007 软件还提供了在公式中引用命名区

57 50 Excel 在财务管理中的应用 ( 第二版 ) 域和表格的结构化引用 (4) 轻松访问命名区域 通过使用 Excel 2007 软件命名管理器, 用户可以在一个中心位置来组织 更新和管 理多个命名区域, 这有助于任何需要使用用户的工作表的人理解其中的公式和数据 6. 新的 OLAP 公式和多维数据集函数 当用户在 Excel 2007 软件中使用多维数据库 ( 如 SQL Server Analysis Services) 时, 可以使用 OLAP 公式建立复杂的 任意形式的 OLAP 数据绑定报表 新的多维数据集函 数可用来从 Analysis Services 中提取 OLAP 数据 ( 数据集和数值 ) 并将其显示在单元格 中 当用户将数据透视表公式转换为单元格公式时, 或者当用户在输入公式时对多维数据集函数参数使用记忆式输入时, 可以生成 OLAP 公式 7. 改进的排序和筛选功能 在 Excel 2007 软件中, 用户可以使用增强了的筛选和排序功能, 快速排列工作表数据以找出所需的信息 例如, 现在可以按颜色和三个以上 ( 最多为 64 个 ) 级别对数据进行排序 用户还可以按颜色或日期筛选数据, 在 自动筛选 下拉列表中显示 1000 多个项, 选择要筛选的多个项, 以及在数据透视表中筛选数据, 如图 图 排序和筛选所示 8.Excel 表格的增强功能 在 Excel 2007 软件中, 用户可以使用新用户界面快速创建 格式化和扩展 Excel 表格 ( 在 Excel 2003 中称为 Excel 列表 ) 来组织工作表上的数据, 以便更容易使用这些数据 下面列出了针对表格的新功能或改进功能 1) 表格标题行 其可以打开或关闭表格标题行 如果显示表格标题, 则当用户在长表格中移动时, 表格标题会替代工作表标题, 从而使表格标题始终与表列中的数据出现在一起 2) 计算列 计算列使用单个公式调整每一行 它会自动扩展以包含其他行, 从而使公式立即扩展到这些行 用户只需输入公式一次, 而无需使用 填充 或 复制 命令 3) 自动筛选 默认情况下, 工作表中会启用 自动筛选 以支持强大的表格数据排序和筛选功能 4) 结构化引用 这种类型的引用允许用户在公式中使用表列标题名称代替单元格引用 ( 如 A2 或 R2C2) 5) 汇总行 在汇总行中, 用户可以使用自定义公式和文本输入 6) 表样式 用户可以应用表样式, 以对表快速添加设计师水平的专业格式 如果在表中启用了可选行样式,Excel 将通过一些操作保持可选样式规则, 而这些操作在以往版本中会破坏布局, 如筛选 隐藏行或者对行和列手动重新排列

58 第一章 Excel 2007 软件基础 新的图表外观在 Excel 2007 软件中, 用户可以使用新的图表工具轻松创建能有效交流信息的 具有专业水准外观的图表 基于应用到工作簿的主题, 新的 最具流行设计的图表外观包含很多特殊效果, 如三维 透明和柔图 图表样式和阴影, 如图 所示 使用新的用户界面, 用户可以轻松浏览可用的图表类型, 以便为自己的数据创建合适的图表 由于 Excel 2007 软件提供了大量的预定义图表样式和布局, 用户可以快速应用一种外观精美的格式, 然后在图表中进行所需的细节设置 可视图表元素选取器除了设置快速布局和快速格式外, 用户还可以在新的用户界面中快速更改图表的每一个元素, 以更好地呈现数据 只需进行几次单击操作, 即可添加或删除标题 图例 数据标签 趋势线和其他图表元素 外观新颖的艺术字 由于 Excel 2007 软件中的图表是用艺术字绘制的, 因而可对艺术字形状所做的几乎任何操作都可应用于图表及其元素 例如, 可以添加柔和阴影或倾斜效果使元素突出显示, 或使用透明效果使在图表布局中被部分遮住的元素可见 用户也可以使用逼真的三维效果 清晰的线条和字体, 图表中的线条减轻了锯齿现象, 而且对文本使用了 ClearType 字体来提高可读性 比以往版本更多的颜色 用户可以轻松地从预定义主题颜色中进行选择和改变其颜色强度 若要对颜色进行更多控制, 用户还可以从 颜色 对话框内的 种颜色中选择来添加自己喜欢的颜色 由于图表模板在新的用户界面中, 所以将喜爱的图表另存为图表模板变得更为轻松 10. 共享的图表在 Microsoft Office 2007 软件中, 图表可在 Excel Word 和 PowerPoint 之间共享 现在,Word 和 PowerPoint 合并了 Excel 强大的图表功能, 而不再使用 Microsoft Graph 提供的图表功能 由于 Excel 工作表被用作 Word 和 PowerPoint 图表的图表数据表, 因而共享的图表提供了 Excel 的丰富功能, 包括使用公式 筛选 排序, 以及将图表链接到外部数据源 [ 如 Microsoft SQL Server 和 Analysis Services(OLAP)] 以使图表具有最新信息的能力 包含图表数据的 Excel 工作表可存储在 Word 文档或 PowerPoint 演示文稿中, 或者存储在一个单独文件中以减小文档大小 用户可以轻松地在文档之间复制和粘贴图表, 或将图表从一个程序复制和粘贴到另一个程序 当将图表从 Excel 复制到 Word 或 PowerPoint 时, 图表会自动更改以匹配 Word 文档或 PowerPoint 演示文稿, 但是用户也可以保留 Excel 图表格式 Excel 工作表数据可嵌入 Word 文档或 PowerPoint 演示文稿中, 但是用户也可以将其保留在 Excel 源文件中 在 PowerPoint 中, 用户可以更轻松地使用动画强调基于 Excel 的图表中的数据, 这样可使整个图表或图例项和轴标签具有动画效果 在柱形图中, 甚至可以让个别柱形

59 52 Excel 在财务管理中的应用 ( 第二版 ) 具有动画效果以更好地阐明某个要点 这样可以更轻松地找到并更好地控制动画功能 例如, 用户可以更改单个动画步骤并使用更多动画效果 11. 易于使用的数据透视表 在 Excel 2007 软件中, 数据透视表比在 Excel 的早期版本中更易于使用 使用新的数据透视表用户界面时, 只需进行几次单击操作即可显示关于要查看的数据信息, 而不再需要将数据拖动到并非总是易于定位的目标拖放区域 用户只图 数据透视表需在新的数据透视表字段列表中选择要查看的字段即可, 如图 所示 创建数据透视表后, 可以利用许多其他新功能或改进功能来汇总 分析和格式化数据透视表数据 1) 在数据透视表中使用撤销功能 用户可以撤销创建或重排数据透视表所执行的大多数操作 2) 加号和减号明细指示器 这些指示器用于指示是否可以展开或折叠部分数据透视表以显示更多或更少的信息 3) 排序和筛选 排序现在变得很简单, 只需在要排序的列中选择一项后单击排序按钮即可 可以使用数据透视表筛选器 ( 如大于 等于或包含 ) 来筛选数据 4) 条件格式 可以按单元格或单元格交叉部分将条件格式应用到 Excel 2007 软件数据透视表 5) 数据透视表样式和布局 正如对 Excel 表格和图表一样, 用户可以快速将预定义或自定义样式应用到数据透视表 而且在新的用户界面中更改数据透视表的布局也更加容易 6) 数据透视图 像数据透视表一样, 在新的用户界面中创建数据透视图也更加容易 所有的筛选改进也可用于数据透视图 创建数据透视图时, 可以使用特定的数据透视图工具和上下文菜单, 从而使用户可以在图表中分析数据 也可以按照对常规图表相同的方式, 更改图表或其元素的布局 样式和格式 在 Excel 2007 软件中, 更改数据透视图时会保留所应用的图表格式, 这是较之 Excel 早期版本工作方式的一个改进 12. 快速连接到外部数据 在 Excel 2007 软件中, 不再需要了解公司数据源的服务器名称或数据库名称 现在, 用户可以使用 快速启动 从管理员或工作组专家提供的可用数据源列表中选择 Excel 中的连接管理器使用户可查看工作簿中的所有连接, 并且重新使用连接或用一种连接替代另一种连接更加容易 13. 新的文件格式 1) 基于 XML 的文件格式 (.xlsx) 在 Microsoft office 2007 软件中,Microsoft 为 Word Excel 和 PowerPoint 引入了新的 称为 Open XML 格式 的文件格式 这些新

60 第一章 Excel 2007 软件基础 53 文件格式便于与外部数据源结合, 还减小了文件大小并改进了数据恢复功能 在 Excel 2007 软件中,Excel 工作簿的默认格式是基于 Excel 2007 软件 XML 的文件格式 其他可用的基于 XML 的格式是基于 Excel 2007 软件 XML 和启用了宏的文件格式 (.xlsm) 用于 Excel 模板的 Excel 2007 软件文件格式 (.xltx), 以及用于 Excel 模板的 Excel 2007 软件启用了宏的文件格式 (.xltm), 如图 所示 Excel 2007 文件格式 Excel 2003 及以下版本文件格式 图 Excel 2007 与 2003 文件格式对比 2)Excel 2007 软件二进制文件格式 除了新的基于 XML 的文件格式,Excel 2007 软件还引入了用于大型或复杂工作簿的分段压缩文件格式的二进制版本 该文件格式, 即 Excel 2007 软件二进制 ( 或 BIFF12) 文件格式 (.xls), 可用于获得最佳性能和向后兼容性 3) 与 Excel 早期版本的兼容性 可以检查 Excel 2007 软件工作簿来查看它是否包含与 Excel 早期版本不兼容的功能或格式, 以便进行必要的更改来获得更好的向后兼容性 在 Excel 早期版本中, 可以安装更新和转换器来帮助打开 Excel 2007 软件工作簿, 这样就可以对其进行编辑 保存, 然后再次在 Excel 2007 软件中打开它而不会丢失任何 Excel 2007 软件特定的功能或特性 14. 更佳的打印体验除了 普通 视图和 分页预览 视图之外,Excel 2007 软件还提供了 页面 视图 用户可以使用该视图来创建工作表, 同时关注打印格式的显示效果 在该视图中, 可以使用位于工作表中右侧的页眉 页脚和边距设置, 以及将对象 ( 如图表或形状 ) 准确放置在所需的位置 在新的用户界面中, 还可轻松访问 页面布局 选项卡中的所有页面设置选项, 以便快速指定选项, 如页面方向 查看每页上要打印的内容也很方便, 这有助于避免多次打印尝试和在打印输出中出现截断的数据, 如图 所示 图 页面 视图

61 54 Excel 在财务管理中的应用 ( 第二版 ) 15. 共享工作的新方法 如果用户能够访问 Excel Services, 则可以使用它与其他人 ( 如组织中的管理人员和 其他利益相关人员 ) 共享用户的 Excel 2007 软件工作表数据 在 Excel 2007 软件中, 可 以将工作簿保存到 Excel Services, 并指定希望其他人查看的工作表数据 这样, 用户可 以在浏览器中解释 HTML 文件 将其格式设置为网页并显示它们的软件 Web 浏览器 ( 如 Internet Explorer) 可跟踪超链接 传输文件并播放嵌入网页中的音频和视频文件 ) 中使用 Excel Web Access 查看 分析 打印和提取这些工作表数据 用户也可以定期或 根据需要来创建静态的数据快照 使用 Excel Web Access 可以轻松地执行操作, 如滚动 筛选 排序 查看图表及在数据透视表中查看明细 用户也可以将 Excel Web Access Web 部件连接到其他 Web 部件, 以另一些可选方式显示数据 在具有适当权限的情况下, 安装了 Excel 的 Excel Web Access 用户可以在 Excel 2007 软件中打开工作簿, 以便使用 Excel 的完整功能在自己的计算机上分析和使用数据 使用此方法共享工作的用户可确 保其他人可以访问同一个位置中的同一个数据版本, 而用户只需使用最新的详细信息保持该版本是最新的 如果需要其他人 ( 如团队成员 ) 向用户提供批注和更新的信息, 则用户可能要像在 Excel 早期版本中那样共享工作簿以收集所需的信息, 然后将其保存到 Excel Services 使用文档管理服务器 Excel Services 可以与文档管理服务器集成, 以围绕新的 Excel 报表和工作簿计算工作流操作创建验证过程, 如基于单元格的通知或基于复杂 Excel 计算的工作流过程 也可以使用文档管理服务器安排在夜间重新计算复杂工作簿模型, 如图 文档管理图 所示 16. 快速访问更多模板 在 Excel 2007 软件中, 可以基于随 Excel 安装的多个模板创建新工作簿, 也可以在 Microsoft Online 网站上快速访问和下载模板 习题一 1.Excel 2007 与低版本 Excel 的区别有哪些? 2. 简述 Excel 2007 的文件 工作簿 工作表和单元格之间的关系 3.Excel 2007 输入的数据类型有哪几种? 4. 怎样才能显示出所有的工作表标签? 5.Excel 2007 对单元格的引用有哪几种公式? 6. 描述公式 =Sheet3!C2+Sheet4!C8+ 成绩单!A4 的含义 7. 不连续的表格区域的选择是如何操作的? 8. 单元格的清除与单元格的删除有什么不同?

62 第一章 Excel 2007 软件基础 如果一个工作表大于一页, 打印输出时要将它放在一页中, 该怎样操作? 10. 制作一份学生个人各门课程 ( 至少 5 门课程 ) 的学习成绩总评报告, 求学生成 绩的总分 平均分 术语积累 Excel 软件标题栏任务功能区工具栏选项卡 工作簿工作表单元格编辑栏填充柄 数据格式公式距数数组

63 Excel 在财务管理基础观念中的应用 第二章 第一节财务管理基础观念及 Excel 软件要点 一 财务管理基础观念要点 资金时间价值和风险价值是财务管理的两个基础观念, 两者的准确计量是财务管理中十分重要的问题 但是, 两者的计量如用手工完成是很烦琐的, 而借助 Excel 软件则可以大大简化相关计算, 提高计算的准确性和速度 ( 一 ) 资金时间价值的概念及其计算 1. 资金时间价值的概念 资金的时间价值是财务管理的基础观念之一, 其指资金在使用过程中随时间的推移而发生的价值增值 资金的时间价值是企业在生产经营过程中产生的, 其表现为一定量的资金在不同时间点上的价值量的差额 其中, 把现在一定量的资金在未来某一时间点上的价值称为资金的终值, 又称将来值, 俗称本利和 ; 而把未来某一时间点上一定量的资金折合为现在的价值称为资金的现值, 又称本金 2. 资金时间价值的计算 (1) 一次性收付款项现值与终值的计算一次性收付款项资金的时间价值的计算可以用单利法和复利法两种方法计算 在计算中, 经常使用以下符号 : P present value, 现值或本金 ; F future value, 将来值, 终值或本利和 ;

64 第二章 Excel 在财务管理基础观念中的应用 57 I interest, 利息 ; i interest rate, 利率 ; n 计算利息的期数 1) 单利终值和现值的计算 单利是指仅计算本金在使用年限中取得的利息的计息 方式 按照这种计息方式计算资金的时间价值, 不论时间长短均按本金计算时间价值, 以往各期产生的利息不计算时间价值 单利终值的计算公式 ( 已知现值 P, 求终值 F) 为 F n =P 0 (1+i n) 单利现值的计算公式 ( 已知终值 F, 求现值 P) 为 P 0 =F n (1+i n) 2) 复利终值与现值的计算 复利是指不仅计算本金在使用年限中取得的利息, 而且计算以往年度的利息继续按利率生息取得的利息 按照这种计息方式计算资金的时间价值, 不仅按本金计算时间价值, 以往各期产生的利息也计算时间价值 复利终值的计算公式 ( 已知现值 P, 求终值 F) 为 F n =P 0 (1+i) n 复利现值的计算公式 ( 已知终值 F, 求现值 P) 为 P 0 =F n (1+i) n (2) 年金终值与现值的计算年金是在一定时期内每次等额收付的系列款项 利息 租金 保险费 等额分期收款 等额分期付款及零存整取或整存零取等都表现为年金的形式 年金按其收付发生的时间点不同, 可分为普通年金 先付年金 递延年金 永续年金等几种 不同种类年金的终值与现值用以下不同的方法计算 年金一般用字母 A 表示 1) 普通年金的计算 普通年金是指一定时期内发生在每期期末的等额收付系列款项, 又称后付年金, 如图 2-1 所示 P F n 0 A A A A A 图 2-1 普通年金 1 普通年金终值的计算 ( 已知年金 A, 求年金终值 F) 普通年金终值是一定时期内每期期末收付款项的复利终值之和, 计算公式为 (+)- F=A 1 i n 1 i 2 普通年金现值的计算 ( 已知年金 A, 求年金现值 P) 普通年金现值是指一定时期内每期期末收付款项的复利现值之和, 计算公式为

65 58 Excel 在财务管理中的应用 ( 第二版 ) P=A 1 1 n -(+) i i 2) 先付年金的计算 先付年金是指一定时期内发生在每期期初的等额系列收付款项, 又称即付年金或预付年金, 如图 2-2 所示 P F n 0 A A A A A 图 2-2 先付年金 1 先付年金终值的计算 先付年金终值是指一定时期内每期期初等额收付款项的复利终值之和, 计算公式为 n+1 (+) 1 i -1 F=A i 2 先付年金现值的计算 先付年金现值是指一定时期内每期期初等额收付款项的复利现值之和, 计算公式为 1 (+) 1 i P= i -( n-) 1 3) 递延年金的计算 递延年金是指第一次收付款发生时间与第一期无关, 而是隔若干期 ( 假设为 m 期,m 1) 后才开始发生的系列等额收付款项 它是普通年金的特殊形式, 凡不是从第一期开始的普通年金就是递延年金, 如图 2-3 所示 无年金 m 年金发生期 + 1 A A A n 图 2-3 递延年金 1 递延年金终值的计算 : 递延年金终值的计算与普通年金类似, 前面没有发生收付款的时期不计算, 按后面发生收付款的时期期数和折现率计算终值 2 递延年金现值的计算 : 递延年金现值的计算有两种方法 一是将递延年金看成 n 期普通年金, 先求出第 m+1 期期初时的 n 期普通年金的现值, 然后再折算到第一期期初, 即得到 n 期递延年金的现值 ; 二是将递延年金看成 m+n 期普通年金, 先求出第 m+n 期普通年金的现值, 然后再减去 m 期普通年金的现值, 即得到 n 期递延年金的现值 4) 永续年金的计算 永续年金是指无限期等额收付的年金, 可视为普通年金的特殊形式 无限期债券的利息和优先股的股利都是永续年金的例子, 如图 2-4 所示

66 第二章 Excel 在财务管理基础观念中的应用 59 P n 0 A A A A A 图 2-4 永续年金 1 永续年金的终值计算 : 由于永续年金持续期无限, 没有终止的时间, 因此没有终值 2 永续年金现值的计算 : P=A/i (3) 资金时间价值计算的特殊问题 1) 年偿债基金的计算 ( 已知年金终值 F, 求年金 A) 偿债基金是指为了在约定的未来时间点清偿某笔债务或积蓄一定数量的资金而分次等额提取的存款准备金 由于每次提取的等额准备金类似于年金, 因而同样可以获得按复利计算的利息, 所以债务实际上等于年金终值, 计算公式为 i A=F 1 n (+)- i 1 2) 年资本回收额的计算 ( 已知年金现值 P, 求年金 A) 资本回收额是指在给定的年限内等额回收或清偿所欠债务 ( 或初始投入资本 ) 的金额 年资本回收额的计算是年金现值的逆运算, 计算公式为 i A=P 1 1 n -(+) i - 3) 名义利率与实际利率 以上计算均假定利率为年利率, 每年复利一次 但实际上, 复利的计息期间不一定是一年, 有可能是季度 月或日 例如, 某些债券半年计息一次 ; 有的抵押贷款每月计息一次 ; 银行之间拆借资金均为每天计息一次 当每年复利计息次数超过一次时, 给出的年利率叫做名义利率, 而每年只复利一次的利率叫做实际利率 把名义利率调整为实际利率的计算公式为 i=(1+r/m) m -1 式中 :i 实际利率 ; r 名义利率 ; m 每年复利次数 ( 二 ) 风险的概念及计量 1. 风险的概念风险一般是指某一行动的结果具有变动性 从财务管理角度来看, 风险是企业在各项生产经营活动过程中, 由于各种难以预料或无法控制的因素作用, 使企业的实际收益与预计收益发生背离, 从而蒙受经济损失的可能性

67 60 Excel 在财务管理中的应用 ( 第二版 ) 2. 风险的计量 (1) 期望值期望值是一个概率分布中所有可能结果以各自相应的概率为权数计算的加权平均值, 是加权平均的中心值, 通常用符号 E 来表示 期望值反映预计收益的平均化, 在各种不确定因素影响下, 它代表着投资的合理预期 E= x i p i 式中 :x i 各种可能结果的值 ; p i 各种可能结果出现的概率 (2) 标准离差标准离差表示未来收益偏离期望值的范围大小, 通常用符号 δ 表示 标准离差是以绝对数衡量决策方案的风险, 在期望值相同的情况下, 标准离差越大, 风险越大 ; 反之, 标准离差越小, 风险越小 δ= 2 xi E Pi ( - ) (3) 标准离差率标准离差率是标准离差同期望值之比, 通常用符号 q 表示 标准离差率是一个相对数指标, 标准离差率越大表示风险越大, 标准离差率越小表示风险越小 标准离差只适用于期望值相同的决策方案的风险比较, 而对于期望值不同的决策方案, 评价和比较其各自的风险程度只能借助于标准离差率这一相对指标 q=δ/e 100% 二 Excel 软件要点 ( 一 ) 终值函数 FV 终值函数 FV(rate,nper,pmt,pv,type) 可以在各期利率 收付款期总数及收付款时间 一次性收付款或年金的现值已知的情况下, 求出一次性收付款或年金的终值 终值函数中的各参数含义如下 : 1)rate: 各期利率 (i) 2)nper: 总投资 ( 或贷款 ) 期 (n), 即该项投资 ( 或贷款 ) 的收付款总期数 3)pmt: 各期所应支付的金额, 其数值在整个年金期间保持不变, 即年金 A 通常 pmt 包括本金和利息, 但不包括其他费用及税款 如果忽略 pmt, 则必须包括 pv 参数 4)pv: 现值, 即从该项投资开始计算时已经入账的款项, 或一系列未来付款的当前值的累积和, 也称为本金 (P) 如果省略 pv, 则假设其值为零, 并且必须包括 pmt 参数 5)type: 数字 0 或 1, 用以指定各期的付款时间是在期初还是期末 0 表示期末, 1 表示期初 如果省略 type, 则假设其值为零 终值函数输入的一般步骤如下 :

68 第二章 Excel 在财务管理基础观念中的应用 61 第一步, 打开 Excel 制表系统 第二步, 在 Excel 工作表中选择要输入函数的单元格 第三步, 单击 公式 选项卡, 再单击 插入函数 选项, 或者直接选择 财务 函数, 如图 2-5 所示 第四步, 在 财务 函数中显示该大类包括的所有函数, 从中选择我们当前需要输入的终值函数, 如图 2-5 所示 选择函数 图 2-5 选择终值函数 第五步, 单击所选的终值函数, 弹出 函数参数 对话框, 在该对话框中进行所选函数参数的输入, 如图 2-6 所示 参数输入 图 2-6 终值函数参数的输入

69 62 Excel 在财务管理中的应用 ( 第二版 ) 第六步, 参数输入完毕后, 单击 确定 按钮完成函数输入 注意 : 1 在所有参数中, 支出的款项如银行存款, 表示为负数 ; 收入的款项如股息收入, 表示为正数 例如, 对于储户来说,500 元银行存款表示为参数 500 元, 而对于银行来 说该参数为 500 元 2 应确认所指定的 rate 和 nper 单位的一致性 例如, 同样是五年期, 年利率为 10% 的贷款, 如果按年支付,rate 应为 10%,nper 为 5; 如果按月支付,rate 应为 10%/12( 将年利率转换为月利率 ),nper 应为 5*12( 将以年为期转换为以月为期 ) ( 二 ) 现值函数 PV 现值函数 PV(rate,nper,pmt,fv,type) 可以在各期利率 收付款期总数及收付 款时间 一次性收付款或年金的终值已知的情况下, 求出一次性收付款或年金的现值 现值函数中的各参数含义如下 : 1)rate: 各期利率 (i); 2)nper: 总投资 ( 或贷款 ) 期 (n), 即该项投资 ( 或贷款 ) 的收付款总期数 3)pmt: 各期所应支付的金额, 其数值在整个年金期间保持不变, 即年金 A 如果忽略 pmt, 则必须包含 fv 参数 4)fv: 终值, 或在最后一次支付后希望得到的现金余额, 如果省略 fv, 则假设其值为零 ( 一笔投资或贷款的未来值即为零 ) 如果忽略 fv, 则必须包含 pmt 参数 5)type: 数字 0 或 1, 用以指定各期的付款时间是在期初还是在期末 0 表示期末, 1 表示在期初 如果省略 type, 则假设其值为零 现值函数输入的一般步骤如下 : 第一步, 打开 Excel 制表系统 第二步, 在 Excel 工作表中选择要输入函数的单元格 第三步, 单击 公式 选项卡, 再单击 财务 函数选项 第四步, 从下拉组合框中选择现值函数, 如图 2-7 所示 第五步, 在弹出的 函数参数 对话框中进行所选函数参数的输入, 如图 2-8 所示 第六步, 参数选择输入完毕后单击 确定 按钮完成函数输入 注意 : 1 在所有参数中, 支出的款项如银行存款, 表示为负数 ; 收入的款项如股息收入, 表示为正数 例如, 对于储户来说,500 元银行存款可表示为参数 500 元, 而对于银行来说该参数为 500 元 2 应确认所指定的 rate 和 nper 单位的一致性 例如, 同样是五年期, 年利 率为 10% 的贷款, 如果按月支付,rate 应为 10%/12( 将年利率转换为月利率 ), nper 应为 5*12( 将以年为期转换为以月为期 ); 如果按年支付,rate 应为 10%,nper 为 5

70 第二章 Excel 在财务管理基础观念中的应用 63 图 2-7 选择现值函数 图 2-8 现值函数参数的输入 ( 三 ) 年金函数 PMT 年金函数 PMT(rate,nper,pv,fv,type) 可以在各期利率 收付款期总数及收付款时间 年金的终值或者现值已知的情况下, 求出年金的数额 年金函数中的各参数含义如下 :

71 64 Excel 在财务管理中的应用 ( 第二版 ) 1)rate: 各期利率 (i) 2)nper: 总投资 ( 或贷款 ) 期 (n), 即该项投资 ( 或贷款 ) 的收付款总期数 3)pv: 年金的现值 如果省略 pv, 则必须包含 fv 参数 4)fv: 年金的终值 如果省略 fv, 则必须包含 pv 参数 5)type: 数字 0 或 1, 用以指定各期的付款时间是在期初还是期末 0 表 示期末, 1 表示期初 如果省略 type, 则假设其值为零 ( 四 ) 名义利率函数 NOMINAL 名义利率函数 NOMINAL(effect_rate,npery) 可以把实际利率转换为名义利率 名义 利率函数中的各参数含义如下 : 1)effect_rate: 实际利率 2)npery: 每年的复利期数 ( 五 ) 实际利率函数 EFFECT 实际利率函数 EFFECT(nominal_rate,npery) 可以把名义利率转换为实际利率 实际 利率函数中的各参数含义如下 : 1)nominal_rate: 名义利率 2)npery: 每年的复利期数 ( 六 )SUMPRODUCT 函数 SUMPRODUCT(array1,array2,array3, ) 是在给定的几组数组中, 将数组间对应的 元素相乘, 并返回乘积之和的函数, 其与以数组形式输入的公式 SUM() 的计算结果相同 例如, SUMPRODUCT(B3:B7,E3:E7) 与 {SUM(B3:B7*E3:E7)} 的运算结果相同 array1,array2,array3, 为 1~30 个数组 注意 : 数组参数必须具有相同的维数, 否则, 函数 SUMPRODUCT() 将返回错误值 #VALUE! 函数 SUMPRODUCT() 将非数值型的数组元素作为 0 处理 ( 七 )SQRT 函数 SQRT(number) 函数的功能是计算数值的平方根, 其参数 number 表示开方的数值 在本章中,SQAT 函数主要用于计算标准差 第二节财务管理基础观念中 Excel 的应用 一 单利终值和现值的计算 1. 单利终值的计算 例 2-1 某人把 1000 元存入银行, 年利率为 3%, 按单利计算这笔资金在第一年

72 第二章 Excel 在财务管理基础观念中的应用 65 第二年 第三年年末的终值分别是多少? 思路 : 该问题为单利终值的计算问题, 可以通过单利终值的计算公式求解 步骤 : 第一步, 创建名称为 时间价值计算 的工作簿, 并在 时间价值计算 工作簿中创建名称为 单利终值的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-9 所示 该图中现值 利率 年限等值可以使用填充柄输入 第三步, 在单元格 B4 C4 D4 中分别输入终值计算公式, 如图 2-10 所示 P i n F n P i n F n 图 2-9 例 2-1 的基本信息 图 2-10 单利终值的计算结果 第三步可以采用以下两种简便方法来实现 : 1) 在单元格 B4 中输入单利终值公式 =B1*(1+B2*B3), 求出第一年的单利终值, 然后使用填充柄把单元格 B4 复制到单元格 C4 D4, 求出第二 三年的单利终值, 如图 2-11 所示 2) 按 Ctrl+Shift+Enter 组合键输入数组来实现 首先选择单元格 B4:D4, 然后按 = 键输入公式 B1:D1*(1+B2:D2*B3:D3), 再按 Ctrl+Shift+Enter 组合键确定数组输入, 如图 2-12 所示 填充柄 图 2-11 第二 三年的单利终值计算结果 图 2-12 使用组合键输入数组计算单利终值 单元格 B4 C4 D4 中的公式均为 {=B1:D1*(1+B2:D2*B3:D3)} 由以上操作可知, 某人把 1000 元存入银行, 在年利率为 3% 的情况下, 按单利计算, 在第一年年末可取得 1030 元, 第二年年末可取得 1060 元, 第三年年末可取得 1090 元的终值 2. 单利现值的计算 例 2-2 某人想在三年后从银行取出 1000 元, 年利率为 3%, 按单利计算, 现在应存入多少钱? 思路 : 该问题为单利现值的计算问题, 可以通过单利现值的计算公式求解 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 单利现值的计算 Excel 工作表

73 66 Excel 在财务管理中的应用 ( 第二版 ) 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-13 所示 第三步, 将单利现值计算公式输入单元格 B4 中, 系统自动计算出现值的金额, 如图 2-14 所示 F n i n P 图 2-13 例 2-2 的基本信息 图 2-14 单利现值的计算结果 F n i n P 由以上操作可知, 某人想在三年后从银行取出 1000 元, 年利率 3%, 按单利计算, 现在存入 917 元即可 二 复利终值和现值的计算 1. 复利终值的计算 例 2-3 某人把 1000 元存入银行, 年利率 3%, 按复利计算这笔资金在第一年 第二年 第三年年末的终值分别是多少? 思路 : 该问题为复利终值的计算问题, 可以使用以下两种方法解决 :1 通过输入复利终值的计算公式 F n =P (1+i) n 求解 ;2 利用终值函数求解 1) 通过输入复利终值的计算公式 F n =P (1+i) n 求解 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 复利终值计算 1 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-15 所示 其中, 现值 利率 年限可以使用填充柄输入 第三步, 将终值计算公式输入工作表内, 计算出第一 二 三年的复制终值, 如 图 2-16 所示 P i n F n P i n F n 填充柄 图 2-15 例 2-3 的基本信息 图 2-16 输入终值计算公式计算复利终值 在单元格 B4 中输入复利终值公式 =B1*(1+B2)^B3, 求出第一年的单利终值 ( ^ 键表示乘方 ), 然后使用填充柄复制单元格求出第二 三年的复利终值

74 第二章 Excel 在财务管理基础观念中的应用 67 也可以按 Ctrl+Shift+Enter 组合键输 入数组来实现此功能 首先选择单元格 B4:D4, 然后按 = 键输入公式 B1:D1*(1+B2:D2) B3:D3, 再按 Ctrl+Shift+Enter 组合键确定 数组输入 单元格 B4 C4 D4 中的公式均为 {=B1:D1*(1+B2:D2)B3:D3}, 如图 2-17 所示 由以上操作可知, 某人把 1000 元存入银行, 年利率 3%, 按复利计算在第一年 年末可取得 1030 元 第二年年末可取得 元 第三年年末可取得 元 的终值 图 2-18 选择输入函数的单元格 2) 通过终值函数求解 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称 为 复利终值的计算 2 Excel 工作表 第二步, 在 Excel 工作表中选择要输入函数的单元格, 如图 2-18 所示 第三步, 单击 公式 选项卡, 再单击 财务 函数选项, 系统弹出 财务 函数 下拉组合框, 单击终值函数 FV, 如图 2-19 所示 图 2-17 使用组合键输入数组计算复利终值 图 2-19 选择终值函数 FV

75 68 Excel 在财务管理中的应用 ( 第二版 ) 第四步, 根据终值函数形式, 分析例题中终值函数相关的参数, 并将参数的值填入 到参数相应的文本框内,pmt 即年金 A, 而本例未涉及, 可不填, 如图 2-20 所示 i n A P 此处因为是客户支付 1000 元给银行, 对于客户来说是 元 本例为一次收付款问题,Type 取值为 0 图 2-20 输入终值函数相关的参数 第五步, 参数选择输入完毕后单击 确定 按钮完成函数输入, 如图 2-21 所示 求得第一年年末的终值为 1030 元, 计算第一年年末的终值函数为图 2-21 第一年年末的终值 =FV(3%,1,, 1000,0) 第二年 第三年年末的终值可用相同方法求得, 分别为 FV(3%,2,, -1000,0)= 和 FV(3%,3,, -1000,0)= 由以上操作可知, 某人把 1000 元存入银行, 年利率 3%, 按复利计算在第一年年末 终值为 1030 元, 第二年年末终值为 元, 第三年年末终值为 元 本例中第二 三年年末的终值可以按 Ctrl+Shift+Enter 组合键输入数组公式来快速 实现, 操作步骤如下 : 第一步, 打开 Excel 制表系统 第二步, 将例 2-3 的信息输入工作表内, 如图 2-22 所示 图 2-22 输入基本信息 此处是客户支付 1000 元给银行, 对于客户 来说是 1000 元 第三步, 选择要插入函数的单元格 B4:D4, 然后插入终值函数并添加相应的参数, 注意参数由单期计算终值的 数值参数 变成了 数组参数, 如图 2-23 所示 第四步, 数组参数输入完毕后按 Ctrl+Shift+Enter 组合键确定数组输入, 数组终值函数输入完毕, 计算结果与前述方法相同, 如图 2-24 所示

76 第二章 Excel 在财务管理基础观念中的应用 69 图 2-23 添加数组参数 数组公式 图 2-24 数组公式计算结果 例 2-4 东方公司于 2011 年年初对 A 项目投资 100 万元, 该项目一年完工, 2012~2014 年各年末预计收益为 30 万元 50 万元及 70 万元, 年利率 4%, 以 2014 年年末为基准点评价该项目的可行性 思路 : 为评价该项目的可行性, 可以利用 Excel 软件对处于不同时间点的投资额及收益额分别计算 2014 年年末的终值, 再进行比较 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 东方公司 1 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-25 所示 图 2-25 例 2-4 的基本信息 第三步, 将需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 2-26 所示 图 2-26 以 2009 年年末为基准点评价项目的结果

77 70 Excel 在财务管理中的应用 ( 第二版 ) 1 在单元格 B6 中插入终值函数并添加相应的参数 2 把单元格 B6 复制到单元格 C6:E6 中 3 在单元格 F6 中输入公式 =SUM(B6:E6) 通过以上操作可知, 以 2014 年年末为基准点, 该项目投资额及收益额的终值之和大于零 因此, 该项目可行 例 2-5 利用 Excel 制作年利率为 1%~10%, 计息期数为 1~15 年的复利终值系数表 思路 : 借助 Excel 软件强大的运算功能, 复利终值系数表可以通过输入复利终值的计算公式 F n =P (1+i) n (P=1) 轻松获得 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 复利终值系数表 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-27 所示 图 2-27 例 2-5 的基本信息 该图中年利率 年限可以使用填充柄输入, 在使用填充柄添加年利率时需将 1%~ 图 2-28 步长值为 % 的单元格设为 百分比 格式, 然后用填充柄 添加序列, 序列的步长值设为 0.01( 可以试验使用 默认步长后的效果 ), 如图 2-28 所示 第三步, 将需要的参数变量信息及公式输入 Excel 工作表内求值 首先选择要求计算复利终值系数的区域, 将该 区域内的所有单元格格式设为数值并保留 4 位小 数, 如图 2-29 所示

78 第二章 Excel 在财务管理基础观念中的应用 71 图 2-29 选择的区域 然后, 在编辑栏中输入复利终值系数计算公式 =(1+B2:K2)^A3:A17, 按 Ctrl+ Shift+Enter 组合键形成数组公式, 如图 2-30 所示 数组公式 图 2-30 复利终值表数表 通过以上操作, 年利率为 1%~10%, 计息期数为 1~15 年的复利终值系数表显示在 Excel 工作表中 2. 复利现值的计算 例 2-6 某人想在三年后从银行取出 1000 元, 年利率 3%, 按复利计算, 现在应存

79 72 Excel 在财务管理中的应用 ( 第二版 ) 入多少钱? 思路 : 该问题为复利现值的计算问题, 可以使用以下两种方法解决 :(1) 通过输入复利现值的计算公式 P=F /(1+i) n 求解 ;(2) 利用现值函数求解 1) 通过输入复利现值的计算公式 P=F /(1+i) n 求解 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 复利现值的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-31 所示 第三步, 将复利现值计算公式输入单元格 B4 中, 系统自动计算出现值的金额, 如图 2-32 所示 F n i n P F n i n P 图 2-31 例 2-6 的基本信息 图 2-32 复利现值的计算结果 由以上述操作可知, 某人三年后从银行取出 1000 元, 在年利率为 3% 的情况下, 按 复利计算, 现在存入 915 元即可 2) 通过现值函数求解 分析例题的信息, 并罗列出来, 参考复利终值函数的步骤完成计算, 如图 2-33 所示 函数所需参数值分别为 : i=3%; n=3; F=1000; A 年金, 本例不涉及, 可不填 i n A F 此处是客户从银行得到 1000 元, 对于客户来说是 此处是客户支付 元现值给银行, 对于客户来说是 图 2-33 复利现值函数求解结果

80 第二章 Excel 在财务管理基础观念中的应用 73 通过以上操作可知, 某人想在三年后从银行取出 1000 元, 在年利率为 3% 的情况下, 按复利计算, 现在应存入 元 例 2-7 资料同例 2-4, 以 2011 年年初为基准点评价该项目的可行性 思路 : 为评价该项目的可行性, 也可以利用 Excel 软件对处于不同时间点的投资额及收益额分别计算 2011 年年初的现值, 再进行比较 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 东方公司 2 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-34 所示 图 2-34 例 2-7 的基本信息 第三步, 将需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 2-35 所示 图 2-35 以 2011 年年初为基准点评价项目的结果 1 在单元格 B6 中插入现值函数并添加相应的参数 2 把单元格 B6 复制到 C6:E6 中 3 在单元格 F6 中输入公式 =SUM(B6:E6) 通过以上操作可知, 以 2011 年年初为基准点, 该项目投资额及收益额的现值之和为 万元 因此, 该项目可行 例 2-8 利用 Excel 软件制作年利率为 1%~10%, 计息期数为 1~15 年的复利现值系数表 思路 : 借助 Excel 软件强大的运算功能, 复利现值系数表可以通过输入复利现值的计算公式 P=F/(1+i) n (F=1) 轻松获得 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 复利现值系数表 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-36 所示 图 2-36 中年利率 年限同样可以使用填充柄输入, 在使用填充柄添加年利率 1%~ 10% 时同参考复利终值系数表的有关操作

81 74 Excel 在财务管理中的应用 ( 第二版 ) 图 2-36 例 2-8 的基本信息 第三步, 将复利现值系数计算公式输入工作表内计算, 操作同复利终值系数计算表 在编辑栏中输入复利现值系数计算公式 =1/((1+B2:K2)^A3:A17), 按 Ctrl+Shift+Enter 组合键形成数组公式, 如图 2-37 所示 数组公式 图 2-37 复利现值系数表 通过以上操作, 年利率为 1%~10%, 计息期数为 1~15 年的复利现值系数表显示 在 Excel 工作表中 三 年金终值和现值的计算 1. 普通年金终值和现值的计算 (1) 普通年金终值的计算 例 2-9 东方公司新增一台设备并立即投入使用, 该设备使用年限为 8 年, 预计每 年年末均可获利 6 万元, 年利率 10%, 计算该设备所获利润的终值

82 第二章 Excel 在财务管理基础观念中的应用 75 思路 : 该问题为普通年金终值的计算问题, 可以使用以下两种方法解决 :1 通过输入年金终值计算公式求解 ;2 通过终值函数求解 1) 通过输入年金终值计算公式求解 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 普通年金终值的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-38 所示 第三步, 将普通年金终值计算公式输入单元格 B4 中, 系统自动计算出普通年金终值的金额, 如图 2-39 所示 A i n F A i n F 图 2-38 例 2-9 的基本信息 图 2-39 普通年金终值的计算结果 由以上操作可知, 该设备所获利润的终值为 元 2) 通过终值函数求解 分析例题的信息, 并罗列出来, 参考复利终值函数的步骤完成计算, 如图 2-40 所示 本例是计算普通年金的终值, 其付款期在每期期末 思考 : 这里为什么获利了还是负数? 图 2-40 普通年金的终值函数求解结果 东方公司每年年末获利 元, 只有将这 元存入银行才能享受到 10% 的利率 若计算年金, 东方公司这 元获利就变成了支付给银行的资金, 所以以负数形式出现 由以上操作可知, 该设备所获利润的终值为 FV(10%,8,60 000,,0)= 元 例 2-10 利用 Excel 制作年利率为 1%~10%, 计息期数为 1~15 年的年金终值系数表

83 76 Excel 在财务管理中的应用 ( 第二版 ) 思路 : 借助 Excel 软件强大的运算功能, 年金终值系数表可以通过输入年金终值的计算公式轻松获得 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 年金终值系数表 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 将年金终值系数计算公式输入工作表内, 如图 2-41 所示 数组公式 图 2-41 年金终值系数表 在编辑栏中输入年金终值系数计算公式 =(((1+B2:K2)^A3:A17) 1)/B2:K2, 按 Ctrl+Shift+Enter 组合键形成数组公式 通过以上操作, 年利率为 1%~10%, 计息期数为 1~15 年的年金终值系数表显示 在 Excel 工作表中 (2) 普通年金现值的计算 例 2-11 资料同例 2-9, 计算该设备所获利润的现值 思路 : 该问题为普通年金现值的计算问题, 可以使用以下两种方法解决 :1 通过输 入年金现值计算公式求解 ;2 通过现值函数求解 下面以输入年金现值计算公式求解为例说明操作过程 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 普通年金现值的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-42 所示 第三步, 将普通年金现值计算公式输入单元格 B4 中, 系统自动计算出普通年金现 值的金额, 如图 2-43 所示

84 第二章 Excel 在财务管理基础观念中的应用 77 A i n P 图 2-42 例 2-11 的基本信息 图 2-43 普通年金现值的计算结果 由以上操作可知, 该设备所获利润的现值为 万元 例 2-12 利用 Excel 制作年利率为 1%~10%, 计息期数为 1~15 年的年金现值系数表 思路 : 借助 Excel 软件强大的运算功能, 年金现值系数表可以通过输入年金现值的计算公式轻松获得 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 年金现值系数表 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 在编辑栏中输入年金现值系数计算公式 =(1-(1+B2:K2)^-A3:A17)/ B2:K2), 按 Ctrl+Shift+Enter 组合键形成数组公式, 如图 2-44 所示 数组公式 图 2-44 年金现值系数表通过以上操作, 年利率为 1%~10%, 计息期数为 1~15 年的年金现值系数表显示在 Excel 工作表中 2. 先付年金终值和现值的计算 (1) 先付年金终值的计算 例 2-13 东方公司拟购置一处房产, 从现在起, 每年年初支付 10 万元, 连续支付 10 年, 年利率为 10%, 计算该房产的终值

85 78 Excel 在财务管理中的应用 ( 第二版 ) 思路 : 该问题为先付年金终值的计算问题, 可以使用以下两种方法解决 :1 通过输入先付年金终值计算公式求解 ;2 利用终值函数求解 下面主要介绍第二种方法的操作 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 先付年金终值计算 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 参考复利终值函数的步骤完成计算, 如图 2-45 所示 本例是计算先付年金的终值, 其付款期在每期期初,type=1 终值函数 先付年金的支付方式为期初支付,Type 取值为 1 图 2-45 先付年金终值的计算结果 通过以上述操作可知, 该房产的终值为 FV(10%,10, 10,,1)= 万元 (2) 先付年金现值的计算 例 2-14 资料同例 2-13, 计算该房产的现值 思路 : 可以使用以下两种方法解决 :1 通过输入先付年金现值计算公式求解 ;2 利用现值函数求解 下面主要介绍第二种方法的操作 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 先付年金现值计算 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 参考复利现值函数的步骤题目完成计算, 如图 2-46 所示 本例是计算先付年金的终值, 其付款期在每期期初,type=1 通过以上操作可知, 该房产的现值为 PV(10%,10, 10,,1)=67.59 万元.

86 第二章 Excel 在财务管理基础观念中的应用 79 现值函数 图 2-46 先付年金现值的计算结果 3. 递延年金终值和现值的计算 例 2-15 东方公司购置的房产, 还可以从第五年起, 每年初支付 15 万元, 连续支付 10 次, 年利率为 10%, 试与例 2-14 比较哪个付款方案更好 思路 : 该问题为递延年金现值的计算问题, 可以通过以下两种方法解决 :1 将递延年金看成 n 期普通年金, 先求出第 m+1 期期初时的 n 期普通年金的现值, 然后再折算到第一期期初, 即得到 n 期递延年金的现值 ;2 将递延年金看成 m+n 期普通年金, 先求出第 m+n 期普通年金的现值, 然后再减去 m 期普通年金的现值, 即得到 n 期递延年金的现值 下面主要介绍第一种方法的操作 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 递延年金现值的计算 1 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 参考复利现值函数的步骤完成计算, 求出第 m+1 期期初时的 n 期先付年金的现值, 如图 2-47 所示 图 2-47 第 m+1 期期初时的 n 期先付年金现值

87 80 Excel 在财务管理中的应用 ( 第二版 ) m=4;n=10;i=10%;a n = ;type=1, 本例是计算先付年金的现值, 其付款期在每期期初 第四步, 参考复利现值函数的步骤完成计算, 求出第一期期初时的 n 期先付年金的现值, 如图 2-48 所示 通过以上操作可知, 例 2-14 的付款方案只需要投入 万元即可达到要求, 而例 2-15 的付款方案则需要投入 万元才能达到要求, 故例 2-14 的方案更优 图 2-48 第一期期初时的 n 期先付年金现值 4. 永续年金终值和现值的计算 例 2-16 某大学拟建立一项永久性奖学金, 每年计划发放 10 万元, 若年利率为 4%, 现在应存入多少钱? 思路 : 该问题为已知永续年金求其现值, 利用永续年金现值的公式 P=A/i 即可求出 图 2-49 永续年金现值的计算结果 四 资金时间价值计算的特殊问题 1. 偿债基金 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 永续年金现值的计算 Excel 工作表 第二步, 在单元格 B4 中输入公式 =B2/B3, 求 得永续年金现值的结果为 元, 如图 2-49 所示 例 2-17 东方公司预计 2016 年年末有一笔 300 万元的债务到期需偿还, 该公司计 划从 2006 年起每年年末等额存入一笔款项, 如果年利率为 8%, 则每年存款额应为多少 才能满足上述偿债需要? 求解 作表 思路 : 该问题为偿债基金的计算, 即已知年金终值求年金问题, 可以使用年金函数 步骤 : 第一步, 在 时间价值计算 工作簿中创建名称为 偿债基金的计算 Excel 工

88 第二章 Excel 在财务管理基础观念中的应用 81 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 利用年金函数完成计算, 如图 2-50 所示 i=8%;n=10;fv=-300;type=0, 其付款期在每期期末 年金函数 图 2-50 偿债基金的计算结果 通过以上操作可知,PMT(8%,10,, 300)=20.71 万元, 即每年应存款 万元才能满足上述偿债需要 2. 资本回收额 例 2-18 东方公司计划于本年年初投资 50 万元, 购买一台设备, 并当即投入使用, 预计使用期为 8 年 如果年利率为 10%, 问此设备每年年均需获利多少元, 该项投资才是可行的? 思路 : 该问题为资本回收额的计算, 即已知现值求年金问题, 可以使用年金函数 PMT 求解 步骤 : 第一步, 打开 Excel 软件 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 利用年金函数完成计算, 如图 2-51 所示 i=10%;n=8;fv= 50;type=0, 其付款期在每期期末 通过以上操作可知,PMT(10%,8, 50)=9.37 万元, 即此设备每年年均需获利 9.37 万元, 该项投资才是可行的 五 风险的计量 例 2-19 东方公司有 A B 两个投资项目, 计划投资额相同, 其收益 ( 净现值 ) 的概率分布如表 2-1 所示

89 82 Excel 在财务管理中的应用 ( 第二版 ) 图 2-51 资本回收额的计算结果 表 2-1 东方公司 A B 两项目的净现值及其概率 单位 : 万元 市场状况概率 A 项目净现值 B 项目净现值 好 一般 差 要求 : 1) 分别计算 A B 两个项目净现值的期望值 2) 分别计算 A B 两个项目期望值的标准离差 3) 分别计算 A B 两个项目期望值的标准离差率 思路 : 该问题为风险的计量问题, 可以使用公式或 SUMPRODUCT 函数 SQAT 函数等求解 步骤 : 第一步, 创建名称为 风险的计量 的工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 2-52 所示 第三步, 将需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 2-53 所示 图 2-52 例 2-19 的基本信息 图 2-53 风险的计量计算结果 1 在单元格中 B8 输入公式 =SUMPRODUCT($B$3: $B$5,C3:C5), 然后把单元格 B8 复制到单元格 C8, 求出 A B 两个项目净现值的期望值

90 第二章 Excel 在财务管理基础观念中的应用 83 2 在单元格 B9 中输入公式 =SUMPRODUCT($B$3:$B$5,(C3:C5-B8)^2), 然后把单元格 B9 复制到单元格 C9, 求出 A B 两个项目净现值的方差 3 在单元格 B10 中输入公式 =SQRT(B9), 然后把单元格 B10 复制到单元格 C10, 求出 A B 两个项目净现值的标准离差 4 在单元格 B11 中输入公式 =B10/ B8, 然后把单元格 B11 复制到单元格 C11, 求出 A B 两个项目净现值的标准离差率 ( 注意 : 把单元格 B11:C11 设置为百分比格式 ) 通过以上操作可知,A 项目收益的期望值较小, 风险程度较低, 而 B 项目收益的期望值较大, 风险程度较高 习题二 1. 利用 Excel 软件制作年利率为 1%~20%, 计息期数为 1~60 年的复利终值系数表 2. 利用 Excel 软件制作年利率为 1%~20%, 计息期数为 1~60 年的复利现值系数表 3. 利用 Excel 软件计算, 在利率为 10% 时, 按复利计算, 五年后的 元的现值是多少? 4. 资料同例 2-4, 分别以 2011 年年末 2012 年年末 2013 年年末为基准点, 利用 Excel 软件评价该项目的可行性 5. 某投资项目于 2011 年年初动工, 设当年投产, 从投产之日起每年可获得收益 元, 按年利率 6% 计算, 利用 Excel 软件计算预期 10 年收益的现值 6. 某人在六年内分期付款购房, 每年年初付款 元, 银行利率为 10%, 利用 Excel 软件计算该项分期付款的现值相当于现在一次性现金支付的现值 7. 假设某公司准备在今后五年内, 每年年末从利润留成中提取 元存入银行, 在五年后, 将这一笔存款用于建造某一福利设备, 如果以年息 4% 计算, 利用 Excel 软件计算五年后共可以积累多少资金 8. 甲公司 2011 年年初对 A 设备投资 10 万元, 该项目 2013 年年初完工投产,2013~ 2015 年各年末预期收益分别为 2 万元 3 万元 5 万元, 同期银行存款利率为 10% 要求 : 利用 Excel 软件计算 1) 按复利计算 2013 年年初投资额的终值 2) 计算各年预期收益折成 2013 年年初时的现值之和 3) 评价该投资项目的可行性 9.A 公司采用融资租赁方式于 2014 年年初租入一台设备, 价款 元, 租期四年, 年利率为 8% 要求 : 利用 Excel 软件计算 1) 计算每年年末应支付的租金

91 84 Excel 在财务管理中的应用 ( 第二版 ) 2) 计算每年年初应支付的租金 3) 分析二者的关系 10. 某项贷款年利率 8%, 每半年复利一次, 利用 Excel 软件计算该笔贷款的实际利率 11. 某企业有 A B 两个投资项目, 计划投资额均为 1000 万元, 其收益 ( 净现值 ) 的概率分布如表 2-2 所示 表 2-2 某企业 A B 两项目的净现值及其概率 单位 : 万元 市场状况概率 A 项目净现值 B 项目净现值 好 一般 差 要求 : 利用 Excel 软件计算 1) 计算 A B 两个项目净现值的期望值 2) 计算 A B 两个项目期望值的标准离差 3) 计算 A B 两个项目期望值的标准离差率 术语积累 单利 复利 终值 现值 年金 普通年金 先付年金 递延年金 永续年金 实际利率 名义利率 资本回收额 偿债基金 风险 期望值 标准离差 标准离差率 数组输入 终值函数 现值函数 年金函数 实际利率函数名义利率函数 SUMPRODUCT 函数 SQRT 函数

92 Excel 在筹资管理中的应用 第三章 第一节筹资管理及 Excel 软件要点 一 筹资管理要点筹集资金是企业根据其生产经营 对外投资 调整资本结构等活动的需要, 通过一定的渠道, 采取适当的方式, 获取所需资金的一种行为 筹资管理是财务管理的重要内容之一 ( 一 ) 确定筹资规模确定筹资规模是对企业未来某一时期内的资金需要量进行科学的预计和判断 筹资规模的确定可以采用销售百分比法和资金习性预测法等方法 1. 销售百分比法销售百分比法是在企业未来销售预测已经完成且未来时期相关项目与销售收入之间的比率关系不变的前提下, 以企业基期的资产负债表和有关销售资料为基础, 利用资金与销售收入之间的比率关系, 来预测企业在未来时期销售变动情况下的筹资量的方法 其步骤如下 : 1) 收集整理企业基期期末资产负债表及销售情况等有关资料, 并估计该企业未来的销售变动情况 2) 将该企业基期资产负债表中预计随销售收入同比例变动的相关项目分离出来 3) 分别计算基期相关项目与基期销售收入的百分比, 即相关资产占基期销售收入的百分比 相关负债占基期销售收入的百分比

93 86 Excel 在财务管理中的应用 ( 第二版 ) 第三章 Excel 在筹资管理中的应用 4) 确定该企业未来时期每百元销售收入需要多少资金 资金需要量与销售收入的百分比 = 相关资产占销售收入百分比 5) 确定该企业未来时期的筹资总额 - 相关负债占销售收入百分比 未来时期的筹资总额 = 销售增加额 未来时期资金需要量的百分比 6) 确定该企业未来时期的外部筹资额 2. 资金习性预测法 外部筹资额 = 未来时期的筹资总额 - 企业内部资金来源数额 资金习性预测法是在企业未来销售预测已经完成且可以将资金占用量按其习性划分为不 变资金和变动资金的前提条件下, 根据历史各期的销售和占用资金量资料, 利用 总资金 = 不变资金 + 单位变动资金 业务量 模型, 来预测未来销量下的资金需要量的方法 其步骤如下 : 1) 收集整理企业历史各期的销售资料和占用资金量资料 2) 根据历史资料, 使用高低点法或回归直线法分解该企业的资金占用量, 求出不变资金 单位变动资金 3) 利用 总资金 = 不变资金 + 单位变动资金 业务量 模型, 预测未来销量下的资金需要量 ( 二 ) 权益资金的筹集 权益资金的筹集可以使用吸收直接投资 发行股票 ( 普通股 优先股 ) 利用留存收益等方式 对于企业而言, 筹集权益资金的优点是有利于增强企业实力, 有利于降低财务风险等 ; 缺点是资金成本较高, 容易分散企业控制权等 各类权益资金的资金成本计算公式如下 : 优先股年股利优先股资金成本 = 100% 优先股筹资总额 (- 1 筹资费率 ) 预期年股利额普通股资金成本 = + 普通股利年增长率普通股筹资额 (- 1 筹资费率 ) 预期年股利额留存收益资金成本 = + 股利年增长率留存收益额 = 普通股第一年股利率 + 股利年增长率 ( 三 ) 负债资金的筹集 企业负债资金的筹集可以使用银行借款 发行公司债券 融资租赁等方式 对于筹资企业而言, 筹集负债资金的优点是资金成本较低, 不分散企业控制权等 ; 缺点是风险较大, 限制条件多等 1. 银行借款 (1) 长期借款的还本付息长期借款金额大, 期限长, 一般使用等额偿还法对其还款做出预先安排

94 第三章 Excel 在筹资管理中的应用 87 (2) 银行借款的资金成本年利息 ( 1- 所得税率 ) 长期借款成本 = 100% 借款总额 ( 1- 筹资费率 ) 2. 发行债券 (1) 债券发行价格的确定 债券的发行价格是投资者购买债券之后所获得的全部现金流量按期望报酬率作为 贴现率所计算的总现值 债券发行时的市场利率 债券的还本付息方式等因素均影响债券的发行价格 (2) 债券的资金成本年利息 ( 1- 所得税率 ) 债券的资金成本 = 100% 发行额 ( 1- 筹资费率 ) 3. 融资租赁 融资租赁是区别于经营租赁的一种长期租赁形式, 由于它可满足企业对资产的长期需要, 有时也称为资本租赁 在我国融资租赁业务中, 一般使用等额年金法计算租金 等额年金法是利用时间价值原理, 以设备价款为现值, 以利率 手续费率的合计为贴现率, 求租金 ( 四 ) 综合资金成本 企业筹集的各类资金的综合资金成本可以采用加权平均法来计算 综合资金成本 = ( 个别筹资方式资金成本 该方式资金占企业总资金的比例 ) ( 五 ) 杠杆作用分析 财务管理中的杠杆作用有经营杠杆 财务杠杆和复合杠杆三种形式 其中, 经营杠杆是由于经营性固定成本的存在而导致息税前利润 (EBIT) 变动幅度大于产销业务量变动幅度的杠杆效应 ; 财务杠杆是由于财务性固定成本即利息和优先股股利等的存在, 净利润 ( 每股利润 ) 的变动幅度大于息税前利润的变动幅度的杠杆效应 ; 复合杠杆也叫总杠杆, 是由于经营性固定成本和财务性固定成本的共同存在而导致的每股利润的变动幅度大于产销业务量变动幅度的杠杆效应 经营杠杆 财务杠杆和复合杠杆的杠杆效应分别用经营杠杆系数 财务杠杆系数和复合杠杆系数来衡量 息税前利润变动率基期边际贡献经营杠杆系数 = = 产销量变动率基期息税前利润普通股每股利润的变动率息税前利润财务杠杆系数 = = 息税前利润变动率息税前利润 - 利息复合杠杆系数 = 经营杠杆系数 财务杠杆系数

95 88 Excel 在财务管理中的应用 ( 第二版 ) ( 六 ) 资金结构 资金结构是指企业各种资金来源的构成及其比例关系, 即债务和所有者权益之间的 比例关系 资金结构问题是企业筹资决策的核心问题 最优资金结构的决策可以使用比较资金成本法和比较每股利润法等方法 其中, 比较资金成本法是分别计算不同资金结构下的加权平均资金成本, 选择其中的最低者作为最优资金结构 ; 比较每股利润法即息税前利润 每股利润分析法 (EPS), 是指在息税前利润一定的前提下, 分别计算不同资金结构下每股利润的大小, 其中每股利润大者为最优资金结构 二 Excel 软件要点 ( 一 )MAX 函数 MAX(number1,number2, ) 函数的功能是给定一组数值中的最大值, 其忽略逻辑值及文本字符 例如,MAX(1,5,13,25)=25 MAX 函数在本章中主要用于高低点法中高点的确定 ( 二 )MIN 函数 MIN(number1,number2, ) 函数的功能是给定一组数值中的最小值, 其忽略逻辑值及文本字符 例如,MIN(1,5,13,25)=1 MIN 函数在本章中主要用于高低点法中低点的确定 ( 三 )INDEX 函数 INDEX 函数是引用函数, 其返回表格或区域中的数值或对数值的引用 INDEX 函数有多种格式, 在本章中使用的格式如下 : INDEX(array,row_num,column_num) INDEX 函数中的各参数含义如下 : 1)array: 指定的单元格区域或数组常量 ; 2)row_num: 数组或引用中要返回的行序号, 如果忽略, 则必须有 column_num 参数 3)column_num: 数组或引用中要返回的列序号, 如果忽略, 则必须有 row_num 参数 在本章中,INDEX 函数与 MATCH 函数相配合, 主要用于高低点法 ( 四 )MATCH 函数 MATCH 函数是查找函数, 其返回在指定方式下与指定数值匹配的数组中的元素的相应位置 MATCH 函数的格式如下 : MATCH(lookup_value,lookup_array,match_type) MATCH 函数中的各参数含义如下 : 1)lookup_value: 在数组中所要查找的匹配的值 2)lookup_array: 所要查找的连续的单元格区域

96 第三章 Excel 在筹资管理中的应用 89 3)match_type: 与 lookup_value 和 lookup_array 中数值进行匹配的方式 在本章中,MATCH 函数与 INDEX 函数相配合, 主要用于高低点法 ( 五 )SLOPE 函数 SLOPE 函数的功能是计算经过给定数据点的线性回归拟合方程的的斜率 SLOPE 函数的格式如下 : SLOPE(known_y's,known_x's) SLOPE 函数中的各参数含义如下 : 1)known_y's: 因变量数组或数值区域 2)known_x's: 自变量数组或数值区域 SLOPE 函数在本章中主要用于回归直线法 ( 六 )INTERCEPT 函数 INTERCEPT 函数的功能是计算线性回归拟合方程的的截距 INTERCEPT 函数的格式如下 : INTERCEPT(known_y's,known_x's) INTERCEPT 函数中的各参数含义如下 : 1)known_y's: 因变量数据点 2)known_x's: 自变量数据点 INTERCEPT 函数在本章中主要用于回归直线法 ( 七 )PMT 函数 PMT 函数的功能是计算在固定利率条件下, 投资或贷款的等额分期偿还额 PMT 函数的格式如下 : PMT(rate,nper,pv,fv,type) PMT 函数中的各参数含义如下 : 1)rate: 各期利率 2)nper: 总投资期或贷款期, 即该项投资或贷款的收付款期总数 3)pv: 现值, 即从该项投资开始计算时已经入账的款项, 或一系列未来付款当前值的累积和, 也称为本金 4)fv: 终值, 或在最后一次付款后希望得到的现金余额 如果省略 fv, 则假设其值为零, 也就是一笔贷款的未来值为零 5)type: 数字 0 或 1, 用以指定各期的付款时间是在期初还是期末 如果为 1, 付款在期初, 如果为 0 或忽略, 付款在期末 PMT 函数在本章中主要用于长期借款的还款计划 ( 八 )IPMT 函数 IPMT 函数的功能是计算给定期次内某项投资回报 ( 或贷款偿还 ) 的利息部分 IPMT

97 90 Excel 在财务管理中的应用 ( 第二版 ) 函数的格式如下 : IPMT(rate,per,nper,pv,fv,type) IPMT 函数中的各参数含义如下 : 1)rate: 各期利率 2)per: 用于计算其利息数额的期数, 必须在 1 到 nper 之间 3)nper: 总投资期或贷款期, 即该项投资或贷款的付款期总数 4)pv: 现值, 即从该项投资开始计算时已经入账的款项, 或一系列未来付款的当前值的累积和, 也称为本金 5)fv: 终值, 或在最后一次付款后希望得到的现金余额 如果省略 fv, 则假设其值为零 6)type: 数字 0 或 1, 用以指定各期的付款时间是在期初还是期末 如果省略 type, 则假设其值为零 IPMT 函数在本章中主要用于长期借款的还款计划 ( 九 )PPMT 函数 PPMT 函数的功能是计算给定期次内某项投资回报 ( 或贷款偿还 ) 的本金部分 PPMT 函数的格式如下 : PPMT(rate,per,nper,pv,fv,type) PPMT 函数中的各参数含义如下 : 1)rate: 各期利率 2)per: 用于计算其本金数额的期数, 必须介于 1 到 nper 之间 3)nper: 总投资期或贷款期, 即该项投资或贷款的付款期总数 4)pv: 现值, 即从该项投资开始计算时已经入账的款项, 或一系列未来付款当前值的累积和, 也称为本金 5)fv: 终值, 或在最后一次付款后希望得到的现金余额 如果省略 fv, 则假设其 值为零, 也就是一笔贷款的未来值为零 6)type: 数字 0 或 1, 用以指定各期的付款时间是在期初还是期末 PPMT 函数在本章中主要用于长期借款的还款计划 一 确定筹资规模 1. 销售百分比法 第二节筹资管理中 Excel 的应用 例 3-1 东方公司 2011 年的销售额为 元, 生产能力还有剩余, 销售净利率为 10%, 股利支付率为 40% 预计 2012 年的销售额为 元, 销售净利率和股利支付率不变, 该公司 2011 年 12 月 31 日的资产负债表如表 3-1 所示

98 第三章 Excel 在筹资管理中的应用 91 表 3-1 东方公司简要资产负债 (2011 年 12 月 31 日 ) 单位 : 万元 资产 负债及所有者权益 货币资金 应付票据 应收账款 应付账款 存货 长期借款 固定资产 ( 净值 ) 实收资本 无形资产 留存收益 总计 总计 要求 : 利用 Excel 软件预测该公司 2012 年的外部筹资需求量 思路 : 该问题为根据企业的历史资料, 利用销售百分比法确定筹资规模问题, 可以通过在 Excel 工作表中输入销售百分比法的相关公式解决 步骤 : 第一步, 创建名称为 筹资管理 的工作簿, 并在 筹资管理 工作簿中创建名称为 销售百分比法 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-1 所示 图 3-1 例 3-1 的基本信息 第三步, 将销售百分比法需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-2 所示 1 在单元格 C15 中输入公式 =C4/$C$22, 然后将单元格 C15 分别复制到单元格

99 92 Excel 在财务管理中的应用 ( 第二版 ) C16:C17 和 E4:E5, 求得相关项目与基期销售额之间的百分比 图 3-2 销售百分比的计算结果 2 在单元格 C20 中输入公式 =SUM(C15:C17), 然后在单元格 E20 中输入公式 =SUM(E15:E16), 求得相关项目与基期销售额之间的百分比的合计数 3 在单元格 C24 中输入公式 =C23 C22, 求得销售增加额 4 在单元格 C27 中输入公式 =C24*(C20 E20), 求得预测期筹资总额 5 在单元格 C28 中输入公式 =C22*C25*C26, 求得内部资金来源数额 6 在单元格 C29 中输入公式 =C27 C28, 求得预测期外部筹资额 通过以上操作可知, 东方公司 2007 年的外部筹资需求量为 4000 元 2. 资金习性预测法 (1) 高低点法 例 3-2 东方公司产销量和资金占用量连续四年的变化情况如表 3-2 所示,2012 年预计产销量为 150 万件, 利用 Excel 软件预测该公司 2012 年的资金需要量 表 3-2 产销量和资金占用量的变化情况 年度产销量 x/ 万件资金占用量 y/ 万元年度产销量 x/ 万件资金占用量 y/ 万元 思路 : 该问题为根据企业的历史资料, 利用高低点法确定筹资规模问题, 可以使用函数

100 第三章 Excel 在筹资管理中的应用 93 MAX(number1,number2, ) MIN(number1,number2, ) 和 INDEX( ) 等求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 高低点法 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-3 所示 图 3-3 例 3-2 的基本信息 第三步, 将高低点法需要的参数变量信息及公式输入 Excel 工作表内求值 1 最高产销量及最低产销量的公式输入, 如图 3-4 所示 输入函数 =MAX(B2:B5) 输入公式 =INDEX(C2:C5,MATCH(B7,B2:B5)) 输入函数 =MIN(B2:B5) 输入公式 =INDEX(C2:C5,MATCH(B8,B2:B5)) 图 3-4 最高及最低产销量的计算结果 2 求出 Δy Δx 的值, 如图 3-5 所示 Δy=C7-C8, 高点 低点的资金占用量之差 Δx=B7-B8, 高点 低点的产销量之差 图 3-5 高点 低点的资金占用量及产销量之差 3 求出常量 a b 的值, 如图 3-6 所示 常量 a=y 高 -b 高 x 或 a=y 低 -b 低 x

101 94 Excel 在财务管理中的应用 ( 第二版 ) Δy 常量 b = Δ x 输入公式 =B9/B10 输入公式 =C8 B11*B8 图 3-6 高低点法求出 a b 的值 年预计产销量为 150 万件,2012 年的资金需要量的计算公式为 y=a+bx, 将公式输入 Excel 工作表中, 如图 3-7 所示 图 3-7 高低点法求解资金需要的预测值 输入公式 =B12+B11*150 通过以上操作可知, 该公司 2012 年在预计产销量为 150 万件的情况下, 需要资金 115 万元 (2) 回归直线法 例 3-3 资料如例 3-2, 利用 Excel 软件计算 2012 年的资金需要量 思路 : 该问题为根据企业的历史资料, 利用回归直线法确定筹资规模问题, 可以使用函数 SLOPE(known_y's,known_x's) 和 INTERCEPT(known_y's,known_x's) 求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 回归直线法 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的图 3-8 例 3-3 的基本信息基本信息, 如图 3-8 所示

102 第三章 Excel 在筹资管理中的应用 95 第三步, 将回归直线法需要的参数变量信息及公式输入 Excel 工作表内求值 1 求出常量 a b 的值, 如图 3-9 所示 2 求出 2012 年资金需要量的预测值, 如图 3-10 所示 输入公式 =SLOPE(C2:C5,B2:B5) 输入公式 =INTERCEPT(C2:C5,B2:B5) 图 3-9 回归直线法求出 a b 的值 输入公式 =B8+B7*150 图 3-10 回归直线法求解资金需要的预测值 通过以上操作可知, 该公司 2012 年在预计产销量为 150 万件的情况下, 需要资金 115 万元 二 权益资金的筹集 1. 普通股的资金成本 例 3-4 东方公司发行面值为 4 元的普通股 100 万股, 筹资总额为 400 万元, 筹资 费率为 5%, 第一年每股股利为 0.4 元, 以后每年按 3% 的比率增长, 利用 Excel 软件计 算该普通股的资金成本 作表 思路 : 该问题为普通股资金成本的计算问题, 可以通过普通股资金成本公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 普通股资金成本计算 的 Excel 工 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-11 所示 第三步, 将需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-12 所示 在单元格 B6 中输入普通股资金成本公式 =(B3/(B2*(1 B4)))+B5 进行求值

103 96 Excel 在财务管理中的应用 ( 第二版 ) 输入公式 =0.4*100 图 3-11 例 3-4 的基本信息 输入公式 =(B3/(B2*(1 B4)))+B5 图 3-12 普通股资金成本的计算结果通过以上操作可知, 该普通股的资金成本是 13.53% 2. 优先股的资金成本 例 3-5 东方公司发行面值总额为 200 万元的优先股, 每年支付 10% 的股利, 筹 资费率为 4% 试利用 Excel 软件计算该优先股的资金成本 作表 思路 : 该问题为优先股资金成本的计算问题, 可以通过优先股资金成本公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 优先股的资金成本计算 Excel 工 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-13 所示 输入公式 =200*10% 图 3-13 例 3-5 的基本信息 第三步, 将需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-14 所示 在 B5 中输入优先股资金成本公式 =(B3/(B2*(1 B4)))*100% 进行求值

104 第三章 Excel 在筹资管理中的应用 97 输入公式 =(B3/(B2*(1 B4)))*100% 图 3-14 优先股资金成本的计算结果通过以上操作可知, 该优先股的资金成本是 10.42% 3. 留存收益的资金成本 例 3-6 东方公司留存收益 200 万元, 第一年普通股股利为 11%, 预计以后每年按 3% 的比率增长 试利用 Excel 软件计算留存收益的资金成本 思路 : 该问题为留存收益资金成本的计算问题, 可以通过留存收益资金成本的公式求解 步骤 : 第一步, 在 筹资管理 工作题目簿中创建名称为 留存收益资金成本的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-15 所示 输入公式 =200*11% 图 3-15 例 3-6 的基本信息 第三步, 在单元格 B5 中输入留存收益资金成本公式 =(B3/B2)+B4 进行求值, 如 图 3-16 所示 输入公式 =(B3/B2)+B4 图 3-16 留存收益的资金成本计算结果 通过以上操作可知, 该留存收益的资金成本是 14% 使用 留存收益的资金成本 = 普通股第一年股利率 + 股利年增长率 也会得出相同

105 98 Excel 在财务管理中的应用 ( 第二版 ) 的值 上机实践时操作此公式, 观察结果是否相同 三 负债资金的筹集 1. 银行借款 (1) 长期借款的还本付息 长期借款金额大, 期限长, 应对其还款做出预先安排 下面以等额偿还法为例说明 如何安排长期借款的还本付息 例 3-7 东方公司向银行借入了一笔为期 10 年 金额为 300 万元的贷款, 年利率 为 8%, 与银行约定以等额偿付方式偿还贷款 试计算该贷款的每年偿还额 每年的本 金及利息 思路 : 该问题为长期借款的还本付息问题, 在等额偿还法操作中, 可以首先使用年 金函数 PMT( 该函数的具体使用方法参照第二章 ) 计算出各期的等额偿还额, 其次用 IPMT 函数计算出各期的付息额, 最后用 PPMT 函数计算出各期的还本额 步骤 : 工作表 第一步, 在 筹资管理 工作簿中创建称名称为 长期借款还本付息表 的 Excel 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-17 所示 图 3-17 例 3-7 的基本信息 第三步, 把长期借款的还本付息等额偿还法需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-18 所示 1 在单元格 B9:B18 中输入年金函数公式 =PMT(B4,B3, B2), 求得第 1~10 年 每年的偿还额 2 在单元格 C9:C18 中输入 IPMT 函数公式 =IPMT(B4,A9:A18,B3, B2), 求得第 1~10 年每年支付的利息额

106 第三章 Excel 在筹资管理中的应用 99 输入数组公式 {=PPMT (B4,A9:A18,B3, B2)} 输入数组公式 {=E8:E17 D9:D18} 输入数组公式 {=PMT(B4,B3, B2)} 输入数组公式 {=IPMT(B4,A9:A18,B3, B2)} 图 3-18 长期借款的还本付息计算结果 3 在单元格 D9:D18 中输入 PPMT 函数公式 =PPMT(B4,A9:A18,B3, B2), 求得 第 1~10 年每年偿还的本金额 4 在单元格 E9:E18 中输入数组公式 ={E8:E7 D9:D18}, 求得第 1~10 年每年的 剩余本金额 (2) 长期借款的资金成本 例 3-8 东方公司向银行借入了一笔 4 年期 金额为 300 万元的贷款, 年利率为 8%, 每年付息一次, 到期还本, 筹资费率为 0.6%, 所得税率为 33% 试计算该借款 成本 思路 : 该问题为长期借款资金成本的计算问题, 可以 通过长期借款资金成本公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 长期 借款的资金成本 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如 图 3-19 所示 图 3-19 例 3-8 的基本信息第三步, 在单元格 B7 中输入长期借款资金成本公式 =(((B2*B4)*(1 B6))/(B2*(1 B5)))*100% 进行求值, 如图 3-20 所示 年利息 ( 1- 所得税率 ) 使用 长期借款成本 = 100% 也会得出相同的值 上机 1- 筹资费率 实践时操作此公式, 观察结果是否相同

107 100 Excel 在财务管理中的应用 ( 第二版 ) =(((B2*B4)*(1 B6))/(B2*(1 B5)))*100% 输入公式 =(((B2*B4)*(1 B6)) / (B2*(1 B5)))*100% 2. 发行债券 (1) 债券发行价格的确定 图 3-20 长期借款的资金成本计算结果 1) 债券发行时的市场利率对债券发行价格的影响 例 3-9 东方公司发行面值为 100 元, 利率为 5%, 期限为 8 年, 每年年末付息, 到期一次还本的债券 试利用 Excel 工作软件计算当发行时市场利率分别为 3% 5% 和 8% 该债券的发行价格 思路 : 该问题为债券发行价格的计算问题, 可以使用现值函数 PV 求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 债券发行价格 1 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-21 所示 第三步, 将债券票面价格计算需要的参数变量信息及公式输入 Excel 工作表内求值 在单元格 B6:D6 中输入数组公式 =PV(B5:D5,B4, B2*B3, B2), 并按 Ctrl+Shift +Enter 组合键结束数组输入, 如图 3-22 所示 图 3-21 例 3-9 的基本信息 输入数组公式 {=PV(B5:D5,B4, B2*B3, B2)} 图 3-22 债劵发行价格的计算结果 通过以上操作可知, 东方公司发行面值为 100 元的债券, 当市场利率为 3% 时的发 行价格为 元 ; 市场利率为 5% 时的发行价格为 100 元 ; 市场利率为 8% 时的发行 价格为 元 2) 债券的还本付息方式对债券发行价格的影响 例 3-10 资料如例 3-9, 试计算当该债券半年付息一次时的发行价格 思路 : 该问题为付息期发生改变后债券发行价格的计算问题, 仍可以通过现值函数 PV 求解

108 第三章 Excel 在筹资管理中的应用 101 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 债券发行价格 2 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息 第三步, 将债券票面价格计算需要的参数变量信息及公式输入 Excel 工作表内 求值 在单元格 B6:D6 中输入数组公式 =PV(B5:D5/2,B4*2, B2*B3/2, B2), 并按 Ctrl+ Shift+Enter 组合键结束数组输入, 如图 3-23 所示 输入数组公式 {=PV(B5:D5/2,B4*2, B2*B3/2, B2)} 图 3-23 付息期发生改变后债券发行价格的计算 通过以上操作可知, 东方公司发行面值为 100 元, 半年付息一次的债券, 当市场利率为 3% 时的发行价格为 元 ; 市场利率为 5% 时的发行价格为 100 元 ; 市场利率为 8% 时的发行价格为 元 (2) 债券的资金成本 1) 面值发行时, 债券资金成本的计算 例 3-11 东方公司按面值发行 1000 万元的票面利率为 9%, 期限为 6 年, 每年年末付息, 到期一次还本的债券, 筹资费率为 3%, 所得税率为 33% 试计算该债券成本 思路 : 该问题为债券资金成本的计算问题, 可以通过债券资金成本公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 面值发行的债券资金成本 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-24 所示 图 3-24 例 3-11 的基本信息第三步, 将面值发行时, 债券资金成本计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-25 所示 通过以上操作可知, 东方公司按面值发行 1000 万元的债券的资金成本为 6.22% 2) 非面值发行时, 债券资金成本的计算 例 3-12 东方公司发行面值为 1000 万元, 票面利率为 9%, 期限为 6 年, 每年年末付息, 到期一次还本的债券 发行价格为 1100 万元, 筹资费率为 3%, 所得税率为 33%, 试计算该债券成本

109 102 Excel 在财务管理中的应用 ( 第二版 ) 输入公式 =(((B2*B4)*(1 B6)) / (B2*(1 B5)))*100% 图 3-25 面值发行时债劵资金成本的计算结果思路 : 该问题为非面值发行的债券资金成本的计算问题, 仍可以通过债券资金成本公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 非面值发行的债券资金成本 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-26 所示 图 3-26 例 3-12 的基本信息第三步, 将非面值发行时, 债券资金成本计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-27 所示 输入公式 (((B2*B5)*(1 B7)) / (B3*(1 B6)))*100% 图 3-27 非面值发行时债劵资金成本的计算结果 通过以上操作可知, 东方公司溢价发行债券的资金成本为 5.65% 3. 融资租赁 (1) 期末支付的租金计算 例 3-13 东方公司于 2012 年 1 月 1 日向租赁公司租入设备一台, 价值 100 万元, 租期为 6 年, 利息与手续费等综合费率为 12% 试计算当该公司分别于每年末 每季末 每月末支付租金时应支付的金额 思路 : 该问题为融资租赁租金支付的计算问题, 可以利用年金函数 PMT 求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 融资租赁租金的计算 1 Excel 工作表

110 第三章 Excel 在筹资管理中的应用 103 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-28 所示 图 3-28 例 3-13 的基本信息 第三步, 将计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-29 所示 输入数组公式 {=PMT(B5:D5/B6:D6,B4:D4*B6:D6, B3:D3,,0)} 图 3-29 融资租赁租金的计算结果 1 在单元格 B7:D7 中输入数组公式 =PMT(B5:D5/B6:D6,B4:D4*B6:D6, B3:D3,,0), 按 Ctrl+Shift+Enter 组合键结束数组输入 通过以上操作可知, 东方公司融资租入的这台设备, 如果每年末支付租金, 租金为 万元 ; 每季末支付租金, 租金为 5.9 万元 ; 每月末支付租金, 租金为 1.96 万元 (2) 期初支付的租金计算 例 3-14 资料如例 3-13, 试分别计算该公司于每年初 每季初 每月初应支付的租金 思路 : 该问题为支付时间为期初的融资租赁租金支付计算问题, 仍可以年金函数 PMT 求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 融资租赁租金的计算 2 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基 本信息, 如图 3-30 所示 第三步, 将计算需要的参数变量信息及公 式输入 Excel 工作表内求值, 如图 3-31 所示 在单元格 B7:D7 中输入数组公式 =PMT (B5:D5/B6:D6,B4:D4*B6:D6, B3:D3,,1), 按 Ctrl+Shift+Enter 组合键结束数组输入 图 3-30 例 3-14 的基本信息

111 104 Excel 在财务管理中的应用 ( 第二版 ) 输入数组公式 {=pmt(b5:d5/b6:d6,b4:d4*b6:d6,-b3:d3,,1)} 图 3-31 融资租赁租金的计算结果 2 注意 : 因为付款时间改为期初, 本例与例 3-16 的区别在于 type 的值选 1 通过以上操作可知, 东方公司融资租入的这台设备, 如果每年初支付租金, 租金为 万元 ; 每季初支付租金, 租金为 5.73 万元 ; 每月初支付租金, 租金为 1.94 万元 四 杠杆作用分析 1. 经营杠杆的计量 例 3-15 东方公司有关资料如表 3-3 所示, 试利用 Excel 软件计算该公司的经营 杠杆系数 表 年和 2012 年东方公司的项目资料 项目 2011 年 2012 年变动率 销售单价 销售量 % 销售额 % 单位变动成本 变动成本 % 固定成本 息税前利润 % 利息 税前利润 % 所得税 % 净利润 % 普通股股数 每股利润 % 思路 : 该问题为经营杠杆系数的计算问题, 可以通过经营杠杆计算公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 经营杠杆系数的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-32 所示 第三步, 将计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-33 所示 1 在单元格 B16 和 C16 中分别输入公式 =B4 B6 和 =C4 C6, 求得 2011 年 2012 年的边际贡献额

112 第三章 Excel 在筹资管理中的应用 在单元格 D16 中输入公式 =C16 B16, 求得 2011 年 2012 年的边际贡献额的变动额 3 在单元格 B17 和 C17 中分别输入公式 =B16/B8 和 =C16/C8, 求得 2011 年 2012 年的经营杠杆系数 图 3-32 例 3-15 的基本信息 图 3-33 经营杠杆系数计算结果 通过以上操作可知, 该公司 2011 年 2012 年的经营杠杆系数分别为 1.67 和 财务杠杆的计量 例 3-16 资料如例 3-15, 试利用 Excel 软件计算该公司的财务杠杆系数 思路 : 该问题为财务杠杆系数的计算问题, 可以通过财务杠杆计算公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 财务杠杆系数的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-34 所示 第三步, 将计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-35 所示 图 3-34 例 3-16 的基本信息 图 3-35 财务杠杆系数的计算结果

113 106 Excel 在财务管理中的应用 ( 第二版 ) 在单元格 B17 和 C17 中分别输入公式 =B8/(B8 B9) 和 =C8/(C8 C9), 求得 2011 年 2012 年的财务杠杆系数 通过以上操作可知, 该公司 2011 年 2012 年的财务杠杆系数分别为 1.50 和 复合杠杆的计量 例 3-17 资料如例 3-15, 试利用 Excel 软件计算该公司的复合杠杆系数 思路 : 该问题为复合杠杆系数的计算问题, 可以通过复合杠杆计算公式求解 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 复合杠杆系数的计算 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-36 所示 第三步, 将计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-37 所示 图 3-36 输入例 3-17 的基本信息 图 3-37 复合杠杆系数的计算结果 在单元格 B19 和 C19 中分别输入公式 =B16/(B8 B9) 和 =C16/(C8 C9), 求得 2011 年 2012 年的复合杠杆系数 通过以上操作可知, 该公司 2011 年 2012 年的复合杠杆系数分别为 2.50 和 五 资本结构的决策方法 1. 比较资金成本法 例 3-18 东方公司拟筹集资金 500 万元, 现有三种筹资方案可供选择 方案甲为负债筹资 200 万, 发行股票筹资 300 万 ; 方案乙为负债筹资与发行股票筹资各 250 万 ; 方案丙为负债筹资 300 万, 发行股票筹资 200 万 负债资金成本为 8%, 发行股票的资金成本为 11%, 要求通过比较资金成本, 利用 Excel 软件选择该公司的最优资金结构 思路 : 该问题为运用比较资金成本法选择最佳资本结构的问题, 可以通过加权计算

114 第三章 Excel 在筹资管理中的应用 107 每一个备选方案的综合资金成本, 找出综合资金成本最低的方案作为最佳资本结构 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 比较资金成本法 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-38 所示 图 3-38 例 3-18 的基本信息 第三步, 将计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-39 所示 图 3-39 比较资金成本法的计算结果 1 在单元格 B6 中输入公式 =B4*C4+B5*C5, 求得方案 A 的综合资金成本 2 在单元格 D6 中输入公式 =D4*E4+D5*E5, 求得方案 B 的综合资金成本 3 在单元格 F6 中输入公式 =F4*G4+F5*G5, 求得方案 C 的综合资金成本 通过以上操作可知, 丙方案的综合资金成本最低, 即丙方案为最佳资本结构 2. 比较每股利润法 例 3-19 东方公司原有资金 800 万元, 其中, 债务资金 300 万元, 利率为 6%; 普通股 25 万股, 每股面值 20 元 因业务扩大, 需增加资金 200 万元, 现有以下两种筹资方案可供选择 : 1) 发行五年期长期债券, 债券利率为 8% 2) 增发 8 万股普通股, 每股发行价格为 25 元 该公司所得税率 33%, 预计增资后息税前利润为 150 万元 要求通过比较每股利润, 利用 Excel 软件选择该公司的最优资金结构 思路 : 该问题为运用比较每股利润法选择最佳资本结构的问题, 可以通过计算每一个备选方案的每股利润, 找出每股利润最大的方案作为最佳资本结构 步骤 : 第一步, 在 筹资管理 工作簿中创建名称为 比较每股利润法 的 Excel 工作表 第二步, 在 Excel 工作表中输入题目的基本信息, 如图 3-40 所示 第三步, 将计算需要的参数变量信息及公式输入 Excel 工作表内求值, 如图 3-41 所示

115 108 Excel 在财务管理中的应用 ( 第二版 ) 图 3-40 例 3-19 的基本信息 图 3-41 比较每股利润法的计算结果 1 在单元格 F10 中输入公式 =((B8 B4*C4 B10*C10)*(1 B6))/D5, 求得筹资方 案 1 的每股利润为 元 / 股 2 在单元格 F11 中输入公式 =((B8 B4*C4)*(1 B6))/(D5+D11), 求得筹资方案 2 的每股利润为 2.68 元 / 股 通过以上操作可知, 方案 1 获得的每股利润要多于方案 2, 故选用方案 1 做新的筹 资方案 习题三 1.A 公司 2011 年的销售额为 元, 这是该公司最大生产能力, 销售净利 率为 4%, 股利支付率为 40%, 预计 2012 年的销售额为 元, 销售净利率和股 利支付率不变 该公司 2011 年 12 月 31 日的资产负债表如表 3-4 所示 表 3-4 资产负债表 (2011 年 12 月 31 日 ) 资产负债及所有者权益 货币资金 应付账款 应收账款 存货 长期借款 固定资产 ( 净值 ) 实收资本 无形资产 留存收益 总计 总计