excel - Excel 2016 中与特定月份相关的数据的小计平均值

Question

我有一个很大的电子表格，我正在努力获得一个平均值，到目前为止，我从谷歌或其他地方使用过的任何公式都没有奏效。

平均值是时间的平均值，特别是开始时间和结束时间之间的小时数，转换为天数。

在 E9:E1833 列中，我的开始时间为 dd/mm/yyy hh:mm

在 J9:J1833 列中，我有以天为单位的时间，从开始到结束为 [h]:mm(有些是空的)

我有一个自动过滤器设置来改变工作类型/区域/状态等，结果需要随着数据的过滤而改变。

以下公式有效，但不会重新计算:
{=AVERAGE(IF(MONTH(E9:E1833)=1,(J9:J1833)))}
以下公式有效，但结果不正确:
=AVERAGE(IF(MONTH(E1833)=1,SUBTOTAL(101,(J9:J1833))))
我尝试过 Offset，结果相似，许多“现成”解决方案产生 #Div/0 , #REF , #Value或完全不允许进入。

任何帮助将不胜感激，最好不要在 VBA 中，因为我不想永远被这个电子表格所困扰。提前致谢。

Answer 1

由于您要进行过滤，并且希望在过滤时改变结果，因此只需使用 AGGREGATE功能:

=AGGREGATE(1,3,J:J)

编辑 :为回答问题添加了动画

值列中的值可以是时间，尽管您可能必须格式化 AGGREGATE 的结果。函数(单元格/数字格式)将结果显示为 [h]:mm
注意:GIF 将播放两次。要重复，请刷新页面