在没有数据透视表的情况下从每日数据计算每月平均值 [英] 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屋！