Prescindiendo de la función SI

La función SI es una de las funciones más utilizadas e intuitivas de Excel. Sin embargo, cuando usamos fórmulas con varios SI anidados, pueden llegar a volverse extraordinariamente confusas y "enredadas". Además, tenemos la limitante de solo poder anidar hasta un máximo de siete SIs.

Existen varias maneras de expresar situaciones lógicas que prescinden de la función SI que vuelven más legibles y fácilmente modificables nuestras fórmulas.

Frecuentemente se nos presentan problemas de este tipo: Dado cierto valor, si este es menor a cero, lo ignoramos; si es mayor, hacemos algún cálculo con él. Se vuelve completamente irresistible pensar en la función SI para formular esto. Por ejemplo:

=SI(A2<0,0,A2/G1)

Es más corto y legible utilizar la función MAX (que devuelve, lógicamente, el máximo de un grupo de valores [máximo 30]):

=MAX(0, A2/G1)

It´s friday...

John Doe is in the hospital and says:

-Doc, it hurts when I touch here (his leg) and here (his arm) and here (his ribs), what's wrong with me?

The doc says:

-You've broke your finger.

Gráficas sin gráficas

Dentro del amplio repertorio de funciones de texto de Excel, encontramos la función REPETIR. El valor devuelto es simplemente una cadena de texto dada, repetida un número especificado de veces. La sintaxis es sencilla:

=REPETIR(texto,núm_de_veces)

En el siguiente ejemplo,

tenemos esta fórmula en B2:

=REPETIR(A2,5) . En B3 tenemos: =REPETIR(A3 & " ",5)

Si queremos vernos "creativos" podemos usar una fuente Wingdings:
Al usar la imaginación y el caracter de barra vertical como texto a repetir:

el resultado semeja una barra de una gráfica de barras horizontal. De esta forma podemos usar la fórmula para simular una gráfica de barras básica y que ocupa mucho menos espacio en disco que una gráfica normal. Lo único que hay que hacer es utilizar los valores que queremos graficar como segundo argumento de REPETIR. En caso de que las "barras" sean muy cortas o largas ajustamos el argumento núm_de_veces con un factor adecuado.



Otras ideas son usar formato condicional o alinear a la derecha valores negativos y positivos a la izquierda, simulando un histograma.

La fórmula en B2 es:

=ENTERO(A2) & " " & REPETIR("-", A2/10)

con valores alineados a la derecha. En C2 tenemos:

=REPETIR("-", A2/10) & " " & ENTERO(A2)

y alineación a la izquierda. Si no encontramos el caracter de barra vertical en el teclado, escribimos:

=ENTERO(A2) & " " & REPETIR(CARACTER(124), A2/10); y

=REPETIR(CARACTER(124), A2/10) & " " & ENTERO(A2)

En esta página de juiceanalytics podemos encontrar otros varios ejemplos de como usar esta técnica.

Combinar BUSCARV y COINCIDIR

Como ya somos expertos en el uso de BUSCARV I, II y III, estamos en la posibilidad de empezar a usarla en combinación con otras funciones, por ejemplo, COINCIDIR.

COINCIDIR nos devuelve la posición de determinado valor dentro determinado rango (matriz) de datos, contando de izquierda a derecha o de arriba a abajo, comenzando en 1. Dicho rango o matriz de datos debe ser unidimensional, es decir, de una sola fila o de una sola columna. La sintaxis es:

COINCIDIR(valor_buscado,matriz_buscada,tipo_de_coincidencia)

En el siguiente ejemplo:


utilizamos la función para saber el turno de atención de un grupo de pacientes de un neurocirujano. La fórmula en E2 es: =COINCIDIR(E1,A2:A11,0)

Si seleccionamos un rango de más de una fila o columna, obtendremos el resultado de error #N/A. Forzosamente nuestro rango de búsqueda debe ser unidimensional. El último argumento, tipo_de_coincidencia, indica a Excel si debe buscar una coincidencia exacta o aproximada.

Retomando nuestro tema, consideremos el siguiente resumen de gastos:

Si queremos conocer lo que gastamos en teléfono en febrero de 2006, escribiremos esta fórmula, digamos que en B4:

=BUSCARV("telefono",A7:G19,3,FALSO)

Si queremos la cantidad de marzo, modificaremos la fórmula de esta manera:

=BUSCARV("telefono",A7:G19,4,FALSO)

Como no resulta práctico el estar modificando una y otra vez una fórmula hacemos las siguientes modificaciones a nuestro modelo:

Creamos en B3 una lista desplegable utilizando Datos - Validación... - Lista, con los encabezados de mes como argumentos:

Luego, en nuestra fórmula de B4, modificamos el tercer argumento de BUSCARV (columna) y lo formulamos utilizando COINCIDIR y la celda B3:


La fórmula de B4 es:

=BUSCARV("telefono", A7:G19, COINCIDIR(B3,B6:G6,0)+1, FALSO)

Ahora, para saber lo gastado en telefono en cada mes simplemente lo seleccionamos de la lista.

Por último, creamos otra lista desplegable en A4 utilizando los conceptos de gasto como argumentos de la lista. En la fórmula, sustituimos "telefono" por A4. De esta manera obtenemos una lista cruzada de valores "dinámica":

It´s friday...

The company sergeant is briefing the recruits:

- For the next ten weeks the commanding officer will be your father, and I will be your mother. Incidentally we are not married, so you know what that makes you...

It´s friday...

One day a police officer clocked a car driven by a marine at over 90 miles per hour. The marine, one of our older military veterans of three wars, immediately pulled over once he saw the blinking lights of the black and white Dudley Do-right on his bumper.

Marine: Hi officer. Did I do anything wrong?

Officer: Sir I clocked you at 90 miles per hour. May I see your license please?

Marine: Naw that aint possible. I lost my license when I was charged with the DUI about 4 years back.

Officer: I see. The can I see your registration?

Marine: No that won't be possible either. See this is a stolen car.

Officer: stolen car?

Marine. Yep. The guy I stole it from is in the trunk.

Officer: he's in the trunk?

Marine: yep. Bopped him on the head, cut him up into a hundred pieces, put em in plastic bags and popped him right into the trunk. You want to see him?

Officer: no. You just stay in the car. The officer goes back to his car.

Five minutes later, five police cars roll up and surround the vehicle. The police captain strolls up to the car, with a drawn gun.

Captain: Sir, would you mind getting out of your car?

Marine: Sure sir. And he gets out

Captain: sir I just received a report from one of my men that he had just apprehended a driver
without a license. Do you have a license to drive sir?

Marine sure do and pulls out his driver license from his wallet.

Captain: I also received word that the man was driving a stolen car, with possibly a body in the trunk. Is this the case?

Marine: Naw sir, this is my car.

He pulls the registration out of the glove compartment, and hands it to the captain.

Captain: would you mind opening your trunk?

Marine: sure sir.

He then opened the trunk to reveal...nothing.

Captain: I am very puzzled. My man reported that he had apprehended an unlicensed driver, driving a stolen car, with a body in the trunk. None of that seems to be true.

Marine: yeah, and I bet the little liar even told you I was speeding didn't he?

Dividir texto en palabras

En ocasiones anteriores aprendimos algunas técnicas introductorias para separar texto en palabras. El objetivo de esas notas era aprender a utilizar las funciones utilizadas, no tanto el dividir textos en palabras. Cuando lo único que nos interesa es hacer dicha división, sin necesidad de fórmulas, podemos utilizar el Asistente para convertir texto en columnas de Excel.

Los pasos son los siguientes:

Seleccionamos la celda o la columna con el texto que queremos dividir y vamos a Datos - Texto en columnas. Con esto se abrirá el referido Asistente, en el Paso 1 de 3.

Aquí, tendremos que especificar uno de dos tipos de datos que manejaremos: Delimitados o De ancho fijo. Son delimitados cuando tenemos claramente identificado el separador que utilizaremos para hacer la división. En nuestro caso (dividir en palabras), los datos son delimitados, ya que usaremos un separador específico: el espacio. Otros separadores pueden ser: el caracter de tabulación, punto y coma, coma, o cualquier otro que esté claramente definido. El segundo tipo de datos que podemos usar es De ancho fijo. En este tipo no hay separador, por lo que el criterio a usar será un número fijo de caracteres por columna. Especificar este tipo es útil cuando queremos separar por ejemplo, una serie de claves de producto, como la siguiente:

abc123wxyz1

en sus distintas subclaves, siendo la primera los primeros tres caracteres, la segunda los caracteres 4 al 6, la tercera los caracteres 7 al 10 y la cuarta el caracter 11.

Como ya hemos seleccionado Delimitados, damos clic en Siguiente para ir al Paso 2 de 3.


En este, tendremos que especificar nuestro separador. En el caso nuestro, seleccionamos Espacio. Podemos especificar también si queremos que los separadores consecutivos sean considerados como uno solo. Damos clic en siguiente.

Finalmente, en el último paso podemos establecer el formato de los datos columna por columna y la celda de destino. Si hay alguna columna que no deseamos que sea mostrada, la seleccionamos y seleccionamos No importar columna (saltar). Damos clic en Finalizar.


En general es más práctico utilizar este método que hacer la división con fórmulas. Pero para divisiones de precisión quirúrgica, las fórmulas son más exactas y controlables.

Ctrl + C Reloaded

Todo mundo sabe para que sirven Ctrl + C, Ctrl + X y Ctrl + V. Son los métodos abreviados para copiar, cortar y pegar respectivamente. Estos atajos están optimizados para ser usados con la mano izquierda. Esto es así porque, en su infinita sabiduría, los programadores de Microsoft dedujeron que a la mayoría de la gente, que es diestra, le fascina utilizar la mano izquierda.

Afortunadamente también hay métodos abreviados para copiar, cortar y pegar optimizados para la mano derecha. Son los siguientes:

Ctrl + Insert = Copiar

Shift + Del = Cortar

Shift + Ins = Pegar

Sabiamente, Microsoft decidió no difundir estos atajos. Por qué, quién sabe. Es uno de los grandes enigmas del siglo XX.

Siento decirles a los zurdos y a mis compañeros de trabajo que leer esta nota no les ha servido de nada. A los primeros porque les es más natural utilizar los métodos de siempre, y a los segundos porque la mayoría de nuestros equipos (que son lap tops) no tienen una tecla Ctrl del lado derecho, o tienen Ins y Del en la parte inferior. Pero para todos aquéllos que utilicen PC, o un teclado estándar completo, es más natural dar Ctrl + Insert que Ctrl + C. De hecho, con un poco de práctica, logra hacerse sin mirar el teclado.

CONTAR.SI (y SUMAR.SI) Reloaded

CONTAR.SI nos retorna el número de celdas (o valores) que cumplen con determinado criterio dentro de determinado rango. La sintaxis es:

CONTAR.SI(rango, criterio)

Generalmente solo es usada en ejemplos como los siguientes:

=CONTAR.SI(A2:A30, 100);

=CONTAR.SI(B2:B20, "alguntexto");

=CONTAR.SI(C2:C40, "texto a" & "texto b"); o

=CONTAR.SI(D2:D50, C1)

Sin embargo, el argumento criterio es mucho más flexible. Podemos especificar expresiones lógicas, wildcards, fórmulas, valores lógicos, fechas... Estos son algunos ejemplos que muestran la potencia de la función al usar criterios complejos:

=CONTAR.SI(A2:A50, ">0") Devuelve el número de celdas con valores mayores a 0.

=CONTAR.SI(A2:A50, "<>1") Devuelve el número de celdas con valores distintos de 1.

=CONTAR.SI(A2:A50, "<" & B1) Devuelve el número de celdas con valores menores a la celda B1.

=CONTAR.SI(A2:A50, HOY()) Cuenta las celdas con fecha igual a la actual.

=CONTAR.SI(A2:A50, ">" & FECHA(2007,10,01)) Devuelve el número de celdas con fecha mayor (más reciente) a 01/10/2007. Ya hemos visto el uso de la función FECHA.

=CONTAR.SI(A2:A50, ">" & PROMEDIO(A2:A50)) Número de celdas con valores superiores al promedio del rango.

=CONTAR.SI(A2:A50, "?????") Da el número de celdas que contienen exactamente cinco caracteres.

=CONTAR.SI(A2:A50, "abc*") Da el número de celdas que empiezan con "abc". No es sensible a mayúsculas

=CONTAR.SI(A2:A50, "*abc*") Da el número de celdas que contienen "abc". No es sensible a mayúsculas.

=CONTAR.SI(A2:A50, "??z*") Devuelve el número de celdas cuyo tercer caracter es "z".

=CONTAR.SI(A2:A50, FALSO) Cuenta las celdas que contienen el valor lógico FALSO.

=CONTAR.SI(A2:A50, "#!REF!") Cuenta el número de celdas que contienen el valor de error #!REF!

Desde luego, podemos combinar las técnicas de los ejemplos anteriores. También, podemos usar este tipo de criterios en otras funciones, como SUMAR.SI.

Esta nota pensaba titularla "En el pedir está el dar".

Convertir texto a valores

Al importar o exportar datos (algo que mis compañeros de trabajo extrañamente llaman "bajar reportes de Sales Force") casi siempre vemos que Excel da formato de texto a los datos numéricos. El porqué Excel hace esto es algo que escapa a las capacidades mentales del que firma el presente. Tal vez sea un error de diseño. Tal vez sea por la diferencia de formatos. Tal vez nadie lo sabe. Tal vez...

El punto es que podemos convertir el formato de números como texto a números de varias maneras. Quizá la menos conocida es la siguiente: Nos situamos en cualquier celda vacía y la copiamos con Ctrl + C (por si alguien desconocía el significado de Ctrl + C...). A continuación seleccionamos el rango de valores que queremos arreglar. Damos clic derecho y vamos a Pegado especial. Una vez aquí seleccionamos la opción Valores y, en Operación, seleccionamos Sumar.

Por último, damos Aceptar.

Ahora tenemos valores numéricos. Si los valores originales tenían ceros a la izquierda, vemos que han sido eliminados.

¿Cómo funciona? Al seleccionar una opción de operación (Sumar), estamos forzando a Excel a tratar (y convertir) los valores como números. Como copiamos una celda vacía, estamos sumando 0 a los valores. Recordemos que una celda vacía es equivalente a 0 para efectos de cálculo.

La opción Valores la seleccionamos preventivamente para no eliminar los formatos que previamente hubiéramos aplicado.

Dos historias felices

Primera Historia Feliz.

Había una vez una usuaria que se llamaba Herenia con el siguiente problema: Había escrito una fórmula en una celda pero al dar Enter ella solo veía su fórmula. Ella quería ver el resultado de su fórmula, no su fórmula en sí -una situación un tanto frecuente. Aquí el detalle fue que la celda rebelde tenía formato de texto, así que cambiamos su formato a número y volvimos a ingresar la fórmula. Y vivió feliz para siempre. Fin.

Segunda Historia Feliz.

Érase una vez una usuaria llamada Claudia que estaba tratando de elaborar una fórmula con SUMAR.SI y CONTAR.SI. Ella quería establecer dos criterios de suma y dos de cuenta para después hacer un promedio. Como ella sabía que SUMAR.SI y CONTAR.SI pueden aceptar fórmulas como argumentos, concatenó los dos criterios con "&", pero no lo lograba. Después de hacer un análisis exhaustivo, vimos que el criterio a utilizar y el establecido en la fórmula no eran iguales. En su fórmula puso "Citas" pero en sus celdas tenía "Citas " (con espacio). Así que cambiamos "Citas " por "Citas" y ya pudo hacer su fórmula (en realidad hicimos un promedio matricial, pero igual vale). Fin.

It´s friday...

Ahora los tres in inglish.

After much arguing farmer #2 states that he is prepared to settle the matter by the Viking method. He explains that the method involves kicking each other in turn between the legs until one gives up, and the other is the winner.

Farmer #1 agrees reluctantly.

Farmer #2 states that since they are on his land, he goes first.

Farmer #1 stands with legs apart and hands on hips while Farmer #2 takes an almighty swing with his foot and sends farmer #1 into the air. After ten minutes writhing on the ground farmer#1 eventually gets to his feet and prepares to take his turn. Farmer #2 turns and walks away saying:

-O.K. I give in! You keep the duck!

It´s friday...

Zebidiah Zacariah joined the army, before long there was a call to war and they all lined up to receive their kit. Now because the army did everything in alphabetical order Zebidiah Zacariah was always last in line.When he got to the head of the line for his rifle there were none left so the seargent gave him a broom and said:

-Point this at the enemy and yell bang bang gun!

Zebidiah wasn't to bright so he accepted his broom with glee. The same thing happened in the line for grenades and he was given lemons and told

-Hurl these at the enemy yelling "Boom Boom grenade!" and youll be right.

He went to war and the battling was fierce, he threw his lemons and bang banged on his gun for all he was worth. Soon he looked around and realized there was only him and a guy from the other side left alive, he pulled out his broom and yelled 'Bang Bang Gun!' but the guy kept on coming so he hauled off with his lemons yelling 'Boom Boom grenade! and still the guy kept on coming ...until he was almost right on top of Zebidiah and Zebidiah heard him say:

-Rumble Rumble Tank!

It´s friday...

The Taco Bell Chihuahua, a Doberman and a Bulldog are in a bar having a drink when the great-looking female Collie comes up to them and says:

-Whoever can say liver and cheese in a sentence can have me.

So the Doberman says:

-I love liver and cheese.

The Collie replies:

-That's not good enough.

The Bulldog says:

-I hate liver and cheese.

She says: -That's not creative enough.

Finally, the Chihuahua says:

-Liver alone... cheese mine!

Those Collie and Chihuahua puppies are really cute.

Crear listas personalizadas

Una de mis dinusuarias favoritas, Maira, me sale con esta pregunta: "Tengo una tabla de datos que quiero ordenar respecto a la columna 'Probabilidad', en el siguiente orden: que aparezcan primero los que tengan la etapa 'venta liberada', luego los que tengan 'datos completos', 'oportunidad perdida' y al final los que tengan 'oportunidad detectada'. En ese orden. ¿Cuál es la serie de pasos secuenciales que debo seguir para poder llevar a cabo correctamente esta tarea?" (sic).

Evidentemente, no podemos utilizar el comando estandar Ordenar, ya que este sigue un criterio alfabético y el orden deseado no es alfabético. Asimismo, podríamos usar una columna adicional que valide los datos de la columna y asigne, mediante un SI, unos, doses, treses o cuatros a todos los registros y después realizar la ordenación respecto a esta columna adicional, pero no es un proceso muy elegante que digamos. O si no nos gusta trabajar, podemos escribir manualmente los unos, doses, treses y cuatros en la columna y después ordenar. De esta manera perderemos completamente la elegancia y caeremos cínicamente en lo vulgar. Otra opción es ordenar manualmente cada una de las filas, lo cual nos rebajaría en la escala social al mismo nivel de los políticos.

La solución consiste en crear y utilizar una Lista Personalizada. Procedemos como sigue:

Vamos a Herramientas - Opciones (Alt, h, n). Seleccionamos la ficha Listas Personalizadas. Luego, de la lista Listas personalizadas seleccionamos nueva lista. Después, en la lista Entradas de lista escribimos los datos que queramos que Excel tome como criterio, en el orden deseado, separados con comas (o bien, un dato por fila) y sin comillas. En el caso nuestro escribiremos:

venta liberada, datos completos, oportunidad perdida, oportunidad inválida

Damos Aceptar. Hemos creado una lista personalizada.

Ahora, para realizar la ordenación usando esta lista como criterio, vamos a Ordenar como normalmente lo hacemos (Datos - Ordenar) y seleccionamos la columna a ordenar ("Probabilidad"). A continuación, damos clic en el botón Opciones, abajo a la izquierda, para ir al cuadro Opciones de ordenación:

En la lista despleglabe que aparece, buscamos y seleccionamos nuestra lista, y damos Aceptar para volver al cuadro Ordenar. Establecemos otros criterios de búsqueda, si los hay, y volvemos a dar clic en Aceptar (o Enter).

Voilà.

Dividir nombres completos en nombre y apellidos II

Continuamos de la nota anterior.

Ya sabemos como extraer el nombre y el apellido paterno de un nombre completo, suponiendo que sea de la forma nombre - apellido materno - apellido paterno. Veamos ahora como extraer el apellido materno. (Por cierto, mi nombre completo es Leonel Quezada Ramírez. Ramírez por partida de madre y Quezada por parte de algo que se movía en la cocina y que espantó a mi mamá).

Para realizarlo necesitamos haber extraido primero el nombre y el last name del nombre en cuestión, como hicimos en la nota anterior,

además de la función EXTRAE. En C2 haremos una extracción sin anestesia.

Pueden imaginar lo que hace la función EXTRAE. La sintaxis es la siguiente:

EXTRAE(texto, posición_inicial, núm_de_caracteres)

EXTRAE devuelve un cadena de texto que se encuetra dentro del argumento texto, que inicia en la posición indicada en posición_inicial y que tiene una longitud igual a núm_de_caracteres.

Siguiendo con el ejemplo, nuestro argumento texto es A2; posición_inicial es la posición en que se encuentra el primer espacio, más uno:

=HALLAR(" ", A2, 1) + 1

núm_de_caracteres debe ser igual al número de caracteres del apellido materno. ¿Cual es la longitud del apellido materno? Simplemente la longitud del nombre completo menos la longitud del nombre y del apellido paterno, más dos, por los espacios. Esta fórmula devuelve el apellido materno:

=EXTRAE(A2, HALLAR(" ", A2, 1) + 1, LARGO(A2) - (LARGO(B2) + LARGO(C2) + 2))

Es importe señalar que las técnicas utilizadas solo sirven para dividir nombres similares al del ejemplo. Si pretendemos usarlas con nombres con títulos (como Mr. o Lic.) o apellidos compuestos (de la Torre), la cosa se complica demasiado, aunque pueden utilizar lo visto como un buen punto de partida. Asimismo, si las utilizamos para dividir otros textos, las técnicas para obtener la segunda y la última palabra solo funcionan con textos de tres palabras. Para tratar con textos más largos, es necesario otro tipo de fórmulas. Además es necesario dominar, primero, las técnicas de esta nota y la anterior.

Dividir nombres completos en nombre y apellidos

En alguna ocasión un conocido mío tenía el siguiente problema: Había recibido por e - mail una lista con nombres de personas en Excel, pero con la particularidad de que se presentaban juntos nombre y apellidos en una misma celda. Aparecía primero el nombre y luego los apellidos de la persona. Él necesitaba separar nombres de apellidos para poder ordenarlos por apellido paterno, entre otras cosas. No podía utilizar DERECHA ni IZQUIERDA (que ya vimos anteriormente) porque obviamente cada nombre tenía una extensión distinta.

En realidad sí tenemos que utilizar DERECHA e IZQUIERDA, aunque en combinación con otras más: EXTRAE, LARGO, ENCONTRAR (o HALLAR) y opcionalmente, ESPACIOS, NOMPROPIO, MAYUSC y MINUSC. Excel es muy flexible al trabajar textos. Solo hay que saber pedirle las cosas.

Dividamos el siguiente nombre completo, el cual está en A2, en nombre, apellido paterno y apellido materno.

Alejandra Reyes Avendaño

Eliminemos también los espacios excedentes entre palabras. Estos espacios casi siempre se producen al importar datos. Para hacerlo usamos la función ESPACIOS, la cual elimina los espacios excedentes excepto los espacios individuales entre palabras.

=ESPACIOS(A2)

Recordamos de la misma nota anterior que ENCONTRAR nos devuelve la posición en la que se encuentra determinado texto (o caracter) dentro de otro texto. Usémosla para determinar la posición del primer espacio en blanco de nuestro nombre, con esta fórmula:

=ENCONTRAR(" ", A2, 1)

En nuestro caso el resultado es 10. Si restamos uno, obtenemos la longitud de "Alejandra" (9).

Por lo tanto, para extraer el nombre, utilizamos la función IZQUIERDA con la fórmula anterior como segundo argumento. Esta fórmula devuelve la primera palabra de un texto:

=IZQUIERDA(A2, ENCONTRAR(" ", A2,1)-1)

Ahora determinemos el último apellido. Esto es un poco más difícil ya que ENCONTRAR solo funciona de izquierda a derecha. Aquí el problema es encontrar la posición del segundo espacio dentro del nombre para poder utilizarlo con la función DERECHA. (En nuestro ejemplo el segundo espacio está en la posición 16). Utilizamos la función ENCONTRAR, pero especificando como caracter inicial de búsqueda la posición del primer espacio más uno: un ENCONTRAR doble (sinceramente espero que vayan entendiendo). En otras palabras, queremos la posición del carácter " " empezando a contar de la posición 11.

Esta fórmula devuelve 16:

=ENCONTRAR(" ", A2, ENCONTRAR(" ", A2, 1)+1)

Ahora usamos la función derecha con el segundo argumento igual a la longitud total del texto menos los primeros 16 carácteres. Esta fórmula extrae el último apellido de un nombre completo:

=DERECHA(A2, LARGO(A2)-ENCONTRAR(" ", A2, ENCONTRAR(" ", A2,1)+1))

Continuamos en la siguiente entrada.

Repetir encabezados al imprimir

Hoy veremos una cuestión básica de Excel, pero que igualmente puede ser de utilidad para algunos.

Buena parte de las veces que imprimimos listas de datos, sucede que estas tienen una extensión de más de una página. Si no hacemos ningún ajuste, solo la primera página mostrará los encabezados. Resulta poco práctico imprimir una lista sin encabezados, sobre todo si tenemos 1000 columnas (o filas) en nuestra lista.

Para lograr que Excel repita la fila o filas de encabezados en cada página impresa, hacemos el siguiente ajuste:

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

Una vez aquí, escribimos en Repetir filas en extremo superior, las filas que queramos que se repitan en cada hoja, por ejemplo $3:$4 (no necesariamente tienen que ser las primeras).

Alternativamente, podemos dar clic en el botón de selección de rango, situado dentro del input box, a la derecha, y seleccionamos las filas con el ratón. Al finalizar volvemos a dar clic en el botón de selección para regresar a Configurar Página.

Desde aquí también tenemos la opción de repetir una columna o columnas en el extremo izquierdo de cada hoja, en los casos en que trabajamos con demasiadas columnas.

Como observación, tenemos que hacer estos ajustes estando en la vista normal. Si lo intentamos desde la vista preliminar (y después haciendo clic en el botón Configurar...) estarán inhabilitadas ambas opciones.

Nota

Los fines de semana no podré publicar notas. Los días festivos, como hoy y mañana, tampoco.

Muchas gracias bye.

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. 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:

Supongamos que su alcance real 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:

0.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.