走光 偷拍

与 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不加班」保举给你的一又友开心影视

本站仅提供存储功绩，系数本色均由用户发布，如发现存害或侵权本色，请点击举报。