部分函数使用说明.docx
《部分函数使用说明.docx》由会员分享，可在线阅读，更多相关《部分函数使用说明.docx（21页珍藏版）》请在冰豆网上搜索。
部分函数使用说明
一、VLOOKUP（查找值，区域，列序号，逻辑值）
“查找值”：
为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。
“区域”：
数组所在的区域，如“B2:
E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。
“列序号”：
即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP返回错误值#VALUE!
；如果大于区域的列数，函数VLOOKUP返回错误值#REF!
。
“逻辑值”：
为TRUE或FALSE。
它指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数VLOOKUP将返回精确匹配值。
如果找不到，则返回错误值#N/A。
如果“查找值”为文本时，“逻辑值”一般应为FALSE。
另外：
·如果“查找值”小于“区域”第一列中的最小数值，函数VLOOKUP返回错误值#N/A。
·如果函数VLOOKUP找不到“查找值”且“逻辑值”为FALSE，函数VLOOKUP返回错误值#N/A。
1．括号里有四个参数，是必需的。
最后一个参数range_lookup是个逻辑值，我们常常输入一个0字，或者False;其实也可以输入一个1字，或者true。
两者有什么区别呢？
前者表示的是完整寻找，找不到就传回错误值＃N/A；后者先是找一模一样的，找不到再去找很接近的值，还找不到也只好传回错误值＃N/A。
这对我们其实也没有什么实际意义，只是满足好奇而已，有兴趣的朋友可以去体验体验。
2．Lookup_value是一个很重要的参数，它可以是数值、文字字符串、或参照地址。
我们常常用的是参照地址。
用这个参数时，有两点要特别提醒：
A）参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致，否则的话有时明明看到有资料，就是抓不过来。
特别是参照地址的值是数字时，最为明显，若搜寻的单元格格式类别为文字，虽然看起来都是123，但是就是抓不出东西来的。
而且格式类别在未输入数据时就要先确定好，如果数据都输入进去了，发现格式不符，已为时已晚，若还想去抓，则需重新输入。
B）第二点提醒的，是使用时一个方便实用的小技巧，相信不少人早就知道了的。
我们在使用参照地址时，有时需要将lookup_value的值固定在一个格子内，而又要使用下拉方式（或复制）将函数添加到新的单元格中去，这里就要用到“$”这个符号了，这是一个起固定作用的符号。
比如说我始终想以D5格式来抓数据，则可以把D5弄成这样：
$D$5，则不论你如何拉、复制，函数始终都会以D5的值来抓数据。
3．Table_array是搜寻的范围，col_index_num是范围内的栏数。
Col_index_num 不能小于1，其实等于1也没有什么实际用的。
如果出现一个这样的错误的值#REF!
，则可能是col_index_num的值超过范围的总字段数。
二、HLOOKUP函数的功能是从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。
语法形式：
HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）
参数：
Lookup_value：
为需要在数据表第一行中进行查找的数值。
Lookup_value可以为数值、引用或文本字符串。
Table_array：
为需要在其中查找数据的数据表。
使用对区域或区域名称的引用。
Row_index_num：
为table_array中待返回的匹配值的行序号。
Range_lookup：
为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。
如果为TRUE或省略，则返回近似匹配值。
FALSE则为精确匹配。
HLOOKUP函数就是查找函数，不过它与lookup和vlookup函数有区别。
hlookup是按行查找的，vlookup是按列查找的。
HLOOKUP函数
HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）
简单说，1-要查找的值，2-要查找的区域，3-返回数据在区域的第几行数，4-精确匹配。
Lookup_value为需要在数据表第一行中进行查找的数值。
Lookup_value可以为数值、引用或文本字符串。
Table_array为需要在其中查找数据的数据表。
使用对区域或区域名称的引用。
Row_index_num为table_array中待返回的匹配值的行序号。
Row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。
如果row_index_num小于1，函数HLOOKUP返回错误值#VALUE!
；如果row_index_num大于table_array的行数，函数HLOOKUP返回错误值#REF!
。
Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。
如果为TRUE或省略，则返回近似匹配值。
也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。
如果lookup_value为FALSE，函数HLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。
三、财务PMT函数
PMT函数即年金函数
PMT基于固定利率及等额分期付款方式，返回贷款的每期付款额。
PMT函数的格式：
PMTrate，nper，pv，fv，type）
Rate贷款利率。
●Nper该项贷款的付款总数。
●Pv现值，或一系列未来付款的当前值的累积和，也称为本金。
●Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略Fv，则假设其值为零，也就是一笔贷款的未来值为零。
●Type数字0或1，用以指定各期的付款时间是在期初还是期末。
1代表期初，不输入或输入0代表期末
说明：
PMT返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。
应确认所指定的rate和nper单位的一致性。
例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。
贷款偿还计算函数PMT
语法：
PMT（rate，nper，pv，fv，type）.
参数：
如今贷款买房子或车子的人越来越多，计算某一贷款的月偿还金额
是考虑贷款的重要依据，Excel提供的PMT函数是完成这一任务的好工具。
语法中的rate是贷款利率；nper为贷款偿还期限；pv是贷款本金；fv为最后一次付款后剩余的贷款金额，如果省略fv，则认为它的值为零；type为0或1，用来指定付款时间是在月初还是月末。
如果省略type，则假设其值为零。
应用实例：
购房还款金额
假如你为购房贷款十万元，如果年利率为7%，每月末还款。
采用十年还清
方式时，月还款额计算公式为“=PMT（7%/12，120，-100000）”。
其结果为￥-1
，161.08，就是你每月须偿还贷款1161.08元。
Excel中的投资计算及举例（三）——PMT函数
求某项投资的年值-Excel函数PMT的使用：
之前已经说过FV函数和PV函数，现在我来说说另一个常用的投资计算函数，即PMT函数。
PMT（Payment）函数是基于固定利率及等额分期付款方式，返回贷款的每期付款额。
PMT函数的语法为：
PMT（rate,nper,pv,fv,type）
注意：
所指定的rate和nper单位要保持一致。
PMT函数举例：
1.零存整取
如下图所示范例：
值得注意的是PMT函数中参数即包含了fv也包含了pv。
在图中绿色高亮区域你可以看到PMT函数公式，其中A4，A5，A6，A7，A8分别对应各单元格中的内容。
在公式中pv参数为0，即首年初未存款。
另外，该例是以年计息的。
2.整取零存
如下图所示范例：
在图中绿色高亮区域你可以看到PMT函数公式，其中A4，A5，A6，A7，A8分别对应各单元格中的内容。
在公式中fv参数为0，即表示最终还清贷款。
同样，该例是以年计息的
REPLACE（参数1,参数2,参数3,参数4）
参数1是要替换其部分字符的文本。
参数2是要用参数4替换的参数1中字符的起始位置.
参数3是希望REPLACE用参数4替换参数1中从参数2开始算起的字符个数。
如为0就是在前面插入。
参数4是要用于替换参数1中字符的文本。
如:
=REPLACE（A3,3,2,"yueliang"）用yueliang替换A3的第3位算起的2个字符.
COUNTBLANK函数
【含义】
计算指定单元格区域中空白单元格的个数。
【语法】
COUNTBLANK（range）
Range为需要计算其中空白单元格个数的区域。
【说明】
即使单元格中含有返回值为空文本（""）的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。
【示例】
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
A
B
1
数据
数据
2
3
6
=IF（B4<30,"",B4）
4
27
5
4
34
公式
说明（结果）
=COUNTBLANK（A2:
B5）
计算上述区域中空白单元格的个数。
公式返回空文本。
（4）
DGET（database,field,criteria）
Database 构成列表或数据库的单元格区域。
数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。
列表的第一行包含着每一列的标志项。
Field 指定函数所使用的数据列。
列表中的数据列必须在第一行具有标志项。
Field可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field也可以是代表列表中数据列位置的数字：
1表示第一列，2表示第二列，等等。
Criteria 为一组包含给定条件的单元格区域。
可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。
MID函数
【名称】
Mid
从字符串中返回指定数目的字符。
【类别】
字符串函数
【原形】
Mid（string,start[,length]）
【参数】
string
字符串表达式，从中返回字符。
如果string包含Null，则返回Null。
Start
string中被提取的字符部分的开始位置。
如果start超过了string中字符的数目，Mid将返回零长度
字符串（""）。
Length
要返回的字符数。
如果省略或length超过文本的字符数（包括start处的字符），将返回字符串中从start到字符串结束的所有字符。
PV函数
【含义】
返回投资的现值。
现值为一系列未来付款的当前值的累积和。
例如，借入方的借入款即为贷出方贷款的现值。
【语法】
PV（rate,nper,pmt,fv,type）
rate为各期利率。
例如，如果按12%的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为12%/12（即1%）。
可以在公式中输入12%/12、1%或0.01作为rate的值。
nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。
例如，对于一笔5年期按月偿还的汽车贷款，共有5*12（即60）个偿款期数。
可以在公式中输入60作为nper的值。
pmt为各期所应支付的金额，其数值在整个年金期间保持不变。
通常pmt包括本金和利息，但不包括其他费用及税款。
例如，$10,000的年利率为12%的四年期汽车贷款的月偿还额为$263.33。
可以在公式中输入-263.33作为pmt的值。
如果忽略pmt，则必须包含fv参数。
fv为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。
例如，如果需要在12年后支付$60,000，则$60,000就是未来值。
可以根据保守估计的利率来决定每月的存款额。
如果忽略fv，则必须包含pmt参数。
type数字0或1，用以指定各期的付款时间是在期初还是期末。
Type值
支付时间
0或省略
期末
1
期初
【说明】
•应确认所指定的rate和nper单位的一致性。
例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。
•以下函数应用于年金：
CUMIPMT、PPMT、CUMPRINC、PV、FV、RATE、FVSCHEDULE、XIRR、IPMT、XNPV、PMT
年金是在一段连续期间内的一系列固定的现金付款。
例如汽车贷款或抵押贷款就是年金。
有关详细信息，请参阅各年金函数的详细说明。
•在年金函数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。
例如，对于储户来说，$1000银行存款可表示为参数-1,000，而对于银行来说该参数为1,000。
【示例】
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
A
B
1
数据
说明
2
400
每月底一项保险年金的支出
3
10%
投资收益率
4
12
付款的年限
公式
说明（结果）
=PV（A3/12,12*A4,A2,,0）
在上述条件下年金的现值（-33,470.61）。
结果为负值，因为这是一笔付款，亦即支出现金流。
如果年金的购买成本是（40,000），则年金的现值（33,470.61）小于实际支付值。
因此，这不是一项合算的投资。
注意利率除以12得到月利率。
支付的年数乘以12得到支付次数。
FV函数
【含义】
基于固定利率及等额分期付款方式，返回某项投资的未来值。
【语法】
FV（rate,nper,pmt,pv,type）
有关函数FV中参数的详细说明，请参阅函数PV
rate为各期利率。
nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。
pmt为各期所应支付的金额，其数值在整个年金期间保持不变。
通常pmt包括本金和利息，但不包括其他费用及税款。
如果忽略pmt，则必须包含pv参数。
pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。
如果省略PV，则假设其值为零，并且必须包括pmt参数。
type数字0或1，用以指定各期的付款时间是在期初还是期末。
Type值
支付时间
0或省略
期末
1
期初
【说明】
•应确认所指定的rate和nper单位的一致性。
例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。
•在所有参数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。
【示例】
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
A
B
1
数据
说明
2
10%
年利率
3
12
付款期总数
4
-400
各期应付金额
5
-900
现值
6
1
各期的支付时间在期初（请参见上面的信息）
公式
说明（结果）
=FV（A2/12,A3,A4,A5,A6）
在上述条件下投资的未来值（6,062.35）
A
B
1
数据
说明
2
9%
年利率
3
32
付款期总数
4
-1800
各期应付金额
5
1
各期的支付时间在期初（请参见上面的信息）
公式
说明（结果）
=FV（A2/12,A3,A4,,A5）
在上述条件下投资的未来值（65,312.89）
注意上述示例中，年利率应除以12，因为它是按月计复利而得的。
Excel函数应用实例：
折旧值计算
无论单位还是家庭，许多固定资产和耐用消费品都存在折旧问题，随着使用时间的延长，其残值在不断减少。
假设某单位有一批2000年购进原价8500元/每台的电脑，预计使用寿命6年，寿命期结束时的资产残值约为1000元，要求使用第二年内的折旧值。
（1）函数分解
DB函数使用固定余额递减法，计算一笔资产在给定期间内的折旧值。
语法:
DB（cost,salvage,life,period,month）
Cost为资产原值;Salvage为资产在折旧期末的价值（也称为资产残值）;Life为折旧期限（有时也称作资产的使用寿命）;Period为需要计算折旧值的期间。
Period必须使用与life相同的单位；Month为第一年的月份数，如省略，则假设为12。
（2）实例分析
为了在参数改变以后仍能进行计算，我们打开一个空白工作表，在A1、B1、C1、D1、E1单元格输入“电脑原值”、“资产残值”、“使用寿命”、“折旧时间”和“折旧值”，然后在其下面的单元格内输入“8500”、“1000”、“6”、“2”。
然后选中E2单元格在其中输入公式“=DB（A2,B2,C2,D2）”，回车后即可得到结果“￥1,785.00”，就是说使用期第二年的折旧值为1785元。
如果你要计算其他设备或财产的折旧值，只需改变A2、B2、C2、D2单元格内的数值即可。
固定资产折旧的函数（EXCEL）
1．年限平均法——SLN函数
年限平均法又称直线法，是将固定资产的应计折旧额均衡地分摊到固定资产预计使用寿命内的一种方法。
采用这种方法计算的每期折旧额均是等额的。
计算公式如下：
年折旧率=（l-预计净残值率）／预计使用寿命（年）
月折旧率=年折旧率/12
月折旧额=固定资产原价×月折旧率
语法
SLN（cost,salvage,life）
Cost 为资产原值。
Salvage 为资产在折旧期末的价值（有时也称为资产残值）。
Life 为折旧期限（有时也称作资产的使用寿命）。
示例
AB数据说明公式 说明（结果）
30,000
资产原值
7,500
资产残值
10
使用寿命
=SLN（A2,A3,A4）
每年的折旧值（2,250）
2．工作量法
工作量法，是根据实际工作量计提固定资产折旧额的一种方法。
计算公式如下：
单位工作量折旧额=固定资产原价／（1-预计净残值率）／预计总工作量
某项固定资产月折旧额=该项固定资产当月工作量／单位工作量折旧额
例10：
甲公司的一台机器设备原价为680000元，预计生产产品产量为2000000件，预计净残值率为3％，本月生产产品34000件。
则该台机器设备的月折旧额计算如下：
单件折旧额=680000×（1-3％）/2000000
=0．3298（元／件）
月折旧额=34000×0．3298=11213．2（元）
3．双倍余额递减法——DDB函数、VDB函数
双倍余额递减法，是在不考虑固定资产预计净残值的情况下，根据每年年初固定资产净值和双倍的直线法折旧率计算固定资产折旧额的一种方法。
应用这种方法计算折旧额时，由于每年年初固定资产净值没有扣除预计净残值，所以在计算固定资产折旧额时，应在其折旧年限到期前两年内，将固定资产的净值扣除预计净残值后的余额平均摊销。
计算公式如下：
年折旧率=2／预计的使用年限
月折旧率=年折旧率/12
月折旧额=固定资产年初账面余额×月折旧率
例11：
乙公司有一台机器设备原价为600000元，预计使用寿命为5年，预计净残值率为4％。
按双倍余额递减法计算折旧，每年折旧额计算如下：
年折旧率=2／5=40％
第一年应提的折旧额=600000×40％=240000（元）
第二年应提的折旧额=（600000－240000）×40％
=144000（元）
第三年应提的折旧额=（360000－144000）×40％
=86400（元）
从第四年起改按年限平均法（直线法）计提折旧：
第四、五年应提的折旧额=（129600－600000×4％）/2
=52800（元）
语法
DDB（cost,salvage,life,period,factor）
Cost 为资产原值。
Salvage 为资产在折旧期末的价值（有时也称为资产残值）。
此值可以是0。
Life 为折旧期限（有时也称作资产的使用寿命）。
Period 为需要计算折旧值的期间。
Period必须使用与life相同的单位。
Factor 为余额递减速率。
如果factor被省略，则假设为2（双倍余额递减法）。
示例：
AB数据说明公式说明（结果）
2400
资产原值
300
资产残值
10
使用寿命
=DDB（A2,A3,A4*365,1）
第一天的折旧值。
MicrosoftExcel自动将factor设置为2。
（1.32）
=DDB（A2,A3,A4*12,1,2）
第一个月的折旧值（40.00）
=DDB（A2,A3,A4,1,2）
第一年的折旧值（480.00）
=DDB（A2,A3,A4,2,1.5）
第二年的折旧值，使用了1.5的余额递减速率，而不用双倍余额递减法（306.00）
=DDB（A2,A3,A4,10）
第十年的折旧值，MicrosoftExcel自动将factor设置为2（22.12）
语法
VDB（cost,salvage,life,start_period,end_period,factor,no_switch）
Cost 为资产原值。
Salvage 为资产在折旧期末的价值（有时也称为资产残值）。
此值可以是0。
Life 为折旧期限（有时也称作资产的使用寿命）。
Start_period 为进行折旧计算的起始期间，Start_period必须与life的单位相同。
End_period 为进行折旧计算的截止期间，End_period必
升级会员 