Pies de página autómaticos

Un usaurio me pregunta cómo insertar rápidamente un pie de página en una hoja. En realidad fueron tres consultas en una: cómo insertar autómaticamente un pie de página, cómo insertar un pie de página automáticamente al insertar una hoja, y cómo abrir un libro nuevo con pies de página incluídos. Dado que $oy amable, responderé a las tres, en ese mismo orden.

Antes de comenzar, diré que, extrañamente, Excel no tiene una manera directa de hacer esta configuración automáticamente. Obviamente, podemos ir a Archivo - Cofigurar página - Encabezado y pie de página - Personalizar pie de página... clic en el botón Ruta de archivo - Aceptar (¿así o más clics?) y hacer la configuración correspondiente. Pero si queremos que esto sea automático, la única vía es utilizando macros. Como aún no hemos visto prácticamente nada acerca de macros avanzadas, solo proporcionaré las macros y el modo de utilizarlas, sin detenerme a explicarlas. Posteriormente tendremos tiempo.

Supongamos que queremos insertar el siguiente pie de página: en la sección izquierda, la ruta completa del archivo, en la sección central la fecha, y en la sección derecha la leyenda "Elaboró: [nombre usuario]"

Para el primer caso, insertar un pie de página automáticamente, abrimos el libro de macros personal, con Herramientas - Macro - Editor de Visual Basic (o Alt + F11). Damos doble clic en PERSONAL.XLS. Si lo deseamos, insertamos un nuevo módulo, con Insertar - Módulo. En la zona de edición, escribimos, tal cual, el siguiente código (desde "Sub" hasta "End Sub"):

Sub piepagina()

Dim ws As Worksheet
Dim nombre As String

Set ws = ActiveSheet
nombre = Application.UserName

---With ws.PageSetup
------.LeftFooter = "&11&Z&F"
------.CenterFooter = "&11&D"
------.RightFooter = "&""Arial,Normal""&11Elaboró: " & _
---------UCase(Left(nombre, 1)) & Mid(nombre, 2, InStr(1, nombre, ".") - 2) _
---------& " " & UCase(Mid(nombre, InStr(1, nombre, ".") + 1, 1)) & _
---------Mid(nombre, InStr(1, nombre, ".") + 2)
---End With

End Sub

Damos Archivo - Guardar (o Ctrl + S), y cerramos la ventana del Editor de Visual. A continuación, hacemos una prueba: Activamos la hoja a la que queremos insertar el pie de página, y ejecutamos la macro, con Herramientas - Macro - Macros (o Alt + F8) - Ejecutar.
Verificamos con Vista previa.

Finalmente, asignamos un método abreviado a la macro. Hay que volver al cuadro Macro (alt + F8). Clic en Opciones... y en Tecla de método abreviado: escribimos la letra que deseemos (cuidándonos de no utilizar un método que ya esté siendo utilizado, como Ctrl + c, ya que Excel ejecutaría siempre la macro, no la instrucción original, en este caso, copiar). Digamos Ctrl + q. Clic en Aceptar y cerramos el cuadro Macro. Ahora, al presionar Ctrl + q, Excel insertará el pie de página en la hoja activa.

Continuamos en la siguiente nota.

Navegar en el libro - tip

No es infrecuente trabajar con libros que tienen demasiadas hojas. Lo que es sí es infrecuente es recordar en que posición exacta se encuentran algunas hojas específicas, lo cual nos obliga a recorrer de principio a fin todas las etiquetas de hojas del libro en cuestión. Un proceso muy lento, a veces. Una forma poco conocida de viajar rápidamente a determinada hoja es, en los botones de navegación...

dar clic derecho, con lo cual Excel nos presentará una mini lista con los nombres de todas las hojas de nuestro libro:

Damos clic en el nombre de la hoja a la que queremos ir (o bién, en Más hojas...), y listo.

Esto no nos quita la obligación de manejar cierta organización en nuestras hojas. Yo, por ejemplo, coloco primeramente, en el extremo izquierdo del navegador, las hojas de resumen de información, a continuación, en ese orden, las hojas de cálculos, hojas de tablas de valores, hojas de datos de entrada y al final, formatos de salida. Así, podrán tener una idea más o menos clara de a que zona dirigirse en cada situación.

La función NOD

NOD es una función bastante simple. Lo único que hace es devolver el error #N/A. La sintaxis:

NOD()

Como vemos, ni siquiera lleva argumentos (aunque no deja de ser obligatorio el uso de paréntesis). La siguiente fórmula:

=NOD() devuelve #N/A.

También es válido escribir #N/A directamente en las celdas. La ayuda de Excel dice:

"Devuelve el valor de error #N/A, que significa "no hay ningún valor disponible". Utilice #N/A para marcar las celdas vacías. Si escribe #N/A en las celdas donde le falta información, puede evitar el problema de la inclusión no intencionada de celdas vacías en los cálculos. (Cuando una fórmula hace referencia a una celda que contiene #N/A, la fórmula devuelve el valor de error #N/A.)
...
Debe incluir paréntesis vacíos con el nombre de la función. De lo contrario no se reconocerá como función.

También puede escribir el valor #N/A directamente en la celda. La función NOD se proporciona por compatibilidad con otros programas para hojas de cálculo. "

Puedo escucharlos: "¿Para qué diablos utilizaría yo esta función?" Un ejemplo podría ser cuando queremos excluir celdas vacías de un cálculo, o bién, forzar la entrada de valores en un rango, por decir:

=SI(A2<>"", A2+B2, NOD())

Suponiendo que después hacemos la sumatoria de la columna que tiene esta fórmula, basta con que uno solo de los resultaldos sea #N/A, para que la sumatoria sea también #N/A. Cualquier fórmula que hace referencia a un valor #N/A, devuelve #N/A. Así, Excel nos alertaría que omitimos ingresar algún valor.

Hay otra situación, más interesante, en la que puede ser útil esta función. Supongamos que queremos graficar la columna de Visitas Totales de la tabla siguiente:

Como vemos, esta tabla contiene algunos ceros. La fórmula de E2 es =SUMA(B2:D2). Si elaboramos una gráfica estándar obtenemos lo siguiente:

Desde luego esta es una gráfica técnicamente correcta. Sin embargo, hay ocasiones en las que deseamos graficar únicamente los valores distintos de cero de una tabla. Cuando esto ocurre, podemos (previa selección de la gráfica) ejecutar Herramientas - Opciones - Gráfico - Trazar celdas vaciás como: - Interpolar.

Otra solución, menos "invasiva" y más flexible, es convertir los ceros en #N/As utilizando NOD. Una gráfica de Excel ignora los errores #N/A, los interpola automáticamente, a diferencia de los ceros, que sí los grafica. Por ejemplo, la fórmula de la tabla puede ser:

=SI(SUMA(B2:D2)=0, NOD(), SUMA(B2:D2))

La tabla quedaría:

Y la gráfica:Otra ventaja de este método es que siempre estaremos seguros de que estamos interpolando los ceros. No tenemos que estar revisando las Opciones de gráfico para verificarlo.

La ayuda on-line no aporta nada extra.

El libro de macros personal

Existen (o seguramente existirán) algunas macros que consideraremos de "aplicación general", aquellas que deseemos que estén disponibles al editar cualquier archivo, y no únicamente en el archivo en el que están guardadas. Por otro lado, puede resultar molesto el tener que habilitar la ejecución de macros cada vez que abramos un archivo que contenga alguna. Una forma de enfrentar ambas situaciones es guardando las macros en nuestro libro de macros personal.

El libro de macros personal es un archivo que Excel carga automáticamente, en caso de que exista, al iniciarse (junto con algunos otros, como el archivo de barras de herramientas y los complementos que tengamos instalados). Contiene todas aquellas macros que queramos que estén disponibles en todo momento, habilitándolas automáticamente al iniciar Excel (esto es, sin preguntarnos si deseamos habilitarlas). Cuando lo creamos, Excel lo guarda en su carpeta de inicio, generalmente en la ruta:

C:\Documents and Settings\[nombre.usuario]\Datos de programa\Microsoft\Excel\XLSTART

Si no encuentran esta ruta en sus equipos, pueden hacer una búsqueda en Windows de "xlstart" para ubicar su carpeta. Si ésta está vacía o no contiene el archivo PERSONAL.xls, necesitaremos crearlo. La forma más sencilla es iniciando la grabadora de macros, con Herramientas - Macros - Grabar nueva macro. En este cuadro de diálogo, establecemos el valor Libro de macros personal en la opción Guardar en:

y aceptamos el cuadro. Acto seguido, terminamos la grabadora presionando el botón Detener grabación de la minibarra que aparece. De esta manera hemos creado el archivo. Ahora, cada vez que queramos grabar una macro de uso frecuente, seleccionaremos la opción Libro de macros personal en el mencionado cuadro. Si queremos que la macro solo esté disponible con un solo archivo, seleccionamos cualquiera de las otras dos opciones.

Para comprobar la existencia del libro personal, repetimos la búsqueda en Windows. Otra manera es iniciando el Editor de Visual Basic, con Herramientas - Macro - Editor de Visual Basic, o bién Alt + F11. Buscamos, incrustado en el margen izquierdo, el Explorador de proyectos, cuya barra de título comienza con la palabra Proyecto:

En caso de que no se muestre el Explorador, lo mostramos ejecutando Ver - Explorador de proyectos.

Ya que ubicamos el explorador de proyectos, buscamos en el mismo el identificador del libro de macros personal, el cual debe ser: VBAProject (PERSONAL.XLS). Damos doble clic en él para mostrar sus subcarpetas, entre las cuales hay una llamada Módulos. A su vez, esta carpeta contiene un archivo llamado "Módulo1", el cual es resultado de la grabación que hicimos.

Si damos doble clic en él, veremos en la zona principal del editor, el código de la macro que grabamos (en realidad no hay ningún código ya que no grabamos nada. Solo veremos el nombre y la descripción de la misma.)

Como este módulo solo contiene una macro que no hace nada, lo eliminamos seleccionandolo en el Explorador de proyectos con clic derecho, y dando clic en Quitar Módulo1. Excel nos preguntará si queremos exportar el módulo antes de eliminarlo, a lo cual contestamos No.

Es aquí, la zona de edición del Editor de Visual Basic, el lienzo donde con la suficiente práctica podremos crear las más bellas obras de arte...

La función DESREF

La función DESREF, entre algunas otras como DIRECCION, es una función bastante atípica de Excel. A diferencia de las demás, no devuelve un valor específico (bueno sí, pero es por excepción). Lo que hace es devolver un rango o referencia.

Como sabemos, una gran cantidad de funciones requieren un rango o una referencia como argumento(s). No obstante, cuando cambia la dirección de nuestro argumento o la dimensión del mismo, nos vemos obligados a reescribir el argumento en la fórmula. O seguimos otras prácticas riesgosas, como referenciar columnas completas. Entonces, para evitar esto, en lugar de escribir un rango directamente en una fórmula, formulamos este rango. Así como es posible formular un argumento numérico, también es posible formular rangos. De ahí la utilidad de la función DESREF.

DESREF devuelve un rango cuya celda superior izquierda se encuentra a determinado número de filas y columnas de distancia de la celda superior izquierda de una referencia o pivote, y que mide determinado número de filas y columnas. Como siempre, los argumentos pueden ser a su vez resultado de otra fórmula.

La ayuda de Excel proporciona la siguiente definición:

"Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver".

La sintaxis es:

DESREF(ref,filas,columnas,alto,ancho)

Ref es el pivote a partir de la cual Excel iniciará el desplazamiento. El segundo y tercer argumento establecen cuantas filas y columnas queremos desplazarnos a partir de ref. Si son positivos Excel se desplazará hacia abajo o a la derecha, según corresponda. Si son negativos, hacia arriba o a la izquierda. Debemos cuidarnos que estos argumentos no nos llevan más allá de los bordes de la hoja de cálculo, ya que obtendríamos un error #¡REF!

Los últimos dos argumentos, alto y ancho, indican las dimensiones en filas y columnas, que tendrá el rango resultante. Ambos deben ser positivos y son opcionales. Si los omitimos, el rango resultante tendrá las mismas dimensiones que ref. Aquí aplica la excepción que mencioné al principio: si ref solo consta de una celda y alto y acho son omitidos, DESREF devolverá un valor: el valor de la celda referenciada por los argumentos filas y columnas.

En el siguiente ejemplo:

=DESREF(A1, 1, 1), obtenemos el valor de la celda B2. En este otro:

=DESREF(A1, 1, 1, 1, 1), obtenemos una referencia a la celda B2.

Otros ejemplos:

=SUMA(DESREF(A1, 2, 0, 4, 2)), devuelve la suma del rango A3:B6

=DESREF(A1, -1, 1, 2, 2), devuelve #¡REF! ya que no hay ninguna celda arriba de A1.

=DESREF(C2, 0, 0, CONTARA(C:C)-1, 1), devuelve el rango que comienza en C2 y que contiene todas las celdas no vacías de la columna C, menos una: la ocupada por el título de la columna.

ref debe referirse únicamente a celdas adyacentes. De otra forma obtendríamos el error #¡VALOR!

Entender esta función es fundamental para dominar el tema de los rangos dinámicos, el cual nos quedó pendiente en entradas anteriores.

Link

Habilitar macros

Existe la posibilidad de que, al haber elaborado una macro, Excel no nos permita ejecutarla mostrándonos este cuadro de diálogo:

Esto puede deberse a que se ha deshabilitado voluntariamente la ejecución de las macros, o bién, a que apenas hemos elaborado nuestra primera macro (como siempre, la ayuda que proporciona Microsoft es punto menos que lamentable...). Por default, Excel tiene la ejecución de macros deshabilitada. Para habiltarla, seguimos los siguientes pasos:

Vamos a Herramientas - Macro - Seguridad...:

Aquí, en Nivel de seguridad, podríamos seleccionar la opción Bajo, de forma que Excel habilite automáticamente todas las macros en nuestros libros, lo cual no es muy recomendable, a menos que estemos en una isla desierta y sin internet. Cualquier seudo programador lo suficientemente experimentado, puede crear código malicioso (un virus), bajo la forma de una macro de Excel, afectando todo el entorno de nuestro equipo con una "macro" que, por ejemplo, se auto ejecute apenas abrir el archivo que la contiene.

Así pues, es mejor idea seleccionar la opción Medio. Con esto, si uno de nuestros archivos contiene macros, Excel nos preguntará si queremos habilitarlas, con el siguiente cuadro:


Si confiamos en el origen del archivo, o si nosotros mismos hemos elaborado las macros, elegimos la opción Habilitar macros. En cualquier otro caso, elegimos Deshabilitar macros.

Tendremos que padecer este cuadro de diálogo cada vez que abramos un archivo con macros. Claro que, por seguridad, es preferible hacerlo a ser víctima de algún virus. Una alternativa a esta situación es guardar las macros en el libro de macros personales, de forma que éstas estén guardadas en nuestro equipo, y no en un archivo específico.

Link

Gráficas combinadas

El motor de gráficos de Excel permite mezclar dos tipos de gráfico en uno solo, como en el siguiente ejemplo: Este gráfico combina los tipos de gráfico de columnas, y de líneas (en azul). No todos los tipos de gráficos pueden combinarse. Por ejemplo, no se puede combinar un gráfico circular con uno de líneas. Ni se puede combinar un gráfico 3D con ningún otro tipo.

Para lograr este efecto, primero graficamos todas las series de datos con el tipo de gráfico principal (en el ejemplo, el tipo principal es columnas):

Después, simplemente seleccionamos la serie que queremos representar con el otro tipo (hay que tener cuidado de seleccionar solo esa serie). Le damos clic derecho y seleccionamos la opción tipo de gráfico. Seleccionamos el tipo (en nuestro caso líneas) y damos Aceptar.

Toca al lector mejorar los formatos utilizados por el autor, o sea yo.

Este tipo de gráficas resultan útiles cuando queremos representar datos con diferentes escalas de valores, por ejemplo dinero y tasas de interés, o cuentas y sumas.

Link.