17

Reto 04: Sumar las horas de un calendario de turnos

¿Te atreves con este reto?

En un gran hotel se ha establecido un calendario de turnos de trabajo para los 14 empleados que trabajan en su restaurante. Todos ellos trabajan en tres turnos que se disponen en la siguiente tabla:

Calendario de turnos excel

Tu misión, si decides aceptarla, sería el hallar el número de horas mensuales de cada empleado basándote en la tabla de la derecha, donde se muestran las correspondencias en horas.

Yo ya he estado trabajando en el reto y he conseguido sumar las horas de trabajo de tres métodos diferentes.

Reto calendario de turnos excel

Venga, te voy a dar una pista. He utilizado las funciones SUMAPRODUCTO, SUMAR.SI, SI.ERROR, BUSCAR, DESREF, CONTARA y CONTAR.BLANCO, pero no te diré a qué método corresponde cada una… También te informo de que dos de los métodos utilizan fórmulas matriciales

Ahora es tu turno. Descárgate el archivo y trabaja con él. Me alegrará ver que escribes un comentario aquí debajo con tu fórmula. Puedes enviar cuantas soluciones quieras (siempre que sean correctas).

¡La próxima semana, tendrás mis soluciones y las de los demás participantes!

Reto tabla de turnos
Título: Reto tabla de turnos (135 clics)
Tamaño: 14 KB
Reto04 - Soluciones
Título: Reto04 - Soluciones (45 clics)
Tamaño: 17 KB

 

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 17 comentarios
John Jairo V - 02/04/2018

¡Hola, a todos!

Dejo mis primeras cuatro opciones (la primera de ellas, matricial):
=SUMA(MMULT(–(B4:AE4=AJ$3:AJ$5);TRANSPONER(COLUMNA(B4:AE4)^0))*AK$3:AK$5)
=SUMAPRODUCTO(CONTAR.SI(B4:AE4;AJ$3:AJ$5);AK$3:AK$5)
=SUMAPRODUCTO(SUMAR.SI(AJ$3:AJ$5;B4:AE4;AK$3:AK$5))
=SUMAPRODUCTO((B4:AE4=AJ$3:AJ$5)*AK$3:AK$5)

¡Bendiciones!

Responder
    Sergio - 02/04/2018

    ¡Muy bien! Me alegra que siempre respondas a los retos!.
    Tus cuatro soluciones funcionan perfectamente. La tercera es prácticamente igual a una de las que he obtenido yo. Sólo se diferencian en que yo uso referencias absolutas en los rangos de la tabla de correspondencias.
    La semana que viene publicaré un archivo con todas las soluciones propuestas.
    Gracias de nuevo!

    Responder
Rafael - 02/04/2018

Pues yo hice dos, la primera con un metodo bien sencillo, para quien solo usa formulas básicas esta es una opción clásica:
=CONTAR.SI(B4:AE4;$AJ$3)*$AK$3+CONTAR.SI(B4:AE4;$AJ$4)*$AK$4+CONTAR.SI(B4:AE4;$AJ$5)*$AK$5
Si ya te adentras un poco y trabajas con formulas mas reducidas esta la opcion 2 que es igual a una de las que tiene Jairo.
=SUMAPRODUCTO((B4:AE4=AJ$3:AJ$5)*AK$3:AK$5)

Responder
    Sergio - 02/04/2018

    Rafael, gracias por tus aportes. Funcionan correctamente. La fórmula que contiene SUMAPRODUCTO ya ha sido publicada por John Jairo un poco antes…

    Responder
    Sergio - 02/04/2018

    Rafael, están perfectas!!! Aunque en la que contiene SUMAPRODUCTO se te han adelantado, jejeje.

    Responder
John Jairo V - 02/04/2018

Otras tres más, matriciales todas:

=SUMA(SI.ERROR(INDICE(AK3:AK5;N(SI(1;COINCIDIR(B4:AE4;AJ3:AJ5;))));))
=SUMA(DESREF(AK2;N(SI(1;SI.ERROR(COINCIDIR(B4:AE4;AJ3:AJ5;);)));))
=SUMA(SI(B4:AE4=AJ3:AJ5;AK3:AK5))

¡Bendiciones!

Responder
    Sergio - 02/04/2018

    John Jairo, las fórmulas están bien, pero al arrastrarlas hacia abajo, no funcionan. Corrige las referencias!
    Gracias por los aportes!

    Responder
      John Jairo V - 02/04/2018

      Tienes razón… aquí van con los amarres:
      =SUMA(SI.ERROR(INDICE(AK$3:AK$5;N(SI(1;COINCIDIR(B4:AE4;AJ$3:AJ$5;))));))
      =SUMA(DESREF(AK$2;N(SI(1;SI.ERROR(COINCIDIR(B4:AE4;AJ$3:AJ$5;);)));))
      =SUMA(SI(B4:AE4=AJ$3:AJ$5;AK$3:AK$5))
      ¡Bendiciones!

      Responder
Roman - 02/04/2018

Buan dia!!
Es la primera ves que participo en este tipo de dinamicas/retos y me parecen de lo mejor para poder aprender o poner en practica lo que ya uno sabe, estas fueron las formulas que yo use:

=COUNTIF(B4:AE4,$AJ$3)*$AK$3+COUNTIF(B4:AE4,$AJ$4)*$AK$4+COUNTIF(B4:AE4,$AJ$5)*$AK$5

=SUMPRODUCT(SUMIF($AJ$3:$AJ$5,$B4:$AE4,$AK$3:$AK$5))

=SUMPRODUCT(SUMIF(AJ$3:AJ$5,$B4:$AE4,$AK$3:$AK$5))

Disculpen que este en ingles, pero asi lo he aprendido yo en el trabajo ya que solo tenemos la version en este idioma.

Saludos!!

Responder
    Sergio - 02/04/2018

    Román, gracias por tus respuestas, aunque tras revisarlas, he visto que ya las han publicado tanto Jairo como Rafael.
    Venga, inténtalo de nuevo!

    Responder
      Roman - 02/04/2018

      Que tal,
      Aquí encontré otro método, pero si es una formula mas larga
      =COUNTIF(B4:AE4,HLOOKUP($AK$3,B4:AE4,1,0))*VLOOKUP(HLOOKUP($AK$3,B4:AE4,1,0),$AK$3:$AL$5,2,0)+COUNTIF(B4:AE4,HLOOKUP($AK$4,B4:AE4,1,0))*VLOOKUP(HLOOKUP($AK$4,B4:AE4,1,0),$AK$3:$AL$5,2,0)+COUNTIF(B4:AE4,HLOOKUP($AK$5,B4:AE4,1,0))*VLOOKUP(HLOOKUP($AK$5,B4:AE4,1,0),$AK$3:$AL$5,2,0)

      no se si parece compleja, lo vi como opción y si me funciono

      Saludos!!

      Responder
        Sergio - 02/04/2018

        Muy bien, Román!! He hecho una pequeña modificación en las referencias que enlazan con la tabla que contiene las horas. Tú has dejado dos celdas de espacio entre las dos tablas cuando sólo hay uno.
        No obstante, pego el resultado traducido al español:
        =CONTAR.SI(B4:AE4;BUSCARH($AJ$3;B4:AE4;1;0))*BUSCARV(BUSCARH($AJ$3;B4:AE4;1;0);$AJ$3:$AK$5;2;0)+CONTAR.SI(B4:AE4;BUSCARH($AJ$4;B4:AE4;1;0))*BUSCARV(BUSCARH($AJ$4;B4:AE4;1;0);$AJ$3:$AK$5;2;0)+CONTAR.SI(B4:AE4;BUSCARH($AJ$5;B4:AE4;1;0))*BUSCARV(BUSCARH($AJ$5;B4:AE4;1;0);$AJ$3:$AK$5;2;0)

        Responder
Manu - 04/04/2018

Yo he utilizado ésta: =(CONTAR.SI(B4:AE4;”M”)*$AK$3)+(CONTAR.SI(B4:AE4;”T”)*$AK$4)+(CONTAR.SI(B4:AE4;”N”)*$AK$5)

Responder
    Sergio - 04/04/2018

    ¡Perfecto! Es una fórmula muy parecida a otras dos publicadas antes, pero no llega a ser igual.
    Muchas gracias, Manu.

    Responder
Rafael Palacios Velasco - 09/04/2018

En AF4:

={SUMA(CONTAR.SI(B4:AE4;AJ$3:AJ$5)*AK$3:AK$5)}

Y copiar hacia abajo…

Responder
    Sergio - 09/04/2018

    ¡Perfecto! Esta es nueva, así que también la apunto.
    ¡Gracias, Rafael!

    Responder

Escribe una respuesta:

Powered by WishList Member - Membership Software