Excel非常实用函数大全

1、Excel实用函数大全,2,01-求和有关的函数的应用,1、SUM 1）行或列求和：=SUM(H3:H12) 2）区域求和：=SUM(D3:D12,F3:F12) 注意： SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。对需要参与求和的某个常数，可用“=SUM（单元格区域，常数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。,3,2、SUMIF SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。 要计算销售部某月份

2、加班情况。公式输入为 =SUMIF($C$3:$C$12,“销售部”,$F$3:$F$12) 其中“$C$3:$C$12”为提供逻辑判断依据的单元格区域，“销售部”为判断条件即只统计$C$3:$C$12区域中部门为“销售部”的单元格，$F$3:$F$12为实际求和的单元格区域。,02-求和有关的函数的应用,4,03-四舍五入,函数：ROUND(number,num_digits) Number：将要进行四舍五入的数字 Num_digits：希望得到的数字的小数点后的位数 平常显示小数点后面的数没有真正的四舍五入， 只是显示结果似乎四舍五入,5,04-逻辑函数(AND),函数：AND(logic

3、al1,logical2,) Logical1, logical2,：待检测的1到30个条件值，满足条件返回TRUE，不满足条件返回FALSE,1）在B2单元格中输入数字50，在C2中写公式=AND(B230,B260)。由于B2等于50的确大于30、小于60。所以两个条件值（logical）均为真，则返回结果为TRUE。,2）如果B1-B3单元格中的值为TRUE、FALSE、TRUE，显然三个参数不一样，所以在B4单元格中公式=AND(B1:B3)等于FALSE,05-逻辑函数(OR),函数：OR(logical1,logical2,) Logical1, logical2,：参数中，任何一

4、个参数逻辑值为TRUE，即返回TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方位真。,1）在B2单元格中输入数字50，在C2中写公式=AND(B230,B260)。由于B2等于50的确大于30、小于60。所以两个条件值（logical）均为真，则返回结果为TRUE。,7,06-逻辑函数(IF),函数：IF(logical_test , value_if_true , value_if_false) Logical_test：表示计算结果为TRUE或FALSE的任意值或表达式。 第一个参数logical_test返回的结果为真的话，则执行第二个参数 Value_if

5、_true的结果，否则执行第三个参数Value_if_false的结果。 IF函数可以嵌套七层。,1）在成绩表中根据不同的成绩区分合格与不合格。,8,07-逻辑函数(IF),函数：COUNTIF(range,criteria) Range：需要计算其中满足条件的单元格数目的单元格区域 Criteria：确定哪个单元格将被计算在内的条件，其形式可以为数字、表达式或文本,1）每位学生取得优秀成绩的课程数，成绩大于90分记做优秀。 =COUNTIF(B4:B10,”90”),9,08-文本函数（大小写转换）,函数： LOWER：将一个文字串中的所有大写字母转换为小写字母 UPPER：将文本转换成大写

6、形式 PROPER：将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。,字符串：pLease ComeE Here!,10,09-文本函数（取出字符串中的部分字符）,函数： MID：=MID(text,start_num,num_chars) text是包含要提取字符的文本串，start_num是文本中要提取的第一个字符的位置 LEFT：=LEFT(text,num_chars) text是包含要提取字符的文本串，num_chars指定要由LEFT所提取的字符数 RIGHT：=RIGHT(text,num_chars) text是包含要提取字符的文本串，num_c

7、hars指定希望要RIGHT提取的字符数,从字符串”This is an apple”分别取出字符“This”，“apple”，“is”,11,10-文本函数（TRIM）,从字符串 ” my name is mary”清除空格,函数：TRIM(text) Text为需要清除其中空格的文本 注意：TRIM函数不会清除单词之间的单个空格。,12,11-文本函数（EXACT）,函数：EXACT(text1,text2) Text1为待比较的第一个字符串 Text2为待比较的第二个字符串,13,12-文本函数(CONCATENATE),函数：CONCATENATE(text1,text2 ) 将若干个

8、文字合并至一个文字项中,1）2001,12,21合并写成 2001年12月21日 =CONCATENATE(B3,年,C3,月,D3,日),2）合并写成2000year =CONCATENATE(A2,B2),14,13-文本函数（FIND）,函数：FIND(find_text,within_text,start_num) FINDB(find_text,within_text,start_num) Find_text 要查找的文本。 Within_text 包含要查找文本的文本。 Start_num 指定要从其开始搜索的字符。within_text 中的首字符是编号为 1 的字符。如果省略

9、start_num，则假设其值为 1 注：查找文本区分大小写,*函数 FIND 面向使用单字节字符集 (SBCS) 的语言，而函数 FINDB 面向使用双字节字符集 (DBCS) 的语言,15,Excel中的SEARCH函数和FIND函数都可以在指定的文本字符串中查找另一个文本字符串第一次出现的位置。其语法分别为： SEARCH： SEARCH(find_text,within_text,start_num) FIND： FIND(find_text, within_text, start_num) SEARCH和FIND函数的区别主要有两点： FIND函数区分大小写，而SEARCH函数则不区

10、分 SEARCH函数支持通配符，而FIND函数不支持,文本函数SEARCH和FIND区别,16,14-文本函数(LEN),函数：LEN(text) LENB(text) Text 是要查找其长度的文本。空格将作为字符进行计数。,17,15-文本函数(REPT),函数： REPT(text,number_times) Text 需要重复显示的文本。 Number_times 是指定文本重复次数的正数。,18,16-文本函数(REPLACE),函数： REPLACE(old_text,start_num,num_chars,new_text) Old_text 是要替换其部分字符的文本 Start

11、_num 是要用 new_text 替换的 old_text 中字符的位置 Num_chars 是希望 REPLACE 使用 new_text 替换 old_text 中字符的个数 New_text 是要用于替换 old_text 中字符的文本,19,17-文本函数(SEARCH),函数： SEARCH(find_text, within_text, start_num) SEARCHB(find_text, within_text, start_num) find_text必需。要查找的文本。 within_text必需。要在其中搜索 find_text 参数的值的文本。 start_num

12、可选。within_text 参数中从之开始搜索的字符编号 注：不区分大小写，以在查找文本中使用通配符、问号 (?) 和星号 (*)。,20,18-日期与时间函数,1) 取当前系统时间/日期信息 函数（当前时间）：NOW() Ctrl+Shift;(分号) 函数（当前日期）：TODAY() Ctrl+;(分号),2）取日期/时间的部分字段值 函数（某日期的年份）：YEAR(serial_number) 函数（某日期的月份）：MONTH(serial_number) 函数（某日期的日期）：DAY(serial_number) 函数（某时间的小时数）：HOUR(serial_number) 函数（

13、某时间的分钟数）：MINUTE(serial_number) 函数（某时间的秒数）：SECOND(serial_number),21,19-日期与时间函数(EDATE),函数： EDATE(start_date,months) Start_date 为一个代表开始日期的日期。应使用 DATE 函数输入日期，或者将函数作为其他公式或函数的结果输入。 Month 为 start_date 之前或之后的月数。正数表示未来日期，负数表示过去日期。,22,20-日期与时间函数(WEEKDAY),函数： WEEKDAY(serial_number,return_type) Serial_number表示一

14、个顺序的序列号，代表要查找的那一天的日期。 Return_type为确定返回值类型的数字 1 或省略 数字1(星期日)到数字7(星期六) 2 数字1(星期一)到数字7(星期日) 3 数字0(星期一)到数字6(星期日),23,21-日期与时间函数(WEEKNUM),函数： WEEKNUM(serial_num,return_type) Serial_num代表一周中的日期。 Return_type为一数字，确定星期计算从哪一天开始。默认值为 1 1 星期从星期日开始。星期内的天数从 1 到 7 记数 2 星期从星期一开始。星期内的天数从 1 到 7 记数,24,22-日期与时间函数(WORKDA

15、Y),函数： WORKDAY(start_date,days,holidays) Start_date为一个代表开始日期的日期 Days为 Start_date 之前或之后不含周末及节假日的天数。Days 为正值将产生未来日期；为负值产生过去日期。 Holidays为可选的列表，其中包含需要从工作日历中排除的一个或多个日期,25,23-日期与时间函数(NETWORKDAYS),函数： NETWORKDAYS(start_date,end_date,holidays) Start_date为一个代表开始日期的日期 End_date为终止日期 Holidays表示不在工作日历中的一个或多个日期所构

16、成的可选区域。例如：省/市/自治区和国家/地区的法定假日以及其他非法定假日。该列表可以是包含日期的单元格区域，或是表示日期的序列号的数组常量。,26,24-日期与时间函数(YEARFRAC),函数： YEARFRAC(start_date,end_date,basis) Start_date为一个代表开始日期的日期。 End_date为终止日期。 Basis为日计数基准类型,27,25-日期与时间函数(DATEDIF),函数：DATEDIF(start_date,end_date,unit) 计算两个日期之间的天数、月数或年数,1）根据入离职时间求司龄 求司龄年数： =DATEDIF(A2,B

17、2,y) 求司龄月数： =DATEDIF(A2,B2,ym),28,1）根据身份证号码求出生日期 =CONCATENATE(19,MID(A2,9,2),/,MID(A2,11,2),/,MID(A2,13,2),2）根据参加工作时间求年资（即工龄） =CONCATENATE(DATEDIF(B2,TODAY(),y),年,DATEDIF(B2,TODAY(),ym),个月),26-日期与时间函数(案例),29,27-查找和引用函数(LOOKUP),函数： LOOKUP(lookup_value, lookup_vector, result_vector) lookup_value必需。LOO

18、KUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。 lookup_vector必需。只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。 result_vector必需。只包含一行或一列的区域。result_vector 参数必须与lookup_vector 大小相同,30,28-查找和引用函数(HLOOKUP),函数：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Lookup_value 为需要在数据表第一行中进行查找的数值。Look

19、up_value 可以为数值、引用或文本字符串。 Table_array 为需要在其中查找数据的数据表。使用对区域或区域名称的引用 Row_index_num 为 table_array 中待返回的匹配值的行序号 Range_lookup 为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配,31,29-查找和引用函数(VLOOKUP),函数：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value为需要在表格第一列中查找的数值。Lookup_value 可以为数值或引用 Table_ar

20、ray为两列或多列数据。使用对区域或区域名称的引用 Col_index_num为 table_array 中待返回的匹配值的列序号 Range_lookup为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值,32,30-统计函数(AVERAGE),函数： AVERAGE(number1, number2,.) number1必需。要计算平均值的第一个数字、单元格引用或单元格区域 number2, .可选。要计算平均值的其他数字、单元格引用或单元格区域,33,31-统计函数(AVERAGEIF),函数： AVERAGEIF(range,criteria,average_range

21、) range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。 criteria是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、32、32、苹果 或 B4。 average_range是要计算平均值的实际单元格集。如果忽略，则使用 range,34,32-统计函数(AVERAGEIFS),函数AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2) Average_range 是要计算平均值的一个或多个单元格，其

22、中包括数字或包含数字的名称、数组或引用。 Criteria_range1, criteria_range2, 是计算关联条件的 1 至 127 个区域。 Criteria1, criteria2, 是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求平均值。例如，条件可以表示为 32、32、32、苹果 或 B4,35,33-统计函数(AVERAGEIFS),36,34-统计函数(MAX),函数 MAX(number1,number2,.) Number1, number2, . 是要从中找出最大值的 1 到 255 个数字参数,37,35-统计函数(MIN),函数 MIN(number1,number2,.) Number1, number2, . 是要从中查找最小值的 1 到 255 个数字,38,36-统计函数(COU