Repetir un rango N veces según marca con MAP y CONTAR.SI

Un usuario necesita repetir un listado de locales (D2:D10) un número variable de veces para cada marca de la columna B. El número de repeticiones se obtiene de una tabla auxiliar con BUSCARV. La fórmula manual con APILARV no escala cuando hay muchas marcas.

Leo construye una solución ingeniosa con MAP + CONTAR.SI + INDICE:

``
=LET(
m; A2:A169;
i; MAP(m; LAMBDA(a;
ENTERO((CONTAR.SI(INDICE(m;1):a; a)-1) / BUSCARV(a; 'maestro marcas recuento'!A:E; 5; 0)) + 1
));
r; 'maestro a repetir'!A2:A10;
INDICE(r; i)
)
`

La clave está en el CONTAR.SI(INDICE(m;1):a; a): cuenta cuántas veces ha aparecido la marca actual desde el inicio hasta la celda actual (un acumulador progresivo). Al dividir entre el número de repeticiones y tomar la parte entera, se obtiene un índice cíclico que recorre el listado de locales.

Para quienes trabajan con tablas de Excel (formato tabla) y prefieren arrastrar la fórmula celda a celda, Leo aporta una versión sin desbordamiento:

`
=LET(
i; ENTERO((CONTAR.SI($A$2:A2; A2)-1) / BUSCARV(A2; 'maestro marcas recuento'!$A:$E; 5; 0)) + 1;
r; 'maestro a repetir'!$A$2:$A$10;
INDICE(r; i)
)
`

Leo también menciona que con la función TRIMRANGE` (disponible en Insider) se podría hacer de forma más elegante referenciando toda la columna.

El problema: repetir un listado un número variable de veces

Un usuario tenía un listado de locales (D2:D10) que debía repetirse un número distinto de veces por cada marca de la columna B. Cuántas veces se repite cada marca sale de una tabla auxiliar con BUSCARV. La típica solución a mano con APILARV funciona con cuatro marcas, pero se vuelve inmanejable cuando hay decenas.

La solución: MAP + CONTAR.SI + INDICE

Leo montó una fórmula que genera un índice cíclico para recorrer el listado de locales tantas veces como haga falta:

=LET(
    m; A2:A169;
    i; MAP(m; LAMBDA(a;
        ENTERO((CONTAR.SI(INDICE(m;1):a; a)-1) / BUSCARV(a; 'maestro marcas recuento'!A:E; 5; 0)) + 1
    ));
    r; 'maestro a repetir'!A2:A10;
    INDICE(r; i)
)

La pieza clave es CONTAR.SI(INDICE(m;1):a; a). El truco está en el rango INDICE(m;1):a: va desde la primera celda hasta la celda actual, así que cuenta cuántas veces ha aparecido la marca hasta ese punto. Es un contador acumulativo que avanza fila a fila.

Al dividir ese acumulado entre el número de repeticiones de la marca (el BUSCARV) y quedarnos con la parte entera (ENTERO), obtenemos un índice que cicla: recorre el listado de locales del 1 al N, vuelve a empezar, y así sucesivamente. INDICE(r; i) traduce ese índice al local concreto.

Una versión para arrastrar en tablas

Para quien trabaja con tablas de Excel (formato tabla) y prefiere arrastrar la fórmula celda a celda en vez de desbordar, Leo dio la versión equivalente con referencias relativas:

=LET(
    i; ENTERO((CONTAR.SI($A$2:A2; A2)-1) / BUSCARV(A2; 'maestro marcas recuento'!$A:$E; 5; 0)) + 1;
    r; 'maestro a repetir'!$A$2:$A$10;
    INDICE(r; i)
)

Es la misma lógica, pero $A$2:A2 es el rango "expansivo" clásico que crece a medida que arrastras hacia abajo, reproduciendo el mismo contador acumulativo sin necesidad de que la fórmula derrame.

El truco del rango expansivo

Merece la pena detenerse en el patrón que aparece dos veces: un rango cuyo inicio está anclado y cuyo final es la fila actual (INDICE(m;1):a en la versión matricial, $A$2:A2 en la arrastrable). Ese rango "que crece" es la forma canónica de construir acumulados en Excel —sumas corridas, conteos progresivos, numeraciones— y aquí es lo que permite saber "cuántas veces llevo vista esta marca".

Leo apuntó además que con la función TRIMRANGE (disponible en canales Insider) se podría referenciar la columna entera de forma aún más limpia.

Funciones clave

  • MAP + LAMBDA: recorren la columna de marcas aplicando el cálculo a cada fila.
  • CONTAR.SI sobre rango expansivo: el contador acumulativo por marca.
  • BUSCARV: trae el número de repeticiones de cada marca.
  • ENTERO: convierte el acumulado en un índice cíclico.
  • INDICE: traduce el índice al elemento del listado.

Conclusión

Lo que empezó como "repetir una lista varias veces" acabó siendo una lección sobre rangos expansivos e índices cíclicos, dos ideas que reaparecen constantemente en fórmulas de acumulado y numeración. La versión matricial derrama en una celda; la arrastrable encaja en tablas. Otro problema cotidiano con solución elegante nacido en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel