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.