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