filter - Excel 2017 公式 - 按月平均数据，同时可过滤
问题描述
我不是 VBA 编码器，如果可能的话，我更喜欢 excel 公式，最简单的解决方案将是最好的解决方案。
如您所见，我有很多可过滤的列。
我正在尝试检索 L 列的平均值，但我希望为 G3:R3 中的正确月份计算数据。
结果计算需要在过滤时在客户、站点、状态、工作类型等之间重新计算。
我在另一张表中引用生成的单元格，这可以让我了解我可以浏览的趋势，因为在每张表中按月过滤不是一种选择。
=AVERAGE(IF(MONTH(E9:E1833)=1,(J9:J1833)))
这个不更新过滤数据。
=SUM(IF(MONTH(E9:E1833)=1,J9:J1833,0)) /SUM(IF(MONTH(E9:E1833)=1,1))
这个不更新过滤数据。
我尝试了 5 种不同的 SUBTOTAL 公式，其中一些带有 OFFSET，这些公式都不会产生与手动检查时相同的结果。
每个工作表有超过 1,500 百行，最大的是 29148 行。数据可以追溯到 2005 年。
请问有人可以帮我找到解决方案吗？
解决方案
一种可能的解决方案是创建一个辅助列，1如果行可见则返回0，如果行不可见（或空白）则返回。这使您的公式更加自由。
例如，如果您想在 column 中创建一个辅助列，请在X单元格中键入X9并向下拖动：
= SUBTOTAL(103,A9)
现在您可以创建自定义平均公式，例如：
= SUMPRODUCT((MONTH(E9:E1833)=1)*(X9:X1833)*(J9:J1833))/
SUMPRODUCT((MONTH(E9:E1833)=1)*(X9:X1833))
不完全漂亮，但它完成了工作。（请注意，这是一个数组公式，因此您必须在键盘上按Ctrl+ Shift+ Enter，而不是Enter在键入此公式之后。）
使用更多的辅助列，您可以SUMPRODUCT完全避免，只需通过一个AVERAGEIFS.
例如，如果您在单元格中键入Y9并向下拖动：
= MONTH(E9)
那么你的公式可能是：
= AVERAGEIFS(J9:J1833,X9:X1833,1,Y9:Y1833,1)
如果没有至少一个辅助函数（如果您想避免使用 VBA），则没有一种干净的方法可以做到这一点。