打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
超全面!WPS表格更新的16个新函数,一次全学会,数组公式退出历史

与 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)

3.根据工作表名称生成目录SHEETSNAME
一个简单的函数即可,而Office需要很复杂的公式或者VBA才行。
=SHEETSNAME(,1)

4.正则表达式REGEXP,提取各种内容

两个软件都有正则,而Office是由3个函数组成。

将字符串的数字、文字分离

[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

2个表的标题顺序不一样,现在想合并在一起,除了一列一列复制粘贴,还有什么更好的方法?
比如要将姓名合并过来。
=CHOOSECOLS(H2:L10,2)

语法:返回区域第几列的内容。
=CHOOSECOLS(区域,第几列)

神奇的地方还在后面,这个函数如果要返回多列也可以,比如返回第2、3、1列。
=CHOOSECOLS(H2:L10,2,3,1)
前面提到可以用MATCH判断内容分别在第几列。
=CHOOSECOLS(H2:L10,MATCH(A1:E1,H1:L1,0))

13.能实现透视表各种统计的GROUPBY

统计每个项目的金额

行区域A1:A72,值区域D1:D72,汇总方式SUM(也就是求和),3代表包含标题。
=GROUPBY(A1:A72,D1:D72,SUM,3)

汇总方式有非常多,最大值MAX,最小值MIN,平均值AVERAGE等等,现在以其中一个演示。
=GROUPBY(A1:A72,D1:D72,AVERAGE,3)

统计每个项目对应负责人的金额

行区域是从左到右按顺序,因此可以写A1:B72
=GROUPBY(A1:B72,D1:D72,SUM,3)

其他情况下,都需要结合HSATCK函数才行,比如求每个负责人对应项目的金额。
=GROUPBY(HSTACK(B1:B72,A1:A72),D1:D72,SUM,3)

其他传统的方式就不再说明,跟普通的透视表差不多,可以互相取代。下面讲新函数优势的地方。

根据项目合并负责人(数据源已去重复)

透视表的强项是处理数据,而处理文本并不擅长。而新函数不管数据还是文本,都可以处理。

ARRAYTOTEXT的作用就是按分隔符号合并文本。
=GROUPBY(A1:A7,B1:B7,ARRAYTOTEXT,3)

如果数据源有重复值,直接处理超级麻烦,建议辅助列用UNIQUE函数去重复。
=UNIQUE(A1:B72)


再引用辅助列的区域。
=GROUPBY(F1:F7,G1:G7,ARRAYTOTEXT,3)

将多表合并后,汇总项目对应的金额

合并多表以前都是借助PQ,再用透视表统计。

现在可以借助VSTACK合并,再用GROUPBY统计。

假如原来是每个项目一张工作表。

使用公式:
=GROUPBY(VSTACK(恩施市:华容区!A1:A72),VSTACK(恩施市:华容区!D1:D72),SUM,3)

这里再单独演示VSTACK的作用,就是将多表的数据合并在一个表。不过直接合并的时候,有一个小缺陷,会出现一大堆无用的0。这种当然也可以处理掉,不过不是这篇文章讨论的内容,以后再说。
=VSTACK(恩施市:华容区!A1:D72)

14.分表录入,总表自动更新

格式相同的分表。

多表合并的方法非常多,有VBA、PQ等,今天卢子分享新函数VSTACK+FILTER。

VSTACK函数语法跟SUM函数几乎一样,懂得SUM就可以。

最原始的用法,就是分别引用每个分表的区域,再用逗号隔开。
=VSTACK('01.现金'!A2:E11,'02.银行'!A2:E12,'03.微信'!A2:E11,'04.支付宝'!A2:E10)

语法:
=VSTACK(区域1,区域2,区域3,区域4)

使用最多的还是下面这种。
=VSTACK('01.现金:04.支付宝'!A2:E12)

语法:
=VSTACK('开始表格名称:结束表格名称'!区域)

因为分表要每天记录新数据,可以将区域写大点,这样就可以动态合并。不过美中不足的是,总表就会出现很多0。
=VSTACK('01.现金:04.支付宝'!A2:E120)

要去掉这些0,其实也不难,借助FILTER函数,判断E列不等于0即可。先来看辅助列方法。
=FILTER(A2:E999,E2:E999<>0)

语法:
=FILTER(返回区域,条件区域=条件)

当然,不用辅助列,一步到位也行,两个区域都套VSTACK函数。这里有一个很容易出错的地方要特别注意,返回区域是A2:E120,条件区域是E2:E120,千万别写一样。
=FILTER(VSTACK('01.现金:04.支付宝'!A2:E120),VSTACK('01.现金:04.支付宝'!E2:E120)<>0)

假如在最后一个表输入一行新内容。

在总表就能看到,相当于自动合并,实现一劳永逸。
15.能实现透视表各种统计的PIVOTBY

PIVOTBY估计是参数最多的函数,共计11个参数,今天只讲前5个。这里多了一个列区域。
=PIVOTBY(行区域,列区域,值区域,汇总方式,是否包含标题)

统计每个项目的金额

行区域A1:A11,列区域不需要就用逗号占位,值区域D1:D11,汇总方式SUM(也就是求和),3代表包含标题。
=PIVOTBY(A1:A11,,D1:D11,SUM,3)

统计每个项目对应负责人的金额

行区域是从左到右按顺序,因此可以写A1:B11
=PIVOTBY(A1:B11,,D1:D11,SUM,3)

其实,还有一种效果,项目在行区域,负责人在列区域,金额在值区域。
=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM,3)

这种带标题的效果感觉不太好,3去掉就是不带标题,看起来更简洁。
=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM)

根据项目、年月合并负责人

将项目、年月用&合并到一起再处理最简单,ARRAYTOTEXT的作用就是按分隔符号合并文本。
=PIVOTBY(A1:A11&C1:C11,,B1:B11,ARRAYTOTEXT,3)

当然也可以将项目、年月分开变成2列,就需要嵌套HSTACK函数。
=PIVOTBY(HSTACK(A1:A11,C1:C11),,B1:B11,ARRAYTOTEXT,3)

还有一种就是年月放在列区域。
=PIVOTBY(A1:A11,C1:C11,B1:B11,ARRAYTOTEXT,3)

其实,这些行列总计、标题之类的都可以去掉,区域从第2行开始,同时设置后面几个参数实现。
=PIVOTBY(A2:A11,C2:C11,B2:B11,ARRAYTOTEXT,0,0,,0,,,0)


陪你学Excel,一生够不够?

一次报名成为VIP会员,所有课程永久免费学,永久答疑,仅需 1500 元,待你加入。

报名后加卢子微信chenxilu2019,发送报名截图邀请进群。

推荐:分表录入数据,总表自动更新,新函数VSTACK+FILTER真好用!

上篇:VLOOKUP函数跟这个巧妙的辅助列,简直就是绝配,狂赞!

请把「Excel不加班」推荐给你的朋友

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
Excel函数教程
Excel-VBA合并工作表
vlookup函数用法
不用VBA,用IF函数就能实现总表录入数据分表自动生成,简单易学
Excel中通过函数批量拆分总表数据到各分表,简单到没朋友!
excel拆分合并技巧:将总表拆分成工作表的方法
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服