Guardar la configuración de las barras de herramientas

Esta entrada no corresponde a ninguna consulta. La publico por considerarla de utilidad para los usuarios avanzados, los power users.

Seguramente los lectores ya habrán advertido lo siguiente: Cada que cerramos Excel, este guarda la configuración de las barras de herramientas al momento de cerrarlo. Gracias e esto, cada que abrimos Excel, las barras de herramientas están exactamente igual, en la misma posición y con los mismos botones que la última vez que trabajamos. Esto lo pueden comprobar fácilmente, arrastrando una de las barras, cerrando el programa y volviéndolo a abrir.

Ahora bién, esta configuración Excel la guarda en un archivo de barras de herramientas, de extensión .xlb y cuyos nombres y ubicación pueden variar. Generalmente se llama PERSONAL.XLB o EXCEL11.XLB. En mi PC lo tengo como:

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

En todo caso, podemos hacer una búsqueda en Windows de todos los archivos terminados en .xlb.

Entonces, cada que cerramos Excel, este archivo se actualiza automáticamente. Al abrirlo, se abre a su vez este archivo (entre otros, como el libro de macros personal y los complementos instalados). En caso de que no exista este archivo, ya sea porque lo eliminemos o le cambiemos el nombre, se creará automáticamente.

Podemos aprovechar este archivo para intercambiar rápidamente entre varias configuraciones de barras de herramientas, sin tener que estar abriendo y cerrando manualmente cada una de ellas. Si, por ejemplo, estamos trabajando en el aspecto visual de nuestro modelo, podemos elegir que Excel nos muestre las barras de herramientas Dibujo, Word Art, Gráfico y una barra personalizada en determinada posición, y que oculte la barra Formato en un solo paso. Y al terminar, que nos devuelva a la configuración Estándar.

Procedemos como sigue:

Realizamos la configuración de barras de herramientas que deseemos. Siguiendo nuestro ejemplo, abriremos cuatro barras de herramientas y ocultaremos otra, la de Formato. Las colocamos en la posición que queramos (la barra de menús también es una barra de herramientas, por lo que también podemos moverla libremente).

Acto seguido, cerramos Excel.

Sabemos que el archivo Excel11.xlb ha sido modificado y que será leído la próxima vez que abramos nuestro venerado programa. Busquemos el archivo con el Explorador de Windows y cambiémosle el nombre, digamos que a diseño.xlb.

Abrimos otra vez Excel. Vemos que, efectivamente,tenemos la última configuración. Sin embargo, al cambiarle el nombre, el archivo Excel11.xlb ya NO existe. Al no existir, Excel lo creó automáticamente, con la configuración estándar, como mencioné líneas arriba. En efecto, si en este punto hacemos una nueva búsqueda en Windows, veremos que ahora tenemos dos archivos .xlb: excel11 y diseño.

Ahora, cada que queramos ver la configuración de diseño, simplemente abrimos el archivo diseño.xlb. Para regresar a la configuración original, abrimos Excel11.xlb.

Por último, repetimos estos pasos tantas veces como configuraciones queramos. También, como tip, podemos crear una minibarra de herramientas y/o menú que abra cada uno de estos archivos y ejecute los cambios con un solo clic, lo cual será tema de otra entrada.

Gracias México.

Nota sobre TRANSPONER

Ciertas funciones de Excel, como la comentada anteriormente TRANSPONER, y otras como FRECUENCIA, son funciones netamente matriciales. Esto es, solo pueden ser usadas en una fórmula matricial, nunca en fórmulas lineales o normales. Si las ingresamos "normalmente" (es decir, sin presionar Ctrl + Shift + Enter), en todos los casos obtendremos el resultado de error #¡VALOR! Por cierto, este el tipo de error más frecuente al utilizar fórmulas matriciales, debido precisamente a que el usuario olvida ingresarlas con la combinación de teclas correcta.

Determinar una fecha de vencimiento

Un usaurio me pregunta como calcular la fecha de término de los contratos que elabora. Es decir, si en la celda A2 tiene una fecha de inicio de contratación del 05/02/2007 y en la celda B2 el plazo del contrato en meses, por ejemplo 10, ¿como hacer para que en la celda C2 aparezca 04/12/2007?

Para obtener la respuesta es necesario conocer la función FECHA. También habremos de utilizar las funciones AÑO, MES y DÍA. Rápidamente, diremos que MES (la más conocida) devuelve el número de mes en una fecha; en nuestro ejemplo, MES(A2) devuelve 2. De manera similar, AÑO(A2) devuelve 2007 y DÍA(A2) devuelve 5.

La función FECHA nos devuelve una fecha, dados los argumentos año, mes y día. La sintaxis es la siguiente:

=FECHA(año, mes, día)

El siguiente ejemplo devuelve 01/01/2007:

=FECHA(2007, 01, 01)

Aparentemente se trata de una función muy poco práctica (¿por qué no escribir la fecha directamente?...)

No obstante, tenemos la gran ventaja de que, como en cualquier otra función de Excel, los argumentos ingresados pueden ser otras fórmulas, o bien, referencias a celdas. Al tratar con fechas, esto tiene un enorme valor. Retomando el ejemplo anterior, si en un primer intento consideramos meses de 30 días y sumamos B2*30, obtenemos:




Esto no nos da la fecha exacta de terminación (04/12/2007) por la sencilla razón de que no todos los meses tienen 30 días. Pero si partimos nuestra fecha inicial en los tres argumentos de la función FECHA, simple y sencillamente sumamos 10 (o su referencia B2) al argumento "mes", como sigue:

=FECHA(2007, MES(A2)+10, 5), o mejor:

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

La cual se convierte en:

=FECHA(2007, 2+10, 5), y a su vez en =FECHA(2007, 12, 5)

Resultando 05/12/2007.

Ahora bién, nosotros buscábamos 4 de diciembre, no cinco. ¿Qué hacemos? Tan fácil como restar 1 al argumento "día":

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


Lo cual ya produce 04/12/2007. Y como esta es una fórmula general, que funciona con cualquier otra fecha inicial o periodo, vemos la gran utilidad que tiene la función FECHA.

It´s friday...

Se esta muriendo la suegra de un hombre.

Toda la familia se encuentra reunida alrededor de su lecho.

La viejita mirando hacia la ventana dice:

- !Que lindo atardecer!

El hombre, dirigiéndose a la suegra:

- !No se distraiga, suegra! !No se distraiga!

It´s friday...

Ahora dos en English y one in Spanish:

Four United States Presidents get caught up in a tornado... and off they whirled to the land of OZ. They finally made it to the Emerald City... and went to find the Great Wizard:

-What brings the four of you before the great Wizard of Oz?

Jimmy Carter stepped forward timidly:

-I've come for some courage.

-No Problem!- said the Wizard, -Who's next?

Richard Nixon stepped forward, and said:

-Well, I think I need a heart. -Done! -Says the Wizard.

-Who comes next before the Great and Powerful Oz?

Up stepped Bush and said:

-The American people say that I need a brain. - No problem! -Said the Wizard. Consider it done.

Then there is a great silence in the hall. Bill Clinton is just standing there, looking around, but he doesn't say a word. Irritated, the Wizard finally asks:

-Well, what do you want?

-Is Dorothy here???

It´s friday...

You can use this!:

The NAB (National Australia Bank) sent this letter to one of its customers:

"Dear Sir, it appears your account is overdrawn... "

Letter to which the gentleman had (correctly) replied:

"To Whom It May Concern:

Please contact me when you are absolutely certain..."

No imprimir resultados de error

Los resultados de error solo son útiles cuando estamos diseñando o probando nuestras fórmulas. Si conocemos la diferencia entre un error #¡VALOR! y uno #¡REF!, por ejemplo, podremos corregir más fácilmente la fórmula en cuestión.

Pero un modelo terminado no debería mostrar resultados de error en sus fórmulas.

Por eso, y como mencioné en una previa nota, no es muy correcto que los informes que entreguemos muestren los resultados de error #N/A (o cualquier otro). Esto es particularmente cierto cuando vamos a entregar nuestros informes electrónicamente al destinatario. Aunque si solo los vamos entregar impresos, tenemos la opción de no imprimir estos valores tal cuales:

Vamos a Archivo - Configurar página - ficha Hoja.

Una vez aquí, desplegamos, en la sección Imprimir, la lista Errores de celda como:, la cual tiene cuatro opciones:

mostrado,
espacio vacío
--
#N/A


Aquí podemos seleccionar como imprimir (o no imprimir) los errores: como un espacio en blanco, como guiones, o bien imprimir todos los tipos de error (#¡REF!, etc.) como #N/A. Para deshacer esta configuración, seleccionamos la opción "mostrado".

Obviamente que esto no afectará en nada a la información mostrada en pantalla.

Uso de BUSCARV III

En la empresa en la que trabajo, se vive cierta sicosis colectiva acerca de BUSCARV. Una obsesión por utilizarlo como sea, donde sea, por quien sea, a la hora que sea. He visto BUSCARVs de BUSCARVs con dos giros y medio hacia atrás. Como veremos en otras entradas, las capacidades de búsqueda y referencia de Excel van muchísimo más allá de esta función.

Decíamos el martes que el argumento ordenado solo puede ser VERDADERO o FALSO. No obstante, debo decir que, como en todos los casos en que debamos ingresar este tipo de argumento en cualquier función, podemos utilizar también los valores 1 o 0, respectivamente. Estas dos fórmulas son equivalentes:

=BUSCARV("smith", A1:D1000, 2, FALSO)
=BUSCARV("smith", A1:D1000, 2, 0)

Estas dos también:

=BUSCARV(0.98, A1:D5, 2, VERDADERO)
=BUSCARV(0.98, A1:D5, 2, 1)
[también:
=BUSCARV(0.98, A1:D5, 2) ]

Como siempre, habrá usuarios que prefieran una u otra nomenclatura. Los que busquen mayor claridad en sus fórmulas (entre los que me incluyo), utilizarán la primera (incluso, en el segundo ejemplo, especifico VERDADERO a pesar de que es equivalente a omitir el argumento). Otros, más prácticos, elegirán la segunda, e incluso omitirán el argumento en el segundo ejemplo.

Por último, apuntaré lo siguiente:

Cuando el valor que deba mostrar BUSCARV sea una cadena de texto vacía (""), nos devolverá en realidad 0 (cero). Habrá casos en los que sea aceptable obtener 0 en lugar de texto vacío. Pero habrá otros en los que sí necesitemos que el resultado de la fórmula sea "" (para distinguir un cero de un valor aún no ingresado, por ejemplo). Si este es el caso, tendremos que validar los resultados "" con la función SI:

=SI(BUSCARV("smith", A1:D1000, 2, FALSO)="","",BUSCARV("smith", A1:D1000, 2, FALSO) )

O de esta forma:

=SI(LARGO(BUSCARV("smith", A1:D1000, 2, FALSO))=0,"",BUSCARV("smith", A1:D1000, 2, FALSO) )

Uso de BUSCARV II

Continúa de la nota anterior.

Empecemos a ver ahora usos más complejos de esta función.

Decíamos en la anterior nota que si el cuarto argumento de la función es FALSO y no existe una coincidencia exacta, obtendremos el resultado de error #N/A. En ocasiones esto no es muy estético que digamos, sobretodo si vamos a elaborar informes con los resultados de nuestra fórmula.

Una forma de evitar que la función nos devuelva estos resultados de error, es utilizando la función ESERROR, la cual, como vimos en esta nota, intercepta cualquier resultado de error y podemos combinarla con la función SI para mostrar el resultado que nosotros queramos. Por ejemplo, podemos escribir:

=SI(ESERROR(BUSCARV("smith", A1:D5, 2, FALSO), "No encontrado", BUSCARV("smith", A1:D5, 2, FALSO))

Pero si, en dado caso, el resultado de BUSCARV es #¡REF!, indicando que hubo un error en las referencias de las fórmulas, el resultado obtenido seguirá siendo el mismo, "No encontrado". Nos haría pensar que no se encontró el valor buscado, cuando puede ser que sí exista pero escribimos mal otro de los argumentos.

Sería preferible utilizar entonces la función ESNOD. Esta funciona de manera similar que ESERROR, pero únicamente intercepta los errores #N/A.

Si en nuestro directorio SÍ tenemos registrado a Smith, esta fórmula nos devolvería "No encontrado":

=SI(ESERROR(BUSCARV("smith", A1:D5, 22, FALSO), "No encontrado", BUSCARV("smith", A1:D5, 22, FALSO))

En cambio, esta fórmula nos devolvería #¡REF!:

=SI(ESNOD(BUSCARV("smith", A1:D5, 22, FALSO), "No encontrado", BUSCARV("smith", A1:D5, 22, FALSO))

Con lo cual Excel nos advertiría que escribimos mal uno de los argumentos de BUSCARV (¿cuál?). De haber utilizado ESERROR, hubiéramos obtenido "No encontrado", sin ninguna advertencia sobre nuestro error.

Uso de BUSCARV

BUSCARV es una de las funciones de búsqueda y referencia más conocidas y utilizadas (si no la única) por las grandes masas. Esta función busca un valor dado en la primera columna de una tabla (matriz) y devuelve el dato que está x número de columnas a la derecha de ese valor.

La sintaxis de esta función es la siguiente:

BUSCARV(valor_buscado, matriz_buscar_en, indicador_columnas, ordenado)

Supongamos que tenemos un directorio de teléfonos y direcciones, en el rango A1:C1000. En la primera columna tenemos los nombres de nuestros contactos (no necesariamente ordenados), en la segunda tenemos sus correspondientes números teléfónicos y en la tercera tenemos sus direcciones.

Si queremos buscar el teléfono de Smith, escribimos la siguiente fórmula:

=BUSCARV("smith", A1:C1000, 2, FALSO)

Escribo Smith con minúsculas por comodidad y porque la función no es sensible a mayúsculas y minúsculas. En matriz_buscar_en incluímos todo el rango que ocupa el directorio. Indicador_columnas es 2 porque los teléfonos están en la columna 2 del directorio (si buscáramos la dirección de Smith escribiríamos 3 como tercer argumento).

El último argumento (ordenado), que en mi opinión debería llamarse "aproximado" o algo así, puede ser FALSO o VERDADERO. Si es FALSO, BUSCARV buscará una coincidencia exacta en los datos; si no la encuentra, devolverá #N/A. Por el contrario, si el el argumento es VERDADERO, BUSCARV buscará la coincidencia más cercana inferior al valor buscado. En los casos como este, en los que buscamos una coincidencia exacta, es muy importante establecer el cuarto argumento a FALSO, ya que si lo omitimos, Excel considerará que es VERDADERO, con la posibilidad de obtener un resultado incorrecto en nuestras fórmulas. Casi siempre será correcto establecerlo a FALSO.

Habrá sin embargo, casos en los que deberemos poner este argumento como VERDADERO, como cuando deseamos saber a que rango de valores pertenece cierto valor. Supongamos que deseamos saber la comisión que debemos pagar a cierto vendedor según su alcance de ventas (me desvelé pensando este ejemplo). Si el vendedor tiene un alcance de 0 a 70%, le pagaremos 0% de comisión; si tiene de 71 a 100% de alcance, le pagamos un 5%; y si tiene más de 100%, le pagaremos el 6%.

Elaboramos entonces la siguiente tabla de comisiones:

Su alcance fue del 98%. Para saber que porcentaje de comisión le corresponde al vendedor, usamos la siguiente fórmula:

=BUSCARV(B6, A1:B4, 2, VERDADERO)

La cual devuelve 5%. 71% es el valor más alto inferior al número buscado (98%). De haber puesto el último argumento FALSO, habríamos obtenido #N/A, lo cual no nos sirve.

La ayuda on-line para esta función nos amplía un poco más:

"Ordenado: Valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada:

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.

Los valores de la primera columna de matriz_buscar_en deben estar clasificados según un criterio de ordenación ascendente; en caso contrario, es posible que BUSCARV no devuelva el valor correcto. ...

Si es FALSO, BUSCARV sólo buscará una coincidencia exacta. En este caso, no es necesario ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o más valores en la primera columna de matriz_buscar_en, se utilizará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.
...
Si Ordenado es FALSO y valor_buscado es un valor de texto, se pueden utilizar los caracteres comodín de signo de interrogación (?) y asterisco (*) en el argumento valor_buscado. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco, escriba una tilde (~) antes del carácter."

Continuamos en la siguiente nota.

Agregar texto a un gráfico

Muchos usuarios coinciden en que Excel no es muy flexible que digamos cuando queremos manipular texto en un gráfico. El título del gráfico, por ejemplo, no puede ser redimensionado, a menos que cambiemos el tamaño de fuente del mismo. Los rótulos de las categorías y de valores, con frecuencia aparecen divididos en dos o más filas, o muy juntos, incluso encimados. En el peor de los casos, aparecen incompletos.

Tampoco podemos vincular los rótulos de ejes a valores en celdas, ni vincular los valores máximo y mínimo del eje de valores a celdas, por ejemplo. A menos, claro está, que utilicemos alguna macro que lo haga.

Tal vez lo que más molesta al usuario común sea el hecho de no poder insertar texto independiente (llamado también texto flotante) a sus gráficos. Afortunadamente, es algo que sí se puede hacer, y es más fácil de lo que parece.

Simplemente, seleccionamos cualquier parte de nuestro gráfico, a excepción de una serie o series de datos, y comenzamos a escribir el texto deseado. Al terminar, presionamos Enter, y Excel nos mostrará un cuadro de texto en el centro de nuestro gráfico con el texto ingresado.

Ahora sí, podemos hacer y deshacer con este texto. Para formatearlo, seleccionamos un borde del mismo, damos clic derecho y seleccionamos Formato de cuadro de texto.

Incluso es posible vincular el texto de este cuadro a una celda, para lo cual simplemente seleccionamos el gráfico, tecleamos el signo =, seleccionamos la celda a la que queremos vincular nuestro texto, y presionamos Enter. Excel creará el vínculo correspondiente.

No pocos usuarios prefieren agregar títulos y textos utilizando esta técnica, en vez de utilizar los comandos nativos de Excel para hacerlo. Así se evitan los contratiempos mencionados al principio.

It´s friday...

Llega un gallego con un campesino que se encontraba arriando sus ovejas y le dice:

- Si le adivino cuantas ovejas tiene me regala una

- A ver...

3 minutos después el gallego dice:

-Son 1534

- ¿¿¿¿¿¿Como le hizo ??????

- Fácil -contesta el gallego- Sumé las patas y las orejas y la suma salio perfecta

El gallego se va y se echa al animal en el lomo... y el campesino le dice:

- Oiga y si adivino de donde es me regresa el animal?

- Ok, a ver...

El campesino le dice: -Usted es gallego

-¿¿¿¿¿Cómo lo supo????

- ¡Por que lo que lleva en el lomo es mi perro!

It´s friday...

Para que ejerciten su inglés:

A priest was being honored at his retirement dinner after 25 years in the parish. A leading local politician and member of the congregation was chosen to make the presentation and give a little speech at the dinner. He was delayed, so the priest decided to say his own few words while they waited.

"I got my first impression of the parish from the first confession I heard here. I thought I had been assigned to a terrible place. The very first person who entered my confessional told me he had stolen a television set and, when questioned by the police, was able to lie his way out of it. He had stolen money from his parents, embezzled from his employer, had an affair with his boss's wife, taken illegal drugs, and gave'em to his sister. I was appalled. But as the days went on I knew that my people were not all like that and I had, indeed, come to a fine parish full of good and loving people."

Just as the priest finished his talk, the politician arrived full of apologies at being late. He immediately began to make the presentation and gave his talk. "I'll never forget the first day our parish priest arrived," said the politician. "In fact, I had the honor of being the first person to go to him for confession."

Moral: NEVER, NEVER, NEVER BE LATE

It´s friday...

Un hombre y su nueva esposa estaban chupando tranquilos, cuando ella le dice:

-¿Me invitas a salir?

A lo que él caballerosamente responde:

-Salte

Fechas y horas en Excel. Introducción II

Continúa de la nota anterior.

Con las horas, Excel maneja otra estrategia. Lo que hace es considerar una hora de un día como una fracción de ese día. Así, el mediodía del 17 de octubre de 2007, escrito así: 17/10/2007 12:00 p.m., corresponde al número de serie 39,372.5; las 6:00 a.m del mismo día corresponden al 39,372.25; y las 6 p.m. corresponden al 39,372.75. Si en la celda A2 tenemos 17/10/2007 06:00 p.m., y en B2, 17/10/2007 12:00 p.m por ejemplo, calculamos la diferencia de horas con la fórmula:

=A2 - B2

Como por lo regular Excel expresa el resultado con el mismo formato que los argumentos, el resultado obtenido es 00/01/1900 06:00. De las 12:00 pm a las 6:00 pm del día 17 han transcurrido cero días y seis horas. Como no tiene mucho sentido hablar de cero días o de cero de enero de 1900, aplicamos el formato hh:mm a nuestra celda (Ctrl + 1, Ficha Número), de forma que nos muestre 06:00.

Aunque estamos claros que internamente Excel ve el valor 0.25, ¿cierto? (39,372.75 - 39,372.5).
Cuando manejamos horas no asociadas a un día específico, Excel no les asigna ninguna parte entera. Por ejemplo, la hora 3:35, así, sin fecha, la convierte internamente a 0.15. Es por ello que necesitamos al cero de enero de 1900 mencionado anteriormente.

Supongamos que administramos un café internet utilizando una hoja de Excel. Nuestro modelo tiene tres columnas, la primera para las horas de entrada, la segunda para las horas de salida y la tercera para el tiempo total consumido. En la celda A2 tenemos 12:00 p.m., y en B2, 06:00 p.m. Usemos esta fórmula en en C2:

=B2 - A2

La cual devuelve 06:00. Seis horas. El tiempo total consumido por nuestro usuario imaginario.

Ahora supongamos que cobramos $10.00 por hora. Para saber cuanto debemos cobrar al usuario normalmente usaríamos la siguiente fórmula:

=C2*G1

Sin embargo, el resultado, el cual esperaríamos que fuera $60.00, es ¡$2.50! Esto, que a primera vista podría parecer algo incomprensible, es, no obstante, algo perfectamente lógico y normal. ¿Por qué? Porque internamente Excel realizó la operación 0.25*10, no 6*10.

Estarán de acuerdo en que no es posible tratar a las horas y minutos como números decimales. Si queremos multiplicar una hora por un número decimal, como es el caso, primero debemos convertir dicha hora a número decimal. Vamos por partes:

Primero demos formato de número con dos decimales a la celda C2, para que nos muestre:

O.25

Este es el número de días consumidos por el usuario. Recordemos que en Excel un día equivale a una unidad. Seis horas son 0.25 días. Entonces, si queremos conocer la respuesta en horas "decimales", simplemente multiplicamos este resultado por el número de horas que tiene un día, 24:

=C2*24 = 0.25*24 = 6

Ahora sí, multiplicamos este resultado por el costo por hora, y obtenemos $60.00.

=(C2*24)*G1

Fechas y horas en Excel. Introducción

Realizar cálculos con fechas y horas suele ser una actividad altamente frustrante para el usuario principiante, debido principalmente al desconocimiento que tiene de la forma en que Excel maneja las fechas y las horas. No tiene por qué ser así. Si bien es cierto que Excel tiene algunas limitaciones con las fechas, también es cierto que tiene muchísimas capacidades.

Para Excel, una fecha es simplemente un número. Más exactamente, un número de serie. Para demostrarlo, escribamos en una celda cualquiera la fecha de hoy, con la fórmula =HOY(), o presionando Ctrl + Shift + ; o bien manualmente escribimos 17/10/2007.

Ahora le damos formato de número con cero decimales (Formato - Celdas (o Ctrl + 1), ficha Número, y en la lista escogemos "Número"). Lo que veremos en nuestra celda es el número 39,372. Esto significa que para Excel, el 17 de octubre de 2007 es el número de serie 39,372 en su sistema; de la misma forma, el 16 de octubre es el 39,371, el 15 es el 39,370, el 14 es el 39,369... y así hasta llegar al uno de enero de 1900 que es el número 1 en la serie. El sistema de fechas de Excel comienza el 1 de enero de 1900 y termina el 31 de diciembre del 9999, correspondiente al serial 2,958,465 (en realidad comienza el cero de enero de 1900, correspondiente al 0 en la serie. Escriban la fecha 00/01/1900 y Excel no reclamará nada. Más adelante veremos para que necesitamos esta fecha inexistente). Entonces, cada que escribimos una fecha, Excel en realidad mira su correspondiente número de serie.

Solo así Excel podría hacer cálculos con fechas.

Ya que conocemos lo anterior, se vuelven evidentes los siguientes procedimientos:

Supongamos que en la celda A1 tenemos 17/10/2007, y en A2, 01/10/2006.

Para conocer cuántos días han pasado entre estas dos fechas, simplemente restamos la fecha más antigua de la más reciente:

= A1-A2 = 381

Para saber cuantas semanas han transcurrido entre estas dos fechas, restamos la fecha más antigua de la más reciente, y dividimos el resultado por 7:

=(A1 -A2 )/7=54 (cambiamos el formato si es necesario)

Para saber cuántos años han transcurrido, restamos la fecha más antigua de la más reciente, y dividimos el resultado por 365:

=(A1-A2 )/365 = 1

Continuamos en la siguiente nota.

Referenciar un rango de celdas

Cuando queremos referenciar una celda a otra, de forma que cualquier cambio en el contenido o en la fórmula de esta, aparezca también en la otra celda, normalmente seleccionamos la celda que vamos a referenciar, escribimos el signo igual, damos clic en la celda referenciada y presionamos Enter, por ejemplo:

=D3

Si queremos referenciar un rango completo podemos escribir esta fórmula en cada una de las celdas a referenciar. O bien, copiamos esta fórmula al resto del rango. Este método, si bien es válido, es poco robusto, ya que podemos perder fácilmente las referencias. Podemos borrar por error una fórmula, podemos escribirla como valor, podemos insertar una columna en la hoja que afecte el rango... y siempre habrá necesidad de revisar las fórmulas. Es muy frecuente el error de sobreescribir una fórmula con solo su resultado, a mitad de una columna con fórmulas (próximamente veremos unas cuantas técnicas de diseño eficiente de hojas y libros).

La solución a estos contratiempos consiste en hacer una referenciación matricial. Para esto, seleccionamos primero nuestro rango de destino, el cual debe ser de las mismas dimensiones que el rango de origen (o más grande, aunque no lo recomiendo). Seguidamente escribimos el signo =. Después, seleccionamos el rango de origen, completo:

Aquí, en lugar de presionar Enter, presionamos Ctrl + Shift + Enter, indicando a Excel que estamos trabajando con una matriz de datos. En la barra de fórmulas, Excel insertará corchetes (brackets) al inicio y final de la fórmula, indicando la existencia de dicha matriz.

Como resultado, tendremos correctamente referenciadas todas las celdas del rango, y no podrá ser posible modificar "accidentalmente" ninguna de ellas, ni modificar la matriz de datos (no podremos eliminar ninguna celda de ella, ni podremos insertar o eliminar columnas por ejemplo). Un auxiliar cuando vamos a enviar nuestro libro a un usaurio y no queremos que modifique nuestras fórmulas.
Una pequeña desventaja es que en nuestros datos de origen no podremos tener celdas vacías, ya que la matriz los mostrará como ceros (claro que tenemos la opción de aplicar color de fuente blanco, con las debidas precauciones).

Si se dan cuenta, es la misma técnica que usamos para transponer una matriz de datos, solo que en esta ocasión no usamos la función TRANSPONER.

Validar la existencia de determinado texto en una celda

La consulta a tratar en esta ocasión es la siguiente: Quiero una fórmula que me devuelva 1 si una celda contiene el texto "mitexto" y que me devuelva 0 si no lo contiene. ¿Cómo?

Desconozco la utilidad que este usaurio daría a la fórmula. Si pensabas usar estos unos y ceros para hacer un filtro, podías activar el Autofiltro (Filtro - Autofiltro), después seleccionar Personalizar... y de la lista desplegable la opción Contiene (o bien Empieza con o Termina con) y escribir "mitexto". En los tiempos que corren ya casi no es necesario el uso de columnas de unos y ceros.

De cualquier modo, aquí va la respuesta:

Hay tres posibilidades: determinar si una celda comienza con "mitexto", termina con "mitexto" o si contiene "mitexto" en cualquier ubicación.

Para el primer caso nos auxiliamos de la función IZQUIERDA:

=IZQUIERDA(A1, 7), la cual nos da las 7 primeras letras del extremo izquierdo del primer argumento (A1).

Ahora la anidamos en la función SI, y pedimos a Excel que devuelva 1 si el resultado de la anterior fórmula es "mitexto", 0 en cualquier otro caso:

=SI(IZQUIERDA(A1,7)="mitexto",1,0)

En el segundo caso, utilizamos la función DERECHA y procedemos de la misma forma:

=SI(DERECHA(A1,7)="mitexto",1,0)

Para el tercer caso, podemos auxiliarnos de la función HALLAR, o bien, de la función ENCONTRAR. Ambas devuelven la posición inicial en la que se encuentra el texto buscado, dentro de un texto especificado, con la diferencia de que ENCONTRAR es sensible a mayúsculas y minúsculas. Utilizan tres argumentos: texto_buscado ("mitexto"), dentro_del_texto (A1) y núm_inicial (1). Núm_inicial se refiere a la posición de dentro_del_texto a partir de la cual queremos que empiece la búsqueda, contando de izquierda a derecha; si lo omitimos, Excel supondrá que es 1:

Suponiendo que en la celda A1 hemos escrito "abc mitexto", la siguiente fórmula nos devuelve 5:

=HALLAR("mitexto", A1, 1)

El problema con HALLAR es que si la celda analizada no contiene el texto buscado, en lugar de devolvernos 0, nos devuelve el valor de error #¡VALOR!

Utilizamos entonces la función ESERR. Esta función devuelve VERDADERO si el resultado de una fórmula es un error del tipo #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO! (o sea todos excepto los errores #¡N/A. En cambio, ESERROR sí toma en cuenta los errores #N/A). Si el resultado de una fórmula no es un valor de error, devuelve FALSO.

Está fórmula devuelve FALSO si "mitexto" se encuentra en A1. Devuelve VERDADERO si la celda no contiene "mitexto":

=ESERR(HALLAR("mitexto", A1, 1))

Ahora la anidamos en una función SI para indicar a Excel que devuelva el 1 o el 0 buscados:

=SI(ESERR(HALLAR("mitexto", A1, 1)), 0, 1)

En los tres casos, podemos elaborar fórmulas más cortas:

=--IZQUIERDA(A1,7)="mitexto"

=--DERECHA(A1,7)="mitexto"

=--NO(ESERR(HALLAR("mitexto",A1)))

En futuras entradas veremos cómo funcionan estas últimas fórmulas, y debatiremos la conveniencia de usar fórmulas reducidas o extensas.

Búsquedas. Introducción

Cuando queremos buscar información en una hoja de Excel, normalmente seleccionamos Edición - Buscar, escribimos el texto buscado y damos click en buscar siguiente.

Este suele ser todo el uso que se le da a este cuadro de diálogo. Sin embargo, tenemos muchas más posibilidades de búsqueda.

Si, por ejemplo, sabemos que nuestro dato buscado está varias veces repetido en nuestra hoja, puede resultar más práctico dar click en el botón Buscar todo, situado a la izquierda del botón Buscar siguiente. Al hacerlo, Excel nos mostrará una lista completa con todas las celdas que incluyen el valor buscado, el valor completo de cada celda y sus ubicaciones (podemos ampliar el tamaño de la ventana, pero solo manualmente. Cómo hace falta un botón Maximizar para esta lista...). Si sabemos cual de todas es la celda que buscamos, simplemente hacemos click en ella y Excel nos llevará automáticamente. Así no tendremos que dar Buscar siguiente una y otra vez.

Cuando queremos realizar búsquedas en todo el libro, tenemos dos opciones, la primera de ellas es seleccionar todas las hojas del libro y realizar nuestra búsqueda normalmente. La segunda opción es ir al cuadro Buscar, dar click en el botón Opciones de la derecha, y en la lista despleglabe "Dentro de:" seleccionamos Libro. Y si queremos realizar búsquedas solo en ciertas hojas de nuestro libro, primero seleccionamos estas, con Ctrl + click en la etiqueta de cada hoja y vamos al cuadro Buscar.

Otras posibilidades son: buscar por filas o columnas, buscar dentro de fórmulas o solo en valores y coincidir mayúsculas y minúsculas en la búsqueda. Incluso podemos buscar celdas que tengan cierto formato específico, pero eso ya lo veremos en otra nota.

Es importante mencionar que las opciones que seleccionemos, Excel las mantendrá para todas las búsquedas subsecuentes, hasta que las cambiemos o desactivemos.

Agregar o eliminar contraseñas

Una compañera de trabajo me hace la siguiente consulta:

Recibí un libro de trabajo protegido con contraseña, y siempre que lo abro tengo que escribir dicha contraseña. ¿Cómo hago para quitar esa contraseña y que Excel deje de preguntármela?

Aclaremos primero que podemos proteger nuestros archivos a distintos niveles. Podemos, en primer lugar, proteger contra apertura, de forma que sea imposible abrir siquiera el archivo sin conocer la contraseña. También podemos proteger contra escritura, con o sin contraseña, de forma que sea imposible modificar el archivo, aunque sí es posible visualizar su contenido.

En tercer lugar, podemos proteger el libro, de forma que sea imposible modificar el contenido y la estructura del archivo. (La diferencia con la anterior opción es que en aquella podemos hacer cualquier modificación al archivo siempre y cuando lo guardemos con otro nombre y/o ruta de acceso. En cambio Proteger el libro no permite hacer ninguna clase de cambio, ni en el contenido ni en la estructura del libro. En una futura nota aclararemos más esta diferencia). Por último, podemos proteger una o varias hojas de cálculo.

Para eliminar una contraseña de apertura tenemos primero que abrir el archivo (obviamente, tenemos que conocer la contraseña). Después, seleccionamos Archivo - Guardar Como (o bien F12).

Una vez que entramos en este cuadro de diálogo, damos click en el botón Herramientas, situado en la esquina superior derecha del mismo. Luego, escogemos Opciones generales. Eliminamos la contraseña del cuadro Contraseña de apertura, damos aceptar y guardamos el archivo.

Desde este mismo cuadro podemos eliminar también la contraseña contra escritura, eliminando la contraseña correspondiente.

Para agregar una contraseña, ya sea de apertura y/o de escritura, seguimos los mismos pasos pero ahora escribiendo la contraseña seleccionada. Excel nos pedirá que confirmemos dicha contraseña antes de continuar.

Otras opciones del cuadro de diálogo en cuestión son:

Crear siempre una copia de seguridad, con la que excel guardará siempre dos copias del archivo, en la misma ruta (por lo que pudiera pasar... No sabrán lo que es vivir hasta que Excel les diga que el archivo que les costó días terminar está "gravemente dañado", tengan que presentarlo al CEO en media hora y no puedan abrirlo). Buena opción cuando empiezan a salirnos mensajes extraños en pantalla y estamos trabajando con archivos extensos. La copia generada por Excel va precedida del texto "Copia de ".

Se recomienda solo lectura. Si seleccionamos esta opción, cada que abramos el archivo aparecerá un cuadro que nos avisa que el archivo debe abrirse como de solo lectura, a menos que deseemos guardar los cambios. Contestamos "Sí" si vamos a hacer cambios. "No" en caso contrario. En realidad es una protección contra escritura pero sin contraseña.

Transponer una matriz de datos II

Continuando la nota anterior, hay otra posibilidad, más elegante aunque no siempre necesaria, para lograrlo. Se trata de hacer una transposición referenciada o matricial, de forma que cualquier cambio en la matriz de origen se vea reflejado automáticamente en la matriz transpuesta. Para lograrlo seguimos los siguientes pasos:

Seleccionamos nuestro rango de destino, el cual debe ser de las mismas dimensiones que el rango de origen, pero lógicamente, invertidas (si queremos transponer una matriz de 3x7, seleccionamos un rango de 7x3). Con el primer método solo había que seleccionar una sola celda.

Escribimos la fórmula: =TRANSPONER( y seleccionamos nuestro rango de origen.

Cerramos paréntesis, y en lugar de presionar Enter, presionamos Ctrl + Shift + Enter, con lo cual Excel insertará corchetes (brackets) al inicio y fin de la fórmula, indicando con esto que está tratando con una matriz de datos, no con datos independientes.

Aplicamos la primera técnica (de la nota anterior) para transponer los formatos de las fechas.

Podemos comprobar que cualquier cambio en la matriz de origen se refleja en la matriz transpuesta. Otra ventaja es que siempre estaremos seguros de que los valores están correctamente referenciados. Si intentamos cambiar manualmente cualquier valor en la matriz, Excel no lo permitirá.

Tampoco podremos insertar o eliminar celdas, filas o columnas en ella, asegurando la integridad de los datos.

Transponer una matriz de datos

Esta primera nota surge de una consulta que me hace un compañero de trabajo, de quien por cierto no recuerdo su nombre. Solo se que es de Argentina.

La pregunta es la siguiente: ¿Cómo puedo transponer una matriz de datos, de forma que las filas se conviertan en columnas y viceversa?

Existen varias posibilidades. La más práctica es seleccionar el rango (o matriz) de datos, y copiarlo.

A continuación, seleccionamos la celda de destino, damos clic derecho y seleccionamos Pegado especial. Una vez ahí, activamos la opción Transponer,

y presionamos Aceptar. Por último, borramos el rango de origen.

Para los perfeccionistas, es un poco más rápido cortar, en vez de copiar, el rango de origen al inicio. De esta forma no será necesario eliminar el rango de origen en un tercer paso.

Desde luego, podemos usar Transponer en combinación con otras opciones de Pegado especial, por ejemplo, Valores o Formatos.

La segunda opción es copiar (o cortar) el rango, y dar clic en la flecha del botón Pegar de la barra de Herramientas estándar, desplegando el menú correspondiente, y seleccionando la opción Transponer.
En la siguiente entrada veremos una tercera opción para realizar esta tarea.

Saludos.

Presentación.

Saludos.

MS Excel es el programa de cómputo más potente y flexible del mundo. Ningún otro programa puede competir con él en cuanto a funciones o flexibilidad.

Sin embargo, y como consecuencia de ello, es también uno de los programas más pésimamente aprovechados por la gente.

En el mundo, miles de millones de dólares se mueven gracias a este programa. Miles de decisiones se toman apoyadas en en él. Millones de empresas de todo el mundo simplemente no podrían operar si no tuvieran Excel en sus equipos de cómputo. Gran parte de los programas "independientes" que existen, en realidad utilizan a Excel como motor de cálculo.

Lamentablemente, la enorme mayoría de las personas que lo usan, solo utilizan, cuando mucho, del 10% al 30% del total de sus capacidades (atención reclutadores: aquellos que dicen tener "un 100% de Excel", es, precisamente, porque no tienen "un 100% de Excel". En el mundo no hay más de 100 personas con ese nivel), condenando al olvido una gran cantidad de características que las harían ser mucho más productivas en sus actividades. En ciertos casos, tareas que a un usuario con un nivel normal de Excel le tomaría varias horas terminar, es posible formularlas, optimizarlas y en el último de los casos, programarlas de forma que puedan realizarse en unos 30 segundos. O hasta menos, si contamos con un Lentium 10 (o 12, o 13, no se que número vaya).

Así, surge la necesidad de contar con un espacio en el que la gente pueda aprender y colaborar con sus conocimientos con otras personas, a fin de mejorar su efectividad, principamente en el trabajo.

En este blog intentaré apoyar a usuarios tanto del nivel básico (llamados también usaurios), como de nivel avanzado, en sus dudas o problemas al utilizar Excel. Comenzaré con las consultas que ya me han hecho algunas personas, y con las que hagan ustedes en el futuro. Si ya no hay más, entonces publicaré notas de interés general. Siéntanse libres de hacer sus comentarios y/o sugerencias para el blog.

Para dudas no relacionadas con alguna de las entradas ya publicadas, favor de enviarlas a:
leonelquezada2003@yahoo.com, o
leonel.quezada@metrored.com.mx

Hasta la próxima entrada.