Note: The other languages of the website are Google-translated. Back to English

Como extrair valores únicos com base em critérios no Excel?

Supondo que você tenha o intervalo de dados esquerdo para o qual deseja listar apenas os nomes exclusivos da coluna B com base em um critério específico da coluna A para obter o resultado conforme mostrado na captura de tela abaixo. Como você poderia lidar com essa tarefa no Excel de forma rápida e fácil?

Extraia valores únicos com base em critérios com fórmula de matriz

Extraia valores únicos com base em vários critérios com fórmula de matriz

Extraia valores únicos de uma lista de células com um recurso útil

 

Extraia valores únicos com base em critérios com fórmula de matriz

Para resolver este trabalho, você pode aplicar uma fórmula de matriz complexa, faça o seguinte:

1. Insira a fórmula abaixo em uma célula em branco onde deseja listar o resultado da extração, neste exemplo, vou colocá-la na célula E2 e, em seguida, pressione Shift + Ctrl + Enter chaves para obter o primeiro valor exclusivo.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Em seguida, arraste a alça de preenchimento para baixo até as células até que as células em branco sejam exibidas, e agora todos os valores exclusivos com base no critério específico foram listados, consulte a captura de tela:

NOTA Na fórmula acima: B2: B15 é o intervalo de colunas que contém os valores exclusivos dos quais você deseja extrair, A2: A15 é a coluna que contém o critério no qual você se baseia, D2 indica o critério no qual você deseja listar os valores exclusivos, e E1 é a célula acima da fórmula inserida.

Extraia valores únicos com base em vários critérios com fórmula de matriz

Se você deseja extrair os valores exclusivos com base em duas condições, aqui está outra fórmula de matriz que pode lhe fazer um favor, faça o seguinte:

1. Insira a fórmula abaixo em uma célula em branco onde você deseja listar os valores únicos, neste exemplo, vou colocá-la na célula G2 e, em seguida, pressione Shift + Ctrl + Enter chaves para obter o primeiro valor exclusivo.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Em seguida, arraste a alça de preenchimento para baixo até as células até que as células em branco sejam exibidas, e agora todos os valores exclusivos com base nas duas condições específicas foram listados, consulte a captura de tela:

NOTA Na fórmula acima: C2: C15 é o intervalo de colunas que contém os valores exclusivos dos quais você deseja extrair, A2: A15 e E2 são o primeiro intervalo com os critérios nos quais você deseja extrair valores exclusivos com base, B2: B15 e F2 são o segundo intervalo com os critérios nos quais você deseja extrair valores exclusivos com base, e G1 é a célula acima da fórmula inserida.

Extraia valores únicos de uma lista de células com um recurso útil

Às vezes, você quer apenas extrair os valores únicos de uma lista de células, aqui, vou recomendar uma ferramenta útil-Kutools for Excel, Com o seu Extraia células com valores únicos (inclua a primeira duplicata) utilitário, você pode extrair rapidamente os valores exclusivos.

NOTAPara aplicar isso Extraia células com valores únicos (inclua a primeira duplicata), em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

1. Clique em uma célula para a qual deseja gerar o resultado. (Nota: Não clique em uma célula na primeira linha.)

2. Então clique Kutools > Fórmula Helper > Fórmula Helper, veja a captura de tela:

3. No Auxiliar de Fórmulas caixa de diálogo, execute as seguintes operações:

  • Selecionar Texto opção do Fórmula para cada ocasião lista suspensa;
  • Então escolha Extraia células com valores únicos (inclua a primeira duplicata) de Escolha uma fromula caixa de listagem;
  • Na direita Entrada de argumentos seção, selecione uma lista de células das quais deseja extrair valores exclusivos.

4. Então clique Ok botão, o primeiro resultado é exibido na célula, selecione a célula e arraste a alça de preenchimento sobre as células que você deseja listar todos os valores exclusivos até que as células em branco sejam mostradas, veja a captura de tela:

Baixe grátis o Kutools para Excel agora!


Mais artigos relativos:

  • Conte o número de valores únicos e distintos de uma lista
  • Supondo que você tenha uma longa lista de valores com alguns itens duplicados, agora você deseja contar o número de valores únicos (os valores que aparecem na lista apenas uma vez) ou valores distintos (todos os valores diferentes na lista, isso significa único valores + primeiros valores duplicados) em uma coluna, como mostrado à esquerda na captura de tela. Neste artigo, irei falar sobre como lidar com esse trabalho no Excel.
  • Soma de valores exclusivos com base em critérios no Excel
  • Por exemplo, eu tenho um intervalo de dados que contém as colunas Nome e Pedido, agora, para somar apenas valores únicos na coluna Pedido com base na coluna Nome, conforme mostrado a seguir. Como resolver essa tarefa com rapidez e facilidade no Excel?
  • Concatenar valores únicos no Excel
  • Se eu tiver uma longa lista de valores preenchida com alguns dados duplicados, agora, quero encontrar apenas os valores exclusivos e, em seguida, concatená-los em uma única célula. Como eu poderia lidar com esse problema de forma rápida e fácil no Excel?

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (40)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Olá, obrigado por este tutorial, ele funciona perfeitamente. Estou tentando modificá-lo para funcionar com uma condição OR, mas parece que não está funcionando - isso é possível? por exemplo =ÍNDICE($B$2:$B$17, CORRESP(0, SE(OU($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), CONT.SE($E$1 :$E1, $B$2:$B$17), ""), 0))
Este comentário foi feito pelo moderador no site
Obrigado por este tutorial! Também estou tentando modificar a fórmula, como o comentarista acima, mas com uma condição AND para que ela atenda a outros critérios condicionais (por exemplo, para este exemplo, gostaria de ver apenas coisas acima de um determinado limite). Você pode por favor aconselhar? Obrigada!
Este comentário foi feito pelo moderador no site
Ei, Uma maneira de fazer isso: Substitua a fórmula if por sumproduct((condition1=rng1)+(condition2=rng2))*countif(... Funcionou para mim. Boa sorte! Ao substituir o + por um * você pode faça disso uma condição OR, mas cuide bem dos colchetes!
Este comentário foi feito pelo moderador no site
Obrigado, isso é ótimo!
Este comentário foi feito pelo moderador no site
Obrigado por isso eu tentei isso e parece estar funcionando bem intermitentemente. O problema que continua se repetindo é que, às vezes, apenas o primeiro valor correspondente retorna e é duplicado quando estou arrastando para baixo para retornar todos os valores correspondentes. Como faço para prevenir isso? Alguma sugestão?
Este comentário foi feito pelo moderador no site
Isso funciona muito bem, mas sempre que o valor que está colocando é duplicado, ele só coloca o valor uma vez. Por exemplo, se sua lista contiver duas Lucys, ela traz apenas uma Lucy para a nova mesa. Existe uma maneira de corrigir isso?
Este comentário foi feito pelo moderador no site
Ao usar essa fórmula ele fica repetindo o primeiro valor, como você faz isso parar e fornecer a lista de valores que iguala o produto em D2?
Este comentário foi feito pelo moderador no site
Oi, para parar a repetição do primeiro valor enquanto você arrasta para baixo, você deve COUNTIF a célula ACIMA da célula em que você está colocando a fórmula. Por exemplo, se a fórmula estiver indo em E2, você deve digitar countif($E$1:$E1...
Este comentário foi feito pelo moderador no site
Olá Ryan. Fórmulas funciona muito bem, no entanto, ao arrastar para baixo o primeiro valor continua repetindo. Certifiquei-me de que CONT.SE referencia a célula ACIMA da célula com a fórmula, mas ainda repete o primeiro valor ao arrastar para baixo? (por exemplo, se a fórmula de matriz estiver em C2, CONT.SE aponta para a célula $C$1:$C$1)
Este comentário foi feito pelo moderador no site
Olá Ryan. Fórmulas funciona muito bem, no entanto, ao arrastar para baixo o primeiro valor continua repetindo. Certifiquei-me de que CONT.SE referencia a célula ACIMA da célula com a fórmula, mas ainda repete o primeiro valor ao arrastar para baixo? (por exemplo, se a fórmula de matriz estiver em C2, CONT.SE aponta para a célula $C$1:$C$1)
Este comentário foi feito pelo moderador no site
Provavelmente não funciona porque você bloqueou as células - Tente substituir $C$1:$C$1 por $C$1:$C1
Este comentário foi feito pelo moderador no site
isso foi super útil, mas continuo recebendo duplicatas de todos os nomes assim:
Corça, Jane
Corça, Jane
Hoover, Tom
Hoover, Tom

Como posso parar isso?
Este comentário foi feito pelo moderador no site
Olá, estou recebendo o erro "#N/A" na "função Match", você pode me orientar?
Este comentário foi feito pelo moderador no site
=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Sim","Não"))) Desejo que "todas as condições" sejam satisfeitas para dizer sim ... excel refletindo erro nesta fórmula .. pls aconselhar
Este comentário foi feito pelo moderador no site
na verdade, eu quero que a célula reflita "YES" se (AL2="AP" e AK2="AD" e Z2>500000)
Este comentário foi feito pelo moderador no site
Estou recebendo um erro #N/A na função Match com esta fórmula. Você pode me ajudar?
Este comentário foi feito pelo moderador no site
Olá, estou recebendo o erro "#N/A" na "função Match", você pode me orientar?
Este comentário foi feito pelo moderador no site
Se você receber o erro #N/A, vá para sua fórmula e use Control + Shift + Enter em vez de Enter.
Este comentário foi feito pelo moderador no site
Estou obtendo 0 em vez dos resultados esperados, a fórmula está indo muito bem para dados na mesma planilha, você tem alguma solução para dados em planilhas diferentes?

esta é a minha fórmula

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
Este comentário foi feito pelo moderador no site
Olá, Gon,
Depois de inserir a fórmula, você deve pressionar as teclas Ctrl + Shift + Enter juntas, não apenas a tecla Enter.
Por favor, tente, obrigado!
Este comentário foi feito pelo moderador no site
Olá Gon, espero que esteja bem. Gostaria de saber se você pode resolver este problema. Estou recebendo o mesmo erro quando a fórmula vem de uma planilha diferente. Eu aprecio compartilhar a solução se você conseguiu.
Este comentário foi feito pelo moderador no site
Obrigado!
Este comentário foi feito pelo moderador no site
Como eu faria com que essa fórmula retornasse cada uma das duplicatas em vez de um de cada um dos nomes? Por exemplo, no exemplo acima, como eu faria com que a coluna de resultados (B:B) retornasse Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? Estou usando isso como uma ferramenta de orçamento puxando para resumos de contas específicos de um livro-razão. No entanto, vários dos valores e descrições de transações são duplicados no razão geral. Uma vez que o primeiro dos valores duplicados é puxado, nenhum mais deles é puxado.
Este comentário foi feito pelo moderador no site
Olá Joe,
Para extrair todos os valores correspondentes com base em um critério de célula específico, a seguinte fórmula de matriz pode ajudá-lo, veja a captura de tela:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Depois de inserir a fórmula, pressione as teclas Shift + Ctrl + Enter juntas para obter o resultado correto e arraste a alça de preenchimento para baixo para obter todos os valores.
Espero que isso possa ajudá-lo, obrigado!
Este comentário foi feito pelo moderador no site
Até agora tudo bem. Consigo duplicar os resultados na planilha de teste, fazer alterações na matriz e corrigir a fórmula para levar em conta as alterações que fiz. Eu pretendo mover isso para a folha mestra hoje e ver como funciona. Obrigado pela ajuda!
Este comentário foi feito pelo moderador no site
Ok, então funciona na pasta de trabalho mestre. Há uma exceção para a qual não consegui determinar a causa: se a matriz (no meu caso, a contabilidade que eu tinha começando na linha 3) não começar na linha 1, os valores retornados estão incorretos. O que causa esse problema e qual termo na fórmula o corrige? Obrigado novamente por sua ajuda com isso!
Este comentário foi feito pelo moderador no site
Última pergunta: Se eu quiser que a coluna de resultados retorne todos os valores não associados a KTE ou KTO (portanto, D:D seria Tom, Nocol, Lily, Angelina, Genna), como eu faria isso?
Este comentário foi feito pelo moderador no site
Para mim a fórmula não funciona. Eu pressiono ctrl shift enter e ainda recebo um erro N/A. Eu gostaria de acrescentar que eu preparei exatamente os mesmos dados que no tutorial. Qual o motivo de não funcionar?
Este comentário foi feito pelo moderador no site
Isso funcionou muito bem para mim com um valor de pesquisa específico. No entanto, se eu quisesse usar um curinga para pesquisar valores parciais, como faria isso? Por exemplo, se eu quisesse pesquisar todos os nomes associados ao KT?

Estou usando esta função para procurar células que contêm vários textos. Por exemplo, se cada produto também tivesse um subproduto dentro da mesma célula, mas eu estivesse apenas procurando por nomes associados ao subproduto "elfo".

KTE - elfo
KTE- bola
KTE - piano
KTO - elfo
KTO- bola
KTO - piano
Este comentário foi feito pelo moderador no site
Existe uma maneira de fazer isso funcionar enquanto PERMITEM valores duplicados? Por exemplo, quero que todas as instâncias de Lucy sejam listadas nos resultados.
Este comentário foi feito pelo moderador no site
Olá, Konstantin,
Para extrair todos os valores correspondentes, incluindo as duplicatas com base em um critério de célula específico, a seguinte fórmula de matriz pode ajudá-lo, veja a captura de tela:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Depois de inserir a fórmula, pressione as teclas Shift + Ctrl + Enter juntas para obter o resultado correto e arraste a alça de preenchimento para baixo para obter todos os valores.
Espero que isso possa ajudá-lo, obrigado!
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL