全国计算机等级考试-二级MS-Office高级应用Excel函数总结.pdf

Excel 函数 VLOOKUP 函数函数 语法规则 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数参数 简单说明简单说明 输入数据类型输入数据类型 lookup_value 要查找的值 数值、引用或文本字符串 table_array 要查找的区域要查找的区域 数据表区域 col_index_num 返回数据在查找区域的第几列数 正整数 range_lookup 模糊匹配 TRUE（或不填） /FALSE 参数说明 Lookup_value 为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数 值、引用或文本字符串。 Table_array 为需要在其中查找数据的数据表。使用对区域或区域名称的引用。 col_index_num 为 table_array 中查找数据的数据列序号。 col_index_num 为 1 时， 返回 table_array 第一列的数值，col_index_num 为 2 时，返回 table_array 第二列的数 值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值#VALUE!；如 果 col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值#REF!。 Range_lookup 为一逻辑值，指明函数 VLOOKUP 查找时是精确匹配，还是近似匹 配。如果为 false 或 0 ，则返回精确匹配，如果找不到，则返回错误值 #N/A。如果 range_lookup 为 TRUE 或 1，函数 VLOOKUP 将查找近似匹配值，也就是说，如果找不 到精确匹配值，则返回小于 lookup_value 的最大数值。如果 range_lookup 省略，则默认 为近似匹配。 例如：例如： 【第1套】 =VLOOKUP(D3=VLOOKUP(D3, ,编号对照编号对照!$A$3:$C$19,!$A$3:$C$19,2,2,FALSEFALSE) ) 【第5套】 =VLOOKUP(E3,=VLOOKUP(E3,费用类别费用类别!$A$3:$B$12,!$A$3:$B$12,2,2,FALSEFALSE) ) 【第9套】 =VLOOKUP(D3,=VLOOKUP(D3,图书编目表图书编目表!$A$2:$B$9,!$A$2:$B$9,2,2,FALSEFALSE) ) 【第10套】 =VLOOKUP(A2,=VLOOKUP(A2,初三学生档案初三学生档案!$A$2:$B$56,!$A$2:$B$56,2,2,0 0) ) Excel 函数 SUMPRODUCT 函数函数 说明: 数组参数必须具有相同的维数数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。 含义：SUM:【数】求和；PRODUCT:【数】(乘)积 20 is the product of 5 and 4.二十是五 与四的乘积。 SUMPRODUCT:组合的汉语意思是:乘积之和乘积之和。在给定的几组数组中，将 数组间对应的元素相乘，并返回乘积之和。 语法：SUMPRODUCT（array1，array2，array3， .） Array1，array2，array3， . 为 2 到 30 个数组，其相应元素需要进行相乘并求和 实例 ： B C D E 1 Array1 Array1 Array2 Array2 2 3 4 2 7 3 8 6 6 7 4 1 9 5 3 公式公式：=SUMPRODUCT(B2：C4*D2：E4) 结果结果：两个数组的所有元素对应相乘，然后把乘积相加，即 3*2+4*7+8*6+6*7+1*5+9*3。 说明 1、SUMPRODUCT 函数不支持“*”和“?”通配符。 SUMPRODUCT 函数不能象 SUMIF、COUNTIF 等函数一样使用“*”和“?”等通配符， 要实现此功能可以用变通的方法，如使用 LEFT、RIGHT、ISNUMBER(FIND()或 ISNUMBER(SEARCH()等函数来实现通配符的功能。 2、SUMPRODUCT 函数多条件求和时使用“， ”和“*”的区别：当拟求和的区域中无 文本时两者无区别，当有文本时，使用“*”时会出错，返回错误值 #VALUE!，而使用 “， ”时 SUMPRODUCT 函数会将非数值型的数组元素作为 0 处理，故不会报错。 应用实例 一、基本功能一、基本功能：函数 SUMPRODUCT 的功能返回相应的区域或数组乘积二、用于多条件计二、用于多条件计 数用数学函数数用数学函数 SUMPRODUCT 计算符合计算符合 2 个及以上条件的数据个数个及以上条件的数据个数 注意：TRUE*1=1，FALSE*1=1*FALSE=0，TRUE*0=0*TRUE=0 。数组中用分号分隔， 表示数组是一列数组，分号相当于换行。两个数组相乘是同一行的对应两个数相乘。 Excel 函数 三、三、用于多条件求和对于计算符合某一个条件的数据求和，可以用用于多条件求和对于计算符合某一个条件的数据求和，可以用 SUMIF 函数来解决。如来解决。如 果要计算符合果要计算符合 2 个以上条件的数据求和，用个以上条件的数据求和，用 SUMIF 函数就不能够完成了。这就可以用函数函数就不能够完成了。这就可以用函数 SUMPRODUCT。用函数 SUMPRODUCT 计算符合多条件的数据和，其基本格式是： SUMPRODUCT（条件（条件 1*条件条件 2*，求和数据区域），求和数据区域）考试题中，考试题中，求和公式在原来的计数求和公式在原来的计数 公式中， 在相同判断条件下， 增加了一个求和的数据区域。公式中， 在相同判断条件下， 增加了一个求和的数据区域。 也就是说， 用函数 SUMPRODUCT 求和，函数需要的参数一个是进行一个是进行判断的条件判断的条件，另一个是用来求和的数据区域另一个是用来求和的数据区域。 *1 的解释的解释 umproduct 函数，逗号分割的各个参数必须为数字型数据，如果是判断的结果逻辑值， 就要乘 1 转换为数字。如果不用逗号，直接用*号连接，就相当于乘法运算，就不必添加*1。 例如：例如： 【第1套】 =SUMPRODUCT(=SUMPRODUCT(1*(1*(订单明细表订单明细表!E3:E262!E3:E262=“=“MS OfficeMS Office高级应用高级应用“ “),),订单明细表订单明细表!H3:H262)!H3:H262) 1 =SUMPRODUCT(=SUMPRODUCT(1*(1*(订单明细表订单明细表!C350:C461!C350:C461=“=“隆华书店隆华书店“),“),订单明细表订单明细表!H350:H461)!H350:H461) =SUMPRODUCT(=SUMPRODUCT(1*(1*(订单明细表订单明细表!C263:C636!C263:C636=“=“隆华书店隆华书店“),“),订单明细表订单明细表!H263:H636)/12!H263:H636)/12 【第5套】 =SUMPRODUCT(=SUMPRODUCT(1*(1*(费用报销管理费用报销管理!D74:D340!D74:D340=“=“北京市北京市“),“),费用报销管理费用报销管理!G74:G340) !G74:G340) =SUMPRODUCT(=SUMPRODUCT(1*(1*(费用报销管理费用报销管理!B3:B401!B3:B401=“=“钱顺卓钱顺卓“),“),1*(1*(费用报销管理费用报销管理!F3:F401=!F3:F401=“ “火车票火车票 “),“),费用报销管理费用报销管理!G3:G401!G3:G401) ) =SUMPRODUCT(=SUMPRODUCT(1*(1*(费用报销管理费用报销管理!F3:F401!F3:F401= =“ “飞机票飞机票“)“), ,费用报销管理费用报销管理!G3:G401)/SUM(!G3:G401)/SUM(费用费用 报销管理报销管理!G3:G401)!G3:G401) =SUMPRODUCT(=SUMPRODUCT( (费用报销管理费用报销管理!H3:H401!H3:H401=“=“是是“)*(“)*(费用报销管理费用报销管理!F3:F401!F3:F401=“=“通讯补助通讯补助“),“),费费 用报销管理用报销管理!G3:G401)!G3:G401) 【第7套】 =SUMPRODUCT(=SUMPRODUCT(1*(1*(D3:D17D3:D17=“=“管理管理“),“),I3:I17)I3:I17) =SUMPRODUCT(=SUMPRODUCT(1*(1*(D3:D17D3:D17=“=“管理管理“),“),M3:M17)M3:M17) Excel 函数 IF 函数函数 IF 函数，根据指定的条件来判断其“真“(TRUE)、“假“(FALSE)；根据逻辑计算的真假值，从 而返回相应的内容。用途：执行真假值判断执行真假值判断 函数用法 1IF 函数的语法结构 IF(logical_test，value_if_true，value_if_false) 即：IF 函数的语法结构：IF(条件条件,结果结果 1,结果结果 2)。 2IF 函数的功能 对满足条件的数据进行处理，条件满足则输出结果 1，不满足则输出结果 2。可以省略 结果 1 或结果 2，但不能同时省略。 3条件表达式 把两个表达式用关系运算符(主要有=，=，=85,“优优“,IF(E2=75,“良良“，IF(E2=60,“及格及格“，“不及格不及格“) 函数从左向右执行。首先计算 E2=85，如果该表达式成立，则显示“优”，如果不成 立就继续计算 E2=75，如果该表达式成立，则显示“良”，否则继续计算 E2=60，如果 该表达式成立，则显示“及格”，否则显示“不及格”。 例如：例如： 【第5套】 =IF(WEEKDAY(A3,2)5,=IF(WEEKDAY(A3,2)5,“ “是是“,“,“ “否否“ “) ) 【第7套】 = =R ROUNDOUND(IF(K380000,K380000,K3*45%K3*45%- -1350513505) ) ) ),2),2) 【第10套】 =IF(MOD(MID(C2,17,1),2)=1,=IF(MOD(MID(C2,17,1),2)=1,“ “男男“,“,“ “女女“ “) ) =IF(F2=102,=IF(F2=102,“ “优秀优秀“,“,IF(IF(F2=84,F2=84,“ “良好良好“,“,IF(IF(F2=72,F2=72,“ “及格及格“,“,IF(IF(F272,F272,“ “及格及格“,“,“ “不及格不及格“)“) ) =IF(F2=90,=IF(F2=90,“ “优秀优秀“,“,IF(IF(F2=75,F2=75,“ “良好良好“,“,IF(IF(F2=60,F2=60,“ “及格及格“,“,IF(IF(F260,F260,“ “及格及格“,“,“ “不及格不及格“)“) ) 【第10套】 =IF(MID(A3,4,2)=“01“,=IF(MID(A3,4,2)=“01“,“1“1班班“ “, ,IF(IF(MID(A3,4,2)=“02“,MID(A3,4,2)=“02“,“2“2班班“,“,“3“3班班“)“) ) Excel 函数 SUMIFS 函数函数 根据多个指定条件对若干根据多个指定条件对若干单元格单元格求和。求和。 函数用法 SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .) 1） sum_range 是需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引 用。忽略空白值和文本值。 2) criteria_range1 为计算关联条件的第一个区域。 3) criteria1 为条件 1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将可用来定义将 对对 criteria_range1 参数中的哪些单元格求和参数中的哪些单元格求和。例如，条件可以表示为 32、“32”、B4、“ 苹果“、或“32“。 4）criteria_range2 为用于条件 2 判断的单元格区域。 5) criteria2 为条件 2，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将可用来定义将 对对 criteria_range2 参数中的哪些单元格求和参数中的哪些单元格求和。 4）和 5）最多允许 127 个区域/条件对，即参数总数不超 255 个。 【第9套】 = =SUMIFSSUMIFS( (销售订单销售订单!$H$3:$H$678,!$H$3:$H$678,销售订单销售订单!$E$3:$E$678,!$E$3:$E$678,A4,A4,销售订单销售订单!$C$3:$C$678,!$C$3:$C$678,1 1) ) =SUMIFS(=SUMIFS(销售订单销售订单!$H$3:$H$678,!$H$3:$H$678,销售订单销售订单!$E$3:$E$678,!$E$3:$E$678,A4,A4,销售订单销售订单!$C$3:$C$678,!$C$3:$C$678,2 2) ) =SUMIFS(=SUMIFS(销售订单销售订单!$H$3:$H$678!$H$3:$H$678, ,销售订单销售订单!$E$3:$E$678,!$E$3:$E$678,A4,A4,销售订单销售订单!$C$3:$C$678,!$C$3:$C$678,3 3) ) 【第20套】 =SUMIFS(=SUMIFS(表表11销售额小计销售额小计,表表11日期日期,“=2013“=2013- -1 1- -1“,1“,表表11日期日期,“=2012“=2012- -1 1- -1“,1“,表表 11日期日期,“=2013“=2013- -7 7- -1“,1“,表表 11日期日期,“=2012“=2012- -1 1- -1“,1“,表表 11日期日期,“=2013“=2013- -1 1- -1“,1“,表表 11日期日期,“=2013“=2013- -1 1- -1“,1“,表表11日日 期期,“ =“1990-05-02“ DATEDIF 函数函数 主要用于计算两日期相差年月日数计算两日期相差年月日数，利用该函数可计算相差的天数、月数和年数。 DATEDIF(start_date,end_date,unit) Start_date 为时间段内的起始日期。End_date 为时间段内的结束日期。 Unit 为所需信息的返回类型。”Y” 时间段中的整年数。”M” 时间段中的整月数。”D” 时间段 中的天数。 实例 1： 计算出生日期为 1973-4-1 人的年龄; 公式： =DATEDIF(“1973-4-1“,TODAY(),“Y“) 结果： 33 简要说明 当单位代码为“Y“ 时,计算结果是两个日期间隔的年数. 【第10套】 = =DATEDIF(DATEDIF(- - - -TEXT(MID(C2,7,8),TEXT(MID(C2,7,8),“0“0- -0000- -00“)00“),TODAY(),TODAY(),“y“y“) ) 【第10套】 =DATEDIF(=DATEDIF(F2,F2,H2,H2,“YD“YD“)*24+(I2)*24+(I2- -G2)G2) Excel 函数 MID 函数函数 Mid是一个字符串函数字符串函数，作用是从一个字符串中截取出指定数量的字符。 函数用法 MID(text,start_num,num_chars) Text： 字符串表达式，从中返回字符。 start_num：text 中被提取的字符部分的开始位置。 num_chars： 要返回的字符数。 例：M=4100 A1=Mid(M,1,1) A1=4 A2=Mid(M,2,2) A2=10 例如： 【第2套】 MID(A2,3,2)MID(A2,3,2) 【第8套】 MID(B3,3,2)MID(B3,3,2) 【第10套】 MID(C2,17,1)MID(C2,17,1) MOD 函数函数 是一个求余函数求余函数，即是两个数值表达式作除法运算后的余数。 函数用法 MOD(number,divisor) Number 为被除数。 Divisor 为除数。如果 divisor 为零，函数 MOD 返回值 为原来 number 例：MOD(-3, 2) 等于 1(与后面的数符号相同)验证 mod(3,-2)；MOD(3, -2) 等于-1(与后面 的数符号相同)；mod（3，0）则出错#DIV/0! 例如：例如： 【第10套】 MOD(MID(C2,17,1),MOD(MID(C2,17,1),2 2)=1)=1 RANK 函数函数 rank 函数是排名函数排名函数。最常用的是求某一个数值在某一区域内的排名。 Excel 函数 函数用法 rank 函数语法形式: rank(number,ref,order) number 为需要求排名的那个数值或者单元格名称(单元格内必须为数字) ref 为排名的参照数值区域 order 的为 0 和 1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order 的值请使用 1。 【第3套】 = =RANKRANK(D2,(D2,$D$2:$D$21,$D$2:$D$21,0 0) ) 【第8套】 =RANK(M3,=RANK(M3,M$3:M$102,M$3:M$102,0 0) ) 【第10套】 = =“ “第第“函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值， 然后返回数组的最后一行或最后一列中相同位置的数值。 函数 LOOKUP 有两种语法形式:向量和数组。 使用方法 （1）向量形式向量形式：公式为 = LOOKUP(lookup_value,lookup_vector,result_vector) lookup_value：函数 LOOKUP 在第一个向量中所要查找的数值，它可以为数字、文本、逻 辑值或包含数值的名称或引用； lookup_vector：只包含一行或一列的区域 lookup_vector 的数值可以为文本、数字或逻辑值； result_vector：只包含一行或一列的区域其大小必须与 lookup_vector 相同。 （2）数组形式：公式为 = LOOKUP(lookup_value,array) Array：包含文本、数字或逻辑值的单元格区域或数组它的值，用于与 lookup_value 进行 比较。 例如：LOOKUP(5.2,4.2,5,7,9,10)=5。 注意：lookup_vector 的数值必须按升序排列，否则函数 LOOKUP 不能返回正确的结果。 文本不区分大小写。如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小 Excel 函数 于或等于 lookup_value 的最大数值。如果 lookup_value 小于 lookup_vector 中的最小值， 函数 LOOKUP 返回错误值#N/A。 【第2套】 =LOOKUP(MID(A2,3,2),=LOOKUP(MID(A2,3,2),“01“,“02“,“03“,“01“,“02“,“03“,“1“1班班“,“2“,“2班班“,“3“,“3班班“) ) ROUND 函数函数 EXCEL中的基本函数，作用按指定的位数进对数值指定的位数进对数值进行四舍五入进行四舍五入。 函数用法 ROUND(number, num_digits) number ，要四舍五入的数字。 num_digits ，位数，按此位数对 number 参数进行四舍五入。 =ROUND(2.15, 1) 将 2.15 四舍五入到一个小数位 2.2 =ROUND(2.149, 1) 将 2.149 四舍五入到一个小数位 2.1 =ROUND(-1.475, 2) 将 -1.475 四舍五入到两个小数位 -1.48 =ROUND(21.5, -1) 将 21.5 四舍五入到小数点左侧一位 20 【第7套】 = =ROUNDROUND( (IF(K380000,K3*45%5505,IF(K380000,K3*45%- -13505),13505),2 2) )， WEEKDAY 函数函数 返回某日期的星期数返回某日期的星期数。在默认情况下，它的值为 1(星期天)到 7(星期六)之间的一个整数。 WEEKDAY(serial_number，return_type) serial_number 是要返回日期数的日期， 它有多种输入方式： 带引号的文本串(如 2001/02/26)、 序 列 号 ( 如 35825 表 示 1998 年 1 月 30 日 ) 或 其 他 公 式 或 函 数 的 结 果 ( 如 DATEVALUE(2000/1/30)。 return_type 为确定返回值类型的数确定返回值类型的数字字， 数字 1 或省略，则 1 至 7 代表星期天到星期六， 数字 2 则 1 至 7 代表星期一到星期天， 数字 3 则 0 至 6 代表星期一到星期天。 实例实例 =WEEKDAY(2001/8/28，2) 返回 2(星期二) =WEEKDAY(2003/02/23，3) 返回 6(星期日)。 【第5套】 =IF(WEEKDAY(A3,=IF(WEEKDAY(A3,2 2)5,“)5,“是是“,“,“否否“)“) Excel 函数 MONTH 函数函数 Month 函数指返回一个 Variant (Integer)，其值为 1 到 12 之间的整数，表示一年中的某月。 函数用法 Month(date) 必要的 date 参数，可以是任何能够表示日期的 Variant、数值表达式、字符串表达式或它 们的组合。如果 date 包含 Null，则返回 Null。 【第9套】 =MONTH($B$3:$B$678)=MONTH($B$3:$B$678) HOUR 函数函数 Hour(time) 必要的 time 参数，可以是任何能够表示时刻的 Variant、数值表达式、字符串表达式或它 们的组合。如果 time 包含 Null，则返回 Null。 返回一个 Variant (Integer)，其值为 0 到 23 之间的整数，表示一天之中的某一钟点。 【第19套】 =HOUR(J2)=HOUR(J2) MINUTE 函数函数 用途：返回时间值中的分钟，即介于 0 到 59 之间的一个整数。 语法：MINUTE(serial_number) 参数：Serial_number 是一个时间值，其中包含着要查 找的分钟数。时间有多种输入方式:带引号的文本串(如“6:45 PM“)、十进制数(如 0.78125 表示 6:45 PM)或其他公式或函数的结果(如 TIMEVaLUE(“6:45 PM“)。 实例：公式“=MINUTE(“15:30:00“)”返回 30，=MINUTE(0.06)返回 26 =MINUTE(TIMEVaLUE(“9:45 PM“)返回 45。 【第19套】 MINUTE(J2) MINUTE(J2) Excel 函数 LEFT 函数函数 得到得到字符字符串左部指定个数的字符。串左部指定个数的字符。 LEFT( string, n ) string 指定要提取子串的字符串。 n 指定子串长度返回值 String。 例：如果 A1=安徽省蚌埠市固镇县杨庙乡，则公式“=LEFT(A1,FIND(“省“,A1