Como extrair uma lista de valores duplicados em duas colunas no Excel

Há várias maneiras de destacar valores duplicados. A formatação condicional pode ser o método mais comum; você pode escolher um formato interno ou usar uma regra para personalizar o formato. O que você descobrirá, porém, é que a maioria dos métodos trabalha diretamente com os dados de origem. Criar uma lista de valores duplicados separada dos dados de origem é um pouco mais difícil. Felizmente, graças à função de matriz dinâmica FILTER() do Excel, criar uma lista de valores duplicados é mais fácil do que antes. Neste artigo, mostrarei como usar FILTER() para criar uma lista de valores duplicados em duas colunas.

VEJA: 83 dicas do Excel que todo usuário deve dominar (TechRepublic)

Estou usando o Microsoft 365 em um sistema Windows 10 de 64 bits. (Recomendo que você não atualize para o Windows 11 até que todos os problemas tenham sido resolvidos, a menos que você tenha um motivo específico para fazê-lo.) FILTER() está disponível apenas no Microsoft 365. Para sua conveniência, você pode baixar a demonstração .xlsx Arquivo.

Sobre FILTER() no Excel

A exibição de um conjunto filtrado em outro local facilita a geração de relatórios e funciona especialmente bem em uma configuração de . Até que FILTER() fosse introduzido, obter um conjunto filtrado em outro local exigia um pouco de esforço e habilidade.

FILTER() é uma das novas funções de matriz dinâmica do Microsoft 365. Ele oferece e ao que é conhecido como intervalo de derramamento, que é o resultado de uma fórmula de matriz dinâmica que retorna vários valores — sua saída extrapola a célula de entrada. Em resumo, um intervalo de derramamento é um intervalo de resultados calculados de uma função ou expressão. Quando você seleciona qualquer célula em um intervalo de derramamento, o Excel destaca todo o intervalo com uma borda azul. Você sempre encontrará a fórmula na célula mais alta desse intervalo.

Esta função usa a sintaxe

FILTER(matriz, incluir, [if_empty])

onde array identifica os dados de origem, include identifica os valores que você deseja ver no conjunto de dados filtrado e o opcional if_empty especifica o valor a ser exibido quando o resultado for um conjunto vazio.

Como você verá, FILTER() é extremamente flexível.

FILTER() em duas colunas para valores repetidos no Excel

O objeto Table simples em Figura A duplica alguns nomes em quatro colunas. Nossa tarefa é criar uma única lista de valores repetidos em quaisquer duas colunas. Começaremos com os trimestres 1 e 2. Com uma rápida olhada, podemos ver facilmente que junho está em ambas as colunas. Em uma folha muito mais ocupada, você não vai querer depender da visão.

Figura A

Primeiro, vamos revisar a sintaxe para esta tarefa:
=FILTER(column2,COUNTIF(column1,column2)>0) onde 1 e 2 denotam as posições das colunas da esquerda para a direita. A primeira referência column2 identifica os dados de origem para a função FILTER(). COUNTIF() retornará TRUE se qualquer valor na coluna1 também ocorrer na coluna2.

Agora, vamos aplicar isso aos dois primeiros trimestres inserindo a seguinte função no G3:
=FILTRO(Tabela1[Qtr 2],CONT.SE(Tabela1[Qtr 1],Tabela 1[Qtr 2])>0)

Como você pode ver em Figura Bessa expressão retornará junho.

Figura B

Essa expressão retorna qualquer valor repetido em Qtr 1 e Qtr 2.

Como as referências são relacionais, você pode copiar a expressão em G3 para H3:I3 para retornar listas semelhantes, conforme mostrado em Figura C. Especificamente, a expressão em H3 retorna duplicatas nos trimestres 2 e 3 e a expressão em I3 retorna duplicatas nos trimestres 3 e 4.

Figura C

Copie a expressão para criar listas semelhantes de valores repetidos.

Observe como as três expressões diferem:
G3: =FILTRO(Tabela1[Qtr 2],CONT.SE(Tabela1[Qtr 1],Tabela 1[Qtr 2])>0)
H3: =FILTRO(Tabela1[Qtr 3],CONT.SE(Tabela1[Qtr 2],Tabela 1[Qtr 3])>0)
I3: =FILTRO(Tabela1[Qtr 4],CONT.SE(Tabela1[Qtr 3],Tabela 1[Qtr 4])>0)

Cada referência de coluna é atualizada por uma coluna; a expressão em H3 avalia os quartos 2 e 3, e a expressão em I3 avalia os quartos 3 e 4. Agora, vamos dividir as coisas para que você possa ver como tudo isso funciona usando a expressão em G3:

=FILTRO(Tabela1[Qtr 2],CONT.SE(Tabela1[Qtr 1],Tabela 1[Qtr 2])>0)
=FILTRO(Tabela1[Qtr 2],{0;0;0;1})>0)
=FILTRO(Tabela1[Qtr 2],{FALSO,FALSO,FALSO,VERDADEIRO})
=FILTER({“Susan”;”Lilly”;”Kate”;”Junho”},{FALSO,FALSO,FALSO,VERDADEIRO})
{“Junho”}

O COUNTIF() primeiro retorna um array de 0s e 1, onde 1 indica um valor repetido e sua posição—o quarto valor em Qtr 2. Ao adicionar o componente >0, este array retorna FALSE e TRUE, onde TRUE identifica o valor repetido nos dados de origem. Tecnicamente, >0 não é necessário, mas é uma maneira fácil de documentar sua intenção, facilitando muito a manutenção posterior.

A referência de array de FILTER() retorna os quatro valores no Qrt 2: Susan, Lilly, Kate e June. O único valor que corresponde a um valor TRUE é junho, portanto, a expressão retorna junho.

A expressão em H3 é avaliada como =FILTER({“Bill”;”John”;”Susan”;”Lilly”},{FALSE;FALSE;TRUE;TRUE}), retornando Susan e Lilly.

A expressão I3 é avaliada como =FILTER({“Jacob”;”Logan”;”Jordan”;”Bill”},{FALSE;FALSE;FALSE;TRUE}) retornando apenas Bill.

H3 é a única expressão que retorna mais de um valor e essa lista não é classificada. Você pode corrigir isso rapidamente adicionando SORT() à expressão em G3 no formulário

=SORT(FILTRO(Tabela1[Qtr 2],CONT.SE(Tabela1[Qtr 1],Tabela 1[Qtr 2])>0))

e, em seguida, copiá-lo para H3:I3. A Figura D mostra a lista ordenada na coluna H.

Figura D

Adicione SORT() para classificar as listas resultantes.

Conforme você atualiza os dados de origem, essa expressão retornará listas classificadas sempre que houver mais de um valor. Isso só é possível porque os dados de origem são um objeto Table. Se você estiver trabalhando com um intervalo de dados normal, as expressões não serão atualizadas.