《Excel在金融上应用(公司内部培训资料)》由会员分享，可在线阅读，更多相关《Excel在金融上应用(公司内部培训资料)（65页珍藏版）》请在金锄头文库上搜索。
1、第三部分：有效利用EXCEL进行管理和财务分析 I、 EXCEL操作技巧II、 EXCEL中公式和函数的使用方法 III、如何运用数据透视表进行分析IV、 各种财务报表及管理报告的制作与分析V、 实际案例演练I、 EXCEL操作技巧一、数据的输入及运算1、数据的输入 2、数据的处理 3、数据的运算 及引用 二、图表处理1、基本图表的处理 2、组合图表 3、趋势线三、假设分析工具1、方案管理器 2、模拟运算表 3、单变量求解 4、规划求解数据的输入Excel 的数据输入*针对数据量较 小的数据手工输入自动生成数据库导入Internet导入*针对有规律的 数据*针对数据库已 有数据*针对Inter
2、net 搜索数据在金融应用中有大量数据来源于Internet,故我们着重讲解如何将数据从Internet导入 。数据的输入Internet导入：*选择IE中所需的数据，使用“复制”、“粘贴”键将数据导入到Excel中 *导入Web页上的表格数据，也可通过IE打开数据所在页面，在表格处“右击-导出到 Microsoft Excel”命令，即可完成数据导入。 *创建Web查询，“数据”“导入Web查询”“新建Web查询”，打开“新建Web查询”对 话框，在地址栏中输入数据源的web地址，打开网页，单击所需数据旁的“ ”标志， 单击“导入”，可将数据导入到Excel中，此外，Excel中有“数据”“
3、刷新”命令可以更 新从Web中导入的数据。 数据的处理排序：排序是按照某一或某几个特定字段重新排列数据 *排序按钮 排序按钮是一种比较简单的方法，选择要排序的单元格区域，点击“常用” 工具栏上的排序按钮” “，即可对选择的区域进行升序或降序排列 *排序命令 首先选中整个数据区域（标题行除外），执行“数据”“排序”命令，打开“ 排序”对话框，在“主要关键字”、“次要关键字”、“第三关键字”中选择标题，选择要排 序的次序。在选项按钮打开“排序选项”对话框，对排序的进行设置，确定完成数据的 排序。在自定义排序中，可以事先在“选项”的“自定义序列”中设置自己的序列次序 数据的处理筛选：筛选是查找和处理
4、区域中数据子集的一种比较快捷的方法 *自动筛选 适用于简单 的筛选 。选定要筛选 的内容，执行“数据”“ 筛选” “自动筛选”命令，工作表每列标题行的右侧会出现筛选按钮 ，点击筛选按钮， 在下拉列表框筛选数据。在主营行业的下拉列表框中有车类、摩托车、汽车制造、 汽配等几项筛选的条件，筛选后的筛选按钮和行标签会呈现蓝色。此外，可以进行 多列筛选 数据的处理筛选：筛选是查找和处理区域中数据子集的一种比较快捷的方法 在自动筛选的下拉列表中有“自定义”筛选，在“自定义自动筛选方式”对话框中输入筛 选的条件，可以对数据进行范围筛选，例如筛选每股收益大于0的上市公司数据的处理筛选：筛选是查找和处理区域中数
5、据子集的一种比较快捷的方法 *高级筛选 用于复杂条件的筛选。执行“数据”“筛选”“高级筛选”命令，在高级筛 选对话框中，选择要筛选的数据、筛选的条件以及筛选结果出现的区域数据清单筛选结果条件区域 条件区域必须有列标志 条件区域与数据清单间至 少有一个空白行 通常在条件区域的数据清 单上至少插入3个空白行， 以利于输入筛选条件数据的处理筛选：筛选是查找和处理区域中数据子集的一种比较快捷的方法 表1高级筛选的重点在条件区域，在筛选数据的工作表中，在条件区域建立筛选条件。 不同变量的条件位于同一行，代表“并”；不同行代表“或”单列多个条件 一列具有多个筛选条件，可以直接在一列从上到下键入条件，如 表
6、1，筛选主营业务利润率小于0或是大于10的纸业行业公司证券代码主营业务利润率(%)10证券 代码主营业务 利润率(%)销售毛 利率(%) 00表2证券 代码主营业 务利润 率(%)销售 毛利 率(%) 资产负 债率 (%) 0 0 0表3多列具有单个条件 在多列中查找需要的数据时，在条件区域的同一行键入条件 ，如表2 ，筛选主营业务利润率0,销售毛利率也0的公司多行或多列具有单个条件 满足多行或多列条件的数据，需在条件区域的不同列 中输入条件，如表3，筛选主营业务利润率0或者销售毛利率0或者资产负债率 0的公司数据的处理筛选：筛选是查找和处理区域中数据子集的一种比较快捷的方法 高级筛选妙用：剔
7、除重复数据在实务中，我们的数据经常会出现重复数据的情况，此时若逐一剔除，在大量数据的情况下 就太太麻烦了。我们可以巧妙地运用高级筛选来解决这一问题。如下图，我们在条件区域不设任 何条件，选择”数据”-“筛选”-“高级筛选“，在”高级筛选“对话框中勾选”选择不重复的记录“，确定后 就可以得到无重复数据的结果数据的运算及引用相对引用与绝对引用相对引用 单元格中的相对引用，在复制公式时，单元格引用会调整到新位置。单 元格D2=B2*C2，D3：D4复制D2中的公式，引用位置相对发生变化，如下图 绝对引用 单元格中的绝对引用，如$A$1（通过按”F4“键实现），是指定位置引用 单元格，在复制公式时，单
8、元格引用绝对不会改变。单元格E4=D4*$E$1，E5：E6 复制E4中的公式，绝对引用$E$1的位置没有发生变化，如下图数据的运算及引用命名：所谓名称是指代表单元格、单元格区域、公式或常量值的单词或字符串 。与行列标志相比较，名称更直观，也更便于理解和运用*单元格和区域命名： 选中要命名的单元格或单元区域，在Excel名称框中输入名称并回车。选中区域 B1:B5，在名称框中输入“单价”，Enter键结束Excel菜单中“插入”“名称”“定义”命令，输入名称和指定的区域。在“当前工 作簿中的名称中输入“物品”，引用位置中输入或选中要命名的区域 =sheet1!$A$1:$A$5数据的运算及引用
9、命名：所谓名称是指代表单元格、单元格区域、公式或常量值的单词或字符串 。与行列标志相比较，名称更直观，也更便于理解和运用*单元格名称指定 ： 在有两列或两行数据，其中一列或行为文本标识（字段名），另一列或行为具 体数值，可为数值自动命名。选中区域，使用Excel菜单中“插入”“名称”“指 定”命令，选择标识所在位置后确认。如下图，选中A4:E9，打开“指定名称”对 话框，选择“最左列”复选框，确定结束，数据的运算及引用命名：所谓名称是指代表单元格、单元格区域、公式或常量值的单词或字符串 。与行列标志相比较，名称更直观，也更便于理解和运用*常量命名 ： 此方法为不在单元格中出现的数值命名。使用E
10、xcel菜单中“插入”“名称”“定 义”命令，“在当前工作簿中的名称”中输入名称，“引用位置”输入数值。在使用 Excel工作表计算分析的过程中，多次使用变量“销售税=0.075”，但工作表中却 未出现，打开“定义名称”对话框，在“当前工作簿中的名称中”输入“销售税”，引 用位置中输入0.075，确定结束，如下图数据的运算及引用函数：Excel作为计算的工具软件，是通过函数和内置工具计算处理数据的。Excel提供 了大量的内置函数，用于执行特定运算，具有强大的数据处理能力. Excel按功能和作用不 同可分为：财务、日期与函数、数学和三角函数、统计函数、查找和引用函数、数据库函 数、文本函数、
11、逻辑函数、信息函数、用户定义、工程函数。* 在后面的学习中会分别具体介绍这几类函数，这里只通过一种逻辑函数（IF函 数）的学习介绍函数的使用，如图在单元格中通过输入“=”插入函数 直接输入函数针对熟悉者 点击 打开函数库，选择所需函数输入函数所需参数，确定即可 图表处理图表是使用Excel进行各种运算分析时一个非常有用的工具，通过图表，可以更 加直观地提示数据之间的内在关系和变化趋势。Excel中提供了14种内置的图表 类型，其中一些类型还包含有其他变化。在金融计算中，各种图表类型都有可能用到。但从数量分析的角度来看，应用最多的是 散点图和带有拆线或曲线的散点图。图表处理基本图表处理*建立图表
12、 单击常用工具 栏上的 按钮 ，出现图表向 导，选择正确 的图表类型确定数据源设定图表选项图表处理基本图表处理*设置图表格式 方法：右击图表每一元素（如图表区、坐标轴、图表网格线图例 等），在快捷菜单中逐一设置右击图表区域，“图表区格式 ”-“填充效果”-“纹理”右击绘图区，“绘图区格式” -“填充效果”-“双色”图表处理组合图表 所谓组合图表是指在同一幅图表中使用不同的图表类型表示不同组别的数据 ，它是在用图表表示多组数据之间的对比关系时的一个常用工具。为考察KJIA30家企业当前的股利收益率和股利支付比例，将其绘制在同一图表 内。为区别两项不同的指标，分别用拆线图和柱形图表示股利收益率和股
13、利支 付比例。 操作方法：在已建立图表的图表区右击,选择“源数据”-“系列”，点击“添 加”，名称设为“股利支付比例”，“值”处输入数据，确定即可。加入股利支付率这一系 列后，由于其数值均较 大而拉升了纵坐标坐标 值，导致股利收益率的 曲线图不能很好显示， 此时，如何实现对比？图表处理次坐标轴 次坐标轴是指在组合图表中因多组数据的数据范围差别较大时，为更好地表示 多组数据的对比关系而设立两条不同的纵坐标轴的技巧。 Step1: 选中股利收益率系列 ，右击“图表类型”Step2:在图表类型中选择 柱形图Step3:选中股利收益率系列 ，右击“数据系列格式”Step4:在“数据系列格式”选项 卡中
14、选择“坐标轴”-“次坐标轴”图表处理趋势线 Excel图表中的另一项重要功能是绘制趋势线。从原理上讲，这一功能是基于统 计学中的回归与预测理论。它是金融定量分析中的常用工具为说明风险与收益之间 的关系，我们计算了KJIA的30家企业在1990-2002年间 的收益率平均值和标准差，并绘制成图表。因事先不知二者到底有何关系，故 我们使用散点图。经观察，二者有线性关系，利用Excel的趋势线功能可以比较 精确地从数学上验证这一推测Step1: 点击图表区，出 现“图表”菜单栏，选择“添 加趋势线”Step2: 选择合理的趋势 线类型Step3：具体设置趋势线“ 选项”，可以选择“显示公 式”。在预
15、测中还有一重 要功能，即“前推”，选择 合适的单位即可假设分析工具方案管理器 所谓方案,是保存在工作表当中并可以在相互之间进行切换的数据。例如， 对于一组现金流量分别使用不同的贴现率计算净现值，就可以把不同条件下的结果保存在 若干方案中，利用Excel的方案管理器在不同的结果间进行切换，以对比和评价各方案之间 的差异假定某投资项目，投资 金额为10000，项目周 期为5年。项目的预期 现金流量和贴现率都受 经济形势的影响：如果 经济不景气，则预期每 年现金流量为1200，贴 现率为6%；如果经济繁 荣，则预期每年现金流 量为2000，贴现率为 10%；如果经济形势一 般，则现金流量为1600
16、，贴现率为8%。要求对 该项目进行评价。Step1:菜单“工具”-“方案”， 打开方案管理器，点击“添 加”Step2:在编辑方案中，输入方 案名和可变单元格Step3：输入方案数值如是添加各方案Step4:再次打开方案管理器，进行方案管理。选择方案， 点击“显示”随着显示方案的变化 ，Excel中的相应数值 也会发生变化。假设分析工具模拟运算表 模拟运算表是一个非常理想的敏感性分析工具，它被广泛应用于金融领域 中，如在资本预算中的敏感性分析等在上例中，我们通过方案管理器给出了3组不同条件下的参数值，得到了项目的评价结果。通过 模拟运算表，可以对项目净现值进行敏感性分析，更详细地研究贴现率和每年现金流量对评价 结果的影响。在B13单元格内输入净现值的计算公式，在公式单元格B13的右边输入一组贴现率 的可能值，在其下侧输入一组每年现金流量的可能值。用鼠标选中B13：G19区域，从菜单中执 行“数据”-“模拟运算表”，在模拟
