No Excel, como faço para contar automaticamente as entradas feitas correspondentes a cada mês?

0

Na folha do Excel a seguir, há datas na coluna A2:A23 no formato dd-mm-yyyy . Em B2:B23 dias são gravados usando a fórmula =TEXT(A2,"ddd") . Na coluna C2:C23 entradas "Y" ou "N" são feitas. As inscrições foram feitas para todos os meses de outubro de 2016 a março de 2017 na coluna C . Como posso escrever automaticamente todos os meses do intervalo A2:A23 , digamos, por exemplo, na coluna E e exibir o número de entradas "Y / N" correspondentes a cada mês?

Por exemplo, quero ver o resultado como mostrado nas colunas E e F . As entradas em E e F são preenchidas manualmente, mas quero preencher essas entradas automaticamente.

Estou ciente da fórmula como =SUMPRODUCT(--(MONTH(A2:A23)=MONTH(E))) , mas para isso, preciso escrever manualmente meses na coluna E de A2:A23 .

+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
|    Date    | Day | Entry(Y/N) |  | Month | Number of Y/N |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 15-10-2016 | Sat | Y          |  | Oct   |             4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 17-10-2016 | Mon | Y          |  | Nov   |             5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 19-10-2016 | Wed | Y          |  | Dec   |             5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 23-10-2016 | Sun | Y          |  | Jan   |             4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 01-11-2016 | Tue | Y          |  | Feb   |             2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 05-11-2016 | Sat | N          |  | Mar   |             2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 11-11-2016 | Fri | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 15-11-2016 | Tue | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 22-11-2016 | Tue | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 05-12-2016 | Mon | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 09-12-2016 | Fri | N          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 11-12-2016 | Sun | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 23-12-2016 | Fri | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 26-12-2016 | Mon | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 02-01-2017 | Mon | N          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 08-01-2017 | Sun | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 10-01-2017 | Tue | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 22-01-2017 | Sun | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 08-02-2017 | Wed | N          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 10-02-2017 | Fri | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 11-03-2017 | Sat | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| 15-03-2017 | Wed | Y          |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
|            |     |            |  |       |               |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Atualização: Deixe-me explicar o que fiz. Em E2 , escrevi =A2 e formatei esta célula usando o formato personalizado mmm, o que me ajudou a exibir Oct em E2 , em E3 usei a fórmula =DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)) , essa fórmula me ajudou a exibir novembro em E3 e assim por diante. Em F2 , escrevi =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2))) , que forneceu a contagem 4 e, posteriormente, em F3 , escrevi =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)+1)) , em F4 =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)+2)) e assim por diante. Tudo é bom até Dec , depois que o resultado zero é exibido para Jan , Feb e March . Eu acho que o incremento no comando = SUMPRODUCT (- (MONTH ($ A $ 2: $ A $ 23) = MÊS (A2) +1)) está me dando resultado errado após Dec , o que obviamente não produz Jan quando um o incremento é dado mês Dec

    
por IgotiT 25.04.2017 / 17:58

2 respostas

1

Na coluna E em E2, se as datas começarem em A2, escreva: =VALUE(TEXT(A2,"mm"))
e arraste-o até E23 última data
Selecione todos E2: E23 copiar e colar valores especiais em F2 (ele se tornará F2: F23) excluir coluna E selecione E2: E23 (os novos meses)
Separador Dados, Remover Duplicados (apenas da coluna E não expande a selecção)
Você terá cada mês uma vez, em F2 escreva sua fórmula:
=SUMPRODUCT(--(MONTH($A$2:$A$23)=E2)) (Array Formula)
Se você quiser contar apenas "Y" para cada mês use:
=SUMPRODUCT(--(MONTH($A$2:$A$23)=VALUE(F2))*(--($C$2:$C$23="Y"))) (Array Formula)
pressione Ctrl + Deslocamento + Entre depois das duas fórmulas e você pode arrastá-lo

Você pode usar uma fórmula diretamente na coluna D, por exemplo:
Em D2 write (apenas em D2):
=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)))
Em D3
=IF(MONTH(A3)<>MONTH(A2),SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A3))),"")
Também formular fórmulas e arrastá-lo para baixo
Quando a mudança do mês, vai dar a contagem
Atualizar
Quanto ao seu comentário, você ainda pode usar sua fórmula:
=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(E2)))
E2 não A2
Se você escreveu em E2 = A2 isso significa que E2 ainda é uma data para e você pode usar o mês (E2)
Mas você tem que inserir os critérios para o ano a menos que você não tenha o mesmo mês em 2016 e 2017
Porque janeiro é afetado por células vazias
Use a fórmula a seguir para fazer o mesmo trabalho que a anterior, mas também testar se a célula está vazia:
=SUM(IF($A$2:$A$23<>"",IF(MONTH($A$2:$A$23)=MONTH(E2),1,0),0))
Pressione a tecla Ctrl + Deslocar + Enter e arraste-a para a caixa de seleção. Se retornará 1 se o mês (A2: A23) = Mês (E2)
E 0 se vazio ou diferente Soma adicionará 1

    
por 25.04.2017 / 18:44
1

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 .

    
por 27.04.2017 / 13:40