excel - 在Excel数组公式中使用MIN/MAX

Question

我在 Excel 中有一个简单的数组公式，但它无法按我希望的方式工作。 A 列和 B 列中有数据(A1 与 B1 配对，依此类推)，而 F 列中有基于 E 列参数的计算。

在单元格 F1 中，公式为:

{=SUM(MAX(A$1:A$9, E1)*B$1:B$9)}

这个公式的作用是:

=MAX(A$1:A$9, E1)*B$1 + MAX(A$1:A$9, E1)*B$2 + ...

相反，我需要一个可以执行此操作的公式:

=MAX(A$1, E1)*B$1 + MAX(A$2, E1)*B$2 + ...

换句话来说，我写的公式(第一个)总是找到 A1 到 A9 的值与 E1 之间的最大值，将其乘以第 i 个 B 值并对结果求和。我需要的是一个公式，可以找到第 i 个 A 值和 E1 之间的最大值，而不是所有 A 值之间的最大值。

我正在寻找的内容很容易通过在 C 列中添加公式 =MAX(A1;E$1)*B1 然后在 F1 中添加 =SUM(A1:A9) 来轻松完成，但我无法使用此解决方案因为在 F 列中重复了相同的公式，而 E 参数每次都在变化。

我可以使用 IF 指令:在 F1 中我可以编写

{=SUM(IF(A$1:A$9>E1, A$1:A$9, E1)*B$1:B$9)}

虽然这个公式可以满足我在这种情况下的需要，但我认为这是一个糟糕的解决方案，因为我发现它很难阅读和扩展。例如，如果 D 列中还有另一个参数，并且因子为 MIN(MAX(A$1:A$9;E1);D1)，则使用 IF 将导致公式非常长且非常难以阅读和复杂。

对于我的问题有更好的解决方案吗？谢谢大家!

注意:语法可能略有不同，因为我使用的是意大利语版本的 Excel。

Answer 1

问题在于 MAX 将数组作为参数。通常采用数组的函数永远不会返回数组 - 它们被设计为将数组转换为一个数字。无论您向 MAX 抛出多少个数组，它始终只会返回一个数字。

我无法想出一个好的解决方案，所以这是一个糟糕的解决方案

=SUMPRODUCT(((A1:A9*(A1:A9>E1))+(E1*(A1:A9<=E1)))*B1:B9)

我认为这并没有真正提高您试图避免的基于 IF 的公式的可维护性。我认为您被 IF 或辅助列困住了。