um amigo meuEstou sempre em contato com ele sobre alguns dos problemas do mundo real que ele enfrenta ao trabalhar com dados no Excel.
Muitas vezes eu traduzo sua consulta em um tutorial do Excel neste site porque pode ser útil para meus outros leitores também.
Este é um desses tutoriais também.
Meu amigo me ligou na semana passada com as seguintes perguntas:
Tenho dados de endereço em uma coluna no Excel e quero identificar/filtrar células que contenham sequências de texto repetidas (palavras) no endereço.
Aqui está um conjunto de dados semelhante em que ele deseja filtrar células que possuem sequências de texto repetidas (células com setas vermelhas):
O que torna isso difícil agora é que os dados não são consistentes.Como esta é uma compilação de conjuntos de dados criados manualmente por representantes de vendas, pode haver discrepâncias nos conjuntos de dados.
pense nisso:
- Qualquer string de texto pode ser repetida neste conjunto de dados.Por exemplo, pode ser um nome de região ou um nome de cidade, ou ambos.
- As palavras são separadas por caracteres de espaço e não há consistência em saber se o nome da cidade está depois de seis ou oito caracteres.
- Existem milhares desses registros e é necessário filtrar aqueles que possuem seqüências de texto repetidas.
Depois de considerar muitas opções, como texto para colunas e fórmulas, finalmente decidi usar o VBA para fazer o trabalho.
Então, criei uma função VBA personalizada ('IdDuplicate') para analisar essas células e me fornecer TRUE se houver palavras duplicadas na string de texto e FALSE se não houver duplicatas (como mostrado abaixo):
Essa função personalizada analisa cada palavra na sequência de texto e verifica quantas vezes ela ocorre no texto.Retorna TRUE se a contagem for maior que 1;Caso contrário, retorne FALSE.
Além disso, 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 função personalizada
Isso é feito criando uma função personalizada no VBA.Essa função pode ser usada como qualquer outra função de planilha no Excel.
Aqui está o seu código VBA:
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), " ") Para i = UBound(StringtoAnalyze) To 0 Etapa -1 Se 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
Como usar este código VBA
Agora que você tem o código VBA, você precisa colocá-lo no backend do Excel para que ele funcione como uma função de planilha normal.
Aqui estão as etapas para colocar o código VBA no back-end:
- Vá para a guia do desenvolvedor.
- Clique em Visual Basic (você também pode usar o atalho de teclado ALT+F11)
- No back-end do editor VB aberto, clique com o botão direito do mouse em qualquer objeto de pasta de trabalho.
- Vá em "Inserir" e clique em "Módulo".Isso inserirá o objeto do módulo da 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 backend, você pode usar a função – 'IdDuplicates' como qualquer outra função de planilha regular.
Esta função recebe um parâmetro, a referência de célula da célula onde você tem o texto.
O resultado desta função é TRUE (se houver palavras duplicadas) ou FALSE (se não houver duplicatas).Uma vez que você tenha esta lista TRUE/FALSE, você pode filtrar aquelas com TRUE para obter todas as células que possuem strings de texto repetidas nelas.
Nota: O código que criei é apenas para contabilizar palavras com mais de três caracteres.Isso garante que, se houver palavras na cadeia de texto com 1, 2 ou 3 caracteres (por exemplo, 12 A, KGM ou LDA), essas palavras serão ignoradas ao contar duplicatas.Você pode alterar isso facilmente no código, se necessário.
Esse recurso só está disponível na pasta de trabalho em que você copiou o código no módulo.Se você quiser que esse código também esteja disponível em outras pastas de trabalho, será necessário copiar e colar esse código nessas pastas de trabalho.Como alternativa, você pode criar um suplemento (habilitar isso tornará o recurso disponível em todas as pastas de trabalho em seu sistema).
Além disso, lembre-se de salvar esta pasta de trabalho com uma extensão .xlsm (porque ela contém código de macro).
