《[计算机软件及应用]Excel常用函数.ppt》由会员分享，可在线阅读，更多相关《[计算机软件及应用]Excel常用函数.ppt（44页珍藏版）》请在三一办公上搜索。
1、Excel常用函数,2,01-求和有关的函数的应用,1、SUM1）行或列求和：=SUM(H3:H12)2）区域求和：=SUM(D3:D12,F3:F12)注意：SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。对需要参与求和的某个常数，可用“=SUM（单元格区域，常数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。,3,2、SUMIFSUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。要计算销售部某月份加班情况。公式输
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(logical1,logical2,)
3、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,：参数中，任何一个参数逻辑值为TRUE，即返回TRU
4、E。它与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_true的结果，否则执行第三个参数Value_i
5、f_false的结果。IF函数可以嵌套七层。,1）在成绩表中根据不同的成绩区分合格与不合格。,8,07-逻辑函数(IF),函数：COUNTIF(range,criteria)Range：需要计算其中满足条件的单元格数目的单元格区域Criteria：确定哪个单元格将被计算在内的条件，其形式可以为数字、表达式或文本,1）每位学生取得优秀成绩的课程数，成绩大于90分记做优秀。=COUNTIF(B4:B10,”90”),9,08-文本函数（大小写转换）,函数：LOWER：将一个文字串中的所有大写字母转换为小写字母UPPER：将文本转换成大写形式PROPER：将文字串的首字母及任何非字母字符之后的首字母
6、转换成大写。将其余的字母转换成小写。,字符串：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_chars指定希望要RIGHT提取的字符数,从字符串”This is an a
7、pple”分别取出字符“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)将若干个文字合并至一个文字项中,1）2001,12,21合并写成2001年12月21日=CONCAT
8、ENATE(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 的字符。如果省略 start_num，则假设其值为 1注：查找文本区分大小写,*函数 FIND 面向使用单字节字符集(SBC
9、S)的语言，而函数 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函数则不区分 SEARCH函数支持通配符，而FIND函数不支持,文本函数SEARCH和FIND区别,16,14-文本函数(LEN),函数：LE
10、N(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_num 是要用 new_text 替换的 old_text 中字符的位置Num_chars 是希望 REPLACE 使用 new_text 替换
11、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可选。within_text 参数中从之开始搜索的字符编号注：不区分大小写，以在查找文本中使用通配符、问号(?)和星号(*)。,20,18-日期与时间函数,1)取当前系统
12、时间/日期信息函数（当前时间）：NOW()Ctrl+Shift;(分号)函数（当前日期）：TODAY()Ctrl+;(分号),2）取日期/时间的部分字段值函数（某日期的年份）：YEAR(serial_number)函数（某日期的月份）：MONTH(serial_number)函数（某日期的日期）：DAY(serial_number)函数（某时间的小时数）：HOUR(serial_number)函数（某时间的分钟数）：MINUTE(serial_number)函数（某时间的秒数）：SECOND(serial_number),21,19-日期与时间函数(EDATE),函数：EDATE(start_
13、date,months)Start_date 为一个代表开始日期的日期。应使用 DATE 函数输入日期，或者将函数作为其他公式或函数的结果输入。Month 为 start_date 之前或之后的月数。正数表示未来日期，负数表示过去日期。,22,20-日期与时间函数(WEEKDAY),函数：WEEKDAY(serial_number,return_type)Serial_number表示一个顺序的序列号，代表要查找的那一天的日期。Return_type为确定返回值类型的数字1 或省略 数字1(星期日)到数字7(星期六)2 数字1(星期一)到数字7(星期日)3 数字0(星期一)到数字6(星期日),
14、23,21-日期与时间函数(WEEKNUM),函数：WEEKNUM(serial_num,return_type)Serial_num代表一周中的日期。Return_type为一数字，确定星期计算从哪一天开始。默认值为 11 星期从星期日开始。星期内的天数从 1 到 7 记数2 星期从星期一开始。星期内的天数从 1 到 7 记数,24,22-日期与时间函数(WORKDAY),函数：WORKDAY(start_date,days,holidays)Start_date为一个代表开始日期的日期Days为 Start_date 之前或之后不含周末及节假日的天数。Days 为正值将产生未来日期；为负值
15、产生过去日期。Holidays为可选的列表，其中包含需要从工作日历中排除的一个或多个日期,25,23-日期与时间函数(NETWORKDAYS),函数：NETWORKDAYS(start_date,end_date,holidays)Start_date为一个代表开始日期的日期End_date为终止日期Holidays表示不在工作日历中的一个或多个日期所构成的可选区域。例如：省/市/自治区和国家/地区的法定假日以及其他非法定假日。该列表可以是包含日期的单元格区域，或是表示日期的序列号的数组常量。,26,24-日期与时间函数(YEARFRAC),函数：YEARFRAC(start_date,end
16、_date,basis)Start_date为一个代表开始日期的日期。End_date为终止日期。Basis为日计数基准类型,27,25-日期与时间函数(DATEDIF),函数：DATEDIF(start_date,end_date,unit)计算两个日期之间的天数、月数或年数,1）根据入离职时间求司龄求司龄年数：=DATEDIF(A2,B2,y)求司龄月数：=DATEDIF(A2,B2,ym),28,1）根据身份证号码求出生日期=CONCATENATE(19,MID(A2,9,2),/,MID(A2,11,2),/,MID(A2,13,2),2）根据参加工作时间求年资（即工龄）=CONCAT
17、ENATE(DATEDIF(B2,TODAY(),y),年,DATEDIF(B2,TODAY(),ym),个月),26-日期与时间函数(案例),29,27-查找和引用函数(LOOKUP),函数：LOOKUP(lookup_value,lookup_vector,result_vector)lookup_value必需。LOOKUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。lookup_vector必需。只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。result_vector必需。只包含一行或一列的区域。
18、result_vector 参数必须与lookup_vector 大小相同,30,28-查找和引用函数(HLOOKUP),函数：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)Lookup_value 为需要在数据表第一行中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。使用对区域或区域名称的引用Row_index_num 为 table_array 中待返回的匹配值的行序号Range_lookup 为一逻辑值，指明函数 HLOOKUP 查找时是
19、精确匹配，还是近似匹配,31,29-查找和引用函数(VLOOKUP),函数：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value为需要在表格第一列中查找的数值。Lookup_value 可以为数值或引用Table_array为两列或多列数据。使用对区域或区域名称的引用Col_index_num为 table_array 中待返回的匹配值的列序号Range_lookup为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值,32,30-统计函数(AVERAGE),函数：AVERAGE(numb
20、er1,number2,.)number1必需。要计算平均值的第一个数字、单元格引用或单元格区域number2,.可选。要计算平均值的其他数字、单元格引用或单元格区域,33,31-统计函数(AVERAGEIF),函数：AVERAGEIF(range,criteria,average_range)range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。criteria是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、32、32、苹果 或 B4。average_range是要计算平均值的实际单元格集。如果忽略，则
21、使用 range,34,32-统计函数(AVERAGEIFS),函数AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)Average_range 是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。Criteria_range1,criteria_range2,是计算关联条件的 1 至 127 个区域。Criteria1,criteria2,是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求平均值。例如，条件可以表示为 32、32
22、、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-统计函数(COUNT),函数：COUNT(value1,value2,.)COUNTA(value1,value2,.)value1必需。要计算其中数字的个数的第一个项、单元格引用或区域。v
23、alue2,.可选。要计算其中数字的个数的其他项、单元格引用或区域，最多可包含 255 个注：若要计算逻辑值、文本值或错误值的个数，请使用 COUNTA 函数,39,37-统计函数(COUNTIF),函数：COUNTIF(range,criteria)range必需。要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。criteria必需。用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。例如，条件可以表示为 32、32、B4、苹果 或 32。,40,38-统计函数(COUNTIF),41,39-统计函数(COUNTIFS),函
24、数：COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2)criteria_range1必需。在其中计算关联条件的第一个区域。criteria1必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。例如，条件可以表示为 32、32、B4、苹果 或 32。criteria_range2,criteria2,.可选。附加的区域及其关联条件。最多允许 127 个区域/条件对,42,40-统计函数(COUNTIFS),43,41-统计函数(COUNTBLANK),函数：COUNTBLANK(range)Range 为需要计算其中空白单元格个数的区域,Thanks,
