Limpieza de datos

Una tabla que parece exacta puede dar una respuesta incorrecta. La limpieza comienza por el significado de sus registros: qué ocurrió, a quién o qué describe cada fila y qué campos necesita el análisis.

Qué significa la integridad de los datos

La integridad de los datos implica exactitud, completitud, consistencia y fiabilidad a lo largo del ciclo de vida de los datos. Los valores deben coincidir con los hechos que afirman registrar. La información necesaria debe estar presente y un mismo hecho debe conservar su significado al pasar de un sistema a otro.

La integridad puede deteriorarse durante la introducción, el almacenamiento, la transferencia o el análisis. Alguien puede escribir mal una cantidad. Las copias en dos sistemas pueden dejar de coincidir, un problema de replicación de datos. Una transferencia interrumpida puede cargar la mitad de un archivo. Ordenar una sola columna puede asociar cada importe al pedido equivocado. Los fallos del sistema y los cambios no autorizados también pueden dañar los registros.

Reconoce el problema antes de corregirlo

Los datos sucios son incompletos, incorrectos, inconsistentes o irrelevantes para la pregunta. Los distintos problemas requieren distintas acciones.

Un formato ordenado no garantiza la exactitud. Una fecha de entrega puede parecer una fecha y aun así ser anterior a la fecha del pedido. Un cliente de prueba puede tener un registro completo pero ser irrelevante para las ventas a clientes reales.

Sigue el efecto sobre una respuesta

Supón que el pedido 1003, por un importe de 250,00, aparece dos veces en una exportación. Contar las filas añade un pedido de más y sumar los importes añade 250,00 de más. Las fórmulas pueden funcionar perfectamente mientras los datos de entrada producen un resultado falso. Las variantes de texto crean otro problema: un gráfico puede repartir las ventas de Lisboa entre etiquetas de ciudad separadas.

Los datos limpios son adecuados para el análisis que se realiza. La ausencia de un segundo nombre no afecta a las ventas por región. La ausencia del importe de un pedido sí afecta a las ventas totales. Evalúa la completitud según los campos necesarios para la pregunta y registra las ausencias que permanezcan.

Protege el punto de partida

Conserva una copia intacta de los datos en bruto antes de hacer cambios. Usa otro archivo, una hoja independiente o la tabla original de la base de datos y limpia una copia de trabajo. Una herramienta como Quitar duplicados elimina filas de su rango seleccionado; la copia de los datos en bruto te permite comparar y volver a tomar una decisión.

Sigue cinco etapas: examinar, decidir, aplicar, documentar y verificar. Registra un problema antes de elegir su tratamiento. Registra cada tratamiento cuando lo apliques y después comprueba el resultado frente a los datos en bruto. Los datos internos necesitan el mismo examen que un archivo externo: las importaciones repetidas y los cambios en el significado de los campos también ocurren dentro de las organizaciones. Comunica al titular de los datos los problemas recurrentes de introducción o exportación para que la siguiente entrega mejore.

El perfilado de datos es un examen sistemático de un conjunto de datos antes de cambiarlo. Te proporciona un valor de referencia inicial y una lista de problemas: el tamaño de la tabla, el contenido de cada columna, las ausencias, las claves repetidas y los valores que necesitan investigación.

Empieza por la estructura de la tabla

Identifica qué significa una fila, por ejemplo, un pedido o un cliente. Comprueba que haya una fila de encabezados y un tipo de información por columna. Encuentra la clave, la columna o combinación de columnas que debe identificar un registro. Un identificador de pedido repetido requiere investigación; que los nombres de ciudad se repitan es esperable.

Registra el recuento inicial de filas. Después, examina el tipo esperado de cada columna, si es obligatoria y sus valores permitidos. Un diccionario de datos o el titular de los datos proporciona estos significados. El mínimo y el máximo pueden revelar una fecha fuera del período del informe o un valor de sustitución como 999 en una columna de edades.

Examina una hoja de cálculo

Ordena y filtra la tabla manteniendo juntas las filas completas. Ordena de forma ascendente y descendente para encontrar celdas vacías, texto inesperado y valores extremos. Los menús desplegables de los filtros revelan las formas de escritura de las categorías. Los filtros de Excel no distinguen mayúsculas de minúsculas, por lo que una sola categoría mostrada puede ocultar diferencias de uso de mayúsculas; usa EXACT cuando necesites comprobarlas. El formato condicional puede resaltar claves repetidas y valores fuera de un rango.

Para las filas 2 a 501, algunas comprobaciones útiles son:

• =COUNTA(B2:B501) cuenta las celdas no vacías.
• =COUNTBLANK(B2:B501) cuenta las celdas vacías, incluidas las fórmulas que devuelven texto vacío.
• =COUNT(B2:B501) cuenta las celdas numéricas.
• =MIN(B2:B501) y =MAX(B2:B501) muestran el rango numérico.
• =COUNTIF(C2:C501,"Lisbon") cuenta las entradas coincidentes sin distinguir mayúsculas de minúsculas.

Si el recuento numérico de una columna de importes es menor que su recuento de celdas no vacías, examina la diferencia. Algunas entradas pueden ser texto numérico; otras pueden ser etiquetas como «N/A». Una celda que solo contiene un espacio también requiere atención aunque parezca vacía.

Examina con SQL

Estas consultas leen los datos sin cambiarlos:

SELECT COUNT(*) AS rows_total,
       COUNT(amount) AS amounts_present,
       COUNT(*) - COUNT(amount) AS amounts_missing,
       MIN(order_date) AS first_date,
       MAX(order_date) AS last_date
FROM orders;

COUNT(*) cuenta las filas. COUNT(amount) cuenta los importes que no son NULL. La diferencia cuenta los importes ausentes representados por NULL, pero no detecta los valores de sustitución de texto.

Para examinar los valores de categoría y su frecuencia:

SELECT city, COUNT(*) AS n
FROM orders
GROUP BY city
ORDER BY city;

GROUP BY reúne los valores iguales; COUNT(*) cuenta las filas de cada grupo. NULL forma su propio grupo. La igualdad de texto sigue la configuración de comparación de la base de datos, llamada intercalación. PostgreSQL y SQLite suelen separar las variantes de mayúsculas y minúsculas; las configuraciones habituales de MySQL las combinan.

Convierte las comprobaciones en una lista de problemas

Para seis importes 40, NULL, 25, 60, NULL y 35, el recuento de filas es 6 y el de valores presentes es 4: dos importes requieren atención. Para las ciudades «Lisbon», «lisbon », «Porto», NULL, «LISBON» y «Porto», la comparación exacta de texto encuentra cuatro formas de escritura que no son NULL, aunque solo están representadas dos ciudades.

Anota ambos hallazgos, junto con la columna afectada y el recuento. Examina los valores frecuentes y los extremos para detectar valores de sustitución ocultos, registros de prueba y fechas como «1900-01-01». Perfilar primero toda la tabla también revela dependencias: eliminar espacios sobrantes puede cambiar el recuento de duplicados, así que la eliminación de duplicados debe esperar hasta que se estandaricen los campos de comparación.

La validación de datos comprueba si las entradas cumplen las reglas para que los datos sean aceptables. Una entrada puede estar presente y mostrarse correctamente y aun así incumplir una regla. Una cantidad de -3 sigue siendo inválida cuando las cantidades de pedido deben ser positivas.

Elige las reglas según el significado de los datos

Lee el diccionario de datos o pregunta al titular de los datos. Una reserva de hotel para uso diurno puede permitir cero noches; otro sistema de reservas puede exigir al menos una. Anota el límite real y si el campo es obligatorio.

Una comprobación de formato verifica un patrón, no si el hecho subyacente es verdadero. Una comprobación básica de correo electrónico que busque una «@» y texto a cada lado no puede confirmar que exista un buzón.

Señala los incumplimientos en una hoja de cálculo

Pon una prueba en una columna auxiliar y cópiala hacia abajo. Para un porcentaje numérico obligatorio en B2, =AND(ISNUMBER(B2),B2>=0,B2<=100) comprueba tanto el tipo como el rango. La comprobación del tipo importa porque una celda vacía puede comportarse como cero en una comparación.

Con fechas reales en C y D, =IF(D2<C2,"check","ok") señala una entrega anterior a su pedido. Comprueba por separado si faltan fechas obligatorias. Para los identificadores de la columna A, =COUNTIF($A$2:$A$501,A2)>1 señala los valores repetidos. Filtra los resultados auxiliares para ver todos los incumplimientos.

Enumera los incumplimientos con SQL

SELECT * FROM orders
WHERE quantity <= 0 OR quantity IS NULL;

Esto encuentra cantidades no positivas y ausentes. Para comprobar los estados:

SELECT * FROM orders
WHERE status NOT IN ('open', 'shipped', 'returned')
   OR status IS NULL;

La comprobación explícita de NULL importa. NOT IN por sí solo no devuelve una fila con un estado NULL, porque su comparación es desconocida. Una comprobación entre campos como delivery_date < order_date también necesita comprobaciones de presencia independientes para las fechas obligatorias.

Evita errores nuevos y resuelve los antiguos

Validación de datos de Excel y de Google Sheets puede proporcionar una lista desplegable, restringir un rango numérico o advertir sobre una entrada. Los tipos y las restricciones de las bases de datos, como NOT NULL y UNIQUE, aplican reglas relacionadas. Añadir una regla no repara los registros antiguos; examínalos por separado. Rodear con un círculo datos no válidos de Excel puede marcar los incumplimientos existentes.

Considera las edades de una encuesta de adultos 34, 0, 41, 230 y «cuarenta», con un rango de números enteros de 18 a 120. «cuarenta» no supera la comprobación del tipo; 0 y 230 no superan la comprobación del rango. La palabra tiene el significado numérico claro de 40. El valor que se pretendía escribir con 230 es incierto, así que no adivines entre 23 y 30. Recupera un valor fiable, márcalo como ausente o excluye el registro, y anota la decisión.

Un valor inusual es distinto de uno inválido. Un pedido de 500 unidades puede ser real. Valídalo según las reglas de pedidos y el registro de origen antes de decidir que es un error.

Dos filas similares no son necesariamente un mismo registro introducido dos veces. Un duplicado describe la misma entidad o el mismo suceso real que otra fila. Empieza por decidir qué identifica a esa entidad o suceso.

Define la clave y las reglas de comparación

Un identificador de pedido puede identificar un pedido. Un nombre por sí solo suele ser una mala clave de cliente porque distintas personas pueden compartirlo. Incluso dos compras idénticas el mismo día pueden ser sucesos reales distintos. Comprueba el significado de las filas y la clave indicada por el titular de los datos antes de eliminar filas que parezcan iguales.

Los duplicados exactos coinciden en todas las columnas. Los duplicados aproximados se refieren a lo mismo pero presentan pequeñas diferencias, como un espacio adicional o un número de teléfono cambiado. Limpia los campos de comparación antes de buscar. Si el correo electrónico es la clave acordada, normaliza los espacios y las mayúsculas y minúsculas en una columna auxiliar.

Para los cinco correos «ana@example.com», «ben@example.com», «Ana@Example.com », «chen@example.com» y «ben@example.com», la comparación exacta de los datos en bruto encuentra la entrada repetida de Ben. Eliminar espacios sobrantes y convertir a minúsculas también revela la entrada repetida de Ana. Quedan tres claves de cliente después de eliminar dos copias adicionales.

Señala las filas antes de eliminarlas

Para las claves limpias en B2:B501:

• =COUNTIF($B$2:$B$501,B2)>1 señala todas las filas cuya clave se repite.
• =COUNTIF($B$2:B2,B2)>1 señala solo la segunda aparición y las siguientes.

El segundo rango crece al copiar hacia abajo, por lo que la primera aparición tiene un recuento de uno. COUNTIF no distingue mayúsculas de minúsculas; elimina primero los espacios sobrantes. Examina los grupos señalados para detectar información contradictoria antes de aplicar una regla de conservación.

Elige qué se conserva

Quitar duplicados de Excel y de Google Sheets compara las columnas que selecciones. Seleccionar solo el correo puede descartar una fila con un número de teléfono distinto. Seleccionar todas las columnas conserva esa discrepancia porque las filas completas son diferentes. Ambas herramientas usan sus propias reglas de coincidencia, incluidas comparaciones que no distinguen mayúsculas de minúsculas, por lo que no sirven para exigir esa distinción carácter por carácter sin una comprobación adicional.

Excel conserva la primera aparición. Si debe conservarse el registro más reciente, ordena primero las filas completas por fecha de actualización, con la más reciente primero. Resuelve explícitamente los empates o registros contradictorios. Una regla documentada podría conservar el registro confirmado más recientemente o combinar los campos verificados. Registra tanto las columnas elegidas como el número de filas eliminadas.

=UNIQUE(A2:C501) devuelve las filas distintas en un rango nuevo en las versiones actuales de Excel y en Google Sheets. Conserva la fuente y es útil para obtener una vista previa del resultado.

Encuentra claves repetidas con SQL

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

GROUP BY crea un grupo para cada correo; HAVING conserva los grupos cuyo recuento supera uno. WHERE filtra las filas individuales antes de agruparlas. Ejecuta la comprobación sobre claves estandarizadas, ya sea usando una tabla limpia o agrupando por la expresión de limpieza.

SELECT DISTINCT email, name FROM customers; devuelve cada combinación de correo y nombre una vez. No garantiza correos únicos: se conservan ambas filas si tienen un mismo correo y distintos nombres. Comprueba la clave acordada después de eliminar duplicados.

Las importaciones repetidas y las exportaciones con datos solapados suelen crear duplicados. Registra la causa con el titular de los datos para que la corrección de la tabla de hoy también mejore la próxima exportación.

Una ausencia puede verse como una celda vacía u ocultarse detrás de un valor aparentemente plausible. Averigua cómo registra la fuente la información ausente antes de contarla. Un cero registrado puede ser real, mientras que «unknown» puede ser un valor de sustitución.

Distingue celdas vacías, texto y NULL

En una hoja de cálculo, una celda vacía, una celda que contiene un espacio y una fórmula que devuelve "" pueden parecer iguales. ISBLANK solo es verdadero para una celda realmente vacía. Un espacio es texto. COUNTBLANK también cuenta las fórmulas que devuelven texto vacío, pero no cuenta una celda que contiene un espacio. Para espacios normales, =TRIM(B2)="" detecta tanto las entradas vacías como las que solo contienen espacios.

SUM, AVERAGE y COUNT de las hojas de cálculo omiten las celdas vacías y el texto en un rango de referencia. Incluyen el cero numérico. Para los importes 48, 52, una celda vacía, 45, una celda vacía, 55, 50 y 46, los importes conocidos suman 296. AVERAGE divide entre seis y da aproximadamente 49,33. Sustituir las celdas vacías por cero cambia el denominador a ocho y la media a 37.

En SQL, NULL señala un valor desconocido o ausente. Cero es un número conocido. La cadena vacía '' es texto sin caracteres en PostgreSQL, MySQL, SQL Server y SQLite. Los campos vacíos importados pueden aparecer de cualquiera de las dos formas, así que comprueba la representación almacenada:

SELECT * FROM orders
WHERE city IS NULL OR TRIM(city) = '';

Usa IS NULL e IS NOT NULL. amount = NULL no encuentra los importes ausentes: la comparación con NULL produce un resultado desconocido, que WHERE no conserva.

Conoce el denominador

COUNT(*) cuenta todas las filas. COUNT(amount), SUM, AVG, MIN y MAX omiten los importes NULL. Para 40, NULL, 25, 60, NULL y 35, hay seis filas pero cuatro importes conocidos. SUM es 160 y AVG es 40 porque divide 160 entre cuatro.

Estas funciones no pueden identificar un valor de sustitución numérico como -1 o 999. Confirma su significado con el titular de los datos y después cuéntalos por separado o normalízalos como valores ausentes. Examina las frecuencias de los códigos inusuales además de los recuentos de celdas vacías.

Busca patrones en las ausencias

Cuenta las entradas ausentes por columna y como proporción de las filas. Dos importes ausentes de ocho representan el 25 %. Después, examina si las ausencias se concentran por tienda, fuente o mes. Un campo ausente solo en una fuente de exportación puede apuntar a un problema de importación que se puede corregir.

La razón de la ausencia importa:

• Ausencia completamente al azar: un suceso no relacionado, como un fallo aleatorio al guardar, causa la ausencia.
• Ausencia al azar: la probabilidad de ausencia depende de información registrada, como que los clientes de más edad omitan con más frecuencia un campo opcional en línea.
• Ausencia no aleatoria: la probabilidad depende del propio valor ausente, como que quienes tienen ingresos muy altos dejen vacío el campo de ingresos.

En el caso de los ingresos, la media de quienes responden puede ser inferior a la media de todas las personas. Rellenar las ausencias con un solo número no elimina ese problema de selección. Informa de lo que falta y de a quiénes describen los valores restantes antes de interpretar una media.

Los valores ausentes requieren una decisión vinculada a la pregunta que se está respondiendo. Un cliente sin edad registrada aún puede contribuir a las ventas totales. Un pedido sin importe no puede aportar un valor conocido a los ingresos.

Elige una acción

Intenta primero recuperar el valor. Consulta una factura, el formulario original o el sistema de registro oficial. Un total de línea calculado a partir de una cantidad y un precio unitario verificados es información recuperada. Un valor estimado a partir de otros clientes es una estimación, llamada imputación.

Conserva el significado de cada número

Rellenar una categoría con «Unknown» mantiene la fila en los recuentos y deja visible la ausencia. Rellenar un campo numérico con cero solo se justifica cuando la ausencia significa realmente cero. Un recuento de devoluciones ausente puede significar cero si el proceso de origen confirma esa convención; un importe de venta desconocido no tiene ese significado.

Para los importes conocidos 48, 52, 45, 55, 50 y 46, la suma es 296 y la media es aproximadamente 49,33. Si dos celdas vacías se cambian a cero, la suma sigue siendo 296 pero la media baja a 37. Las filas adicionales cuentan ahora como observaciones de cero.

Rellenar con la media o la mediana también cambia los datos. La mediana de los seis importes conocidos es 49. Rellenar ambas ausencias con 49 añade 98 y hace que la media de las ocho entradas sea 49,25. Esos dos valores añadidos no se observaron. Los valores centrales repetidos hacen que los datos parezcan menos dispersos, así que conserva un indicador que los identifique. Para valores muy asimétricos como los ingresos, la mediana suele describir mejor un valor típico que la media.

Aplica la regla de forma visible

En SQL, COALESCE(city, 'Unknown') devuelve el primer argumento que no es NULL. Rellena las ciudades NULL, pero deja sin cambios una cadena vacía. NULLIF(city, 'N/A') convierte exactamente ese valor de sustitución en NULL. Combinar la normalización y el relleno hace explícito el tratamiento:

COALESCE(NULLIF(TRIM(city), ''), 'Unknown') AS city

Una columna auxiliar de categorías en una hoja de cálculo puede usar =IF(TRIM(B2)="","Unknown",B2). Un indicador separado puede usar =TRIM(B2)="" para que el estado ausente original siga visible después de rellenar.

Ajusta las exclusiones al análisis

Para calcular los ingresos totales de 1000 pedidos con tres importes ausentes, busca primero las facturas. Si no logras recuperarlos, informa de que el total excluye 3 de 1000 pedidos, es decir, el 0,3 %. No descartes una fila porque un campo que no se usa esté vacío.

Si faltan 400 de 1000 edades de clientes, rellenarlas con la media convertiría el 40 % de la columna en estimaciones. Un análisis de las 600 edades conocidas necesita una declaración clara sobre su cobertura. Comprueba por qué faltan esas edades: eliminar filas también puede distorsionar los resultados cuando esos clientes difieren del resto. Mantén un tratamiento consistente y documenta cualquier cambio posterior.

La limpieza de texto hace comparables los valores con el mismo significado. Empieza por enumerar los valores distintos y sus recuentos. Esto revela si una columna necesita cambios de espacios, mayúsculas y minúsculas consistentes o una asignación de varias formas de escritura a una sola etiqueta.

Elimina espacios y caracteres ocultos

En Excel y Google Sheets, =TRIM(A2) elimina los espacios de ambos extremos y reduce los espacios internos repetidos a uno. Convierte « Ana Silva » en «Ana Silva». =CLEAN(A2) elimina caracteres no imprimibles como los saltos de línea.

Un espacio de no separación copiado de una página web puede sobrevivir a TRIM. Sustituye primero el carácter 160 por un espacio normal:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

Lee las funciones anidadas de dentro hacia fuera: SUBSTITUTE se ejecuta primero y después TRIM actúa sobre su resultado. Examina ejemplos limpios, porque eliminar un salto de línea puede unir palabras que deberían permanecer separadas.

En SQL, TRIM(city) elimina los espacios de los extremos, pero deja los espacios internos repetidos. Por tanto, TRIM(' Ana Silva ') conserva tres espacios entre los nombres. LTRIM cambia el extremo izquierdo y RTRIM el derecho. Elige una expresión de limpieza adecuada a los caracteres que hayas encontrado.

Estandariza las mayúsculas y minúsculas de forma deliberada

UPPER y LOWER funcionan en ambas herramientas. Las minúsculas pueden ser una convención útil para el correo electrónico; las mayúsculas pueden ser adecuadas para los códigos de país. PROPER de una hoja de cálculo pone las palabras en mayúscula inicial, pero no puede determinar la escritura correcta de un nombre: «mcdonald» se convierte en «Mcdonald» y «PT123» en «Pt123». Revisa los nombres y conserva los códigos con las mayúsculas y minúsculas previstas.

Las comparaciones de texto siguen la configuración de la herramienta. COUNTIF, VLOOKUP y los filtros de Excel no distinguen mayúsculas de minúsculas. PostgreSQL suele tratar «Lisbon» y «LISBON» como valores de texto distintos. =EXACT(A2,B2) de una hoja de cálculo comprueba si el texto es idéntico, incluidas las mayúsculas y minúsculas. Estandariza explícitamente cuando una misma categoría deba funcionar de forma consistente entre herramientas, conservando las distinciones significativas en identificadores que distinguen mayúsculas de minúsculas.

Asigna valores completos a una categoría

Eliminar espacios sobrantes y cambiar mayúsculas y minúsculas no corrige las distintas formas de escritura. Una tabla de correspondencias puede asociar cada variante con una etiqueta estándar. Busca el valor limpio completo con coincidencia exacta y examina los valores sin correspondencia.

En SQL, CASE puede expresar la misma asignación:

CASE
  WHEN UPPER(TRIM(country)) IN
       ('NL', 'NETHERLANDS', 'THE NETHERLANDS', 'HOLLAND')
    THEN 'Netherlands'
  ELSE TRIM(country)
END AS country

ELSE conserva los valores sin correspondencia para revisarlos. Cuenta las categorías restantes después de la asignación; no supongas que las primeras filas representan todas las formas de escritura.

Evita daños por sustitución

SUBSTITUTE, Buscar y reemplazar y REPLACE de SQL pueden sustituir texto dentro de un valor más largo. Sustituir «NY» en todas partes cambia «Store NYC, NY» a «Store New YorkC, New York». Para etiquetas de categorías, busca la coincidencia con toda la celda o usa una tabla de correspondencias. Para direcciones y nombres, examina el alcance previsto de cada sustitución.

Para las ciudades «Lisbon», «lisbon », «Porto», un valor ausente, «LISBON» y «Porto», UPPER(TRIM(city)) produce LISBON tres veces y PORTO dos veces, con una ausencia. Analiza la columna limpia y después úsala para buscar coincidencias y comprobar duplicados. Conservar la columna auxiliar mientras se agrupa por la ciudad en bruto mantendría el problema original.

Un valor de texto combinado puede ocultar información necesaria para ordenar o buscar coincidencias. Pon cada parte necesaria en su propia columna y conserva el original hasta que hayas comprobado la división.

Divide por un separador fiable

Un delimitador marca el límite entre partes. Una coma en «Silva, Ana» separa el apellido del nombre. Texto en columnas de Excel y Dividir texto en columnas de Google Sheets permiten elegir ese separador. Inserta columnas vacías a la derecha antes de dividir para que el resultado no sobrescriba otros campos.

Google Sheets puede usar =SPLIT(A2,","); las versiones actuales de Excel pueden usar =TEXTSPLIT(A2,","). Elimina los espacios sobrantes de las partes obtenidas si hay un espacio después de la coma. Comprueba las filas sin delimitador o con más de uno antes de aplicar una regla a toda la columna.

No dividas todos los nombres por los espacios. «Ana Maria Silva» tiene más partes que un nombre de dos palabras, y un espacio por sí solo no establece qué palabras pertenecen al nombre o al apellido. Usa una estructura de origen definida o conserva un campo de nombre completo cuando la estructura sea incierta.

Extrae por posición cuando la estructura sea fija

Para el código de producto «PT-LIS-0042», el país ocupa las posiciones 1–2, la ubicación las posiciones 4–6 y el código final las posiciones 8–11.

El recuento de posiciones comienza en uno. Conserva el código extraído como texto para mantener sus ceros iniciales. Las posiciones fijas solo funcionan cuando la estructura del campo es consistente.

Para texto de longitud variable, localiza el delimitador. En «Silva, Ana», FIND(",",A2) devuelve 6. =LEFT(A2,FIND(",",A2)-1) toma los cinco caracteres anteriores a la coma. La resta excluye la propia coma. FIND distingue mayúsculas de minúsculas; SEARCH no las distingue al buscar letras.

Usa la función equivalente de SQL

SUBSTRING(code, 4, 3) extrae el código de ubicación central en PostgreSQL; algunas bases de datos usan SUBSTR. Las funciones de longitud de caracteres también varían: PostgreSQL y SQLite usan LENGTH, MySQL usa CHAR_LENGTH y SQL Server usa LEN, que ignora los espacios finales. LENGTH de MySQL cuenta bytes, por lo que puede diferir del recuento de caracteres para texto como «é».

Una comprobación de PostgreSQL WHERE LENGTH(code) <> 11 encuentra códigos de longitud incorrecta. Una comprobación de longitud por sí sola no determina si las letras, los dígitos y los guiones están en los lugares correctos.

Combina partes con un separador claro

=A2&" "&B2 o =CONCATENATE(A2," ",B2) une dos campos de una hoja de cálculo con un espacio. =TEXTJOIN(" ",TRUE,A2:C2) une un rango y omite las celdas vacías. CONCAT de Excel acepta varios argumentos; CONCAT de Google Sheets admite exactamente dos.

En PostgreSQL, first_name || ' ' || last_name concatena texto. Con este operador, una parte NULL hace que el resultado sea NULL. CONCAT trata NULL de forma distinta según el dialecto: MySQL lo propaga, mientras que PostgreSQL y SQL Server lo omiten. Trata las partes opcionales de forma deliberada con COALESCE y comprueba los separadores resultantes.

Una presentación consistente solo es útil cuando los valores significan lo mismo. Las fechas necesitan un orden conocido, los números necesitan separadores conocidos y las mediciones necesitan una sola unidad antes de comenzar los cálculos.

Establece la convención de la fuente

El texto «03/04/2025» puede significar el 4 de marzo o el 3 de abril. Una fecha como «25/03/2025» establece que el día va primero en esa entrada, pero un archivo combinado puede contener varios formatos. Confirma la convención con el titular de los datos o en la configuración de exportación y comprueba si hay fuentes mezcladas antes de convertir entradas ambiguas.

Usa el orden de fecha ISO 8601, YYYY-MM-DD, para un intercambio claro: «2025-03-04» indica el 4 de marzo. El texto de fechas ISO uniforme también se ordena cronológicamente. Usa el reloj de 24 horas para las horas, como «14:30», y registra la zona horaria cuando los sucesos abarquen varias zonas.

Separa los valores almacenados de su presentación

Las hojas de cálculo almacenan las fechas reales como números de serie. En el sistema de fechas predeterminado de Excel y en Google Sheets, el 1 de enero de 2025 es 45 658 y el 4 de marzo de 2025 es 45 720. Aplicar a esa fecha numérica el formato YYYY-MM-DD cambia su apariencia; no cambia el día registrado.

El texto que parece una fecha es distinto. Aplicar un formato de fecha al texto no crea un valor de fecha real. Comprueba el tipo y convierte el texto usando la estructura de origen confirmada antes de darle formato. La alineación puede ser una pista, pero las comprobaciones del tipo y el comportamiento en los cálculos aportan pruebas más sólidas porque la alineación puede cambiarse manualmente.

El formato numérico funciona del mismo modo. Un valor almacenado de 0,125 puede mostrarse como 0,13 con dos decimales mientras los cálculos siguen usando 0,125. =ROUND(A2,2) cambia el resultado numérico a dos decimales. Conserva toda la precisión durante la limpieza a menos que la definición del campo requiera un valor redondeado: tres tercios redondeados por separado a 0,33 suman 0,99.

Interpreta correctamente los separadores numéricos

«1,234.5» y «1.234,5» expresan el mismo número según distintas convenciones. Importar con la configuración regional incorrecta puede cambiar la magnitud o dejar un valor como texto. Compara varios valores importados con su fuente, incluidos valores con separadores decimales y de agrupación.

En Excel, =NUMBERVALUE("1.234,5",",",".") indica explícitamente la coma decimal y el punto de agrupación y devuelve 1234,5. Configura correctamente la región de importación en otras herramientas. No elimines todas las comas sin comprobar su función: pueden separar millares o marcar la parte decimal.

Convierte las unidades antes de combinar valores

Una columna de peso que contiene «2.5 kg», «750 g» y «1.2 kg» mezcla etiquetas y unidades. Extrae el número y la unidad, convierte los gramos a kilogramos y almacena 2,5, 0,75 y 1,2 en una columna numérica weight_kg.

Sumar los números originales da 753,7, que no tiene una unidad consistente. Mantén una unidad en el encabezado de la columna y números en las celdas. Una columna monetaria también necesita una sola moneda para que los totales o las medias tengan un significado común; registra cualquier regla de conversión utilizada.

Después de la conversión, examina las fechas y mediciones mínimas y máximas. Comprueba las fechas ambiguas individuales frente a la fuente aunque estén dentro del período permitido: intercambiar el mes y el día puede producir una fecha válida pero incorrecta.

Un tipo de dato indica al programa cómo tratar un valor. Las cantidades numéricas permiten operaciones aritméticas, las fechas permiten ordenarlas y calcular diferencias, el texto contiene etiquetas e identificadores y los campos booleanos contienen TRUE o FALSE. Las importaciones suelen convertir en texto números o fechas previstos como tales.

Diagnostica el tipo antes de cambiarlo

El texto numérico puede alinearse a la izquierda por defecto, mostrar una advertencia de Excel u ordenarse con «10» antes de «9». Comprueba toda la columna con COUNT y COUNTA. En un rango de referencia que contiene el número 120, el texto «80» y el número 45, SUM da 165 y COUNT da 2. El total previsto es 245, por lo que el error de tipo está afectando al cálculo.

Cambiar el formato de presentación a Número no convierte el texto existente. Crea una columna auxiliar, realiza allí la conversión y compara el resultado con la entrada en bruto.

Convierte texto en una hoja de cálculo

=VALUE(A2) interpreta texto numérico según la configuración regional de la hoja de cálculo. =DATEVALUE(A2) interpreta fechas de texto según las convenciones de fecha que reconoce. Confirma primero los separadores y el orden de las fechas.

Para un valor conocido con el día primero, «04.03.2025», construye una fecha a partir de sus partes. Las llamadas explícitas a VALUE hacen que las entradas sean numéricas tanto en Excel como en Google Sheets:

=DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))

RIGHT devuelve el texto del año «2025», MID devuelve el texto del mes «03» y LEFT devuelve el texto del día «04». VALUE convierte cada parte; DATE construye el 4 de marzo de 2025. Valida primero las partes: DATE puede desplazar un día o mes imposible a una fecha posterior en lugar de informar de un error.

Para un valor como « 1250 kg», elimina la etiqueta de unidad conocida y los espacios antes de convertirlo: =VALUE(TRIM(SUBSTITUTE(A2,"kg",""))). No elimines letras indiscriminadamente de todos los campos, porque pueden tener significado o indicar una entrada inválida. =IFERROR(VALUE(A2),"check") mantiene visibles las conversiones fallidas para su revisión.

Conserva los identificadores como texto

El código «00427» identifica algo; no es una cantidad que se deba sumar. Convertirlo en número da 427 y hace perder los ceros iniciales. Los números de teléfono, muchos códigos postales y los identificadores de cuenta también pertenecen a columnas de texto. Configura el tipo antes de importar. Excel conserva solo 15 dígitos significativos en el almacenamiento numérico, por lo que un identificador de 16 dígitos puede perder información antes de que te des cuenta. Aplicarle después formato de texto no puede recuperar los dígitos perdidos.

Convierte con SQL y examina los fallos

En bases de datos como PostgreSQL, CAST(amount_text AS DECIMAL(10,2)) interpreta texto numérico y devuelve un decimal con dos posiciones. CAST(quantity_text AS INTEGER) solicita un número entero. El texto ISO puede convertirse a DATE en muchos dialectos; otras estructuras de fecha necesitan un intérprete adecuado, como TO_DATE de PostgreSQL, STR_TO_DATE de MySQL o PARSE_DATE de BigQuery.

El texto inválido suele detener una consulta CAST. TRY_CAST de SQL Server y SAFE_CAST de BigQuery devuelven NULL cuando fallan las conversiones ordinarias. Cuenta estos fallos por separado de los valores que ya faltaban. Por ejemplo, en SQL Server:

SELECT amount_text FROM sales
WHERE TRY_CAST(amount_text AS DECIMAL(10,2)) IS NULL
  AND amount_text IS NOT NULL;

SQLite puede convertir «12abc» en 12 y «abc» en 0 sin avisar. Por tanto, que una consulta se ejecute correctamente no es prueba suficiente de una conversión correcta. Comprueba las cadenas originales, los valores convertidos, los recuentos y los rangos antes de aceptar la columna limpia.

Un flujo de trabajo en una hoja de cálculo hace visible cada cambio y conserva evidencia suficiente para repetirlo. Usa una hoja de datos en bruto, una hoja de trabajo con columnas auxiliares y una tabla limpia comprobada.

Prepara y ordena el trabajo

Mantén intactos los datos en bruto. Inmoviliza la fila de encabezados y usa una tabla o filtros para seleccionar el rango completo de datos. Al ordenar, mueve juntas las filas completas para que los nombres, identificadores e importes sigan relacionados.

Una secuencia práctica es:

1. Perfila la tabla y escribe una lista de problemas.
2. Corrige los encabezados y divide los campos que contienen varias partes necesarias.
3. Elimina los espacios sobrantes, estandariza las mayúsculas y minúsculas y asigna las variantes de categorías.
4. Convierte los tipos y unifica los formatos de fechas, números y unidades.
5. Trata los valores ausentes y conserva los indicadores cuando sea necesario.
6. Elimina duplicados usando la clave y la regla de conservación acordadas.
7. Valida, concilia y comprueba el resultado de forma puntual.

Este orden tiene en cuenta las dependencias. Una comprobación de duplicados sobre claves en bruto puede pasar por alto espacios adicionales. Una prueba de rango sobre texto numérico puede no comprobar los valores previstos. Registra el efecto después de cada paso para poder rastrear hasta su causa cualquier discrepancia posterior.

Crea y examina columnas auxiliares

Si B2 contiene « ana SILVA», =PROPER(TRIM(B2)) devuelve «Ana Silva». Copia la fórmula por toda la tabla y compara los nombres en bruto y limpios. Revisa las formas de escritura que PROPER no puede deducir correctamente.

Una fórmula auxiliar como =UPPER(TRIM(SUBSTITUTE(B2,CHAR(160)," "))) funciona de dentro hacia fuera: sustituye los espacios de no separación, elimina los espacios normales sobrantes y después cambia las mayúsculas y minúsculas. Usa encabezados auxiliares descriptivos como city_clean para que las fórmulas posteriores apunten a la columna prevista.

Filtra los resultados y comprueba valores al principio, en medio y al final del rango. Una fórmula copiada solo en parte de la tabla deja una columna mezclada. Busca tanto errores como celdas vacías.

Asigna categorías con una búsqueda exacta

Mantén una tabla de correspondencias de dos columnas con la variante y la etiqueta estándar. Con una variante preparada en B2, =VLOOKUP(B2,Map!$A$2:$B$50,2,FALSE) encuentra la etiqueta correspondiente. Las referencias absolutas mantienen fijo el rango de correspondencias al copiar hacia abajo. FALSE solicita una coincidencia exacta; la coincidencia aproximada puede devolver una categoría plausible pero incorrecta para un valor no incluido en la lista.

XLOOKUP puede usar rangos separados para la búsqueda y el resultado. Por defecto usa la coincidencia exacta. Una variante sin correspondencia debe permanecer visible como error para su revisión. Añade o corrige su correspondencia en lugar de convertir todos los fallos de búsqueda en una categoría conocida.

Finaliza y documenta

Después de comprobar las columnas auxiliares, copia sus resultados y usa Pegado especial > Valores en Excel o Pegado especial > Solo valores en Google Sheets para crear la tabla limpia. Comprueba que sea independiente antes de eliminar las columnas de origen: las fórmulas que aún hacen referencia a celdas eliminadas pueden devolver #REF!.

Las herramientas integradas como Quitar duplicados, Buscar y reemplazar y Texto en columnas pueden cambiar muchas celdas a la vez. Registra sus opciones, el rango seleccionado y el recuento afectado mientras la información esté visible. Las fórmulas auxiliares muestran directamente su lógica; conserva esa hoja de trabajo junto con el registro.

Las fórmulas en inglés de estos ejemplos usan comas. El idioma y la configuración regional de la hoja de cálculo pueden cambiar los nombres de las funciones y los separadores; por ejemplo, Excel en neerlandés usa SPATIES.WISSEN para TRIM y las configuraciones regionales con coma decimal suelen usar punto y coma entre argumentos.

Excel permite 1 048 576 filas y 16 384 columnas por hoja; Google Sheets limita las celdas por archivo. El rendimiento puede convertirse en un problema mucho antes de alcanzar un límite fijo. Los datos que se actualizan con frecuencia o que ya están en una base de datos suelen adaptarse mejor a un flujo de trabajo SQL guardado que a ediciones manuales repetidas en una hoja de cálculo.

Una consulta de limpieza SQL puede devolver datos utilizables sin cambiar la tabla en bruto. Cada expresión hace explícita una regla y un alias da nombre al resultado limpio.

Construye el resultado columna por columna

La siguiente consulta es adecuada para PostgreSQL cuando el texto del importe y la fecha ya ha superado las comprobaciones de conversión y la cantidad es numérica:

SELECT DISTINCT
  order_id,
  LOWER(TRIM(email)) AS email,
  COALESCE(NULLIF(UPPER(TRIM(city)), ''), 'UNKNOWN') AS city,
  CAST(amount AS DECIMAL(10,2)) AS amount,
  CAST(order_date AS DATE) AS order_date
FROM raw_orders
WHERE order_id IS NOT NULL
  AND quantity > 0;

Se eliminan los espacios sobrantes del correo y se convierte a minúsculas. Para la ciudad, TRIM elimina los espacios de los extremos, UPPER estandariza las mayúsculas y minúsculas, NULLIF convierte el texto vacío en NULL y COALESCE proporciona «UNKNOWN». Las conversiones devuelven importes numéricos y fechas. WHERE excluye identificadores ausentes y cantidades no positivas o NULL. DISTINCT elimina las filas repetidas del resultado seleccionado.

Perfila el resultado después de añadir cada expresión. Una consulta complicada puede ejecutarse correctamente aunque una asignación o conversión sea incorrecta.

Comprende el orden de filtrado

El orden lógico útil es FROM, WHERE, SELECT, DISTINCT, ORDER BY. WHERE ve las columnas de origen antes de que las expresiones SELECT produzcan sus alias. Un alias no limpia una columna en bruto dentro de la cláusula WHERE de la misma consulta.

Para filtrar una ciudad en bruto de forma consistente, usa WHERE UPPER(TRIM(city)) = 'LISBON', o guarda el resultado limpio y fíltralo en una consulta posterior. Filtrar la ciudad en bruto con city = 'LISBON' puede omitir variantes aunque SELECT limpie la ciudad que muestra.

CASE comprueba las condiciones en orden y devuelve el primer resultado correspondiente:

CASE
  WHEN LOWER(TRIM(status)) IN ('shipped', 'sent') THEN 'shipped'
  WHEN status IS NULL THEN 'unknown'
  ELSE LOWER(TRIM(status))
END AS status

Sin ELSE, un valor que no cumple ninguna condición se convierte en NULL. Mantén visibles los estados sin correspondencia hasta que se resuelva su significado.

Elige entre filtrar y señalar

WHERE elimina una fila del resultado. Un indicador conserva la fila y marca una incidencia, por ejemplo CASE WHEN delivery_date < order_date THEN 1 ELSE 0 END AS date_issue. Comprueba por separado si las fechas obligatorias son NULL.

Cuenta explícitamente las filas excluidas:

SELECT COUNT(*) FROM raw_orders
WHERE order_id IS NULL
   OR quantity <= 0
   OR quantity IS NULL;

Esto tiene en cuenta todos los motivos por los que el filtro anterior rechaza una fila, incluidas las cantidades ausentes. Después de DISTINCT, comprueba la clave de negocio con GROUP BY y HAVING. Las filas que comparten un identificador pero tienen importes distintos sobreviven a DISTINCT y aún necesitan una resolución.

Guarda el método y elige el dialecto adecuado

CREATE VIEW clean_orders AS SELECT ... guarda una consulta que lee los datos actuales de origen cada vez que se usa la vista. CREATE TABLE clean_orders AS SELECT ... crea un resultado almacenado en muchos dialectos; SQL Server suele usar SELECT INTO. Conserva la consulta y los comentarios para poder reconstruir ese resultado cuando cambien las entradas.

Los nombres y el comportamiento de las funciones varían entre sistemas SQL. Comprueba el dialecto para la interpretación de fechas, la longitud de caracteres y la conversión segura. UPDATE y DELETE modifican los registros almacenados: revisa previamente la misma condición WHERE con SELECT y ten una copia de seguridad antes de aplicar cualquiera de las dos instrucciones. Sin WHERE, afectan a todas las filas. Limpiar mediante SELECT es una opción predeterminada útil para analistas con acceso de solo lectura.

Una tabla limpia necesita una explicación de cómo se creó. La documentación permite a otra persona rastrear un valor comunicado, evaluar una decisión y repetir el trabajo con la siguiente entrega.

Escribe una entrada útil en el registro de cambios

Un registro de cambios es una relación fechada de cambios. Cada entrada debe identificar:

• La fecha y el autor.
• El conjunto de datos y la columna o rango afectados.
• El problema y su magnitud original.
• La acción, incluidas la fórmula, la consulta o las opciones de la herramienta.
• El número de filas o valores afectados.
• El motivo y cualquier fuente utilizada para confirmar una corrección.

Por ejemplo: «2025-03-10, A. Silva, orders.city: se eliminaron los espacios sobrantes de 412 valores y se convirtieron a mayúsculas con UPPER(TRIM(city)); 9 formas de escritura se redujeron a 4 ciudades; necesario para las ventas por ciudad».

Esa entrada explica tanto el método como su finalidad. «Se limpiaron las ciudades» no identifica la regla de comparación, el alcance ni el recuento. Registra las entradas mientras trabajas para que el mensaje de la herramienta sobre las filas afectadas y el motivo de una decisión sigan disponibles.

Registra tanto las decisiones como las operaciones

Una fórmula no explica por qué era adecuada. Para los duplicados, indica las columnas clave y qué registro se conservó. Para los importes ausentes, indica si se recuperaron, excluyeron, rellenaron o dejaron ausentes. Si rellenaste valores, registra la regla de estimación y la columna indicadora. Para una corrección, identifica la factura o el registro de origen que proporcionó el valor real.

Registra también las filas señaladas pero conservadas. Quien revise el trabajo necesita distinguir un problema conocido que mantuviste visible de uno que la limpieza pasó por alto. Si un tratamiento cambia después, añade una entrada fechada que explique la nueva decisión.

Conserva el método y su historial

Las notas de celda de una hoja de cálculo pueden explicar una corrección, mientras que una hoja de registro de cambios contiene la secuencia completa. El historial de versiones de Google Sheets puede restaurar estados anteriores del archivo. El historial de versiones de Excel está disponible para archivos almacenados en OneDrive o SharePoint. El historial ayuda a recuperar un archivo, pero un registro separado explica la intención de los cambios.

Los comentarios de SQL pueden explicar una regla junto a su expresión. Usa -- para una línea o /* ... */ para un comentario más largo. Conserva las versiones de las consultas en un sistema de control de versiones o en un almacenamiento con historial de versiones. Una carpeta compartida corriente permite acceder a la consulta actual, pero no conserva las versiones anteriores.

Haz posible repetir el proceso

Un proceso es reproducible cuando la misma entrada en bruto y las mismas reglas producen el mismo resultado limpio. Las consultas guardadas y las fórmulas auxiliares conservadas muestran sus reglas. Para que alguien pueda repetir con precisión una edición manual de celda, necesita el identificador del registro, el valor anterior, el valor nuevo y el motivo.

Conserva juntos la copia en bruto, los pasos de transformación, las tablas de correspondencias y el registro. Si una regla depende del orden de conservación, como conservar el registro de cliente más reciente, guarda también la regla de ordenación o selección. Reproducir un resultado requiere tanto las decisiones como las expresiones del programa.

Comparte el registro y un breve resumen de la limpieza con las personas que usan los datos. Quien interprete los resultados necesita saber que se eliminaron pedidos de prueba o que falta la edad de una parte de los clientes. Comunica los errores recurrentes de origen al titular de los datos para que pueda corregir el formulario, la importación o la exportación que los crea.

La verificación pregunta si la limpieza corrigió los problemas sin crear otros nuevos. También comprueba que los datos restantes sigan respondiendo a la pregunta original. Usa pruebas y comparaciones de columnas completas y después examina registros individuales.

Concilia los recuentos de filas paso a paso

Empieza con el recuento de filas en bruto. Resta las filas eliminadas en cada etapa, contando cada eliminación una sola vez. Para 1250 filas en bruto, 37 copias duplicadas eliminadas y 12 filas inválidas excluidas después:

El recuento limpio esperado es 1201. Un recuento distinto necesita una explicación antes de la entrega. Comprueba el paso en el que el recuento empezó a diferir. Un filtro puede haber ocultado filas durante una operación de la hoja de cálculo, o un rango seleccionado puede haber omitido parte de la tabla.

Si una fila tiene varios problemas, no la restes varias veces. Usa el recuento real de filas antes y después de cada operación. Mantén recuentos separados para las filas eliminadas y las celdas corregidas: cambiar diez celdas de ciudad no elimina diez filas.

Repite el perfilado y las reglas

Ejecuta las comprobaciones originales sobre el resultado limpio. Compara cada resultado con la decisión registrada:

• Los recuentos de ausencias coinciden con los valores que se dejaron ausentes, se recuperaron o se rellenaron.
• Las listas de categorías tienen las formas de escritura estándar previstas.
• Los mínimos y máximos numéricos y de fechas son válidos.
• La clave no tiene duplicados sin resolver.
• Las comprobaciones de tipo, presencia, rango y consistencia no tienen incumplimientos sin explicar.

Un recuento de cero celdas vacías después del relleno no significa que no faltaran valores. Comprueba también los indicadores. Una clave de negocio repetida puede permanecer después de DISTINCT cuando un campo seleccionado difiere, así que comprueba esa clave directamente.

Concilia los importes y examina registros

Cuando no se corrigieron ni rellenaron valores, el total limpio debe ser igual al total en bruto menos los importes de las filas eliminadas. Un total en bruto de 152 400 menos 3100 de pedidos duplicados eliminados debe dejar 149 300. Si se corrigieron o rellenaron valores, incluye esos cambios por separado en la conciliación.

Compara registros de varias partes de la tabla con los datos en bruto y, cuando sea posible, con la fuente original. Céntrate en las filas afectadas por pasos que pueden causar problemas: sustituciones, divisiones, interpretación de texto y conversiones de tipo. Examina los nombres para detectar sustituciones demasiado amplias, las columnas para detectar resultados de división desplazados, los identificadores para detectar ceros perdidos y las fechas para detectar días y meses intercambiados. Una fecha plausible puede seguir siendo incorrecta.

Comprueba la cobertura de la pregunta

Asegúrate de que los datos limpios sigan cubriendo el período, las regiones, las tiendas, los productos y los campos necesarios. Eliminar todos los pedidos de una tienda porque sus fechas usan otra estructura cambia la población analizada. Corrige la regla de interpretación cuando sea posible e informa de cualquier cobertura que siga faltando.

Escribe un informe breve que indique la fuente y el período, los problemas y recuentos, las acciones realizadas, las exclusiones o los rellenos, los resultados de la verificación y las limitaciones restantes. Por ejemplo: «Falta la edad del 12 % de los clientes; los resultados por edad describen al 88 % restante». Un resultado debe interpretarse teniendo en cuenta esa cobertura.

Repite tanto la limpieza como la verificación cuando lleguen nuevos datos en bruto. Las reglas guardadas hacen que el proceso sea repetible, pero las categorías nuevas, los formatos cambiados y los nuevos patrones de valores ausentes siguen necesitando revisión.