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
.