我正在尝试在工作表中使用AVERAGEIF公式来基于当前月份计算移动平均值。我有此数据:
A B C D E F G H I J K L M N
1 2017 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Ave
2 Cost/Req. $430 $432 $365 $391 $1250 $- $- $- $- $- $- $- ??
我想将今天(5月)的1月至4月的平均N2和1月至5月的6月的N2平均。我尝试使用COLUMN()函数,但无法正常工作。
AVERAGEIF(B2:M2,"COLUMN()<MONTH(NOW()")
任何的想法?
谢谢!
让我们分别解决这个问题的几个角度:
=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))
该AVERAGEIF
函数接受标准参数,但是它们似乎无法执行任何非常复杂的操作。我还没有找到关于它们的语法的真正解释,但是在线文档中的示例没有做比深刻的事情">32"
,并且您正在尝试使用整个公式。我没有看到任何证据表明该方法即使在理论上也行得通。(我很乐意在此方面显示错误!)AVERAGEIF
(或AVERAGEIFS
)不是您想要的功能。
平均范围的值很容易。您显然已经知道该怎么做,甚至可以问自己一个问题!假设值在A2:L2中(月标题位于顶部):
=AVERAGE(A2:L2)
但是,等等,您只想平均最多(不包括当月)。因此,我们需要一种计算进入基本平均公式的范围的方法。五月,那是A2:D2
。我们如何计算呢?
好的,所以新的问题是如何AVERAGE
从当前月份获取一个范围(该函数的参考)。这些Lookup & Reference
功能将在这里为您提供帮助。可能有一种更简单的方法来执行此操作,但是我能想到的最好的方法是,INDIRECT
如果我们给它适当的文本,则返回一个参考值/范围,这是一个更简单的问题。现在,我们所需要的只是想出文本“ A2:D2”(5月份)。A2很简单:应该是一个常数。可能有一种方法可以将4(对于您想要的最后一个月的4月)转换为D(您想要的列),但是您也可以使用该ADDRESS
函数来计算D2
行/列号。请注意,它认为是正常的“后退”。因此,将A2输入为ADDRESS(2,1)
。在您的情况下,您知道第2行,并且可以使用MONTH
您想要的函数,然后减去1返回上个月。这样我们就可以得到的“ D2” ADDRESS(2,MONTH(NOW())-1)
。然后,只需要将它们放在一起即可。A2和D2的组合是使用&号完成的。
="A2:"&(ADDRESS(2,MONTH(NOW())-1))
现在,我们有了要平均的区域的文本表示形式。该INDIRECT
函数会将其转换为该AVERAGE
函数可以使用的实际范围。然后,最终函数将所有结果求平均:
=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))
如果您的正确范围确实是U192:AF192,则将公式中的2替换为192,并在row参数中添加20(因为U在A的右边是20行)。
我还将附上屏幕截图,介绍各个中间步骤的计算方式。最终公式是第8行的公式。我还进行了下一步,仅对第12行中的相同想法进行平均,而不是对过去几个月进行平均。
请注意,整个方法将使您的平均值取决于打开电子表格的时间。如果您明年2月将其开放,则平均期限将只有两个月,而不是整个2017年。一月将完全窒息。您可能想提出一个更聪明的公式来处理month参数。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句