Vacaciones por nombres

Jaime Franco Jimenez
24 dic 2023
4 Min. de lectura

Tenemos una serie de nombres junto con sus vacaciones.

Debemos de crear un reporte donde en la primera columna debe de aparecer los nombres, en la segunda columna, el numero de vacaciones, por ejemplo, si Smith, tomo vacaciones en dos fechas, debe de aparecer 1 y 2, en una tercera columna debe de aparecer el inicio de las vacaciones, en la siguiente columna, el final de las vacaciones, y, en una ultima columna, el numero de dias de vacaciones.

Si la fecha de vacaciones o la fecha de finalización es un fin de semana, debe omitirse.

El modelo debe de quedar como sigue:

En la celda D2, usamos LET, creamos una variable, y, seleccionamos el modelo, probamos variable.

=LET(xx;B2:B20;xx)

Creamos otra variable, seleccionamos el rango A2:A20.

=LET(xx;B2:B20;yy;A2:A20;yy)

Creamos otra variable, abrimos un paréntesis, usamos TEXTO, como argumento valor, es la variable “xx”, como argumento formato, entre comillas dobles, ponemos “dddd” e igualamos a sábado, cerramos paréntesis.

=LET(xx;B2:B20;a;(TEXTO(xx;"dddd")="sábado")

Ponemos el signo de mas (+), abrimos otro paréntesis, volvemos a usar TEXTO pero igualamos a domingo.

=LET(xx;B2:B20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");a)

Es decir, preguntamos si el resultado de la función TEXTO es igual a sábado o domingo.

Obtenemos una matriz desbordada en vertical con 1 donde hay coincidencia, y, 0 dono no lo es.

Creamos otra variable, nos interesan los valores ceros, que quiere decir que ni es sábado, ni es domingo, usamos FILTRAR, como argumento array, ponemos la variable “xx”, como argumento include, ponemos la variable “a” e igualamos a 0.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);b)

Tenemos las fechas donde no se incluyen sábados y domingos.

Creamos otra variable, nos quedamos con el mes de la variable “b”.

=LET(xx;A2:B20;a;BYROW(TEXTO(xx;"dddd");LAMBDA(x;O(x="sábado";x="domingo")));b;FILTRAR(xx;a=FALSO);c;MES(b);c)

Nos quedamos con los valores únicos.

=LET(xx;A2:B20;a;BYROW(TEXTO(xx;"dddd");LAMBDA(x;O(x="sábado";x="domingo")));b;FILTRAR(xx;a=FALSO);c;UNICOS(MES(b;;-1));c)

Usamos ENFILA, y, como argumento es la función UNICOS, vamos a obtener los años únicos en horizontal.

Creamos otra variable, preguntamos que, si el mes de la ultima columna de la variable “b”, por lo que usamos TOMAR es igual a la variable “c”, que nos devuelva dicha columna, en caso contrario, que devuelva blanco.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)))

Tenemos una matriz desbordada en horizontal con los meses únicos.

Creamos otra variable, preguntamos que, si el mes de la variable “b” es igual a la variable “c”, que devuelva la variable “b”, en caso contrario, que devuelva un texto en blanco.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)));d;SI(MES(b)=c;b;"");d)

Obtenemos una matriz desbordada con una columna para cada mes, con las fechas que corresponden a cada mes.

Creamos otra variable, usamos BYCOL, como argumento array, ponemos la variable “b”, como argumento función, ponemos LAMBDA, declaramos una variable, como argumento calculo de LAMBDA, usamos K.ESIMO.MENOR, como argumento matriz, ponemos la variable “x”, como argumento K, ponemos 1, vamos a obtener la fecha mínima de cada mes.

Creamos otra variable, es la misma que para la variable “e”, pero, cambiamos K.ESIMO.MENOR por K.ESIMO.MAYOR.

Tenemos la fecha máxima para cada mes.

Creamos otra variable, usamos BUSCARX, como argumento valor buscado, ponemos la variable “e”, como argumento matriz de búsqueda, tomamos la variable “xx”, como argumento matriz devuelta, tomamos la variable “yy”.

Obtenemos el nombre que corresponde con cada mes.

Creamos otra variable, usamos APILARV, como argumento matriz1, ponemos la variable “g”, como argumento matriz2, ponemos la variable “e”, como argumento matriz3, ponemos la variable “f”, como argumento matriz4, restamos la variable “f” menos la variable “e”, y, sumamos 1.

Vamos a obtener el nombre para cada mes, la fecha de inicio de vacaciones, la fecha de fin de vacaciones, y, la diferencia de dias.

Usamos TRANSPONER.

Nos queda una columna, que es el número de vacaciones que corresponde a cada nombre.

Creamos otra variable, usamos ENFILA, como argumento usamos UNICOS y como argumento de UNICOS, tomamos la primera columna de la variable “h”, lo igualamos a la primera columna de la variable “h”, pues, esta expresión es el argumento prueba lógica del condicional SI.

=LET(xx;A2:B20;a;BYROW(TEXTO(xx;"dddd");LAMBDA(x;O(x="sábado";x="domingo")));b;FILTRAR(xx;a=FALSO);c;ENFILA(UNICOS(MES(TOMAR(b;;-1))));d;SI(MES(TOMAR(b;;-1))=c;TOMAR(b;;-1);"");e;BYCOL(d;LAMBDA(x;K.ESIMO.MENOR(x;1)));f;BYCOL(d;LAMBDA(x;K.ESIMO.MAYOR(x;1)));g;BUSCARX(e;TOMAR(b;;-1);TOMAR(b;;1));h;TRANSPONER(APILARV(g;e;f;f-e+1));i;SI(ENFILA(UNICOS(TOMAR(h;;1)))=TOMAR(h;;1)

Como argumento valor si verdadero, ponemos 1, como argumento valor si falso, ponemos 0.

Obtenemos una matriz desbordada de tres columnas, donde aparece 1 donde hay coincidencia, y, 0 donde no la hay, en otras palabras, tenemos la secuencia de números para cada nombre.

Creamos otra variable, usamos BYCOL, como argumento array, ponemos la variable “i”, como argumento función, ponemos LAMBDA, declaramos una variable, y, sumamos dicha variable.

Obtenemos la secuencia a crear.

Creamos otra variable, usamos REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “j”, como argumento función, ponemos LAMBDA, y, declaramos dos variables, como argumento cálculo de LAMBDA, usamos APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos SECUENCIA, como argumento filas, ponemos la variable “y”.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)));d;SI(MES(b)=c;b;"");e;BYCOL(d;LAMBDA(x;K.ESIMO.MENOR(x;1)));f;BYCOL(d;LAMBDA(x;K.ESIMO.MAYOR(x;1)));g;BUSCARX(e;xx;yy);h;TRANSPONER(APILARV(g;e;f;f-e+1));i;SI(ENFILA(UNICOS(TOMAR(h;;1)))=TOMAR(h;;1);1;0);j;BYCOL(i;LAMBDA(x;SUMA(x)));k;REDUCE("";j;LAMBDA(x;y;APILARH(x;SECUENCIA(y))));k)

Tenemos una matriz desbordada de cuatro columnas, donde podemos ver que hemos creado las secuencias que necesitamos, y, error donde no hay secuencia que crear.

Vamos a quitar la primera columna que es el argumento valor inicial, para ello, usamos EXCLUIR.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)));d;SI(MES(b)=c;b;"");e;BYCOL(d;LAMBDA(x;K.ESIMO.MENOR(x;1)));f;BYCOL(d;LAMBDA(x;K.ESIMO.MAYOR(x;1)));g;BUSCARX(e;xx;yy);h;TRANSPONER(APILARV(g;e;f;f-e+1));i;SI(ENFILA(UNICOS(TOMAR(h;;1)))=TOMAR(h;;1);1;0);j;BYCOL(i;LAMBDA(x;SUMA(x)));k;EXCLUIR(REDUCE("";j;LAMBDA(x;y;APILARH(x;SECUENCIA(y))));;1);k)

Usamos ENCOL e ignoramos blancos y errores.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)));d;SI(MES(b)=c;b;"");e;BYCOL(d;LAMBDA(x;K.ESIMO.MENOR(x;1)));f;BYCOL(d;LAMBDA(x;K.ESIMO.MAYOR(x;1)));g;BUSCARX(e;xx;yy);h;TRANSPONER(APILARV(g;e;f;f-e+1));i;SI(ENFILA(UNICOS(TOMAR(h;;1)))=TOMAR(h;;1);1;0);j;BYCOL(i;LAMBDA(x;SUMA(x)));k;ENCOL(EXCLUIR(REDUCE("";j;LAMBDA(x;y;APILARH(x;SECUENCIA(y))));;1);3);k)

Ya tenemos nuestra secuencia.

Pero esta secuencia no es correcta, porque para Smith, debe de aparecer 1, y, 2, y, vemos que aparece 1, y, 1.

Vayamos a la expresión, donde quitamos ENCOL.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)));d;SI(MES(b)=c;b;"");e;BYCOL(d;LAMBDA(x;K.ESIMO.MENOR(x;1)));f;BYCOL(d;LAMBDA(x;K.ESIMO.MAYOR(x;1)));g;BUSCARX(e;xx;yy);h;TRANSPONER(APILARV(g;e;f;f-e+1));i;SI(ENFILA(UNICOS(TOMAR(h;;1)))=TOMAR(h;;1);1;0);j;BYCOL(i;LAMBDA(x;SUMA(x)));k;EXCLUIR(REDUCE("";j;LAMBDA(x;y;APILARH(x;SECUENCIA(y))));;1);k)

Obtenemos:

Cuando usamos ENCOL, se transpone de izquierda a derecha, es decir, viendo el modelo, seria 1, 1, 1, 2, 2, y 3.

Vamos a usar TRANSPONER antes de EXCLUIR.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)));d;SI(MES(b)=c;b;"");e;BYCOL(d;LAMBDA(x;K.ESIMO.MENOR(x;1)));f;BYCOL(d;LAMBDA(x;K.ESIMO.MAYOR(x;1)));g;BUSCARX(e;xx;yy);h;TRANSPONER(APILARV(g;e;f;f-e+1));i;SI(ENFILA(UNICOS(TOMAR(h;;1)))=TOMAR(h;;1);1;0);j;BYCOL(i;LAMBDA(x;SUMA(x)));k;TRANSPONER(EXCLUIR(REDUCE("";j;LAMBDA(x;y;APILARH(x;SECUENCIA(y))));;1));k)

Obtenemos:

Ahora, si usamos ENCOL, obtenemos la secuencia correcta.

=LET(xx;B2:B20;yy;A2:A20;a;(TEXTO(xx;"dddd")="sábado")+(TEXTO(xx;"dddd")="domingo");b;FILTRAR(xx;a=0);c;ENFILA(UNICOS(MES(b)));d;SI(MES(b)=c;b;"");e;BYCOL(d;LAMBDA(x;K.ESIMO.MENOR(x;1)));f;BYCOL(d;LAMBDA(x;K.ESIMO.MAYOR(x;1)));g;BUSCARX(e;xx;yy);h;TRANSPONER(APILARV(g;e;f;f-e+1));i;SI(ENFILA(UNICOS(TOMAR(h;;1)))=TOMAR(h;;1);1;0);j;BYCOL(i;LAMBDA(x;SUMA(x)));k;ENCOL(TRANSPONER(EXCLUIR(REDUCE("";j;LAMBDA(x;y;APILARH(x;SECUENCIA(y))));;1));3);k)

Usamos el argumento cálculo de LET, usamos APILARH, como argumento matriz1, tomamos la primera columna de la variable “h”, como argumento matriz2, ponemos la variable “k”, como argumento matriz3, tomamos las tres ultimas columnas de la variable “h”.

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Vacaciones por nombres

Entradas recientes

Comentarios