excel常用函数公式有实例有分析有重点docx.docx
- 文档编号:16596557
- 上传时间:2023-07-15
- 格式:DOCX
- 页数:12
- 大小:21.77KB
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季度”)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 常用 函数 公式 实例 分析 重点 docx