Como faço para contar as linhas com dados semelhantes em 2 colunas como 1 entrada de dados?

Question

Os dados de teste do programa SPC são inseridos não são capazes de realizar a validação de conformidade. Abaixo está uma imagem de dados exportados para o excel. Se a data e a hora forem uma correspondência exata, será considerado um teste concluído. O número de linhas será muito baseado em colunas não mostradas (dados coletados). Eu incluí uma caixa em torno dessas linhas que devem ser contadas como 1 teste concluído.

Eu gostaria de poder usar uma tabela dinâmica que mostrará o número de testes concluídos (não o número de linhas) por início de turno para que eu possa criar gráficos de tendência para mostrar conformidade (os requisitos de conformidade estão em um campo calculado ). Eu estou procurando sugestões sobre a melhor maneira de realizar isso (ou seja, coluna auxiliar com uma fórmula, campo calculado de pivô, etc.) para que eu possa rapidamente exportar / importar os dados semanalmente e enviar gráficos para minha equipe.

    

Answer 1

Eu adicionaria outra coluna (N no seu exemplo) que marcaria a última linha de um teste completo. Vamos chamar essa coluna de "Teste exclusivo". A fórmula seria:

=IF(OR(G2<>G3, H2<>H3), TRUE, FALSE)

Agora, se você filtrar a coluna N para mostrar apenas TRUE valores, você verá uma lista de testes concluídos, uma linha por teste.

Independentemente deste filtro, você pode criar uma tabela dinâmica para contar testes por turno assim:

1. Selecione as colunas M e N.
2. Inserir uma tabela dinâmica (na guia Insert ).
3. Arraste o campo ShiftStart para a área Row Labels.
4. Arraste o campo Unique Test para a área Valores (obtendo Count of Unique Test como o valor).

5. No PivotTable Tools , sob a guia Options , clique em Insert Slicer , escolha Unique Test e clique em TRUE no slicer.

   

   

Answer 2

Como mencionado acima, as tabelas dinâmicas incluem essa funcionalidade em 2013 e posteriormente.

resultados

Método

Supondo que seus dados iniciem na linha 2, a maneira mais eficiente de calcular e mais fácil de entender parece ser:

=IF(COUNTIFS($G$2:G2,G2,$H$2:H2,H2)>1,0,1)

para x = 2 an, conte o número de vezes Gx = Gn E Hx = Hn

A parte countifs conta o número de ocorrências únicas de G & H até essa pount. Com o IF, isso retornará 0 para valores repetidos e 1 para a primeira ocorrência dessa combinação de ge h.

Arraste isso para a parte inferior de sua tabela e adicione uma soma dessa coluna à tabela dinâmica final e você terá uma contagem de horários e datas exclusivas.

Isso também funciona para dados não contíguos, em que valores repetidos não são consecutivos (embora pareça que isso não se aplica a seus dados).

para versões do Excel que não incluem =countifs :

SUMPRODUCT(($G$2:$G3=G3)*($H$3:$H3=H3))

Observe os $ s. Essa fórmula será aplicada da linha 2 até a linha atual, expandindo a seleção à medida que ela desce. Isso soma o produto TRUE e TRUE (1 e 1) para cada linha x de x = 2 para x = n, onde ambos Gx = Gn e Hx = Hn. A linha atual (linha n) é contada. Se a combinação for encontrada antes da linha atual, a fórmula contará (pela soma de TRUE*TRUE ).

Em resumo, ele conta o número de ocorrências únicas de G e H. Então, PUT

N2 = SE (SUMPRODUCT (($ G $ 2: $ G2 = G2) * ($ H $ 2: $ H2 = H2)) > 1,0,1)