年折旧率计算公式excel常用函数公式(有实例有分析有重点)

excel 公式笔记
一、 vlookup
1.查找A列中第一个以厦门开头的记录对应B列的值。
=vlookup(厦门*
其中第一个参数为要寻找的文本,第二个参数为一个区域,第二个参数
的第一列为要寻找的区域,第三个 参数的2表示第二个参数的第二列
显示出来,第四个参数的0表示精确查找。
二、 countif
1.统计A1:A10区域中型号为的记录个数。
=countif(A1 :A10,
在excel常用函数中,支持通配符的主要有vlookup、hlookup、matc h、
sumif、countif、search、searchB,而find、findB、sub situte不支持通配
符。*表示任意字符,？表示单个字符,~解除字符的通配性。
2.统计A1:A10中不重复数的个数。
=SUMPRODUCT((1COUNTIF(A1:A10,A1:A10)))
3.求小于60的数据有多少
=count(A2:A10,
三、 sumproduct
1.求购物总花费,A列表示购买数量,B列表示购买单价
=sumproduct(A2:A8,B2:B8)
意思为A2*B2+A3*B3。。。。+A8*B8
2.求二班有多少学生学习了数学
=sumproduct((A2:A10=二班数学
表示二班的数学有几个
3.求二班数学分数总和
=sumproduct((A2:A10=二班数学
4. 统计技术部考试成绩为0的个数(缺考除外)
=sumproduct((B2:B9=技术部
excel会将空值看成0,所以在统计成绩为0的考生时,需要把成绩为空
的考生去除。
四、 search、searchB
1.查找C2中是否有北京字符。
=sea rch(北京
第一个参数为要找的字符,第二个参数是查找区域,第三个参数表示从
第1个字符 开始查找。返回字符在字符串中第一次出现的位置。
2.查找在字符串中第一次出现的位置。
=search(该公式返回3
search支持通配符,并为模糊查找,不区分大小写,f ind不支持通配符,为
精确查找,并且区分大小写。
3.查找D2中第一个半角字符的位置
=searchB(？
五、 match
1.找到第一个包含中国的单元格,并返回单元格在第几行
=match(中国*
这 个公式是返回字符所在的行,不是返回字符在字符串的位置。第一
个参数为要查找的字符,第二个参数是 寻找区域,第三个参数为0表示
精确查找。
六、 find
1.查找怎么在中find函数怎么用中的位置
=find(怎么
第三个参数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,righ t(A2,4),
2.用代替第二次出现的
=substitute(A2,
第二个参 数为被替换的字符,第三个参数为新的需要的字符,第四个参
数为第几次出现。replace函数是用 字符的位置来控制代替、插入或
删除。
十二、 sumif
1.求成都发货平台的发货总量
=sumif(A2:A13,成都发货平台
=su mif(A2:A13,成都*
第二个参数必须在第一个参数内,sumif支持通配符,如果记录数目 较
多,可以使用↓一次性全部选取。
2.求一组数据中所有正数之和
=sumif (A1:A10,
第一个参数为条件比较区,第二个参数为比较条件,第三个参数为求和
区。
十三、 sumifs
1.求数学与英语同时大于等于80分的同学总分之和
=s umifs(E2:E10,C2:C10,
和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
其中ROUN DUP是通过指定小数位来得到舍入结果,而ceiling则是指定
舍入基数来得到结果。
2.向下舍去保留一位小数
=rounddown(123.456,1)
=floor(123.456,0.1)
返回123.4
其中rounddow n是通过指定小数位来得到舍入结果,而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})={
2.两数相除,如果除数为0则返回除数不能为0
=iferror(A2B2,除数不能为0
二十、 iserror
1.判断公式是否返回错误值
=iserror(公式)
excel有7中错误： #value!、#NA、#ref！、#Div0！、#Num！、#name？、
#Null！
2.如果公式有错误则返回空
=if(iserror(公式),公式)
二十一、 isodd、isnumber
1.判断是否为奇数
isodd(数值)
2.判断返回值是否为数值
isnumber(公式)
二十二、 exact
1.判断两个单元格是否完全相同
=exact(B1,B2)
相同则返回true,不相同则返回false
二十三、 lower、upper、proper
1.将所有字符转换为小写
=lower(！
2.将所有字符转换为大写
=upper(！
3.将首字母和非字母字符之后的首字母转成大写,其余小写
=p roper(苹果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！R[1]C[1]
二十五、 &
1.合并A1和B1
=A1&B1
2.合并A1和B1并自动换行
=A1&char(10)&B1
还需要设置格式单元格对齐自动换行
二十六、 datedif、date
1.返回两个日期相差多少年
=DATEDIF(
第三个参数y表示返回整年,所以此例返回4
2.返回两个日期相差多少个月
=DATEDIF(
第三个参数m表示返回整月,所以此例返回49
3.返回两个日期相差多少天
=DATEDIF(
第三个参数d表示返回天数,所以此例返回1494
4.返回两个日期相差多少天,忽略年和月
=DATEDIF(
第三个参数md表示返回天数,忽略年和月,所以此例返回2
5.返回两个日期相差多少个月,忽略年和日
=DATEDIF(
第三个参数ym表示返回月数,忽略年,所以此例返回1
6.返回两个日期相差多少天,按照月日计算天数
=DATEDIF(
第三个参数yd表示返回天数,忽略年,所以此例返回33
7.计算今天距离2016年8月20日还有多少天
=datedif(today(),d ate(2016,8,20),
=date(2016,8,20)-today()
二十七、 len、lenB
1.返回A1所占字节数
=lenB(A1)
2.返回A1所占字数
=len(A1)
3.求单元格里有几个人名如：张三、李四、王五
=len(C3)-len(substi tute(C3,、
人名数目比、符号多一个,用整个字符串的长度减去去掉、符号以
后的长度 再加1就得到人名数目。
二十八、 replace
1.从第8位开始,后四位用*代替
=replace(A2,8,4,
2.从第3位开始,删除2个字符
=replace(A2,3,2,或=replace(A2,3,2,)
3.在第7位插入两个字符
=replace(B2,7,,
substitute 函数是通过具体的字符来控制替代,replace是通过字符的位
置来控制。
二十九、 widechar、asc
1.将半角字母转换为全角字符
=widechar(
返回
2.将全角字符转换为半角字符
=ASC(我爱E x c e l
返回我爱Excel
三十、 text
1.将数字年份2000转换为中文年份
=TEXT(F51,年
返回二○○○年< br>=TEXT(F51,年
返回贰零零零年
[dbnum1]是中文小写格式,[dbnu m2]是中文大写格式,后面加一个0的
意思是让数字逐位显示。
2.将数字10月转换为中文月份
=text(A1,月
返回十月
=tex t(A1,月
返回壹拾月
月是把数字1-12设置为日期格式,d月更符合中文月份的
表示方法。
3.将数字21设置为中文日期
=text(A1,日
返回二十一日< br>=text(A1,日
返回贰拾壹日
三十一、 rand、randbetween
1. 返回一个大于等于0且小于1的随机数
=rand()
2.返回一个介于5到100之间的整数
=randbetween(5,100)
3.生成大于等于60小于100的带两位小数的随机成绩
=round(rand()*40+60,2)
4.生成开头的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=男
2.判断一个日期所在的季度
=ROUNDUP(month(A3)3,0)
=text(ceiling(month(A3)3,1),第0季度