martes, octubre 30, 2007

Comentarios y consultas en el blog

Todo deleite tiene su precio, o como dicen los americanos, "no free meals". De vuelta de las vacaciones me he encontrado con más de 50 mails esperando respuesta y más de 20 comentarios que deben ser moderados. En las tres semanas que han pasado desde mi regreso he logrado moderar todos los comentarios y responder a parte de los mails.
Dado que también debo ocuparme de mi trabajo (como decimos los argentinos: "hay que parar la olla"), tomará su tiempo hasta que pueda ponerme al día con todos. Es más, supongo que algunos mails quedarán sin responder.

Esta es una buena oportunidad para hacer algunas aclaraciones en lo que respecta a mails y comentarios.

Los comentarios están ligados a una nota específica y su cometido es, como su nombre lo dice, comentar o aportar algo sobre la nota. Los comentarios son uno de los indicadores más importantes de la relevancia y del éxito del blog.

Cada vez veo más casos de lectores que usan el comentario como medio de contacto conmigo o para plantear consultas fuera del contexto del tema de la nota.
No tengo dudas que todo está hecho con las mejores intenciones. Pero consultas específicas, más aún sino están ligadas al tema de la nota, deben ser dirigidas a mi dirección electrónica: jorgedun@gmail.com.

En cuanto a las consultas, estas pueden ser clasificadas en tres categorías. Ciertas consultas se limitan a algún problema específico del tipo "como hago para realizar una búsqueda de acuerdo a dos criterios" o "cómo usar la función SI con más de 7 condiciones". Este tipo de consultas han dado origen a muchas de las notas que he publicado en este blog.

Otro tipo de consultas van más lejos. Por ejemplo, "tengo un cuaderno con 40 hojas con datos de regiones, meses, agentes de ventas y montos. Cómo hago para consolidar las hojas de acuerdo al criterio que elija el usuario y que produzca un informe con gráfico incluido".
Este tipo de consultas tocan muchas veces el límite de la consulta profesional. Sin embargo ha habido casos en los cuales les he dedicado muchas horas sin esperar ninguna recompensa económica, como el caso del modelo para un campeonato de fútbol. Este modelo se originó en un pedido de una escuela de fútbol sin fines de lucro manejada por voluntarios en un barrio pobre de Santiago de Chile.

Hay otras que van aún más lejos: "por favor, envíame un modelo para organizar la contabilidad de mi pequeña empresa que acabo de lanzar esta semana. Mi mail es XXX". Por lo general no respondo a este tipo de pedidos o, si la consulta está en el marco de mis conocimientos y tiempo disponible, envío un presupuesto tentativo del proyecto.

En resumen: gracias por los comentarios, gracias por las consultas. Como digo más arriba, estos son los indicadores más importantes del éxito del blog. Más que el número de visitantes diario (que el 25 de octubre último supero los 1500 visitantes diarios por primera vez). Y, por favor, usemos el comentario como herramienta para enriquecer la nota o para consultar temas relacionados con la nota. Toda otra consulta puede ser enviada vía mail.



Technorati Tags:

sábado, octubre 27, 2007

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:

miércoles, octubre 24, 2007

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:

lunes, octubre 15, 2007

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:

sábado, octubre 06, 2007

CONTAR.SI con rangos en varias hojas de Excel

Entre las muchas consultas (más de 50 y que serán respondidas por lo que pido paciencia a mis lectores) que se han acumulado durante mis vacaciones me encuentro con la siguiente:

Si uso CONTAR.SI en el rango de una hoja me funciona perfectamente, pero me sale
error si lo uso en un rango 3D (en varias hojas)

La función nativa CONTAR.SI de Excel no funciona con rangos tridimensionales. Una posibilidad es usar la función COUNTIF.3D del complemento desarrollado por Laurent Longre, y que ya había mencionado en mi nota sobre la función INDIRECTO con cuadernos Excel cerrados. Este complemento (add-in) puede descargarse del sitio de Laurent.
Una vez instalado el complemento, accedemos a la función el asistente de funciones




La función tiene los mismos argumentos que la función CONTAR.SI nativa de Excel



Hay que tener en cuenta que si la referencia es a un archivo remoto, éste tiene que estar abierto.

Me permito agregar que el complemento de Laurent Longre contiene una miríada de funciones muy útiles.


Otra solución es crear una función definida por el usuario (UDF), como las que aparecen en esta nota de Daily Dose of Excel.

En los próximos días espero publicar una nota más amplia sobre el tema de fórmulas tridimensionales en Excel.


Technorati Tags: