Análisis de mermas por producto con SUMAR.SI y porcentajes
Este instructivo técnico está diseñado para profesionales de la ingeniería industrial, control de calidad y analistas de datos que necesitan cuantificar y evaluar la eficiencia operativa mediante el análisis de mermas en procesos productivos.
Definición del problema
En entornos de manufactura o producción de alimentos, la merma (pérdida de material durante el proceso) es un indicador crítico de ineficiencia, desperdicio de recursos y potencial impacto financiero negativo. El reto operativo consiste en pasar de un registro crudo de pérdidas a un informe analítico que permita identificar qué productos específicos están generando las mayores mermas y cuál es el porcentaje de pérdida asociado a cada uno.
Si solo se suman las mermas totales, se pierde la capacidad de tomar decisiones focalizadas. Necesitamos una herramienta que filtre y agregue datos basándose en la categoría (el producto) y luego calcule la proporción de esa pérdida respecto a la cantidad total procesada.
Explicación técnica
Para resolver este problema de agregación condicional, utilizaremos la función SUMAR.SI (SUMIF en inglés). Esta función permite sumar valores en un rango que cumplen con un criterio específico definido en otro rango. En nuestro caso, el criterio será el nombre del producto.
La lógica matemática se desglosa en dos partes:
- Cálculo de la Merma Total por Producto: Se usa
SUMAR.SIpara sumar la columna de «Cantidad Perdida» solo cuando la columna «Producto» coincide con el nombre del producto en la fila de resumen. - Cálculo del Porcentaje de Merma: Se divide la Merma Total por Producto (calculada en el paso 1) entre la Cantidad Total Producida de ese mismo producto. El resultado se formatea como porcentaje.
La estructura general de la fórmula será: =SUMAR.SI(Rango_Criterio; Criterio; Rango_Suma).
Guía paso a paso
Paso 1: Creación del Dataset de Mermas (Datos Crudos)
Primero, debemos simular la base de datos que registra cada evento de merma. Crearemos una tabla con al menos cuatro columnas: ID de Lote, Producto, Cantidad Producida (Base), y Cantidad Perdida (Merma).
Acción: Ingresa los siguientes datos en tu hoja de cálculo, comenzando en la celda A1.
Resultado: Tendremos una tabla de registro detallada de las operaciones.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID Lote | Producto | Cantidad Producida | Cantidad Perdida |
| 2 | L001 | Tornillo M8 | 1000 | 25 |
| 3 | L002 | Tuerca Hexagonal | 500 | 10 |
| 4 | L003 | Tornillo M8 | 1200 | 40 |
| 5 | L004 | Arandela Plana | 2000 | 50 |
| 6 | L005 | Tuerca Hexagonal | 800 | 15 |
| 7 | L006 | Tornillo M8 | 900 | 30 |
| 8 | L007 | Arandela Plana | 1500 | 20 |
Paso 2: Definición de la Tabla de Resumen
Necesitamos una tabla separada donde listaremos los productos únicos y calcularemos sus métricas. Esta tabla debe contener al menos dos columnas: Producto y Merma Total.
Acción: En una nueva área de la hoja (ej. comenzando en F1), lista todos los productos únicos encontrados en la columna B (F2: Tornillo M8, F3: Tuerca Hexagonal, F4: Arandela Plana).
Resultado: Una lista limpia de los ítems que vamos a analizar.
| F | G | H | |
|---|---|---|---|
| 1 | Producto | Merma Total (SUMAR.SI) | % Merma |
| 2 | Tornillo M8 | ||
| 3 | Tuerca Hexagonal | ||
| 4 | Arandela Plana |
Paso 3: Cálculo de la Merma Total por Producto con SUMAR.SI
Aquí aplicamos la función clave. Queremos sumar todos los valores de la columna D (Cantidad Perdida) siempre y cuando la columna B (Producto) coincida con el nombre en F2.
Acción: En la celda G2, escribe la siguiente fórmula:
=SUMAR.SI($B:$B; F2; $D:$D)
Explicación:
$B:$B: Es el rango donde Excel buscará el criterio (toda la columna de Productos).F2: Es el criterio específico que estamos buscando («Tornillo M8»).$D:$D: Es el rango que se sumará si el criterio es verdadero (toda la columna de Cantidad Perdida).
Resultado: La celda G2 mostrará la suma total de mermas para «Tornillo M8» (25 + 40 + 30 = 95).
Importante: Arrastra esta fórmula hacia abajo (G3 y G4) para aplicarla a los otros productos.
Paso 4: Cálculo del Porcentaje de Merma
El porcentaje de merma se calcula como: (Merma Total / Cantidad Total Producida) * 100. Necesitamos calcular la cantidad total producida para cada producto, lo cual requiere otra aplicación de SUMAR.SI.
Acción 4a (Calcular Producción Total): En una columna auxiliar (ej. I2), calcula la producción total para el producto en F2:
=SUMAR.SI($B:$B; F2; $C:$C)
Acción 4b (Calcular Porcentaje): En la celda H2, divide la Merma Total (G2) entre la Producción Total (I2) y formatea la celda como Porcentaje (%).
=G2/I2
Resultado: La celda H2 mostrará el porcentaje de merma para «Tornillo M8» (95 / 3100 ≈ 3.06%).
| F | G (Merma Total) | I (Producción Total) | H (% Merma) | |
|---|---|---|---|---|
| 1 | Producto | SUMAR.SI | SUMAR.SI | =G2/I2 |
| 2 | Tornillo M8 | 95 | 3100 | 3.06% |
| 3 | Tuerca Hexagonal | 25 | 1300 | 1.92% |
| 4 | Arandela Plana | 70 | 3500 | 2.00% |
Ejercicio propuesto
Una vez dominado el análisis básico, el siguiente nivel es la optimización. Supongamos que la gerencia establece un límite máximo de merma del 2.5% para cualquier producto. Utiliza la tabla de resumen creada en el Paso 4 y añade una columna final (J) llamada «Cumple Meta».
Tarea: Implementa una fórmula condicional (SI) en la celda J2 que devuelva «OK» si el porcentaje de merma en H2 es menor o igual a 2.5%, y «REVISAR» si es mayor. Arrastra esta fórmula para evaluar todos los productos.
Esto transforma el análisis descriptivo (¿cuánto perdemos?) en un análisis prescriptivo (¿estamos cumpliendo con los estándares?).
Errores habituales
Al trabajar con funciones de agregación condicional, los usuarios suelen cometer errores sutiles que invalidan los resultados. Aquí se detallan tres de los más comunes:
- Error de Referencia Absoluta en Criterios: Usar referencias relativas (ej.
B2en lugar de$B:$B) en el rango de búsqueda. Si arrastras la fórmula, el rango de búsqueda se moverá, causando que Excel busque el criterio en la columna equivocada. Solución: Siempre utiliza referencias de columna completas ($B:$B) o fija la referencia de la columna con el símbolo de dólar ($B2) si usas rangos limitados. - Error de Formato de Datos: Mezclar texto y números en las columnas de cantidad. Si la columna «Cantidad Perdida» contiene un espacio invisible o un formato de texto,
SUMAR.SIlo ignorará, resultando en un total de cero o incorrecto. Solución: Selecciona la columna de cantidades y aplica el formato «Número» o «General». Usa la funciónVALOR()si sospechas de datos mixtos. - Error de Comparación de Criterios: Olvidar los operadores de comparación al buscar criterios complejos. Si intentas buscar «Mayor que 100», no basta con escribir
100. Solución: Debes encerrar el operador y el valor entre comillas:">100".
Deja una respuesta