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

Catálogo de Imágenes en Excel – otra versión

Crear catálogos de imágenes en Excel es uno de los temas más consultados en este blog. Hace un poco más de dos años y medio publiqué la primer nota sobre base de datos de imágenes que se convirtió en una de las más leídas del blog.

La técnica empleada para crear el catálogo de imágenes en Excel no es trivial y a principios de este año publique una nota aclaratoria que incluye un enlace para descargar una animación del proceso.

Además de los problemas potenciales que señalaba en la nota original, si guardamos las imágenes en el cuaderno, tal como muestra la técnica empleada en la nota original, el archivo tiende a volverse muy “pesado”, en especial si guardamos imágenes de alta resolución.

En esta nota mostraremos una técnica distinta, donde las imágenes están guardadas en alguna carpeta del sistema (computador personal o red) y sólo usamos Excel para mostrarlas de acuerdo a un número de código que se haya elegido en alguna celda.

Empezamos por guardar las imágenes en una única carpeta. Por comodidad las pondré en D:\catalogo





En el siguiente paso construimos el cuaderno Excel que tendrá dos hojas, Lista y Catalogo.
Como su nombre indica, en la primer hoja pondremos la lista de imágenes pero en lugar de guardar la imagen en la hoja pondremos la referencia a la dirección en el sistema





Podemos ver que los valores en la primer fila no son números de código y dirección en el disco duro. La función de estos valores será explicada más adelante.

Ahora creamos dos rangos dinámicos que encapsularemos en dos nombres

imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),1)

path_imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),2)

Estos nombres se adaptan automáticamente a la cantidad de filas en la lista usando la técnica que ya hemos mostrado en varias oportunidades. “Imagenes” se refiere al rango de la columna A que contiene valores; “path_imagenes“ se refiere al rango A:B con el número de filas correspondiente. Estos nombres serán usados en la hoja “Catalogo” donde mostramos las imágenes.

En la hoja Catalogo creamos una lista desplegable usando la opción Lista de Validación de Datos




Los valores de la lista desplegable están definidos por el rango dinámico “imagenes”



Como pueden ver, el primer valor de la lista sirve como indicación al usuario qué es lo que tiene que hacer. Los valores de la lista desplegable sirven como argumentos en para la función BUSCARV que ponemos en la celda C3
=BUSCARV(A3,path_imagenes,2,0)
Esta fórmula da como resultado la dirección de la imagen (que pusimos en la hoja Lista).

Nuestro próximo paso es poner en la hoja un medio de mostrar la imagen correspondiente al código que hayamos elegido. Activamos la barra de herramientas Cuadro de Controles y elegimos el control Imagen





Ponemos el control en una posición cercana a la lista desplegable y para mejorar el aspecto de la hoja quitamos las líneas de división. La posición del control no es casual y nos sirve para ocultar la fórmula que hemos puesto en C3.




Ahora necesitamos un método para cargar la imagen correspondiente, lo que haremos con una macro, y un evento que haga correr la macro cuando el usuario elige un número de código.

Sub show_pic() es la macro para cargar la imagen en el control

Sub show_pic()

Dim PicAddress

PicAddress = Sheets("Catalogo").Range("C3").Value

If IsError(PicAddress) Then
Sheets("Catalogo").Image1.Picture = Nothing
Else
Sheets("Catalogo").Image1.Picture = LoadPicture(PicAddress)
End If

End Sub

Esta macro va en un módulo común del editor de VBa.
Para que la macro corra cuando se elige un número de código programamos este evento en el módulo de la hoja Catalogo

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celControl As Range

Set celControl = [A3]

If Union(Target, celControl).Address = celControl.Address Then show_pic

End Sub


Elegimos un número de código en la lista desplegable, lo que dispara la macro que carga la imagen correspondiente en el control




La ventaja de esta técnica reside en que al no guardar las imágenes en un cuaderno de Excel, éste es muy estable y nos permite usar imágenes de alta resolución.

Finalmente agregaremos dos mejoras a nuestro modelo. Tenemos que evitar que el usuario guarde el cuaderno con una imagen cargada en el control. Esto puede suceder de dos maneras:

1 – que el usuario intente cerrar el cuaderno y Excel le pida guardarlo antes de cerrarlo (y que el usuario decida guardarlo)

2 – que el usuario decida guardar el cuaderno por propia iniciativa.

La idea no es evitar que el usuario guarde el cuaderno, sino que al hacerlo no haya ninguna imagen cargada en el control.
Para lograr esto programamos dos eventos en el objeto ThisWorkbook





Workbook_BeforeClose

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub


Workbook_BeforeSave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub


El ejemplo se puede descargar aquí. Para que el modelo funcione hay que reemplazar los valores del rango A2:B6 por referencias a archivos que existan en el computador o red del usuario.



Technorati Tags: MS Excel