☰

在多个定制日期范围内按月平均 excel

tag-icon • tag-icon microsoft-excel array
在多个定制日期范围内按月平均 excel

我有 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)

希望这会有所帮助并祝你好运。

相关内容