EXCEL常用函数

1、.1常用函数介绍.2函 数 的 构 成.3函数由函数表达式与工作参数构成,一般格式:函数名(参数1,参数2,参数N)SUM (A1,A2,A4)函数运算时需要注意事项:v 函数名与其后的“(”之间的不能有空格。v 当有多个参数时,参数之间用“,”分隔。v 参数部分总长度不能超过1024个字符。v 根据具体函数,参数可以是数字,文本,逻辑值,工作表中的单元格或区域地址等,也可以是各种表达式或函数。v 注意函数格式中的“( )”和“,”均是半角字符,而非全角的中文字符。.4函数运算时需要注意事项:v 文本内容:表示输出文本,引号之间的内容以文本类型输出.(函数公式里面使用)v : 表示单元格输出空

2、,没有输入任何数据(包括数据零0).(函数公式里面使用)v (单个):单个的单引号表示把单元格里面的内容转成文本类型.如,E3单元格输入前导零数据001,程序自动把结果转化为1,如果先输入,再输入001,那么结果就为001,即把数值型内容转化成文本型内容.v (双个):表示引用名称.如,8月!C4表示引用名称为8月的工作表里的C4单元格的数据.如果没有工作表的名称时,可以不用,如,Sheet2!C4 (函数公式里面使用)v $ : 表示绝对引用,没有加就表示相对引用.绝对引用不会随着下拉复制公式而改变引用.v C:C表示引用整个C列.v $C:$C表示绝对引用C列.v 2:2表示引用第2行.v

3、 $2:$2表示绝对引用第2行.5标点符号的含义.6,分隔参数,一个公式里面需要很多参数时用逗号分隔.公式中引用的固定字段.&连接符号,表示将两个固定字段(包括公式返回值)连接成文字.+加法.-减法.*乘法./除法.-负负为正$公式里面代表绝对参照,可以选中公式里的范围,按F4快速转换不同参照方式.大于=大于等于不等于=等于.7代表文字格式.*代表不定字符.?代表一个字符.#代表一个数字.格式中代表数字格式.%百分号次方.如43 代表4的3次方,结果就是64( )括弧是函数开始和结束的标记. 引用里面代表参照了另一个Excel文件.!引用了另一个Sheet工作表。注意:星号(*)和问号

4、(?)是通配符,如果要查找真正的问号或星号,请在该字符前键入波形符 ()。引用本薄本表的A1单元格, 输入A1引用本薄sheet2的A1单元格,输入=sheet2!A1引用He工作薄的sheet2表的A1单元格,输入He.xlssheet2!A1.8A.冒号(:)区域操作符如:A1:F13表示A1单元格到F13单元格的区域。B.空格( )相交区域操作符例:假如定义A1:F13区域为AA,定义了B2:L5区域为BB。如何快速定义 CC为AA与BB相交的区域(即 B2:F5区域),可直接编辑公式CC=AA BB可以用sum函数验证:20=SUM(AA BB)。C.逗号(,)合并区域操作符例:定义了

5、A1:F13区域名称为AA,定义了B2:L5区域为BB。如何快速定义CC为AA与BB合并的区域 (即A1:F13+B2:L5区域),可直接编辑公式CC=AA,BB可以用sum函数验证: 110=SUM(AA,BB) 110=SUM(AA)+SUM(BB).9数 学 统 计 函 数.10ABS:求出参数的绝对值v 主要功能:求出相应数字的绝对值。 v 使用格式:ABS(number) v 参数说明:number代表需要求绝对值的数值或引用的单元格。 .11v 应用举例:如果在B1单元格中输入公式:=ABS(A1),则在A1单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数

6、(如100)。 v 特别提醒:如果number参数不是数值,而是一些字符(如A A等),则B2中返回错误值#VALUE#VALUE！。 .12INT函数v 主要功能:将数值向下取整为最接近的整数。 v 使用格式:INT(number) v 参数说明:number表示需要取整的数值或包含数值的引用单元格。 .13v 应用举例:输入公式:=INT(18.88),确认后显示出18。 特别提醒:取整时,不会进行四舍五入;如果输入的公式为=INT(-18.88),则返回结果为-19。如果想在取整时对小数部分四舍五入,可将计算式写为INT(参数+0.5).14SUMIF函数 v主要功能:计算符合指定条件的

7、单元格区域内的数值和。 v使用格式:SUMIF(Range,Criteria,Sum_Range) v参数说明:Range代表条件判断的单元格区域;Criteria为指定条件表达式;Sum_Range代表需要计算的数值所在的单元格区域。.15 应用举例:如图所示,在B11单元格中输入公式:=SUMIF(A2:C10,A4,B2:B10),确认后即可求出1 1月合计。特别提醒:如果把上述公式修改为:=SUMIF(A2:C10,2月,C2:C10),即可求出2月合计。其中“小计”由于是文本型的,需要放在英文状态下的双引号里面。.16AVERAGE函数v 主要功能:求出所有参数的算术平均值。 v 使

8、用格式:AVERAGE(number1,number2,) v 参数说明:number1,number2,:需要求平均值的数值或引用单元格(区域),参数不超过30个。 .17v 应用举例:在B8单元格中输入公式:=AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 v 特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算。 .18l主要功能:返回列表或数据库中的分类汇总。 l使用格式:SUBTOTAL(function_num, ref1, ref2, .) l参数说明:Fu

9、nction_num为1到11,用来指定使用什么函数在列表中进行分类汇总计算;ref1, ref2,代表要进行分类汇总区域或引用,不超过29个。.19MAX函数 v 主要功能:求出一组数中的最大值。v 使用格式:MAX(number1,number2) v 参数说明:number1,number2代表需要求最大值的数值或引用单元格(区域),参数不超过30个。 v 应用举例:输入公式:=MAX(E44E44:J44J44,7 7,8 8,9 9,1010),确认后即可显示出E44至J44单元和区域和数值7 7,8 8,9 9,1010中的最大值。 v 特别提醒:如果参数中有文本或逻辑值,则忽略。

10、.20MIN函数v 主要功能:求出一组数中的最小值。 v 使用格式:MIN(number1,number2) v 参数说明:number1,number2代表需要求最小值的数值或引用单元格(区域),参数不超过30个。 v 应用举例:输入公式:=MIN(E44:J44,7,8,9,10),确认后即可显示出E44至J44单元和区域和数值7,8,9,10中的最小值。 v 特别提醒:如果参数中有文本或逻辑值,则忽略。 .21MOD函数v 主要功能:求出两数相除的余数。 v 使用格式:MOD(number,divisor) v 参数说明:number代表被除数;divisor代表除数。 v 应用举例:输

11、入公式:=MOD(13,4),确认后显示出结果“1”。 v 特别提醒:如果divisor参数为零,则显示错误值“#DIV/0!”;MOD函数可以借用函数INT来表示:上述公式可以修改为:=13-4*INT(13/4)。.22COUNTIF函数 v 主要功能:统计某个单元格区域中符合指定条件的单元格数目。 v 使用格式:COUNTIF(Range,Criteria) v 参数说明:Range代表要统计的单元格区域;Criteria表示指定的条件表达式。 .23v 应用举例:在B1单元格中输入公式:=COUNTIF(A1:A6,=60,B1=60),确认。如果C1中返回TRUE,说明A1和B1中的

12、数值均大于等于60,如果返回FALSE,说明A1和B1中的数值至少有一个小于60。 v 特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME?”。 .44OR函数 v 主要功能:返回逻辑值,仅当所有参数值均为逻辑“假(FALSE)”时返回函数结果逻辑“假(FALSE)”,否则都返回逻辑“真(TRUE)”。 v 使用格式:OR(logical1,logical2, .) v 参数说明:Logical1,Logical2,Logical3:表示待测试的条件值或表达式,最多这30个。 .45v 应用举例:C1单元格输入公式:=OR(A1=60,B1=60

13、),确认。如果C1中返回TRUE,说明A1和B1中的数值至少有一个大于或等于60,如果返回FALSE,说明A1和B1中的数值都小于60。 l特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME?”。 .46IF函数 v 主要功能:根据对指定条件的逻辑判断的真假结果,返回相对应的内容。 v 使用格式:=IF(Logical,Value_if_true,Value_if_false) v 参数说明:Logical代表逻辑判断表达式;Value_if_true表示当判断条件为逻辑“真(TRUE)”时的显示内容,如果忽略返回“TRUE”。Value_if_

14、false表示当判断条件为逻辑“假(FALSE)”时的显示内容,如果忽略返回“FALSE”。.47v 应用举例(1):在B2单元格中输入公式:=IF(A2=60,及格,不及格),确定以后,如果A2单元格中的数值大于或等于60,则B2单元格显示“及格”字样,反之显示“不及格”字样。 v 特别提醒:IF函数可以依据我们所需的逻辑关系进行多重套用,方法为:将关系层层剥离,用公式层层连接,巧用AND与OR搭桥。.48v 应用举例(2):.49v 应用举例(3):F2单元格 =IF(D2=60,D275,D285),良,优秀)学生成绩的等级排名.50True函数v主要功能:返回逻辑值true.v使用格式

15、:true() v参数说明:函数没有参数,不是返回参数的逻辑值,相当于直接输入TRUE,故不经常使用.51False函数v主要功能:返回逻辑值False.v使用格式:False() v参数说明:函数没有参数,不是返回参数的逻辑值,相当于直接输入false,故不经常使用.52Not函数v 主要功能:对参数的逻辑值求反:如果参数值为逻辑“真(TRUE)”,则返回逻辑“假(FALSE)”,反之返回逻辑“真(TRUE)”。 v 使用格式:NOT(参数)v 例:D3单元格输入=NOT(2*2=4),D3单元格的计算结果为“FALSE”.v 注意:EXCEL函数里面的不等于不是“!=”,而是“”。.53条

16、 件 格 式.54条件格式条件格式:为符合指定条件的单元格添加样式,例如:文本颜色,填充,线框.步骤:条件格式 - 选择合适的格式.55信 息 函 数.56ISERROR函数v 主要功能:用于测试函数式返回的数值是否有错。如果有错,该函数返回TRUE,反之返回FALSE。 v 使用格式:ISERROR(value) v 参数说明:Value表示需要测试的值或表达式。.57ISNUMBER函数 v 主要功能:判断测试值是否为数值形式,如果是数值形式,则返回为TRUE。 v 使用格式:ISNUMBER(value) v 参数说明:Value测试值,可以是单元格,公式,数值的名称。.58查 找 引

17、用 函 数.59MATCH函数 v主要功能:返回在指定方式下与指定数值匹配的数组中元素的相应位置。 v使用格式:MATCH(lookup_value,lookup_array,match_type) lookup_value 查找什么,为一个数值 或者 一个单元格(内容可能是数值或是文本).lookup_array 在哪些数据区别中查找,为一行或者一列数据.match_type 匹配类型:- -1 1 : 查找大于或等于lookup_value最小值的位置. 1 1 : 查找小于或等于lookup_value最大值的位置. 0 0 : 查找等于lookup_value值的位置,或者文本内容为l

18、ookup_value的位置.60MATCH函数 l参数说明: Lookup_value代表需要在数据表中查找的数值。 Lookup_array表示可能包含所要查找的数值的连续单元格区域。 Match_type表示查找方式的值(-1 1,0 0或1 1)。v 如果match_type为-1,查找大于或等于 lookup_value的最小数值。v Lookup_array 必须按降序排列。v 如果match_type为1,查找小于或等于 lookup_value 的最大数值。v Lookup_array 必须按升序排列。v 如果match_type为0,查找等于lookup_value 的第一个

19、数值。v Lookup_array 可以按任何顺序排列。v 如果省略match_type,则默认为1。.61v 应用举例:如图4所示,F2单元格中输入公式:=MATCH(E2,B1:B11,0),确认后则返回查找的结果“9”。 v 特别提醒:Lookup_array只能为一列或一行。 .62ADDRESS函数 v 主要功能:使用文字形式返回对工作薄中某一单元格的引用。 v 使用格式:ADDRESS(row_num,column_num, abs_num, A1,sheet_text) v 参数说明:v Row_num 在单元格引用中使用的行号。v Column_num 在单元格引用中使用的列标

20、。v Abs_num 指明返回的引用类型。(绝对引用=1;绝对行/ /相对列=2;相对行/ /绝对列=3;相对引用=4)v A1逻辑值,指定是以A1还是以R1C1样式表示返回的引用:A1样式=1或TRUE;R1C1样式=0或FALSE。v Sheet_text字符串,指定用作外部引用的工作表名称。.63v 应用举例:v 特别提醒:ISNUMBER、ADDRESS、MATCH通常组合使用。 .64COLUMN 函数 v 主要功能:显示所引用单元格的列标号值。 v 使用格式:COLUMN(reference) v 参数说明:reference为引用的单元格。.65v 应用举例:B1单元格中输入公式

21、:=COLUMN(B1),确认后显示2(即B列)。 v 特别提醒:如果在B2单元格中输入公式:=COLUMN(),显示出2;与之相对应的还有一个返回行标号值的函数:ROW(reference)。 .66INDEX函数v 主要功能:返回列表或数组中的元素值,此元素由行序号和列序号的索引值进行确定。 v 使用格式:INDEX(array,row_num,column_num) v 参数说明:v Array代表单元格区域或数组常量。v Row_num表示指定的行序号(如果省略row_num,则必须有 column_num)。v Column_num表示指定的列序号(如果省略column_num,则必

22、须有 row_num)。 .67v 应用举例:如图3所示,在F11单元格中输入公式:=INDEX(A1:D11,4,3),确认后则显示出A1至D11单元格区域中,第4行和第3列交叉处的单元格(即C4)中的内容。 v 特别提醒:此处的行序号参数(row_num)和列序号参数(column_num)是相对于所引用的单元格区域而言的,不是Excel工作表中的行或列序号。.68VLOOKUP函数 v 主要功能:用于搜索表区域首列中查找满足条件的元素,确定行序号,返回该行中参数索引列号指定的单元格中的值。v 使用格式: VLOOKUP (lookup_value,table_array,col_inde

23、x_num,range_lookup)v 参数说明:v lookup_value:查找值,需要在数据表首列进行搜索的值,可以是数值、引用或字符串。v table_array:查找表或区域,需要在其中搜索数据的信息表,可以是对区域或区域名称的引用。v col_index_num:索引列号,满足条件单元格在数据区域(table_array)里的列序号,首列序号为1。v range_lookup:检索类型,指定在查找时要求精确匹配,FALSE指大致匹配,TRUE或忽略指精确匹配。(数字0表示大致匹配）.69v 应用举例:.70HLOOKUP函数 v 主要功能:用于在一个构造好的表中的最上行查找一个指

24、定值(列号),返回该列中参数索引行号指定的单元格的值。v 使用格式:HLOOKUP (lookup_value,table_array,row_index_num,range_lookup) v 参数说明:text代表需要转换文本型字符串数值。 v lookup_value:查找值,需要在数据表首列进行搜索的值,可以是数值、引用或字符串。v table_array:查找表或区域,需要在其中搜索数据的信息表,可以是对区域或区域名称的引用。v row_index_num:索引行号,满足条件单元格在数组区域(table_array)里的行序号,表中第一行序号为1。v range_lookup:检索类

25、型,指定在查找时要求精确匹配,FALSE指大致匹配,TRUE或忽略指精确匹配。.71v 应用举例:v 特别提醒:查找表或区域应从第1行开始搜索。.72v LookUp函数LOOKUP函数和VLOOKUP函数是EXCEL中最常用的两个查找函数。VLOOKUP函数能做到的LOOKUP函数同样可以做到,而且可以做得更好。lLOOKUP函数有两种语法形式：向量和数组。l向量形式的语法：LOOKUP(lookup_value ,lookup_ve ctor,result_v e ctor)lLookup_value:所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用

26、。lLookup_vector:只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。lLookup_vector的数值必须按升序排序:.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE,否则,LOOKUP不能返回正确的结果。文本不区分大小写。lResult_vector:只包含一行或一列的区域,区域大小必须与 lookup_vector 相同。.73v LookUp函数v LOOKUP函数说明：v 第一,如果函数 LOOKUP 找不到 lookup_value,则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。v

27、第二，如果 lookup_value 小于 lookup_vector 中的最小值,函数 LOOKUP 返回错误值 #N/A。v 利用“以大欺小”特性,我们可以运用通用公式来作查找引用。l 通用公式:(第二个参数要求对源数据进行升序排序,使用通用公式可以避免排序)LOOKUP(a,b/(条件),查找数组或区域)a=bLOOKUP(a,b/(条件1)*(条件2),查找数组或区域)a=bl注意:a必须大于等于b。.74v LookUp函数l 公式中的2、1、0等数字的含义是什么? v 首先,条件为一组逻辑判断的值。一组由TRUE和FALSE逻辑值组成数组,或者为0与非0组成的数组。因而:【0/(条

28、件)】的作用是用于构建一个由0或者#DI V! 0错误值组成的数组。例如:数据源里面能查找到对应值就是ture,没有就是false。形如:0/True=0,0/false=#DI V0! ,查找到就0,查不到就是错误值。v 因此 【0/(条件)】数组由0和#DI V0!组成。v LOOKUP 函数在一个由0和#DI V! 0组成的数组中里面查找 lookup_value (即：1),肯定是查找不到了lookup_value (即：1)了,则它与 lookup_vector 中小于或等于 lookup_value 的最大值(即：0)匹配。l 通用公式:LOOKUP(1,0/(条件),查找数组或区

29、域)以此为例.75v LookUp函数l 公式中的2、1、0等数字的含义是什么? v 首先,条件为一组逻辑判断的值。一组由TRUE和FALSE逻辑值组成数组,或者为0与非0组成的数组。因而:【1/(条件)】的作用是用于构建一个由1或者#DI V! 0错误值组成的数组。例如:数据源里面能查找到对应值就是ture,没有就是false。形如:1/True=1,1/false=#DI V0! ,查找到就1,查不到就是错误值。v 因此 【1/(条件)】数组由1和#DI V0!组成。v LOOKUP 函数在一个由1和#DI V! 0组成的数组中里面查找 lookup_value (即：2),肯定是查找不到

30、了lookup_value (即：2)了,则它与 lookup_vector 中小于或等于 lookup_value 的最大值(即：1)匹配。l 通用公式:LOOKUP(2,1/(条件),查找数组或区域)以此为例.76v LookUp函数l 公式中的2、1、0等数字的含义是什么? v 首先,条件为一组逻辑判断的值。一组由TRUE和FALSE逻辑值组成数组,或者为0与非0组成的数组。因而:【0/(条件)】的作用是用于构建一个由0或者#DI V! 0错误值组成的数组。例如:数据源里面能查找到对应值就是ture,没有就是false。形如:0/True=0,0/false=#DI V0! ,查找到就0

31、,查不到就是错误值。v 因此 【0/(条件)】数组由0和#DI V0!组成。v LOOKUP 函数在一个由0和#DI V! 0组成的数组中里面查找 lookup_value (即：0),肯定能如偿所愿了。l 通用公式:LOOKUP(0,0/(条件),查找数组或区域)以此为例.77时 间 函 数.78DATE函数 v 主要功能:给出指定数值的日期。 v 使用格式:DATE(year,month,day) v 参数说明:year为指定的年份数值(小于9999);month为指定的月份数值(可以大于12);day为指定的天数。 v 应用举例:C20单元格中输入公式:=DATE(2003,13,35)

32、,确认后,显示出2004-2-4。 v 特别提醒:由于上述公式中,月份为13,多了一个月,顺延至2004年1月;天数为35,比2004年1月的实际天数又多了4天,故又顺延至2004年2月4日。.79DATEDIF函数 主要功能:计算返回两个日期参数的差值。 使用格式:DATEDIF(date1,date2,y) DATEDIF(date1,date2,m)DATEDIF(date1,date2,d)参数说明:date1代表前面一个日期,date2代表后面一个日期;y(m、d)要求返回两个日期相差的年(月、天)数。.80v 应用举例:B1单元格中输入公式:=DATEDIF(A1,TODAY(),y),确认后返回系统当前日期(用TODAY()表示)与A1单元格中日期的差值,并返回相差的年数。 v 特别提醒:这是Exce