☰

Excel: conte o número de linhas únicas/distintas no intervalo com condição

tag-icon • tag-icon microsoft-excel
Excel: conte o número de linhas únicas/distintas no intervalo com condição

Eu tenho uma planilha excel com:

  • na coluna A: números da semana
  • na coluna B: datas (entradas na planilha de horas)

Preciso saber quantos dias trabalhados em cada semana. Portanto, preciso do número de entradas de data exclusivas por semana.

Encontrei fórmulas (matrizes e não matrizes) que tratam disso para um intervalo fixo, mas quero ter os resultados em outra coluna (por número da semana).

O resultado do conjunto de dados de exemplo abaixo seria (os dois pontos são apenas para maior clareza):

14: 2 
15: 3 
17: 6 
20: 2 
21: 3

Se estes forem os dados de origem:

14: 4/04/2012
14: 4/04/2012
15: 10/04/2012
15: 10/04/2012
15: 11/04/2012
17: 26/04/2012
17: 26/04/2012
17: 26/04/2012
17: 26/04/2012
17: 27/04/2012
17: 27/04/2012
20: 14/05/2012
20: 14/05/2012
21: 23/05/2012
21: 23/05/2012
21: 25/05/2012

Responder1

para contar o número de entradas, =countif(A:B,D1)suponha que o número da semana esteja na célula D1 e que a lista de entradas esteja nas colunas A e B.

Outra opção é criar uma tabela dinâmica, com os números das semanas como rótulos de linha e a contagem de entradas como dados. Isso fornecerá um bom resumo que pode ser atualizado rapidamente.

Responder2

É possível fazer inteiramente com fórmulas. Ele precisa de um pouco de endereçamento indireto e uma (mas para maior clareza farei duas) colunas de trabalho separadas ao longo dos dados originais e três colunas extras na tabela de resultados:

Presumo que os dados reais comecem na linha 3 para permitir alguns cabeçalhos. Usarei ;para separação de argumentos, que não é padrão para a localidade dos EUA.Não vou assumir que as datas estão ordenadas. Com esta suposição a solução seria mais simples.

  1. Célula H2 (quantas linhas na entrada):=COUNT(A3:A1048576)
  2. Célula C3 (faixa de pesquisa dinâmica): nada
  3. Células C4:C1000:=ADDRESS(ROW(A$3);COLUMN(A$3)) & ":" & ADDRESS(ROW(A3);COLUMN(A3))
  4. Célula D3 (é única):TRUE
  5. Célula D4:D1000:=COUNTIF(INDIRECT(C4);A4)=0
  6. Célula E3 (número de entrada única):1
  7. Célula E4:E1000:=IF(D4;E3+1;E3)
  8. Célula I2 (quantas únicas encontradas):=OFFSET(E3;H2-1;0)
  9. Célula J2 (intervalo de dias da semana):=ADDRESS(ROW(A3);COLUMN(A3);4) & ":" & ADDRESS(ROW(A3)-1+$H$2;COLUMN(A3);4)
  10. Célula K2 (Nr do intervalo exclusivo de dias da semana):=ADDRESS(ROW(E3);COLUMN(E3);4) & ":" & ADDRESS(ROW(E3)-1+$H$2;COLUMN(E3);4)
  11. Célula H5 (contador):1
  12. Célula H6:H100=H5+1
  13. Célula I5:I100 (posição):=MATCH(H5;INDIRECT($K$2);0)
  14. Célula J5:J100 (dia da semana):=OFFSET($A$3;I5-1;0)
  15. Célula K5:K100 (Contagem):=COUNTIF(INDIRECT($J$2);J5)

O resultado final está na faixa K5:K100.

Observe que embora eu trabalhe com fórmulas indiretas, a solução funcionará se você inserir uma coluna em qualquer lugar ou excluir a coluna F:F de G:G. Você também pode mover células, desde que mantenha as colunas com dados juntas.

É importante manter tudo em uma planilha. Se você insistir em mover a tabela H4:K100 para outra planilha, deverá modificar os endereços nas células J2 e K2 para incluir o nome da planilha.

insira a descrição da imagem aqui

Responder3

Uma tabela dinâmica pode ser mais fácil

Exemplo

informação relacionada