问与答81： 如何求一组数据中满足多个条件的最大值？

Q：在工作表中有一些数据，如下图1所示，我想要获取“参数3”等于“A”、”参数4“等于”C1“对应的”参数5”中的最大值，能够使用公式解决吗？

图1

A：这种情况用公式很容易解决。

首先，我们将单元格区域B2:B12命名为“参数1”，C2:C12命名为“参数2”，D2:D12命名为“参数3”，E2:E12命名为“参数4”，F2:F12命名为“参数5”。

在单元格F13中输入数组公式：

=MAX(IF((参数3=D13)*(参数4=E13),参数5,0))

记得按Ctrl+Shift+Enter组合键完成输入。

我们看看公式中的：

(参数3=D13)*(参数4=E13)

将D2:D12中的值与D13中的值比较：

{"A";"B";"A";"B";"A";"A";"B";"A";"B";"A";"A"}=”A”

得到：

{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}

将E2:E12中的值与E13中的值比较：

{"C1";"C2";"C1";"C1";"C1";"C2";"C2";"C1";"C2";"C2";"C1"}=”C1”

得到：

{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

将上面生成的两个中间数组相乘：

{TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}*{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

得到数组：

{1;0;1;0;1;0;0;1;0;0;1}

其中的1代表同一行的列D和列E中包含“A”和“C1”。

将上面的数组代入IF语句：

=MAX(IF({1;0;1;0;1;0;0;1;0;0;1},参数5,0))

转换为：

=MAX(IF({1;0;1;0;1;0;0;1;0;0;1},{0.08;2.389;0.198;0.128;0.019;0.491;0.168;0.545;1.45;0.034;0.246},0))

转换为：

=MAX({0.08;0;0.198;0;0.019;0;0;0.545;0;0;0.246})

即由同一行的列D和列E中包含“A”和“C1”对应的列F中的值和0组成的数组，取其最大值就是想要的结果：

0.545

本例可以扩展到更多的条件。例如，在上述条件基础上，要求“参数1”为“M-I”、”参数2”为 M-IA”，可以使用数组公式：

=MAX(IF((参数1=B13)*(参数2=C13)*(参数3=D13)*(参数4=E13),参数5,0))

可以看到，返回值为0.198，如下图2所示。

图2