计算工具EXCEL

-9-
数据运算与引用（5）
• 当需要引用位于其他工作表或其他文件中的数据时，就要 使用三维引用。三维引用的基本格式是： [spreadsheet]sheet!A1 其中，前面的方括号“[ ]”内是要引用的文件名，如果源 数据在同一文件内则可省略；跟在文件名后面的是工作 表的名称，注意工作表名后面要跟有叹号“!”；最后是引 用的单元格或单元格区域的名称。
第二类
第三类
饼 图， 这三种图的基本面都是圆形的，主要用来观察数据之间的比例。 圆环图， 雷达图 面积图 气泡图， 股价图 面积图与折线图类似，但它具有堆积面积图和百分比堆积面积图两种变 种，因此可以更好地反映某一(组)数据在全部数据( 组)中所占比例。 气泡图可以看作是散点图的扩展，它用气泡大小反映数据点的另一组属 性。股价图顾名思义是反映类似股市行情的图表，它在每一个数据点上 可以包括：开盘价、收盘价、最高价、最低价、成交量。
-12-
数据输入与运算
• 数据输入与运算
– 数据的导入 – 数据运算与引用 – 排序与筛选
• 图表 • 内置函数和自定义函数
– – – – 函数的使用 统计函数 金融函数 自定义函数
-14-
图表类型
Excel中的主要图表类型
类 别 类型 柱形图， 折线图， 散点图 曲面图 特 征 第一类 这类图表主要用来反映数据的变化趋势及对比。其中散点图与柱型、 折线图的区别在于其横轴按数值表示，是连续的；而前者的横轴按类别 表示，是离散的。条形图、圆柱图、圆锥图、棱锥图都是柱形图的变种 曲面图是一种真三维图表(三维柱形、圆柱、圆锥、棱锥等类型，当数 据轴上只有一组数据时，本质上只是二维图) ，它适合对于分析多组数 据的对比与变化趋势。
-22-
EXP函数
• EXP说明： 返回 e 的 n 次幂。常数 e 等于 2.71828182845904
，是自然对数的底数。
• EXP语法
EXP(number) – Number 必需。应用于底数 e 的指数。 – 若要计算以其他常数为底的幂，请使用指数操作符 (^)。
-23-
IF函数
• 说明：如果指定条件的计算结果为 TRUE， IF 函数将返回某个值 ；如果该条件的计算结果为 FALSE，则返回另一个值。
“2003-5-28” –“2001- 求出两个日期之间的天数837； 2-10” 求出两个时间之间的间隔8:15。 “17:30”–“09:15” A1>A2, C2<>C3 判断两个数据之间的比较关系， 返回逻辑值，
-7-
比较运算 < > <= >= <>
数据运算与引用（3）
Excel中的运算函数
-8-
数据运算与引用（4）
• 相对引用：公式中的相对单元格引用(例如 A1)是基于单元 格的相对位置。如果公式所在单元格的位置改变，引用也 随之改变。如果多行或多列地复制公式，引用会自动调整 。默认情况下，公式使用相对引用。
• 绝对引用：单元格中的绝对单元格引用(格式为$A$1，即 在行列标志前加“$”符号)总是在指定位置引用单元格。如 果公式所在单元格的位置改变，绝对引用保持不变。如果 多行或多列地复制公式，绝对引用将不作调整。 • 混合引用：混合引用具有绝对列和相对行，或是绝对行和 相对列。绝对引用列采用$A1、$B1等形式，绝对引用行 采用 A$1、 B$1等形式。如果公式所在单元格的位置改变 ，则相对引用改变，而绝对引用不变。如果多行或多列地 复制公式，相对引用自动调整，而绝对引用不作调整。
-6-
数据运算与引用（2）
Excel中的运算类型
运算类型 运算符 算术运算 + – * / %^ 文本运算 & 日期运算 – 示例 说明
10000.00*(1+1.1%)^ 年息1.1，本金10000元，按复利 2 计算两年的本息合计10221.21。 “Product A” & “Actual” 将两个文本连接，得到 “Product A Actual”。
-20-
Hale Waihona Puke MAX函数• MAX说明： 返回一组值中的最大值。
• MAX语法
MAX(number1, [number2], ...) – Number1, number2, ... Number1 是必需的，后续数 值是可选的 – 参数可以是数字或者是包含数字的名称、数组或引 用。 – 如果参数不包含数字，函数 MAX 返回 0（零）。 – 如果参数为错误值或为不能转换为数字的文本，将 会导致错误。
– 从菜单中选取“插入→函数……”命令； – 点击编辑区左侧的“fx”标志；
• 在某些情况下，可能需要将某函数作为另一函数的参数使 用，这称为嵌套函数。公式可包含多达七级的嵌套函数。
-17-
统计函数
• Excel中有 70余个统计函数，大致可分为6类：基本统计类 函数，概率分布类，数字特征类，抽样分布类，统计检验 类，相关、回归与预测类。
-18-
金融函数
• Excel内置有 50余个金融财务方面的函数。 • 计算货币时间价值的函数：现值PV()、终值FV()、利率 RATE()、每期现金流量PMT()、期数NPER()。
• 固定资产折旧类函数：直线折旧法SLN()、年限总和法 SYD()、固定余额递减法DB()、双倍余额递减法DDB()以及 用余额递减法计算若干期内累积折旧额的VDB()。
• 当数据量很小而且又没有规律的情况下，一般采用手工输入的 方法；而对于那些有规律的数据，如连续的数字或字符序列可 以用 Excel的填充命令自动生成。
• 当数据量比较大或有现成的数据来源如网络资源、数据库资源 等可供应用，就需要采用导入的方法。 – 从 Excel菜单上的“文件” →“打开”命令处，就可以直接 读入数据库文件、 Web文件、XML文件、文本文件以及其 他电子表格式的文件等。 – 从外部导入数据的另一种方法是建立数据查询。根据数据 源的不同，查询可以分成“ Web查询”和“数据库查询”两 大类。 -3-
• 投资评价类函数：净现值NPV()、内部收益率IRR()、修正 内部收益率MIRR()以及用于计算非定期现金流量净现值和 内部收益率的函数XNPV()和XIRR()。 • 债券计算的函数。
-19-
自定义函数
• 自定义函数是指用户根据自己的需要，通过Excel内置的 Visual Basic for Application编写代码，设计出实现特定功 能的函数。
函数类别 逻辑函数 数学函数 统计函数 工程函数 金融函数 示 例
IF(判断), NOT(非), OR(或), AND(与), TRUE(真), FALSE(假) LOG(对数), POWER(乘幂), SQRT(开平方), COS(余弦), FACT(阶乘), …… VAR(方差), MEDIAN(中位数), NORMSDIST(标准正态分布), … BESSELJ(贝塞尔函数), HEX2BIN(16进制到2进制), IMAGINARY(复数的虚部系数), …… NPV(净现值), FV(净现值), INTRATE(利率), YIELD(收益), ……
-11-
排序与筛选（2）
• 在 Excel环境下筛选数据有简单和高级两种方法： – 执行菜单上的“数据” →“筛选”→“自动筛选”，工作表 中每列的标题行上就会出现筛选按钮“▼”，点击要作为 筛选依据的那一列的筛选按钮，从中选择要显示项目的条 件，其他不符合该条件的行就会被隐藏起来。可以同时在 一个数据表上的多个列上进行筛选。
-15-
第四类 第五类
内置函数和自定义函数
• 数据输入与运算
– 数据的导入 – 数据运算与引用 – 排序与筛选
• 图表 • 内置函数和自定义函数
– – – – 函数的使用 统计函数 金融函数 自定义函数
-16-
函数的使用
• Excel内置有大约330个的函数，这些函数可以分成11大类 ：数据库函数，日期和时间函数，外部函数，工程函数， 财务函数，信息函数，逻辑函数，查找和引用函数，数学 和三角函数，统计函数以及文本和数据函数。 • 如果要在某单元格或单元格区域内调用函数：
• 金融计算中常用的统计函数：计算平均值的AVERAGE()、 计算方差的VAR()或VARP()、计算标准差的STDEV()或 STDEVP()、标准正态分布NORMSDIST()和 NORMSINV()；
• 财务预测中有时会用到线性回归与预测函数LINEST()、 FORECAST()、 TREND()、线性方程的斜率函数SLOPE()、指 数回归与预测函数LOGEST()、 GROWTH()； • 在进行数据分析时会用到相关系数函数CORREL()和乘积矩 相关系数 (也称为广义相关系数)函数RSQ()。
-21-
SUM函数
• SUM说明： 将指定为参数的所有数字相加。 • SUM语法
SUM(number1,[number2],...]) – number1 必需。想要相加的第一个数值参数。 – number2可选。想要相加的 2 到 255 个数值参数。 – 如果参数是一个数组或引用，则只计算其中的数字 。数组或引用中的空白单元格、逻辑值或文本将被 忽略。 – 如果任意参数为错误值或为不能转换为数字的文本 ，Excel 将会显示错误。
-10-
排序与筛选（1）
• 在 Excel 中对数据进行整理最常见的方式是排序和筛选。 排序是按照某一个或某几个特定字段重新排列数据的顺序 ，筛选是从全体数据中选择符合某种条件的数据。 • Excel 提供了两种对数据进行排序的方法： – 一种方法是选中数据表中作为排序依据所在列中的单 元格，然后点击常用工具栏上的排序按钮 – 如果需要先后按照若干列来排序数据表，则需要从菜 单上执行“数据”→“排序”命令，打开排序对话框。
计算工具EXCEL
王恺 管理科学与工程系