Macros para combinar archivos de diferentes turnos en un solo reporte
Este instructivo técnico está diseñado para ingenieros de procesos, analistas de datos y personal de operaciones que manejan grandes volúmenes de datos generados en turnos de trabajo distintos (mañana, tarde, noche) y necesitan consolidarlos eficientemente en un único informe maestro.
Definición del problema
En el sector de la manufactura avanzada, la recopilación de datos de producción es crítica para el control de calidad y la optimización de la cadena de suministro. Imaginemos una planta de ensamblaje de componentes electrónicos. Cada turno (Mañana, Tarde, Noche) genera su propio archivo de registro de producción (ej. Produccion_Mañana_01052024.xlsx, Produccion_Tarde_01052024.xlsx, etc.). Estos archivos contienen métricas vitales como ID de Lote, Producto, Cantidad Producida, Tiempo de Ciclo (segundos) y Operador.
El reto operativo es que, al finalizar el día, el supervisor necesita un único reporte consolidado que muestre el rendimiento total del día, sin tener que abrir, copiar y pegar manualmente los datos de tres o más archivos diferentes. La repetición manual es propensa a errores, consume tiempo valioso y retrasa la toma de decisiones gerenciales.
Explicación técnica
La solución más robusta y escalable para este problema es el uso de una Macro de VBA (Visual Basic for Applications) en Excel. Aunque se podría resolver con funciones avanzadas como Power Query (Get & Transform Data), el uso de VBA permite automatizar el proceso de iteración sobre múltiples archivos en una carpeta específica, lo cual es ideal para la automatización de tareas repetitivas en entornos industriales.
La lógica técnica se basa en:
- Iteración de Archivos: El código VBA recorrerá todos los archivos `.xlsx` dentro de una carpeta designada.
- Apertura y Lectura: Para cada archivo encontrado, la macro lo abrirá, leerá los datos de la hoja de trabajo específica y los copiará.
- Concatenación: Los datos leídos se pegarán secuencialmente en una hoja de destino maestra.
- Cierre y Limpieza: El archivo fuente se cerrará sin guardar cambios, asegurando que solo se consoliden los datos.
Conceptos clave: Dir() para listar archivos, Workbooks.Open() para acceder a ellos, y Range.Copy para la transferencia de datos.
Guía paso a paso
Paso 1: Preparación del Entorno y Datos de Ejemplo
Antes de escribir la macro, debemos simular los archivos de entrada. Cree tres archivos de Excel separados (Turno Mañana, Tarde, Noche) y guárdelos en una carpeta única llamada «Datos_Turnos». Cada archivo debe tener la misma estructura de columnas.
Acción: Cree los tres archivos y asegúrese de que la estructura de datos sea idéntica.
Resultado: Tres archivos de datos listos para ser procesados por la macro.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID Lote | Producto | Cantidad | Tiempo Ciclo (s) |
| 2 | L001 | Chip X1 | 150 | 12.5 |
| 3 | L002 | Chip Y2 | 210 | 15.0 |
Paso 2: Creación de la Hoja Maestra y Activación del Editor VBA
Abra un nuevo libro de Excel (este será el reporte final). Cree una hoja llamada «Reporte_Consolidado». Luego, acceda al Editor VBA.
Acción: Presione Alt + F11. En el menú, vaya a Insertar > Módulo. Pegue el código VBA proporcionado (ver anexo conceptual).
Resultado: Un módulo de código listo para contener la lógica de la macro.
Paso 3: Implementación de la Macro de Consolidación
El código VBA debe estar configurado para apuntar a la carpeta donde se encuentran los archivos de turno. La macro recorrerá la carpeta, abrirá cada archivo, copiará los datos (saltándose el encabezado) y los pegará debajo de la última fila en la hoja «Reporte_Consolidado».
Acción: Ejecute la macro (ej. presionando F5 mientras está en el subprocedimiento, o asignándola a un botón en la hoja de Excel).
Resultado: La macro procesa los tres archivos, y la hoja «Reporte_Consolidado» contiene todos los registros de los tres turnos apilados uno tras otro.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID Lote | Producto | Cantidad | Tiempo Ciclo (s) |
| 2 | L001 | Chip X1 | 150 | 12.5 |
| 3 | L002 | Chip Y2 | 210 | 15.0 |
| 4 | L003 | Chip X1 | 180 | 13.0 |
| 5 | L004 | Chip Y2 | 250 | 14.5 |
Paso 4: Análisis Posterior (Opcional pero Recomendado)
Una vez que los datos están consolidados, el verdadero valor analítico se obtiene con una Tabla Dinámica. Esto permite resumir el rendimiento total por producto o por operador, sin necesidad de modificar la macro.
Acción: Seleccione todo el rango de datos en «Reporte_Consolidado» (A1 hasta la última fila). Vaya a Insertar > Tabla Dinámica. Arrastre ‘Producto’ a Filas y ‘Cantidad’ a Valores (configurado como SUMA).
Resultado: Un resumen ejecutivo instantáneo del rendimiento total del día.
Ejercicio propuesto
Escenario de Extensión: Su planta ahora opera en tres turnos, pero además, cada archivo de turno contiene una columna adicional: «ID_Turno» (Mañana, Tarde, Noche). Modifique la macro VBA para que, además de concatenar los datos, añada automáticamente el valor de «ID_Turno» a la columna A del reporte consolidado, y coloque el resto de los datos en las columnas B, C, D y E. Luego, utilice la Tabla Dinámica para calcular la media del Tiempo de Ciclo por Producto, segmentada por Turno.
Errores habituales
La automatización, aunque poderosa, es sensible a la configuración. Aquí se detallan tres fallos comunes:
- Error: «Subscript out of range» o Archivo no encontrado.
Causa: La ruta de la carpeta en el código VBA es incorrecta, o el archivo de Excel no está guardado en la ubicación esperada. Los nombres de archivo deben coincidir exactamente (mayúsculas/minúsculas si el sistema operativo es sensible).
Solución: Verifique dos veces la ruta completa (ej.
C:ReportesDatos_Turnos) y asegúrese de que la macro se ejecute desde el mismo libro que contiene la carpeta de datos. - Error: Duplicación de Encabezados.
Causa: La macro está copiando la fila de encabezados (Fila 1) de cada archivo de turno y pegándola en el reporte consolidado. Esto resulta en múltiples filas de títulos.
Solución: En el código VBA, asegúrese de que el bucle de copia comience en la fila 2 del archivo fuente (
SourceWorksheet.Rows("2:" & LastRow).Copy) y no en la fila 1. - Error: Problemas de Formato de Datos.
Causa: Si un archivo tiene la cantidad como texto («150 unidades») en lugar de número (150), la macro lo pegará como texto. Las funciones de resumen (SUMA, PROMEDIO) fallarán o darán resultados erróneos.
Solución: Antes de ejecutar la macro, revise manualmente los archivos fuente. Si el problema persiste, implemente una función de limpieza de datos (ej.
CDbl()oVal()) dentro del bucle de la macro para forzar la conversión a tipo numérico.
Deja una respuesta