Contar todas as correspondências / duplicados entre duas colunas no Excel

Comparar duas colunas de dados e contar todas as correspondências ou duplicados entre elas é uma tarefa comum para muitos de nós. Por exemplo, imagine que tem duas colunas com nomes e que alguns desses nomes aparecem tanto na primeira como na segunda coluna. O seu objetivo é agora contar todos os nomes correspondentes — ou seja, as coincidências que ocorrem em qualquer posição nas duas colunas — tal como ilustrado na imagem seguinte. Este tutorial apresenta algumas fórmulas úteis para alcançar este objetivo no Excel.

• Contar todas as correspondências entre duas colunas com as funções SOMARPRODUTO e CONTAR.SE
• Contar todas as correspondências entre duas colunas com as funções CONTAR e CORRESP
• Contar todas as correspondências entre duas colunas com as funções SOMARPRODUTO, ÉNÚM e CORRESP

Contar todas as correspondências entre duas colunas com as funções SOMARPRODUTO e CONTAR.SE

Para contar todas as correspondências entre duas colunas, a combinação das funções SOMARPRODUTO e CONTAR.SE pode ajudá-lo. A sintaxe genérica é:

• range1, range2Os dois intervalos contêm os dados cujas correspondências pretende contar.

Introduza ou copie a fórmula seguinte para uma célula vazia e, em seguida, prima a tecla Enterpara obter o resultado:

Explicação da fórmula:

=SUMPRODUCT(COUNTIF(A2:A12,C2:C12))

• COUNTIF(A2:A12,C2:C12): Esta função CONTAR.SE verifica se cada nome da coluna C existe na coluna A. Se o nome existir, devolve 1; caso contrário, devolve 0. O resultado obtido será: {1;1;0;0;0;1;0;0;1;0;1}.
• SOMARPRODUTO(CONTAR.SE(A2:A12;C2:C12))=SOMARPRODUTO({1,1,0,0,0,1,0,0,1,0,1}): A função SOMARPRODUTO soma todos os elementos desta matriz e obtém o resultado: 5.

Contar todas as correspondências entre duas colunas com as funções CONTAR e CORRESP

Combinando as funções CONTAR e CORRESP, também pode obter o número de correspondências entre duas colunas. A sintaxe genérica é:

• range1, range2Os dois intervalos contêm os dados cujas correspondências pretende contar.

Introduza ou copie a fórmula seguinte para uma célula vazia e, em seguida, prima simultaneamente as teclas Ctrl + Shift + Enterpara obter o resultado correto, conforme ilustrado:

Explicação da fórmula:

=COUNT(MATCH(A2:A12,C2:C12,0))

• MATCH(A2:A12,C2:C12,0): Esta função CORRESP procura os nomes da coluna A na coluna C e devolve a posição de cada valor correspondente. Se um valor não for encontrado, é apresentado um erro. Assim, obtém a seguinte lista de matriz: {11;2;#N/D;#N/D;#N/D;6;1;#N/D;#N/D;#N/D;9}.
• COUNT(MATCH(A2:A12,C2:C12,0))= COUNT({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): A função CONTAR conta os valores numéricos na lista de matriz, obtendo o resultado: 5.

Contar todas as correspondências entre duas colunas com as funções SOMARPRODUTO, ÉNÚM e CORRESP

No Excel, pode encontrar correspondências entre duas colunas e contá-las utilizando as funções SOMARPRODUTO, ÉNÚM e CORRESP. A sintaxe genérica é:

• range1, range2Os dois intervalos contêm os dados cujas correspondências pretende contar.

Introduza ou copie a fórmula seguinte para uma célula vazia para obter o resultado e, em seguida, prima a tecla Enterpara devolver o cálculo, conforme ilustrado:

Explicação da fórmula:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))

• MATCH(A2:A12,C2:C12,0): Esta função CORRESP procura os nomes da coluna A na coluna C e devolve a posição de cada valor correspondente. Se um valor não for encontrado, é apresentado um erro. Assim, obtém a seguinte matriz: {11;2;#N/D;#N/D;#N/D;6;1;#N/D;#N/D;#N/D;9}.
• ISNUMBER(MATCH(A2:A12,C2:C12,0))= ISNUMBER({11;2;#N/A;#N/A;#N/A;6;1;#N/A;#N/A;#N/A;9}): Aqui, a função ÉNÚM converte os valores numéricos em VERDADEIRO e todos os restantes em FALSO na matriz, resultando numa matriz semelhante a esta: {VERDADEIRO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO}.
• --(ISNUMBER(MATCH(A2:A12,C2:C12,0)))=--({TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}): o duplo sinal negativo é utilizado para converter um valor VERDADEIRO em 1 e um valor FALSO em 0, devolvendo o seguinte resultado: {1;1;0;0;0;1;1;0;0;0;1}.
• SUMPRODUCT(--(ISNUMBER(MATCH(A2:A12,C2:C12,0))))=SUMPRODUCT({1,1,0,0,0,1,1,0,0,0,1}): Por fim, a função SOMARPRODUTO soma todos os elementos desta matriz e obtém o resultado: 5.

Função relativa utilizada:

Mais artigos: