Distribuir importes en tramos dinámicos con funciones matriciales

Distribuir importes en tramos dinámicos con funciones matriciales

Ante una columna de importes, lo primero que hace falta es entender cómo se distribuyen: cuántos hay en cada franja y cuánto suman. Es el histograma de toda la vida, pero aquí se construye con matrices dinámicas, así que cambias el número de tramos en una celda y la tabla entera se recalcula.

El montaje, paso a paso:

- SECUENCIA genera la columna de tramos a partir del número que pidas
- MAX sobre los importes da el techo, y REDONDEA.MULT lo lleva a una cifra redonda (4.759 → 4.800), para que los cortes salgan legibles en vez de con decimales
- Ese techo dividido entre el número de tramos da el ancho de cada franja
- El desde de cada tramo es el ancho por la secuencia menos uno, para que el primero arranque en cero; el hasta, el ancho por la secuencia
- Con los límites resueltos, SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO rellenan la suma y el recuento de cada franja

La gracia del enfoque es que no hay nada fijo: el número de tramos es un parámetro, y todo lo demás se deriva de él y del máximo de los datos.

Cuando te dan una columna con cientos de importes, la media te dice bastante poco. Lo que de verdad describe esos datos es cómo se reparten: cuántos registros caen en cada franja y cuánto pesa cada una. Es un histograma, y con matrices dinámicas se puede montar de forma que el número de tramos sea un parámetro y no una decisión grabada en las fórmulas.

El objetivo: escribes 4 en una celda y obtienes cuatro tramos con su suma y su recuento; escribes 10 y la tabla se rehace sola.

Paso 1: los tramos

Todo arranca del parámetro. Si el número de tramos vive en una celda, generar la columna es directo:

=SECUENCIA(num_tramos)

Esto devuelve 1, 2, 3… hasta el número pedido. No es más que un contador, pero es el esqueleto: todo lo demás se calcula a partir de él.

Paso 2: el ancho de cada tramo, y por qué se redondea

El ancho sale de dividir el techo de los datos entre el número de tramos. El techo es el máximo:

=MAX(importes)

Y aquí viene el detalle que separa una tabla legible de una incómoda. Si el máximo es 4.759 y quieres cuatro tramos, los cortes caen en 1.189,75, 2.379,5… Nadie lee eso. La solución es subir el máximo a una cifra redonda antes de dividir:

=REDONDEA.MULT(MAX(importes);100)

4.759 pasa a 4.800, y dividido entre 4 da tramos de 1.200 exactos. El múltiplo se elige según la magnitud de los datos: 100, 1.000 o lo que corresponda.

Es un ajuste cosmético en apariencia, pero decide si el resultado se puede enseñar en una reunión.

Paso 3: los límites de cada franja

Con el ancho calculado, cada tramo necesita un desde y un hasta. El truco está en apoyarse en la secuencia:

desde  =ancho*(SECUENCIA(num_tramos)-1)
hasta  =ancho*SECUENCIA(num_tramos)

El -1 del desde es lo que hace que el primer tramo empiece en cero: para la fila 1 calcula ancho*0. A partir de ahí, el desde de cada franja coincide exactamente con el hasta de la anterior, sin huecos ni solapes.

Paso 4: rellenar suma y recuento

Con los límites resueltos, el resto es condicional de dos condiciones. Para lo que suma cada tramo:

=SUMAR.SI.CONJUNTO(importes;importes;">"&desde;importes;"<="&hasta)

Y para cuántos elementos contiene:

=CONTAR.SI.CONJUNTO(importes;">"&desde;importes;"<="&hasta)

Fíjate en los criterios: van entre comillas y concatenados con el límite mediante &, que es como se construye una comparación dinámica en esta familia de funciones. No se puede escribir ">desde" porque quedaría como texto literal.

La combinación > para el desde y <= para el hasta es deliberada: garantiza que un importe que caiga justo en un corte se cuente una sola vez, en el tramo inferior. Usar >= y <= en ambos extremos duplicaría esos valores.

Funciones clave

  • SECUENCIA — genera la columna de tramos desde un parámetro; el esqueleto de todo
  • MAX — el techo de los datos
  • REDONDEA.MULT — sube el techo a una cifra redonda para que los cortes sean legibles
  • SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO — rellenan cada franja; criterios concatenados con &

Conclusión

Lo interesante aquí no es ninguna función suelta, es que el único dato fijo del montaje es el número de tramos. El ancho se deriva del máximo, los límites se derivan del ancho y de la secuencia, y las agregaciones se derivan de los límites. Cambiar un número reordena la tabla entera, que es justo lo que se necesita cuando estás explorando datos y todavía no sabes qué granularidad los explica mejor.

SECUENCIA y las matrices dinámicas requieren Microsoft 365.

Más casos con estas funciones

Más contenido de Excel en InflueXcel