Podemos pesquisar e retornar vários valores em uma célula no Excel (separados por vírgula ou espaço)?
Esta pergunta já me foi feita várias vezes por muitos de meus colegas e leitores.
O Excel tem algumas fórmulas de pesquisa incríveis, como VLOOKUP, INDEX / MATCH (e agora XLOOKUP), mas nenhuma delas oferece uma maneira de retornar vários valores correspondentes. Tudo isso funciona identificando a primeira correspondência e retornando-a.
Então, fiz um pouco de codificação VBA para chegar a uma função personalizada (também chamada de Função Definida pelo Usuário) no Excel.
Atualizar: Depois que o Excel lançou matrizes dinâmicas e funções incríveis, como UNIQUE e TEXTJOIN, agora é possível usar uma fórmula simples e retornar todos os valores correspondentes em uma célula (abordado neste tutorial).Neste tutorial, vou mostrar como fazer isso (se você estiver usando a versão mais recente do Excel - Microsoft 365 com todas as novas funções), bem como uma maneira de fazer isso caso você esteja usando versões mais antigas ( usando VBA).
Então vamos começar!
Pesquisar e retornar vários valores em uma célula (usando fórmula)
Se você estiver usando o Excel 2016 ou versões anteriores, vá para a próxima seção, onde mostro como fazer isso usando o VBA.
Com a assinatura do Microsoft 365, seu Excel agora tem funções e recursos muito mais poderosos que não existiam nas versões anteriores (como XLOOKUP, Dynamic Arrays, funções UNIQUE / FILTER, etc.)
Portanto, se você estiver usando o Microsoft 365 (anteriormente conhecido como Office 365), você pode usar os métodos abordados nesta seção para pesquisar e retornar vários valores em uma única célula no Excel.
E como você verá, é uma fórmula muito simples.
Abaixo, tenho um conjunto de dados onde tenho os nomes das pessoas na coluna A e o treinamento que eles fizeram na coluna B.
Clique aqui para baixar o arquivo de exemplo e acompanhar
Para cada pessoa, quero descobrir qual treinamento ela completou. Na coluna D, tenho a lista de nomes exclusivos (da coluna A) e quero pesquisar e extrair rapidamente todo o treinamento que cada pessoa fez e obtê-los em um único conjunto (separados por uma vírgula).
Abaixo está a fórmula que fará isso:
= TEXTJOIN (",", VERDADEIRO, SE (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))
Após inserir a fórmula na célula E2, copie-a para todas as células onde deseja os resultados.
Como essa fórmula funciona?
Deixe-me desconstruir essa fórmula e explicar cada parte de como ela se junta nos dá o resultado.
O teste lógico na fórmula IF (D2 = $ A $ 2: $ A $ 20) verifica se o nome da célula D2 é o mesmo que no intervalo A2: A20.
Ele passa por cada célula no intervalo A2: A20 e verifica se o nome é o mesmo na célula D2 ou não. se for o mesmo nome, retorna VERDADEIRO, caso contrário, retorna FALSO.
Portanto, esta parte da fórmula fornecerá uma matriz conforme mostrado abaixo:
{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Como queremos apenas obter o treinamento para Bob (o valor na célula D2), precisamos obter todo o treinamento correspondente para as células que estão retornando TRUE na matriz acima.
Isso é feito facilmente especificando [valor_se_verdadeiro] parte da fórmula IF como o intervalo que contém o treinamento. Isso garante que, se o nome na célula D2 corresponder ao nome no intervalo A2: A20, a fórmula SE retornará todo o treinamento que a pessoa fez.
E sempre que a matriz retorna um FALSO, especificamos o valor [valor_se_falso] como “” (em branco), portanto, ele retorna um espaço em branco.
A parte IF da fórmula retorna a matriz conforme mostrado abaixo:
{“Excel”; ””; ””; ”PowerPoint”; ””; ””; ””; ””; ””; ””; ”;” ”;” ”;” ”;” ”;” ”; ””; ””; ””}
Onde tem os nomes do treinamento que Bob fez e espaços em branco sempre que o nome não era Bob.
Agora, tudo o que precisamos fazer é combinar esses nomes de treinamento (separados por uma vírgula) e retorná-los em uma célula.
E isso pode ser feito facilmente usando a nova fórmula TEXTJOIN (disponível no Excel2021-2022 e Excel no Microsoft 365)
A fórmula TEXTJOIN leva três argumentos:
- o Delimitador - que é “,” em nosso exemplo, pois quero que o treinamento seja separado por uma vírgula e um caractere de espaço
- TRUE - que diz à fórmula TEXTJOIN para ignorar as células vazias e apenas combinar aquelas que não estão vazias
- A fórmula If que retorna o texto que precisa ser combinado
Se você estiver usando o Excel no Microsoft 365 que já possui matrizes dinâmicas, basta inserir a fórmula acima e pressionar Enter. E se você estiver usando o Excel2021-2022, você precisa inserir a fórmula e segurar as teclas Control e Shift e pressionar Enter
Clique aqui para baixar o arquivo de exemplo e acompanhar
Obtenha vários valores de pesquisa em uma única célula (sem repetição)
Uma vez que a fórmula UNIQUE está disponível apenas para Excel no Microsoft 365, você não poderá usar este método no Excel2021-2022Caso haja repetições em seu conjunto de dados, conforme mostrado abaixo, você precisa alterar um pouco a fórmula para obter apenas uma lista de valores únicos em uma única célula.
No conjunto de dados acima, algumas pessoas fizeram o treinamento várias vezes. Por exemplo, Bob e Stan fizeram o treinamento em Excel duas vezes, e Betty fez o treinamento em MS Word duas vezes. Mas em nosso resultado, não queremos que o nome de um treinamento se repita.
Você pode usar a fórmula abaixo para fazer isso:
= TEXTJOIN (",", VERDADEIRO, ÚNICO (SE (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))
A fórmula acima funciona da mesma maneira, com uma pequena alteração. usamos a fórmula IF dentro da função UNIQUE para que, no caso de haver repetições no resultado da fórmula if, a função UNIQUE o remova.
Clique aqui para baixar o arquivo de exemplo
Pesquisar e retornar vários valores em uma célula (usando VBA)
Se você estiver usando o Excel 2016 ou versões anteriores, não terá acesso à fórmula TEXTJOIN. Portanto, a melhor maneira de pesquisar e obter vários valores correspondentes em uma única célula é usando uma fórmula personalizada que você pode criar usando o VBA.Para obter vários valores de pesquisa em uma única célula, precisamos criar uma função em VBA (semelhante à função VLOOKUP) que verifica cada célula em uma coluna e, se o valor de pesquisa for encontrado, adiciona-o ao resultado.
Aqui está o código VBA que pode fazer isso:
'Código por Sumit Bansal (https://trumpexcel.com) Função SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Função Final
Onde colocar este código?
- Abra uma pasta de trabalho e clique em Alt + F11 (isso abre a janela do Editor VBA).
- Nesta janela do Editor VBA, à esquerda, há um explorador de projeto (onde todas as pastas de trabalho e planilhas são listadas). Clique com o botão direito em qualquer objeto na pasta de trabalho onde deseja que este código funcione e vá para Inserir -> Módulo.
- Na janela do módulo (que aparecerá à direita), copie e cole o código acima.
- Agora você está pronto. Vá para qualquer célula da pasta de trabalho e digite = SingleCellExtract e insira os argumentos de entrada necessários (ou seja, LookupValue, LookupRange, ColumnNumber).
Como essa fórmula funciona?
Esta função funciona de forma semelhante à função VLOOKUP.
Leva 3 argumentos como entradas:
1. Valor de pesquisa - Uma string que precisamos pesquisar em um intervalo de células.
2. LookupRange - Uma matriz de células de onde precisamos buscar os dados ($ B3: $ C18 neste caso).
3. ColumnNumber - É o número da coluna da tabela / matriz da qual o valor correspondente deve ser retornado (2 neste caso).
Quando você usa esta fórmula, ela verifica cada célula na coluna mais à esquerda no intervalo de pesquisa e quando encontra uma correspondência, adiciona ao resultado na célula na qual você usou a fórmula.
Lembrar: Salve a pasta de trabalho como uma pasta de trabalho habilitada para macro (.xlsm ou .xls) para reutilizar essa fórmula novamente. Além disso, essa função estaria disponível apenas nesta pasta de trabalho e não em todas as pastas de trabalho.
Clique aqui para baixar o arquivo de exemplo
Aprenda a automatizar tarefas repetitivas chatas com VBA no Excel. Junte-se a Curso Excel VBAObtenha vários valores de pesquisa em uma única célula (sem repetição)
Existe a possibilidade de que você tenha repetições nos dados.
Se você usar o código usado acima, ele também repetirá o resultado.
Se você deseja obter o resultado sem repetições, é necessário modificar um pouco o código.
Aqui está o código VBA que fornecerá vários valores de pesquisa em uma única célula sem nenhuma repetição.
'Code by Sumit Bansal (https://trumpexcel.com) Função MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells (J, 1) = Lookupvalue Then If LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Then GoTo Skip End If End If Next J Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Função
Depois de colocar este código no Editor VB (como mostrado acima no tutorial), você será capaz de usar o MultipleLookupNoRept função.
Aqui está um instantâneo do resultado que você obterá com este MultipleLookupNoRept função.
Clique aqui para baixar o arquivo de exemplo
Neste tutorial, abordei como usar fórmulas e VBA no Excel para encontrar e retornar vários valores de pesquisa em uma célula no Excel.
Embora possa ser feito facilmente com uma fórmula simples se você estiver usando o Excel na assinatura do Microsoft 365, se você estiver usando versões anteriores e não tiver acesso a funções como TEXTJOIN, você ainda pode fazer isso usando VBA criando seu própria função personalizada.
