二级Excel函数常用函数
常用函数
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。
(3) 圆周率函数PI
格式: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
1
(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个)中数值型数据的个数。
说明:函数计数时,会把直接作为参数输入的数字、文本型数字、空值、逻辑值、
日期计算进去;但对于错误值或无法转化成数据的内容则被忽略。如果参数是数组或引用,那么只统计数组或引用中的数字,数组或引用中的空白单元格、逻辑值、文本型数字也将被忽略。这里的“空值”是指函数的参 2
数中有一个“空参数”,和工作表单元格的“空白单元格”是不同的。 例如: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)
功能:将一个字符串中的所有大写字母转换为小写字母。
3
说明: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("peking university",1,6)为“peking”
(6) LEN函数
格式:LEN(text)
功能:返回字符串text中字符的个数。
例如:len("university")为10。
4、日期与时间函数
(1) DATE函数
4
格式: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:00 AM)到23:59:59(11:59:59 PM)的时间。其中hour介于0到23,代表小时;minute介于0到59,代表分钟;second介于0
到59,代表秒。
例如:TIME(12,0,0)为0.5,对应12:00:00 PM;TIME(17,58,10)为0.748726852,对应5:58PM。
(7) NOW函数
格式:NOW()
功能:返回计算机系统内部时钟的现在日期和时间的序列数。
说明:该序列数是一个大于1的带小数的正数,其中整数部分代表当前日期,小
数部分代表当前时间。
5
例如: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函数进行等级分类
例如:在如图4-51所示的商品销售业绩表中,要求用IF函数完成对每位人员的 6
业绩评价。评价的
标准
excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载
是:全年销售额>=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、其他函数
7
图4-65 数据库统计函数结果
(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。
图4-52 学生成绩
图4-53使用频度函数 ? 选定显示结果的区域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。
8