Excel. Compare listas, encontre itens que ocorram em todas as listas

Question

Por exemplo Eu tenho 3 listas de coluna única. Cada lista contém apenas valores exclusivos. As listas não estão ordenadas Quero identificar valores que ocorrem em cada uma das três listas.

AAA AAA AAA
BBBB BBBB CCC
CCC CCC BBB

    

Answer 1

Esta resposta é destinada a usuários que não são especialistas em todos os recursos das fórmulas do Excel (como eu).

Uma solução simples é contar o número de vezes que os itens da coluna A são encontrados em todas as colunas (nenhuma duplicação permitida em nenhuma coluna).

Na célula D1 = COUNTIF ($ A $ 1: $ C $ 3, A1) Isso conta o número de vezes que 'AAA' ocorre nas 3 colunas Resposta 3

Na célula D2 = COUNTIF ($ A $ 1: $ C $ 3, B1)
Isso conta o número de vezes que 'BBB' ocorre nas 3 colunas Resposta 2

Na célula D3 = COUNTIF ($ A $ 1: $ C $ 3, C1) = 3 Isso conta o número de vezes que 'CCC' ocorre nas 3 colunas Resposta 3

Se você tiver uma lista longa, poderá aplicar um filtro de dados e apenas selecionar as linhas com um total de 3.

    

Answer 2

Aqui está uma ótima oportunidade para falar sobre funções de array!

Digite esta fórmula em D1 e insira-a com CTRL-Shift Enter. Se inserida corretamente, a fórmula será colocada entre chaves {}:

=LARGE((A$1:A$20=B$1:B$20)*(B$1:B$20=C$1:C$20)*ROW(A$1:A$20),ROW())

Em seguida, clique em D1 e preencha até ver um resultado zero. Isto dá os números de linha onde Colunas A, B & C são os mesmos.

Veja como isso funciona: A expressão A $ 1: A $ 20 = B $ 1: B $ 20 pergunta se o intervalo A1: A20 é igual ao intervalo B1: B20 e retorna um array de TRUE / FALSE valores:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

A matriz contém TRUE em cada posição onde a Coluna A é igual à Coluna B. Aqui, isto é, posições 10,17 e 20.

A expressão B $ 1: B $ 20 = C $ 1: C $ 20 realiza uma operação semelhante nas Colunas B e C. A multiplicação dessas duas matrizes de valores VERDADEIROS / FALSOS realiza o equivalente à operação AND () e também converte VERDADEIRO e VERDADEIRO. FALSE para 1 e 0, respectivamente.

Portanto, agora temos uma matriz que tem 0 em todos os lugares, exceto as posições onde Colunas A, B e C são iguais a {0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0} e multiplicando por ROW(A$1:A$20) (a matriz de números de linha), fornece uma matriz contendo os números de linha onde Colunas A, B e C são iguais: {0;0;0;0;0;0;0;0;0;10;0;0;0;0;0;0;17;0;0;0}

Agora, a única coisa que resta a fazer é extrair os números das linhas. A função LARGE (array, n) retorna o enésimo maior valor na matriz. A fórmula aqui usa ROW () como n, então, quando estiver preenchida, retorna o primeiro maior valor da linha 1, o segundo maior da linha 2, etc.

Espero que você tenha gostado disso. Boa sorte.