Como concatenar apenas valores únicos no Excel?
Ao trabalhar com folhas de cálculo, é comum encontrar situações em que necessita de fundir (concatenar) apenas os valores únicos de uma coluna ou compilar listas que resumam entradas únicas com os seus registos correspondentes. Lidar com duplicados e apresentar informação resumida não só organiza os seus dados, mas também torna os relatórios mais claros e perspicazes. No Excel, existem várias formas práticas de alcançar estes objetivos, desde o uso de funções incorporadas até à utilização de suplementos avançados ou código personalizado. Este tutorial oferece uma exploração detalhada de múltiplos métodos para concatenar valores únicos e listar entradas únicas juntamente com os seus dados relacionados. As soluções abrangidas adaptam-se a diferentes versões do Excel e preferências dos utilizadores, ajudando-o a selecionar a melhor abordagem para o seu cenário.
Concatenar apenas valores únicos de uma coluna
- Utilizando as funções TEXTJOIN e UNIQUE
- Utilizando KUTOOLS AI Aide
- Utilizando uma Função Definida pelo Utilizador
- Utilizando uma fórmula avançada do Excel (solução alternativa)
Listar valores únicos e concatenar valores correspondentes
- Utilizando as funções TEXTJOIN e UNIQUE
- Utilizando Kutools for Excel
- Utilizando código VBA
- Utilizando uma Tabela Dinâmica do Excel com fórmulas (solução alternativa)
Concatenar apenas valores únicos de uma coluna
Durante o trabalho com Análise de Dados no Excel, uma tarefa frequente consiste em fundir apenas as entradas distintas de uma coluna numa única célula. Esta abordagem revela-se especialmente útil para criar relatórios resumo, eliminar valores duplicados numa lista ou preparar dados para processamento posterior. A escolha do método ideal depende da sua versão do Excel, do volume de dados e do seu grau de familiaridade com fórmulas ou código. Os métodos seguintes respondem a diferentes necessidades, destacam considerações específicas e incluem dicas práticas para garantir uma execução eficaz e precisa.
Método 1: Utilização das funções TEXTJOIN e UNIQUE
Para utilizadores do Excel 365 e Excel 2021, as funções TEXTJOIN e UNIQUE tornam a combinação de valores únicos de uma coluna simultaneamente simples e flexível.
Esta solução é ideal quando a coluna dos seus dados é contínua e pretende consolidar rapidamente todos os itens únicos numa única célula, utilizando o delimitador à sua escolha. Elimina automaticamente duplicados, é facilmente auditável e permite-lhe ajustar tanto o intervalo como o delimitador sempre que necessário. Contudo, tenha em atenção que esta abordagem só está disponível nas versões mais recentes do Excel, já que as versões anteriores não suportam a função UNIQUE.
Na célula onde pretende apresentar o resultado, introduza a seguinte fórmula (assumindo que os seus dados estão nas células A2:A18):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18)Filtra as entradas duplicadas e devolve apenas os valores únicos do intervalo A2:A18.
- TEXTJOIN(", ", TRUE, ...)Combina (concatena) esses valores únicos numa única célula, separados por uma vírgula e um espaço. O argumento VERDADEIRO garante que quaisquer células vazias sejam ignoradas na concatenação.
Dicas úteis e resolução de problemas:
- Certifique-se de que a sua versão do Excel suporta as funções UNIQUE e TEXTJOIN. Se vir o erro #NOME?, é provável que esteja a utilizar uma versão mais antiga.
- O delimitador utilizado no TEXTJOIN pode ser personalizado à sua escolha, como "; " ou «|».
- Ao adicionar ou remover dados no intervalo original, a fórmula atualiza-se automaticamente.
- Para evitar espaços extra ou delimitadores não intencionais, verifique atentamente o argumento do delimitador na fórmula.
Método 2: Utilização do KUTOOLS AI Aide
Quando precisa de uma forma mais rápida e totalmente automatizada de concatenar valores únicos — sem ter de escrever fórmulas —, a ferramenta «Assistente de IA» do Kutools para Excel oferece uma solução prática que economiza tempo a utilizadores de todos os níveis de competência. Este método é especialmente útil se não estiver familiarizado com fórmulas avançadas do Excel ou se os seus dados mudarem frequentemente, exigindo tarefas repetitivas.
Após instalar o Kutools para Excel, aceda a esta funcionalidade clicando em «Kutools» > «Assistente de IA» para abrir o painel «KUTOOLS AI Aide».
- Selecione as células que contêm os valores que deseja combinar numa única célula, certificando-se de que a sua seleção corresponde exatamente aos dados pretendidos.
- Na caixa de chat, descreva o seu pedido. Por exemplo, pode escrever:
Concatenar valores únicos com vírgula a partir do intervalo selecionado e colocar o resultado combinado na célula C2 - Prima a tecla Enter ou clique no botão «Enviar». Após o processamento do seu pedido pela IA, prima «Executar» para que o Kutools realize a operação. O resultado será devolvido conforme descrito.
Notas e dicas:
- Certifique-se de que está a utilizar a versão mais recente do Kutools para aceder a todas as funcionalidades de IA.
- Seja específico no seu comando textual para obter os melhores resultados: indique claramente o delimitador e a célula de destino.
- O KUTOOLS AI revela-se especialmente eficiente em intervalos extensos ou em fluxos de trabalho que exijam repetição em múltiplos conjuntos de dados.
Método 3: Utilização de uma Função Definida pelo Utilizador
Para utilizadores que exigem flexibilidade avançada, delimitadores personalizados ou uma ferramenta reutilizável em múltiplos livros, criar uma Função Definida pelo Utilizador (UDF) em VBA é uma solução eficaz para concatenar automaticamente valores únicos. Esta abordagem em VBA é compatível com todas as versões do Excel e não depende da disponibilidade de funções mais recentes.
- Deve ativar as macros no seu livro.
- Guarde o ficheiro como «ativado para macros» ().xlsm), caso pretenda continuar a utilizar este código VBA no futuro.
- Recomendamos que faça cópias de segurança regulares do seu livro antes de executar novo código.
1. Mantenha premidas as teclas ALT + F11 para abrir a janela Microsoft Visual Basic para Aplicações.
2. Na janela do VBA, clique em Inserir > Módulo e, de seguida, copie e cole o seguinte código:
Código VBA: Concatenar valores únicos numa ú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.Regresse à sua folha de cálculo e, numa célula vazia (por exemplo, C2), introduza a seguinte fórmula:
=ConcatUniq(A2:A18,",")Prima Enter para confirmar. A célula irá apresentar todos os valores únicos do intervalo limitado, separados por vírgulas.
- Se o seu intervalo for diferente, ajuste A2:A18 em conformidade.
- Se precisar de um delimitador diferente, substitua o ","na fórmula pelo símbolo pretendido (por exemplo,)";" ou |).
- Se encontrar um erro #NOME?, verifique se as macros estão ativadas e se o nome da UDF corresponde exatamente.
Dica: Para reutilizar esta função noutros livros, copie também o código VBA para os respetivos módulos.
Método 4: Utilização de uma 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 no Excel 2019), ainda pode concatenar valores únicos utilizando uma combinação mais complexa das funções clássicas SE, CONTAR.SE e JUNTAR.TEXTO em fórmulas matriciais. Esta abordagem funciona, mas é mais adequada a conjuntos de dados pequenos devido à sobrecarga de cálculo.
1. Numa célula de destino (por exemplo, C2), introduza a seguinte fórmula matricial (após a introdução, prima)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 vir chavetas {} a rodear a sua fórmula, esta foi introduzida corretamente como uma fórmula matricial. A fórmula devolverá os valores únicos concatenados do intervalo A2:A18, separados por vírgulas.
Nota: Este método exige que ajuste os intervalos de acordo com os seus dados. Com intervalos muito extensos, o tempo de cálculo poderá aumentar. Se não se sentir à vontade com fórmulas matriciais, experimente as soluções em VBA ou os add-ins descritos anteriormente.
Listar valores únicos e concatenar valores correspondentes
Na elaboração de relatórios, é frequente precisar não só de extrair os valores únicos de uma coluna, mas também de agregar ou consolidar as respetivas entradas correspondentes de outra coluna — por exemplo, reunir todos os produtos vendidos por cada vendedor ou compilar todas as entradas associadas ao mesmo ID. A escolha do método ideal depende da complexidade dos seus dados e de o que mais valoriza: automação, facilidade de utilização ou compatibilidade.
Método 1: Utilização das funções JUNTAR.TEXTO e UNIQUE
Ao utilizar o Excel 365 ou o Excel 2021, pode combinar as funções UNIQUE e FILTRAR com JUNTAR.TEXTO para obter uma abordagem robusta e totalmente baseada em fórmulas. Este método é ideal para resumir dados em que um valor esteja associado a múltiplos registos, permitindo-lhe obter uma lista desses registos relacionados, separada por um delimitador à sua escolha.
1. Numa coluna vazia, introduza a seguinte fórmula para obter todos os valores únicos da Coluna A:
=UNIQUE(A2:A17)
2. Agora, para concatenar os valores correspondentes da Coluna B associados a cada entrada única, insira esta fórmula na coluna imediatamente à direita do seu primeiro valor único (por exemplo, em E2, se os seus valores únicos começarem em D2) e arraste-a para baixo conforme necessário:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17)Crie um vetor com itens únicos da Coluna A.
- FILTER(B2:B17, A2:A17 = D2)Gera um vetor com todos os valores correspondentes da coluna B para cada valor único presente em D2.
- TEXTJOIN(", ", TRUE, ...)Una esses valores correspondentes, separados por vírgulas.
- Se precisar de um delimitador diferente, altere ", " no TEXTJOIN em conformidade.
- Para evitar erros, certifique-se de que os intervalos nas suas fórmulas têm o mesmo comprimento e de que a função FILTRAR não devolve erros devido à ausência de correspondências.
- Esta abordagem atualiza automaticamente os resultados sempre que os seus dados forem alterados, tornando-a ideal para tabelas dinâmicas.
Método 2: Utilização do Kutools para Excel
O Kutools para Excel inclui a ferramenta «Mesclar Linhas Avançado», concebida especificamente para agrupar dados por valores únicos e combinar os respetivos valores com o delimitador à sua escolha. Esta solução é ideal para utilizadores que preferem uma abordagem gráfica e não se sentem à vontade a escrever fórmulas ou código. Revela-se especialmente valiosa ao trabalhar com conjuntos de dados extensos ou quando é necessária uma reagrupação frequente, como em relatórios periódicos ou na manutenção contínua de dados.
Antes de efetuar alterações, é uma boa prática criar uma cópia de segurança dos seus dados, copiando-os para outra localização. Depois, siga estes passos:
- Selecione o intervalo de dados que deseja organizar.
- Navegue até «Kutools» > «Mesclar e Dividir» > «Mesclar Linhas Avançado», conforme ilustrado abaixo:
- Na caixa de diálogo que se abre:
- Selecione a coluna que contém os duplicados a serem mesclados e defina-a como «Chave Primária» na coluna «Operação».
- Escolha a coluna que pretende agregar (ou seja, os valores a concatenar) e especifique o delimitador pretendido no menu suspenso em «Operação».
- Clique em OK para executar.
Resultado:
O Kutools reorganiza os seus dados, extraíndo entradas únicas e concatenando todos os valores associados, conforme a sua configuração.
- Se cometer um erro, utilize a funcionalidade Anular do Excel (Ctrl+Z) para reverter imediatamente.
- Este processo funciona com conjuntos de dados que contêm centenas ou milhares de registos e suporta uma variedade de delimitadores.
Método 3: Utilização de código VBA
A utilização de um script VBA oferece controlo total sobre a forma como extrai e resume os seus dados. Esta abordagem é compatível com todas as versões do Excel e revela-se ideal para fluxos de trabalho personalizados, automação ou quando funções como UNIQUE ou FILTRAR não estão disponíveis. Se a estrutura dos seus dados mudar frequentemente, esta solução VBA pode ser facilmente adaptada.
Para utilizar o código abaixo, basta seguir estes passos:
1. Prima ALT + F11 para abrir o editor do VBA.
2. Aceda a Inserir > Módulo e, de seguida, cole o seguinte código na janela do 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. Prima F5 para executar o script. Irá aparecer uma janela a pedir-lhe que selecione o Intervalo de Dados. Certifique-se de que escolhe exatamente duas colunas: a primeira para os valores únicos e a segunda para os valores correspondentes.
4. Clique em OK e selecione a primeira célula onde a tabela de resultados deverá começar.
5. Após clicar em OK, o código gera uma tabela com apenas os valores únicos e os respetivos dados concatenados.
- Se receber um erro relacionado com o número de colunas, certifique-se de que a sua seleção inclui apenas duas colunas.
- Se o seu delimitador tiver de mudar de uma 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. - Faça sempre uma cópia de segurança do seu ficheiro antes de executar novos scripts VBA.
Em resumo, o Excel oferece várias abordagens para concatenar valores únicos e consolidar dados relacionados. Os métodos baseados em fórmulas são rápidos e dinâmicos no Excel moderno, enquanto as soluções VBA e Kutools proporcionam maior compatibilidade e controlo. Escolha sempre o método mais adequado ao tamanho dos seus dados, à versão do Excel e ao seu fluxo de trabalho preferido. Lembre-se de testar com dados de exemplo ou de fazer cópias de segurança do seu trabalho, especialmente antes de experimentar operações baseadas em scripts ou em lote. Para obter mais orientações e dicas avançadas sobre Excel,explore a nossa coleção completa de tutoriais.
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