Dividir horas entre días

Tenemos el siguiente modelo:

Vemos nombres como Sander, que trabajo 5 horas el día 2, y, el día 3.

Debemos de crear un modelo donde en vertical aparezca el nombre las veces de días que ha trabajado, es decir, la fecha, y, en horas debemos de dividir las horas entre los días que ha trabajado, como sigue:

En la celda I2, ponemos LET, creamos una variable, usamos MAP, como argumento array, seleccionamos el rango D2:G8, como argumento funcion, ponemos LAMBDA, creamos una variable, como argumento cálculo de LAMBDA, usamos el condicional SI, preguntamos si la variable “x” es igual a 1, en ese caso, que nos devuelva 1, en caso contrario, que nos devuelva 0.

Probamos variable.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));a)

Tenemos una matriz desbordada con 0 donde no hay coincidencia, y, 1, donde hay coincidencia.

Creamos otra variable, usamos BYROW, como argumento array, ponemos la variable “a”, como argumento funcion, ponemos LAMBDA, creamos una variable, como argumento calculo de LAMBDA, sumamos la variable “x”.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));b)

Obtenemos los días trabajados por cada nombre.

Creamos otra variable, usamos el condicional SI, preguntamos si la variable “a” es igual a 1, en ese caso, que nos devuelva el rango A2:A8, concatenamos con un espacio, concatenamos con el rango B2:B8, concatenamos con un espacio, y, concatenamos con el rango C2:C8, como argumento valor si falso, ponemos un texto en blanco.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");c)

Obtenemos una matriz desbordada con el nombre, la fecha y número de horas.

Creamos otra variable, usamos la funcion DERECHA, como argumento texto, ponemos la variable “c”, como argumento número de caracteres, ponemos 2, y, dividimos por la variable “b”.

Vamos a dividir cada numero de hora entre los días trabajados.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;DERECHA(c;2)/b;d)

Obtenemos una matriz desbordada con el numero de horas que corresponde a cada trabajador.

Usamos la funcion ENCOL, como argumento matriz es la funcion DERECHA, como argumento ignorar, ponemos 3, es decir, ignorar blancos y errores.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(DERECHA(c;2)/b;3);d)

Obtenemos en vertical el numero de horas de cada trabajador.

Creamos otra variable, la llamamos nombres, usamos la función EXTRAE, como argumento texto, ponemos la variable “c”, como argumento posición inicial, ponemos 1, como argumento número de caracteres, usamos la funcion ENCONTRAR, para encontrar el espacio, como argumento texto buscado, ponemos el espacio, como argumento dentro del texto, ponemos la variable “c”, y, restamos1, para que extraiga hasta antes del espacio.

LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(ABS(DERECHA(c;2))/b;3);nombres;ENCOL(EXTRAE(c;1;ENCONTRAR(" ";c)-1);nombres)

Obtenemos una matriz desbordada con el nombre donde tiene que ir, y, error donde no hay nombre que poner.

Podemos ver que aparece el nombre en el día que dicho nombre ha trabajado.

Usamos la funcion ENCOL, como argumento matriz es la funcion EXTRAE, como argumento ignorar, seleccionamos 3.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(ABS(DERECHA(c;2))/b;3);nombres;ENCOL(EXTRAE(c;1;ENCONTRAR(" ";c)-1);3);nombres)

Tenemos los nombres en vertical.

Creamos otra variable, la llamamos fechas, vamos a extraer la fecha de la matriz desbordada obtenida en la variable “c”.

Debemos de tener en cuenta los dos espacios, porque los caracteres a extraer esta entre los dos espacios, usamos EXTRAE, como argumento texto, ponemos la variable “c”, como argumento posición inicial, usamos ENCONTRAR, como argumento texto buscado, ponemos un espacio, como argumento dentro del texto, ponemos la variable “c”, y, sumamos 1.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(ABS(DERECHA(c;2))/b;3);nombres;ENCOL(EXTRAE(c;1;ENCONTRAR(" ";c)-1);3);fechas;EXTRAE(c;ENCONTRAR(" ";c)+1

Como argumento numero de caracteres, volvemos a usar la funcion ENCONTRAR, como argumento texto buscado, ponemos un espacio, como argumento dentro del texto, ponemos la variable “c”, como argumento numero de caracteres, ponemos ENCONTRAR, como argumento texto buscado ponemos un espacio, como argumento dentro del texto, ponemos la variable “c”, y, restamos 1, volvemos a restar con la funcion ENCONTRAR, como argumento texto buscado, ponemos un espacio, como argumento dentro del texto, ponemos la variable “c”.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(ABS(DERECHA(c;2))/b;3);nombres;ENCOL(EXTRAE(c;1;ENCONTRAR(" ";c)-1);3);fechas;EXTRAE(c;ENCONTRAR(" ";c)+1;ENCONTRAR(" ";c;ENCONTRAR(" ";c)+1)-1-ENCONTRAR(" ";c));fechas)

Tenemos una matriz desbordada con las fechas.

Usamos la funcion ENCOL, e, ignoramos blancos y errores.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(ABS(DERECHA(c;2))/b;3);nombres;ENCOL(EXTRAE(c;1;ENCONTRAR(" ";c)-1);3);fechas;ENCOL(EXTRAE(c;ENCONTRAR(" ";c)+1;ENCONTRAR(" ";c;ENCONTRAR(" ";c)+1)-1-ENCONTRAR(" ";c));3);fechas)

Obtenemos una matriz desbordada en vertical con las fechas en formato general.

Si la ponemos en formato de fecha corta, vemos que no ocurre nada, estas fechas están alineadas a la izquierda, quiere decir, que están en formato de texto, pues, vamos a la variable fechas, antes de ENCOL, ponemos la funcion ABS, para convertir las fechas en números, vemos como ahora si la podemos poner en fecha corta.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(ABS(DERECHA(c;2))/b;3);nombres;ENCOL(EXTRAE(c;1;ENCONTRAR(" ";c)-1);3);fechas;ABS(ENCOL(EXTRAE(c;ENCONTRAR(" ";c)+1;ENCONTRAR(" ";c;ENCONTRAR(" ";c)+1)-1-ENCONTRAR(" ";c));3));fechas)

Creamos otra variable, la llamamos resul, usamos APILARH, como argumento matriz1, ponemos la variable nombres, como argumento matriz2, ponemos la variable fechas, como argumento matriz3, ponemos la variable “d”.

=LET(a;MAP(D2:G8;LAMBDA(x;SI(x="X";1;0)));b;BYROW(a;LAMBDA(x;SUMA(x)));c;SI(a=1;A2:A8&" "& B2:B8&" "&C2:C8;"");d;ENCOL(ABS(DERECHA(c;2))/b;3);nombres;ENCOL(EXTRAE(c;1;ENCONTRAR(" ";c)-1);3);fechas;ABS(ENCOL(EXTRAE(c;ENCONTRAR(" ";c)+1;ENCONTRAR(" ";c;ENCONTRAR(" ";c)+1)-1-ENCONTRAR(" ";c));3));resul;APILARH(nombres;fechas;d);resul)

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco