El extraño caso del vínculo fantasma

Cuando abrimos un archivo Excel que contiene vínculos a otro u otros archivos, vemos este famoso cuadro de diálogo:

Con lo cual Excel nos recuerda la existencia de dichos vínculos. Si queremos "romper" todos los vínculos, podemos usar el menu Edición - Vínculos:

Al hacerlo (romper todos los vínculos), veremos que, obviamente, el submenú Edición - Vínculos estará deshabilitado ya que no hay vínculos que mostrar:


Sin embargo hay ocasiones en las que, a pesar de haber eliminado todos los vínculos del libro, sigue apareciendo aquel mensaje. De hecho, puede aparecer incluso ¡si nunca hemos insertado ningún vínculo en el libro! ¿Por qué? ¿Cómo evitar definitivamente este mensaje?

En Excel, las celdas de una hoja de cálculo no son el único lugar donde puede existir un vínculo. Estos vínculos fantasma pueden producirse principalmente por dos razones:

1. Si utilizamos nombres, y hemos eliminado el archivo o la celda a la que se refieren. Para comprobarlo, revisamos nuestra lista de nombres con Insertar - Nombre - Definir. Si alguno de estos nombres tiene un error #REF en su fórmula, lo eliminamos.

2. Si utilizamos gráficas en nuestro archivo, y su rango de origen está en otro archivo. Para comprobarlo, seleccionamos una a una las series de datos de nuestras gráficas, y revisamos su correspondiente fórmula. Si alguna de ellas se refiere a algún archivo externo, la eliminamos, o bién, copiamos la información respectiva al archivo actual y cambiamos la referencia en la fórmula.

En caso de que tengamos una gran cantidad de nombres o series de datos en nuestras gráficas, podemos recurrir a alguna macro que haga la eliminación por nosotros. Con la ventaja de que podemos guardar esta macro como una macro "personal" que podremos usar en todo momento.

Caso cerrado.

Desvincular una gráfica de sus datos de origen

A propósito de la nota sobre resolver una fórmula paso a paso, en la que aprendimos a convertir una fórmula en su resultado utilizando la tecla F9, les traigo este otro tip:

En ocasiones, al estar trabajando con gráficas, nos damos cuenta que solo las necesitamos para una presentación o para imprimirlas, y que por lo tanto, son gráficas estáticas. Es decir, el rango de datos de origen nunca va a cambiar, solo estará ocupando espacio en nuestro libro. Podríamos llevar una vida normal si desvinculáramos la gráfica de los datos y elimináramos estos.

Supongamos la siguiente gráfica:

Para desvincularla de su rango de origen, procedemos como sigue:

1. Seleccionamos la serie de datos (o una de las series de datos) de nuestra gráfica. Con esto, podremos ver, en la barra de fórmulas, que Excel representa una serie de datos con una fórmula, utilizando la función SERIES:

=SERIES(Hoja1!$B$1;Hoja1!$A$2:$A$20;Hoja1!$B$2:$B$20;1)

2. Convertimos los argumentos de dicha fórmula en valores. Seleccionamos primero (en la barra de fórmulas) el segundo argumento de la fórmula y presionamos F9. Con esto la fórmula quedará como sigue:

=SERIES(Hoja1!$B$1;{"Subtech de Mexico SA de CV";"TELECONFERENCE GLOBAL SERV";"UNISIA MEXICANA S.A. DE C.";"Vitech S.A de C.V.";"Vitech S.A de C.V.";"Vitech S.A de C.V.";"Walbridge de México, S.A. ";"Mexico Analytica";"Carolina Yaday Perez Hermo";"Autodesk De Mexico, S.A. D";"Grupo Jardín Guadalupano";"Hotel Fiesta Americana";"Servicios Integrales en Co";"Transportes Escolares y de";"Urvina Servicios Internaci";"Applied Biosystems";"Corporacion Financiera Int";"Adelantus";"Adelantus"};Hoja1!$B$2:$B$20;1)

Excel ha convertido el argumento a su correspondiente constante matricial (o matriz de constantes).

3. Repetimos los mismos pasos con el tercer argumento. La fórmula queda:

=SERIES(Hoja1!$B$1;{"Subtech de Mexico SA de CV";"TELECONFERENCE GLOBAL SERV";"UNISIA MEXICANA S.A. DE C.";"Vitech S.A de C.V.";"Vitech S.A de C.V.";"Vitech S.A de C.V.";"Walbridge de México, S.A. ";"Mexico Analytica";"Carolina Yaday Perez Hermo";"Autodesk De Mexico, S.A. D";"Grupo Jardín Guadalupano";"Hotel Fiesta Americana";"Servicios Integrales en Co";"Transportes Escolares y de";"Urvina Servicios Internaci";"Applied Biosystems";"Corporacion Financiera Int";"Adelantus";"Adelantus"};{100;70.48;95.89;25;68.29;97.17;79;85.78;0;0;70.68;0;68.35;25;25;0;0;89.21;25};1).

4. El primer argumento también lo podemos convertir a su correspondiente valor, o bién, lo podemos eliminar de la fórmula, ya que es opcional. Presionamos Enter quedando así la fórmula final:

=SERIES(;{"Subtech de Mexico SA de CV";"TELECONFERENCE GLOBAL SERV";"UNISIA MEXICANA S.A. DE C.";"Vitech S.A de C.V.";"Vitech S.A de C.V.";"Vitech S.A de C.V.";"Walbridge de México, S.A. ";"Mexico Analytica";"Carolina Yaday Perez Hermo";"Autodesk De Mexico, S.A. D";"Grupo Jardín Guadalupano";"Hotel Fiesta Americana";"Servicios Integrales en Co";"Transportes Escolares y de";"Urvina Servicios Internaci";"Applied Biosystems";"Corporacion Financiera Int";"Adelantus";"Adelantus"};{100;70.48;95.89;25;68.29;97.17;79;85.78;0;0;70.68;0;68.35;25;25;0;0;89.21;25};1)

Ahora podemos eliminar el rango de datos de origen sin ningún temor. La única manera de modifcar esta gráfica es arrastrando libremente cada punto de la serie o bién, escribiendo el correspondiente valor directamente en la fórmula. Si queremos cometer un fraude millonario, arrastramos todos los puntos al valor máximo.

Cabe aclarar que este procedimiento solo funciona si nuestras series tienen un número pequeño de datos o puntos (aprox. 50, dependiendo del tamaño de la matriz resultante). Si la matriz de valores resultara muy larga, obtendriamos este mensaje al presionar F9:

It's friday...

Earl and Bubba, two guys from Elbert County, Georgia, are quietly sitting in a boat fishing, chewing tobacco and drinking beer when suddenly Bubba says:

-I think I'm going to divorce my wife. She hasn't spoke to me in over 2 months.

Earl spits, sips his beer and after several minutes, says:

-You better think it over, Bubba. Women like that are hard to find.

It's friday...

A man appeared before St. Peter at the Pearly Gates.

St. Peter ask:

-Have you ever done anything of particular merit?

-Well, I can think of one thing. - the man offered.

-On a trip to the Black Hills of South Dakota, I came upon a gang of bikers who were threatening a young woman. I directed them to leave her alone, but they wouldn't listen. So I approached the largest and most heavily tattooed biker and smacked him in the face, kicked his bike over, ripped out his nose ring, and threw it on the ground. I yelled: 'Now, back off, or I'll kick the shit out of all of you!'

St. Peter was impressed.

-When did this happen?

-Couple of minutes ago.

Resolver una fórmula paso a paso

Frecuentemente, cuando trabajamos con fórmulas complejas, necesitamos tener un altísimo grado de concentración. El tratar de comprender o modificar una fórmula larga y compleja (sobre todo si no la hicimos nosotros), puede llegar a ser un proceso altamente frustrante, más aún si no utilizamos nombres. Consideremos la siguiente fórmula:

=SI(K9>=$B$69;$D$69;SI(K9<=$B$66;K9*BUSCARV(K9;$B$62:$C$69;2;1);(K9-1)*BUSCARV(K9;$B$62:$C$69;2;1)+1))

Por ello, es importante saber como ejecutar parcialmente una fórmula, es decir, resolviendo primero las subfórmulas interiores hasta llegar a la función más externa, para comprender más fácilmente que es lo que hace determinada fórmula.

Excel nos brinda dos opciones: la primera de ellas consiste en ejecutar las cálculos parciales directamente en la barra de fórmulas. Para ello, selecionamos con el cursor la fórmula exacta que queremos ejecutar (ni un paréntesis menos o más), en la barra de fórmulas, y presionamos F9. Siguiendo con nuestro ejemplo, seleccionaremos la fórmula más interna, es decir, el segundo BUSCARV. Desde la "B" hasta el penúltimo paréntesis de la fórmula general.

Después de presionar F9, vemos, en la barra de fórmulas, que el BUSCARV se ha convertido en su valor resultante. De acuerdo a los números que manejo en mi libro, el resultado de dicho BUSCARV es 1.68:

=SI(K9>=$B$69;$D$69;SI(K9<=$B$66;K9*BUSCARV(K9;$B$62:$C$69;2;1);(K9-1)*1.68+1))

Hacemos lo mismo con el primer BUSCARV, que en este caso es exactamente igual al segundo:

=SI(K9>=$B$69;$D$69;SI(K9<=$B$66;K9*1.68;(K9-1)*1.68+1))

Evidentemente la fórmula se vuelve un poco más manejable al ser más corta. Para terminar el proceso, debemos recordar presionar Esc para dejar la fórmula como al principio, ya que si presionamos Enter, las fórmulas se habrán convertido permenentemente en sus valores.

La segunda opción que tenemos es utilizar el Evaulador de fórmulas de Excel. Para acceder a él, ejecutamos, después de seleccionar la fórmula de interés, Herramientas - Auditoría de fórmulas - Evaluar fórmula. Lo que veremos es:
Vemos que Excel ha subrayado el primer valor o fórmula a evaluar (K9, siempre según el ejemplo). Aquí tenemos dos opciones. Si presionamos el botón Evaluar, Excel convertirá K9 a su valor resultante. En cambio, si presionamos Paso a paso para entrar, el cuadro mostrará la fórmula de K9 (en caso de que haya una fórmula en K9), y así sucesivamente hasta llegar a los valores de origen.

Esto es especialmente útil al momento de auditar un modelo.

Otra opción es agregar una o varias columnas auxiliares, cada una con la correspondiente subfórmula, tema de otra nota.

El editor de macros de Excel

Continúa de la nota anterior.

Para verificar la ejecución de nuestra macro, primero eliminamos los formatos que aplicamos a la celda C6 (o no tendría sentido ejecutarla), y la ejecutamos desde el cuadro de diálogo Macro, visto en la previa nota. De esta manera, veremos que la celda C6 cambia su color de fondo a rojo y el de fuente a blanco. Hasta ahora todo va bién.

Sin embargo, si seleccionáramos otra celda o celdas y ejecutáramos la macro para cambiarles el formato, veríamos que esto no funciona: los formatos se aplicarán siempre a la celda C6 (y sólo en el libro que grabamos la macro). Esto es así ya que en el proceso de grabación de la macro incluímos la selección de la celda C6. Entonces, ¿cómo podemos hacer para darle más flexibilidad a la macro, de forma que aplique los formatos sobre cualquier celda o celdas seleccionadas?

Para lograrlo, necesitamos editar el código de nuestra macro. Y para acceder a este, necesitamos recurrir al Editor de macros de Excel. Ejecutamos el cuadro de diálogo Macro y presionamos el botón Modificar... El editor tiene una apariencia similar a esta:

En la pantalla principal podemos ver el nombre de la macro, su descripción y el código de la misma (puede variar un poco con el que tengan ustedes). Cada vez que ejecutamos una macro, Excel "lee" su código asociado y lo ejecuta línea por línea, siguiendo una lógica absolutamente exacta e implacable. El código del ejemplo es el siguiente:

Sub Macro2()
'
' Macro2 Macro
' Cambia el color de celda a rojo y el color de texto a blanco
'
Range("C6").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
End Sub

Intentemos comprender qué es lo que significa cada línea antes de continuar...

Como vemos, el código incluye la selección de C6 en la primera línea. Entonces, como no queremos que la macro seleccione C6, sino simplemente que aplique formatos, eliminamos la primera línea del código (¡sí, con Supr!), quedando como sigue:

Sub Macro2()
'
' Macro2 Macro
' Cambia el color de celda a rojo y el color de texto a blanco

With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
End Sub

Ahora, volvemos a Excel. Selecionamos cualquier rango de celdas y ejecutamos la macro.

Si hemos hecho la edición correctamente,veremos que el proceso ahora es más flexible, ya que aplica los formatos en cualquier selección activa, no sólo en C6.

En futuras notas emplearemos más tiempo (mucho más) al estudio del Editor de macros y a la redacción de macros.

Consulta

El día de ayer una usauria, a quien llamaremos Maira Nannet, se acercó a mi lugar con toda la seguridad del mundo y me hizo la siguiente consulta:

- Leonel, ¿la celda AK47 es de uso exclusivo del ejército?...

Son las preguntas de este tipo las que reinvindican mi creencia de que este blog sí tiene razón de ser. Haciendo un esfuerzo sobrehumano para no dejarme intimidar por este golpe tan bajo, contesté sobriamente:

-No. La celda AK47 es una celda como cualquier otra. No hay nada ilegal en el uso de la misma.

Macros o uso racional de la fuerza bruta

Una macro es, en términos sencillos, una secuencia de instrucciones que se ejecutan en una sola acción por parte del usuario. Son útiles cuando nos vemos precisados a llevar a cabo constantemente una misma serie de pasos en nuestro modelo. Estas series de pasos pueden ir desde las sencillas (p. ej. dar color de celda rojo y color de fuente blanco a la celda seleccionada) hasta las muy complejas (crear una consulta en Access, copiarla a Excel, ordenar los datos, subtotalizarlos en otra hoja, formatear las filas de subtotal, proteger las hojas y enviar el archivo resultante a nuestros contactos).

Para ejecutar una macro, podemos hacerlo a través de los comandos nativos de Excel (Herramientas - Macro - Macros - Ejecutar), o bién, con un menú personalizado y/o un método abreviado personalizado (Ctrl + Shift + q) y/o una barra de herramientas personalizada.

Una macro puede ser redactada o bien, grabada utilizando la grabadora de macros de Excel. Procedamos a grabar la macro del primer ejemplo, dar color de celda rojo y color de fuente blanco a la celda seleccionada. Seguimos los siguientes pasos al pie de la letra:

1. Activamos la grabadora de macros de Excel. Para hacerlo, vamos a Herramientas - Macro - Grabar nueva macro..., con lo cual aparecerá en cuadro de diálogo Grabar macro:

Le damos un nombre descriptivo a nuestra macro, cuidándonos de utilizar solamente letras y/o números y de no utilizar espacios ya que Excel no lo permitirá. Si queremos asignar un método abreviado a la macro podemos hacerlo desde aquí también, pero deberemos prestar atención de no asignar un método que tenga ya asignado Excel, como Ctrl + c. Por último anotamos una breve descripción de la macro en el último cuadro, y aceptamos el cuadro de diálogo.

Con esto Excel iniciará su Grabadora de macros y aparecerá la minibarra de herramientas Detener grabación con dos o tres botones: Detener grabación, Pausa y Referencia relativa. No presionamos ninguno (aún).

2. Situamos el cursor en la celda que queramos formatear, digamos C6.

3. Modificamos adecuadamente el color de celda y el color de fuente.

4. Damos clic en el botón Detener de la barra de herramientas Detener grabación.

De esta forma hemos grabado una macro, muy sencilla, pero funcional. Para comprobar que efectivamente existe, ejecutamos Herramientas - Macro - Macros para ver el cuadro de diálogo Macro, donde podremos ver en todo momento las macros que hemos grabado (excepto si se trata de una macro oculta), además de la descripción de las mismas, si es que hemos escrito alguna:

Para ejecutar la macro, la seleccionamos de la lista y damos clic en Ejecutar.

Continuamos en la siguiente nota.

It's friday...

Costello (a 90 year old man) calls to buy a computer from Abbott (a stupid retailer):

ABBOTT: Super Duper computer store. Can I help you?
COSTELLO: Thanks. I'm setting up an office in my den and I'm thinking about buying a computer.
ABBOTT: Mac?
COSTELLO: No, the name's Lou.
ABBOTT: Your computer?
COSTELLO: I don't own a computer. I want to buy one.
ABBOTT: Mac?
COSTELLO: I told you, my name's Lou.
ABBOTT: What about Windows?
COSTELLO: Why? Will it get stuffy in here?
ABBOTT: Do you want a computer with Windows?
COSTELLO: I don't know. What will I see when I look at the windows?
ABBOTT: Wallpaper.
COSTELLO: Never mind the windows. I need a computer and software.
ABBOTT: Software for Windows?
COSTELLO: No. On the computer! I need something I can use to write proposals, track expenses and run my business. What do you have?
ABBOTT: Office.
COSTELLO: Yeah, for my office. Can you recommend anything?
ABBOTT: I just did.
COSTELLO: You just did what?
ABBOTT: Recommend something.
COSTELLO: You recommended something?
ABBOTT: Yes.
COSTELLO: For my office?
ABBOTT: Yes.
COSTELLO: OK, what did you recommend for my office?
ABBOTT: Office.
COSTELLO: Yes, for my office!
ABBOTT: I recommend Office with Windows.
COSTELLO: I already have an office with windows! OK, let's just say I'm sitting at my computer and I want to type a proposal. What do I need?
ABBOTT: Word.
COSTELLO: What word?
ABBOTT: Word in Office.
COSTELLO: The only word in office is office.
ABBOTT: The Word in Office for Windows.
COSTELLO: Which word in office for windows?
ABBOTT: The Word you get when you click the blue 'W'.
COSTELLO: I'm going to click your blue 'w' if you don't start with some straight answers. What about financial bookkeeping? You have anything I can track my money with?
ABBOTT: Money.
COSTELLO: That's right. What do you have?
ABBOTT: Money.
COSTELLO: I need money to track my money?
ABBOTT: It comes bundled with your computer.
COSTELLO: What's bundled with my computer?
ABBOTT: Money.
COSTELLO: Money comes with my computer?
ABBOTT: Yes. No extra charge.
COSTELLO: I get a bundle of money with my computer? How much?
ABBOTT: One copy.
COSTELLO: Isn't it illegal to copy money?
ABBOTT: Actually, Microsoft gave us a license to copy Money.
COSTELLO: They can give you a license to copy money?
ABBOTT: Why not? THEY OWN IT!

(A few days later) ...

ABBOTT: Super Duper computer store. Can I help you?
COSTELLO: How do I turn my computer off?
ABBOTT: Click on 'START'...

It's friday...

I saw a fat woman wearing a sweatshirt with 'Guess' on it. I said:

- Thyroid problem?

It's friday...

Dear "Ask Richard":

I hope you can help me here. The other day I set off for work leaving my husband in the house watching the TV as usual. I hadn't gone more than a mile down the road when my engine conked out and the car shuddered to a halt. I walked back home to get my husband's help. When I got home I couldn't believe my eyes. He was in the bedroom with a neighbor lady making mad passionate love to her. I am 58, my husband is 60 and we have been married forever. When I confronted him, he tried to make out that he went into the back yard and heard a lady scream, had come to her rescue but found her unconscious. He'd carried the woman back to our house, laid her in bed, and began CPR. When she awoke she immediately began thanking him and kissing him and he was attempting to break free when I came back. But when I asked him why neither of them had any clothes on, he broke down and admitted that he'd been having an affair for the past six months. I told him to stop or I would leave him. He was let go from his job six months ago and he says he has been feeling increasingly depressed and worthless. I love him very much, but ever since I gave him the ultimatum he has become increasingly distant. I don't feel I can get through to him anymore. Can you please help?

Sincerely, Mrs. Jane

Dear Jane:

A car stalling after being driven a short distance can be caused by a variety of faults with the engine. Start by checking that there is no debris in the fuel line. If it is clear, check the jubilee clips holding the vacuum pipes onto the inlet manifold. If none of these approaches solves the problem, it could be that the fuel pump itself is faulty, causing low delivery pressure to the carburetor float chamber. I hope this helps.

Richard

Convertir una gráfica en imagen

Cuando queremos convertir una gráfica en un archivo de imagen, como .bmp, o .gif, regularmente copiamos la imagen de pantalla en el portapapeles de Windows, oprimiendo la tecla Impr Pant, abrimos nuestro programa editor de imágenes favorito (léase Paint) y ejecutamos Edición - Pegar (Ctrl + v ó Shift + Insert para los diestros). Después recortamos la imagen para que solo contenga la gráfica y guardamos el archivo.

Si bién el anterior es un método válido, existe otra forma de lograrlo. No es algo directo, pero es más práctico.

Primeramente, seleccionamos la gráfica o gráficas que queremos convertir. Después, ejecutamos Archivo - Guardar como... En la lista Guardar como tipo, seleccionamos Página web. Con esto, el cuadro de diálogo cambiará un poco, quedando de esta forma:

En la nueva sección que aparece, Guardar, damos clic en Selección: Gráfica. Finalmente, damos clic en Aceptar.

Si lo hicimos correctamente, veremos en el Explorador de Windows que Excel, además de haber guardado el archivo como documento HTML, ha creado una carpeta con el mismo nombre. Esta carpeta contendrá todas las gráficas que hayamos seleccionado en el primer paso, con extensión .gif.

Para terminar, hay que tener presente que el archivo HTML está de cierta manera vinculado a la carpeta de las imágenes. Si lo eliminamos, Excel eliminará también la carpeta. Evitemos hacerlo.

Calcular el número de trimestre

En la empresa en la que trabajo, se maneja un cierre de ventas trimestral, además de otro cierre mensual. Así que para algunos de nosotros es importante tener correctamente formulado el Q (así se dice trimestre aquí. Es una empresa cosmopolita...), al que pertenece una fecha determinada.

La fórmula que utilizo para calcular el trimestre al que corresponde una fecha es la siguiente:

=REDONDEAR.MAS(MES(A2)/3, 0)

Donde A2 es la fecha de interés. REDONDEAR.MAS redondea siempre hacia arriba el valor especificado en el primer argumento, con el número de decimales especificado en el segundo argumento.

Alternativamente, podemos usar también:

=ENTERO((MES(A2)+2)/3)

Lo cual es más corto, y por lo tanto, mejor.

ENTERO simplemente devuelve la parte entera de un número con decimales.

Calcular el número de días de un mes

En una nota anterior, obtuvimos la siguiente fórmula para obtener la fecha del último día de un mes:

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

Y para conocer el número de días de un mes, utilizamos esta otra:

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

La cual está basada en funciones estándar o nativas de Excel. Sin embargo, si tenemos instalado el complemento Herramientas para análisis, podemos utilizar la función FIN.MES. La sintaxis es:

FIN.MES(fecha, num_de_meses)

El argumento fecha es la fecha inicial del cálculo. El argumento num_de_meses es un número entero que indica el número de meses posteriores a la fecha inicial, cuyo número de días queremos calcular. Por ejemplo, si tenemos en la celda A1 la fecha 05/02/2008, y queremos saber el número de días de ese mes (es decir, cero meses posteriores), utilizaremos la fórmula:

=FIN.MES(A1, 0)

La cual devuelve 29. Si queremos saber el número de días de marzo (un mes posterior) utilizaremos:

=FIN.MES(A1, 1), resultando 31.