Reporte fechas por H1 y H2

Jaime Franco Jimenez
6 dic 2023
3 Min. de lectura

Tenemos una columna de fechas, a partir del conjunto de datos de la columna A, debemos de crear la tabla en la columna Respuesta.

1H = Enero a Junio y 2H = Julio a Diciembre. Ordénalo en función del año seguido de la mitad.

Vamos a resolver otro desafío de Excel BI.

En la celda C2, usamos LET, creamos una variable, nos traemos el mes del rango A2:A20.

=LET(xx;MES(A2:A20)

Creamos otra variable, y, nos traemos el año del rango A2:A20.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20)

Creamos otra variable, ponemos APILARH, como argumento matriz1, usamos ORDENAR, como argumento matriz, traemos los años únicos, concatenamos con un espacio, y, concatenamos con H1 entre comillas dobles.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;APILARH(ORDENAR(UNICOS(yy))&" "&"H1"

Como argumento matriz2, es la misma expresión, pero, cambiamos H1 por H2.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;APILARH(ORDENAR(UNICOS(yy))&" "&"H1";ORDENAR(UNICOS(yy))&" "&"H2"))

Usamos ENCOL, donde solo usamos el argumento matriz que es la expresión anterior.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;ENCOL(APILARH(ORDENAR(UNICOS(yy))&" "&"H1";ORDENAR(UNICOS(yy))&" "&"H2"));a)

Ya tenemos preparados los años, de manera, que aparezca cada año dos veces, el primer año, aparece con H1, y, el segundo año aparece con H2.

Creamos una variable, transponemos la variable “a”.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;ENCOL(APILARH(ORDENAR(UNICOS(yy))&" "&"H1";ORDENAR(UNICOS(yy))&" "&"H2"));b;TRANSPONER(a):b)

Creamos otra variable, usamos la funcion UNICOS, como argumento matriz, usamos IZQUIERDA, como argumento texto, ponemos la variable “b”, como argumento numero de caracteres, ponemos 4 y multiplicamos por 1, para que nos devuelva los años en formato de número, como argumento by_col, seleccionamos VERDADERO, es decir, que nos devuelva los valores únicos por columnas.

Vamos a tener los años únicos.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;ENCOL(APILARH(ORDENAR(UNICOS(yy&" "&"H1"));ORDENAR(UNICOS(yy&" "&"H2"))));b;TRANSPONER(a);c;UNICOS(IZQUIERDA(b;4)*1;VERDADERO);c)

Creamos otra variable, abrimos paréntesis, ponemos la variable “xx”, y, comparamos con mayor o igual a 1, cerramos paréntesis, ponemos el símbolo de asterisco, abrimos otro paréntesis, volvemos a poner la variable “xx”, y, comparamos con menor o igual a 6, cerramos paréntesis, ponemos el símbolo de asterisco, abrimos otro paréntesis, ponemos la variable “yy” e igualamos a la variable “c”.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;ENCOL(APILARH(ORDENAR(UNICOS(yy&" "&"H1"));ORDENAR(UNICOS(yy&" "&"H2"))));b;TRANSPONER(a);c;UNICOS(IZQUIERDA(b;4)*1;VERDADERO);d;(xx>=1)*(xx<=6)*(yy=c);d)

Tenemos una matriz desbordada de tres columnas con 1 donde hay coincidencia, y, 0 donde no la hay.

Creamos otra variable, abrimos paréntesis, ponemos la variable “xx” comparamos con mayor o igual a 7, cerramos paréntesis, ponemos el símbolo de asterisco, abrimos otro paréntesis, comparamos la variable “xx” con menor o igual a 12, cerramos paréntesis, ponemos el símbolo de asterisco, abrimos paréntesis, comparamos la variable “yy” con la variable “c”.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;ENCOL(APILARH(ORDENAR(UNICOS(yy))&" "&"H1";ORDENAR(UNICOS(yy))&" "&"H2"));b;TRANSPONER(a);c;UNICOS(IZQUIERDA(b;4)*1;VERDADERO);d;(xx>=1)*(xx<=6)*(yy=c);e;(xx>=7)*(xx<=12)*(yy=c);e)

Volvemos a obtener una matriz desbordada de tres columnas, con 1 donde hay coincidencia, y, 0 donde no la hay.

Creamos una variable, usamos APILARH, como argumento matriz1, ponemos la variable “d”, como argumento matriz2, ponemos la variable “e”.

Creamos otra variable, usamos el condicional SI, preguntamos si la variable “f” es igual a 1, que nos devuelva el rango A2:A20, en caso contrario, que devuelva blanco.

Tenemos las fechas de los tres primeros años que corresponden a los meses entre 1 y 6, y, las fechas de los tres primeros años, que corresponden a los meses entre 7 y 12.

Creamos otra variable, usamos BYCOL, como argumento array, ponemos la variable “g”, como argumento funcion, ponemos LAMBDA, creamos una variable, usamos MIN, y, como argumento ponemos la variable “x”.

=LET(xx;MES(A2:A20);yy;AÑO(A2:A20);a;ENCOL(APILARH(ORDENAR(UNICOS(yy&" "&"H1"));ORDENAR(UNICOS(yy&" "&"H2"))));b;TRANSPONER(a);c;UNICOS(IZQUIERDA(b;4)*1;VERDADERO);d;(xx>=1)*(xx<=6)*(yy=c);e;(xx>=7)*(xx<=12)*(yy=c);f;APILARH(d;e);g;SI(f;A2:A20;"");h;BYCOL(g;LAMBDA(x;MIN(x)));h)

Tenemos la fecha mínima para cada año y para cada periodo.

Volvemos a la expresión, debemos de ordenar las fechas para que queden ordenadas por años, para ello, antes de BYCOL, ponemos ORDENAR, como argumento matriz, es la funcion BYCOL, ignoramos el argumento ordenar índice, ignoramos el argumento criterio ordenación, como argumento por_col, seleccionamos VERDADERO, para que ordene por columna.

Ya tenemos las fechas ordenadas por años.

Antes de la funcion ORDENAR, usamos APILARV, como argumento matriz1, es la funcion ORDENAR, como argumento matriz2, es la misma expresión, pero, cambiamos MIN por MAX.

Tenemos tanto las fechas mínimas como las fechas máximas de cada periodo.

Usamos el argumento calculo de LET, ponemos APILARH, como argumento matriz1, ponemos la variable “a”, como argumento matriz2, transponemos la variable “h”.

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Reporte fechas por H1 y H2

Entradas recientes

Comentarios