Ejemplos10

Vamos a ver un ejemplo de la función EXPANDIR, y, FILTRAR, donde de nuevo, vamos a volver a usar la función INDIRECTO, para referirnos a una columna de la tabla.

El ejemplo se trata de obtener la cantidad, precio, y, total de uno de los siguientes encabezados:

·        Fecha

·        Provincia

·        Centro

·        Producto

Vamos a empezar, e, iremos comprendiendo el ejemplo.

En la celda I2, voy a crear una lista desplegable con los 4 primeros encabezados, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.

Desplegamos permitir, y, seleccionamos lista, y, en la ventana origen, seleccionamos los cuatro primeros encabezados.

Seleccionamos un encabezado.

Vemos que tenemos el encabezado de fecha, pero cuando elija dicho encabezado, no quiero que se carguen las fechas, sino los años únicos, decir, que, con la nueva actualización de validación de datos, cuando seleccionamos una columna donde hay valores repetidos, en la lista desplegable, solo aparecerán los valores únicos.

Para ello, en la celda N2, voy a preguntar que si el valor de la celda I2, es igual a fecha, que me devuelva los años de la columna fecha, donde usare la función AÑO, pero no voy a quitar duplicados, porque la lista desplegable, ya los quita, si no tuviéramos esta actualización, si tendríamos que quitarlos.

=SI(I2="Fecha";AÑO(Table1[Fecha]);"")

En la celda O2, voy a usar la función INDIRECTO, para traerme el valor del encabezado seleccionado, para ello, ponemos el signo igual signo igual, ponemos la función INDIRECTO, como argumento referencia, abrimos un paréntesis, entre comillas dobles, ponemos le nombre de la tabla, junto con un corchete de apertura.

=INDIRECTO("Table1["

Concatenamos con la celda I2.

=INDIRECTO("Table1[" & I2

Concatenamos con un corchete de cierre, y, cerramos paréntesis.

=INDIRECTO("Table1[" & I2 & "]")

Aceptamos.

Ya tenemos rescatada la columna.

En la celda J2, creo otra lista desplegable con los valores de la columna rescatada, pero en este caso, en la ventana origen de lista, he de usar el condicional SI, para preguntar que, si el valor de la celda I2 es igual a Fecha, que lo rellene con los valores a partir de la celda N2, en caso contrario, que lo rellene con los valores a partir de la celda O2, en ambas celdas, uso el operador de rango derramado (#) para que siempre se rellene desde N2 u O2 hasta la última celda ocupada.

Aceptamos.

Si probamos, y, elegimos fecha veremos que queda rellena con los años únicos.

Pero, si seleccionamos un encabezado distinto a fecha, se rellenan con los valores de dicho encabezado.

Lo siguiente es con la función FILTRAR, filtrar las columnas cantidad, precio, y, total, lo haremos en la celda I6, como argumento array, seleccionamos las columnas desde cantidad a total.

=FILTRAR(Table1[[Cantidad]:[Total]

Como argumento include, volvemos a usar INDIRECTO, es la misma expresion usada anteriormente, para rescatar el encabezado seleccionado, e, igualarlo al valor de la celda J2.

=FILTRAR(Table1[[Cantidad]:[Total]];INDIRECTO("Table1["&I2&"]")=J2

Cerramos paréntesis y aceptamos.

=FILTRAR(Table1[[Cantidad]:[Total]];INDIRECTO("Table1["&I2&"]")=J2)

Y tenemos filtrado el modelo para el valor de la celda J2.

Voy a rellenar el argumento si vacío, con un texto en blanco.

=FILTRAR(Table1[[Cantidad]:[Total]];INDIRECTO("Table1["&I2&"]")=J2;"")

Ahora mismo, la función FILTRAR, está filtrando por el valor de la celda J2, pero si elegimos fecha, y, un año, no nos devuelve nada.

Cosa que es correcto, porque al seleccionar la columna fecha en la lista desplegable, vemos que la siguiente lista se rellena con los años, pero está comparando la columna fecha con el valor de J2, donde hay un año, pero en la columna fecha, solo hay fechas, no años, por lo que no nos presenta nada, es correcto.

Por lo que debo de añadir una nueva condición a la función FILTRAR.

Voy a poner la condición a añadir a la función FILTRAR en una celda, donde después del signo igual, pongo la función AÑO, después INDIRECTO como lo hemos hecho anteriormente.

=AÑO(INDIRECTO("Table1["&I2&"]"))

Vemos que obtenemos la columna fecha, pero solo los años.

Ya sabemos que no tenemos que quitar duplicados, porque la lista desplegable lo hará.

Pues, esta es la segunda condición para añadir en la función FILTRAR.

Lo hare con un condicional SI, he de modificar el argumento include de la función FILTRAR, donde pregunto que si el valor de I2 es diferente a Fecha.

=FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha"

Si el valor de la celda I2, es diferente a Fecha, debe de realizar la condición que ya tenemos.

=FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2

En caso contrario, aplicamos la expresión que hemos realizado antes en una celda, y, la igualamos al valor de J2.

=FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2))

Aceptamos.

Vemos como ahora, el modelo ha sido filtrado por el año seleccionado.

Si elegimos una provincia, por ejemplo, vemos como se filtra correctamente.

Bien, hemos hecho que nos devuelva tres columnas, que son cantidad, precio, y, total, pues, ahora con la función EXPANDIR, que será aplicada a la función FILTRAR, le diré que me devuelva 4 columnas, por lo que he de ignorar el argumento filas.

=EXPANDIR(FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2));;4)

Vemos que nos devuelve las tres columnas más una, con un error, porque, de momento, no la hemos rellenado.

Si no queremos ver el error, la función EXPANDIR, tiene un último argumento que es pad_with, es decir, lo que queremos que aparezca si devuelve un error, en este caso, voy a poner un texto en blanco.

=EXPANDIR(FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2));;4;"")

Pero, aun no hemos puesto los encabezados, lo haremos en la celda I5, usamos la siguiente sintaxis para traernos los encabezados desde cantidad a total.

=Table1[[#Encabezados];[Cantidad]:[Total]]

Al encabezado que tenemos en la celda I5, voy a aplicar también la función EXPANDIR, igual que antes, añado 4 columnas.

=EXPANDIR(Table1[[#Encabezados];[Cantidad]:[Total]];;4)

Tenemos el encabezado más una celda más con un error.

Pues, en el argumento pad_with, voy a señalar la celda I2, para que ponga el encabezado seleccionado.

=EXPANDIR(Table1[[#Encabezados];[Cantidad]:[Total]];;4;I2)

En la función FILTRAR anterior, en la celda I6, vamos a cambiar el argumento pad_with, por el valor de la celda J2, para que aparezca por lo que estamos filtrando.

=EXPANDIR(FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2));;4;J2)

Ahora, en la columna M, queremos que aparezca los valores del siguiente encabezado seleccionado, es decir, si elijo fecha, el siguiente encabezado es provincia, pero si elegimos producto, que es el último encabezado, debe de elegir el primer encabezado.

En la celda M6, voy a usar el condicional SI, para preguntar (prueba logica) que si el valor de la celda I2, es igual a Producto.

=SI(I2="Producto"

Como argumento valor si verdadero, que ponga el valor 1.

=SI(I2="Producto";1

En caso contrario, valor si falso, uso la función COINCIDIR, para encontrar el valor de la celda I2, en los encabezados desde fecha a producto, con una coincidencia exacta.

=SI(I2="Producto";1;COINCIDIR(I2;Table1[[#Encabezados];[Fecha]:[Producto]];0)

Pero, debo de sumar 1, para que me dé el siguiente encabezado.

=SI(I2="Producto";1;COINCIDIR(I2;Table1[[#Encabezados];[Fecha]:[Producto]];0)+1)

Aceptamos, y, tenemos la posición del siguiente encabezado seleccionado.

Ahora, con la función INDICE, voy a traerme la columna devuelta por la expresión anterior del modelo.

Como me tengo que traer una columna, voy a omitir el argumento numero de fila.

=INDICE(Table1;;SI(I2="Producto";1;COINCIDIR(I2;Table1[[#Encabezados];[Fecha]:[Producto]];0)+1))

Ahora, tengo que filtrar la función INDICE, por las mismas condiciones que hemos usado anteriormente en la función FILTRAR.

=FILTRAR(INDICE(Table1;;SI(I2="Producto";1;COINCIDIR(I2;Table1[[#Encabezados];[Fecha]:[Producto]];0)+1));SI(I2<>"Fecha";INDIRECTO("Table1[" & I2 & "]")=J2;AÑO(INDIRECTO("Table1[" & I2 & "]"))=J2))

En este caso, he elegido fecha, pues me ha devuelto la provincia correspondiente a cada fila.

Pero, no tenemos el encabezado de la ultima columna, vamos a la celda M5, usamos la función INDICE, como argumento matriz, ponemos los encabezados, omitimos el argumento numero de fila, como argumento número de columna, usamos la función COINCIDIR, como argumento texto buscado, ponemos la celda L5, como argumento matriz_tabla, ponemos los encabezados, como tipo de coincidencia, ponemos exacta, y, sumamos 1 para que nos devuelva el siguiente encabezado.

=INDICE(Table1[#Encabezados];;COINCIDIR(L5;Table1[#Encabezados];0)+1)

Ya tenemos el siguiente encabezado al seleccionado.

Seleccionar un año, y, que aparezcan los años siguientes al seleccionado

Seguimos trabajando con el modelo de datos del ejemplo anterior.

Queremos crear una lista desplegable donde aparezcan los años, después, en la celda siguiente deben de aparecer los demás años, a partir del año seleccionado, teniendo en cuenta, que si seleccionamos el ultimo año, debe de aparecer desde el primer año a un año antes hasta el último.

Vemos en el modelo, que tenemos una columna con los años.

En la celda J2, voy a crear la lista desplegable con todos los años, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.

Desplegamos permitir, y, seleccionamos lista, en la ventana origen, vamos a usar la función INDIRECTO, para traernos la columna años, como sigue:

Si desplegamos, veremos los años, ya sabemos que, en la última actualización de validación de datos, quita los duplicados, por lo que no tenemos que hacerlo nosotros.

Seleccionamos un año.

Pues a partir de la celda J3, deben de aparecer los siguientes años al seleccionado, por ejemplo, si elijo 2020, pues, solo debe de aparecer el año 2021.

Vamos a empezar trayendo los años únicos de la columna años, para ello, usamos la función UNICOS, y, como argumento seleccionamos la columna años de table1.

=UNICOS(Table1[Años])

Ahora, con la función FILTRAR, voy a filtrar la columna de años únicos, la expresión anterior, siempre que la columna de años únicos sea mayor al año seleccionado.

=FILTRAR(UNICOS(Table1[Años]);UNICOS(Table1[Años])>J2)

Aceptamos, y, vemos los años superiores al seleccionado.

Pero, que ocurre, si seleccionamos el ultimo año, pues, que nos devuelve un error de cálculo.

Entonces, cuando seleccione el ultimo año, quiero que me aparezcan todos los años, excepto, el ultimo, para ello, voy a usar el argumento si vacío, de la función FILTRAR, donde vuelvo a filtrar los años únicos, siempre que sea menor al año seleccionado.

=FILTRAR(UNICOS(Table1[Años]);UNICOS(Table1[Años])>J2;FILTRAR(UNICOS(Table1[Años]);UNICOS(Table1[Años])<J2))

Vemos como ahora, aparecen todos los años, excepto el ultimo.

Podemos ver que la expresión, UNICOS(Table1[Años]), se repite varias veces, por lo que voy a usar la función LET, donde voy a declarar una variable, y, almaceno dicha expresión, y, después, la sustituyo, que es el argumento calculo.

=LET(a;UNICOS(Table1[Años]);FILTRAR(a;a>J2;FILTRAR(a;a<J2)))

De esta manera, la sintaxis es más corta.

En la celda K1, voy a traerme los centros únicos en horizontal.

=TRANSPONER(UNICOS(Table1[Centro]))

En la celda K2, donde hemos seleccionado un año, voy a traerme los centros donde se ha vendido durante el año seleccionado.

Voy a preguntar con el condicional SI, que si la columna años, es igual al año seleccionado (J2), pues que me devuelva el centro, en caso contrario, que me devuelva un texto en blanco.

=SI(Table1[Años]=J2;Table1[Centro];"")

Obtenemos una matriz desbordada con los centros a los que corresponde el año seleccionado.

Me quedo con los valores únicos.

=UNICOS(SI(Table1[Años]=J2;Table1[Centro];""))

Y transpongo los datos.

=TRANSPONER(UNICOS(SI(Table1[Años]=J2;Table1[Centro];"")))

Lo siguiente es preguntar que, si el resultado de la expresión anterior es igual al valor de K1, donde le añado el operador de rango derramado, entonces, que una ponga una “X”, en caso contrario, que ponga un texto en blanco.

=SI(TRANSPONER(UNICOS(SI(Table1[Años]=J2;Table1[Centro];"")))=K1#;"X";"")

Vemos que ahora en vez de aparecer el centro, aparece una X, pero nos aparece una celda más con un error.

Pues, voy a usar la función SI.ERROR, para que en caso de error, ponga un texto en blanco.

=SI.ERROR(SI(TRANSPONER(UNICOS(SI(Table13[Años]=J2;Table13[Centro];"")))=K1#;"X";"");"")

Fijamos K1, y, arrastramos, y, ya lo tenemos.

=SI.ERROR(SI(TRANSPONER(UNICOS(SI(Table13[Años]=J2;Table13[Centro];"")))=$K$1#;"X";"");"")

Crear turnos a partir de una hora de comienzo, y, un número de horas

Vamos a ver un ejemplo, donde tenemos cinco trabajadores, vamos a especificar una hora de inicio, que sería la hora de comienzo del primer trabajador, en una celda vamos a indicar de cuantas horas es cada turno, y, vamos a calcular a qué hora deberían de entrar a su puesto el resto de los trabajadores, partiendo de la hora de inicio, por ejemplo, si la hora de inicio son las 9:00 AM, y cada turno es de seis horas, el trabajador2, debe de incorporarse a su puesto a las 15:00 PM.

En la celda B2, he puesto la hora de inicio para el primer trabajador, en la celda C2, he puesto las horas de cada turno.

Cuando ponemos una hora, equivale a un número decimal, en este caso, tenemos las ocho de la mañana, si pongo en formato general, veremos al número decimal al que corresponde.

Quiere decir que el numero 0,3333 equivale a las 8 de la mañana.

Si divido 8 entre 24 (horas), obtengo el mismo resultado.

Cuando ingresamos una hora, es la proporción de 24 horas, si divido 12 entre 24 (horas), vemos que nos devuelve 0,5, es decir, la mitad del día.

Bien, el turno va a ser de seis horas.

En este caso, el primer trabajador va a entrar a las 8 de la mañana, debemos de calcular la hora de entrada de los demás trabajadores.

En la celda D2, voy a dividir el valor de la celda C2 entre 24, para saber el numero decimal a que corresponde.

=C2/24

Vemos que seis horas, equivale a 0,25, es decir, a un cuarto del día.

El ejercicio va a consistir en crear el cuadro de forma dinamica, en la primera fila debe de aparecer cada fecha, pero, al lado de cada fecha debe de aparecer el nombre del día de la semana.

Debe de aparecer 12 columnas, cada dos columnas es para un horario y un trabajador.

Debemos de omitir los fines de semana.

En la celda A5, usamos LET, creamos una variable, usamos la función SECUENCIA, como argumento filas, ponemos 6, que es el horario para cubrir, omitimos el argumento columnas, como argumento inicio, ponemos la celda A2, omitimos el argumento paso.

=LET(a;SECUENCIA(6;;A2);a)

Tenemos una matriz desbordada en vertical con seis fechas empezando por la fecha de la celda A2.

Creamos otra variable, usamos la función TEXTO, como argumento valor, ponemos la variable “a”, como argumento formato, entre comillas dobles, ponemos “dddd”, para obtener el día de la semana de cada fecha.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");b)

Creamos otra variable, usamos la función MAP, como argumento array, ponemos la variable “a”, como argumento array2, ponemos la variable “b”, como argumento función, ponemos LAMBDA, y, declaramos dos variables.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y

Como argumento cálculo de LAMBDA, usamos la función UNIRCADENAS, como argumento delimitador, dejamos un espacio, omitimos celdas vacías, u, omitimos el argumento, como argumento texto1 ponemos la variable “x”, como argumento texto2, ponemos la variable “y”, cerramos paréntesis.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;x;y)));c)

Tenemos una matriz desbordada de dos columnas, en la primera columna tenemos las fechas, y, en la segunda columna, el día de la semana correspondiente a cada fecha.

Vemos que la fecha aparece en formato general, pues, antes de la variable “x”, usamos la función TEXTO, como argumento valor es la variable “x”, como argumento formato, entre comillas dobles, ponemos “dd/mm/aaaa”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));c)

Tenemos la fecha en formato de fecha.

Creamos otra variable, usamos la función REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “c”, como argumento función, ponemos LAMBDA, y, declaramos dos variables.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;REDUCE("";c;LAMBDA(x;y

Como argumento cálculo de LAMBDA, usamos la función APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos la función TEXTOANTES, como argumento texto, ponemos la variable “y”, como argumento delimitador, ponemos un espacio.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ")

Como argumento matriz3, usamos la función TEXTODESPUES, como argumento texto, ponemos la variable “y”, como argumento delimitador, ponemos espacio.

Cerramos paréntesis, y, probamos variable.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));d)

Aceptamos, y, ya tenemos el encabezado como queremos, una fecha seguida del día de la semana.

La primera columna está de más, usamos la función EXCLUIR, como argumento matriz es la función REDUCE, omitimos el argumento filas, como argumento columnas ponemos 1.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);d)

Ya tenemos el encabezado.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, usamos la función HALLAR, como argumento texto buscado, entre comillas dobles ponemos barra inclinada, como argumento dentro del texto, ponemos la variable “d”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);e;SI(HALLAR("/";d)

Como argumento valor si verdadero, usamos la función SECUENCIA, como argumento filas, ponemos 5, omitimos el argumento columnas, como argumento inicio, ponemos la celda B2, como argumento paso, ponemos la celda D2, como argumento valor si falso, ponemos blanco.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);e;SI(HALLAR("/";d);SECUENCIA(5;;$B$2;$D$2);"");e)

Tenemos una matriz desbordada de 12 columnas, donde tenemos el horario para cada trabajador, y, error donde debía de poner blanco.

Creamos otra variable, usamos la función SI.ERROR, como argumento valor ponemos la variable “e”, como argumento valor si error, entre comillas dobles ponemos Trabajador, y, concatenamos  con la función SECUENCIA, donde usamos el argumento filas, y, ponemos 5.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);e;SI(HALLAR("/";d);SECUENCIA(5;;$B$2;$D$2);"");f;SI.ERROR(e;"Trabajador"&SECUENCIA(5));f)

Vemos que donde había error, ahora aparecen los trabajadores.

Creamos otra variable, usamos la función APILARV, como argumento matriz1, ponemos la variable “d”, como argumento matriz2, ponemos la variable “f”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);e;SI(HALLAR("/";d);SECUENCIA(5;;$B$2;$D$2);"");f;SI.ERROR(e;"Trabajador"&SECUENCIA(5));g;APILARV(d;f);g)

Ya tenemos el modelo creado de forma dinamica.

Pero, aun no tenemos contemplado los fines de semana, pues, vamos a ello.

Creamos otra variable, usamos la función BYCOL, como argumento array, ponemos la variable “d”, como argumento función, ponemos LAMBDA, declaramos una variable, como argumento calculo de LAMBDA, usamos el operador lógico O, como argumento valor logico1, ponemos la variable “x” e igualamos entre comillas dobles con sábado, como argumento valor logico2, volvemos a poner la variable “x” e igualamos entre comillas dobles a domingo.

Cerramos paréntesis, probamos variable.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);e;SI(HALLAR("/";d);SECUENCIA(5;;$B$2;$D$2);"");f;SI.ERROR(e;"Trabajador"&SECUENCIA(5));g;APILARV(d;f);h;BYCOL(d;LAMBDA(x;O(x="sábado";x="domingo")));h)

Tenemos una matriz desbordada en vertical con VERDADERO donde es sábado, o, domingo, y, FALSO donde no lo es.

Volvemos a la variable, antes de BYCOL, usamos el condicional SI, preguntamos si el resultado de BYCOL es igual a VERDADERO, como argumento valor si verdadero, usamos la función SECUENCIA, como argumento filas, ponemos 5, omitimos el argumento columnas, como argumento inicio, ponemos blanco, omitimos el argumento paso, como argumento valor si falso, ponemos la variable “g”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);e;SI(HALLAR("/";d);SECUENCIA(5;;$B$2;$D$2);"");f;SI.ERROR(e;"Trabajador"&SECUENCIA(5));g;APILARV(d;f);h;SI(BYCOL(d;LAMBDA(x;O(x="sábado";x="domingo")));SECUENCIA(5;;"");g);h)

Vemos que donde es sábado, o, domingo aparece error.

Usamos la función SI.ERROR, y, ponemos blanco, si la expresion devuelve blanco.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;EXCLUIR(REDUCE("";c;LAMBDA(x;y;APILARH(x;TEXTOANTES(y;" ");TEXTODESPUES(y;" "))));;1);e;SI(HALLAR("/";d);SECUENCIA(5;;$B$2;$D$2);"");f;SI.ERROR(e;"Trabajador"&SECUENCIA(5));g;APILARV(d;f);h;SI.ERROR(SI(BYCOL(d;LAMBDA(x;O(x="sábado";x="domingo")));SECUENCIA(5;;"");g);"");h)

Ya no tenemos el error.

Lo siguiente es el mismo modelo, pero en el encabezado, en una celda debe de aparecer la fecha junto con el día de la semana, y, en la siguiente celda la palabra Trabajador.

La siguiente expresión es la misma que para la primera expresión:

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)))

Vamos a crear otra variable, usamos la función ENFILA, como argumento ponemos la variable “c”, concatenamos con un punto, concatenamos entre comillas dobles con la palabra Trabajador.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";d)

Tenemos en horizontal en cada celda, la fecha, el día de la semana al que corresponde, un punto, y, la palabra Trabajador.

Creamos otra variable, usamos la función APILARV, como argumento matriz1, usamos la función TEXTOANTES, como argumento texto, ponemos la variable “d”, como argumento delimitador, entre comillas dobles, ponemos punto, como argumento matriz2, usamos la función TEXTODESPUES, como argumento texto, ponemos la variable “d”, como argumento delimitador, entre comillas dobles, ponemos punto.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));e)

Tenemos una matriz desbordada de dos filas, en la primera fila, tenemos la fecha y el día de la semana, en la segunda fila, tenemos la palabra Trabajador.

Creamos otra variable, usamos la función ENFILA, como argumento usamos la función TRANSPONER, como argumento de TRANSPONER, ponemos la variable “e”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));f)

Ya tenemos el encabezado preparado.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, usamos la función HALLAR, como argumento texto buscado, entre comillas dobles, ponemos la barra inclinada, como argumento dentro del texto, ponemos la variable “f”, como argumento valor si verdadero, usamos la función SECUENCIA, como argumento filas, ponemos 5, omitimos el argumento columnas, como argumento inicio, ponemos la celda B2, como argumento paso, ponemos la variable D2.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));g;SI(HALLAR("/";f);SECUENCIA(5;;B2;D2);"");g)

Ya tenemos el modelo con los horarios, y, error donde debe de ir el trabajador.

Creamos otra variable, usamos la función SI.ERROR, como argumento valor ponemos la variable “g”, como argumento valor si error, entre comillas dobles, ponemos Trabajador y concatenamos con la función SECUENCIA, donde usamos el argumento filas, ponemos 5, para que a cada trabajador le asigne el numero correspondiente.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));g;SI(HALLAR("/";f);SECUENCIA(5;;B2;D2);"");h;SI.ERROR(g;"Trabajador"&SECUENCIA(5));h)

Ya tenemos sustituido el error por la palabra Trabajador correspondiente.

Creamos otra variable, usamos la función APILARV, como argumento matriz1, ponemos la variable “f”, como argumento matriz2, ponemos la variable “h”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));g;SI(HALLAR("/";f);SECUENCIA(5;;B2;D2);"");h;SI.ERROR(g;"Trabajador"&SECUENCIA(5));i;APILARV(f;h);i)

Ya tenemos el modelo junto con el encabezado.

Nos queda anular los fines de semana, creamos otra variable, usamos la función BYCOL, como argumento array, usamos la función TEXTODESPUES, como argumento texto, usamos la función ELEGIRFILAS, como argumento matriz, ponemos la variable “i”, como argumento numero de fila1, ponemos 1, vamos hasta el argumento si no se encuentra, y, ponemos blanco, de la matriz obtenida en la variable “i”, vamos a tomar la primera fila.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));g;SI(HALLAR("/";f);SECUENCIA(5;;B2;D2);"");h;SI.ERROR(g;"Trabajador"&SECUENCIA(5));i;APILARV(f;h);j;BYCOL(TEXTODESPUES(ELEGIRFILAS(i;1);" ";;;;"")

Como argumento cálculo de LAMBDA, usamos el operador lógico O, como argumento valor logico1, preguntamos si la variable “x” es igual a sábado, como argumento valor logico2, preguntamos si la variable “x” es igual a domingo.

Cerramos paréntesis.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));g;SI(HALLAR("/";f);SECUENCIA(5;;B2;D2);"");h;SI.ERROR(g;"Trabajador"&SECUENCIA(5));i;APILARV(f;h);j;BYCOL(TEXTODESPUES(ELEGIRFILAS(i;1);" ";;;;"");LAMBDA(x;O(x="sábado";x="domingo")));j)

Tenemos una matriz desbordada en horizontal con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, ponemos la variable “j” e igualamos a VERDADERO, pero, no hace falta poner la comparación, porque de forma predeterminada compara con VERDADERO, como argumento valor si verdadero, ponemos blanco, como argumento valor si falso, ponemos la variable “h”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));g;SI(HALLAR("/";f);SECUENCIA(5;;B2;D2);"");h;SI.ERROR(g;"Trabajador"&SECUENCIA(5));i;APILARV(f;h);j;BYCOL(TEXTODESPUES(ELEGIRFILAS(i;1);" ";;;;"");LAMBDA(x;O(x="sábado";x="domingo")));k;SI(j;"";h);k)

Ya aparece blanco en los horarios que corresponde con sábado, o, domingo.

Usamos el argumento calculo de LET, usamos la función APILARV, como argumento matriz1, ponemos la variable “f”, como argumento matriz2, ponemos la variable “k”.

=LET(a;SECUENCIA(6;;A2);b;TEXTO(a;"dddd");c;MAP(a;b;LAMBDA(x;y;UNIRCADENAS(" ";VERDADERO;TEXTO(x;"dd/mm/aaaa");y)));d;ENFILA(c )&"."&"Trabajador";e;APILARV(TEXTOANTES(d;".");TEXTODESPUES(d;"."));f;ENFILA(TRANSPONER(e));g;SI(HALLAR("/";f);SECUENCIA(5;;B2;D2);"");h;SI.ERROR(g;"Trabajador"&SECUENCIA(5));i;APILARV(f;h);j;BYCOL(TEXTODESPUES(ELEGIRFILAS(i;1);" ";;;;"");LAMBDA(x;O(x="sábado";x="domingo")));k;SI(j;"";h);APILARV(f;k))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco