Excel-取每月数据的平均值 [英] Excel - Take Average of Monthly Data

问题描述

我有一个用于商品定价的历史数据集.在整个数据集中，数据开始输入特定日期的价格，而不是整个月的平均值.为了保持每月只有平均价格的流量.

I have a historical data set for commodity pricing. Throughout the data set, the data starts inputting prices on specific days, rather than the average of the entire month. In order to keep the flow of having only the average pricing for the months.

在最佳情况下，我将使用Averageif函数，但是每个月的数据显示的天数并不一致.

In the best case scenario, I would use an Averageif function, however, the data for each month doesn't display a consistent amount of days.

如何自动执行此过程:如果月份与上一行相同但与下一行不同，请计算^行的平均值，直到您达到下个月为止.

How can I automate this the process: If the month is the same as the previous row and different than the next row, calculate the average of the ^ rows until you hit the next month.

这是我的意思的简单显示:

Here's a simple display of what I mean:

] 推荐答案

对于没有数据透视表的解决方案，可以使用以下公式:
=AVERAGEIFS($B$1:$B$30;$A$1:$A$30;">="&(A1-DAY(A1)+1);$A$1:$A$30;"<="&EOMONTH(A1;0))

For a solution without pivot tables, you can use the following formula :
=AVERAGEIFS($B$1:$B$30;$A$1:$A$30;">="&(A1-DAY(A1)+1);$A$1:$A$30;"<="&EOMONTH(A1;0))

上面的示例来自单元格C1，可以向下复制整个列表.日期在$A$1:$A$30中，值在$B$1:$B$30中.第一个条件在该月的第一天进行测试(计算为A1-DAY(A1)+1)，第二个条件在该月的最后一天进行测试(计算为EOMONTH(A1;0)

The above example is from cell C1, and can be copied down the entire list. The dates are in $A$1:$A$30 and the values in $B$1:$B$30. The first conditions test on the first day of the month (calculated as A1-DAY(A1)+1),and the second condition as last day of the month (calculated as EOMONTH(A1;0)

显然，这将在每个行中显示月份的平均值，但是如果您的数据未按日期排序，也可以使用.如果是这种情况，并且您只希望每月在该列中显示一个数字(如您自己的示例)，则可以在公式周围添加一个附加的IF语句:
=IF(EOMONTH(A2;0)=EOMONTH(A1;0);"";AVERAGEIFS($B$1:$B$30;$A$1:$A$30;">="&(A1-DAY(A1)+1);$A$1:$A$30;"<="&EOMONTH(A1;0)))
因此，除了月份更改的地方之外，所有单元格都将显示为空.

This will obviously put the average value of the month on each row, but will also work if your data is not sorted on date. If this is the case, and you only want to display one number per month in the column (as in your own example), you can add an additional IF statement wrapped around the formula:
=IF(EOMONTH(A2;0)=EOMONTH(A1;0);"";AVERAGEIFS($B$1:$B$30;$A$1:$A$30;">="&(A1-DAY(A1)+1);$A$1:$A$30;"<="&EOMONTH(A1;0)))
So it will display empty in all cells, except where the month changes.

这篇关于Excel-取每月数据的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！