Medir fracciones de segundos en Excel (décimas, centésimas, milésimas)

Uno de mis lectores me consulta cómo hacer cálculos de diferencia de tiempos en Excel tomando en consideración fracciones de segundos. Es decir, expresar los tiempos con décimas, centésimas y/o milésimas de segundos.

El formato de Excel para expresar fracciones de segundos es

hh:mm:ss,000

Este es un formato personalizado y debemos definirlo usando el diálogo Formato de Celdas




Dependiendo de las definiciones del sistema, la coma en "ss,000" debe ser reemplazada por un punto.

Digamos que tomamos los tiempos de una competición de natación. Para calcular la diferencia de tiempos empezamos por dar el formato adecuado a los rangos que contendrán los tiempos (en nuestro caso mm:ss,000, es decir minutos, segundos y fracción de segundo); luego ponemos los tiempos y calculamos la diferencia



Un detalle interesante de notar es el siguiente:



A pesar de haber escrito en la celda 01:24,140, la barra de las fórmulas muestra 12:01:24 a.m. Esto no tendría gran importancia si no fuera por el siguiente hecho:

- seleccionamos la celda B2 de nuestra tabla, que contiene el tiempo del primer nadador
- ahora presionamos F2 para editar el contenido de la celda
- inmediatamente presionamos Enter, cuidándonos de no cambiar nada en la celda



El resultado es que Excel, a pesar del formato que hemos dado, cambia el contenido de la celda a la hora del día.

La única explicación que le encuentro a este fenómeno es que si bien Excel usa las fracciones de segundos para los cálculos, no los expone en la barra de fórmulas. Por esto al editar el contenido de la celda, las fracciones de segundo se pierden.

La conclusión es que cuando usamos Excel para cálculos que incluyen fracciones de segundos, debemos tener mucho cuidado cuando editamos las celdas.
Otra conclusión importante es que la máxima precisión posible con Excel es de milésimas de segundo. Si agregamos un cero más al formato mostrado más arriba, por ejemplo mm:ss,00000, Excel seguirá exhibiendo los resultados hasta la precisión de milésimas de segundo como con mm:ss,000.

Una solución para estos problemas es usar una función definida por el usuario (UDF) como la propuesta por Doug Jenkins en un comentario a una nota en Daily Dose of Excel:

Function ETime(Hours As Double, Mins As Double, Secs As Double) As Double
ETime = ((Secs / 60 + Mins) / 60 + Hours) / 24
End Function

Esta función permite usar fracciones de segundos más precisas que las milésimas de segundo.

Technorati Tags: MS Excel

Atajos de teclado en Excel

Como soy un ferviente usuario de estos atajos (también llamados métodos abreviados), ya en el pasado he publicado una nota sobre Métodos abreviados en MS Excel para aplicar bordes a celdas .
En el último setiembre David Gainer publicó una tabla con todos los atajos de teclado de Excel.
No todos estos atajos tienen el mismo grado de utilidad o practicidad.
Algunos ejemplos:

- Ctrl + F1 abrir Panel de Tareas

- Shift + Alt + F1 agrega una hoja al cuaderno

- Ctrl + 1 abre el diálogo de formato de celdas

- Shift + Ctrl + 1 da formato decimal con posiciones después del punto

- Ctrl + 5 produce efecto de tachado al contenido de la celda

- Ctrl + 9 oculta la fila

- Ctrl + ' copia la celda superior y queda en estado de edición

- Ctrl + - abre el diálogo de eliminar celdas

- Alt + = inserta la función SUMA

- Shift + Ctrl + = abre el diálogo Insertar Celdas

- Ctrl + Z rehacer

- Ctrl + Y rehacer borrar

Technorati Tags: MS Excel

Rangos tridimensionales en Excel (3D ranges)

En la nota anterior sobre CONTAR.SI con rangos en varias hojas de Excel mencionábamos los rangos tridimensionales (rangos 3D).
Rangos tridimensionales son aquellos que se extienden a más de una hoja. Supongamos un cuaderno con tres hojas, donde cada hoja contiene datos de ventas de los agentes de una empresa. Cada hoja contiene los datos de un año determinado. Por ejemplo, la tabla de la Hoja1 se ve así:

La Hoja2 y la Hoja3







Si queremos sumar el total del Agente 1 para los años 2002 (Hoja1), 2003 (Hoja2) y 2004 (Hoja3) podemos usar esta fórmula:

=SUMA(B2;Hoja2!B2;Hoja3!B2)

Pero en lugar de usar referencias separadas para cada rango (la celda B2 en cada hoja, en nuestro ejemplo), podemos usar un rango tridimensional:

SUMA(Hoja1:Hoja3!B2)

De la misma manera, si queremos sumar las ventas de todos los agentes a lo largo de los tres años, usamos la fórmula

=SUMA(Hoja1:Hoja3!B2:B6)

O el promedio de ventas de los agentes a lo largo de los tres años

=PROMEDIO(Hoja1:Hoja3!B2:B6)


No todas las funciones pueden ser usadas con rangos tridimensionales. De acuerdo a la ayuda de Excel, las funciones que permiten el uso de rangos 3D son:
SUMA, PROMEDIO, PROMEDIOA, CONTAR, CONTARA, MAX, MAXA, MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA.
A juzgar por un artículo del año 1999 del Excel Experts E-letter (EEE) publicado por David Hager, hay más funciones que aceptan rangos 3D que las mencionadas por la ayuda de Excel. Entre ellas: PERCENTIL, CUARTIL, K.ESIMO.MENOR, K.ESIMO.MAYOR, JERARQUIA, MEDIANA, MEDIA.ACOTADA, COEFICIENTE.ASIMETRIA, Y, O, DESVPROM, DESVIA2, SUMA.CUADRADOS.


Como con todo rango en una fórmula, este puede ser introducido manualmente, escribiendo cada uno de los términos, o "apuntando" al rango con el mouse.
En este caso, empezamos escribiendo el nombre de la función



Luego, manteniendo la tecla Mayúsculas (Shift) apretada señalamos con el mouse la pestaña de la última hoja en nuestro rango



Finalmente marcamos el rango de celdas



Los rangos tridimensionales no pueden ser usados con fórmulas matriciales. Es posible hallar muchas UDF's (funciones definidas por el usuario) en la red que dan respuesta al uso de rangos tridimensionales en fórmulas matriciales o funciones que no usan este tipo de rangos.

Mi favorito es el complemento MOREFUNC.XLL de Laurent Longre, ya mencionado en mi nota anterior, y que puede descargarse aquí.
Este complemento trae varias funciones que trabajan con rangos 3D y amplían su uso más allá de la capacidad de las funciones nativas de Excel:

COUNTIF.3D: la función CONTAR.SI con rangos 3D

THREED: convierte un rango 3D en un rango simple, permitiendo de esta manera el uso de rangos tridimensionales en fórmulas matriciales.

Por ejemplo, si queremos contar todos los valores mayores a 5000 en las tres hojas de nuestro ejemplo, al usar la fórmula

=CONTAR.SI(Hoja1:Hoja3!B2:B6;">5000")

el resultado es #¡VALOR!

En cambio usando =COUNTIF.3D(Hoja1:Hoja3!B2:B6;">5000") obtenemos el resultado "10".

Para usar estas fórmulas debemos primero instalar el complemento. Luego podeemos usar el asistente de funciones



y seleccionar la función

Technorati Tags: MS Excel