Contar participantes por mes en cursos con rangos de fechas

Interesante reto planteado por Jaime: tiene una lista de cursos, cada uno con fecha de inicio, fecha de fin y un numero de participantes. Necesita saber cuantos participantes tiene activos cada mes, teniendo en cuenta que un mismo curso puede abarcar varios meses. Por ejemplo, un curso del 15 de febrero al 23 de junio con 3 participantes aporta 3 participantes a cada uno de esos cinco meses.

El problema parece sencillo a primera vista, pero las condiciones cruzadas entre rangos de fechas y meses lo complican bastante. Jaime lo habia intentado con SUMAR.SI.CONJUNTO sin exito. La comunidad respondio con nada menos que 7 enfoques distintos, cada uno con su propia logica y estilo.

---

Columna auxiliar con SI y FIN.MES

El primer enfoque usa una columna auxiliar por cada mes con una formula SI que comprueba si el curso solapa con ese mes:

``
=SI(MAX(0; 1+MIN($B2; E$1) - MAX($A2; 1+FIN.MES(E$1; -1))) > 0; $C2; 0)
`

La logica calcula si hay al menos un dia de solapamiento entre el rango del curso y el mes de la columna. Si lo hay, devuelve los participantes; si no, cero. Las sumas por columna dan el total mensual. Es el enfoque mas intuitivo y facil de auditar, aunque ocupa muchas columnas.

---

Hugo: MAP + BYROW + MEDIANA

Hugo propuso una solucion elegante usando MAP con BYROW y un truco con MEDIANA para comprobar la superposicion de fechas:

`
=MAP(E2:E16; LAMBDA(r;
SUMA(
BYROW(FIN.MES(+A2:B178; {-1;0}) + {1;0};
LAMBDA(f; MEDIANA(f; r) = r)
) C2:C178
)
))
`

La idea es ingeniosa: para cada mes (r), convierte las fechas de inicio y fin de cada curso en un rango mensual y usa MEDIANA para comprobar si el mes cae dentro. Si la mediana de (inicio_mes, fin_mes, r) es r, significa que r esta entre ambos extremos. Ademas, aporto una variante con TEXT que convierte las fechas a formato numerico "emm" para la comparacion.

---

Miki: MAP + DATEDIF + MATRIZATEXTO + AGRUPAR.POR

Miki tomo un camino completamente diferente: en lugar de comprobar solapamiento, expande cada curso en sus meses individuales y luego agrupa:

`
=MAP(A2:A178; B2:B178; LAMBDA(a; b;
SIFECHA(FIN.MES(a; -1) + 1; FIN.MES(b; 0); "m") + 1
))
`

Primero calcula cuantos meses abarca cada curso con SIFECHA. Luego usa MATRIZATEXTO para generar la lista de meses como texto ("25-01; 25-02; ..."). Finalmente, con TEXTSPLIT, TOCOL y AGRUPAR.POR agrupa los participantes por mes. Es un enfoque creativo que demuestra el poder de las funciones de texto combinadas con funciones de agrupacion.

---

Un miembro de la comunidad: LET + BYCOL + EDATE + SECUENCIA

Otra propuesta genera la secuencia completa de meses con EDATE y SECUENCIA, y usa BYCOL para sumar los participantes de los cursos activos en cada mes:

`
=LET(
i; A2:A178;
f; B2:B178;
m; MIN(i);
e; FECHA.MES(m; SECUENCIA(SIFECHA(m; MAX(f); "m") + 1;; 0));
d; ENFILA(e - DIA(e) + 1);
APILARH(
TEXTO(e; "mmm-aa");
TOCOL(BYCOL(
(i <= FIN.MES(+d; 0)) (f >= d) C2:C178;
SUMA
))
)
)
`

Lo interesante de esta solucion es que genera automaticamente el rango de meses a partir de los datos, sin necesidad de definirlos manualmente. Ademas, incluye las etiquetas de mes en el resultado con APILARH.

---

John: MAP + SUMA con FIN.MES (y SUMAR.SI.CONJUNTO)

John aporto la solucion mas concisa con MAP, multiplicando condiciones booleanas directamente:

`
=MAP(E3:E17; LAMBDA(x;
SUMA(C2:C178 (FIN.MES(x; 0) >= A2:A178) (x <= B2:B178))
))
`

La logica es directa: para cada mes, un curso esta activo si su inicio es anterior o igual al fin del mes y su fin es posterior o igual al inicio del mes. John ademas demostro que se puede resolver sin MAP usando SUMAR.SI.CONJUNTO con los criterios como matriz:

`
=SUMAR.SI.CONJUNTO(C2:C178; A2:A178; "<="&FIN.MES(+E3:E17; 0); B2:B178; ">="&E3:E17)
`

Y finalmente, una version todo-en-uno con LET que genera los meses automaticamente y aplica SUMAR.SI.CONJUNTO:

`
=LET(
F; FIN.MES;
i; A2:A178;
e; B2:B178;
d; E3:E17;
a; MIN(i);
m; UNICOS(F(SECUENCIA(1 + MAX(e) - a;; a); -1));
APILARH(1 + m; SUMAR.SI.CONJUNTO(C2:C178; i; "<="&FIN.MES(+d; 0); e; ">="&d))
)
`

Un detalle importante que John aclaro: en SUMAR.SI.CONJUNTO, los rangos de criterio y suma deben ser referencias, pero el criterio si puede ser una matriz manipulada. Esto permite pasar el vector de meses directamente como criterio.

---

Leo: MMULT (producto matricial)

Leo trajo su funcion favorita, MMULT, para resolver el problema con multiplicacion matricial pura:

`
=MMULT(
(ENFILA(A2:A178) <= FIN.MES(+E2:E16; 0)) (ENFILA(B2:B178) >= E2:E16);
C2:C178
)
`

La formula construye una matriz booleana donde cada fila es un mes y cada columna es un curso, con 1 si el curso esta activo ese mes y 0 si no. Al multiplicar esta matriz por el vector de participantes con MMULT, se obtiene directamente la suma por mes. Es la solucion mas compacta y matematicamente elegante.

Leo ademas aporto una variante con BYCOL que invierte la orientacion de los vectores:

`
=LET(
f; ENFILA(E2:E16);
ENCOL(BYCOL(
(A2:A178 <= FIN.MES(f; 0)) (B2:B178 >= f) C2:C178;
SUMA
))
)
`

Hugo le pregunto si esta solucion tendria problemas con el limite de columnas de Excel al escalar con mas datos, y Leo confirmo que efectivamente MMULT requiere girar los vectores con ENFILA, lo que puede alcanzar el limite de 16.384 columnas con datasets muy grandes. La alternativa con BYCOL y ENFILA en el vector de meses (en lugar de en los datos) soluciona este problema.

---

Un caso con 7 soluciones distintas al mismo problema, cada una con su propio enfoque: columnas auxiliares, MAP+MEDIANA, expansion de meses con texto, BYCOL con secuencias, SUMAR.SI.CONJUNTO matricial, producto MMULT y BYCOL+ENFILA`. El fichero adjunto, recopilado por Jaime, incluye todas las soluciones en hojas separadas para poder compararlas.

El reto: participantes activos por mes con rangos de fechas

Jaime planteó un problema que suena fácil hasta que te pones: tiene una lista de cursos, cada uno con fecha de inicio, fecha de fin y número de participantes, y necesita saber cuántos participantes hay activos cada mes. La gracia está en que un mismo curso puede abarcar varios meses: uno que va del 15 de febrero al 23 de junio con 3 participantes suma 3 a cada uno de esos cinco meses.

Jaime lo había intentado con SUMAR.SI.CONJUNTO sin éxito. La comunidad respondió con nada menos que siete enfoques distintos. Repasamos los más representativos.

Enfoque 1: columna auxiliar con SI y FIN.MES

El más intuitivo. Una columna auxiliar por cada mes, con una fórmula que comprueba si el curso solapa con ese mes:

=SI(MAX(0; 1+MIN($B2; E$1) - MAX($A2; 1+FIN.MES(E$1; -1))) > 0; $C2; 0)

Calcula los días de solapamiento entre el rango del curso y el mes de la columna; si hay al menos uno, devuelve los participantes, y si no, cero. Sumando por columna sale el total mensual. Fácil de auditar, aunque ocupa muchas columnas.

Enfoque 2: Hugo, con MAP + BYROW + MEDIANA

Hugo tiró de un truco muy ingenioso con MEDIANA para detectar si un mes cae dentro del rango del curso:

=MAP(E2:E16; LAMBDA(r;
    SUMA(
        BYROW(FIN.MES(+A2:B178; {-1;0}) + {1;0};
            LAMBDA(f; MEDIANA(f; r) = r)
        ) * C2:C178
    )
))

La idea: si la mediana de (inicio de mes, fin de mes, r) es exactamente r, entonces r está entre ambos extremos. Una forma elegante de comprobar pertenencia a un rango sin comparaciones encadenadas.

Enfoque 3: John, la vía concisa con MAP

John dio con la fórmula más compacta, multiplicando condiciones booleanas. La escribimos con las comparaciones en sentido "mayor o igual" para que se lea de corrido:

=MAP(E3:E17; LAMBDA(x;
    SUMA(C2:C178 * (FIN.MES(x; 0) >= A2:A178) * (B2:B178 >= x))
))

Un curso está activo en un mes si su inicio es anterior o igual al fin de ese mes y su fin es posterior o igual al inicio del mes. John además demostró que puede resolverse con SUMAR.SI.CONJUNTO, pasando el vector de meses como criterio (con los operadores de comparación en texto), y aclaró un detalle importante: en SUMAR.SI.CONJUNTO los rangos deben ser referencias, pero el criterio sí puede ser una matriz manipulada.

Enfoque 4: Leo, producto matricial con MMULT

Leo trajo su función favorita para resolverlo con álgebra pura:

=MMULT(
    (FIN.MES(+E2:E16; 0) >= ENFILA(A2:A178)) * (ENFILA(B2:B178) >= E2:E16);
    C2:C178
)

Construye una matriz booleana donde cada fila es un mes y cada columna un curso, con 1 si el curso está activo ese mes. Al multiplicarla por el vector de participantes con MMULT, sale directamente la suma por mes. Es la solución más matemáticamente elegante. Eso sí, Leo advirtió que MMULT obliga a girar los vectores con ENFILA, lo que con datasets enormes puede rozar el límite de 16.384 columnas; una variante con BYCOL esquiva ese tope.

Otros enfoques

  • Miki tomó el camino contrario: en lugar de comprobar solapamiento, expande cada curso en sus meses individuales con SIFECHA y MATRIZATEXTO, y luego agrupa con TEXTSPLIT, TOCOL y AGRUPARPOR.
  • Otra propuesta genera automáticamente el rango de meses con FECHA.MES y SECUENCIA, y suma con BYCOL, incluyendo las etiquetas de mes en el resultado.

Funciones clave

MAP, LAMBDA, MMULT, BYCOL, BYROW, FIN.MES, SUMAR.SI.CONJUNTO, MEDIANA, SECUENCIA, SIFECHA, AGRUPARPOR. Un muestrario casi completo del Excel moderno.

Conclusión

Siete formas de resolver el mismo problema, desde columnas auxiliares hasta producto matricial. La lección de fondo: para saber si un mes cae dentro de un rango de fechas, casi siempre se reduce a "inicio anterior o igual al fin del mes y fin posterior o igual al inicio". El fichero de Jaime reúne todas las soluciones en hojas separadas para compararlas. Retos así se cuecen cada semana en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel