Microsoft Word - 新建 Microsoft Word Document

Transcription

1

2 普通高等教育经管类专业 十二五 规划教材 Excel 在会计和财务中的应用 ( 第四版 ) 崔杰姬昂崔婕穆乐福编著 北 京

3 内 容 简 介 本书结合 2007 年新会计准则 从 Excel 2010 的基础知识入手 以企业的会计核算流程为主 线 采用循序渐进的讲解方法 由浅入深地介绍如何利用 Excel 解决企业会计核算和财务分析等 问题 本书主要包括 Excel 概述 Excel 基础知识 Excel 高级应用 Excel 在会计凭证中的应用 Excel 在会计账簿中的应用 Excel 在会计报表中的应用 Excel 会计核算案例 Excel 在工资核算 中的应用 Excel 在固定资产管理中的应用 Excel 在财务分析中的应用以及 Excel 在财务管理中 的应用等内容 本书中运用大量实例对 Excel 的各种应用进行了详细介绍 本书内容翔实 结构清晰 图文并茂 通俗易懂 既突出基础性内容 又重视实践性应用 具有很强的实用性和可操作性 易学易懂 每章都穿插了大量极富实用价值的示例 并对重要的 知识和应用技巧进行了归纳和总结 每章末尾都安排了有针对性的思考练习 以便读者巩固所学 的基本概念和知识 同时可以培养实际应用能力 本书可作为高等院校信息系统专业 信息管理 专业 会计电算化专业及相关专业的教材 还可作为 Excel 会计应用的培训教材 也是广大 Excel 使用者不可多得的一本参考书 本书的教学课件 实例源文件和习题答案可以到 网站下载 本书封面贴有清华大学出版社防伪标签 无标签者不得销售 版权所有 侵权必究 侵权举报电话 图书在版编目(CIP)数据 Excel 在会计和财务中的应用/崔杰等 编著. 4 版. 北京 清华大学出版社 (普通高等教育经管类专业 十二五 规划教材) ISBN Ⅰ. ①E Ⅱ. ①崔 Ⅲ. ①表处理软件 应用 会计 高等学校 教材 ②表处理软件 应 用 财务管理 高等学校 教材 Ⅳ. ①F232 ②F 中国版本图书馆 CIP 数据核字(2013)第 号 责任编辑 胡辰浩 马玉萍 封面设计 周周设计局 版式设计 牛艳敏 责任校对 曹 阳 责任印制 出版发行 清华大学出版社 网 址 地 址 北京清华大学学研大厦 A 座 邮 编 社 总 机 邮 购 投稿与读者服务 c-service@tup.tsinghua.edu.cn 质 量 反 馈 zhiliang@tup.tsinghua.edu.cn 课 件 下 载 印 刷 者 装 订 者 经 销 全国新华书店 开 本 185mm 260mm 印 张 19.5 字 数 474 千字 版 次 2006 年 4 月第 1 版 2013 年 10 月第 4 版 印 次 2013 年 10 月第 1 次印刷 印 数 定 价 元 产品编号

4 前 言 Excel 是 Office 系列软件中用于创建和维护电子表格的应用软件, 它不仅具有强大的制表和绘图功能, 而且还内置了数学 财务 统计和工程等多种函数, 同时也提供了数据管理与分析等多种方法和工具 通过它, 用户可以进行各种数据处理 统计分析和辅助决策操作, 因此其被广泛地运用于会计 财务和管理等工作中 Excel 2010 以其全新的界面 更简洁的操作等优势, 逐渐成为 Excel 应用最广泛的版本 本书以图文并茂的方式, 结合大量实例和详尽的操作步骤说明, 全面地向读者介绍了 Excel 2010 在会计 财务工作中的具体运用 全书共分两大部分 : 第一部分由浅入深地介绍了 Excel 2010 的基础知识 ; 第二部分介绍了如何运用 Excel 2010 进行账务核算 财务分析以及企业的日常管理等 本书共有 11 章, 各章的主要内容如下 第 1 至 2 章的内容主要让读者了解 Excel 的工作界面, 掌握单元格和工作表编辑的各种操作方法, 掌握公式 函数等功能在 Excel 中的应用 ; 第 3 章的内容是在读者掌握基本知识的基础上, 介绍 Excel 数据管理与分析 Excel 的图表以及 Excel 图形与艺术字等高级功能的使用, 使读者对 Excel 的掌握再上一个新台阶 ; 第 4 至 6 章结合会计工作的账务处理程序, 分别讲解如何利用 Excel 编制会计凭证 登记账簿和编制会计报表等 ; 第 7 章介绍了运用 Excel 进行会计核算的综合案例, 运用第 4 至 6 章所学的知识, 进行企业的完整日常会计账务处理, 使读者对使用 Excel 进行会计核算有更深入 更全面的了解 ; 第 8 章介绍 Excel 在工资账务处理流程中的应用, 主要包括工资数据的查询 汇总分析等 ; 第 9 章介绍 Excel 管理固定资产的运用, 主要包括如何计算固定资产的累计折旧 账面价值等 ; 第 10 章介绍 Excel 针对编制完成的财务会计报表进行财务分析的应用, 主要包括运用 Excel 对企业财务报表进行比率分析 趋势分析 比较分析和综合财务分析等 ; 第 11 章介绍 Excel 在财务管理投资方面的应用 本书实例丰富 针对性强, 既可作为高等院校信息系统专业 信息管理专业 会计电算化专业及相关专业的教材, 又可作为 Excel 会计应用的培训教材, 也是广大 Excel 使用者不可多得的一本参考书 本书是多人智慧的结晶 作者都是从事多年教学工作并有丰富实践经验的老师 本书由崔杰担任主编, 姬昂 崔婕担任副主编 具体编写分工如下 : 刘翠编写第 1 至 2 章 ; 何保国编写第 4 至 5 章 ; 崔婕编写第 6 章 ; 姬昂编写第 7 章 ; 李新颖编写第 9 至 10 章 ; 崔杰编写第 章 除以上作者外, 本书编纂工作还得到了高光辉 董帅 穆乐福 付强 张玉英 王勇 罗峰 孙建伟 高红军 杨凯 张小林 牛静 任丽丽 周友文和王平等人的支持与帮助, 在此特向他们表示感谢 在本书的编写过程中, 参考了一些相关著作和文献, 在此向这些著作文献的作者深表感谢 由于作者水平有限, 本书难免有不足之处, 欢迎广大读者批评指正 我们的信箱是 huchenhao@263.net, 电话是 : 作者 2013 年 8 月

5 目 录 第 1 章 Excel 概述 Excel 2010 的工作界面 Excel 2010 的新功能 快速访问工具栏 标题栏 功能区 启动器 按钮 名称框与编辑栏 工作表编辑区 工作表 标签 状态栏 水平 垂直滚动条 自定义 Excel 设置屏幕显示 设置默认值 自定义快速访问工具栏 本章小结 思考练习 第 2 章 Excel 进阶 基本操作 Excel 的操作对象 创建工作簿 打开工作簿 保存工作簿 数据的输入 数据快速填充 编辑工作表 修饰工作表 打印工作表 公式 公式概述 公式的基本操作 公式的引用 公式的错误与审核 数组计算 函数 函数概述 常见的函数 本章小结 思考练习...79 第 3 章 Excel 高级应用 数据管理与分析 建立数据列表 数据排序 数据筛选 分类汇总数据 数据透视表 图表 图表的概述 创建图表 改变图表类型 编辑图表 迷你图表 三维图表 本章小结 思考练习 第 4 章 Excel 在会计凭证中的应用 会计凭证概述...112

6 目 录 会计凭证的含义及作用 会计凭证的类型 建立和处理会计科目表 建立会计科目表 设置记录单 修改和删除会计科目 美化会计科目表 建立会计凭证表 设计会计凭证表 自动生成会计凭证编号 自动显示会计科目 数据筛选 本章小结 思考练习 第 5 章 Excel 在会计账簿中的应用 会计账簿概述 会计账簿的意义与作用 会计账簿的类型 日记账 设置日记账格式 借贷不平衡自动提示 分类账 设置总分类账格式 建立总分类账 修改总分类账版面 显示单一科目分类账 自动更新数据透视表 科目汇总表 科目汇总表概述 建立科目汇总表 科目余额表 设计科目余额表 编制科目余额表 本章小结 思考练习 III 第 6 章 Excel 在会计报表中的应用 会计报表概述 会计报表的含义及作用 会计报表的分类 Excel 在资产负债表 中的应用 设置资产负债表格式 资产负债表的编制 Excel 在利润表中的应用 设置利润表格式 利润表的编制 Excel 在现金流量表 中的应用 设置现金流量表格式 现金流量表的编制 本章小结 思考练习 第 7 章 Excel 会计核算 会计核算概述 手工记账会计循环流程 Excel 记账会计循环流程 使用 Excel 进行会计 核算案例 企业资料概况 使用 Excel 进行会计 核算的准备工作 使用 Excel 进行会计核算 使用 Excel 编制会计报表 本章小结 思考练习 第 8 章 Excel 在工资核算中的应用 制作员工工资表 背景资料...204

7 Excel 在会计和财务中的应用 ( 第四版 ) 基本工资项目和数据的 输入 工资项目的设置 岗位工资 项目的设置 住房补贴 项目的设置 奖金 项目的设置 应发合计 项目的设置 事假扣款 项目的设置 病假扣款 项目的设置 扣款合计 项目的设置 养老保险 医疗 保险 项目的设置 应扣社保合计 项目的 设置 应发工资 项目的 设置 代扣税 项目的设置 实发合计 项目的 设置 工资数据的查询与汇总分析 利用筛选功能进行工资 数据的查询 利用 VLOOKUP 函数进行 工资数据的查询 依据部门和员工类别的 统计分析 工资发放条 生成工资发放条 打印工资发放条 本章小结 思考练习 第 9 章 Excel 在固定资产管理中的应用 固定资产概述 固定资产的概念 对固定资产进行单独 管理的必要性 固定资产卡片账的管理 固定资产卡片账的建立 固定资产卡片账的查询 固定资产折旧 固定资产折旧的计提 固定资产折旧函数 折旧函数应用举例 固定资产计提折旧的 具体应用 本章小结 思考练习 第 10 章 Excel 在财务分析中的应用 财务分析概述 财务分析目的 财务报表分析的方法 财务分析的数据来源 Excel 在财务比率分析中的 应用 财务比率分析的 具体指标 利用 Excel 计算和分析 财务比率 财务状况的趋势分析 趋势分析的具体方法 Excel 在财务状况趋势 10.4 企业间财务状况的 分析中的应用 比较分析 财务状况比较分析的 具体方法 Excel 在企业间财务状况 比较分析中的应用 IV

8 目 录 10.5 财务状况综合分析 财务状况综合分析的 具体方法 Excel 在企业财务状况 综合分析中的应用 本章小结 思考练习 第 11 章 Excel 在财务管理的基本应用 货币时间价值的计量 货币时间价值概述 运用 Excel 计算时间 价值指标 时间价值运用 长期债券决策分析 资本成本的计量 资本成本概述 个别资本成本的计量 综合资本成本的计量 项目投资评价基本指标 项目投资评价指标概述 动态评价指标的计量 项目投资决策模型 本章小结 思考练习 参考文献 V

9 第 1 章 Excel 概述 随着计算机对人类社会的全方位渗透, 面向各行各业的计算机应用软件应运而生 其中电子报表软件因给人们提供了一种高效的数据通信 组织 管理和分析工具, 而备受众人瞩目 Excel 是微软公司 Office 办公系列软件中的电子表格处理软件 它是目前市场上最强大的电子表格制作软件, 和 Word PowerPoint 和 Access 等软件一起, 构成了 Office 办公软件的完整体系 它不仅具有强大的数据组织 计算 分析和统计功能, 而且可以通过图表 图形等多种形式处理结果形象地显示出来, 还能够方便地与 Office 其他软件互相调用数据, 并能够通过 Internet 功能实现资源共享 对于未使用过 Excel 的用户来说, 第一次使用 Excel 时, 会因不熟悉它的界面 菜单栏和工具栏而不知所措 本章的主要目的便是帮助 Excel 初学者建立对 Excel 的感性认识 Excel 2010 是微软公司于 2010 年推出的 Office 办公软件中的一个组件, 相比之前推出的 Excel 2003 和 Excel 2007,Excel 2010 有了较大的变化 本章的学习目标 认识 Excel 2010 的工作界面 熟悉自定义工作环境 1.1 Excel 2010 的工作界面 Excel 2010 的工作界面由 快速访问 工具栏 标题栏 功能区 编辑栏 工作表 编辑区 状态栏和滚动条等部分组成, 如图 1-1 所示

10 Excel 在会计和财务中的应用 ( 第四版 ) 图 1-1 Excel 2010 的工作界面 Excel 2010 的新功能 1. 文件 按钮在 Excel 2010 中, 添加了 文件 按钮 单击 文件 按钮, 将打开 文件 面板 (Microsoft Office Backstage) Microsoft Office Backstage 视图取代了传统的文件菜单, 用户只需通过单击, 即可执行与工作簿相关的各项操作, 如图 1-2 所示 图 1-2 文件 面板 2. 迷你图功能迷你图是 Excel 2010 中的一个新功能 借助迷你图功能, 用户可以在同一单元格中创建小图表, 从而快速查看数据模型, 如图 1-3 所示 (C3 单元格 ) 迷你图可以显示一系 2

11 第 1 章 Excel 概述 列数值的趋势, 或者可以突出显示最大值和最小值 3. 智能粘贴功能 Excel 2010 的粘贴功能有较大改进, 菜单的格式的改变使粘贴更加易于使用, 如图 1-4 所示 粘贴菜单上的图表会根据复制的源对象来自动调整各自的功能 图 1-3 迷你图表 图 1-4 智能粘贴功能 快速访问工具栏 快速访问工具栏是 Excel 左上角一个工具栏, 其中包含 保存 按钮 撤销 按钮和 恢复 按钮 如图 1-5 所示为快速访问工具栏 图 1-5 快速访问工具栏快速访问工具栏可以放置在功能区的下方 右击快速访问工具栏, 在弹出的快捷菜单中选择 在功能区下方显示快速访问工具栏 命令, 如图 1-6 所示, 快速访问工具栏将移动到功能区的下方, 效果如图 1-7 所示 单击快速访问工具栏右侧的 自定义快速访问工具栏 按钮, 在弹出的 自定义快速访问工具栏 菜单中选择 在功能区下方显示 命令, 也可以将快速访问工具栏移动到功能区下方 图 1-6 在功能区下方显示快速访问工具栏 命令 图 1-7 快速访问工具栏移动到功能区的下方 3

12 Excel 在会计和财务中的应用 ( 第四版 ) 标题栏 标题栏位于 Excel 窗口的最上方, 用于显示当前工作簿和窗口名称, 如图 1-8 所示 标题栏的最右端是对 Excel 窗口进行操作的 3 个按钮, 分别为 最小化 按钮 最 大化 / 还原 按钮和 关闭 按钮, 单击相应按钮即可对窗口进行相应的操作 图 1-8 标题栏 功能区 菜单和按钮的旧外观已被新的功能区取代,Excel 2007 放弃了沿用多年的下拉菜单, 将各个命令经过精心的组织, 以功能区这一全新的面貌出现 Excel 2010 仍沿用了 Excel 2007 中的功能区 功能区位于标题栏的下方, 由一排选项卡组成较宽的带形区域, 其中包含各种按钮和命令, 如图 1-9 所示 默认情况下, 功能区由文件 开始 插入 页面布局 公式 数据 审阅 视图和加载项 9 个选项卡组成 图 1-9 常用 工具栏和 绘图 工具栏选项卡构成中的专有名词如下 (1) 选项卡 : 每个选项卡代表在 Excel 中或者执行的一组的核心任务, 如图 1-10 所示 (2) 组 : 每个选项卡包含一些功能类似的组并且将组中相关项显示在一起, 如图 1-10 所示 (3) 命令 : 选项卡的各种按钮或者菜单项, 如图 1-10 所示 图 1-10 选项卡 组 命令使用功能区的方法很简单, 只需单击需要使用的功能按钮即可 使用鼠标指向某个功能按钮并在其上停留片刻, 将会出现该按钮的功能说明 4

13 第 1 章 Excel 概述 有些功能按钮含有下拉箭头, 单击该箭头可以打开下拉库, 从中可以选择该功能的子功能, 如图 1-11 所示, 下拉库在很大程度上将复杂的对话框设置简化 如果需要将功能区最小化, 以便为工作区留出更多的空间 可以鼠标移至功能区, 右击, 在弹出的 自定义快速访问工具栏 菜单中选择 功能区最小化 命令, 如图 1-12 所示, 功能区将会隐藏起来 图 1-11 下拉库 图 1-12 功能区最小化 启动器 按钮 启动器 按钮位于选项卡中某个组中的右下方, 单击如图 1-13 所示的 启动器 按钮即可打开对应组的对话框或者任务空格 图 1-13 启动器 按钮 名称框与编辑栏 名称框和编辑栏位于功能区的下方, 如图 1-14 所示 名称框用于显示所选单元格或单元格区域的名称, 如果单元格还未命名, 则名称框显示该单元格的坐标 编辑栏用于显示活动单元格中的数据或公式 5

14 Excel 在会计和财务中的应用 ( 第四版 ) 图 1-14 名称框和编辑栏 工作表编辑区 工作表编辑区是 Excel 的主要工作区, 由行线和列线组成的表格区域, 用于显示或 者编辑工作表中的数据 它是占据屏幕最大且用于记录数据的区域, 所有的信息都将存 放在这张表中 如图 1-15 所示为工作表编辑区 图 1-15 工作表编辑区 工作表 标签 工作表 标签位于工作表区域的左下方, 如图 1-16 所示 工作表 标签用于显 示工作表的名称, 可以通过单击这些标签来切换工作表, 即只需单击 工作表 标签就 可以激活相应的工作表 图 1-16 工作表 标签 状态栏 状态栏位于工作表区域的下方, 如图 1-17 所示 在状态栏中, 不仅可以显示当前命 令或操作的相关信息, 而且可以根据当前的操作显示相应的提示信息 图 1-17 状态栏默认情况下, 在状态栏的右侧显示 视图 工具栏 视图 工具栏中列有 视图快捷方式 按钮 显示比例 按钮和 显示比例 区域 使用 视图 工具可以快速选择合适的视图方式和设置工作表编辑区的显示比例 6

15 第 1 章 Excel 概述 水平 垂直滚动条 水平 垂直滚动条分别位于工作表区域的右下方和右边, 如图 1-1 所示 水平 垂直滚动条用于在水平 垂直方向改变工作表的可见区域 滚动条的使用方法有以下 3 种 (1) 单击滚动条两端的方向键, 单击一次则工作表区域向指定的方向滚动一个单元格位置 ; 如果按住鼠标, 则工作表区域将一格一格地持续滚动 (2) 单击滚动条内的空白区, 工作表区域将以一次一屏的频率向指定的方向滚动 (3) 拖动滚动条中的小方块, 在拖动的过程中, 屏幕将显示所移动到的行号或者列号, 释放鼠标后, 工作表区域将显示所移动到的区域 1.2 自定义 Excel 用户在使用 Excel 2010 进行数据处理时, 对工作环境中的某些参数进行设置, 如设 置工作表中网格线的颜色 设置是否显示滚动条等, 可以通过系统设置来实现 设置屏幕显示 工作表的多数操作都与定制的屏幕显示相关 例如, 在工作表窗口中是否显示网 格线, 单元格中是否显示公式或值, 以及显示或隐藏批注等 更改屏幕显示通常使用 如图 1-18 所示的 视图 选项卡 图 1-18 视图 选项卡 视图 选项卡由以下 4 个选项组组成, 其中各选项组的含义如下 1. 工作簿视图 选项组该选项组用于控制查看 显示或者预览文档的外观 2. 显示 / 隐藏 选项组该选项组用于控制是否在 Excel 2010 中显示编辑栏 网格线和标题的窗口, 用户只需选中或取消相应的命令按钮即可 3. 显示比例 选项组该选项组用于控制文档的缩放显示, 使文档缩放为用户所需的比例要求 单击 显 7

16 Excel 在会计和财务中的应用 ( 第四版 ) 示比例 按钮, 选择所需的显示比例, 然后单击 确定 按钮即可 4. 窗口选项 选项组该选项组用于设置工作窗口显示要求 设置默认值 如果不对 Excel 2010 进行设置,Excel 2010 系统将自动使用其默认设置 在实际应用中, 如果一些经常用的默认值不符所需, 可以对其进行修改 1. 设置默认文件位置单击 文件 按钮, 在打开的 文件 面板中单击 选项 按钮, 打开 Excel 选项 对话框, 在该对话框中单击 保存 标签, 如图 1-19 所示 在该选项卡的 默认文件位置 文本框中输入默认文件夹的路径, 然后单击 确定 按钮即可设置默认文件位置 图 1-19 Excel 选项 对话框 保存 界面 2. 设置工作表中的字体和大小单击 文件 按钮, 在打开的 文件 面板中单击 选项 按钮, 打开 Excel 选项 对话框, 在该对话框中单击 常规 标签, 如图 1-20 所示 在 新建工作簿时 组合框中单击 使用的字体 的下拉箭头, 从该下拉列表中选择需要使用的字体, 单击 字号 的下拉箭头, 从该下拉列表中选择需要使用的字号, 然后单击 确定 按钮即可设置工作表中的字体及其大小 8

17 第 1 章 Excel 概述 3. 设置工作簿中工作表的数量单击 文件 按钮, 在打开的 文件 面板中单击 选项 按钮, 打开 Excel 选项 对话框, 在该对话框中单击 常规 标签, 如图 1-20 所示 在 新建工作簿时 组合框中的 包含的工作表数 文本框中输入所需工作表的数量, 然后单击 确定 按钮即可设置工作簿中工作表的数量 图 1-20 Excel 选项 对话框 常规 界面 自定义快速访问工具栏 Excel 的早期版本中, 用户可以自定义菜单或者工具栏, 在菜单或工具栏中增加或减少某个命令, 更改菜单和工具栏的排列, 将菜单或者某个工具栏拖动成为浮动的菜单或者工具条, 自定义快捷键 但是在 Excel 2010 中, 用户不能自定义功能区, 只能根据需要自定义快速访问工具栏, 从而添加命令 如果要自定义快速访问工具栏, 单击快速访问工具栏右侧的 自定义快速访问工具栏 按钮, 在弹出的 自定义快速访问工具栏 菜单中选择其中的命令, 下面通过一个实例来讲解 例 1-1 如图 1-21 所示为 自定义快速访问工具栏 菜单 单击 新建 命令, 将 新建 按钮添加到快速访问工具栏中, 添加效果如图 1-22 所示 9

18 Excel 在会计和财务中的应用 ( 第四版 ) 图 1-21 自定义快速访问工具栏 菜单 图 1-22 添加 新建 按钮 Excel 2010 将某些早期版本中功能设定为选择性命令, 如果在 Excel 2010 中使用这些命令, 必须以自定义方式将该命令取出后, 才可以执行该命令 具体操作步骤如下 (1) 单击 文件 按钮, 在打开的 文件 面板中单击 选项 按钮, 打开 Excel 选项 对话框, 在该对话框中单击 自定义功能区 标签, 如图 1-23 所示 图 1-23 选择 Excel 选项 自定义 命令 (2) 在左侧的 从下列位置选择命令 列表中, 单击所需的命令类别 在所选类别 10

19 第 1 章 Excel 概述 的命令列表中, 选择要添加到快速访问工具栏的命令, 单击 添加 按钮, 该命令将被 添加到右侧的命令列表中 单击 确定 按钮, 该命令将被添加到快速访问工具栏 当 不需要该命令时, 在右侧的命令列表中选中要删除的命令, 单击 删除 按钮即可 1.3 本章小结 通过本章的学习, 读者应该对 Excel 的工作界面有了全面的认识, 并对 Excel 的基本操作有所了解 读者应该掌握的内容如下 : 熟悉 Excel 的工作界面, 知道各部分的名称 用途 ; 学会使用水平 垂直滚动条 ; 了解 Excel 的菜单类型并掌握其操作方法 ; 学会识别各种命令符号的含义, 掌握快捷键的使用方法 ; 学会使用 Excel 对话框, 了解对话框中的各种选项及其操作方法 这是使用 Excel 的基础 1.4 思考练习 1. 填空题 (1) 在 Excel 2010 中, 添加了 按钮 单击该按钮, 打开的 Microsoft Office Backstage 视图取代了传统的文件菜单, 用户只需通过单击, 即可执行与工作簿相关的各项操作 (2) 用于显示工作表的名称, 还可以进行工作表切换, 只需单击 就能够激活相应的工作表 (3) 快速访问工具栏 Excel 标志右边的一个工具栏, 其中包含 和 (4) 默认情况下,Excel 功能区有 和 9 个选项卡 (5) 标题栏的最右端是对 Excel 窗口进行操作的 3 个按钮, 分别是 和 按钮 (6) 编辑栏可以用来 2. 上机操作题在 快速访问工具栏 中添加 打印预览 命令 11

20 第 2 章 Excel 进阶 Excel 作为功能强大的电子表格软件具有很强的数据计算功能 用户可以在单元格中直接输入公式或者使用 Excel 提供的函数对工作表中的数据进行计算与分析 通过本章的学习, 读者将能够熟练使用 Excel 的基本功能进行数据计算 本章的学习目标 掌握 Excel 的基本操作 了解 Excel 的公式并熟练运用 了解 Excel 的常用函数并可以灵活运用 2.1 基本操作 Excel 的基本操作主要包括 Excel 的基本操作对象 创建工作表 编辑工作表和修饰 工作表等内容 下面分别对其进行详细介绍 Excel 的操作对象 Excel 的基本操作对象包括单元格 工作表 工作簿和工作范围 1. 单元格单元格是 Excel 工作簿的基本对象的核心, 也是组成 Excel 工作簿的最小单位 如图 2-1 所示为单元格示例, 图中的白色长方格就是单元格 单元格可以记录字符或者数据 在 Excel 的操作中, 一个单元格内记录信息的长短并不重要, 关键是以单元格作为整体进行操作 单元格的长度 宽度及单元格内字符串的类型可以根据需要进行改变 单元格可以通过位置标识, 每一个单元格均有对应的列号 ( 列标 ) 和行号 ( 行标 ) 如图 2-1 所示中的 B2 C4 D6 等就是相应单元格的位置, 可以向上找到列号字母 再向左找到行号数字, 将它们结合在一起就可以作为该单元格的标识

21 第 2 章 Excel 进阶 图 2-1 单元格示例 2. 工作表使用工作表可以对数据进行组织和分析, 可以同时在多张工作表上输入并编辑数据, 并且可以对来自不同工作表的数据进行汇总计算 在创建图表之后, 既可以将其置于源数据所在的工作表上, 也可以放置在单独的图表工作表上 如图 2-2 所示为工作表, 工作表由单元格组成, 纵向为列, 分别以字母命名 (A B C ); 横向为行, 分别以数字命名 (1 2 3 ) 工作表的名称显示在工作簿窗口底部的工作表标签上 要从一个工作表切换到另一工作表进行编辑, 可以单击 工作表 标签 活动工作表的名称以下划线显示 可以在同一工作簿内或两个工作簿之间对工作表进行改名 添加 删除 移动或复制等操作 如图 2-2 所示的工作表, 当前的名字为 Sheet1 每张工作表均有一个标签与之对应, 标签上的内容就是工作表的名称 一张工作表最多可以有 行 256 列数据 将鼠标移到工作表中的某一单元格上单击, 该单元格的边框将变为粗黑线, 这就表示该单元格已被选中 在图 2-2 中, 选中的单元格是 C6, 即 C 列第 6 行 在工作表中选中单元格后, 即可在该单元格中输入字符串 数字 公式和图表等信息 图 2-2 工作表 3. 工作簿 Excel 工作簿是计算和储存数据的文件, 每一个工作簿都可以包含多张工作表, 因此, 可在单个文件中管理各种类型的相关信息 如图 2-2 所示的工作簿就有 3 个工作表, 分别是 Sheet1 Sheet2 和 Sheet3, 当前显示的工作表是 Sheet1 在工作簿中, 要切换到相应的工作表, 只需单击工作表标签, 相应的工作表就会成为当前工作表, 而其他工作表就被隐藏起来 如果想要在屏幕上同时看到一个工作簿中的 13

22 Excel 在会计和财务中的应用 ( 第四版 ) 多个工作表 ( 如 Sheet1 和 Sheet2), 首先打开该工作簿并且显示其中的一个工作表 Sheet1, 然后进行以下步骤 (1) 选择 视图 新建窗口 命令 (2) 单击新建窗口中的 Sheet2 (3) 选择 视图 全部重排 命令, 如图 2-3 图 2-3 视图 全部重排 命令所示 (4) 在如图 2-4 所示的 重排窗口 对话框中, 选择 垂直并排 选项, 单击 确定 按钮, 如图 2-5 所示, 就可以同时看到工作簿中的 Sheet1 和 Sheet2 工作表了 图 2-4 重排窗口 对话框 图 2-5 一个工作簿显示多个工作表 如果工作中需要同时打开多个工作簿进行编辑, 可以在打开多个工作簿后, 参照以上步骤进行操作 4. 选取工作范围 Excel 中的工作范围是指一组选定的单元格, 它们可以是连续的, 也可以是离散的, 如图 2-6 所示 如果选定一个范围后再进行操作, 则这些操作将作用于该范围内的所有单元格 例如, 可以对一个工作范围内的单元格同时设置大小 边框和注释 当然, 工作范围由用户选定, 它可以是一个单元格, 也可以是许多单元图 2-6 工作范围格, 还可以是整个工作表或整个工作簿 工作范围如果是一个单元格, 操作将很简单, 只要单击该单元格就可以选中这个工作范围 不过, 在实际应用中, 工作范围一般都是若干个单元格 这又分为以下几种情况 如果要选中工作表中的一片连续区域, 可以在要选区域一角的单元格上单击并按住鼠标左键, 然后拖动鼠标, 这时屏幕上会出现一片黑色区域, 当这片黑色区域刚好覆盖要选中的区域时, 释放鼠标左键, 此区域就被选中为工作范围 14

23 第 2 章 Excel 进阶 如果要选择几片不相连的区域或单元格, 可以按住 Ctrl 键, 再选择单个或多个单元格即可选定所需的工作范围 如果要选中一行或一列单元格, 可以单击列号区的字母或者行号区的数字, 则该列或者该行就被选中为工作范围 如果单击行号区和列号区的交界处, 将选中左上角的单元格 创建工作簿 创建工作簿也就是新建一个 Excel 文档, 可使用以下几种常用方法创建工作簿 1. 启动 Excel 2010, 自动创建工作簿 启动 Excel 2010,Excel 将自动创建一个空白工作簿, 如图 2-7 所示 图 2-7 启动 Excel 2010 自动创建工作簿 2. 使用 新建 按钮创建工作簿 启动 Excel 2010, 单击 快速访问 工具栏中的 新建 按钮 名为工作簿 1 的空白工作簿, 如图 2-8 所示,Excel 将创建一个 图 2-8 使用 新建 按钮创建工作簿 15

24 Excel 在会计和财务中的应用 ( 第四版 ) 3. 使用 文件 选项卡 (1) 打开 文件 选项卡, 在菜单中选择 新建 命令, 弹出如图 2-9 所示的 新 建工作簿 对话框 图 2-9 新建工作簿 对话框 (2) 选择 空白工作簿 选项创建一个空白工作簿, 或者选择系统中已经安装的创建模板创建一个基于模板的工作簿 (3) 单击 创建 按钮, 完成创建 打开工作簿 打开 文件 选项卡, 在菜单中选择如图 2-10 所示的 打开 命令, 在弹出的 打 开 对话框中选择所需的工作簿, 单击 打开 按钮即可打开工作簿 图 2-10 打开 对话框 保存工作簿 通过以下步骤即可完成保存工作薄的操作 16

25 第 2 章 Excel 进阶 (1) 打开 文件 选项卡, 在菜单中选择 保存 命令, 或者选择 另存为 命令, 弹出如图 2-11 所示的 另存为 对话框 图 2-11 另存为 对话框 (2) 在 文件名 组合框中输入 成绩表 (3) 单击 保存 按钮, 完成操作 这样即可将新文件命名为 成绩表 由于采用了默认工作路径, 所以这个工作表被存放在 Office 所在驱动器的 My Documents 目录下, 也可以根据需要选择工作路径 在图 2-11 中的 保存类型 下拉列表框中可以改变文件的格式, 这样可以在其他程序中使用 Excel 制作的电子表格 当工作告一段落或者需要进行其他工作的时候, 就需要对已经完成的工作进行保存 保存操作可以将所完成的操作从内存中储存到硬盘上 在实际使用 Excel 的过程中, 随时保存十分必要, 这样可以避免数据的意外丢失 使用以下设置还可以实现对文件的自动定时保护 打开 文件 选项卡, 选择 选项 保存 命令, 在打开的 Excel 选项 对话框的 自定义工作簿的保存方法 界面中设置保存自动恢复信息的时间间隔, 如图 2-12 所示, 一般系统默认的时间间隔为 10 分钟, 可以根据实际需要进行设置 图 2-12 文件自动定时保护设置 17

26 Excel 在会计和财务中的应用 ( 第四版 ) 数据的输入 要创建工作表, 就必须在单元格中输入数据 当启动所需的输入法并选中目标单元格后, 即可开始输入数据 在工作表的单元格中, 可以使用常数和公式这两种基本数据格式 常数是指文本 数字 日期和时间等数据 ; 而公式则是指包含等号 = 的函数 宏命令等 在向单元格中输入数据时, 需要掌握以下 3 种基本输入方法 单击目标单元格, 然后直接输入数据 双击目标单元格, 单元格中会出现插入光标, 将光标移到所需的位置后, 即可输入数据 ( 这种方法多用于修改单元格中的数据 ) 单击目标单元格, 再单击编辑栏, 然后在编辑栏中编辑或修改数据 1. 输入文本文本包括汉字 英文字母 特殊符号 数字 空格以及其他能够从键盘输入的符号 在 Excel 中, 一个单元格内最多可以容纳 个字符, 编辑栏可以显示全部的字符, 而单元格内最多只可以显示 个字符 在向单元格中输入文本时, 如果相邻单元格中没有数据, 那么 Excel 允许长文本覆盖到其右边相邻的单元格中 ; 如果相邻单元格中有数据, 则当前单元格只显示该文本的开头部分 要想查看并编辑单元格中的所有内容, 可以单击该单元格, 此时编辑栏可以将该单元格的内容显示出来, 如图 2-13 所示 图 2-13 显示单元格中的所有内容在输入文本的过程中, 文本会同时出现在活动单元格和编辑栏中, 按 BackSpace 键可以删除光标左边的字符 ; 如果要取消输入, 单击编辑栏中的 取消 按钮, 或按 Esc 键即可 在单元格中输入文本后, 如果要激活当前单元格右侧相邻的单元格, 按 Tab 键即可 ; 如果要激活当前单元格下方相邻的单元格, 按 Enter 键即可 ; 如果要使当前单元格成为活动单元格, 单击编辑栏中的 输入 按钮即可 默认情况下, 按 Enter 键后单元格会向下移动 如果要改变按 Enter 键后单元格的移动方向, 具体操作步骤如下 18

27 第 2 章 Excel 进阶 (1) 打开 文件 选项卡, 选择 选项 高级 命令, 弹出如图 2-14 所示的 Excel 选项 对话框的 使用 Excel 时采用的高级选项 界面 图 2-14 设置单元格的移动方向 (2) 单击 方向 下拉箭头, 在弹出的下拉列表中选择单元格移动的方向 ( 该下拉列表中包含 向下 向右 向上 和 向左 4 个选项 ) (3) 单击 确定 按钮, 完成设置 2. 输入数字数字也是一种文本, 和输入其他文本一样, 在工作表中输入数字也很简单 要在一个单元格中输入一个数字, 首先选中该单元格, 然后输入数字, 最后按 Enter 键 在 Excel 中, 可作为数字使用的字符包括 : ( ). e E, / $ % 在单元格中输入数字时, 有一点与其他文本不同, 即单元格中的数字和其他文本的对齐方式不同 默认情况下, 单元格中文本的对齐方式为左对齐, 而数字却是右对齐 如果要改变对齐方式, 可以在 单元格格式 对话框中进行设置 ( 在后面的章节中将作介绍 ) 在向单元格中输入某些数字时, 其格式不同, 输入方法也不相同 下面着重介绍分数和负数的输入方法 (1) 输入分数在工作表中, 分式常以斜杠 / 来分界分子和分母, 其格式为 分子 / 分母, 但日期的输入方法也是以斜杠来分隔年月日, 如 2005 年 6 月 28 日 可以表示为 2005/6/28, 这就有可能造成在输入分数时系统会将分数当成日期处理的错误 为了避免发生这种情况,Excel 规定 : 在输入分数时, 须在分数前输入 0 作区别, 并且 0 和分子之间要用一个空格隔开 例如, 要输入分数 2/3, 需输入 0 2/3 如果没有输入 0 和一个空格,Excel 会将该数据作为日期处理, 认为输入的内容是 2 月 3 日, 如图 2-15 所示 19

28 Excel 在会计和财务中的应用 ( 第四版 ) 图 2-15 没有输入 0 和空格的分数显示结果 (2) 输入负数在输入负数时, 可以在负数前输入减号 - 作为标识, 也可以将数字置于括号 ( ) 中 例如, 在选定的单元格中输入 (1), 然后按 Enter 键, 即可显示为 输入日期和时间日期和时间实际上也是一种数字, 只不过有其特定的格式 Excel 能够识别绝大多数用普通表示方法输入的日期和时间格式 在输入 Excel 可以识别的日期或时间数据之后, 该日期或时间在单元格中的格式将变为 Excel 某种内置的日期或时间格式 (1) 输入日期用户可以使用多种格式来输入一个日期, 斜杠 / 或 - 可以用来分隔日期的年 月和日 传统的日期表示方法以两位数来表示年份, 如 2001 年 6 月 28 日, 可以表示为 01/6/28 或 当在单元格中输入 01/6/28 或 并按 Enter 键后,Excel 会自动将其转换为默认的日期格式, 并将 2 位数表示的年份更改为 4 位数表示的年份 默认情况下, 当用户输入以 2 位数字表示的年份时, 会出现以下两种情况 当输入的年份为 00~29 之间的两位数年份时,Excel 将解释为 2000 至 2029 年 例如, 输入日期 29/6/28, 则 Excel 将认为日期为 2029 年 6 月 28 日 当输入的年份为 30~99 之间的两位数年份时,Excel 将解释为 1930 至 1999 年 例如, 输入日期 30/6/28, 则 Excel 将认为日期为 1930 年 6 月 28 日, 如图 2-16 所示 图 2-16 输入 30/6/28 后显示的结果提示 : 为了尽可能地避免出错, 建议用户在输入日期时不要输入以 2 位数字表示的年份, 而要输入 4 位数字表示的年份 20

29 第 2 章 Excel 进阶 如图 2-16 所示的是多种日期显示格式中的一种 如果要设置日期的其他显示格式, 具体操作步骤如下 1 选中目标单元格 2 选择 开始 单元格 格式 设置单元格格式 命令, 如图 2-17 所示 3 在 设置单元格格式 对话框中打开 数字 选项卡, 然后选择 分类 列表框中的 日期 选项, 如图 2-18 所示 图 2-17 选择 设置单元格格式 命令图 2-18 数字 选项卡 4 在 类型 列表框中列出了日期的所有显示格式, 选择所需的格式, 然后单击 确定 按钮 (2) 输入时间在单元格中输入时间的方式有按 12 小时制和按 24 小时制输入两种 二者的输入方法不同 如果按 12 小时制输入时间, 要在时间数字后加一个空格, 然后输入 a(am) 或 p(pm)( 字母 a 表示上午,p 表示下午 ) 例如, 下午 4 时 30 分 20 秒的输入格式为 4:30:20p 而如果按 24 小时制输入时间, 则只需输入 16:30:20 即可 如果用户在按 12 小时制输入时间时只输入时间数字, 而不输入 a 或 p, 则 Excel 将默认为上午的时间 提示 : 在同一单元格中输入日期和时间时, 须用空格分隔, 否则 Excel 将把输入的日期和时间当做文本 在默认状态下, 日期和时间在单元格中的对齐方式为右对齐 如果 Excel 无法识别输入的日期和时间, 也会把它们当做文本, 并在单元格中左对齐 此外, 要输入当前日期, 可使用 Ctrl+; 快捷键 ; 而要输入当前时间, 则可使用 Ctrl+Shift+; 快捷键 21

30 Excel 在会计和财务中的应用 ( 第四版 ) 4. 输入公式公式指的是一个等式, 利用它可以通过已有的值计算出一个新值 公式中可以包含数值 算术运算符 单元格引用和内置等式 ( 即函数 ) 等 Excel 最强大的功能之一是计算 用户可以在单元格中输入公式, 以便对工作表中的数据进行计算 只要输入正确的计算公式, 经过简单的操作, 计算结果将显示在相应的单元格中 如果工作表内的数据有变动, 系统会自动将变动后的答案计算出来 在 Excel 中, 所有的公式都以等号开始 等号标志着数学计算的开始, 它也提示 Excel 将其后的等式作为一个公式来存储 公式中可以包含工作表中的单元格引用 这样, 单元格中的内容即可参与公式中的计算 单元格引用可与数值 算术运算符以及函数一起使用 输入公式的具体操作步骤如下 (1) 选中要输入公式的单元格 (2) 在单元格中输入一个等号 = (3) 输入公式的内容, 如 3+5 A2+A3 或 Al+5 等 (4) 按 Enter 键, 完成 5. 输入符号和特殊字符 (1) 输入符号输入键盘上没有的符号, 具体操作步骤如下 1 选中目标单元格 2 选择 插入 文本 符号 命令, 打开 符号 选项卡, 如图 2-19 所示 图 2-19 符号 选项卡 3 在该选项卡的列表框中选择所需的符号, 然后单击 插入 按钮 4 此时 取消 按钮将变为 关闭 按钮, 单击该按钮, 即可在单元格中输入所需的符号 (2) 输入特殊字符输入键盘上没有的特殊字符, 具体操作步骤如下 22

31 第 2 章 Excel 进阶 1 选中目标单元格 2 选择 插入 特殊符号 命令, 打开 插入特殊符号 对话框 3 在该对话框中打开如图 2-20 所示的 特殊符号 选项卡, 在列表框中选择所需的特殊符号, 然后单击 确定 按钮 图 2-20 特殊字符 选项卡 4 此时 取消 按钮将变为 关闭 按钮, 单击该按钮, 即可在单元格中输入所需的特殊符号 6. 输入多行数据需要在一个单元格中输入两行数据时, 只要同时按 Enter 和 Alt 键就可以在第二行开始输入了, 如图 2-21 所示 选择 开始 单元格 格式 设置单元格 命令, 打开 设置单元格格式 对话框中的 对齐 选项卡, 在 文本控制 选项组中选中 自动换行 复选框, 如图 2-22 所示,Excel 会自动将超出单元格宽度的内容转到第二行显示 图 2-21 输入多行数据 图 2-22 对齐 选项卡 数据快速填充 在表格中经常要输入一些有规律的数据, 如果按常规逐个输入这些数据, 既费时又 容易出错 下面介绍又快又准确地输入这些有规律的数据的方法 23

32 Excel 在会计和财务中的应用 ( 第四版 ) 1. 在多个单元格中输入相同的数据如果表格中有很多单元格的内容是相同的, 逐个单元格重复输入会很麻烦 可使用一次填充多个单元格的方法, 其具体操作步骤如下 (1) 选中需要输入相同数据的多个单元格, 然后输入数据 此时, 只在活动单元格 ( 最后选取的单元格 ) 中显示输入的内容 (2) 同时按 Ctrl 和 Enter 键, 在所有选中的单元格中都将出现相同的输入数据, 效果如图 2-23 所示 图 2-23 输入相同数据注意 : 一定要同时按 Ctrl 和 Enter 键 如果只按 Enter 键, 那么只会在活动单元格中输入数据 2. 自动完成输入功能如果在单元格中输入的起始字符与该列已有单元格中的内容相符, 那么 Excel 可以自动填写其余的字符, 如图 2-24 所示 按 Enter 键将接受自动提供的字符 如果不想使用自动完成输入功能, 继续输入就可以忽略 按 BackSpace 键可以清除自动提供的字符 自动完成功能还有另外一种形式 如图 2-25 所示, 右击单元格, 然后在弹出的快捷菜单中选择 从下拉列表中选择 命令, 系统将列出所在列所有相邻单元格中的内容供用户选择 图 2-24 自动完成输入 图 2-25 选择列表 3. 自动填充如果需要输入的数字或文字数据并不是完全一样, 而是遵循某种规律 例如, 需要输入 1~100 作为编号 显然, 逐个手动输入会很麻烦 这时可使用 Excel 的自动填充功能, 在连续的单元格内产生有规律的序列, 其具体操作步骤如下 (1) 建立一段有规律的数据, 然后选中它们 这段有规律的数据既可以在同一列, 也可以在同一行, 但是必须在相邻的单元格中 假设建立了 2~4 的一个序列, 如图 2-26 所示 (2) 单击按住填充控制点, 向下拖动到合适的位置后释放鼠标,Excel 就会按照已有数据的规律来填充选中的单元格, 如图 2-27 所示 24

33 第 2 章 Excel 进阶 图 2-26 自动填充前 图 2-27 自动填充后 自动填充还有另外一种方式 如果用鼠标右键拖动填充控制点, 单击出现的下拉箭头, 将会弹出如图 2-28 中所示的快捷菜单 在该快捷菜单中, 可以改变填充的方式或指定填充的规律 其中各命令及其含义如下 复制单元格 指用选中的单元格中的内容填充到拖动范围内其他的单元格中 填充序列 指按照选中的单元格中数据的规律进行填充 仅填充格式 指仅填充格式而不填充数据 不带格式填充 指按照新单元格的格式填充数据 使用 Excel 处理日常事务时, 经常需要填充日期序列 Excel 提供了十分方便的日期填充功能 首先在单元格中输入一个日期, 如 , 然后用鼠标右键拖动填充控制点, 在打开的快捷菜单中选择日期的填充方式, 结果将会用日期填充到拖动的区域, 如图 2-29 所示 此时, 新增 4 种填充方式 : 以天数填充 指依次填入以输入日期开始的每一天 ; 以工作日填充 指跳过周六和周日, 只填充工作日 ; 以月填充 指填充每月中和输入日期同处在一天的日期 ; 以年填充 指填充每年中和输入日期处在同一月 同一天的日期 ( 即仅改变年份 ) 图 2-28 填充的方式 图 2-29 日期的填充方式 4. 用户自定义填充序列 Excel 提供了多种预定义的序列, 除此之外, 还允许用户根据实际需要自定义序列 自定义序列的具体操作步骤如下 (1) 打开 文件 选项卡, 选择 选项 高级 命令 (2) 在 Excel 选项 对话框的 常规 界面中单击 编辑自定义列表 按钮, 如图 2-30 所示 (3) 弹出 自定义序列 文本框, 对话框左侧有系统已经默认定义好的序列 ; 如要 25

34 Excel 在会计和财务中的应用 ( 第四版 ) 定义一个新序列, 在右侧输入新的序列数据, 序列内容之间按 Enter 键隔开, 如输入 中, 美, 法, 如图 2-31 所示 (4) 单击 添加 按钮, 此时自定义序列下方出现新建序列内容 (5) 单击 确定 按钮, 完成自定义序列的设置, 并返回工作界面 图 2-30 编辑自定义列表 按钮 图 2-31 自定义填充序列的设置 此时, 单击工作表中的某一单元格, 输入 中, 然后向右拖动填充柄, 释放鼠标即可得到自动填充的 中, 美, 法 序列内容, 如图 2-32 所示 图 2-32 自定义填充序列 编辑工作表 建立工作表之后, 用户需要根据实际需求, 运用 Excel 提供的编辑功能, 对工作表中的数据进行修改和调整, 使其符合实际需要 Excel 提供了强大的编辑功能, 用于对工作表及其数据进行各种操作 本节将详细介绍工作表的基本操作, 包括工作表的拆分与冻结 工作表和工作簿的保护和模板应用等内容 通过学习本节内容, 读者应掌握工作表数据的编辑方法 1. 工作表的基本操作一个工作簿中最多可以包含 255 个工作表 下面将介绍如何对这些工作表进行操作 (1) 激活工作表激活一个工作表, 可以使用以下方法之一 单击工作簿底部的 工作表 标签 使用键盘, 按 Ctrl+PageUp 快捷键激活当前页的前一页工作表, 然后按 Ctrl+PageDown 快捷键激活当前页的后一页工作表 使用工作表 标签滚动 按钮 当在工作簿中显示不了过多的工作表标签时, 可以单击 标签滚动 按钮对 工作表 标签进行翻页 标签滚动 按钮在工作簿的左下方, 图 2-33 标签滚动 按钮如图 2-33 所示 26

35 第 2 章 Excel 进阶 另外, 如果要滚动显示其他工作表标签, 在所需方向上连续单击 标签滚动 按钮中的滚动箭头, 直到所需 工作表 标签显示在屏幕上 ; 如果要一次滚动多个工作表, 按 Shift 键, 然后单击 标签滚动 按钮中的标签滚动箭头 ; 如果要显示最前或者最后一个工作表, 则可以单击 标签滚动 按钮左侧或者右侧的标签滚动按钮 ; 如果右击 标签滚动 按钮, 则可以直接从弹出的当前工作簿的所有工作表列表中选择需要切换到的 工作表 标签 (2) 插入工作表在编辑工作表的过程中, 经常要在一个已有的工作表中插入一个新的工作表, 可以使用以下操作方法之一 选择 开始 单元格 插入 插入工作表 命令, 如图 2-34 所示 图 2-34 开始 单元格 插入 插入工作表 命令 选择工作表, 单击 插入工作表 按钮 (3) 删除工作表删除某个工作表, 可以使用以下操作方法之一 单击目标单元格, 然后直接输入数据 在需要删除的工作表标签上右击, 从弹出的快捷菜单中选择 删除 命令 注意 : 一张工作表被删除以后将无法恢复, 所以在删除之前一定要慎重考虑 (4) 移动和复制 Excel 工作表可以在一个或者多个工作簿中移动 如果要将一个工作表移动或者复制到不同的工作簿时, 两个工作簿必须都是打开的 1 使用菜单使用菜单移动或复制工作表的具体步骤如下 右击要移动的工作表 选择 移动或复制工作表 命令, 打开 移动或复制工作表 对话框, 如图 2-35 所示 在 移动或复制工作表 对话框中的 工作簿 下拉列表框中选择需要移到的工作 图 2-35 移动或复制工作表 对话框 27

36 Excel 在会计和财务中的应用 ( 第四版 ) 簿, 然后在 下列选定工作表之前 列表框中选择要移至位置之后的工作表 如果是移动, 则要取消 建立副本 复选框的选中状态 ; 如果是复制, 则应选中 建立副本 复选框, 最后单击 确定 按钮 2 使用鼠标单击需要移动的工作表标签, 将它拖动到指定的位置, 然后释放鼠标 在拖动的过程中鼠标将变成一个小表和一个小箭头 如果是复制操作, 则需要在拖动鼠标时按住 Ctrl 键 注意 : 若将一个工作表从一个工作簿移动到另外一个工作簿, 而目标工作簿含有与此工作表同名的工作表,Excel 将自动改变此工作表的名称并使之变为唯一的名称 例如,Sheet2 将变为 Sheet2(2) (5) 对多个工作表同时进行操作一次对多个工作表进行操作, 方法是先选中多个工作表, 然后执行移动 复制和删除等操作 选择多个工作表的方法有以下两种 单击工作表标签的同时按 Ctrl 键, 则该工作表与以前选择的工作表同时被选中 单击工作表标签的同时按 Shift 键, 则选中连续的工作表 注意 : 如果要取消选中工作表中的其中一个, 可以按 Ctrl 键的同时单击该工作表标签 如果要取消所有被选中的工作表, 可以右击某个选中的 工作表 标签, 然后在弹出的快捷菜单中选择 取消成组工作表 命令 ; 或者不按键盘, 直接单击一个未选中的工作表标签即可 (6) 重命名工作表为工作表起一个有意义的名称, 以便辨认 查找和使用工作表 为工作表命名有以下 3 种方法 单击工作表标签 ( 如表 Sheet l), 然后选择 开始 单元格 格式 重命名工作表 命令, 此时工作表标签 Sheet l 的颜色变黑, 如图 2-36 所示 输入新的工作表名称, 效果如图 2-37 所示 图 2-36 重命名工作表 图 2-37 重命名工作表后 在工作表标签上右击, 从弹出的快捷菜单中选择 重命名 命令, 工作表标签 颜色变黑后, 输入新的工作表名称即可 28

37 第 2 章 Excel 进阶 双击工作表标签, 工作表标签的颜色变黑后, 输入新的工作表名称即可 (7) 隐藏与显示工作表如果不希望被他人查看某些工作表, 可以使用 Excel 的隐藏工作表功能将工作表隐藏起来 隐藏工作表还可以减少屏幕上显示的窗口和工作表, 避免不注意的改动 例如, 可隐藏包含敏感数据的工作表 当一个工作表被隐藏后, 它的标签也被隐藏起来 隐藏的工作表仍处于打开状态, 其他文档仍可以利用其中的信息 隐藏工作表的操作步骤如下 1 右击需要隐藏的工作表 2 选择 隐藏 命令, 选定的工作表将被隐藏 注意 : 不能将同一个工作簿中所有的工作表都隐藏起来, 每一个工作簿至少应有一个可见的工作表 显示隐藏的工作表的步骤如下 1 右击工作表, 选择 取消隐藏 命令, 打开如图 2-38 所示的 取消隐藏 对话框 2 在 取消隐藏 对话框中选择需要取消隐藏的工作表, 然后单击 确定 按钮即可 图 2-38 取消隐藏 对话框 2. 拆分和冻结工作表 Excel 提供了拆分和冻结工作表窗口的功能, 利用这些功能可以更加有效地利用屏幕空间 拆分和冻结工作表窗口功能是两个非常相似的功能 (1) 拆分工作表拆分工作表窗口是把工作表当前活动的窗口拆分成若干窗格, 并且在每个被拆分的窗格中都可以通过滚动条来显示工作表的每一个部分 所以, 使用拆分窗口功能可以在一个文档窗口中查看工作表不同部分的内容 1 拆分 选定拆分分隔处的单元格, 该单元格的左上角就是拆分的分隔点 选择 视图 窗口 拆分 命令, 如图 2-39 所示 工作表窗口将拆分为上下左右 4 个部分, 如图 2-40 所示 图 2-39 视图 窗口 拆分 命令 图 2-40 拆分工作表窗口 29

38 Excel 在会计和财务中的应用 ( 第四版 ) 提示 : 拆分时, 也可选定行号或者列标, 这样拆分后的窗口将为上下或者左右两部分 2 取消拆分单击拆分窗口的任一单元格, 选择 视图 窗口 拆分 命令 或者在分割条的交点处双击 ; 如果要删除一条分割条, 在该分割条上方双击 (2) 冻结工作表对于比较大的工作表, 屏幕无法在一页里同时显示标题和数据, 冻结工作表窗口功能也是将当前工作表活动窗口拆分成窗格 所不同的是, 在冻结工作表窗口时, 活动工作表的上方和左边窗格将被冻结, 即当垂直滚动时, 冻结点上方的全部单元格不参与滚动 ; 当水平滚动时, 冻结点左边的全部单元格不参与滚动 通常情况下, 冻结行标题和列标题, 然后通过滚动条来查看工作表的内容 使用冻结工作表窗口功能不影响打印 1 冻结 选择一个单元格作为冻结点, 在冻结点上方和左边的所有单元格都将被冻结, 并保留在屏幕上 选择 视图 窗口 冻结窗格 冻结拆分窗格 命令, 如图 2-41 所示 冻结拆分窗口后, 工作表将变为如图 2-42 所示 拖动垂直 / 水平滚动条, 可保持显示冻结区域中行 / 列的数据 图 2-41 冻结工作表窗口 图 2-42 冻结工作表窗口 2 撤销冻结选择 视图 窗口 冻结窗格 取消冻结窗格 命令, 即可撤销被冻结的窗口 3. 保护工作表和工作簿如果必须放下手中的工作, 但又不想退出 Excel, 此时可以为工作表和工作簿建立保护, 这样能防止错误操作对工作表数据造成的损害 (1) 保护工作表保护工作表功能可以防止修改工作表中的单元格 Excel 宏表 图表项 对话框编辑表项和图形对象等 保护工作表的具体操作步骤如下 30

39 第 2 章 Excel 进阶 1 激活需要保护的工作表 2 选择 审阅 更改 保护工作表 命令, 打开如图 2-43 所示的 保护工作表 对话框 3 在该对话框中选择保护的选项并输入密码, 然后单击 确定 按钮 输入密码 ( 可选 ) 可以防止未授权用户取消对工作表的保护 密码可以为字母 数字和符号, 并且字母要区分大小写 密码的长度不能超过 255 个字符 4 弹出 确认密码 对话框, 如图 2-44 所示, 再次输入密码, 单击 确定 按钮 图 2-43 保护工作表 对话框 图 2-44 确认密码 对话框 若有人试图修改受保护的工作表, 这时将会弹出如图 2-45 所示的警告对话框 图 2-45 工作表受到保护后 (2) 保护工作簿保护工作簿功能可以保护工作簿的结构和窗口, 以防止对工作簿进行插入 删除 移动 隐藏 取消隐藏以及重命名工作表等操作, 从而保护窗口不被移动或改变大小 启用保护工作簿功能的操作步骤如下 1 激活需要保护的工作簿 2 选择 审阅 更改 保护工作簿 命令, 如图 2-46 所示 3 弹出 保护结构和窗口 对话框, 选择需要保护的选项并输入密码, 然后单击 确定 按钮, 如图 2-47 所示 图 2-46 审阅 更改 保护工作簿 命令 图 2-47 保护结构和窗口 对话框 31

40 Excel 在会计和财务中的应用 ( 第四版 ) 4 弹出 确认密码 对话框, 再次输入密码, 单击 确定 按钮 保护结构和窗口 对话框中的各选项及其含义如下 结构 : 保护工作簿的结构, 避免删除 移动 隐藏 取消隐藏 插入工作表或者重命名工作簿 窗口 : 保护工作簿的窗口不被移动 缩放 隐藏 取消隐藏或关闭 密码 ( 可选 ) : 与 保护工作表 中的密码功能相同, 可以防止未授权用户的非法操作 (3) 取消保护如果要取消工作表或者工作簿的保护状态, 可以选择 审阅 更改 撤销工作表保护 命令 如果原来没有设置密码, 选择所需命令即可取消保护 ; 如果原来设置了密码, 选择所需要的命令后将打开 撤销工作表保护 对话框或 撤销工作簿保护 对话框, 输入正确的密码, 然后单击 确定 按钮, 即可取消保护 修饰工作表 一个专业的电子表格不仅需要有翔实的数据内容和公式分析 统计功能, 而且还应配有外观 本节将通过对表格外观参数 ( 文字大小 字体 颜色 对齐方式 单元格的边框线 底纹以及表格的行高和列宽等 ) 的设置来美化工作表, 从而更有效地显示数据内容 1. 设置单元格格式用户可以对 Excel 中的单元格设置各种格式, 包括设置单元格中数字的类型 文本的对齐方式 字体 单元格的边框以及单元格保护等 不仅可以对单个单元格和单元格区域设置格式, 还可以同时对一个或多个工作表设置格式 设置单元格格式的操作步骤如下 选择需要进行格式设置的单元格或者单元格区域 选择 开始 单元格 格式 设置单元格格式 命令, 或者在选中的单元格上右击, 在弹出的快捷菜单中选择 设置单元格格式 命令, 打开 设置单元格格式 对话框, 如图 2-48 所示 在 设置单元格格式 对话框中设置单元格的格式, 然后单击 确定 按钮即可 (1) 设置数据的对齐方式为了排版整齐, 单元格中的数据一般需要对齐 默认情况下, 单元格中的文字是左对齐, 数字是右对齐 如果要改变数据的对齐方式, 需要将 设置单元格格式 对话框切换到如图 2-49 所 32

41 第 2 章 Excel 进阶 示的 对齐 选项卡, 在该选项卡中进行文本对齐方式的设置 图 2-48 设置单元格格式 对话框 图 2-49 对齐 选项卡 1 设置水平对齐方式 水平对齐 下拉列表框中包含常规 左 ( 缩进 ) 居中 靠右 填充 两端对齐 跨列居中和分散对齐等选项, 默认选项为常规, 即文本左对齐, 数字右对齐, 逻辑值和错误值则居中对齐 2 设置垂直对齐方式 垂直对齐 下拉列表框中包含常规靠上 靠下 居中 两端对齐和分散对齐等选项, 默认选项为常规选项, 即文本靠下垂直对齐 3 设置缩进 缩进 列表框是将单元格内容从左向右缩进, 缩进的单位是一个字符 4 设置文本控制选项 文本控制 选项包括 自动换行 缩小字体填充 和 合并单元格 3 个复选框 33

42 Excel 在会计和财务中的应用 ( 第四版 ) 自动换行 复选框 : 选中该复选框, 单元格中的文本自动换行, 行数的多少与文本的长度和单元格的宽度有关 缩小字体填充 复选框 : 选中该复选框,Excel 会根据列宽自动缩小单元格中字符的大小, 使之一致 合并单元格 复选框 : 选中该复选框, 所选单元格将被合并成一个单元格, 如果所选的单元格都有数据, 则单击 确定 按钮后,Excel 会弹出一个消息框, 提示只保留左上方单元格内容, 单击消息框的 确定 按钮即可 5 设置文本的旋转方向 方向 用来改变单元格中文本旋转的角度, 例如, 要将文本从右上往左下转, 使用负数, 反之则使用正数 开始 选项卡中提供了常用的文本对齐方式的快图 2-50 对齐方式的快捷键捷键, 如图 2-50 所示 利用这些快捷键可以大大提高工作效率 (2) 设置单元格字体要设置单元格字体, 将 设置单元格格式 对话框切换到如图 2-51 所示的 字体 选项卡, 然后在 字体 选项卡中对字体 字形 字号 下划线 颜色和特殊效果进行设置 图 2-51 字体 选项卡 1 设置字体 字形 字号 Excel 提供的字体包括宋体 仿宋体和楷体等字体, 可以在 字体 列表框中选择系统提供任意一种字体 Excel 提供的字形包括常规 倾斜 加粗和加粗倾斜, 可以在 字形 列表框中选择系统提供任意一种字形 字体的大小由字号决定, 可用字号取决于打印机和文本所用的字体, 在 字号 列表框中选择一种字号以设置文本或者数字的大小 2 设置下划线 颜色打开 下划线 或者 颜色 下拉列表框, 根据需要选择不同的下划线类型或者颜色 34

43 第 2 章 Excel 进阶 3 设定普通字体选中 普通字体 复选框, 字体 选项卡中的各个选项将重置为默认值 4 设置特殊效果 删除线 复选框 : 选中该复选框可以产生一条贯穿字符中间的直线 上标 和 下标 复选框 : 选中这两个复选框中的其中之一, 可以将选中的文本或数字设置为上标或者下标 开始 选项卡提供了常用的单元格字体格式设置的工具按钮, 如图 2-52 所示 使用这些按钮可以使工作变得方便 (3) 设置单元格边框在工作表中给单元格添加边框可以突出显示工作表数据, 使工作表更加清晰明了 要设置单元格边框, 将 设置单元格格式 对话框切换到如图 2-53 所示的 边框 选项卡, 然后在该选项卡中进行设置即可 图 2-52 单元格字体格式设置的 工具按钮 图 2-53 边框 选项卡 技巧 : 可以利用边框产生三维效果, 方法是将浅色作为背景色, 以白色作为边框顶部和左部的颜色, 黑色作为边框底部和右部的颜色, 这样就会产生凸起的效果, 反之则产生凹进的效果 (4) 设置单元格图案如果想改善工作表的视觉效果, 可以为单元格添加图案,Excel 提供了设置单元格图案的方法 设置单元格图案, 首先将 设置单元格格式 对话框切换到如图 2-54 所示的 填充 选项卡, 然后在该选项卡中进行图案的设置 在 填充 选项卡中可以对单元格的底色 单元格底纹的类型和颜色进行设置 在 示例 预览框中可以预览设置的效果 35

44 Excel 在会计和财务中的应用 ( 第四版 ) 图 2-54 填充 选项卡 (5) 单元格保护可以为单元格设置保护, 防止非法的修改 在此之前必须设置工作表保护 当设置工作表保护后, 只需将 设置单元格格式 对话框切换到如图 2-55 所示的 保护 选项卡, 即可对单元格进行保护设置 图 2-55 保护 选项卡 技巧 : 当定义一个单元格格式后, 又要把这个单元格格式用于另外的单元格时, 使用格式刷能够快速地实现此操作 选中已经定义了格式的单元格或范围, 单击 常用 工具栏中的 格式刷 按钮, 然后单击需要复制单元格格式的单元格或单元格区域即可 2. 格式化行和列为了使工作表更加美观, 需要适当地调整工作表的列宽和行高 适当的调整会有助于在一页中显示更多的数据, 甚至可以在一行或者一列中隐藏保密的数据 (1) 调整行高和列宽 Excel 中, 工作表的默认行高为 列宽为 8.38 要改变行高和列宽, 可以使用 36

45 第 2 章 Excel 进阶 鼠标直接在工作表中进行修改, 也可以利用菜单进行修改 方法一 : 将鼠标移到行号区数字上 下边框或列号区字母的左 右边框上, 按住鼠标左键并拖动调整行高或列宽至所需位置后释放鼠标即可 方法二 : 选择 开始 单元格 格式 列宽 命令, 打开 列宽 对话框, 如图 2-56 所示 在 列宽 文本框中输入列宽值, 然后单击 确定 按钮即可 如果选择 开始 单元格 格式 自动调整列宽 命令,Excel 将自动调整列宽, 使之适合列中最长的单元格的高度 调整行高和调整列宽的操作相似, 只是应选择 开始 单元格 格式 行高 命令 (2) 隐藏与取消隐藏要将某些行和列隐藏起来, 首先选中需要隐藏的行的行号区数字或列的列号区字母, 然后选择 开始 单元格 格式 隐藏或取消隐藏 命令, 最后在子菜单中选择需要隐藏的内容即可, 如图 2-57 所示 图 2-56 列宽 对话框 图 2-57 开始 单元格 格式 隐藏或取消隐藏 命令 要将隐藏的行和列显示出来, 首先选择包含隐藏行或列的上下行行号区数字或上下列的列号区字母, 然后选择 开始 单元格 格式 隐藏或取消隐藏 命令, 最后在子菜单中选择相应的取消隐藏命令即可 3. 自动套用格式 Excel 为用户提供了多种工作表格式, 用户可以使用 自动套用格式功能 为自己的工作表穿上一件 Excel 自带的 修饰外套 即使第一次使用 Excel 的新手, 也可以不使用任何复杂的格式化操作, 就能够创建出各种漂亮的表格和报告 这样既可以美化工作表, 又能节省用户大量的时间 自动套用格式的具体操作步骤如下 37

46 Excel 在会计和财务中的应用 ( 第四版 ) (1) 打开需要套用格式的工作表 (2) 选择 开始 式样 套用表格格式 命令, 如图 2-58 所示, 其中提供 了多种可供选择的式样 图 2-58 开始 式样 套用表格格式 命令 (3) 单击需要选择套用的格式, 如中等深浅 27, 弹出如图 2-59 所示的 创建表 对话框 (4) 选择需要套用的区域, 单击 确定 按钮返回工作表, 套用格式化后的效果如图 2-60 所示 图 2-59 创建表 对话框 图 2-60 套用格式化后的效果 4. 使用样式为了方便,Excel 提供了将数字 对齐 字体 边框 图案和保护等格式设置成样式的方法 可以根据需要将这几种格式组合成样式, 并命名 当需要设置工作表的格式时, 只要使用样式功能将所定义的样式应用于选中的单元格区域, 而不必使用 设置单元格格式 功能逐项设置 38

47 第 2 章 Excel 进阶 (1) 使用样式 选择 开始 样式 单元格样式 命令, 如图 2-61 所示, 即可使用样式功能 图 2-61 选择 开始 样式 单元格样式 命令 Excel 提供了多种预定义的样式, 如图 2-61 所示 选定单元格, 单击需要的样式即可 当 Excel 提供的样式不能满足需求时, 可以采用自定义样式 新建自定义样式的具体操作步骤如下 1 选择要添加样式的工作簿 2 选择 开始 样式 单元格样式 新建单元格样式 命令, 打开如图 2-62 所示的 样式 对话框 3 在 样式名 文本框中输入样式名, 单击 格式 按钮, 在弹出的 设置单元格格式 对话框中设定单元格样式 4 单击 确定 按钮, 返回 样式 对话框, 再单击 确定 按钮即可 (2) 合并样式如果要把一个已经设置了样式的工作簿应用于另一个工作簿, 可以使用合并样式功能 合并样式的具体操作步骤如下 1 打开源工作簿 ( 已经设置好样式的工作簿 ) 和目标工作簿 ( 要并入样式的工作簿 ), 并激活目标工作簿 2 在目标工作簿上选择 开始 样式 单元格样式 合并样式 命令, 打开如图 2-63 所示的 合并样式 对话框 3 在该对话框中的 合并样式来源 列表框中选择源工作簿, 然后单击 确定 按钮即可 39

48 Excel 在会计和财务中的应用 ( 第四版 ) 图 2-62 样式 对话框 图 2-63 合并样式 对话框 打印工作表 打印工作表是 Excel 的一项重要内容, 这是使用电子表格的一个关键步骤 事实上, 当在屏幕上编制好工作表后,Excel 就会按默认设置安排好打印过程, 只需要单击 标准 工具栏中的 打印 按钮即可打印 但是, 不同行业的用户需要的报告样式不同, 每个用户都会有自己的特殊要求 为方便用户,Excel 通过页面设置 打印预览等命令提供了许多用来设置或调整打印效果的实用功能 本节将介绍怎样使用这些功能, 以便打印出完美的 具有专业化水平的工作表 1. 预览打印结果在准备打印和输出工作表之前, 有一些工作要做 例如, 可以使用 打印预览 功能来快速查看打印页面的效果与最终期望的输出结果之间的差距, 然后通过 页面设置 相关功能予以调整, 以达到理想的打印结果 打开 嘉佑小电器有限公司 2010 年销售情况 工作表, 如图 2-64 所示, 这个工作表由表格和曲线图两部分组成 图 2-64 嘉佑小电器有限公司 2010 年销售情况 工作表 40

49 第 2 章 Excel 进阶 查看 嘉佑小电器有限公司 2010 年销售情况 的打印预览结果, 具体操作步骤如下 (1) 选中 嘉佑小电器有限公司 2010 年销售情况 工作表 (2) 打开 文件 选项卡, 选择 打印 选项, 在窗口的右侧可以看到预览的结果, 如图 2-65 所示 图 2-65 打印预览状态预览窗口底部状态栏信息 第 1 页共 1 页 说明这个工作表内容需要用 1 页纸打印出来 Excel 对于超过一页信息的内容, 根据打印纸张的大小自动进行分页处理 可单击窗口顶部的 下一页 按钮或直接按 PageDown 键, 依次浏览剩余几页的打印预览效果 (3) 进入预览模式后, 单击 关闭打印预览 按钮, 如图 2-65 所示, 将退出打印预览模式, 并返回工作表的常规显示状态 2. 打印设置如果按照默认状态打印出的工作表不能满足要求, 可通过 页面布局 选项卡进行一些操作以使打印结果更加符合要求 (1) 设置纸张方向可以按纵向和横向两个方向来设置文件的打印方向 纵向是以纸的短边为水平位置打印 ; 横向是以纸的长边为水平位置打印 具体操作步骤如下 1 选择 页面布局 选项卡 2 单击 纸张方向 按钮, 选择 纵向 或者 横向 命令, 如图 2-66 所示, 完成纸张方向的设置 41

50 Excel 在会计和财务中的应用 ( 第四版 ) (2) 设置纸张大小 选择 页面布局 页面设置 纸张大小 命令, 用户可以在该命令的下拉列表 中选择需要的纸张大小, 单击选择的纸张大小即可, 如图 2-67 所示 图 2-66 纸张方向 按钮 图 2-67 纸张大小 按钮 (3) 设置缩放比例 页面布局 选项卡中有一个 调整为合适大小 选项组, 如图 2-68 所示 用户可以通过单击 缩放比例 :100% 正常尺寸 选项中间的向上或向下的箭头, 或者在选项中的小方框内输入数字来设置放大或缩小打印的工作表比例 Excel 允许用户将工作表缩小到正常大小的 10%, 放大到 400% (4) 设置页边距页边距是指工作表中打印内容与页面上 下 左 右页边的距离 正确地设置页边距可以使工作表中的数据打印到页面的指定区域中, 其具体操作步骤如下 1 选择 页面布局 选项卡 2 单击 页面设置 组中的 启动器 按钮, 弹出 页面设置 对话框 3 选择如图 2-69 所示的 页边距 选项卡 4 输入页边距 页眉和页脚的数值, 或者单击文本框右边的向上或向下的箭头, 也可以调整页边距 设置 页眉 页脚 距页边的距离的方法同上 只是页眉和页脚距页边的距离应小于工作表上端和下端页边距 5 单击 确定 按钮, 完成设置 在 居中方式 选项组中, 选中 水平 复选框, 工作表在水平方向居中 ; 选中 垂直 复选框, 工作表在垂直方向居中 若两个复选框都选中, 则工作表位于页面中间 42

51 第 2 章 Excel 进阶 图 2-68 调整为合适大小 选项组 图 2-69 页边距 选项卡 (5) 设置页眉页脚页眉是打印在工作表顶部的眉批或文本或页号 页脚是打印在工作表底部的眉批或文本或页号 用户可以选择 Excel 提供的页眉和页脚, 如果页眉和页脚列表框中没有用户需要的格式, 还可以自己定义页眉和页脚 具体操作步骤如下 1 选择 页面布局 选项卡 2 单击 页面设置 组中的 启动器 按钮, 弹出 页面设置 对话框 3 选择 页眉 / 页脚 选项卡, 单击 自定义页眉 或 自定义页脚 按钮, 如图 2-70 所示 4 弹出 页眉 或 页脚 对话框, 如图 2-71 所示 可以通过对话框中的 文字 页号 总页数 日期 时间 文件名 和 标签名 按钮定义页眉或页脚 图 2-70 页眉 / 页脚 选项卡 图 2-71 页眉 对话框 (6) 设置打印网络线与标题通常, 网格线与标题是用户根据需要设计的 打印前, 用户可以选中 页面布局 选项卡 工作表 选项组中的 网格线打印 或者 标题打印 选项, 如图 2-72 所示 43

52 Excel 在会计和财务中的应用 ( 第四版 ) (7) 设置打印区域 Excel 默认的打印区域是整个工作表, 通过打印区域的设置, 也可打印需要的局部工作表内容 具体操作步骤如下 1 选择单元格区域 2 选择 页面布局 选项卡 3 选择 打印区域 设置打印区域 命令, 如图 2-73 所示 图 2-72 网格线打印 选项 图 2-73 打印区域 设置打印区域 命令 4 设置为打印区域的单元格区域将被虚线框中, 效果如图 2-74 所示 图 2-74 虚线框中打印区域 5 打开 文件 选项卡, 选择 打印 选项, 检查打印结果, 可以看到打印局部 内容的效果, 如图 2-75 所示 图 2-75 预览局部打印当需要打印整个工作表中的数据时, 选择 打印区域 取消打印区域 命令即可 3. 打印打印工作表, 可以通过按快捷键 Ctrl+P, 或者单击 Office 按钮后选择 开始 打印 命令, 打开如图 2-76 所示的 打印内容 对话框, 单击 确定 按钮执行打印命令 44

53 第 2 章 Excel 进 阶 图 2-76 打印内容 对话框 如果用户只想打印工作表中的内嵌图表而不打印其他内容 在打印之前 双击需要 打印的图表 使选中的图表周围出现实心小方块(表示选定此图表) 然后选择 打印 命令即可 打印内容 对话框中的主要选项及其含义如下 选定区域 单选按钮 选中此选项后 只打印工作表中的选定单元格区域 活动工作表 单选按钮 选中此选项后 打印当前工作表的所有区域 按选 择的页号逐页打印 如果没有定义打印页号区域 则打印整个工作表 整个工作簿 单选按钮 选中此选项后 打印工作簿的整个打印区域 打印份数 列表框 可以选择打印的份数 当份数大于 1 时 可以选择分页 打印 全部 单选按钮 打印所选工作表的全部内容 页 单选按钮 打印 由 到 组合框中指定的页 属性 按钮 单击此按钮 将打开当前打印机的 属性 对话框 打印到文件 复选框 如果用户要将选定的范围保存到硬盘或软盘上 并保 存为 Excel 的打印输出格式 可以选择此项 预览 按钮 显示打印预览窗口 设置完成后 单击该对话框中的 确定 按钮即可开始打印 此外 单击 标准 工具栏中的 打印 按钮 可以不打开 打印 对话框而直接开始打印 2.2 公式 本节将介绍公式的基本概念和语法的内容 并详细介绍如何建立 修改 移动和复 制公式 公式的引用 公式的审核 用数组公式进行计算等内容 45

54 Excel 在会计和财务中的应用 ( 第四版 ) 公式概述 公式主要用于计算 可以说, 没有公式的 Excel 就没有使用价值 使用公式可以进行简单的计算, 如加 减 乘和除等的计算 ; 也可以完成很复杂的计算, 如财务 统计和科学计算等 ; 还可以使用公式进行比较或者操作文本和字符串 工作表中需要计算结果时, 使用公式是最好的选择 简单地说, 公式就是一个等式, 或者说是连续的一组数据和运算符组成的序列 考察以下公式 : =10*2/3+4 =SUM(Al:A3) =B5&C6 第一个公式用户很熟悉, 只是等号左边是省略的单元格 第二 第三个公式可能以前没有接触过 在 Excel 中, 公式有其本身的特点, 并且有自己的规定, 或者叫做语法 在工作表单元格中输入公式后, 使用公式所产生的结果就会显示在工作表中 要查看产生结果的公式, 只需选中该单元格, 公式就会出现在公式栏中 要在单元格中编辑公式, 双击该单元格或者按 F2 键即可 下面介绍公式中的运算符和公式的运算顺序 1. 运算符 Excel 中的运算符可以分为 : 算术运算符 比较运算符 文本运算符和引用运算符 4 类 用户通过算术运算符可以完成基本的数学运算, 如加 减 乘 除 乘方和求百分数等 如表 2-1 所示的是 Excel 中所有的算术运算符 表 2-1 Excel 中的算术运算符公式中使用的符号和键盘符含义示例 + 加 减 负号 -8 * 乘 8*8 / 除 8/8 ^ 乘方 8^8 % 百分号 88% ( ) 括号 (3+3)*3 比较运算符用于比较两个数值, 并产生逻辑值 TRUE 和 FALSE 如表 2-2 所示的是 46

55 第 2 章 Excel 进阶 Excel 中所有的比较运算符 表 2-2 Excel 中的比较运算符 公式中使用的符号和键盘符 含 义 示 例 = 等于 C1=C2 > 大于 C1>C2 < 小于 C1<C2 >= 大于等于 C1>=C2 <= 小于等于 C1<=C2 <> 不等于 C1<>C2 文本运算符可以将一个或者多个文本连接为一个组合文本 文本运算符只有一个, 即 &, 其含义是将两个文本值连接或串联起来产生一个连续的文本值, 如 CLASS&ROOM 的结果是 CLASSROOM 引用运算符可以将单元格区域合并运算, 如表 2-3 所示的是 Excel 中所有的引用运 算符 表 2-3 Excel 中的引用运算符 公式中使用的符号和键盘符 含 义 示 例 :( 冒号 ) 区域运算符, 对于两个引用之间, 包括两个引用在内的所有单元格进行引用 A1:B5,( 逗号 ) 联合运算符, 将多个引用合并为一个引用 SUM(Al:B2,A3:A4) ( 空格 ) 交叉运算符, 产生同时属于两个引用的单元格区域的引用 SUM(A4:H4 B3:B8) 2. 运算顺序 当公式中既有加法, 又有乘法和除法, 还有乘方时,Excel 应怎样确定其运算先后 顺序呢? 这就需要理解运算符的运算顺序, 也就是运算符的优先级 对于同级运算, 则 按照从等号开始从左到右进行运算 ; 对于不同级的运算符, 则按照运算符的优先级进行 运算 如表 2-4 所示的是常用运算符的运算优先级 表 2-4 公式中运算符的优先级 运算符 说 明 :( 冒号 ) 区域运算符,( 逗号 ) 联合运算符 ( 空格 ) 交叉运算符 ( ) 括号 47

56 Excel 在会计和财务中的应用 ( 第四版 ) ( 续表 ) 运算符 说 明 -( 负号 ) 如 :-5 % 百分号 ^ 乘方 * 和 / 乘和除 + 和 - 加和减 & 文本运算符 = < > >= <= <> 比较运算符 3. 文本运算文本运算符 (&) 用于连接字符串, 如 " 我爱 "&" 伟大的 "&" 中国 " 的结果是 我爱伟大的中国 当然, 文本运算符还可以连接数字, 如 12&34 的结果是 1234 字符串 注意 : 当用 & 来连接数字时, 数字串两边的双引号可以没有, 但对于连接一般的字母 字符串和文本时, 双引号不可以去掉, 否则公式将返回错误值 4. 比较运算比较运算符可以对两个数字或者两个字符串进行比较, 以产生逻辑值 TRUE 或 FALSE 例如, 公式 =200<400 的结果是 TRUE; 而公式 =100>400 的结果则是 FALSE 注意 : 当用比较运算符对字符串进行比较时,Excel 会先将字符串转化成内部的 ASCII 码, 然后再作比较 因此, 公式 ="AB">"BC" 的结果是 FALSE 5. 数值转换在公式中, 每个运算符都需要特定类型的数值与之对应 如果输入数值的类型与所需的类型不同,Excel 有时可以对这个数值进行转换 下面举几个例子来说明公式中数值的转换 例如, 公式 ="1"+"2", 产生的结果是 3 这是因为使用 (+) 时,Excel 会认为公式中运算项为数值 虽然公式中的引号说明 1 和 2 是文本型数字, 但 Excel 会自动将文本数字转换成数值 又如, 公式 ="1"+"$2.00", 产生的结果也是 3, 其原因与前面的例子相同 再如, 使用函数的公式 =SQRT("9"), 则公式也会先将字符 9 转换成数值 9, 然后再计算 SQRT() 函数, 即对 9 的开方 ( 有关函数的使用参看本章后面的章节 ), 产生的结果 3 例如, 公式 ="A"&TRUE 产生的结果是 ATRUE 这是因为需要文本时,Excel 会自 48

57 第 2 章 Excel 进阶 动将数值和逻辑型值转换成文本 6. 日期和时间在 Excel 中, 不仅可以对数字或者字符进行运算, 而且可以对日期进行运算 Excel 会将日期存储为一系列的序列数, 而将时间存储为小数, 因为时间可以被看成日期的一部分 用户可以用一个日期减去另外一个日期来计算两个日期的差值 例如, 公式 ="98/10/1"-"97/8/1" 产生的结果为 426 即 1998 年 10 月 1 日和 1997 年 8 月 1 日之间相差 426 天 同样可以对日期进行其他的混合运算 例如, 公式 ="98/10/1"-"97/8/1"/"98/10/1" 产生的结果为 提示 : 当在 Excel 中输入日期, 并且年份输入为两位数时,Excel 会将在 00~29 之间的输入数解释为 年, 而将在 30~99 之间的输入数解释为 年 例如, 对于 10/10/1,Excel 会认为这个日期为 2010 年 10 月 1 日, 而将 95/10/1 认为是 1995 年 10 月 1 日 7. 语法公式的语法是指公式中元素的结构或者顺序 Excel 中的公式遵循一个特定的语法 : 最前面是等号 =, 后面是参与运算的元素和运算符 元素可以是常量数值 单元格引用 标志名称以及工作表函数 公式的基本操作 公式的运用在 Excel 中占有很重要的地位 下面介绍公式的一些基本操作 1. 建立公式公式的建立在前面的一些例子中都曾经提到过, 这一节将正式介绍怎样通过键盘和公式选项板来创建公式 (1) 输入公式用键盘创建公式的操作步骤如下 1 选择要输入公式的单元格 2 先输入等号 =, 然后输入计算表达式 ; 如果使用的是函数向导向单元格输入公式,Excel 会自动在公式前面插入等号 3 按 Enter 键完成公式的输入 49

58 Excel 在会计和财务中的应用 ( 第四版 ) 注意 : 如果在某一区域内输入同一个公式, 逐个输入显然太慢了 此时, 可以选中该单元格区域, 输入所需要的公式, 然后按 Ctrl+Enter 键, 则 Excel 会自动将所有单元格都粘贴上该公式 这不仅对公式有效, 而且对其他文本和字符都有效 (2) 公式选项板使用公式选项板来输入公式 如果创建含有函数的公式, 那么公式选项板有助于输入工作表函数和公式 要显示公式选项板, 可以单击编辑栏中的按钮, 当在公式中输入函数时, 公式选项板会显示函数的名称 函数中的每个参数 函数的当前结果和整个公式的结果等 如图 2-77 所示为 函数参数 对话框 下面以计算 的平均值为例来说明公式选项板的使用, 此时需要借助 AVERAGE 函数 平均值计算的操作步骤如下 1 选中一个单元格 2 在编辑栏中输入 =AVERAGE(), 图 2-77 函数参数 对话框如图 2-78 所示, 此时为了预览结果, 可以用公式选项板 3 单击编辑栏中的按钮, 将会弹出 函数参数 对话框, 并且 函数参数 对话框上会自动增加 AVERAGE 函数的使用选项 4 在 函数参数 对话框的函数 AVERAGE 参数栏中分别输入 和 30 5 输入完毕后, 计算结果将出现在 函数参数 对话框上, 如图 2-79 所示 图 2-78 在编辑栏中输入函数公式 图 2-79 输入函数的 函数参数 对话框 6 单击 确定 按钮, 完成公式的输入 完整的公式将出现在编辑栏中, 而计算结果将显示在所选单元格上 2. 修改公式如果发现某个公式有错误, 就必须对该公式进行修改 对公式的修改非常简单, 下面介绍修改公式的操作步骤 50

59 第 2 章 Excel 进阶 学会设置公式的操作步骤如下 (1) 单击包含需要修改公式的单元格 (2) 在编辑栏中对公式进行修改 如果需要修改公式中的函数, 则替换或修改函数的参数 3. 公式的移动和复制如果要将含有公式的单元格整个 ( 包括格式 边框等 ) 移动或者复制到另外的单元格或区域, 可以按照前面章节介绍的移动和复制单元格的方法进行操作, 也可以只粘贴单元格的公式来实现 如图 2-80 所示, 在单元格 A1 中有一个公式 =40+50*3, 现在要将它移动或者复制到 C3 单元格, 可以按照如下步骤进行操作 单元格公式粘贴的操作步骤如下 (1) 单击 A1 单元格 (2) 打开 开始 选项卡 剪贴板 组中的 复制 按钮 (3) 在 C3 单元格上右击, 在弹出的快捷菜单中选择 选择性粘贴 命令, 打开 选择性粘贴 对话框, 如图 2-81 所示 图 2-80 单元格中的公式 图 2-81 选择性粘贴 对话框 (4) 在 选择性粘贴 对话框中选中 公式 单选按钮 (5) 单击 确定 按钮, 完成公式的移动或者复制操作 公式的引用 每个单元格都有自己的行 列坐标位置, 在 Excel 中将单元格行 列坐标位置称之为单元格引用 在公式中可以通过引用来代替单元格中的实际数值 在公式中不但可以引用本工作簿中任何一个工作表中任何单元格或单元格组的数据, 而且也可以引用其他工作簿中的任何单元格或者单元格组的数据 引用单元格数据以后, 公式的运算值将随着被引用的单元格数据变化而变化 当被引用的单元格数据被修改后, 公式的运算值将自动修改 51

60 Excel 在会计和财务中的应用 ( 第四版 ) 1. 引用的类型为满足用户的需要,Excel 提供了相对引用 绝对引用和混合引用 3 种不同的引用类型 在引用单元格数据时, 要弄清这 3 种引用类型 (1) 绝对引用绝对引用是指被引用的单元格与引用的单元格的位置关系是绝对的, 无论将这个公式粘贴到哪个单元格, 公式所引用的还是原来单元格的数据 绝对引用的单元格名称的行和列前都有符号 $, 如 $A$l $D$2 等 (2) 相对引用相对引用的格式是直接用单元格或者单元格区域名, 而不加符号 $, 如 A1 D2 等 使用相对引用后, 系统将会记住建立公式的单元格和被引用的单元格的相对位置关系, 在粘贴这个公式时, 新的公式单元格和被引用的单元格仍保持这种相对位置 如图 2-82 所示是包含 4 位学生成绩的成绩表 要计算 4 个人各科的平均分和总评成绩 图 2-82 成绩表计算平均分的公式是 4 人成绩的平均值 计算总评成绩的公式是 : 总评成绩 = 语文 *30%+ 数学 *30%+ 英语 *30%+ 体育 *10%; 各科在总评中所占比例已经列于第二行 运用绝对引用和相对引用计算平均分和总评成绩的操作步骤如下 1 在单元格 B9 中输入公式 =AVERAGE(B5,B6,B7,B8); 在这个公式中, 对单元格 B5 至 B8 都使用了相对引用 2 完成相对引用后, 可以在单元格 B9 中得到计算结果 下面将单元格 B9 的公式复制到 C9 D9 E9 和 F9 中, 复制完成后, 用户就会发现这些单元格中的公式与 B9 单元格的公式不同了 原来公式中的 B 分别被改为 C D E 和 F 这就是相对引用 3 在单元格 F5 中输入公式 =B5*$B$2+C5*$C$2+D5*$D$2+E5*$E$2 在这个公式中, 对单元格 B5 至 D5 都使用了相对引用, 而对 B2 至 D2 则采用了绝对引用 下面将单元格 F5 的公式复制到 F6 F7 和 F8 中, 复制完成后, 可以发现这些单元格中, 公式相对引用的单元格名称改变了, 而绝对引用的单元格名称没有改变 这时可以按 Ctrl+` 快捷键 ( 用来切换查看公式还是公式的结果的快捷键 ) 来查看工作表的所有公式 52

61 第 2 章 Excel 进阶 (3) 混合引用如果符号 $ 在数字之前, 而字母前没有 $, 那么被应用的单元格的行位置是绝对的, 列位置是相对的 反之, 则行的位置是相对的, 而列的位置是绝对的 这就是混合引用, 如 $E3 或 E$3 2. 引用同一工作簿中的单元格在当前工作表中可以引用其他工作表中单元格的内容 例如, 当前的工作表是 Sheet1, 如要在 A1 单元格中引用工作表 Sheet3 中 B6:B8 的内容之和, 有以下两种方法 (1) 直接输入 在 Sheet1 中选择 A1 单元格, 输入 =SUM(Sheet3!B6:B8), 然后按 Enter 键 (2) 用鼠标选择需要引用的单元格 在 Sheet1 中选择 A1 单元格, 输入 =SUM( ; 单击 Sheet3 工作表的标签, 在 Sheet3 中选择 B6:B8 单元格, 然后按 Enter 键 注意 : 当编辑栏中显示 Sheet1 中 A1 单元格的公式 =SUM(Sheet3!B6:B8 时, 此公式还缺少一个 ), 此时可以在编辑栏中补上 ), 也可以直接按 Enter 键,Excel 会自动加上一个 ) 3. 引用其他工作簿中的单元格在当前工作表中可以引用其他工作簿中的单元格或者单元格区域的数据或者公式 例如, 当前的工作簿是 工作簿 2, 如果 工作簿 的 Sheet1 工作表中的 A1 单元格要引用 工作簿 1 ( 文件存放的路径是 C:\My Documents\ 工作簿 1.xlsx ) 中的 $B$3:$B$4 单元格中的数据, 有以下两种方法 (1) 直接输入 在 Sheet l 中选择 A1 单元格, 输入 = SUM('C:\My Documents\[ 工作簿 1.xls]Sheet1'!$B$3:$B$4), 然后按 Enter 键 (2) 选择需要引用的单元格 在 Sheet1 中选择 A1 单元格, 输入 =SUM(, 打开 工作簿 1, 在其中单击工作表 Sheet1 的标签, 在 Sheet1 中选择 $B$3:$B$4 单元格, 然后按 Enter 键, 关闭 工作簿 1 为了便于操作和观察, 可以选择 视图 窗口 全部重排 命令, 然后单击 确认 按钮或者按 Enter 键, 使 工作簿 1 和 工作簿 2 同时显示在屏幕上, 再进行上述的操作 公式的错误与审核 审核公式对公式的正确性来说至关重要, 它包括循环引用 公式返回的错误值 审 核及检查等内容 53

62 Excel 在会计和财务中的应用 ( 第四版 ) 1. 循环引用使用公式时引用公式自身所在的单元格, 这时公式将把它视为循环引用 公式的循环引用, 是指公式直接或间接引用该公式所在的单元格的数值 在计算循环引用的公式时,Excel 必须使用前一次迭代的结果来计算循环引用中的每个单元格 迭代是指重复工作表直到满足特定的数值条件 如果不改变迭代的默认设置,Excel 将在 100 次迭代以后或者两个相邻迭代得到的数值变化小于 时停止迭代运算 在使用循环引用时, 可以根据需要来设置迭代的次数和迭代的最大误差,Excel 中默认的迭代次数为 100 次 更改默认迭代设置的操作步骤如下 (1) 打开 文件 选项卡, 选择 Excel 选项 公式 命令, 打开如图 2-83 所示的 Excel 选项 对话框 图 2-83 Excel 选项 对话框 (2) 选中 迭代计算 选项 (3) 根据需要在 最多迭代次数 文本框中和 最大误差 文本框中输入进行迭代运算时的最多迭代次数和最大误差 (4) 单击 确定 按钮, 完成设置 2. 公式返回的错误值如果输入的公式不符合格式或者其他要求, 就无法在 Excel 工作表的单元格中显示运算结果, 该单元格中将会显示错误值信息, 如 #####! #DIV/01 #N/A # NAME? #NULL! #NUM! #REF! #VALUE! 了解这些错误值信息的含义有助于用户修改单元格中的公式 如表 2-5 所示的是 Excel 中的错误值及其含义 54

63 第 2 章 Excel 进阶 表 2-5 错误值及其含义 错误值含义 #####! #DIV/0! #N/A # NAME? #NULL! #NUM! #REF! #VALUE! 公式产生的结果或输入的常数太长, 当前单元格宽度不够, 不能正确地显示出来, 将单元格加宽即可避免这种错误公式中产生了除数或者分母为 0 的错误, 这时候就要检查是否存在以下几种情况 : (1) 公式中是否引用了空白的单元格或数值为 0 的单元格作为除数 ;(2) 引用的宏程序是否包含有返回 #DIV/0! 值的宏函数 ;(3) 是否有函数在特定条件下返回 #DIV/0! 错误值引用的单元格中没有可以使用的数值, 在建立数学模型缺少个别数据时, 可以在相应的单元格中输入 #N/A, 以免引用空单元格公式中含有不能识别的名字或者字符, 这时候就要检查公式中引用的单元格名称是否输入了不正确的字符试图为公式中两个不相交的区域指定交叉点, 这时候就要检查是否使用了不正确的区域操作符或者不正确的单元格引用公式中某个函数的参数不对, 这时候就要检查函数的每个参数是否正确引用中有无效的单元格, 移动 复制和删除公式中的引用区域时, 应当注意是否破坏了公式中的单元格引用, 检查公式中是否有无效的单元格引用在需要数值或者逻辑值的位置输入了文本, 检查公式或者函数的数值和参数 3. 审核及检查 Excel 提供了公式审核功能, 使用户可以跟踪选定范围中公式的引用或者从属单元格, 也可以追踪错误 使用这些功能的操作方法如下 选中需要审核的公式所在的单元格, 然后选择 公式 选项卡的 公式审核 选项组, 如图 2-84 所示, 该选项组包含了审核公式功能的各种命令 图 2-84 公式审核 选项组如果需要显示公式引用过的单元格, 可在图 2-84 中单击 公式审核 选项组中的 追踪引用单元格 按钮, 此时公式所引用过的单元格就会有追踪箭头指向公式所在的单元格 单击 公式审核 选项组中的 移去追踪引用单元格箭头 按钮即可取消该追踪箭头 如果需要显示某单元格被哪些单元格的公式引用, 可在图 2-84 中单击 公式审核 选项组中的 追踪从属单元格 按钮, 此时该单元格就会产生指向引用它的公式所在单元格的追踪箭头 在删除单元格之前, 最好使用这种方法来检查该单元格是否已被其他公式所引用 单击 公式审核 选项组中的 移去追踪从属单元格箭头 按钮即可取消 55

64 Excel 在会计和财务中的应用 ( 第四版 ) 该追踪箭头 当单元格显示错误值时, 选择 公式审核 选项组中的 错误检查 命令的下拉箭头, 在下拉菜单中选择 追踪错误 命令, 如图 2-85 所示, 即可追踪出产生错误的单元格 图 2-85 追踪错误 命令要取消上述的所有追踪箭头, 可以在 公式审核 选项中选择 移去箭头 命令的下拉箭头, 选择下拉菜单中合适的命令 要在每个单元格显示公式, 而不是结果值, 可以单击 公式审核 选项组中 显示公式 按钮, 此时工作表中所有设置公式的单元格均将显示公式, 如图 2-86 所示 图 2-86 单元格显示公式 数组计算 数组是一组公式或值的长方形范围,Excel 视数组为一组 有些数组计算公式返回一组出现在很多单元格中的结果 数组计算是小空间进行大量计算的强有力的方法 它可以代替很多重复的公式 1. 输入数组公式输入数组公式的具体操作步骤如下 (1) 选中需要输入数组公式的单元格或者单元格区域 (2) 输入公式的内容 (3) 按 Shift+Ctrl+Enter 快捷键结束输入 输入数组公式其实是一个非常简单的操作, 但要理解它并不容易 下面举例来帮助用户理解怎样建立数组公式 如图 2-87 所示的内容, 要在 C 列得到 A 列和 B 列 1~4 行相乘的结果, 可以在 C1 单元格输入公式 =Al*B1, 然后复制 使用数组的方法得到这些结果,A1 至 A4 和 B1 至 B4 的数据就是数组的参数 具体操作步骤如下 (1) 选定 C1 至 C4 单元格区域 ( 注意 :4 个单元格全部选中 ) (2) 在编辑栏中输入公式 =Al:A4*B1:B4 (3) 按 Shift+Ctrl+Enter 快捷键结束输入, 得到如图 2-88 所示的结果 56

65 第 2 章 Excel 进阶 图 2-87 数组参数 图 2-88 返回多个结果 注意 : 数组公式如果返回多个结果, 当删除数组公式时, 必须删除整个数组公式, 即选中整个数组公式所在单元格区域然后删除, 而不能只删除数组公式的一部分 2. 选中数组范围通常情况下, 输入数组公式的范围, 其大小与外形应该与作为输入数据的范围的大小和外形相同 如果存放结果的范围太小, 就看不到所有的结果 ; 如果范围太大, 有些单元格中就会出现不必要的 #N/A 错误 为避免以上情况的发生, 选择的数组公式的范围必须与数组参数的范围一致 3. 数组常量在数组公式中, 通常都使用单元格区域引用, 也可以直接输入数值数组 直接输入的数值数组被称为数组常量 在工作表中逐个单元格输入数值很费时, 此时可以使用建立数组常量的方法化繁为简 可以使用以下的方法来建立数组中的数组常量 : 直接在公式中输入数值, 并且用大括号 {} 括起来, 需要注意的是, 把不同列的数值用逗号, 分开, 不同行的数值用分号 ; 分开 例如, 如果要表示一行中的 和下一行中的 , 应该输入一个 2 行 3 列的数组常量 {100,200,300;400,500,600} 在实际应用中, 先选中一个 2 行 3 列的矩形区域, 然后输入公式 ={100,200,300; 400,500,600}, 按 Shift+Ctrl+Enter 快捷键结束输入, 则在这个 2 行 3 列的矩形区域即可一次得到所需要的数值, 如图 2-89 所示 图 2-89 数组常量举例数组常量有其输入的规范, 因此, 无论在单元格中输入数组常量还是直接在公式中输入数组常量, 都并非是随便输入一个数值或者公式就可以的 在 Excel 中, 使用数组常量时应该注意以下规定 数组常量中不能含有单元格引用, 并且数组常量的列或者行的长度必须相等 数组常量可以包括数字 文本 逻辑值 FALSE 和 TRUE 以及错误值, 如 #NAME? 数组常量中的数字可以是整数 小数或者科学记数公式 57

66 Excel 在会计和财务中的应用(第四版) 在同一数组中可以有不同类型的数值 如{1,2,"A",TURE} 数组常量中的数值不能是公式 必须是常量 并且不能含有$ ( )或者% 文本必须包含在双引号内 如"CLASSROOMS" 2.3 函数 函数处理数据的方式与公式处理数据的方式相同 函数通过引用参数接收数据 并 返回结果 大多数情况下 返回的是计算结果 也可以返回文本 引用 逻辑值 数组 或者工作表的信息 本节中列出的函数都可以用于工作表或 Excel 宏表中 本节将介绍 如何使用函数 并介绍 Excel 中的一些工作表函数及其参数说明 函数概述 通过前面的学习知道 单元格中可以包括文本 公式或者函数 通过公式和函数可 以在单元格中放置计算的值 公式可以进行加 减 乘和除运算 也可以包含函数 Excel 用预置的工作表函数进行数学 文本 逻辑的运算或者查找工作表的信息 与直接用公式进行计算相比 使用函数进行计算的速度更快 例如 公式=(A1+ A2+A3+A4+A5+A6+A7+A8)/8 与使用函数公式=AVERAGE(A1:A8)是等价的 但是 使 用函数速度更快 而且占用工具栏的空间更少 同时可以减少输入出错的机会 因此 应该尽量使用函数 函数通过参数接收数据 输入的参数应该放在函数名之后 并且必须用括号括起来 各函数使用特定类型的参数 如数值 引用 文本或者逻辑值 函数中使用参数的方式 与等式中使用变量的方式相同 函数的语法以函数的名称开始 后面是左括号 逗号隔开的参数和右括号 如果函 数要以公式的形式出现 则要在函数名前输入等号 1. 函数分类 Excel 提供了大量的函数 这些函数按功能可以分为以下几种类型 (1) 数字和三角函数 用于处理简单和复杂的数学计算 (2) 文本函数 用于在公式中处理字符串 (3) 逻辑函数 用于真假值判断 或者进行符号检验 (4) 数据库函数 用于分析数据清单中的数值是否符合特定条件 (5) 统计函数 用于对选定区域的数据进行统计分析 (6) 查找和引用函数 用于在数据清单或者表格中查找特定数据 或者查找某一单 元格的引用 58

67 第 2 章 Excel 进阶 (7) 日期与时间函数 : 用于在公式中分析和处理日期和时间值 (8) 财务函数 : 用于一般的财务计算 2. 输入函数输入函数与输入公式的操作步骤类似 可以在单元格中直接输入函数的名称 参数, 这是最快捷的方法 如果不能确定函数的拼写以及函数的参数, 则可以使用函数向导插入函数 输入单个函数的操作步骤如下 (1) 选中需要输入函数的单元格 (2) 选择 公式 函数库 插入函数 命令, 或者单击编辑栏中的按钮, 打开如图 2-90 所示的 插入函数 对话框 (3) 在 或选择类别 下拉列表框中选择所需的函数类型, 则该函数类型的所有函数将显示在 选择函数 列表框中, 在该列表框中可选择需要使用的函数 图 2-90 插入函数 对话框 (4) 单击 确定 按钮, 完成函数的输入 插入函数 对话框中的两个列表框下方有选中函数的说明, 通过这些说明可以了解所选函数的作用 常见的函数 Excel 中的函数有 200 多个, 下面将列出比较常用的 Excel 函数及其参数, 并且对其进行解释 说明和举例 1. 财务函数输入财务函数, 并不需要输入财务等式, 因为 Excel 函数处理的速度很快, 而且不容易出错 (1) DB 函数 DB 函数是用固定余额递减法来计算一笔资产在给定期间内的折旧费 其语法如下 : DB(cost, salvage, life, period, month) 其中,cost 为资产的初始价值 ;salvage 为资产全部折旧后的剩余价值 ;life 为资产折旧的时间长度 ;period 为需要计算折旧值的单个时间周期, 它的单位必须与 life 相同 ; month 为第一年的月份数, 如果缺省, 则默认为 12 个月 59

68 Excel 在会计和财务中的应用 ( 第四版 ) 例如, 要计算 在剩余价值为 ,3 年使用期限, 第一年中使用 6 个月的情况下第一年的固定余额递减折旧费, 应使用公式 : =DB(500000, , 3, l, 6) 值为 (2) DDB 函数 DDB 函数利用双倍余额递减法或其他方法来计算指定期间内某项固定资产的折旧费 它返回加速利率的折旧费 早期大, 后期小 这种方法是以资产的净账簿值 ( 资产价值减去前几年的折旧费 ) 的百分比来计算折旧费 其语法如下 : DDB(cost, salvage, life, period, factor) 其中, 前 4 个参数的定义可以参看 DB 函数 factor 参数是指定余额递减法, 默认为 2, 表示一般的双倍余额递减法, 如果设置为 3, 则表示 3 倍余额递减法 例如, 要计算 的机器在剩余价值为 ,5 年使用期限 (60 个月 ) 的折旧费, 可以使用公式 =DDB(100000,10000,60,1) 来计算第一个月的双倍余额递减折旧费为 ; 使用公式 =DDB(100000,10000,5,1) 来计算第一年的双倍余额递减折旧费为 ; 使用公式 =DDB(100000,10000,5,5) 来计算第五年的双倍余额递减折旧费为 (3) PV 函数 PV 函数是计算某项投资的一系列等额分期偿还额的现值之和或一次性偿还额 其语法如下 : PV(rate, nper, pmt, fv, type) 其中,rate 为各期利率 ;nper 为投资期限 ;pmt 为各个数额相同时的定期支付额 ;fv 为投资在期限终止时的剩余值, 其默认值为 0;type 用于确定各期的付款时间是在期初还是期末,type 为 0 表示期末,type 为 1 表示期初, 其默认值为 0 例如, 有一个投资机会, 只需要现在投资 , 就可以在未来五年中每年返回 为决定这项投资是否可以接受, 必须计算将得到的等额分期偿还额 的现值之和 假设现在的银行利率为 4.0%, 可以使用以下公式 : =PV(4%,5,30000) 该公式使用了 pmt 参数 ; 没有 fv 参数 ; 也没使用 type 参数, 表示支付发生在每个周期的期末 该公式返回值为 , 意味着现在投入 才能得到每年返回的 由于现在只需要 , 因此这是一项可以接受的投资 如果该投资不是在未来五年中每年返回 , 而是一次性的, 这时就应该使用公式 : 60

69 第 2 章 Excel 进阶 =PV(4%,5,,150000) 这里必须使用逗号作为占位符来表示未用到 pmt 参数, 以便使 Excel 知道 为 fv 参数 ; 这里同样省略了 type 参数, 其含义同上 该公式返回值为 , 意味着现在投入 才能得到 5 年后返回 , 因此这仍然是一项可以接受的投资 (4) NPV 函数 NPV 函数是基于一系列现金流和固定的各期利率, 返回一项投资的净现值 一般来说, 任何产生大于 0 的净现值都被认为有利可图 其语法如下 : NPV(rate, value1, value2, ) 其中,rate 为各期利率 :value1,value2, 为 1~29 笔支出及收入的参数值 它们所属各期间的长度必须相等, 支付及收入的时间都发生在期末 NPV 按次序使用 value1, value2, 来注释现金流的次序 所以一定要保证支出和收入的数额按正确的顺序输入 如果参数是数值 空白单元格 逻辑值或表示数值的文字表达式, 则都会计算在内 ; 如果参数是错误值或不能转化为数值的文字, 则被忽略 ; 如果参数是一个数组或引用, 只有其中的数值部分计算在内, 忽略数组或引用中的空白单元格 逻辑值 文字及错误值 NPV 函数在两个重要方面不同于 PV 函数 PV 函数假定相同的支付额, 而 NPV 则允许可变的支付额 另一个重要区别在于 PV 函数允许支付和接收发生在周期开始或者结束, 而 NPV 函数假定所有支付和接收都均等分布, 发生在周期结束 如果投资费用必须在前面全部付清, 则不应将此项费用作为 value 参数之一, 而应当从函数结果中减去它 另一方面, 如果该费用必须在第一期结束时付清, 则应当将它作为第一个负 value 参数 例如, 要进行一项投资 , 预计第一年末损失 , 而第二年末 第三年末和第四年末分别可以获得 银行利率为 5%, 要估计这项投资是否划算, 则应使用以下公式 : =NPV(5%,-10000,50000,75000,95000) 返回值为 因此, 这是一项可以接受的投资 (5) RATE 函数 RATE 函数用于计算得到一系列等额支付或者一次总支付的投资收益率 其语法如下 : RATE (nper, pmt, pv, fv, type, guess) 其中,nper,pmt,fv,type 可以参考 PV 函数 ;pv 为投资额现值 ;guess 提供给 Excel 开始计算收益率的一个起点, 默认值为 0.1, 即 10% 例如, 考虑一项 4 年内每年可以得到 的投资, 投资费用为 要 61

70 Excel 在会计和财务中的应用 ( 第四版 ) 计算投资的实际收益率, 可以利用以下公式 : =RATE(4,100000, ) 返回值为 10% 准确的返回值为 , 但是由于答案是一个百分比, 因此 Excel 将单元格格式转化为百分比 RATE 函数是利用迭代过程来计算利率的 函数从给定的 guess 参数值开始计算投资的利润率 如果第一个净现值大于 0, 则函数选择一个较低的利率进行第二次迭代 RATE 函数继续这个过程直到得到正确的收益率或者已经迭代 20 次 如果在输入 RATE 函数后得到错误值 #NUM!, 则 Excel 也许不能在 20 次迭代内求得收益率 选择一个不同的 guess 参数为函数提供一个运行起点 (6) IRR 函数 IRR 函数是计算一组现金流的内部收益率 这些现金流必须按固定的间隔发生, 如按月或按年 其语法如下 : IRR(values, guess) 其中,values 为数组或包含用来计算内部收益率的数字单元格的引用 允许只有一个 values 参数, 它必须至少包括一个正数值和负数值 IRR 函数忽略文字 逻辑值和空白单元格 IRR 函数根据数值的顺序来解释现金流的顺序, 故应确定按需要的顺序输入了支付和收入的数值 guess 参看 RATE 函数 IRR 函数很近似于 RATE 函数 RATE 函数和 IRR 函数的区别类似于 PV 函数和 NPV 函数的区别 与 NPV 函数一样,IRR 函数考虑了投资费用和不等支付额的问题, 故其应用范围更广一些 例如, 要进行一项 120,000 的投资, 并预期今后 5 年的净收益为 和 建立一个包含投资和收入信息的简单工作表 在工作表的 A1:A6 单元格中分别输入以下数值 : 和 然后输入以下公式 : =IRR(Al:A6) 计算此项投资在 5 年后的内部收益率, 返回值为 11% 以下公式 : =IRR(Al:A5) 计算此项投资 4 年后的内部收益率, 返回值为 2% 以下公式 : =IRR(Al:A4,-10%) 计算此项投资 3 年后的内部收益率, 并由 -10% 的利率开始算起, 返回值为 -14% 62

71 第 2 章 Excel 进阶 2. 日期与时间函数在 2.2 节中提到过日期与时间的运算,Excel 中将日期和时间记录为序列数 下面简单介绍常用的日期与时间函数 (1) NOW 函数 NOW 函数返回计算机的系统日期和时间所对应的日期 时间序列数 其语法如下 : NOW( ) 例如, 当前日期为 1999 年 6 月 14 日 20:52, 如果正在使用的是 1900 日期系统, 则以下公式 : =NOW( ) 返回 , 如果单元格的格式为日期格式, 则显示 :52 10 分钟后 NOW 函数将返回 , 如果单元格的格式为日期格式, 则显示以下结果 : :02 (2) TODAY 函数 TODAY 函数返回当前日期的序列数, 该函数与 NOW 函数的作用一样, 只是不返回序列数的时间部分 工作表打开或者重新计算时 Excel 会更新序列数 其语法如下 : TODAY( ) (3) DATE 函数 DATE 函数返回某一特定日期的序列数 其语法如下 : DATE(year, month, day) 其中,year 为年 ;month 为月份, 如果输入的月份大于 12, 将从指定年份的一月份开始往上累加 ;day 为在该月份中第几天的数字, 如果 day 大于该月份的最大天数, 将从指定月份的第一天开始往上累加 函数 DATE 在那些年份 月份和日期由公式计算而非常数的公式中非常有用 例如, 以下公式 : =DATE(99,5,1) 将返回 , 此序列数对应于 1999 年 5 月 1 日 (4) DATEVALUE 函数 DATEVALUE 函数返回 date text 参数所表示的序列数 它可以将文字表示的日期转换成一个序列数 其语法如下 : 63

72 Excel 在会计和财务中的应用 ( 第四版 ) DATEVALUE(date_text) 其中,date_text 参数可以是 Excel 预定义的任何日期格式 例如, 要返回 1998 年 6 月 1 日的序列数, 可以使用以下公式 : =DATEVALUE(" ") 将返回结果 (5) NETWORKDAYS 函数 NETWORKDAYS 函数返回参数 start_data 和 end_data 之间总的工作天数 计算工作天数时应注意工作日不包括周末和专门指定的假期 其语法如下 : NETWORKDAYS=(start_date,end_date,holidays) 其中,start_date 为起始日期的日期值 ;end_date 为终止日期的日期值 ;holidays 为可选清单, 指需要从工作日历中排除的日期值, 如各种法定假日或自定假日 如果该函数不存在, 运行 安装 程序来加载 分析工具库 安装完毕之后, 必须通过选择 工具 加载宏 命令, 在 加载宏 对话框中选择并启用它 如果任何参数为非法日期值, 函数 NETWORKDAYS 将返回错误值 #NUM! 例如, 要计算 1998 年 3 月 1 日到 1998 年 3 月 19 日的总工作天数 ( 除去节假日和 3 月 8 日 ), 则使用以下公式 : =NETWORKDAYS(DATEVALUE(" "),DATEVALUE(" "),DATEVALUE (" ") 返回值为 14 (6) WEEKDAY 函数 WEEKDAY 函数返回与 serial_number 对应的序列数是星期几 其值为 1~7 之间的整数 其语法如下 : WEEKDAY(serial_number, return_type) 其中,serial_number 为日期 - 时间代码, 它可以是数字, 也可以是文本, 如 30-Apr-1998 或者 35915;return_type 为可选, 用以确定一星期从哪天开始, 默认值由星期日开始为 1, 到星期六为 7; 其值为 2, 则由星期一开始为 1, 到星期日为 7; 其值为 3, 则由星期一开始为 0, 到星期日为 6 例如, 要想知道 1998 年 12 月 25 日是星期几, 可以输入以下公式 : =WEEKDAY(" ", 2) 返回值为 5, 即星期五 (7) EOMONTH 函数 EOMONTH 函数返回 start_date 之前或之后指定月份中最后一天的日期序列数 其语法如下 : 64

73 第 2 章 Excel 进阶 EOMONTH(start_date, months) 其中,start_date 为起始日期的日期值 ;months 为 start_date 之前或之后的月数 如果是正数, 指将来的日期 ; 如果是负数, 指过去的日期 例如, 要想知道这个月底的序列数, 可以使用以下公式 ( 设今天为 1998 年 10 月 1 日 ): =EOMONTH (TODAY( ),0) 返回值为 或 10/31/98 3. 数学与三角函数数学与三角函数是工作表中大部分计算的基础, 特别是在它当中可以找到大多数科学与过程函数 (1) SUM 函数 SUM 函数用于计算一系列数字之和 其语法如下 : SUM(number1, number2, ) 其中,number1,number2, 为 1~30 个需要求和的参数, 它们可以是数字 公式 范围或者产生数字的单元格引用 SUM 函数忽略数组或引用中的空白单元格 逻辑值和文本 如果参数为错误值或为不能转换成数字的文本, 将会导致错误 例如, 以下公式 : =SUM(13, 12) 返回值为 25 而以下公式 : =SUM("13", 22, TRUE) 返回值为 36, 因为文本值被转换成数字, 而逻辑值 TRUE 被转换成数字 1 如果单元格 A1 中为 TEXT, 公式如下 : =SUM(13, 22, A1) 返回值为 35, 因为 Excel 忽略了文本 如果单元格 A1:A5 包含 和 50, 则以下公式 : =SUM(A1:A3) 返回值为 60 =SUM(A1:A5, 100) 返回值为

74 Excel 在会计和财务中的应用 ( 第四版 ) (2) ROUND 函数 ROUND 函数将参数引用的数舍入到指定的小数位数 其语法如下 : ROUND(number, num_digits) 其中,number 为需要进行舍入的数值 包含数值的单元格引用或者结果为数值的公式 ;num_digits 为舍入的位数, 可以为任意整数 当它为负数时将舍入小数点左边的位数 ; 当它为 0 时将舍入最近的整数 在 Excel 中, 下舍小于 5 的数字, 上入大于或等于 5 的数字 如表 2-6 所示的是 ROUND 函数的几个例子 表 2-6 ROUND 函数返回值输入项返回值 =ROUND( ,-2) 100 =ROUND( ,-1) 120 =ROUND( ,0) 123 =ROUND( ,1) =ROUND( ,2) =ROUND( ,3) (3) EVEN 函数 EVEN 函数返回沿绝对值增大方向取整后最接近的偶数 其语法如下 : EVEN(number) 其中,number 为要取整的数值 如果 number 为非数值参数, 则 EVEN 函数返回错误值 #VALUE! 不论 number 的正负号如何, 函数都向远离零的方向舍入, 如果 number 恰好是偶数, 则无须进行任何舍入处理 如表 2-7 所示的是 EVEN 函数的几个例子 表 2-7 EVEN 函数返回值输入项返回值 =EVEN(23.4) 24 =EVEN(2) 2 =EVEN(3) 4 =EVEN(-3) -4 (4) PI 函数 PI 函数返回常量 π 的精确到 14 个小数位的数值 : 其语法如下 : 66

75 第 2 章 Excel 进阶 PI ( ) PI 函数没有参数, 通常嵌套在公式或其他函数中 例如, 要计算圆的面积, 可以用 π 乘以圆半径的平方 公式如下 : = PI ( ) * (5^2) 计算半径为 5 的圆的面积 结果舍入到两个小数位后是 (5) RADIANS 函数 RADIANS 函数用来将角度转换为弧度 三角函数是以弧度而不是角度来度量角度的 弧度根据常量 π 计算角度的大小, 其中 180º 定义为 π 弧度 其语法如下 : RADIANS(angle) 其中,angle 为用度数度量的角度 例如, 以下公式 : =RADIANS(180) 返回值为 (6) SIN 函数 SIN 函数返回角度的正弦值 与之类似的有求余弦值的 COS 函数, 求正切值的 TAN 函数 SIN 函数的语法如下 : SIN(angle) 其中,angle 为以弧度度量的角度 如果已知角度要求其正弦值, 可以将角度乘以 π/180, 或者用 RADIANS 函数转换成弧度 例如, 要计算 30 的正弦值, 可以使用以下公式 : =SIN (30*PI ( )/180) 或者 =SIN (RADIANS(30)) 返回值为 0.5 (7) ASIN 函数 ASIN 函数返回角度的反正弦值 与之类似的有求反余弦值的 ACOS 函数, 求反正切值的 ATAN 函数 ASIN 函数的语法如下 : ASIN{number} 其中,number 为角度的正弦值, 介于 -1~1 之间 ASIN 函数总是返回 -π/2~π/2 之间的一个弧度值 67

76 Excel 在会计和财务中的应用 ( 第四版 ) 例如, 以下公式 : =ASIN(0.5) 返回值为 , 即为 π/6 4. 统计函数统计函数可以帮助用户处理一些简单的问题, 如计算平均值 计算某些项目的个数等 统计函数还可以进行一些简单的统计分析, 如标准偏差 方差等 (1) AVERAGE 函数 AVERAGE 函数返回参数平均值 ( 算术平均值 ) 其语法如下 : AVERAGE(number1, number2, ) 其中,number1,number2, 为要计算平均值的 1~30 个参数, 参数可以是单个值或者范围, 范围包括数字 单元格引用或者包含数字的数组 AVERAGE 函数忽略文本 逻辑值或空单元格 例如,C12:C15 单元格中分别是以下的数值 : , 公式如下 : =AVERAGE(C12:C15) 返回值为 3.5 而以下公式 : =AVERAGE(C12:C15,11) 返回值为 5 (2) COUNT 函数 COUNT 函数返回参数中数字的个数 其语法如下 : COUNT (value1, value2, ) 其中,value1,value2, 为要计算数字个数的 1~30 个参数, 参数可以是单个值或者范围, 范围包括数字 单元格引用或者包含数字的数组 COUNT 函数忽略文本 逻辑值或空单元格, 只计算数字类型的数据个数 如果要统计逻辑值 文字或错误值, 则使用函数 COUNTA 例如,A6:A9 分别是 , 则以下公式 : =COUNT (A6:A9) 返回值为 4 如果 A8 为空白单元格, 则该公式返回值为 3 (3) COUNTA 函数 COUNTA 函数返回参数中非空白值的个数 其语法如下 : COUNTA (value1, value2, ) 68

77 第 2 章 Excel 进阶 其中,value1,value2, 为要计算非空白值个数的 1~30 个参数, 参数可以是单个值或者范围, 范围包括数字 单元格引用或者包含数字的数组 COUNTA 函数忽略数组或者单元格引用中的空单元格 例如,B14 是唯一的空单元格, 则以下公式 : =COUNTA (B1:B15) 返回值为 14 (4) STDEV 函数 STDEV 函数是计算某一样本的标准偏差 其语法如下 : STDEV (number1, number2, ) 其中,number1, number2, 为对应于总体样本的 1~30 个参数 STDEV 函数忽略逻辑值 (TRUE 或 FALSE) 和文本 如果不能忽略逻辑值和文本, 应使用 STDEVA 函数 如果数据代表全部样本总体, 则应该使用 STDEVP 函数来计算标准偏差 例如, 要计算在 B5:B14 中样本值 的标准偏差, 利用以下公式 : =STDEV(B5:B14) 返回值为 (5) DEVSQ 函数 DEVSQ 函数返回数据点与各自样本均值偏差的平方和 其语法如下 : DEVSQ(numberl, number2, ) 其中,numberl,number2 等参数参考 STDEV 函数 例如, 要计算在 A2:A11 中样本值 的均值偏差的平方和, 利用以下公式 : =DEVSQ(A2:A11) 返回值为 (6) MAX 函数 MAX 函数返回参数中的最大值 其语法如下 : MAX(number1, number2, ) 其中,number1,number2, 为需要找出最大数值的 1~30 个数值 参数可以是单个值或者范围, 范围包括数字 单元格引用或者包含数字的数组 MAX 函数忽略文本 逻辑值或空单元格, 只考虑数字类型的数据大小 如果逻辑值和文本不能忽略, 可使用函数 69

78 Excel 在会计和财务中的应用 ( 第四版 ) MAXA 来代替 ; 如果参数不包含数字,MAX 函数将返回 0 例如, 单元格 C1:D3 包含数字 , 则以下公式 : =MAX(Cl:D3) 返回值为 查找及引用函数当用户需要确定单元格内容 范围或者选择的范围时, 查找及引用函数显得非常有用 (1) ADDRESS 函数 ADDRESS 函数返回指定的单元格引用, 结果用文本形式来表示 其语法如下 : ADDRESS(row_num, column_num, abs_num, A1, sheet_text) 其中,row_num 为单元格引用中的行号 ;column_num 为单元格引用中的列号 ;abs_ num 用以指定引用类型, 默认值为 1, 即表示绝对引用 ; 当其为 2 时, 表示绝对行, 相对列 ; 当其为 3 时, 表示相对行, 绝对列 ; 当其为 4 时, 表示相对引用 ;A1 用以指明引用样式, 默认值为 TRUE, 即返回 A1 形式的引用, 如果其为 FALSE, 即返回 R1C1 形式的引用 ;sheet_text 是文本, 指明作为外部引用的工作表名, 如果省略, 则不使用任何工作表名 例如, 以下公式 : =ADDRESS(15, 4, 2, TRUE) 返回值为 D$l5 而以下公式 : =ADDRESS(10, 5, 4, FALSE) 返回值为 R10C5 (2) VLOOKUP 函数 VLOOKUP 函数用于查找所构造的表格中存放的信息 当在表格中查找信息时, 一般用行索引或者列索引来定位特定单元格 Excel 在利用这种方式时做了一些变动, 即通过查找第一列中小于或者等于用户所提供的最大值来得到一个索引, 然后用另一指定参数作为其他索引 这样可以根据表格中的信息查找数值, 而不必确切地知道数值在哪里 其语法如下 : VLOOKUP(lookup_value, table_array, col_index_num, range lookup) 其中,lookup_value 为要在表格中查找以得到第一个索引的数值, 它可以为数值 引用或文字串 ;table_array 为定义表格的数组或者范围名称, 其第一行的数值可以为文本 数字或逻辑值 ;col_index_num 为开始选择结果的表格列 ( 第二个索引 ), 当其值为 1 时, 返 70

79 第 2 章 Excel 进阶 回 table_array 第一列的数值, 当其值为 2 时, 返回 table_array 第二列的数值, 以此类推 如果其值小于 1,VLOOKUP 函数返回错误值 #VALUE! ; 如果其值大于 table_array 的列数,VLOOKUP 函数返回错误值 #REF! ;range_lookup 为一逻辑值, 指明函数 VLOOKUP 查找时是精确匹配还是近似匹配, 其默认值为 TRUE, 此时函数返回近似匹配值, 如果其为 FALSE,VLOOKUP 函数将查找精确匹配值, 如果找不到, 则返回错误值 #N/A! 例如, 要在图 2-91 所示的表格中检索数据, 以下公式 : =VLOOKUP(42, A2:C6, 3) 图 2-91 要检索数据的表格 返回值为 24 其检索过程如下 : 首先找到包含比较值的列, 这里为列 A, 然后扫描比较值查找小于或者等于 lookup value 的最大值 由于第 4 个比较值 40 小于 41, 而第 5 个比较值 50 又大于 41, 因此以包含 40 的行 ( 即行 5) 作行索引 列索引是 col_index_num 参数, 这里是 3, 因此列 C 中包含所要的数据 由此可以得到单元格 C5 中的数值 24 (3) HLOOKUP 函数 HLOOKUP 函数在表格或数值数组的首行查找指定的数值, 并由此返回表格或数组当前列中指定行处的数值 HLOOKUP 的用法可以参看 VLOOKUP 其语法如下 : HLOOKUP(lookup_value, table_array, col_index_num, range_lookup) (4) OFFSET 函数 OFFSET 函数返回具有指定高度和宽度, 位于相对于另一个引用的指定位置的引用 其语法如下 : OFFSET(reference, rows, cols, height, width) 其中,reference 为计算偏移的起点位置 ;rows 指定 reference 参数和被返回引用之间的垂直距离, 正值指定相对 reference 参数向下偏移 ;cols 指定 reference 参数和被返回引用之间的水平距离, 正值指定相对 reference 参数向右偏移 ;height 为高度, 即所要返回的引用区域的行数, 它必须为正数, 如果省略则以 reference 参数的高度为高度 ;width 为宽度, 即所要返回的引用区域的列数, 它必须为正数, 如果省略则以 reference 参数的宽度为宽度 例如, 以下公式 : =OFFSET(A3:C5, -1, 0, 3, 3) 71

80 Excel 在会计和财务中的应用 ( 第四版 ) 将返回 A2:C4 单元格引用 一般 OFFSET 函数不单独使用, 它是与需要将引用作为参数的函数连用, 如以下公式 : =SUM(OFFSET(A3:C5, -1, 0, 3, 3)) 将返回 A2:C4 单元格区域数值之和 (5) INDIRECT 函数 INDIRECT 函数可以从单元格引用找到单元格的内容 其语法如下 : INDIRECT(ref_text, A1) 其中,ref_text 为一个 A1 形式的引用 R1C1 形式的引用或者单元格名称, 如果它的输入项无效, 则函数返回错误值 #REF! ;A1 为一个逻辑值, 指示使用的是哪一种引用的类型 如果其值为 FALSE, 则 Excel 将其解释为 R1C1 格式 ; 如果其值为 TRUE( 默认值 ), 则 Excel 将其解释为 A1 格式 例如, 工作表的单元格 C5 包含文本 B2, 而单元格 B2 中为数值 17, 则以下公式 : =INDIRECT(C5) 返回值为 17 如果工作表设置为 R1C1 格式的引用, 而且单元格 R5C3 包含单元格 R2C2 的引用, 单元格 R2C2 中为数值 17, 则以下公式 : =INDIRECTT(R5C3, FALSE) 返回值为 17 (6) INDEX 函数 INDEX 函数返回指定范围中特定行与特定列交叉点上的单元格引用, 其语法如下 : INDEX(reference, row_num, column_num, area_num) 其中,reference 指定一个或多个区域的引用, 如果指定多个区域, 则必须用括号括起来, 区域之间要用逗号隔开 ;row_num 指定引用中的行序号 ;column_num 指定引用中的列序号 ;area_num 指定 reference 所确定的几个区域中的某个, 其默认值为 1 例如, 以下公式 : =INDEX((D5:F9, D10:F14), 1, 2, 2) 返回区域 D10:F14 中第 1 行 第 2 列的单元格引用 6. 数据库函数 Excel 中各个数据库都使用同样的参数 : 数据库 (database) 字段(field) 和条件 (criteria) 函数 DAVERAGE 中讨论的参数说明适用于所有的数据库函数 72

81 第 2 章 Excel 进阶 (1) DAVERAGE 函数 DAVERAGE 函数计算满足查询的数据库记录中给定字段内数值的平均值 其语法如下 : DAVERAGE(database, field, criteria) 其中,database 为构成数据清单或数据库的单元格区域, 它可以是一个范围或者一个范围引用的名称 ;field 指定函数所使用的数据列 它可以是引号中的字段名, 包含字段名的单元格引用或者是数字 ;criteria 为对一组单元格区域的引用 这组单元格区域用来设定函数的匹配条件 数据库函数可以返回数据清单中与条件区域所设定的条件相匹配的信息 条件区域包含了函数所要汇总的数据列 ( 即 field) 在数据清单中的列标志的一个副本 例如, 如图 2-92 所示的工作表, 单元格 A1:C13 为数据库区域, 要统计收入大于 元的收入平均值, 在区域 E3:G4 上输入如图 2-92 中所示的条件, 使用以下公式 : =DAVERAGE(A1:C13, " 收入 ", E3) 将得到收入平均值为 图 2-92 数据库区域 (2) DCOUNT 函数 DCOUNT 函数用于计算数据库中给定字段满足条件的记录数, 其语法如下 : DCOUNT(database, field, criteria) 其中, 参数 field 为可选项, 如果省略,DCOUNT 函数将返回数据库中满足条件 criteria 的所有记录数 ; 其他参数参考 DAVERAGE 函数 例如, 图 2-92 所示的数据库区域, 要统计收入大于 元的记录数, 可以使用以下公式 : =DCOUNT(A3:C15, E3: G4) 返回值为 9 (3) DSTDEVP 函数 DSTDEVP 函数将数据清单或数据库的指定列中满足给定条件单元格中的数字作为 73

82 Excel 在会计和财务中的应用 ( 第四版 ) 总体样本计算其标准偏差 其语法如下 : DSTDEVP(database, field, criteria) 其中各参数与 DAVERAGE 函数相同 例如, 如图 2-87 所示的数据库区域, 要计算收入大于 元的样本的标准偏差, 可以使用以下公式 : =DSTDEVP(A3:C15, " 收入 ", E3:G4) 返回值为 (4) DMAX 函数 DMAX 函数将返回数据库中满足条件的记录中给定字段的最大值, 其语法如下 : DMAX(database, field, criteria) 其中各参数与 DAVERAGE 函数相同 例如图 2-87 所示的数据库区域, 要查找收入大于 元的记录中支出的最大值, 可以使用以下的公式 : =DMAX(A3:C15," 支出 ",E3:G4) 返回值为 文本函数文本函数又称为字符串函数, 对于处理转化到 ASCII 文件的文本以及要装载到主机的文本, 都是非常重要的 (1) CONCATENATE 函数 CONCATENATE 函数是返回将给出的几个字符串合并的一个字符串 其语法如下 : CONCATENATE(Text1, Text2, ) 其中,Text1,Text2, 为 1~30 个将要合并成单个字符串的文本 这些文本可以是字符串 数字或单个单元格引用 例如, 以下公式 : =CONCATENATE("Welcome", "President! ") 将返回合成字符串 Welcome President 又如图 2-92 所示的工作表, 以下公式 : =CONCATENATE(" 今年 ", A8, " 的 ", B3, " 为 ", B8, " 元 ") 将返回今年五月的收入为 元 74

83 第 2 章 Excel 进阶 (2) VALUE 函数 VALUE 函数将以文本形式输入的数字转换成数值 其语法如下 : VALUE(text) 其中,text 为括在双引号内的字符串, 也可以是包含文字的单元格引用 它可以是任何可识别的格式, 包括自定义的格式 如果它不是其中的任何一种格式,VALUE 函数将返回错误值 #VALUE! 例如, 以下公式 : =VALUE("13425") 返回值为 , 如果单元格 B5 中为文本 , 则以下公式 : =VALUE(B5) 返回值为 VALUE 函数还可以将日期和时间格式的文本转换为日期值, 例如, 以下公式 : =VALUE(" ") 将返回日期系列值 (3) FIXED 函数 FIXED 函数将数字四舍五入到指定的小数位数, 用逗号和一个圆点来格式化结果, 并以文本形式显示结果 其语法如下 : FIXED(number, decimals, no_commas) 其中,number 为要转换成字符串的数 ;decimals 为一整数, 当其为正值时指定小数点右边的位数, 为负值时指定小数点左边的位数 ;no_commas 为逻辑值, 用于指定结果中是否要包括逗号, 其默认值为 FALSE, 即在结果中插入逗号 例如, 以下公式 : =FIXD( ,2, TRUE) 将返回字符串 , 而以下公式 : =FIXD( , -l, FALSE) 将返回字符串 (4) LEN 函数 LEN 函数返回输入项中的字符个数, 其语法如下 : LEN (text) 75

84 Excel 在会计和财务中的应用 ( 第四版 ) 其中,text 为要计算字符个数的字符串, 它可以是括在括号里的文本, 也可以是单元格引用 例如, 以下公式 : =LEN("text") 返回值为 4 如果单元格 B5 中包含字符串 text, 则以下公式 : =LEN(B5) 返回值为 4 LEN 函数返回显示文字或者数值的长度, 而不是基本单元格内容的长度 例如, 单元格 B5 中公式如下 : =B1+B2+B3+B4 其计算结果为 98, 则以下公式 : =LEN(B5) 将返回数值 98 的长度 2 (5) REPLACE 函数 REPLACE 函数用某一文字串替换另一个字符串中的全部或者部分内容 其语法如下 : REPLACE(old_text, start_num, num_chars, new_text) 其中,old_text 为被替换的字符串 ;start_num 为 old_text 中要替换为 new_text 字符的起始位置 ;num_chars 为 old_text 中要替换为 new_text 字符的个数 ;new_text 为用于替换 old_ text 字符的字符串 例如, 单元格 A5 中为字符串 Hello,Kitty!, 要将其放到单元格 B1 中, 并用字符串 Windy? 来替换其中的 Kitty, 则选择 B1 单元格, 然后使用以下公式 : =REPLACE(A5, 7, 5, "Kitty") 返回结果为 Hello,Windy! (6) REPT 函数 REPT 函数将指定字符串重复指定次数作为新字符串填充单元格 其语法如下 : REPT(text,number_times) 其中,text 指定要重复的字符串 ;number_times 为重复的次数, 它可以是任意整数, 但重复的结果不能超过 255 个字符, 如果其值为 0, 则 REPT 函数保持单元格为空白, 如果它不是整数, 则忽略其小数部分 例如, 要想得到 100 个 -, 可以使用以下公式 : =REPT("-",100) 76

85 第 2 章 Excel 进阶 返回结果是一个由 100 个 - 组成的字符串 (7) SEARCH 函数 SEARCH 函数返回一个指定字符或者字符串首次出现在另外一个字符串中的起始位置, 其语法如下 : SEARCH(find_text, within_text, start_num) 其中,find_text 为要查找的字符串, 可以在其中使用通配符问号? 和星号 * 问号可以匹配任何单个字符 ; 星号匹配任何字符序列 如果要查找实际的问号或星号, 那么应在该字符前加一个代字符 (~) 如果找不到 find_text, 函数返回错误值 #VALUE! ; within_text 为被查找的字符串 ;start_num 为开始查找的位置, 默认值为 1, 从左边开始搜索, 如果其值为小于等于 0 或大于 within_text 的长度, 则返回错误值 #VALUE! 例如, 以下公式 : =SEARCH("here", "Welcome here!") 返回值为 9 而以下公式 : =SEARCH("a?d", "Welcome here, ladies and gentlemen! ") 返回值为 逻辑函数逻辑函数是功能强大的工作表函数, 它可以使用户对工作表结果进行判断和逻辑选择 (1) IF 函数 IF 函数返回根据逻辑测试真假值的结果 它可以对数值和公式进行条件检测 其语法如下 : IF(logical_test, value_if_true, value_if_false) 其中,logical_test 为逻辑值, 它可以是 TRUE 或者 FALSE, 也可以是计算结果为 TRUE 或 FALSE 的任何数值或表达式 value_if_true 是 logical_test 为 TRUE 时函数的返回值, 可以是某一个公式 如果 logical_test 为 TRUE 并且省略 value_if_true, 则返回 TRUE Value_if_false 是 logical_test 为 FALSE 时函数的返回值, 可以是某一个公式 如果 logical_ test 为 FALSE 并且省略 Value_if_false, 则返回 FALSE IF 函数最多可以嵌套 7 层, 方法是用 value_if_true 及 value_if_false 参数构造复杂的检测条件 例如, 判断单元格 B5 中的数值是否小于 60, 返回 FAIL!, 否则返回 PASS!, 可以使用以下公式 : 77

86 Excel 在会计和财务中的应用 ( 第四版 ) =IF(B5<60,"FAIL! ","PASS! ") 如果还要对 PASS 的情况细分等级, 即 60~85 为 FINE!,85 及以上为 EXCELIENT!, 可以使用以下嵌套公式 : =IF(B5<60,"FAIL! ",IF(B5<85,"FINE! ","EXCELLENT! ")) 这样即可得到所需的等级 (2) AND 函数 AND 函数是判断所有参数的逻辑值是否为真, 是则返回 TRUE, 否则只要有一个逻辑值为假即返回 FALSE 其语法如下 : AND(logical1, logical2, ) 其中,logical1,logical2, 为 1~30 个逻辑值参数, 各逻辑值参数可以为单个逻辑值 TRUE 或 FALSE, 也可以是包含逻辑值的数组或者单元格引用 如果数组或者单元格引用中包含文字或空单元格, 则忽略其值 如果指定的单元格区域内包括非逻辑值,AND 将返回错误值 #VALUE! 例如, 要判断单元格 B10 中的数值是否大于 5 而且小于 10, 可以使用以下公式 : =AND(B10>5, B10<10) 则当 B10 单元格的数值大于 5 而且小于 10 时返回 TRUE, 否则返回 FALSE (3) NOT 函数 NOT 函数对给定参数的逻辑值求反 其语法如下 : NOT(logical) 其中,logical 是一个逻辑值参数, 可以是单个逻辑值 TRUE 或 FALSE 或者是逻辑表达式 如果逻辑值为 FALSE, 函数返回 TRUE; 如果逻辑值为 TRUE, 函数返回 FALSE 例如, 以下公式 : =NOT(B5=10) 在 B5 单元格数值等于 10 时返回 FALSE, 否则返回 TRUE (4) OR 函数 OR 函数判断给定参数中的逻辑值是否为真, 只要有一个为真即返回 TRUE, 如果全部为假, 则返回 FALSE 其语法如下 : OR(logical1, logical2, ) 其中,logical1,logical2, 与 AND 函数相同 78

87 第 2 章 Excel 进 阶 例如 要判断单元格 C6 中是否为 10 或者 20 可以使用以下公式 =OR(C6=10, C6=20) 当单元格中是 10 或 20 时返回 TRUE 否则返回 FALSE 2.4 本章小结 本章深入介绍了单元格和工作表编辑的各种操作方法 重点介绍了对单元格和工作 表设置和格式化 同时 对 Excel 中的计算功能 公式与函数进行了深入的介绍 通过 本章的学习 读者应熟练各种单元格的编辑操作 掌握单元格的命名规则 能够调整工 作表的行高和列宽 使不同单元格的数据都能显示在工作表上 熟悉工作表的各种操作 包括插入 删除 移动 复制 重命名和隐藏工作表 会利用这些操作管理好工作表 熟练使用打印预览功能 在实际打印之前先进行打印预览 确保实际打印的准确性以减 少出错 掌握公式的各种基本概念及公式的基本操作 包括建立公式 修改公式 公式 的移动和复制 掌握什么是函数及其语法 了解 Excel 函数的使用方法 2.5 思考练习 1. 选择题 (1) 在 Excel 中编辑单元格的内容的方法有( ) A. 通过编辑栏进行编辑 B. 在单元格中直接进行编辑 C. 一旦输入就无法编辑 (2) 在 Excel 中插入空的单元格的操作( ) A. 选定要插入单元格的位置 B. 选择 插入 单元格 命令 或者在选中的单元格上右击 在弹出的快 捷菜单中选择 插入 命令 C. 从打开的 插入 对话框中选择插入方式 并单击 确定 按钮 (3) 在 Excel 中可以自动完成的操作有( A. 排序 ) B. 填充 C. 求和 (4) 下列选项在 Excel 中可被当做公式的有( A. =10*2/3+4 ) B. =SUM(A1:A3) C. =B5&C6 (5) 在 Excel 中正确地引用其他工作簿中单元格的操作是( A. =SUM(Al:A3) B. =SUM(Sheet31B6:B8) 79 )

88 Excel 在会计和财务中的应用 ( 第四版 ) C. =SUM('C:\MYDOCUMENTS\[ 工作簿 1.xls]Sheet1'!$B$3:$B$4) (6) 在 Excel 中, 公式 =SUM("13", 22, TRUE) 得到的结果是 ( ) A. 0 B. 1 C 填空题 (1) 要将某些行或列隐藏起来, 先选定要隐藏行的行号区数字或列的列号区字母, 然后选择 命令, 接着在弹出的子菜单中选择 命令, 即可把行或列隐藏起来 (2) 是工作簿的基本对象的核心, 也是组成 Excel 工作簿的最小单位 (3) Excel 提供的 功能可以将工作表中选定的单元格的上窗格和左窗格冻结在屏幕上, 从而使得在滚动工作表时屏幕上一直显示行标题和列标题, 使用户能够将表格标题和数据相对应的看清楚, 而且使用冻结工作表窗口不影响打印 (4) 函数可求一系列数字之和 ; 函数返回参数平均值 ( 算术平均值 ); 函数返回一个指定字符或者字符串首次出现在另外一个字符串中的起始位置 ; 函数返回根据逻辑测试真假值的结果 (5) 如果在某一区域内输入同一个公式, 逐个输入显然太慢了 这时可以选中该单元格区域, 输入所需要的公式, 然后按 键, 则 Excel 会自动将所有单元格都粘贴上该输入公式 这不仅对公式有效, 而且对其他文本和字符都有效 (6) Excel 提供了 3 种不同的引用类型 : 和 3. 上机操作题 (1) 设计一个班级成绩表, 对其中的数据进行条件格式操作 操作提示 : 1 设计成绩表 2 在条件格式对话框中进行设置 3 要求用红色显示小于 60 分的单元格中的数据 (2) 求 10 个 0~100 之间的整数的和 平均数和最大值 操作提示 : 1 输入 10 个整数 2 用 SUM 函数求和 3 用 AVERAGE 函数求平均数 4 用 MAX 函数求最大值 80

89 第 3 章 Excel 高级应用 本章主要对 Excel 的常用高级功能进行分析讲解, 读者可以通过本章的学习全面了解 Excel 功能的高级应用 本章的学习目标 数据管理与分析 Excel 的图表 3.1 数据管理与分析 Excel 在数据管理方面提供了强大的功能 本节将从介绍如何获取数据入手, 讲解如何编辑分析工作表中的数据 如何对数据进行汇总计算以及如何运用数据透视表等知识 建立数据列表 准备对工作表中的数据进行管理, 首先要建立数据列表 可以通过两种途径建立 : 一种是将数据区域转化为表, 直接建立数据列表 ; 另一种是记录单功能 1. 建立数据列表 数据列表即常说的表格, 在 Excel 中, 用户只要是执行了数据库命令,Excel 会自动将数据列表默认为是一个数据库, 数据列表中的每一行则对应数据库中的一条记录 建立数据列表, 具体操作步骤如下 (1) 打开 插入 选项卡, 在 表格 选项组中单击如图 3-1 所示的 表格 按钮 (2) 弹出 创建表 对话框, 在该对话框的 表数据的来源 文本框中输入准备创建列表的单元格 图 3-1 单击 表 按钮

90 Excel 在会计和财务中的应用 ( 第四版 ) 区域 $A$2:$F$10, 如图 3-2 所示 (3) 单击 确定 按钮, 完成创建列表, 效果如图 3-3 所示 图 3-2 创建表 对话框 图 3-3 创建列表完成 2. 使用记录单数据清单, 就是包含相关数据的一系列工作表数据行, 若干数据清单组成数据列表 当对工作表中的数据进行操作时,Excel 会将数据清单当成数据库来处理 对数据清单的各种编辑操作经常要用到记录单 由于 Excel 2010 将记录单命令设为自定义命令, 使用该命令时, 用第 1 章介绍的自定义 Excel 的方法先将记录单命令调出 (1) 使用记录单记录单可以提供简捷的方法在数据清单中一次输入或显示一个完整的信息行, 即记录 在使用记录单向新数据清单添加记录时, 这个数据清单在每一列的最上面必须有标志 使用记录单的具体操作步骤如下 1 单击需要添加记录的数据清单中的任意一个单元格, 如图 3-4 所示 2 单击 记录单 按钮, 打开记录单对话框, 如图 3-5 所示 图 3-4 添加记录的数据清单 图 3-5 记录单对话框 3 单击 新建 按钮, 新建记录单 4 输入新记录所包含的信息 如果要移到下一字段, 可以按 Tab 键 ; 如果要移到上一字段, 可以按 Shift+Tab 快捷键 完成数据输入后, 按 Enter 键继续添加记录 5 完成记录的添加后, 单击 关闭 按钮完成新记录的添加并关闭记录单 (2) 修改记录使用记录单不但可以在工作表中为数据清单添加数据, 还可以对数据清单进行修改 具体操作步骤如下 82

91 第 3 章 Excel 高级应用 1 单击需要修改的数据清单中的任意一个单元格 2 单击 记录单 按钮, 打开 记录单 对话框 3 单击 下一条 按钮或者 上一条 按钮找到需要修改的记录, 然后在记录中对数据进行修改, 如图 3-6 所示 4 记录修改完成后, 单击 关闭 按钮更新当前显示的记录并关闭记录单, 完成工作表中数据的修改, 如图 3-7 所示 图 3-6 对数据进行修改 图 3-7 修改后的工作表 (3) 删除记录使用记录单在数据清单中删除记录和修改记录的方法类似 具体操作步骤如下 1 单击需要删除的数据清单中任一单元格 2 单击 记录单 按钮, 打开记录单对话框 3 单击 下一条 按钮或者 上一条 按钮找到需要删除的记录 4 单击 删除 按钮, 系统将弹出如图 3-8 所示的消息提示对话框 图 3-8 删除记录时的提示 5 在该对话框中单击 确定 按钮, 即可删除被选中的记录 6 单击 记录单 对话框中的 关闭 按钮关闭该对话框 (4) 查找记录当数据清单比较大时, 要找到数据清单中的记录就不容易了 记录单提供了快速查找数据清单记录的功能 如果需要每次移动一条记录, 可以单击 记录单 对话框中的滚动条箭头 ; 如果需要每次移动 10 条记录, 单击滚动条与下箭头之间的空白 使用记录单可以对数据清单中的数据设置查找条件, 所设置的条件通常为比较条件 查找记录具体操作步骤如下 1 单击数据清单中的任意一个单元格 2 选择 数据 记录单 命令, 打开 记录单 对话框 3 单击 条件 按钮, 然后在记录单中输入查找条件 83

92 Excel 在会计和财务中的应用 ( 第四版 ) 4 单击 上一条 按钮或者 下一条 按钮进行查找, 可以按顺序找到符合查找条件的记录 如果要在找到符合指定条件的记录之前退出搜索, 可以单击 表单 按钮 5 找到记录后, 可以对记录进行各种编辑操作 操作完毕后或者查找中想要退出 表单 对话框时, 直接单击 关闭 按钮即可 数据排序 在工作表或者数据清单中输入数据后, 一般需要进行排序操作, 以便更加直观地比较各个记录 1. 默认的排序顺序在进行排序之前, 首先来介绍 Excel 中数据的排序是按照怎样的规则进行的 在对数据进行排序时,Excel 有默认的排序顺序 按升序排序时,Excel 的排序规则如下 ( 在按降序排序时, 除了空格总是在最后外, 其他的排序顺序反转 ) (1) 数字从最小的负数到最大的正数排序 (2) 文本以及包含数字的文本, 按下列顺序进行排序 : 首先是数字 0~9, 然后是字符 - ( 空格 )!# $ % & ( ) - { } ~ + < = >, 最后是字母 A~Z (3) 在逻辑值中,FALSE 排在 TRUE 之前 (4) 所有错误值的优先级等效 (5) 空格排在最后 2. 简单排序简单排序, 是指对数据列表中的单列数据进行排序 具体操作步骤如下 (1) 在待排序数据列表中单击任一单元格 (2) 选择 数据 排序 命令, 如图 3-9 所示 (3) 弹出如图 3-10 所示的 排序 对话框 图 3-9 数据 排序 命令 图 3-10 排序 对话框 (4) 在该对话框中, 选择 关键字 排序依据 和 次序 选项的内容, 如 84

93 第 3 章 Excel 高级应用 图 3-10 所示 (5) 单击 确定 按钮, 工作表 数学 一列的数字将从小到大排列, 如图 3-11 所示 如果是通过建立工作列表的方式进行数据管理, 则直接单击需要排序的列标题右侧的下拉菜单, 选择准备应用的排序次序即可 如图 3-12 所示 图 3-11 排序后的工作表 图 3-12 列标题右侧的排序下拉菜单 3. 多列排序在根据单列数据对工作表中的数据进行排序时, 如果该列的某些数据完全相同, 则这些行的内容就按原来的顺序进行排列, 这会给数据排序带来一定的麻烦 选择多列排序方式可以解决这个问题, 而且在实际操作中也经常会遇到按照多行的结果进行排序的情况 例如, 足球比赛中是按总积分来排列名次的 往往有一些球队总积分相同, 这时就要通过净胜球来分出名次 多列排序的具体操作步骤如下 (1) 单击需要排序的数据列表中的任意一个单元格 (2) 选择 数据 排序 命令, 弹出 排序 对话框 (3) 在 排序 对话框中, 选择 列 2 作为主要关键字 升序排列 (4) 单击 添加条件 按钮, 选择 列 3 作为次要关键字, 如图 3-13 所示 图 3-13 选择次要关键字 (5) 单击 确定 按钮, 工作表进行了多列排序, 效果如图 3-14 所示 85

94 Excel 在会计和财务中的应用 ( 第四版 ) 图 3-14 多列排序后的工作表 数据筛选 数据筛选, 是指从数据中找出符合指定条件的数据 筛选与排序不同, 它并不重排数据列表, 而只是暂时隐藏不必显示的行 下面介绍各种筛选的方法 1. 自动筛选自动筛选的功能比较简单, 可以很快地显示出符合条件的数据 自动筛选的具体操作步骤如下 (1) 单击需要筛选的数据列表中的任意一个单元格 (2) 选择 数据 筛选 命令, 如图 3-15 所示 (3) 工作表变成如图 3-16 所示的格式, 在每个字段的右边都出现了一个下拉箭头按钮 图 3-15 数据 筛选 命令 图 3-16 带箭头的工作表 提示 : 如果是已经建立好的数据列表, 则不需要步骤 (1) 至步骤 (3) 的操作 (4) 单击列标题右侧的下列箭头, 弹出如图 3-17 所示的下列菜单 (5) 取消选中 全选 复选框, 选中 90 复选框 (6) 单击 确定 按钮, 工作表变成如图 3-18 所示的格式, 仅显示符合筛选条件的数据 如果要清除所有的筛选, 以显示所有行 可以单击 排序与筛选 按钮, 然后选中 清除 即可 86

95 第 3 章 Excel 高级应用 图 3-17 下列菜单 图 3-18 工作表仅显示符合筛选条件的数据 2. 高级筛选使用高级筛选可以对工作表和数据清单进行更复杂的筛选 对于简单的工作表和数据清单来说, 使用高级筛选比较麻烦, 但是, 对于大型的工作表和数据清单是非常有用的 高级筛选的具体操作步骤如下 (1) 单击需要筛选的工作表中的任意一个单元格 (2) 打开 数据 选项卡, 单击 高级 按钮, 如图 3-19 所示 图 3-19 高级 按钮 (3) 弹出如图 3-20 所示的 高级筛选 对话框 选中 在原有区域显示筛选结果 按钮, 在数据区域和条件区域中输入相应内容 ; 也可以通过单击折叠按钮, 在工作表中选定区域, 然后单击 框伸展 按钮来选定数据区域和条件区域 (4) 单击 确定 按钮, 筛选结果如图 3-21 所示 87

96 Excel 在会计和财务中的应用 ( 第四版 ) 图 3-20 高级筛选 对话框 图 3-21 筛选结果 提示 : 进行高级筛选前, 应在筛选区域以外的单元格区域中输入高级筛选的条件, 条件中包括筛选条件和其所在列的列标题 3. 取消筛选对工作表的数据进行筛选后, 工作表中将只显示符号筛选条件的数据, 需要查看其他数据时可以取消筛选 如果要取消自动筛选, 可打开 数据 选项卡, 单击 排序与筛选 选项组中的 清除 按钮即可, 如图 3-22 所示 图 3-22 取消自动筛选 分类汇总数据 分类汇总是将数据按照某一字段进行分类并计算汇总 ( 个数 和 平均值等 ) 通过分类汇总可以方便地分析出各类数据在总数据中所占的位置 例如, 有一个销售数据清单, 数据清单中包含了日期 账户 产品 单位 价格以及收入等项 可以按账户来查看分类汇总, 也可以按产品来查看分类汇总 Excel 可以自动创建公式 插入分类汇总与总和的行并且自动分级显示数据 数据结果可以方便地用来进行格式化 创建图表或者打印 1. 建立分类汇总建立分类汇总的具体操作步骤如下 (1) 在需要分类汇总的工作表中单击任意一个单元格, 如图 3-23 所示 88

97 第 3 章 Excel 高级应用 图 3-23 在要分类汇总的工作表中单击任意一个单元格 (2) 选中 数据 选项卡中的 分级显示 选项组, 单击该选项组中的 分类汇总 按钮, 如图 3-24 所示 图 3-24 单击 分类汇总 按钮 (3) 弹出如图 3-25 所示的 分类汇总 对话框 单击该对话框中的 分类字段 下拉列表框, 从中选择需要分类汇总的数据列, 所选的数据列应已经排序 (4) 单击 汇总方式 下拉列表框, 在下拉列表中选择所需的用于计算分类汇总的函数 (5) 在 选定汇总项 列表框中, 选中与需要对其汇总计算的数值列对应的复选框 (6) 设置 分类汇总 对话框中的其他选项 如果想要替换任何现存的分类汇总, 选中 替换当前分类汇总 复选框 如果想要在每组之前插入分页, 选中 每组数据分页 复选框 如果想在数据组末段显示分类汇总及总和, 选中 汇总结果显示在数据下方 复选框 (7) 单击 确定 按钮, 完成分类汇总操作, 分类汇总后的结果如图 3-26 所示 图 3-25 分类汇总 对话框 图 3-26 分类汇总 结果 89

98 Excel 在会计和财务中的应用 ( 第四版 ) 对数据进行分类汇总后, 如果要查看数据清单中的明细数据或者单独查看汇总总计, 则要用到分级显示的内容 在图 3-26 中, 工作表左上方是分级显示的级别符号, 如果要分级显示包括某个级别, 则单击该级别的数字 如图 3-27 所示显示的为 2 级明细 图 3-27 分级显示分级显示级别符号下方有显示明细数据符号, 单击它可以在数据清单中显示数据清单中的明细数据, 如图 3-28 所示 分级显示级别符号下方还有隐藏明细数据符号, 单击它可以在数据清单中隐藏数据清单中的明细数据, 如图 3-28 所示 图 3-28 显示 / 隐藏明细数据符号 2. 删除分类汇总 对工作表中的数据进行分类汇总后, 如果需要将工作表还原到分类汇总前的状态可以删除工作表的分类工作 删除分类汇总的具体操作步骤如下 (1) 在需要分类汇总的工作表中单击任意一个单元格 (2) 选中 数据 选项卡中的 分级显示 选项组, 单击该选项组中的 分类汇总 按钮, 打开 分类汇总 对话框 (3) 在该对话框中单击 全部删除 按钮, 如图 3-29 所示, 工作表中的分类汇总结果将被清除 图 3-29 全部删除 按钮 90

99 第 3 章 Excel 高级应用 数据透视表 阅读一个具有大量数据的工作表很不方便, 用户可以根据需要, 将这个工作表生成能够显示分类概要信息的数据透视表 数据透视表能够迅速方便地从数据源中提取并计算需要的信息 1. 数据透视表简介数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格 它可以用于转换行和列, 以便查看源数据的不同汇总结果, 可以显示不同页面的筛选数据, 还可以根据需要显示区域中的明细数据 2. 数据透视表的组成数据透视表由报表筛选 列标签 行标签和数值 4 个部分组成 其中各组成部分的功能如下 报表筛选 : 用于基于报表筛选中的选定项来筛选整个报表 行标签 : 用于将字段显示为报表侧面的行 列标签 : 用于将字段显示为报表顶部的列 数值 : 用于显示汇总数值数据 3. 数据源在 Excel 中, 可以利用多种数据源来创建数据透视表 可以利用的数据源如下 Excel 的数据清单或者数据库 外部数据源包括数据库 文本文件或者除了 Excel 工作簿以外的其他数据源, 也可以是 Internet 上的数据源 可以是经过合并计算的多个数据区域以及另外一个数据透视表 4. 建立数据透视表使用数据透视表不仅可以帮助用户对大量数据进行快速汇总, 而且可以查看数据源的汇总结果 假设有如图 3-30 所示的一张数据清单, 现在要以这张数据清单作为数据透视表的数据源来建立数据透视表 具体操作步骤如下 (1) 打开准备创建数据透视表的工作簿 (2) 打开 插入 选项卡, 在 表 组中单击 数据透视表 按钮的下拉箭头, 在该下拉菜单中选择 数据透视表 命令, 如图 3-31 所示 (3) 弹出 创建数据透视表 对话框, 在 请选择要分析的数据 区域单击 表 / 区域 文本框右侧的 压缩对话框 按钮, 如图 3-32 所示 91

100 Excel 在会计和财务中的应用 ( 第四版 ) 图 3-30 企业销售统计表 图 3-31 数据透视表 命令 图 3-32 压缩对话框 按钮 (4) 选择准备创建数据透视表的数据区域, 如图 3-33 所示 图 3-33 选择准备创建数据透视表的数据区域 (5) 单击 创建数据透视表 文本框右侧的 展开对话框 按钮, 返回 创建数据透视表 对话框, 在 选择放置数据透视表的位置 区域中选中 新工作表 选项, 如图 3-34 所示 (6) 单击 确定 按钮, 打开 数据透视表字段列表 任务窗格, 如图 3-35 所示 (7) 在该任务窗格中 选择要添加到报表的字段 区域选择准备设置为 列标签 的字段, 单击并拖动选择的字段到相应的区域中 ; 在该任务窗格中 选择要添加到报表的字段 区域选择准备设置为 行标签 的字段, 单击并拖动选择的字段到相应的区域中 如图 3-36 所示 92

101 第 3 章 Excel 高级应用 图 3-34 创建数据透视表 对话框 图 3-35 数据透视表字段列表 任务窗格 图 3-36 设置 列标签 与 行标签 (8) 在该任务窗格中 选择要添加到报表的字段 区域选择准备设置为 数值 的 字段, 单击并拖动选择的字段到相应的区域中, 如图 3-37 所示, 完成数据透视表的创建 图 3-37 设置 数值, 完成数据透视表的创建 93

102 Excel 在会计和财务中的应用 ( 第四版 ) 5. 刷新数据如果数据透视表数据源中的数据发生变化, 可以通过刷新数据透视表中的数据进行修改 具体操作步骤如下 (1) 打开已经完成数据修改的工作表, 如图 3-38 所示 修改的数据 图 3-38 完成数据修改的工作表 (2) 打开数据透视表所在的工作表, 选中数据透视表 (3) 打开 选项 选项卡, 在 数据 选项组中单击如图 3-39 所示的 刷新 按钮 (4) 通过上述操作, 数据透视表中的数据完成更新, 刷新结果如图 3-40 所示 图 3-39 刷新 按钮 图 3-40 刷新数据后的数据透视表 6. 删除数据透视表对于不需要的数据透视表可以将其删除 删除数据透视表的具体操作步骤如下 (1) 打开需要删除的数据透视表 (2) 打开 数据透视表工具 选项 选项卡 (3) 在 操作 组中单击 选中 选项的下拉菜单, 在该下拉菜单中选择 整个数据透视表 选项, 如图 3-41 所示, 选中整个数据透视表 图 3-41 整个数据透视表 选项 94

103 第 3 章 Excel 高级应用 (4) 单击 Delete 键, 删除整个工作表 7. 设置汇总方式在默认情况下, 汇总使用的是与相关数据字段相同的汇总函数, 但是有时需要使用与相关字段不同的汇总函数 设置汇总方式的具体操作步骤如下 (1) 单击数据透视表中的任意一个单元格 (2) 选择 数据透视表工具 中的 选项 选项卡, 单击该选项卡的 显示 组中的 字段列表 按钮, 如图 3-42 所示 图 3-42 字段列表 按钮 (3) 在打开的 数据透视表字段列表 任务窗格中单击 求和项 按钮, 并选择 值字段设置 命令, 如图 3-43 所示 (4) 打开 值字段设置 对话框, 在 值字段汇总方式 选项组中选择需要使用的汇总函数, 如图 3-44 所示 图 3-43 值字段设置 命令 图 3-44 值字段设置 对话框 (5) 单击 确定 按钮, 完成设置汇总方式 95

104 Excel 在会计和财务中的应用 ( 第四版 ) 3.2 图表 使用 Excel 的图表功能可以将工作表中枯燥的数据转化为简洁的图表形式 当编辑工作表中的数据时, 图表也相应地随数据的改变而改变, 不需要再次生成图表 本节将介绍有关图表的术语和类型, 以及建立图表 自定义图表和三维图表的操作等 图表的概述 图表具有很好的视觉效果, 创建图表后, 可以清晰地看到数据之间的差异 应用图表不仅可以把数据形象地表示, 而且可以对图表中的数据进行预测分析, 得到一系列数据的变化趋势 使用 Excel 的图表功能可以将工作表中枯燥的数据转化为简洁的图表形式 设计完美的图表与处于大量网格线中的数据相比, 可以更迅速有力地传递信息 1. 数据系列创建图表需要以工作表中的数据为基础 工作表中转化为图表的一连串数值的集合称作数据系列 例如, 要画出某公司下属的各个分公司各季度的利润图表, 某个分公司各个季度的利润就构成了一个数据系列 2. 引用每个数据系列都包含若干个数值点,Excel 的数据系列中最多可以有 个数值点, 用 引用 作为各数据系列中数值点的标题 3. 嵌入式图表嵌入式图表是把图表直接插入数据所在的工作表中, 主要用于说明工作表的数据关系 嵌入式图表具有更强的说服力和更为直观的表达力 如图 3-45 所示是一个嵌入式图表的例子 图 3-45 嵌入式图表 96

105 第 3 章 Excel 高级应用 4. 图表工作表为创建的图表工作表专门新建一个工作表, 整个工作表中只有这一张图表, 如图 3-49 所示的就是图表工作表, 图表工作表主要用于只需要图表的场合, 因为用户输入的数据往往只是为了建立一张图表, 因而在最后的输出文档中只需出现一张单独的图表即可 5. 图表类型 Excel 一共提供了 14 种图表类型, 每种图表类型还包含几种不同的子类型, 子类型是在图表类型基础上变化而来的 用户在创建图表前需要根据要求决定采用哪一种图表类型, 每一种类型都有其各自的特点, 如表 3-1 所示为各种图表类型及其用途 表 3-1 各种图表类型用途图表类型用途 柱形图条形图折线图饼图 XY( 散点 ) 面积图圆环图雷达图曲面图气泡图股价图圆锥 圆柱和棱锥图 用于显示一段时期内数据的变化或者各项之间的比较关系用于描述各项之间的差异变化或者显示各个项与整体之间的关系用于显示数据的变化趋势用于显示数据系列中各项占总体的比例关系, 注意饼图一般只显示一个数据系列多用于科学数据, 用于比较不同数据系列中的数值, 以反应数值之间的关联性用于显示局部和整体之间的关系, 更强调幅值随时间的变化趋势用于显示部分和整体之间的比例关系, 这一点和饼图类似, 但可以表示多个数据系列用于多个数据系列之间的总和值的比较, 各个分类沿各自的数值坐标轴相对于中点呈辐射状分布, 同一序列的数值之间用折线相连用于确定两组数据之间的最佳逼近一种特殊类型的 XY 散点图用于分析股票价格的走势它们都是三维效果图, 用途与二维效果图类似 创建图表 根据图表放置方式的不同, 将图表分为嵌入式图表和工作表图表 这两种图表的创建方式类似, 下面以建立嵌入式图表为例介绍如何使用图表向导来创建图表 创建图表的具体操作步骤如下 (1) 打开准备创建图表的工作簿, 选择用来创建图表的数据区域, 如图 3-46 所示 97

106 Excel 在会计和财务中的应用 ( 第四版 ) 所示 图 3-46 选择用来创建图表的数据区域 (2) 打开 插入 选项卡, 单击 图表 组中 柱形图 命令的下拉箭头, 如图 3-47 图 3-47 柱形图 命令 (3) 在下拉菜单中单击所需的一种柱形图类型, 完成图表类型选择, 如图 3-48 所示 图 3-48 选择柱形图类型 (4) 如图 3-49 所示为柱形图创建完成效果 图 3-49 创建完成的柱形图 98

107 第 3 章 Excel 高级应用 改变图表类型 在创建图表时, 用户不一定清楚为数据选择哪一种图表类型更合适, 通常在创建了图表之后, 才发现选择另一种图表类型更适合当前的数据, 这里就涉及了如何改变图表类型的问题 改变图表类型的具体操作步骤如下 (1) 打开需要修改图表的工作簿, 激活需要改变的图表 (2) 右击, 在快捷菜单中选择 更改图表类型 命令, 如图 3-50 所示 图 3-50 更改图表类型 命令 (3) 弹出如图 3-51 所示的 更改图表类型 对话框, 可以从中选择所需的图表类型 图 3-51 更改图表类型 对话框 (4) 单击 确定 按钮, 新类型的图表将出现在工作表中, 如图 3-52 所示 图 3-52 改变后的图表类型 99

108 Excel 在会计和财务中的应用 ( 第四版 ) 编辑图表 1. 调整图表位置如果在同一个工作表中调整图表的位置, 直接用鼠标将图表拖动到合适的位置, 松开鼠标即可 如果在不同的工作表中调整图表的位置, 具体操作步骤如下 (1) 打开需要移动图表的工作簿, 激活需要移动的图表 (2) 打开 图表工具 设计 选项卡, 单击 移动图表 按钮, 如图 3-53 所示 图 3-53 移动图表 按钮 (3) 弹出 移动图表 对话框, 在该对话框中选择所需移动到工作表 ( 如 Sheet4), 如图 3-54 所示 图 3-54 移动图表 对话框 (4) 单击 确定 按钮, 图表将出现在 Sheet4 工作表中, 如图 3-55 所示 图 3-55 图表出现在 Sheet4 工作表 100

109 第 3 章 Excel 高级应用 2. 标题操作 (1) 添加标题图表标题是用来对图表进行说明的一个标志 用户可以在图表中添加图表标题, 具体操作步骤如下 1 打开需要添加标题图表所在的工作簿, 激活该图表 2 打开 图表工具 布局 选项卡, 单击 图表标题 下拉箭头, 打开 图表标题 下拉菜单如图 3-56 所示 图 3-56 图表标题 下拉菜单 3 在该下拉菜单中选择 居中覆盖标题 命令 4 图表中出现标题文本框, 在该文本框中输入标题说明文字, 如图 3-57 所示, 标题添加完成 图 3-57 标题文本框中输入标题说明文字 (2) 修改标题属性双击需要修改的标题, 打开该标题所对应的 设置图表标题格式 对话框, 如图 3-58 所示, 图表标题格式 对话框中有 6 个选项卡, 用户可以根据需要设置图表标题 如图 3-59 所示, 将图表标题设置为紫色填充色, 有外表阴影的标题格式 101

110 Excel 在会计和财务中的应用 ( 第四版 ) 图 3-58 设置图表标题格式 对话框 图 3-59 图表完成标题格式设置 对于图表标题格式的设置, 也可以采用 图表工具 格式 选项卡中提供的现有 模式进行快速设置, 如图 3-60 所示 图 3-60 图表工具 格式 选项卡 3. 添加网格线网格线扩展了坐标轴上的刻度线, 有助于用户弄清数值点的数值大小 添加网格线的具体操作步骤如下 (1) 打开需要添加网格线图表所在的工作簿, 激活该图表 (2) 选择 图表工具 布局 选项卡, 单击 网格线 下拉箭头, 如图 3-61 所示 图 3-61 主要横网格线 主要网格线 命令 102

111 第 3 章 Excel 高级应用 所示 (3) 在 网格线 下拉菜单中选择 主要横网格线 主要网格线 命令, 如图 3-61 (4) 图表中显示主要横网格线, 如图 3-62 所示 图 3-62 图表显示主要横网格线如果用户不需要网格线, 可以在网格线的下拉菜单中选择 无 命令, 图表中的网格线即可清除 注意 : 主要网格线通过坐标轴的数据标志点, 次要网格线位于主要网格线之间 4. 数据系列操作由于图表与其源数据之间在创建图表时已经建立了链接关系, 因此, 在对工作表中数据进行修改后,Excel 将会自动更新图表 ; 在对图表进行修改后, 其源数据中的数据也会随着改变 (1) 添加数据添加数据的具体操作步骤如下 1 在 Sheet6 工作表中添加 F 列数据, 如图 3-63 所示 图 3-63 添加 F 列数据后的工作表 2 单击 Sheet4 工作表中的图表, 选中该图表 3 打开 图表工具 设计 选项卡, 单击该选项卡中的 选择数据 按钮, 如图 3-64 所示 103

112 Excel 在会计和财务中的应用 ( 第四版 ) 图 3-64 选择数据 按钮 4 弹出如图 3-65 所示的 选择数据源 对话框, 然后单击 图表数据区域 文本 框右侧的 压缩对话框 按钮 图 3-65 选择数据源 对话框 5 单击 Sheet6 工作表标签, 选择 A2 到 F7 区域, 单击 图表数据区域 文本框右侧的 展开对话框 按钮, 返回 选择数据源 对话框 6 单击 确定 按钮, 电饭锅 数据将添加到图表中, 结果如图 3-66 所示 图 3-66 添加数据后的新图表 (2) 删除数据系列如果要同时删除工作表和图表中的数据, 只要从工作表中删除数据, 图表将会自动更新 如果从图表中删除数据系列, 则只在图表单击所要删除的数据系列, 然后按 Delete 键即可 104

113 第 3 章 Excel 高级应用 5. 设置图表区格式在某些情况下, 对图表区的背景进行重新设置, 可以更好地突出图表的内容 设置图表区格式的具体操作步骤如下 (1) 单击需要设置的图表, 选中该图表 (2) 打开 图表工具 布局 选项卡, 单击 设置所选内容格式 按钮, 如图 3-67 所示 图 3-67 设置所选内容格式 按钮 (3) 弹出 设置绘图区格式 对话框, 如图 3-68 所示 (4) 选择 渐变填充 选项, 选择一种预设颜色, 如图 3-68 所示 图 3-68 设置绘图区格式 对话框 (5) 单击 关闭 按钮, 图表区变为如图 3-69 所示的样式 图 3-69 设置后的图表 105

114 Excel 在会计和财务中的应用 ( 第四版 ) 6. 添加文本框文本框用于输入文本和对文本进行格式化, 可以用于为工作表和图表添加注释性文字, 并且常常与箭头和圆一起使用, 以指明信息所解释的对象 添加文本框的具体操作步骤如下 (1) 打开需要添加文本框图表所在的工作表 (2) 打开 图表工具 布局 选项卡, 单击 文本框 按钮, 如图 3-70 所示 图 3-70 文本框 按钮 (3) 此时, 鼠标指针将变成十字形状, 拖动鼠标光标画出一个文本框并在其中输入 文字, 如图 3-71 所示 图 3-71 画出文本框并在其中输入文字 (4) 打开 图表工具 布局 选项卡, 单击 形状 下拉箭头, 在该下拉菜单中 选择 箭头 按钮, 如图 3-72 所示 图 3-72 选择 箭头 按钮 106

115 第 3 章 Excel 高级应用 (5) 拖动鼠标画出一个箭头, 图表插入一条解释, 如图 3-73 所示 图 3-73 图表插入一条解释 另外, 双击文本框, 会出现文本框编辑的对话框, 如图 3-74 所示 在该对话框中选 择相应的选项, 便可以对文本框格式进行设置 如图 3-75 所示为设置后的文本框 图 3-74 文本框编辑的对话框 图 3-75 设置后的文本框 迷你图表 Excel 2010 新增了迷你图的功能, 这是工作表单元格中的一个微型图表 要在单元 格中创建迷你图, 操作步骤如下 107

116 Excel 在会计和财务中的应用 ( 第四版 ) (1) 打开需要迷你图所在的工作表, 选中需要创建迷你图的单元格 (2) 打开 插入 选项卡, 单击 迷你图 组中的 柱形图 命令, 如图 3-76 所示 图 3-76 迷你图 组中的 柱形图 命令 (3) 在 创建迷你图 对话框中选择数据源, 如图 3-77 所示 (4) 单击 确定 按钮, 在单元格 B7 中显示迷你柱形图, 如图 3-78 所示 图 3-77 创建迷你图 对话框 3-78 迷你柱形图 三维图表 要创建一张三维图表, 三维图表比二维图表更符合人的视觉习惯, 所以它比二维图表更能吸引人的注意力 如果三维图表使用得当, 会达到事半功倍的效果 要创建一张三维图表, 可打开 插入 选项卡, 单击 图表 组中 柱形图 命令的下拉箭头, 在下拉菜单选择一种三维图类型, 如图 3-79 所示, 单击所选择的类型即可 如图 3-80 所示为创建好的一张三维图表 图 3-79 选择三维图类型 图 3-80 创建三维图表 108

117 第 3 章 Excel 高级应用 创建三维图表的操作, 基本与二维图形相同, 但是对三维图表可以进行三维旋转的操作 旋转三维图表的具体操作步骤如下 (1) 激活需要旋转的三维图表, 右击, 在弹出的快捷菜单中选择 三维旋转 命令, 如图 3-81 所示 图 3-81 三维旋转 命令 (2) 弹出 设置图表区格式 对话框, 在 三维旋转 选项组中进行设置, 如图 3-82 所示, 在该对话框中可以调整三维图的仰角 转角 透视系数和高度 图 3-82 设置三维旋转 (3) 设置完成, 单击 关闭 按钮, 三维图表发生旋转, 效果如图 3-83 所示 109