01 / 11El Gráfico Dio Veinte Días por Estables

EstadísticaCalidad de DatosCeldas Vacías

Límites de control en Excel con PROMEDIO y DESVEST.M

El Gráfico de Control Dio la Línea 2 por Estable Veinte Días, Incluido el Día que Envasó 41.600 Bolsas Faltas de Peso, Porque Dos Días de Parada se Anotaron Como Cero

2 nov 20269 min de lectura

UsaDESVEST, VARPROMEDIOFILTRARCONTAR.SI.CONJUNTO

El gráfico de control de Harrowgate Mills decía que la línea 2 no salió de sus límites en todo octubre. El 23 de octubre llenó una media de 494,6 g en bolsas de 500 g. Dos días de mantenimiento anotados como 0 g habían estirado los límites hasta que nada podía quedar fuera.

Línea 2, octubre de 2026Cifra
Días de producción en la hoja20
Días de parada anotados como 0 g2
Llenado medio del 23 de octubre494,6 g
Puntuación z que le dio el gráfico+0,29
Puntuación z sin los días de parada−3,77
Bolsas rechazadas por el cliente41.600

Alcance. PROMEDIO, DESVEST.M, DESVEST.P, PROMEDIO.SI.CONJUNTO, CONTAR.SI.CONJUNTO y ABS funcionan en todas las versiones de Excel desde 2010, en Windows, Mac y la web. FILTRAR necesita Microsoft 365 o Excel 2021; la sección 5 da una versión con SI para las anteriores.

En todo el artículo la hoja está en A1:F25: fecha en A, estado en B y llenado medio del día, en gramos, en C. La columna B dice Running (en marcha) o Down (parada), tal como sale del sistema de planta.

Las filas 2 a 21 son los veinte días. Las filas 22 a 25 guardan la media, la desviación estándar y los límites.

01El Gráfico Dio Veinte Días por Estables

Cada día, la responsable de calidad anotaba en la columna C el peso medio de llenado de la línea 2. Los dos días de mantenimiento la línea no envasó nada, así que la celda recibió un 0. La columna D puntuaba cada día frente a la media y la desviación estándar del propio mes:

=(C2-PROMEDIO($C$2:$C$21))/DESVEST.M($C$2:$C$21)

Un día a más de tres desviaciones estándar de la media está fuera de control. Ninguna celda de la columna D pasó de 3 ni de −3, así que el gráfico mostraba un mes tranquilo.

Veinte Días, Dos Ceros, Ningún Día Fuera de Control

La hoja de pesos de llenado de octubre de la línea 2 de Harrowgate Mills, en A1:F25. La columna B indica si la línea estaba en marcha (Running) o parada (Down), y C guarda el llenado medio del día en gramos, con los dos días de parada anotados como 0. La columna D es la puntuación z frente a los veinte días, ceros incluidos; E es la puntuación z frente a los días en marcha, y F marca lo que pase de ±3. Las filas 22 a 25 guardan la media, DESVEST.M y los límites de ±3 de cada versión.

ABCDEF
1
Date
Status
Mean fill (g)
z, zeros in
z, running only
Outside ±3?
2
01 Oct
Running
500.8
0.33
0.34
No
3
02 Oct
Running
501.2
0.33
0.61
No
4
05 Oct
Running
500.4
0.33
0.08
No
5
06 Oct
Running
499.6
0.32
-0.45
No
6
07 Oct
Running
501
0.33
0.47
No
7
08 Oct
Down
0
-2.92
8
09 Oct
Running
500.6
0.33
0.21
No
9
12 Oct
Running
501.4
0.33
0.74
No
10
13 Oct
Running
500.2
0.32
-0.06
No
11
14 Oct
Running
499.8
0.32
-0.32
No
12
15 Oct
Running
500.9
0.33
0.41
No
13
16 Oct
Running
501.1
0.33
0.54
No
14
19 Oct
Running
500.5
0.33
0.14
No
15
20 Oct
Down
0
-2.92
16
21 Oct
Running
499.9
0.32
-0.25
No
17
22 Oct
Running
500.7
0.33
0.28
No
18
23 Oct
Running
494.6
0.29
-3.77
Yes
19
26 Oct
Running
501.3
0.33
0.67
No
20
27 Oct
Running
500.3
0.32
0.01
No
21
28 Oct
Running
500.8
0.33
0.34
No
22
Mean (g)
450.26
500.28
23
STDEV.S (g)
153.99
1.51
24
Lower limit (g)
-11.72
495.76
25
Upper limit (g)
912.23
504.81

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

El 23 de octubre se desajustó un cabezal de llenado y la línea dio una media de 494,6 g todo el día. La columna D le puso +0,29, algo por encima de la media.

El control de recepción del cliente pesó los palés y rechazó las 41.600 bolsas.

02Dos Ceros Movieron la Media 50 Gramos

Un cero es una medición. PROMEDIO y DESVEST.M cuentan un 0 exactamente igual que un 500,4, así que dos días vacíos se convirtieron en dos bolsas que no pesaban nada. Hundieron la media y multiplicaron por cien la desviación estándar.

EstadísticoCon los cerosSolo días en marcha
Media450,26 g500,28 g
DESVEST.M153,99 g1,51 g
Límite inferior−11,72 g495,76 g
Límite superior912,23 g504,81 g

Un límite inferior de −11,72 g es una bolsa que pesa menos que nada. Un límite que ninguna bolsa podría alcanzar indica que los datos están mal, no que el proceso esté tranquilo.

Escenario: Pon los veinte días en A2:C21 con 0 en C7 y C15. Escribe =PROMEDIO(C2:C21) en D22 y =DESVEST.M(C2:C21) en D23, y comprueba que D22 da 450,26 y D23 da 153,99.

Pruébalo en la cuadrícula

03Una Puntuación z Es Distancia en Desviaciones Estándar

Una puntuación z dice a cuántas desviaciones estándar está un valor de la media. Solo significa algo si la desviación estándar describe los datos.

Medida contra 153,99 g, cada bolsa real parece la media y cada cero parece algo bajo.

DíaLlenadoz con cerosz solo en marcha
22 oct500,7 g0,330,28
23 oct494,6 g0,29−3,77
08 oct0 g−2,92vacío

Con los ceros dentro, el día falto de peso queda por encima de la media de 450,26 g, así que su z es positiva. Frente a los días en marcha se queda 3,77 desviaciones estándar corto.

Excel tiene también NORMALIZACION, que recibe el valor, la media y la desviación estándar: =NORMALIZACION(C18;$E$22;$E$23) devuelve −3,77. Hace la misma resta y la misma división; no arregla los datos de entrada.

Escenario: Escribe =(C18-PROMEDIO($C$2:$C$21))/DESVEST.M($C$2:$C$21) para el 23 de octubre y lee +0,29. Luego borra C7 y C15 y mira cómo la misma celda baja a −3,77.

Pruébalo en la cuadrícula

04Deja Vacío el Día de Parada, No a Cero

El arreglo más sencillo está en los datos. PROMEDIO y DESVEST.M se saltan las celdas vacías y el texto, así que una celda vacía o la palabra "Parada" en la columna C sale de ambos sin tocar ninguna fórmula.

La fórmula de la puntuación z necesita entonces una protección, para que un día vacío siga vacío en vez de puntuar como un cero:

=SI(C2="";"";(C2-$E$22)/$E$23)

Cuidado con las primas. DESVESTA y PROMEDIOA cuentan el texto como 0, así que "Parada" escrito en C devolvería el problema. Usa las funciones normales.

Escenario: Borra C7 y C15 y escribe =PROMEDIO(C2:C21) en E22 y =DESVEST.M(C2:C21) en E23. Comprueba que E22 da 500,28 y E23 da 1,51; luego escribe Parada en C7 y confirma que ninguno de los dos se mueve.

Pruébalo en la cuadrícula

05O Filtra por Estado en la Fórmula

A veces los ceros llegan en una exportación del sistema y no puedes borrarlos. Deja que la columna B decida qué días cuentan, en lugar de los valores de C:

=PROMEDIO.SI.CONJUNTO(C2:C21;B2:B21;"Running")
=DESVEST.M(FILTRAR(C2:C21;B2:B21="Running"))

No existe un DESVEST.SI.CONJUNTO, y por eso la segunda línea filtra primero. Las dos devuelven las cifras de los días en marcha, 500,28 g y 1,51 g, con los ceros todavía en la hoja.

En Excel 2019 o anterior, =DESVEST.M(SI(B2:B21="Running";C2:C21)) hace lo mismo. Confírmala con Ctrl+Mayús+Entrar; DESVEST.M ignora el FALSO que SI devuelve en los días de parada.

Escenario: Deja los ceros en C7 y C15 y escribe =DESVEST.M(FILTRAR(C2:C21;B2:B21="Running")) en E23. Comprueba que da 1,51; luego cambia B7 a Running y mira cómo salta a unos 115.

Pruébalo en la cuadrícula

06Fija los Límites con un Periodo Limpio

Incluso sin los ceros, el día malo ayuda a fijar los límites con los que se le juzga. El día de 494,6 g por sí solo estira la desviación estándar de 0,53 g a 1,51 g.

Límites calculados conMediaDESVEST.MLímite inferiorz del 23 oct
Los 18 días en marcha500,28 g1,51 g495,76 g−3,77
17 días sin el 23 oct500,62 g0,53 g499,03 g−11,34

Los gráficos de control están pensados para trabajar en dos fases. Primero fijas los límites con un periodo de confianza; después los congelas y juzgas cada día nuevo contra ellos.

Si recalculas sobre todos los días, una racha de desajuste ensancha sus propios límites.

Escenario: Copia E22 y E23 y pégalos como valores en G22 y G23. Apunta la fórmula de la puntuación z a $G$22 y $G$23, para que los límites no se muevan cuando lleguen los días de noviembre.

Pruébalo en la cuadrícula

07DESVEST.M o DESVEST.P

Las dos se diferencian en uno en el divisor. DESVEST.M divide entre n − 1 y DESVEST.P entre n, así que la versión muestral sale algo mayor y los límites algo más anchos.

FunciónDías en marchaLímite inferior
DESVEST.M1,51 g495,76 g
DESVEST.P1,47 g495,88 g

Dieciocho días son una muestra de cómo funciona la línea 2, no su totalidad, así que usa DESVEST.M. La diferencia se reduce al sumar días. Ninguna de las dos salva una columna llena de ceros.

08Cuenta los Días Fuera de Control

Un gráfico se puede leer mal; un recuento cuesta más ignorarlo. CONTAR.SI.CONJUNTO cuenta las dos colas y se salta los días de parada vacíos, que ABS convertiría en #¡VALOR!:

=CONTAR.SI.CONJUNTO(E2:E21;">3")+CONTAR.SI.CONJUNTO(E2:E21;"<-3")

En la hoja de octubre, la columna D devuelve 0 y la E devuelve 1. Un total mensual de cero en una línea de llenado merece la misma sospecha que un límite inferior por debajo de cero.

Escenario: Escribe =CONTAR.SI.CONJUNTO(E2:E21;">3")+CONTAR.SI.CONJUNTO(E2:E21;"<-3") en F22. Comprueba que da 1; luego apunta una copia a D2:D21 y mira cómo da 0.

Pruébalo en la cuadrícula

09Cinco Trampas

  1. Cero por falta de lectura. Un 0 es una medición: hunde la media y estira la dispersión.
  2. Las funciones con A. DESVESTA y PROMEDIOA vuelven a convertir el texto "Parada" en 0.
  3. Límites imposibles. Un límite inferior por debajo de cero gramos significa que los datos están mal.
  4. Límites que incluyen los días malos. Un desajuste ensancha sus propios límites si cada día los recalcula.
  5. ABS sobre celdas vacías. ABS("") devuelve #¡VALOR!; cuenta las colas con CONTAR.SI.CONJUNTO.

10Cuatro Ejercicios

  1. Reprodúcelo. Puntúa los veinte días con los ceros dentro y confirma que nada pasa de ±3.
  2. Vacíalo. Borra los días de parada y comprueba que el 23 de octubre da −3,77.
  3. Fíltralo. Vuelve a poner los ceros y reconstruye los límites con PROMEDIO.SI.CONJUNTO y FILTRAR.
  4. Congélalo. Fija como valores los límites de los 17 días limpios y vuelve a puntuar el mes.

Qué Llevarse

Cada peso de la hoja de Harrowgate era correcto, y la aritmética también. Dos celdas registraron "no pasó nada" como "una bolsa pesó 0 g", y la estadística se lo creyó.

Deja vacía la lectura que falta, o filtra por estado, antes de que PROMEDIO y DESVEST.M la vean. Después comprueba que tus límites son pesos que una bolsa podría tener de verdad.

Comparte este artículo:
Volver al Blog

Practícalo

DESVEST, VAR

Un ejercicio en una cuadrícula real

Abrir el ejercicio

Las funciones que usa este artículo

Sintaxis, ejemplos resueltos y los errores que esperar: una página de referencia para cada una.

Sigue leyendo

  1. Promedio de promedios en Excel: por qué tu porcentaje fallaLeer el artículo
  2. MAX.SI.CONJUNTO y MIN.SI.CONJUNTO: por qué devuelven 0Leer el artículo
  3. Cómo detectar valores fijos en fórmulas de ExcelLeer el artículo
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy