超全面！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.能实现透视表各种统计的PIVOTBYPIVOTBY估计是参数最多的函数，共计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不加班」推荐给你的朋友

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。