从没有数据透视表的每日数据计算月平均值 [英] Calculate monthly average from daily data without PivotTable

问题描述

我的工作表包含两列:A 列包含 dd/mm/yyyy 格式的日期，B 列包含每日数据.根据每日数据，我必须在没有数据透视表的情况下生成每个月的月平均值.有些日子根本不包含任何数据.我不能使用数据透视表，因为我必须在稍后阶段使用月平均值来识别每日数据中的异常值.

My worksheet contains two columns: column A contains the date in the format dd/mm/yyyy and column B contains the daily data. From the daily data I have to generate the monthly average for each month without a PivotTable. Some of the days contain no data at all. I cannot use a PivotTable as I have to use the monthly average to identify outliers in the daily data at a later stage.

对于此示例，重要的是我不使用数据透视表并且我可以生成列表中任何月份和年份的月平均数据.解决方案可以是 Excel 公式或 VBA.

For this example it's important that I don't use a PivotTable and that I can generate the monthly average data for any month and year in my list. The solution can be an Excel formula or VBA.

推荐答案

假设您在 D 列中有月份，在 E2 中输入此公式并复制到上个月

Assuming you have the months in column D enter this formula in E2 and copy till last month

=AVERAGEIFS($B$1:$B$62,$A$1:$A$62,">="&D2,$A$1:$A$62,"<="&EOMONTH(D2,0))

这篇关于从没有数据透视表的每日数据计算月平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！