Z-Score en Excel: Ejercicio Práctico de Control de Calidad

Z-Score en Excel · Control de Calidad | Ingeniería Industrial

X ZScore_Calidad.xlsx — Excel

ArchivoInicioInsertar Diseño de páginaFórmulasDatosRevisar

A1 fx

Z-Score en Excel: caza las mediciones fuera de especificación

⚙ Área de aplicación: Ingeniería Industrial · Control estadístico de la calidad (SPC)

⏱ 20–25 min📊 Nivel: intermedio 🖥 Excel 365 / 2021 / 2019🔧 Caso: tornería CNC

El caso: ejes que no admiten sorpresas

Situación de planta

Trabajas en el departamento de calidad de una planta de mecanizado. Un torno CNC fabrica ejes con diámetro nominal 25,00 mm y, en cada inspección, se mide una muestra de piezas por lote.

El problema: entre mediciones normales se cuelan valores atípicos —piezas deformadas, desgaste de herramienta, errores del instrumento— que distorsionan cualquier análisis si no se detectan y apartan a tiempo.

La solución clásica de la estadística industrial es el Z-Score: estandariza cada medición para expresarla en una escala con media 0 y desviación estándar 1. Así, cualquier dato queda descrito por una única pregunta: ¿a cuántas desviaciones de la media está?. Si la respuesta supera el umbral acordado (|Z| > 2), la medición se marca y se filtra.

Muestra0piezas medidas

Media μ0mm (se calcula en el Paso 2)

Desviación σ̂0mm (se calcula en el Paso 2)

Regla de decisión|Z| > 0≈ 5 % de probabilidad bajo normalidad

Funciones que vamos a usar

Ocho funciones bastan. Cada ficha incluye su sintaxis general; las fórmulas concretas del caso, ya con rangos reales, aparecen en el desarrollo con su botón de copiado.

=PROMEDIO()ESTADÍSTICA

Devuelve la media aritmética del rango. Es nuestro valor de referencia μ, el «centro» del proceso.

=PROMEDIO(rango)

=DESVEST.M()ESTADÍSTICA

Desviación estándar muestral (divide entre n−1). Mide la dispersión del proceso. Usa DESVEST.P solo si tienes la población completa.

=DESVEST.M(rango)

=ABS()MATEMÁTICAS

Valor absoluto: convierte −2,45 en 2,45. Imprescindible para comparar |Z| contra el umbral sin importar el signo.

=ABS(número)

=REDONDEAR()MATEMÁTICAS

Redondea al número de decimales indicado. Presenta los Z-Score legibles (2 decimales) sin alterar el cálculo base.

=REDONDEAR(número; núm_decimales)

=SI()LÓGICAS

Evalúa una condición y devuelve un valor u otro. Aquí clasifica cada pieza: ATÍPICA u OK.

=SI(prueba_lógica; valor_si_verdadero; valor_si_falso)

=CONTAR.SI()ESTADÍSTICA

Cuenta celdas que cumplen un criterio. Con ella montamos el KPI: cuántas piezas quedaron fuera.

=CONTAR.SI(rango; criterio)

=CONTAR.SI.CONJUNTO()ESTADÍSTICA

Cuenta con varios criterios a la vez. La usamos en la Variante A: atípicos… que además sean de un turno o lote concretos.

=CONTAR.SI.CONJUNTO(rango1; criterio1; rango2; criterio2)

=SI.ERROR()ERROR

Atrapa cualquier error (#¡VALOR!, #¡DIV/0!…) y lo sustituye por el valor que decidas. En la Variante B evita que un dato sucio rompa la hoja.

=SI.ERROR(fórmula; valor_si_error)

⚠ Nota sobre el separador: las fórmulas de este artículo usan punto y coma (;). Si tu Excel está configurado con coma (,), sustitúyelas al pegar. El botón copia siempre la versión con ;.

Los datos de inspección

Muestreo de 12 piezas de un eje Ø25,00 mm, repartidas en 4 lotes y 2 turnos. Copia esta tabla en tu hoja: encabezados en la fila 1, datos en A2:D13.

	A	B	C	D
1	ID	Diámetro (mm)	Lote	Turno
2	M-01	24,98	L-21	Mañana
3	M-02	25,03	L-21	Mañana
4	M-03	25,01	L-21	Mañana
5	M-04	24,95	L-22	Mañana
6	M-05	25,07	L-22	Mañana
7	M-06	25,00	L-22	Mañana
8	M-07	24,83	L-23	Tarde
9	M-08	25,02	L-23	Tarde
10	M-09	25,11	L-23	Tarde
11	M-10	24,99	L-24	Tarde
12	M-11	25,04	L-24	Tarde
13	M-12	24,97	L-24	Tarde

Hoja1 ▸ datos_crudos

👁 A simple vista, M-07 (24,83) y M-09 (25,11) «cantan». Pero en un proceso real con 300 mediciones no puedes fiarte del ojo: necesitamos un criterio numérico y repetible.

Desarrollo paso a paso

Bloque de parámetros: μ, σ̂ y umbral

Reserva las celdas H1:I4 como «panel de mandos» del análisis. Concentrar la media, la desviación y el umbral en un solo sitio tiene una ventaja enorme: si mañana cambias el umbral, toda la hoja se recalcula sola.

I2=PROMEDIO(B2:B13)

I3=DESVEST.M(B2:B13)

I42

	H	I
1	PARÁMETRO	VALOR
2	Media μ (mm)	25,000
3	Desv. típica σ̂ (mm)	0,0693
4	Umbral \|Z\|	2

Hoja1 ▸ parámetros H1:I4

La media cae clavada en el nominal (25,000 mm): el proceso está centrado. La dispersión muestral es de 0,0693 mm. El umbral 2 se escribe a mano: es una decisión de ingeniería, no un cálculo.

Z-Score pieza a pieza (columna E)

La fórmula de estandarización es Z = (x − μ) / σ. En E2 la escribimos con referencias absolutas ($I$2, $I$3) para que, al arrastrar, todas las piezas apunten al mismo panel de parámetros:

E2=REDONDEAR((B2-$I$2)/$I$3;2)

↘ Arrastra E2 con el controlador de relleno hasta E13 (o doble clic en la esquina)

	A	B	C	D	E
1	ID	Diámetro (mm)	Lote	Turno	Z-Score
2	M-01	24,98	L-21	Mañana	=REDONDEAR((B2-$I$2)/$I$3;2)
3	M-02	25,03	L-21	Mañana	0,43
4	M-03	25,01	L-21	Mañana	0,14
5	M-04	24,95	L-22	Mañana	−0,72
6	M-05	25,07	L-22	Mañana	1,01
7	M-06	25,00	L-22	Mañana	0,00
8	M-07	24,83	L-23	Tarde	−2,45
9	M-08	25,02	L-23	Tarde	0,29
10	M-09	25,11	L-23	Tarde	1,59
11	M-10	24,99	L-24	Tarde	−0,14
12	M-11	25,04	L-24	Tarde	0,58
13	M-12	24,97	L-24	Tarde	−0,43

Hoja1 ▸ columna Z añadida

La hoja ya habla otro idioma: M-07 está a 2,45 desviaciones por debajo de la media; M-09 se queda en 1,59 por encima, cerca del límite pero dentro. El resto vive entre −0,72 y +1,01: comportamiento perfectamente normal.

Filtrar con SI: columna Estado (F)

Convertimos el número en decisión. En F2, comparamos el valor absoluto del Z contra el umbral del panel:

F2=SI(ABS(E2)>$I$4;"ATÍPICO";"OK")

↘ Arrastra F2 hasta F13

	A	B	F
1	ID	Diámetro (mm)	Estado
2	M-01	24,98	=SI(ABS(E2)>$I$4;»ATÍPICO»;»OK»)
3	M-02	25,03	OK
4	M-03	25,01	OK
5	M-04	24,95	OK
6	M-05	25,07	OK
7	M-06	25,00	OK
8	M-07	24,83	ATÍPICO
9	M-08	25,02	OK
10	M-09	25,11	OK
11	M-10	24,99	OK
12	M-11	25,04	OK
13	M-12	24,97	OK

Hoja1 ▸ filtro aplicado

Con el umbral en 2, solo M-07 queda fuera. Puedes filtrar la columna F directamente con el autofiltro (Inicio ▸ Filtro) y quedarte solo con las filas ATÍPICO.

KPIs: el resumen que pide producción

En el bloque H6:I8 contamos resultados con CONTAR.SI. Estos tres números son los que llevan al informe de turno:

I6=CONTAR.SI(F2:F13;"ATÍPICO")

I7=CONTAR.SI(F2:F13;"OK")

I8=CONTAR.SI(F2:F13;"ATÍPICO")/CONTARA(A2:A13)

	H	I
6	Piezas atípicas	1
7	Piezas OK	11
8	% fuera de control	8,3 %

Hoja1 ▸ KPIs

🔎 Lectura como ingeniero/a

Z ≈ 0 → la pieza se comporta como el resto del proceso: conforme.
|Z| > 2 → bajo una distribución normal, solo el ~5 % de los datos cae tan lejos: hay causa especial que investigar (herramienta, material, medición).
Regla 68–95–99,7: con umbral 3 serías más permisivo; con 1,5 más estricto. El umbral es una decisión de coste-riesgo, y por eso vive en una celda editable ($I$4).
Acción inmediata sobre M-07: apartar la pieza, verificar el calibre y revisar el lote L-23.

Dos variantes para exprimir el modelo

VARIANTE A

Umbral dinámico: el cliente aprieta a |Z| > 1,5

El cliente endurece la especificación y pide vigilar todo lo que supere 1,5 desviaciones. Como el umbral está en el panel, basta con reescribir la celda I4 — de 2 a 1,5 — y toda la columna Estado se recalcula al instante. Sin tocar una sola fórmula.

	H	I
4	Umbral \|Z\|	1,5 ← editada (antes: 2)

La hoja reacciona y M-09 (Z = 1,59) cae ahora del lado de los atípicos:

	A	B	E	F
1	ID	Diámetro	Z	Estado
2	M-01	24,98	−0,29	OK
3	M-02	25,03	0,43	OK
4	M-03	25,01	0,14	OK
5	M-04	24,95	−0,72	OK
6	M-05	25,07	1,01	OK
7	M-06	25,00	0,00	OK
8	M-07	24,83	−2,45	ATÍPICO
9	M-08	25,02	0,29	OK
10	M-09	25,11	1,59	ATÍPICO
11	M-10	24,99	−0,14	OK
12	M-11	25,04	0,58	OK
13	M-12	24,97	−0,43	OK

Hoja1 ▸ umbral 1,5

Ahora investigamos dónde se concentran los atípicos. CONTAR.SI.CONJUNTO cruza el Estado con el turno y el lote:

I10=CONTAR.SI.CONJUNTO(F2:F13;"ATÍPICO";D2:D13;"Tarde")

I11=CONTAR.SI.CONJUNTO(F2:F13;"ATÍPICO";C2:C13;"L-23")

Atípicos en turno Tarde

2de 2 atípicos totales → foco en el turno de tarde

Atípicos en lote L-23

2el lote L-23 concentra el 100 % de las alarmas

Y la pregunta clave: ¿cómo queda el proceso si filtramos los atípicos? Calculamos la media solo de las piezas OK…

I13=PROMEDIO.SI.CONJUNTO(B2:B13;F2:F13;"OK")

…y, si tienes Excel 365/2021, puedes extraer la muestra limpia completa con una sola fórmula matricial dinámica:

K2=FILTRAR(A2:B13;F2:F13="OK";"Sin datos válidos")

Indicador	Con atípicos	Solo piezas OK	Cambio
Media (mm)	25,000	25,006	+0,006
Desv. típica (mm)	0,0693	0,0357	−48 %
% fuera de control	16,7 % con umbral 1,5 (2 de 12)		KPI

Conclusión de la Variante A: apartar 2 mediciones atípicas reduce la variabilidad aparente del proceso casi a la mitad. Esto demuestra por qué los Z-Score se usan como filtro previo antes de calcular índices de capacidad (Cp, Cpk): un solo dato contaminado puede hacer parecer incapaz a un proceso que sí lo es.

VARIANTE B

Datos sucios sin pánico: blindaje con SI.ERROR

Situación real de planta: el sensor de la pieza M-13 falla y en lugar de un número entrega el texto «N/D» en la columna B. ¿Qué pasa con nuestra fórmula? Una resta entre texto y número revienta:

	A	B	E (sin proteger)	E (con SI.ERROR)
14	M-13	N/D	#¡VALOR!	REVISAR

Hoja1 ▸ fila 14

Envolvemos la fórmula en SI.ERROR para que el dato dudoso quede marcado para revisión humana en vez de romper el informe:

E14=SI.ERROR(REDONDEAR((B14-$I$2)/$I$3;2);"REVISAR")

F14=SI.ERROR(SI(ABS(E14)>$I$4;"ATÍPICO";"OK");"REVISAR")

Un segundo escenario clásico: la muestra llega vacía (nadie ha medido aún). DESVEST.M sobre un rango sin números devuelve #¡DIV/0!, y ese error se propaga a cada Z-Score. Blindamos también el panel:

I3=SI.ERROR(DESVEST.M(B2:B13);0)

🛡 Buenas prácticas

Elige un texto de error que no se confunda con un estado válido: «REVISAR» nunca será contado como «ATÍPICO» por CONTAR.SI.
Si además quieres excluirlas del recuento de piezas, usa =CONTAR.SI(F2:F14;"OK")+CONTAR.SI(F2:F14;"ATÍPICO") en lugar de CONTARA.
SI.ERROR envuelve la fórmula completa; si solo quieres detectar texto, la alternativa es ESNUMERO(B14) dentro de un SI anidado.

Tu turno: reto de práctica

🎯 Reto: piezas moldeadas por inyección

Ahora eres tú quien dirige el análisis. Una inyectora produce piezas con peso nominal 250 g. El registro del último turno es este (cópialo en A1:B11 de una hoja nueva):

	A	B
1	ID	Peso (g)
2	P-01	250,4
3	P-02	249,7
4	P-03	251,2
5	P-04	250,1
6	P-05	248,9
7	P-06	250,6
8	P-07	253,8
9	P-08	249,5
10	P-09	250,2
11	P-10	250,0

Hoja2 ▸ inyección

Tarea 1. Monta un bloque de parámetros (media μ, desviación σ̂ y umbral = 2) con PROMEDIO y DESVEST.M. Tarea 2. Calcula el Z-Score en C2 con REDONDEAR y referencias absolutas, y arrastra hasta C11. Tarea 3. Clasifica cada pieza en la columna D con SI + ABS y cuenta los atípicos con CONTAR.SI. Tarea 4. Baja el umbral a 1: ¿cuántas piezas quedan marcadas ahora y cuáles son? Anota qué cambia en tu conclusión. Tarea 5. Añade una fila 12 con el texto «N/D» en el peso y protege tus fórmulas con SI.ERROR para que devuelvan «REVISAR». Tarea 6 (extra). Con el umbral original, calcula el peso medio de las piezas válidas con PROMEDIO.SI.CONJUNTO y compáralo con la media bruta.

0/6 completadas

Necesito una pista (sin spoilers de la solución)

Bloquea las referencias del panel con F4 antes de arrastrar: deben quedar como $H$2, con dólares en letra y número.
ABS convierte −2,52 en 2,52: sin él, los atípicos por defecto (Z negativo grande) se escaparían del filtro.
Piensa antes de calcular: al bajar el umbral, la regla se vuelve más estricta, así que deberían aparecer más piezas marcadas, no menos.
Para ver todas tus fórmulas de golpe y revisar rangos, pulsa Ctrl + ` (acento grave).

Qué entregar

Captura de la hoja con las columnas ID, Peso, Z-Score y Estado, más el bloque de parámetros.
Las cuatro fórmulas clave en un documento (Z, Estado, recuento y SI.ERROR).
Una frase de decisión: qué pieza apartarías de la línea y por qué.