El almacén de Pellbrook Engineering cuenta las piezas A cada mes y las C una vez al año. La lista ABC dejó siete de diez piezas en la clase A, y AX-110, que vale 31.000 £ al año, en la clase C.
| Lista | Piezas A | Cuota del valor en A |
|---|---|---|
| Acumulado en orden de referencia | 7 de 10 | 67,0 % |
| Cuota por encima de cada pieza | 4 de 10 | 81,5 % |
Alcance.
SUMA,SUMAR.SI,JERARQUIA,SIyCONTAR.SIfuncionan en todas las versiones de Excel.MIN.SI.CONJUNTOyMAX.SI.CONJUNTO, de la sección 6, necesitan Excel 2019 o posterior.El análisis ABC ordena los artículos por valor anual, normalmente unidades consumidas por coste unitario. Las líneas del 80 % y el 95 % son una elección habitual, no una norma.
La sección 5 las deja en celdas para que pongas las tuyas.
01El Acumulado Se Calculó en Orden de Referencia
La lista sumaba el valor anual hoja abajo y lo dividía por el total. En C2, copiada hasta C11:
=SUMA($B$2:B2)/SUMA($B$2:$B$11)
Después clasificaba cada pieza con ese porcentaje. En D2:
=SI(C2<=0,8;"A";SI(C2<=0,95;"B";"C"))
Almacén de Pellbrook Engineering, valor anual por pieza
Diez piezas en A2:G11, en orden de referencia. B es el valor anual en libras: unidades consumidas al año por coste unitario, 200.000 £ en total. C es el porcentaje acumulado que calculó la lista antigua hoja abajo, y D la clase que imprimió. E es el puesto de cada pieza por valor, F la cuota del valor que tienen las piezas mayores y G la clase a partir de F. La línea de A, 80 %, está en J1 y la de B, 95 %, en J2.
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
Las dos fórmulas están bien escritas. La hoja estaba en orden de referencia, así que el acumulado medía lo abajo que estaba una pieza en el catálogo, no cuánto valor tenía.
| Pieza | Valor anual | Clase impresa |
|---|---|---|
| AX-106 | 2.000 £ | A |
| AX-110 | 31.000 £ | C |
| AX-108 | 30.000 £ | B |
Escenario: Escribe las diez piezas y sus valores en A2:B11 en orden de referencia. Pon =SUMA($B$2:B2)/SUMA($B$2:$B$11) en C2, cópiala hacia abajo y comprueba que AX-106 muestra 62,5 %.
02La Lista Tiene Que Ir de Mayor a Menor
El análisis ABC pregunta cuántas pocas piezas concentran la mayor parte del valor. Esa pregunta solo tiene respuesta si las piezas mayores van primero, y así cada acumulado es el valor de esa pieza y de todas las mayores.
Ordena A2:B11 por la columna B, de mayor a menor, con Datos > Ordenar. Selecciona las dos columnas, o los valores se separarán de sus referencias.
| Puesto | Pieza | Valor anual | Acumulado |
|---|---|---|---|
| 1 | AX-102 | 58.000 £ | 29,0 % |
| 2 | AX-105 | 44.000 £ | 51,0 % |
| 3 | AX-110 | 31.000 £ | 66,5 % |
| 4 | AX-108 | 30.000 £ | 81,5 % |
| 5 | AX-104 | 12.000 £ | 87,5 % |
Con la lista ordenada, la misma fórmula con SUMA funciona. Cuatro piezas tienen el 81,5 % del valor, que es lo que el almacén necesitaba saber.
Escenario: Selecciona A1:B11, ordena por Valor anual de mayor a menor y comprueba que C5 muestra ahora 81,5 % en la fila de AX-108.
Pruébalo en la cuadrícula03SUMAR.SI Da la Cuota Por Encima Sin Ordenar
Ordenar funciona una vez. La siguiente exportación vuelve a llegar en orden de referencia, y una copia ordenada se queda vieja. Una fórmula que no depende del orden de las filas evita las dos cosas.
Para cada pieza, suma el valor de todas las piezas que valen más. En F2, copiada hasta F11:
=SUMAR.SI($B$2:$B$11;">"&B2)/SUMA($B$2:$B$11)
Es el acumulado justo antes de la pieza, como si la lista estuviera ordenada. AX-102, la mayor, no tiene nada por encima y devuelve 0 %.
| Pieza | Valor anual | Cuota por encima |
|---|---|---|
| AX-102 | 58.000 £ | 0,0 % |
| AX-110 | 31.000 £ | 51,0 % |
| AX-104 | 12.000 £ | 81,5 % |
=JERARQUIA(B2;$B$2:$B$11) en E2 muestra el mismo orden como un número, lo que ayuda a leer la hoja de arriba abajo.
Escenario: Deja las piezas en orden de referencia. Escribe =SUMAR.SI($B$2:$B$11;">"&B2)/SUMA($B$2:$B$11) en F2, cópiala hacia abajo y comprueba que AX-110 devuelve 51,0 %.
04La Pieza Que Cruza el 80 % Es A
Siempre hay una pieza a caballo de la línea. Clasifica con la cuota anterior a la pieza, y la que lleva el total por encima del 80 % entra en la clase A.
AX-108 empieza en 66,5 % y termina en 81,5 %. Si se clasifica con la cuota posterior, cae a B, mientras AX-110, que solo vale 1.000 £ más, sigue siendo A.
| Regla | AX-110, 31.000 £ | AX-108, 30.000 £ | Piezas A |
|---|---|---|---|
| Cuota posterior ≤ 80 % | A | B | 3 |
| Cuota anterior < 80 % | A | A | 4 |
Dos piezas de casi el mismo valor contadas con calendarios distintos es justo lo que el análisis quiere evitar. La columna de cuota por encima de la sección 3 ya es la cuota anterior.
Escenario: Junto a la lista ordenada de la sección 2, compara =C5<=0,8 con =C4<0,8 para AX-108. Comprueba que la primera devuelve FALSO y la segunda VERDADERO.
05Deja las Líneas en Celdas
El 80 % y el 95 % estaban escritos dentro de cada fórmula. Ponlos en J1 y J2, y un solo cambio reclasifica todas las piezas.
Escribe 0,8 en J1 y 0,95 en J2, con formato de porcentaje. En G2, copiada hasta G11:
=SI(F2<$J$1;"A";SI(F2<$J$2;"B";"C"))
| Clase | Piezas | Valor anual | Cuota |
|---|---|---|---|
| A | 4 | 163.000 £ | 81,5 % |
| B | 3 | 27.000 £ | 13,5 % |
| C | 3 | 10.000 £ | 5,0 % |
AX-109 tiene exactamente un 95,0 % por encima, así que es C. La comparación es <, y una pieza que empieza justo en la línea pertenece a la clase de abajo.
Escenario: Escribe 0,8 en J1 y 0,95 en J2, y después =SI(F2<$J$1;"A";SI(F2<$J$2;"B";"C")) en G2. Cambia J1 a 0,6 y comprueba que AX-108 pasa a B.
06Comprueba las Clases Antes del Calendario
Una sola prueba detecta una lista calculada en el orden equivocado. Toda pieza A debe valer al menos tanto como cualquier pieza B:
=MIN.SI.CONJUNTO(B2:B11;G2:G11;"A")>=MAX.SI.CONJUNTO(B2:B11;G2:G11;"B")
Con la columna G devuelve VERDADERO: la A más pequeña vale 30.000 £ y la B más grande 12.000 £. Con la columna D antigua devuelve FALSO, porque AX-106, de 2.000 £, era A y AX-108, de 30.000 £, era B.
Después resume cada clase con dos fórmulas: =CONTAR.SI(G2:G11;"A") devuelve 4, y =SUMAR.SI(G2:G11;"A";B2:B11) devuelve 163.000. Divide por =SUMA(B2:B11) para obtener la cuota.
Escenario: Escribe =MIN.SI.CONJUNTO(B2:B11;D2:D11;"A")>=MAX.SI.CONJUNTO(B2:B11;D2:D11;"B") con las clases antiguas y comprueba que devuelve FALSO. Apúntala a la columna G y comprueba que da VERDADERO.
07Cinco Trampas
- Un acumulado sobre una lista sin ordenar.
=SUMA($B$2:B2)en orden de referencia mide la posición, no el valor. - Ordenar una sola columna. Ordenar solo B separa los valores de sus referencias. Selecciona toda la tabla.
- Clasificar con la cuota posterior a la pieza. La que cruza el 80 % cae a B junto a una casi gemela en A.
- Umbrales escritos en 800 fórmulas. Déjalos en J1 y J2.
- Ordenar por unidades en vez de por valor. 4.000 arandelas a 2 peniques son 80 £ al año. Multiplica unidades por coste unitario primero.
08Cuatro Ejercicios
- Reprodúcelo. Escribe las diez piezas en orden de referencia con el acumulado en C, y confirma que siete salen A.
- Ordénalo. Ordena por valor anual de mayor a menor y confirma que el acumulado de AX-108 es 81,5 %.
- Evita el orden. Deshaz la ordenación, añade la cuota por encima con
SUMAR.SIen F y confirma que AX-110 devuelve 51,0 %. - Compruébalo. Añade las clases en G y la prueba con
MIN.SI.CONJUNTOyMAX.SI.CONJUNTO, y confirma que=CONTAR.SI(G2:G11;"A")devuelve 4.
Qué Llevarse
Los valores eran correctos y las fórmulas también. El acumulado se calculó en un orden que no tenía nada que ver con el valor, así que el almacén contaba cada mes una pieza de 2.000 £ y una vez al año una de 31.000 £.
Mide la cuota por encima de cada pieza con SUMAR.SI, clasifica con la cuota anterior, deja las líneas en celdas y comprueba que toda A pesa más que toda B.