《Excel在财务中的运用》-5

甘苦人生2010 2012-05-13

展开全文

第五章　Excel2007常用函数的使用

　　5.1 认识公式
　　各种运算符的含义及示例

类别	运算符及含义	含义	示例
算术	+（加号）	加	1+2
	–（减号）	减	2–1
	–（负号）	负数	–1
	*（星号）	乘	2*3
	/（斜杠）	除	4/2
	%（百分比）	百分比	10%
	（乘方）	乘幂	3?2
比较	=（等号）	等于	A1=A2
	>（大于号）	大于	A1>A2
	<（小于号）	小于	A1<A2
	>=（大于等于号）	大于等于	A1>=A2
	<=（小于等于号）	小于等于	A1<=A2
	<>（不等号）	不等于	A1<>A2
文本	&（连字符）	将两个文本连接起来产生连续的文本	“2009年”&“”（结果为“”）
引用	:（冒号）	区域运算符，对两个引用之间包括这两个引用在内的所有单元格进行引用	A1:D4（引用A1到D4范围内的所有单元格）
	,（逗号）	联合运算符，将多个引用合并为一个引用	SUM（A1:D1,A2:C2）将A1:D2和A2:C2两个区域合并为一个
	（空格）	交集运算符，生成对两个引用中共有的单元格的引用	A1:D 1:B4（引用A1:D1和A1:B4两个区域的交集即A1:B1）

　　各种运算符的优先级

运算符（优先级从高到低）	说明
：（冒号）（单个空格），（逗号） –（负号） %（百分号）（乘方） *和/ +和– & =、>、<、>=、<=、<>	区域运算符交集运算符联合运算符负数百分比乘幂乘和除加和减连接两个文本字符串（串连）比较运算符

　　5.2 使用公式
　　操作方法：
　　控制柄
　　CTRL+ENTER
　　

　　5.3 单元格的引用
　　相对引用和绝对引用
　　单元格的引用通常是为了使用某个单元格的公式，而对单元格进行标识的方式。
　　引用单元格能够通过所标识的单元格来快速获得公式对数据的运算。
　　

　　
　　跨工作表引用
　　

　　跨工作簿引用
　　

　　5.4 认识函数
　　函数格式＝函数名（参数1，参数2，…）
　　

序号	分类	功能简介
1	数据库工作表函数	分析数据清单中的数值是否符合特定条件
2	日期与时间函数	在公式中分析和处理日期值和时间值
3	工程函数	用于工作分析
4	信息函数	确定存储在单元格中数据的类型
5	财务函数	进行一般的财务计算
6	逻辑函数	进行逻辑判断或者进行复合检验
7	统计函数	对数据区域进行统计分析
8	查找和引用函数	在数据清单中查找特定数据或者查找一个单元格的引用
9	文本函数	在公式中处理字符串
10	数学和三角函数	进行数学计算

　　5.5 使用函数
　　函数定义
　　

　　插入函数
　　

　　5.6 常用函数的应用
　　SUM函数
　　“sum”在英语中表示“总数、总和、求和”的意思，SUM函数是用来计算某一个或多个单元格区域中所有数字的总和的求和函数。
　　

　　AVERAGE函数
　　“average”在英语中表示“平均、平均数”的意思，AVERAGE函数是用来计算指定数据集合中所有数值平均值的函数。
　　其语法结构为：
　　AVERAGE（number1,number2,…）
　　

　　MAX和MIN函数
　　“max”/“min”是“maximum”和“minimum”的缩写。在英语中分别表示“最大量”和“最小量”的意思，分别用来返回指定区域内所有数值的最大值和最小值。
　　其语法结构为：
　　MAX（number1,number2,…）
　　MIN（number1,number2,…）
　　

　　LARGE和SMALL函数
　　“large”/“small”是分别表示“大的”和“小的”的意思，分别用来返回指定区域内所有数值的第k个最大值和最小值。
　　其语法结构为：
　　LARGE（array，k）（k<n）
　　SMALL（array，k）（k<n）
　　注意：LARGE和MAX的区别
　　LARGE函数的统计范围比MAX函数广，可以返回第k个最大值，而MAX函数只能返回指定数据集合中的最大值。
　　同理，SMALL和MIN函数也是如此。
　　RANK函数
　　“rank”在英语中表示“等级、排列”的意思，用来返回指定区域内所有数值的大小排序的情况。
　　其语法结构为：
　　RANK（number，ref，order）
　　number 需要进行排位的数值
　　ref 数字列表数组
　　order 表示对ref进行排序的方式
　　为0或省略时表示降序，为1表示升序
　　
　　IF函数
　　“if”在英语中表示“如果，假设”的意思，用来判断真假值，再根据逻辑判断的真假值返回不同结果的函数。
　　其语法结构为：
　　IF（logical_test,value_if_true,value_if_false）
　　logical_test 判断的方式，返回值或表达式
　　value_if_true 表示判断结果为真时返回的值
　　value_if_false 表示判断结果为假时返回的值
　　

　　COUNTIF函数
　　COUNTIF函数是计算数据区域中满足给定条件的单元格数量。
　　其语法结构为：
　　COUNTIF（range，criteria）
　　range 需要计算满足条件的单元格数目的单元格区域；
　　criteria 确定哪些单元格将被计算在内的条件。
　　嵌套函数
　　

　　将市场部各小组按照第1季度销售合计情况进行排名，但是只显示前三名的名次。
　　

　　VLOOKUP函数
　　VLOOKUP函数是在表格数据的首列查找指定的值，并由此返回表格数据当前行其他列的值。其语法结构为： VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）
　　lookup_value 需要在表格数组第一列查找的数值；
　　table_array 需要进行查询转置的数组或单元格区域；
　　ol_index_num 待返回的匹配值的列序号；
　　range_lookup 逻辑值，希望查找匹配的是精确值还是近似值，如果为true或省略，则为模糊查询；false为精确匹配。
　　

　　INDEX函数
　　返回数组中指定单元格或单元格数组的数值。
　　INDEX（array,row_num,column_num）
　　Array：单元格区域或数组常数；
　　Row_num为数组中某行的行序号，函数从该行返回数值；
　　Column_num为数组中某列的列序号，函数从该列返回数值。
　　OFFSET函数
　　以指定引用为参考，通过给定偏移量返回新的引用
　　

　　OFFSET（reference,rows,cols）
　　Reference：参考系的引用区域，其左上角是偏移量的起始位置；
　　Rows：相对于参考系的起始位置上（下）偏移的行数；
　　Cols：相对于参考系的起始位置左（右）偏移的列数；
　　

　　5.7 财务管理常用函数的应用

　　5.7.1 折旧计算函数的使用
　　折旧函数
　　直线法（SLN）
　　年数总和法（SYD）
　　双倍余额递减法（VDB）
　　工作量法
　　

　　5.7.2 投资预算函数的使用
　　EFFECT函数
　　利用给定的名义利率和一年中复利的期数，计算实际利率
　　EFFECT（nominal_rate,npery）
　　Nominal_rate：名义利率；
　　Npery：每年的复利期数；
　　

　　NOMINAL函数
　　利用给定的实际利率和一年中复利的期数，计算名义利率
　　NAMINAL（effect_rate,npery）
　　Effect_rate：实际利率；
　　Npery：每年的复利期数；
　　

　　FV函数
　　基于固定利率和年金返回某项投资的未来值
　　FV（rate,nper,pmt,pv,type）
　　Rate：各期利率；
　　Nper：表示总投资期；
　　Pmt：年金；
　　

　　Pv：开始计算时已经入账的款项；
　　Type：0或者忽略表示期末年金，1表示期初年金；
　　PV函数
　　基于固定利率和年金返回某项投资的未来值PV（rate,nper,pmt,fv,type）
　　Rate：各期利率；
　　Nper：表示总投资期；
　　Pmt：年金；
　　Fv：终值；
　　Type：0或者忽略表示期末年金，1表示期初年金；
　　

　　NPER函数
　　基于固定利率和年金返回某项投资的总投资期NPER（rate, pmt,pv,fv，type）
　　Rate：各期利率；
　　Pmt：年金；
　　Pv：现值；
　　Fv:终值；
　　Type：0或者忽略表示期末年金，1表示期初年金；
　　NPV函数
　　通过使用贴现率以及一系列未来支出和收入，返回一项投资的净现值
　　NPV（rate,value1,value2,…）
　　Rate：贴现率；
　　Value1,value2,…：在时间上必须具有相等的间隔，并且都是发生在期末；
　　

　　5.7.3 本利函数的使用
　　IPMT函数
　　基于固定利率或等额分期付款方式，用于返回某项投资在给定期次内的投资回报或贷款偿还利息部分
　　IPMT（rate,per,nper,pv,fv,type）
　　Rate：各期利率；
　　Per：用于计算其利息数额的期数；
　　Nper：总投资期；
　　Pv：现值；
　　Fv：终值；
　　Type：0或者忽略表示期末，1表示期初；
　　