拨丝留其产 / 技巧 / 工作中常用的Excel函数公式大全

0 0

   

工作中常用的Excel函数公式大全

2016-03-19  拨丝留其产

?

使用率比较高的函数公式整理齐了,居家旅行必备!


强烈建议留意下方小窍门!!!!!

小窍门:在动图上轻滑屏幕后停住,可暂停动图哦!图片较多,建议使用WIFI!



一:数字、字符处理



1、取绝对值

=ABS(A1)

A1单元格为数字



2、去空格

去单元格内容前后的空格

=TRIM(A1)


去单元格内所有空格

=SUBSTITUTE(A1,' ','')

公式中第一个' '中间为空格



3、多单元格合并

=PHONETIC(B2:D2)

PHONETIC只能合并字符,不能对数字合并




4、取整数

=INT(A1)

A1单元格为数字



5、四舍五入

=ROUND(A1,N)

A1单元格为数字;N为小数位数,譬如N=1,四舍五入1位小数;N=2,四舍五入2位小数




6、取字符数

=LEN(A1)






7、取字符串左边或者右边的字符

=LEFT(A1,3)

=RIGHT(A1,3)





二:判断公式



1、借方显示正数,贷方显示负数

=IF(A2=0,-B2,A2)

A2 B2单元格为数字





2、错误值显示为空白

=IFERROR((C2-B2)/B2,'')




PS:也可以根据需要,错误值显示为0或1

=IFERROR((C2-B2)/B2,1)





3、IF多条件判断

=IF((B2>=70)*(C2>=70)*(D2>=70),A2,'')




PS:任何一个条件成立 IF+OR函数

=IF(OR(B2<><><>






三:统计公式




1、统计重复次数

=COUNTIF(A:A,A2)





PS:多条件统计重复次数

=COUNTIFS(B:B,'>=70',C:C,'>=70',D:D,'>=70')





PS:出现重复值则显示“重复”,IF+COUNTIF函数

=IF(COUNTIF(A:A,A2)>=2,'重复','')






2、统计不重复的总人数

=SUMPRODUCT(1/COUNTIF(A2:A13,A2:A13))





四:求和



1、单条件求和

=SUMIF(A:A,D2,B:B)





2、隔行求和

=SUMIF($B$2:$G$2,H$2,$B3:$G3)




PS:如标题行没有规律

=SUMPRODUCT((MOD(COLUMN(B$3:G$3),2)=0)*$B3:$G3)






3、多条件模糊求和


=SUMIFS(C:C,A:A,E2&'*',B:B,F2)

公式中使用通配符*





4、按日期和产品求和


=SUMPRODUCT((MONTH($B$2:$B$7)=F$1)*($A$2:$A$7=$E2),$C$2:$C$7)





四:查找




1、简单查找


=VLOOKUP(E2,A:C,3,0)

根据姓名查找总业绩





2、多条件查找

=INDEX(A:D,MATCH(F2,A:A,0),MATCH(G2,1:1,0))名查找总业绩

说明:INDEX(查找区间,行号,列号)






3、查找最后一个符合条件的值


=LOOKUP(2,1/(A2:A9=A12),E2:E9)




PS:多条件查找最后符合条件的值

=LOOKUP(2,1/((A2:A9=A12)*(D2:D9=D12)),E2:E9)






4、根据区间取数

{=LOOKUP(D9,IF(B$2:B$7=B9,D$2:D$7),C$2:C$7)}

注:大括弧并非手工打上去,数组公式输入完毕同时按CTRL+SHIFT+ENTER




    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    猜你喜欢

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多