Listar todos los vínculos en el libro

Hay ocasiones en que deseamos conocer la ubicación de los vínculos, a otros libros, que tiene nustro libro de trabajo, ya sea para modificarlos, o bién, para eliminarlos. Desafortunadamente, Excel no cuenta con alguna característica que permita conocer la ubicación exacta de dichos vínculos. A lo más, podemos conocer el nombre y la ruta de acceso del libro vinculado. Si ejecutamos Edición - Vínculos, Excel nos mostrará el siguiente cuadro de diálogo:

Como vemos, Excel solo nos muestra la ubicación del vínculo o vínculos y su estado, entre otras opciones.

Cuando este cuadro no es suficiente, y queremos conocer la ubicación exacta de cada vínculo, podemos valernos del comando Buscar (Edición - Buscar o Ctrl + B). Tengamos en cuenta lo siguiente:

Buscar nos da la opción de realizar la búsqueda ya sea en el valor de las celdas, o bién, dentro de las fórmulas utilizadas. En este caso, utilizaremos esta opción. Por otra parte, si seleccionamos cualquier celda con vínculos, y observamos la barra de fórmulas, veremos la nomenclatura que utiliza Excel para referencias externas:

='ruta_de_acceso[nombre_libro]nombre_hoja'!celda

Por ejemplo:

='X:\reportes\esquemas\[Esquema Medianas 3.xls]Esquema Medianas'!$B$1

Como vemos, la fórmula de cualquier vínculo externo siempre incluirá el nombre del libro respectivo entre corchetes (la ruta solo se muestra si el libro referenciado está cerrado).

Por lo tanto, podemos utilizar el siguiente procedimiento para obtener una lista completa de todos los vínculos en el libro:

Ejecutamos Edición - Buscar. En la input box Buscar, escribimos "[" o bien "]". En la opción Dentro De, selecionamos Libro, y en Buscar dentro de, seleccionamos Fórmulas. Por último, damos clic en el botón Buscar todo, y aumentamos el tamaño de esta ventana (recordemos que este cuadro no tiene un botón Maximizar).

Excel nos devolverá una lista completa con todos los vínculos en el libro, su valor, su ubicación, y vínculos que nos llevarán a su vez a cada celda conteniendo los vínculos del libro al hacer clic en ellos.

Número de días hábiles entre dos fechas

Q: ¿Cómo puedo calcular el número de días hábiles entre dos fechas, es decir, excluyendo sábados y domingos?

A: Utilizando la función DIAS.LAB, incluída en el complemento Herramientas para Análisis de Excel.

DIAS.LAB no es una función nativa de Excel, sino que pertenece al complemento mencionado. Un complemento (add-in, en inglés), es un "miniprograma" o conjunto de características que no tiene de manera predeterminada Excel y que aumentan o extienden su funcionalidad. En el caso del complemento Herramientas para análisis, este incluye una serie de interfases y funciones para análisis financiero y científico.

Para poder utilizar las características del referido complemento, necesitamos instalarlo primero. Es sencillo: Ejecutamos Herramientas - Complementos... para abrir el cuadro de diálogo Complementos, y activamos la casilla de verificación correspondiente:

Aceptamos el cuadro.

Podemos ver ahora que el menú herramientas contiene un nuevo submenú llamado Análisis de datos, el cual contiene las nuevas funcionalidades instaladas:

Asimismo, podemos ver que tenemos nuevas funciones, incluyendo la que nos interesa, DIAS.LAB, que estará incluída en la categoría Fecha y hora en el cuadro Insertar función:

Seleccionamos la función y damos Aceptar.

Ingresamos nuestra fechas inicial y final como primeros dos argumentos. El tercer argumento, Festivos, lo usamos en caso de que queramos que Excel excluya los días festivos del cálculo. En caso afirmativo, elaboramos una lista en nuestra hoja con los días festivos que queremos que sean excluídos. Finalmente, ingresamos el rango de esta lista como argumento Festivos (o bién podemos ingresar las fechas directamente con la función FECHA). De cualquier forma, el argumento Festivos es opcional, podemos prescindir de él.

Si damos clic en el link Ayuda sobre esta función, veremos que Excel no dispone de ayuda para esta función, ya que el fabricante del complemento, no la incluyó en el mismo.

Elaborar una gráfica circular con subgráfica

Me pregunta una usauria, Claudia A., como elaborar una gráfica circular (o de pastel) doble, es decir, con el desglose de una "rebanada" de la misma, en otra gráfica circular (técnicamente hablando, un gráfico circular con subgráfico circular):


Es importante notar que una gráfica circular, por su propia naturaleza, solo puede aceptar una única serie de datos; por lo tanto, nuestro rango de origen solo podrá tener una columna (o fila) de datos (más otra columna o fila para los rótulos). Entonces, si nuestros datos están dispersos en varias columnas, tendremos que ajustarlos en otra tabla auxiliar.

Supongamos la siguiente tabla datos:


Deseamos graficar los totales mensuales, y en la segunda gráfica, el detalle del mes de octubre. Como solo podemos usar una columna, elaboramos la siguiente tabla auxiliar:


Es decir, ponemos los totales que aparecerán en la primera gráfica más los subtotales de la segunda SIN la categoría de estos. Nótese que las entradas de la gráfica secundaria siempre van al final. Más adelante se verá porqué.

A continuación, situamos el cursor en la tabla y vamos a Insertar - Gráfico o bién, damos clic en el botón Asistente para gráficos de la barra de herramientas Estándar. En el primer paso seleccionamos el tipo Circular y el subtipo Circular con subgráfico circular.


Avanzamos hasta el paso 3, opciones de gráfico. Escribimos un título para nuestra gráfica. En la pestaña Leyenda desactivamos la opción Mostrar leyenda, y en la pestaña Rótulos de datos seleccionamos Nombre de la categoría y Valor.

Damos clic en finalizar.

Vemos que Excel intentó adivinar cuantos puntos queremos incluir en la subgráfica. En el caso mío, adivinó mal ya que solo incluyó el último punto. Realizamos entonces los siguientes ajustes: damos clic derecho en la serie de datos, seleccionamos Formato de serie de datos y vamos a la pestaña Opciones.

Aquí, en la lista despleglable Dividir series por: seleccionamos Posición, y en Segundo trazado contiene últimos: especificamos 4 valores. Aprovechamos el viaje para ajustar las opciones Tamaño del segundo trazado, el cual cambia la proporción de los tamaños de las gráficas, y Ancho del rango, el cual cambia el tamaño del primer gráfico y la separación entre ambas. Aceptamos el cuadro de diálogo.

Por último, cambiamos manualmente la etiqueta Otros y escribimos Octubre, resultando la gráfica mostrada al principio.

Calcular el último día de un mes

Ocasionalmente nos vemos en la necesidad de calcular la fecha del último día de determinado mes, de determinado año, o su pariente cercano, el número de días que tiene un mes.

En una previa nota vimos como calcular la fecha de término de un periodo particular, para un número entero de meses. La fórmula utilizada era:

=FECHA(AÑO(A2), MES(A2)+B2, DIA(A2)-1)

Donde A2 es la fecha inicial y B2 la duración del periodo en meses.

Si nuestro periodo está expresado en días, entonces usamos esta variante:

=FECHA(AÑO(A2), MES(A2), DIA(A2)+B2)

Esta fórmula funciona aún si DIA(A2)+B2 es mayor al número de días del mes MES(A2), ya que Excel "ajusta" el mes (y si es necesario, también el año) de la fecha resultante automáticamente. En efecto, si ingresamos la siguiente "fecha" en Excel:

33/01/2008

Excel nos mostrará:

02/02/2008

Ajustando el mes (y el día) adecuadamente para mostrar una fecha válida.

Supongamos ahora que en A2 tenemos una fecha de febrero de 2008, por ejemplo:

14/02/2008

Y queremos saber cuántos días tiene el mismo mes. Para calcularlo, primeramente calculamos la fecha del primer día del mes siguiente, como sigue:

=FECHA(AÑO(A2), MES(A2)+1, 1)

Lo cual devuelve 01/03/2008. Llegados a este punto, les revelaré algo que me llevó 10 años de meditación profunda descubrir: El último día de un mes es siempre el día anterior al primer día del mes siguiente. Conociendo esto, y sabiendo que en Excel, un día es igual a una unidad, obtenemos la fórmula para calcular el último día de un mes, en este caso febrero 2008:

=FECHA(AÑO(A2), MES(A2)+1, 1) - 1

Nuevamente, como un día es igual a una unidad, podemos restar 1 directamente al argumento día:

=FECHA(AÑO(A2), MES(A2)+1, 1-1)

Simplificando:

=FECHA(AÑO(A2), MES(A2)+1, 0)

Lo cual se convierte en:

=FECHA(2008, 3, 0)

Como el cero de marzo no es una fecha válida, Excel la ajustará adecuadamente y nos mostrará el día anterior al 1 de marzo:

29/02/2008

Para finalizar, si solo nos interesa saber el número de días de un mes (no una fecha) lo extraemos de la fórmula anterior con la función DIA:

=DIA(FECHA(AÑO(A2), MES(A2)+1, 0))

Visualizar en celdas las fórmulas utilizadas

Al repasar la historia feliz que publiqué hace algún tiempo, recordé la situación opuesta: Hay ocasiones en que deseamos ver en nuestras celdas las fórmulas utilizadas, en vez del resultado de las mismas. Esto puede ser útil para corregir la lógica de nuestro modelo, si por ejemplo, una fórmula nos devuelve un resultado, que aunque no es de error, no es el esperado. También puede resultar mas cómodo mirar todas nuestras fórmulas de una vez en lugar de estar seleccionando celdas y mirando cada vez la barra de fórmulas.

Para visualizar las fórmulas que estamos usando, tenemos dos opciones: La primera es ir a Herramientas - Opciones (Alt, h, n) - Ficha Ver, y en la sección Opciones de ventana, activamos Fórmulas.

La segunda es ir a Herramientas - Auditoría de fórmulas - Modo de auditoría de fórmulas (Alt, h, d, a). Con la segunda opción tenemos la ventaja de que al seleccionar una celda formulada, Excel resaltará las celdas precedentes, similar a cuando damos F2 o doble clic en una fórmula, pero pudiendo desplazarnos libremente por la hoja.

Ciertamente, a muchos usuarios no les gusta esta característica, aunque no pierden nada con experimentar. Para desactivarla, repetimos los mismos pasos.