excel公式根据条件过滤范围

Question

我有一个奇怪的问题，在过去的几个小时里我一直在努力寻找一个好的解决方案。我在 Excel 中有一个类似于以下的表格:

     |A     B     C     D     E     F
-----+-----+-----+-----+-----+-----+-----
1    |2011  1     0    -5     4     2
2    |2011  2     1     1.2   2     3
3    |2011  3     1     2     4    -4
4    |2011  0     1    -3     3    -2
5    |2012  5     1     0     0     1
6    |2012  0     1     0.25  0     0

数据根据 A-C 列分为不同类别，D-F 列是美元金额。

我想弄清楚的是，有没有办法使用 A-C 列中的值作为过滤器值来返回这些行的子集 D-F 列的最大绝对值？现在，我知道如何返回范围的最大绝对值:=max(abs(START:END))，但我不知道如何过滤掉一些行基于其列 A-C 值的范围。

也许是我正在寻找的具体示例:

我如何获得范围 D-F 其中:

• A=2011
• B<>0
• C=1

对于上述一组条件和数据，我希望返回的答案是 4，来自 E3。

我并不反对在合理的范围内添加辅助列，但是还有更多的过滤条件集，这导致了我拼凑的其他一些解决方案的失败； 16-32 辅助列不会被我试图为其开发的小组接受。

Answer 1

如果您的excel版本支持较新的MAXIFS function ，将其与 ABS 功能一起使用和 MINIFS function .

=max(maxifs(d:f, a:a, 2011, b:b, "<>", c:c, 1), abs(minifs(d:f, a:a, 2011, b:b, "<>", c:c, 1)))

如果没有，那么 AGGREGATE 将提供条件。

=AGGREGATE(14, 6, ABS(D1:F6)/((A1:A6=2011)*(B1:B6<>0)*(C1:C6=1)), 1)