Agrupamento Fuzzy no Excel: Como Unir Dados Semelhantes, mas Não Idênticos

Trabalhar com dados não padronizados no Excel pode ser uma atividade frustrante. Frequentemente, nos deparamos com valores que deveriam ser considerados iguais, mas apresentam pequenas diferenças de digitação, formatação ou sintaxe. Agrupar manualmente esses elementos consome tempo precioso e aumenta o risco de erros. Felizmente, o Excel oferece uma solução eficaz, mas pouco conhecida: o agrupamento fuzzy no Power Query, uma técnica avançada de fuzzy matching que simplifica significativamente esse processo.

Compreendendo o agrupamento fuzzy no Excel

O agrupamento fuzzy é uma funcionalidade avançada do Power Query que permite identificar e unir automaticamente elementos semelhantes, mas não idênticos. Mas antes de nos aprofundarmos, vamos esclarecer: o que é Power Query? É uma poderosa ferramenta de preparação de dados integrada ao Excel que permite transformar e limpar dados de forma eficiente. Diferente das funções padrão do Excel que exigem correspondências exatas, o agrupamento fuzzy utiliza algoritmos de similaridade para reconhecer variações do mesmo dado. Essa funcionalidade é particularmente útil quando você trabalha com:

• Listas de nomes com variações ortográficas (ex: “João Silva” e “Silva João”)
• Dados inseridos por pessoas diferentes com convenções distintas
• Informações provenientes de sistemas diferentes com formatações não uniformes
• Respostas a perguntas abertas em pesquisas ou questionários

O agrupamento fuzzy baseia-se em um “score de similaridade” configurável que determina o quão semelhantes duas strings devem ser para serem consideradas equivalentes. Além disso, você pode criar tabelas de tradução personalizadas para mapear termos específicos que deseja tratar como idênticos. Nos próximos parágrafos, vou guiar você por todos os passos necessários para implementar essa poderosa funcionalidade em suas planilhas, incluindo a preparação inteligente dos dados e a operação de agregação.

Preparar os dados para o agrupamento fuzzy

Antes de usar o agrupamento fuzzy, é fundamental organizar corretamente os dados. A preparação adequada garantirá resultados ótimos e simplificará todo o processo. O primeiro passo consiste em converter seus dados em uma tabela do Excel. Este é um requisito essencial para usar o Power Query e acessar as funcionalidades de agrupamento fuzzy.

Para criar uma tabela:

1. Selecione qualquer célula dentro dos seus dados
2. Pressione Ctrl+T ou vá para a aba “Inserir” e clique em “Tabela”
3. Verifique se a opção “Minha tabela tem cabeçalhos” está selecionada se a primeira linha contiver os nomes das colunas
4. Confirme clicando em “OK”

Uma vez criada a tabela, é aconselhável atribuir um nome significativo e conciso a ela. Isso facilitará a referência em fórmulas e no Power Query. Para renomear a tabela:

1. Selecione qualquer célula na tabela
2. Na guia “Design da Tabela” que aparece, altere o nome no campo “Nome da Tabela” no canto superior esquerdo

É importante também verificar a qualidade dos dados antes de prosseguir. Verifique a presença de células vazias, erros de formatação ou caracteres especiais que possam influenciar o processo de agrupamento. Se necessário, execute uma limpeza de dados removendo espaços extras, padronizando maiúsculas/minúsculas ou corrigindo erros evidentes. Esta fase de limpeza de dados é crucial para garantir a integridade do conjunto de dados e melhorar a eficácia do agrupamento fuzzy.

Finalmente, identifique quais colunas contêm os valores que você deseja agrupar. O agrupamento fuzzy funciona melhor quando aplicado a uma única coluna por vez, então você pode precisar reorganizar seus dados de acordo. Esta preparação inteligente dos dados o ajudará a obter resultados mais precisos nas fases seguintes.

Criar uma tabela de tradução personalizada

Uma das características mais poderosas do agrupamento fuzzy é a possibilidade de usar uma tabela de tradução personalizada. Esta tabela, que funciona como uma tabela de referência, permite definir explicitamente quais termos devem ser considerados equivalentes, independentemente do seu score de similaridade. A tabela de tradução deve ter uma estrutura específica com duas colunas:

• De: contém os valores originais que você deseja mapear
• Para: contém os valores para os quais você deseja converter os termos originais

Por exemplo, você pode querer considerar “email”, “e-mail” e “correio eletrônico” como o mesmo conceito. Neste caso, a tabela de tradução pode parecer assim:

Para criar esta tabela de transformação:

1. Insira os cabeçalhos “De” e “Para” em duas células adjacentes
2. Preencha as linhas com os pares de valores a serem mapeados
3. Selecione toda a área e converta em tabela (Ctrl+T)
4. Atribua um nome significativo à tabela, por exemplo, “Traducao”

A tabela de tradução é particularmente útil para:

• Padronizar terminologias específicas do setor
• Unificar abreviações e formas estendidas
• Gerenciar sinônimos ou termos equivalentes em diferentes contextos
• Corrigir erros comuns de digitação ou formatação

Quanto mais completa e precisa for sua tabela de tradução, melhores serão os resultados do agrupamento fuzzy. Vale a pena dedicar tempo à criação de uma tabela de tradução completa, especialmente se você planeja executar frequentemente operações de união em dados semelhantes.

Importar os dados para o Power Query

Uma vez que os dados estejam preparados e a tabela de tradução criada, é hora de importar tudo para o Power Query para iniciar o processo de agrupamento fuzzy. O carregamento dos dados no Power Query é uma etapa fundamental que permitirá aplicar transformações avançadas antes de carregar os resultados de volta para o Excel.

Para importar a tabela principal:

1. Selecione qualquer célula dentro da tabela de dados
2. Vá para a guia “Dados” na faixa de opções
3. Clique em “De Tabela/Intervalo” no grupo “Obter e Transformar Dados”

O Editor do Power Query será aberto com seus dados. Este ambiente permite aplicar transformações avançadas antes de carregar os resultados de volta para o Excel. Em seguida, você deve importar também a tabela de tradução (se a criou). O processo é idêntico:

1. Volte ao Excel sem fechar o Editor do Power Query
2. Selecione uma célula na tabela de tradução
3. Vá para a guia “Dados” e clique em “De Tabela/Intervalo”

Agora você terá duas consultas separadas no Editor do Power Query, visíveis no painel “Consultas” à esquerda. É importante que ambas as consultas estejam disponíveis no ambiente Power Query antes de prosseguir com o agrupamento fuzzy.

Antes de continuar, é aconselhável verificar se os tipos de dados estão corretos em ambas as tabelas. O Power Query atribui automaticamente tipos de dados com base no conteúdo, mas às vezes pode ser necessário corrigi-los:

1. Selecione a coluna a ser modificada
2. Clique com o botão direito e escolha “Alterar Tipo”
3. Selecione o tipo de dado apropriado (geralmente “Texto” para os dados a serem agrupados)

Com os dados corretamente importados e formatados, você está pronto para aplicar o agrupamento fuzzy. Se precisar carregar mais arquivos de uma pasta, o Power Query também oferece essa funcionalidade, que pode ser útil para projetos mais complexos que envolvem múltiplas fontes de dados.

Aplicar o agrupamento básico no Power Query

Antes de usar o agrupamento fuzzy, é útil entender como funciona o agrupamento padrão no Power Query. Isso nos fornecerá a base para depois modificar a fórmula e implementar o agrupamento fuzzy. Para aplicar um agrupamento padrão:

1. No Editor do Power Query, selecione a coluna que contém os valores a serem agrupados
2. Vá para a guia “Transformar” na faixa de opções
3. Clique no botão “Agrupar Por”

Será aberta a caixa de diálogo “Agrupar Por” com várias opções:

• Agrupar por: selecione a coluna a ser utilizada para o agrupamento
• Nova coluna: insira um nome para a coluna que conterá os resultados do agrupamento
• Operação: escolha “Todas as linhas” para manter todos os dados originais

Após configurar essas opções, clique em “OK” para aplicar o agrupamento padrão. O Power Query gerará uma fórmula M que utiliza a função Table.Group(). Esta fórmula aparecerá na barra de fórmulas na parte superior do editor. O resultado será uma nova tabela com duas colunas:

• A coluna com os valores exclusivos encontrados no campo selecionado
• Uma coluna contendo tabelas aninhadas com todas as linhas correspondentes a cada valor exclusivo

Este agrupamento padrão, no entanto, funciona apenas com correspondências exatas. Para obter um agrupamento baseado na similaridade, precisamos modificar a fórmula gerada e transformá-la em um agrupamento fuzzy, implementando assim uma correspondência fuzzy mais flexível.

Modificar a fórmula para agrupamento fuzzy

O passo crucial para implementar o agrupamento fuzzy consiste em modificar manualmente a fórmula gerada pelo agrupamento padrão. Isso é necessário porque a interface do usuário do Power Query não oferece um botão direto para o agrupamento fuzzy. Após aplicar o agrupamento padrão, observe a barra de fórmulas na parte superior do editor. Você verá uma fórmula semelhante a esta:

= Table.Group(#"Tipo Alterado", {"NomeColuna"}, {{"Dados", each _, type table [NomeColuna=nullable text]}})

Para convertê-la em um agrupamento fuzzy, você deve:

1. Mudar Table.Group para Table.FuzzyGroup
2. Adicionar um quarto parâmetro que define as opções do agrupamento fuzzy

A fórmula modificada deve aparecer assim:

= Table.FuzzyGroup(#"Tipo Alterado", {"NomeColuna"}, {{"Dados", each _, type table [NomeColuna=nullable text]}}, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.8, TransformationTable=Traducao])

As opções no quarto parâmetro controlam o comportamento do agrupamento fuzzy:

• IgnoreCase: quando definido como true, o agrupamento ignora as diferenças entre maiúsculas e minúsculas
• IgnoreSpace: quando definido como true, os espaços são ignorados durante a comparação
• Threshold: um valor entre 0 e 1 que determina o quão semelhantes duas strings devem ser para serem agrupadas (0.8 é um bom ponto de partida)
• TransformationTable: o nome da consulta que contém a tabela de tradução

Depois de modificar a fórmula, pressione Enter ou clique na marca de verificação ao lado da barra de fórmulas para aplicar a alteração. O Power Query executará o agrupamento fuzzy de acordo com os parâmetros especificados.

É importante notar que o valor de Threshold requer experimentação. Um valor muito alto (próximo a 1) exigirá uma similaridade quase perfeita, enquanto um valor muito baixo (próximo a 0) pode agrupar elementos que não deveriam ser considerados semelhantes. Esta operação de agregação baseada na similaridade das strings é o cerne do agrupamento fuzzy.

Configurar as opções de similaridade

O sucesso do agrupamento fuzzy depende em grande parte da correta configuração das opções de similaridade. Essas opções determinam quais elementos serão considerados semelhantes e, portanto, agrupados. A opção Threshold (limiar) é particularmente importante. Representa o score mínimo de similaridade (de 0 a 1) necessário para que duas strings sejam consideradas equivalentes:

• Um valor de 1.0 exige uma correspondência exata (equivalente ao agrupamento padrão)
• Um valor de 0.0 agruparia todos os elementos (raramente útil)
• Valores entre 0.7 e 0.9 são geralmente mais eficazes para a maioria das aplicações

A escolha do valor ótimo depende da natureza dos seus dados:

• Para dados com pequenas variações ortográficas: tente com 0.8-0.9
• Para variações mais significativas na formulação: tente com 0.6-0.8
• Para conceitos relacionados, mas expressos de forma diferente: tente com 0.5-0.7

As opções IgnoreCase e IgnoreSpace são mais simples de configurar:

• IgnoreCase=true: útil na maioria dos casos, pois as diferenças entre maiúsculas e minúsculas raramente indicam significados diferentes
• IgnoreSpace=true: útil quando os espaços são inconsistentes (ex: “data base” vs “database”)

É aconselhável começar com configurações conservadoras (threshold alto) e reduzir gradualmente o valor, se necessário. Após cada modificação, examine cuidadosamente os resultados para verificar se o agrupamento é lógico e consistente com suas expectativas.

Lembre-se que você sempre pode voltar e modificar essas configurações se os resultados não forem satisfatórios. O processo de otimização das opções de similaridade é frequentemente iterativo e requer experimentação. Alguns algoritmos de similaridade, como o algoritmo de similaridade Jaccard, podem ser particularmente eficazes para certos tipos de dados, então vale a pena explorar diferentes opções.

Expandir os resultados do agrupamento

Após aplicar o agrupamento fuzzy, você obterá uma tabela com duas colunas: a coluna dos valores agrupados e uma coluna contendo tabelas aninhadas com todos os dados originais. Para tornar esses resultados mais utilizáveis, você deve expandir as tabelas aninhadas.

Para expandir os resultados:

1. Na coluna que contém as tabelas aninhadas, clique no ícone de expansão (duas setas divergentes) no cabeçalho da coluna
2. Na caixa de diálogo que aparece, selecione as colunas que você deseja incluir nos resultados expandidos
3. Escolha se deseja manter ou remover o prefixo do nome da coluna original
4. Clique em “OK” para aplicar a expansão

Este processo de expansão da tabela transformará a estrutura aninhada em uma tabela plana com todos os dados originais, mas agora organizados de acordo com o agrupamento fuzzy aplicado. Cada linha mostrará o valor agrupado juntamente com os dados originais correspondentes.

Se a tabela original continha muitas colunas, você pode querer selecionar apenas as mais relevantes durante a expansão para manter os resultados gerenciáveis. Você sempre pode modificar essa seleção posteriormente, se necessário. Em alguns casos, pode ser útil considerar a remoção de colunas desnecessárias para simplificar ainda mais o conjunto de dados.

A expansão dos resultados é particularmente útil quando você deseja:

• Ver todos os valores originais que foram agrupados
• Verificar a precisão do agrupamento fuzzy
• Executar análises adicionais sobre os dados agrupados

Preparar os dados para visualização ou relatórios

Após a expansão, é aconselhável reordenar as colunas de forma lógica para facilitar a interpretação dos resultados. Você pode fazer isso arrastando os cabeçalhos das colunas para a posição desejada ou usando a opção “Mover” no menu de contexto das colunas. Esta etapa é importante para criar uma série bem disposta e ordenada de dados que será mais fácil de analisar e apresentar.

Nesta fase, você também pode querer considerar a padronização dos valores em algumas colunas para garantir a consistência em seus relatórios. Por exemplo, você pode querer uniformizar o formato dos campos de data ou garantir que todos os nomes estejam em um formato consistente (ex: “Sobrenome, Nome”). Essas operações de limpeza final contribuirão para melhorar a qualidade geral do seu conjunto de dados.

Carregar os resultados no Excel

Uma vez concluído o agrupamento fuzzy e configurada a saída conforme desejado, é hora de carregar os resultados novamente no Excel para a análise final ou apresentação. Para carregar os resultados:

1. No Editor do Power Query, vá para a guia “Página Inicial” na faixa de opções
2. Clique no botão “Fechar e Carregar” para enviar os dados diretamente para o Excel
3. Alternativamente, clique na seta abaixo de “Fechar e Carregar” e selecione “Fechar e Carregar para…” para mais opções

Na caixa de diálogo “Importar Dados”, você pode escolher:

• Tabela: carrega os dados como uma tabela formatada do Excel (opção recomendada)
• Tabela Dinâmica: cria diretamente uma tabela dinâmica a partir dos dados agrupados
• Somente Conexão: cria apenas uma conexão aos dados sem carregá-los em uma planilha
• Adicionar esses dados ao Modelo de Dados: útil para análises mais complexas ou para uso com o Power Pivot

Selecione também o local onde deseja carregar os dados:

• Planilha existente: especifica uma célula em uma planilha existente
• Nova planilha: cria uma nova planilha para os resultados

Após confirmar suas escolhas, o Excel carregará os dados agrupados na posição especificada. Os dados manterão um link dinâmico com a consulta do Power Query, o que significa que você poderá atualizar os resultados se os dados de origem mudarem.

Para atualizar os dados no futuro:

1. Selecione qualquer célula na tabela de resultados
2. Vá para a guia “Dados” na faixa de opções
3. Clique em “Atualizar Tudo” ou “Atualizar” no grupo “Consultas e Conexões”

Isso executará novamente a consulta do Power Query, aplicando novamente o agrupamento fuzzy a quaisquer dados atualizados. Essa funcionalidade de atualização automática é particularmente útil quando você trabalha com dados que mudam frequentemente ou quando deseja executar uma mesclagem de consultas de fontes distintas.

Verificar e refinar os resultados

Após carregar os resultados no Excel, é fundamental verificar a precisão do agrupamento fuzzy e fazer quaisquer ajustes necessários. Mesmo com as melhores configurações, o agrupamento automático pode não ser perfeito na primeira tentativa. Aqui estão algumas estratégias para verificar e melhorar os resultados:

1. Examine os grupos criados: ordene os dados pelo valor agrupado e verifique se todos os elementos em cada grupo estão realmente relacionados. Procure anomalias ou elementos que pareçam fora do lugar.
2. Identifique falsos positivos: elementos distintos que foram erroneamente agrupados. Isso indica que o limiar de similaridade pode ser muito baixo.
3. Procure falsos negativos: elementos semelhantes que não foram agrupados como esperado. Isso sugere que o limiar pode ser muito alto.
4. Atualize a tabela de tradução: se você encontrar erros recorrentes, adicione novos mapeamentos à tabela de tradução para corrigi-los explicitamente.
5. Modifique as configurações de similaridade: volte ao Editor do Power Query e altere o valor do Threshold ou as outras opções de similaridade para melhorar os resultados.

Para modificar a consulta e refinar o agrupamento:

1. Selecione qualquer célula na tabela de resultados
2. Vá para a guia “Consulta” ou “Dados” na faixa de opções
3. Clique em “Editar” para reabrir o Editor do Power Query
4. Modifique a fórmula de agrupamento fuzzy ou a tabela de tradução
5. Feche e carregue novamente para atualizar os resultados

O refinamento do agrupamento fuzzy é frequentemente um processo iterativo que requer várias tentativas para obter os resultados ótimos. Não hesite em experimentar diferentes configurações até encontrar a combinação que funciona melhor para seus dados específicos. Este processo de aprimoramento contribuirá para garantir a integridade do conjunto de dados e a qualidade dos seus resultados finais.

Casos de uso práticos do agrupamento fuzzy

O agrupamento fuzzy no Excel é uma ferramenta versátil com inúmeras aplicações práticas em vários setores. Aqui estão alguns casos de uso comuns onde essa funcionalidade pode fazer a diferença:

• Limpeza de bancos de dados de clientes: Identificar duplicatas com pequenas variações nos nomes (ex: “Empresa Ltda.” e “Empresa Ltda”)
  • Padronizar nomes de empresas adquiridas ou com marcas diferentes
  • Unificar registros de clientes provenientes de sistemas diferentes
• Análise de feedback e pesquisas: Agrupar respostas a perguntas abertas com significado semelhante
  • Identificar temas comuns em avaliações ou comentários de clientes
  • Categorizar sugestões ou reclamações para priorização
• Gerenciamento de inventário: Padronizar nomes de produtos inseridos manualmente
  • Identificar produtos semelhantes ou equivalentes de fornecedores diferentes
  • Consolidar categorias de produtos com nomenclaturas ligeiramente diferentes
• Análise financeira: Agrupar itens de despesa semelhantes, mas registrados com nomes diferentes
  • Padronizar descrições de transações bancárias
  • Consolidar categorias de custo para relatórios mais precisos
• Pesquisa e análise de mercado: Agrupar nomes de concorrentes com diferentes variações ortográficas
  • Padronizar nomes de locais ou regiões geográficas
  • Unificar termos do setor ou jargão técnico

Para cada um desses casos de uso, o agrupamento fuzzy oferece uma significativa economia de tempo em comparação com a categorização manual, reduzindo ao mesmo tempo o risco de erros humanos. A chave para o sucesso é adaptar as configurações de similaridade e a tabela de tradução às necessidades específicas do seu cenário.

Limitações e alternativas ao agrupamento fuzzy

Apesar de sua potência, o agrupamento fuzzy no Power Query apresenta algumas limitações que é importante conhecer:

Limitações principais:

• Funciona melhor com textos relativamente curtos; frases longas podem gerar resultados imprevisíveis
• Requer Power Query, que pode não estar disponível em todas as versões do Excel
• O desempenho pode degradar-se com conjuntos de dados muito grandes (dezenas de milhares de linhas)
• O algoritmo de similaridade não é completamente transparente ou personalizável
• Não lida bem com comparações multilíngues ou caracteres especiais

Para situações em que o agrupamento fuzzy não é adequado, considere estas alternativas:

• Funções de pesquisa aproximada: PROCV combinado com funções como TEXTOSIMILAR ou DIST.TEXTO
• Fórmulas de matriz complexas para identificar correspondências aproximadas
• Add-ins de terceiros especializados em correspondências fuzzy
• Abordagens externas ao Excel: Software especializados para deduplicação de dados
• Ferramentas ETL (Extract, Transform, Load) com funcionalidades de correspondência fuzzy
• Soluções de banco de dados com capacidades de pesquisa fuzzy
• Linguagens de programação como Python ou R com bibliotecas para matching fuzzy
• Métodos híbridos: Pré-processamento dos dados para padronizar formatos comuns
• • Agrupamento inicial baseado em partes do texto (ex: primeiras letras)
  • Combinação de agrupamento automático e revisão manual

Se o agrupamento fuzzy no Power Query não satisfaz suas necessidades, avalie se uma dessas alternativas pode ser mais adequada para o seu caso específico. Em muitos cenários, uma abordagem combinada que utiliza diferentes técnicas pode oferecer os melhores resultados.

Conclusões e melhores práticas

O agrupamento fuzzy no Power Query representa uma ferramenta poderosa, mas muitas vezes subestimada, no arsenal do Excel. Permite automatizar um processo que de outra forma exigiria horas de trabalho manual e estaria sujeito a erros. Para obter os melhores resultados com o agrupamento fuzzy, considere estas melhores práticas:

1. Prepare adequadamente os dados: limpe os dados antes do agrupamento, removendo formatações inconsistentes ou caracteres especiais desnecessários.
2. Invista na tabela de tradução: uma tabela de tradução bem construída pode melhorar significativamente os resultados, especialmente para termos específicos do setor ou abreviações comuns.
3. Itere e refine: não espere resultados perfeitos na primeira tentativa. Esteja pronto para experimentar diferentes configurações de similaridade e aprimorar o processo.
4. Verifique os resultados: sempre verifique manualmente uma amostra dos resultados para garantir que o agrupamento seja lógico e consistente com suas expectativas.
5. Documente o processo: anote as configurações utilizadas e as decisões tomadas, especialmente se você planeja repetir o processo no futuro.
6. Considere o contexto: adapte as configurações de similaridade ao contexto específico dos seus dados e ao nível de precisão exigido.
7. Mantenha os dados originais: sempre guarde uma cópia dos dados originais não agrupados para referência futura ou para iterações alternativas.

O agrupamento fuzzy é particularmente valioso em uma era de crescente volume e variedade de dados. Dominar essa técnica permitirá transformar dados desorganizados e inconsistentes em informações estruturadas e utilizáveis, melhorando significativamente a qualidade das suas análises e de seus relatórios no Excel.

Se vuoi rimanere aggiornato su Agrupamento Fuzzy no Excel: Como Unir Dados Semelhantes, mas Não Idênticos iscriviti alla nostra newsletter settimanale