Hoy contaremos cuántos meses consecutivos se dan bajo ciertas condiciones en un listado de matriculaciones:
Vemos que tenemos un listado de matriculaciones de ciertos alumnos en ciertos cursos durante meses del año corriente (entre ENERO y AGOSTO), y deseamos conocer para cada par Alumno-Curso cuántos meses consecutivos se ha matriculado cada uno de ellos.
En primer lugar toca transformar los meses en texto a valores de tipo fecha, lo que haremos con una sencilla fórmula FECHANUMERO:
=FECHANUMERO("1/"&[@[Mes matrícula]]&"2021")
rápido y simple.
La función FECHANUMERO transforma una fecha en formato de texto a tipo de dato fecha.
Vamos ahora a por la clave del asunto...
En primer lugar necesitamos listar elementos únicos de Alumno-Curso, lo que se consigue con la función UNICOS:
Nuestra fórmula, combina ORDENAR y UNICOS es:
=ORDENAR(UNICOS(Tabla1[[Curso]:[Alumno]]);2)
donde ordenamos con prioridad en el segundo de las columnas/campos, i.e., por los Alumnos...
A continuación obtendremos una secuencia de números naturales que nos indicarán en qué meses se ha producido cada matriculación, para cada par de elementos:
La fórmula buscada es:
=SI.ERROR(COINCIDIR(FECHA.MES("1/1/2021";SECUENCIA(1;9;0));ORDENAR(TRANSPONER(UNICOS((SI(Tabla1[Alumno]=$H3;SI(Tabla1[Curso]=$G3;Tabla1[Fecha convertida])))));;;VERDADERO);0);0)
Un poco larga, sí, pero nos da lo que necesitamos...
Identifica y posiciona en qué meses se ha matriculado tal alumno en tal curso.
Por ejemplo, el cuarto caso de la lista: alumno ANA + curso VBA, donde si verificamos la tabla original comprobaremos que ha cursado durante los meses de: FEBRERO, MARZO y ABBRIL
Y qué hace nuestra fórmula para listar de esa forma las coincidencias??.
En primer lugar generamos un listado de fechas correlativas correspondiente al periodo de estudio:
FECHA.MES("1/1/2021";SECUENCIA(1;9;0))
que genera una matriz de nueve fechas comenzando el 1/1/2021 y finalizando el 1/9/2021
Por otro lado buscamos la matriz ordenada de fechas en las que cada alumno+curso se ha matriculado:
ORDENAR(TRANSPONER(UNICOS((SI(Tabla1[Alumno]=$H3;SI(Tabla1[Curso]=$G3;Tabla1[Fecha convertida])))));;;VERDADERO)
Podemos ver el resultado, a modo de ejemplo, en la imagen siguiente:
Con ambas matrices 'creadas virtualmente', procedemos a compararlas con la función COINCIDIR mediante un tipo de coincidencia exacta:
COINCIDIR(matriz_9_meses;matriz_matriculaciones;0)
Esto nos devolverá una nueva matriz de números naturales consecutivos para aquellas posiciones o meses donde existe la coincidencia, esto es, donde se hubieran matriculado... con errores de tipo N/D (o N/A) para las no coincidencias...
Errores que tratamos finalmente con SI.ERROR.
Solo nos queda contar el valor máximo de meses consecutivos matriculados.
Esto lo conseguiremos con la siguiente fórmula:
=MAX(FRECUENCIA(SI(I3#>0;COLUMNA(I3#));SI(I3#=0;COLUMNA(I3#))))
Al trabajar con Microsoft 365, y sobre fórmulas matriciales desbordadas, adquiere la notación correspondiente con las almohadillas o numerales #
Además no hace falta ejecutarla matricialmente presionando Ctrl Mayusc Enter.
La fórmula se apoya en la función FRECUENCIA, identificando, por número de columna, dónde se encuentran los ceros (Meses de NO matriculación) y los valores positivos (Meses de SI matriculación).
Esto es muy interesante, ya que FRECUENCIA permite contar el número de los resultados que se encuentran dentro de un rango.. justo lo que necesitamos.
Veamos un ejemplo del comportamiento de FRECUENCIA...
En la imagen, en la columna DATOS se replica el resultado del condicional anterior:
SI(rng_datos>0;COLUMNA(rang_datos))
Mientras que en la columna GRUPOS vemos el resultado de:
SI(rng_datos=0;COLUMNA(rang_datos))
A la derecha el resultado de FRECUENCIA y una breve explicación de lo que vemos...
Poco más queda por hacer, salvo recuperar el valor máximo de entre esos conteos:
=MAX(FRECUENCIA(datos;grupo))
Obteniendo el resultado buscado!!!
Vemos que tenemos un listado de matriculaciones de ciertos alumnos en ciertos cursos durante meses del año corriente (entre ENERO y AGOSTO), y deseamos conocer para cada par Alumno-Curso cuántos meses consecutivos se ha matriculado cada uno de ellos.
En primer lugar toca transformar los meses en texto a valores de tipo fecha, lo que haremos con una sencilla fórmula FECHANUMERO:
=FECHANUMERO("1/"&[@[Mes matrícula]]&"2021")
rápido y simple.
La función FECHANUMERO transforma una fecha en formato de texto a tipo de dato fecha.
Vamos ahora a por la clave del asunto...
En primer lugar necesitamos listar elementos únicos de Alumno-Curso, lo que se consigue con la función UNICOS:
Nuestra fórmula, combina ORDENAR y UNICOS es:
=ORDENAR(UNICOS(Tabla1[[Curso]:[Alumno]]);2)
donde ordenamos con prioridad en el segundo de las columnas/campos, i.e., por los Alumnos...
A continuación obtendremos una secuencia de números naturales que nos indicarán en qué meses se ha producido cada matriculación, para cada par de elementos:
La fórmula buscada es:
=SI.ERROR(COINCIDIR(FECHA.MES("1/1/2021";SECUENCIA(1;9;0));ORDENAR(TRANSPONER(UNICOS((SI(Tabla1[Alumno]=$H3;SI(Tabla1[Curso]=$G3;Tabla1[Fecha convertida])))));;;VERDADERO);0);0)
Un poco larga, sí, pero nos da lo que necesitamos...
Identifica y posiciona en qué meses se ha matriculado tal alumno en tal curso.
Por ejemplo, el cuarto caso de la lista: alumno ANA + curso VBA, donde si verificamos la tabla original comprobaremos que ha cursado durante los meses de: FEBRERO, MARZO y ABBRIL
Y qué hace nuestra fórmula para listar de esa forma las coincidencias??.
En primer lugar generamos un listado de fechas correlativas correspondiente al periodo de estudio:
FECHA.MES("1/1/2021";SECUENCIA(1;9;0))
que genera una matriz de nueve fechas comenzando el 1/1/2021 y finalizando el 1/9/2021
Por otro lado buscamos la matriz ordenada de fechas en las que cada alumno+curso se ha matriculado:
ORDENAR(TRANSPONER(UNICOS((SI(Tabla1[Alumno]=$H3;SI(Tabla1[Curso]=$G3;Tabla1[Fecha convertida])))));;;VERDADERO)
Podemos ver el resultado, a modo de ejemplo, en la imagen siguiente:
Con ambas matrices 'creadas virtualmente', procedemos a compararlas con la función COINCIDIR mediante un tipo de coincidencia exacta:
COINCIDIR(matriz_9_meses;matriz_matriculaciones;0)
Esto nos devolverá una nueva matriz de números naturales consecutivos para aquellas posiciones o meses donde existe la coincidencia, esto es, donde se hubieran matriculado... con errores de tipo N/D (o N/A) para las no coincidencias...
Errores que tratamos finalmente con SI.ERROR.
Solo nos queda contar el valor máximo de meses consecutivos matriculados.
Esto lo conseguiremos con la siguiente fórmula:
=MAX(FRECUENCIA(SI(I3#>0;COLUMNA(I3#));SI(I3#=0;COLUMNA(I3#))))
Al trabajar con Microsoft 365, y sobre fórmulas matriciales desbordadas, adquiere la notación correspondiente con las almohadillas o numerales #
Además no hace falta ejecutarla matricialmente presionando Ctrl Mayusc Enter.
La fórmula se apoya en la función FRECUENCIA, identificando, por número de columna, dónde se encuentran los ceros (Meses de NO matriculación) y los valores positivos (Meses de SI matriculación).
Esto es muy interesante, ya que FRECUENCIA permite contar el número de los resultados que se encuentran dentro de un rango.. justo lo que necesitamos.
Veamos un ejemplo del comportamiento de FRECUENCIA...
En la imagen, en la columna DATOS se replica el resultado del condicional anterior:
SI(rng_datos>0;COLUMNA(rang_datos))
Mientras que en la columna GRUPOS vemos el resultado de:
SI(rng_datos=0;COLUMNA(rang_datos))
A la derecha el resultado de FRECUENCIA y una breve explicación de lo que vemos...
Poco más queda por hacer, salvo recuperar el valor máximo de entre esos conteos:
=MAX(FRECUENCIA(datos;grupo))
Obteniendo el resultado buscado!!!
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.