Excel情报局

用案例讲Excel

探索挖掘分享Excel实用技能
SuperExcelMan
用1%的Excel基础搞定99%的职场难题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气
数万表格爱好者聚集地

在Excel和WPS表格的新版本中，MAKEARRAY与LAMBDA函数的结合，将”数组思维”推向了新的高度。它不仅大幅提升了公式的效率，更重要的是，它改变了我们处理二维数据的逻辑方式。过去需要拖拽公式、多个辅助列的工作，现在可以用一个公式快速的解决。



我们以一个最简单的例子浅谈，引出makearray到底是用来干嘛的。

比如B3:B4纵列有数据1和2，C2:D2横列也有数据1和2。我们想要横向数据与纵向数据交叉相加，显示在中间的二维值区域C3:D4。



混合引用公式

一般我们会在C3单元格输入公式：
=$B3+C$2

用B3+C2得到C3第一个结果，但是我们要想得到C3:D4所有的交叉相加结果，需要对B3混合引用“锁列不锁行”，需要对C2混合引用“锁行不锁列”，这样才能横向拖拽公式，纵向拖拽公式，填满C3:D4区域所有公式后得到所有结果。

C3=$B3+C$2=1+1=2
D3=$B3+D$2=1+2=3
C4=$B4+C$2=2+1=3
D4=$B4+D$2=2+2=4

虽然单个单元格公式书写比较直观，但这样做的话，有2个不舒服的地方：
一个就是需要熟练使用混合引用，另一个就是公式必须拖拽填充。



动态数组直接计算

我们还可以这样：

=B3:B4+C2:D2

利用Excel和WPS表格的动态数组特性，直接对两个区域：B3:B4列向量和C2:D2行向量进行运算，结果自动溢出为二维数组。
这样做不用填充公式，输入一次即可自动生成整个数组。数据源变化时，结果自动更新。

但是这样做非常依赖数据源区域的结构，无法直接定义复杂计算规则，比如条件判断、自定义函数等。


动态数组函数

MAKEARRAY函数的含义是：
按指定的行数和列数，动态生成一个数组，数组中每个单元格的值都由一个你自定义的LAMBDA计算函数来生成。

语法观察：

=MAKEARRAY(行数,

                       列数,

                             lambda(r,c,r与c计算逻辑)

                             )

行数：生成数组的行数，必须是正整数（如2表示2行）

列数：生成数组的列数，必须是正整数（如2表示2列）

其中第3参数：定义数组每个单元格计算规则的Lambda函数，包含3个要素：

lambda(r,

            c,

             r与c计算逻辑)

r当前单元格的行号（将行数从1开始自动逐个传入）；
c当前单元格的列号（将列数从1开始自动逐个传入）；

r与c的计算逻辑

所以我们可以这样输入公式：

=MAKEARRAY(2,2,LAMBDA(r,c,r+c))



目的是生成一个2行2列返回结果的数组区域。

2行：包含第1行和第2行，即将数值1和2逐个传递给r。
2列：包含第1列和第2列，即将数值1和2逐个传递给c。


2行2列数组的返回结果，它的执行过程：
第1行第1列（r=1,c=1）：1+1=2
第1行第2列（r=1,c=2）：1+2=3
第2行第1列（r=2,c=1）：2+1=3
第2行第2列（r=2,c=2）：2+2=4

最终生成的数组为：
{2,3;
 3,4;}

使用MAKEARRAY函数，可以支持复杂逻辑：如条件判断IF、循环MAP、多条件分支SWITCH等。可自定义任意计算规则，修改规则只需调整 LAMBDA函数的计算逻辑，无需批量修改单元格。可结合其他动态数组函数：如FILTER、SORT等实现更复杂的数据处理。


下面我们来看一个MAKEARRAY职场中更实用一点的案例，搞明白它是如何由拖拽法向动态数组公式法进阶的。

如下图所示：

A2:C7是数据源区域。E3:E4是已知条件“类别”，F2:G2是已知条件“规格”，我们想要通过这两个已知的条件查找二维区域中的“名称”。




拖拽法：

首先使用FILTER筛选函数：
=FILTER($B$3:$B$7,($A$3:$A$7=$E3)*($C$3:$C$7=F$2),””)

还在手动拖公式？makearray从单元格到数组思维…让你一个公式搞定整个报表
FILTER(要返回的数据, 筛选条件, 无匹配数据)

要返回的数据: $B$3:$B$7 (名称列)
筛选条件: (条件1) * (条件2)，乘号连接表示两个条件同时成立。
条件1: $A$3:$A$7=$E3 → 类匹配
条件2: $C$3:$C$7=F$2 → 规格匹配
无匹配数据：当匹配不到任何值时返回空值””。

FILTER当符合这两个条件时，返回$B$3:$B$7区域内对应的1个或多个名称值。



继续使用ARRAYTOTEXT函数：
=ARRAYTOTEXT(FILTER($B$3:$B$7,($A$3:$A$7=$E3)*($C$3:$C$7=F$2),””))

用ARRAYTOTEXT函数将数组转换为文本，转换结果可将数组中的多个元素用逗号连接后合并显示在单个单元格中。

至此，可以实现通过手动填充公式得到所有结果了。

但我们并不满足于此，想要通过动态公式，自动扩展填充结果。那我们就需要用到MAKEARRAY函数了。



动态数组公式法


众所周知，动态数组公式，不需要考虑混合引用和绝对引用的事儿。去掉所有绝对引用和混合引用。

原公式：
=ARRAYTOTEXT(FILTER($B$3:$B$7,($A$3:$A$7=$E3)*($C$3:$C$7=F$2),””))

去掉后公式：

=ARRAYTOTEXT(FILTER(B3:B7,(A3:A7=E3)*(C3:C7=F2),””))

1）引入动态行条件

完善FILTER的条件部分：

=ARRAYTOTEXT(FILTER(B3:B7,(A3:A7=INDEX(E3:E4,1))*(C3:C7=F2),””))

用INDEX获取行条件：
INDEX(E3:E4,1)
从条件区域E3:E4中取第1个值。



2）引入动态行列条件

继续完善FILTER的条件部分：

=ARRAYTOTEXT(FILTER(B3:B7,(A3:A7=INDEX(E3:E4,1))*(C3:C7=INDEX(F2:G2,,1)),””))

用INDEX获取列条件：

INDEX(F2:G2,,1)

从F2:G2中取第1个值。

至此，行列条件都从固定区域获取：
行条件：E3:E4的第1个
列条件：F2:G2的第1个

行列索引都写死了，都是“1”。

公式目前只能用于F3单元格，无法通过填充获得其他单元格的值。

所以我们构造：

LAMBDA(r,c,ARRAYTOTEXT(FILTER(B3:B7,(A3:A7=INDEX(E3:E4,r))*(C3:C7=INDEX(F2:G2,,c)),””)))

最后套上MAKEARRAY函数的壳子：

=MAKEARRAY(2,2,LAMBDA(r,c,ARRAYTOTEXT(FILTER(B3:B7,(A3:A7=INDEX(E3:E4,r))*(C3:C7=INDEX(F2:G2,,c)),””))))

将第一参数，行数2：其中的1、2分别传递给r

将第二参数，列数2：其中的1、2分别传递给c

实现行列索引的动态变化。

这样公式无需拖拽，结果自动扩展。




当然了，第一参数与第二参数也可以使用ROWS和COLUMNS获取：
=MAKEARRAY(ROWS(E3:E4),COLUMNS(F2:G2),LAMBDA(r,c,ARRAYTOTEXT(FILTER(B3:B7,(A3:A7=INDEX(E3:E4,r))*(C3:C7=INDEX(F2:G2,,c)),””))))


学习Excel/如果你没有天赋/那就一直重复/当你快到本能反应的时候/你的重复就是别人眼中的天赋/冲破捆绑/展翅翱翔/回顾关键内容/善用图片表达/学会建立联系/拓展深度广度/浓缩关键概念/应用到行动中/善于归纳总结/尝试进行分享

map遍历scan遍历reduce迭代pivotby降维

pivotby函数byrow函数groupby函数let函数

对单元格部分文字内容替换 格式更改←

借助这个函数！合并工作表简直太好用←

比vlookup还强大的动态随机查询←

根据起始序号与终止序号写入内容←

regexp正则提取：款式&尺码多组提取←

一个关于人口普查Excel户籍整理的问题←

一个关于人口普查Excel户籍整理的问题2←

regexp+vlookup根据简称查询全称←

regexp+countif+filter根据全称查找简称←

excel1秒批处理1000条混乱时间数据←

Regexp这个大哥级函数人狠话不多←

公式里常出现的”@”、”#”、”.”是干啥的←