Ok, então nos meus primeiros dias de análise de dados, tive a necessidade de obter automaticamente os itens exclusivos no Excel de uma lista, em vez de remover itens duplicados a cada vez. E descobri uma maneira de fazer isso. Depois disso, meu painel do Excel ficou ainda mais dinâmico do que antes. Então, vamos ver como podemos fazer isso …
Para extrair uma lista de valores únicos de uma lista, usaremos INDEX, MATCH e COUNTIF. Também vou usar o IFERROR, só para ter resultados limpos, é opcional.
E sim, será uma fórmula de matriz … Então vamos fazer isso …
Fórmula genérica para extrair valores únicos no Excel
{= INDEX (lista_ref, CORRESPONDÊNCIA (0, CONTADOR (intervalo_de_output_expansão, lista_ref), 0))}
Ref_list: A lista da qual você deseja extrair valores únicos
Expanding_output_range: Agora, isso é muito importante. Este é o intervalo onde você deseja ver sua lista extraída. Este alcance deve ter um cabeçalho distinto que não faça parte da lista e sua fórmula estará abaixo do cabeçalho (se o cabeçalho estiver em E1, a Fórmula estará em E2).
Agora, expandir significa que, quando você arrasta sua fórmula para baixo, ela deve se expandir ao longo do intervalo de saída. Para fazer isso, você precisa dar referência ao título como $ E $ 1: E1 (Meu título está em E1). Quando eu o arrastar para baixo, ele se expandirá. Na E2, será $ E $ 1: E1. Na E3, será $ E $ 1: E2. Na E2, será $ E $ 1: E3 e assim por diante.
Agora vamos ver um exemplo. Isso deixará isso claro.
Exemplo de extração de valores únicos no Excel
Então, aqui estou esta lista de clientes na coluna UMA no alcance A2: A16. Agora, na coluna E, desejo obter valores exclusivos apenas dos clientes. Agora, esse intervalo A2: A16 também pode aumentar, então quero que minha fórmula busque qualquer novo nome de cliente da lista, sempre que a lista aumentar.
Ok, agora para buscar valores únicos da coluna A, escreva esta fórmula em Célula E2, e acertar CTRL + SHIFT + ENTER para torná-lo uma fórmula de matriz.
{= ÍNDICE ($ A $ 2: A16, CORRESPONDÊNCIA (0, CONTADOR ($ E $ 1: E1, $ A $ 2: A16), 0))}
A $ 2: A16: Espero que essa lista se expanda e possa ter novos valores exclusivos que gostaria de extrair. É por isso que o deixei aberto de baixo por referência não absoluta de A16. Isso permitirá que ele se expanda sempre que você copiar a fórmula abaixo.
Portanto, sabemos como funcionam as funções INDEX e MATCH. A parte principal aqui é:
CONT.SE ($ E $ 1: E1, $ A $ 2: A16): Esta fórmula retornará uma matriz de 1s e 0s. Sempre que um valor na faixa $ E $ 1: E1 é encontrado na lista de critérios $ A $ 2: A16, o valor é convertido em 1 em sua posição no intervalo $ A $ 2: A16.
Agora, usando a função MATCH, estamos procurando os valores 0. Match retornará a posição do primeiro 0 encontrado no array retornado pela função COUNTIF. Em seguida, INDEX irá olhar para A $ 2: A16 para retornar o valor encontrado no índice retornado pela função MATCH.
Pode ser um pouco difícil de entender, mas funciona. O 0 no final da lista indica que não há mais valores exclusivos. Se você não vir esse 0 no final, deve copiar a fórmula nas células abaixo.
Agora para evitar #N / D em você pode usar a função IFERROR do excel.
{= IFERROR (INDEX ($ A $ 2: A16, MATCH (0, COUNTIF ($ E $ 1: $ E1, $ A $ 2: A16), 0)), "")}
Então, sim, você pode usar esta fórmula para obter valores exclusivos de uma lista. No excel 2019 com assinatura do Office 365, a Microsoft oferece uma função chamada UNIQUE. Ele simplesmente pega um intervalo como argumento e retorna uma matriz de valores exclusivos. Não está disponível no Microsoft Excel 2016 para compra única.
⇬ Fazer download do arquivo:
Fórmula do Excel para extrair valores únicos de uma listaFórmula do Excel para extrair valores únicos de uma lista
Como contar valores únicos no Excel
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade
Como usar a função PROCV no Excel
Como usar a função CONT.SE no Excel
Como usar a função SUMIF no Excel