工作中excel常用经典函数汇总

原创文章:如有下载及转载请注明来源链接，否则视为侵权

(1)左边取到倒数第二位

=left(k1,len(k1)-1)

(2)右边取到顺数第4位

=right(k2,len(k2)-3)

(3)excel if 判断

=IF(k3>90,"M3+",IF(k3>=61,"M3",IF(k3>=31,"M2","M1")))
= IF(C17>90,"优秀",IF(C17>80,"良好",IF(C17>60,"及格","不及格")))

(4)字符连接符

="200"&a4

(5)某个字符左右两边加单引号及逗号

="'"&K5&"'"&","

(6)select 某些合同进去oracel 取数时做临时表的辅助加工

="select '"&K6&"'"&"合同号 from dual union all"

(7)select 某些合同进去oracel 做临时表,注意日期的格式的写法

="select '"&K7&"'"&"合同号,"&"to_date('"&YEAR(L7)&"/"&MONTH(L7)&"/"&DAY(L7)&"','yyyy/mm/dd')"&" from dual union all"

(8)oracel 数据框insert into 插入数据辅助写法注意日期的格式的写法

="insert into collection.xn_agency_recovery values("&"'"&K10&"'"&","&"to_date('"&YEAR(L10)&"/"&MONTH(L10)&"/"&DAY(L10)&"','yyyy/mm/dd'),"&M10&","&"'"&N10&"');"

(9)sumif函数是用来根据指定条件对若干单元格进行求和。（即按单条件求和）

=SUMIF( $D$ 2: $D$ 9,G2, $C$ 2: $C$ 9) [求和条件范围,求和条件,求和值范围]
=SUMIFS(D32:D39,D32:D39,">=80",F32:F39,"男")#多条件求和

(10)COUNTIF函数是用来根据指定条件对若干单元格进行统计。（即按条件计数）

=COUNTIF( $D$ 2: $D$ 9,G2)[计数条件范围,计数条件]

(11)vlookup匹配有规律向右边移动匹配B4代表默认重第二列匹配

=VLOOKUP( $K4,$ A:$D,COLUMN(B4),0)

image.png

(12)Text函数

image.png

Text函数的语法为：=text(value,format_text)。Value为数字值。Format_text为设置单元格格式中自己所要选用的文本格式。
TEXT+MID函数提取出生年月

image.png

=TEXT(MID(E96,7,8),"0000-00-00")

(13)DATEDIF函数计算年龄

=DATEDIF(TEXT(MID(E113,7,8),"000-00-00"),TODAY(),"y")&"周岁"

(14)截取-前的部说明用FIND函数查找位置用LEFT截取

image.png

=Left(A1,FIND("-",A1)-1)

(13)条件求和：SUMIF、SUMIFS函数。

image.png

=SUMIF(F32:F39,"男",D32:D39)
=SUMIFS(D32:D39,D32:D39,">=80",F32:F39,"男")

(14)条件求和：COUNTIF、COUNTIFS函数。

image.png

=COUNTIF(F46:F53,"男")
=COUNTIFS(D46:D53,">=80",F46:F53,"男")

(15)逆向查询：LOOKUP函数

image.png

=LOOKUP(1,0/(E70:E77=H70),C70:C77)

(15)INDEX+MATCH 函数

image.png

=INDEX(G90:G97,MATCH(H90,E90:E97,0))

(16)DATEDIF函数计算年龄

image.png

=DATEDIF(TEXT(MID(E108,7,8),"0000-00-00"),TODAY(),"y")&"周岁"

(17)取绝对值 =ABS(数字)

=ABS(C119)

(18)取整 =INT(数字)

=INT(C120)

(19)四舍五入

=ROUND(C121,2)

(20)ERROR空值判断公式

1、把公式产生的错误值显示为空公式：C2
=IFERROR(A2/B2,"")
说明：如果是错误值则显示为空，否则正常显示。
=IFERROR(C122/D122,"空值")

(21)统计是否重复公式

1、统计两个表格重复的内容公式:B2
=COUNTIF(Sheet15!A:A,A2)
说明：如果返回值大于0说明在另一个表中存在，0则不存在。
=IF(COUNTIF(C127:C130,"a")>1,"重复","非重复")

image.png

=IF(COUNTIF(208:214,D208)>1,"重复","非重复")

image.png

=IF(COUNTIF(D$219:D219,D219)>1,"重复","非重复")

image.png

(22)字符串查找判断--一对一

image.png

=IF(COUNT(FIND("河南",C138))=0,"否","是")

(23)字符串查找判断--一对多

image.png

=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},C145))>0,"东北","其他")

(24)日期计算公式/两日期相隔的年\月\天数计算

相隔多少天? =DATEDIF(C152,D152,"d")
相隔多少月? =DATEDIF(C154,D154,"M")
不考虑年相隔多少月? =DATEDIF(C158,D158,"Ym")
不考虑年相隔多少天? =DATEDIF(C161,D161,"Yd")
不考虑月相隔多少天? =DATEDIF(C164,D164,"md")