Excel函数整理.docx

Excel函数整理.docx

《Excel函数整理.docx》由会员分享，可在线阅读，更多相关《Excel函数整理.docx（11页珍藏版）》请在冰豆网上搜索。

Excel函数整理

4.8.3常用函数

Excel一共提供了数百个内部函数，限于篇幅，此处仅对一些最常用的函数作一简单介绍。

如有需要，可查阅Excel的联机帮助或其它参考资料，以了解更多函数和更详细的说明。

1、数学函数

（1）绝对值函数ABS

格式：

ABS（number）

功能：

返回参数number的绝对值。

例如：

ABS（-7）的返回值为7；ABS（7）的返回值为7。

（2）取整函数INT

格式：

INT（number）

功能：

取一个不大于参数number的最大整数。

例如：

INT（8.9），INT（-8.9）其结果分别是8,-9。

格式：

PI（）

功能：

返回圆周率π的值。

说明：

此函数无需参数，但函数名后的括号不能少。

（4）四舍五入函数ROUND

格式：

ROUND（number，n）

功能：

根据指定位数，将数字四舍五入。

说明：

其中n为整数，函数按指定n位数，将number进行四舍五入。

当n>0，数字将被四舍五入到所指定的小数位数；当n＝0，数字将被四舍五入成整数；当n<0，数字将被四舍五入到小数点左边的指定位数。

例如：

Round（21.45,1），Round（21.45,0）,Round（21.45,-1）其结果分别是21.5，21，20。

（5）求余函数MOD

格式：

MOD（number,divisor）

功能：

返回两数相除的余数。

结果的正负号与除数相同。

说明：

Number为被除数，Divisor为除数。

例如：

MOD（3,2）等于1，MOD（-3,2）等于1，MOD（3,-2）等于-1，MOD（-3,-2）等于-1。

（6）随机函数RAND

格式：

RAND（）

功能：

返回一个位于[0,1）区间内的随机数。

说明：

此函数无需参数，但函数名后的括号不能少。

产生[a,b]区间内的随机整数公式：

int（rand（）*（b-a+1））+a

（7）平方根函数SQRT

格式：

SQRT（number）

功能：

返回给定正数的平方根。

例如：

SQRT（9）等于3。

（8）求和函数SUM

格式：

SUM（number1，number2，…）

功能：

返回参数表中所有参数之和。

说明：

number1,number2,……是1-30个需要求和的参数。

若在参数中直接输入数值、逻辑值或文本型数字，则逻辑真和假值将转换为数值1和0，文本型数字将转换成对应的数值型数字参加运算。

若引用的单元格中出现空白单元格、逻辑值、文本型数字，则该参数将被忽略。

（9）条件求和函数SUMIF

格式：

SUMIF（range，criteria，sum_range）

功能：

根据指定条件对若干单元格求和。

说明：

range：

用于条件判断的单元格区域。

criteria：

进行累加的单元格应满足的条件，其形式可以为数字，表达式或文本。

如：

条件可以表示为5、"6"、"<60"、"教授"。

sum_range:

求和的实际单元格。

如果省略sum_range，则直接对range中的单元格求和。

例如：

A1:

A4单元区域中分别存放四个职工的月收入，分别为：

2000，2500，3000和5000；B1:

B4单元区域中的内容为：

教授、讲师、教授、副教授。

则SUMIF（B1:

B4,"教授",A1:

A4）的值为5000，表示求B1:

B4单元格中职称为教授人员对应于A1:

A4单元格中月收入的和。

2、统计函数

（1）求平均值函数AVERAGE

格式：

AVERAGE（number1，number2，…）

功能：

求参数的平均值。

说明：

最多可有30个参数，参数可以是数值、区域或区域名。

若引用参数中包含文字、逻辑值或空单元格，则将忽略这些参数。

例如：

a1:

a5区域中的数值分别为1，2，3，4，5，则average（a1:

a5）为3。

（2）COUNT函数

格式：

COUNT（value1，value2，…）

功能：

计算所列参数（最多30个）中数值型数据的个数。

说明：

函数计数时，会把直接作为参数输入的数字、文本型数字、空值、逻辑值、日期计算进去；但对于错误值或无法转化成数据的内容则被忽略。

如果参数是数组或引用，那么只统计数组或引用中的数字，数组或引用中的空白单元格、逻辑值、文本型数字也将被忽略。

这里的“空值”是指函数的参数中有一个“空参数”，和工作表单元格的“空白单元格”是不同的。

例如：

COUNT（0.1,FALSE,"5","three",4,6.66,70,,8,#div/0!

）中就有一个空值，计数时也计算在内，该函数的值为8；而COUNT（A1:

D4）是计算区域A1:

D4中非空白的数字单元格的个数，注意，空白单元格不计算在内。

（3）COUNTA函数

格式：

COUNTA（value1，value2，…）

功能：

计算所列参数（最多30个）中数据项的个数。

说明：

这里，“数据”是一个广义的概念，可以包含任何类型的数据。

但如果参数是单元格引用，则引用中的空白单元格将被忽略。

例如：

COUNTA（5，TRUE，，""，"ABC"）的计算结果为5。

（4）条件计数函数COUNTIF

格式：

COUNTIF（range,criteria）

功能：

计算给定区域内满足特定条件的单元格数目。

说明：

range：

希望计算的满足特定条件的非空单元格目的区域。

criteria：

需计数单元格应满足的条件，其形式可以为数字、表达式或文本。

例如：

设A1:

A4中的内容分别是“red”，“green”，“red”和“black”，则COUNTIF（A1:

A4,"red"）为2；若B1:

B4中的内容分别为25，35，40和60，则COUNTIF（B1:

B4,">=40"）为2。

例在工资表中统计职称是副教授的人数和40岁以上职工人数。

=countif（c3:

c22,”副教授”）

=countif（e3:

e22,”>=40”）

（5）最大值函数MAX

格式：

MAX（number1，number2，…）

功能：

求参数表（最多30个）中的最大值。

说明：

参数可以是数值、空白单元格、逻辑值或数字的文本表达式等。

错误值或不能转化为数值的文字作为参数时，会引起错误。

若参数中不含数字，则返回0。

例如：

MAX（78,"98",TRUE,,66）的计算结果为98。

（6）最小值函数MIN

格式：

MIN（number1，number2，…）

功能：

求参数表（最多30个）中的最小值。

说明：

参数说明与MAX相同。

3、文本函数

（1）LOWER函数

格式：

LOWER（text）

功能：

将一个字符串中的所有大写字母转换为小写字母。

说明：

text是要转换为小写形式的字符串。

函数LOWER不改变字符串中的非字母的字符。

例如：

LOWER（"Apt.2B"）等于"apt.2b"。

（2）UPPER函数

格式：

UPPER（text）

功能：

将一个字符串中的所有小写字母转换为大写字母。

说明：

text是要转换为大写形式的字符串。

函数UPPER不改变字符串中的非字母的字符。

例如：

UPPER（"total"）等于"TOTAL"。

（3）LEFT函数

格式：

LEFT（text,num_chars）

功能：

在字符串text中从左边第一个字符开始截取num_chars个字符。

说明：

参数num_chars为截取的字符串的长度，必须大于等于零。

如果num_chars大于text的总长度，则返回text全部内容。

如果省略num_chars，则视为1。

例如：

LEFT（"计算机应用基础",5）为“计算机应用”，LEFT（"abcd"）为“a”。

（4）RIGHT函数

格式：

RIGHT（text,num_chars）

功能：

在字符串text中从右边第一个字符开始截取num_chars个字符。

说明：

参数说明同LEFT函数。

例如：

RIGHT（"Merry，Chrismas"，8）为“Chrismas”，RIGHT（"abcd"）为“d”。

（5）MID函数

格式：

MID（text,start_num,num_chars）

功能：

从字符串text的第start_num个字符开始截取num_chars个字符。

说明：

start_num是截取字符串的起始位置。

如果start_num大于字符串的长度，则函数mid返回“”（空字符串）；如果start_num小于字符串的长度，但start_num与num_chars的和超过字符串长度，则函数mid返回从start_num到字符串结束的所有字符；如果start_num小于1，则函数Mid将返回错误值#VALUE!

例如：

MID（"pekinguniversity",1,6）为“peking”

（6）LEN函数

格式：

LEN（text）

功能：

返回字符串text中字符的个数。

例如：

len（"university"）为10。

4、日期与时间函数

（1）DATE函数

格式：

DATE（year,month,day）

功能：

返回指定日期的序列数，所谓序列数是从1900年1月1日到所输入日期之间的总天数。

说明：

year代表年份，是介于1900到9999之间的一个数字。

month代表月份，如果输入的月份大于12，将从指定年份的一月份开始往上加算。

day代表该月份中第几天，如果day大于该月份的最大天数，将从指定月份的第一天开始往上加算。

例如：

DATE（2008,5,1）为39569，返回代表2008年5月1日的序列数。

（2）YEAR函数

格式：

YEAR（serial_number）

功能：

返回于序列数serial_number相对应的年份数。

例如：

YEAR（39569）为2008。

（3）MONTH函数

格式：

MONTH（serial_number）

功能：

返回序列数serial_number相对应的月份数。

例如：

MONTH（39569）为5。

（4）DAY函数

格式：

DAY（serial_number）

功能：

返回序列数serial_number相对应的天数。

例如：

DAY（39569）为1。

（5）TODAY函数

格式：

TODAY（）

功能：

返回计算机系统内部时钟现在日期的序列数。

例如：

TODAY（）为39570，表示计算机系统当前日期是2008年5月2日。

（6）TIME函数

格式：

TIME（hour，minute，second）

功能：

返回指定时间的序列数。

说明：

该序列数是一个介于0到0.999999999之间的十进制小数，对应着自0:

00:

00（12:

00:

00AM）到23:

59:

59（11:

59:

59PM）的时间。

其中hour介于0到23，代表小时；minute介于0到59，代表分钟；second介于0到59，代表秒。

例如：

TIME（12,0,0）为0.5，对应12:

00:

00PM；TIME（17,58,10）为0.748726852，对应5:

58PM。

（7）NOW函数

格式：

NOW（）

功能：

返回计算机系统内部时钟的现在日期和时间的序列数。

说明：

该序列数是一个大于1的带小数的正数，其中整数部分代表当前日期，小数部分代表当前时间。

例如：

NOW（）为39523.486866667，表示2008年3月16日11:

52AM。

5、逻辑函数

（1）AND函数

格式：

AND（logical1，logical2，…，logical30）

功能：

当所有参数的逻辑值为真（TRUE）时返回TRUE；只要一个参数的逻辑值为假（FALSE）即返回FALSE。

说明：

logical1,logical2,...为待检测的若干个条件值（最多30个），各条件值必须是逻辑值（TRUE或FALSE）、计算结果为逻辑值的表达式、或者是包含逻辑值的单元格引用。

如果引用的参数包含文字或空单元格，则忽略其值；如果指定的单元格区域内包含非逻辑值，则返回错误值#VALUE。

例如：

AND（TRUE,7>2）为TRUE；AND（9-5=4,5>8）为FALSE。

（2）OR函数

格式：

OR（logical1,logical2,…，logical30）

功能：

在参数中，任何一个参数逻辑值为真，即返回逻辑值TRUE；只有全部参数为假，才返回FALSE。

说明：

与AND函数相同。

例如：

OR（TRUE,3+5=8）为TRUE；OR（1+1=3,3+2=6）为FALSE。

（3）NOT函数

格式：

NOT（logical）

功能：

对逻辑参数logical求相反的值。

例如：

NOT（FALSE）为TRUE；NOT（1+4=5）为FALSE。

（4）IF函数

格式：

IF（logical_test,value_if_true,value_if_false）

功能：

对条件式logical_test进行测试，如果条件为逻辑值TRUE，则取value_if_true的值，否则取value_if_false的值。

说明：

函数IF最多可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

●在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。

例如：

在如图4-51所示的商品销售业绩表中，要求用IF函数完成对每位人员的业绩评价。

评价的标准是：

全年销售额>=2000000为“优秀”，1500000<=全年销售额<2000000为“良好”，1000000<=全年销售额<1500000为“一般”，全年销售额<1000000为“较差”。

其操作步骤如下：

在J3单元格输入函数：

＝IF（J3>=2000000,"优秀"，IF（J3>=1500000,"良好",IF（J3>=1000000,"一般","较差"）））

将J3中的公式复制到J4:

J17。

6、数据库统计函数

数据库统计函数的格式为：

函数名（database，field，criteria）。

其中database是包含字段的数据库区域；field指定函数所要统计的数据列，可以是带引号的字段名，如“级别”，也可以是是字段名所在单元格地址，还可以是代表数据库中数据列位置的序号，1表示第一列，2表示第二列等；criteria为一组包含给定条件的单元格区域，即条件区域。

条件区域的写法同高级筛选。

常用的数据库统计函数有：

DAVERAGE（database,field,criteria）：

对数据库中满足条件记录的指定字段求平均值。

DSUM（database,field,criteria）：

对数据库中满足条件记录的指定字段求和。

DMAX（database,field,criteria）：

对数据库中满足条件记录的指定字段求最大值。

DMIN（database,field,criteria）：

对数据库中满足条件记录的指定字段求最小值。

DCOUNT（database,field,criteria）：

计算指定数据库中符合条件且包含有数字的单元格数。

DCOUNTA（database,field,criteria）：

返回数据库中满足给定条件的非空单元格数目。

例如，在商品销售业绩表（见图4-54）中，计算男性中级销售人员的人数以及他们业绩奖金的平均值。

操作步骤如下：

（1）在空白区域E19:

F20建立条件，方法同“高级筛选”条件区域的建立。

（2）在E21、E22单元分别输入各统计函数：

E21：

＝DCOUNT（A2:

J17，，E19:

F20）

E22：

＝DAVERAGE（A2:

J17,8,E19:

F20）

统计结果如图4-65所示。

8、其他函数

（1）频率分析函数FREQUENCY

格式：

FREQUENCY（range1,range2）

功能：

将区域range1中的数据按垂直区域range2（分段点）进行频率分布的统计，统计结果放在range2右边列的对应位置。

说明：

输入公式前要选定显示结果的区域，返回数组中的元素个数比range2中的元素数目多一个，输入公式完毕要按“Ctrl+Shift+Enter”组合键，不能按Enter键。

例如：

若工作表中已输入某班学生成绩，如图4-52所示。

统计成绩在0~59、60~69、70~79、80~89和90~100各区间中的人数。

操作步骤如下：

在区域C2:

C5中输入分段点的分数59、69、79、89。

选定显示结果的区域D2:

D6。

用鼠标单击编辑栏，在编辑栏中输入公式“=FREQUENCY（A1:

A10,C2:

C5）”。

按“Ctrl+Shift+Enter”组合键，结果如图4-53所示。

（2）排名函数RANK

格式：

RANK（number,range,rank-way）

功能：

返回单元格number在一个垂直区域range中的排位名次，rank-way是排位的方式。

rank-way为0或省略，则按降序排列（值最大的为第1名）。

Rank-way不为0则按升序排列（值最小的为第1名）。

说明：

RANK函数对相同数的排位相同。

但相同数的存在将影响后续数值的排位。

例如：

A1:

A5区域中含有数字5、7、3、9、3，则RANK（A2,A1:

A5）=2；RANK（A2,A1:

A5,1）=4。