免辅助列的利器：mmult和多维引用

你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天通过几个简单的题目来分享一下免辅助列的方法。虽然题目很简单，你也一定会做，但是如果增加要求的话，简单的题目也会变得不简单，也会有你未曾触碰的存在。

1.求每一行的和

计算A2:D7这个区域中每一行的和，结果如E列所示。对于这个问题，我相信每个小伙伴都会。在E2单元格输入公式=SUM(A2:D2)，向下填充。

现在来增加一点难度，我们需要将得到的这些和放在一个内存数组中，方便后续的运算，这样可以免去辅助列。如果这样说你不理解，下面举个例子。

假如现在有这么一个要求，先算出每一行的和，然后再从这些和中取出最大值。通常是用下图所示的方法，先用sum求和，然后用max取最大值。这样就用到了辅助列，不能一步直接得到结果。

我们希望不用辅助列，一步得到结果。这时就需要用到免辅助列的方法，常用的免辅助列方法有mmult和多维引用，当然还有其他函数的数组用法也可以免辅助列。今天主要分享多维引用和mmult。

如果你的数据源是单元格区域引用，那么可以考虑用多维引用。比如上面的问题可以用下图的公式直接得到答案。其中subtotal函数得到的结果和辅助列E列的结果是一样的，只不过存放在内存数组中，这部分是我们学习的关键，最后用max取出最大值就ok了。

OFFSET(A1:D1,ROW(1:6),)这部分从A1:D1开始，分别向下偏移1,2,3,4,5,6行，得到由A2:D2、A3:D3、···、A7:D7共6行组成的多维引用，注意这6行是相对独立的，它们在不同的平面上。

SUBTOTAL(9,OFFSET(A1:D1,ROW(1:6),))这部分用subtotal对多维引用的每一行分别求和，得到6个值，存放在内存数组中。想要把这6个值输出到单元格中，相应地也要选中6个单元格，且方向也要一样。然后输入公式，按ctrl+shift+enter。如下图F2:F7所示。

得到了这个内存数组，后续求最大值，还是求平均值就简单了。这样就免去了辅助列。

如果你的数据源不是单元格区域，而是一个数组，那么就不能用多维引用了，可以考虑用mmult。假如下图的A2:D7不是单元格区域，而是由函数产生的二维数组，那么可以用mmult对每一行求和。公式如下图所示。

mmult的用法不再重复说明，可以回顾之前的文章《初识矩阵乘积函数mmult的原理和基本用法》。

上面举例的那个问题，也可以用下图的公式一步完成，而且不用按三键。

2.求每一行的最大值

计算A11:D16这个区域中每一行的最大值，直接在E11单元格输入公式=MAX(A11:D11)，向下填充。这个不会的打屁屁。

用多维引用来生成内存数组，选中F11:F16，在编辑栏输入下图的公式，按ctrl+shift+enter输出结果。和第1题的公式基本一样，只有subtotal的第1参数变为4。

用mmult来完成，嗯？这个目前还不会哈。可以用矩阵求最大值法来完成。选中G11:G16，在编辑栏输入下图的公式，按ctrl+shift+enter输出结果。

这个方法之前说过一次，今天再来简单说一下。下图的公式得到灰色区域的结果，也就是第①步的结果。简单来说，就是给A11:D16这个区域的第1行加100，第2行加200，···，第6行加600。

第②步的结果，是从第①步的结果中取出第4,8,12,16,20,24个最小值得到的，公式为=SMALL(ROW(1:6)/1%+A11:D16,ROW(1:6)*4)。可以看到K列的每个结果刚好是灰色区域每一行的最大值，这部分你可以好好琢磨一下。

最后把K列的结果除以100取余数就得到了我们想要的结果。

3.求每一行正数的乘积

计算A20:D25这个区域每一行的乘积，常规做法，在E20单元格输入公式=PRODUCT(A20:D20)，向下填充。

用多维引用生成内存数组，选中F20:F25，在编辑栏输入下图的公式，按三键。subtotal的第1参数6代表product。

用mmult+对数函数log得到内存数组，选中G20:G25，输入下图的公式，按三键。想要理解这个公式，需要对数的一些知识。并且这个公式只能对正数求乘积。

我在网上找了几个对数的公式，主要用到第1个和第3个。感兴趣的小伙伴可以研究下。

下图是一个运算过程图，方便大家理解。第①步，用log将二维区域转为对数的形式。第②步，用mmult对第①步结果的每一行相加，根据上图第3个公式，可以转为相乘。第③步，将第②步的结果作为10的n次方，根据上图第1个公式，可以得到最后的结果。

还可以用ln+mmult+exp来生成内存数组，如下图所示。

链接：

https://pan.baidu.com/s/1gK4fGQdF00_7TsnKwxOQnA

提取码：cl7j