Retornar vários valores correspondentes com base em múltiplos critérios no Excel (Guia completa)
Os usuários do Excel frequentemente enfrentam cenários onde é necessário extrair vários valores que satisfaçam vários critérios simultaneamente e apresentar todos os resultados correspondentes em uma coluna, linha ou consolidados em uma única célula. Este guia explora métodos para todas as versões do Excel, bem como a nova função FILTER disponível no Excel 365 e 2021.
Retornar vários valores correspondentes com base em múltiplos critérios em uma única célula
No Excel, extrair vários valores correspondentes com base em múltiplos critérios dentro de uma única célula é um desafio comum. Aqui estão dois métodos eficientes para isso.
Método 1: Usando a função Textjoin (Excel365 / 2021, 2019)
Para obter todos os valores correspondentes em uma única célula com delimitadores, a função TEXTJOIN pode ajudá-lo.
Digite ou copie a seguinte fórmula em uma célula em branco, depois pressione a tecla 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 atende ambas as condições: “Vendedor igual a E2” e “Mês igual a F2.” Se ambas as condições forem atendidas, o resultado é 1; caso contrário, é 0. O asterisco * significa que ambas as condições devem ser verdadeiras.
- SE(..., $C$2:$C$21, "") retorna o nome do produto se a linha corresponder; caso contrário, retorna em branco.
- TEXTJOIN(", ", VERDADEIRO, ...) combina todos os nomes de produtos não vazios em uma célula, separados por ", ".
Método 2: Usando Kutools para Excel
Kutools para Excel oferece uma solução poderosa e simples, permitindo que você recupere e combine rapidamente várias correspondências em uma única célula com base em múltiplos critérios sem fórmulas complexas.
Após instalar Kutools para Excel, siga estas instruções:
- Selecione o intervalo de dados do qual deseja obter todos os valores correspondentes com base nos critérios.
- Em seguida, clique em Kutools > Mesclar e Dividir > Mesclar Linhas Avançado, veja a captura de tela:
- Na caixa de diálogo Mesclar Linhas Avançado, configure as seguintes opções:
- Escolha os cabeçalhos de coluna que contêm seus critérios de correspondência (por exemplo, Vendedor e Mês). Para cada coluna selecionada, clique em Chave Primária para defini-los como suas condições de pesquisa.
- Clique no cabeçalho da coluna onde deseja os resultados combinados (por exemplo, Produto). Na seção Combinar, selecione seu delimitador preferido (por exemplo, vírgula, espaço ou separador personalizado).
- Por fim, clique no botão OK.
Resultado: Kutools irá mesclar instantaneamente todos os valores correspondentes em uma única célula por combinação única de critérios.
Retornar vários valores correspondentes com base em múltiplos critérios em uma coluna
Quando você precisa extrair e exibir vários registros correspondentes de um conjunto de dados com base em várias condições, retornando os resultados em formato de coluna vertical, o Excel oferece várias soluções poderosas.
Método 1: Usando uma fórmula de matriz (para todas as versões)
Você pode usar a seguinte fórmula de matriz para retornar resultados verticalmente em uma coluna:
1. Copie ou insira a seguinte fórmula em uma célula em branco:
=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. Pressione Ctrl + Shift + Enter para obter o primeiro resultado correspondente, e então selecione a primeira célula da fórmula e arraste a alça de preenchimento para baixo até que uma célula em branco seja exibida, agora todos os valores correspondentes são retornados conforme mostrado na captura de tela abaixo:
- $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 devem 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)): Busca o n-ésimo menor número de linha correspondente (à medida que a fórmula é arrastada para baixo).
- ÍNDICE(...): Retorna o produto da linha correspondente.
- SEERRO(..., ""): Retorna uma célula em branco se não houver mais correspondências.
Método 2: Usando a função Filter (Excel365 / 2021)
Se você estiver usando o Excel 365 ou Excel 2021, a função FILTER é uma excelente escolha para retornar vários resultados com base em múltiplos critérios, graças à sua simplicidade, clareza e capacidade de derramar resultados dinamicamente sem fórmulas de matriz complexas.
Copie ou insira a fórmula abaixo em uma célula em branco, depois pressione a tecla Enter, todos os registros correspondentes serão retornados com base nos múltiplos critérios.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTRAR(...) retorna todos os valores de C2:C18 onde ambas as condições são atendidas.
- (A2:A18=E2)*(B2:B18=F2): Array lógico que verifica o vendedor e o mês correspondentes.
- "Sem correspondência": Mensagem opcional se nenhum valor for encontrado.
Retornar vários valores correspondentes com base em múltiplos critérios em uma linha
Os usuários do Excel muitas vezes precisam extrair vários valores de um conjunto de dados que atendam a várias condições e exibi-los horizontalmente (em uma linha). Isso é útil para criar relatórios dinâmicos, dashboards ou tabelas resumo onde o espaço vertical é limitado. Nesta seção, exploraremos dois métodos poderosos.
Método 1: Usando uma fórmula de matriz (para todas as versões)
As fórmulas de matriz tradicionais permitem extrair vários valores correspondentes usando funções ÍNDICE, MENOR, SE e COLUNA. Ao contrário da extração vertical (baseada em colunas), ajustamos a fórmula para retornar resultados em uma linha.
1. Copie ou insira a fórmula abaixo em uma célula em branco:
=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. Pressione Ctrl + Shift + Enter para obter o primeiro resultado correspondente, e então selecione a primeira célula da fórmula e arraste a fórmula para a direita através das colunas para recuperar 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.
- *: Lógica E — ambas as condições devem ser verdadeiras.
- LIN($C$2:$C$18)-LIN($C$2)+1: Cria números de linha relativos.
- COL(A1): Ajusta qual correspondência retornar, dependendo de quão longe a fórmula foi arrastada para a direita.
- SEERRO(...): Previne erros quando as correspondências se esgotarem.
Método 2: Usando a função Filter (Excel365 / 2021)
Copie ou insira a fórmula abaixo em uma célula em branco, depois pressione a tecla Enter, todos os valores correspondentes são extraídos e localizados em uma linha. Veja a captura de tela:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTRAR(...): Recupera valores correspondentes da coluna C com base nas duas condições.
- (A2:A18=E2)*(B2:B18=F2): Ambas as condições devem ser verdadeiras.
- TRANSPOR(...): Converte a matriz vertical retornada por FILTRAR em uma matriz horizontal.
🔚 Conclusão
Recuperar vários valores correspondentes com base em múltiplos critérios no Excel pode ser feito de várias maneiras, dependendo de como você deseja exibir os resultados — seja em uma coluna, linha ou dentro de uma única célula.
- Para usuários com Excel 365 ou Excel 2021, a função FILTER oferece uma solução moderna, dinâmica e elegante que minimiza a complexidade.
- Para aqueles que usam versões mais antigas, as fórmulas de matriz permanecem ferramentas poderosas, embora exijam um pouco mais de configuração e cuidado.
- Além disso, se você deseja consolidar resultados em uma única célula ou prefere uma solução sem código, a função TEXTJOIN ou ferramentas de terceiros como Kutools para Excel podem simplificar significativamente o processo.
Escolha o método que melhor se adapta à sua versão do Excel e ao layout preferido, e você estará bem equipado para lidar com pesquisas multicritério de forma eficiente e precisa. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais para ajudá-lo a dominar o Excel.
Mais artigos relacionados:
- Retornar vários valores de pesquisa em uma célula separada por vírgula
- No Excel, podemos aplicar a função PROCV para retornar o primeiro valor correspondente de uma tabela, mas, às vezes, precisamos extrair todos os valores correspondentes e separá-los por um delimitador específico, como vírgula, traço, etc., em uma única célula, conforme mostrado na captura de tela a seguir. Como podemos obter e retornar vários valores de pesquisa em uma célula separada por vírgula no Excel?
- Procurar e retornar vários valores correspondentes de uma só vez no Google Sheets
- A função PROCV normal no Google Sheets pode ajudá-lo a encontrar e retornar o primeiro valor correspondente com base em um dado. No entanto, às vezes, você pode precisar procurar e retornar todos os valores correspondentes, conforme mostrado na captura de tela a seguir. Você tem alguma boa e fácil maneira de resolver essa tarefa no Google Sheets?
- Procurar e retornar vários valores de uma lista suspensa
- No Excel, como você faria para procurar e retornar vários valores correspondentes de uma lista suspensa, o que significa que, ao escolher um item da lista suspensa, todos os seus valores relativos são exibidos de uma vez, conforme mostrado na captura de tela a seguir. Este artigo apresentará a solução passo a passo.
- Procurar e retornar vários valores verticalmente no Excel
- Normalmente, você pode usar a função PROCV para obter o primeiro valor correspondente, mas, às vezes, você deseja retornar todos os registros correspondentes com base em um critério específico. Este artigo discutirá como procurar e retornar todos os valores correspondentes verticalmente, horizontalmente ou em uma única célula.
- Procurar e retornar dados correspondentes entre dois valores no Excel
- No Excel, podemos aplicar a função PROCV normal para obter o valor correspondente com base em um dado. Mas, às vezes, queremos procurar e retornar o valor correspondente entre dois valores, conforme mostrado na captura de tela a seguir. Como você lidaria com essa tarefa no Excel?
Melhores Ferramentas de Produtividade para Office
Potencialize suas habilidades no Excel com o Kutools para Excel e experimente uma eficiência sem igual. O Kutools para Excel oferece mais de300 recursos avançados para aumentar sua produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
O Office Tab traz interface com abas para o Office e facilita muito o seu trabalho
- Habilite a edição e leitura com abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas abas na mesma janela, em vez de abrir novas janelas.
- Aumente sua produtividade em50% e reduza centenas de cliques do mouse todos os dias!