Duas abordagens:
Fórmula desarrumada:
Defina E2 para =A2 .
Defina E3 para
=IF(OR(E2=0,E2=""), "", MIN(IF(A$2:A$23>=DATE(YEAR(E2),MONTH(E2)+1,1), A$2:A$23, "")))
e pressione Ctrl + Deslocar + Enter .
Arraste / preencha isso.
Isso inicialmente produzirá
15-Oct-2016
1-Nov-2016
5-Dec-2016
2-Jan-2017
8-Feb-2017
11-Mar-2017
0-Jan-1900
(Aqui eu tenho formatado as células como o formato 14-Mar-2012 ,
que é implementado como [$-409]d-mmm-yyyy;@ .)
E2 claramente é A2 , a primeira data.
Então, DATE(YEAR(E2),MONTH(E2)+1,1) avalia para 1 de novembro de 2016;
o primeiro dia do mês seguinte à data em E2 .
E finalmente, a fórmula completa em E3 (dada acima)
encontra a data mais baixa / mais antiga (mínimo) em A2:A23 , ≥ essa data.
Algo que confunde, isto é 1-nov-2016, como está na sua lista de datas.
Mas E4 é 5 de dezembro de 2016, porque essa é a data mais baixa ≥ 1 de dezembro de 2016.
E8 procura a data mais baixa ≥ 1 de abril de 2017.
Não há, então recebemos MIN("") ,
que avalia para 0, que exibe como 0-jan-1900.
É por isso que a fórmula começa com IF(OR(E2=0,E2=""), "", …
- para evitar que a fórmula se ligue em nós.
Se você alterar o formato de exibição da coluna E para [$-409]mmm yyyy;;
(excluindo o d- inicial, (opcionalmente) alterando o segundo - para um espaço,
e alterando o @ final para um segundo ; ), você receberá
Oct 2016
Nov 2016
Dec 2016
Jan 2017
Feb 2017
Mar 2017
Isso suprime a exibição do dia do mês,
e totalmente suprime a exibição da entrada de janeiro de 1900.
Duas colunas auxiliares:
Defina M1 para =DATE(YEAR(A2), MONTH(A2), 1) .
Defina N2 para =N1+COUNTIF(M$2:M$23, E2) .
Defina E2 para =IFERROR(SMALL(M$2:M$23, N1+1), "") .
Arraste / preencha.
A coluna M deve ser arrastada até a linha 23;
os outros dois só precisam ir longe o suficiente para gerar os meses distintos.
Isso nos dá
+----+------------+------------+-----+
| | E | M | N |
+----+------------+------------+-----+
| 1 | | | |
| 2 | 1-Oct-2016 | 1-Oct-2016 | 4 |
| 3 | 1-Nov-2016 | 1-Oct-2016 | 9 |
| 4 | 1-Dec-2016 | 1-Oct-2016 | 14 |
| 5 | 1-Jan-2017 | 1-Oct-2016 | 18 |
| 6 | 1-Feb-2017 | 1-Nov-2016 | 20 |
| 7 | 1-Mar-2017 | 1-Nov-2016 | 22 |
| 8 | | 1-Nov-2016 | 22 |
| 9 | | 1-Nov-2016 | 22 |
| 10 | | 1-Nov-2016 | 22 |
| 11 | | 1-Dec-2016 | 22 |
| 12 | | 1-Dec-2016 | 22 |
| ︙ | ︙ |
em que Coluna M rastreia a coluna A
e a coluna N repete apenas o número total de dias na sua planilha.
Coluna M mostra claramente o primeiro dia do mês
da data correspondente na coluna A .
E2 é o menor desses valores; %código%.
SMALL(M$2:M$23, 1) conta as datas na Coluna N2 que são iguais a este valor
(há 4 datas em outubro) e adiciona a M ,
que está em branco (isto é, zero).
(Você pode colocar um N1 em 0 explícito, se quiser.)
Então N1 é a quinta menor data, E3 .
E assim por diante.
E, claro, você pode novamente formatar a coluna SMALL(M$2:M$23, 5)
para suprimir a exibição do dia do mês.
Ocorre-me, depois de digitar todos os itens acima,
que eu poderia ter feito isso com E em vez de INDEX .
Nota
Ambas as soluções acima
lista apenas os meses que estão presentes na coluna SMALL .
Por exemplo,
Se a coluna A tiver datas em outubro de 2016, novembro de 2016, dezembro de 2016, fevereiro de 2017 e março de 2017
(mas não em janeiro de 2017)
a coluna A será listada em outubro de 2016, novembro de 2016, dezembro de 2016, fevereiro de 2017 e março de 2017
(mas não em janeiro de 2017).
Eu fiz isso porque a pergunta diz:
“Como eu posso escrever automaticamente todos os meses do intervalo E ,…?”
Se você quiser incluir todos os meses
de out 2016 ( A2:A23 ) para mar 2017 ( A2 ),
incluindo meses que não estão presentes na coluna A23 ,
você precisa esclarecer a questão.
De qualquer forma,
Para contar as linhas "Y" de cada mês, defina A para
=SUMPRODUCT(--(MONTH($A$2:$A$99)=MONTH(E2)), --($C$2:$C$99="Y"))
e arraste / preencha.
P.S. Diagnóstico de falha
Então você já entendeu o problema:
as últimas oito datas na coluna F2 são em janeiro, fevereiro e março (de 2017),
então eles têm valores A de 1, 2 e 3.
Mas quando você usa MONTH() , etc., em MONTH(A2)+3 , etc.,
você está contando linhas que têm valores de F5 de 13, 14 e 15
(que, claro, não existe).
Depois de ter uma lista de meses na coluna MONTH() ,
você deve usar esses valores no cálculo da coluna E .