Cargo de horas de trabajo con Excel.

sábado, febrero 09, 2008

Otras de las tareas usuales con Excel es el cálculo de horas a cargar por un proyecto. Sencillamente, calculamos el total de horas y lo multiplicamos por la tarifa. A pesar de lo obvio que parece, debemos tomar en cuenta cómo Excel considera las horas en los cálculos.
Por ejemplo, si hemos invertido 10 horas de trabajo y la tarifa acordada con nuestro cliente es 50 euros, estaríamos tentado ha hacer este cálculo:"10:00 X € 50.00 = € 500". Pongámoslo ahora en una hoja de Excel



El resultado en la celda C2 es "incorrecto". Como explicamos en la nota anterior, esto se debe a que el valor de la celda A2 es en realidad 0.416666666666667 (10 horas dividido por 24 horas).
Para corregir este "error" (que en realidad no lo es) multiplicamos nuestra fórmula original por 24



En la "vida real" las cosas son más complicadas. Por lo general trabajamos con tarifas horarias diferenciales. Supongamos esta tarifa diferencial para cargar un proyecto



La idea es cargar a nuestro cliente con una tarifa decreciente en función de la cantidad de horas.

Siguiendo las indicaciones de la nota anterior sobre el cálculo con horas, construimos esta tabla con las horas trabajadas en el proyecto



Para cargar a nuestro cliente tenemos que dividir las horas de trabajos en porciones de 8 horas. El modelo propuesto es el siguiente



Todas las celdas en el rango A11:B13 tienen el formato [hh]:mm. La celda A14 tiene el formato [h]:mm +, para que el sino + aparezca, sin convertir el contenido de la celda en texto.
Los intervalos los fijamos en forma dinámica, donde la duración del intervalo está definida por las celdas B11 – A11 (8 horas en nuestro caso). Esta diferencia la ponemos en un nombre (escala):



En la celda A12 ponemos la fórmula =B11, en la celda A13, =B12. En la celda B12 ponemos la fórmula =A12+escala, en la celda B13 =A13+escala. De esta manera nuestro modelo será dinámico y se ajustará automáticamente a los cambios en el tamaño del intervalo.
En la columna Horas, en la celda D11 ponemos la fórmula

=MIN(escala,$D$6)

Usamos esta fórmula en lugar de la función SI para determinar si el total de horas trabajadas es menor que el primer intervalo de nuestra tarifa diferencial.
En las celdas del rango D12:D14 usamos la fórmula

=MIN(escala,$D$6-SUMA($D$11:D11))

La expresión SUMA($D$11:D11) va resumiendo le total de horas que ya hemos cargado. Hay que prestar atención a las direcciones absolutas y relativas en la función SUMA.

Para ver cómo el modelo ajusta automáticamente los intervalos, cambiamos el primer intervalo a, por ejemplo, seis horas







Technorati Tags:

3 comments:

Anónimo,  21 octubre, 2009 15:57  

Hola Jorge

Veo un problema en la última parte del desarrollo de la nota. Cito literalmente:

"....
En las celdas del rango D12:D14 usamos la fórmula

=MIN(escala,$D$6-SUMA($D$11:D11))
....."

Es así que a D14 le asigna la fórmula:
=MIN(escala;$D$6-SUMA($D$11:D13))
Pero en este caso la fórmula debería ser:
=$D$6-SUMA($D$11:D13)
Esto es porque superadas las 24 horas no tiene que hacer el mínimo con el intervalo como argumento ya que podría dar un resultado inesperado como en tu ejemplo, en el que aparecen 6 horas en lugar de 6:15. Lo mismo pasaría si ponemos un intervalo de 5, 4, etc.

Saludos

Jorge L. Dunkelman 21 octubre, 2009 19:28  

Tenés razón. Sucede que publique la tabla equivocada. Si descargás el ejemplo veras que los intervalos del cuadro de tarifas (A11:B14) son de ocho horas, cuando en la tabla que publique en el blog son de 6 horas. Gracias por comentar.

Anónimo,  21 octubre, 2009 23:11  

Hola Jorge
Es cierto que en el ejemplo vienen intervalos de 8 horas (en el blog también y luego lo cambiás por 6 horas).
El caso es que el modelo debe funcionar se ponga el intervalo que se ponga. En tu ejemplo de 24:15 horas trabajadas, si el intervalo es de 8 horas, el resultado de D14 está bien pero un poco por casualidad. Si el intervalo es de 6 horas o menos ya no funciona si ponemos la función MIN.
Es que yo creo que en todos los casos la fórmula que hay que poner para D14 (o sea para el resto de horas) es
=$D$6-SUMA($D$11:D13)
Esto funcionará bien, tanto para el intervalo de 8 horas como para cualquiera.

Un saludo

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP