EXCEL常用函数.ppt

函 数 的 构 成函数由函数表达式与工作参数构成,一般格式:函数名(参数1,参数2,参数N)SUM (A1,A2,A4)函数运算时需要注意事项:v 函数名与其后的“(”之间的不能有空格。v 当有多个参数时,参数之间用“,”分隔。v 参数部分总长度不能超过1024个字符。v 根据具体函数,参数可以是数字,文本,逻辑值,工作表中的单元格或区域地址等,也可以是各种表达式或函数。v 注意函数格式中的“( )”和“,”均是半角字符,而非全角的中文字符。函数运算时需要注意事项:v 文本内容:表示输出文本,引号之间的内容以文本类型输出.(函数公式里面使用)v : 表示单元格输出空,没有输入任何数据(包括数据零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 $2:$2表示绝对引用第2行.标点符号的含义,分隔参数,一个公式里面需要很多参数时用逗号分隔.公式中引用的固定字段.&连接符号,表示将两个固定字段(包括公式返回值)连接成文字.+加法.-减法.*乘法./除法.-负负为正$公式里面代表绝对参照,可以选中公式里的范围,按F4快速转换不同参照方式.大于=大于等于不等于=等于代表文字格式.*代表不定字符.?代表一个字符.#代表一个数字.格式中代表数字格式.%百分号次方.如43 代表4的3次方,结果就是64( )括弧是函数开始和结束的标记. 引用里面代表参照了另一个Excel文件.!引用了另一个Sheet工作表。注意:星号(*)和问号(?)是通配符,如果要查找真正的问号或星号,请在该字符前键入波形符 ()。引用本薄本表的A1单元格, 输入A1引用本薄sheet2的A1单元格,输入=sheet2!A1引用He工作薄的sheet2表的A1单元格,输入He.xlssheet2!A1A.冒号(:)区域操作符如: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.逗号(,)合并区域操作符例:定义了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)数 学 统 计 函 数ABS:求出参数的绝对值v 主要功能:求出相应数字的绝对值。 v 使用格式:ABS(number) v 参数说明:number代表需要求绝对值的数值或引用的单元格。 v 应用举例:如果在B1单元格中输入公式:=ABS(A1),则在A1单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 v 特别提醒:如果number参数不是数值,而是一些字符(如A A等),则B2中返回错误值#VALUE#VALUE！。 INT函数v 主要功能:将数值向下取整为最接近的整数。 v 使用格式:INT(number) v 参数说明:number表示需要取整的数值或包含数值的引用单元格。 v 应用举例:输入公式:=INT(18.88),确认后显示出18。 特别提醒:取整时,不会进行四舍五入;如果输入的公式为=INT(-18.88),则返回结果为-19。如果想在取整时对小数部分四舍五入,可将计算式写为INT(参数+0.5)SUMIF函数 v主要功能:计算符合指定条件的单元格区域内的数值和。 v使用格式:SUMIF(Range,Criteria,Sum_Range) v参数说明:Range代表条件判断的单元格区域;Criteria为指定条件表达式;Sum_Range代表需要计算的数值所在的单元格区域。 应用举例:如图所示,在B11单元格中输入公式:=SUMIF(A2:C10,A4,B2:B10),确认后即可求出1 1月合计。特别提醒:如果把上述公式修改为:=SUMIF(A2:C10,2月,C2:C10),即可求出2月合计。其中“小计”由于是文本型的,需要放在英文状态下的双引号里面。AVERAGE函数v 主要功能:求出所有参数的算术平均值。 v 使用格式:AVERAGE(number1,number2,) v 参数说明:number1,number2,:需要求平均值的数值或引用单元格(区域),参数不超过30个。 v 应用举例:在B8单元格中输入公式:=AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 v 特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算。 l主要功能:返回列表或数据库中的分类汇总。 l使用格式:SUBTOTAL(function_num, ref1, ref2, .) l参数说明:Function_num为1到11,用来指定使用什么函数在列表中进行分类汇总计算;ref1, ref2,代表要进行分类汇总区域或引用,不超过29个。MAX函数 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 特别提醒:如果参数中有文本或逻辑值,则忽略。MIN函数v 主要功能:求出一组数中的最小值。 v 使用格式:MIN(number1,number2) v 参数说明:number1,number2代表需要求最小值的数值或引用单元格(区域),参数不超过30个。 v 应用举例:输入公式:=MIN(E44:J44,7,8,9,10),确认后即可显示出E44至J44单元和区域和数值7,8,9,10中的最小值。 v 特别提醒:如果参数中有文本或逻辑值,则忽略。 MOD函数v 主要功能:求出两数相除的余数。 v 使用格式:MOD(number,divisor) v 参数说明:number代表被除数;divisor代表除数。 v 应用举例:输入公式:=MOD(13,4),确认后显示出结果“1”。 v 特别提醒:如果divisor参数为零,则显示错误值“#DIV/0!”;MOD函数可以借用函数INT来表示:上述公式可以修改为:=13-4*INT(13/4)。COUNTIF函数 v 主要功能:统计某个单元格区域中符合指定条件的单元格数目。 v 使用格式:COUNTIF(Range,Criteria) v 参数说明:Range代表要统计的单元格区域;Criteria表示指定的条件表达式。 v 应用举例:在B1单元格中输入公式:=COUNTIF(A1:A6,=60,B1=60),确认。如果C1中返回TRUE,说明A1和B1中的数值均大于等于60,如果返回FALSE,说明A1和B1中的数值至少有一个小于60。 v 特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME?”。 OR函数 v 主要功能:返回逻辑值,仅当所有参数值均为逻辑“假(FALSE)”时返回函数结果逻辑“假(FALSE)”,否则都返回逻辑“真(TRUE)”。 v 使用格式:OR(logical1,logical2, .) v 参数说明:Logical1,Logical2,Logical3:表示待测试的条件值或表达式,最多这30个。 v 应用举例:C1单元格输入公式:=OR(A1=60,B1=60),确认。如果C1中返回TRUE,说明A1和B1中的数值至少有一个大于或等于60,如果返回FALSE,说明A1和B1中的数值都小于60。 l特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME?”。 IF函数 v 主要功能:根据对指定条件的逻辑判断的真假结果,返回相对应的内容。 v 使用格式:=IF(Logical,Value_if_true,Value_if_false) v 参数说明:Logical代表逻辑判断表达式;Value_if_true表示当判断条件为逻辑“真(TRUE)”时的显示内容,如果忽略返回“TRUE”。Value_if_false表示当判断条件为逻辑“假(FALSE)”时的显示内容,如果忽略返回“FALSE”。v 应用举例(1):在B2单元格中输入公式:=IF(A2=60,及格,不及格),确定以后,如果A2单元格中的数值大于或等于60,则B2单元格显示“及格”字样,反之显示“不及格”字样。 v 特别提醒:IF函数可以依据我们所需的逻辑关系进行多重套用,方法为:将关系层层剥离,用公式层层连接,巧用AND与OR搭桥。v 应用举例(2):v 应用举例(3):F2单元格 =IF(D2=60,D275,D285),良,优秀)学生成绩的等级排名.True函数v主要功能:返回逻辑值true.v使用格式:true() v参数说明:函数没有参数,不是返回参数的逻辑值,相当于直接输入TRUE,故不经常使用.False函数v主要功能:返回逻辑值False.v使用格式:False() v参数说明:函数没有参数,不是返回参数的逻辑值,相当于直接输入false,故不经常使用.Not函数v 主要功能:对参数的逻辑值求反:如果参数值为逻辑“真(TRUE)”,则返回逻辑“假(FALSE)”,反之返回逻辑“真(TRUE)”。 v 使用格式:NOT(参数)v 例:D3单元格输入=NOT(2*2=4),D3单元格的计算结果为“FALSE”.v 注意:EXCEL函数里面的不等于不是“!=”,而是“”。条 件 格 式条件格式条件格式:为符合指定条件的单元格添加样式,例如:文本颜色,填充,线框.步骤:条件格式 - 选择合适的格式.信 息 函 数ISERROR函数v 主要功能:用于测试函数式返回的数值是否有错。如果有错,该函数返回TRUE,反之返回FALSE。 v 使用格式:ISERROR(value) v 参数说明:Value表示需要测试的值或表达式。ISNUMBER函数 v 主要功能:判断测试值是否为数值形式,如果是数值形式,则返回为TRUE。 v 使用格式:ISNUMBER(value) v 参数说明:Value测试值,可以是单元格,公式,数值的名称。查 找 引 用 函 数MATCH函数 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值的位置,或者文本内容为lookup_value的位置.MATCH函数 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 的第一个数值。v Lookup_array 可以按任何顺序排列。v 如果省略match_type,则默认为1。v 应用举例:如图4所示,F2单元格中输入公式:=MATCH(E2,B1:B11,0),确认后则返回查找的结果“9”。 v 特别提醒:Lookup_array只能为一列或一行。 ADDRESS函数 v 主要功能:使用文字形式返回对工作薄中某一单元格的引用。 v 使用格式:ADDRESS(row_num,column_num, abs_num, A1,sheet_text) v 参数说明:v Row_num 在单元格引用中使用的行号。v Column_num 在单元格引用中使用的列标。v Abs_num 指明返回的引用类型。(绝对引用=1;绝对行/ /相对列=2;相对行/ /绝对列=3;相对引用=4)v A1逻辑值,指定是以A1还是以R1C1样式表示返回的引用:A1样式=1或TRUE;R1C1样式=0或FALSE。v Sheet_text字符串,指定用作外部引用的工作表名称。v 应用举例:v 特别提醒:ISNUMBER、ADDRESS、MATCH通常组合使用。 COLUMN 函数 v 主要功能:显示所引用单元格的列标号值。 v 使用格式:COLUMN(reference) v 参数说明:reference为引用的单元格。v 应用举例:B1单元格中输入公式:=COLUMN(B1),确认后显示2(即B列)。 v 特别提醒:如果在B2单元格中输入公式:=COLUMN(),显示出2;与之相对应的还有一个返回行标号值的函数:ROW(reference)。 INDEX函数v 主要功能:返回列表或数组中的元素值,此元素由行序号和列序号的索引值进行确定。 v 使用格式:INDEX(array,row_num,column_num) v 参数说明:v Array代表单元格区域或数组常量。v Row_num表示指定的行序号(如果省略row_num,则必须有 column_num)。v Column_num表示指定的列序号(如果省略column_num,则必须有 row_num)。 v 应用举例:如图3所示,在F11单元格中输入公式:=INDEX(A1:D11,4,3),确认后则显示出A1至D11单元格区域中,第4行和第3列交叉处的单元格(即C4)中的内容。 v 特别提醒:此处的行序号参数(row_num)和列序号参数(column_num)是相对于所引用的单元格区域而言的,不是Excel工作表中的行或列序号。VLOOKUP函数 v 主要功能:用于搜索表区域首列中查找满足条件的元素,确定行序号,返回该行中参数索引列号指定的单元格中的值。v 使用格式: VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)v 参数说明:v lookup_value:查找值,需要在数据表首列进行搜索的值,可以是数值、引用或字符串。v table_array:查找表或区域,需要在其中搜索数据的信息表,可以是对区域或区域名称的引用。v col_index_num:索引列号,满足条件单元格在数据区域(table_array)里的列序号,首列序号为1。v range_lookup:检索类型,指定在查找时要求精确匹配,FALSE指大致匹配,TRUE或忽略指精确匹配。(数字0表示大致匹配）.v 应用举例:HLOOKUP函数 v 主要功能:用于在一个构造好的表中的最上行查找一个指定值(列号),返回该列中参数索引行号指定的单元格的值。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:检索类型,指定在查找时要求精确匹配,FALSE指大致匹配,TRUE或忽略指精确匹配。v 应用举例:v 特别提醒:查找表或区域应从第1行开始搜索。v LookUp函数LOOKUP函数和VLOOKUP函数是EXCEL中最常用的两个查找函数。VLOOKUP函数能做到的LOOKUP函数同样可以做到,而且可以做得更好。lLOOKUP函数有两种语法形式：向量和数组。l向量形式的语法：LOOKUP(lookup_value ,lookup_ve ctor,result_v e ctor)lLookup_value:所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。lLookup_vector:只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。lLookup_vector的数值必须按升序排序:.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE,否则,LOOKUP不能返回正确的结果。文本不区分大小写。lResult_vector:只包含一行或一列的区域,区域大小必须与 lookup_vector 相同。v LookUp函数v LOOKUP函数说明：v 第一,如果函数 LOOKUP 找不到 lookup_value,则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。v 第二，如果 lookup_value 小于 lookup_vector 中的最小值,函数 LOOKUP 返回错误值 #N/A。v 利用“以大欺小”特性,我们可以运用通用公式来作查找引用。l 通用公式:(第二个参数要求对源数据进行升序排序,使用通用公式可以避免排序)LOOKUP(a,b/(条件),查找数组或区域)a=bLOOKUP(a,b/(条件1)*(条件2),查找数组或区域)a=bl注意:a必须大于等于b。v 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,查不到就是错误值。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/(条件),查找数组或区域)以此为例v 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),肯定是查找不到了lookup_value (即：2)了,则它与 lookup_vector 中小于或等于 lookup_value 的最大值(即：1)匹配。l 通用公式:LOOKUP(2,1/(条件),查找数组或区域)以此为例v 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,查不到就是错误值。v 因此 【0/(条件)】数组由0和#DI V0!组成。v LOOKUP 函数在一个由0和#DI V! 0组成的数组中里面查找 lookup_value (即：0),肯定能如偿所愿了。l 通用公式:LOOKUP(0,0/(条件),查找数组或区域)以此为例时 间 函 数DATE函数 v 主要功能:给出指定数值的日期。 v 使用格式:DATE(year,month,day) v 参数说明:year为指定的年份数值(小于9999);month为指定的月份数值(可以大于12);day为指定的天数。 v 应用举例:C20单元格中输入公式:=DATE(2003,13,35),确认后,显示出2004-2-4。 v 特别提醒:由于上述公式中,月份为13,多了一个月,顺延至2004年1月;天数为35,比2004年1月的实际天数又多了4天,故又顺延至2004年2月4日。DATEDIF函数 主要功能:计算返回两个日期参数的差值。 使用格式:DATEDIF(date1,date2,y) DATEDIF(date1,date2,m)DATEDIF(date1,date2,d)参数说明:date1代表前面一个日期,date2代表后面一个日期;y(m、d)要求返回两个日期相差的年(月、天)数。v 应用举例:B1单元格中输入公式:=DATEDIF(A1,TODAY(),y),确认后返回系统当前日期(用TODAY()表示)与A1单元格中日期的差值,并返回相差的年数。 v 特别提醒:这是Excel中的一个隐藏函数,函数向导中是找不到的,可以直接输入使用,对于计算年龄、工龄等非常有效。 DAY函数v 主要功能:求出指定日期或引用单元格中的日期的天数。v 使用格式:DAY(serial_number) v 参数说明:serial_number代表指定的日期或引用的单元格。 v 应用举例:输入公式:=DAY(2003-12-18),确认后,显示出18。 v 特别提醒:如果是给定的日期,请包含在英文双引号中。MONTH函数v 主要功能:求出指定日期或引用单元格中的日期的月份。 v 使用格式:MONTH(serial_number) v 参数说明:serial_number代表指定的日期或引用的单元格。 v 应用举例:输入公式:=MONTH(2003-12-18),确认后,显示出12。 v 特别提醒:如果是给定的日期,请包含在英文双引号中;如果将上述公式修改为:=YEAR(2003-12-18),则返回年份对应的值2003。NOW函数 v 主要功能:给出当前系统日期和时间。 v 使用格式:NOW() v 参数说明:该函数不需要参数。 v 应用举例:输入公式:=NOW(),确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变,只要按一下F9功能键,即可让其随之改变。 v 特别提醒:显示出来的日期和时间格式,可以通过单元格格式进行重新设置。 v 应用举例:输入公式:=ISERROR(A1/ /B1),确认以后,如果B1单元格为空或“0”,则A1/ /B1出现错误,此时前述函数返回TRUE结果,反之返回FALSE。 v 特别提醒:此函数通常与IF函数配套使用,如果将上述公式修改为:=IF(ISERROR(A1/ /B1),A1/ /B1),如果B1为空或“0”,则相应的单元格显示为空,反之显示A1/B1的结果。公式错误值分析v #DIV/0!除数为零。v #N/A公式中无可用的数值或函数缺少参数。v #NUM！在需要数字参数的函数中使用了不能接受的参数或公式产生的数字太大(太小),不能表示。v #VALUE！输入了一个不能运算的参数或单元格里的内容包含不能运算的对象。 v #NULL！使用了不正确的区域运算或不正确的单元格引用。v #REF！误删了公式中的引用区域,使公式或函数中缺少应有的参数。v #NAME！删除了公式中使用的名称或使用了不存在的名称以及名称有拼写错误。v #！公式产生的结果太长,单无格容纳不下。如果用户对日期和时间进行减法运算,请确认格式是否正确。