Um dos meus amigos trabalha em uma empresa de análise de saúde. Ele sempre se conecta a mim sobre alguns dos problemas da vida real que enfrenta ao trabalhar com dados no Excel.
Muitas vezes, eu converto suas consultas em tutoriais do Excel neste site, pois pode ser útil para meus outros leitores também.
Este também é um desses tutoriais.
Meu amigo me ligou na semana passada com o seguinte problema:
Há dados de endereço em uma coluna do Excel e desejo identificar / filtrar células onde o endereço contém sequências de texto duplicadas (palavras).
Aqui está o conjunto de dados semelhante no qual ele queria filtrar células que continham uma sequência de texto duplicada (aquelas com setas vermelhas):
Agora, o que torna isso difícil é que não há consistência nesses dados. Como esta é uma compilação de um conjunto de dados criado manualmente por representantes de vendas, pode haver variações no conjunto de dados.
Considere isto:
- Qualquer string de texto pode se repetir neste conjunto de dados. Por exemplo, pode ser o nome da área ou o nome da cidade ou ambos.
- As palavras são separadas por um caractere de espaço e não há consistência em saber se o nome da cidade estaria lá após seis palavras ou oito palavras.
- Existem milhares de registros como este, e a necessidade é filtrar aqueles registros onde haja qualquer sequência de texto duplicada.
Depois de considerar muitas opções (como texto para colunas e fórmulas), finalmente decidi usar o VBA para fazer isso.
Então, eu criei uma função VBA personalizada (‘IdDuplicate’) para analisar essas células e me dar TRUE se houver uma palavra duplicada na string de texto, e FALSE se não houver repetições (conforme mostrado abaixo):
Esta função personalizada analisa cada palavra na string de texto e verifica quantas vezes ela ocorre no texto. Se a contagem for maior que 1, ele retornará TRUE; caso contrário, ele retorna FALSE.
Além disso, ele foi criado para contar apenas palavras com mais de três caracteres.
Depois de ter os dados TRUE / FALSE, posso filtrar facilmente todos os registros que são TRUE.
Agora, deixe-me mostrar como fazer isso no Excel.
Código VBA para a função personalizada
Isso é feito criando uma função personalizada no VBA. Essa função pode então ser usada como qualquer outra função de planilha no Excel.
Aqui está o código VBA para isso:
Função IdDuplicates (rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Passo -1 If Len (StringtoAnalyze (i)) <minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: Função final
Obrigado Walter por sugerir uma abordagem melhor para este código!
Como usar este código VBA
Agora que você tem o código do VBA, precisa colocá-lo no back-end do Excel para que funcione como uma função de planilha normal.
Abaixo estão as etapas para colocar o código VBA no back-end:
- Vá para a guia Desenvolvedor.
- Clique em Visual Basic (você também pode usar o atalho de teclado ALT + F11)
- No back-end do Editor VB que se abre, clique com o botão direito em qualquer um dos objetos da pasta de trabalho.
- Vá para ‘Inserir’ e clique em ‘Módulo’. Isso irá inserir o objeto de módulo para a pasta de trabalho.
- Na janela de código do módulo, copie e cole o código VBA mencionado acima.
Depois de ter o código VBA no back-end, você pode usar a função - ‘IdDuplicates’ como qualquer outra função de planilha regular.
Esta função tem um único argumento, que é a referência de célula da célula onde você tem o texto.
O resultado da função é TRUE (se houver palavras duplicadas) ou FALSE (se não houver duplicatas). Depois de ter essa lista de VERDADEIRO / FALSO, você pode filtrar aqueles com VERDADEIRO para obter todas as células que contêm sequências de texto duplicadas.
Observação: criei o código apenas para considerar as palavras com mais de três caracteres. Isso garante que, se houver palavras com 1, 2 ou 3 caracteres (como 12 A, K G M ou L D A) na sequência de texto, elas serão ignoradas durante a contagem das duplicatas. Se desejar, você pode alterar isso facilmente no código.
Esta função só estará disponível na pasta de trabalho onde você copiou o código no módulo. Caso queira que isso esteja disponível em outras pastas de trabalho também, você precisa copiar e colar este código nessas pastas de trabalho. Como alternativa, você também pode criar um suplemento (habilitando o que tornaria esta função disponível em todas as pastas de trabalho em seu sistema).
Além disso, lembre-se de salvar esta pasta de trabalho na extensão .xlsm (uma vez que contém um código de macro).
