Fechas mínimas y máximas

Jaime Franco Jimenez
27 feb 2024
3 Min. de lectura

Para el siguiente ejemplo, tenemos una columna con fechas.

Debemos generar las fechas mínimas y máximas para diferentes combinaciones de años y meses.

En una celda, usamos LET, creamos una variable, usamos la funcion PIVOTARPOR, como argumento row fields, usamos la funcion MES, como argumento seleccionamos el rango A1:A25, como argumento col fields, usamos la funcion AÑO, como argumento seleccionamos el rango A1:A25, como argumento values, seleccionamos el rango A1:A25, como argumento funcion, usamos CONCAT, sin paréntesis de apertura no de cierre, omitimos el argumento field headers, como argumento row total Depth, ponemos 0, omitimos el argumento row sort order, como argumento col total Depth, ponemos 0.

Probamos variable.

Tenemos en filas los números de meses, en columnas, los años, y, en el centro del modelo las fechas coincidentes.

Pero, las fechas aparece unidas, y, en formato general, y, queremos ver las fechas separadas, por lo que volvemos a la expresión, vamos al argumento values, antes de A1:A15, usamos la funcion TEXTO, como argumento valor, es el rango A1:A25, como argumento formato, entre comillas dobles, ponemos “dd/mm/aaaa”, y, concatenamos con un espacio.

=PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0)

Ya podemos ver las fechas correctamente, y, separadas unas de otras.

Vamos a excluir la última fila, y, la última columna porque tenemos error, para ello, antes de PIVOTARPOR, usamos EXCLUIR, como argumento matriz, es la funcion PIVOTARPOR, como argumento filas, ponemos -1, como argumento columnas, ponemos -1.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);a)

Vamos a quedarnos con las fechas, para ello, creamos otra variable, usamos la funcion ELEGIRCOLS, porque vamos a elegir las columnas que contienen fechas, como argumento matriz, ponemos la variable “a”, como argumento número de fila1, usamos una constante de matriz, abrimos unas llaves, ponemos el número 2, barra inclinada, ponemos el número 3, barra inclinada, ponemos el número 4, cerramos llaves, y, paréntesis.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;ELEGIRCOLS(a;{2\3\4});b)

Tenemos solo las fechas.

Aunque en la primera fila, tenemos los años, pues, los quitamos con la funcion EXCLUIR.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;EXCLUIR(ELEGIRCOLS(a;{2\3\4});1);b)

Creamos otra variable, y, transponemos la variable “b”.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;EXCLUIR(ELEGIRCOLS(a;{2\3\4});1);c;TRANSPONER(b);c)

Antes de TRANSPONER, usamos ENCOL para que las fechas queden en vertical.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;EXCLUIR(ELEGIRCOLS(a;{2\3\4});1);c;ENCOL(TRANSPONER(b));c)

Creamos otra variable, usamos la funcion FILTRAR, como argumento array, ponemos la variable “c”, como argumento Include, ponemos la variable “c” e igualamos a desigual a blanco.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;EXCLUIR(ELEGIRCOLS(a;{2\3\4});1);c;ENCOL(TRANSPONER(b));d;FILTRAR(c;c<>"");d)

Tenemos las fechas en vertical donde en cada celda, tenemos las fechas que corresponden con un mes y un año.

Creamos otra variable, usamos la funcion REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “d”, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

Como argumento cálculo de LAMBDA, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos la funcion DIVIDIRTEXTO, como argumento texto, ponemos la variable “y”, como argumento delimitador de columna, entre comillas dobles, ponemos un espacio, como el resultado nos no lo va a devolver en formato de texto, multiplicamos por 1.

Tenemos una matriz desbordada de cinco columnas, donde tenemos las fechas separadas, y, error donde no hay fecha que separar.

Usamos la funcion SI.ERROR, y, ponemos blanco donde hay error.

Quitamos la primera fila que está en blanco, para ello, usamos la funcion EXCLUIR, como argumento matriz, es la funcion SI.ERROR, como argumento filas, ponemos 1.

Creamos otra variable, usamos la funcion BYROW, como argumento array, ponemos la variable “e”, como argumento funcion, ponemos LAMBDA, declaramos una variable, como argumento calculo de LAMBDA, usamos TEXTO, como argumento valor, usamos la funcion MIN, como argumento ponemos la variable “x”, como argumento formato, entre comillas dobles, ponemos “dd/mm/aaaa”, concatenamos con un espacio, concatenamos con la funcion TEXTO, como argumento valor, usamos la funcion MAX, como argumento ponemos la variable “x”, como argumento formato, entre comillas dobles, ponemos “dd/mm/aaaa”.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;EXCLUIR(ELEGIRCOLS(a;{2\3\4});1);c;ENCOL(TRANSPONER(b));d;FILTRAR(c;c<>"");e;EXCLUIR(SI.ERROR(REDUCE("";d;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;" ")*1)));"");1);f;BYROW(e;LAMBDA(x;TEXTO(MIN(x);"dd/mm/aaaa")&" "&TEXTO(MAX(x);"dd/mm/aaaa")));f)

Ya tenemos las fechas mínimas y máximas para cada celda.

Usamos el argumento cálculo de LET, usamos la funcion APILARH, como argumento matriz1, usamos la funcion DERECHA, como argumento texto, ponemos la variable “f”, como argumento numero de caracteres, ponemos 4, nos vamos a traer el año de cada celda, como argumento matriz2, usamos la función MES, como argumento usamos la funcion ELEGIRCOLS, como argumento matriz, ponemos la variable “e”, como argumento numero de columna1, ponemos 1, de esta manera, extraemos el mes de la primera columna de la variable “e”, ya que el mes de cada celda es el mismo, como argumento matriz3, ponemos la variable “f”.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;EXCLUIR(ELEGIRCOLS(a;{2\3\4});1);c;ENCOL(TRANSPONER(b));d;FILTRAR(c;c<>"");e;EXCLUIR(SI.ERROR(REDUCE("";d;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;" ")*1)));"");1);f;BYROW(e;LAMBDA(x;TEXTO(MIN(x);"dd/mm/aaaa")&" "&TEXTO(MAX(x);"dd/mm/aaaa")));APILARH(DERECHA(f;4);MES(ELEGIRCOLS(e;1));f))

Aceptamos, y, ya lo tenemos.

Volvemos al argumento cálculo de LET, antes de APILARH, usamos la funcion APILARV, como argumento matriz1, usamos una constante de matriz, abrimos unas llaves, entre comillas dobles, ponemos Año, barra inclinada, entre comillas dobles, ponemos Mes, barra inclinada, entre comillas dobles, ponemos Mínimo_Maximo, cerramos llaves, como argumento matriz2, es la funcion APILARH.

Lo que vamos a hacer es poner un encabezado.

=LET(a;EXCLUIR(PIVOTARPOR(MES(A1:A25);AÑO(A1:A25);TEXTO(A1:A25;"dd/mm/aaaa")&" ";CONCAT;;0;;0);-1;-1);b;EXCLUIR(ELEGIRCOLS(a;{2\3\4});1);c;ENCOL(TRANSPONER(b));d;FILTRAR(c;c<>"");e;EXCLUIR(SI.ERROR(REDUCE("";d;LAMBDA(x;y;APILARV(x;DIVIDIRTEXTO(y;" ")*1)));"");1);f;BYROW(e;LAMBDA(x;TEXTO(MIN(x);"dd/mm/aaaa")&" "&TEXTO(MAX(x);"dd/mm/aaaa")));APILARV({"Año"\"Mes"\"Minimo_Maximo"};APILARH(DERECHA(f;4);MES(ELEGIRCOLS(e;1));f)))

Aceptamos, y, lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Fechas mínimas y máximas

Entradas recientes

Comentarios