Cómo filtrar y segmentar datos de inventario con tablas dinámicas
Este instructivo técnico está diseñado para profesionales de la logística, ingeniería industrial y analistas de datos que buscan transformar grandes volúmenes de datos transaccionales de inventario en información accionable y visualmente clara utilizando la potencia de las Tablas Dinámicas de Excel.
Definición del problema
En entornos de gestión de inventarios (WMS o ERP), se generan miles de registros diarios sobre entradas, salidas, ubicaciones y niveles de stock. El reto operativo principal es la sobrecarga de información. Un gerente de almacén no necesita ver cada transacción individual; necesita respuestas rápidas como: «¿Cuál es el valor total en inventario de la Categoría ‘Electrónica’ en el Almacén Norte durante el último trimestre?» o «¿Qué SKU ha tenido la mayor tasa de rotación en el último mes?».
Si se utilizan filtros manuales en una hoja de datos cruda, el proceso es lento, propenso a errores y no permite cruzar múltiples dimensiones (Producto, Ubicación, Fecha, Proveedor) simultáneamente. La necesidad es de una herramienta dinámica que permita segmentar, resumir y analizar estos datos complejos de manera interactiva.
Explicación técnica
El concepto central aquí es la Tabla Dinámica (Pivot Table). Técnicamente, una Tabla Dinámica no es una función de cálculo como SUMA o PROMEDIO, sino una herramienta de resumen y reorganización de datos. Funciona mediante la agregación de datos brutos (transacciones) en dimensiones significativas (campos).
La lógica se basa en la manipulación de cuatro áreas clave: Filas, Columnas, Valores y Filtros. Al arrastrar un campo a «Filas», Excel agrupa automáticamente los datos por ese valor único (ej. agrupa todas las entradas de «Producto A»). Al arrastrar un campo a «Valores», Excel aplica una función de agregación (SUMA, CONTAR, PROMEDIO) sobre los datos asociados a ese grupo.
Los Segmentadores de Datos (Slicers) son una capa de interactividad adicional. Son filtros visuales que se conectan a la Tabla Dinámica, permitiendo al usuario hacer clic en un elemento (ej. «Almacén Sur») y actualizar instantáneamente todos los resúmenes de la tabla sin modificar la estructura subyacente.
Guía paso a paso
Para ilustrar este proceso, utilizaremos un caso práctico de control de inventario para una distribuidora de componentes electrónicos.
Paso 1: Creación y estructuración de los datos fuente
Primero, debemos tener una base de datos limpia y estructurada. Crearemos un conjunto de datos simulado con transacciones de inventario.
Acción: Cree una hoja de cálculo y ingrese los siguientes datos en el rango A1:F15.
Por qué: La estructura tabular es el requisito fundamental para que Excel pueda interpretar los campos de manera coherente.
Resultado: Una tabla de datos lista para ser analizada.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID Transacción | Fecha | SKU Producto | Categoría | Almacén | Cantidad Movida |
| 2 | T001 | 01/03/2024 | P101 | Capacitor | Norte | +500 |
| 3 | T002 | 03/03/2024 | P205 | Resistor | Sur | -120 |
| 4 | T003 | 05/03/2024 | P101 | Capacitor | Sur | +300 |
| 5 | T004 | 10/03/2024 | P310 | IC | Norte | +80 |
| 6 | T005 | 12/03/2024 | P205 | Resistor | Norte | -250 |
| 7 | T006 | 15/03/2024 | P101 | Capacitor | Norte | +1000 |
| 8 | T007 | 18/03/2024 | P310 | IC | Sur | -40 |
| 9 | T008 | 20/03/2024 | P205 | Resistor | Sur | +50 |
| 10 | T009 | 22/03/2024 | P101 | Capacitor | Sur | -50 |
| 11 | T010 | 25/03/2024 | P310 | IC | Norte | +200 |
| 12 | T011 | 28/03/2024 | P205 | Resistor | Norte | -50 |
| 13 | T012 | 30/03/2024 | P101 | Capacitor | Norte | +200 |
| 14 | T013 | 31/03/2024 | P310 | IC | Sur | +10 |
| 15 | T014 | 01/04/2024 | P205 | Resistor | Norte | -100 |
Paso 2: Creación de la Tabla Dinámica
Acción: Seleccione todo el rango de datos (A1:F15). Vaya a la pestaña Insertar y haga clic en Tabla Dinámica. Confirme el rango y elija colocarla en una «Nueva hoja de cálculo».
Por qué: Esto transforma el rango estático en un objeto dinámico que permite la reorganización de datos sin alterar la fuente original.
Resultado: Se abre una nueva hoja con el panel de campos de la Tabla Dinámica.
Paso 3: Configuración inicial para resumir movimientos
Queremos saber el total de movimientos por Categoría y Almacén.
Acción: Arrastre el campo Categoría al área de Filas. Arrastre el campo Almacén al área de Columnas. Arrastre el campo Cantidad Movida al área de Valores.
Por qué: Esto agrupa los datos por Categoría (filas) y los desglosa por Almacén (columnas), y luego suma las cantidades asociadas (valores).
Resultado: Una tabla que muestra la suma total de movimientos para cada Categoría en cada Almacén.
| Capacitor | IC | Resistor | Total General | |
|---|---|---|---|---|
| Almacén Norte | 1700 | 300 | -400 | 1600 |
| Almacén Sur | 1250 | 110 | -220 | 1140 |
| Total General | 2950 | 410 | -620 | 2740 |
Paso 4: Implementación de Segmentadores de Datos (Slicers)
Para hacer el análisis interactivo, usaremos un Segmentador de Datos para filtrar por Fecha.
Acción: Haga clic en cualquier celda de la Tabla Dinámica. Vaya a la pestaña Analizar Tabla Dinámica (o Opciones) y seleccione Insertar Segmentación de Datos. Seleccione el campo Fecha.
Por qué: Los Segmentadores de Datos proporcionan botones visuales y fáciles de usar para filtrar grandes conjuntos de datos, mucho más intuitivos que usar los filtros de la tabla original.
Resultado: Aparece un panel flotante con botones de fecha (o meses/años, dependiendo de cómo Excel interprete el campo de fecha). Al hacer clic en un mes, la tabla se recalcula instantáneamente.
Paso 5: Filtrado avanzado con campos de valor
Si deseamos ver solo los productos con movimientos positivos (entradas de stock), debemos modificar el campo de valor.
Acción: En el panel de campos, haga clic en la flecha desplegable junto a Suma de Cantidad Movida y seleccione Configuración de Campo de Valor. Cambie la función de Suma a Suma de Cantidad Movida (si ya está en suma) y, más importante, aplique un Filtro de Valor, seleccionando «Mayor que» y estableciendo el valor en 0.
Por qué: Esto aplica un filtro lógico directamente sobre el resultado agregado, permitiendo aislar solo las entradas de inventario (movimientos positivos) sin perder la estructura de la tabla.
Resultado: La tabla se actualiza para mostrar únicamente las sumas de cantidades positivas, ignorando las salidas (-).
Ejercicio propuesto
Una vez que haya dominado la segmentación por Categoría y Almacén, realice el siguiente ejercicio de optimización:
- Objetivo: Identificar la Categoría con el mayor volumen de movimientos negativos (salidas) en el Almacén Norte.
- Acción requerida: Cree una nueva Tabla Dinámica. Coloque Categoría en Filas y Almacén en Columnas. Coloque Cantidad Movida en Valores.
- Aplicación de Filtro: Utilice un Segmentador de Datos para seleccionar únicamente el Almacén Norte.
- Análisis: Aplique un Filtro de Valor a la suma de Cantidad Movida para mostrar solo los valores Menor que 0.
- Conclusión: Determine qué Categoría está generando la mayor pérdida neta de stock en ese almacén.
Errores habituales
La complejidad de las Tablas Dinámicas a menudo lleva a errores conceptuales. Aquí se detallan tres fallos comunes:
- Error: No actualizar la fuente de datos. Si usted modifica los datos originales (la tabla fuente) después de crear la Tabla Dinámica, esta no se actualizará automáticamente. Solución: Siempre haga clic derecho sobre la Tabla Dinámica y seleccione Actualizar.
- Error: Confundir la suma con el conteo. Si arrastra un campo de texto (ej. SKU) al área de Valores, Excel intentará sumarlo, lo cual resulta en un error o un valor incorrecto. Solución: Asegúrese de que el campo en el área de Valores sea numérico y verifique que la función de agregación seleccionada sea Suma, Recuento o Promedio, según lo que necesite.
- Error: Segmentadores de Datos no conectados. Si crea varios Segmentadores de Datos, pero solo uno afecta a la Tabla Dinámica, significa que no están conectados. Solución: Haga clic derecho en el Segmentador de Datos y seleccione Conexiones de informe. Marque todas las Tablas Dinámicas a las que desea que afecte ese filtro.
Deja una respuesta