21

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 (486 clics)
Tamaño: 14 KB
Reto04 - Soluciones
Título: Reto04 - Soluciones (377 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 21 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
        Sergio - 02/04/2018

        Perfecto, amigo. Muchas gracias!

        Responder
          Fran - 27/04/2018

          Disculpa, pero a mi la última fórmula [=SUMA(SI(B4:AE4=AJ$3:AJ$5;AK$3:AK$5))] me arroja #VALOR! como respuesta, cual puede ser el problema?

          Gracias, Un saludo

          Responder
          Sergio - 02/05/2018

          Fran, te aparece ese error porque estás tratando de introducir una fórmula matricial de forma incorrecta.
          Para hacerlo correctamente, introduce =SUMA(SI(B4:AE4=AJ$3:AJ$5;AK$3:AK$5)) sin los corchetes y pulsa Ctrl + Mayús + Intro.
          Saludos.

          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
Eddie Eddie - 28/06/2018

Hola Sergio soy nuevo en tu pagina y en esta dinámica de los retos, la verdad no soy muy bueno en Excel, considero que tengo un 30 a 40% de conocimiento y he tenido que aprender con el paso del tiempo (tengo una formación de secundaria, nunca estudie informática o similares), pero aquí esta otra forma que me funciono, por cierto te has ganado otro suscriptor 🙂

{=SUMA(SI($B4:$AE4=$AJ$3,$AK$3),SI($B4:$AE4=$AJ$4,$AK$4),SI($B4:$AE4=$AJ$5,$AK$5))}

Responder
    Sergio - 28/06/2018

    ¡Muy bien, Eddie Eddie! Funciona perfectamente.
    … y me alegra que hayas comentado a seguir la web… espero verte por aquí de vez en cuando.

    Responder

Escribe una respuesta:

Powered by WishList Member - Membership Software