EXCEL中的函数
财务函数
1(PMT函数
功能:基于固定利率及等额分期付款方式,返回贷款的每期付款额
:PMT(rate,nper,pv,fv,type)
rate:贷款利率
nper:该项贷款的总贷款期限或者总投资期
pv:从该项贷款(或投资)开始计算时已经入账的款项,或一系列未来付款当前值的累积和 fv:未来值,或在最后一次付款后希望得到的现金余额,如果忽略该值,将自动默认为0 type:一个逻辑值,用以指定付款时间是在期初还是在期末,1表示期初,0表示期末,其默认值为0
2(IPMT函数
功能:基于固定利率及等额分期付款方式,返回投资或贷款在某一给定期限内的利息偿还额 格式:IPMT(rate,per,nper,pv,fv)
rate:各期利率
per:用于计算利息数额的期数,介于1,nper之间
nper:总投资(或贷款)期,即该项投资(或贷款)的付款期总数
pv:从该项投资(或贷款)开始计算时已经入账的款项,或一系列未来付款当前值的累积和 fv:未来值,或在最后一次付款后希望得到的现金余额,如果忽略该值,将自动默认为0 3(FV函数
功能:基于固定利率及等额分期付款方式,返回某项投资的未来值
格式:FV (rate,nper,pmt,pv,type)
rate:各期利率
nper:总投资(或贷款)期,即该项投资(或贷款)的付款期总数
pmt:各期所应支付的金额
pv:现值,即从该项投资开始计算时已经入账的款项,或一系列未来付款的当前值的累积和,也称为本金
type:一个逻辑值,用以指定付款时间是在期初还是在期末,1表示期初,0表示期末,其默认值为0
4(PV函数
功能:一系列未来付款的当前值的累积和,返回的是投资现值
格式:PV(rate,nper,pmt,fv,type)
rate:贷款利率
nper:该项贷款的总贷款期限或者总投资期
pmt:各期所应支付的金额
fv:未来值,或在最后一次付款后希望得到的现金余额,如果忽略该值,将自动默认为0 type:一个逻辑值,用以指定付款时间是在期初还是在期末,1表示期初,0表示期末,其默认值为0
5。SLN函数
功能:某项资产在一个期间中的线性折旧值
格式:SLN(cost,salvage,life)
cost:资产原值
salvage:资产在折旧期末的价值,即资产残值
life:折旧期限,即资产的使用寿命
文本函数
1(EXACT函数
功能:用来比较两个文本字符串是否相同。如果两个字符串相同,则返回“TRUE”,反之,则返回“FALSE”
格式:EXACT(text1,text2)
text1 和 text2:两个要比较的文本字符串
注意:1、在字符串中如果有多余的空格,会被视为不同
2、EXACT函数在判别字符串的时候,会区分英文的大小写,但不考虑格式设置的差异
2(CONCATENATE函数
功能:将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中 格式:CONCATENATE(text1,text2,……)
text1,text2,……:需要连接的字符文本或引用的单元格
注意:1、该函数最多可以附带30个参数
2、如果其中的参数不是引用的单元格,且为文本格式的,请给参数加上英文状态下的双引号
如果将上述函数改为使用“&”符连接也能达到相同的效果
3(SUBSTITUTE函数
功能:实现替换文本字符串中的某个特定字符串
格式:SUBSTITUTE(text,old_text,new_text,instance_num)
text:原始内容或是单元格地址
old_text:要被替换的字符串
new_text:替换old_text的新字符串
如果字符串中含有多组相同的old_text时,可以使用参数instance_num来指定要被替换的字符串是文本字符串中的第几组。如果没有指定instance_num的值,默认的情况下,文本中的每一组old_text都会被替换为new_text。
4(REPLACE函数
功能:将某几位的文字以新的字符串替换。其替换功能与SUBSTITUTE函数大致类似 格式:REPLACE(old_text,start_num,num_chars,new_text)
old_text:原始的文本数据
start_num:设置要从old_text的第几个字符位置开始替换
num_chars:设置共有多少字符要被替换
new_text:要用来替换的新字符串
5(SEARCH函数
功能:用来返回指定的字符串在原始字符串中首次出现的位置
格式:SEARCH(find_text,within_text,start_num)
find_text:要查找的文本字符串
within_text:要在哪一个字符串查找
start_num:从within_text的第几个字符开始查找。
注意:在find_text中,可以使用通配符,例如:问号“,”和星号“*”。其中问号“,”代表任何一个字符,而星号“*”可代表任何字符串。如果要查找的字符串就是问号或星号,则必须在这两个符号前加上“,”符号。
日期与时间函数
1(DATE函数
功能:计算某一特定日期的系列编号
格式:DATE(year,month,day)
year:指定年份
month:每年中月份的数字
day:在该月份中第几天的数字
注意:1、若year是介于0,1899之间,则Excel会自动将该值加上1900,再计算year ;若year是介于1900,9999之间,则Excel将使用该数值作为year 。
2、如果所输入的月份month值大于12,将从指定年份一月份开始往上累加 。
3、如果所输入的天数day值大于该月份的最大天数时,将从指定月数的第一天开始往上累加 。
2(DAY函数
功能:返回指定日期所对应的当月中的第几天的数值,介于1,31之间 格式:DAY(serial_number)
serial_number:指定的日期或数值
DAY函数的使用有两种方法:
参数serial_number使用的是日期输入
参数serial_number使用的是数值的输入:在Excel中,系统将1900年1月1日对应于序列号1,后面的日期都是相对于这个时间进行对序列号的进行累加
3(TODAY()
功能:返回当前系统的日期
格式:TODAY()
其语法形式中无参数,若要显示当前系统的日期,可以在当前单元格中直接输入公式TODAY()。
4(TIME函数
功能:返回某一特定时间的小数值,它返回的小数值从0,0.99999999之间,代表0:00:00(12:00:00A.M) ~ 23:59:59 (11:59:59P.M)之间的时间
格式:TIME(hour,minute,second)
hour:0,23之间的数,代表小时
minute:0,59之间的数,代表分
second:0,59之间的数,代表秒
查找与引用函数
1(VLOOKUP函数
功能:从一个数组或表格的最左列中查找含有特定值的字段,再返回同一列中某一指定单元格中的值
格式:
VLOOKUP(lookup_value,talbe_array,col_index_num,range_lookup) look_value:要在数组中搜索的数据,它可以是数值、引用地址或文字字符串 table_array:要搜索的数据表格、数组或数据库
col_index_num:一个数字,代表要返回的值位于table_array中的第几列。 rang_lookup:一个逻辑值,如果其值为“TRUE”或被省略,则返回部分符合的数值;如果该值为“FALSE”时,函数只会查找完全符合的数值,如果找不到,则返回错误值“,N/A”。 如果range_lookup为“TRUE”,则table_array第一列的值必须以递增次序排列 2(HLOOKUP函数
功能:可以用来查询表格的第一行的数据
格式:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) look_value:要在表格第一行中搜索的值
table_array:要搜索的数据表格、数组或数据库
row_index_num:要返回的值位于table_array列中第几行 rang_lookup:一个逻辑值,如果其值为“TRUE”或被省略,则返回部分符合的数值;如果该值为“FALSE”时,函数只会查找完全符合的数值,如果找不到,则返回错误值“,N/A”。
如果 range_lookup 为 “TRUE” ,则 table_array 第一列的值必须以递增次序排列,如果 rang_lookup 是 “FALSE” ,则table_array不需要先排序。
3(LOOKUP函数
LOOKUP函数有两种语法形式:向量(较常用)和数组。
向量形式的格式:
LOOKUP(lookup_value,lookup_vector,result_vector)
look_value:要录找的数据
lookup_vector:一个单行或单列范围,内容可以是文字、数字或逻辑值,但要以递增方式排列,否则不会返回正确的值
result_vector:一个单行或单列范围,大小应与lookup_vector相同
查询时,若LOOKUP函数无法找到完全符合的lookup_value,则会采用在lookup_value中仅次于lookup_value的值。
数据库函数
数据库函数格式为:
函数名称(database,field,criteria)
database:构成数据清单或数据库的单元格区域
field:指定函数所使用的数据列
criteria:一组包含给定条件的单元格区域
, 数据库信息函数
• DCOUNT函数 :返回列表或数据库中满足指定条件的
字段
(列)中包含数值的单元格的个数
• DGET函数:从列表或数据库的列中提取符合指定条件的单个值
• DCOUNTA函数:返回列表或数据库中满足指定条件的记录字段
(列)中非空单元格的个数
, 数据库
函数
• DAVERAGE函数:计算列表或数据库的列中满足指定条件的数值的
平均值
• DMAX函数:返回列表或数据库的列中满足条件的最大值
• DPRODUCT函数:返回列表或数据库中满足指定条件的记录字段
(列)中数值的乘积
• DSUM函数是用来返回列表或数据库中满足指定条件的记录字段
(列)中的数字之和
其它函数 1(SUM和SUMIF函数
SUM函数功能:返回指定参数所对应的数值之和 格式:SUM(number1,number2,……)
number1,number2等这些是指定所要进行求和的参数 注意:函数中可以包含的参数个数为1到30个之间,参数类型可以是数字、逻辑值和数字
的文字表示等形式
SUMIF函数:用于计算符合指定条件的单元格区域内的数值进行求和 ,格式为:SUMIF
(range,criteria,sum_range)
range 表示的是条件判断的单元格区域;
criteria 表示的是指定条件表达式;
sum_range 表示的是需要计算的数值所在的单元格区域 2(AVERAGE函数
功能:返回指定参数所对应数值的算术平均数 格式:AVERAGE(number1,number2,……) number1,number2等是指定所要进行求平均值的参数 注意:该函数只对参数的数值求平均数,如区域引用中包含了非数值的数据,则AVERAGE
不把它包含在内
3(IF函数
IF函数是一个条件函数
格式:IF(logical_test,value_if_true,value_if_false) logical_test:当值函数的逻辑条件
value_if_true:当值为“真”时的返回值
value_if_false:当值为“假”时的返回值
功能:能为对满足条件的数据进行处理,条件满足则输出value_if_true,不满足则输出
value_if_false
注意:在IF函数中三个参数中可以省略value_if_true或value_if_false,但不能同时省略
在IF函数中使用嵌套函数,最多可用嵌套7层 4(IS函数
功能:测试单元格中的内容是否为目标内容或者格式。 格式:IS(xxx)(value )
value:测试的值或单元格地址
IS类函数有以下一些:
ISBLANK(value):是否为空白单元格
ISERR(value):是否为 #N/A之外的任何一种错误值 ISERROR(value):是否为任何一种错误值
ISLOGICAL(value):是否为逻辑值
ISNA(value):是否为错误值 #N/A
ISNONTEXT(value):是否为任何非文本或空单元格 ISNUMBER(value):是否为数字
ISREE(value):是否为引用
ISTEXT(value) :是否为文本
5(TYPE函数
功能:返回测试值的数据类型
格式:TYPE(value)
value::任何数据值,如数字、文本、逻辑值等
返回结果情况:
如果测试值value是数字,则函数会返回1
如果测试值value是文本,则函数会返回2
如果测试值value是逻辑值,则函数会返回4
如果测试值value是错误值,则函数会返回16
如果测试值value是数组型,则函数会返回64
6(COUNT函数
功能:用于返回数字参数的个数,即统计数组或单元格区域中含有数值类型的单元格个数
格式:COUNT(value1,value2,……)
value1,value2,……表示包含或引用各类型数据的参数 注意: 1、函数可以最多附带上1,30个参数
2、只有数字类型的数据才能被统计
类似函数:
COUNTA函数:返回参数组中非空值的数目,即计算数组或单元格区域中数据项
的个数
COUNTBLANK函数:计算某个单元格区域中空白单元格的数目
COUNTIF函数:计算区域中满足给定条件的单元格的个数 7(ROUND函数
功能:根据指定的位数,将数字四舍五入
格式:ROUND(number,num_digits)
number:将要进行四舍五入的数字
num_digits:用户希望得到的数字的小数点后的位数 类似函数:
ROUNDDOWN函数:按指定位数舍去数字指定位数后面的小数 ROUNDUP函数:按指定位数向上舍入指定位数后面的小数 8(最值函数
功能:MAX函数:求参数列表中对应数字的最大值 MIN函数:求参数列表中对应数字的最小值
格式:MAX(number1,number2,……)
MIN(number1,number2,……)
number1、number2、……:从中找出最大值或最小值的1,30个数字参数 类似函数:MAXA(value1,value2,……)
MINA(value1,value2,……)
区别:MAXA和MINA函数的参数类型可以是数字、空参数、逻辑值或数字的文本表示等
形式 。