Domina el Análisis ABC en Excel

El análisis ABC es una técnica clásica para clasificar clientes, productos o cualquier magnitud de una empresa según su peso real en el total. Se basa en el principio de Pareto: el 80% de las ventas suele concentrarse en el 20% de los clientes. Antes, este tipo de cálculo ocupaba un montón de hojas dentro de Excel; hoy, gracias a las funciones matriciales, podemos condensar todo el proceso en una única celda y aplicarlo rápidamente a cualquier tabla. En este tutorial repasamos las cuatro funciones clave y luego las combinamos para construir el análisis completo.

Las cuatro funciones que vamos a combinar

Antes de montar el cálculo final, conviene entender por separado las funciones que intervienen: AGRUPARPOR, BUSCARX, SCAN y LET.

AGRUPARPOR

AGRUPARPOR permite tomar dos columnas y agrupar los valores por uno de sus elementos. Si tenemos una tabla donde los clientes se repiten en varias líneas y queremos un acumulado, basta con indicar:

• La columna por la que queremos agrupar (por ejemplo, el cliente).
• Los valores que queremos acumular (las ventas).
• La operación de agregación, en este caso SUMA (podría ser promedio u otro cálculo).

El resultado es el listado de clientes con sus ventas agregadas. La función admite parámetros adicionales que veremos más adelante para ordenar y controlar las cabeceras.

BUSCARX

BUSCARX vino a complementar y sustituir a la antigua BUSCARV, ofreciendo búsquedas mucho más flexibles. Admite dos tipos de búsqueda:

• Búsqueda exacta: por ejemplo, asignar a cada cliente el descuento que le corresponde. Indicamos el valor buscado (el cliente), la matriz donde buscar, el valor por defecto si no se encuentra (un 0%) y el modo de coincidencia 0 para exigir coincidencia exacta.
• Búsqueda aproximada por rangos: por ejemplo, asignar descuentos según tramos de ventas (de 2.000 a 4.000, de 4.000 a 6.000, etc.). Los rangos deben estar ordenados, y en el modo de coincidencia elegimos el siguiente elemento menor, de modo que un valor intermedio como 2.500 devuelva lo que corresponde al tramo de 2.000.

A diferencia de BUSCARV, en BUSCARX solo señalamos la columna de búsqueda y la matriz de retorno por separado, lo que da mucha más libertad.

SCAN

SCAN es una de las nuevas funciones matriciales y permite hacer en una sola fórmula cálculos que antes se hacían celda a celda, como un acumulado. Su estructura es:

• Un valor inicial desde el que arranca la secuencia (por ejemplo, 0 ventas).
• El array o rango sobre el que iterar línea a línea.
• Una función LAMBDA con dos parámetros: el valor acumulado hasta ese momento y el valor de la fila actual.

Para ir sumando ventas, la lambda sería simplemente acumulado + fila. El resultado es el acumulado de cada línea, pero trabajando una sola celda en lugar de arrastrar fórmulas hacia abajo.

LET

LET no aporta ningún cálculo nuevo, pero estructura y ordena fórmulas largas y complejas, sobre todo cuando reutilizamos un mismo rango varias veces. Definimos parejas de nombre y valor (variables) y, al final, el cálculo que devuelve el resultado. Por ejemplo, si filtramos un rango repitiendo la misma referencia tres veces, podemos definir _rango una sola vez y reutilizar ese nombre. La gran ventaja es el mantenimiento: si queremos cambiar el rango, solo lo modificamos en un sitio, evitando errores y pérdidas de tiempo.

Montando el análisis ABC paso a paso

Con la tabla de clientes, productos y ventas, queremos catalogar cada cliente con una etiqueta (A, B, C...). La idea es: los clientes A acumulan hasta el 75% de las ventas globales, los B van del 75% al 90% y los C completan hasta el 100%. Los umbrales se definen en una pequeña matriz de referencia que podemos ajustar a nuestra necesidad.

Los pasos son:

1. Agrupar y ordenar las ventas por cliente con AGRUPARPOR, sumando las ventas. Añadimos parámetros para no traer cabeceras (0), no incluir totales (0) y ordenar de mayor a menor por la segunda columna usando -2 (el signo negativo invierte el orden).
2. Calcular el acumulado con SCAN, partiendo de 0 y trabajando sobre la columna de ventas. Para aislar esa columna usamos TOMAR indicando todas las filas y la columna -1 (la última, contando desde la derecha). La lambda vuelve a ser acumulado + fila. Como truco, en las versiones recientes de Excel podemos sustituir esa lambda por la palabra SUMA directamente, aunque para cálculos más complejos conviene recuperar la fórmula extendida con los parámetros de acumulado y fila.
3. Convertir el acumulado en porcentaje dividiendo cada valor entre el total. El total es el último valor del acumulado: con la referencia derramada (#) y TOMAR con -1 en filas obtenemos esa última fila, que contiene el total.
4. Asignar la letra con BUSCARX, buscando cada porcentaje en la matriz de umbrales y devolviendo la letra correspondiente. Usamos el modo siguiente elemento mayor, de modo que un valor por debajo del 75% caiga en la A, y así sucesivamente. Con la referencia derramada (#) el cálculo se aplica a todas las filas.
5. Llevar la categoría a la tabla original con otro BUSCARX, buscando el cliente y devolviendo su letra. Aquí usamos ELEGIRCOLUMNAS para quedarnos solo con la columna de clientes de la matriz de resultados, y bloqueamos las referencias con F4.

Con la categoría asignada a cada línea, ya podemos enriquecer el análisis: por ejemplo, insertar una tabla dinámica que cruce categorías con productos para ver qué tipo de clientes compra cada producto.

Conclusión

El análisis ABC deja de ser un proceso laborioso de múltiples hojas para convertirse en una secuencia limpia de funciones matriciales encadenadas. Combinando AGRUPARPOR, SCAN, BUSCARX y LET obtenemos en pocas fórmulas una clasificación de clientes lista para analizar, mantenible y fácil de reaplicar a cualquier tabla. Es un punto de partida ideal para detectar de un vistazo dónde está concentrado el valor de tu negocio.

Más casos con estas funciones

• AGRUPARPOR
• SCAN
• ORDENAR

Más contenido de Excel en InflueXcel

• Acumular saldo con registros duplicados usando AGRUPARPOR y SCAN Caso
  Juan intenta acumular saldos cuando hay registros con la misma clave, pero ni BYROW, ni SCAN, ni AGRUPARPOR por separado le dan el resultado
• Precio más reciente por artículo: REDUCE, AGRUPARPOR y números complejos Caso
  Nuevo reto interesante planteado por Miki: dada una tabla con artículos y sus precios históricos por año (múltiples filas por artículo), obt
• Despivotar una tabla cruzada con fórmulas: SCAN+GROUPBY vs TEXTSPLIT+TOROW Caso
  Un miembro comparte un fichero con datos en formato tabla cruzada: 4 proyectos en filas, 12 meses en columnas, cada mes con dos sub-columnas
• Regularización trimestral con AGRUPARPOR y ARCHIVOMAKEARRAY: del caos a una fórmula Caso
  Caso fresquito de la comunidad. Juan plantea un problema contable: tiene una tabla de movimientos (Nombre, Concepto, Importe, Cuenta, Fecha)
• COMBINARX falla con datos de tablas dinámicas: cómo solucionarlo con AGRUPARPOR Caso
  Un miembro de la comunidad utiliza COMBINARX para cruzar datos financieros (códigos de cuenta, nombres y comentarios) entre dos años fiscale
• Por qué SUMAR.SI falla al arrastrar y cómo resolverlo con AGRUPARPOR Caso
  Un miembro de la comunidad comparte un archivo con datos de nóminas de varias empresas (cantidad, salario base, bonificación, vacaciones, de
• Agrupar y limpiar referencias con AGRUPARPOR, DIVIDIRTEXTO y APILARV Caso
  Interesante problema de un miembro que trabaja con una tabla de "Push Money cancelado" con más de 150 productos. Las referencias vienen dupl
• Extraer máximos por clave y calcular tiempo transcurrido: AGRUPARPOR vs fórmulas clásicas Caso
  Interesante caso doble que arrancó con una necesidad muy concreta: dada una tabla con columnas Clave, Secuencia y Rev, obtener el máximo de
• Mejora un 90% el rendimiento de Power Query con SQLite Tutorial
  Power Query es una herramienta potente para consolidar, combinar y calcular datos, pero cuando trabajamos con millones de registros y cálcul
• Por qué ELEGIRFILAS falla con índices vacíos o erróneos Caso
  Un miembro de la comunidad comparte un problema que a muchos nos ha dado algún dolor de cabeza: intenta usar SI.ND() para manejar errores en