vba 从没有数据透视表的每日数据计算月平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33769790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate monthly average from daily data without PivotTable
提问by ssteinec
My worksheet contains two columns: column A contains the date in the format dd/mm/yyyyand 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.
我的工作表包含两列：A 列包含格式中的日期，dd/mm/yyyyB 列包含每日数据。根据每日数据，我必须在没有数据透视表的情况下生成每个月的月平均值。有些日子根本不包含任何数据。我不能使用数据透视表，因为我必须在稍后阶段使用月平均值来识别每日数据中的异常值。
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.
对于此示例，重要的是我不使用数据透视表，并且我可以生成列表中任何月份和年份的月平均数据。解决方案可以是 Excel 公式或 VBA。
回答by EEM
Assuming you have the months in column Denter this formula in E2and copy till last month
假设您在列中有月份D输入此公式E2并复制到上个月
=AVERAGEIFS($B:$B,$A:$A,">="&D2,$A:$A,"<="&EOMONTH(D2,0))
回答by PankajR
Some of the days contain no data at all.
有些日子根本不包含任何数据。
In case this means that there are no row for some dates and you want to calculate average based on full month than you can use the below formula:
如果这意味着某些日期没有行，并且您想根据整月计算平均值，则可以使用以下公式：
Continuing with example given by EEM
继续 EEM 给出的例子
=SUMIFS($B:$B,$A:$A,">="&D2,$A:$A,"<="&EOMONTH(D2,0))/DAY(EOMONTH(D2,0))
回答by gerdami
In case Daily Data contains some #N/A, one can add a additional criteria to skip non number data:
如果每日数据包含一些 #N/A，则可以添加额外的条件来跳过非数字数据：
=AVERAGEIFS($B:$B,$A:$A,">="&D2,$A:$A,"<="&EOMONTH(D2,0),$B:$B,"<10E69")
