EXCEL中返回多条件组合下，出现最多的值？

来源：蜘蛛抓取(WebSpider) 时间：2022-07-06 02:57 标签： if多个条件返回相应的值

导语：继续研究来自于excelxor.com的案例。这个案例很复杂，但解决方案却很精彩，值得好好研究。建议结合本文参阅原文，会有更大的收获。

本次的练习是：如下图1所示，在一个4行4列的单元格区域A1:D4中，每个单元格内都是一个一位整数，并且目标值单元格（此处为F2）也为整数，要求在单元格G2中编写一个公式返回单元格A1:D4中四个不同值的组合的数量，条件如下：

1. 这四个值的总和等于F2中的值

2. 这四个值中彼此位于不同的行和列

下图2是图1示例中满足条件的6种组合。

先不看答案，自已动手试一试。

在单元格G2中的数组公式为：

本案例的条件是：在所给定的单元格区域中选择四个单元格，其和等于目标值，并且这四个单元格彼此都不同行同列。这就意味着，结果在下列组合中产生：

共有24种组合。组合数为n!，对于4行4列为4!=24。

现在，看看上面给出的那24个和，可以推断出它们可以使用OFFSET获得，即：

关键是，参数cols固定为数组{0,1,2,3}，显然意味着四个元素组合中的每个都将分别来自四个不同列，然后变换传递给参数rows的数组，即满足确保没有两个元素在同一行的条件的所有可能排列。因此，这24个rows参数传递给OFFSET时将产生与先前给出的24个和相等的结果，即集合{0,1,2,3}的24个可能的排列，即：

现在，我们有24个单独的和要计算。然而，我们不仅限于将一维数组传递给OFFSET函数：如果我们能够以某种方式生成一个数组，该数组由上述四个元素组成的所有数组组成。该数组如下：

一个由24行4列组成的数组，其中的每一行等于上面给出的24种排列之一，然后将其传递给OFFSET函数，实现对所有24个数组的同时处理。

实际上，这也是为什么有些解决方案将定义排列为：

的原因。然后，进一步操作该数组以获取传递给OFFSET函数的矩阵。

可是，尽管这样确实可以提供我们所需要的结果，但我们还是希望能够动态生成这样的数组。因为如果案例扩展到5行5列或6行6列，那么矩阵元素会大幅增长，手工构造排列就不可取了。

不幸的是，在Excel中生成这种排列的数组绝非易事。在Excel中生成大型数组唯一现实的方法是通过使用ROW函数的公式构造。但是，这不仅限制了结果数组的大小（我们至少不能生成比工作表中的行数即1,048,576多的元素的数组），而且意味着，取决于我们所需的输出，最终可能想要比预期更多的元素。

虽然我们可以将诸如SMALL之类的函数与其他一些函数例如LARGE、FREQUENCY或MODE.MULT一起使用，返回一个大小与传递给函数的大小不同的数组，但是通常根本没有必要将数组缩减到这样的程度：可以简单地将大数组传递给IF语句，排除无关的元素。这就是本案例使用的技术。

首先，获取传递给OFFSET函数作为参数rows的排列数组，即公式中的：

虽然会在一开始就生成一个比必需的值大得多的数组，但是由于存在最小和最大的返回值，因此可以将数组大小进行限制。本例中，我们感兴趣的将是1234和4321（实际上我们最终需要的是0123和3210；但是，如果将0123传递给ROW函数，将被解释为123，因此我们的计算将是比目前更大的数组。以这种方式获得1234、1243、1324等，然后在公式的后面将它们处理为0123、0132、0213等。）

处理至少比将整列的值（即1:1048576）传递给ROW要占用更少的资源。因此：

将生成由1234至4321范围内的整数构成的数组：

然后测试数组中每个元素是否都包含数字1、2、3、4：

将产生一个3088行4列的数组，其12352个元素将是对上述数组的所有3088个元素分别查找1，2、3和4的结果：

由于仅对具有由四个非错误值组成的行感兴趣，因此首先将此数组传递给ISNUMBER并强制将结果布尔值TRUE/FALSE转换成为数字，从而：

上面的数组传递给MMULT作为第一个参数，其第二个参数为{1;1;1;1}，这样：

数组中的4表明原来的ROW函数生成的值中分别包含1、2、3、4各一个，将该数组与4进行比较：

数组中标红的TRUE值与ROW生成的原数组中的1234、1243、1324相对应。

现在，对于将在公式的IF语句中生成TRUE的24个值（1234、1243、1324等）中的每一个，提取一个由这四个数字组成的数组（其每个数组为{1,2,3,4}、{1,2,4,3}、{1,3,2,4}等）传递给OFFSET函数。使用MID函数来实现，其参数start_num指定为{1,2,3,4}：

由上文得出的结果，可知：

这个数组为OFFSET函数的rows参数值，而先前已讨论过，其cols参数值为{0,1,2,3}，因此：

传递给IFERROR函数，这样：

数组中红色数字分别对应着执行下面的公式操作：

接着使用MMULT对已经生成的数组矩阵中的每行求和，因此：

然后，与单元格F2中的目标值比较：

现在，我们来看一下减少数组元素的方法。上文中，由

生成了含有3088个元素的数组，而我们只对其中的24个元素感兴趣，也就是由1234进行排列的元素，因此与我们无关的有3064个元素，占总元素的99%以上。

这里首先将这个数组减少到只有256个元素，这样无关元素比提高至了91%。采取下面的公式构造：

这种构造背后的原理是Excel与数学的综合。如果我们减少元素的数量，只考虑3×3的类似构造，将更容易解释，也更容易理解。此时，上面的公式构造等价于：

暂时不考虑TEXT和MMULT函数，此公式构造的关键是生成一个由三个整数组成的数组，包含数字0、1和2的所有可能排列。即：

这是上述构造中取27的原因。对于4个元素取256，因为n的元素的可能排列数为n^n，所以3^3=27，4^4=256。

将转换成的数组是什么呢？

实际上，我们在这里所做的就是将一系列以10为底的值转换为以3为底的值。因此，以10为底的0到26之间的值，我们可以用3的底数表示的等效表为：

这正是我们要生成的27个排列。

对于以10为底的给定值，为了确定该值的以3为底的表示形式中存在的3^2、3^1和3^0的数量，首先确定3^2、3^1和3^0的以基数为10的值，然后对所得值取模（模为3）。例如，以10为底的值7，以3为底的值的表示形式为021，由于3^2=9在7中出现0次且MOD(0,3)=0，3^1=3在7中出现2次且MOD(2,3)=2，3^0=1在7中出现1次且MOD(1,3)=1。这意味着：

然后，使用MMULT和合适的第二个数组将该数组合并为10为底的值：

最后，使用TEXT函数格式化以生成所需要的排列：

1.找到规律，然后寻求解决之道。

2.不仅要理解Excel函数原理，而且要打好数学基础，这是灵活应用公式的一切。

我要回帖

更多关于 if多个条件返回相应的值 的文章

 

随机推荐