excel - 了解 Excel 如何处理数组函数

Question

这个问题是基于我回答的别人的问题。可以找到原问题here ，如果需要的话。 (而且，也许可以提供比我更好的答案。)但是，回答这个问题所需的一切都在这里。

背景

鉴于 A1:C5 中的以下数据

+-------------+--------+----------+
| Invoice Nbr |  Type  |  Status  |
+-------------+--------+----------+
| A0001       | Credit | Paid     |
| A0002       | Credit | Not Paid |
| B0001       | Debit  | Paid     |
| B0002       | Debit  | Not Paid |
+-------------+--------+----------+

目标是使用计算两列的数组公式在表中查找特定值。也就是说，给我第一个发票(A 列)，其中类型为借方，状态为未付款。

我的第一个答案是尝试解决我认为 OP 条件的问题。我将 AND 包装器放在两个条件周围，如下所示:

{=INDEX($A$2:$A$5,
        SMALL(IF(AND($B$2:$B$5 = "Debit", 
                     $C$2:$C$5 = "Not Paid"),
                 ROW($A$2:$A$5)-ROW($A$2)+1),
              1)
       )}

但是，这不起作用。

我最终建议了这个答案，因为它确实有效:

{=INDEX($A$2:$A$5,
        SMALL(IF($B$2:$B$5 & $C$2:$C$5 = "DebitNot Paid",
                 ROW($A$2:$A$5)-ROW($A$2)+1),
              1)
       )}

我的问题

Excel 中的数组公式有时对我来说太神秘了。 似乎任何一个都应该提供结果B0002。事实上，只有第二个公式才能给出所需的结果。 Excel (2013) 遵循什么原则或评估流程才能做到这一点？或者，换句话说，我对 Excel 如何管理数组公式有什么不理解的地方？

Answer 1

@StoneGiant 是对的。有些函数不能在数组公式中使用，因为它们唯一的“存在原因”是将数组转换为标量值。有时您实际上希望这些函数返回数组公式内的单个值，那么公式执行算法如何知道您想要哪种方式。

AND() 和 OR() 就是很好的例子。 =AND(值数组) 返回单个值。 MIN() 是另一个例子。取数组公式

=SUM((A1:A10="鲍勃")*(最小值(B1:B10))

MIN() 应该接受一堆值并返回最低的值。如何编程 MIN() 返回一个数组？您在什么情况下决定什么是最低的？你不能。这就是它在数组公式中不起作用的原因。

接下来采用SMALL()。它可以返回一个数组公式，因为它不仅应该返回最低值，而且应该返回第n个最低值。由于第 n 个参数，您现在可以返回一个数组

=SMALL(A1:A10,{2,3})

这将返回一个包含两个元素的数组。 MS 可以进行小型编程，使其不能用作数组公式，但总的来说，我发现它们在任何实用的地方都包含了数组功能。

我说过 AND() 和 OR() 是很好的例子。 MS 可以对它们进行编程以返回一个像@StoneGiant 的评论那样的数组。这对我来说是一个奇怪的设计决定，但他们本来可以做到的。但他们在数组公式中已经有了 AND 和 OR。 AND 相乘，OR 相加。

=MAX((B2:B5="Debit)*(C2:C5="Not Paid")*(ROW(B2:B5))

这将返回借方但未支付的最后一行的行号。 (您的示例问题想要第一个，但 MIN 引入了使该示例复杂化的问题)。等式之间的乘法与 AND 相同。如果您希望最后一行是借方，并且 C 列显示“未付款”或“不确定”，您可以通过添加来实现。

=MAX((B2:B5="Debit)*((C2:C5="Not Paid")+(C2:C5="Not Sure"))*(ROW(B2:B5))

加号是这两组括号之间的 OR。该 OR 的结果作为 AND(相乘)包含在其他括号中。

您可以将它们放入索引

=INDEX(A2:A5, MAX((B2:B5="Debit)*(C2:C5="Not Paid")*(ROW(B2:B5)))

因此，为了回答您的问题，我认为:一般来说，旨在将一堆值转换为一个值的函数在数组公式中不起作用。 AND() 和 OR() 有点特殊，因为加法和减法已经在数组公式中执行此操作。

在此处查看有关数组的更多信息 http://dailydoseofexcel.com/archives/2004/04/05/anatomy-of-an-array-formula/