普通高等教育经管类专业 十三五 规划教材 基于 Excel 的财务管理综合模拟实训 谭洪益主编 北京

Transcription

1

2 普通高等教育经管类专业 十三五 规划教材 基于 Excel 的财务管理综合模拟实训 谭洪益主编 北京

3 内容简介 本书是财务管理专业的实训教材, 借助于 Excel 工具并将其引入财务管理课程的实训教学中, 结合大量的实例运用 Excel 构建相应的财务管理模型, 去解决其相应的筹资管理 投资管理 营运资金管理 利润分配管理 财务预算和财务分析等问题, 从而提高学生的动手能力和实际解决问题的能力 本书可作为普通高等院校财务管理课程的教材, 也可供对 Excel 操作和财务管理学感兴趣的读者参阅 本书封面贴有清华大学出版社防伪标签, 无标签者不得销售 版权所有, 侵权必究 侵权举报电话 : 图书在版编目 (CIP) 数据基于 Excel 的财务管理综合模拟实训 / 谭洪益主编. 北京 : 清华大学出版社,2018 ( 普通高等教育经管类专业 十三五 规划教材 ) ISBN Ⅰ. 1 基 Ⅱ. 1 谭 Ⅲ. 1 表处理软件 - 应用 - 财务管理 - 教材 Ⅳ. 1F 中国版本图书馆 CIP 数据核字 (2018) 第 号 责任编辑 : 王定 封面设计 : 周晓亮 版式设计 : 思创景点 责任校对 : 牛艳敏 责任印制 : 李红英 出版发行 : 清华大学出版社 网 址 : 地 址 : 北京清华大学学研大厦 A 座 邮 编 : 社总机 : 邮 购 : 投稿与读者服务 : ,c-service@tup.tsinghua.edu.cn 质量反馈 : ,zhiliang@tup.tsinghua.edu.cn 印装者 : 北京嘉实印刷有限公司 经 销 : 全国新华书店 开 本 :185mm 260mm 印 张 :15.25 字 数 :380 千字 版 次 :2018 年 9 月第 1 版 印 次 :2018 年 9 月第 1 次印刷 定 价 :48.00 元 产品编号 :

4 前 言 本书按照 理论与实践高度结合 的思路, 改变过去的教学模式, 试图把 Excel 工具引入到财务管理模拟实训环节, 去解决财务管理中的筹资管理 投资管理 营运资金管理 利润分配管理 财务预算和财务分析等问题 本书紧扣企业实际财务管理需要, 以易学 易用 实用为宗旨, 谋篇布局 书中列示的操作步骤详细易懂 案例素材完整易用, 内容由浅入深 循序渐进, 是一本非常实用的基于 Excel 的财务管理实训教材 与其他同类财务管理实训教材相比, 本书主要有以下特色 : 一是突出了实践性, 提高实训的有效性 本书在教学模式上主要以学生为中心, 借助 Excel 建立各种财务管理模型, 去解决财务管理的实际问题 教材中的每一项实训模块, 都结合了大量的例题进行讲解, 操作步骤简单明了, 便于学生学习 理解和实践操作 二是基本涵盖了财务管理的主要内容 从具体内容来讲, 本书主要分为 11 大模块进行实训 :Excel 基础实训, 财务分析实训, 资金时间价值实训, 筹资决策实训, 资本成本和资本结构实训, 投资决策实训, 营运资金管理实训, 利润预测 规划与分配实训, 财务预测实训, 财务预算实训和企业价值评估实训 三是实训目标明确 通过实训, 一方面使学生熟练掌握 Excel 的相关操作, 能够运用 Excel 的相关工具决策财务管理中的问题, 从而提高财务管理实务操作技能, 为就业打下坚实基础 ; 另一方面使学生进一步理解和掌握财务管理等主要课程的基本理论和方法, 并能够熟练构建 Excel 模式解决财务管理中的具体问题, 培养和提高其分析解决实际财务管理问题的能力 本书是 2015 年度广东省高等教育教学改革项目 基于 Excel 财务管理模拟实训 的最终成果, 受到广东培正学院及广东省教育厅的资助 另外, 在教材的编写过程中, 笔者阅读了大量的书籍和文献, 并得到了清华大学出版社的指导和帮助, 在此向有关的作者和指导编写的老师们表示感谢! 由于本人的水平有限, 再加上时间仓促, 书中难免存在不足之处, 敬请广大读者批评指正 本书课件及相关素材下载 : 课件下载 实训案例下载 编 者 2018 年 5 月

5 目 录 项目一 Excel 基础实训 1 实训一 Excel 基础操作 1 一 Excel 工作簿的打开与保存 1 二 输入和编辑数据 2 三 Excel 工作表的编辑 5 四 Excel 文件的打印 14 五 Excel 常用函数 16 实训二 Excel 图表的创建与编辑 19 一 认识 Excel 2010 中的图表 19 二 图表的创建与编辑 20 实训三 Excel 数据管理 23 一 数据管理 23 二 数据透视表和数据透视图 32 实训四 Excel 数据分析工具 42 一 数据分析工具 42 二 规划求解 47 实训练习 50 项目二财务分析实训 52 实训一财务分析模型 52 一 建立财务分析模型 52 二 从外部导入财务数据 55 实训二比率分析模型 57 实训三比较分析模型 63 实训四财务综合分析模型 68 实训练习 71 项目三资金时间价值实训 72 实训一终值和现值的计算 72 一 单利终值和现值 73 二 复利终值和现值 73 实训二年金终值与现值的计算 77 一 普通年金的终值和现值 78 二 先付年金的终值和现值 80 三 延期年金的终值和现值 80 四 永续年金的现值 83 实训三名义利率和实际利率的换算 83 一 Excel 计算实际年利率 83 二 Excel 计算名义年利率 84 实训四资金时间价值的具体运用 85 一 贷款年利率的计算 85 二 贷款偿还期的计算 86 三 等额分期付款方式下贷款年偿还额的计算 87 四 现金流不规则分布情况下的现值计算 90 五 其他资金时间价值问题的计算 92 实训练习 96 项目四筹资决策实训 97 实训一股票筹资分析模型 97 实训二长期负债筹资分析模型 101 一 长期银行借款筹资分析模型 102 二 债券筹资分析模型 104 实训三租赁筹资分析模型 106 实训练习 112 项目五资本成本和资本结构实训 113 实训一资本成本实训 113 一 基于 Excel 的个别资本成本率的计算 113 二 基于 Excel 的综合资本成本率的计算 116 三 基于 Excel 的边际资本成本率的计算 117

6 IV 基于 Excel 的财务管理综合模拟实训 实训二杠杆计算 122 一 基于 Excel 的经营杠杆的计算 123 二 基于 Excel 的财务杠杆的计算 124 三 基于 Excel 的联合杠杆的计算 124 实训三资本结构决策 127 一 基于 Excel 的比较资本成本法 127 二 基于 Excel 的每股收益 (EPS) 分析法 131 实训练习 133 项目六投资决策实训 135 实训一投资决策指标计算 135 一 投资决策的现金流量分析 136 二 投资决策评价指标的计算 138 实训二固定资产折旧计算 143 一 直线法 144 二 工作量法 144 三 双倍余额递减法 145 四 年数总和法 147 五 折旧对现金流量计算的影响 147 实训三投资方案评价 149 一 独立方案的评价 149 二 互斥方案的评价 151 三 固定资产更新决策 153 实训四证券投资决策 156 一 债券投资 157 二 股票投资 159 实训练习 161 项目七营运资金管理实训 163 实训一现金管理 163 一 成本分析模型 164 二 存货模型 165 实训二应收账款管理 165 一 基于 Excel 的信用标准分析模型 166 二 基于 Excel 的信用条件分析模型 168 三 基于 Excel 的收账政策分析模型 170 四 基于 Excel 的应收账款综合决策模型 172 实训三存货管理 174 一 基于 Excel 的经济订货批量模型 175 二 基于 Excel 的 ABC 存货管理模型 176 实训练习 178 项目八利润预测 规划与分配实训 179 实训一利润预测与规划 179 一 利润预测模型 180 二 保本点计算模型 181 三 利润敏感性分析模型 182 四 利用规划求解进行利润规划 184 实训二利润分配 186 实训练习 189 项目九财务预测实训 191 实训一利用数据分析工具进行预测 191 一 利用移动平均工具进行财务预测 193 二 利用指数平滑工具进行财务预测 194 三 利用回归工具进行财务预测 195 四 利用绘图工具进行财务预测 196 实训二 Excel 中的预测函数 197 一 LINEST 函数 198 二 LOGEST 函数 200 三 TREND 函数 201 四 GROWTH 函数 202 五 FORECAST 函数 202

7 目录 V 六 SLOPE 函数 203 七 INTERCEPT 函数 203 实训三基于 Excel 的财务预测 204 一 销售预测 205 二 成本预测 207 三 利润预测 209 四 资金需要量的预测 210 实训练习 213 项目十财务预算实训 214 实训一日常业务预算 214 一 销售预算 216 二 生产预算 216 三 直接材料消耗及采购预算 217 四 直接人工成本预算 218 五 制造费用预算 219 六 产品成本预算 220 七 销售及管理费用预算 221 实训二财务预算 221 一 现金预算 222 二 预计利润表 223 三 预计资产负债表 224 实训练习 225 项目十一企业价值评估实训 226 实训一现金流量折现模型 226 实训二经济利润模型 228 实训三相对价值模型 230 实训练习 233

8

9 项目一 Excel 基础实训 实训目标 熟练掌握 Excel 2010 版的基本操作, 如打开 保存 编辑和打印等 熟练掌握 Excel 2010 版的常用函数 熟练掌握图表的创建与编辑 熟练掌握数据的排序 筛选和分类汇总 熟练掌握数据分析工具 教学建议 建议本项目主要采取讲授与实训相结合的方法, 课时安排为 6 个课时, 实训练习作为学生的课后练习, 并记录实训步骤 实训一 Excel 基础操作 知识准备 Excel 2010 版的基本操作知识 实训目的与要求 熟练掌握 Excel 2010 版的基本操作, 如打开 保存 编辑和打印等 实训指导 一 Excel 工作簿的打开与保存 1. 新建工作簿 例 1-1 新建一张工作簿 新建工作簿的方法主要有以下几种 (1) 单击桌面左下角的 开始 按钮, 在 所有程序 中找到 Microsoft Office 下的 Microsoft Office Excel 2010, 系统会自动打开一个新的工作簿 (2) 在已经打开的某个 Excel 工作簿中, 单击 文件 中的 新建 命令, 选择 空白工作簿 (3) 在已经打开的某个 Excel 工作簿中使用组合键 Ctrl+N (4) 使用模板创建工作簿, 单击 文件 中的 新建 命令, 在 OFFICE.COM 模板 中选择所需要的模板, 如图 1-1 所示

10 2 基于 Excel 的财务管理综合模拟实训 图 1-1 工作簿模板的设置 2. 打开工作簿 例 1-2 打开文件名为 销售报表 的工作簿 打开已有工作簿的方法主要有以下几种 (1) 单击 文件 中的 打开 命令或者按组合键 Ctrl+O, 系统会弹出 打开 对话框, 在该对话框中找到需要打开的文件路径和文件名, 再单击该对话框中的 打开 按钮即可 (2) 鼠标双击需要打开的工作簿图标, 可直接打开工作簿 3. 保存工作簿 例 1-3 保存文件名为 销售报表 的工作簿 保存工作簿的方法主要有以下几种 : (1) 单击快速访问工具栏中的 保存 按钮 (2) 单击 文件 中的 保存 按钮 (3) 使用组合键 Ctrl+S 二 输入和编辑数据 1. 数据的输入在 Excel 中使用的数据类型主要包括以下几种 (1) 文本型数据 文本型数据是指以文本或字符串形式存储的数据, 一般由字母 汉字和数字组成 文本型数据主要用于数据表的标签, 直观反映数据表的含义 文本型数据不一定只是文字, 也可以是数字, 比如身份证号码 在单元格中输入身份证号码有两种方式, 一

11 项目一 Excel 基础实训 3 种是先输入, 然后再输入身份证号码 ; 另外一种是先把单元格格式类型设置为文本型, 然后再输入 (2) 数值型数据 数值型数据是由数字组成, 包括整数 分数 百分数等 一般整数 百分数和小数直接输入即可, 但分数不能直接输入, 否则会变成日期 分数的输入需要先输入 0, 再输入空格, 最后再输入分数 (3) 日期和时间型数据 日期型数据是表示日期的数据, 如 2018 年 1 月 1 日 日期型数据输入的格式一般为 : 年 / 月 / 日 年 - 月 - 日 年 / 月 年 - 月 时间型数据是表示具体时间的数据, 如 时间型数据输入的格式一般为: 时 分 秒 数据既可以在单元格中输入, 也可以在编辑栏中输入 一个单元格最多可以容纳 个字符, 其中单元格中只显示 1024 个字符 如果需要在单元格内输入多行数据时, 可以在输完每行数据后, 按 Alt+Enter 组合键进行换行操作 数据的输入应注意以下方面 1 在单元格区域输入相同的数据 例 1-4 在单元格区域 A1:E7 中输入 12 首先选取单元格区域 A1:E7, 然后直接输入数据 12, 最后同时按下 Ctrl+Shift+Enter 组合键, 结果如图 1-2 所示 2 序列数据的输入 例 1-5 使用数据填充的方法输入等差序列 3,5,7,9,11,13,15,17,19,21 对于像等差序列 等比序列 时间序列等序列数据, 可以采用填充复制的方法输入 在 开始 选项卡 编辑 功能组中单击 填充 命令, 然后在下拉列表选择 序列 命令, 打开 序列 对话框, 如图 1-3 所示 在打开的对话框中, 类型 选择 等差序列, 步长值 输入 2, 终止值 输入 21, 最后单击 确定 按钮即可 图 1-2 操作结果 图 1-3 序列 对话框 3 自定义序列的输入 例 1-6 输入自定义序列 广州地区 长沙地区 武汉地区 上海地区 北京地区 首先在 文件 中打开 Excel 选项 对话框, 如图 1-4 所示 在 高级 区域中找到 编辑自定义列表 按钮, 然后单击 编辑自定义列表 按钮, 弹出 自定义序列 对话框, 如图 1-5 所示 在 自定义序列 对话框中, 输入要建立的自定义序列 : 广州地区 长沙地区 武汉地区 上海地区 北京地区 这里需要注意的是在输入过程中, 各项数据之间既可以用半角字符的逗号隔开, 也可以每输完一个数据之后回车换行, 再输入下一个数据 输入完成之后, 再单击 确定 按钮, 这样就建立了所设置的自定义序列

12 4 基于 Excel 的财务管理综合模拟实训 图 1-4 Excel 选项 对话框 图 1-5 自定义序列 对话框建立自定义序列之后, 只要在工作表中输入 广州地区, 然后选中这个单元格, 将鼠标指针对准右下角的填充柄按住左键不放拖动, 就会自动实现 广州地区 长沙地区 武汉地区 上海地区 北京地区 的输入 2. 数据的编辑数据的编辑主要包括数据的修改 移动 复制 删除 查找和替换 添加批注等操作 数据的修改既可以在单元格中直接修改, 也可以在编辑栏中修改 数据的移动和复制主要是利用 复制 剪切 粘贴 命令进行操作 下面重点讲述数据删除 查找和替换 添加批注等操作 (1) 数据的删除删除单元格中的数据主要有两种方法 一种是选取需要删除的单元格, 直接按 Delete 键 这种方法只能删除单元格的内容, 不能删除格式 另一种是选择需要删除数据的单元格或者

13 项目一 Excel 基础实训 5 单元格区域, 在 开始 选项卡 编辑 功能组中执行 清除 命令, 可以选择不同的清除方式, 如果选择 全部清除, 则是将格式 批注和内容等全部清除 ; 如果选择 清除格式, 则仅仅清除格式 ; 如果选择 清除内容, 则仅仅清除内容 ; 如果选择 清除批注, 则只是清除批注, 如图 1-6 所示 (2) 查找和替换利用查找和替换功能可以快速查找所需信息, 或者利用替换功能进行替换 在 开始 选项卡 编辑 功能组中选择 查找和选择 命令, 在下拉菜单中选择 查找 或 替换, 就会弹出 查找和替换 对话框, 如图 1-7 所示 图 1-6 清除 下拉菜单 图 1-7 查找和替换 对话框 (3) 添加批注为单元格添加批注有助于用户更好地记忆和理解单元格中的信息, 有助于方便地输入和编辑数据 首先选择需要添加批注的单元格, 然后在 审阅 选项卡 批注 功能组中单击 新建批注 命令, 或者单击鼠标右键, 在快捷菜单中选择 插入批注 命令, 最后在批注框中输入相关的内容 批注框默认是隐藏的, 如果需要始终显示批注, 可以在 审阅 选项卡 批注 功能组中单击 显示 / 隐藏批注 命令, 也可以单击鼠标右键, 在快捷菜单中选择 显示 / 隐藏批注 命令 如果需要编辑批注的内容, 首先选择要编辑批注的单元格, 然后在 审阅 选项卡 批注 功能组中单击 编辑批注 命令, 或者单击鼠标右键, 在快捷菜单中选择 编辑批注 命令, 即可编辑批注中的内容和格式 三 Excel 工作表的编辑 1. 调整行 列或单元格 (1) 插入行 列或单元格在 开始 选项卡 单元格 功能组中单击 插入 命令, 可以在下拉菜单中选择 插入工作表行 插入工作表列 和 插入单元格 等, 如图 1-8 所示 或者选中某一个单元格, 单击鼠标右键, 在快捷菜单中选择 插入 命令, 打开 插入 对话框, 选择 整行 或者 整列 按钮 如果需要同时插入多行或者多列, 则需要同时选中多行或者多个单元格, 然后在 开始 选项卡 单元格 功能组中单击 插入 命令, 在下拉菜单中选择 插入工作表行 插入工作表列

14 6 基于 Excel 的财务管理综合模拟实训 (2) 删除行 列或单元格删除行 列或单元格的方法主要有两种 一种是选择要删除的行 列或单元格, 在 开始 选项卡 单元格 功能组中单击 删除 命令, 在下拉菜单中选择相应命令, 如图 1-9 所示 另外一种是选择要删除的行 列或单元格, 单击鼠标右键, 在快捷菜单中选择 删除 命令, 打开 删除 对话框, 再选择相应命令 (3) 设置行高和列宽设置行高和列宽的方法主要有以下几种 1 设置行高和列宽精确数值 在 开始 选项卡 单元格 功能组中单击 格式 命令, 在下拉菜单中选择 行高 和 列宽 命令, 在弹出的 行高 和 列宽 对话框中输入行高和列宽的数值, 如图 1-10 所示 图 1-8 插入 下拉菜单图 1-9 删除 下拉菜单图 1-10 格式 下拉菜单 2 将鼠标移到行 ( 列 ) 标题的交界处, 成双向拖拉箭头状时, 按住左键向右 ( 下 ) 或向左 ( 上 ) 拖拉, 即可调整行 ( 列 ) 宽 ( 高 ) 3 将鼠标移到行 ( 列 ) 标题的交界处, 双击鼠标左键, 即可快速将行 ( 列 ) 的行高 ( 列宽 ) 调整为 最合适的行高 ( 列宽 ) 2. 设置工作表格式 (1) 设置单元格格式首先选择需要设置格式的单元格或单元格区域, 在 开始 选项卡 单元格 功能组中单击 格式 命令, 在下拉菜单中选择 设置单元格格式 命令, 或者单击鼠标右键, 在快捷菜单中选择 设置单元格格式 命令, 打开 设置单元格格式 对话框, 如图 1-11 所示 在 设置单元格格式 对话框中, 可以选择对数字格式 对齐方式 字体 边框 填充和单元格保护等进行相应的设置 其中 数字 选项卡可以对数据类型进行设置 ; 对齐 选项卡可以设置数据的对齐方式, 还可以设置自动换行 合并单元格 文字方向等 ; 字体选项卡 可以设置字体 字形 字号 字体颜色等 ; 边框 选项卡可以设置边框的格式; 填充 选项卡可以设置单元格或单元格区域的背景颜色 ; 保护 选项卡可以设置单元格或者单元格区域的保护

15 项目一 Excel 基础实训 7 图 1-11 设置单元格格式 对话框 (2) 自动套用格式 Excel 2010 提供了多种工作表格式, 用户可以使用自动套用格式功能直接建立具有一定格式的工作表 首先选取需要自动套用格式的单元格区域, 然后在 开始 选项卡 样式 功能组中单击 套用表格格式 命令, 在下拉菜单中选择所需要的表格样式, 如图 1-12 所示 图 1-12 套用表格格式 下拉菜单

16 8 基于 Excel 的财务管理综合模拟实训 (3) 使用条件格式在 Excel 2010 中, 用户可以利用条件格式将满足一定条件的单元格区域按照设定的格式显示出来, 从而方便用户查看特别关注的数据 首先选取需要使用条件格式的单元格区域, 然后在 开始 选项卡 样式 功能组中单击 条件格式 命令, 在下拉菜单中选择所需要的表格样式, 如 突出显示单元格规则 项目选取规则 数据条 色阶 图标集, 在每一项格式后面还可以选择相应的命令, 如图 1-13 所示 如果需要设置两个及两个以上的多个条件格式, 可以在 条件格式 下拉菜单中选择 管理规则 命令, 打开 条件格式规则管理器 对话框, 如图 1-14 所示 图 1-13 条件格式 下拉菜单 图 1-14 条件格式规则管理器 对话框 在 条件格式规则管理器 对话框中单击 新建规则, 打开 新建规则 对话框, 如图 1-15 所示 在 新建规则 对话框中选择相应的规则类型, 并进行格式设置, 设置完成后单击 确定 按钮, 再回到 条件格式规则管理器 对话框, 再次打开 新建规则 对话框进行格式设置, 单击 确定 按钮, 这样就设置了两个条件格式 图 1-15 新建格式规则 对话框

17 项目一 Excel 基础实训 9 设置条件格式之后, 如果不再需要所设置的条件格式, 可以将其清除 首先选择已经设置条件格式的单元格区域, 在 开始 选项卡 样式 功能组中单击 条件格式 命令, 在下拉菜单中选择 清除规则, 然后选择 清除所选单元格的规则 命令, 如果要清除当前工作表中的所有条件格式, 则可以选择 清除整个工作表的规则 命令 例 1-7 巧用大于和小于条件格式 若想在表格中快速标识出大于或小于某个特定值的所有单元格, 可按如下方法进行操作 1 打开需要设置样式的工作表, 选中单元格区域, 如图 1-16 所示 图 1-16 员工销售情况日登记表 2 单击 开始 选项卡 样式 功能组中的 条件格式 下拉按钮, 在下拉菜单中选择 突出显示单元格规则 / 大于 选项, 如图 1-17 所示 3 弹出 大于 对话框, 在左侧文本框中输入作为特定值的数值, 在右侧的下拉列表框中选择单元格样式, 如图 1-18 所示 图 1-17 突出显示单元格规则 / 大于 选项 图 1-18 大于 对话框 4 单击 确定 按钮, 将设置的格式应用到单元格中, 效果如图 1-19 所示

18 10 基于 Excel 的财务管理综合模拟实训 图 1-19 条件格式效果 (1) 5 再次单击 条件格式 下拉按钮, 在下拉菜单中选择 突出显示单元格规则 / 小于 选项, 如图 1-20 所示 6 弹出 小于 对话框, 在左侧文本框中输入作为特定值的数值, 在右侧的下拉列表框中选择单元格样式, 如图 1-21 所示 图 1-20 突出显示单元格规则 / 小于 选项 图 1-21 小于 对话框 7 单击 确定 按钮, 将设置的格式应用到单元格中, 效果如图 1-22 所示 图 1-22 条件格式效果 (2)

19 项目一 Excel 基础实训 11 (4) 取消和恢复网格线 Excel 2010 默认工作表是有网格线的, 如果需要取消网格线, 可以在 视图 选项卡 显示 功能组中, 单击 网格线 左边的复选框以去掉其中的 ; 如果需要恢复网格线, 只需单击 网格线 左边的复选框重新显示 3. 保护单元格和保护工作表 (1) 保护单元格所谓保护单元格, 是用户对含有重要数据或公式的单元格或单元格区域设置保护 因为 Excel 工作表中的所有单元格默认是锁定即受到保护的状态, 所以首先需要解除锁定, 单击工作表左上角的 全选 按钮, 单击鼠标右键, 在快捷菜单中选择 设置单元格格式 命令, 打开 设置单元格格式 对话框, 单击打开 保护 选项卡, 如图 1-23 所示 图 1-23 保护 选项卡在 保护 选项卡中的 锁定 复选框中, 单击以去掉其中的, 单击 确定 按钮, 这样工作表就设置为不锁定状态了 然后再选择需要设置保护的单元格区域, 打开 设置单元格格式 对话框中的 保护 选项卡, 单击 保护 选项卡中的 锁定 复选框, 使其显示, 再单击 确定 按钮, 这样可以将所选择的单元格区域设置为锁定状态了 锁定的单元格区域就受到了保护, 避免对受保护的单元格区域进行移动 修改 删除等操作 (2) 保护工作表对工作表中的单元格设置保护后, 为了使其发挥作用, 需要进一步保护工作表 单击 审阅 选项卡 更改 功能组中的 保护工作表 命令, 则会弹出 保护工作表 对话框, 如图 1-24 所示 在 保护工作表 对话框中的 取消工作表保护时使用的密码 栏中输入密码, 并根据需要在 允许此工作表保护的所有用户进行 列表框中选择相应的项目, 单击 确定 按钮再重新输入密码, 需要注意的是两次输入的密码需要一致, 最后单击 确定 按钮, 这样就完成了工作表保护的设置 如果用户需要撤销对工作表的保护, 单击 审阅 选项卡 更改 功能组中的 撤销工作表保护 命令, 则会弹出 撤销工作表保护 对话框, 在其中输入保护工作表时所设置的密码, 再单击 确定 按钮, 这样就撤销了对工作表的保护

20 12 基于 Excel 的财务管理综合模拟实训 4. 隐藏与显示 (1) 隐藏与显示行在 Excel 中, 可以把已经编辑好的行或列隐藏起来, 方便用户进行下一步的数据编辑 隐藏行的操作方法有两种, 一种是利用菜单命令操作, 在 开始 选项卡 单元格 功能组中单击 格式 命令, 然后在下拉菜单中的 可见性 区域下选择 隐藏和取消隐藏 子菜单中的 隐藏行 命令, 如图 1-25 所示 另外一种方法是直接利用鼠标拖动, 将鼠标指针对准要隐藏行的下边界线, 按住鼠标左键向上拖动, 直到和上面相邻行的界线重合, 即可将行隐藏起来 在打印工作表时, 被隐藏的行是不会打印出来的 图 1-24 保护工作表 对话框 图 1-25 可见性 相关菜单命令 如果要重新显示隐藏的行, 可以通过取消隐藏命令进行操作, 在具有隐藏行的数据表中任选一列, 或者在数据表的某列中选择能够跨越已经隐藏行的某个单元格区域, 然后在 开始 选项卡 单元格 功能组中单击 格式 命令, 在下拉菜单中的 可见性 区域下选择 隐藏和取消隐藏 子菜单中的 取消隐藏行 命令 (2) 隐藏与显示列隐藏列的操作方法有两种 一种是利用菜单命令操作, 在 开始 选项卡 单元格 功能组中单击 格式 命令, 然后在下拉菜单中的 可见性 区域下选择 隐藏和取消隐藏 子菜单中的 隐藏列 命令 另外一种方法是直接利用鼠标拖动, 将鼠标指针对准要隐藏列的右边界线, 按住鼠标左键向左拖动, 直到和左面相邻列的界线重合, 即可将列隐藏起来 在打印工作表时, 被隐藏的列是不会打印出来的 如果要重新显示隐藏的列, 可以通过取消隐藏命令进行操作, 在具有隐藏列的数据表中任选一列, 或者在数据表的某列中选择能够跨越已经隐藏列的某个单元格区域, 然后在 开始 选项卡 单元格 功能组中单击 格式 命令, 在下拉菜单中的 可见性 区域下选择 隐藏和取消隐藏 子菜单中的 取消隐藏列 命令 (3) 隐藏与显示工作表隐藏工作表的操作方法有两种, 一种是利用菜单命令操作, 在 开始 选项卡 单元格 功能组中单击 格式 命令, 然后在下拉菜单中的 可见性 区域下选择 隐藏和取消隐藏 子菜单中的 隐藏工作表 命令 另外一种方法是在工作表标签处单击鼠标右键, 在快捷菜单中选择 隐藏 命令, 即可将工作表隐藏起来 如果要重新显示隐藏的工作表, 可以通过取消隐藏命令进行操作, 在具有隐藏工作表的数据表中任选一张工作表, 或者在数据表的某工作表中选择能够跨越已经隐藏工作表的某个

21 项目一 Excel 基础实训 13 单元格区域, 然后在 开始 选项卡 单元格 功能组中单击 格式 命令, 在下拉菜单中的 可见性 区域下选择 隐藏和取消隐藏 子菜单中的 取消隐藏工作表 命令 5. 划分窗口 (1) 重排窗口用户有时需要同时打开几张工作表, 并在几张工作表之间进行数据核对, 这时可以通过重排窗口功能将多张工作表同时显示出来 在 视图 选项卡 窗口 功能组中单击 新建窗口 命令, 则系统会自动新建一个窗口, 然后激活另一张工作表, 在 视图 选项卡 窗口 功能组中单击 全部重排 命令, 系统会弹出一个 重排窗口 对话框, 如图 1-26 所示, 根据需要选择相应的排列方式 如果不再需要某个窗口, 则单击该窗口右上角的 关闭 按钮即可 (2) 拆分窗口如果一张工作表中存有大量数据, 用户需要同时查看该工作表中不同区域的数据, 可以利用拆分功能将工作表拆分成几个窗口 首先在需要拆分的工作表中选取某一行 某一列或者某一个单元格, 然后在 视图 选项卡 窗口 功能组中单击 拆分 命令, 如图 1-27 所示 如果选取的是某一行, 则工作表拆分成上下两个部分 ; 如果选取的是某一列, 则工作表拆分成左右两个部分 ; 如果选取的是某一个单元格, 则工作表拆分成四个部分 图 1-26 重排窗口 对话框 图 1-27 拆分 命令 对于已经拆分窗口的工作表, 取消拆分有两种方法 : 一种是利用菜单命令操作, 在 视图 选项卡 窗口 功能组中再次单击 拆分 命令 ; 另外一种是利用鼠标操作, 将鼠标指针对准要取消拆分的水平或垂直拆分条, 双击鼠标左键即可取消 (3) 冻结窗格在 Excel 中, 如果需要输入大量数据时, 用户可以利用冻结窗格命令, 将工作表中的某个单元格区域冻结, 从而便于用户的编辑 选取某个单元格, 在 视图 选项卡 窗口 功能组中单击 冻结窗格 命令, 然后根据需要在下拉菜单中选择 冻结拆分窗格 冻结首行 冻结首列 命令, 如图 1-28 所示 图 1-28 冻结窗格 相关菜单命令

22 14 基于 Excel 的财务管理综合模拟实训 对于已经冻结窗格的工作表, 可以在 视图 选项卡 窗口 功能组中单击 冻结窗格 命令, 然后在下拉菜单中选择 取消冻结窗格 命令即可取消冻结 四 Excel 文件的打印 Excel 文件在打印之前首先要进行设置, 主要包括页面设置和分页设置等 1. 页面设置在 页面布局 选项卡 页面设置 功能组中单击对话框启动器按钮, 可以打开 页面设置 对话框, 主要包括了 页面 页边距 页眉 / 页脚 工作表 四个选项卡, 可以分别对页面进行设置 另外也可以在 页面布局 选项卡 页面设置 功能组中分别单击 页边距 纸张方向 纸张大小 打印区域 打印标题 等命令进行相应设置, 如图 1-29 所示 图 1-29 页面布局 功能组 (1) 设置页面单击 页面设置 对话框中的 页面 选项卡, 如图 1-30 所示, 在 页面 选项卡中, 可以对打印方向 缩放比例 纸张大小 打印质量和起始页码进行设置 (2) 设置页边距单击 页面设置 对话框中的 页边距 选项卡, 如图 1-31 所示, 在 页边距 选项卡中, 可以对打印页面的上 下 左 右边距, 页眉 页脚的边距以及居中方式进行设置 图 1-30 页面设置 对话框的 页面 选项卡 图 1-31 页面设置 对话框的 页边距 选项卡 (3) 设置页眉和页脚单击 页面设置 对话框中的 页眉 / 页脚 选项卡, 如图 1-32 所示, 在 页眉 / 页脚 选项卡中, 单击 自定义页眉 和 自定义页脚 按钮, 可以对打印页面的页眉 页脚进行设置 (4) 设置工作表单击 页面设置 对话框中的 工作表 选项卡, 如图 1-33 所示, 在 工作表 选项

23 项目一 Excel 基础实训 15 卡中, 可以对打印区域 打印标题 是否打印网格线 是否打印行号和列标 是否打印批注以及打印顺序进行设置 图 1-32 页面设置 对话框的 页眉 / 页脚 选项卡 图 1-33 页面设置 对话框的 工作表 选项卡 2. 分页设置如果用户需要打印的工作表中内容比较多, 还要进行分页设置以达到满意的打印效果 (1) 插入分页符首先选定拟作为新起页的左上角的单元格, 然后在 页面布局 选项卡 页面设置 功能组中单击 分隔符 命令, 再在下拉菜单中选择 插入分页符 命令, 如图 1-34 所示 图 1-34 插入分页符 命令 (2) 分页调整插入分页符之后, 在普通视图下是看不到分页符的, 需要调整到分页预览, 在 视图 选项卡 工作簿视图 功能组中单击 分页预览 命令, 这样就可以在分页预览页面下对分页符进行调整, 直接将鼠标指针对准要调整位置的分页符, 将其拖动到所需要的位置, 即可完成分页调整 (3) 删除分页符对工作表进行分页设置以后, 如果不再需要原来已经设置的分页符, 可以将其删除 单击工作表左上角的全部选中按钮, 选取整张工作表, 然后在 页面布局 选项卡 页面设置 功能组中单击 分隔符 命令, 再在下拉菜单中执行 删除分页符 或 重设所有分页符 命令, 则原来设置的所有分页符就会被删除 3. 打印预览在完成页面设置和分页设置之后, 可以通过打印预览功能事先预览打印效果 在 文件 中选择 打印 命令, 则可以看到打印预览图, 如图 1-35 所示

24 16 基于 Excel 的财务管理综合模拟实训 图 1-35 打印 选项卡在打印预览取得满意效果之后, 单击图 1-35 所示的 打印 命令, 即可完成打印输出 五 Excel 常用函数 1. 函数与公式 Excel 函数是预先定义, 执行计算 分析等处理数据任务的特殊公式 以常用的求和函数 SUM 为例, 它的语法是 SUM(number1,number2,...) 其中 SUM 称为函数名称, 一个函数只有唯一的一个名称, 它决定了函数的功能和用途 函数名称后紧跟左括号, 接着是用逗号分隔参数的内容, 最后用一个右括号表示函数结束 参数是函数中最复杂的组成部分, 它规定了函数的运算对象 顺序或结构等 函数括号中的部分称为参数, 假如一个函数可以使用多个参数, 那么参数与参数之间使用半角逗号进行分隔 参数可以是常量 ( 数字和文本 ) 逻辑值( 例如 TRUE 或 FALSE) 数组 错误值( 例如 #N/A) 或单元格引用 ( 例如 E1:H1), 甚至可以是另一个或几个函数等 参数的类型和位置必须满足函数语法的要求, 否则将返回错误信息 在 Excel 中, 函数的输入方法有两种 一种是通过 插入函数 对话框, 如图 1-36 所示 首先选中存放计算结果 ( 即需要应用公式 ) 的单元格, 单击编辑栏 ( 或工具栏 ) 中的 f x 按钮, 则表示公式开始的 = 出现在单元格和编辑栏, 然后在打开的 插入函数 对话框中的 选择函数 列表找到 SUM 函数 另一种是在编辑栏中输入函数 首先选中存放计算结果的单元格, 然后鼠标单击 Excel 编辑栏, 按照函数的组成顺序依次输入各个部分, 函数输入完毕后, 单击编辑栏中的 输入 ( 即 ) 按钮 ( 或回车 ) 即可 公式是由数字 运算符号以及 Excel 的内置函数等组成的数学表达式 函数与公式既有区别又互相联系 如果说前者是 Excel 预先定义好的特殊公式, 后者就是由用户自行设计对工作表进行计算和处理的公式 在单元格中输入公式的时候要以 = 作为开头, 然后才是公式的表达式, 在完成公式的输入并按 Enter 键后, 单元格中就会显示公式计算的结果 以公式

25 项目一 Excel 基础实训 17 =SUM(E1:H1)*A1+26 为例, 它要以等号 = 开始, 其内部可以包括函数 引用 运算符和常量 上式中的 SUM(E1:H1) 是函数,A1 是对单元格 A1 的引用 ( 使用其中存储的数据 ),26 是常量, * 和 + 是算术运算符 ( 另外还有比较运算符 文本运算符和引用运算符 ) 图 1-36 插入函数 对话框 Excel 2010 中各种运算符的含义及示例如表 1-1 所示 表 1-1 Excel 中各种运算符的含义及示例 类别 运算符及含义 含义 示例 +( 加号 ) 加 1+2 ( 减号 ) 减 2 1 ( 负号 ) 负数 1 算术 *( 星号 ) 乘 2*3 /( 斜杠 ) 除 4/2 %( 百分比 ) 百分比 10% ^( 乘方 ) 乘幂 3^2 =( 等号 ) 等于 A1=A2 >( 大于号 ) 大于 A1>A2 比较 <( 小于号 ) 小于 A1<A2 >=( 大于等于号 ) 大于等于 A1>=A2 <=( 小于等于号 ) 小于等于 A1<=A2 <>( 不等号 ) 不等于 A1<>A2 文本 &( 连字符 ) 将两个文本连接起来产生连 2017 年 & 3 月 ( 结果为 2017 续的文本年 3 月 ) :( 冒号 ) 区域运算符, 对两个引用之 A1:D4( 引用 A1 到 D4 范围内的所间包括这两个引用在内的所有单元格 ) 有单元格进行引用 引用联合运算符, 将多个引用合 SUM(A1:D1,A2:C2) 将 A1:D2 和,( 逗号 ) 并为一个引用 A2:C2 两个区域合并为一个 ( 空格 ) 交集运算符, 生成对两个引 A1:D1 A1:B4( 引用 A1:D1 和 A1:B4 用中共有的单元格的引用两个区域的交集即 A1:B1) 在公式和函数的输入过程中还需注意单元格的引用 单元格引用是指对单元格或单元格 区域的引用, 以获取公式中所使用的数值或数据 单元格引用主要分为相对引用 绝对引用 和混合引用

26 18 基于 Excel 的财务管理综合模拟实训 (1) 相对引用 公式中的相对单元格引用是基于包含公式和单元格引用的单元格的相对位置 如果公式所在单元格的位置改变, 引用也随之改变 如果多行或多列地复制公式, 引用会自动调整 默认情况下, 新公式使用相对引用 例如, 在单元格 C1 中输入公式 =A1+B1, 当该公式被复制到单元格 C2 时, 公式将自动调整为 =A2+B2 (2) 绝对引用 公式中的绝对单元格引用总是在指定位置引用单元格 如果公式所在单元格的位置改变, 绝对引用的单元格始终保持不变 如果多行或多列地复制公式, 绝对引用将不作调整 默认情况下, 新公式使用相对引用, 需要时将它们转换为绝对引用 绝对引用的单元格在其列号和行号前都要加上 $ 在单元格 C1 中输入公式 =$A$1+$B$1, 当该公式被复制到单元格 C2 时, 公式仍然为 =$A$1+$B$1 (3) 混合引用 混合引用具有绝对列和相对行, 或是绝对行和相对列 绝对引用列采用 $A1 $B1 等形式 绝对引用行采用 A$1 B$1 等形式 如果公式所在单元格的位置改变, 则相对引用改变, 而绝对引用不变 如果多行或多列地复制公式, 相对引用自动调整, 而绝对引用不作调整 例如, 如果将一个混合引用从 A2 复制到 B3, 它将从 =A$1 调整到 =B$1 在 Excel 中可以使用 F4 键对单元格的相对引用 绝对引用和混合引用进行切换 比如在某个单元格中输入公式为 =SUM(A1:A8) 选中整个公式, 按下 F4 键, 该公式调整为 =SUM ($A$1:$A$8), 这时就切换成了绝对引用 第二次按下 F4 键, 公式内容变为 =SUM(A$1:A$8), 这时切换成了对行进行绝对引用, 对列相对引用的混合引用 第三次按下 F4 键, 公式则变为 =SUM($A1:$A8), 这时切换成了对行进行相对引用, 对列绝对引用的混合引用 第四次按下 F4 键时, 公式变回到初始的相对引用 =SUM(A1:A8) (4) 三维引用 三维引用是指在 Excel 公式引用方式转换的方法和技巧中, 要分析同一工作簿中多个工作表上的相同单元格或单元格区域中的数据而使用的引用 三维引用包含单元格或区域引用, 前面加上工作表名称的范围 Excel 使用存储在引用开始名和结束名之间的任何工作表 例如, 公式 =SUM(Sheet2:Sheet5!B5) 将计算包含在 B5 单元格内所有值的和, 单元格取值范围是从 工作表 2 到 工作表 5 2.Excel 常用函数介绍 Excel 2010 包含了大约几百种函数, 具体可以分成以下几类 (1) 数据库函数 : 用于分析数据清单中的数值是否符合特定条件 主要包括 DAVERAGE DCOUNT DCOUNTA DGET DMAX DMIN 等函数 (2) 日期与时间函数 : 主要用于在公式中分析和处理日期值和时间值, 主要包括 DATE TODAY DAY NETWORKDAYS MONTH WEEKDAY 等函数 (3) 工程函数 : 用于工程数据分析与处理, 主要包括 BESSELI BIN2DEC COMPLEX CONVERT DELTA ERF 等函数 (4) 信息函数 : 用于判定单元格或公式中的数据类型, 主要包括 CELL INFO ISERR NA ISODD ISREF 等函数 (5) 财务函数 : 用于财务分析和财务数据计算, 主要包括 PRICE RATE FV PV NPV PMT IRR 等函数 (6) 逻辑函数 : 用于逻辑判断或者进行复合检验, 主要包括 AND NOT OR TRUE FLASE IFERROR 等函数 (7) 统计函数 : 用于对数据区域进行统计分析, 主要包括 AVEDEV AVERAGE COUNT

27 项目一 Excel 基础实训 19 COVARIANCE.P DEVSQ MAX 等函数 (8) 查找和引用函数 : 用于在数据清单中查找特定数据或者查找一个单元格的引用, 主要包括 ADDRESS LOOKUP INDEX MATCH COLUMN OFFSET 等函数 (9) 文本函数 : 用于在公式中处理字符串, 主要包括 ASC CLEAN CODE FIND LEFT LEN REPT 等函数 (10) 数学和三角函数 : 用于数学计算, 主要包括 ABS EXP LN LOG POWER ROUND SIN 等函数 这里只做个简单介绍, 具体的函数参数 用途和操作过程将在本书的其他章节体现 实训二 Excel 图表的创建与编辑 知识准备 Excel 2010 版中图表的基本操作知识 实训目的与要求 熟练掌握 Excel 图表的创建与编辑 实训指导 一 认识 Excel 2010 中的图表 Excel 2010 创建图表更加可视化, 更加简单形象 在 Excel 2010 中单击 插入, 并将鼠标移到 图表 功能区, 单击右下角的按钮便可弹出 插入图表 对话框, 如图 1-37 所示 图 1-37 插入图表 对话框 Excel 2010 中的图表主要包括柱形图 折线图 饼图 条形图 面积图 散点图和其他 图表 图表的意义如图 1-38 所示

28 20 基于 Excel 的财务管理综合模拟实训 图 1-38 Excel 2010 中图表的意义 二 图表的创建与编辑 例 1-8 根据数据表格创建图表 (1) 按照图 1-39, 将数据表格填入 Excel 表格中 图 1-39 数据表格 (2) 选中数据区域, 单击 插入, 并将鼠标移到 图表 功能区, 单击 柱形图, 再 选择柱形图样式, 这时选中的数据就以柱形图形式显示出来了, 如图 1-40 所示 图 1-40 插入柱形图

29 项目一 Excel 基础实训 21 (3) 在柱形图的基础上可以利用鼠标拖动的方式改变柱形图的大小, 如图 1-41 所示 (4) 选中棕色的柱状图, 右击鼠标右键, 在弹出的快捷菜单中选择 设置数据系列格 式, 如图 1-42 所示 图 1-41 改变柱形图大小 图 1-42 设置数据系列格式 (5) 在弹出的对话框中选择 次坐标轴, 然后单击 关闭 按钮, 如图 1-43 所示 (6) 选中 同比增长, 然后单击右键, 选择 更改系列图表类型, 如图 1-44 所示 图 1-43 选择 次坐标轴 图 1-44 更改系列图表类型 (7) 在弹出的对话框中选择 折线图, 选中一个适当的折线图模板, 如图 1-45 所示 (8) 选择 主纵轴, 然后单击右键, 选择 设置坐标轴格式, 弹出 设置坐标轴格式 对话框, 这时就可以修改 主纵轴 的坐标刻度值, 如图 1-46 所示

30 22 基于 Excel 的财务管理综合模拟实训 图 1-45 更改成折线图 图 1-46 设置坐标轴格式 对话框 (9) 添加图表标题及纵坐标标题, 如图 1-47 和图 1-48 所示 图 1-47 添加图表标题 图 1-48 添加坐标轴标题 最终效果如图 1-49 所示 图 1-49 最终效果图

31 项目一 Excel 基础实训 23 实训三 Excel 数据管理 知识准备 数据排序 筛选和分类汇总, 以及数据透视表和数据透视图的基本操作 实训目的与要求 熟练掌握数据的排序 筛选和分类汇总, 以及数据透视表和数据透视图 实训指导 一 数据管理 1. 数据的排序在实际工作中, 输入的表格数据都是无序排列的 根据数据管理与分析的需要, 可以将表格中的记录按升序或降序进行排列, 以方便观察和分析表格中的数据 数据的排序是根据数据表格中的相关字段名, 将数据表格中的记录按升序或降序的方式进行排列 Excel 的排序分升序和降序两大类型, 对于字母, 升序是从 A 到 Z 排列 ; 对于数字, 升序是按数值从小到大排列的 Excel 使用如表 1-2 所示的排序规则 表 1-2 表格的排序规则符号排序规则 ( 升序 ) 数字字母文本以及包含数字的文本逻辑值错误值空格 数字从最小的负数到最大的正数进行排序按字母先后顺序排序, 在按字母先后顺序对文本项进行排序时,Excel 从左到右一个字符接一个字符地进行排序 ( 空格 )! " # $ % & ( ) *,. / : [ \ ] ^ _ ` { } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 在逻辑值中,FALSE 排在 TRUE 之前所有错误值的优先级相同空格始终排在最后 在 Excel 2010 中, 对表格排序可以按单个条件进行排序, 还可以按多个条件进行先后排序 选中数据区域中的任一单元格, 单击 数据 选项卡 排序与筛选 功能组中的 排序 命令, 则会弹出 排序 对话框, 在 排序 对话框中选择主要关键字, 再选择排序的依据和次序, 如图 1-50 所示 单击 选项 按钮, 弹出 排序选项 对话框, 可以选择排序的方向和方法, 如图 1-51 所示

32 24 基于 Excel 的财务管理综合模拟实训 图 1-50 排序 对话框图 1-51 排序选项 对话框在 Excel 2010 中, 也可以利用自定义序列进行排序 单击 文件, 在弹出的对话框中选择 Excel 选项, 则弹出 Excel 选项 对话框, 如图 1-52 所示 在 Excel 选项 对话框中, 单击打开 高级 选项卡, 然后单击 编辑自定义列表, 弹出 自定义序列 对话框, 在 输入序列 列表框中输入自定义的序列, 比如 财务部 人事部 生产部 销售部 研发部, 单击 添加 按钮, 则新序列就添加到了自定义序列中, 最后单击 确定 按钮即可, 如图 1-53 所示 图 1-52 Excel 选项 对话框 图 1-53 自定义序列 对话框

33 项目一 Excel 基础实训 25 例 1-9 对工资考核成绩表进行排序 (1) 打开员工考核成绩表, 选择单元格区域 B3:E15, 如图 1-54 所示 (2) 单击 数据 选项卡 排序和筛选 组中的 排序 按钮, 如图 1-55 所示 图 1-54 在考核成绩表中选择单元格区域 图 1-55 排序 按钮 (3) 弹出 排序 对话框, 在 主要关键字 下拉列表框中选择 工作能力 选项, 在 次序 下拉列表框中选择 降序 选项, 然后单击 添加条件 按钮, 添加次要关键字, 在 次要关键字 下拉列表框中选择 沟通能力 选项, 在 次序 下拉列表框中选择 降序 选项, 如图 1-56 所示 (4) 单击 确定 按钮, 完成排序, 结果如图 1-57 所示 图 1-56 排序 对话框 图 1-57 排序结果 2. 数据的筛选 (1) 自动筛选自动筛选是最简单的筛选, 在一般情况下使用自动筛选能够满足最基本的筛选要求 例 1-10 对公司员工工资表进行自动筛选 1 任意选择数据单元格, 然后单击 数据 选项卡 排序和筛选 组中的 筛选 按钮, 如图 1-58 所示 2 自动筛选数据表后, 将在数据表的列标题上出现筛选下拉按钮, 如图 1-59 所示

34 26 基于 Excel 的财务管理综合模拟实训 图 1-58 筛选 按钮 图 1-59 自助筛选下拉按钮 3 单击标题名称 所在部门 右侧的下拉按钮, 在弹出的下拉面板的列表框中选中 办公室 复选框, 如图 1-60 所示 4 单击 确定 按钮, 筛选出办公室员工的工资情况数据表, 效果如图 1-61 所示 图 1-60 筛选下拉列表框 图 1-61 办公室员工的工资筛选结果 (2) 自定义筛选在自动筛选后, 用户还可以自定义筛选的条件, 从而筛选出满足要求的数据 例 1-11 对公司员工工资表进行自定义筛选 1 打开公司员工的工资表, 选择任意数据单元格, 单击 数据 选项卡 排序和筛选 组中的 筛选 按钮, 自动筛选, 如图 1-62 所示 2 单击 基本工资 列右侧的下拉按钮, 在弹出的下拉面板中选择 数字筛选 中的 大于 选项, 如图 1-63 所示

35 项目一 Excel 基础实训 27 图 1-62 自动筛选 图 1-63 自定义筛选列表 3 弹出 自定义自动筛选方式 对话框, 输入数值 6000, 如图 1-64 所示 4 单击 确定 按钮, 筛选的结果如图 1-65 所示 图 1-64 自定义自动筛选方式 对话框 图 1-65 自定义筛选结果 (3) 高级筛选当需要使用多个条件对数据进行筛选时, 可以利用高级筛选, 例 1-12 利用高级筛选将员工档案资料表中性别为女性, 学历为专科的数据筛选出来 1 打开员工档案资料表, 如图 1-66 所示, 然后在单元格区域 I5:J6 中输入条件, 同一行中的条件是与条件, 即两个条件必须同时满足, 如图 1-67 所示 图 1-66 员工档案资料表 图 1-67 建立条件区域

36 28 基于 Excel 的财务管理综合模拟实训 2 单击 数据 选项卡 排序和筛选 组中的 高级 按钮, 弹出 高级筛选 对话 框, 分别设置 列表区域 和 条件区域, 如图 1-68 所示 3 单击 确定 按钮, 进行高级筛选, 筛选结果如图 1-69 所示 图 1-68 高级筛选 对话框图 1-69 高级筛选结果 (1) 还是以上例为例, 如果在筛选时, 只要满足多个条件中的某个条件, 则就被筛选出来, 可按如下方法操作 : 1 在员工档案资料表中, 重新设置条件区域, 不同行中的条件是或条件, 即两个条件只要满足其一即可, 如图 1-70 所示 2 打开 高级筛选 对话框, 分别设置 列表区域 和 条件区域, 如图 1-71 所示 图 1-70 建立条件区域 图 1-71 高级筛选 对话框 3 单击 确定 按钮, 进行高级筛选, 筛选结果如图 1-72 所示 图 1-72 高级筛选结果 (2)

37 项目一 Excel 基础实训 分类汇总 (1) 自动分类汇总分类汇总就是利用汇总函数对同一类别中的数据进行计算, 得到统计结果 使用 分类汇总 功能时, 一定要先按分类字段对数据表进行排序 经过分类汇总, 可分级显示汇总结果 例 1-13 对公司员工工资表按照部门进行分类汇总 1 打开公司员工工资表, 选择单元格 B3, 单击 数据 选项卡 排序和筛选 组中的 升序 按钮, 对该列进行排序, 然后选择单元格区域 B3:G15, 如图 1-73 所示 2 单击 数据 选项卡 分级显示 组中的 分类汇总 按钮, 弹出 分类汇总 对话框, 在 分类字段 下拉列表框中选择 所在部门 选项, 在 汇总方式 下拉列表框中选择 求和 选项, 在 选定汇总项 列表框中选中 基本工资 补助 和 保险 复选框, 如图 1-74 所示 图 1-73 在公司员工工资表中选择 图 1-74 分类汇总 对话框 3 单击 确定 按钮, 创建分类汇总, 如图 1-75 所示 图 1-75 分类汇总结果 4 单击窗口编辑区域左侧的 5 单击窗口编辑区域左侧的 按钮, 显示二级汇总数据, 如图 1-76 所示 按钮, 显示一级汇总数据, 如图 1-77 所示

38 30 基于 Excel 的财务管理综合模拟实训 图 1-76 分类汇总 二级汇总数据 图 1-77 分类汇总 一级汇总数据 (2) 清除分级显示符号如果在分类汇总时, 不显示分级显示符号, 可单击 数据 选项卡 分级显示 组中的 取消组合 下拉按钮, 在弹出的下拉菜单中选择 清除分级显示 选项, 如图 1-78 所示 图 1-78 清除分级显示 选项 根据上例, 清除分级显示符号的效果如图 1-79 所示 图 1-79 清除分级显示 效果 (3) 打印分类汇总数据在 Excel 中, 如果用户要分类打印出各种汇总数据, 可通过 分类汇总 对话框轻松实现 例 1-13 打印客户订单明细表中不同客户的订单信息 1 打开客户订单明细表, 如图 1-80 所示 2 选择单元格 B3, 然后单击 数据 选项卡 排序和筛选 组中的 升序 按钮, 将 客户名称 列按升序排列, 效果如图 1-81 所示

39 项目一 Excel 基础实训 31 图 1-80 客户订单明细 图 1-81 客户订单明细排序后的效果 3 单击 数据 选项卡 分级显示 组中的 分类汇总 按钮, 弹出 分类汇总 对话框, 从中设置 分类字段 为 客户名称, 汇总方式 为 计数, 在 选定汇总项 列表框中选中 订单号 和 备注 复选框, 并选中 每组数据分页 复选框, 如图 1-82 所示 4 单击 确定 按钮, 将创建分类汇总并自动分页, 切换到分页视图可查看分页效果, 如图 1-83 所示, 在进行打印时, 将分页打印不同客户名称的订单信息 图 1-82 分类汇总 对话框 图 1-83 分类汇总 效果 (4) 分类汇总数据的复制 1 按 F5 键, 弹出 定位 对话框, 从中单击 定位条件 按钮, 如图 1-84 所示 2 弹出 定位条件 对话框, 选中 可见单元格 单选按钮, 如图 1-85 所示 3 单击 确定 按钮, 关闭 定位条件 对话框, 选定单元格, 按 Ctrl+C 组合键, 然后切换到 Sheet2 工作表中, 按 Ctrl+V 组合键, 粘贴分级显示数据即可

40 32 基于 Excel 的财务管理综合模拟实训 (5) 删除汇总数据选择任意汇总单元格, 然后单击 数据 选项卡 分级显示 组中的 分类汇总 按钮, 在弹出的 分类汇总 对话框中单击 全部删除 按钮, 如图 1-86 所示, 即可删除分类汇总 图 1-84 定位 对话框图 1-85 定位条件 对话框图 1-86 删除分类汇总 二 数据透视表和数据透视图利用数据透视表可以快速汇总大量数据并进行交互, 还可以深入分析数值数据, 并回答一些预计不到的数据问题 使用 Excel 数据透视图可以将数据透视表中的数据可视化, 以便于查看 比较和预测趋势, 帮助用户做出关键数据的决策 1. 建立数据透视表 例 1-14 根据年销售统计表建立数据透视表 (1) 打开工作表, 选中数据区域中任意单元格, 单击 插入 选项卡 表格 组中 数据透视表 按钮下方的下拉按钮, 在弹出的下拉菜单中选择 数据透视表, 如图 1-87 所示 图 1-87 选择 数据透视表 (2) 弹出 创建数据透视表 对话框, 单击 表 / 区域 文本框右侧的折叠按钮, 选择数

41 项目一 Excel 基础实训 33 据区域, 如图 1-88 所示 (3) 再次单击折叠按钮, 展开对话框, 其他选项保持默认, 如图 1-89 所示 在 创建数据透视表 对话框的 选择放置数据透视表的位置 选项区中选中 新工作表 单选按钮, 则在创建数据透视表的同时新建新工作表 ; 若选中 现有工作表 单选按钮, 可在所选位置创建数据透视表 图 1-88 选择数据区域 图 1-89 创建数据透视表 对话框 (4) 单击 确定 按钮, 在新工作表中创建数据透视表, 此时, 新工作表中将显示 数据透视表字段列表 任务窗格, 如图 1-90 所示 (5) 在 数据透视表字段列表 任务窗格的 选择要添加到报表的字段 选项区中选中要在数据透视表中显示的字段, 最终效果如图 1-91 所示 图 1-90 数据透视表字段列表 任务窗格 图 1-91 数据透视表最终效果 2. 数据透视表布局用户可根据不同的需求, 创建不同的透视表, 具体操作步骤如下 (1) 在表格中选择任意单元格, 单击 插入 选项卡 表格 组中 数据透视表 按钮下方的下拉按钮, 在弹出的下拉菜单中选择 数据透视表 选项, 弹出 创建数据透视表 对话框 (2) 保持默认设置, 单击 确定 按钮, 新建工作表, 并显示数据透视表选项, 如图 1-92 所示

42 34 基于 Excel 的财务管理综合模拟实训 图 1-92 数据透视表字段列表 任务窗格 (3) 根据用户需要, 在 选择要添加到报表的字段 列表中选中需要显示字段, 此时 的数据透视表字段如图 1-93 所示 图 1-93 选择数据透视表字段 (4) 使用鼠标将 数据透视表字段列表 任务窗格的 行标签 选项区中的 产品名称 选项拖动到 列标签 选项区中, 如图 1-94 所示 视表 图 1-94 选择行标签和列标签 (5) 在 选择要添加到报表的字段 列表中选中其他需要显示字段, 得到不同的数据透

43 项目一 Excel 基础实训 35 用户可以根据需要, 调整数据透视表的布局, 具体操作步骤如下 (1) 在表格中选择任意单元格, 单击 设计 选项卡 布局 组中 报表布局 下拉按钮, 在弹出的下拉菜单中选择 以大纲形式显示 选项, 如图 1-95 所示 改变布局后的数据透视效果如图 1-96 所示 图 1-95 以大纲形式显示 选项 图 1-96 大纲形式显示的数据透视 (2) 若在 报表布局 下拉菜单中选择 以表格形式显示 选项, 效果如图 1-97 所示 图 1-97 表格形式显示的数据透视表 3. 更改数据透视表汇总方式默认情况下, 数据透视表的汇总方式为求和, 用户可根据需要自定义某一项数据的汇总方式, 操作方法如下 (1) 单击 数据透视表字段列表 任务窗格的 数值 选项区中 求和项 : 总销售额 选项右侧的下拉按钮, 在弹出的快捷菜单中选择 值字段设置 选项, 如图 1-98 所示 (2) 弹出 值字段设置 对话框, 在 选择用于汇总需所选字段数据的计算类型 列表框中选择 最大值 选项, 如图 1-99 所示 图 1-98 值字段设置 选项 图 1-99 值字段设置 对话框

44 36 基于 Excel 的财务管理综合模拟实训 (3) 单击 确定 按钮, 将数据透视表的计算方式更改为求最大值, 此时的数据透视表 最终效果如图 所示 图 数据透视表最终效果 4. 添加计算字段到数据透视表在实际工作中, 经常需要按照不同的方法对数据透视表数据进行汇总, 在数据透视表中添加计算字段, 可以完善数据的汇总, 具体操作步骤如下 (1) 数据透视表中选择任意单元格, 单击 选项 选项卡 计算 组中的 域 项目和集 下拉按钮, 在弹出的下拉菜单中选择 计算字段 选项, 如图 所示 图 计算字段 选项 (2) 弹出 插入计算字段 对话框, 在 名称 下拉列表框中输入 净收益, 在 公式 文本框中输入公式 = 销售额 *(1-0.35)-15000, 如图 所示 (3) 单击 添加 按钮, 将新字段添加到 字段 列表框中, 然后单击 确定 按钮, 此时的数据透视表中显示 净收益 字段, 并按公式计算得出结果, 如图 所示 图 插入计算字段 对话框 图 数据透视表最终效果 5. 添加计算项到数据透视表 通过向数据透视表中添加计算项, 可以满足用户各种汇总要求, 其添加方法如下

45 项目一 Excel 基础实训 37 (1) 数据透视表中选择列标签行中任意单元格, 单击 选项 选项卡 计算 组中的 域 项目和集 下拉按钮, 在弹出的下拉菜单中选择 计算字段 选项, 如图 所示 图 计算字段 选项 (2) 弹出 在 网点 中插入计算字段 对话框, 在 名称 下拉列表框中输入名称 季度净利润, 在 公式 文本框中输入公式 =( 北京总店 + 河北分店 + 湖北分店 + 江苏分店 )* , 如图 所示 (3) 单击 添加 按钮, 再单击 确定 按钮, 添加计算项后的数据透视表如图 所示 图 在 网点 中插入计算字段 对话框 图 添加计算项后的数据透视表 (4) 选中数据透视表任意单元格, 单击鼠标右键, 在弹出的快捷菜单中选择 数据透视表选项 选项, 弹出 数据透视表选项 对话框, 如图 所示 (5) 单击 汇总和筛选 选项卡, 取消选择 总计 选项区中的 显示行总计 复选框, 如图 所示 图 数据透视表选项 对话框 图 汇总和筛选 选项卡

46 38 基于 Excel 的财务管理综合模拟实训 (6) 单击 确定 按钮, 此时的数据透视表效果如图 所示 图 数据透视表效果图 6. 组合数据透视表内的项使用数据透视表进行数据汇总, 有时得到的汇总数据很多, 分析起来有一定难度, 用户可通过将数据透视表的部分项组合, 来解决这个问题, 具体操作步骤如下 (1) 打开需要组合项的数据透视表, 选择行标签中任意一个单元格, 单击 选项 选项卡 分组 组中的 将所选内容分组 按钮, 如图 所示 图 将所选内容分组 按钮 (2) 弹出 分组 对话框, 在 步长 选项区中同时选中 季度 和 年 选项, 如图 所示 (3) 单击 确定 按钮, 将 时间 列中的数据进行分组, 效果如图 所示 图 分组 对话框 图 数据透视表效果图

47 项目一 Excel 基础实训 更新数据透视表创建好数据透视表后, 如源工作表中的数据发生变化, 则需要更新数据透视表, 在数据透视表中选择任意单元格, 单击 选项 选项卡 数据 组中的 刷新 按钮下方的下拉按钮, 在弹出的下拉菜单中选择 全部刷新 选项即可 8. 数据透视图用 Excel 数据透视图可以将数据透视表中的数据可视化, 以便于查看 比较和预测趋势, 帮助用户做出关键数据的决策 例 1-15 根据 客户订单明细表 建立数据透视图 (1) 打开要创建数据透视图的工作表, 选中任意单元格, 单击 插入 选项卡 表格 组中 数据透视表 按钮下方的下拉按钮, 在弹出的下拉菜单中选择 数据透视图 选项, 如图 所示 图 数据透视表图 选项 (2) 弹出 创建数据透视表及数据透视图 对话框, 单击 表 / 区域 文本框右侧的折叠按钮, 选择数据区域, 如图 所示 图 选择数据来源 (3) 再次单击折叠按钮, 展开对话框, 其他选项保持默认, 如图 所示 (4) 单击 确定 按钮, 在新工作表中创建数据透视表和数据透视图, 此时, 新工作表中将显示 数据透视表字段列表 和 数据透视图筛选窗格 任务窗格, 如图 所示

48 40 基于 Excel 的财务管理综合模拟实训 (5) 在 数据透视表字段列表 任务窗格的 选择要添加到报表的字段 选项区中选中 要在数据透视表中显示的字段, 创建数据透视表和透视图, 如图 所示 图 创建数据透视表 对话框 图 数据透视表字段列表 和 数据透视图筛选 窗格 任务窗格 图 数据透视表字段列表 任务窗格 (6) 数据透视图的最终效果如图 所示 图 数据透视图的最终效果

49 项目一 Excel 基础实训 41 (7) 选择数据透视图, 在其上单击鼠标右键, 在弹出的快捷菜单中选择 更改图表类型 选项, 如图 所示 图 更改图表类型 (8) 弹出 更改图表类型 对话框, 在左侧列表中选择 条形图 选项, 在右侧的选项 区中选择 簇状条形图 选项, 如图 所示 图 选择图表类型

50 42 基于 Excel 的财务管理综合模拟实训 (9) 单击 确定 按钮, 更改数据透视图类型后的效果, 如图 所示 图 更改数据透视图类型后的效果 实训四 Excel 数据分析工具 实训准备 单变量求解 模拟运算表 规划求解的基本操作 实训目的与要求 熟练掌握单变量求解 模拟运算表 规划求解的应用 实训指导 一 数据分析工具 Excel 2010 提供了很多实用的数据分析工具, 主要包括财务分析工具 统计分析工具 规划求解和方案管理器等 利用这些分析工具, 可以有效解决财务管理中的很多问题 下面主要介绍单变量求解 模拟运算表等 1. 单变量求解 单变量求解 是求解只有一个变量的根 如果已知单个公式的预期结果, 而用于确定此公式结果的输入值未知, 则可使用 单变量求解 功能 例 1-16 某人现在存入银行一笔钱, 年利率为 8%, 在复利的计息方式下,10 年后达到 3000 元, 问现在该存入多少钱? 这个问题可以通过单变量求解方法计算, 具体操作步骤如下 (1) 在 C2 单元格内使用公式 =A2*(1+B2)^10, 如图 所示