01 / 09El Acumulado Se Calculó en Orden de Referencia

Análisis ABCTotales AcumuladosAnálisis de Datos

Análisis ABC en Excel: clasifica por porcentaje acumulado

El Almacén Contaba Siete Piezas Cada Mes y la Tercera Más Valiosa una Vez al Año, Porque el Porcentaje Acumulado del ABC Se Calculó en Orden de Referencia

16 nov 20268 min de lectura

UsaSUMAR.SIJERARQUIASISUMA

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.

ListaPiezas ACuota del valor en A
Acumulado en orden de referencia7 de 1067,0 %
Cuota por encima de cada pieza4 de 1081,5 %

Alcance. SUMA, SUMAR.SI, JERARQUIA, SI y CONTAR.SI funcionan en todas las versiones de Excel. MIN.SI.CONJUNTO y MAX.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.

ABCDEFG
1
Part
Annual value
Running % printed
Class printed
Rank
Share above
Class true
2
AX-101
6000
3.0%
A
7
92.0%
B
3
AX-102
58000
32.0%
A
1
0.0%
A
4
AX-103
3000
33.5%
A
9
97.5%
C
5
AX-104
12000
39.5%
A
5
81.5%
B
6
AX-105
44000
61.5%
A
2
29.0%
A
7
AX-106
2000
62.5%
A
10
99.0%
C
8
AX-107
9000
67.0%
A
6
87.5%
B
9
AX-108
30000
82.0%
B
4
66.5%
A
10
AX-109
5000
84.5%
B
8
95.0%
C
11
AX-110
31000
100.0%
C
3
51.0%
A

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.

PiezaValor anualClase impresa
AX-1062.000 £A
AX-11031.000 £C
AX-10830.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 %.

Pruébalo en la cuadrícula

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.

PuestoPiezaValor anualAcumulado
1AX-10258.000 £29,0 %
2AX-10544.000 £51,0 %
3AX-11031.000 £66,5 %
4AX-10830.000 £81,5 %
5AX-10412.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ícula

03SUMAR.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 %.

PiezaValor anualCuota por encima
AX-10258.000 £0,0 %
AX-11031.000 £51,0 %
AX-10412.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 %.

Pruébalo en la cuadrícula

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.

ReglaAX-110, 31.000 £AX-108, 30.000 £Piezas A
Cuota posterior ≤ 80 %AB3
Cuota anterior < 80 %AA4

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.

Pruébalo en la cuadrícula

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"))
ClasePiezasValor anualCuota
A4163.000 £81,5 %
B327.000 £13,5 %
C310.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.

Pruébalo en la cuadrícula

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.

Pruébalo en la cuadrícula

07Cinco Trampas

  1. Un acumulado sobre una lista sin ordenar. =SUMA($B$2:B2) en orden de referencia mide la posición, no el valor.
  2. Ordenar una sola columna. Ordenar solo B separa los valores de sus referencias. Selecciona toda la tabla.
  3. Clasificar con la cuota posterior a la pieza. La que cruza el 80 % cae a B junto a una casi gemela en A.
  4. Umbrales escritos en 800 fórmulas. Déjalos en J1 y J2.
  5. 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

  1. Reprodúcelo. Escribe las diez piezas en orden de referencia con el acumulado en C, y confirma que siete salen A.
  2. Ordénalo. Ordena por valor anual de mayor a menor y confirma que el acumulado de AX-108 es 81,5 %.
  3. Evita el orden. Deshaz la ordenación, añade la cuota por encima con SUMAR.SI en F y confirma que AX-110 devuelve 51,0 %.
  4. Compruébalo. Añade las clases en G y la prueba con MIN.SI.CONJUNTO y MAX.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.

Comparte este artículo:
Volver al Blog

Practícalo

SUMAR.SI

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. MAX.SI.CONJUNTO y MIN.SI.CONJUNTO: por qué devuelven 0Leer el artículo
  2. Promedio de promedios en Excel: por qué tu porcentaje fallaLeer el artículo
  3. Combinar consultas en Power Query: filas duplicadasLeer 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