excel常用函数公式(有实例有分析有重点)

excel 公式笔记

一、vlookup

1.查找A列中第一个以"厦门"开头的记录对应B列的值。

=vlookup("厦门*",A:B,2,0)

其中第一个参数为要寻找的文本,第二个参数为一个区域,第二个参数的第一列为要寻找的区域,第三个参数的2表示第二个参数的第二列显示出来,第四个参数的0表示精确查找。

二、countif

1.统计A1:A10区域中型号为"

2.5m*3m"的记录个数。

=countif(A1:A10,"2.5m~*3m")

在excel常用函数中,支持通配符的主要有vlookup、hlookup、match、sumif、countif、search、searchB,而find、findB、subsitute不支持通配符。*表示任意字符,？表示单个字符,~解除字符的通配性。

2.统计A1:A10中不重复数的个数。

=SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10)))

3.求小于60的数据有多少

=count(A2:A10,"<60")

三、sumproduct

1.求购物总花费,A列表示购买数量,B列表示购买单价

=sumproduct(A2:A8,B2:B8)

意思为A2*B2+A3*B3。。。。+A8*B8

2.求二班有多少学生学习了数学

=sumproduct((A2:A10="二班")*(B2:B10="数学"))

表示二班的数学有几个

3.求二班数学分数总和

=sumproduct((A2:A10="二班")*(B2:B10="数学")*(C2:C10))

4.统计"技术部"考试成绩为0的个数(缺考除外)

=sumproduct((B2:B9="技术部")*(E2:E9=0)*(E2:E9<>""))

excel会将空值看成0,所以在统计成绩为0的考生时,需要把成绩为空的考生去除。

四、search、searchB

1.查找C2中是否有"北京"字符。

=search("北京",C2,1)

第一个参数为要找的字符,第二个参数是查找区域,第三个参数表示从

第1个字符开始查找。返回字符在字符串中第一次出现的位置。2.查找"i"在字符串"baidujingyan"中第一次出现的位置。

=search("i",A1,1)该公式返回3

search支持通配符,并为模糊查找,不区分大小写,find不支持通配符,为精确查找,并且区分大小写。

3.查找D2中第一个半角字符的位置

=searchB("？",D2)

五、match

1.找到第一个包含"中国"的单元格,并返回单元格在第几行=match("*中国*",C2:C11,0)

这个公式是返回字符所在的行,不是返回字符在字符串的位置。第一个参数为要查找的字符,第二个参数是寻找区域,第三个参数为0表示精确查找。

六、find

1.查找"怎么"在"excel中find函数怎么用"中的位置

=find("怎么",A1,5)

第三个参数5为开始查找的位置。

find不支持通配符,第二个参数A1为单元格,不是一个区域。

七、offset(reference,rows,cols,[height],[width])

1.得到一个区域

=offset(C5,-3,3,3,3)

得到了F2:H4区域

八、left

1.返回左边前6位字符

=left(A1,6)

九、mid、midB

1.从第11位开始,提取3个字符

=mid(A1,11,3)

2.从第7个字节开始,提取6个字节(3个字)

=mid(A1,7,6)

十、right

1.返回右边前6位字符

=right(A1,6)

十一、substitute

1.将后4位数字用"*"代替

=substitute(A2,right(A2,4),"****")

2.用"e"代替第二次出现的"i"

=substitute(A2,"i","e",2)

第二个参数为被替换的字符,第三个参数为新的需要的字符,第四个参数为第几次出现。replace函数是用字符的位置来控制代替、插入或删除。

十二、sumif

1.求"成都发货平台"的发货总量

=sumif(A2:A13,"成都发货平台",B2:B13)

=sumif(A2:A13,"成都*",B2:B13)

第二个参数必须在第一个参数内,sumif支持通配符,如果记录数目较多,可以使用"ctrl+shift+↓"一次性全部选取。

2.求一组数据中所有正数之和

=sumif(A1:A10,">0",A1:A10)

第一个参数为条件比较区,第二个参数为比较条件,第三个参数为求和区。

十三、sumifs

1.求数学与英语同时大于等于80分的同学总分之和

=sumifs(E2:E10,C2:C10,">=80",D2:D10,">=80")

和sumif正好相反,sumifs的第一个参数为求和区,第二个参数为条件区,第三个参数为比较条件,第四个参数为条件区,第五个参数为比较条件,以此类推。

十四、numberstring

1.将数字转换为小写中文数字

=numberstring(1234567890,1)

显示为：一十二亿三千四百五十六万七千八百九十

2.将数字转换为大写中文数字

=numberstring(1234567890,2)

显示为：壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾

3.将数字转换为中文数字

=numberstring(1234567890,3)

显示为：一二三四五六七八九O

十五、round、int、trunc

1.按四舍五入的方式保留2为小数,A1为17.567 =round(A1,2)

返回17.57

2.按四舍五入的方式取整数

=round(A1,0)

返回18

3.对数据取证,得到不大于数值本身的最大整数=int(3.2)

返回3

=int(-3.2)

返回-4

4.直接去除小数取整

=trunc(3.2)

返回3

=trunc(-3.2)

返回-3

5.截去第2位小数之后数字的值

=trunc(1234.5678,2)

返回1234.56

round是按四舍五入的方式来截取,int只能取整数,并且只返回不大于本身的整数,不能保留小数,trunc是直接截取,可以按要求保留小数位数。

6.四舍五入到十位

=round(123.456,-2)

返回100

十六、ROUNDUP、rounddown、floor、ceiling

1.向上保留两位小数

=ROUNDUP(123.654,2)

=ceiling(123.654,0.01)

返回123.66

其中ROUNDUP是通过指定小数位来得到舍入结果,而ceiling则是指定舍入基数来得到结果。

2.向下舍去保留一位小数

=rounddown(123.456,1)

=floor(123.456,0.1)

返回123.4

其中rounddown是通过指定小数位来得到舍入结果,而floor则是指定舍入基数来得到结果。

十七、small

1.返回最小值

=small(A1:A10,1)

第一个参数为需要比较的区域,第二个参数为返回第几个数。

2.返回最大值

=small(A1:A10,10)

十八、用来结束有数组运算的公式

十九、if、iferror

1.以1,5,03开头则返回0,其他返回0.3%

=if(or(left(A1,{1,1,2})={"1","5","03"}),0,0.3%)

2.两数相除,如果除数为0则返回除数不能为0

=iferror(A2/B2,"除数不能为0")

二十、iserror

1.判断公式是否返回错误值

=iserror(公式)

excel有7中错误：#value!、#N/A、#ref！、#Div/0！、#Num！、#name？、#Null！

2.如果公式有错误则返回空

=if(iserror(公式),"",公式)

二十一、isodd、isnumber

1.判断是否为奇数

isodd(数值)

2.判断返回值是否为数值

isnumber(公式)

二十二、exact

1.判断两个单元格是否完全相同

=exact(B1,B2)

相同则返回true,不相同则返回false

二十三、lower、upper、proper

1.将所有字符转换为小写

=lower("I Love ExcelHome！")

2.将所有字符转换为大写

=upper("I Love Excelhome！")

3.将首字母和非字母字符之后的首字母转成大写,其余小写=proper("苹果apple香蕉BANANA")

返回"苹果Apple香蕉Banana"

二十四、address

=address(1,1)

返回$A$1

=address(1,1,4)

返回A!

=address(1,1,4,true)

返回A!

=address(1,1,4,false)

返回R[1]C[1]

=address(1,1,4,false,"sheet2")

返回sheet2！R[1]C[1]

二十五、&

1.合并A1和B1

=A1&B1

2.合并A1和B1并自动换行

=A1&char(10)&B1

还需要设置"格式"-"单元格"-"对齐"-"自动换行" 二十六、datedif、date

1.返回两个日期相差多少年

=DATEDIF("1999-1-1","2003-2-3","y")

第三个参数y表示返回整年,所以此例返回4 2.返回两个日期相差多少个月

=DATEDIF("1999-1-1","2003-2-3","m")

第三个参数m表示返回整月,所以此例返回49 3.返回两个日期相差多少天

=DATEDIF("1999-1-1","2003-2-3","d")

第三个参数d表示返回天数,所以此例返回1494

4.返回两个日期相差多少天,忽略年和月

=DATEDIF("1999-1-1","2003-2-3","md")

第三个参数md表示返回天数,忽略年和月,所以此例返回2 5.返回两个日期相差多少个月,忽略年和日

=DATEDIF("1999-1-1","2003-2-3","ym")

第三个参数ym表示返回月数,忽略年,所以此例返回1 6.返回两个日期相差多少天,按照月日计算天数

=DATEDIF("1999-1-1","2003-2-3","yd")

第三个参数yd表示返回天数,忽略年,所以此例返回33 7.计算今天距离2016年8月20日还有多少天

=datedif(today(),date(2016,8,20),"D")

=date(2016,8,20)-today()

二十七、len、lenB

1.返回A1所占字节数

=lenB(A1)

2.返回A1所占字数

=len(A1)

3.求单元格里有几个人名如：张三、李四、王五

=len(C3)-len(substitute(C3,"、",))+1

人名数目比"、"符号多一个,用整个字符串的长度减去去掉"、"符号以后的长度再加1就得到人名数目。

二十八、replace

1.从第8位开始,后四位用*代替

=replace(A2,8,4,"****")

2.从第3位开始,删除2个字符

=replace(A2,3,2,"")或=replace(A2,3,2,)

3.在第7位插入"19"两个字符

=replace(B2,7,,"19")

substitute函数是通过具体的字符来控制替代,replace是通过字符的位置来控制。

二十九、widechar、asc

1.将半角字母转换为全角字符

=widechar("Excelhome")

返回"E x c e l h o m e"

2.将全角字符转换为半角字符

=ASC("我爱E x c e l")

返回"我爱Excel"

三十、text

1.将数字年份2000转换为中文年份

=TEXT(F51,"[dbnum1]0年")

返回"二○○○年"

=TEXT(F51,"[dbnum2]0年")

返回"贰零零零年"

[dbnum1]是中文小写格式,[dbnum2]是中文大写格式,后面加一个0的意思是让数字逐位显示。

2.将数字10月转换为中文月份

=text(A1,"[dbnum1]d月")

返回"十月"

=text(A1,"[dbnum2]d月")

返回"壹拾月"

"[dbnum1]d月"是把数字1-12设置为日期格式,d月更符合中文月份的表示方法。

3.将数字21设置为中文日期

=text(A1,"[dbnum1]d日")

返回"二十一日"

=text(A1,"[dbnum2]d日")

返回"贰拾壹日"

三十一、rand、randbetween

1. 返回一个大于等于0且小于1的随机数

=rand()

2.返回一个介于5到100之间的整数

=randbetween(5,100)

3.生成"大于等于60小于100"的带两位小数的随机成绩

=round(rand()*40+60,2)

4.生成"PC"开头的1000和9999之间的随机设备编号="PC"&randbetween(1000,9999)

rand是返回一个大于等于0且小于1的随机数,randbetween是返回一个介于参数1与参数2之间的整数。只有先加载"分析工具库"才能使用randbetween函数。

三十二、mod

1.对2求余数

=mod(A1,2)

三十三、year、month、day

1.求退休日期,C列是性别,D列是出生日期

=date(year(D2)+(C2="男")*5+55,month(D2),day(D2)+1) 2.判断一个日期所在的季度

=ROUNDUP(month(A3)/3,0)

=text(ceiling(month(A3)/3,1),"[dbnum1]第0季度")