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

excel公式笔记

一、vlookup

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

=vlookup(H厦门性A:B20)

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

二、countif

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

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

=countif(Al:A10,,,2.5m~*3m H)

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

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

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

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

二count(A2:AKVv6(T)

sumproduct

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

=sumproduct(A2:A8,B2:B8)

意思为A2*B2+A3*B3oooo +A8*B8

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

=sumproduct((A2:A10=H~ B,,)*(B2:B10=H数学“))

表示二班的数学有几个

3.求二班数学分数总和

=sumproduct((A2:A10=M Z：B M)*(B2:B10=H数学H)*(C2:C1O))

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

=sumproduct((B2:B9=H技术部,,)*(E2:E9=0)*(E2:E9o,,H))

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

四、search、searchB

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

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

第一个参数为要找的字符，第二个参数是查找区域，第三个参数表示从第1个字符开始查找。返冋字符在字符串中第一次出现的位置。

•查找,,i11在字符串"baidujingyan"中第一次出现的位2

1=1

置。

=search(,,i",Al,l)该公式返回3

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

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

=searchB(H? "Q2)

五、match

1•找到第一个包含”中国“的单元格，并返回单元格在第几行

=match(H* 中国*n,C2:Cll,0)

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

精确查找。

六、find

1 •查找,,怎么,,在“excel中find函数怎么用,,中的位置二find(” 怎么•用5)

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

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

七、offset(Feference,FowsFCols』heightL[width]) 1•得到一个区域

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

得到了F2:H4区域

八、left

1.返回左边前6位字符

=left(Al,6)

九、mid、midB

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

=mid(Al/ll,3)

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

=mid(Al/7,6)

十、right

1•返回右边前6位字符

=right(Al,6)

H—、substitute

1•将后4位数字用”杯代替

=su bst it ute( A2, right( A2,4)z "***♦")

2用宜代替第二次出现的,,i11

=substitute(A2,,,i,,/'e,,,2)

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

十二、sumif

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

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

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

第二个参数必须在第一个参数内sumif支持通配符，如果记录数冃较

多，可以使用“ctrl+shift+ I 11一次性全部选取。

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

=sumif(Al:A10,H>0,,,Al:A10)

第一个参数为条件比较区，第二个参数为比较条件，第三个参数为求和十三、sumifs

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

二sumifs(E2:E10,C2:Cl(V> 二80 蔦D2:D10/>=8(T)

和sum讦正好札I反‘sumifs的第一个参数为求和区，第二个参数为条件区，第三个参数为比较条件,第四个参数为条件区，第五个参数为比较条件，以此类推。

十四、numbeFstring

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

=numberstring(1234567890,l)

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

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

=numberstring(1234567890,2)

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