微软一口气测试14个新函数，Excel函数加速向编程语言进化~

書生的图书馆 2022-04-17

展开全文

前段时间微软一口气测试了14个新函数，其中有些函数极其重要（本文第3小节），较大程度改变了函数核心数据结构数组的薄弱性。函数获取数据的能力提高了，解决复杂数据查询问题的能力更是指数级提升，今天就给大家稍微展开聊一下。

文本处理函数

这次测试的14个新函数里有三个文本函数：

TEXTSPLIT

TEXTBEFORE

TEXTAFTER

这仨货，特别是TextSplit对Excel函数来说还是很重要的，毕竟字符串拆分是极其常见的基础问题。这个函数可以将字符串按一到多个分隔符拆成一维或二维的内存数组。

但这函数本来20年前就该有，微软硬是拖拖拉拉磨蹭了7个版本才更新——这就好比男足20年后好不容易赢了越南，你那时问我什么感受？男足赢越南当然是重要的，但要问我感受嘛，那我只能说：草，是一种植物。

TEXTSPLIT有5个参数。前俩参数是必须的，第1个参数是源字符串，第2个参数是列分隔符。其它3个参数依次是行分隔符、是否忽略空值以及指定缺失值的填充值。

举点简单的小栗子。

如上图所示，需要将A列的字符串按分隔符'-'拆分为B:E的数据。

B2单元格输入以下公式向下复制填充即可：

=TEXTSPLIT(A2,'-')

当分隔符有多个时，第2和3参数还可以使用数组形式。

如下图所示的A列数据，分隔符既有-又有/，可以使用以下公式拆分。

=TEXTSPLIT(A2,{'-','/'})

还可以将一个复杂的字符串按分隔符拆成二维数组。

如下图所示，A2单元格是一个混合字符串，有多个记录构成，每个记录的结构均为人名-性别-科目-成绩，不同记录之间用分号串联。

使用以下函数可以将A2拆成为二维数组：

=TEXTSPLIT(A2,'-',';',,'数据缺失')

对于源字符串，公式先用；拆分为多行，再用-拆分为多列，对于缺失值，例如那个既美丽又知性的姑娘小白没有成绩，则用第5参数的指定值'数据缺失'填充。

剩下两个文本函数

TEXTBEFORE和TEXTAFTER

顾名思义，分别获取指定分隔符前和后的数据，意义不大，没啥好说的。

数组结构转换函数

Excel函数一直缺失结构转换类函数，例如，二维数组转一维，一维数据转二维；之前需要使用OFFSET等函数操作，过程复杂效率低下不说，涉及到内存数组维度转换时，在不同版本的稳定性也很糟糕。

这次更新了4个新函数。

TOROWS：将二维数组转换为一行

TOCOL：将二维数组转换为一列

WRAPCOLS：将一维数组按列转换为二维数组

WRAPROWS：将一维数组按行转换为二维数组

以上，举俩常见的小栗子。

有一个经典的数据结构转换问题，将多列数据转换为一列。这问题的旧的常规解法可以查阅【往期教程】，而现在用新函数一个函数就可以了：

=TOCOL(A1:C4,1,1)

第1个参数是数据源，第2个参数表示忽略空值，第3个参数表示按列扫描数据。

这个问题延伸一下，就有了多列数据转N列的情况。

如下图所示，A:D是数据源，有4列N行，需要转换为F:G所示的2列N行。

参考公式如下：

=WRAPROWS(TOCOL(A2:D6,1,0),2

公式先用TOCOL函数将4列数据转换为1列的一维数组，再用WRAPROW函数将一维数组按每2个元素为一行转换为二维数组。

数组动态扩展函数

打个响指，以上无聊的小东西总算说完了，这节说下这波新函数里最重要的几个。看我小眼神，这很有趣

。

2年前微软刚推出LAMBDA的时候，国外有大佬说Excel函数正式进化为编程语言了。那时看还是妄言，甚至是笑话。毕竟一个编程语言，不但要有分支和循环等语句，还得有数据结构做支撑，典型如数组和字典等。而Excel函数有什么？那时什么都没有。

可能有朋友说有数组——但函数的数组太弱，连基础的动态读写及调整大小的能力都没有，完全不足以支撑循环函数的广泛应用。

微软后来不断测试推出各种循环作用的函数：

MAP/REDUCT

SCAN/BYROW等等

这使Excel函数日益强大的同时，也进一步凸显它唯一的数据结构——数组的极度薄弱性，

摊手，而这一波更新终于明显增强了函数数组的能力。拥有这类函数，搭配变量功能的LET，循环语句的REDUCT/MAP/SCAN等，以及自定义过程的LAMBDA，再说Excel加速向编程语言进化就一点都不为过了。

当你熟练掌握了Excel函数，后期再学习其它编程语言，完全可以事半功倍，少掉很多头发——嗯，是的，毕竟你已经掉过了。

这类函数一共有7个▼

1，VSTACK：将多个数组纵向合并。
2，HSTACK：将多个数组横向合并。
3，TAKE：从数组的头或尾截取指定行/列数
4，DROP：从数组的头或尾删除指定行/列数
5，CHOOSEROWS：从二维数组中选取指定的行数
6，CHOOSECOLS：从二维数组中选取指定的列数
7，EXPAND：将数组扩展指定行/列，并填入指定值

照例举几个简单的小栗子。

▎选取指定行列

有一张数据表，如下图所示。

假设你现在只需要选取班级、姓名和英语三个字段的数据，参考函数如下：

=CHOOSECOLS(A1:E7,{1,2,5})

第1参数是数据源，第2参数表示选取的列的序列号。

如果事前并不知道班级/姓名和英语的序列号，可以将公式修改为以下，使用MATCH函数定位相关字段的位置。

=CHOOSECOLS(A1:E7,MATCH({'班级','姓名','英语'},A1:E1,0))

▎合并多个子数组

将多个子数组合并是很常见的问题，以前的解决方式需要借助LOOKUP函数或者字符串结构，复杂低效不灵活，这可以参考往期推文【看晕了，用Excel函数将多个一维数组合并成一维数组】

如下图所示，需要计算A列和C列两列人名不重复的名单。

E1单元格参考新公式如下：

代码看不全可以左右拖动...▼

=UNIQUE(VSTACK({'不重复名单'},A2:A7,C2:C8))

又或者将多个工作表的数据合并👇

如上图所示，有两张工作表，名称分别是一班和二班，需要将两表合并成总表↓

旧函数的解法极其繁琐，感兴趣的朋友可以参考【往期推文】，而新函数在总表的A1单元格输入以下公式即可：

=VSTACK(一班!A1:C7,二班!A2:C7)

VSTACK可以将多个数组按垂直方向合并成一个数组。和VLOOKUP一样，这里的V是vertical，表示垂直的、纵向的。与之相对的还有一个HSTACK，可以将多个数组横向合并成一个数组。

=HSTACK(一班!A2:C7,二班!A2:C7)

▎综合小案例

以上都是比较基础实在的用法，再给大家举一个数组动态扩展函数和循环函数搭配使用的简单小案例。

如下图所示，B列是开始的发票号，C列是结束的发票号，需要展开如E列所示。

比如12340-12346，展开后是12340/12341...12346。

参考函数解法如下：

代码看不全可以左右拖动...▼

=REDUCE({'发票展开'},B2:B4,LAMBDA(_a,_b,LET(_y,_b+SEQUENCE(VLOOKUP(_b,B:C,2,0)-_b+1)-1,VSTACK(_a,_y))))

REDUCT是累加循环函数，源值是常量数组{'发票展开'}，遍历范围是B2:B4，计算过程是第3参数的LAMBDA。

第2行代码利用SEQUENCE函数生成递增序列，再和初始发票号相加，返回一个内存数组：

12340,12341,12342,12343,12345,12346

第3行代码使用VSTACK函数，将第2行代码返回的内存数组和REDUCT的源数组纵向合并，作为新的源数组。

如此迭代直至遍历完成B2:B4区域的最后一个元素。

将数组动态扩展函数和REDUCT搭配使用，工作表函数就可以有机会像Power Query M函数的List.Accumulate似的，变成万能套路；如果你擅长M函数，那么对于该套路的强大应该并不陌生

再举个小栗子。

如上图所示，A列是混合字符串，需要从中统计人名及总分。

这样的问题，旧的常规的函数解法是先用辅助列把数据拆分整理，然后再分类汇总。而拥有这波新函数，我们就可以实现编程式函数，一条公式直接搞定：

=LET(_lst,DROP(REDUCE({''},A2:A11,LAMBDA(_d,_s,VSTACK(_d,INDEX(TEXTSPLIT(_s,{'-',';'}),{1,4})))),1),_nm,CHOOSECOLS(_lst,1),_score,CHOOSECOLS(_lst,2)*1,_unm,UNIQUE(_nm),_rst,MAP(_unm,LAMBDA(_r,SUM(FILTER(_score,_nm=_r)))),VSTACK({'姓名','总分'},HSTACK(_unm,_rst)))