超全面！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不加班」推选给你的一又友

本站仅提供存储做事，统统本色均由用户发布，如发现存害或侵权本色，请点击举报。