EXCEL常用函数29种+实例.docx
《EXCEL常用函数29种+实例.docx》由会员分享,可在线阅读,更多相关《EXCEL常用函数29种+实例.docx(17页珍藏版)》请在冰豆网上搜索。
EXCEL常用函数29种+实例
1.SUM
求参数的和,就是求指定的所有参数的和。
2.SUMIF
1.条件求和,Excel中sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。
2.sumif函数语法是:
SUMIF(range,criteria,sum_range)
sumif函数的参数如下:
第一个参数:
Range为条件区域,用于条件判断的单元格区域。
第二个参数:
Criteria是求和条件,由数字、逻辑表达式等组成的判定条件。
第三个参数:
Sum_range为实际求和区域,需要求和的单元格、区域或引用。
当省略第三个参数时,则条件区域就是实际求和区域。
(注:
criteria参数中使用通配符(包括问号(?
)和星号(*))。
问号匹配任意单个字符;星号匹配任意一串字符。
如果要查找实际的问号或星号,请在该字符前键入波形符(~)。
)
3.实例:
计算人员甲的营业额($K$3:
$K$26:
绝对区域,按F4可设定)
3.COUNT
1.用途:
它可以统计数组或单元格区域中含有数字的单元格个数。
2.函数语法:
COUNT(value1,value2,...)。
参数:
value1,value2,...是包含或引用各种类型数据的参数(1~30个),其中只有数字类型的数据才能被统计。
3.实例:
如果A1=90、A2=人数、A3=〞〞、A4=54、A5=36,则公式“=COUNT(A1:
A5)”返回得3。
4.COUNTA
1.说明:
返回参数组中非空值的数目。
参数可以是任何类型,它们包括空格但不包括空白单元格。
如果不需要统计逻辑值、文字或错误值,则应该使用COUNT函数。
2.语法:
COUNTA(value1,value2,...)
3.实例:
如果A1=6.28、A2=3.74,A3=“我们”其余单元格为空,则公式
“=COUNTA(A1:
A5)”的计算结果等于3。
5.COUNTBLANK
1.用途:
计算某个单元格区域中空白单元格的数目。
2.函数语法:
COUNTBLANK(range)
参数:
Range为需要计算其中空白单元格数目的区域。
3.实例:
如果A1=88、A2=55、A3=(空格)、A4=72、A5=(空格),则公式
=COUNTBLANK(A1:
A5”)返回得2
6.COUNTIF
用途:
计算区域中满足给定条件的单元格的个数。
语法:
COUNTIF(range,criteria)
参数:
Range:
需要计算其中满足条件的单元格数目的单元格区域。
Criteria:
确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。
实例:
在A1:
A7之间,确定数字55被计算在内的条件,返回得2。
7.MAX
返回选定数据库项中的最大值
8.MIN
返回选定数据库项中的最小值
9.IF
1)IF函数的语法结构:
IF(条件,结果1,结果2)。
2)IF函数的功能
对满足条件的数据进行处理,条件满足则输出结果1,不满足则输出结果2可以省略结果1或结果2,但不能同时省略。
3)条件表达式
把两个表达式用关系运算符(主要有=,<>,>,<,>=,<=等6个关系运算符)连接起来就构成条件表达式。
例如,在IF(a1+b1+50<>b1+c1-50,1,2)函数式中,条件表达式是a1+b1+50<>b1+c1-50。
4)执行过程
面以IF(a1+b1+50<>b1+c1-50,1,1)函数式为例来说明IF函数的执行过程
例2:
IF(E2>=85,“优”,IF(E2>=75,“IF良(E”2>,=60,"及格","不及格")))公式理解:
函数从左向右执行。
首先计算E2>=85,如果该表达式成立,则显示“优”,
如果不成立就继续计算E2>=75,如果该表达式成立,则显示“良”,否则继续计算E2>=60,如果该表达式成立,则显示“及格”,否则显示“不及格”例3:
IF(HOUR(F9)<1,1,IF(MINUTE(F9)>15,HOUR(F9)+1,HOUR(F9)))公式理解:
函数从左向右执行。
如果F9中的数字折算成小时后的值<1小时,成立输出“1”不,成立输出“如果F9中的数字折算成分钟后的值>15”,成立输出“中F的9数字折算成小时后的值+1”不成立输出“F中9的数字折算成小时后的值”。
注:
时间函数HOUR(X),MINUTE(X)可使列表数字折算成对应值。
例:
=HOUR("0:
58:
33")→0
10.AND
1.说明:
所有参数的计算结果为TRUE时,返回TRUE;只要有一个参数的计算结果为FALSE,即返回FALSE。
2.函数语法:
AND(logical1,[logical2],...)
logical1:
必需。
要检验的第一个条件,其计算结果可以为TRUE或FALSE
logical2,...:
可选。
要检验的其他条件,其计算结果可以为TRUE或FALSE,最多可包含255个条件。
3.实例:
=AND(2+2=4,2+3=5)所有参数的计算结果均为TRUE
=AND(2+2=4,2+3=6)所有参数的计算结果均为FALSE
11.HLOOKUP
说明:
HLOOKUP函数主要用于在表格或数值组的首行查找指定的数值(即在水平查
函数语法:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
实例:
用下面这个实例展示Hlookup函数用法
A1:
B2表示要在其中查找的区间,公式中的table_array
F1到G1中表示我们要查找的对象,公式中的lookup_value
现在我们要查找F1到G1分别对应的数值。
(按F4键,将其变成绝对引用。
)
12.VLOOKUP
说明:
VLOOKUP函数是Excel中的一个纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数,在工作中都有广泛应用。
功能是按列查找,最终返回该列所需查询列序所对应的值;与之对应的HLOOKUP是按行查找的。
函数语法:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
13.RANK
说明:
rank函数是排名函数。
rank函数最常用的是求某一个数值在某一区域内的排名。
函数语法:
rank(number,ref,[order])实例:
给下列成绩排名
14.REPLACE
1.说明:
用新字符串替换旧字符串,而且替换的位置和数量都是指定的
2.函数语法:
=Replace(old_text,start_num,num_chars,new_text)
=replace(要替换的字符串,开始位置,替换个数,新的文本)
注意:
第四个参数是文本,要加上引号。
3.
xxxx
实例:
=REPLACE(替换区域A2,第八个数字开始,替换4个,替换为
15.时间函数
1.TODAY:
用于返回当前系统的日期,其完整的格式:
=TODAY()
2.NOW:
用于返回当前系统的时间,其完整的格式:
=DAY()
3.HOUR:
将值转成小时
4.MINUTE:
将值转成分钟
5.DATE函数:
返回指定日期的序列号
函数语法:
DATE(year,month,day)
16.DAVERAGE
1.说明:
返回数据库或数据清单中满足指定条件的列中数值的平均值
2.函数语法:
DAVERAGE(database,field,criteria)
参数:
Database构成数据表或数据库的单元格区域。
Field指定函数所使用的数据列。
Criteria为一组包含给定条件的单元格区域。
3.实例:
实例1:
利用daverage函数单条件求平均值,求电视的平均数量。
实例2:
求B分店电视的平均数量
输入公式=DAVERAGE(A1:
C9,C1,E1:
E2)
数据区域——A1:
C9(整个查找表)
字段名——C1,指定求平均值的数据列。
也可以等于3(参数Field=C1等价于参数Field=3。
即表示返回第三列的值。
)条件区域——E1:
E2,表示品名为电视。
17.DSUM
1.说明:
将数据库中符合条件的记录的字段列中的数字的和。
使用它可以对数据进行多条件累加,这种方式可以使条件的修改变得方便,因此,相对于SUM和SUMIF函数,DSUM更加灵活。
2.函数语法:
DSUM(database,field,criteria)
参数:
Database构成列表或数据库的单元格区域。
Field指定函数所使用的数据列。
列表中的数据列必须在第一行具有标志项。
Field可以是文本,即两端带引号的标志项,如“树龄”或“产量”;此外,Field也可以是代表列表中数据列位置的数字:
1表示第一列,2表示第二列,等等
Criteria为一组包含给定条件的单元格区域。
可以为参数criteria指定任意区域,只要它至少包含一个列标志和列标志下方用于设定条件的单元格。
实例:
如图所示,利用dsum函数单条件求和,
实例1:
求电视的总数量。
(实例2:
求B分店电视的总数量。
)
输入公式=DSUM(A1:
C9,F1,D1:
D2)
数据区域——A1:
C9(整个查找表)
字段名——C1,指定求和的数据列。
也可以等于3。
条件区域——A11:
A12,表示品名为电视。
18.ROUND
1.说明:
round函数用来对数值进行四舍五入。
2.函数语法:
ROUND(number,num_digits)
参数,
number表示需要进行四舍五入的数值或单元格内容。
num_digits表示需要取多少位的参数。
num_digits>0时,表示取小数点后对应位数的四舍五入数值。
num_digits=0时,表示则将数字四舍五入到最接近的整数。
num_digits<0时,表示对小数点左侧前几位进行四舍五入
19.MOD
1.说明:
mod函数是一个用来求余数函数,返回两数相除的余数。
mod函数在Excel中一般不单独使用,经常和其他函数组合
起来使用。
2.函数语法:
=mod(number,divisor)=mod(被除数,除数)。
20.OR
1.说明:
OR函数可以用来对多个逻辑条件进行判断,只要有1个逻辑条件满足时就返回”TURE“,最多可以有30个条件。
与AND函数不同,AND函数要求所有的条件都满足时才返回”TURE“,只要有条件不满足时就返回”FALSE“
2.函数语法:
=OR(logical1,logical2,...)
3.实例:
例题:
判断当前年份是否为闰年,结果为TRUE或FALSE。
闰年:
年数能被4整除而不能被100整除,或者能被400整除的年份。
当前年份:
YEAR(TODAY())
年数能被4整除:
MOD(YEAR(TODAY()),4)=0
不能被100整除:
MOD(YEAR(TODAY()),100)>0
能被400整除的年份:
MOD(YEAR(TODAY()),400)=0
AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)>0):
满足当前“年数能被4整除而不能被100整除,”得TURE,不满足得FALSE。
OR(AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)>0),
MOD(YEAR(TODAY()),400)):
当前年数能被4整除而不能被100整除,或者能被400整除的年份。
21.SUMPRODUCT
1.说明:
在给定的几组数组中,然后把数组间对应的元素相乘,最后返回乘积之
和。
从字面上可以看出,sumproduct有两个英文单词组成,sum是和,product
是积,所以是乘积之和的意思。
(返回相应的数组或区域乘积的和)
2.函数语法:
=SUMPRODUCT(array1,array2,array3,...)Array为数组。
3.实例:
当sumproduct函数中的参数只有一个数组时,即对数组{1;2;3;4;5;6;7}进行求和,1+2+3+4+5+6+7=28。
当sumproduct函数中参数为两个数组时,两个数组的所有元素对应相乘。
公式
=sumproduct(A2:
A8,B2:
B8)可转化为=sumproduct(数组1,数组2)
=sumproduct({1;2;3;4;5;6;7},{1;2;3;4;5;6;7})
=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。
SUMPRRODUCT用法
多条件统计个数函数语法:
=sumproduct((条件1")*(条件2)*(条件n))
例题:
统计A1-A10中奇数的个数(注:
mod函数是一个用来求余数函数)
22.DCOUNT
1.说明:
统计满足指定条件并且包含数字的单元格的个数
2.函数语法:
=DCOUNT(database,field,criteria)=(数据区域,字段名,条件区域)
参数field——字段名(函数所使用的列)Field也可以是数字:
1代表第一列,2代表第二列。
参数criteria——条件区域,可以是一个或者多个条件。
1.实例:
统计产品为白炽灯,其瓦数大于等于80且小于等于100的品种数
23.ISTEXT
1.说明:
ISTEXT函数用于判断值是否为文本,如果是,则返回TRUE;否则返
回FALSE。
2.
3.实例:
24.ABS
1.说明:
函数语法:
ISTEXT(value)参数说明:
value:
表示需要判断是否为文本的值。
计算绝对值。
2.函数语法:
ABS()
3.实例:
25.MID
1.说明:
可以用来从指定的字符串中截取出指定数量字符的函数
2.函数语法:
MID(text,start_num,num_chars)
参数:
text是一串我们想从中截取字符的“字符串“;start_num是一个数字,是指从”字符串“的左边第几位开始截取;num_chars也是数字,是指从start_num开始,向右截取的长度。
3.实例:
判断该身份证号码男还是女?
(倒数第二位为奇数为的男,为偶数的为女)
26.MROUND1.说明:
MROUND函数返回最接近要舍入值的基数的倍数。
2.函数语法:
MROUND(,)
参数:
numbe要舍入的数字。
multiple要将数字舍入到的基数
3.实例:
将B1中的时间四舍五入到最接近15分钟的倍数。
27.RANK.EQ1.说明:
用于返回一个数字在数字列表中的排位,如果多个值都具有相同的排位,则返回该组数值的最高排位。
2.函数语法:
RANK.EQ(number,ref,order)
参数:
number:
表示要查找排名的数字。
ref:
表示要在其中查找排名的数字列表。
order:
表示指定排名方式的数字。
3.实例:
给下列人员的销售额排序
注意事项:
RANK.EQ函数对相同的数值的排位相同。
但是,相同数值的存在会影响后续数值的排位
28.COUNTIFS
1.说明:
统计符合多个个条件的单元格个数的函数
2.函数语法:
countifs(criteria_range1,criteria1,criteria_range2,criteria2,⋯)
参数:
criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域(简称条件区域)。
criteria1为第一个区域中将被计算在内的条件(简称条件),其形式可以为数字、表达式或文本。
同理,criteria_range2为第二个条件区域,criteria2为第二个条件,依次类推。
最终结果为多个区域中满足所有条件的单元格个数。
3.实例:
计算某列表书本订购数量大于110,且小于850的书种类输:
29.AVERAGE
1.说明:
计算平均值函数,在数据库中average使用简写avg
2.函数语法:
VERAGE(Number1,Number2⋯⋯)
参数:
Number1,number2,...是要计算平均值的1~30个参数。