Como devolver vários intervalos de valores de pesquisa, separados por vírgulas, numa única célula?
Ao trabalhar com dados no Excel, poderá deparar-se com situações em que precise de obter vários valores correspondentes a uma única procura e apresentá-los numa só célula, separados por vírgulas, travessões ou outro delimitador. Embora funções incorporadas como «VLOOKUP» e «ÍNDICE-CORRESP» sejam excelentes para encontrar uma correspondência individual, combinar múltiplos resultados numa única célula exige uma abordagem mais avançada. Este artigo mostra-lhe algumas formas eficazes de resolver esta tarefa no Excel.
Devolver vários Intervalo de valor de pesquisa numa célula separados por vírgulas
Devolver vários Intervalo de valor de pesquisa numa célula separados por vírgulas com as funções TEXTJOIN e FILTER
Se tiver acesso ao Excel 365 ou ao Excel 2021 e versões posteriores, utilizar uma fórmula de matriz dinâmica é a forma mais simples.
Aplique a fórmula abaixo numa célula vazia para obter o resultado e, em seguida, prima Enter.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))Depois, arraste a alça de preenchimento para baixo para copiar a fórmula para outras células; veja a imagem:
- FILTER(B2:B15; A2:A15=D2): extrai todos os valores da coluna B cujas células correspondentes na coluna A sejam iguais ao valor em D2.
- TEXTJOIN(«, »; VERDADEIRO; ...): combina os valores resultantes numa única cadeia, separados por vírgulas.
Devolver vários Intervalo de valor de pesquisa numa célula separados por vírgulas com Kutools para Excel
«Kutools para Excel» é um suplemento concebido para simplificar tarefas complexas no Excel. A sua avançada funcionalidade «Pesquisa um-para-muitos» permite-lhe extrair facilmente todos os valores correspondentes para uma única célula, utilizando um delimitador à sua escolha — sem fórmulas complicadas nem programação em VBA.
Após instalar o Kutools para Excel, siga estes passos:
Clique em «Kutools» > «Super PROC» > «Pesquisa um-para-muitos (devolve vários resultados)» para abrir a caixa de diálogo. Em seguida, especifique as operações conforme indicado abaixo:
- Selecione «Área de Colocação da Lista e Intervalo de valor de pesquisa» nas caixas de texto separadamente;
- Selecione o intervalo da tabela que pretende utilizar;
- Especifique os campos Coluna Chave e Coluna de retorno nos menus pendentes «Coluna Chave» e «Coluna de retorno», respetivamente;
- Por fim, clique no botão «OK».
Resultado:
Todas as células correspondentes com o mesmo valor foram combinadas numa única célula, separadas por vírgula; veja a imagem:
Devolver vários Intervalo de valor de pesquisa numa célula separados por vírgulas com uma Função Definida pelo Utilizador
Se não tiver o Excel 365 ou o Excel 2021, esta secção orientá-lo-á passo a passo na criação e utilização de uma FDU para devolver vários intervalos de valores de pesquisa numa única célula, separados por vírgulas.
1. Mantenha premidas as teclas «ALT» + «F11» para abrir a janela «Microsoft Visual Basic para Aplicações».
2. Clique em «Inserir» > «Módulo» e cole o seguinte código na janela do Módulo.
Código VBA: Devolver vários Intervalo de valor de pesquisa numa única célula separada por vírgulas
Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
'Updateby Extendoffice
Dim result As String
Dim i As Long
result = ""
For i = 1 To lookupRange.Rows.Count
If lookupRange.Cells(i, 1).Value = lookupValue Then
result = result & returnRange.Cells(i, 1).Value & delimiter
End If
Next i
If Len(result) > 0 Then
JoinLookupValues = Left(result, Len(result) - Len(delimiter))
Else
JoinLookupValues = ""
End If
End Function
3. Guarde este código e feche a janela do Módulo. Volte à sua folha de cálculo e introduza a seguinte fórmula numa célula vazia onde pretenda obter o resultado. Depois, arraste a alça de preenchimento para baixo para aplicar a fórmula às restantes células; veja a imagem:
=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")
Em conclusão, devolver vários intervalos de valores de pesquisa numa única célula, separados por vírgulas, pode ser feito de forma eficiente através de diversos métodos, consoante a sua versão do Excel e as suas necessidades. Ao escolher o método que melhor se adapta aos seus requisitos, irá agilizar a sua análise de dadose aumentar a produtividade. Se quiser descobrir mais dicas e truques do Excel,o nosso site oferece milhares de tutoriais.
Artigos Relacionados:
- Procurar com VLOOKUP e devolver vários valores sem duplicados
- Ao trabalhar com dados no Excel, poderá por vezes precisar de obter vários valores correspondentes a um determinado critério de pesquisa. No entanto, a função VLOOKUP padrão devolve apenas um único valor. Em situações em que existam várias correspondências e pretenda apresentá-las numa única célula sem duplicados, pode recorrer a métodos alternativos para alcançar este objetivo.
- Devolver vários valores correspondentes com base num ou em vários critérios
- Normalmente, encontrar um valor específico e obter o item correspondente é fácil para a maioria de nós com a função VLOOKUP. Mas já alguma vez tentou obter vários valores correspondentes com base num ou mais critérios, tal como ilustrado na imagem seguinte? Neste artigo, apresento-lhe algumas fórmulas para resolver esta tarefa complexa no Excel.
- Procurar com VLOOKUP e devolver o último valor correspondente
- Se tiver uma lista de itens repetidos várias vezes e quiser apenas saber o último valor correspondente ao seu dado especificado, como proceder?
- Procurar com VLOOKUP e devolver dados correspondentes entre dois valores
- No Excel, podemos aplicar a função VLOOKUP normal para obter o valor correspondente com base num dado fornecido. Porém, por vezes, queremos procurar com VLOOKUP e obter o valor correspondente entre dois valores, tal como mostrado na seguinte imagem. 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