ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 55270|回复: 327

[分享] 365最新版函数基本用法介绍(一楼更新视频下载链接)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2022-5-22 19:46 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:LAMBDA
本帖最后由 shaowu459 于 2022-7-12 09:20 编辑

写在前面的话

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

本文所有示例我都在一个工作簿中更新,并在陆续发的帖子中更新附件,所以后续帖子中的附件会包含从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

评分

39

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2022-5-22 19:50 | 显示全部楼层
本帖最后由 shaowu459 于 2022-5-28 18:55 编辑

第1个函数:LAMBDA函数

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


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

TA的精华主题

TA的得分主题

 楼主| 发表于 2022-5-22 23:20 | 显示全部楼层
本帖最后由 shaowu459 于 2022-5-22 23:32 编辑

第2个函数:VSTACK函数

该函数语法如下:VSTACK(参数,参数,参数……)

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

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

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

图片.png

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

图片.png

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

图片.png

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

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

图片.png

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

图片.png


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

图片.png

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

图片.png

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

365函数-VSTACK.rar

8.51 KB, 下载次数: 149

评分

4

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2022-5-23 22:27 | 显示全部楼层
本帖最后由 shaowu459 于 2022-5-24 08:48 编辑

第3个函数:HSTACK函数

该函数语法如下:HSTACK(参数,参数,参数……)

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

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

图片.png

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

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

图片.png

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

图片.png

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

图片.png

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

如果需要去重统计出现次数:
图片.png 图片.png

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

图片.png

365函数-HSTACK.rar

12.09 KB, 下载次数: 134

评分

3

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2022-5-24 19:37 | 显示全部楼层
本帖最后由 shaowu459 于 2022-5-25 18:33 编辑

第4个函数:TOCOL函数

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

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

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

图片.png

如果区域中有空单元格,该单元格位置会被保留,但转化成了0:
图片.png

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

图片.png

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

图片.png

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

图片.png

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

图片.png

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

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

图片.png

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

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


图片.jpg

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

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

图片.png

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

图片.jpg

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

图片.jpg

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


图片.jpg
TOCOL配合联合区域,可以将不同区域的数据整理成一列(满坛提供技巧):
图片.png

365函数-TOCOL.rar

15.59 KB, 下载次数: 154

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-25 07:26 | 显示全部楼层
衷心感谢版主的分享和付出!早就期待着有大神能出一个这样的帖子,专门着重介绍M365新函数,特别是LAMBDA相关函数,终于是等到了

TA的精华主题

TA的得分主题

发表于 2022-5-25 14:37 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
哇,还有这么神奇的函数。

TA的精华主题

TA的得分主题

 楼主| 发表于 2022-5-25 18:45 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 shaowu459 于 2022-5-31 11:46 编辑

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

图片.png

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

图片.png

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

图片.png

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

图片.png

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

图片.png

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

图片.png

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

图片.png

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



纯文本函数的方法,不推荐,会把数字变成文本,要变成数字还得嵌套处理一下:
图片.png

365函数-TOCOL.rar

16.79 KB, 下载次数: 131

评分

5

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2022-5-25 23:32 | 显示全部楼层
本帖最后由 shaowu459 于 2022-5-25 23:36 编辑

第5个函数:TOROW函数

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

将数组按默认方式转化成一行:
图片.png

第二参数设置为1,忽略数组中的空白:
图片.png

按列扫描将数组转化成一行:
图片.png

按列方向扫描多个数组,并将多个数组各个元素转化成一行:
图片.png

365函数-TOROW.rar

18.6 KB, 下载次数: 63

评分

2

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2022-5-26 22:10 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
第6个函数:WRAPROWS函数

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

该函数语法如下:

=WRAPROWS(vector,wrap_count,[pad_with])

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

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

图片.png

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

图片.png

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

图片.png


您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-6-19 08:34 , Processed in 0.062810 second(s), 18 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表