工作中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")

(24)文本提取

image.png

(25)生成随机数

image.png

(26)首字母大小写转换

image.png

(27) rank排序统计

image.png

(28)基本日期函数，生成当前日期函数：TODAY和当前时间的函数NOW

=TODAY()
=NOW()
=DATE(B247,C247,D247)
=DAY(B251)
=C256-B256
=INT((MONTH(B261)+2)/3)

image.png

(29)判断指定日期所在的星期五、TEXT：星期（中国）

image.png

星期（中国） =TEXT(B261,"aaaa")
星期（英文） =TEXT(B261,"dddd")
LEN+MONTH：计算季度 =WEEKNUM(B261)
第X天 = B261-"1-1"+1
DAY+EOMONTH：本月有几天 =DAY(EOMONTH(B261,0))

(30) 快速求和？用 “Alt + =”

image.png

(31)比如<Ctrl+shift+4>就能立刻把数字加上美元符号

image.png

(32)在不同的工作表之间快速切换

在不同的工作表之间切换，不代表你的手真的要离开键盘（可以想象如果你学会了这些酷炫狂拽的快捷键，你根本不需要摸鼠标）
“Ctrl + PgDn”可以切换到右边的工作表，反之，“Ctrl + PgUp”可以切换回左边。

(33)NETWORKDAYS和WORKDAY函数

image.png

=NETWORKDAYS(C362,D362)

image.png

=WORKDAY(D377,30)

(34)Trim() 函数

image.png

(35)Clean()函数

image.png

(37) SUBTOTAL函数

image.png

avg: =SUBTOTAL(1,418:422)
count: =SUBTOTAL(2,418:422)

(38)SUMPRODUCT函数

image.png

(39) SMALL & LARGE 函数

image.png

=SMALL(D450:D454,3)

(40)INDEX+MATCH函数(Index(查找区域,返回来行与列交叉区域内单元格的值))

Index+Match函数比vlookup函数有优势的几个用法
很多人在工作中都使用过vlookup函数进行查找内容。但是遇到反向查找、双向查找等比较复杂的操作时就可以看到vlookup函数的劣势，此时我们就可以使用index+Match组合函数。

image.png

(单向查找)

image.png

位置:=MATCH(B485,C478:C482,0)
位置返回值编号:=INDEX(B478:B482,MATCH(B486,C478:C482,0))

如果使用vlookup函数则必须将源数据区域编号列和产品名称列调换顺序。此处我们可以使用index和match函数进行操作。
在此公式中我们先利用Match函数根据产品名称在B列查找位置;
之后再使用Index函数根据查找到的位置从A列取值。

(双向查找)

以下图为例，我们需要根据年度和类别查找出对应的具体金额。

image.png

=INDEX(C499:G503,MATCH(C506, $B$ 499: $B$ 503,0),MATCH(B506, $C$ 498: $G$ 498,0))

(多条件查找)

image.png

=INDEX(D514:D518,MATCH(C520,B514:B518,0),MATCH(C521,C514:C518,0))

(vlookup函数——按列查找。函数从右到左查找怎么做???)

image.png

正常查找:=VLOOKUP(C536:C540,D526:E531,2,0)
逆向查找:=INDEX(D527:D531,MATCH(E536,E527:E531))

image.png

用column(b2)代替手工改返回第几列值,
b2默认是返回来第二列值,
C2默认是返回来第三列值
=VLOOKUP( $G544,$ D $543:$ F $548,COLUMN(B2),0) =VLOOKUP($ G544, $D$ 543: $F$ 548,COLUMN(C2),0)

image.png

[图片上传中...(image.png-197ea6-1566717796305-0)]
COLUMN(C2)-1 向左移一列
=VLOOKUP(DFC2,Sheet2!543:548,COLUMN(B2)+1,0)

(hlookup函数——按行查找。函数从下往左查找怎么做????)

image.png

正常查找:左往右查找:
=HLOOKUP(BGB574,564:567,COLUMN(C2),0)
COLUMN(C2)-1 向上移一行:
=HLOOKUP(BGB574,564:567,COLUMN(C2)+1,0)

最后编辑于：2019.08.25 15:30:45

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,590评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 86,808评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,151评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,779评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,773评论 5赞 367
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,656评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,022评论 3赞 398
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,678评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,038评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,659评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,756评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,411评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,005评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,973评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,203评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,053评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,495评论 2赞 343

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

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

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

(3)excel if 判断

(4)字符连接符

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

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

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

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

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

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

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

(12)Text函数

(13)DATEDIF函数计算年龄

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

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

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

(15)逆向查询：LOOKUP函数

(15)INDEX+MATCH 函数

(16)DATEDIF函数计算年龄

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

(18)取整 =INT(数字)

(19)四舍五入

(20)ERROR空值判断公式

(21)统计是否重复公式

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

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

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

(24)文本提取

(25)生成随机数

(26)首字母大小写转换

(27) rank排序统计

(28)基本日期函数，生成当前日期函数：TODAY和当前时间的函数NOW

(29)判断指定日期所在的星期五、TEXT：星期（中国）

(30) 快速求和？用 “Alt + =”

(31)比如<Ctrl+shift+4>就能立刻把数字加上美元符号

(32)在不同的工作表之间快速切换

(33)NETWORKDAYS和WORKDAY函数

(34)Trim() 函数

(35)Clean()函数

(37) SUBTOTAL函数

(38)SUMPRODUCT函数

(39) SMALL & LARGE 函数

(40)INDEX+MATCH函数(Index(查找区域,返回来行与列交叉区域内单元格的值))

(单向查找)

(双向查找)

(多条件查找)

(vlookup函数——按列查找。 函数从右到左查找怎么做???)

(hlookup函数——按行查找。函数从下往左查找怎么做????)

推荐阅读更多精彩内容

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

(vlookup函数——按列查找。函数从右到左查找怎么做???)