Excel在财务分析中的应用.ppt

Excel在财务分 析中的应用 目录 第一章 第二章 第三章 第四章 第五章 第六章 开始之前 管理 Excel工作表 让 Excel的函数为你工作 简单的几招让财务数据的处理得心应手 分析数据 财务分析报表的格式设计和美化 运用 Excel图表来阐述财务分析结果 管理 Excel工作表 更改工作表的保存位置 单击 “ Office 按钮” ， 选择 Excel选项 点击保存，显示如右 图 可以根据个人工作习惯更改 设置 管理 Excel工作表 - Excel工作簿中的字体和字号 默认情况下， Microsoft Office Excel 2007 使用字号为 “ 11”的 “正文字体” （当 您在工作表、页眉或页脚或者文本框中键入数据时，它们显示为字号为 “ 11”的 “ Calibri”字体） 在日常的财务工作中，我们可以选择中文的默认字体是宋体，英文的默认字 ARIAL. 所以如果绝大多数工作表是中文输入，则将默认字体设为宋体。字号视 乎个人的使用习惯，一般是 10号 单击 “ Office 按钮” ，然后单击 “ Excel 选项” 。 在 “常用” 类别中的 “新建工作簿时” 下，执行下列操作： 在 “使用的字体” 框中，单击要使用的字体 在 “字号” 框中，输入要使用的字号 为了开始使用新的默认字体和字号，必须重新启动 Excel。新的默认字体和字号只 应用于重新启动 Excel 后创建的新工作簿，现有工作簿不受影响。若要使用新的默认 字体，可以将工作表从现有工作簿移到新工作簿 (参见移动或复制工作表 ) 管理 Excel工作表 -更改新工作簿中的工作表数 默认情况下， Excel 的工作簿内提供三个工作表 。但是有的时候我们发出去的工 作簿可能只是在第一个工作表上有数据，后面两张是 excel自带的，所以为了避 免误解和保证财务工作的严谨性，我们可以更改将新工作簿内默认显示的工作表 数从三个变成一个。 单击 “ Office 按钮” ，然后单击 “ Excel 选项” 。 在 “常用” 选项卡上 “新建工作簿时” 下的 “包含的工作表数” 框中，输入一 必须重新启动 Excel新的设置才会生效，并且 新的设置只应用于重新启动 Excel 后创建的新工作簿，现有工作簿不受影响 目录 第一章 第二章 第三章 第四章 第五章 第六章 开始之前 管理 Excel工作表 让 Excel的函数为你工作 简单的几招让财务数据的处理得心应手 分析数据 财务分析报表的格式设计和美化 运用 Excel图表来阐述财务分析结果 Excel 函数 - Product PRODUCT(number1, number2, .) PRODUCT 函数语法具有以下参数 number1 必需。要相乘的第一个数字或单元格区域 number2, . 可选。要相乘的其他数字或单元格区域，最多可以使用 255 个参数。 如果参数为数组或引用，只有其中的数字将被计算乘积。数组或引用中的空白单元格、 逻辑值和文本将被忽略。 如果需要让许多单元格相乘，则使用 PRODUCT 函数很有用。例如，公式 =PRODUCT(A1:A3, C1:C3) 等同于 =A1 * A2 * A3 * C1 * C2 * C3。 Excel 函数 Sumproduct介绍 语法 : SUMPRODUCT(array1,array2,array3, .) Array1, array2, array3, . 为 2 到 255 个数组，其相应元素需要进行相乘并求和。 数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。 =SUMPRODUCT(A2:A4,B2:B4) 两个数组的所有元素对 应相乘，然后把乘积相加，即 3*4 + 8*7 + 2*3 = 74 2 1 3 =SUMPRODUCT(A8:B10,C8:D10) 两个 数组的所有元素对应相乘，然后把乘积 相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3 = 156 =SUMPRODUCT(A15:B17,C15:D 17,E15:F17) 两个数组的所有元素 对应相乘，然后把乘积相加，即 3*5*7 + 4*2*6 + 8*7*7 + 6*6*5 + 1*3*5 + 9*5*4 = 914 Sumproduct的财务分析应用 举例： 计算产品单位总成本。 产品 A由若干个零部件组成，每个零部件的材料用 量和单价各异，运用 Sumproduct可以很容易计算出产品总成本 =SUMPRODUCT(B4:B20,C4:C20)+SUMPRODUCT(F4:F20, G4:G20)+SUMPRODUCT(J4:J20,K4:K20) = 857.3605 Excel 函数 Sumif介绍 使用 SUMIF 函数可以对区域 中符合指定条件的值求和。 SUMIF(range, criteria, sum_range) SUMIF 函数语法具有以下参数 range 必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数 字或名称、数组或包含数字的引用。空值和文本值将被忽略。 criteria 必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表 达式、单元格引用、文本或函数。例如，条件可以表示为 32、 32、 B5、 32、 32、 苹果 或 TODAY()。 要点 任何文本条件或任何含有逻辑或数学符号的条件 都必须使用双引号 () 括起来。如果条件为数字，则无需使用双引号。 sum_range 可选。要求和的实际单元格（如果要对未在 range 参数中指定的 单元格求和）。如果 sum_range 参数被省略， Excel 会对在 range 参数中指 定的单元格（即应用条件的单元格）求和。 sum_range 参数与 range 参数的大小和形状可以不同 Sumif的简单举例 Excel 函数 Sumifs介绍 SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ) sum_range 必需。对一个或多个单元格求和，包括数值或包含数值的名称、区域或单元 格引用 ，忽略空白和文本值。 criteria_range1 必需。在其中计算关联条件的第一个区域。 criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对 criteria_range1 参数中的哪些单元格求和。例如，条件可以表示为 32、 32、 B4、 苹 果 或 32。 criteria_range2, criteria2, 可选。附加的区域及其关联条件。最多允许 127 个区域 /条 件对。 SUMIFS 函数中每个 criteria_range 参数包含的行数和列数必须与 sum_range 参数相同 仅在 sum_range 参数中的单元格满足所有相应的指定条件时，才对该单元格求和。例如， 假设一个公式中包含两个 criteria_range 参数。如果 criteria_range1 的第一个单元格满足 criteria1，而 criteria_range2 的第一个单元格满足 critera2，则 sum_range 的第一个单元 格计入总和中。对于指定区域中的其余单元格，依此类推。 在条件中可以使用通配符，即问号 (?) 和星号 (*)。问号匹配任一单个字符；星号匹配任 一字符序列。请在字符前键入波形符 来查找实际的问号或星号 Sumifs的简单举例 Sumifs的财务分析应用 某企业用如下报告来追踪收入支出及净利的路线图，如何将路线图的追踪结 果分开机会与风险，细化到地区及业务线，分开完成日期来审阅呢？ Sumifs的财务分析应用 (续 ) 某企业用如下报告来追踪收入支出及净利的路线图，如何将路线图的追踪结 果分开 机会与风险 ，细化到 地区及业务线 ，分开 红绿灯 来审阅呢？ 课堂练习 Countif 的介绍 COUNTIF 函数对区域中满足单个指定条件的单元格进行计数。 语法 COUNTIF(range, criteria) COUNTIF 函数语法具有下列参数 （ range 必需。要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或 包含数字的引用。空值和文本值将被忽略。 criteria 必需。用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或 文本字符串。例如，条件可以表示为 32、 32 、 B4、 苹果 或 32。 Countifs的介绍 COUNTIF 函数对区域中满足多个指定条件的单元格进行计数。 语法 : COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) criteria_range1 必需。在其中计算关联条件的第一个区域。 criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单 元格进行计数。 criteria_range2, criteria2, . 可选。附加的区域及其关联条件。最多允许 127 个区域 /条件对。 点 每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。这些区域无需 彼此相邻。 Excel 函数 CONCATENATE函数介绍 语法 : CONCATENATE (text1,text2,.) Text1, text2, . 为 2 到 255 个将要合并成单个文本项的文本项。这些文本项可以为文本 字符串、数字或对单个单元格的引用。 注解 -也 可使用连接符号 ( Text指需要清除其中空格的文本。 举例： 公式 = TRIM( First Quarter Earnings ) 结果：删除公式中文本的前导空格和尾部空格 (First Quarter Earnings) 在财务的日常工作中，我们经常需要从系统里面导出数据，有时这些数据 由于前端或者后端包含空格，经常会导致在引用的时候不能完全匹配。一 个简单的应用就是运用 trim函数将要使用的数据去除头尾的空格 Excel 函数 Vlookup介绍 VLOOKUP在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列 的值。 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Lookup_value 为需要在表格数组 第一列中查找的数值。 Lookup_value 可以为数值或引 用。若 lookup_value 小于 table_array 第一列中的最小值， VLOOKUP 返回错误值 #N/A。 Table_array 为两列或多列数据。使用对区域或区域名称的引用。 table_array 第一列中的 值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。 Col_index_num 为 table_array 中待返回的匹配值的列序号。 Col_index_num 为 1 时，返 回 table_array 第一列中的数值； col_index_num 为 2，返回 table_array 第二列中的数值， 以此类推。如果 col_index_num ： 小于 1， VLOOKUP 返回错误值 #VALUE!。 大于 table_array 的列数， VLOOKUP 返回错误值 #REF!。 Range_lookup 为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值： 如果为 TRUE 或省略，则返回精确匹配值或近似匹配值 1. 如果为 TRUE 或省略，则返回精确匹配值或近似匹配值 2. 如果为 FALSE， VLOOKUP 将只寻找精确匹配值 Vlookup的财务分析应用 对于用编码来管理的信息，尽量输入编码，再使 用 vlookup函数将编码对应的文本信息调出，完善 数据收集工作 Vlookup函数 编码及其对应的文本信息 关键字段 保证关键字段的符合系统设定 对于由关键字段导出的文本信 息，尽量使用 excel的查找功能 实现，而非手工输入 Vlookup 课堂练习 练习对 K4/L4,写入 vlookup公式，然后拷贝至第 23行 练习对 S4/T4,写入 vlookup公式，然后拷贝至第 23行 Excel 函数 Round函数介绍 ROUND 函数可将某个数字四舍五入为指定的位数 语法 ROUND(number, num_digits) number 必需。要四舍五入的数字 num_digits 必需。位数，按此位数对 number 参数进行四舍五入 1. 如果 num_digits 大于 0（零），则将数字四舍五入到指定的小数位 2. 如果 num_digits 等于 0，则将数字四舍五入到最接近的整数 3. 如果 num_digits 小于 0，则在小数点左侧进行四舍五入 Excel 函数 Roundup， Rounddown函数 ROUNDUP , ROUNDDOWN和函数 ROUND 功能相似，不同之处在于 函数 ROUNDUP 总是向上舍入数字而 ROUNDDOWN总是向上舍入数字 日期函数 原始录入只需输入日期，对应的的年度月份通过书写 日期函数获得，从而避免手工输入的重复劳动及可能 出现的错误或格式不整齐 函数 month (date),对一个日期格式的单元格取值，返 回当前单元格该日期对应的月份； 函数 year (date)折 返回当前单元格该日期对应的年份 下面是两组例子，分别针对日期格式用函数获取日期 对应的月份和年份 目录 第一章 第二章 第三章 第四章 第五章 第六章 开始之前 管理 Excel工作表 让 Excel的函数为你工作 简单的几招让财务数据的处理得心应手 分析数据 财务分析报表的格式设计和美化 运用 Excel图表来阐述财务分析结果 传统财务分析数据处理 输入：数据收集模板内容过于简单，或者缺乏钩 稽关系，或者格式不统一，从而造成后期的汇总 和分析困难重重。 中间整理：数据汇总较少运用公式、函数、链结， 基本仍停留在将 Excel当在电子草稿纸使用的阶段。 表与表之间很少或无数据联动关系。 输出：难以形成系统的分析模式，基本是头痛医 头，脚痛医脚。没有办法形成财务分析报表体系 利用 Excel整理财务分析数据 财务分析针对各种目的，从不同角度出发，通常 让初入门者觉得目不暇接 其实任何财务分析都是构建在数据基础之上，再 结合企业的业务特点，战略目标所展开的。 所以如何构建一个大而全的数据库是进行财务分 析的基础 原始数据录入模板举例 日期类输入， 格式统一； 从而便于针 对 日期进行分析 数字类输入，格式统一， 无论是作为报告阅读还 是作为数据源调用都很 方便 涉及到统一编码的输入应录入编码，应 录入 编码， 由 编码查找到对 应 的文字信息 ； 这样可以保证收集到的数据整齐划一 原始数据录入 通常原始数据数量庞大，录入工作是协同完 成的。如果事先没有很好的规划，各自为政 的话，很可能导致录入格式不一致，或字段 设置不同，给更进一步的数据汇总分析工作 带来无穷的麻烦 为了是整个工作可以更好的协调，通常我们 会依赖 excel的一些简单功能来规范原始数据 的录入工作。 数据有效性 控制原始数据的录入质量 数据有效性 控制输入格式 提示信息减低输入错误 错误提示 完成一个单元格的定义后很方便拷 贝到多个单元格 数据有效性 单元格设置举例 打开本课件下面带的练习文件 (原始数据录入 -模板设计 .xls) 鼠标移动到要定义的单元格 B4 鼠标点开菜单路径 数据 -数据有效性 数据有效性 单元格设置举例 双井打开数据有效性，鼠标移动到第一张标签，因为这个单元格是用于录入订单 号的，而且我们知道标准订单号是 10位的，所以我们在允许输入的下拉菜单中， 点开文本长度 ,将数据长度定义为等于 10 同样的道理，我们还可以 对日期输入格式进行定义 , 在允许输入的下拉箭头选 择日期即可 数据有效性 单元格设置举例 鼠标移动到第二张标签，定义输入提示信息为销售订单输入 鼠标移动到第二张标签，选择 方式 =停止，然后定义出错信息为销售订单应该是 10 位数 点击确定，完成设置 数据有效性 输入举例 鼠标移动到 B4,这时我们会看到提示信息 销售订单输入 尝试输入 G0100000002，因为这个是 1位，系统会提示 销售订单应该为 10位数 ,并 且拒绝接受这个错误的输入 数据有效性 复制到更多单元格 鼠标移动到 B4,选定 并复制，选择要复制 的单元格（如下图所 示从 B5到 B23），点 击菜单：编辑 -选择 性粘贴；点开后选择 粘贴有效性 ；点击 确定 数据有效性 课堂练习 对订单日期和发货日期定义输入单元格的数据有效性 常见练习文档 原始数据录入 -数据有效性 .xls 由原始数据收集到整理出可用的数据源 原始数据 待用数据源 使用文本导入向导来 导入数据 在日常的财务工作中，我们经常要将系统的数据引出加以分析。很多时候数据引出 的格式是可以多样化的。今天我们选择最简单的文件格式 文本文件，使用 Excel 的文本导入向导来导入数据 启动“文本导入向导”，请在 “数据” 选项卡上的 “获取外部数据” 组中，单击 “自文本” 。然后，在 “导入文本文件” 对话框中，双击要导入的文本文件 步骤 1（共 3 步） 原始数据类型 如果文本文件中的各项以制表符、冒号、分号、空格或其他字符分隔， 请选择 “分隔符号” 。如果每个列中所有项的长度都相同，请选择“固定宽度”。 导入起始行 键入或选择行号以指定要导入数据的第一行。 文件原始格式 选择文本文件中所使用的字符集。多数情况下，可以将此设置保留为 默认设置。 预览文件 该框以文本被分到各工作表列后的形式来显示文本。 使用文本导入向导来 导入数据 步骤 2（共 3 步）（分隔数据） 分隔符号 选择在文本文件中分隔值的字符。如果未列出该字符，则选中 “其他” 复选 框，然后在包含光标的框中键入该字符。如果数据类型为“固定宽度”，则这些选项不 可用。 连续分隔符号视为单个处理 如果数据包含数据字段之间的一个具有多个字符的分隔符 号，或者数据包含多个自定义分隔符号，请选择此选项。 文本识别符号 选择文本文件中将值括起来的字符。当 Excel 遇到文本识别符号时，该 识别符号后、下一识别符号前的所有文本都会作为一个值导入，即使文本中包含一个分 隔符也是如此。 如果分隔符号出现在两个文本识别符号中间，则 Excel 将在导入值中省略识别符号。如 果两个文本识别符号中没有分隔符号，那么 Excel 将在导入值中包含识别符号。 数据预览 在该框中查看文本以验证文本是否按照您期望的方式分到工作表上的列中。 使用文本导入向导来 导入数据 步骤 2（共 3 步）（固定宽度数据） 数据预览 在此部分设置字段宽度。单击预览窗口以设置由竖线表示的分栏符。双击 分栏符可将其删除，也可通过拖动来移动它。 步骤 3（共 3 步） 单击 “高级” 按钮执行下列一项或多项操作： 指定文本文件中所使用的小数分隔符和千位分隔符的类型。在将数据导入到 Excel 中 时，分隔符将与“区域设置”（ Windows“控制面板”）中所指定的您所在国家 /地区 的分隔符相匹配。 指定一个或多个可能包含尾随减号的数值。 列数据格式 单击 “数据预览” 部分中所选列的数据格式。如果不希望导入所选列， 请单击 “不导入此列 (跳过 )”。 选择选定列的数据格式后， “数据预览” 下的列标题将显示该格式。如果选择 “日 期” ，请在 “日期” 框中选择日期格式。 选择与预览数据最接近的数据格式，这样 Excel 便可以准确地转换导入数据。 如果转换可能产生某些意外结果， Excel 将以 常规 格式导入列 使用文本导入向导来 导入数据 课堂练习 定位并重新设置工作表上的最后一个单元格 保存工作簿时， Microsoft Office Excel 仅存储每个工作表中包含数据或格式的一 部分单元格。空单元格可能包含有格式，因而使一行或一列中的最后一个单元格 位于包含数据的单元格区域之外。这会使工作簿的文件过大，并在打印工作表或 工作簿时产生更多的打印页。 要避免这些问题，我们可以通过定位到工作表上最后一个包含数据或格式的单元 格，然后清除可能应用于数据与最后这个单元格之间的空行或空列的所有格式来 重新设置最后这个单元格。 要定位到包含数据或格式的最后一个单元格，请单击工作表中的任意位置，然后 按 Ctrl+End。 清除最后一个单元格与数据之间的所有格式 1. 选择包含数据的最后一列右侧的所有列，然后清除这些列 2. 选择包含数据的最后一行下方的所有行，然后清除这些行 目录 第一章 第二章 第三章 第四章 第五章 第六章 开始之前 管理 Excel工作表 让 Excel的函数为你工作 简单的几招让财务数据的处理得心应手 分析数据 财务分析报表的格式设计和美化 运用 Excel图表来阐述财务分析结果 条件格式 在日常的分析过程中，我们经常需要突出异常情况，趋势， 高低值，或需要关注某一特定区域内的数值。比如说： 在过去五年的利润汇总中，有哪些异常情况？ 过去两年的营销调查反映出哪些倾向？ 这个月哪几个销售人员的销售额超过 ￥ 50,000？ 雇员的总体年龄分布情况如何？ 哪些产品的年收入增长幅度大于 10% 条件格式方便我们使用数据条、色阶和图标集来直观地显 示数据 使用数据条设置所有单元格的格式 选择区域、表或数据透视表中的一个或多个单元格。 在 “开始” 选项卡上的 “样式” 组中，单击 “条件格式” 旁边的箭头，然后单击 “数据条” 。 选择所要的颜色数据条图标 课堂练习 使用三色刻度设置所有单元格的格式 课堂练习 色阶是一种直观的指示来了解数据分布和数据变化。三色刻度使用三种颜色的渐变 来帮助您比较单元格区域。颜色的深浅表示值的高、中、低。 选择区域、表或数据透视表中的一个或多个单元格。 在 “开始” 选项卡上的 “样式” 组中，单击 “条件格式” 旁边的箭头，然后单击 “色阶” 选择三色刻度。最上面的颜色代表较高值，中间的颜色代表中间值，最下面的颜色代表较 低值 使用图标集设置所有单元格的格式 选择区域、表或数据透视表中的一个或多个单元格。 在 “开始” 选项卡的 “样式” 组中，单击 “条件格式” 旁边的箭头，单击 “图标 集” ，然后选择图标集。 课堂练习 对数据进行筛选 -筛选文本 / 筛选数字 / 筛选日期或时间 选择包含数值数据的单元格区域。 在 “开始” 选项卡上的 “编辑” 组中，单击 “排序和筛选” ，然后单击 “筛选” 。 对数据进行筛选和排序 对于数字筛选，还可以 筛选平均数以上或以下的数字 筛选顶部或底部数字 筛选空或非空值 对数据进行筛选 - 按选定内容筛选 可以用 等于活动单元格内容的条件 快速筛选数据。 在单元格区域或表列中，右键单击包含要作为筛选依据的值、颜色、字体颜色或 图标的单元格。 单击 “筛选” ，然后执行下列操作之一： 1. 若要按文本、数字或者按日期或时间进行筛选，请单击 “按所选单元 格的值筛选” 。 2. 若要按单元格颜色进行筛选，请单击 “按所选单元格的颜色筛选” 。 3. 若要按字体颜色进行筛选，请单击 “按所选单元格的字体颜色筛选” 。 4. 若要按图标进行筛选，请单击 “按所选单元格的图标筛选” 。 对数据进行筛选 - 按选定内容筛选 可以用 等于活动单元格内容的条件 快速筛选数据。 在单元格区域或表列中，右键单击包含要作为筛选依据的值、颜色、字体颜色或 图标的单元格。 单击 “筛选” ，然后执行下列操作之一： 1. 若要按文本、数字或者按日期或时间进行筛选，请单击 “按所选单元 格的值筛选” 。 2. 若要按单元格颜色进行筛选，请单击 “按所选单元格的颜色筛选” 。 3. 若要按字体颜色进行筛选，请单击 “按所选单元格的字体颜色筛选” 。 4. 若要按图标进行筛选，请单击 “按所选单元格的图标筛选” 。 对数据进行筛选 - 筛选唯一值 选择单元格区域，或确保活动单元格在一个表中。 在 “数据” 选项卡上的 “排序和筛选” 组中，单击 “高级” 。 在 “高级筛选” 对话框中，请执行下列操作之一： 若要就地筛选单元格区域或表，请单击 “在原有区域显示筛选结果” 。 若要将筛选结果复制到另一个位置，请执行以下操作： 1. 单击“将筛选结果复制到其他位置”。 2. 在 “复制到” 框中，输入单元格引用。 选中 “选择不重复的记录” 复选框，然后单击 “确定” 。 所选区域中的唯一值 将复制到新位置。 对数据进行筛选 - 删除重复值 删除重复值时，只有单元格区域或表中的值会受影响。在单元格区域或表以外的 其他任何值都不会更改或移动。 注意 由于要永久删除数据，因此最好在删除重复值之前，先将原始单元格区域或表复制到另一个工作 表或工作簿。 选择单元格区域，或确保活动单元格在一个表中。 在 “数据” 选项卡上的 “数据工具” 组中，单击 “删除重复项” 。 执行下列一项或多项操作： 1. 在 “列” 下，选择一个或多个列。 2. 若要快速选中所有列，请单击 “全选” 。 3. 若要快速清除所有列，请单击 “取消全选” 。 如果单元格区域或表包含很多列， 并且您只想选择几个列，您会发现更容易的做法是单击 “取消全选” ，然后在 “列” 下面选择这些列。 单击 “确定” 。 将显示一条消息，指示删除了多少重复值，以及保留了多少唯 一值，或者是不是没有删除重复值。 单击 “确定” 。 对数据进行筛选 -突出显示唯一值或重复值 选择区域、表或数据透视表中的一个或多个单元格。 在 “开始” 选项卡的 “样式” 组中，单击 “条件格式” 旁边的箭头，然后单击 “突出显示单元格规则” 。 选择 “重复值” 。 输入要使用的值，然后选择格式。 运用透视表进行数据汇总及分析 数据透视表的若干应用 案例 对比两组数据，找出指定的差异 （参见所附 excel工作簿 数据透视表的应用） 现在我们要针对公司现有的全部合同来分析计划和 预测的差异。我们有两组合同数据，分别代表合同 的计划销售收入，毛利及毛利率和合同的最新预测 销售收入，毛利及毛利率。 如何快速指出造成差异 的主要合同 ？ 数据透视表的若干应用 - 合同计划及预测细节 数据透视表的若干应用 找出造成差异的主要合同 加工数据。做法 很简单，将这两 组数据叠加在一 起。注意：增加 一列，名称是 类别 接着我们运用透 视表分析找到差 异较大的合同。 数据透视表的若干应用 找出造成差异的主要合同 行标签选择合同号； 数据区分别选择收入和毛利 列标签选择类别 数据透视表的若干应用 找出造成差异的主要合同 数据透视表的进一步加工已适应分析要求 1.使用千位分隔符 点一下菜单上方数据透视表工具，工具栏出 现后选择活动字段的字段设置 点击打开后选择数字格式，点击后出现设 置单元格格式，选择数值，然后勾上使用千 位分隔符，并将小数点位数设为 0位 数据透视表的若干应用 找出造成差异的主要合同 数据透视表的进一步加工已适应分析要求 2.定义透视表表单，以方便进一步分析 点一下菜单上方数据透视表工具，工具栏出 现后选择数据透视表的选项 点击打开后选择汇总和筛选 标签，将显示 行总计的复选框勾掉。 点击确定退出后透视表将只显示列标签项，而不 再显示行总计 目录 第一章 第二章 第三章 第四章 第五章 第六章 开始之前 管理 Excel工作表 让 Excel的函数为你工作 简单的几招让财务数据的处理得心应手 分析数据 财务分析报表的格式设计和美化 运用 Excel图表来阐述财务分析结果 财务分析报表的格式设计 设置单元格数字格式 课堂练习 1. 选择要使用数字格式的单元格或单元格区域 2. 在 “开始” 选项卡上，单击 “数字” 旁边的 “对话框启动器” 3. 选择第一个标签数字，在 “分类” 框中，可以看到如下 的数字显示格式。 4. 选择需要的的数字显示样式 财务分析报表的格式设计 -隐藏或显示单元格值 某个工作表 包含员工工资等机密信息，我们不想在办公桌短暂逗留的同事看 到该信息。或者我们需要将一组单元格中的值乘以不希望在该工作表上显示 的另一单元格中的值。或者我们在某些场合希望不显示某些单元格的值。 这时我们可以通过应用自定义数字格式来隐藏该工作表上这些单元格中的值。 1. 选择包含要隐藏的值的单元格或单元格区域 2. 在 “开始” 选项卡上，单击 “数字” 旁边的 “对话框启动器” 3. 在 “分类” 框中，单击 “自定义” 。 4. 在 “类型” 框中，选择现有的代码。 5. 键入 ;（三个分号）。 6. 单击 “确定” 。 虽然含有隐藏值的单元格在工作表上显示为空，但是这些单元格的值仍显示在编辑栏 课堂练习 财务分析报表的格式设计 取消 /显示工作表的网格线 默认情况下， Excel 的工作簿内的工作表是显示浅灰色的网格线的 。这种网格线 在打印的时候不会出现，但有的时候为了让工作表的显示更接近打印的效果，我 们可以手动更改网格线的显示 单击菜单栏上菜单视图，将网格线前面的复选项取消，网格线就去除了。这个更 改是立刻生效的，适用于单个工作表的 财务分析报表的格式设计 常见财务报告样式 财务分析报表的格式设计 常见财务报告样式 财务分析报表的格式设计 常见财务报告样式 财务分析报表的格式设计 常见财务报告样式 财务分析报表的格式设计 常见财务报告样式 目录 第一章 第二章 第三章 第四章 第五章 第六章 开始之前 管理 Excel工作表 让 Excel的函数为你工作 简单的几招让财务数据的处理得心应手 分析数据 财务分析报表的格式设计和美化 运用 Excel图表来阐述财务分析结果 运用图表形象表达数字 Excel 基本图表概述 在 Microsoft Office Excel 2007 中，可以很轻松地创建具有专业外观的图表。只需选 择图表类型、图表布局和图表样式，便可在每次创建图表时即刻获得专业效果。 Excel 2007 包含很多不同类型的图表和图形， 财务上的核心应用是使用图表讲数字 性息形象，清晰的表达出来，从而避免非财务人员在面对大量财务数据时无所适从 的感觉 若要在 Excel 中创建可在以后进行修改并设置格式的基本图表，请首先在工作表中 输入该图表的数据，然后，只需选择该数据并在 Office Fluent 功能区中选择要使用 的图表类型（位于“插入”选项卡上的“图表”组中）即可。 在 Excel中 创建基本图表 在工作表上，排列要绘制在图表中的数据。 1. 数据可以排列在行或列中， Excel 自动确定将数据绘制在图表中的最佳方式。 2. 选择包含要用于图表的数据的单元格。 3. 在“插入”选项卡的“图表”组中，请执行下列操作之一： 单击图表类型，然后单击要使用的图表子类型。 若要查看所有可用的图表类型，请单击某图表类型，然后单击 “所有图表 类型” 以显示 “插入图表” 对话框，单击箭头滚动浏览所有可用的图表类 型和图表子类型，然后单击要使用的图表类型。 4. 默认情况下，图表作为嵌入图表 放在工作表上。 5. Excel 自动为该图表指定一个名称 课堂练习 在 Excel中 创建基本图表 示例 1 6 2 3 4 5 4 图表区 绘图区 在图表中绘制的数据系列的数据点 横（分类）和纵（值）坐标轴 图表的图例 图表以及可以在该图表中使用的坐标轴标题 将图表放在单独的图表工作表中 默认情况下，图表作为嵌入图表 放在工作表上。如果要将图表放在单独的图 表工作表中，可以通过执行下列操作来更改其位置 1. 单击嵌入图表将其选中。此时将显示“图表工具”，其上增加了“设计”、“布局” 和“格式”选项卡。 2. 在“设计”选项卡的“位置”组中，单击“移动图表”。 3. 在“选择放置图表的位置”下，执行下列操作之一： 若要将图表显示在图表工作表中，请单击“新工作表”。如果要替换图表的建议名 称，则可以在“新工作表”框中键入新的名称。 若要将图表显示为工作表中的嵌入图表，请单击“对象位于”，然后在“对象位于” 框中单击工作表。 课堂练习 修改基本图表 更改图表坐标轴的显示 您可以指定坐标轴的刻度并调整显示的值或分类之间的 间隔。为了使图表更容易理解，还可以在坐标轴上添加刻度线，并指定刻度线的 显示间隔。 向图表中添加标题和数据标签 为了帮助阐明图表中显示的信息，可以添加图表 标题、坐标轴标题和数据标签。 添加图例或数据表 您可以显示或隐藏图例，更改图例的位置或者修改图例项。 在一些图表中，还可以显示其中列有图表中出现的图例项标示 和值的数据表 针对每个图表类型应用特殊选项 特殊的折线（如高低点连线和趋势线 ）、柱线 （如涨跌柱线和误差线）、数据标记 以及其他选项均可用于不同的图表类型。 课堂练习 应用预定义的图表布局和图表样式获得专业外观 Excel 提供了多种有用的预定义布局和样式 - 单击嵌入图表将其选中。 - 此时将显示“图表工具”，其上增加了“设计”、“布局”和“格式”选项卡。 可以根据需要手动更改各个图表元素（如图表的图表区、绘图区、数据系列或图例） 的布局和格式，从而对布局或样式进行微调。 应用预定义的图表布局时，会有一组特定的图表元素（如标题、图例、数据表或数 据标签）按特定的排列顺序显示在图表中。 课堂练习 附录 如何选择单元格 .区域 .行或列 使用组合图来显示数据 要强调图表中的不同信息类型，可以在该图表中组合两种或更多种图表类型。例 如，可以组合柱形图和折线图来显示即时视觉效果，从而令该图表更易于理解。 当图表中不同数据系列 的值范围很宽，或者当混合了多种数据类型时，可以在 次要纵坐标（数值）轴上以不同的图表类型绘制一个或多个数据系列。 1.将示例工作表数据复制到一个空白工作表中，或者打开包含要绘制到组合图中的数 据的工作表。 2.选择要在组合图中绘制的数据。 3. 在“插入”选项卡上，在“图表”组中单击“柱形图”。 4. 在“二维柱形图”下单击“簇状柱形图”。 5. 在图表中，单击要以不同图表类型显示的数据系列，或者从图表元素列表中选择 该数据系列（“布局”选项卡中的“当前所选内容”组的“图表元素”框）。 6.在 “设计” 选项卡上的 “类型” 组，单击 “更改图表类型” 。 7.在 “折线图” 下，单击 “带数据标记的折线图” ，再单击 “确定” 。 8. 要在次坐标轴上绘制折线图，请执行下面的操作： 在图表中，单击一下表示“平均价格”的折线图以选择该数据系列，或者从图表元素列表中选择该 数据系列（ “布局” 选项卡中的 “当前所选内容” 组的 “图表元素” 框）。 在 “布局” 选项卡上的 “当前所选内容” 组中，单击 “设置所选内容格式” 。 在 “系列选项” 类别的 “系列绘制在” 下，单击 “次坐标轴” ，再单击 “关闭” 。 9. 单击图表的图表区 （图表区：整个图表及其全部元素。）。 10. 在“设计”选项卡上的“图表样式”组中，单击要使用的图表样式。 11. 要更改图表的大小，请在 “格式” 选项卡上 “大小” 组中的 “形状高度” 和 “形状宽度” 框中选择所需的形状大小，然后按 Enter。 12. 要在图表上添加和定位图表标题，并设置它的格式，请单击图表区 （图表 区：整个图表及其全部元素。），然后执行下列操作： 1. 在 “布局” 选项卡上的 “标签” 组中，单击 “图表标题” ，然后单击 “图表上 方” 。 2. 在图表中，单击图表标题，然后键入所需的文本 3. 要减小图表标题的字号，请右键单击该标题，然后在快捷菜单上的 “字号” 框中输入 所需的字号。 13. 要移动图例，请执行下列操作： 1. 单击图例将其选中。 2. 在 “布局” 选项卡上的 “标签” 组中，单击 “图例” ，然后单击所需的位置 。 14. 要添加纵坐标轴标题，请执行下列操作： 1. 在 “布局” 选项卡上的 “标签” 组中，单击 “坐标轴标题” ，然后执行下列操作： 单击 “主要纵坐标轴标题” ，然后单击所需的标题选项。 单击 “次要纵坐标轴标题” ，然后单击所需的标题选项 2. 单击每个坐标轴标题，然后键入要为该标题使用的文本 3. 要更改轴标题的字号，请单击每个轴标题，然后在 “字号” 框中单击所需的字号。 提 示 此示例组合图使用的字号是 “ 14”。 15. 要更改“平均价格”折线图中显示的数据标记的外观，请执行下列操作： 1. 右键单击标记，然后单击快捷菜单上的 “设置数据系列格式” 。 2. 单击 “数据标记选项” ，然后在 “数据标记类型” 下单击 “内置” 。 3. 在 “类型” 框中，单击要使用的数据标记类型。 16. 单击图表的图表区 17. 在 “格式” 选项卡上的 “形状样式” 组中，单击 “其他” 按钮 ，然后单击要 使用的效果。 18. 如果要使用的主题颜色不同于应用到您的工作簿的默认主题颜色，请执行下 列操作： 1. 在 “页面布局” 选项卡上的 “主题” 组中，单击 “主题” 。 2. 在 “内置” 下，单击要使用的主题。