Excel常用函数使用技巧Word文档格式.docx
《Excel常用函数使用技巧Word文档格式.docx》由会员分享,可在线阅读,更多相关《Excel常用函数使用技巧Word文档格式.docx(12页珍藏版)》请在冰豆网上搜索。
▲比较运算的结果为逻辑值TRUE或FALSE
(3)文本运算符:
&
(连接多个文本字符,产生一串新文本)
(4)引用运算符:
引用单元格,有相对引用(A5:
B7)、绝对引用($A$5:
$B$7)和混合引用($A5:
B$7)。
(5)逻辑运算符:
NOTANDOR
(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▓DATEDIF
▓YEAR/MONTH/DAY▓WEEKDAY
▓HOUR/MINUTE/SECOND
▓DATE
给出指定数值的日期。
=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(time)
▓LEN▓LEFT/RIGHT▓MID
▓REPLACE▓TEXT
▓VALUE▓TRIM
▓LEN
统计文本字符串中字符数目。
=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▓PRODUCT
▓SUMPRODUCT▓ROUNDUP▓SUMIF
▓SUMIFS▓CEILING▓FLOOR
▓ODD▓ABS
▓INT
将数值向下取整为最接近的整数。
=INT(number)
number表示需要取整的数值或包含数值的单元格。
number大于0时,不进行四舍五入;
number小于0时,进行四舍五入。
▓MOD
求出两数相除的余数。
=MOD(number,divisor)
number代表被除数,divisor代表除数
应用举例:
从XX中提取性别;
判断是否是闰年
—如果divisor参数为零,则显示错误值“#DIV/0!
”;
—值的符号与divisor(除数)的符号相同。
▓PRODUCT
求若干个数字的连乘积
=PRODUCT(number1,number2.....)
最多可以使用255个参数
▓SUMPRODUCT
计算多列中对应值相乘之后的和,即乘积之和。
=SUMPRODUCT(array1,array2,array3,…)或
=SUMPRODUCT((条件1)*(条件2)*…,加总范围)
—数组为2到30个,各个数组参数必须具有相同的维数,否则将返回错误值#VALUE!
。
—若数据区域中有错误值时,计算出现错误值#N/A。
—若数据区域中有非数值时,计算时视为0
▓ROUNDUP
对任意实数向上舍入。
=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)
▓ODD
将一个不是奇数的数值向上舍入为最接近的奇数。
=ODD(number)
number必须为数值型,若number非数值参数,函数ODD将返回错误值#VALUE!
▓ABS
求数值或数值表达式的绝对值。
=ABS(number/reference)
number/reference必须为数值型。
▓COUNTIF▓COUNTIFS▓COUNTBLANK
▓RANK▓FREQUENCY▓AVERAGEIF
▓MAX/MIN▓MODE▓MEDIAN
▓LARGE/SMALL
▓COUNTIF
对符合指定条件的单元格区域计数。
=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个。
返回在某一数据组中的第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”组合键进行确认。
▓COLUMN
返回一个引用的列号。
=COLUMN(reference)
Reference为需要得到其列号的单元格或单元格区域。
▓VLOOKUP
在数据表的首列查找指定的数值,并由此返回数据表当前行中指定列处的数值。
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value代表需要查找的数值;
Table_array代表需要在其中查找数据的单元格区域;
Col_index_num为在table_array区域中待返回的匹配值的列序号(当Col_index_num为2时,返回table_array第2列中的数值,为3时,返回第3列的值……);
Range_lookup为一逻辑值,如果为FALSE或0,表示精确查找;
如果为TRUE或1,表示模糊查找(返回小于lookup_value的最大数值);
如果找不到,则返回错误值#N/A。
—查找值(lookup_value)必须位于查找区域(table_array)的第一列;
—查找值与被查找值(在查找区域中)的数据类型必须一样,否则返回“#NA”。
▓ISERROR
用于测试函数式返回的数值是否有错。
如果有错,该函数返回TRUE,反之返回FALSE。
=ISERROR(value)
Value表示需要测试的值或表达式。
此函数通常与IF函数配套使用,比如公式:
=IF(ISERROR(A35/B35),"
"
A35/B35),如果B35为空或“0”,则相应的单元格显示为空,反之显示A35/B35。