【Excel新函数】动态数组系列
一、简介
相比Power BI,Power Query和Power Pivot在行列层级运行计算,Excel一直以来主要还是在单元格层面上的。Excel里,每行每列所有单元格进行相同逻辑的计算时,常规的做法是在第一个单元格填写公式,然后向下向右填充每一个单元格。如下图所示,计算各洲折后价的表格,蓝色区域所有单元格都要填入一个公式。
近年Excel提供了动态数组运算能力和一系列相关函数,能够类似于Power BI那样,直接在行列层级运算。一方面节省了公式填充复制的工作量,另一方面为更复杂的计算提供了可能性和便捷性。所谓数组,可以粗略地理解为一组数据,即行或列的数据。上面这个例子,利用数组运算,我们先清空b3:d5区域,然后直接在B3单元格输入,只需填一次公式,即可自动将运算填充到整个区域。
=A3:A5*B2:D2
二、支持版本和函数功能
目前下列版本的Excel支持使用动态数组:
- Excel 365 for Windows
- Excel 365 for Mac
- Excel 2021
- Excel 2021 for Mac
- Excel for iPad
- Excel for iPhone
- Excel for Android tablets
- Excel for Android phones
- Excel for the web
动态数组函数包括下列这些:
- UNIQUE - 从一系列单元格中提取去重的项目。
- FILTER - 根据您定义的标准过滤数据。
- SORT - 按指定列对一系列单元格进行排序。
- SORTBY - 按另一个范围或数组对一系列单元格进行排序。
- RANDARRAY - 生成随机数数组。
- SEQUENCE - 生成序列号列表。
- TEXTSPLIT - 跨列或/和行按指定的分隔符拆分字符串。
- TOCOL - 将数组或范围转换为单个列。
- TOROW - 将范围或数组转换为单行。
- WRAPCOLS - 根据每行指定的值数将行或列转换为二维数组。
- WRAPROWS - 根据每列指定的值数将行或列重新整形为二维数组。
- TAKE - 从数组的开头或结尾提取指定数量的连续行或列。
- DROP - 从数组中删除一定数量的行或列。
- EXPAND - 将数组增长到指定的行数和列数。
- CHOOSECOLS - 从数组中返回指定的列。
- CHOOSEROWS - 从数组中提取指定的行
以及lookup函数升级版XLOOKUP和match函数升级版XMATCH。这些函数将在后面的文章里展开介绍。
三、应用案例——查询多列结果
以我们常用的vlookup为例。在下图这种场景中,需要查询不同产品,三个地区的售价。常规做法,我们需要在I2:K2三个单元格中,各写一个相似的vlookup公式。当然,可以把第三个参数统一改成column()-6,直接复制填充即可。
如果使用数组运算,我们只需要在I3单元格输入一个公式,即可自动填充到J和K中。注意,此时的数组是通过大括号来触发的。公式中第三个参数,用大括号引用了3、4、5列,即要查询第3、4、5列的值。
=VLOOKUP(H2,$A:$E,{3,4,5},0)
三、隐式交集运算符@
隐式交集逻辑将多个值减少为单个值。上文两个例子中,我们一个公式产生的结果,会自动填充到相邻的范围。假设我们不需要这种扩展填充,希望只显示当前单元格的值,那么我们只需要在公式中的数组部分前面加上@。比如第一例把公式改为:
=@B3:B5*@C2:E2
则仅C3单元格会显示结果,其他区域不填充,如下图所示。
四、优点
1. 简单高效
通过数组计算,以往一些麻烦的运算,可以更简便地实现。比如一列数据去重、计算非重复值的个数,可以无需点击去重按钮,直接通过一个公式实现。后续文章会分享这个做法。
2. 绝对和相对引用不再那么讲究
以前我们下拉填充公式的时候,比如第一个例子,往往需要考虑行列位置谨慎注意使用绝对还是相对引用。一招不慎就会出错。而动态数组的自动填充功能,使得单元格的引用不再那么严格,节省了很多时间。
五、不足和限制
1. 计算结果无法点击排序按钮来排序
动态数组生成的结果,不支持升序降序按钮来排序。但可以通过sort函数解决这个问题。
2. 无法删除结果数列中的任意值
动态数组生成的结果,是一个整体,无法像平常excel列那样,删除其中任意的值。
3. 不支持超级表和Power Query
预告:下期将会逐步介绍动态数组函数的应用
相关文章
- C#生成Excel出现8000401a的错误的另一种解决办法。「建议收藏」
- pycharm界面颜色设置_Excel护眼色打印
- Python办公自动化 | word 文本转 excel
- BufferedWriter导出数据excel文件
- python将一维数组导入到excel表格,并使用Origin绘图
- excel批量导入图片 宏_怎么启用excel的宏
- idea 删除一行快捷键_excel删除一行快捷键
- 图片链接如何在excel里转成图片_mdf文件怎么转成Excel
- python随机产生数字_随机数生成excel
- Python自动化办公--Pandas玩转Excel数据分析【二】
- Python实现批量上传excel
- 程序员如何巧用Excel提高工作效率详解编程语言
- 报表 MySQL导出Excel数据报表的指南(mysql导出excel)
- 数据库用Excel快速导入Oracle数据库(excel导入oracle)
- 如何使用Excel连接MySQL数据库(excel连接mysql数据库)
- 快速安装Excel:Linux用户指南(linux安装excel)
- 利用Excel快速连接MySQL数据库(excel连接mysql)
- 使用Excel快速读取MySQL数据(excel读取mysql)
- 轻松导出:用SQL Server批量将数据导出到Excel(sqlserver导出excel)
- MSSQL导入Excel数据的精彩实战(mssql读excel)
- Excel中抓取Oracle数据的有效方法(excel抓oracle)
- Excel与Oracle精准大数据统计分析利器(excel和oracle)
- Excel数据快速导入Oracle数据库(excel入oracle)
- ASP.NET技巧:数据岛出到Excel最为简易的方法
- js读取本地excel文档数据的代码
- VBA中操作Excel常用方法总结