新金瓶梅

找色妹妹 超全面!WPS表格更新的16个新函数,一次全学会,数组公式退出历史
你的位置:新金瓶梅 > 欧美色图 亚洲色图 > 找色妹妹 超全面!WPS表格更新的16个新函数,一次全学会,数组公式退出历史
找色妹妹 超全面!WPS表格更新的16个新函数,一次全学会,数组公式退出历史
发布日期:2025-06-30 07:55    点击次数:190

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

本站仅提供存储职业,通盘现实均由用户发布,如发现存害或侵权现实,请点击举报。