与 30万 粉丝一起学Excel
最近几个月新函数很多,有个别是WPS表格特有的,大部分Office365也可以用,卢子全部整理到一起,一共16个,方便学习。所有公式都是直接输入后,回车即可,不需要像旧版本的数组那样按三键。
1.将一列内容转换成多列(WRAPCOLS和WRAPROWS)
这种有2个新函数处理,语法一样。
一个是先按列排序。
=WRAPCOLS(A2:A26,5)
一个是先按行排序。
=WRAPROWS(A2:A26,5)
2.将多行多列转换成一列或一行(TOCOL和TOROW)
TOCOL是转换成一列。
=TOCOL(A1:E5)
TOROW是转换成一行,转成行的不直观,平常几乎不用。
=TOROW(A1:E5)
假如多行多列内容里面存在错误值或者空单元格,可以设置第二参数为3忽略。2个函数的用法一样。
=TOCOL(A1:E5,3)
=SHEETSNAME(,1)
将字符串的数字、文字分离
[0-9]+代表连续的数字。
=REGEXP(A2,"[0-9]+")
^就是非的意思,[^0-9]+代表不是数字,也就是剩下的文字。
=REGEXP(A2,"[^0-9]+")
也可以用[一-龟]+。
=REGEXP(A2,"[一-龟]+")
5.将同一个单元格的内容拆分到多个单元格TEXTSPLIT
=TEXTSPLIT(A1," ",CHAR(10))
有的时候会出现输入不规范,也就是同时存在不同分隔符号,比如现在有空格和横杆存在。
正常人的思维,用查找替换,将符号统一。经过了测试,发现这个函数,即使不统一也行,分隔符号可以同时输入多个符号。{" ","-"},也就是{"符号1","符号2"}。
=TEXTSPLIT(A1,{" ","-"},CHAR(10))
6.用UNIQUE函数就可以提取不重复
只需在一个单元格输入公式,回车以后会自动扩展区域,并提取不重复。
=UNIQUE(A1:A18)
除了可以针对一列,同时也可以针对多列,比如针对公司名称和软件提取不重复。
=UNIQUE(A1:B18)
7.不重复计数
UNIQUE可以提取不重复值,怎么进行不重复计数?
那太简单了,再嵌套个COUNTA统计个数就行。
=COUNTA(UNIQUE(B2:B18))
那如果是按公司名称、软件2个条件不重复计数呢?
同样简单,改下区域,再除以2就可以。
=COUNTA(UNIQUE(A2:B18))/2
8.用SORT函数对内容自动排序
对月份降序。
=SORT(F2:G4,1,-1)
语法说明:
=SORT(区域,对第几列排序,-1为降序1为升序)
比如现在要对金额升序。
=SORT(F2:G4,2,1)
9.凭证自动生成的最简单公式
以前卢子分享过凭证自动生成的方法,不过实在太繁琐了,详见:凭证自动生成,太难了?
只需在一个单元格输入公式,就自动扩展,简单到没朋友。
=FILTER(C2:G11,B2:B11=D14)
语法说明:
=FILTER(返回区域,条件区域=条件)
10.找不到对应值,不用再嵌套IFERROR
正常情况下,用VLOOKUP或者LOOKUP查找的时候,找不到对应值会显示#N/A,一般情况下需要嵌套IFERROR。
而XLOOKUP即便是找不到对应值,也不需要嵌套其他函数。
=XLOOKUP(E2,A:A,B:B,"")
语法说明:
=XLOOKUP(查找值,查找区域,返回区域,错误值显示值)
11.将查找到的所有对应值去除重复,再合并在一个单元格
这个前阵子帮学员写了一个公式,套了又套,挺复杂的。现在有了新函数,那一切就不一样了。
=TEXTJOIN(",",1,UNIQUE(FILTER($A$2:$A$18,$B$2:$B$18=F2)))
这个就相当于将前面学的函数综合起来,FILTER就是将符合条件的筛选出来,再用UNIQUE去除重复值,最后用TEXTJOIN将内容合并起来。
12.标题顺序不一样的合并CHOOSECOLS
=CHOOSECOLS(H2:L10,2)
=CHOOSECOLS(区域,第几列)
=CHOOSECOLS(H2:L10,2,3,1)
=CHOOSECOLS(H2:L10,MATCH(A1:E1,H1:L1,0))
13.能实现透视表各种统计的GROUPBY
=GROUPBY(A1:A72,D1:D72,SUM,3)
=GROUPBY(A1:A72,D1:D72,AVERAGE,3)
=GROUPBY(A1:B72,D1:D72,SUM,3)
=GROUPBY(HSTACK(B1:B72,A1:A72),D1:D72,SUM,3)
=GROUPBY(A1:A7,B1:B7,ARRAYTOTEXT,3)
=UNIQUE(A1:B72)
=GROUPBY(F1:F7,G1:G7,ARRAYTOTEXT,3)
=GROUPBY(VSTACK(恩施市:华容区!A1:A72),VSTACK(恩施市:华容区!D1:D72),SUM,3)
=VSTACK(恩施市:华容区!A1:D72)
14.分表录入,总表自动更新
=VSTACK('01.现金'!A2:E11,'02.银行'!A2:E12,'03.微信'!A2:E11,'04.支付宝'!A2:E10)
=VSTACK(区域1,区域2,区域3,区域4)
=VSTACK('01.现金:04.支付宝'!A2:E12)
=VSTACK('开始表格名称:结束表格名称'!区域)
=VSTACK('01.现金:04.支付宝'!A2:E120)
=FILTER(A2:E999,E2:E999<>0)
=FILTER(返回区域,条件区域=条件)
=FILTER(VSTACK('01.现金:04.支付宝'!A2:E120),VSTACK('01.现金:04.支付宝'!E2:E120)<>0)
=PIVOTBY(行区域,列区域,值区域,汇总方式,是否包含标题)
=PIVOTBY(A1:A11,,D1:D11,SUM,3)
=PIVOTBY(A1:B11,,D1:D11,SUM,3)
=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM,3)
=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM)
=PIVOTBY(A1:A11&C1:C11,,B1:B11,ARRAYTOTEXT,3)
=PIVOTBY(HSTACK(A1:A11,C1:C11),,B1:B11,ARRAYTOTEXT,3)
=PIVOTBY(A1:A11,C1:C11,B1:B11,ARRAYTOTEXT,3)
=PIVOTBY(A2:A11,C2:C11,B2:B11,ARRAYTOTEXT,0,0,,0,,,0)
一次报名成为VIP会员,所有课程永久免费学,永久答疑,仅需 1500 元,待你加入。
报名后加卢子微信chenxilu2019,发送报名截图邀请进群。
推荐:分表录入数据,总表自动更新,新函数VSTACK+FILTER真好用!
上篇:VLOOKUP函数跟这个巧妙的辅助列,简直就是绝配,狂赞!
请把「Excel不加班」推荐给你的朋友
联系客服