un amigo míoCon frecuencia estoy en contacto con él acerca de algunos de los problemas del mundo real que enfrenta cuando trabaja con datos en Excel.
Muchas veces traduzco su consulta a un tutorial de Excel en este sitio porque también podría ser útil para mis otros lectores.
Este es uno de esos tutoriales también.
Mi amigo me llamó la semana pasada con las siguientes preguntas:
Tengo datos de dirección en una columna en Excel y quiero identificar/filtrar celdas que contienen cadenas de texto repetidas (palabras) en la dirección.
Aquí hay un conjunto de datos similar en el que quiere filtrar celdas (celdas con flechas rojas) que tienen cadenas de texto repetidas:
Lo que dificulta esto ahora es que los datos no son consistentes.Dado que se trata de una compilación de conjuntos de datos creados manualmente por los representantes de ventas, puede haber discrepancias en los conjuntos de datos.
Piénsalo:
- Cualquier cadena de texto se puede repetir en este conjunto de datos.Por ejemplo, puede ser el nombre de una región o de una ciudad, o ambos.
- Las palabras están separadas por caracteres de espacio y no hay consistencia en si el nombre de la ciudad está después de seis u ocho caracteres.
- Hay miles de registros de este tipo y es necesario filtrar aquellos que tienen cadenas de texto repetidas.
Después de considerar muchas opciones, como texto en columnas y fórmulas, finalmente decidí usar VBA para hacer el trabajo.
Así que creé una función VBA personalizada ('IdDuplicate') para analizar estas celdas y darme VERDADERO si hay palabras duplicadas en la cadena de texto y FALSO si no hay duplicados (como se muestra a continuación):
Esta función personalizada analiza cada palabra en la cadena de texto y verifica cuántas veces aparece en el texto.Devuelve VERDADERO si el recuento es mayor que 1;De lo contrario, devuelve FALSO.
Además, fue creado para contar solo palabras con más de tres caracteres.
Una vez que tengo los datos VERDADERO/FALSO, puedo filtrar fácilmente todos los registros que son VERDADEROS.
Ahora déjame mostrarte cómo hacer esto en Excel.
Código VBA para función personalizada
Esto se hace creando una función personalizada en VBA.Esta función se puede usar como cualquier otra función de hoja de cálculo en Excel.
Aquí está su código VBA:
Función 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 Paso -1 Si Len(StringtoAnalyze(i)) < minWordLen Entonces Ir a SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) Entonces IdDuplicates = "TRUE" Ir a SkipB End If Next j SkipA: Next i IdDuplicates = "FALSO" SkipB: función final
Cómo usar este código VBA
Ahora que tiene el código VBA, debe colocarlo en el backend de Excel para que pueda funcionar como una función de hoja de cálculo normal.
Estos son los pasos para poner el código VBA en el backend:
- Ve a la pestaña de desarrollador.
- Haga clic en Visual Basic (también puede usar el atajo de teclado ALT+F11)
- En el backend del editor de VB abierto, haga clic con el botón derecho en cualquier objeto del libro de trabajo.
- Vaya a "Insertar" y haga clic en "Módulo".Esto insertará el objeto de módulo del libro de trabajo.
- En la ventana del código del módulo, copie y pegue el código VBA mencionado anteriormente.
Una vez que tenga el código VBA en el backend, puede usar la función - 'IdDuplicates' como cualquier otra función de hoja de trabajo regular.
Esta función toma un parámetro, la referencia de celda de la celda donde tienes el texto.
El resultado de esta función es VERDADERO (si hay palabras duplicadas) o FALSO (si no hay palabras duplicadas).Una vez que tenga esta lista VERDADERO/FALSO, puede filtrar aquellos con VERDADERO para obtener todas las celdas que tienen cadenas de texto repetidas.
Nota: El código que creé solo tiene en cuenta las palabras que tienen más de tres caracteres.Esto garantiza que si hay palabras en la cadena de texto que tienen 1, 2 o 3 caracteres (por ejemplo, 12 A, KGM o LDA), esas palabras se ignoran al contar los duplicados.Puede cambiar esto fácilmente en el código si es necesario.
Esta característica solo está disponible en el libro de trabajo donde copió el código en el módulo.Si desea que este código también esté disponible en otros libros de trabajo, debe copiar y pegar este código en esos libros de trabajo.Alternativamente, puede crear un complemento (al habilitar esto, la función estará disponible en todos los libros de su sistema).
Además, recuerde guardar este libro de trabajo con una extensión .xlsm (porque contiene código de macro).
