En este ejercicio práctico se analiza si existe una relación lineal entre la inversión mensual en mantenimiento preventivo y el tiempo de parada de una línea de producción. El objetivo es calcular el coeficiente de correlación de Pearson con COEF.DE.CORREL, interpretar su signo e intensidad y utilizar el resultado como apoyo para decisiones de mantenimiento.
1. Funciones de Excel que se utilizarán
La función central compara dos conjuntos numéricos del mismo tamaño: la inversión en mantenimiento y las horas de parada observadas en los mismos meses. El resultado siempre se encuentra entre -1 y 1.
| Función | Sintaxis | ¿Para qué sirve? | |
|---|---|---|---|
| 1 | COEF.DE.CORREL | =COEF.DE.CORREL(matriz1; matriz2) | Calcula el coeficiente de correlación de Pearson entre dos rangos numéricos. |
| 2 | PROMEDIO | =PROMEDIO(rango) | Obtiene el valor medio de un rango. Ayuda a comparar cada observación con su media. |
| 3 | SI | =SI(prueba_lógica; valor_si_verdadero; valor_si_falso) | Clasifica el resultado según una condición, por ejemplo, correlación fuerte o moderada. |
| 4 | SI.ERROR | =SI.ERROR(valor; valor_si_error) | Evita mostrar errores si faltan datos o si los rangos no contienen suficientes valores válidos. |
2. Tabla de datos de ejemplo
Registra diez meses de operación en el rango A1:D11. La columna A contiene el periodo, B la inversión mensual en mantenimiento preventivo, C las horas de parada y D una observación operativa.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Mes | Inversión mantenimiento (€) | Tiempo de parada (h) | Observación |
| 2 | Enero | 1,200 | 18 | Preventivo programado |
| 3 | Febrero | 900 | 24 | Recambio tardío |
| 4 | Marzo | 1,500 | 14 | Inspección ampliada |
| 5 | Abril | 700 | 31 | Falla no planificada |
| 6 | Mayo | 1,800 | 10 | Lubricación completa |
| 7 | Junio | 1,100 | 20 | Parada menor |
| 8 | Julio | 2,000 | 8 | Plan preventivo reforzado |
| 9 | Agosto | 650 | 35 | Reparación reactiva |
| 10 | Septiembre | 1,400 | 15 | Componentes críticos revisados |
| 11 | Octubre | 1,000 | 22 | Disponibilidad irregular |
Tabla 1. Datos simulados para practicar el análisis de mantenimiento. La inversión está expresada en euros y el tiempo de parada en horas.
3. Desarrollo paso a paso
Coloca los datos exactamente en A1:D11. Antes de calcular, comprueba que B2:B11 y C2:C11 tengan diez valores numéricos, que no existan textos dentro de los rangos y que cada inversión corresponda al mismo mes que sus horas de parada.
=CONTAR(B2:B11)=CONTAR(C2:C11)En una tabla auxiliar, por ejemplo en F2:G4, los controles quedan así:
| F | G | |
|---|---|---|
| 2 | Valores de inversión | 10 |
| 3 | Valores de parada | 10 |
| 4 | Estado de validación | Válido |
En F6:G7 calcula la inversión media y el tiempo medio de parada. Estos valores permiten interpretar el comportamiento general antes de analizar la correlación.
=PROMEDIO(B2:B11)=PROMEDIO(C2:C11)| F | G | |
|---|---|---|
| 6 | Inversión promedio | 1,225 € |
| 7 | Parada promedio | 19.7 h |
En F9 escribe la fórmula. Es importante que ambos rangos tengan la misma cantidad de filas y estén alineados por periodo.
=COEF.DE.CORREL(B2:B11;C2:C11)| F | G | |
|---|---|---|
| 9 | Coeficiente de Pearson | -0.9624 |
| 10 | Interpretación inicial | Relación negativa fuerte |
El resultado aproximado es -0.9624. En estos datos, los meses con mayor inversión tienden a presentar menos horas de parada. La conclusión es una asociación lineal fuerte y negativa, no una prueba definitiva de que toda reducción de parada sea causada exclusivamente por la inversión.
Para convertir el indicador en una lectura operativa, coloca en F12 una etiqueta con SI. Se considera fuerte una correlación cuyo valor absoluto sea al menos 0.80.
=SI(ABS(G9)>=0.8;"Relación fuerte";"Relación débil o moderada")| F | G | |
|---|---|---|
| 12 | Clasificación | Relación fuerte |
Aplica formato condicional al resultado de G9: fondo verde cuando el valor sea menor o igual que -0.80, fondo amarillo entre -0.79 y 0.79 y fondo rojo cuando sea mayor o igual que 0.80. Así, la hoja comunica visualmente la señal del indicador.
La hoja pasa de datos sin procesar a un pequeño panel de diagnóstico. Esta tabla intermedia resume cómo quedan reunidos los valores clave:
| F | G | H | |
|---|---|---|---|
| 14 | Indicador | Resultado | Lectura |
| 15 | Inversión media | 1,225 € | Referencia mensual |
| 16 | Parada media | 19.7 h | Referencia mensual |
| 17 | Correlación Pearson | -0.9624 | Negativa fuerte |
| 18 | Decisión de análisis | Investigar el plan preventivo | Validar con más meses |
4. Variantes del ejercicio
Variante 1: umbral dinámico de inversión
En lugar de utilizar todos los meses, compara únicamente los periodos cuya inversión supere el promedio. En H2 calcula un umbral dinámico:
=PROMEDIO(B2:B11)Después, puedes filtrar la tabla por inversión mayor o igual a H2 y calcular la correlación con los registros visibles. En versiones modernas de Excel, una alternativa es construir rangos filtrados:
=COEF.DE.CORREL(FILTRAR(B2:B11;B2:B11>=$H$2);FILTRAR(C2:C11;B2:B11>=$H$2))Esta variante responde a una pregunta distinta: ¿la relación se mantiene en los meses con un nivel de inversión alto?
Variante 2: manejo de errores con SI.ERROR
Si los rangos están vacíos, tienen menos de dos observaciones válidas o contienen una combinación no numérica, Excel puede devolver un error. En G9 puedes proteger la fórmula así:
=SI.ERROR(COEF.DE.CORREL(B2:B11;C2:C11);"Revisar datos")| F | G | |
|---|---|---|
| 20 | Resultado protegido | -0.9624 |
| 21 | Si faltan datos | Revisar datos |
Variante 3: interpretación por niveles
Para evitar una etiqueta binaria, crea tres niveles de interpretación. La siguiente fórmula considera fuerte una relación negativa menor o igual a -0.80, moderada entre -0.79 y -0.50 y débil por encima de ese valor:
=SI(G9<=-0.8;"Fuerte negativa";SI(G9<=-0.5;"Moderada negativa";"Débil o positiva"))5. Ejercicio propuesto para el estudiante
Una planta desea analizar la relación entre el porcentaje de cumplimiento del mantenimiento preventivo y las horas de parada no planificada durante doce semanas. Construye una tabla en A1:C13 con las columnas Semana, Cumplimiento preventivo (%) y Parada no planificada (h).
- Calcula el promedio de ambas variables con
PROMEDIO. - Obtén el coeficiente de Pearson con
COEF.DE.CORREL. - Protege el resultado usando
SI.ERROR. - Clasifica el resultado como relación fuerte, moderada o débil mediante
SI. - Aplica formato condicional y redacta una conclusión de tres líneas aclarando que correlación no implica causalidad.
| A | B | C | |
|---|---|---|---|
| 1 | Semana | Cumplimiento preventivo (%) | Parada no planificada (h) |
| 2 | 1 | [dato] | [dato] |
| 3 | 2 | [dato] | [dato] |
| 4 | 3 | [dato] | [dato] |
| 5 | 4 | [dato] | [dato] |
| 6 | 5 | [dato] | [dato] |
| 7 | 6 | [dato] | [dato] |
| 8 | 7 | [dato] | [dato] |
| 9 | 8 | [dato] | [dato] |
| 10 | 9 | [dato] | [dato] |
| 11 | 10 | [dato] | [dato] |
| 12 | 11 | [dato] | [dato] |
| 13 | 12 | [dato] | [dato] |
Deja una respuesta