Cálculos de tiempo con MS Excel – Funciones para cálculos de horas

En las últimas entradas he estado hablando sobre cálculos de tiempo con MS Excel, una explicación general sobre el método de calcular fechas y horas de Excel y en la entrada más reciente sobre el problema de "tiempos negativos". En el pasado también he escrito sobre sumas de tiempo con Excel.
En esta entrada haré una breve revisión de las distintas funciones de tiempo de Excel:

AHORA() - Devuelve el número de serie de la fecha y hora actuales. Esta función no utiliza ningún argumento. La función AHORA sólo cambia cuando se realiza un cálculo en la hoja de cálculo o cuando se ejecuta una macro que contiene la función. No se actualiza constantemente. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Esto quiere decir que el resultado de AHORA() aplicado hoy en una celda con formato General es "15/05/2006 18:17".
Si queremos obtener la hora del momento, sin la fecha, podemos usar la fórmula "=AHORA()-HOY()". Para ver el resultado de esta fórmula con formato de hora debemos dar este formato a la celda: "hh:mm".

HORA(núm_de_serie) – Da como resultado la hora correspondiente al número de serie (que indica horas, minutos segundos). Por ejemplo, =HORA(0.885) da 21:00, cuando 0.885 corresponde a la hora 21:14:24

HORANUMERO(texto_de_hora) - Devuelve el número decimal de la hora representada por una cadena de texto. Por ejemplo, =HORANUMERO("21:14:24") da como resultado 0.885.

MINUTO(núm_de_serie) - Devuelve los minutos de un valor de hora. Los minutos se expresan como números enteros comprendidos entre 0 y 59. Siguiendo con nuestro ejemplo, =MINUTO(0.885) da como resultado 14.

SEGUNDO(núm_de_serie) - Funciona como MINUTO respecto a los segundos.

NSHORA(hora;minuto;segundo) - Devuelve el número decimal de una hora determinada. El número decimal que NSHORA devuelve es un valor comprendido entre 0 (cero) y 0, 99999999 que representa las horas entre 0:00:00 (00:00:00 a. m.) y 23:59:59 (11:59:59 p.m.). Por ejemplo, =NSHORA(21,14,24) da como resultado 0.885 (si la celda tiene formato General).
Lo interesante con esta función es que acepta valores aparentemente "inválidos" y los corrige. Por ejemplo, si en la fórmula anterior usamos "65" en lugar de "14" para los minutos, y usamos el formato de "h:mm AM/PM", veremos como resultado 10:05 PM, en lugar de 9:14 PM. Excel corrige automáticamente el exceso de minutos y los transforma en horas.

Categorías: Funciones&Formulas_

Technorati Tags: Fechas y Horas

Cálculos de tiempo con MS Excel – Diferencia de horas

En la entrada de ayer hablé sobre como Excel realiza cálculos de fechas y tiempo
Vimos que Excel representa las fechas como una serie de números enteros. Si trabajamos con la base del año 1900, el primer número de la serie, el 1, representa el 1 de Enero de 1900. Si usamos la base 1904, el 1 representa el 1 de Enero de 1904.
Las horas, minutos y segundos del día son representados por fracciones del número 1. De esta manera la hora 12:00 (mediodía) es representada por el número 0.5. Las seis de la tarde, 18:00, por el número 0.75.
Cuando efectuamos cálculos de fechas y tiempos, de hecho estamos trabajando con números, que Excel representa con formatos especiales como "dd-mm-yy", "h:mm:ss", o "dd-mmm-yyyy hh:mm". Cuando vemos la fecha 9-05-2006 en una celda, Excel "ve" el número 38846. Cuando queremos calcular cuantos días transcurrieron entre el 17-07-1991 y el 9-07-1992, restamos la última fecha de la primera. De hecho estamos efectuando la resta 33794-33436 = 358.
Si trabajamos con la base 1900 hay que tener en cuenta que Excel no "sabe" representar tiempos y fechas negativos.
Para aclarar este punto, mostraré un ejemplo de cálculo de lapsos entre dos horas.
Supongamos que queremos calcular el tiempo trabajado por operarios. Cuando el comienzo y el final de la jornada no caen en el mismo día, la hora del final es menor que la del principio y Excel no sabrá representar el resultado




El resultado ######## nos indica que Excel no sabe "traducir" la diferencia a formato de tiempo (tiempo negativo).
En este caso no podemos utilizar la función ABS (valor absoluto) a que el resultado sería incorrecto (ABS daría 16 horas en lugar de 8 horas).
En caso de tener que realizar este tipo de operaciones hay varias soluciones:
1 – Utilizar las horas con sus correspondientes fechas, como "08/05/2006 19:00" (ver en el archivo del ejemplo);
2 - Utilizar la fórmula que aparece en la fila 20 del ejemplo, =C20 + (C20"<"B20) - B20. En esta fórmula la expresión (C20"<"B20) puede dar como resultado 1 (verdadero) o 0 (falso). Si la hora del final es menor que la del comienzo, (C20"<"B20)=1, por lo tanto agregamos 1 a la hora del final, que es como agregar un día entero.
3 – El mismo efecto se puede conseguir con la fórmula que aparece en la fila 26 del ejemplo: =RESIDUO(C26-B26,1);


Categorías: Varios_


Technorati Tags: Fechas y tiempo en Excel

Análisis de Tablas de Datos con MS Excel – Análisis de Sensibilidad

Cuando hablo de análisis de sensibilidad me refiero a como influyen cambios en determinados datos en el resultado de algún modelo de cálculo construido en Excel.
Excel dispone de varias herramientas como el comando Buscar Objetivo (Goal Seek) y el Solver.
Una herramienta menos utilizada, según mi experiencia, es el comando Tabla del menú Datos. Esta herramienta puede ser muy útil y ahorrar mucho tiempo.
Expliquemos esto con un ejemplo del uso de tablas de datos en MS Excel. Supongamos que nuestro modelo mide la tasa de ganancia neta, dados el monto de las ventas sabiendo que los gastos variables representan el 35% de las ventas y los gastos fijos son 400 mil (qué moneda usar depende de ustedes, por supuesto).

Una tabla de análisis de sensibilidad nos muestra, por ejemplo, cuál será la tasa de ganancia neta de acuerdo a variaciones en el monto de las ventas y en el porcentaje de los gastos variables:



Excel nos permite construir esta tabla automáticamente usando el comando Tabla en el menú Datos



En nuestro ejemplo mostramos el caso de una Tabla de Datos de doble entrada (Two-Input), que enseguida explicaremos. Por supuesto existe también el caso de Tabla de Datos de entrada simple (One-Input Data Table).

Para generar nuestra Tabla de Análisis de Sensibilidad necesitamos un modelo de cálculo como en nuestro ejemplo.
Luego creamos una matriz para los resultados con el siguiente diseño:
1 – la fórmula que da el resultado aparece en el ángulo superior izquierdo de la matriz (o una referencia a la celda que la contiene, como en nuestro ejemplo)

2 – en la fila superior de la matriz anotamos los distintos valores para una de las variables de nuestro modelo (en nuestro ejemplo, el monto de las ventas);
3 – el la columna izquierda de la matriz anotamos los distintos valores de la segunda variable (en nuestro ejemplo, el porcentaje de los gastos variables respecto a las ventas);
4 – Seleccionamos toda la matriz (en nuestro ejemplo el rango B12:G17)

y activamos el menú Datos--->Tabla

5 – en la ventanilla "celda de entrada (fila)" señalamos la celda que contiene el valor de la variable correspondiente (en nuestro caso B4, el monto de las ventas)

6 - en la ventanilla "celda de entrada (columna)" señalamos la celda que contiene el valor de la variable correspondiente (en nuestro caso C6, el porcentaje de los gastos variables);
7 – apretamos "aceptar" y Excel calculará automáticamente los valores para cada una de las intersecciones en la matriz.
8 - La fórmula, o la referencia a ella, que aparece en la celda superior izquierda de la matriz puede causar confusión. Por lo tanto conviene ocultarla. Una de las formas de hacerlo es formarla el font con el mismo color del celda, de manera que se torne invisible.

Si usamos Tablas de Datos con una sola entrada, marcamos sólo la variable que corresponde a la fila (o columna).

Categorías: Manejo de Datos_

Technorati Tags: Tabla de Datos, Analisis de Sensibilidad