Excel常用函数使用技巧.doc

Excel常用函数使用技巧目 录一、EXCEL函数概述二、逻辑函数三、日期时间函数四、文本函数五、数学函数六、统计函数七、检视与参照函数一、EXCEL函数概述1、函数结构：=函数名称(参数)说明：函数以公式形式表达，输入公式应加引导符号 = ;参数：可以是数字、文本、逻辑值(TRUE 或FALSE)、数组、错误值(#N/A)、单元格引用等;圆括号 “()”尽量在英文状态下输入。2、数据格式：通用型文本型（文字文本和数字文本）数值型百分比日期型时间型货币型科学计数3、运算符（1）算术运算符：+ - * / （2）比较运算符： = = = 比较运算的结果为逻辑值TRUE或FALSE（3）文本运算符：&（连接多个文本字符，产生一串新文本）（4）引用运算符： 引用单元格，有相对引用（A5:B7）、绝对引用（$A$5:$B$7）和混合引用（$A5:B$7）。（5）逻辑运算符：NOT AND OR （6）通配符（特殊）：星号“*”： 可代替任意数目的字符（一个或多个）。问号“?”： 可代替任何单个字符。仅对字符型数据进行操作的(包括：Match()、Vlookup()、Countif()、Sumif()等函数)，对数值型数据无效的 。4、函数嵌套函数嵌套是指根据需要，在一个函数的使用过程中调用其他函数。比如：=WEEKDAY(TODAY(),2); =IF(MAX(A3:A8),IF(),“ ”)5、公式运算结果出错信息（举例）：（1）#NUM!：数字不能被运算（2）#NAME!：引用了不能被识别的函数名或单元格（3）#VALUE!：使用了错误的数据类型（4）#!：单元格显示不下内容（宽度不够） 运算符和数字、文本等连接，可构成表达式(如：A5=30;left(A4,1)= “王”)二、逻辑函数IF主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。使用格式：=IF(Logical,Value_if_true,Value_if_false)参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。特别提醒：IF函数嵌套最多：excel07版64层；excel03版7层。OR主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。使用格式：=OR(logical1,logical2, .)参数说明：Logical1,Logical2,Logical3：表示待测试的条件值或表达式，最多这30个。特别提醒：AND和NOT函数的用法相似三、日期时间函数DATE TODAY/NOW DATEDIFYEAR/MONTH/DAY WEEKDAYHOUR/MINUTE/SECONDDATE主要功能：给出指定数值的日期。 使用格式：=DATE(year,month,day) 参数说明：year为指定的年份（小于9999）；month为指定的月份（可以大于12）；day为指定的天数（可以大于30）。TODAY/NOW主要功能：用于显示系统日期/时间。使用格式：=TODAY()；=NOW()参数说明：该函数不需要参数。特别提醒：显示出来的日期/时间格式，可以通过单元格格式进行重新设置。DATEDIF主要功能：计算返回两个日期参数的差值。使用格式：=DATEDIF(start_date,end_date,unit)参数说明： start_date表示开始日期，end_date代表结束日期，而且结束日期必须大于开始日期； unit（形式）：（Y 、M、D）要求返回两个日期相差的年、月、天数；YM：忽略年和日，计算两个日期的月份差；YD：忽略年，计算两个日期中的天数差；MD：忽略年和月，计算日差。特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。YEAR/MONTH/DAY主要功能：返回指定日期年份/月份/日。使用格式：=YEAR(date)WEEKDAY主要功能：给出指定日期的对应的星期数(星期几)使用格式：=WEEKDAY(serial_number,return_type)参数说明： serial_number代表指定的日期或引用含有日期的单元格； return_type代表星期的表示方式（参数）：参数是2：（星期一）为1，（星期日）为7（符合习惯）；参数是1：（星期日）为1，（星期六）为7；参数是3：（星期一）为0，（星期日）为6。特别提醒：如果是指定的日期，请放在英文状态下的双引号中，如=WEEKDAY(2003-12-18,2)。HOUR/MINUTE/SECOND主要功能：返回指定时间的时/分/秒。使用格式：= HOUR/MINUTE/SECOND (time)四、文本函数LEN LEFT/RIGHT MIDREPLACE TEXTVALUE TRIMLEN主要功能：统计文本字符串中字符数目。使用格式：= LEN (text)参数说明：LEN要统计时，无论中全角字符，还是半角字符，每个字符均计为“1”。LEFT/RIGHT主要功能：从一个文本字符串的最后一个字符/第一个字符，截取指定数目的字符。使用格式：= LEFT/RIGHT (text,num_chars)参数说明：text代表要截字符的字符串,num_chars代表给定的截取数目。MID主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：=MID (text,start_num,num_chars) 参数说明：text代表要截字符的字符串, start_num表示指定的起始位置，num_chars代表给定的截取数目。REPLACE主要功能：根据所指定的字符数，使用其他文本替换某字符串中的部分文本(连续一些)。使用格式：=REPLACE(old_text,start_num,num_chars,new_text)参数说明：Old_text是要替换其部分字符的文本或文本所在单元格。Start_num是要用 new_text 替换的 old_text 中字符的位置。Num_chars是希望 REPLACE 使用 new_text 替换 old_text 中字符的个数。New_text是要用于替换 old_text 中字符的文本。区别:=SUBSTITUE (text,old_text,new_text,instance_num)本函数:用新文本替换原字符串中指定位置的文本(或者)从某位置开始,用新文本替换原字符串中的指定文本(此时省略instance_num)TEXT主要功能：根据指定的数值格式将相应的数字转换为文本形式。使用格式：= TEXT(value,format_text)参数说明：value代表需要转换的数值或引用的单元格；format_text为指定文字形式的数字格式。特别提醒： format_text参数可以根据“单元格格式”对话框“数字”标签中的类型进行确定。VALUE主要功能：将一个代表数值的文本型字符串转换为数值型。使用格式：=VALUE(text)参数说明：text代表需要转换文本型字符串数值。特别提醒：如果文本型数值不经过上述转换，在用函数处理这些数值时，常常返回错误。TRIM主要功能：删除字符串中多余的空格，但会在英文字符串中保留一个作为词与词之间分隔的空格。使用格式：= TRIM(string)五、数学函数INT MOD PRODUCTSUMPRODUCT ROUNDUP SUMIFSUMIFS CEILING FLOORODD ABSINT主要功能：将数值向下取整为最接近的整数。使用格式：=INT(number)参数说明：number表示需要取整的数值或包含数值的单元格。特别提醒：number大于0时，不进行四舍五入；number小于0时，进行四舍五入。MOD主要功能：求出两数相除的余数。使用格式：=MOD(number,divisor)参数说明：number代表被除数，divisor代表除数应用举例:从身份证号码中提取性别;判断是否是闰年特别提醒： 如果divisor参数为零，则显示错误值“#DIV/0!”； 值的符号与divisor（除数）的符号相同。PRODUCT主要功能：求若干个数字的连乘积使用格式：= PRODUCT（number1,number2.）特别提醒：最多可以使用 255 个参数SUMPRODUCT主要功能：计算多列中对应值相乘之后的和，即乘积之和。使用格式：=SUMPRODUCT(array1,array2,array3, ) 或 =SUMPRODUCT(条件1)* (条件2)*，加总范围)参数说明： 数组为 2 到 30 个，各个数组参数必须具有相同的维数，否则将返回错误值 #VALUE!。 若数据区域中有错误值时，计算出现错误值#N/A。 若数据区域中有非数值时,计算时视为0ROUNDUP主要功能：对任意实数向上舍入。使用格式：=ROUNDUP(number,num_digits)参数说明：number表示需要舍入的实数，Num_digits 舍入后的数字的小数位数。特别提醒：如果 num_digits 大于 0，则向上舍入到指定的小数位；如果 num_digits 等于 0，则向上舍入到最接近的整数；如果 num_digits 小于 0，则在小数点左侧向上进行舍入。SUMIF主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：=SUMIF（Range,Criteria,Sum_Range）参数说明：Range代表条件判断所在的区域(参考区域)；Criteria为指定条件表达式(仅一个条件)；Sum_Range代表需要计算的数值区域。SUMIFS主要功能：根据多个指定条件对若干单元格求和使用格式：= SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)参数说明：sum_range表示实际求和区域, criteria_range1表示参考区域1, criteria1表示条件1。CEILING主要功能：返回大于等于数值的最小整数使用格式：= CEILING(number,significance)参数说明：number 必须为数值型，significance为该数值的倍数。FLOOR主要功能：返回小于等于数值的最大整数使用格式：= FLOOR(number,significance)参数说明：number 必须为数值型，significance为该数值的倍数。ODD主要功能：将一个不是奇数的数值向上舍入为最接近的奇数。使用格式：= ODD(number)参数说明：number 必须为数值型，若number非数值参数，函数 ODD 将返回错误值 #VALUE!。ABS主要功能：求数值或数值表达式的绝对值。使用格式：= ABS(number/reference)参数说明：number/reference必须为数值型。六、统计函数COUNTIF COUNTIFS COUNTBLANKRANK FREQUENCY AVERAGEIFMAX/MIN MODE MEDIANLARGE/SMALLCOUNTIF主要功能：对符合指定条件的单元格区域计数。使用格式：=COUNTIF（Range,Criteria）参数说明：Range表示需要计数的区域；Criteria为指定条件表达式(数字、表达式或文本)。COUNTIFS主要功能：根据多个指定条件对若干单元格计数使用格式：= COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, .)参数说明：criteria_range1表示参考区域1, criteria1表示条件1。用法与countif类似，但countif针对单一条件，而countifs可以实现多个条件同时求结果。COUNTBLANK主要功能：计算指定单元格区域中空白单元格个数使用格式：= COUNTBLANK(range)参数说明：空文本 ” ”也会计算在内应用举例：查看员工缺勤天数/次数RANK主要功能：求某一个数值在某一区域内的排名使用格式：= RANK(number,reference,order)参数说明： Number必须有一个数字值,reference必须是一个数组或单元格区域(数据值)； order:为 0 或不输入，结果按降序排列。为1，结果按升序排列。特别提醒：reference采用绝对引用符号（ “$”）。这样设置后，选中 “填充柄”向下拖拉，公式才不会随之变动。FREQUENCY主要功能：以一列垂直数组返回某个区域中数据的频率分布。使用格式：= FREQUENCY(data_array,bins_array)参数说明：Data_array表示用来计算频率的一组数据或单元格区域；Bins_array表示为前面数组进行分隔一列数值（分段标准）。特别提醒：输入结果时，先选定结果所在区域。输入完成后，需要通过按“Ctrl+Shift+Enter”组合键进行确认，确认后公式两端出现一对大括号（），此大括号不能直接输入。AVERAGEIF主要功能：返回某个区域内满足给定条件的的平均值（求非零平均值）。使用格式：= AVERAGEIF(criteria_range, criteria, average_range) 或= AVERAGEIF(average_range, criteria)参数说明： criteria_Range为必需，指条件所在或代表的区域，其中包含数字或包含数字的名称、数组或引用； Criteria是条件表达式，形式为数字、表达式、单元格引用或文本的条件； Average_range可选，计算平均值的实际单元格组。如果省略，则使用 range。MAX/MIN主要功能：求一组数的最大值/最小值使用格式：= MAX/MIN (number1, number2, number3) 参数说明：参数含文本或逻辑值，则忽略，且参数不超过30个。MODE主要功能：返回在某一数组或数据区域中出现频率最多的数值。使用格式：= MODE (number1, number2, number3) 参数说明：如果数组中不含有重复的数据，则返回错误值N/A。参数不超过30个。MEDIAN主要功能：返回在某一数组或数据区域的值。使用格式：= MEDIAN (number1, number2, number3) 参数说明：参数不超过255个。LARGE/SMALL主要功能：返回在某一数据组中的第K个最大值/最小值。使用格式：= LARGE/SMALL (array,k) 参数说明： array为需要找到第k个最大值/最小值的数组或数字型数据区域。 k为返回的数据在数组或数据区域里的位置(从大到小) /(从小到大)。七、检视与参照函数INDEX主要功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。使用格式：=INDEX(array,row_num,column_num)参数说明：Array代表单元格区域或数组常量；Row_num表示指定的行序号（如果省略row_num，则必须有 column_num）；Column_num表示指定的列序号（如果省略column_num，则必须有 row_num）。（row_num和column_num必须有一个）MACTH主要功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式：=MATCH(lookup_value,lookup_array,match_type)参数说明： Lookup_value代表需要在数据表中查找的数值； Lookup_array表示可能包含所要查找的数值的区域； Match_type表示查找方式（-1、0或1）：值为-1：查找大于等于 lookup_value的最小数值，Lookup_array 必须按降序排列；值为0：查找等于lookup_value 的第一个数值，Lookup_array 可以按任何顺序排列；值为1：查找小于等于lookup_value 的最大数值，Lookup_array 必须按升序排列；如果省略match_type，则默认为1。特别提醒：区域必须是某一行或某一列，即一维数据ROW主要功能：返回一个引用的行号。使用格式：=ROW(reference)参数说明：Reference为需要得到其行号的单元格或单元格区域。特别提醒：若Reference为一个区域, 输入结果时，先选定结果所在区域。输入完成后，需要通过按“Ctrl+Shift+Enter”组合键进行确认。COL