EXCEL常用函数29种 实例.docx

EXCEL常用函数29种 实例.docx

《EXCEL常用函数29种 实例.docx》由会员分享，可在线阅读，更多相关《EXCEL常用函数29种 实例.docx（19页珍藏版）》请在冰点文库上搜索。

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（E2>=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”,成立输出“F9中的数字折算成小时后的值+1”，不成立输出“F9中的数字折算成小时后的值”。

注：

时间函数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.实例：

=REPLACE（替换区域A2，第八个数字开始，替换4个，替换为“xxxx”

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.函数语法：

ISTEXT（value）参数说明：

value：

表示需要判断是否为文本的值。

3.实例：

24.ABS

1.说明：

计算绝对值。

2.函数语法：

ABS（）

3.实例：

25.MID

1.说明：

可以用来从指定的字符串中截取出指定数量字符的函数。

2.函数语法：

MID（text,start_num,num_chars）

参数：

text是一串我们想从中截取字符的“字符串“；

start_num是一个数字，是指从”字符串“的左边第几位开始截取；

num_chars也是数字，是指从start_num开始，向右截取的长度。

3.实例：

判断该身份证号码男还是女?

（倒数第二位为奇数为的男，为偶数的为女）

26.MROUND

1.说明：

MROUND函数返回最接近要舍入值的基数的倍数。

2.函数语法：

MROUND（,）

参数：

numbe要舍入的数字。

multiple要将数字舍入到的基数。

3.实例：

将B1中的时间四舍五入到最接近15分钟的倍数。

27.RANK.EQ

1.说明：

用于返回一个数字在数字列表中的排位，如果多个值都具有相同的排位，则返回该组数值的最高排位。

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个参数。