|
这是一个难题！ 数组之数组，顾名思义，就是一个数组的元素是另外一个数组。很多现实问题，本来有巧妙地解法，但是因为Excel不支持数组之数组，导致这些问题根本解决不了。 其实，Excel本身是可以进行这样的计算的，只不过需要另外的技巧。这里我们介绍一个非常特殊的公式，利用这个公式，可以解决数组之数组的难题 下图展示了什么是数组之数组：
公式1，查找的结果是一个值"a"。 公式2，返回A，B两列，所以查找值变成了一个数组{1, "a"}。 公式3，由于查找条件成为一个数组，{1;2}，所以返回结果也是一个数组：{"a"; "b"}。 来到公式4， 这里条件是一个数组，返回值是A，B两列，所以，我们期望返回结果是一个两行两列的数组：{1, "a"; 2, "b"}。 我们期望的实际上就是数组之数组：因为有两个条件，所以返回一个两行的数组，因为返回两列，所以每行都是一个1行两列的数组。 但是，Excel不支持数组之数组，我们只能得到第一列，即{1; 2}。 我们可以猜想，毕竟XLOOKUP函数是VLOOKUP函数的升级，它们的工作原理基本没有变化，本身不能支持循环处理数组的元素。 但是，我们有一批新函数：MAP，REDUCE，SCAN等等，这些函数可以进行循环，单独处理其中的每个元素。既然如此，我们就可以从它们入手找找解决办法。 先试试这个公式： 这个公式可以用下面的图示说明：
MAP将条件数组映射成一个结果数组（3行），每一个元素映射规则是使用XLOOKUP进行查找，返回一个1行两列的数组。 但是这个函数失败了：
失败的原因还是“数组之数组”。 为了挽救我们的尝试，我们需要一个神奇的公式： 这是一个嵌套的LAMBDA公式（详情参见这里）。这个公式的工作原理见下图：
输入参数x后，该公式返回一个LAMBDA函数，该函数没有参数，只是简单地返回x。 用这个公式创建自定义函数后： 调用是这样的：
第一个括号里可以任意写，可以是一个值，也可以是个数组，整个公式会返回第一个括号里的内容。 不要忘了第二个括号。 这个自定义函数的作用是将x的值存放在一个未知的"位置"中，等待第二个括号时，将其返回！ 现在我们进行第二次尝试： 作为对照，我们看看第一次尝试的公式： 这里，将每一次映射的XLOOKUP公式返回的结果放在FUNC函数的第一个括号里。 明白这个意思吧。 既然，数组的元素不能是数组，那么我们将其放在一个自定义函数里总可以吧，一个自定义函数作为数组的元素，这是允许的！ 我们再看现在的返回值：
仍然是同样的错误！ 不过有一点区别，从一个错误变成了一个错误数组了！ 这是进步，表示计算了三行，MAP的功能实现了！ 再看每个错误值：
错误解释变成了“单元格包含LAMBDA”！ 什么意思？ 这个意思是说产生错误的这个单元格中有一个LAMBDA函数没有参数。 曙光乍现。 现在很明白了，就是每个单元格代表一个LAMBDA函数，但是这个LAMBDA函数没有最后一个括号。 我们使用这个公式： 其中的MAP公式就是前面介绍的公式。但是，我们使用INDEX将其第二行，第一个元素取出。 这就是2这个条件，用XLOOKUP查找出的一行两列数组。不过我们将它放在了FUNC函数中。但是这个FUNC函数返回的是一个LAMBDA公式，所以需要参数。因此，后面要加上一个()。 结果就是
这是我们期望的结果。 这说明，这个公式中每一个返回错误值的元素，实际上都可以用同样的方法返回一个数组。 有了这个基础，返回数组之数组就不在话下了。 甚至，我们可以做到其中的每个数组都是不同大小的。
假设，我们需要将左边的A1:A3做映射，其中将1，映射成1行，将2映射成两行，将3映射成3行。 这里扫描原始数据，每一个元素都映射成一个n行数组，n是原始数组中循环到的当前元素值。 但是，我们知道，这个公式肯定会返回一个错误值。 所以，我们将它处理下： 显然，这个函数一定可以计算出每行的数组，只不过我们需要将它取出，并通过函数运算释放这个数组： 这个公式取出第三个元素映射成的数组，当然是三行一列的数组：
详细解释请看视频  加入E学会，永久免费学习更多Excel应用技巧 http://www./portal/learn/class_list Excel+Power Query+Power Pivot+Power BI 自定义函数 底部菜单：知识库->自定义函数 面授培训 底部菜单：培训学习->面授培训 Excel企业应用 底部菜单：企业应用 也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。 |
|
|
