excel常用函数大全及使用实例解析.docx
《excel常用函数大全及使用实例解析.docx》由会员分享,可在线阅读,更多相关《excel常用函数大全及使用实例解析.docx(7页珍藏版)》请在冰豆网上搜索。
excel常用函数大全及使用实例解析
Excel常用函数大全及使用实例解析
(一)信息函数(5条)
1.Info:
用途:
返回当前操作系统信息。
语法:
info(type-text)。
参数:
“directory”—当前目录或文件夹路径。
“numfile”—当前打开所有工作表数目。
“osversion”—返回当前操作系统版本号。
“recale”—当前重计算方式【自动或人工】。
“release”—返回Excel版本号。
“system”—返回当前操作系统名称。
(macintosh=“mac”,Windows=“pcdos”)。
举例:
“info(“osversion”)”返回“windows(32-bit)”
2.Iseven:
用途:
测试参数奇偶性,参数为偶数时返回“true”,为奇数时返回“false”。
语法:
iseven(number)。
【如果数值为小数,则自动截去小数部分,计算整数】。
参数:
如果参数“number”不是数值,则函数返回错误值“#value!
”举例:
Ⅰ.A1=11,“=iseven(A1)”返回“false”Ⅱ.A2=12,“=iseven(A2)”返回“true”。
3.Isodd:
用途:
测试参数奇偶性,参数为偶数时返回“false”,为奇数时返回“true”。
语法:
isodd(number)。
【如果数值为小数,则自动截去小数部分,计算整数】。
参数:
如果参数“number”不是数值,则函数返回错误值“#value!
”
举例:
“Ⅰ.A1=19,=isodd(A1)”返回“true”,Ⅱ.A2=,“=isodd(A2)”返回“false”。
4.N:
用途:
返回转化为数值后的值。
语法:
N(value)。
参数:
“value”为要转化的值。
“value”为数值时返回该数值,为日期时返回该日期序列号,为“true”时返回“1”,为“false”时返回“0”,为错误值【如#value!
】时返回该错误值,其他值均返回“0”。
举例:
Ⅰ.A1=5,“N(A1)”返回“5”,Ⅱ.A2=“人民”,“N(A2)”返回“0”,Ⅲ.A3=“true”,“N(A3)”返回“1”。
5.Type:
用途:
返回数值类型。
语法:
type(value)。
参数“value”可以是数字,文本,逻辑值等。
如果“value”=“number”返回“1”,“value”=“text”返回“2”,“value”=“逻辑值”返回“4”,“value”=“错误值”返回“16”,“value”=“错误值”返回“64”。
举例:
Ⅰ.A1=5,“=type(A1)”返回“1”,Ⅱ.A2=“我们”,“=type(A2)”返回“2”。
(二)逻辑运算符(2条)
1.If:
用途:
执行逻辑判断,可以根据表达式的真假,返回不同的结果,执行数值或公式的条件检测。
语法:
if(logical-text,“value-if-true”,“value-if-false”)。
参数:
“value-if-true”是“logical-text”为true时的函数返回值,同样,“value-if-false”是“logical-text”为false时的函数返回值。
举例:
Ⅰ.A1=5,“=if(A1=5,“true”,“false”)”返回“true”。
Ⅱ.【嵌套语句】A2=67,“=if(A2>=85,“A”,if(A2>=70,“B”,if(A2>=60,“C”,if(A2<60,“D”))))”返回“C”。
【嵌套语句中第二个if语句是第一个的“value-if-false”参数,同样,第三个if语句是第二个的“value-if-false”参数。
即如果表达式A2>=85成立,则返回“A”,如果表达式A2>=85不成立,则计算第二个if语句,依此类推直至满足条件显示返回值】
2.Or:
用途:
所有参数中任意一个逻辑值为真时即返回“true”。
语法:
or(logical1,logical2,……)。
参数:
logical1,logical2,…是需要进行检验的一至三十个逻辑表达式,其结论分别是“true”或“false”。
如果数组或引用的参数包含数值、文本或空白单元格,它们将被忽略。
如果指定区域内无逻辑值,or函数将返回错误值“#value!
”。
举例:
Ⅰ.A1=6,A2=8,“=or(A1+A2>A2,A1=A2)”返回“true”,Ⅱ.“=or(A1>A2,A1=A2)”返回“false”。
3.And:
用途:
所有参数中所有逻辑值均为真时返回“true”。
语法:
and(logical1,logical2,……)。
参数:
logical1,logical2,…是需要进行检验的一至三十个逻辑表达式,其结论分别是“true”或“false”。
如果数组或引用的参数包含数值、文本或空白单元格,它们将被忽略。
如果指定区域内无逻辑值,and函数将返回错误值“#value!
”。
举例:
Ⅰ.A1=7,A2=10,“=and(A1+A2=17,A2-A1=3)”返回“true”,Ⅱ.“=and(A1+A2=17,A1=A2)”返回“false”。
(三)时间函数(2条)
1.Now:
用途:
返回当前系统时间与日期。
语法:
now()。
参数:
在单元格中输入函数“now()”,返回系统当前日期。
举例:
Ⅰ.“=now()”,返回“2012/12/220:
01”。
2.Networkdays:
用途:
返回参数“start-date”和“end-date”之间的完整工作日【不包括周末】的数值。
语法:
networkdays(“start-date”,“end-date”,holidays)。
参数:
“start-date”表示开始日期,“end-date”表示终止日期,holidays表示其他节假日数值。
举例:
Ⅰ.“=networkdays(“2011/1/1”,“2012/1/1”,“2011/2/15”)”返回“259”。
(四)查找、引用函数(2条)
1.Hyperlink:
用途:
创建一个快捷方式,打开网址或超链接到硬盘上的某个文件。
语法:
hyperlink(“link-location”,“friendly-name”)。
参数:
“link-location”是文件路径及完整文件名【加后缀,如“文本文档.txt”】或完整网址,它还可以指向某文档中更具体的位置,如excel工作表或工作簿种特定的单元格或命令区域,或是word中的书签。
“friendly-name”是超链接的名称,如果省略则名称用“link-location”的文本值表示。
举例:
Ⅰ.“=hyperlink(“D:
\”,“说明文件”)”,回车后单击链接显示“”文本内容,Ⅱ.“=hyperlink(“,“XX”)”,回车后单击链接显示“XX”网页。
2.Lookup:
用途:
返回向量【单行或单列】或数组中的数值。
该函数有两种语法形式:
向量形式和数组形式。
⑴向量形式:
在向量中查找数值,然后返回第二个向量【单行或单列】中相同位置的数值。
⑵数组形式:
在第一行或第一列查找指定数值,然后返回数组最后一行或最后一列的相同位置的数值。
语法:
⑴向量形式:
lookup(lookup-value,lookup-vector,result-vector);⑵数组形式:
lookup(lookup-value,array)。
参数:
⑴向量形式:
“lookup-value”为函数“lookup”在第一个向量中所要查找的数值,“lookup-value”可以是数值、文本、逻辑值或包含数值的名称、引用。
“lookup-vector”为包含第一个向量的行列【单行或单列】。
“result-vector”为与第一个向量对应的行列【单行或单列】。
⑵数组形式:
“lookup-value”为函数“lookup”在数组中所要查找的数值,“lookup-value”可以是数值、文本、逻辑值或包含数值的名称、引用。
如果函数“lookup”找不到“lookup-value”,则使用数组中小于或等于“lookup-value”的最大数值。
“array”为包含数字、文本、逻辑值的单元格区域,它的值用于和“lookup-value”比较。
【向量形式中“lookup-vector”的行或列的数值必须按升序排列】
举例:
Ⅰ.向量形式:
AB公式说明(结果)
1.频率颜色“=lookup(,A2:
A5,B2:
B5)”在A列中查找“”并于B列对应
2.Red“=lookup(,A2:
A5,B2:
B5)”在A列中查找“”并返回“Orange”
3.Orange“=lookup(,A2:
A5,B2:
B5)”在A列中查找“,”,返回次小值
4.Yellow“”并显示B列“Green”
5.Green“=lookup(,A2:
A5,B2:
B5)”在A列中查找“”,返回错误值
“#N/A”,【“”<“”】
Ⅱ.数组形式:
AB公式说明(结果)
1.a1“=lookup(“bump”,A1:
B4)”在数组第一行中查找“bump”,并返回
2.b2同一行最后一列值“2”
3.c3“lookup(“a”,A1:
B4)+lookup(“d”,A1:
B4)”
4.d4在数组第一行中查找“a”,其返回值与“d”得返回值相加,最终返回“5”。
(五)数学和三角函数(24条)
1.Abs:
用途:
返回某一参数的绝对值。
语法:
abs(number)。
参数:
“number”为任何需求其绝对值的实数。
举例:
Ⅰ.A1=-5,“=abs(A1)”返回“5”。
2.Countif:
用途:
统计某一区域中符合条件的单元格数目。
语法:
countif(range,criteria)。
参数:
“range”为须统计的符合条件的单元格数目所在区域。
“criteria”为参与计算的单元格条件,可以为数字、表达式或文本。
表达式、文本必须加引号。
举例:
Ⅰ.A1:
A5区域内的文本分别为“女”、“男”、“女”、“男”、“女”。
“=countif(A1:
A5,“男”)”返回“2”。
3.Exp:
用途:
返回自然对数e的n次幂。
语法:
exp(number)。
参数:
“number”为底数e的指数。
【exp函数是计算自然对数的ln函数的反函数】
举例:
Ⅰ.A1=3,“=exp(A1)”返回“”【e3】
4.Fact:
用途:
返回一个数值的阶乘。
【阶乘:
数学表达式为1*2*3*…*n】语法:
fact(number)。
参数:
“number”值必须大于等于0,如果“number”不为整数,则计算其整数部分【不四舍五入】。
举例:
Ⅰ.“=fact(3)”返回“6”,Ⅱ.“=fact()”返回“120”。
5.Gcd:
用途:
返回数值的最大公因【约】数。
语法:
gcd(number1,number2,……)。
参数:
如果“number”不为整数,则计算其整数部分【不四舍五入】。
举例:
Ⅰ.A1=16,A2=28,A3=46,“=gcd()A1:
A3”返回“2”。
6.Lcm:
用途:
返回数值的最小公倍数。
语法:
lcm(number1,number2,……)。
参数:
如果“number”不为整数,则计算其整数部分【不四舍五入】。
举例:
Ⅰ.A1=4,A2=16,A3=8,“=lcm(A1:
A3)”返回“16”。
7.Ln:
用途:
返回数值的自然对数。
语法:
ln(number)。
参数:
“number”必须为正数。
【ln函数是exp函数的反函数】
举例:
A1=100,“ln(A1)”返回“”。
8.Log:
用途:
按所指定的底数,返回某个数的对【指】数。
语法:
log(number,base)。
参数:
“number”是须计算对数的任意实数,“base”是对数的底数。
如果省略底数,则默认它的底数为10。
举例:
Ⅰ.A1=8,“=log(A1,2)”返回“3”,Ⅱ.“=log(100)”返回“2”。
9.Mod:
用途:
返回两数相除的余数,其结果的正负与除数相同。
语法:
mod(number,divisor)。
参数:
“number”为被除数,“divisor”为除数。
【“divisor”不为0】
举例:
Ⅰ.A1=51,“=mod(A1,4)”返回“3”,Ⅱ.“=mod(-101,-2)”返回“-1”,Ⅲ.“=mod(5,0)”返回错误值“#DIV/0!
”。
10.Pi:
用途:
返回圆周率π,精确到小数点后14位。
语法:
pi()。
参数:
表格中大多显示为“”。
举例:
“=pi()”返回“”。
11.Quotient:
用途:
返回商的整数部分:
舍去其小数部分。
语法:
quotient(numerator,denominator)。
参数:
“numerator”为被除数,“denominator”为除数。
举例:
Ⅰ.A1=86,A2=9,“=quotient(A1,A2)”返回“9”,Ⅱ.“=quotient(-10,3)”返回“-3”,Ⅲ.“=quotient
(5,0)”返回错误值“#DIV/0!
”。
12.Randbetween:
用途:
产生位于两个指定参数间的随机数,每次重计算工作表【按F9】都将返回一个新的随机数值。
语法:
randbetween(bottom,top)。
参数:
“bottom”为最小指定数值【返回下限】,“top”为最大返回数值【返回上限】。
举例:
Ⅰ.“=randbetween(1,10)”返回“1~10”之间的随机数,Ⅱ.将“=randbetween(1,33)”写入A1:
F1区域内。
【将返回6个随机数,其中任意两个随机数相同的概率约为,可用此函数做双色球机选】
13.Round:
用途:
按指定位数四舍五入某数值。
语法:
round(number,num-digits)。
参数:
“number”是需四舍五入的数值,“num-digits”为指定舍入的位数。
举例:
Ⅰ.A1=,“=round(A1,2)”返回“65”,Ⅱ.“=round(A1,3)”返回“70”。
14.Sum:
用途:
返回某单元个区域所有数值之和。
语法:
sum(number1,number2,……)。
参数:
数值的数目最多为30。
【符号“+”与sum函数等效】
举例:
Ⅰ.A1=1,A2=2,A3=3,“=sum(A1:
A3)”返回“6”。
Ⅱ.A1=2,A2=3,“=A1+A2”返回“5”。
15.Product:
用途:
返回某单元个区域所有数值之积。
语法:
product(number1,number2,……)。
参数:
数值的数目最多为30。
【符号“*”与product函数等效】
举例:
Ⅰ.A1=1,A2=2,A3=3,“=product(A1:
A3)”返回“6”。
Ⅱ.A1=2,A2=3“=A1*A2”返回“6”。
16./:
用途:
返回某单元个区域所有数值之商。
语法:
number1/number2/……。
参数:
数值的数目最多为30。
举例:
Ⅰ.A1=3,A2=2,A3=1,“=A1/A2/A3”返回“”。
17.Power:
用途:
返回给定数字的乘幂。
语法:
power(number,power)。
参数:
“number”为底数【任意实数】,“power”为指数。
【符号“^”与power函数等效】
举例:
Ⅰ.A1=3,“=power(A1,3)”返回“27”,Ⅱ.A1=2,“=A1^2”返回“4”。
18.Sumif:
用途:
根据指定条件对若干单元格、区域或引用求和。
语法:
sumif(range,criteria,sum-range)。
参数:
range为用于条件判断的单元格区域,criteria是由数字、逻辑表达式等组成的判定条件,sum-range为需要求和的单元格、区域或引用。
举例:
Ⅰ.A1:
A7列存放员工级别,中级2人,初级4人,高级1人。
B1:
B7列存放员工工资,初级1500,中级2000,高级2600。
“=sumif(A1:
A7,“高级”,B1:
B7)”返回“2600”。
19.Sqrt:
用途:
返回某数值的算术平方根。
语法:
sqrt(number)。
参数:
“number”为需求算数平方根的非负数。
举例:
Ⅰ.A1=8,“=sqrt(A1)”返回“”。
20.Sqrtpi:
用途:
返回一个正数与π乘积的平方根。
语法:
sqrtpi(number)。
参数:
“number”是与π相乘的正数。
举例:
“=sqrtpi
(2)”返回“”。
21.Sumsq:
用途:
返回所有参数的平方和。
语法:
sumsq(number1,number2,……)。
参数:
数值的数目最多为30。
举例:
Ⅰ.A1=1,A2=2,A3=3,“=sumsq(A1:
A3)”返回“14”。
22.Sin:
用途:
返回某角度正弦值。
语法:
sin(number)。
参数:
“number”是待求正弦值的一个角度【采用弧度单位】,如果某角的单位是度,则必须乘以PI()/180转换为弧度。
举例:
Ⅰ.A1=60,“=sin(A1*pi()/180)”返回“”,
Ⅱ.A1=45°,“=sin(A1*pi()/180)”返回错误值“#value!
”
23.Cos:
用途:
返回某角度余弦值。
语法:
cos(number)。
参数:
“number”是待求余弦值的一个角度【采用弧度单位】,如果某角的单位是度,则必须乘以PI()/180转换为弧度。
举例:
Ⅰ.A1=60,“=cos(A1*pi()/180)”返回“”,
Ⅱ.A1=45°,“=cos(A1*pi()/180)”返回错误值“#value!
”
24.Tan:
用途:
返回某角度正切值。
语法:
tan(number)。
参数:
“number”是待求正切值的一个角度【采用弧度单位】,如果某角的单位是度,则必须乘以PI()/180转换为弧度。
举例:
Ⅰ.A1=60,“=tan(A1*pi()/180)”返回“”,
Ⅱ.A1=45°,“=cos(A1*pi()/180)”返回错误值“#value!
”
(六)统计函数(4条)
1.Average:
用途:
计算所有参数的算术平均值。
语法:
average(number1,number2,……)。
参数:
数值的数目最多为30。
举例:
Ⅰ.A1=5,A2=10,A3=15,A4=20,“=average(A1:
A4)”返回“”。
2.Max:
返回参数中的最大值。
语法:
max(number1,number2,……)。
参数:
数值的数目最多为30。
举例:
Ⅰ.A1=3,A2=12,A3=,A4=,“=max(A1:
A4)”返回“”。
3.Min:
返回参数中的最小值。
语法:
min(number1,number2,……)。
参数:
数值的数目最多为30。
举例:
Ⅰ.A1=3,A2=12,A3=,A4=,“=max(A1:
A4)”返回“3”。
4.Mode:
返回参数中的众数。
语法:
mode(number1,number2,……)。
参数:
数值的数目最多为30。
举例:
Ⅰ.A1=,A2=12,A3=,A4=,A5=,A6=12“=mode(A1:
A6)”返回“”。
(七)文本函数(3条)
1.Lower:
用途:
将文本中的所有大写形式全部转为小写形式。
语法:
lower(text)。
参数:
“text”是包含待转换字母的文本。
举例:
Ⅰ.A1=“MakeCookies”,“=lower(A1)”返回“makecookies”。
2.Proper:
用途:
将文本中的所有首字母转换成大写形式,其余的字母转换成小写形式。
语法:
proper(text)。
参数:
“text”是包含待转换字母的文本。
举例:
Ⅰ.A1=“学习excel”,“=proper(A1)”返回“学习Excel”。
3.Upper:
用途:
将文本全部转换为大写形式。
语法:
upper(text)。
参数:
“text”为需要转换成大写形式的文本,它可以是引用或文本。
举例:
Ⅰ.A1=“apple”,“=upper(A1)”返回“APPLE”。
ARTICLEEND
ThearticlewrittenbyTom