Devolver vários valores correspondentes com base em múltiplos critérios no Excel (Guia completo)
Os utilizadores do Excel frequentemente enfrentam cenários em que é necessário extrair vários valores que satisfazem simultaneamente diversos critérios e apresentar todos os resultados correspondentes numa coluna, numa linha ou consolidados numa única célula. Este guia explora métodos para todas as versões do Excel, bem como a nova função FILTRAR disponível no Excel 365 e 2021.
Devolver vários valores correspondentes com base em múltiplos critérios numa única célula
No Excel, extrair vários valores correspondentes com base em múltiplos critérios contidos numa única célula é um desafio frequente — e aqui apresentamos duas soluções eficazes.
Método 1: Utilizar a função TEXTJOIN (Excel 365 / 2021,2019)
Para obter todos os valores correspondentes numa única célula com delimitadores, a função TEXTJOIN é a solução ideal.
Introduza ou copie a seguinte fórmula para uma célula vazia e, em seguida, prima Enter (Excel 2021 e Excel 365) ou Ctrl + Shift + Enter no Excel 2019 para obter o resultado:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) verifica se cada linha cumpre ambas as condições: «vendedor igual a E2» e «mês igual a F2». Quando as duas condições são verdadeiras, o resultado é 1; caso contrário, é 0. O asterisco * indica que ambas as condições têm de ser satisfeitas simultaneamente.
- SE(..., $C$2:$C$21, «») devolve o nome do produto se a linha corresponder; caso contrário, devolve uma célula vazia.
- TEXTJOIN(", ", VERDADEIRO, ...) combina todos os nomes de produtos não vazios numa única célula, separados por ", ".
Método 2: Utilizar o Kutools para Excel
O Kutools para Excel oferece uma solução poderosa e simples, permitindo-lhe recuperar e combinar rapidamente várias correspondências numa única célula com base em múltiplos critérios — sem fórmulas complexas.
Após instalar o Kutools para Excel, proceda da seguinte forma:
- Selecione o intervalo de dados a partir do qual pretende obter todos os valores correspondentes com base nos critérios.
- Em seguida, clique em Kutools > Mesclar e Dividir > Mesclar Linhas Avançado, conforme ilustrado:
- Na caixa de diálogo Mesclar Linhas Avançado, configure as seguintes opções:
- Escolha os cabeçalhos de coluna que contêm os seus critérios de correspondência (por exemplo, Vendedor e Mês). Para cada coluna selecionada, clique em Chave Primária para definir essas colunas como as suas condições de pesquisa.
- Clique no cabeçalho da coluna onde pretende obter os Resultados Mesclados (por exemplo, Produto). Na secção Combinar, selecione o delimitador pretendido — como vírgula, espaço ou um separador personalizado.
- Por fim, clique no botão OK.
Resultado: O Kutools funde instantaneamente todos os valores correspondentes numa única célula, com base em cada combinação única de critérios.
Devolver vários valores correspondentes com base em múltiplos critérios numa coluna
Quando precisa de extrair e apresentar vários registos correspondentes a partir de um conjunto de dados com base em múltiplas condições, devolvendo os resultados num formato de coluna vertical, o Excel oferece soluções poderosas.
Método 1: Utilizar uma fórmula matricial (para todas as versões)
Pode utilizar a seguinte fórmula matricial para devolver os resultados verticalmente numa coluna:
1. Copie ou introduza a seguinte fórmula numa célula vazia:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Prima Ctrl + Shift + Enter para obter o primeiro resultado correspondente, selecione a primeira célula com a fórmula e arraste a alça de preenchimento para baixo até surgir uma célula vazia. Assim, todos os valores correspondentes são devolvidos, conforme ilustrado na imagem seguinte:
- $A$2:$A$18=$E$2: Verifica se o vendedor corresponde ao valor na célula E2.
- $B$2:$B$18=$F$2: Verifica se o mês corresponde ao valor na célula F2.
- * é um operador lógico E (ambas as condições têm de ser verdadeiras).
- LIN($C$2:$C$18)-LIN($C$2)+1: Gera um número de linha relativo para cada produto.
- MENOR(..., LIN(1:1)): Obtém a n-ésima linha correspondente mais pequena (à medida que arrasta a fórmula para baixo).
- ÍNDICE(...): Devolve o valor da linha correspondente.
- SEERRO(..., «»): Devolve uma célula vazia quando já não existirem correspondências.
Método 2: Utilizar a função FILTRAR (Excel 365 / 2021)
Se estiver a utilizar o Excel 365 ou o Excel 2021, a função FILTRO é uma excelente opção para obter múltiplos resultados com base em vários critérios, graças à sua simplicidade, clareza e capacidade de expandir dinamicamente os resultados sem recorrer a fórmulas matriciais complexas.
Copie ou introduza a fórmula abaixo numa célula vazia e prima Enter; todos os registos correspondentes serão devolvidos com base em múltiplos critérios.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTRAR(...) devolve todos os valores de C2:C18 que cumprem ambas as condições.
- (A2:A18=E2)*(B2:B18=F2): Matriz lógica que verifica se há correspondência entre vendedor e mês.
- «Sem correspondência»: Mensagem opcional exibida quando nenhum valor for encontrado.
Devolver vários valores correspondentes com base em múltiplos critérios numa linha
Os utilizadores do Excel frequentemente precisam de extrair vários valores de um conjunto de dados que cumpram múltiplas condições e apresentá-los horizontalmente (numa linha). Esta abordagem é ideal para criar relatórios dinâmicos, painéis ou tabelas resumo quando o espaço vertical é limitado. Nesta secção, exploraremos dois métodos poderosos.
Método 1: Utilização de uma fórmula matricial (para todas as versões)
As fórmulas matriciais tradicionais permitem extrair múltiplos valores correspondentes com as funções ÍNDICE, MENOR, SE e COLUNA. Ao contrário da extração vertical (baseada em colunas), adaptamos a fórmula para devolver os resultados numa única linha.
1. Copie ou introduza a fórmula abaixo numa célula vazia:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Prima as teclas Ctrl + Shift + Enter para obter o primeiro resultado correspondente e, em seguida, selecione a primeira célula com a fórmula e arraste-a para a direita ao longo das colunas para obter todos os resultados.
- $A$2:$A$18=$E$2: Verifica se o vendedor corresponde.
- $B$2:$B$18=$F$2: Verifica se o mês corresponde.
- *: Operador lógico E — ambas as condições têm de ser verdadeiras.
- LIN($C$2:$C$18)-LIN($C$2)+1: Gera números de linha relativos para definir o número total de linhas.
- COL(A1): Ajusta a correspondência a devolver conforme a fórmula é arrastada para a direita.
- SEERRO(...): Evita erros quando já não há correspondências disponíveis.
Método 2: Utilização da função FILTRO (Excel 365 / 2021)
Copie ou introduza a fórmula abaixo numa célula vazia e prima Enter — todos os valores correspondentes serão extraídos e dispostos numa linha. Veja a captura de ecrã:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTRAR(...): Obtém os valores correspondentes da coluna C com base nas duas condições.
- (A2:A18=E2)*(B2:B18=F2): Ambas as condições têm de ser verdadeiras.
- TRANSPOR(...): Converte a matriz vertical devolvida pela função FILTRAR numa matriz horizontal.
🔚 Conclusão
A obtenção de múltiplos valores correspondentes com base em vários critérios no Excel pode ser feita de diversas formas, consoante a forma como pretende apresentar os resultados — numa coluna, numa linha ou numa única célula.
- Para utilizadores com Excel 365 ou Excel 2021, a função FILTRAR oferece uma solução moderna, dinâmica e elegante que reduz a complexidade ao mínimo.
- Para quem utiliza versões mais antigas, as fórmulas matriciais continuam a ser ferramentas poderosas — embora exijam um pouco mais de configuração e atenção.
- Além disso, se pretender consolidar os resultados numa única célula ou preferir uma solução sem código, a função TEXTJOIN ou ferramentas de terceiros como o Kutools para Excel podem simplificar consideravelmente o processo.
Escolha o método que melhor se adapta à sua versão do Excel e à disposição pretendida, e estará perfeitamente preparado para realizar pesquisas com múltiplos critérios de forma eficiente e precisa. Se quiser explorar ainda mais dicas e truques do Excel, o nosso site oferece milhares de tutoriais para o ajudar a dominar o Excel.
Mais artigos relacionados:
- Devolver vários Intervalo de valor de pesquisa numa única célula separada por vírgulas
- No Excel, podemos utilizar a função PROCV para devolver o primeiro valor correspondente de uma tabela, mas por vezes precisamos de extrair todos os valores correspondentes e separá-los por um delimitador específico — como vírgula, travessão, entre outros — numa única célula, conforme ilustrado na imagem seguinte. Como obter e devolver todos os valores correspondentes ao intervalo de pesquisa numa única célula, separados por vírgulas, no Excel?
- PROCV e devolver vários valores correspondentes de uma só vez na folha Google
- A função PROCV normal na Google Sheets permite-lhe encontrar e devolver o primeiro valor correspondente com base num determinado critério. No entanto, por vezes, poderá precisar de utilizar o PROCV para devolver todos os valores correspondentes, tal como ilustrado na imagem seguinte. Conhece alguma solução simples e eficaz para resolver esta tarefa na Google Sheets?
- PROCV e devolver vários valores a partir de uma lista suspensa
- No Excel, como utilizar o PROCV para devolver vários valores correspondentes a partir de uma lista suspensa — ou seja, ao selecionar um item na lista, todos os seus valores relacionados são apresentados imediatamente, tal como ilustrado na imagem seguinte? Neste artigo, mostro-lhe a solução passo a passo.
- PROCV e devolver vários valores verticalmente no Excel
- Normalmente, pode utilizar a função PROCV para obter o primeiro valor correspondente, mas por vezes pretende devolver todos os registos que correspondam a um determinado critério. Neste artigo, explico como usar o PROCV para devolver todos os valores correspondentes verticalmente, horizontalmente ou numa única célula.
- PROCV e devolver dados correspondentes entre dois valores no Excel
- No Excel, podemos utilizar a função PROCV normal para obter o valor correspondente com base num dado fornecido. Contudo, por vezes, pretendemos usar o PROCV para devolver o valor correspondente entre dois valores, conforme ilustrado na imagem seguinte. Como resolver esta tarefa no Excel?
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos