Como concatenar valores únicos no Excel?
Ao trabalhar com planilhas, é comum encontrar situações em que você precisa mesclar (concatenar) apenas os valores únicos de uma coluna ou compilar listas que resumem entradas únicas com seus registros correspondentes. Lidar com duplicatas e apresentar informações resumidas não só organiza seus dados, mas também torna os relatórios mais claros e informativos. No Excel, existem várias maneiras práticas de alcançar esses objetivos, desde o uso de funções embutidas até a utilização de complementos avançados ou código personalizado. Este tutorial oferece uma exploração detalhada de vários métodos para concatenar valores únicos e listar entradas únicas junto com seus dados relacionados. As soluções abordadas atendem a diferentes versões do Excel e preferências de usuário, ajudando você a escolher a melhor abordagem para seu cenário.
Concatenar apenas valores únicos de uma coluna
- Usando as funções TEXTJOIN e UNIQUE
- Usando o Assistente de IA do Kutools
- Usando Função Definida pelo Usuário
- Usando fórmula avançada do Excel (solução alternativa)
Listar valores únicos e concatenar valores correspondentes
- Usando as funções TEXTJOIN e UNIQUE
- Usando Kutoolsfor Excel
- Usando código VBA
- Usando Tabela Dinâmica do Excel com fórmulas (solução alternativa)
Concatenar apenas valores únicos de uma coluna
Durante a análise de dados no Excel, uma tarefa frequente é mesclar apenas as entradas distintas em uma coluna em uma única célula. Isso pode ser particularmente útil para criar relatórios resumidos, evitar valores duplicados em uma lista ou preparar dados para processamento adicional. A escolha do método depende da sua versão do Excel, do tamanho do conjunto de dados e da sua familiaridade com fórmulas ou código. Os seguintes métodos abordam diferentes necessidades, destacam considerações para cada um e oferecem dicas práticas para garantir a execução correta.
Método 1: Usando as funções TEXTJOIN e UNIQUE
Para usuários do Excel365 e Excel2021, a introdução das funções TEXTJOIN e UNIQUE torna a combinação de valores únicos de uma coluna simples e flexível.
Essa solução é ideal quando sua coluna de dados é contínua e você deseja consolidar rapidamente todos os itens únicos em uma única célula com um delimitador escolhido. Ela elimina duplicatas automaticamente, é facilmente auditable e permite alterar o intervalo ou delimitador conforme necessário. No entanto, observe que essa abordagem está disponível apenas nas versões mais recentes do Excel; versões anteriores não suportam a função UNIQUE.
Na célula onde você deseja exibir o resultado, insira a seguinte fórmula (assumindo que seus dados estão nas células A2:A18):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18) filtra entradas duplicadas e retorna apenas os valores únicos do intervalo A2:A18.
- TEXTJOIN(", ", VERDADEIRO, ...) combina (concatena) esses valores únicos em uma célula, separados por vírgula e espaço. O argumento VERDADEIRO garante que quaisquer células vazias sejam ignoradas na concatenação.
Dicas úteis e solução de problemas:
- Certifique-se de que sua versão do Excel suporte as funções UNIQUE e TEXTJOIN. Se você vir um erro #NOME?, pode estar usando uma versão mais antiga.
- O delimitador usado em TEXTJOIN pode ser alterado para qualquer coisa que você preferir, como "; " ou "|".
- Se você adicionar ou remover dados no intervalo original, a fórmula será atualizada automaticamente.
- Para evitar espaços ou delimitadores extras involuntários, verifique novamente o argumento delimitador na fórmula.
Método 2: Usando o Assistente de IA do Kutools
Quando você precisa de uma maneira mais rápida e totalmente automatizada para concatenar valores únicos — sem escrever fórmulas — a ferramenta "Assistente de IA" do Kutools para Excel oferece uma solução prática que economiza tempo para usuários de todos os níveis de habilidade. Esse método é especialmente útil se você não estiver familiarizado com fórmulas avançadas do Excel ou se seus dados mudarem frequentemente, exigindo tarefas repetidas.
Após instalar o Kutools para Excel, acesse esse recurso clicando em "Kutools" > "AI Aide" para abrir o painel "Kutools AI Aide".
- Selecione as células que contêm os valores que você deseja combinar em uma única célula, garantindo que sua seleção corresponda aos seus dados pretendidos.
- Na caixa de chat, descreva seu requisito. Por exemplo, você pode digitar:
Concatenar valores únicos com vírgula do intervalo selecionado e colocar o resultado combinado na célula C2 - Pressione a tecla Enter ou clique no botão "Enviar" . A IA analisa sua solicitação e, após o processamento, pressione “Executar” para que o Kutools execute a operação. O resultado será retornado conforme descrito.
Notas e dicas:
- Verifique se você está executando a versão mais recente do Kutools para acessar todos os recursos de IA.
- Seja específico no comando de texto para melhores resultados; mencione o delimitador e a célula de destino.
- O Kutools AI é particularmente eficiente para grandes intervalos ou fluxos de trabalho que precisam ser repetidos em vários conjuntos de dados.
Método 3: Usando Função Definida pelo Usuário
Para usuários que exigem flexibilidade avançada, precisam de delimitadores personalizados ou desejam uma ferramenta reutilizável para várias pastas de trabalho, codificar uma Função Definida pelo Usuário (UDF) em VBA é uma maneira eficaz de concatenar automaticamente valores únicos. Essa solução VBA é compatível com todas as versões do Excel e não é restrita pela disponibilidade de novas funções.
- Você deve habilitar macros em sua pasta de trabalho.
- Salve o arquivo como "habilitado para macro" (.xlsm) caso continue a usar este código VBA no futuro.
- Recomenda-se fazer backups regulares de sua pasta de trabalho antes de executar novo código.
1. Mantenha pressionado ALT + F11 para abrir a janela Microsoft Visual Basic for Applications.
2. Na janela VBA, clique em Inserir > Módulo, depois copie e cole o seguinte código:
Código VBA: Concatenar valores únicos em uma única célula:
Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRg
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, xChar)
Set xDic = Nothing
End Function
3. Retorne à sua planilha e, em uma célula em branco (por exemplo, C2), digite a seguinte fórmula:
=ConcatUniq(A2:A18,",")Pressione Enter para confirmar. A célula exibirá todos os valores únicos do intervalo especificado, separados por vírgulas.
- Se o seu intervalo for diferente, ajuste A2:A18 de acordo.
- Se um delimitador diferente for necessário, substitua "," na fórmula pelo símbolo de sua preferência (por exemplo, ";" ou |).
- Se você encontrar um erro #NOME?, verifique se as macros estão habilitadas e se o nome da UDF corresponde exatamente.
Dica: Para reutilizar essa função em outras pastas de trabalho, copie o código VBA para seus módulos também.
Método 4: Usando fórmula avançada do Excel (solução alternativa)
Em ambientes onde a função UNIQUE não está disponível (por exemplo, no Excel 2016 ou Excel 2019), você ainda pode concatenar valores únicos usando uma combinação mais complexa das funções clássicas SE, CONT.SE e TEXTJOIN em fórmulas de matriz. Essa abordagem funciona, mas é mais adequada para conjuntos de dados menores devido ao seu custo de cálculo.
1. Em uma célula de destino (por exemplo, C2), insira a seguinte fórmula de matriz (após a entrada, pressione Ctrl+Shift+Enter em vez de apenas Enter):
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))2. Se você vir chaves {} aparecerem ao redor de sua fórmula, ela foi inserida corretamente como uma fórmula de matriz. A fórmula retornará valores únicos concatenados do intervalo A2:A18, separados por vírgulas.
Nota: Esse método requer que você ajuste os intervalos para corresponder aos seus dados. Para intervalos muito grandes, o tempo de cálculo pode aumentar. Se você não estiver confortável com fórmulas de matriz, considere experimentar as soluções VBA ou de complemento descritas acima.
Listar valores únicos e concatenar valores correspondentes
Frequentemente, em relatórios de dados, você pode querer não apenas extrair os valores únicos de uma coluna, mas também agregar ou unir suas entradas correspondentes de outra coluna. Por exemplo, consolidar todos os produtos vendidos por cada vendedor ou compilar todas as entradas vinculadas ao mesmo ID. Escolher o método certo depende da complexidade dos seus dados e de se automação, facilidade de uso ou compatibilidade é sua prioridade.
Método 1: Usando as funções TEXTJOIN e UNIQUE
Quando você está usando o Excel 365 ou Excel 2021, pode combinar as funções UNIQUE e FILTRAR com TEXTJOIN para uma abordagem robusta e totalmente baseada em fórmulas. Esse método funciona bem para resumir dados onde um valor pode se relacionar com vários registros, e você deseja uma lista delimitada desses registros relacionados.
1. Em uma coluna vazia, insira a seguinte fórmula para listar todos os valores únicos da Coluna A:
=UNIQUE(A2:A17)
2. Agora, para concatenar os valores correspondentes da Coluna B para cada entrada única, na próxima coluna ao lado do seu valor único (por exemplo, E2 se seus valores únicos começarem em D2), insira esta fórmula e arraste conforme necessário:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17) cria uma matriz de itens únicos da Coluna A.
- FILTER(B2:B17, A2:A17 = D2) gera uma matriz contendo todos os valores correspondentes da Coluna B para cada valor único em D2.
- TEXTJOIN(", ", VERDADEIRO, ...) junta esses valores correspondentes, separados por vírgulas.
- Se você precisar de um delimitador diferente, altere ", " em TEXTJOIN de acordo.
- Para evitar erros, certifique-se de que os intervalos em suas fórmulas tenham comprimentos iguais e que FILTER não retorne erros para correspondências ausentes.
- Essa abordagem atualiza os resultados automaticamente conforme seus dados mudam, tornando-a adequada para tabelas de resumo dinâmicas.
Método 2: Usando Kutools para Excel
O Kutools para Excel apresenta uma ferramenta "Mesclar Linhas Avançado" criada especificamente para agrupar dados por valores únicos e mesclar valores correspondentes com seu delimitador de escolha. Isso é adequado para usuários que desejam uma solução gráfica e não estão confortáveis escrevendo fórmulas ou código. É especialmente valioso ao lidar com grandes conjuntos de dados ou quando é necessária uma regroupagem frequente, como em relatórios periódicos ou manutenção contínua de dados.
Antes de fazer alterações, é uma boa prática fazer backup dos seus dados copiando os dados originais para outro local. Depois siga estas etapas:
- Selecione o intervalo de dados que deseja organizar.
- Navegue até "Kutools" > "Mesclar & Dividir" > "Mesclar Linhas Avançado", conforme ilustrado abaixo:
- Na caixa de diálogo que abre:
- Selecione a coluna com duplicatas para ser mesclada, definindo-a como "Chave Primária" na coluna "Operação".
- Escolha a coluna que deseja agregar (os valores a concatenar); especifique seu delimitador preferido na lista suspensa sob "Operação".
- Clique em OK para executar.
Resultado:
O Kutools reorganizará seus dados, extraindo entradas únicas e concatenando todos os valores associados, com base na sua configuração.
- Se você cometer um erro, use o recurso Desfazer no Excel (Ctrl+Z) para reverter.
- O processo funciona para conjuntos de dados com potencialmente centenas ou milhares de registros e suporta uma variedade de delimitadores.
Método 3: Usando código VBA
Usar um script VBA fornece controle total sobre como você extrai e resume dados. Essa abordagem é compatível com todas as versões do Excel e é especialmente adequada para fluxos de trabalho personalizados, automação ou quando funções como UNIQUE ou FILTER não estão disponíveis. Se a estrutura dos seus dados variar frequentemente, essa solução VBA pode ser facilmente adaptada.
Para usar o código abaixo, basta seguir estas etapas:
1. Pressione ALT + F11 para abrir o editor VBA.
2. Vá para Inserir > Módulo, depois cole o seguinte código na janela de módulo aberta:
Código VBA: Listar valores únicos e concatenar dados correspondentes
Sub test()
'updateby Extendoffice
Dim xRg As Range
Dim xArr As Variant
Dim xCell As Range
Dim xTxt As String
Dim I As Long
Dim xDic As Object
Dim xOutputRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Does not support multiple selections", , "Kutools for Excel"
Exit Sub
End If
If xRg.Columns.Count <> 2 Then
MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
Exit Sub
End If
Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
If xOutputRg Is Nothing Then Exit Sub
xArr = xRg
Set xDic = CreateObject("Scripting.Dictionary")
xDic.CompareMode = 1
For I = 1 To UBound(xArr)
If Not xDic.Exists(xArr(I, 1)) Then
xDic.Item(xArr(I, 1)) = xDic.Count + 1
xArr(xDic.Count, 1) = xArr(I, 1)
xArr(xDic.Count, 2) = xArr(I, 2)
Else
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
Next
xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub
3. Pressione F5 para executar o script. Um pop-up pedirá que você selecione o intervalo de dados. Certifique-se de selecionar exatamente duas colunas: a primeira para valores únicos e a segunda para valores correspondentes.
4. Clique em OK e selecione a primeira célula onde a tabela de resultados deve começar.
5. Após clicar em OK, o código gerará uma tabela contendo apenas valores únicos e seus dados concatenados associados.
- Se você receber um erro sobre o número de colunas, verifique se sua seleção inclui apenas duas colunas.
- Se o delimitador precisar ser alterado de vírgula para outro símbolo, ajuste o código na linha
xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)conforme necessário. - Sempre faça backup de seu arquivo antes de executar novos scripts VBA.
Em resumo, o Excel oferece uma variedade de abordagens para concatenar valores únicos e consolidar dados relacionados. Métodos baseados em fórmulas são rápidos e dinâmicos no Excel moderno, enquanto soluções VBA e Kutools oferecem maior compatibilidade e controle. Sempre escolha o método adequado ao tamanho dos seus dados, versão do Excel e fluxo de trabalho preferido. Lembre-se de testar em dados de amostra ou fazer backup do seu trabalho, especialmente ao tentar operações baseadas em scripts ou em lote. Para mais orientações e dicas avançadas sobre o Excel, explore nossa coleção completa de tutoriais.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins