公式与函数汇总

1、格式格式:Sum(number1,number2,)功能功能:返回参数所对应的数值之和返回参数所对应的数值之和.Average(number1,number2)对参数中的数值求平均数对参数中的数值求平均数MAX (number1,number2,) MIN (number1,number2,)COUNT (number1,number2,) 统计数值型数据个数统计数值型数据个数COUNTIF (range,criteria) 统计给定区域内满足特定条件单元格的数目，条件可以表示为统计给定区域内满足特定条件单元格的数目，条件可以表示为32,”32”,”32”,”apples”,b4IF(logi

2、cal_test,value_if_true, value_if_false) 根据逻辑计算的真假值根据逻辑计算的真假值,返回不同结果返回不同结果.若若logical_test为真为真,返回返回value_if_true,否则否则,返回返回value_if_false Replace( )REPLACE(old_text,start_num,num_chars,new_text)Old_text是要替换其部分字符的文本。Start_num是要用new_text替换的old_text中字符的位置。Num_chars是希望REPLACE使用new_text替换old_text中字符的个数。值为0时

3、,即为插入.New_text是要用于替换old_text中字符的文本。如:REPLACE(A2,6,5,“*”)从第六个字符开始，替换5个字符REPLACE(A3,3,2,“10”)用10替换2009的最后两位REPLACE(A4,1,3,“”)用替换前三个字符 Year( ) Now( ) AND( ) OR( ) ABS( ) 数组公式可以同时进行多个计算并返回一种或多种结果。数组公式对两组或多组被称为数组参数的数值进行运算。每个数组参数必须有相同数量的行和列。除了不能用Ctrl+Shift+Enter生成公式外，创建数组公式的方法与创建其他公式的方法相同。输入数组公式首先必须选择用来存放

4、结果的必须选择用来存放结果的单元格区域单元格区域（可以是一个单元格）， RANK(number,ref,order)Number为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字，指明排位的方式。如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。如:RANK(A3,A2:A6,1) 数组公式数组公式:HLOOKUP(lookup_value,table_array,row_index

5、_num,range_lookup)Lookup_value为需要在数据表第一行中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。row_index_num 为table_array中待返回的匹配值的行序号。 Range_lookup 为一逻辑值，如果range_lookup为TRUE，则table_array的第一行的数值必须按升序排列；如果range_lookup为FALSE，则table_array不必进行排序。(指定在查找时要求精确匹配，还是大致匹配。如果为FALSE，大致匹配，如

6、果为TRUE或忽略，精确匹配）计算两个时间的差值计算两个时间的差值使用减号运算符(-)求时间之间的差值，并使用TEXT函数设置时间的格式。text(b2-a1,”h:mm:ss”)“hh:mm:ss”“h:mm”“h”等MINUTE(),HOUR()SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。如:SUMIF(A2:A5,160000,B2:B5) 数据库函数DSU

7、M(database,field,criteria)database：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含每一列的标签。field：指定函数所使用的列。输入列标签，并将其包括在双引号中，例如“树龄”或“产量”；此外，也可以输入代表列表中列位置的数字（不加引号）：1表示第一列，2表示第二列，依此类推。criteria：为包含指定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标签并且在列标签下方至少有一个用于为该列指定条件的单元格。Istext()Istext()ABCDEF

8、树种树种高度高度树龄树龄产量产量利润利润高度高度=苹果树1016=梨树树种树种高度高度树龄树龄产量产量利润利润苹果树182014105梨树12121096樱桃树13149105苹果树14151075梨树98876.8苹果树89645本次的主要内容本次的主要内容文本函数的应用文本函数的应用(istext)(istext)其它其它(VlookupCountblank)(VlookupCountblank)文本函数文本函数MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。MID(text,start_num,n

9、um_chars)Text是包含要提取字符的文本字符串。Start_num是文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。Num_chars指定希望MID从文本中返回字符的个数。如果start_num大于文本长度，则MID返回空文本()。如果start_num小于文本长度，但start_num加上num_chars超过了文本的长度，则MID只返回至多直到文本末尾的字符。如果start_num小于1，则MID返回错误值#VALUE!。如果num_chars是负数，则MID返回错误值#VALUE!。如:A2=FluidFlow=MID(A2,1,5)上面字符串

10、中的5个字符，从第一个字符开始(Fluid)=MID(A2,7,20)上面字符串中的20个字符，从第七个字符开始(Flow)=MID(A2,20,5)因为要提取的第一个字符的位置大于字符串的长度，所以返回空文本()文本函数文本函数CONCATENATE (text1,text2,.)将几个文本字符串合并为一个文本字符串。Text1,text2,.为1到30个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。说明：也可以用&（和号）运算符代替函数CONCATENATE实现文本项的合并。Countblank(range )计算指定单元格区域中空白单元格的个

11、数。 PROPER( ) :将文本字符串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。语法:PROPER(text) Text:包括在一组双引号中的文本字符串、返回文本值的公式或是对包含文本的单元格的引用。EXACT :该函数测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则，返回FALSE。函数EXACT能区分大小写，但忽略格式上的差异。语法语法:EXACT(text1,text2)Text1 待比较的第一个字符串。Text2 待比较的第二个字符串。DGET ( ) 从列表或数据库的列中提取符合指定条件的单个值,且是唯一的。语法:DGET(datab

12、ase,field,criteria)database：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含每一列的标签。field：指定函数所使用的列。输入列标签，并将其包括在双引号中，例如“树龄”或“产量”；此外，也可以输入代表列表中列位置的数字（不加引号）：1表示第一列，2表示第二列，依此类推。criteria：为包含指定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标签并且在列标签下方至少有一个用于为该列指定条件的单元格。财务函数财务函数DB ：使用固定余额递减法，计算一笔资产在

13、给定期间内的折旧值。语法：DB(cost,salvage,life,period,month)Cost为资产原值。Salvage为资产在折旧期末的价值（也称为资产残值）。Life为折旧期限（有时也称作资产的使用寿命）。Period为需要计算折旧值的期间。Period必须使用与life相同的单位。Month为第一年的月份数，如省略，则假设为12。SYD 返回某项资产按年限总和折旧法计算的指定期间的折旧值。语法：SYD(cost,salvage,life,per)Cost为资产原值。Salvage为资产在折旧期末的价值（也称为资产残值）。Life为折旧期限（有时也称作资产的使用寿命）。Per为期间

14、，其单位与life相同SLN ：返回某项资产在一个期间中的线性折旧值。语法：SLN(cost,salvage,life)Cost为资产原值。Salvage为资产在折旧期末的价值（也称为资产残值）。Life为折旧期限（有时也称作资产的使用寿命）。示例零存整取函数零存整取函数FV( )假如你每月初向银行存入现金假如你每月初向银行存入现金500元，如果年利元，如果年利2.15%（按月计息，即月息（按月计息，即月息2.15%/12）。如果你想知道）。如果你想知道5年后的存款年后的存款总额是多少，可以使用公式总额是多少，可以使用公式“=FV（2.15%/12，60，-500，0，1）”计算，其结果为￥计

15、算，其结果为￥31，698.67。 式中的2.15%/12为月息；60为总的付款时间，在按月储蓄的情况下为储蓄月份的总和；-500为每月支出的储蓄金额（-表示支出）；0表示储蓄开始时账户上的金额，如果账户上没有一分钱，则为0否则应该输入基础金额；1表示存款时间是月初还是月末，1为月初0或忽略为月末。 例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利元，如果按年利2.25%，按月计，按月计息（月利为息（月利为2.25%/12），那么两年以后该账户的存款额会是多少呢？），那么两年以

16、后该账户的存款额会是多少呢？公式写为：公式写为：FV(2.25%/12, 24,-2000,0,1)Pmt()Ipmt() PMT函数,FV函数,PV函数有什么区别PMT（ ）功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额。格式：PMT(rate，nper，pv，fv，type)参数：Rate贷款利率，Nper为总投资期(即该项投资的付款期总数)，Pv为现值(也称为本金)，fv为最后一次付款后剩余的贷款金额，如果省略fv，则认为它的值为零，Type指定各期的付款时间是在期初还是期末(1为期初。0为期末)。应用实例：购房还款金额假如你为购房贷款十万元，如果年利率为7%，每月末还款。采

17、用十年还清方式时，月还款额计算公式为“=PMT（7%/12，120，-100000）”。其结果为￥-1，161.08，就是你每月须偿还贷款1161.08元。FV( ) 功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。格式：FV(rate，nper，pmt，pv，type)参数：Rate为各期利率，Nper为总投资期(即该项投资的付款期总数)，Pmt为各期所应支付的金额，Pv为现值(即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金)，Type为数字0或1(0为期末，1为期初)。.PV( )功能：返回投资的现值(即一系列未来付款的当前值的累积和)，如借入方的借入款即为贷出方贷款的现值。格式：PV(rate，nper，pmt，fv，type)参数：Rate为各期利率，Nper为总投资(或贷款)期数，Pmt为各期所应支付的金额，Fv为未来值，Type指定各期的付款时间是在期初还是期末(1为期初。0为期末)。Ipmt()函数用来计算等额还款条件下每期应偿还的利息部分格式:IPMT(rate,per,nper,pv,fv,type) Rate 为各期利率。 Per 用于计算其利息数额的期数，必须在 1 到 nper 之间。