En el siguiente ejemplo, tenemos una serie de empleados, con una fecha de entrada, una hora de entrada, y, una hora de salida.
Estamos trabajando con el mes de agosto, y, solo con tres días, queremos calcular que numero de empleados estuvieron trabajando en cada día.
Lo haremos de dos formas.
Lo primero que debemos de hacer es extraer de la columna fecha los días del mes, y, quedarnos con los días únicos, para ello, en la celda F4, voy a usar la función DIA aplicada a la columna de fechas, y, me quedare con la función UNICOS, con los valores únicos.
=UNICOS(DIA(A4:A23))
Vemos que solo tenemos los días 29, 30, y, 31 del mes de agosto.
Ahora, con la función FILTRAR, voy a extraer los empleados para cada fecha, para ello, en la celda G4, escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.
=FILTRAR(
Como argumento array, seleccionamos los empleados.
=FILTRAR(D4:D23;
Como argumento include, aplicamos la función DIA a la columna de fechas, y, la igualamos al valor de la celda F4.
Cerramos paréntesis y aceptamos.
=FILTRAR(D4:D23;DIA(A4:A23)=F4)
Tenemos una matriz desbordada con los empleados para el día 29.
Usamos la función UNICOS, para quedarnos con los empleados únicos.
=UNICOS(FILTRAR($D$4:$D$23;DIA($A$4:$A$23)=F4))
Transponemos.
=TRANSPONER(UNICOS(FILTRAR($D$4:$D$23;DIA($A$4:$A$23)=F4)))
Y tenemos los empleados para el día 29.
Lo siguiente es fijas las siguientes referencias, y, arrastramos.
=TRANSPONER(UNICOS(FILTRAR($D$4:$D$23;DIA($A$4:$A$23)=F4)))
Ya tenemos los empleados para cada día del mes de agosto de 2021.
Ahora, lo haremos con la función FRECUENCIA, y, el condicional SI.
La función FRECUENCIA tiene dos argumentos, que son datos, y, grupos, y, nos dice las veces que se repite o con la frecuencia que aparece un valor dentro de un rango.
La función FRECUENCIA la vamos a usar para obtener los días únicos del mes.
Para el argumento datos, con la función DIA voy a extraer el día de la columna fecha.
=FRECUENCIA(DIA(A4:A23);
Y, como argumento grupos, uso la misma expresión.
=FRECUENCIA(DIA(A4:A23);DIA(A4:A23))
Cerramos paréntesis y aceptamos.
Tenemos las veces que aparece cada día, y, ceros.
Con el condicional SI voy a preguntar que si el resultado de la función FRECUENCIA, es diferente a cero, que nos devuelva el día, en caso contrario, que devuelva un texto en blanco.
=SI(FRECUENCIA(DIA(A4:A23);DIA(A4:A23))<>0;DIA(A4:A23);"")
Obtenemos una matriz desbordada con los números de día del mes, y, espacios.
Debemos de quedarnos con los días, y, eliminar los espacios en blanco, lo haremos de dos formas diferentes, la primera de ellas, la haremos con la función K.ESIMO.MENOR, que nos devuelve el primer, segundo, tercer valor menor, o más, si hubiera.
Pero, como queremos que todo quede en una sola función, como argumento K de K.ESIMO.MENOR, usaremos una constante de matriz, para que nos devuelva el primer, segundo, y, tercer valor menor.
=K.ESIMO.MENOR(SI(FRECUENCIA(DIA(A4:A23);DIA(A4:A23))<>0;DIA(A4:A23);"");{1;2;3})
Ya tenemos los días únicos.
Vemos que la función DIA, la usamos tres veces, por lo que vamos a usar la función LET, donde creare una variable, y, almacenare la función DIA.
=LET(a;DIA(A4:A23);K.ESIMO.MENOR(SI(FRECUENCIA(a;a)<>0;a;"");{1;2;3}))
La sintaxis queda más corta, y, tenemos el mismo resultado.
Otra forma que podemos hacerlo es con la función FILTRAR, donde filtramos la función FRECUENCIA, junto con el condicional SI, y, como argumento include, es la función FRECUENCIA, siempre que sea diferente a blanco.
=FILTRAR(SI(FRECUENCIA(DIA(A4:A23);DIA(A4:A23))<>0;DIA(A4:A23);"");SI(FRECUENCIA(DIA(A4:A23);DIA(A4:A23))<>0;DIA(A4:A23);"")<>"")
Como usamos dos veces la función FRECUENCIA, volvemos a usar la función LET.
=LET(a;SI(FRECUENCIA(DIA(A4:A23);DIA(A4:A23))<>0;DIA(A4:A23);"");FILTRAR(a;a<>""))
Ahora, vamos a por los empleados, con el condicional SI, voy a preguntar que, si el día de la columna fecha es igual al valor de la celda F11, que devuelva el empleado, en caso contrario, que devuelva un texto en blanco.
=SI(DIA(A4:A23)=F11;D4:D23;"")
Obtenemos una matriz desbordada con los empleados para el día 29.
Nos quedamos con los valores únicos.
=UNICOS(SI(DIA(A4:A23)=F11;D4:D23;""))
Transponemos.
=TRANSPONER(UNICOS(SI(DIA(A4:A23)=F11;D4:D23;"")))
Tenemos los empleados para el día 29, pero, vemos que nos devuelve una celda en blanco dentro de la matriz desbordada.
Para eliminar dicha celda, y, como solo queremos ver los nombres de los empleados, voy a usar la función UNIRCADENAS, el primer argumento de esta función es delimitador, voy a poner entre comillas dobles, la coma.
=UNIRCADENAS(","
En el siguiente argumento, ignoramos las celdas vacías.
Y, como argumento texto1, es la función anterior.
=UNIRCADENAS(",";VERDADERO;TRANSPONER(UNICOS(SI(DIA(A4:A23)=F11;D4:D23;""))))
Tenemos los empleados para el día 29.
Fijamos las siguientes referencias, y, arrastramos.
UNIRCADENAS(",";VERDADERO;TRANSPONER(UNICOS(SI(DIA($A$4:$A$23)=F11;$D$4:$D$23;""))))
Tenemos los empleados para cada día del mes.
Ahora, queremos saber, el tiempo que trabajo cada empleado en cada día, para ello, voy a usar la función FILTRAR, donde como argumento matriz son las columnas entrada y salida.
=FILTRAR(B4:C23;
Como argumento include, abrimos un paréntesis, para poner la primera condición, el día de la columna fecha, debe de ser igual al valor de F18, es decir, al día 29.
=FILTRAR(B4:C23;(DIA(A4:A23)=F18)
Ponemos el símbolo de asterisco, para poner la segunda condición, el símbolo de asterisco es igual que el operador lógico Y, es decir, devuelve VERDADERO, si todas las condiciones se cumplen, abrimos un paréntesis, y, la siguiente condición es que la columna de empleados debe de ser igual al valor de G17, el primer empleado.
Cerramos paréntesis de la condición, cerramos paréntesis de la función, y, aceptamos.
=FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17))
Tenemos una matriz desbordada de dos columnas con la entrada y salida para el primer empleado.
Para saber el tiempo que ha estado un empleado, debo de restar la hora de salida menos la hora de entrada, el problema, es que tenemos una matriz desbordada de dos columnas, y, queremos restar una columna sobre la otra, bien, después del signo igual, pongo de nuevo la función FILTRAR, y, abro un paréntesis.
=FILTRAR(
Como argumento array, es la expresión anterior.
=FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17))
Como argumento include, voy a usar una constante de matriz, para que me devuelva la columna 2, que es la primera a restar, para ello, debemos de poner cero para la columna que no queremos que aparezca, y, uno para la columna que si queremos que aparezca.
Cerramos paréntesis y aceptamos.
=FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17));{0\1})
Vemos que tenemos la segunda columna.
Ahora, tenemos que restarle la primera columna, para ello, voy a seleccionar la expresión anterior, excepto, el signo igual y la copio con CTRL más C, voy a final de la función, pongo el signo menos, pego la expresión con CTRL mas V, y, en la constante de matriz, invierto los valores, para que me devuelva la columna 1.
=FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17));{0\1})-FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17));{1\0})
Tenemos la diferencia.
Pero, el resultado lo queremos en horas, por lo que la expresión anterior, la encierro entre paréntesis, y, multiplico por 24.
=(FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17));{0\1})-FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17));{1\0}))*24
Tenemos los tiempos de diferencia.
Ahora, usamos la función SUMA para obtener el tiempo trabajado del primer empleado.
=SUMA((FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17));{0\1})-FILTRAR(FILTRAR(B4:C23;(DIA(A4:A23)=F18)*(D4:D23=G17));{1\0}))*24)
Y ya lo tenemos.
Ahora, debemos de fijar las siguientes referencias, antes de arrastrar.
=SUMA((FILTRAR(FILTRAR($B$4:$C$23;(DIA($A$4:$A$23)=$F18)*($D$4:$D$23=G$17));{0\1})-FILTRAR(FILTRAR($B$4:$C$23;(DIA($A$4:$A$23)=$F18)*($D$4:$D$23=G$17));{1\0}))*24)
Arrastramos hacia la derecha, y, hacia abajo.
Ya tenemos el tiempo de cada empleado, pero dijimos que no todos los empleados, trabajaron todos los días, por lo que el empleado que no trabajo un día nos devuelve un error.
Vamos a usar la función SI.ERROR, para que ponga un texto en blanco, en caso de error.
=SI.ERROR(SUMA(FILTRAR(FILTRAR($B$4:$C$23;(DIA($A$4:$A$23)=$F18)*($D$4:$D$23=G$17));{0\1})-FILTRAR(FILTRAR($B$4:$C$23;(DIA($A$4:$A$23)=$F18)*($D$4:$D$23=G$17));{1\0}))*24;"")
Ya lo tenemos.
Lo siguiente es saber que empleados trabajaron en cada día, para ello, con el condicional SI voy a preguntar que si el día de la columna fecha es igual al valor de la celda F18, el día 29, además, si la columna empleado es igual al valor de la celda G17, donde uso el operador de rango derramado, para seleccionar todos los vendedores, en ese caso, que devuelva el vendedor, en caso contrario, que devuelva un texto en blanco para el segundo SI, y, en caso de que no haya coincidencia en el primer SI, que ponga también un texto en blanco.
=SI(DIA(A4:A23)=F18;SI(D4:D23=G17#;G17#;""))
Aceptamos, y, tenemos una matriz desbordada con los nombres de empleados donde hay coincidencia, y, un texto en blanco, donde no la haya.
Ahora, usamos la función UNIRCADENAS, donde como argumento delimitador ponemos la coma, ignoramos las celdas en blanco, y, como argumento texto1, es la expresión anterior.
=UNIRCADENAS(",";VERDADERO;UNICOS(SI(DIA(A4:A23)=F18;SI(D4:D23=G17#;G17#;"");"")))
Aceptamos, y, ya tenemos los empleados para cada día.
Ahora, fijamos el rango A4:A23, la referencia F18, solo fijamos la columna, para que al copiar hacia la derecha, no se actualice la columna, pero al copiar hacia abajo, si se actualice la fila, fijamos el rango D4:D23, de la referencia G17, solo fijamos la fila, para que copiar hacia abajo, no se actualice, ya que los empleados siempre van a estar en la fila 17.
=UNIRCADENAS(",";VERDADERO;UNICOS(SI(DIA($A$4:$A$23)=$F18;SI($D$4:$D$23=G$17#;G$17#;"");"")))
Aceptamos, y, arrastramos, y tenemos los empleados para cada día.
Por último, queremos saber las entradas de cada trabajador, sabiendo que un trabajador en el mismo día, pudo haber entrado a diferentes horas, esto será fácil, vamos a usar la función CONTAR.SI de forma matricial, donde como argumento rango, seleccionamos los empleados, y, como argumento criterio, seleccionamos todos los vendedores.
=CONTAR.SI(D4:D23;G17#)
Aceptamos, y, tenemos las veces que entro cada trabajador.
Commenti