Excel金融计算专业教程

函数类别
示例
逻辑函数 数学函数
统计函数 工程函数
金融函数
IF(判断), NOT(非), OR(或), AND(与), TRUE(真), FALSE(假)
LOG(对数), POWER(乘幂), SQRT(开平方), COS(余弦), FACT(阶乘), …… VAR(方差), MEDIAN(中位数), NORMSDIST(标准正态分布), …
21
1.3 内置函数和自定义函数
• 1.1 数据输入与运算
– 1.1.1 数据的导入 – 1.1.2 数据运算与引用 – 1.1.3 排序与筛选
• 1.2 图表和数据透视表
– 1.2.1 图表类型 – 1.2.2 组合图表 – 1.2.3 趋势线 – 1.2.4 数据透视表
• 1.3 内置函数和自定义函数
– 1.3.1 函数的使用 – 1.3.2 统计函数 – 1.3.3 金融函数 – 1.3.4 自定义函数
• 1.4 假设分析工具
– 1.4.1 方案管理器 – 1.4.2 模拟运算表 – 1.4.3 单变量求解 – 1.4.4 规划求解
16
1.2.1 图表类型
类别 第一类
第二类
第三类
第四类 第五类
11
1.1.3 排序与筛选
在Excel中对数据进行整理最常见的方式是排序和筛选。 排序是按照某一个或某几个特定字段重新排列数据的顺序， 筛选是从全体数据中选择符合某种条件的数据。 Excel提供了两种对数据进行排序的方法： 一种方法是选中数据表中作为排序依据所在列中的单元格， 然后点击常用工具栏上的排序按钮 如果需要先后按照若干列来排序数据表，则需要从菜单上 执行“数据”→“排序”命令，打开排序对话框。 例如，从Web下载了某股票期权的报价后，需要按照期权 期限、执行价格和交易场所对报价进行排序，则对数据进行初 步整理后，即可按如下操作。
= 60 + AVERAGE(B2:B8)/SIGMA 等号表示所在单元格内容是一个公式，等式后面的“60”是 常量，“+”和“/”是运算符，AVERAGE()是求平均值的函数， SIGMA是用户自己给特定单元格的命名。 当需要同时对一组数据进行处理或公式、函数的计算结果 不只一个数据时，就需要使用数组公式或数组函数。在Excel中 用大括号“{}”表示数组，如“{2,2,4; 3,6,8;…}” 数组公式和数组函数的使用方法与普通公式基本相同，但 在输入时要首先选中返回数组结果的全部单元格，并使用 [Shift]+[Ctrl]+[Enter]进行输入
当需要引用位于其他工作表或其他文件中的数据时，就要 使用三维引用。三维引用的基本格式是：
[spreadsheet]sheet!A1 其中，前面的方括号“[ ]”内是要引用的文件名，如果源数 据在同一文件内则可省略；跟在文件名后面的是工作表的名称， 如果同时引用多个工作表相同位置的数据，则可以使用冒号“:” 标识工作表范围，如Sheet1:Sheet3，注意工作表名后面要跟有 叹号“!”；最后是引用的单元格或单元格区域的名称，如A1、 B2或C3:D6等等。
如果要在某单元格或单元格区域内调用函数：
– 从菜单中选取“插入→函数……”命令； – 点击编辑区左侧的“fx”标志； – 点击工具栏上的自动求和标志∑； – 按[Shift]+[F3]。
在某些情况下，可能需要将某函数作为另一函数的参数使 用，这称为嵌套函数。公式可包含多达七级的嵌套函数。
许多Excel函数通过对一组或多组数据进行运算，得出一组 运算结果。这类函数实际上是数组公式，必须按数组的方式输 入(选中整个数组所在区域，按[Shift]+[Ctrl]+[Enter]输入)
Excel 金融计算专业教程
第1章 计算工具EXCEL
——更有效地使用电子表软件
本章总结了Excel中一些很重要但又容易为人们所忽视的 使用技巧和运算工具，特别是强调了本书后续内容里经 常用到的一些方法。
1.1 数据输入与运算
• 1.1 数据输入与运算
– 1.1.1 数据的导入 – 1.1.2 数据运算与引用 – 1.1.3 排序与筛选
12
1.1.3 排序与筛选
Excel中的排序
13
1.1.3 排序与筛选
在Excel环境下筛选数据有简单和高级两种方法。 执行菜单上的“数据”→“筛选”→“自动筛选”，工作表中 每列的标题行上就会出现筛选按钮“▼”，点击要作为筛选依据 的那一列的筛选按钮，从中选择要显示项目的条件，其他不符 合该条件的行就会被隐藏起来。可以同时在一个数据表上的多 个列上进行筛选。
可以包括：开盘价、收盘价、最高价、最低价、成交量。
17
1.2.2 组合图表
在用图表表示多组数据之间的对比关系时，有两个常用的 技巧：组合图表和次坐标轴。
所谓组合图表是指在同一幅图表中使用不同的图表类型表 示不同组别的数据。一个常见的例子是带有交易量的股价图。
Excel允许在同一图表中对两组具有不同数量级的数据使用 不同的坐标轴，在原有的数据轴之外，用不同单位或刻度使用 次坐标轴表示另外一组数据。
• 1.2 图表和数据透视表
– 1.2.1 图表类型 – 1.2.2 组合图表 – 1.2.3 趋势线 – 1.2.4 数据透视表
• 1.3 内置函数和自定义函数
– 1.3.1 函数的使用 – 1.3.2 统计函数 – 1.3.3 金融函数 – 1.3.4 自定义函数
• 1.4 假设分析工具
– 1.4.1 方案管理器 – 1.4.2 模拟运算表 – 1.4.3 单变量求解 – 1.4.4 规划求解
– 1.3.1 函数的使用 – 1.3.2 统计函数 – 1.3.3 金融函数 – 1.3.4 自定义函数
• 1.4 假设分析工具
– 1.4.1 方案管理器 – 1.4.2 模拟运算表 – 1.4.3 单变量求解 – 1.4.4 规划求解
22
1.3.1 函数的使用
Excel内置有大约330个的函数，这些函数可以分成11大类： 数据库函数，日期和时间函数，外部函数，工程函数，财务函 数，信息函数，逻辑函数，查找和引用函数，数学和三角函数， 统计函数以及文本和数据函数。
9
1.1.2 数据运算与引用
相对引用：公式中的相对单元格引用(例如A1)是基于单元格 的相对位置。如果公式所在单元格的位置改变，引用也随之改 变。如果多行或多列地复制公式，引用会自动调整。默认情况 下，公式使用相对引用。
绝对引用：单元格中的绝对单元格引用(格式为$A$1，即在 行列标志前加“$”符号)总是在指定位置引用单元格。如果公式 所在单元格的位置改变，绝对引用保持不变。如果多行或多列 地复制公式，绝对引用将不作调整。
14
1.1.3 排序与筛选
当需要设定更复杂的筛选条件，可以使用高级筛选。 “高级筛选”命令像“自动筛选”命令一样筛选清单，但 不显示列的下拉列表，而是在工作表上单独的条件区域中键入 筛选条件，它允许根据更复杂的条件进行筛选。并且，在筛选 完成后，可以选择把筛选结果显示在原来位置或复制到其他区 域。
混合引用：混合引用具有绝对列和相对行，或是绝对行和相 对列。绝对引用列采用$A1、$B1等形式，绝对引用行采用A$1、 B$1等形式。如果公式所在单元格的位置改变，则相对引用改变， 而绝对引用不变。如果多行或多列地复制公式，相对引用自动 调整，而绝对引用不作调整。
10
1.1.2 数据运算与引用
Excel中的绝对引用、相对引用和混合引用
2
1.1.1 数据的导入
在Excel环境下进行计算，所需要的数据大致有3种来源： 手工输入、自动生成和从外部导入。当数据量很小而且又没有 规律的情况下，一般采用手工输入的方法；而对于那些有规律 的数据，如连续的数字或字符序列可以用Excel的填充命令自动 生成。当数据量比较大或有现成的数据来源如网络资源、数据 库资源等可供应用，就需要采用导入的方法。
18
1.2.3 趋势线
Excel图表中的另一项重要功能是绘制趋势线。这一功能是 基于统计学中的回归和预测理论。
19
1.2.4 数据透视表
分类汇总
20
1.2.4 数据透视表
Excel的数据透视表将不同类别的数据组分成不同的页，就 好像是将多个二维平面工作表层叠起来，从而形成了三维的数 据表示结构，使用户可以通过二维表透视到三维的数据
从Excel菜单上的“文件”→“打开”命令处，就可以直接读 入数据库文件、Web文件、XML文件、文本文件以及其他电子 表格式的文件等。
从外部导入数据的另一种方法是建立数据查询。根据数据 源的不同，查询可以分成“Web查询”和“数据库查询”两大 类。
3
1.1.1 数据的导入
从Web上导入少量数据的简单方法： 第一种方法是在浏览器中右击所需要的数据，从快捷菜单 中执行“复制”(快捷键[Ctrl]+[C])选项，然后在Excel工作表中 选中要放置数据的位置(可以选中一个或一组单元格)，从菜单或 常用工具栏中执行“粘贴”命令(快捷键[Ctrl]+[V])，就可以把 Web页上的数据拷贝到工作表中。 第二种方法适合于导入Web页上的表格数据，在IE浏览器 中打开数据所在页面，在所需要数据所在的表格处点击鼠标右 键，从快捷菜单中执行“导出到Microsoft Excel”，无论此时 Excel是否打开，操作系统会自动在Excel中建立一个空白工作簿 文件，将数据输入。使用这种方法导入数据，还会同时建立对 源地址的查询链接，可以在需要时，执行刷新命令以更新数据。
15
1.2 图表和数据透视表
• 1.1 数据输入与运算
– 1.1.1 数据的导入 – 1.1.2 数据运算与引用 – 1.1.3 排序与筛选
• 1.2 图表和数据透视表
– 1.2.1 图表类型 – 1.2.2 组合图表 – 1.2.3 趋势线 – 1.2.4 数据透视表
• 1.3 内置函数和自定义函数
6
1.1.2 数据运算与引用
Excel中的运算类型
运算类型 运算符
示例
说明
算术运算 + – * / 10000.00*(1+1.1%)^ 年息1.1，本金10000元，按复利
%^
2
计算两年的本息合计10221.21。
文本运算 &
“Product A” & “Actual”
将两个文本连接，得到“Product A Actual”。
饼 图， 这三种图的基本面都是圆形的，主要用来观察数据之间的比例。 圆环图， 雷达图
面积图
面积图与折线图类似，但它具有堆积面积图和百分比堆积面积图两种变 种，因此可以更好地反映某一(组)数据在全部数据(组)中所占比例。
气泡图， 气泡ห้องสมุดไป่ตู้可以看作是散点图的扩展，它用气泡大小反映数据点的另一组属 股价图 性。股价图顾名思义是反映类似股市行情的图表，它在每一个数据点上
BESSELJ(贝塞尔函数), HEX2BIN(16进制到2进制), IMAGINARY(复数的虚部系数), …… NPV(净现值), FV(净现值), INTRATE(利率), YIELD(收益), ……
8
1.1.2 数据运算与引用
Excel中引用单元格的方法分为：A1引用和R1C1引用。前 者是用字母数字组合标识引用单元格的绝对位置；后者表示对 当前选中单元格相对位置的引用。
Excel中的主要图表类型
类型
特征
柱形图， 这类图表主要用来反映数据的变化趋势及对比。其中散点图与柱型、折 折线图， 线图的区别在于其横轴按数值表示，是连续的；而前者的横轴按类别表 散点图 示，是离散的。条形图、圆柱图、圆锥图、棱锥图都是柱形图的变种
曲面图
曲面图是一种真三维图表(三维柱形、圆柱、圆锥、棱锥等类型，当数 据轴上只有一组数据时，本质上只是二维图)，它适合对于分析多组数 据的对比与变化趋势。
4
1.1.1 数据的导入
从Web上导入大规模数据， 更规范的做法是创建Web查询。 从Excel菜单上执行“数 据”→“导入Web数据” →“新 建Web查询”，打开创建查询 对话框。
5
1.1.2 数据运算与引用
Excel中的运算是通过公式或函数实现的。Excel中的公式。 它是由等号打头，包括常量、对单元格或单元格区域的引用、 运算符和函数等几种成分。例如在下面一行中，
日期运算 –
比较运算 < > <= >= <>
“2003-5-28” –“2001- 求出两个日期之间的天数837；
2-10”
求出两个时间之间的间隔8:15。
“17:30”–“09:15”
A1>A2, C2<>C3
判断两个数据之间的比较关系， 返回逻辑值，
7
1.1.2 数据运算与引用
Excel中的运算函数