Excel公式练习：查找每行中的最小值并求和（续）

引言：本文的练习整理自chandoo.org。多练习，这是我们从小就在使用的学习方法。在练习的过程中，认真思考，不断尝试，以此来磨练自己的公式与函数应用技能，也让研究Excel的大脑时刻保持着良好的状态。同时，想想自己怎么解决这个问题，看看别人又是怎样解决的，从而快速提高Excel公式应用水平。

在《Excel公式练习：查找每行中的最小值并求和》中，我们提供的示例数据每行只有2列，如果数据有3列，又如何求每行最小值之和呢？

本次的练习是：如下图1所示，求每行最小值之和。

图1

示例数据中结果为：3+8+7+10+6+5+2+7+3+2=53

同样要求不能使用易失性函数。

请写下你的公式。

解决方案

公式1：《Excel公式练习：查找每行中的最小值并求和》中的公式5可以应用到3列：

=SUM(LARGE(A1:C10,MOD(LARGE(ROW(A1:C10)*10^6+RANK(A1:C10,A1:C10),(ROW(A1:C10)-MIN(ROW(A1:C10)))*COLUMNS(A1:C10)+1),10^6)))

这是一个数组公式，可以应用到更多的列。下面详细解析这个公式的运行原理。

LARGE函数将一维区域/数组和多行多列区域视为相同，了解这一点是理解这一公式的关键。

实际上，如果我们可以将包含多行和多列的二维区域转换为仅包含一列的一维区域，则可以按如下方式重新定义任务：给定一个单列区域，我们是否可以确定应该查看哪些索引，以便获得每行中的最小数？

首先，假设我们有一个单列区域，比如A1:A10，找出每行中的最小值是显而易见的，只是获取每一值本身！

假设现在我们将区域扩展到两列：A1:B10。要找出每行中的最小值，如果我们将两列区域转换为具有两倍原始行数的单列区域，就不那么容易了。上面的公式告诉我们，我们需要从20个元素范围中获取以下值：

{19;18;11;19;14;5;4;8;8;17}

即使我们将问题扩展到两列以上，原理仍然相同。

那么这是如何工作的呢？

为了直观地解释这一点，我在第G列和第H列中插入了RANK函数。RANK函数也LARGE函数一样，处理一维和二维区域。

在列G和列H中，可以看到上面数组中给定的值已按条件格式化，如下图2所示。这清楚地表明，LARGE函数返回的是每行中的最大的秩。

图2

不要被误导！

RANK函数给出的最大值（省略第三个参数）对应于区域内的最小值！

因此，实际上，通过查看由RANK函数形成的数组中的最大秩值，我们能够提取原始区域中的最小值。

但问题是，仅看最大的秩值是不够的！我们需要查看每行中的最大秩值。因此，使用了ROW函数。

如果我们只看ROW函数，并将其与RANK函数相结合，那么这两个值恰好位于同一区域，因此，将两者结合起来将不可避免地改变总体值，使我们无法在后面检索RANK值。

因此乘以10^6。之所以使用10^6这个值，是因为考虑到原始数据集中的数值较小，使用10^6作为乘数似乎是安全的。如果数据集中的数字恰好也是如此，那么这个数字就必须增加。

让我们更详细地了解一下它是如何工作的。

RANK函数应用于整个范围A1:C10时，返回以下值矩阵：

{23,26,27;

11,8,6;

8,11,15;

6,5,3;

3,8,20;

23,20,15;

11,29,20;

15,15,15;

27,2,1;

29,23,11}

现在，让我们将其与ROW函数结合起来，如下所示：

{1000023,1000026,1000027;

2000011,2000008,2000006;

3000008,3000011,3000015;

4000006,4000005,4000003;

5000003,5000008,5000020;

6000023,6000020,6000015;

7000011,7000029,7000020;

8000015,8000015,8000015;

9000027,9000002,9000001;

10000029,10000023,10000011}

使用ROW函数可自动分隔各行值到各自区间中，即前三个值位于下面区间中：

1000000到1000099，假设原始数据集的值在0到99之间。

数组中接下来的3个值位于下面区间中：

2000000至2000099

接下来的3个值位于：

3000000至3000099

对于数据区中的其余行，依此类推。

如果我们现在对这些组合值使用LARGE函数，很明显，最后一行（第10行）中的3个值将位于结果数组的顶部；接下来是第9行中的3个值，然后是第8行中的3个值，依此类推，直到最后3个元素成为第1行中的3个值。

请记住，所有这些值都将按降序排列。

因为我们每行有3个元素，所以我们所要做的就是从第一个元素开始逐个到第四个元素！

所以，如果我们考虑以下元素：

1 , 4 , 7 , 10 , 13 ,...., 25 , 28

我们将能够提取每行中的最大秩值；显然，这些将对应于每行中最小的数据值！

因此，剩下的就是导出一个值数组，如下所示：

{1;4;7;10;13;16;19;22;25;28}

这正是公式的以下部分所做的：

(ROW(A1:C10)-MIN(ROW(A1:C10)))*COLUMNS(A1:C10)+1

其中，ROW(A1:C10)-MIN(ROW(A1:C10))返回：

{0;1;2;3;4;5;6;7;8;9}

再乘以列数，即3，得到数组：

{0;3;6;9;12;15;18;21;24;27}

再加上1，即得到我们想要的数组。

稍等，总结一下我们到目前为止所讲解的：

1.使用RANK函数返回值矩阵，按以下顺序对原始数据进行排序：原始数据集中的最大值分配秩1，原始数据集中的最小值分配秩30。

2.将其与ROW函数结合，乘以足够大的数字，使RANK值即使在组合后也不会改变。使用ROW函数可自动确保结果值按行分组，从而更容易提取行最大值。

3.从第一个值开始，通过查看数组中的每n个值来提取行最大值，其中n是原始数据集中的列数。

我们现在的数组，如下所示：

{10000029;9000027;8000015;7000029;6000023;5000020;4000006;3000015;2000011;1000027}

我们感兴趣的值仅为以下值：

{29;27;15;29;23;20;6;15;11;27}

上述数值代表什么？

值29表示秩数组中的最大值。没有值80，因为有两个值为29的秩，因为原始数据中最小的值2有2个。因为RANK函数从秩1开始（对于最大的数据值），当它向下移动数据集时，分配更高的秩值，当涉及到重复时，它将相同的秩分配给相同数据值的所有重复实例，然后在将下一个秩分配给数据集中下一个较小的值时跳过秩。

提取上述秩值很简单，使用MOD函数，与之前使用的乘数值相同。

剩下的就是使用这个最终的秩数组作为LARGE函数的第二个参数，而原始数据集作为第一个参数。因此，公式转换为：

=LARGE(A1:C10,{29;27;15;29;23;20;6;15;11;27})

返回值数组：

{2;3;7;2;5;6;10;7;8;3}

这是每行数据中的最小值，从第20行的最小值2开始，第19行的最小值3，依此类推，直到第一行的最小值3为止。

有兴趣的朋友，可以参照上面的讲述，理解下述公式。

公式2：数组公式。

=SUM(MOD(LARGE(MAX(A1:C10)*ROW(A1:C10)+A1:C10,ROW(A1:C10)*COLUMNS(A1:C10)),MAX(A1:C10)))

公式3：数组公式。

=SUM(MOD(LARGE(((ROUNDUP(MAX(A1:C10),0)-MIN(A1:C10,0))*ROW(A1:C10)-MIN(A1:C10,0))+A1:C10,ROW(A1:C10)*COLUMNS(A1:C10)),(ROUNDUP(MAX(A1:C10),0)-MIN(A1:C10,0))))+MIN(A1:C10,0)*ROWS(A1:C10)

公式4：

=SUMPRODUCT(HLOOKUP(1,FREQUENCY(ROW(A1:C10),ROW(A1:C10)+PERCENTRANK.EXC(A1:C10,A1:C10,6)),TREND(2*COUNT(A1:C10)+1^A1:C10,,,0)/3),A1:C10)

越来越有趣了！