Actualización automática de gráficos con listas.

Allá por el verano del 2006 (o el invierno, dependiendo de donde resida el lector) expliqué como crear un gráfico que se actualice automáticamente a medida que le agregamos datos.

La técnica consistía en usar rangos dinámicos en la función SERIES del gráfico. La ventaja de esta técnica es que es consistente con todas las versiones de Excel. Pero si usamos la versión 2003 o 2007 de Excel podemos usar una técnica mucho más sencilla.

Supongamos que tenemos esta tabla de ventas por mes a partir de la cual creamos un sencillo gráfico de columnas

Queremos que al agregar los próximos meses el gráfico se actualice automáticamente. Para hacerlo sacaremos provecho de la funcionalidad Datos--Listas.

Empezamos por seleccionar alguna de las celdas de la tabla, por ejemplo A1 y abrimos el menú Datos-Lista-Crear Lista



Excel selecciona automáticamente todo el área de la tabla



Después de controlar que la selección es la deseada, apretamos Aceptar. Excel ha creado ahora una lista y expandirá el área de la tabla automáticamente cada vez que agreguemos (o quitemos) una fila. Excel también abre la barra de herramientas de listas donde tenemos todo tipo de herramientas para administrarla



Para agregar una fila a la lista seleccionamos alguna celda del área. Veremos que aparece un marco azul alrededor del área de la lista y una estrella azul en la primer celda libre, donde debemos poner los datos



Todo los que nos queda por hacer es agregar los datos y el gráfico se actualizará automáticamente



En Excel 2007 esta técnica funciona de la misma manera pero con, como no podía ser de otra manera, algunas diferencias funcionales:

# - La funcionalidad no se llama Lista sino Tablas y no se encuentra en la pestaña Datos como en Excel 2003 sino en la pestaña Insertar



# - Al seleccionar alguna celda de la lista/tabla no aparece una nueva línea en blanco en la tabla. De todas maneras si agregamos un mes inmediatamente debajo del último registro de la tabla, Excel expande la lista en forma automática. Otra técnica en Excel 2007 es usar la tecla Tab. Por ejemplo, si después de introducir el dato de setiembre en la celda B10 apretamos TAB, Excel selecciona automáticamente la celda A11 y expande la tabla.

Microsoft ha agregado muchas otras herramientas y funcionalidades a las tablas en Excel 2007, pero esto será tema de una futura nota.

Technorati Tags: MS Excel

Insertar un calendario permanente en Excel 2007.

Hace ya más de dos años publiqué la nota sobre como insertar un calendario permanente en hojas de calculo Excel con Controles ActiveX. Eran los días previos a la aparición de Excel 2007 y los controles estaban ligados a dos barras de herramientas, Formularios y Cuadros de Controles (controles ActiveX).
En Excel 2007 no hay barras de herramientas y los controles están ubicados en la pestaña Programador de la cinta de opciones

Nótese que todas las herramientas necesarias están a nuestra vista en la misma sección de la pestaña (Modo diseño, Propiedades, etc.).

Como pueden ver, al apretar Insertar vemos de hecho las mismas barras de herramientas a las que estábamos acostumbrados en las versiones anteriores de Excel.

También el modelo de validación de fechas programando un evento que muestre el calendario si se cumplen ciertas condiciones podemos usarlo en Excel 2007. Si bien la interfaz del editor de Visual Basic no ha cambiado algunos lectores me comentan que no encuentran el botón de controles adicionales. Al igual que en las versiones anteriores podemos usar el menú Herramientas-Controles Adicionales o hacer un clic con el botón derecho del mouse en cuadro de herramientas del UserForm

También podemos crear un calendario permanente en una hoja de Excel sin usar controles. Esto puede hacerse con fórmulas matriciales, como en este modelo desarrollado por John Walkenbach



Este modelo usa fórmulas matriciales de rango, es decir una fórmula que da el resultado en varias celdas simultáneamente. Para crear este modelo empezamos por introducir en la celda B2 el mes y el año del calendario. Podemos hacer esto poniendo en B2 la fórmula =HOY() y dando un formato “mmmm,aaaa” a la celda. Luego seleccionamos el rango B4:H9 asegurándonos que la celda activa sea B4, introducimos esta fórmula en la celda activa

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1);"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1)

y apretamos simultáneamente Ctrl+Mayúsculas+Enter

Podemos usar una fórmula más sencilla,

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-1)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

pero que mostrará las fechas en todas las casillas del rectángulo

Como ven, estos calendarios son “gringos” con la semana comenzando en domingo. Si queremos que la primer columna de la semana sea el lunes, modificamos levemente la fórmula

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7});"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7})

O esta fórmula

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-2)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

para el modelo sencillo

Technorati Tags: MS Excel

Auditoría de fórmulas en Excel - la ventana de inspección

En el pasado ya he mencionado la barra de auditoría de fórmulas. Vimos, por ejemplo, como localizar valores repetidos en una tabla o como analizar el funcionamiento de una fórmula.
Otra herramienta útil y poco conocida en esta barra es la ventana de inspección.

En Excel 2007 la ventana de inspección se encuentra en la pestaña de Fórmulas



El uso de esta ventana es muy sencillo. Supongamos un modelo con el cual calculamos descuentos en función de la cantidad. En la Hoja1 calculamos los descuentos



Como pueden ver, el descuento se calcula dinámicamente en base a una tabla de descuentos que se encuentra en la Hoja2



Si queremos investigar como influyen las distintas tasas de descuentos al resultado, tenemos que navegar a la Hoja2, cambiar las tasas, y luego volver a la Hoja1 para ver el resultado.

Una alternativa es crear referencias a las celdas de la Hoja1 en la Hoja2. Una alternativa más elegante y eficiente es usar la ventana de inspección.

En nuestro caso vamos a la Hoja1 y abrimos la ventana de inspección apretando el icono en la barra de auditoría de fórmulas

Ahora seleccionamos las celdas que queremos inspeccionar, por ejemplo B3, B4 y B5 y apretamos "agregar inspección"



Apretamos agregar. Las celdas aparecerán en la ventana.

Podemos adaptar la ventana a nuestras necesidades ocultando campos que no nos interesan y ampliando el ancho de campos relevantes. Todo esto lo hacemos arrastrando los límites del campo con el mouse.



Navegamos a la Hoja2 y vemos que la ventana sigue flotando sobre la hoja



Todo cambio que ocurra en las celdas de la ventana de inspección se reflejará inmediatamente en la ventana.

También podemos usar esta funcionalidad con celdas en hojas de otros cuadernos.

Si las celdas a inspeccionar están definidas en nombres podemos hacer que éstos aparezcan en la ventana de inspección, facilitando de esta manera la lectura de los resultados

Technorati Tags: MS Excel