Técnicas de limpieza e integración de datos en Excel

Así que recientemente descubrió que el título de su trabajo en realidad debería ser «conserje de datos». ¡Bienvenido al apasionante mundo de los negocios digitales!

Pasé años limpiando varios tipos de datos en Excel, desde encuestas de investigación de mercado hasta exportaciones de CRM y plataformas de análisis web.

En este artículo, compartiré algunos trucos que he aprendido a lo largo del camino, incluidos algunos consejos para manipular cadenas de texto de los veteranos jinetes de Excel en nuestro equipo de publicidad. También veremos muy brevemente cómo las herramientas de descubrimiento de datos pueden acelerar la limpieza de datos.

Esto es lo que cubriremos:


Deduplicación de varias columnas con EXACT
Integre hojas de cálculo y datos de codificación con VLOOKUP
Recortar y limpiar texto
La función RECORTAR
LIMPIO/INFERIOR
Combinar texto de varias celdas con el operador &
Texto en columnas para recortar URL
extracción de texto
Texto a columnas para extraer parte de una URL
Extracción de la palabra ‘Nth’
Herramientas de limpieza de datos más rápidas que Excel

Deduplicación de varias columnas con EXACT

Parece obvio usar la función de eliminación de duplicados de Excel para deduplicar sus datos:

Usado con permiso de microsoft

El problema es que suelen aparecer valores duplicados en columnas diferentes.

Por ejemplo, registramos las URL de las páginas a las que llegan los visitantes, así como las páginas a las que se convierten. Si el visitante convierte en la misma página en la que aterriza, este valor de URL es el mismo. En tal caso, la deduplicación debe hacerse con fórmulas.

Una de las fórmulas más útiles para este propósito es EXACTO. La sintaxis es increíblemente simple:

=EXACTO(celdaref1,ref2 de celda)

Ejemplo: =EXACTO(A1,B1)

El problema es que EXACT solo funciona con dos referencias de celda. Para probar un rango de celdas con EXACTO, deberá usar un operador AND:

=SI(Y(EXACTO(celdaref1,celdaref1:ref2 de celda), «OK», «¡ALERTA!»)

Ejemplo: =SI(Y(EXACTO(AI2,AI2:AK2)),»OK», «¡ALERTA!»)

Notar: También deberá ingresar la fórmula manteniendo presionadas las teclas CTRL y SHIFT mientras presiona la tecla ENTER. Si lo hace correctamente, verá corchetes alrededor de la fórmula en la ventana de fórmula (no funciona si solo escribe los corchetes antes de escribir la fórmula).

resultados exactos de la fórmula

Resultados de usar la fórmula EXACTA

Integre hojas de cálculo y datos de codificación con VLOOKUP

Si ha trabajado lo suficiente en un departamento de marketing, es probable que alguien haya hecho esta broma tonta sobre la creación de «la única hoja de cálculo para gobernarlos a todos»:

Anillo Lotr modelo 3d

Por lo general, esto significa que deberá consolidar muchas hojas de cálculo en una sola. Me gusta llamar a esto «integración de datos de hoja de cálculo», es decir, si los datos de hoja de cálculo realmente se pueden considerar integrados.

Puede ser un proceso manual tedioso si copia y pega valores. Afortunadamente, Tablas de búsqueda proporcionar una alternativa.

Por lo general, no usará tablas de búsqueda para incrustar algunas hojas de cálculo pequeñas. En cambio, son útiles cuando se intenta incrustar hojas de cálculo con decenas de miles de filas.

Si no está familiarizado con las tablas de búsqueda, básicamente le permiten buscar en una columna completa para que coincida con el valor de una celda determinada. Por ejemplo, puede buscar en la columna «A» para ver si hay una celda correspondiente al texto en la celda B2 en otra pestaña de la hoja de cálculo.

Luego puede recuperar los valores en las celdas adyacentes a la celda correspondiente en A. Por ejemplo, suponga que está buscando hacer coincidir una URL en la celda B2 con una URL en la columna A:

recuperar los valores de las celdas correspondientes

Después de que la fórmula BUSCARV encuentre la URL en la columna A, puede completar los valores para las columnas B, C y D.

La sintaxis de BUSCARV es un poco compleja. Una fórmula típica se ve así:
=(f2,’Pestaña de búsqueda’!$A$2:$D$11910,4,FALSO)

En este ejemplo:

  • F2 es la celda probada
  • $A$2 es superior la izquierda esquina de la mesa de búsqueda
  • $D$11910 es el mínimo correcto rincón de la mesa
  • 4 significa que la fórmula devolverá los valores de la Cuatro columna izquierda, columna D en este ejemplo

Entonces, la fórmula busca la URL en la columna A y, una vez que encuentra la URL, devuelve la clasificación de «mercado» adecuada.

Las tablas BUSCARV son una excelente manera de integrar rápidamente nuevos datos en los esquemas de clasificación existentes.

Obtenga más información sobre esta técnica de limpieza de datos de Excel que ahorra tiempo con este tutorial:

Recortar y limpiar texto

Mario Méndez es especialista sénior en software publicitario en Software Advice. Al analizar nuestros esfuerzos de búsqueda paga, se trata de una gran cantidad de texto y tiene algunos consejos útiles para compartir para limpiar el texto en Excel.

La función RECORTAR

Méndez explica que «si por alguna razón accidentalmente pones espacio extra en una celda, puedes usar la función de recorte para eliminarlo»:

=CORTAR(celdaref1)

Ejemplo: =CORTAR(A1)

función de recorte

LIMPIO/INFERIOR

Otro buen truco que destaca Méndez es usar funciones para limpiar las mayúsculas.

La función APROPIADA aplica mayúsculas y minúsculas a cadenas de texto en mayúsculas y minúsculas:
=PROPIO(celdaref1)

Ejemplo: =PROPIO(A1)

buen funcionamiento

La función LOWER cambia todo a minúsculas:

=INFERIOR(celdaref1)

Ejemplo: =INFERIOR(a1)

función inferior

Combinar texto de varias celdas con el operador &

Probablemente sepa lo que significa fusionar celdas. Esencialmente, esta función combina dos celdas adyacentes en una sola celda. Si fusiona B1 con A1, el texto de B1 se agrega a la celda A1 y B1 pierde todo el texto que contiene.

Hay dos problemas con la fusión:

  1. Es posible que no desee perder el texto en B1
  2. La fusión puede hacer que sus columnas se desordenen, si no tiene cuidado

Méndez comparte una solución alternativa: el operador &.

=celdaref1&ref2 de celda

Ejemplo: =A1&» «B1

Notar: Las comillas con el espacio dentro están ahí para agregar un espacio entre los valores de texto de las celdas combinadas; de lo contrario, funcionarán juntos como puede ver en la celda «C1» a continuación.

excel combinar texto de varias celdas

Texto en columnas para recortar URL

Otro problema común de limpieza de datos en los negocios digitales es acortar las URL o extraer información de ellas.

Por ejemplo, su sitio puede ofrecer diferentes versiones de la misma página, lo que puede resultar confuso al integrar datos para el análisis web.

Considere el siguiente ejemplo:

https://www.softwareadvice.com/crm/
https://www.softwareadvice.com/crm/?layout=var_so0
https://www.softwareadvice.com/crm/?layout=var_so1

Todas estas son versiones de la misma URL. Para hacerlos idénticos, lo cual es necesario para la integración de datos, necesitamos eliminar la parte final del «diseño». Esto se puede hacer con texto en columnas:

quitar URL final

Por lo general, elegirá la opción «delimitado» para separar el texto en un carácter:

separar el texto usando la opción delimitada

En este caso, elegiría dividir en «?» para eliminar la parte final de la URL:

eliminar URL final para normalizar

Lo bueno ahora está en una columna y la basura en otra:

URL final separada en una columna separada

extracción de texto

La extracción de texto es un poco más avanzada que solo cortar y limpiar cadenas. En la extracción de texto, intenta separar partes específicas de una cadena del resto de la cadena. Aquí hay dos técnicas bastante diferentes para hacer esto, una de las cuales usa texto en columnas.

Texto a columnas para extracción Parte desde una URL

A veces es necesario extraer parte desde una URL. Jennifer Sakaida, nuestra Gerente de Programas de Anuncios, desarrolló un truco para hacer esto para un proyecto de web scraping a gran escala. En él, volcamos toneladas de código fuente de páginas web en Excel para extraer información importante.

La técnica consiste en reemplazar partes de cadenas de texto con caracteres especiales. La zanahoria («^») es una buena opción para este propósito, ya que rara vez se encuentra en HTML.

Por ejemplo, imagina que queremos extraer nombres de productos de una larga lista de URL como:

https://www.softwareadvice.com/crm/salesforce-profile/
https://www.softwareadvice.com/crm/sugarcrm-profile/
https://www.softwareadvice.com/crm/infusionsoft-crm-profile/

Necesitamos ver qué tienen en común todas estas URL. Claramente, la parte que queremos, los nombres de marca, viene justo después de «/CRM/» en cada URL. Entonces podemos reemplazar «/CRM/» con «^» usando buscar/reemplazar para obtener la siguiente lista:

http://www.softwareadvice.com^salesforce-profile/
http://www.softwareadvice.com^sugarcrm-profile/
http://www.softwareadvice.com^infusionsoft-crm-profile/

También podemos ver que la parte posterior al nombre de la marca es la misma en todas las URL, por lo que usamos buscar/reemplazar para reemplazar «^» con «-perfil/» para obtener la siguiente lista:

http://www.softwareadvice.com^salesforce^
http://www.softwareadvice.com^sugarcrm^
http://www.softwareadvice.com^infusionsoft^

En este punto, podemos usar texto en las columnas en «^» para obtener solo los nombres de marca:

convertir texto a columnas

Extracción de la palabra ‘Nth’

El truco más elegante compartido por Méndez es extraer la enésima palabra de una cadena de texto.

Es una fórmula larga:

=RECORTAR(MEDIO(SUSTITUIR(celdaref1,» «,REPETIR(» «,LARGO(celdaref1))),(ref2 de celda)*LARGO(celdaref1)+1,LARGO(celdaref1)))

Cellref2 es la parte de la fórmula que controla la palabra de la cadena que está extrayendo. Pegas la cadena de texto en celdaref1. luego en ref2 de celdaingrese el número de la palabra que desea extraer (0 para la primera palabra, 1 para la segunda, 2 para la tercera, etc.)

Ejemplo: =RECORTAR(MEDIO(SUSTITUIR(A1,» «,REPETIR(» «,LARGO(A1))),(B1)*LARGO(A1)+1,LARGO(A1)))

En el siguiente ejemplo, puede ver cómo los números en la columna B controlan qué palabra de la cadena se extrae:

palabras de control en la cadena

Méndez explica que usa la fórmula «para ver si hay un tema común» en las cadenas de texto que analiza. Esta es una fórmula particularmente útil para preparar texto para análisis en contextos publicitarios y de marketing digital, ya que los enfoques basados ​​en palabras clave para la optimización de motores de búsqueda (SEO) y la investigación de mercado de motores de búsqueda (SEM) siguen siendo la norma.

Herramientas de limpieza de datos más rápidas que Excel

Si pasa una buena parte de su jornada laboral en tareas de limpieza de datos, podría ser el momento de considerar otras herramientas además de Excel. Existe toda una clase de software, conocido como herramientas de preparación de datos de autoservicio, para acelerar el tedioso trabajo de limpieza e integración de datos.

Estas herramientas ofrecen características avanzadas como «coincidencia aproximada» (coincidencia de campos que son similar pero no idéntico aprovechando la tecnología de aprendizaje automático) y extrayendo texto de formatos difíciles como PDF. Examinamos sus capacidades en nuestro informe de fusión de datos.

Además, eche un vistazo a nuestra guía de preparación de datos de autoservicio, que proporciona una descripción general de cómo se diferencian de las herramientas de análisis tradicionales.

Si tiene alguna pregunta sobre las herramientas de preparación de datos de autoservicio o desea compartir sus propias técnicas de limpieza de datos, puede comunicarse conmigo en danielharris@softwareadvice.com.

Deja un comentario

Tu dirección de correo electrónico no será publicada.