365最新版函数基本用法介绍（一楼更新视频下载链接）

shaowu459

写在前面的话

本来打算多积累一些内容后再正式发出来，不过由于截图比较多，现在稍微修改帖子就会触发审核，因此先发出来吧，以后慢慢陆续更新。

本文所有示例我都在一个工作簿中更新，并在陆续发的帖子中更新附件，所以后续帖子中的附件会包含从2楼到该楼层所有的示例，只下载最新帖子中的文件即可获得全部帖子里的示例数据和公式。

Office 2003出来之后，Excel工作表函数公式已经成为一个庞大的体系，随着近20年来的发展，微软陆续开发并在不同版本的Excel中更新了很多工作表函数，函数家族成员不断扩充，功能也越来越强大。在非Office 365的各个Office版本中，Excel内置工作表函数已经被固定，某些函数只有在某些特定版本中才能使用，低版本是无法使用的。但Office 365作为订阅版本，可享受持续不断的函数更新服务，并且在使用预览版通道时，还能提前尝鲜一些预览版的函数，不断更新的这些新函数，极大的简化了之前复杂的函数嵌套，同时使编辑函数公式的思路更加清晰、简洁，为函数公式提供了更强的可阅读性。

Excel各固定版本函数（本帖中指Excel2003、2007、2019等版本）已经被论坛的各位大神玩出了花，新套路层出不穷，使人如痴如醉，沉浸其中，欲罢不能。这些技巧总结在论坛的知识树、精华帖、竞赛帖、得分帖中大部分都能找到，只不过系统的学习起来，完全的吸纳和接受可能有一定的难度，再能融会贯通灵活运用在实际工作中，就更需要持续的研究和练习。

Office 365各种新函数的出现，降低了解决一些复杂问题的门槛，学习函数的性价比较之前版本来说有了显著提高。比如说，一个XLOOKUP函数就能解决原来精确查询、模糊查询、倒序查询、查询不到错误值屏蔽等功能，也无需使用辅助列或用IF函数重构数组、不用去数或用函数判断返回数据所在列数等，花费较低的时间成本就可以有效地满足工作、生活需要。

正因为Office 365中的新函数功能如此强大，越来越多的人也开始使用Office 365版本。普通的Office 365函数，例如FILTER、UNIQUE、SORT、SORTBY等，论坛和网上已经有不少资源，所以本帖不再赘述。本帖主要集中讲解一下截至2022年5月22日Office 365预览版Beta频道中最新更新的一批函数。

阅读本帖时请注意：

1）本帖主要目的为普及365新函数的基本用法，其中的一些例子可能是为了展现用法而特意准备的，365新函数解法并非最优，新老版本函数技巧结合才能使函数迸发出更强大的生命力。

2）本帖中涉及的部分公式是我自己缩写，部分示例数据及公式可能来自QQ群及与其他人的交流和沟通，对记不清出处的，超人在此一并表示感谢。

3）本帖中公式可能大部分使用动态数组的溢出功能，在一个单元格中输入公式，回车后返回包含行列标题的最终结果，无需拖拉。但实际使用中，更方便地操作方式仍然可能是引用已有行列标题制作拖拉的公式。

4）欢迎随时讨论，大家集思广益，共同使用新函数解决更多的应用场景。但如果不是就本帖示例的讨论，还请上传压缩后的Excel文档，方便沟通和交流。

5）对于有创意的新看法或解法，会使用予以财富值奖励，主要是我没有鲜花可以给。

6）对帖子中描述不当或错误支出，请不吝批评指正。

下面，让我们一起共同讨论一下Office 365中最新版函数的一些基本用法。

本帖365 函数视频陆续更新在以下链接：
链接：https://pan.baidu.com/s/1mLvhIYmaQp-HQ-fw6AwgBQ?pwd=opcb
提取码：opcb

shaowu459

第1个函数：LAMBDA函数

这个函数，满坛的帖子已经做了非常清晰和完善的介绍，可参考下述链接。这个函数还是比较重要的，希望大家能花些时间浏览一下。
如果对那个帖子中的例子有需要讨论的，也可以发在这里讨论。


https://club.excelhome.net/thread-1570500-1-1.html

shaowu459

第2个函数：VSTACK函数

该函数语法如下：VSTACK(参数，参数，参数……)

简单说，VSTACK函数的作用是把各个参数纵向堆叠在一起，请看以下的例子：

A5=VSTACK(A1:C2,D3:F3,G2:I2)

上述公式将三个颜色的数组按参数顺序纵向堆叠在一起。

当被合并的数组列数不一致时，返回结果的列数以各数组中最大列数为准，少于最大列数的数组可以理解成被扩充了缺少的列，但以#N/A填充，如下图：

可以用IFNA函数将错误值屏蔽，例如将错误值屏蔽为空文本：

在实际运用过程中，常见的一种方式是为数据添加列标题，例如A21:A26单元格为学生姓名，需要生成一个带着“姓名”标题的数组：

C20 =VSTACK("姓名",A21:A26)

将一行两列的表头与数据区域合并（行标题与数据区域连接是常用功能之一）：

将单列常量数组增加长度：

如果把常量数组改成行方向，用IFNA函数控制错误值屏蔽为A29:A34单元格内容：

如果只引用A39:A44的数组可以将第二参数也用VSTACK加工一下：

shaowu459

第3个函数：HSTACK函数

该函数语法如下：HSTACK(参数，参数，参数……)

HSTACK函数的作用是把各个参数横向堆叠在一起，请看以下的例子：

D1=HSTACK(A1:A4,B3:B6)

上述公式将两个颜色的数组按参数顺序横向堆叠在一起。

当被合并的数组行数不一致时，返回结果的行数以各数组中最大行数为准，少于最大行数的数组可以理解成被扩充了缺少的行，但以#N/A填充，如下图：

可以用IFNA函数将错误值屏蔽，例如将错误值屏蔽为空文本：

HSTACK函数的参数可以是公式生成的数组，例如将数据和出现次数形成一个数组：

公式中用COUNTIF函数生成A列数据出现次数的数组，用VSTACK函数将“次数”这个标题和COUNTIF函数生成的数组连接起来，最后用HSTACK函数将原A列数据和VSTACK函数生成的数组横向连接在一起。

如果需要去重统计出现次数：

再比如，将下图数组按第2列及之后的行标题升序排列。首先可以用SORTBY函数将第一列之后的数组按棕色标题行升序排列，然后再将第一列Subject用HSTACK函数连接起来即可。

shaowu459

第4个函数：TOCOL函数

TOCOL函数可以将一个数组所有值转化为一列。
该函数语法如下：=TOCOL(array, [ignore], [scan_by_column])，其中第一参数是为需要转化成列的数组，第二参数可以选择是否忽略空白或错误，如果不写第二参数则不忽略空白或错误，第三参数可以设定是按行方向扫描数组还是按列方向扫描数组。

该函数的基本用法下面通过几个例子来说明。

当函数只输入第一参数的数组，该数组的所有元素将逐行输入到一列中，如下图：



如果区域中有空单元格，该单元格位置会被保留，但转化成了0：


第二参数中有4个选项，如下图所示，通过设置不同数字可以选择保留所有制、忽略错误或（和）空值。



例如，当第二参数输入1时，将忽略第一参数中的空：



如果需要将数组中符合一定条件的值返回在一列里，可以将不符合条件的值转化成错误值，然后第二参数选择2，转化成列时忽略这些错误值：



最后一个参数可以设置按行还是按列扫描数组。如果想将数组中元素按列方向转化成一列，最后一个参数可以选择TRUE。



下面用一个稍微综合点的例子（借用满坛版主昨天写的公式）来展示下前面介绍函数的用法。原帖链接：https://club.excelhome.net/thread-1628408-1-1.html

如下图，要将左侧的表格转化成右侧的样子，以第一户为例，将户主叠加到家庭成员的上方：



观察数据源，因为户主所在单元格是合并单元格，每户的户主和第一个家庭成员在同一行，第二户的户主在第一户的下面。如果先用TOCOL转化成一列，那么每一户的人员信息正好都能连接起来。同时，转化成一列时我们不需要合并单元格中的空白，因此可以将空白转化成错误值。

第一步公式：=HSTACK(A50:A60,IF(A50:A60="",0/0,"户主"),B50:B60,C50:E60)




因为绿色部分户主信息缺少“户主”这个字段，因此用IF(A50:A60="",0/0,"户主")生成“户主”这个字段，然后用HSTACK（户主姓名，“户主”，户主身份证，家庭成员的3列区域）合并起来，生成上图右侧的结果。

下一步可以使用TOCOL按行扫描，并且忽略空白和错误值，结果如下：



然后需要用到一个暂时没有介绍的函数WRAPROWS，该函数可以将一列按指定每行的元素折叠起来。此例因为结果需要3列，因此WRAPROWS第二参数设置为3。效果如下：



还缺一个列标题，用前面介绍过的VSTACK函数将标题堆叠在上述结果上即可：



如果按原贴的要求，加上前面两列的内容仍然可以用上面的方法。但我个人习惯用后面要介绍的涉及循环的函数REDUCE来解决：



TOCOL配合联合区域，可以将不同区域的数据整理成一列（满坛提供技巧）：

一烟难禁

衷心感谢版主的分享和付出！早就期待着有大神能出一个这样的帖子，专门着重介绍M365新函数，特别是LAMBDA相关函数，终于是等到了

Sean_zhang810

哇，还有这么神奇的函数。

shaowu459

TOCOL函数再举一个例子，模仿逆透视功能。如下图左侧，是一个二维表，要求生成右侧的表格，并且去除空值。



首先，原表的数据区域，直接用TOCOL函数就可以返回一列，只不过空单元被转化成了0。



如果把源数据区域A79:A85的城市名重复3次，转化成一列和数据区域转化的列横向合并在一起，然后筛选数据区域不为0的就可以了。那么首先，可以用以下方法将城市重复3次并转化成1列：



然后用HSTACK函数将刚才生成的两列合并在一起：



然后按源数据区域非0筛选以下即可：



为了完善，我们用VSTACK函数给上图生成的结果加个表头：



如果需要把品名加上，直接再定义一个u即可：



分析完了做法，是不是很简单？用新函数很轻松的完成了转化列和筛选功能，从而模拟了逆透视功能。



纯文本函数的方法，不推荐，会把数字变成文本，要变成数字还得嵌套处理一下：

shaowu459

第5个函数：TOROW函数

TOROW函数可以将一个数组所有值转化为一行，性质和TOCOL函数类似。
该函数语法如下：=TOROW(array, [ignore], [scan_by_column])，参数和TOCOL一致，就不再赘述，下面简单列几个示例说明基础用法。

将数组按默认方式转化成一行：


第二参数设置为1，忽略数组中的空白：


按列扫描将数组转化成一行：


按列方向扫描多个数组，并将多个数组各个元素转化成一行：

shaowu459

第6个函数：WRAPROWS函数

WRAPROWS函数功能和用法比较简单，可以实现将一行按指定列数折叠起来。

该函数语法如下：

=WRAPROWS(vector,wrap_count,[pad_with])

第一参数是需要折叠的数组，第二参数是每行需要保留的列数，第三参数是对错误值的处理。下面通过几个例子来说明一下。

第一参数A1:I1单元格区域，第二参数使用3，则A1:I1区域将按每行3个元素折叠起来，如下图：



如果每行选择保留4个元素，最后一行不足4的部分默认会返回错误值：



这时可以使用第三参数，将错误值屏蔽成需要显示的值，例如空文本：