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

Crear el cuaderno Personal.xls en Excel 2007

Cuando queremos que una macro esté disponible para todo cuaderno abierto en una sesión de Excel, el método más directo de hacerlo es guardar el código en el cuaderno Personal.xls. En el pasado he mostrado como crear el cuaderno Personal.xls cuando este no existe.
En Excel 2007 el proceso es similar con algunas pequeñas diferencias funcionales que mostraré en esta nota.

Como en las versiones anteriores a Excel 2007, si el cuaderno Personal.xls (en Excel 2007 el cuaderno es Personal .xlsb) no existe podemos crearlo grabando una macro y ligándola a este cuaderno. Para comenzar a grabar una macro en Excel 2007 activamos la pestaña Programador y apretamos el icono de grabar




También podemos usar el icono de grabar macros que se encuentra en el ángulo inferior izquierdo de la hoja



Como en Excel 2003 (y versiones anteriores) elegimos la opción de guardar la macro en el libro de macros personal



Para saber dónde está guardado el cuaderno, o si existe, podemos hacer una búsqueda en las carpetas del computador o usar esta técnica:

1 – Abrimos el editor de Vb (con Alt+F11 o con Visual Basic de la cinta de opciones)
2 – Agregamos la ventana Inmediate con Ctrl+G o View—Inmediate Window
3 – En la ventana Inmediate ponemos este código y apretamos Enter: ?Application.StartupPath
4 – SI el resultado es una fila en blanco, el cuaderno no existe. En ese caso empezamos la grabación, como indiqué más arriba y la cerramos inmediatamente (no hay necesidad de grabar alguna acción).



5 – Ahora que hemos creado el cuaderno, podemos ver dónde Excel lo ha guardado

Technorati Tags: MS Excel