我有 2013 年 1 月到 2017 年 10 月的月度数据
MONTH MY DATA
Jan-13 45.0
Feb-13 23.0
.
.
.
Oct-17 98.4
我希望按日历月计算特定、不连续日期范围的平均数据。例如 2013 年 1 月 -> 2013 年 3 月和 2016 年 10 月 -> 2017 年 5 月。
我相信这可以通过使用带有 OR 的 IF 函数来实现，以包含多个日期范围，以及使用 AND 来包含每个范围内的两个日期，以返回一个数组（当作为数组公式输入时），然后在返回的数组上使用 averageif。
我的数据在 B5:C62 中，我的定制日期范围设置为：
Period 1 Start: S2
Period 1 End: T2
Period 2 Start: S3
Period 2 End: T3
我的日历月份在 P6:P17，我输入公式如下
=AVERAGEIF($B$5:$B$62,P6,IF(OR(AND($B$5:$B$62>=$S$2,$B$5:$B$62<=$T$2),
AND($B$5:$B$62<=$S$3,$B$5:$B$62>=$T$3)),$C$5:$C$62,"ERROR"))
我收到 #VALUE! 错误，但我不明白为什么。据我所知，我的 IF 子句应该返回 AVERAGEIF 公式要运行的“average_range”。
我的公式或方法有什么错误？
答案1
在您的公式中，当逻辑测试中有 AND() 时，IF() 部分不会返回数组。您可以使用 Excel 公式的内置调试工具来检查这一点：突出显示公式栏中公式的一部分，然后按 F9。
但确实有一种方法可以计算出你想要的平均值。在 Excel 中，将逻辑值相乘会将 True/False 值转换为 1 和 0，然后可以对其进行进一步的操作。例如，以下语句
=(A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)
返回数组
{0;0;0;1;1;1;1;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;0;0}
根据下表中的数据。此处的乘法就像逻辑与，而加法几乎就像逻辑或。（@Máté Juhász 可能会在这里插话来解释为什么它不完全是或 :-)
该数组可被视为 A 列的“掩码”，其中 1 对应开始和结束日期指定的两个日期范围。
现在，IF() 公式可以使用此数组来生成要求平均值的值列表：
IF(((A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)),B2:B25)
这给出了数组
{FALSE;FALSE;FALSE;42.9;82.3;90.5;15.6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;70.8;85.5;19.2;85.4;21.3;55.7;FALSE;FALSE}
现在可以将其输入到 AVERAGE()。
因此，解决您的问题的一个方法是使用类似于此（数组）公式的公式，在 D6 中：
=AVERAGE(IF(((A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)),B2:B25))
更改范围和定制日期值以适合您的情况。
“检查”下面的单元格仅包含使用以下方法手动计算的平均值
=AVERAGE(B5:B8,B18:B23)
希望这会有所帮助并祝你好运。