JLD en Castellano – balance del cuarto año

El tiempo vuela (o como diría mi profesora de Latín, tempus fugit). En unos días concluirá esta primera decena del siglo 21 y ha llegado el momento de presentar las estadísticas del blog a mis socios, sus lectores.

A pesar del título de la nota, sólo llevo estadísticas completas desde el año 2007 (gracias a los servicios de Statcounter), por lo que estaremos analizando estos últimos tres años.

En el 2009 470,000 lectores han visto casi 950,000 páginas. Esto significa un crecimiento de algo más del 11% en lectores y del 4% en las páginas vistas.

El blog ha seguido creciendo, conteniendo ahora 418 entradas que han merecido algo más de 3400 comentarios (incluyendo mis respuestas).

Estas estadísticas nos dan poca información así que decidí, siguiendo la corriente de mis últimas notas, organizar los datos en un dashboard

El gráfico en la sección izquierda del tablero nos permite comparar datos mensuales por año

La lista desplegable en el encabezado nos permite visualizar distintos tipos de datos y analizar sus tendencias.

Así vemos que el número de páginas vistas en el 2009 se ha mantenido estable, mientras que en el 2007 el crecimiento fue acelerado y la desaceleración empezó en el 2008.

Vemos que el número de visitantes se ha comportado de la misma manera

Otro efecto notable es que la cantidad de lectores y páginas vistas tiende a descender en los meses de julio y agosto (el verano en Europa), alcanza el máximo del año en Octubre para caer “en picada” en Noviembre y Diciembre.

Para agregar más información a partir de los mismos datos, he agregado cuatro gráficos, uno para cada tipo de datos, que muestran las tendencias de los tres años

He agregado líneas de tendencia para hacerlas más evidentes. En todo los casos vemos que la tendencia es ascendente pero también podemos observar que los últimos meses están por debajo de la línea de tendencia.
Podemos ver que el 2009 se diferencia en su comportamiento de los años anteriores y que ningún mes logro superar las cifras del 2008. Espero que esta tendencia se revierta en el año entrante.
Donde ha habido un gran crecimiento es en la cantidad de lectores del feed (de 30 en enero del 2007 a 1307 en diciembre del 2009).

Sólo me queda por desear a mis lectores lo mejor para el 2010 y agradecer a todos por el apoyo, por los halagos (a veces inmerecidos) y también por las (por lo general merecidas) observaciones.

Salud!

pd.: quien haya descargado alguno de los tableros anteriores (dashboard-1 o dashboard-2) y quiera recibir el archivo del tablero de esta nota sólo tiene enviarme un mail usando la misma dirección usada para la descarga.

Apertura de cuadernos Excel con contraseña

Pareciera ser que Microsoft evita tocar ciertos temas, o por lo menos, hacerlos públicos. No, no se trata de nada escandaloso. Por ejemplo, el caso de la función SIFECHA que no está documentada en ninguna versión de Excel, con la excepción de Excel 2000. El otro caso es la posibilidad de codificar la apertura de un cuaderno con una contraseña.

Excel permite determinar una contraseña sin la cual no se puede abrir el cuaderno. Las ventajas de esta funcionalidad son evidentes. Por ejemplo, podemos poner un archivo Excel en una red compartida y sólo quien conozca la contraseña podrá abrirlo.
Estas contraseñas pueden tener hasta 15 caracteres de largo y son “case sensitive” (distinguen entre mayúsculas y minúsculas).

Existen dos posibilidades para establecer una contraseña para abrir el cuaderno:



En Excel Clásico

# Con el menú Guardar Como

Después de crear el cuaderno activamos la opción Guardar Como, en la parte superior derecha activamos la opción Herramientas y apretamos Opciones Generales



En el formulario que se abre



En la ventanilla “Contraseña de apertura” introducimos la contraseña deseada. El botón “Avanzadas” permite elegir el tipo de cifrado. Las otras posibilidades son:
Crear siempre una copia de seguridad: si marcamos esta opción Excel guarda una versión del cuaderno bajo el nombre de “Copia de xxx.xls”.
Contraseña de escritura: esta contraseña sirve para controlar cambios al cuaderno uqe queramos guardar bajo el mismo nombre.
Se recomienda sólo lectura: la intención es señalar que el archivo sea sólo “read only”

Al introducir la contraseña de apertura, se abre un formulario de confirmación



Una vez protegido, al tratar de abrir el cuaderno, tendremos que introducir la contraseña



# Usando el menú Herramientas-Opciones-Seguridad



En Excel 2007, el proceso es similar. Usamos el botón del Office-Guardar Como. Aquí el botón de Herramientas se encuentra en la parte inferior a la izquierda



El resto del proceso es similar.

La segunda opción en Excel 2007 es con Office-Preparar-Cifrar Documento



Microsoft recomienda guardar las contraseñas en un cuaderno o documento. Claro que entonces tendríamos que protegerlo con una contraseña que tendríamos que guardar en otro documento que tendríamos que proteger con una contraseña que…ad infinitum

Cambios en el diseño del blog

Como seguramente habrán notado, he cambiado la plantilla del blog. Mi intención es lograr un diseño más claro, menos cargado, que el de la plantilla anterior.

En los próximos días seguiré haciendo cambios y agregando o quitando elementos. Ya que este blog está dirigido a sus lectores, vuestras opiniones, observaciones y sugerencias tienen una gran importancia para mí.

Están invitados a dejar vuestros comentarios en el formulario de comentarios que aparece al pie de la nota.

Technorati Tags: MS Excel

El cuaderno Personal.xls no aparece

El Personal.xls (en las versiones 97-2003 de Excel) o Personal.xlsb (en Excel 2007) es un cuaderno oculto que se activa cuando abrimos una sesión de Excel.

Toda rutina o función que guardemos en un módulo de este cuaderno estará disponible para todo cuaderno abierto en la misma sesión.

Ya hemos explicado que si el Personal.xls(b) no existe, podemos crearlo grabando una macro y guardándola en el este cuaderno

Este cuaderno está ubicado por defecto en la carpeta XLSTART. Para saber la ubicación exacta podemos usar la ventana Immediate del editor de Vba de esta manera



Como se puede ver en mi máquina la ubicación es

C:\Documents and Settings\locuser\Application Data\Microsoft\Excel\XLSTART

Todo cuaderno guardado en esta carpeta es activado al abrir una sesión de Excel.

Existen situaciones en las cuales hemos creado el Personal.xls(b) pero no es activado al abrir Excel. Esto puede deberse a muchas razones. Por ejemplo, puede haber sido borrado accidentalmente o haber sido movido a otra carpeta.

Muchas veces el remedio es volver a crear el cuaderno como hemos indicado más arriba. Pero si tampoco esto ayuda lo más probable es que el Personal.xls(b) haya sido incluido en la lista de elementos deshabilitados. Es decir, el cuaderno existe en la carpeta indicada pero Excel lo ha deshabilitado. Para “rehabilitarlo” usamos el menú Ayuda (puede aparecer también como “?”) – Acerca de Microsoft Excel

En la parte inferior de la ventanilla apretamos el botón Elementos deshabilitados



y en la lista de elementos deshabilitados



elegimos Personal.xls(b) y apretamos el botón Habilitar.

En Excel 2007 tenemos que apretar el botón de Office, luego el botón de Opciones de Excel, elegir la opción Complementos. En la parte inferior izquierda veremos la ventanilla Administrar, allí elegimos Elementos deshabilitados.

Technorati Tags: MS Excel

Icono de pegar fórmulas en Excel 2003

Si usamos a menudo la opción Pegar Fórmulas es muy práctico tener un icono para hacer el pegado con un solo clic. En Excel 2007 podemos agregarlo con facilidad a la barra de herramientas de acceso rápido

con la técnica para agregar iconos en la barra de acceso rápido.

Curiosamente en Excel 2003 esta opción no aparece en la opción Personalizar de las barras de herramientas



Una alternativa es grabar o escribir una macro y asociarla a un icono. Pero como de hecho el icono existe en la barra de herramientas Estándar



podemos sencillamente arrastrar el icono (en forma de texto) y posicionarlo en la barra de herramientas de manera que siempre esté visible.

Para hacer esto empezamos por crear una fórmula cualquiera en una celda y copiarla (hacemo9s esto para que la opción pegar fórmulas aparezca en la lista).

Luego usamos el menú Herramientas-Personalizar (o clic con el botón derecho en la zona de la barras de herramientas)

Ahora arrastramos el texto Fórmulas y lo ponemos, por ejemplo, al lado del icono de Pegado



A partir de ahora podemos pegar fórmulas con un solo clic.

Technorati Tags: MS Excel

Selecciones múltiples en Excel basadas en un valor

Ya hemos visto en el pasado cómo realizar selecciones múltiples usando Ir A (F5 o Ctrl+G). El problema surge cuando queremos hacer una selección múltiple basándonos en algún valor.
Por ejemplo, después de importar un informe del sistema ERP en mi empresa, tuve la necesidad de borrar todas las filas cuyas celdas en la columna A contengan la palabra “Total”.

Pongamos un ejemplo reducido

Necesitamos borrar todas las líneas con totales para poder usar los datos en una tabla dinámica.
Una solución posible es escribir una macro que haga la tarea. Pero el blog Bacon Bits trae una sugerencia mucha más sencilla y todo el crédito va para él.

Empezamos por seleccionar el rango relevante, en nuestro caso A1:A14. Abrimos el menú Buscar y reemplazar (Ctrl+B o Edición—Buscar) y en la casilla Buscar ponemos “Total” (o la palabra o combinación de valores que queremos usar). Apretamos Buscar todo

Todas las celdas aparecen en la ventanilla del diálogo pero sólo la primera en la lista está seleccionada (con un fondo azul). Apretamos Ctrl+E para elegir todas las celdas de la lista (o apretando Mayúsculas señalamos la última celda en la lista)

Al hacer esto, todas las celdas que contienen la palabra “Total” son seleccionadas.

Apretamos Cerrar y poniendo el marcador del mouse sobre una de las celdas seleccionadas abrimos el menú contextual con el botón derecho.

Elegimos Eliminar y Toda la fila y apretamos Aceptar

Eso es todo!




De la misma manera podemos aplicar un fondo de color a las celdas seleccionadas, o borrar el contenido, etc.

Technorati Tags: MS Excel

Formato indeseado de número en Excel

Cuando introducimos un valor en una celda, Excel intenta determinar el tipo de valor y darle el formato adecuado. Por ejemplo, si escribimos 1/2 en una celda Excel lo transforma al primero de febrero del año corriente



Lo mismo sucede si el valor contiene un guión.

Si el número (de hecho, un conjunto de cifras) contiene dos puntos o las letras A o P antecedidas por un espacio, el valor es transformado en hora



Si ingresamos un número que contiene ceros iniciales, Excel elimina los ceros y convierte el valor en número. Además y como ya hemos visto, si el número excede las 12 cifras Excel aplica el formato científico y si se exceden las 15, las cifras supernumerarias son transformadas en ceros. Esto último lo he vivido en carne propia hace varios años atrás cuando tuvimos que ingresar números de tarjetas de crédito (que tienen 16 cifras).

El remedio a este tipo de situaciones es darle el formato adecuado al rango de celdas que vamos a usar, evitando así que Excel decida por su cuenta. Existen también algunos trucos de teclado, en caso que queramos ahorrarnos el proceso de dar formato a las celdas.

# Si queremos ingresar números con ceros iniciales o códigos de tarjetas de crédito debemos darle al rango el formato Texto. Otra alternativa es poner un apóstrofe al comienzo (por ejemplo, '00001509).

# Si queremos ingresar fracciones debemos dar el formato Fracción o escribir la parte entera de la fracción, dejar un espacio y escribir el resto de la expresión. Por ejemplo, para escribir 2 1/3, escribimos 2, apretamos la barra de espacios e inmediatamente 2/3



Nótese que en la barra de las fórmulas el valor aparece como 2,3333333333

Si queremos ingresar una fracción de tipo x/y, por ejemplo 3/4 escribimos en la celda 0 3/4




# Si queremos ingresar una expresión que indica relación entre dos valores, por ejemplo 5:6, debemos formar la celda como Texto o anteceder la expresión con un espacio (o apóstrofe).

Números grandes en Excel – segunda nota

Excel guarda los números con un límite de precisión de 15 cifras. Esto se hace evidente cuando introducimos un número de más de 15 cifras. Veamos qué pasa cuando introducimos el número 123456789123456789 (18 cifras) en una celda a la cual le hemos de antemano el formato de número para evita que Excel ponga el formato científico automáticamente




Al apretar Enter todas las cifras a la derecha de la posición 15 son convertidas en ceros.
El mismo problema existe cuando la parte fraccional de un número supera las 15 cifras.


Por lo general esta limitación no es significativa, en especial si usamos Excel para cálculos financieros. Si necesitamos operar con mayor precisión de la que permite Excel dos posibilidades son:


# - descargar el complemento xlPrecision


# - utilizar los conceptos esbozados por Tushar Metha en esta nota



La ventaja del complemento xlPrecision es que también soluciona el problema precisión y redondeo que existe en Excel.



Es este el problema de la conversión binaria (no sólo de Excel) que se hace evidente con este ejemplo: restamos 4.0 de 4.1

Aparentemente el resulta es el correcto: 0.1. Veamos qué pasa cuando formamos la celda para que muestre más de 15 decimales

El resultado no es exactamente 0.1.

Technorati Tags: MS Excel

Números grandes en Excel – primera nota

Un lector me consulta cómo hacer en Excel para que cuando introducimos un número grande éste no se convertido en forma automática a la notación científica.

Por ejemplo, si introducimos el número 123456789123 en una celda, lo que veremos en pantalla es 1.23457E+11

Esta consulta involucra en realidad dos temas:

# formato automático de números en Excel (cómo exhibe Excel los números en la pantalla)

# cálculos con números de más de 15 cifras significativas.

En esta nota tocaremos el primer tema, y dejaremos el segundo para una futura nota.

Empecemos por ver un ejemplo donde nuestro número tiene por lo menos 12 cifras, pero menos de 15.

Volviendo a nuestro ejemplo (que tiene 12 cifras) veamos qué significa 1.23568E+11.

La notación E+11 es una forma abreviada de escribir el número. Esta notación nos dice que hemos tomado el número y lo hemos divido por 10 elevado la potencia de 11 (es decir 100,000,000,000).

Cuando se trata de números grandes, Excel pone el formato científico automáticamente si la celda está formada como General (el formato por defecto de Excel).

Si queremos evitar que Excel ponga el formato científico tenemos que dar un formato de número a la celda antes de ingresar el número. Si el número tiene menos de 15 cifras, podemos cambiar el formato después de haber introducido el número.

En esta imagen podemos ver que si bien los números en la columna A aparecen en notación científica, Excel los “ve” tal como son; en la columna B vemos los mismos números introducidos en celdas previamente formadas con formato numérico

Ahora veamos una “curiosidad” de Excel cuando se trata de números grandes



En las celdas A1:A2 hemos puesto el número 123456789123 que Excel exhibe como 1,23457E+11. En la barra de fórmulas podemos ver que sólo se trata de exhibición; Excel sigue “viendo” el número tal como la introducimos en la celda. En el rango B1:B2 ponemos el mismo número pero previamente hemos formado las celdas como Número.

Ahora usaremos el icono de aumentar decimales para agregar un decimal en las celdas A1 y A2

Como puede observarse, se ha producido una pérdida de precisión. Ahora el número en las celdas, tanto en el rango A1:A2 como en B1:B2, es 123456800000!

Hasta aquí hemos usado un número de menos de 15 cifras. ¿Qué paso cuando usamos un número de más de 15 cifras?

En la celda A1 introducimos el número 123456789123456789 (18 cifras)

Sin embargo, el número que aparece en Excel después de apretar Enter es 123456789123456000

Es decir, las últimas tres cifras han sido reemplazadas por ceros!. Toda cifra después de la posición 15, será convertida a cero. Esto se debe a una limitación, no solamente de Excel, que será explicada en la próxima nota sobre el tema.

Lo que debemos saber a esta altura de los acontecimientos es que:

# Excel tiene una limitación de precisión si el número excede las 15 cifras significativas

# para introducir números de más de 15 cifras debemos convertirlos en texto, ya sea anteponiendo un ' o dando el formato Texto a la celda antes de introducir el número.

Esto genera un serio problema si tenemos que realizar cálculos con números grandes. Como enfrentarnos a estos problemas será el tema de la próxima nota.

Technorati Tags: MS Excel

Cambios en el diseño, consultas de lectores y encuestas.

Este mayo ha sido un mes intenso. Mis ocupaciones cotidianas apenas me han dejado algo de tiempo para este blog. Como habrán notado la cantidad de notas publicadas este mes es sensiblemente menor a lo habitual. También he respondido a muy pocas de las consultas que me llegan por mail.

En el escaso tiempo disponible he hecho un primer intento de cambiar el diseño del blog. La intención es hacerlo más claro y aprovechar mejor el ancho de pantalla (ya que la mayoría de mis lectores usan resolución de pantalla 1024X768). Espero recibir vuestros comentarios.

A la encuesta sobre el tipo de usuarios de Excel respondieron 359 lectores. La mayoría, el 42%, se considera “intermedio”, un cuarto se consideran “avanzados, lo mismo los “principiantes” y el restante 7% “súper avanzados”.






Si bien había propuesto algunos criterios generales para la evaluación, supongo que cada uno se califica de acuerdo a su propia percepción del nivel de conocimientos y destreza con Excel.
Hace 10 años atrás mi respuesta a la pregunta cuál es mi nivel de Excel hubiera sido “avanzado”. Desde la perspectiva de mi nivel actual la respuesta correcta tendría que haber sido “básico-intermedio”.

Este tema es a la vez interesante y complejo. Hace unos días me consultaban del departamento de Recursos Humanos qué preguntar para establecer el grado de conocimientos de Excel de los postulantes a un determinado puesto. Mi respuesta fue que esto dependía de la definición de las tareas que el postulante tendría que realizar. En la medida que el puesto no incluya desarrollo de aplicaciones con Excel, un conocimiento superficial de Vba sería suficiente. Pero si estamos buscando alguien para el departamento de control de gestión, un buen dominio de tablas dinámicas es esencial. Un buen dominio del tema de gráficos no me parece crítico, pero sí lo es el conocimiento de las funciones de búsqueda (BUSCARV, INDICE, COINCIDIR, etc).

Technorati Tags: MS Excel

Análisis de sensibilidad dinámico con Excel

Hace dos años atrás comentaba el uso de la función Tabla para hacer análisis de sensibilidad. Esta herramienta se encuentra en el menú Datos en Excel 97-2003 o en la pestaña Datos—Análisis Y si en Excel 2007

Por ejemplo, este modelo analiza la sensibilidad del pago mensual por un préstamo en función de la tasa de interés anual y el número de cuotas



Podemos apreciar los valores de los parámetro de la tabla (la tasa de interés anual y el número de cuotas) se “distribuyen” hacia arriba y hacia abajo en relación al valor central. La tasa de interés crece o decrece en “saltos” de 0.25% y el número de cuotas en escalones de 5 años.

Estos valores son constantes y es evidente que lo ideal sería que nuestro modelo fuera dinámico. Si cambiamos la tasa de interés en la tabla superior, queremos que los parámetros de la tabla de sensibilidad se ajusten automáticamente. Lo mismo con el número de cuotas.

Parece bastante evidente que todo lo que tenemos que hacer determinas los valores de los parámetros con una fórmula y ligar el parámetro “central” a la celda correspondiente en la tabla superior. El problema es que TABLA no se comporta como hubiéramos supuesto. Veamos que pasa cuando determinamos los valores de los parámetros con fórmulas

Por ahora hemos dejado los valores de las celdas E7 y B10 constantes. En C7:D7 hemos puesto la fórmula “=E7-0.25%”, en F7:G7 “=E7+0.25%”. Lo mismo con el número de cuotas; en B11 ponemos “=B10+5”. Como podemos ver, los resultados se mantienen.

Ahora queremos ligar E7 a B2 y B10 a B3 para que cuando cambiemos alguno de estos valores, la tabla de sensibilidad se adapte automáticamente.

El resultado es desastroso!

La solución es programar un evento para cambiar los datos sin usar referencias a otras celdas en la hoja.

El primer paso es definir nombres para las celdas B2, B3, E7 y B10. Esto nos permitirá simplificar el código del evento y hacerlo más claro. La forma más práctica de definir los nombres es usando el cuadro de nombres

Los nombres que hemos definido son ("variable" es el nombre de la hoja que contiene la tabla) :

cuotas=variable!$B$3

paramCuotas=variable!$B$10

paramInteres=variable!$E$7

tasa_interes=variable!$B$2

Abrimos el editor de Vba usando la opción Ver Código de la hoja

En el módulo de la hoja ponemos este código


Private Sub Worksheet_Change(ByVal Target As Range)

If Union(Target, Range("tasa_interes")).Address = Range("tasa_interes").Address _
Or Union(Target, Range("cuotas")).Address = Range("cuotas").Address Then
Range("paramInteres") = Range("tasa_interes")
Range("paramCuotas") = Range("cuotas")
End If

End Sub



Como estamos usando el evento Worksheet_Change, cada vez que cambie el valor de la celda B2 o B3, la tabla de sensibilidad es vuelta a calcular. Usamos UNION para evitar que el cáclulo se efectúe sólo si el cambio se produjo en las celdas B2 o B3.

Technorati Tags: MS Excel

Lectores del feed

Por primera veo que el feed del blog ha superado los 1000 lectores



No se cuan importante pueda ser este hecho, pero no quería dejar de compartirlo con mis lectores.

Auditoría de fórmulas en Excel – señalar precedentes y dependientes

Ya hemos hablado en el pasado sobre la barra de auditoría de fórmulas. Es ésta una excelente herramienta para controlar y auditar las fórmulas en las hojas. Sin embargo, y de acuerdo a mi experiencia es poco usada y menos aún conocida por gran parte de los usuarios, incluidos aquello que se consideran usuarios avanzados.

En esta nota mostraremos sucintamente el rastreo de celdas precedentes y dependientes en un cuaderno de Excel.

Precedentes son celdas que afectan el valor de la celda inspeccionada. Dependientes son las celdas afectadas por la celda seleccionada. Veamos esto con un ejemplo

En Hoja1 tenemos una tabla de bonos que corresponden a intervalos de edades. En la hoja Nombres tenemos la lista de los nombres con sus respectivas edades.

En la celda E1 ponemos una lista desplegable con Validación de Datos, basada en la lista de nombres.



En la celda E2 ponemos la fórmula

=BUSCARV(E1,nombres!A2:B6,2,0)

que nos da la edad del nombre que aparece en la celda E1. Finalmente en la celda E3 ponemos

=BUSCARV(E2,A2:B7,2)

para determinar el bono de acuerdo a la edad.

Seleccionamos A7 (o cualquier celda en el rango A2:A7) y accionamos la opción Rastrear dependientes

Excel traza una flecha que indica que la celda E3 es afectada por el valor de la celda A7.



Ahora seleccionamos la celda E3 y activamos Rastrear Precedentes



Vemos que E2 es precedente de E3 (afecta su valor) y también todo el rango A2:B7 que además de la flecha aparece enmarcado con un borde de color azul.

¿Qué pasa cuando las celdas precedentes están en otra hoja? Ese es el caso de la fórmula en la celda E2 que es afectada por los valores de la tabla nombres en la hoja Nombres.
En ese caso Excel señala que se trata de una referencia remota poniendo una flecha y en su extremo el símbolo de una tabla.



Para ver las referencias a las celdas precedentes tenemos que apuntar con el mouse a la flecha (la figura del mouse cambia de una cruz a una flecha) y hacer un doble clic



Esto abrirá el menú de Ir A, donde podemos ver la lista de las celdas remotas que afectan a la fórmula



Podemos elegir una de las celdas de la lista y apretar aceptar para ir a la celda en cuestión.

Technorati Tags: MS Excel