Fórmula genérica
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Resumo
Para calcular a média por mês, você pode usar uma fórmula baseada na função AVERAGEIFS, com ajuda da função EOMONTH. No exemplo mostrado, a fórmula em F4 é:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Esta fórmula usa os intervalos nomeados "valores" (D5: D104) e "datas" (C5: C104).
Explicação
A função AVERAGEIFS pode fazer a média de intervalos com base em vários critérios. Nesse caso, configuramos AVERAGEIFS para valores médios por mês usando dois critérios: (1) coincidir datas maiores ou iguais ao primeiro dia do mês, (2) coincidir datas menores ou iguais ao último dia do mês. Se codificássemos as datas de janeiro de 2016 na fórmula usando a função DATE, ela ficaria assim.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Mas não queremos datas codificadas, queremos que o Excel gere essas datas para nós. Normalmente, isso é uma dor, porque se você adicionar nomes de meses como texto (ou seja, "janeiro", "fevereiro", "março", etc.) na coluna F, você terá que se esforçar para criar datas que possa usar como critérios .
No entanto, neste caso, usamos um truque simples para facilitar as coisas: na coluna F, em vez de digitar os nomes dos meses, adicionamos as datas reais para o primeiro dia de cada mês (01/01/2016, 01/02/2016, 3 / 1/2016, etc.) e use um formato de data personalizado ("mmm") para exibir os nomes dos meses.
Isso torna mais fácil construir os critérios de que precisamos para AVERAGEIFS. Para combinar datas maiores ou iguais ao primeiro dia do mês, usamos:
">="&E4
E para coincidir com datas menores ou iguais ao último dia do mês, usamos:
"<="&EOMONTH(E4,0)
EOMONTH retorna automaticamente o último dia do mesmo mês porque fornecemos zero para o argumento de meses.
Nota: a concatenação com um E comercial (&) é necessária ao construir critérios com base em uma referência de célula.
Solução de tabela dinâmica
Uma tabela dinâmica é uma solução excelente quando você precisa resumir dados por ano, mês, trimestre e assim por diante, porque as tabelas dinâmicas fornecem controles para agrupamento automático por data. Para uma comparação lado a lado de fórmulas e tabelas dinâmicas, veja este vídeo: Por que tabelas dinâmicas.
