Mínimo máximo de cada mes

Tenemos una columna con fechas desde el 01/01/2023 hasta el día 31/12/2023, y, una segunda con una cantidad asignada a cada fecha.

Nos solicitan crear un reporte donde en una primera columna aparezcan los meses únicos, en la segunda columna el valor mínimo de cada mes, en la tercera columna, debe de aparecer las fechas que corresponden con cada valor mínimo, en la siguiente columna, debe de aparecer el valor máximo de cada mes, y, en la ultima columna, las fechas que corresponden con dicho valor máximo.

El modelo debe de quedar como sigue:

En la celda D2, usamos LET, creamos una variable, la llamamos meses, usamos la función UNICOS, como argumento usamos la función MES, y, como argumento de MES, seleccionamos el rango A2:A366, vamos a obtener los meses únicos.

Creamos otra variable, la llamamos min_max, usamos la función MAP, como argumento array, ponemos la variable meses, ponemos LAMBDA, y, creamos una variable.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x

Como argumento función de LAMBDA, usamos MIN, como argumento usamos FILTRAR, como argumento array, seleccionamos el rango B2:B366, como argumento include, usamos la función MES como argumento de MES, seleccionamos el rango A2:A366 e igualamos a la variable “x”.

Con la función FILTRAR, vamos a obtener las cantidades para cada mes, y, con la función MIN, nos va a devolver el valor mínimo para cada mes.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))

Concatenamos con un blanco, volvemos a concatenar con misma función MIN, pero, cambiamos MIN por MAX.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));min_max)

Tenemos una matriz desbordada de dos columnas, en la primera columna tenemos el valor mínimo para cada mes, y, en la segunda columna, el valor máximo.

Creamos otra variable, y, transponemos la variable min_max.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);a)

Creamos otra variable, usamos TEXTOANTES, como argumento texto, ponemos la variable “a”, como argumento delimitador, ponemos un espacio, multiplicamos por 1, para que el resultado este en formato de número.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;TEXTOANTES(a;" ")*1;b)

Tenemos los valores mínimos para cada mes.

Preguntamos que si el resultado de TEXTOANTES es igual a uno de los valores del rango B2:B366.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;TEXTOANTES(a;" ")*1=B2:B366

Como argumento valor si verdadero, preguntamos si la variable meses, pero le aplicamos la funcion ENFILA, es igual al mes del rango A2:A366, como argumento valor si verdadero del segundo SI, seleccionamos el rango A2:A366, como argumento valor si verdadero del segundo SI, ponemos blanco, como argumento valor si falso del primer SI, ponemos otro blanco.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");b)

Obtenemos una matriz desbordada de 12 columnas, una por mes, en cada columna tenemos las fechas donde hay coincidencia.

Antes del condicional SI, usamos BYCOL, como argumento array es el condicional SI, ponemos LAMBDA, creamos una variable, usamos UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, como argumento texto1, ponemos la variable “x”.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x)));b)

Obtenemos las fecha o fechas que corresponden con el valor mínimo para cada mes.

Antes de BYCOL, usamos ENCOL.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;ENCOL(BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));b)

Tenemos las fechas en formato general en vertical.

Creamos otra variable, es la misma expresión que para la variable “b”, pero, cambiamos TEXTOANTES por TEXTODESPUES.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;ENCOL(BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));c;ENCOL(BYCOL(SI(TEXTODESPUES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));c)

Obtenemos las fechas para el valor máximo para cada fecha.

Creamos otra variable, usamos APILARH, como argumento matriz1, ponemos la variable “b”, como argumento matriz2, ponemos la variable “c”, vamos a obtener una matriz desbordada de dos columnas, en la primera columna tenemos las fechas que corresponden con el valor mínimo, y, en la segunda columna, las fechas que corresponden con el valor máximo.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;ENCOL(BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));c;ENCOL(BYCOL(SI(TEXTODESPUES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));d;APILARH(b;c);d)

Creamos otra variable, usamos APILARH, como argumento matriz1, usamos DIVIDIRTEXTO, como argumento matriz, ponemos la variable “d”, como argumento delimitador de columna, entre comillas dobles, ponemos coma, y, multiplicamos por 1, para que nos devuelva la fecha en formato de fecha.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;ENCOL(BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));c;ENCOL(BYCOL(SI(TEXTODESPUES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));d;APILARH(b;c);e;APILARH(DIVIDIRTEXTO(d;",")*1

Como argumento matriz2, usamos TEXTODESPUES, como argumento texto, ponemos la variable “d”, como argumento delimitador, entre comillas dobles, ponemos coma, y, volvemos a multiplicar por 1.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;ENCOL(BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));c;ENCOL(BYCOL(SI(TEXTODESPUES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));d;APILARH(b;c);e;APILARH(DIVIDIRTEXTO(d;",")*1;TEXTODESPUES(d;",")*1);e)

Tenemos una matriz desbordada de cuatro columnas, en la primera columna tenemos las fechas que corresponden con el valor mínimo, en la segunda columna, las fechas que corresponden con el valor máximo, en la tercera columna, tenemos la o las segundas fechas que corresponden con el valor mínimo, y, en la cuarta columna, la o las segundas fechas que corresponden con el valor máximo, pero, obtenemos un error donde no hay fechas que mostrar.

En la funcion TEXTODESPUES, usamos SI.ERROR, y, ponemos blanco en caso de error.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;ENCOL(BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));c;ENCOL(BYCOL(SI(TEXTODESPUES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));d;APILARH(b;c);e;APILARH(DIVIDIRTEXTO(d;",")*1;SI.ERROR(TEXTODESPUES(d;",")*1;""));e)

Por último, debemos de ordenar las columnas, usamos el argumento cálculo de LET, usamos APILARH, como argumento matriz1, ponemos la variable meses, como argumento matriz2, usamos DIVIDIRTEXTO, como argumento texto, ponemos la variable min_max, como argumento delimitador de columna, ponemos un espacio, como argumento matriz3, usamos ELEGIRCOLS, como argumento matriz, ponemos la variable “e”, como argumento numero columna1, ponemos 1, como argumento numero columna2, ponemos 3, es decir, que nos devuelva las columnas 1 y 3, como argumento matriz4, usamos TEXTODESPUES, como argumento texto, ponemos la variable min_max, como argumento delimitador, ponemos espacio, como argumento matriz5, usamos ELEGIRCOLS, como argumento matriz, ponemos la variable “e”, como argumento numero columna1, ponemos 2, como argumento numero columna2, ponemos 4, cerramos paréntesis.

=LET(meses;UNICOS(MES(A2:A366));min_max;MAP(meses;LAMBDA(x;MIN(FILTRAR(B2:B366;MES(A2:A366)=x))&" "&MAX(FILTRAR(B2:B366;MES(A2:A366)=x))));a;TRANSPONER(min_max);b;ENCOL(BYCOL(SI(TEXTOANTES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));c;ENCOL(BYCOL(SI(TEXTODESPUES(a;" ")*1=B2:B366;SI(ENFILA(meses)=MES(A2:A366);A2:A366;"");"");LAMBDA(x;UNIRCADENAS(",";VERDADERO;x))));d;APILARH(b;c);e;APILARH(DIVIDIRTEXTO(d;",")*1;SI.ERROR(TEXTODESPUES(d;",")*1;""));APILARH(meses;DIVIDIRTEXTO(min_max;" ");ELEGIRCOLS(e;1;3);TEXTODESPUES(min_max;" ");ELEGIRCOLS(e;2;4)))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco