excel常用函数公式有实例有分析有重点docx.docx
《excel常用函数公式有实例有分析有重点docx.docx》由会员分享，可在线阅读，更多相关《excel常用函数公式有实例有分析有重点docx.docx（12页珍藏版）》请在冰点文库上搜索。
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~*3mH）
在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=MZ：
Bm）*（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个字符开始查找。
返冋字符在字符串中第一次出现的位置。
1=1
2•查找,,i11在字符串"baidujingyan"中第一次出现的位置。
=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位数字用”杯代替
=substitute（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+I11一次性全部选取。
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）
显示为：
壹拾贰亿畚仟肆佰伍拾陆万柴仟捌佰玖拾
3.将数字转换为中文数字
=numberstring（1234567890,3）
显示为：
一二三四五六七八九0
十五、round、int、trunc
1•按四舍五入的方式保留2为小数,Al为17.567
=round（Al,2）
返冋17.57
2•按四舍五入的方式取整数
=round（Al,0）
返回18
3.对数据取证，得到不大于数值本身的最大整数
=int（3.2）
返回3
=int（-3.2）
返回・4
4•直接去除小数取整
=trunc（3.2）
返回3
=trunc（-3.2）
返回
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.654Q01）
返冋123.66
其中ROUNDUP是通过指定小数位来得到舍入结果，而ceiling则是指定舍入基数來得到结果。
2•向下舍去保留一位小数
=rounddown（123.456,1）=floor（123.456,0.1）返冋123.4
其中rounddown是通过指定小数位来得到舍入结果'而floor则是指定舍入基数來得到结果。
十七、small
1.返回最小值
=small（Al:
A10,l）
第一个参数为需要比较的区域，第二个参数为返回第几个数。
2.返回最大值
=small（Al:
A10,10）
十八、vctrl+shift+enter＞用来结束有数组运算的公式
十九、if、iferror
1.以1,5,03开头则返回0,其他返回0.3%
=if（or（left（Al,{l,l,2}）={,,l，7,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!
乙如果公式有错误则返回空
=if（iserror（公式）「舄公式）
二H—、isodd、isnumber
1•判断是否为奇数
isodd（数值）
2•判断返回值是否为数值
isnumber（公式）
二十二、exact
1•判断两个单元格是否完全相同
=exact（Bl,B2）
相同则返回true,不相同则返回false
二十三、lower、upper、proper
1.将所有字符转换为小写
=lower（MlLoveExcelHome!
H）
2•将所有字符转换为大写
=upper（HlLoveExcelhome!
n）
3.将首字母和非字母字符之后的首字母转成大写，其余小写
二proper（”苹果apple香蕉BANANA'1）
返回“苹果Apple香蕉Banana"
二十四、address
=address（l,l）
返回$A$1
=address（l,l,4）
返回A!
=address（l,1,4,true）
返回A!
=address（l,1/4,false）
返回R[1]C[1]
=address（l,l/4,false/"sheet2,'）
返冋sheet2!
R[1]C[1]
二十五、&
1•合并A1和B1
=A1&B1
2.合并Al和Bl并自动换行
=A1&char（10）&B2
还需要设置“格式r单元格⑺对齐叮‘自动换行“
二十六、datedif、date
1.返回两个日期相差多少年
二DATEDIF「1999・lJT2003・2・3Ty“）
第三个参数y表示返回整年，所以此例返回4
2.返回两个日期相差多少个月
=DATEDIF（,,1999-l-ll,；,2003-2-3,,；,mn）
第三个参数m表示返回整月'所以此例返回49
3•返回两个日期相差多少天
=DATEDIF（,,1999-l-l,,；,2003-2-3,7，d,'）
笫三个参数d表示返回天数，所以此例返回1494
4•返回两个日期相差多少天，忽略年和月
二DATEDIFCT999JJT2003・2・3TmcT）
第三个参数md表示返回天数，忽略年和月，所以此例返回2
5•返回两个日期相差多少个月，忽略年和日
=DATEDIF（,,1999-l-l,,/,,2003-2-3,,/,,ym,'）
第三个参数ym表示返刨月数，忽略年,所以此例返回1
6•返回两个日期相差多少天，按照月日计算天数
=DATEDIF（,,1999-l-l,,；,2003-2-3'7，ydH）
第三个参数yd表示返冋天数，忽略年,所以此例返冋33
7•计算今天距离2016年8月20日还有多少天
=datedif（today（）/date（2016/8/20）/,,D,'）
=date（2016,8,20）-today（）
二十七、len、lenB
1•返回A2所占字节数
=lenB（Al）
2•返回A2所占字数
=len（Al）
3•求单元格里有几个人名如：
张三、李四、王五
=len（C3）-len（substitute（C3z"、",））+1
人名数目比"、"符号多一个,用整个字符串的长度减去去掉"、"符号以
后的长度再加1就得到人名数目。
二十八、replace
1•从第8位开始，后四位用*代替
=replace（A2/8A，，****n
2•从第3位开始，删除2个字符
=replace（A2,3,2/N,）或二replace（A2,32）
3•在第7位插入U卯两个字符
=replace（B2,7„,,19"）
substitute丙数是通过具体的字符来控制替代‘replace是通过字符的位置来控制。
二十九、widechar>asc
1•将半角字母转换为全角字符
=widechar（"ExcelhomeH）
返回,,ExceIhomeH
2•将全角字符转换为半角字符
二ASC「我爱Excel”）
返回,'我爱Excel"
三十、text
1.将数字年份2000转换为中文年份
=TEXT（F51/'[dbnuml]0年”）
返回“二OOO年"
二TEXT（F5叮[dbnum2]0年”）
返回“贰零零零年“
[dbnuml]是中文小写格式」dbnum2]是中文大写格式，后面加一个0的意思是让数字逐位显示。
2.将数字10月转换为中文月份
=text（Alz"[dbnuml]d月"）
返回11十月,,
=text（Al；,[dbnum2]d月“）
返回“壹拾月”
“[dbnuml]d月“是把数字M2设置为日期格式川月更符合中文月份的表示方法。
3•将数字21设置为中文日期
二text（Ab“[dbnuml]dH1'）
返回“二十一日^
二text（Ab“[dbnum2]d日”）
返回”贰拾壹日“
三H—、rand、randbetween
1.返回一个大于等于0且小于1的随机数
二rand（）
2•返回一个介于5到100之间的整数
=randbetween（5,100）
3•生成,,大于等于60小于:
LOO11的带两位小数的随机成绩
=round（rand（）*40+60/2）
4•生成“PC”开头的1000和9999之间的随机设备编号
=nPCn&randbetween（1000,9999）
rand是返回一个人于等于0且小于1的随机数,randbetween是返回一个介于参数1与参数2之间的整数。
只有先加载"分析工具库"才能使用randbetween函数。
三十二、mod
1•对2求余数
=mod（Al,2）
三十三、year、month、day
1.求退休日期,C列是性别,D列是出生日期
=date（year（D2）+（C2=H男”）*5+55,month（D2）,day（D2）+l）
2.判断一个日期所在的季度
=ROUNDUP（month（A3）/3,0）
=text（ceiling（month（A3）/3,1）,"[dbnuml]第0季度”）
升级会员 