Promedio de tiempos de ciclo por máquina con PROMEDIO.SI.CONJUNTO
Este instructivo técnico está diseñado para ingenieros de procesos, analistas de producción y profesionales de la optimización industrial que necesitan calcular métricas de rendimiento específicas utilizando las capacidades avanzadas de Excel.
Definición del problema
En el entorno de manufactura moderna, la eficiencia operativa es crítica. Una planta de producción maneja múltiples máquinas (CNC, Tornos, Impresoras 3D, etc.) que procesan diferentes tipos de componentes. El tiempo de ciclo (el tiempo que tarda una máquina en completar una unidad de producto) varía significativamente no solo por la máquina, sino también por el tipo de producto que se está fabricando y, a veces, por el turno de operación.
El reto operativo es obtener un indicador de rendimiento clave (KPI) preciso: ¿Cuál es el tiempo de ciclo promedio para fabricar el «Componente X» específicamente en la «Máquina CNC-01»?
Si se utiliza simplemente la función PROMEDIO() sobre toda la columna de tiempos, se obtendrá un promedio general sesgado, lo cual no permite tomar decisiones informadas sobre la capacidad o el cuello de botella de una máquina específica para un producto determinado. Necesitamos una herramienta que filtre y calcule el promedio simultáneamente basándose en múltiples criterios.
Explicación técnica
Para resolver este problema de agregación condicional múltiple, utilizaremos la función PROMEDIO.SI.CONJUNTO (AVERAGEIFS en inglés). Esta función es la evolución de PROMEDIO.SI, permitiendo que el cálculo del promedio se realice solo sobre aquellas celdas que cumplen con una serie de condiciones lógicas especificadas.
Sintaxis general:
=PROMEDIO.SI.CONJUNTO(rango_promedio; rango_criterio1; criterio1; [rango_criterio2; criterio2]; ...)
rango_promedio: Es el rango que contiene los valores numéricos que deseamos promediar (en nuestro caso, los Tiempos de Ciclo).rango_criterio1: Es el primer rango donde se aplicará la primera condición (ej. la columna de Máquinas).criterio1: Es la condición que debe cumplirse en ese rango (ej. «CNC-01»).[rango_criterio2; criterio2]: Son pares opcionales para añadir más filtros (ej. el tipo de producto).
La lógica matemática subyacente es que la función itera sobre todas las filas de datos. Solo si la fila cumple simultáneamente con el criterio de Máquina Y el criterio de Producto, el valor de la columna de Tiempo de Ciclo de esa fila es incluido en el cálculo del promedio.
Guía paso a paso
A continuación, se detalla el proceso utilizando un caso práctico de optimización de producción.
Paso 1: Creación y estructuración de los datos de producción
Primero, debemos simular la base de datos de registros de producción. Necesitamos al menos cuatro columnas: Máquina, Producto, Turno y Tiempo de Ciclo (en minutos).
Acción: Ingrese los siguientes datos en la hoja de cálculo, comenzando en la celda A1.
Resultado: Se obtiene una tabla de datos crudos listos para el análisis.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Máquina | Producto | Turno | Tiempo Ciclo (min) |
| 2 | CNC-01 | Componente X | Mañana | 12.5 |
| 3 | Torno-05 | Componente Y | Mañana | 8.0 |
| 4 | CNC-01 | Componente Z | Tarde | 15.0 |
| 5 | CNC-01 | Componente X | Tarde | 13.0 |
| 6 | Torno-05 | Componente X | Tarde | 9.5 |
| 7 | CNC-01 | Componente X | Mañana | 12.0 |
| 8 | CNC-01 | Componente Y | Mañana | 14.0 |
Paso 2: Definición de los criterios de búsqueda
Para el análisis, queremos el promedio del Componente X en la máquina CNC-01.
Acción: En una celda separada (ej. F2), escriba el criterio de Máquina: CNC-01. En la celda F3, escriba el criterio de Producto: Componente X.
Resultado: Se tienen variables claras para alimentar la fórmula, lo que facilita la auditoría y el cambio de parámetros.
Paso 3: Aplicación de la función PROMEDIO.SI.CONJUNTO
Ahora aplicamos la función, especificando el rango a promediar, seguido de los rangos y criterios.
Acción: En la celda G2 (donde se mostrará el resultado), escriba la siguiente fórmula:
Desglose de la fórmula:
D2:D8: Es el rango_promedio (los tiempos de ciclo).A2:A8: Es el rango_criterio1 (las máquinas).F2: Es el criterio1 («CNC-01»).B2:B8: Es el rango_criterio2 (los productos).F3: Es el criterio2 («Componente X»).
Resultado Esperado: La función buscará todas las filas donde la columna A sea «CNC-01» Y la columna B sea «Componente X». Los tiempos correspondientes son 12.5, 13.0 y 12.0. El promedio es (12.5 + 13.0 + 12.0) / 3 = 12.5 minutos.
| F | G | |
|---|---|---|
| 2 | CNC-01 | 12.5 |
| 3 | Componente X | |
| 4 | Resultado Promedio: | 12.5 |
Ejercicio propuesto
Para consolidar el aprendizaje, realice la siguiente extensión al caso práctico:
Objetivo: Calcule el tiempo de ciclo promedio para el «Componente Y», pero solo considerando los registros realizados durante el «Turno de Mañana», independientemente de la máquina que lo haya procesado.
Instrucciones:
- Asigne un nuevo criterio en la celda F4:
Componente Y. - Asigne un nuevo criterio en la celda F5:
Mañana. - Modifique la fórmula en G2 para incluir un tercer par de criterios (Rango Criterio 3: C2:C8; Criterio 3: F5).
Pista: Recuerde que el rango de tiempo de ciclo (D2:D8) debe permanecer como el primer argumento de la función.
Errores habituales
Al implementar PROMEDIO.SI.CONJUNTO, los usuarios suelen cometer errores conceptuales o sintácticos. Aquí se detallan los tres más comunes:
1. Invertir el orden de los rangos y criterios
Error: Intentar poner el rango de tiempo de ciclo (el que se promedia) como un criterio. Por ejemplo: =PROMEDIO.SI.CONJUNTO(A2:A8; D2:D8; "CNC-01").
Solución: Siempre el primer argumento debe ser el rango de valores numéricos que deseas promediar. Los rangos de criterios deben seguirlo.
2. Olvidar la correspondencia de rangos
Error: Usar rangos de diferente tamaño. Por ejemplo, si los tiempos de ciclo están en D2:D8 (7 filas), pero se usa el rango de máquinas A2:A9 (8 filas) como criterio.
Solución: Todos los rangos de criterios (A2:A8, B2:B8, C2:C8) y el rango de promedio (D2:D8) deben tener exactamente el mismo número de filas.
3. Confundir operadores lógicos (AND vs OR)
Error: Pensar que PROMEDIO.SI.CONJUNTO funciona como un OR (O). Si usted quiere promediar el tiempo para «Componente X» O «Componente Y», esta función no es la adecuada.
Solución: PROMEDIO.SI.CONJUNTO opera bajo lógica AND (Y). Si necesita una lógica OR, deberá usar una combinación de funciones como SUMAR.SI.CONJUNTO y dividir el resultado por el conteo total de registros que cumplen al menos una de las condiciones, o recurrir a una Tabla Dinámica.
Deja una respuesta