Excel Total

Validación de datos dependiente

2011-10-24T12:31:00.003-05:00

El problema es el siguiente: Dada una lista de validación, queremos limitar los valores mostrados en ella, dependiendo del valor de otra celda. Es decir, si en la celda A2 selecciono la categoría Frutas, quiero que solo aparezcan frutas en la lista desplegable de B2:

... pero si selecciono Verduras en A2, la lista de B2 solo debe mostrar verduras. Una forma de lograrlo es combinando la validación de datos con la función INDIRECTO y un par de nombres. Comenzamos nombrando nuestra lista principal. En una zona vacía de la hoja escribimos los valores que deben aparecer en la lista ("Frutas" y "Verduras"), seleccionamos ambos valores (sin el encabezado) y escribimos "categorias" en el cuadro de nombres (a la izquierda de la barra de fórmulas). Enter.

Ahora definimos nuestras listas auxiliares, cuyos encabezados deben ser exactamente iguales a los valores de la primera lista.

Escribimos los valores de cada lista y definimos los nombres Frutas y Verduras de la misma forma que hicimos con las categorías.

Procedemos ahora con la validación en sí. Selecionamos las celdas en las que escribiremos las categorías, vamos a Datos - Validación... Permitir: Lista y en en cuadro Origen escribimos la fórmula: =categorias. Aceptar.

Solo nos falta crear la validación dependiente. Seleccionamos las celdas donde aplicaremos ésta, nuevamente vamos a Datos - Validación... Permitir: Lista y en el cuadro Origen escribimos el signo igual y la función INDIRECTO, referenciándola a la primera celda de la columna de Categorías, A2 en este caso: =INDIRECTO(A2). Aceptar.

En este punto, si la celda A2 está vacía veremos el siguiente mensaje:

Damos clic en Sí para continuar.

Probemos el funcionamiento de las listas. En A2 seleccionemos el valor Frutas, y en B2 demos clic en la flecha de la lista desplegable. Si hicimos todo correctamente, la lista mostrará los valores Fresa, Manzana, Sandía, Pera y Kiwis, como en la primera imagen.

Convertir números positivos a negativos

2010-09-08T12:42:00.003-05:00

Para convertir una lista de números positivos a negativos rápidamente, les paso este tip.

En primer lugar, copiamos los valores a convertir. En este caso el rango A2:A11.

Seleccionamos la celda donde queremos que aparezcan los valores negativos (B2). Damos clic derecho - Pegado especial...

Habilitamos la opción Restar y finalmente damos Aceptar. Obviamente, si hacemos el pegado sobre el mismo rango copiado obtendremos una lista de ceros.

Estamos en twitter

2010-02-23T16:18:00.007-06:00

A partir de ahora inicio con la publicación de tips y notas rápidas de Excel a través de twitter. Sígueme en @leonel_xl. Pueden acceder rápidamente dando clic en la imagen de la derecha.

Dadas las características de twitter, estimo que tendré posts mucho más frecuentes allí que en este blog (al cual reconozco que ya no le he dedicado el tiempo que se merece). No obstante, seguiré estando al pendiente del chat.

Saludos.

¡Nos vemos en twitter!

Mejora el manejo de tablas en Excel 2010

2010-01-26T12:56:00.011-06:00

Esta vez veremos algunas mejoras en Excel 2010 en lo que respecta al manejo de tablas. No se trata de características nuevas en sí, sino de mejoras a características ya existentes en 2007.

La primera de estas mejoras, Autofiltros en los encabezados de la hoja, son una versión mejorada de los encabezados de hoja de la versión anterior. En Excel 2007, cuando los encabezados de una tabla seleccionada eran desplazados por el usuario fuera del rango visible de la hoja, las etiquetas de las columnas se insertaban en los encabezados de la hoja para poder visualizarlos en todo momento. Esta funcionalidad ahora mejora e inserta los autofiltros desplegables en los encabezados de columna de la hoja, cuando el usuario se desplaza por la tabla hacia abajo ocultando los títulos de columna en la parte de arriba de la hoja. Además, estos autofiltros permiten ordenar y hacer búsquedas sin la necesidad de estarse desplazando hasta arriba de la tabla cada vez. Digamos adiós a Inmovilizar paneles.

La imagen de la izquierda siguiente muestra una hoja de Excel con una tabla. La imagen de la derecha muestra la misma hoja desplazada hacia abajo de modo que los encabezados quedan fuera del rango visible. En el círculo rojo vemos que los encabezados de la hoja han cambiado para mostrar los encabezados de columna así como las listas desplegables de los autofiltros.

La segunda mejora que presenta Microsoft es la introducción de búsquedas en los Autofiltros. Los usuarios frecuentemente trabajan con grandes volúmenes de datos en Excel. Estas bases de datos pueden ser difíciles de explorar y navegar en las versiones previas ya que todos los elementos de las listas se muestran en la lista desplegable. Con un cuadro de búsqueda directamente en las listas de autofiltro, Excel permite una navegación rápida de la tabla al aplicar rápidamente filtros personalizados, como se ve en esta imagen:

La tercera característica simplifica la notación de las fórmulas utilizadas en fórmulas, haciéndolas más compactas y legibles. Excel 2007 utilizaba la notación "[#Esta fila]" para indicar que las celdas referenciadas eran de la misma fila en la tabla. Esta notación requería la mención explícita del nombre de la tabla referenciada, lo que usualmente resultaba en fórmulas demasiado extensas.

2010 ha condensadado esta notación, reemplazándola con una arroba "@", y reduce el uso de la mención explícita a la tabla de origen cuando es posible. Veamos esta imagen, con la misma fórmula utilizada en 2007 (arriba) y en 2010 (abajo).

Además, este cambio en las notaciones no afectará la versión de nuestros archivos. Es decir, Excel mostrará las fórmulas de uno u otro modo dependiendo de la versión, sin afectar la compatibilidad de las fórmulas. Asimismo, en 2010 podemos ingresar fórmulas con cualquier notación previa, siendo reemplazadas por la nueva notación automáticamente.

Confiemos en que estos cambios mejorarán importantemente la experiencia del usuario al manejar tablas de datos en Excel 2010.

BUSCARV cambia a CONSULTAV

2010-01-07T16:52:00.004-06:00

Me encuentro en los foros de Excel que los nombres de algunas funciones han cambiado en Excel 2010 beta, como la popular BUSCARV.

Por el momento se tienen estos casos:

Si buscamos en la ayuda vemos que sigue apareciendo aunque un poco confusa ya que en algunos momentos las cita en inglés (VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) pero también lo hace si buscamos en la Web de Office. La sintaxis de estas funciones no cambian, solo lo hacen sus nombres, probablemente para darles mayor claridad.

Tengamos en cuenta que se trata de una versión beta, por lo que estos cambios pueden no ser definitivos.

Lanzamiento de Excel 2010 beta

2009-11-19T11:31:00.011-06:00

Finalmente, Microsoft lanza al mercado la versión beta de Excel 2010. Para aquellos interesados en probarlo, pueden descargarlo del sitio de MS Office. Asegúrense de activar la opción de descarga personalizada y activar "Mantener versiones anteriores". De otro modo, la descarga actualizará la versión actual de Office y no será posible volver a ella. De preferencia, realicen la descarga en una máquina secundaria para no arriesgar datos importantes.

De acuerdo a Microsoft:

Microsoft Excel 2010 posibilita analizar, administrar y compartir información de más formas que nunca, lo que le ayudará a tomar mejores decisiones y más inteligentes. Las nuevas herramientas de visualización y análisis de datos le ayudarán a realizar un seguimiento y a resaltar tendencias de datos importantes. Cargue los archivos con facilidad en la Web y trabaje simultáneamente con otros usuarios en línea. Tenga acceso a sus datos importantes cuando esté de viaje desde casi cualquier explorador web.

Tanto si está creando informes financieros o administrando sus gastos personales, Excel 2010 le ofrece mayor flexibilidad y eficacia para cumplir sus objetivos.

Algunas de las nuevas características de Excel 2010 son:

La posibilidad de utilizar sparklines o gráficas en una sola celda, una buena manera de detectar tendencias en los datos.

Los slicers, de los que ya hemos hablado anteriormente. Finalmente, Microsoft les dio el nombre, en español, de "filtros de búsqueda".

Las tablas dinámicas son más fáciles de usar y tienen mejor aspecto. Existe un complemento de PowerPoint que, supuestamente, otorga una apariencia tipo dashboard a las tablas.

Publicar, visualizar y editar nuestras hojas desde cualquier lugar conectado a la web o incluso desde un smartphone, gracias a las aplicaciones Excel Web App y Microsoft Excel Mobile 2010. Será posible también editar una hoja entre varios usurios on line.

Mejoras en los formatos condicionales, con nuevos iconos y diseños.

La posibilidad de crear fichas personalizadas.

Una nueva vista, llamada Backstage, que reemplaza al tradicional menú Archivo.

En general, la interfaz es más ligera y más rápida que el malogrado Excel 2007. Parece que Microsoft intenta reivindicar a MS Excel como la hoja de cálculo líder del mercado. Démosle una oportunidad y probemos esta versión beta.

Evitar resultados #¡DIV/0!

2009-10-26T18:03:00.013-06:00

Me pregunta una usuaria, a quien llamaremos Claudia Acosta, cómo hacer que Excel muestre un cero en lugar del código de error #¡DIV/0!

Siempre que intentamos realizar una división por cero, algo matemáticamente imposible, Excel nos alerta de esta situación con el resultado de error #¡DIV/0!

Claro que esto no se ve muy bien que digamos. Una forma de evitar este resultado y hacer que Excel nos devuelva otro resultado más "estético", (un cero), es valiéndonos de la función SI:

=SI(B2=0, 0, A2/B2)

Es decir, que si el divisor (B2 en este caso) es igual a cero, Excel debe devolver un cero. En caso contrario, devuelve la división A2/B2.

Existe otra manera, más corta, de evitar los resultados #¡DIV/0!, utilizando la fórmula:

=--SI(B2,A2/B2)

¿Cómo funciona? Debemos tomar en cuenta que para Excel, un cero equivale al valor lógico FALSO; mientras que cualquier otro valor equivale a un VERDADERO. Entonces, suponiendo que en A2 tengamos un 4 y en B2 un 2, la fórmula se convierte en:

=--SI(2, 4/2), lo que resulta en:
=--SI(VERDADERO,4/2)
=--4/2
=--2
=2

Pero, si en B2 tenemos un cero, entonces la fórmula resulta:

=--SI(0, 4/0)
=--SI(FALSO,4/0)

Cuando no establecemos el segundo argumento de SI (valor_si_falso), SI devuelve FALSO:

=--FALSO

Al aplicar un signo menos a este resultado, Excel lo convierte a su equivalente numérico, es decir, cero:

=0

Y para no cambiar de signo el resultado (en el primer caso), aplicamos un doble signo menos.

Slicers - Novedad en Excel 2010

2009-09-24T13:15:00.019-05:00

En la nota Primeras imágenes de Excel 2010, comentaba sobre una nueva característica en la barra Insertar, llamada Slicers, sobre la cual no tenía idea de su funcionamiento.

El día de hoy, el blog oficial de Microsoft Excel nos revela cual es la funcionalidad de estos slicers (¿cómo les llamaríamos en español?). Esta es mi traducción a la nota en cuestión, escrita por el Excel MVP Joseph Chirilov (las imágenes están en inglés):

Los slicers son una novedad para Excel 2010, que son controles visuales que permiten al usuario rápida y fácilmente filtrar sus datos de forma interactiva. "Flotan" sobre la hoja, como las gráficas y autoformas.

Este es un ejemplo del mismo slicer en tres situaciones distintas. El usuario puede seleccionar uno, todos, o algunos de los países. Un slicer actúa como un filtro de reporte, así que se puede vincular a una tabla dinámica, una gráfica dinámica o a un cubo de datos para crear un reporte interactivo.

¿Por qué los slicers?

En esta primera versión, el equipo Excel quiso permitir al usuario:

Ver lo que está haciendo en todo momento
Interactuar fácilmente con los datos
Filtrar múltiples tablas o graficos dinámicos o consultas juntos
Crear mejores tableros de datos

Detallemos un poco más cada uno de estos objetivos:

Ver lo que se está haciendo en todo momento

En este ejemplo, quiero analizar la utilidad generada por clientes con uno o más hijos y con una escolaridad de bachillerato o superior. En Excel 2007, puedo agregar los campos de página "Numero de hijos" y "Educación" a mi tabla dinámica y seleccionar los elementos correspondientes. Sin embargo, el campo de página mostrará "múltiples elementos", lo que no es de gran ayuda.

En Excel 2010, hemos agregado slicers a la caja de herramientas de forma que el usuario pueda ver lo que está haciendo en todo momento. Ahora queda completamente claro qué datos está mostrando el reporte en cada momento.

Interactuar fácilmente con los datos

Filtrar datos en Excel 2007 era bastante tedioso. Primero, había que dar clic en la lista desplegable o en el icono Filtro, luego expandir los elementos necesarios, seleccionar / deseleccionar los elementos que queramos y finalmente dar clic en Aceptar.

En contraste, seleccionar un elemento de un slicer es fácil. Sólo requiere un clic. Para seleccionar múltiples elementos, se pueden usar combinaciones de Ctrl + clic, Shift + clic o clic + arrastrar. Esta forma de seleccionar elementos es la misma con la que el usuario está familiarizado al seleccionar celdas, elementos de lista o iconos en Windows. Los slicers lucen como controles en lugar de celdas, induciendo al usuario a utilizarlos. Utilizar slicers para crear reportes maximiza la interactividad, permitiendo al usuario y a sus colaboradores pasar menos tiempo escudriñando y más tiempo analizando.

Filtrar múltiples tablas dinámicas, gráficas dinámicas o consultas juntos

Una de las limitaciones de los filtros de los reportes es solo permiten una relación 1:1 con la tabla dinámica que están filtrando. Si el usuario necesitaba aplicar un filtro a mútiples tablas dinámicas, tenía que recrear el filtro en cada tabla. Ahora, el usuario puede vincular un slicer a varias tablas, gráficos dinámicos y/o consultas de datos. Todo lo que se haga en el slicer se aplicará convenientemente a todo lo que este tenga conectado. Más sobre esto en la siguiente nota.

Crear mejores tableros de datos (dashboards)

Los slicers pueden ser personalizados de varias maneras, y pueden crearse ahora tableros que antes no eran posibles. En una futura nota, profundizaré más en las formas en que se puede mejorar el aspecto visual de los slicers. Por ahora, aquí está un ejemplo del tipo de reportes que se pueden lograr con los slicers:

Y una cosa más: los slicers son compatibles con Excel Services 2010 y Excel Web App, así que podemos utilizarlos y compartirlos en la web.

Excel 2010 - Logo

2009-09-09T12:02:00.008-05:00

Microsoft acaba de dar a conocer los nuevos iconos de los programas de la suite Office 2010. Este es el de Excel 2010:

Otros iconos, el de Word:

Power Point:

Outlook:

y Access:

Fuente.

Insertar 'bullets' rápidamente

2009-09-04T16:25:00.009-05:00

Hay ocasiones en las que deseamos preceder las entradas de una lista con viñetas (o bullets, en inglés), como en este ejemplo:

Normalmente insertaríamos estas viñetas yendo a Insertar - Símbolo...

Localizamos el símbolo correspondiente y clic en Insertar.

Una forma más rápida, es (para esta viñeta en específico) escribiendo la combinación de teclas Alt + 7, pero del teclado numérico, ya que con el 7 del teclado alfabético no funciona. Con esto aparecerá la viñeta en la celda faltando solamente escribir el valor de la celda.

Esta es la tabla completa de símbolos que se pueden utilizar con Alt + [núm. de teclado numérico]:

Observemos que tanto Alt + 0 como Alt + 47 no devuelven ningún símbolo, en tanto Alt + 32 devuelve un espacio en blanco. A partir de Alt + 48 comienzan los números y letras "normales".

Aparentemente, esta no es una forma muy práctica que digamos para obtener este formato, ya que requiere que cada entrada de celda esté precedida, manualmente, de Alt + 7; además de que esto convertirá en texto nuestros valores numéricos. Sin embargo, también es posible utilizar esta combinación de teclas a la hora de crear un formato de celda personalizado. Si vamos al cuadro de diálogo Formato de celdas (Formato - Celdas... ó Ctrl + 1), categoría Personalizada, podemos teclear Alt + 7 (o cualquier otro número de la tabla anterior), en el cuadro de texto Tipo, seguido del resto del formato deseado. Por ejemplo: [Alt + 7]#,##0 (hay que soltar la tecla Alt antes de escribir "#,##0"). Si lo hacemos correctamente, Excel mostrará el resultado previo en la ventana Muestra:

Por último, clic en Aceptar y copiamos el formato al resto de las celdas de interés. De esta manera, ya no es necesario escribir Alt + [número] al ingresar cada valor, sino solamente el valor (o la fórmula) dejando a Excel el resto del trabajo.

Trabajar con usuarios avanzados

2009-08-11T17:20:00.015-05:00

Como dice Jorge Camoes en su blog, "perder estúpidamente el tiempo en el trabajo me molesta realmente".

Cuando se trabaja en una compañía en la que un alto porcentaje de gente usa Excel como su principal herramienta de trabajo, inevitablemente surgen historias de terror de gente que gasta días enteros en tareas que debieron durar minutos. Por ejemplo:

Miles de sumas, calculadas una a una, cuando una tabla dinámica pudo dar los mismos resultados en segundos.
Dos personas comparando dos listas, cuando pueden utilizar una simple función de búsqueda para hacerlo.
Miles de copias de exactamente la misma gráfica, pero con diferentes mercados, territorios de venta o medidas (una sola gráfica con algo de interactividad básica serviría igual).
Cientos de celdas coloreadas ¡manualmente!, simulando un espantoso confetti electrónico, y de las cuales luego ni el mismo autor de la hoja recuerda qué significa cada color.

Todo esto es, además, propenso a una alta cantidad de errores. Es frustrante ver a la gente perdiendo tiempo de esta forma, cuando fácilmente podrían salir temprano de su trabajo y jugar con sus hijos (o las amigas de sus hijos...). Y es fácil... todo lo que hay que hacer es decirse a uno mismo: "esto es estúpido; debe haber alguna forma más rápida de hacerlo". En Excel, debemos tener intuición, asumir que debe haber una forma más fácil de hacer las cosas. Y generalmente, la hay. Lo fácil es aprender; lo difícil, saber que necesitamos aprender.

Si el lector es gerente, le compartiré un "secreto de Excel": No te imaginas lo ineficiente que puede llegar a ser un usuario principiante de Excel, y lo más probable es que tengas a varios eternos principiantes en tu equipo. No es cuestión de capacitación. La gente olvida la teoría y los ejemplos que no se aplican a sus propios problemas. Mejor contrata a un usuario de nivel avanzado. Conviértelo en la referencia del equipo (o de la empresa) para las cuestiones relativas a Excel. Haz que se integre con los usuarios, que encuentre ineficiencias y que las resuelva. En lo personal, siempre he pensado que toda empresa que se considere eficiente debe contar con especialistas en Excel, dedicados exclusivamente al desarrollo de modelos y a la asesoría y capacitación del personal.

Aunque no lo parezca, Excel por sí solo puede incrementar la eficiencia de un equipo de trabajo -y hasta de toda un área.

Día especial

2009-07-06T12:59:00.003-05:00

El día de hoy es el día 40,000 Excel. Y no porque Excel tenga 40,000 días en el mercado, sino porque este es el número serial que corresponde al 6 de julio de 2009. Recordemos que el sistema de fechas en Excel comienza el 1 de enero de 1900. Han pasado 40,000 días desde esa fecha.

Para comprobarlo, escribimos la fecha de hoy (Ctrl + Shift + ;) en una celda y posteriormente, aplicamos formato de número a la celda (Ctrl + Shift + 1).

Resultado inesperado - resolviendo el misterio

2009-06-22T09:22:00.003-05:00

En relación a la nota Resultado inesperado, en la que comentaba algunos resultados aparentemente incomprensibles que Excel me devolvía, ckarlanga me hizo llegar su comentario y análisis de la situación, al que decidí dedicarle su nota propia dada su relevancia. Comenta ckarlanga:

"Hola, me hago llamar ckarlanga, y navegando por los foros di con esta info que me pareció bastante curiosa y sobre todo que alguien la mencionara, siendo un curioso del excel, estuve tratando de abrir la funcion de distribución normal, para ello intenté dar con el complemento que en excel lo contenía y me di la lata de abrir uno por uno los complementos, y por ahí di con el paquete que contiene atpvbes.xla atpvben.xla analisys32.xll funcres.xla y procdb.xla, les saque las claves a estos archivos y en los ATP tanto español como inglés, se encuentra la función networkdays y dias.lab, éste es el código:

Function Networkdays(start_date As Variant, end_date As Variant, Optional holidays As Variant) As Variant
Networkdays = Application.Run(GetMacroRegId("NETWORKDAYS"), start_date, end_date, holidays)

End Function

Cuando uno ejecuta esa function desde el excel va a esta función que ejecuta una función base para todas las creadas dentro de este paquete llamada GetMacroRegId. Esta a su vez, si logras convertir el archivo xla a xls, tiene una descripción de cada una de las funciones hechas dentro del xla:

Private Function GetMacroRegId(FuncText As String) As String
Debug.Print ("[GetMacroRegId] '" & FuncText & "' <") For i = LBound(FunctionIDs) To UBound(FunctionIDs)
If (LCase(FunctionIDs(i, 0)) = LCase(FuncText)) Then
If (Not (IsError(FunctionIDs(i, 1)))) Then
GetMacroRegId = FunctionIDs(i, 1)
Debug.Print ("[GetMacroRegId] '" & FuncText & "' -> '" & GetMacroRegId & "' >")
Exit Function
End If End If Next i
Debug.Print ("[GetMacroRegId] Error while finding '" & FuncText & "' >")
End Function

dentro del xla covertido a xls tienes esto ...

Function table
Labels VBAname Module Procedure Type text Function text Argument text Macro type Category Shortcut text Help topic Help text Arg1 Arg2 Arg3
HEX2DEC Hex2Dec hex2dec PP# HEX.A.DEC número 1 Ingeniería xlmain10.chm!1929

Convierte un número hexadecimal en decimal es el número hexadecimal que desea convertir

(encabezados)

y estos son los datos

NETWORKDAYS Networkdays networkdays PPPP# DIAS.LAB fecha_inicial;fecha_final;festivos 1 Fecha y hora xlmain10.chm!1963 Devuelve el número total de días laborables entre dos fechas es un número de serie que representa la fecha inicial es un número de serie que representa la fecha final.

El código al que haces referencia, 840368184, es el id de la macro que tiene adentro la fórmula de cálculo de los días hábiles, apenas tenga una respuesta específica dentro de que archivo se encuentra ( lo mas probable un dll) te lo informo...

un saludo...

perdona lo enredado de la respuestas... pero a esta hora no pienso más..."

Con esto comenzamos a resolver el misterio. Intentaré reproducir estos pasos en mi equipo para comparar resultados. Te agradezco esta colaboración, ckarlanga. Así mismo, te extiendo una cordial invitación a postear notas en este blog, las cuales, desde luego, llevarían tu firma. Si te gusta la idea, házmelo saber para otorgarte permisos de escritura.

Formatear el eje y

2009-05-27T17:44:00.008-05:00

Supongamos el siguiente gráfico:

Queremos que para facilitar la lectura del mismo, tengamos tres colores en la escala del eje y: el verde para los valores mayores o iguales a 30, negro para los resultados entre -10 y 30 y rojo para los menores de -10, quedando de esta manera:

Para lograrlo seguimos los siguientes pasos:

Clic derecho en el eje y - Formato de ejes..., activamos la ficha Número. Seleccionamos la Categoría Personalizado. En Tipo: escribimos el siguiente formato personalizado:

[verde][>=30]#,##0;[Rojo][<-10]-#,##0;Estándar

Aceptar. Recordemos, sin embargo, lo que vimos en la nota pasada: nuestro principal objetivo es contar una historia con la gráfica, más que impactar visualmente.

Una buena gráfica

2009-05-25T12:34:00.015-05:00

Como lo dice Chandoo en su blog, "una gráfica bién hecha nos cuenta una historia. Tan simple como eso."

Este es un ejemplo de lo que es una buena gráfica, "Tiempo empleado para comer por día vs tasa nacional de obesidad" (clic en la imagen para agrandarla):

x: minutos utilizados para comer por día
y: porcentaje de población con IMC > 30

Se toma unos cinco segundos en entender de qué trata la gráfica. Y luego, conoces la historia. Lo más importante, estimula al lector a hacerse preguntas y entender los datos.

En sí misma, la gráfica es sencilla. Nada del otro mundo y, sobretodo, nada extravagante. Pero combina brillantemente dos tipos de datos: la tasa de obesidad en los países y el tiempo utilizado para comer, para contar una historia.

El artículo demuestra cabalmente uno de los fundamentos de Excel: el principal objetivo de una gráfica no es impactar visualmente, sino transmitir información clara rápidamente.

La gráfica de nuestro ejemplo nos "cuenta" algo mucho más rápidamente que esta otra, que aunque más visual, es mucho más confusa:

Si una gráfica Excel no comunica información más rápidamente que una tabla de datos, podemos decir, con toda justicia, que es una mala gráfica. En ese caso, lo mejor es prescindir de ella y mostrar en su lugar solo los datos fuente.

Controlar el ingreso de datos en un formulario

2009-05-21T16:05:00.007-05:00

Cuando trabajamos en un formulario Excel, a menudo sucede que las celdas de ingreso de datos están dispersas por toda la hoja, o no están en celdas adyacentes. Esto nos hace perder tiempo al tener que situar el cursor en la celda correspondiente cada vez:

Supongamos que necesitamos ingresar los datos en el orden mostrado en la imagen cada vez. Una forma de solucionar esto es creando un nombre que contenga las celdas de ingreso de datos. Procedemos como sigue:

Seleccionamos las celdas de entrada de datos en el orden que queramos llenarlas, pero comenzando por la número 2. Lo hacemos así para que la última celda seleccionada (que es la primera en el orden) sea la celda activa del nombre. Entonces, según el ejemplo, presionamos Ctrl y, en este orden, las celdas 2, 3, 4, 5, 6, 7, 8, 1.

Finalmente, le damos un nombre a este rango, digamos input_data.

De esta manera, cada vez que vayamos a ingresar datos en el formulario, simplemente vamos al nombre input_data (con F5 o seleccionándolo de la lista de nombres)

y comenzamos a escribir los datos presionando Enter para avanzar a la siguiente celda cada vez.

Primeras imágenes de Excel 2010

2009-05-20T11:53:00.007-05:00

Microsoft ha decidido filtrar algunas imágenes de Office 2010. Curiosamente, le ha pedido a algunos sitios que las publicaron, que las retiraran inmediatamente. Ars Technica es uno de ellos. Pero mientras a mí no me digan nada, comparto con ustedes las correspondientes a Excel 2010.

En esta primera imagen podemos ver que el enorme botón de Office ha sido reemplazado por un rectángulo situado a la izquierda de la Pestaña Home (Inicio):

Otra de las imagenes filtradas muestra que todas las pestañas permanecen idénticas a las de 2007, a excepción de la de Insertar.

Los cambios observables son:

Hay un nuevo comando, Screenshot. Supongo que será similar a la cámara fotográfica actual. O bien, para salvar todo o parte de la hoja como imagen.

Un nuevo grupo de gráficas, Sparklines, o minigráficas. Excel 2010 soportará de forma predeterminada este tipo de gráfica.

Un nuevo comando, al final de la barra llamado Slicer. No tengo idea de su función.

El filtro avanzado - Introducción

2009-05-18T00:03:00.007-05:00

La mayor parte de las veces el autofiltro satisface nuestras necesidades de filtrado. No obstante, tiene sus limitaciones. Una usauria me pregunta como filtrar una lista de forma que el campo filtrado muestre cuatro valores (criterios).

Este un ejemplo sencillo de filtrado que el autofiltro no puede realizar. Recordemos que en el cuadro Autofiltro Personalizado solo podemos establecer dos valores como criterio:

En estos casos, tenemos la opción de usar el filtro avanzado de Excel, mucho más flexible que aquel, aunque requiere de un poco más de trabajo. Antes de comenzar a utilizarlo, necesitamos elaborar una tabla de criterios. Esta tabla contiene la información que Excel utilizará para filtrar la lista. Consiste en al menos dos filas, la primera de las cuales contiene uno o más nombres de campo de la lista (excepto cuando utilizamos criterios calculados, tema de otra nota). Las filas restantes contienen los criterios a utilizar. Puede estar en cualquier parte de la hoja, siendo recomendable situarla en la parte superior de la lista, para que esté visible en todo momento (si la ponemos al lado de la lista, al filtrar, posiblemente se ocultarán algunas de sus filas).

La siguiente lista muestra una lista de los edificios conectados de una importantísima empresa de telecomunicaciones (clic en la imagen para agrandarla).

La columna E contiene la clave asociada a cada edificio. El requerimiento es mostrar únicamente aquéllos edificios que tengan clave A, B, C o D. Insertamos unas 6 filas para escribir nuestro rango de criterios, como sigue:

En este ejemplo, simplemente escribimos los valores buscados uno a continuación del otro, en el rango A1:A5. Estos deben tener exactamente el mismo encabezado que tienen en la lista original. Tengamos en cuenta que los criterios escritos en una misma fila Excel los une con el operador Y, mientras que los que están en otra fila los une con el operador O.

Nos situamos en cualquier celda de la lista y vamos a Datos - Filtro - Filtro avanzado... nos aseguramos que el valor de Rango de lista: sea el correcto. En Rango de criterios, escribimos el rango de la tabla de criterios, en nuestro caso, A1:A5. Si queremos que los registros filtrados se muestren en la misma ubicación original, damos Aceptar. Por el contrario, si queremos que la lista resultante esté en otro rango independiente, activamos la opción Copiar a otro lugar, y en Copiar a: escribimos la celda donde queramos que comience la lista. Aceptamos el cuadro.

Hemos visto un ejemplo muy sencillo que ejemplifica el uso del filtro avanzado. Sin embargo, la flexibilidad y potencia del mismo va mucho más allá. Podemos, por poner un ejemplo, filtrar la lista mostrando aquellos campos cuyos valores son superiores al promedio de los mismos, utilizando criterios formulados. Este será tema de otra nota.

Biohazard

2009-05-02T02:12:00.003-05:00

Tipos de gráficos personalizados

2009-04-20T08:17:00.005-05:00

Prácticamente todo mundo está de acuerdo en que el formato que los gráficos de Excel tienen de manera predeterminada, es punto menos que horrible.

Luce mal, los colores no combinan, el fondo del gráfico "cansa" la vista, las líneas de división sobrecargan el gráfico... en fin, siempre será necesario (si queremos comunicar información gráfica efectivamente) que mejoremos el formato predeterminado de estas gráficas. Excel nos brinda la oportunidad de hacerlo, así como de elaborar nuestra propia galería de tipos de gráfico, de manera que no tengamos que pasar por todo el proceso de "maquillaje" una y otra vez. Simplemente, seleccionaremos nuestros datos y les aplicamos alguno de nuestros tipos de gráfico personalizados.

Para lograrlo, procedemos como sigue:

Creamos una gráfica estándar, aplicándole los formatos y personalizaciones que más nos gusten. Recomiendo utilizar una muestra típica de los datos que estaremos graficando.

Una vez que el gráfico luzca como deseamos, lo agregamos a la galería de gráficos personalizada, siguiendo los siguientes pasos:

Teniendo seleccionado el gráfico, vamos a Gráfico - Tipo de gráfico..., pestaña Tipos personalizados.
En la sección Seleccionar desde, activamos la opción Definidos por el usuario.
Clic en Agregar...

En el cuadro Agregar tipo de gráfico personalizado, escribimos un nombre y, opcionalmente, una descripción de nuestro gráfico.

Clic en Aceptar. Con esto agregamos el gráfico a la galería. Aceptar nuevamente para cerrar el cuadro de diálogo.

Finalmente, probamos el gráfico. Seleccionamos una nueva muestra de datos, e iniciamos el Asistente para gráficos. En el paso 2, en Seleccionar el tipo de gráfico, escogemos el nuevo tipo de gráfico, bajo la pestaña Tipos personalizados, opción Definidos por el usuario (¿personalizados definidos por el usuario? Redundante pero cierto. Gracias, Bill). Clic en finalizar.

Sobre los comentarios

2009-04-16T08:21:00.000-05:00

Esta vez, solo para informarles que desde hace un par de semanas he eliminado el paso de verificación de palabra al enviar comentarios. En otras palabras, cuando envíen sus comentarios, ya no verán cuadros como estos:

Sino que directamente llegarán sus comentarios a mi buzón, haciéndoles, así lo espero, más fácil el envío de los mismos.

Reglas de ordenación

2009-04-15T08:32:00.009-05:00

Las celdas pueden contener diferentes tipos de contenido. Pueden contener texto, números, valores lógicos, resultados de error, o bien, pueden estar vacías o contener espacios en blanco (que no es lo mismo).

Si tenemos solamente valores numéricos, Excel los ordenará (Edición - Ordenar... ) del menor negativo al mayor positivo (para un orden ascendente). Pero, ¿qué pasa si queremos ordenar texto, o valores lógicos? ¿que pasa si tenemos varios tipos de información en las celdas?

En una ordenación ascendente, Excel presenta la información en el siguiente orden:

Valores numéricos, siguiendo el orden indicado anteriormente. Las horas y fechas son tratadas como números. Se toma en cuenta el valor intrínseco de la celda, no su valor formateado.
Texto, en orden alfabético, como sigue: 0 1 2 3 4 5 6 7 8 9 (espacio) ' - ! # $ % ( ) * , . / : ; ? @ [ \ ] ^ _ { | } ~ ¡ ´ ¿ + < = > ¬ ° A B C D E F G H I J K L M Ñ O P Q R S T U V W X Y Z.
Valores lógicos: primero FALSO, luego VERDADERO.
Valores de error (#¡NUM!, #N/A, etc.), apareciendo en su orden original, sin ordenarse por tipo de error.
Celdas vacías, siempre al final.

De manera predeterminada, la ordenación no es sensible a mayúsculas / minúsculas. Podemos cambiar esto dando clic en Opciones... del cuadro Ordenar:

Siguiendo un orden descendente, la secuencia queda a la inversa, excepto por las celdas vacías, que volverán a quedar al final de la lista. En la version en inglés, el texto sigue un orden ligeramente diferente.

Este blog cada día está más nerd.

Link.

Ingreso manual de referencias - Tip

2009-04-13T09:08:00.005-05:00

En el blog Daily Dose of Excel, en la nota Illegal Range Names (nombres de rango inválidos), encontramos lo siguiente:

Podemos utilizar puntos al definir nombres, por ejemplo, mi.rango. Sin embargo, si el nombre que utilizamos, además de incluir un punto, parece una referencia de celda en la notación F1C1, por ejemplo, cu50.s1, Excel la evaluará como si fuera una referencia verdadera (en este caso, S1:CU50), y por lo tanto, nos avisará que es un nombre inválido.

En la misma nota, Dick Kusleika nos provee este tip: al introducir manualmente una referencia de celda, podemos utilizar el punto (.) en lugar de los dos puntos (:). Después, al presionar Enter, Excel lo convertirá a ":".

Si escribimos "=SUMA(A2.A120)", al presionar Enter veremos en la barra de fórmulas:

=SUMA(A2:A120)

Seleccionar todos los objetos de una hoja

2009-04-08T09:00:00.005-05:00

En Yahoo respuestas, encontré la siguiente pregunta:

¿Como selecciono en Excel miles de lineas "auto-formas" de una sola?

Necesito modificarlas a todas. Conozco una manera de seleccionar varias con la tecla Ctrl y clickeando en cada una, pero aun así es muy laborioso si son miles de ellas.
Agradezco muchísimo de antemano a quien pueda ayudarme.

La solución es:

Edición - Ir a... - Especial... - Objetos.

Con el teclado:

F5, Alt+e, j, Aceptar.

Cabe aclarar que con esto seleccionaremos todos los objetos, es decir, incluyendo gráficos e imagenes, no solamente las autoformas. Tengámoslo en cuenta.

Habría que revisar también porqué el usuario tiene estos miles de líneas en su hoja. Aunque es posible realizar dibujos de aceptable calidad en Excel, no es la mejor herramienta para hacerlo.

Contar subcadenas en cadena

2009-04-06T10:00:00.006-05:00

En mundoexcel.com, uno de los foros que frecuento, un usuario envió esta consulta:

Hola tengo una gran pequeña duda y por mas que leo y busco no encuentro la solucion: quiero contar cuantas veces esta escrita una letra en una celda.

Ejemplo:

Palabra " Antonio"

Mi pregunta es cuantas letras "O" tiene esa palabra.

La solución que propuse fue utilizar una fórmula matricial, como sigue:

Supongamos que en A2 está la palabra "Antonio", en B2 la letra a contar "o".

En C2 escribe la siguiente fórmula matricial:

=SUMA(--(EXTRAE(A2,FILA(INDIRECTO("1:"&LARGO(A2))),1)=B2))

Recuerda aceptarla con Ctrl + Shift + Enter.

LARGO(A2) devuelve el número de caracteres de Antonio, 7.

INDIRECTO devuelve el string "1:7", que sirve de argumento a FILA.

FILA(1:7) genera una lista de consecutivos de 1 a 7. En otras palabras, genera la constante matricial {1; 2; 3; 4; 5; 6; 7}.

EXTRAE devuelve cada uno de los caracteres 1o. a 7o. La constante matricial cambia a: {"A"; "n"; "t"; "o"; "n"; "i"; "o;"}.

Se compara cada uno con B2, "o". Si son iguales, se genera un VERDADERO. Si no son iguales, se genera un FALSO: {FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO; VERDADERO}.

Al agregar -- Excel convierte los VERDADEROS en unos, y los FALSOS en ceros: {0; 0; 0; 1; 0; 0; 1}.

Finalmente se suman estos unos y ceros, resultando 2.

PHD entrevista a John Walkenbach

2009-03-30T14:01:00.003-06:00

Chandoo ha publicado la entrevista que realizó a John Walkenbach. Para quienes deseen leerla (en inglés), clic aquí.

Debo decir que quedé un poco decepcionado. Esperaba más de esta entrevista, viniendo de mr. Spreadsheet. Al leerla me pareció que Mr. Walkenbach solo intentaba vendernos alguno de sus libros, más que enseñarnos algo. Indudablemente, la de Charley Kyd fue una entrevista mucho más didáctica y amena.

Advanced Excel Conference

2009-03-27T13:43:00.007-06:00

Jon Peltier ha anunciado en su sitio, la primera edición de la Excel User Conference, que se llevará a cabo los días 17 y 18 de junio, en Atlanta, Georgia (EU). Contará con la participación del mismo Jon:

y de Bob Umlas, otro Excel MVP (algunos llaman a sus fórmulas forUmlas):

Cada uno de ellos dará un par de clases acerca de elaboración de gráficos y programación en Excel. Esta conferencia entra en sustitución de la Excel User Conference, que se estuvo realizando en años previos. Lamentablemente, los organizadores no pudieron organizar la edición de este año. Afortunadamente, Jon tomó la iniciativa y organizó su propia conferencia. Así pues, a ahorrar se ha dicho. La oportunidad de escuchar y dialogar con dos Excel MVP's simultáneamente no se da todos los días.

La función SIFECHA

2009-03-23T18:00:00.010-06:00

La función SIFECHA es un caso aparte. No por su simpleza o complejidad. Simplemente, porque desde Excel 5.0 no está documentada. Es una función secreta, por decirlo de algún modo. En efecto, si buscamos la función en el listado de funciones de Excel, veremos que no aparece, aún si como categoría seleccionamos Todas. Tampoco aparece en la ayuda on-line de Excel. Supuestamente (y esto es solo un mito urbano) Microsoft mantiene oculta esta función porque planea desaparecerla, ya que es una función originaria de Lotus. Por si fuera poco, está mal traducida, ya que su nombre original, en inglés, es DATEDIF (date diference), no DATEIF.

SIFECHA devuelve la diferencia entre dos fechas, expresada en determinado intervalo. La sintaxis:

=SIFECHA(fecha_1, fecha_2, intervalo)

fecha_1 y fecha_2 deben ser fechas válidas, de otra forma, obtendremos un error #¡VALOR!. fecha_1 debe ser menor (más antigua) que fecha_2. Si no es así, se obtiene #¡NUM! El argumento intervalo especifica la unidad de medida en la que Excel devolverá el resultado. Puede ser uno de los siguientes valores:

"m" - meses. Número de meses completos entre fecha_1 y fecha_2.

"d" - días. Número de días entre fecha_1 y fecha_2.

"y" - años. Número de años completos entre fecha_1 y fecha_2.

"ym" - meses excluyendo años. Número de meses entre fecha_1 y fecha_2, suponiendo que fecha_1 y fecha_2 son del mismo año.

"yd" - días excluyendo años. Número de días entre fecha_2 y fecha_2, suponiendo que fecha_1 y fecha_2 son del mismo año.

"md" - días excluyendo meses y años. Número de días entre fecha_2 y fecha_2, suponiendo que fecha_1 y fecha_2 son del mismo mes y del mismo año.

Supongamos que queremos calcular la diferencia entre las fechas 01/03/2007 (uno de marzo de 2007) y la actual 24/03/2009. El resultado de SIFECHA variará según el intervalo especificado, como sigue:

Nótese que si el argumento intervalo es una referencia, no debe estar escrito entre comillas.

Para calcular la edad de una persona podemos usar una fórmula como la siguiente:

=SIFECHA(A1,HOY(), "y") & " años, " & SIFECHA(A1,HOY(),"ym") & " meses, " & SIFECHA(A1, HOY(), "md") & " días."

Suponiendo que la fecha de nacimiento del individuo estudiado está en A1.

Utilizando VBA, SIFECHA no puede ser llamada utilizando WorksheetFunction. Es preciso elaborar manualmente el código que haga los cálculos, por ejemplo:

Function Age(fecha1 As Date, fecha2 As Date) As String

Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date

Temp1 = DateSerial(Year(fecha2), Month(fecha1), Day(fecha1))
Y = Year(fecha2) - Year(fecha1) + (Temp1 > fecha2)
M = Month(fecha2) - Month(fecha1) - (12 * (Temp1 > fecha2))
D = Day(fecha2) - Day(fecha1)

If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(fecha2), Month(fecha2) + 1, 0)) + D + 1
End If

Age = Y & " años " & M & " meses " & D & " dias"

End Function

It´s friday

2009-03-20T09:00:00.000-06:00

Del sitio Comics I don't understand:

El jefe no dejará que nadie abandone está reunión hasta que alguien explique cómo es que se rompió la ventana.

En el blog de Jon Peltier:

-Nuestra relación comenzó a declinar en este punto.
-Fue ahí cuando comenzaste a graficarlo todo.
-¡Coincidencia!

Ocultar hojas efectivamente

2009-03-13T10:00:00.005-06:00

Algunos de nuestros libros contienen hojas que preferimos ocultar, ya sea porque son hojas con cálculos parciales, o porque contienen información confidencial. O porque simple y sencillamente, no queremos que otros las vean. Normalmente, las ocultamos con Formato - Hoja - Ocultar.

Desde luego, el ocultar hojas de este modo no evita de ninguna manera que el usuario husmée en ellas e incluso modifique nuestras fórmulas, llegando a descomponer completamente el libro. Mucho menos sirve para ocultar información de carácter confidencial. Todo lo que se tiene que hacer es ir a Formato - Hoja - Mostrar... y seleccionar la hoja que se quiera ver.

Para estos casos, tenemos la posibilidad de ocultar una hoja de forma que ni siquiera aparezca en el cuadro Mostrar hoja... utilizando el editor de Visual Basic (VBE).

Procedemos como sigue:

Ingresamos al Editor de VB (Alt + F11) y localizamos nuestro libro en el Explorador de proyectos (precedido con la palabra "VBAProject").

Las hojas del libro están dentro de la subcarpeta Microsoft Excel Objetos. En caso de que no se vean, las mostramos dando clic en el botón + de dicha subcarpeta. Seleccionamos la hoja que queremos ocultar.

En la ventana Propiedades buscamos la propiedad Visible y establecemos su correspondiente valor a 2 - xlSheetVeryHidden (si no se muestra la referida ventana, la mostramos con Ver- Ventana propiedades).

Con este valor en la propiedad Visible, la hoja estará oculta y no aparecerá en el cuadro Mostrar hoja...

Ahora bién, existe la posibilidad de que el usuario conozca este "truco" y reestablezca la propiedad Visible a -1 - xlSheetVisible (hoja visible) siguiendo los mismos pasos. Para evitar esto es necesario proteger el proyecto con una contraseña. Vamos a Herramientas - Propiedades de VBAProject..., ficha Protección:

Activamos la casilla Bloquear proyecto para visualización, escribimos alguna contraseña y aceptamos el cuadro. Con esto, si el usuario intenta mostrar el editor de Visual Basic, Excel le requerirá escribir la contraseña para abrirlo.

Finalmente cerramos el editor VB y guardamos el archivo. De regreso en Excel, damos Formato - Hoja - Mostrar...:

y comprobamos que la hoja que recién ocultamos no aparece en este cuadro. Más aún, si solo hemos ocultado una hoja, ni siquiera estará habilitado el comando Mostrar...

Pegado especial valores - Revisited

2009-03-10T17:00:00.008-06:00

Copiar un rango de celdas y pegar únicamente los valores de las mismas es una operación un tanto frecuente.

Normalmente, lo hacemos copiando el rango de celdas, seleccionando la celda de destino y dando clic derecho - Pegado especial... Valores:

Otra opción es copiar el rango, clic en la lista desplegable del botón pegar, Valores:

La forma más rápida, para mi gusto, es la siguiente:

Seleccionamos el rango a copiar, y luego lo arrastramos a su nueva ubicación con el ratón, pero utilizando el botón derecho del mismo. Al soltar este botón, aparecerá un menú desplegable. Damos clic en la opción Pegar aquí solo como valores.

Si queremos convertir un rango a valores (es decir, pegar como valores pero en la misma ubicación), arrastramos el rango (con clic derecho) una columna a la derecha, regresamos a la posición original, soltamos y seleccionamos Pegar aquí solo como valores.

Para los que saben inglés, pueden ver la explicación de esta técnica en el siguiente video:

La función utilizada en el video, RAND, es la función ALEATORIO en español.

Sobre el chat de este blog

2009-03-09T10:00:00.004-06:00

Algunos lectores ya se habrán dado cuenta de que dispongo de la sección Excel chat, en la parte inferior del margen derecho.

El objetivo de este chat es poder dar respuestas puntuales a dudas que tengan los lectores. Desafortunadamente, las respuestas pueden no ser tan rápidas como quisiera, ya que me es imposible darle seguimiento las 24 horas del día. Además, no recibo ninguna clase de alerta cuando se produce un mensaje. Tengo que estar revisando periódicamente el sitio para verificar. Así pues, pueden pasar varias horas antes de que envíe alguna respuesta. Me ayudarían bastante enviando preguntas lo más concisas y puntuales posible.

Si la ayuda requerida es breve o puntual, y puede ser contestada a través del chat, será gratuita. Por el contrario, si se requiere que revise sus archivos y trabaje directamente en los mismos, la ayuda será considerada una consultoría y tendrá un costo, el cual puede ir del "semi-gratuito" (que no es otra cosa que recomendar este blog) hasta los USD 50.00.

Ahora bién, si alguno de los lectores conoce la respuesta de alguna pregunta planteada en el chat, puede contestar. Cualquiera que conozca la respuesta exacta a alguna pregunta puede participar. Esto servirá además para reducir los tiempos de respuesta para quien pregunta.

Para finalizar, les comento que he agregado un botón de búsqueda aleatoria de notas, arriba a la derecha. Puede resultar de utilidad a los lectores nuevos para darse una idea del contenido del blog, o bién, a aquéllos que quieran aprender algo nuevo sobre Excel, pero no tengan claro sobre que tema. En otras palabras, para los partidiarios del aprender-por-aprender.

Obtener el nombre de una hoja

2009-03-05T10:40:00.012-06:00

Un usaurio pregunta: ¿Como puedo hacer que en Excel, una celda vincule el texto del titulo de la hoja?

Utilizando la siguiente fórmula:

=DERECHA(CELDA("nombrearchivo",A1), LARGO(CELDA("nombrearchivo", A1))- ENCONTRAR("]", CELDA("nombrearchivo", A1), 1))

CELDA("nombrearchivo",A1) devuelve la ubicación completa de la celda especificada en el segundo argumento (puede ser de otra hoja incluso), incluyendo la ruta completa del archivo y el nombre de la hoja. Por ejemplo:

C:\Documents and Settings\leonel.quezada\[milibro.xls]Mi hoja

Al final extraemos únicamente el nombre de la hoja utilizando DERECHA.

LARGO(CELDA("nombrearchivo", A1)) devuelve el número total de caracteres del resultado previo.

ENCONTRAR("]", CELDA("nombrearchivo", A1) ,1) devuelve la posición del caracter "]" en el resultado anterior.

Por tanto, LARGO(CELDA("nombrearchivo", A1))- ENCONTRAR("]", CELDA("nombrearchivo", A1), 1) devuelve el número de caracteres que deberá extraer la función DERECHA.

PD: Técnicamente, estamos hablando del "nombre" de la hoja, no del "título".

Excel 14, no en 2009

2009-03-04T10:00:00.000-06:00

Durante la reunión anual "Strategic Update" con analistas de Wall Street, Steve Balmer, CEO de Microsoft, he revelado que Microsoft Office 14 no será lanzado en este año (2009).

Los voceros de Microsoft han sido muy reservados a la hora de definir una fecha de lanzamiento para Office 14, a pesar de que en este año la anunciaron un par de veces. Hace apenas un mes, hubo información acerca del lanzamiento del nuevo Office para finales del 2009.

En realidad, ya se lanzó una versión beta que solo disfrutan algunos clientes selectos. No obstante, la versión comercial será hasta 2010 (por lo menos). Excel luce prácticamente igual a 2007.
Confiemos en que Microsoft se repondrá del tropiezo que significó Excel 2007.

Entrevista con John Walkenbach

2009-03-02T11:52:00.008-06:00

Como consecuencia de la excelente entrevista que Chandoo realizó a Charley Kid, en uno de los comentarios le sugerí realizar las mismas preguntas a otros Excel MVP's, como por ejemplo, a John Walkenbach, a lo que él me respondió que sería buena idea.

Pues bien, tengo el agrado de informarles que recientemente, Chandoo ha anunciado en su blog Pointy Hairy Dilbert que John, mr. Spreadsheet, aceptó realizar dicha entrevista. En la nota, nos pide a sus lectores sugerir algunas de las preguntas que le haría a mr. Spreadsheet. Mis sugerencias fueron:

¿Cuál es la mejor aplicación Excel comercial que has visto y porqué?
¿Cuál es la mejor aplicación Excel gratuita que has visto y porqué?

Espero que Chandoo incluya mis preguntas en su entrevista. De todos modos, si no lo hace, será una excelente entrevista. Muchos de nosotros consideramos a John nuestro maestro. Estaremos atentos a la publicación de la entrevista, Chandoo.

Validación de datos dependiente

2009-02-24T18:08:00.007-06:00

Como es sabido, la validación de datos nos permite establecer reglas que determinan lo que puede y lo que no puede ser ingresado en una celda. Podemos especificar un mensaje de entrada y un mensaje de error (y el tipo de este mensaje, es decir, de información, de advertencia o de límite).

Un usuario me envía la siguiente pregunta: "... lo que necesito hacer es que de acuerdo a la selección que hagan de un campo, en el siguiente solo me den las opciones referentes a ese campo y no todas,... un ejemplo es cuando abres una cuenta de correo y te piden tu país, le das "México" y en el siguiente campo te aparecen solo los estados de México..."

Desde luego podemos intentar con un par de controles y un código VBA más o menos sencillo aunque, en realidad, es posible lograrlo sin necesidad de utilizar macros. Al igual que sucede con el formato condicional, al utilizar criterios personalizados (formulados), la validación de datos se vuelve una herramienta muy potente.

El primer paso es organizar nuestros datos. En la primera columna ponemos los valores independientes y, a la derecha, los dependientes. Es necesario que la lista esté ordenada por la primera columna. En otra columna, pongamos la D, escribimos una lista de los elemento únicos de la primer columna:

Por comodidad definimos los siguientes nombres:

La celda A1 con el nombre "inicio"; la columna A con el nombre "independiente"; la columna B como "dependiente" y la lista de la columna D como "lista". Opcionalmente, en otra celda en blanco, escribimos "Seleccione un valor en la columna A" y la definimos con el nombre "mensaje_error"

En otra hoja, creamos un tabla sencilla para crear las listas de validación:

Seleccionamos el rango A2:A10 y vamos a Datos - Validación... Como valor Permitir seleccionamos Lista. En el campo Origen escribimos:

=SI(O(B2="",B2="Seleccione un Ramo"), lista, INDICE(independiente, COINCIDIR(B2, dependiente, 0)))

Esto sirve para que, si no hay ningún valor en la columna B, o bien, el valor en la columna B sea "Seleccione un ramo", en la lista de la columna A aparezcan todos los valores. Por el contrario, si ya tenemos un valor establecido en la columna B (la dependiente), en la lista de validación de la columna A solo aparecerá su correspondiente valor, y no todos.

Solo nos falta crear la validación de la columna B. Seleccionamos el rango B2:B10, vamos a Datos - Validación... en Permitir seleccionamos Lista y, en Origen, escribimos la fórmula:

=SI(A2="",mensaje_error,DESREF(inicio,COINCIDIR(A2,independiente,0)-1,1,
CONTAR.SI(independiente,A2),1))

Con esta fórmula, si el usuario pretende seleccionar un valor en la columna B, sin haber seleccionado primero el correspondiente valor de la columna A, la lista solo mostrará la opción "seleccione un valor en la columna A".

De otra forma, la lista mostrará los valores adecuados.

Convertir números a letras

2009-02-13T13:35:00.019-06:00

Algo que los usuarios preguntan con cierta frecuencia, es si Excel cuenta con alguna función que convierta un número (40), a su forma "verbal" o textual ("cuarenta"). Principalmente, para elaborar facturas.

Bien, la respuesta es no. Las única formas son utilizar una macro o descargar algún complemento que pueda hacerlo. Hay varios sitios que proveen dichas macros. La que utilizo es la siguiente:

Option Explicit

Dim cTexto As String 'Variable para las funciones

Public Function NumLetras(ByVal Numero As Double, ByVal Mayusculas As Integer) As String

Dim NumTmp As String
Dim c01 As Integer
Dim c02 As Integer
Dim pos As Integer
Dim dig As Integer
Dim cen As Integer
Dim dec As Integer
Dim uni As Integer
Dim letra1 As String
Dim letra2 As String
Dim letra3 As String
Dim Leyenda As String
Dim Leyenda1 As String
Dim TFNumero As String

If Numero < 0 Then numero = Abs(Numero)
numtmp =" Format(Numero,"00000000000000.00")
c01 = 1
pos = 1
tfnumero = ""
Do While C01 <=5
C02 = 1
Do While c02 <= 3 'Extrae un digito cada vez de izquierda a derecha
dig = Val(Mid(NumTmp, pos, 1))
Select Case c02
Case 1: cen = dig
Case 2: dec = dig
Case 3: uni = dig
End Select
c02 = c02 + 1
pos = pos + 1
Loop
letra3 = Centena(uni, dec, cen)
letra2 = Decena(uni, dec)
letra1 = Unidad(uni, dec)

Select Case c01
Case 1
If cen + dec + uni = 1 Then
Leyenda = "Billon "
ElseIf cen + dec + uni > 1 Then
Leyenda = "Billones "
End If
Case 2
If cen + dec + uni >= 1 And Val(Mid(NumTmp, 7, 3)) = 0 Then
Leyenda = "Mil Millones "
ElseIf cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 3
If cen + dec = 0 And uni = 1 Then
Leyenda = "Millon "
ElseIf cen > 0 Or dec > 0 Or uni > 1 Then
Leyenda = "Millones "
End If
Case 4
If cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 5
If cen + dec + uni >= 1 Then
Leyenda = ""
End If
End Select

c01 = c01 + 1

TFNumero = TFNumero + letra3 + letra2 + letra1 + Leyenda

Leyenda = ""
letra1 = ""
letra2 = ""
letra3 = ""

Loop

If Val(NumTmp) = 0 Or Val(NumTmp) <1 Then
leyenda1 = "Cero Pesos "
ElseIf Val(NumTmp) = 1 Or Val(NumTmp) <2 Then
leyenda1 = "Peso "
ElseIf Val(NumTmp) = 0 Or Val(Mid(NumTmp, 10, 6)) = 0 Then
Leyenda1 = "de Pesos "
Else
Leyenda1 = "Pesos "
End If

TFNumero = TFNumero & Leyenda1 & Mid(NumTmp, 17) & "/100 M.N."

If Mayusculas = 1 Then
TFNumero = UCase(TFNumero)
Else
TFNumero = LCase(TFNumero)
End If

NumLetras = TFNumero

End Function

Private Function Centena(ByVal uni As Integer, ByVal dec As Integer, _
ByVal cen As Integer) As String

Select Case cen
Case 1
If dec + uni = 0 Then
cTexto = "cien "
Else
cTexto = "ciento "
End If
Case 2: cTexto = "doscientos "
Case 3: cTexto = "trescientos "
Case 4: cTexto = "cuatroscientos "
Case 5: cTexto = "quinientos "
Case 6: cTexto = "seiscientos "
Case 7: cTexto = "setescientos "
Case 8: cTexto = "ochoscientos "
Case 9: cTexto = "novescientos "
Case Else: cTexto = ""
End Select

Centena = cTexto
cTexto = ""

End Function

Private Function Decena(ByVal uni As Integer, ByVal dec As Integer) As String

Select Case dec
Case 1
Select Case uni
Case 0: cTexto = "diez "
Case 1: cTexto = "once "
Case 2: cTexto = "doce "
Case 3: cTexto = "trece "
Case 4: cTexto = "catorce "
Case 5: cTexto = "quince "
Case 6 To 9: cTexto = "dieci"
End Select
Case 2
If uni = 0 Then
cTexto = "veinte "
ElseIf uni > 0 Then
cTexto = "veinti"
End If
Case 3: cTexto = "treinta "
Case 4: cTexto = "cuarenta "
Case 5: cTexto = "cincuenta "
Case 6: cTexto = "sesenta "
Case 7: cTexto = "setenta "
Case 8: cTexto = "ochenta "
Case 9: cTexto = "noventa "
Case Else: cTexto = ""
End Select

If uni > 0 And dec > 2 Then cTexto = cTexto + "y "

Decena = cTexto
cTexto = ""

End Function

Private Function Unidad(ByVal uni As Integer, ByVal dec As Integer) As String

If dec <> 1 Then
Select Case uni
Case 1: cTexto = "un "
Case 2: cTexto = "dos "
Case 3: cTexto = "tres "
Case 4: cTexto = "cuatro "
Case 5: cTexto = "cinco "
End Select
End If

Select Case uni
Case 6: cTexto = "seis "
Case 7: cTexto = "siete "
Case 8: cTexto = "ocho "
Case 9: cTexto = "nueve "
End Select

Unidad = cTexto
cTexto = ""

End Function

En realidad, son cuatro funciones las que se utilizan para lograr el cometido.

Para usarla, simplemete escribimos en alguna celda:

=NUMLETRAS(A2,1)

Esto, claro, si hemos guardado la función en el mismo libro en el que la vamos a usar. Si la guardamos en el libro de macros personal (de forma que esté disponible en todos los libros), entonces tendremos que escribir:

=Personal.xls!NUMLETRAS(A2,1)

Para mayor seguridad, utilicen el asistente de funciones (el pequeño botón fx situado a la izquierda de la barra de fórmulas). La función estará en la categoría Definidas por el usuario.

Si queremos el resultado en minúsculas, escribimos 0 (pero no FALSO) como segundo argumento. Al final, la función agrega el texto " pesos 00/100 m.n.", el cual puede ser ajustado en el código.

La fórmula =NOMPROPIO(NUMLETRAS(1425300,1))devuelve:

Un Millon Cuatroscientos Veinticinco Mil Trescientos Pesos 00/100 M.N.

Generar un número aleatorio - Tip

2009-02-10T13:39:00.002-06:00

Realizando una inspección de rutina de Excel, encuentro este tip en la ayuda de la función ALEATORIO:

"Si desea usar ALEATORIO para generar un número aleatorio pero no desea que los números cambien cada vez que se calcule la celda, puede escribir =ALEATORIO() en la barra de fórmulas y después presionar la tecla F9 para cambiar la fórmula a un número aleatorio."

Obviamente, después de presionar F9 hay que presionar Enter para aceptar el número.

Muy útil cuando estamos probando fórmulas.

Representar escalas en Excel

2009-02-06T12:52:00.002-06:00

En el dibujo técnico o arquitectónico, es un "principio generalmente aceptado" el uso de los dos puntos (:) para especificar la escala en la que está hecho determinado dibujo. Por ejemplo, 1:125, lo cual quiere decir que una pulgada en el dibujo representa 125 pulgadas del modelo real.

¿Cómo representar esto en Excel? Supongamos la siguiente tabla de medidas originales y las utilizadas en el dibujo:

Primeramente, dividimos la cantidad final entre la original, resultando un número decimal, lo cual no nos sirve.

¿Cuestión de formato? Intentemos dar formato de fracciones. Seleccionamos el rango, Ctrl + 1, ficha Número, Fracciones. Seleccionamos la opción Hasta tres décimas. El resultado:

Se acerca, pero sigue sin ser lo que buscamos. Intenté elaborar algún formato personalizado y tampoco. No obtuve el resultado deseado.

La única vía que veo es la utilización de fórmulas, como la siguiente:

=B2/M.C.D(B2,A2) & ":" & A2/M.C.D(B2,A2)

M.C.D devuelve el máximo común divisor de dos hasta 29 números. Está disponible en el complemento Herramientas para análisis (en las versiones 2003 o anteriores; es función nativa en 2007).

La importancia de Excel en el mundo

2009-02-03T10:00:00.007-06:00

Microsoft Office Excel (Excel) es la hoja de cálculo líder en el mercado. Es además, el software más potente, más flexible y más utilizado del mundo. Ningún otro programa puede competir con él en cuanto a funciones o flexibilidad. Su ámbito de aplicabilidad va de la economía a la sicología, de la biología al dibujo, de las matemáticas aplicadas a la administración de los recursos humanos.

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 a la medida o "independientes" que existen, en realidad utilizan a Excel como motor de cálculo. Casi todos muestran sus resultados en una hoja Excel. Cuando el usuario tiene un nivel avanzado del mismo, 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 en lenguaje VBA (Visual Basic for Applications o, más exactamente, Visual Basic for Excel) de forma que puedan realizarse en unos pocos segundos. Al dominar plenamente la programación en Excel, el lenguaje VBA, es posible elaborar en minutos el trabajo que anteriormente llevaba días enteros.

Imaginemos, por un momento, que el día de mañana Excel desapareciera. Seguramente habría pérdidas económicas. La primera acción que nos vendría a la mente sería migrar a otra hoja de cálculo, pero ¿a cuál? ¿Lotus? ¿Multiplan? ¿habría suficientes copias para distribuir a todos los equipos del mundo? ¿entonces, Google Spreadsheets, on-line y por lo tanto más lenta...? Iniciaría una nueva guerra por establecer un nuevo estándar de hoja de cálculo, con los previsibles problemas de compatibilidad entre los usuarios. ¿Cuánto tiempo llevaría capacitar a los nuevos usuarios? ¿cuánto tiempo llevaría convertir los archivos al nuevo formato? ¿soportarían las macros Excel o los gráficos al menos, podrían interactuar con el resto de programas de oficina? probablemente no. ¿Cuánto tiempo llevaría integrar todos los programas que actualmente utilizan Excel como motor de cálculo al nuevo programa?

Pero no nos preocupemos, esto nunca va a ocurrir (espero). En cambio, si Lotus desapareciera, dudo mucho que hubiera algún efecto perceptible.

Imprimir los comentarios de una hoja

2009-01-28T10:00:00.003-06:00

En ciertos casos, podemos necesitar imprimir el contenido de todos los comentarios de la hoja, o de todo el libro. Sobre todo si es un libro compartido en el que varias personas realizan comentarios.

Para imprimir los comentarios de una hoja en Excel, hacemos el siguiente ajuste:

Archivo - Configurar página - ficha Hoja. En la sección imprimir, está la opción Comentarios:

Aquí, podemos seleccionar las opciones Al final de la hoja (en esta, Excel hará un resumen de los comentarios al final del área de impresión) , o Como en el libro (para imprimir los comentarios tal y como aparecen en al hoja al pasar el mouse por las celdas, o bien, al dar clic derecho en una celda con comentario - Mostrar u ocultar comentarios).

Anivesario de Lotus 1-2-3

2009-01-26T13:06:00.006-06:00

Hoy se cumplen 25 años del lanzamiento de la hoja de cálculo Lotus 1-2-3, la gran sucesora de VisiCalc, y que dominó durante varios años el mercado de las hojas de cálculo electrónicas.

Fuente .

Nuevo gurú Excel

2009-01-26T10:00:00.002-06:00

Acabo de recibir la Taza Oficial del Gurú Excel, de parte de MrExcel.com:

Esto me convierte oficialmente en un auténtico gurú del Excel.

El único detalle es que me costó 13 dólares (mas envío).

Si desean adquirir una (entre algunos otros artículos alusivos a Excel), clic aquí.

Ordenar con más de tres criterios

2009-01-23T10:00:00.003-06:00

En alguna ocasión escuché a alguien decir que quería ordenar una lista de datos considerando cinco criterios de ordenación. Recuerdo también que alguien comentó que eso era imposible, ya que el comando Datos - Ordenar solo admitía tres entradas como criterios:

Efectivamente, Ordenar solo acepta tres criterios de ordenación. No hay una forma directa de especificarle a Excel un número mayor de criterios. Aún así, podemos ordenar una lista con cualquier número de criterios. Solo tenemos que dar un par de pasos extras.

Primeramente jerarquizamos nuestros criterios del más al menos importante. Después tomamos los tres menos importantes y los establecemos como criterios 1, 2 y 3. Explico: supongamos que queremos ordenar una lista con cinco criterios: ciudad, fecha de ingreso, sueldo, cuota y nombre, en ese orden de importancia. Entonces damos Datos - Ordenar y establecemos el criterio sueldo como primer criterio, cuota como segundo criterio y nombre como tercer criterio.

Damos Aceptar.

Ahora, como ya tenemos los datos ordenados por los últimos tres criterios, simplemente volvemos a ir a Datos - Ordenar y ordenamos la lista por los primeros dos criterios.

Eso es todo. La lista estará ordenada siguiendo los cinco criterios solicitados. Recordemos aquí que si Excel encuentra varios registros que cumplen con los criterios establecidos, los presentará en el mismo orden en el que estaban en la lista original. En nuestro caso, cuando ejecutamos Ordenar por segunda vez, la lista "original" ya cumplía con los últimos tres criterios.

Tal como comenté en la presentación de este blog, Excel es el programa más potente del mundo, pero no hace milagros. Habrá ocasiones en que tendremos que echarle una mano.

Arte en Excel

2009-01-21T10:00:00.002-06:00

Hemos sabido de expresiones artísticas inverosímiles. Entre ellas está el realizar obras de arte utilizando Excel, como esta persona:

Una muestra más de la increíble flexibilidad que nos proporciona Excel.

Otra muestra, que pudiera no catalogarse como arte, pero que es tanto o más sorprendente:

Reconozco el tiempo que han invertido estas personas en sus obras, aunque no obstante, no recomendaría utilizar Excel como herramienta de dibujo. Hay infinidad de programas de dibujo (como Corel Draw), con los se obtienen mucho mejores resultados, y en menos tiempo. Con tantísimos trazos, ha sido una suerte que no se dañara el archivo.

Sumar por colores

2009-01-19T10:00:00.004-06:00

Un usaurio me pregunta si es posible sumar los valores de celdas que tengan determinado color.
En Excel 2007 sí es posible, pero el usuario tiene la versión 2003.

En Excel 2003 la única forma es a través de una función personalizada, como la siguiente:

Function SUMARCOLOR(RangoColor As Range, CeldaColor As Range) As Long

Dim rngCelda As Range

'revisamos cada celda del rango
For Each rngCelda In RangoColor

'si los colores coinciden, sumar el valor de la celda al resultado previo
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
SUMARCOLOR = SUMARCOLOR + rngCelda.Value
End If

Next

End Function

Como vemos, no es un código muy complejo que digamos. Simplemete comparamos cada celda del rango contra el color del segundo argumento y, si coinciden, lo vamos sumando al resultado de la función.

Para utilizarla, utilizamos el asistente para funciones, buscamos la función en la categoría Definidas por el usuario, y especificamos los argumentos, quedando una fórmula similar a:

=SUMARCOLOR(A2:I20, K2)

Aunque por otra parte, lo mejor hubiera sido establecer alguna condición para los colores, y después sumar los valores usando esta condición, así como utilizar esta misma condición en un formato condicional para obtener el color en las celdas. Hacerlo hubiera llevado menos tiempo que el estar coloreando cada una de las celdas.

Tipos de errores

2009-01-06T17:45:00.025-06:00

Al estar depurando alguna fórmula, es posible que obtengamos un resultado de error, es decir, un valor que comienza con un signo #. Esto no siempre es malo (de hecho, puede ser un resultado correcto). Si sabemos interpretar el error, podremos corregirlo fácilmente. Téngase en cuenta que para deshacerse del error puede ser necesario modificar ya sea la fórmula misma, o bien alguna de las celdas a las que hace referencia la fórmula.

En Excel existen siete resultados de error:

#¡N/A!
#¡REF!
#¡NUM!
#¿NOMBRE?
#¡DIV/0!
#¡VALOR!
#¡NULO!

Veamos que significa cada uno de ellos. De esta manera, podremos depurarlo y corregirlo fácilmente.

#¡N/A!

Este error se produce cuando una fórmula de búsqueda o referencia no encuentra ninguna coincidencia exacta en la correspondiente matriz de búsqueda. Significa que el valor buscado no existe en la matriz de búsqueda.

#¡REF!

Este tipo de error surge cuando tenemos una referencia de celda inválida en la fórmula. Por ejemplo, en la fórmula: =BUSCARV("mi_string",A2:B8,3,FALSO), obtenemos #¡REF! ya que no podemos buscar en la tercera columna de una matriz que solo tiene dos columnas. En esta otra: =DESREF(Hoja1!A1, -1,0,1,1)

también obtenemos #¡REF! ya que no hay ninguna fila encima de la celda A1. Siguiendo con esta fórmula, si eliminamos la primera fila de la hoja "Hoja1", o si eliminamos la Hoja1, la fórmula mostrará #¡REF!, ya que se ha "perdido" la referencia a la celda Hoja1!A1.

#¡NUMERO!

Este se produce cuando ingresamos algún valor no numérico como un argumento de función que Excel espera que sea argumento numérico (o una referencia a un valor numérico). Otra posibilidad es ingresar un número inválido, como uno negativo cuando se espera uno positivo, o un 2 cuando el argumento solo admite 0 ó 1. La fórmula =COINCIDIR(123, B1:B10,3) devuelve #NUM!, ya que el último argumento de COINCIDIR solo puede ser -1, 0 ó 1.

#¡NOMBRE!

Este error lo obtenemos cuando escribimos mal el nombre de alguna función. También puede surgir cuando utilizamos alguna función personalizada y tenemos deshabilitadas las macros o el complemento correspondiente. Otra situación que dispara este error es el escribir mal el nombre de algún rango nombrado. La fórmula =SUMARSI(A2:A10,"criterio",C2:C10) devuelve #¡NOMBRE! porque la función SUMARSI no existe (más bien está mal escrita. Lo correcto es SUMAR.SI). Finalmente puede suceder también que no utilizamos comillas al ingresar un argumento de texto.

#¡DIV/0!

Este es fácil. Se produce al hacer una división por cero, o bién, por una referencia a un cero. (¿Cómo corregirlo?)

#¡VALOR!

Similar a #¡NUMERO!, lo obtenemos cuando el tipo de argumento solicitado por la función, es distinto al ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función requiere un rango, o un número cuando la función espera texto.

#¡NULO!

Este es muy poco frecuente. Una fórmula devolverá #¡NULO! cuando la celda de intersección de dos rangos, no existe. En Excel, el operador de intersección es un espacio en blanco. Por tanto, la fórmula =A2:D2 J1:J10, devuelve #¡NULO! ya que los rangos A2:D2 y J1:J10 no se intersectan en ningún punto. En cambio, =A2:D2 C1:C10 devuelve C2, celda común a ambos rangos.

A menudo sucede que una celda de error está correctamente escrita pero, al hacer referencia a un resultado de error, refleja este resultado. Para saber cuál es la celda exacta que está generando el error, podemos ejecutar (previa selección de la celda con error) Herramientas - Auditoría de fórmulas - Rastrear error. Excel señalará con una línea roja la celda que está produciendo el error.

Otro error común es cuando la celda aparece llena de símbolos #. Esto se debe a que la celda no es lo suficientemente ancha para mostrar el resultado o bien, cuando contiene una fecha inválida.

Obtener los elementos únicos de una lista

2008-12-23T11:29:00.004-06:00

Una tarea común es obtener los elementos únicos de una lista. La siguiente macro permite realizarlo:

Sub elementosunicos()

Dim celda As Range
Dim unicos As Collection
Dim sh As Worksheet
Dim i As Long

'nos aseguramos que la seleccion sea un rango
If TypeName(Selection) = "Range" Then

'inicializamos la coleccion
Set colUnique = New Collection

'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Selection.Cells

'si el elemento existe, se genera un error, ignorarlo
On Error Resume Next

'una coleccion solo agrega elementos no repetidos
unicos.Add celda.Value, CStr(celda.Value)
On Error GoTo 0
Next celda

'agregar hoja para la lista
Set sh = ActiveWorkbook.Worksheets.Add

'escribir los datos unicos
For i = 1 To unicos.Count
sh.Range("A1").Offset(i, 0).Value = unicos(i)
Next i

'ordenar
sh.Range(sh.Range("A2"), sh.Range("A2").End(xlDown)) _
.Sort sh.Range("A2"), xlAscending, , , , , , xlNo

End If

End Sub

Otras alternativas son generar una tabla dinámica y agregar el campo correspondiente al área de filas, o bién, utilizar el filtro avanzado. El análisis que hagamos del modelo nos dirá cuál método es el mejor.

La entrevista en español

2008-12-17T15:12:00.006-06:00

Como lo prometido es deuda, les presento en español la entrevista que el Excel MVP Charley Kyd le concedió al sr. Chandoo en su blog.

-¿Cuáles son tus tres fórmulas favoritas?
-No tengo fórmulas favoritas, pero hay tres funciones que utilizo todo el tiempo:

INDICE
COINCIDIR (con el tercer argumento igual a cero)
SUMAPRODUCTO

-Si soy un principiante en Excel, qué libros o recursos me recomendarías?
-El foro de Mr. Excel.com para hacer preguntas. También los grupos de discusión de Microsoft y el grupo de noticias microsoft.public.excel para preguntar.

-¿Cómo podrían los gerentes y analistas ser más productivos con Excel?
-No actualicen a Excel 2007. Y si lo hacen, mantener una copia de Excel 2003 en sus equipos. (Al instalar Excel 2007 sobre 2003, responder No cuando el programa de instalación pregunte si quieren actualizar a la nueva versión).

Donde sea posible, separar datos de resumenes. Después, utilizar fórmulas para jalar los datos en el resumen. (Mis tres funciones favoritas te ayudarán a hacerlo).

Aprender métodos abreviados con el teclado. En las versiones anteriores a 2007, los comandos Alt... son consistentes. Y 2007 te permite utilizar las combinaciones Alt+tecla de las versiones anteriores.

-¿Qué recursos (libros, sitios web) recomiendas para este tipo de usuarios?
-Estaré hablando más sobre separación de datos y presentaciones en ExcelUser.com en el próximo año. Suscríbete a mi newsletter para estar al tanto de los nuevos desarrollos.

-Piensas que un microempresario puede administrar su negocio utilizando solo Excel y otros programas gratuitos?
-Sí y no. No recomendaría utilizar Excel para llevar la contabilidad. Quicken es muy barato y hace un mucho mejor trabajo. Pero Excel puede ayudar de muchas otras maneras, como en análisis de datos, presupuestos, cálculo de precios y más.

-¿En dónde crees tú que la mayoría de nosotros desperdiciamos más tiempo...?

...importando datos de otros sistemas/fuentes?
-Realizando la misma actividad analítica o de reporteo una y otra vez, pero con diferentes datos. Cuando te descubras haciendo esto, intenta buscar otro método en el que utilices fórmulas para jalar los datos que necesites de otro libro de datos. Así, podrás enfocar tu atención únicamente en la actualización de los datos, en lugar de comenzar de cero cada vez.

...en fórmulas y errores?
-Mucha gente no sabe cómo cambiar al Modo de cálculo manual. (Herramientas - Opciones - Calcular - Manual). Esto nos permite trabajar en libros extensos sin tener que esperar a que recalcule todo el tiempo. Después, cuando queramos calcular, simplemente presionamos F9. Mucha gente elabora hojas y libros mucho más grandes de lo que deberían, y entonces se pierden en ellos. Yo intento mantener mis libros y hojas reducidos, a menos que tenga una razón específica para no hacerlo.

Mucha gente crea demasiados vínculos entre libros. Esto es un problema porque los vínculos pueden dañar o dañarse, o generar errores de referencias circulares. Yo trato de vincular solo de mis datos a mi presentación.

Supongamos que tenemos una columna de datos en el rango A5:A10. Si queremos sumar estos datos, regularmente utilizamos la fórmula =SUMA(A5:A10). Yo en su lugar, formateo las celdas A4 y A11 con borde medio y relleno gris. Después sumo utilizando el rango A4:A11. Esto me permite agregar o eliminar filas entre los bordes grises sin tener que preocuparme por las fórmulas que hacen referencia a este rango. Mientras no toque las filas de límite grises, me siento seguro. (No utilizo este método si voy a imprimir las hojas para alguien más, porque luce mal. Pero esto no es problema la mayor parte del tiempo).

...aplicando formatos?
-Trato de no utilizar nunca el botón Combinar y centrar para centrar rótulos en varias columnas. (De hecho, dudo haber utilizado este comando más de media docena de veces *en mi vida*). En su lugar, utilizo Formato - Celdas - Alineación - Centrar en la selección. Esto proporciona el mismo efecto pero sin obligarme a lidiar con los contratiempos que generan las celdas combinadas.

...en VBA?
-VBA es muy potente, y puede ser muy divertido. Pero hay que tener cuidado, puede volverse una adicción. Muchos usuarios de VBA pasan muchas horas creando macros que les ahorran varios minutos de trabajo. Esto obviamente no es un buen uso de nuestro tiempo. Por otra parte, en lo personal trato fuertemente de comentar mi código detalladamente. Y cuando reviso código viejo, *siempre* he deseado haberlo comentado con más detalle todavía . Cuando estás a mitad de un proyecto, la razón de cada línea de código parece obvia. Pero seis meses después, todo es un misterio. COMENTA TU CÓDIGO.

-¿Cuál es la mejor forma para un no programador de aprender y utilizar VBA en su trabajo cotidiano?
-Continúa con una versión anterior a 2007, por dos razones: no hay buenos libros acerca de macros 2007, y la grabadora de macros no funciona con muchas acciones en 2007.

Consigue un libro para principiantes y comienza a experimentar.

Utiliza la grabadora de macros y mira los resultados.

Haz preguntas en grupos de trabajo y foros.

Familiarízate con el Explorador de objetos. (En el VBE, presiona Ver - Explorador de objetos. O simplemente presiona F2).

Diccionario de funciones en inglés

2008-12-16T11:00:00.001-06:00

Navegando por la red, me encontré con un Diccionario de funciones de Excel, propiedad del sr. Peter Noneley. Es una excelente compilación de funciones explicadas y ejemplos de fórmulas.

Las funciones son alrededor de 150.

También tiene numerosos ejemplos de uso.

Finalmente, agrupa las funciones en las categorías que maneja Excel.

Recomiendo ampliamente descargar el archivo y dedicarle unas cuantas horas. Aprenderán bastante. Es un archivo plano, sin macros ni VBA. El único detalle es que está en inglés. Es necesario conocer la traducción de las funciones al español.

Entrevista con Charley Kyd

2008-12-15T13:35:00.004-06:00

En su excelente blog Pointy Hairy Dilbert, Chandoo publica la entrevista que sustuvo con Charley Kyd, quien es uno de los escasos 74 Excel MVP (Most Valuable Professional) reconocidos por Microsoft en todo el mundo. Aunque breve, es sumamente ilustrativa. Para ver la entrada original (en inglés) pueden dar clic aqui.

Para quienes no dominan el inglés, intentaré realizar una decente traducción al español, misma que publicaré lo antes posible.

Dividir en múltiplos de 10

2008-12-11T16:02:00.006-06:00

Problema:

Dado cierto número, por ejemplo el 123456, obtener una fórmula que devuelva los resultados:

12345
1234
123
12
1

Es decir, dividirlo sucesivamente por 10, 100, 1000 etc.

Para lograrlo utilizamos la función MULTIPLO.INFERIOR. Esta, redondea un número hacia cero, al múltiplo especificado. La sintaxis:

MULTIPLO.INFERIOR(número, cifra_significativa)

Ejemplo:

=MULTIPLO.INFERIOR(2530,100) devuelve 25, ya que divide el número (2530) entre 100 (25.3) y lo redondea hacia abajo a cero.

De vuelta a nuestro caso, construyamos un tabla auxiliar que muestre los resultados (y algunos ejemplos más) como sigue:

Escribimos esta fórmula en la celda B3:

=MULTIPLO.INFERIOR($A3/10^B$2,1)

Copiamos y pegamos al resto del rango y listo.

Introducción a la programación VBA

2008-12-11T13:27:00.008-06:00

En lo sucesivo comenzaré a publicar ejemplos y técnicas de programación útiles en el desarrollo de modelos Excel. Así que supondré que el lector tiene conocimientos intermedios en el lenguaje VBA (Visual Basic for Aplications). Si no es así y desean una introducción al tema, les recomiendo el siguiente vínculo del sitio Excel Worker, que además está en español:

http://www.excelworker.virtuabyte.cl/index.php?option=content&task=section&id=3&Itemid=27

En cambio, si tienen conocimientos intermedios - avanzados de inglés, entonces recomiendo Erlandsen Data Consulting o el sitio de Chip Pearson.

Todos contienen material suficiente para iniciarse por lo que no pretenderé reinventar la rueda.

Saludos.

Funciones personalizadas

2008-12-03T18:22:00.007-06:00

Excel cuenta con un inmenso número de funciones integradas, de la más diversa índole. Asimismo, hay numerosos complementos (el más famoso es Herramientas para análisis) que agregan todavía más funciones a Excel. Por si fuera poco, podemos crear nuestras propias funciones, lo cual puede ser útil en el (improbable) caso de que no exista una función que haga lo que necesitemos; o bien, para simplificar nuestras fórmulas. Aunque para lograrlo es necesario tener conocimientos más o menos sólidos en programación de macros.

Programar una función es parecido a programar una macro. De hecho, algunos autores llaman también macros a las funciones personalizadas. No obstante, existe una diferencia fundamental entre ambas: una función solo puede devolver un resultado. No puede operar o cambiar el entorno de Excel. (OK, sí puede, pero son excepciones ya muy rebuscadas). Lo siguiente es una macro:

Sub insertahoja()
ActiveWorkbook.Worksheets(1).Copy Before:=ActiveWorkbook.Worksheets(4)
ActiveWorksheet.Name = "Hoja número 3"
End Sub

En cambio, esto es una función:

Private function micalculo(num_1 As Integer, num_2 As Integer) As Integer

Dim j As Integer

If num_1 <= num_2 Then
j = num_2 - num_1
Else
j = num_1 - num_2
End If

micalculo = j

End function

Como vemos, en el primer caso, alteramos el entorno Excel al copiar una hoja en el libro activo, mientras que en el segundo, solo obtuvimos el resultado j (que es la diferencia absoluta entre dos números). No hicimos nada más. Otra diferencia evidente es la forma de declarar y terminar una función con las cláusulas Private function y End function. Por lo demás el lenguaje utilizado es el mismo.

Por otra parte, el resultado que devuelve una función puede no ser (como pudiera pensarse) un número. Puede ser texto, un resultado lógico (FALSO o VERDADERO), un rango, un nombre, entre otros. Por ello, es necesario declarar el tipo de dato del resultado (el último "As Integer").

Una función puede ser utilizada o llamada por una macro, por otra función, o a través de la interfaz de Excel. Para el primer caso veamos este ejemplo:

Sub insertahoja()
ActiveWorkbook.Worksheets(1).Copy Before:=ActiveWorkbook.Worksheets(4)
ActiveWorksheet.Name = "Hoja número 3"

ActiveWorksheet.Range("A3") = micalculo(ActiveWorksheet.Range("A1").Value, ActiveWorksheet.Range("A2").Value)

End Sub

Con esta macro, modificación de la previa, escribiremos en la celda A3 el resultado de la fórmula =micalculo(A1, A2)

Para el segundo caso, veamos:

Private function otrocalculo(num_1 As Integer, num_2 As Integer) As Integer

j = micalculo(num_1 As Integer, num_2 As Integer)

j = j * -1

otrocalculo= j

End function

Esta función simplemente multiplica por - 1 el resultado de micalculo(num_1 As Integer, num_2 As Integer)

Como último caso, si queremos usar esta función desde la interfaz de Excel, simplemente escribimos en una celda:

=MICALCULO(A1, A2)

Si queremos utilizar el Asistente para funciones de Excel, necesitamos dar clic en la categoría Definidas por el usuario, al final de la lista de categorías. Un detalle importante a tener en cuenta es que Excel de alguna manera "recuerda" la forma en que escribimos el nombre de la función la primera vez, es decir, con mayúsculas o minúsculas. Si la primera vez que escribamos la función utilizamos minúsculas, Excel la mostrará siempre así en la barra de fórmulas.

Hemos visto un ejemplo sencillo, pero cuando tenemos necesidad de hacer cálculos complejos o grandes, podemos mejorar la legibilidad de la fórmula simplemente agregando argumentos a una sola función.

Excel on-line

2008-11-18T16:53:00.012-06:00

Recientemente, Microsoft anunció el lanzamiento de Microsoft Office Live, una versión on-line de sus productos insignia Word, Power Point, One Note y por supuesto, Excel. MS promete que estas aplicaciones web permitirán crear, editar y compartir con el explorador nuestros documentos. Desde luego que no podrán igualar la funcionalidad de sus contrapartes de escritorio, pero hay que ver las ventajas: nada de actualizaciones periódicas, incompatibilidad de versiones, corre en Firefox, es gratis...

Finalmente, Microsoft ha decidido incursionar en el software de oficina on-line, mercado actualmente dominado por Google (¿alguna objeción?) y su suite Docs.

De acuerdo a Read Write Web, las aplicaciones tendrán los mismos nombres que sus contrapartes de escritorio. La versión Beta estará disponible este mismo año.

Lamentablemente, parece ser que la de Excel será una versión muy parecida a 2007. Como sea, démosle una oportunidad. Si llega a igualar la funcionalidad de una Google Spreadsheet,

bienvenido sea.

Feliz cumpleaños, Excel

2008-11-12T15:32:00.024-06:00

El mes pasado, concretamente, el 17 de octubre, se cumplieron 29 años del lanzamiento de VisiCalc, la primera hoja de cálculo comercial y predecesor más antiguo de Excel.

Durante sus primeros cinco años de vida, VisiCorp. (compañía propietaria) fue la mayor vendedora de software, venciendo rotundamente a Microsoft.

El programa original aún corre en las PC's actuales (descargar). A pesar de la simpleza que le dan sus 27KB (es correcta la "K"),

el impacto que tuvo fue enorme. Fue el programa que realmente acercó los PC a la gente común. De no ser por VisiCalc no hubiera sido posible el sueño de Bill Gates de colocar una PC en cada hogar norteamericano. Antes de su lanzamiento, las computadoras solo podían ser utilizadas por programadores experimentados. El resto de la gente solo podía utilizar programas ya elaborados que resolvían problemas muy específicos. VisiCalc le dio a la gente el poder de resolver problemas "personales" sin la necesidad de tener formación como programador. El hecho de poder cambiar un número en cualquier parte de la pantalla, y ver inmediatamente los resultados, brindó formas de análisis nunca antes vistas, y de forma totalmente intuitiva.

Su manual constaba de ocho páginas.

Sus creadores, Dan Bricklin y Bob Frankston:

La competencia responde. El 26 de enero de 1983 Lotus Software presenta el software para computadoras personales Lotus 1-2-3, que llegó a convertirse en el estándar de las hojas de cálculo en los inicios de la computación personal. La primera versión para DOS, desplazó del mercado rápidamente a VisiCalc, que dominaba desde 1980. Su enorme popularidad afianzó el éxito de los PC en las empresas. Fiel al sistema operativo OS/2, fue lento en su migración al sistema Windows, lo que motivó de su debacle.

Desarrollado por estudiantes de doctorado de Harvard, sigue disponible como parte de la suite Lotus SmartSuite, después de mucho tiempo de reusarse a migrar a Windows.

En 1987, Lotus demandó legalmente por separado a Paperback Software y a Mosaic Software, alegando que eran copias de Lotus 1-2-3. Por su parte VisiCorp. demandó a Lotus, aduciendo que 1-2-3 era idéntico a VisiCalc. Lotus ganó todas estas batallas legales, pero perdió la guerra por el mercado con Microsoft.

Otros competidores con más pena que gloria fueron Quatro, Fortran y Multiplan.

Mientras tanto, Microsoft desarrollaba Excel. La primera versión de Excel, la 0.99, fue creada originalmente para Macintosh. Fue uno de los primeros programas en utilizar una interfaz gráfica con menús despleglables así como de un puntero manejado por mouse. Esta interfaz gráfica demostró ser mucho más fácil de usar que las líneas de comandos de DOS. Así, motivó que mucha gente se comprara una Mac con el único objetivo de conocer a Excel.

Apoyado por la gran popularidad que empezó a tener el sistema operativo Windows, ha sido el software de hoja de cálculo líder desde 1985, año en que vio la luz la versión 1.0 para Windows. Durante mucho tiempo, fue la única hoja de cálculo en correr bajo Windows. Fue solo hasta 1992 que otros competidores crearon hojas de cálculo para Windows.

A la fecha han sido lanzadas 11 versiones (Excel 2007 es oficialmente la versión 12, no obstante, no hubo Excel 6.0, ya que a partir de la versión 7 Microsoft decidió estandarizar las versiones de todos los productos de la suite Office).

Actualmente, Excel 2003 es la versión más popular en todo el mundo. La gran mayoría de las entradas de este blog tratan sobre la versión 2003. 2007 no fue bien aceptado debido a los muchos inconvenientes que presenta y a su interfaz muy diferente a la de sus predecesores, tema de una futura nota.

Sofía, asistente personal

2008-11-11T12:44:00.011-06:00

Para aquellos usuarios de la versión 2007, Microsoft pone a su disposición a Sofía, la asistente robot. Aparece en la página principal del sitio oficial de MS Excel:

Como vemos, Sofía tiene un rostro y hasta una cuenta de Messenger. Solo hay que dar un par de clics para agregarla a nuestros contactos. O bien, darla de alta con el nombre offibot@hotmail.com.

Siempre he sido víctima de la curiosidad. No tuve más remedio que agregarla a mis contactos y ponerla a prueba. Esta fue la "conversación" que tuve con "ella" (curiosamente, MS envía un mensaje de alerta sobre riesgos de seguridad al dar clic en los enlaces. Enlaces de su propio sitio...):

-Hola
Sofia dice:
-La declaración de privacidad en línea de Microsoft (http://go.microsoft.com/fwlink/?LinkId=74170) se aplica a toda información que proporciones a este agente de Windows Live.
-Para poder darte respuestas más acertadas, ¿me podrías decir en qué país vives? Esto solo lo utilizaré para dirigirte a los sitios más adecuados de acuerdo a tu respuesta. En un futuro puedes cambiar tu selección con sólo poner el nombre del país en nuestra conversación.
Leonel dice:
-México
Sofia dice:
-Entendido, te mostraré las páginas que corresponden a tu nacionalidad
¡Hola! leonel. Me llamo Sofía, y soy un robot de Microsoft. Puedo resolver tus dudas sobre Word, Excel, PowerPoint y Outlook de Office 2007, y también sé algo sobre los problemas más frecuentes de instalación y soporte. Intentaré contestarte siempre con una URL en la que encontrarás la información que buscas. ¿En qué puedo ayudarte?
Leonel dice:
-¿Cómo hago un informe de tabla dinámica en Excel 2007?
Sofia dice:
-Buena pregunta. En el siguiente articulo puedes encontrar como crear una tabla dinámica o Pivote. Un Tip si quieres buscar más información. En el recuadro de busqueda de esa página escribe tabla dinamica, te sorprenderas. http://office.microsoft.com/es-mx/excel/HA010864773082.aspx

-Sofia te está invitando a iniciar Office Online. ¿Deseas Aceptar (Alt+W) o Rechazar (Alt+X) la invitación?

Has aceptado la invitación para iniciar Office Online
Leonel dice:
-Y estudias o trabajas?
Sofia dice:
-No muchas personas saben que al tener Office tienen acceso a usar más de 400,000 imágenes para usarlos en sus documentos. Si quieres ver algunas haz clic aquí: http://office.microsoft.com/es-es/clipart/FX101321033082.aspx

Conclusiones: Sofía no existe. No es real. (¿Por qué Sofía? ¿Por qué no Maricela, o Britney?) Solo es un agente con respuestas predefinidas que lo único que hace es enviarnos vínculos a páginas relacionadas en el sitio de MS. Cuando pasamos algún tiempo sin hablarle, nos comienza a enviar tips diversos. Igualmente, predefinidos. La única ventaja que le veo es que está disponible a través del Messenger.

Supongo que los robots japoneses deben comenzar a preocuparse.

Para los fanáticos de AC/DC

2008-11-07T16:39:00.013-06:00

El equipo creativo del grupo de rock AC/DC, Phil Clandillon y Steve Milbourne, ha anunciado el lanzamiento del primer video musical en formato .xls. La canción, Rock N Roll Train. Su objetivo, dijeron, era que el video pudiera superar los firewalls más potentes. A fin de cuentas, a nadie le prohiben recibir un archivo Excel ¿cierto?

El video, en realidad, es una macro (recordemos habilitar las macros antes de abrir el archivo) que copia las "imágenes" (texto en realidad) de la columna Q mientras reproduce un archivo WAV. No imagino la cantidad de trabajo que representó dibujar cada uno de los frames de este archivo.

El código del archivo no está protegido. Para ver las imagenes, primero hay que desactivar el evento Worsheet_SelectionChange de la hoja y mostrar la columna Q.

Descargar.

Descargar macros de la red

2008-10-28T12:35:00.022-06:00

En la red hay una gran cantidad de sitios que ponen a nuestra disposición macros de la más diversa índole. Ejemplos de estos sitios son Daily Dose of Excel, Erlandsen Data, xltoday entre muchos otros (en la sección Recursos Excel, al margen, encontrarán los enlaces a estos sitios y varios más). Lo más valioso de estas macros es que son open source, es decir, públicas y gratuitas, ahorrándonos gran cantidad de trabajo y tiempo.

Un detalle común a casi todos estos sitios es que no explican al lector cómo descargar las macros a sus equipos (como dicen algunos, "a mi Excel"). ¿Cómo copiar una macro a "nuestro Excel" y ejecutarla? Seguimos los siguientes pasos:

Supongamos que queremos utilizar la siguiente macro de ejemplo (de Daily Dose of Excel):

Sub CreateTest()

Dim rCell As Range
Dim sFname As String
Dim lFnum As Long
Dim sInput As String
Dim sOutput As String

If TypeName(Selection) = "Range" Then
'Pick a name for the test file
sFname = Application.InputBox("Enter test file name to create.", "File Name")

If sFname <> "False" Then 'if inputbox not canceled

'Add file extension if not there
If Right$(sFname, 4) <> ".txt" Then
sFname = sFname & ".txt"
End If

sInput = "[Input]" & vbNewLine
sOutput = "[Output]" & vbNewLine

'Build input and output strings based on selection
For Each rCell In Selection.Cells
If rCell.HasFormula Then
sOutput = sOutput & ActiveSheet.Name & "|" & _
rCell.Address & "|" & rCell.Value2 & vbNewLine
Else
sInput = sInput & ActiveSheet.Name & "|" & _
rCell.Address & "|" & rCell.Value2 & vbNewLine
End If
Next rCell

sOutput = Left$(sOutput, Len(sOutput) - 2) 'delete new line

'Create the test file
lFnum = FreeFile
Open sFname For Output As lFnum

Print #lFnum, sInput
Print #lFnum, sOutput

Close lFnum

End If
Else
MsgBox "Please select one or more cells and try again"
End If

End Sub

Como leemos en Daily Dose... lo que hace esta macro es generar un archivo de texto que lista las referencias ingresadas como argumento, y su correspondiente valor, separadas en entradas de fórmula y fórmulas. Útil para auditar lás fórmulas de nuestros modelos.

El primer paso es seleccionar el texto de la macro y copiarlo.

A continuación, activamos Excel y vamos al editor de Visual Basic (Herramientas - Macro - Editor de Visual Basic, ó Alt + F11).

Una vez aquí, tenemos que determinar algo muy importante: el ámbito de aplicabilidad de la macro. Es decir, si la macro va a ser utilizada sólo en determinado libro o bién, en todos los libros. Esto dependerá del análisis general que hagamos de la situación. En este caso, supongamos que queremos que la macro esté disponible en todos los libros. Por lo tanto, la guardaremos en nuestro libro de macros personal.

En el editor de Visual Basic, en el Explorador de proyectos, damos doble clic en Personal y a continuación, pegamos el código que hemos copiado al final del código existente.

Si queremos pegar la macro en su propio módulo, lo insertamos con Insertar - Módulo.
Finalmente, salimos del editor, con Archivo - Salir y volver a Microsoft Excel (Alt + q). Podemos guardar previamente la macro aunque esto no es estrictamente indispensable.

Procedamos ahora a ejecutar la macro. De acuerdo a las instrucciones de la nota original, primero hay que seleccionar un grupo de celdas con o sin fórmulas. Después vamos a Herramientas - Macro - Macros... (ó Alt + F8). En este punto, podemos elegir asignar una tecla de método abreviado a la macro y/o agregar una descripción a la misma dando clic en el botón Opciones...
Finalmente, seleccionamos el nombre de la macro y hacemos clic en Ejecutar.

En caso de que queramos que la macro solo esté disponible en determinado libro, entonces, damos doble clic en el proyecto correspondiente en el Explorador de proyectos. Acto seguido, damos Insertar - Módulo, y pegamos aquí el código.

Functions list

2008-10-21T11:34:00.006-05:00

Versión en Español.

Please read this post.

=CONVERT, 638844971
=WORKDAY, 1679294519
=YEARFRAC, 734789689
=WEEKNUM, -601489317
=AMORLINC, 2050949212
=AMORDEGRC, -760479651
=RECEIVED, 1825046605
=COUPDAYS, -1225457601
=COUPDAYBS, -1886650304
=COUPDAYSNC, 85852225
=COUPPCD, 1738735682
=COUPNCD, -2019426237
=COUPNUM, -1858994114
=DURATION, -2027749294
=MDURATION, -2114781101
=ACCRINT, -692060092
=EFFECT, 226623539
=TBILLEQ, -600899503
=TBILLPRICE, -2087911345
=TBILLPRICE, -2087911345
=TBILLYIELD, -2095251376
=DOLLARDE, -2108293060
=DOLLARFR, -2128216003
=CUMIPMT, 1933180986
=CUMPRINC, -1730019269
=PRICE, -1461321658
=PRICEDISC, 1303642186
=ODDFPRICE, -2005663660
=ODDLYIELD, 1307508823
=YIELDMAT, 526319689
=YIELD, 1736245319
=YIELDDISC, -2110848949
=ODDFYIELD, 1206845525
=ODDLYIELD, 1307508823
=YIELDMAT, 526319689
=DISC, -1081081780
=INTRATE, -1046478770
=NOMINAL, 1421541428
=XIRR, -1080295335
=FVSCHEDULE, 1605632050
=XNPV, -841220008
=RANDBETWEEN, 1788346458
=GCD, 1334050856
=LCM, -15466455
=MULTINOMIAL, -164822998
=SQRTPI, 406257673
=MROUND, 147456011
=SERIESSUM, 1365508135
=SERIESSUM, 1365508135
=BESSELI, 571539472
=BESSELJ, 572588046
=BESSELY, 588316687
=BIN2DEC, 908066819
=BIN2HEX, -141557713
=BIN2OCT, -2125463506
=COMPLEX, -1477705690
=CONVERT, 638844971
=DEC2BIN, 908066853
=DEC2HEX, -1690304477
=DEC2OCT, -310378460
=DELTA, -286195700
=FACTDOUBLE, 1382350854
=ERF, 2124677124

Partial list of ATP's functions and returned results when typed without arguments and parenthesis.

Unexpected result

2008-10-15T10:45:00.017-05:00

Versión en español.

When you type an equal sign and a function name (for example "=SUM") and press Enter, you get the #NAME? error, because Excel "thinks" that you want to work with a name, wich is not defined.

Or not?

A few days ago, I was working on a model involving date calculations. Since I wanted to calculate the number of labor days between two dates, I used the NETWORKDAYS function.
However, while writing the formula, I (accidentally) hit Enter right after the function name (i. e. "=NETWORKDAYS", Enter). Unexpectedly Excel returned:

840368184

Where that number comes from, what does it refers to, or why I got preciselly that number and no other, is something that I completely ignore. Since this behavior was totally unexpected, I wanted to repeat it in other machines. Same result. I wondered if this would happen too with other functions, so I tried with =MAX, =MIN, =OFFSET, =RIGHT, =MID, and some others. In all this cases I got the expected #NAME? However, trying with =CONVERT gave me:

638844971

Other results were:

=WORKDAY, 1679294519

=CONVERT, 638844971

There's even negative results. =WEEKNUM returns -601489317.

I concluded that this is an exclusive behavior of the Analysis Toolpak add-in functions (for a complete list of functions and results, click here).

Continuing with these tests in my lab (sure...), I tested now with other add-ins functions I've downloaded from the web. For example, with =COUNTDIFF I got: 1769668796. With all the functions of my add-ins I watched the same behavior. After this, I tried with some UDF's. In all cases I got #NAME?, so I had to modify my original theory: This behavior ocurrs with any function that belongs to any add-in (ATP or any other), but it doesn't happen nor with built-in neither user defined functions.

Since all this was kind of an oddity to me, I sent an e-mail to John Walkenbach (brief Spanish biography). This was my original message:

Hi John:

I entered this “formula”:

=CONVERT

Notice that I didn’t put the parenthesis. Excel returned:

638844971

That happens only whit add-in’s functions. With any other built-in function Excel returns, as usual, #NAME?

Other examples:

=NETWORKDAYS produces 840368184

=WEEKNUM, -601489317

=UNIQUEVALUES, -1451032386

=WORKDAY, 1679294519

Always the syntax =[add-in function] (no parenthesis)

I think this is kind of an oddity. Or, if you may explain me where those numbers came from…

Thank you.

This was his reply:

That's pretty strange. It doesn't happen in Excel 2007 because the ATP functions are now built-in.

I have Excel 2003 installed, but I didn't install the ATP. I'll see if I can find the original CD and install the ATP to check it out.

Regards,
John

If any reader have some idea of why Excel returns this mysterious numbers, please post your comments.

Lista de funciones

2008-10-14T14:51:00.008-05:00

English version.

Por favor lean esta nota.

=CONVERTIR, 638844971
=DIA.LAB, 1679294519
=FRAC.AÑO, 734789689
=NUM.DE.SEMANA, -601489317
=AMORTIZ.LIN, 2050949212
=AMORTIZ.PROGRE, -760479651
=CANTIDAD.RECIBIDA, 1825046605
=CUPON.DIAS, -1225457601
=CUPON.DIAS.L1, -1886650304
=CUPON.DIAS.L2, 85852225
=CUPON.FECHA.L1, 1738735682
=CUPON.FECHA.L2, -2019426237
=CUPON.NUM, -1858994114
=DURACION, -2027749294
=DURACION.MODIF, -2114781101
=INT.ACUM, -692060092
=INT.EFECTIVO, 226623539
=LETRA.DE.TES.EQV.A.BONO, -600899503
=LETRA.DE.TES.PRECIO, -2087911345
=LETRA.DE.TES.PRECIO, -2087911345
=LETRA.DE.TES.RENDTO, -2095251376
=MONEDA.DEC, -2108293060
=MONEDA.FRAC, -2128216003
=PAGO.INT.ENTRE, 1933180986
=PAGO.PRINC.ENTRE, -1730019269
=PRECIO, -1461321658
=PRECIO.DESCUENTO, 1303642186
=PRECIO.PER.IRREGULAR.1, -2005663660
=RENDTO.PER.IRREGULAR.2, 1307508823
=RENDTO.VENCTO, 526319689
=RENDTO, 1736245319
=RENDTO.DESC, -2110848949
=RENDTO.PER.IRREGULAR.1, 1206845525
=RENDTO.PER.IRREGULAR.2, 1307508823
=RENDTO.VENCTO, 526319689
=TASA.DESC, -1081081780
=TASA.INT, -1046478770
=TASA.NOMINAL, 1421541428
=TIR.NO.PER, -1080295335
=VF.PLAN, 1605632050
=VNA.NO.PER, -841220008
=ALEATORIO.ENTRE, 1788346458
=M.C.D, 1334050856
=M.C.M, -15466455
=MULTINOMIAL, -164822998
=RAIZ2PI, 406257673
=REDOND.MULT, 147456011
=SUMA.SERIES, 1365508135
=SUMA.SERIES, 1365508135
=BESSELI, 571539472
=BESSELJ, 572588046
=BESSELY, 588316687
=BIN.A.DEC, 908066819
=BIN.A.HEX, -141557713
=BIN.A.OCT, -2125463506
=COMPLEJO, -1477705690
=CONVERTIR, 638844971
=DEC.A.BIN, 908066853
=DEC.A.HEX, -1690304477
=DEC.A.OCT, -310378460
=DELTA, -286195700
=FACT.DOBLE, 1382350854
=FUN.ERROR, 2124677124

Parte de las funciones del complemento Herramientas para análisis y resultados obtenidos al tipearlas sin argumentos ni paréntesis.

John Walkenbach: Mr. Spreadsheet

2008-10-02T09:31:00.028-05:00

John Walkenbach (Mr. Spreadsheet) es considerado la máxima autoridad mundial en hojas de cálculo (léase Excel).

John se dedica de tiempo completo al estudio y desarrollo de productos en Excel. Es autor de The Spreadsheet Page, así como de más de 50 libros de Excel (listados aquí). Ha escrito más de 300 artículos y revisiones en revistas como InfoWorld, PC World, PC/Computing, entre otras. Fue autor durante tres años de la columna mensual de hojas de cálculo de PC World. Asimismo, es autor de The Spreadsheet Page Blog y co-autor de Daily Dose of Excel, dos de los blogs sobre Excel más populares. Desde el 2000, ha sido nombrado Excel MVP por Microsoft por noveno año consecutivo. Ha elaborado numerosos complementos y programas en Excel, la mayoría de ellos gratuitos, y continuamente da conferencias. Recibe las nuevas versiones de Excel seis meses antes que los demás usuarios para evaluarlas. Actualmente reside en Tucson, Arizona, en los Estados Unidos.

El día de ayer, 1 de octubre, recibí un correo del sr. Walkenbach.

En él, Mr. Spreadsheet me da su opinión acerca de un raro comportamiento que observé en Excel 2003, el cual documentaré después, con más calma (por aquello de la piratería, you know...). Esta fue su respuesta tal cual:

That's pretty strange. It doesn't happen in Excel 2007 because the ATP functions are now built-in.

I have Excel 2003 installed, but I didn't install the ATP. I'll see if I can find the original CD and install the ATP to check it out.

Regards,

John

En lo que a Excel se refiere, el haber encontrado algo sobre lo cual Mr. Spreadsheet no tenía noticia, constituye uno de mis mayores logros.

Es exactamente esto lo que me apasiona de Excel: su absoluta inabarcabilidad. Nadie, ni siquiera John, puede preciarse de conocerlo todo.

Nota: ATP son las iniciales de Analysis Toolpak, nombre en inglés de Herramientas para análisis.

Resultado inesperado

2008-10-01T13:39:00.026-05:00

English version.

Cuando ingresamos una función sin argumentos ni paréntesis (por ejemplo, "=PROMEDIO"), al aceptarla, Excel nos devolverá el error #¿NOMBRE?, ya que interpreta que quisimos introducir un nombre, el cual no está definido.

¿O no?

Hace poco, estuve trabajando en un modelo con fechas en Excel. Como necesitaba calcular el número de días hábiles entre dos fechas, me dispuse a utilizar la función DIAS.LAB. Sin embargo, al comenzar a escribir la fórmula, presioné (accidentalmente) Enter justo después de escribir el nombre de la función (es decir "=DIAS.LAB", Enter). Inesperadamente Excel devolvió:

840368184

De dónde viene este número, a qué se refiere o por qué se obtiene precisamente este número y no otro, es algo que ignoro completamente. Como este comportamiento fue completamente inesperado, quise reproducirlo en otros equipos. Mismo resultado. Me pregunté también si esto ocurriría con otras funciones, así que ingresé =SUMA, =MIN, =DESREF, =DERECHA, =EXTRAE, entre otras. En todos estos casos obtuve el resultado esperado #¿NOMBRE?. Sin embargo, al probar con =CONVERTIR Excel devolvió:

638844971.

Otros resultados fueron:

=DIA.LAB devuelve 1679294519

=FRAC.AÑO devuelve 734789689

Incluso hay resultados negativos. =NUM.DE.SEMANA devuelve -601489317.

Concluí que esto es un comportamiento exclusivo de las funciones del complemento Herramientas para análisis (para una lista completa de sus funciones y resultados, clic aquí).

Al continuar con esta "experimentación", probé ahora con funciones de otros complementos que he descargado de la red. Por ejemplo, con =COUNTDIFF obtuve: 1769668796. Similar comportamiento observé con el resto de las funciones de mis complementos. Posteriormente, traté con algunas funciones personalizadas. En todos los casos el resultado fue #¿NOMBRE?. Así pues, tuve que modificar mi teoría original: Este comportamiento ocurre con cualquier función perteneciente a cualquier complemento (Herramientas para análisis o cualquier otro). No ocurre con funciones nativas de Excel ni con funciones definidas por el usuario (FDU o UDF).

Dado que todo esto me pareció muy extraño, envié un correo a John Walkenbach sobre esto. Este fue mi mensaje original:

Hi John:

I entered this “formula”:

=CONVERT

Notice that I didn’t put the parenthesis. The cell shows:

638844971

That happens only whit add-in’s functions. With any other Excel built-in function we get #NAME?

Other examples:

=NETWORKDAYS produces 840368184

=WEEKNUM, -601489317

=UNIQUEVALUES, -1451032386

=WORKDAY, 1679294519

Always the syntax =[add-in function] (no parenthesis)

I think this is kind of an oddity. Or, if you may explain me where those numbers came from…

Thank you.

Esta fue su respuesta:

That's pretty strange. It doesn't happen in Excel 2007 because the ATP functions are now built-in.

I have Excel 2003 installed, but I didn't install the ATP. I'll see if I can find the original CD and install the ATP to check it out.

Regards,
John

Si algún lector tiene idea de por qué se producen estos números misteriosos, por favor háganoslo saber. Entre tanto, veré que obtengo de las distintas discusiones que surjan sobre esto para comentarlo aquí.

Primer año de vida

2008-10-01T10:53:00.004-05:00

Este mes se cumple un año desde la publicación de la primera nota de este blog. Estas son las estadísticas del mismo según OneStat.com (gráfica semanal):

Los principales indicadores son (pido una disculpa a los lectores por no utilizar un reporte en Excel):

Total de vistas de página: 14,753

Total de visitas: 8,898

Visitantes únicos por año: 7,802 (hay un discrepancia con lo mostrado por la gráfica de NeoCounter, al margen, debido a que este contador lo agregué ligeramente antes que el de OneStat)

La tercera semana de abril ha sido la más activa con 996 visitas. El día más activo fue el 25 de abril con 255 visitas. Puede verse que las visitas tuvieron un rápido incremento en los primeros meses de vida del blog hasta llegar al máximo observado en abril, así como un descenso menos acelerado, en los siguientes meses. Supongo que esto se debió a la menor frecuencia de publicación de las entradas. No obstante, al retomar una mayor frecuencia de publicación (a partir de agosto), el número de vistas ha vuelto a aumentar. Espero retomar la periodicidad en la publicación mostrada en los primeros meses.

A todos aquellos que han visitado este blog, gracias.

Seleccionar celdas con determinado valor

2008-09-25T12:21:00.017-05:00

Hay ocasiones en las que necesitamos seleccionar todas las celdas de una hoja que contienen determinado valor, para borrarlas o editarlas de algún modo.

Normalmente utilizaríamos el Autofiltro. Supongamos la siguiente lista:

Queremos seleccionar todas las celdas que contienen el valor 1,000 para cambiar su formato a "#,##0.00".

Desplegamos el Autofiltro, y en una de las listas desplegables seleccionamos el valor que buscamos (1,000):

Finalmente editamos las celdas. Sin embargo, existe la posibilidad de que el valor que buscamos esté disperso en varias columnas, como en el presente ejemplo. Además, debemos editar las celdas una a una. No debemos editar toda la columna filtrada, ya que al editarla estaremos modificando también las celdas ocultas.

La mejor manera de lograrlo es utilizando el comando Buscar (Edición - Buscar... o Ctrl + B). Podemos aprovechar el hecho de que al seleccionar uno de los resultados de búsqueda, Excel selecciona también la celda respectiva. Retomando el ejemplo, procedemos como sigue:

Vamos a Edicion - Buscar...

Establecemos el valor a buscar (1,000) y damos clic en el botón Buscar todo.

Seleccionamos todos los resultados de búsqueda

y presionamos Esc para cerrar el cuadro Buscar.

Finalmente, procedemos a cambiar el formato. Formato - Celdas... (o Ctrl + 1), ficha Número, valor "#,##0.00", Aceptar (o clic dos veces en el botón Aumentar decimales de la Barra de herramientas formato).

El símbolo de promedio

2008-09-24T12:54:00.006-05:00

Recientemente, estuve trabajando con una columna que indicaba los promedios de ciertos valores. Dado que el título que utilizaba (así es, "Promedio") era sustancialmente más lago que los valores mostrados, quise reemplazar el título y poner únicamente el símbolo de promedio.

Los lectores seguramente sabrán que dicho símbolo es una "X", con una barra horizontal encima. Como consideré que este era un símbolo especial, ejecuté Insertar - Símbolo... para buscarlo.

Cuál va siendo mi sorpresa al descubrir que este símbolo no existe en este cuadro (gracias, Bill). Así que me puse a buscar otras formas de lograr mi objetivo. Lo primero que se me ocurrió fue escribir el caracter "_" y, en otro renglón de la misma celda (lo cual se logra con Alt + Enter, en modo edición) la letra "X". El resultado fue aceptable.

En su sitio Contextures, Debra Dalgleish propone esta solución:

1. Cambiar la fuente a Symbol.
2. Tipear el caracter de acento grave (`)
3. Cambiar la fuente a la que utilizaremos para la "X" y tipearla.

Aunque no obtuve buenos resultados en pantalla, Debra sostiene que la impresión sí debe verse bién.

Formato condicional avanzado III

2008-09-09T10:44:00.022-05:00

Continúa de la nota anterior.

Hasta ahora, solo hemos utilizado la opción "Valor de la celda" de la primera lista desplegable del cuadro Formato condicional. Como recordarán, la segunda opción de esta lista se llama Fórmula. Es con esta opción, con la que se pueden establecer condiciones de formato mucho más complejas y por lo tanto más útiles, ya que podremos valernos de cualquier función Excel integrada y/o referirnos a cualquier otra celda del libro.

Supongamos que tenemos una columna con fechas, y queremos aplicar formato a las que tengan una semana o menos de antigüedad. Seleccionamos entonces el rango, por decir A2:A150, damos Formato - Formato condicional... y seleccionamos la opción Fórmula:

En la sección Fórmula: escribimos:

=A2>(HOY()-7)

Cualquier fórmula que ingresemos en esta sección deberá ser, en realidad, una expresión lógica; es decir, una expresión cuyos únicos resultados posibles sean o VERDADERO o FALSO. En nuestro caso, si en la celda A2 tenemos la fecha del día de ayer, la fórmula devolverá VERDADERO. Y si tenemos una fecha de hace más de un mes, devolverá FALSO. Cada vez que el resultado de la expresión sea VERDADERO, Excel aplicará el formato que hayamos especificado.

Observemos ahora que la referencia que hacemos a la celda A2 es una referencia relativa. Al hacerlo, estamos forzando a Excel a ajustar las referencias al resto de las celdas (recordemos que hemos seleccionado un rango: A2:A150), tal y como sucede cuando copiamos una fórmula normalmente. De esta forma, si revisamos las condiciones de cada celda, veremos que son:

=A2>(HOY()-7)
=A3>(HOY()-7)
=A4>(HOY()-7)
...
=A150>(HOY()-7)

Por el contrario, si el caso fuera comparar todas las celdas contra la fecha de la celda B2, entonces utilizaríamos una referencia absoluta para la celda B2 (y relativa para A2):

=A2>$B$2

De esta forma, Excel ajustará las condiciones como sigue:

=A2>$B$2
=A3>$B$2
=A4>$B$2
...
=A150>$B$2

Para cambiar el tipo de referencia, se puede usar F4 o escribir directamente los signos $ en la fórmula. Téngase presente que la celda utilizada como primer argumento (A2) debe ser la celda activa. De lo contrario, Excel podría ajustar erróneamente las referencias.

Una vez comprendido lo anterior, las condiciones que pueden elaborarse para formatos condicionales solo están limitadas por la habilidad del usuario para elaborar expresiones lógicas. Algunos ejemplos:

=A2=MAX($A$2:$A$101). Formatea el valor máximo del rango.

=ESERROR(B2). Esta condición aplica formato a las celdas que contengan resultados de error en el rango. Puede utilizarse para ocultar dichos resultados, aplicando color de fuente blanco.

=CONTAR.SI($A$2:$A$100,A2)>1. Formatea valores duplicados en el rango. Para encontrar los no duplicados, cambiamos el signo > por =.

=ESTEXTO(A2). Señala los valores no numéricos en el rango.

=RESIDUO(FILA(),2). Formatea las filas del rango alternadamente. Por ejemplo, para colorear las filas impares.

=A1>PROMEDIO($A$2:$A$17). Muestra los valores numéricos que están por arriba del promedio del rango.

=RESIDUO(SUBTOTALES(3,$A$1:$A2),2). Formatea alternadamente las filas de un rango filtrado. Nótese la referencia mixta a la celda A2: columna absoluta, fila relativa.

Entre muchos otros. Toca al lector definir los formatos a aplicar.

Link.

Formato condicional avanzado II

2008-09-05T11:15:00.012-05:00

Continúa de la nota anterior.

Veamos ahora algunas consideraciones a tomar en cuenta al utilizar formatos condicionales.

Tal como vimos en la nota previa, podemos establecer un máximo de tres condiciones, cada una con su correspondiente formato. Las condiciones que establecimos fueron:

Valor de la celda igual a 0
Valor de la celda menor o igual que 30,000
Valor de la celda mayor o igual que 70,000

Cuando ninguna de las condiciones se cumpla (es decir, cuando el valor de la celda esté entre 30,001 y 69,999) Excel no aplicará ningún formato, manteniendo el formato original de la celda. Ahora bien, si hubiéramos aplicado condiciones incluyentes, es decir, condiciones que pudieran ser cumplidas simultáneamente por un mismo valor, entonces Excel aplicaría el formato de la primera condición satisfecha. Supongamos que trabajamos con las siguientes condiciones:

Valor de la celda igual a 10
Valor de la celda menor o igual que 30
Valor de la celda entre 25 y 40

Si ingresamos el valor 10, entonces se satisfarían las condiciones 1 y 2. Por tanto, Excel aplicará el formato de la condición 1. Si ingresamos el valor 28, entonces se satisfarían las condiciones 2 y 3. Por tanto, Excel aplicará el formato de la condición 2. Debemos determinar cuidadosamente el orden en que establecemos las condiciones, sobre todo si son incluyentes.

Cuando copiamos una celda (o rango) que no contiene formato condicional, y lo pegamos en una celda (o rango) que sí tiene formato condicional, Excel eliminará este sin advertencia alguna. No estaría mal un mensaje de alerta advirtiendo al usuario la posibilidad de perder los formatos. (Gracias Bill). En estos casos, si queremos conservar los formatos condicionales del rango de destino, debemos usar Pegado especial - Valores. Si, por el contrario, queremos conservar los formatos del rango de origen, usamos Pegado especial - Formatos, o simplemete, Pegar, ya que al copiar una celda, Excel copia también su formato condicional. Así pues, hay que tener cuidado al copiar y pegar de o en celdas formateadas.

Para borrar el formato condicional, abrimos el cuadro Formato condicional, y damos clic en el botón Eliminar... Selecionamos las condiciones que queremos eliminar y aceptamos el cuadro. Este cuadro siempre presenta cuadros de selección para tres condiciones, incluso si el rango solo contiene una o dos. Otro error de los genios de Microsoft. Alternativamente, podemos usar Edición - Borrar - Formatos. No obstante, esto eliminará también los formatos no condicionales del rango.

Si queremos seleccionar las celdas a las que hemos aplicado formato condicional, podemos usar el cuadro Ir a Especial. Damos Edición - Ir a... (o F5), clic en Especial...:

Activamos la opción Celdas con formatos condicionales. Tenemos dos opciones: para seleccionar todas las celdas con formatos condicionales, activamos las opción Todos. Para seleccionar solo aquellas celdas con los mismos formatos condicionales de la celda activa, seleccionamos Iguales a celda activa.

Habrá ocasiones en que surja la necesidad de usar referencias a otras hojas. Pero si indicamos la referencia directamente en el cuadro Formato condicional, Excel responderá con un mensaje de error, diciendo que esto es imposible.

Para solucionar esto, simplemente establecemos la referencia en una celda de la hoja en la que aplicaremos el formato, por ejemplo:

=Hoja2!A2

Luego, usamos esta celda en el cuadro Formato condicional. Alternativamente, podemos definir un nombre que haga referencia a la otra hoja, y escribir este nombre en dicho cuadro.

Continuamos en la siguiente nota.

Formato condicional avanzado

2008-07-03T18:07:00.016-05:00

El formato condicional es una de las características más útiles con que cuenta Excel. Desafortunadamente no es algo que el usuario normal aproveche al máximo. En efecto, es posible elaborar condiciones de formato que buena parte de los usuarios ni siquiera imagina. Conforme avancemos en este tema se irá haciendo cada vez más evidente el poder de esta característica.

Básicamente, el formato condicional nos permite aplicar determinado formato a las celdas, basado en el contenido de las mismas. Por ejemplo, podemos indicarle a Excel que nos resalte con color rojo celdas con valor igual a cero, con verde aquellas cuyo valor sea menor o igual a 30,000, y con azul las que tengan valores mayores o iguales a 70,000, ningún formato con cualquier otro valor. Este formato cambiará automáticamente según cambie el valor de las celdas analizadas. Así, podremos identificar fácilmente las celdas que cumplan con cualesquiera de las condiciones. Si nos tocó trabajar con una lista de 3,000 datos, es indudable la utilidad que pueden tener los formatos condicionales. Además del color de celda (tramas) podemos especificar color y tipo de borde y estilo y color de fuente, así como el tipo de subrayado.

Procedamos ahora a aplicar los formatos de nuestro ejemplo. Suponiendo que tenemos los siguientes datos:

Comencemos seleccionando el rango al cual aplicaremos el formato condicional (tercera columna). Ejecutamos Formato - Formato condicional... para abrir el siguiente cuadro:

Excel está listo para aceptar la primera condición ("Condición 1"). La primera lista desplegable solo tiene dos valores: "Valor de la celda" y "Fórmula". Seleccionamos el primero. Esta opción sirve para basarnos única y exclusivamente en el contenido de la celda a formatear. La segunda lista (la cual solo veremos si seleccionamos Valor de la celda) sirve para especificar el operador que utilizaremos para evaluar el contenido de la celda. Este operador puede ser uno de los siguientes ocho:

entre (predeterminado)

no está entre

igual a

no igual a

mayor que

menor que

mayor o igual que

menor o igual que

Lógicamente, si seleccionamos el primer o el segundo operadores, deberemos especificar dos valores, los límites entre, mientras que si seleccionamos cualquier otro operador, solo necesitaremos especificar un solo valor. Retomando nuestro ejemplo, seleccionamos "igual a" y en el cuadro de texto a continuación escribimos 0. El siguiente paso es especificar el formato que utilizaremos. Para ello damos clic en el botón Formato..., que nos lleva a este cuadro, versión modificada del cuadro Formato de celdas "normal":

De aquí podemos ver que el formato condicional no permite cambiar ni la protección ni la alineación ni el formato numérico de la celda.

Activamos la pestaña Tramas, seleccionamos el color rojo y damos Aceptar. Si queremos ver en este momento el efecto de esta primera condición, damos clic en Aceptar:

de otra forma damos clic en el botón Agregar>>, con lo cual Excel agrega otro apartado para que especifiquemos la Condición 2:

Para esta segunda condición (siempre según nuestro ejemplo), seleccionamos el operador menor o igual a en la segunda lista desplegable, y ponemos 30,000 en el cuadro de texto. Clic en el botón Formato..., pestaña Tramas y seleccionamos el color verde. Clic en Aceptar.

Solo nos resta especificar la tercera condición. Presionamos Agregar>> y repetimos los pasos anteriores, pero esta vez especificando los valores Valor de la celda, Mayor o igual a, 70,000, color azul, Aceptar:

Volvemos a dar clic en Aceptar y obtenemos:

Si cambiamos los valores de las celdas, veremos que el color de la misma se ajustará automáticamente de acuerdo a las condiciones que especificamos.

Continuamos en la siguiente nota.

La cámara fotográfica

2008-06-26T15:01:00.010-05:00

La cámara fotográfica de Excel permite tomar una "fotografía" de un rango de celdas y después insertarla en cualquier otra parte de la hoja o del libro, como si fuera una imagen cualquiera. La principal ventaja de esto es que nuestras fotografías (de 64 megapixeles...) serán dinámicas: cualquier cambio que ocurra en el rango de origen se verá reflejado en la imagen.

Para poder utilizar esta característica, primero debemos sacarla del olvido, personalizando las barras de herramientas. Procedemos como sigue:

Ponemos a Excel en modo Personalizar. Para ello, damos Herramientas - Personalizar. En el cuadro de diálogo Personalizar que aparece, vamos a la pestaña Comandos, seleccionamos la categoría Herramientas y buscamos el icono de la cámara (que se llama, así es, "Cámara"):

Finalmente arrastramos el icono hacia una de nuestras Barras de herramientas y damos clic en Cerrar.

Para empezar a tomar instantáneas, seleccionamos el rango que queremos fotografiar. Luego damos clic en el botón Cámara y seleccionamos con el mouse la zona donde queremos colocar la imagen:

Como sucede con cualquier imagen, podemos ajustar su tamaño y formato (clic derecho - Formato de imagen...):

Incluso, podemos girar la imagen. De lo único que debemos cuidarnos es de no colocar la imagen sobre el mismo rango fotografiado, ya que aparecería "eco" en la imagen:

Indudablemente esta es una herramienta útil. Pero sabiamente, Microsoft decidió ocultarla en el fin del mundo.

Gráficos velocímetro II

2008-06-24T12:36:00.018-05:00

Continúa de la nota anterior.

Procedamos a la parte difícil, la aguja de nuestro velocímetro. Esto se hace con un gráfico de dispersión XY (que es distinto a un gráfico de líneas). Para lograr que la aguja marque la proporción exacta en el disco (i. e. la proporción de circunferencia correcta), necesitamos convertir el alcance (siguiendo el ejemplo, 88%) en radianes para posteriormente, con las funciones SENO y COS, calcular las coordenadas x y y del punto correspondiente. Luego simplemente agregamos el punto (0, 0) para que Excel los una con la línea correspondiente.

Recordemos los datos a graficar y la tabla auxiliar que ya hemos hecho para la elaboración del disco:

Calculemos ahora cúantos grados debe medir cada punto porcentual. Para ello escribimos en la celda B8 la fórmula:

=(B3-D2)/(D5-D2)*PI()

Resultando 1.837571.

Ahora elaboramos en A10:C12 otra tabla auxiliar:

En B12 escribimos la fórmula:

=-COS(B8), y en C12:

=SENO(B8)

Con esto tenemos ya las coordenadas de los dos puntos que necesitamos: (0, 0) y (0.2636, 0.9646). El siguiente paso es copiar estas dos puntos en la gráfica. Para ello seleccionamos el rango A10:C12, y damos Edición - Copiar (o Ctrl + Insert). Seleccionamos los datos de la gráfica (el "disco") y damos Edición - Pegado especial... (no clic derecho - Pegado especial...). Excel mostrará el siguiente cuadro de diálogo:

Seleccionamos los valores mostrados y damos Aceptar. La gráfica queda:

Evidentemente no es lo que buscábamos. Seleccionamos la segunda serie de datos (la exterior), y damos clic derecho - Tipo de gráfico - Dispersión XY subtipo Dispersión - Aceptar.

Seleccionamos el Eje Y, clic derecho - Formato de ejes... Escala. En valor mínimo escribimos -1, en valor máximo 1 y en Eje de valores (X) cruza en: establecemos 0. Repetimos los mismo con el eje X:

Damos clic derecho en la nueva serie de datos, Formato de serie de datos... y en Tramas - línea seleccionamos Personalizada y el estilo, color y grosor preferido. En Marcador seleccionamos Ninguno. Aceptar.

Si queremos reducir el tamaño de la aguja entonces aumentamos la escala de ambos ejes. Finalmente eliminamos ambos ejes y sus etiquetas:

Para obtener los colores exactos de Sales Force, necesitamos modificar la paleta de colores de Excel, tema de otra futura nota.

Gráficos velocímetro

2008-06-13T14:03:00.013-05:00

Varias empresas utilizan los servicios de Sales Force (por cierto, uno de los peores softwares que he utilizado. Está a años luz de la flexibilidad y facilidad de uso que nos proporciona Excel. Realmente, en cuanto a confiabilidad de la información es absolutamente pésimo. A menos que el usuario tenga un PhD en bases de datos) para gestionar su información. Sales Force utiliza gráficos tipo velocímetro para mostrar alcances:

Excel no soporta de forma predeterminada este tipo de gráfico. Sin embargo, combinando un par de gráficas, podemos obtener algo prácticamente idéntico. Esto fue lo que logré:

Este gráfico es en realidad un gráfico tipo anillo de cuatro secciones (de las cuales una ocupa el 50% del mismo y no tiene color de línea ni de relleno), combinado con un gráfico de dispersión XY (cuyo origen está en el centro del gráfico y que tiene dos puntos, que al unirlos con una línea, forman la "aguja" del velocímetro), con los formatos adecuados. Seguimos los siguientes pasos:

Comencemos por elaborar la escala (el "disco") del velocímetro. Supongamos que queremos graficar los siguientes datos:

Si deseamos que la escala vaya de 0 a 150%, con divisiones de 50%, elaboramos la siguiente tabla auxiliar:

Es decir, del lado izquierdo tenemos las etiquetas que llevarán los segmentos, y, del lado derecho, los valores proporcionales de cada segmento. Como cada uno de los primeros tres segmentos miden lo mismo (50%), ponemos el mismo valor (0.5, puede ser cualquier otro) a cada uno. El cuarto segmento, como ocupará la mitad del "disco", tiene un valor igual a la suma de los tres primeros (1.5).

Seleccionamos la tabla e iniciamos el Asistente para gráficos. En Tipo de gráfico, seleccionamos Anillos, subtipo Anillos. Clic dos veces en Siguiente... para ir directamente al paso 3. Aquí, en la pestaña Título no ponemos nada, en Leyenda desactivamos Mostrar leyenda y en Rótulos de datos activamos la opción Categoría (sugeridos). Damos clic en Finalizar obteniendo el siguiente gráfico:

A continuación, damos clic derecho en el gráfico, Formato de Serie de datos... y vamos a la pestaña Opciones. En Ángulo del primer sector, establecemos 270, Aceptar.

Finalmente, seleccionamos la sección inferior del gráfico (150%), damos clic derecho, Formato de punto de datos... En la pestaña Tramas, en Borde y en Área seleccionamos ninguno. Movemos las etiquetas, y les damos formato. Quedando:

Continuamos en la siguiente entrada.

Como encontrar Duplicados

2008-05-24T20:03:00.004-05:00

Marcar Duplicados
Uno de los problemas mas frecuentes que nos encontramos los contadores en nuestro trabajo es tener duplicidad de elementos en una lista donde no debe de tenerlos.

Cuando estamos trabajando con una lista de unos cuantos elementos no hay problema, saltan a la vista, pero cuando la lista es de 5000 renglones y por algun motivo no es aconsejable ordenar estamos en problemas.

Para esto propongo usar el formato condicional

1.- Format-Contional Formating

2.- “Formula is” y escribir lo siguiente: “=COUNTIF($F$18:$F$22,F18)>1″Donde “$F$18:$F$22″ es el rango donde se pueden repetir los valores y “F18″ es la primera celda de ese rango (Noten que no tiene los signos “$”)

3.- En el boton “Format” agrega los formatos que sean de tu gusto y da click en “Ok” para finalizar
Si esto es de tu agrado y tienes que hacerlo continuamente tienes la opcion de usar VBA para automatizarlo, el codigo seria el siguiente:

Sub Mark_Duplicates()

Dim Rng_Cmplte As String

Dim Rng_Cell As String

Rng_Cmplte = Selection.Address

Rng_Cell_a = Range(ActiveCell.Address).Address

Rng_Cell_b = Replace(Rng_Cell_a, “$”, “”)

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ “=COUNTIF(” & Rng_Cmplte & “,” & Rng_Cell_b & “)>1″

With Selection

.FormatConditions(1).Font.Bold = True

.Italic = False

.ColorIndex = 3

End With

Selection.FormatConditions(1).Interior.ColorIndex = 6

End Sub

Posiblemente este codigo sea sujeto a perfeccionarse pero hasta ahora lo he usado frecuentemente sin ningun problema.

Se aceptan sugerencias, Leonel, algun comentario??

Autor invitado

2008-05-02T13:19:00.003-05:00

Agradezco la colaboración de Ricardo López, de IsSolved.com, quien publicó la nota anterior "Adiós a ese molesto Get Pivot Data".

Aprovecho la ocasión para comunicar a mis lectores que, en reciprocidad con Ricardo, estaré publicando algunas notas en su blog. Les comunicaré cuando así sea. Asimismo, si alguno de ustedes desea realizar alguna consulta, profesional o no, puede contactarnos a cualquiera de los dos. Confío en que al ser dos autores, mejore la calidad de las notas y consultoría ofrecidas.

Marcar máximos y mínimos en gráficas

2008-05-02T09:21:00.023-05:00

En la nota sobre gráficos anterior, aplicamos formato condicional a los valores máximo y mínimo de una gráfica de barras. Obviamente, aquella técnica no funciona si utilizamos una gráfica de líneas (o de dispersión XY). En estos casos, lo que podemos hacer es marcar y/o identificar los respectivos valores, como en el siguiente ejemplo:

Como en el caso anterior, buscamos que los marcadores sean dinámicos, es decir, que en todo momento señalen el valor máximo y el mínimo, sin tener que hacer ningún ajuste manual. Para lograrlo seguimos los siguienter pasos:

La siguiente es la tabla de datos original y su respectiva gráfica:

Agregamos dos columnas auxiliares a nuestra tabla de datos, como sigue:

La columna C muestra únicamente el valor máximo de la tabla, mientras que la D solo tiene el valor mínimo. En C2 tenemos la fórmula:

=SI(B2=MAX(B$2:B$11), B2, NOD())

Y en D2:

=SI(B2=MIN(B$2:B$11), B2, NOD())

A continuación agregamos las dos columnas (series) al gráfico. Clic derecho en la gráfica, Datos de origen... y cambiamos el rango a A1:D13. Aceptamos el cuadro.

En este punto la gráfica parece mostrar una sola serie de datos, luciendo exactamente igual que la gráfica original. Pero en realidad tenemos tres series de datos, de las cuales dos tienen un solo punto de datos que a su vez se superpone a algún punto de la primera serie. Para distinguirlas, necesitamos realizar los siguientes ajustes:

Seleccionamos la Serie 2 ("Max"). Dado que esto puede ser complicado, ya que está superpuesta en la Serie 1, podemos seleccionarla de la lista desplegable de la barra de herramientas Gráfico, la cual aparece automáticamente al seleccionar la gráfica:

o bién, seleccionamos cualquier otro elemento de la gráfica y pulsamos las teclas de dirección izquierda y derecha hasta que la mencionada lista desplegable muestre "Serie 2".

Clic derecho en la Serie 2 (o bién, tecla de menú contextual), clic en Formato de serie de datos...

En la pestaña Tramas, establecemos el marcador adecuado, y ajustamos estilo, colores y tamaño a nuestro gusto.

En la pestaña Rótulos de datos activamos Valor y Nombre de la serie (sugerido).

Repetimos estos pasos con la Serie 3 ("Min"), resultando la gráfica mostrada al inicio.

Cambiamos algunos valores de la tabla para ver que los marcadores se ajustan adecuadamente.

Como siempre, el lector sabrá mejorar los formatos utilizados.

Adios a ese molesto GetPivotData()

2008-04-20T01:29:00.014-05:00

Con frecuencia me cuestionan si las nuevas funcionalidades de Excel tienen la finalidad de hacer la vida mas facil o mas difícil a los usuarios… y mi respuesta casi siempre es que las nuevas caracteristicas tienden a hacer mas facil la vida de los usarios si estos se toman la molestia de pagar el precio de poner un poco de atención a los cuadros de dialogo y razonar sobre cual es la mejor manera de hacerlo.
El ejemplo clasico es la funcion GetPivotData que sin preguntar se activa cada vez que hacemos referencia a un valor que existe en una tabla pivote.
Esto no deja de tener algunas ventajas para un usuario experimentado, pero para la gran mayoría es un dolor de cabeza tener que lidiar con este favor no solicitado que nos hicieron los programadores de Microsoft.

La solucion es muy simple y muy poco conocida también siga estos pasos:
1.- En la barra de herramientas de clic derecho y vaya al menú “Customise” o “Personalizar”

2.- Busque en el tab “Commands” el menú “Data” y busque el comando “Generate Pívot Data”

3.- Arréstelo a la barra de herramientas de su preferencia y listo.

A partir de ahora esta listo para evitar esos molestos GetPivotData y solo utilizarlo cuando mejor le convenga.

Tal vez ahora se preguntara “mmmm y como para que es bueno el dichoso GetPivotData?” y claro que sirve, de hecho es una funcion que ahorra bastante tiempo y evita errores.

Pero eso sera la proxima vez.

Formato condicional en gráficas

2008-04-16T17:07:00.030-05:00

Una usauria me pregunta si es posible dar formato condicional a una gráfica de barras, de forma que la columna con el valor mínimo tenga color rojo, y la que tenga el valor máximo sea verde.

Suponiendo la siguiente tabla de datos:

Si hacemos una gráfica de columnas estándar, obtenemos lo siguiente:

Desde luego, se puede cambiar manualmente el color a los puntos (barras) máximo y mínimo de la serie. La desventaja es que, si los datos son cambiantes, habrá que estar coloreando las barras cada que esto ocurra. Lo que buscamos es un formato condicional, automático, que en todo momento de el color adecuado a cada columna.

Excel no tiene una forma directa de hacer este ajuste. De cualquier forma, podemos simular este efecto, llevando a cabo unos cuantos ajustes en nuestros datos y en nuestra gráfica.
Iniciamos aquí el uso de las series auxiliares o dummy series, imprescindibles para elaborar varios tipos de gráficas avanzadas. Comenzamos por añadir a nuestra tabla de datos tres columnas, como sigue:

En la celda C2 tenemos la fórmula:

=SI(B2=MAX($B$2:$B$13),B2,NOD())

En D2:

=SI(B2=MIN($B$2:$B$13),B2,NOD() )

Y en E2:

=SI(ESNOD(C2)*ESNOD(D2), B2,NOD())

Con estas fórmulas, logramos que la columna C muestre únicamente el valor máximo de la serie, la D el mínimo y la E todos los demás. Las celdas restantes muestran resultados de "error". El objetivo de estas fórmulas es crear una matriz de números y valores #N/A. Recordemos de notas anteriores que al graficar, Excel ignora los valores de error y los interpola de forma automática.

El siguiente paso es ajustar el rango de origen de la gráfica. Damos clic derecho en cualquier parte de la gráfica y seleccionamos Datos de origen... y en Rango de datos escribimos el rango:

=Hoja1!$A$1:$A$13,Hoja1!$C$1:$E$13

Es decir, ignoramos la columna B. Aceptamos el cuadro obteniendo la siguiente gráfica:

Observamos que los valores máximo y mínimo tienen colores diferentes. El efecto se logra ya que en realidad estamos graficando tres series de datos (de las cuales las dos primeras constan de un solo punto), pero formuladas de tal manera que aparentan ser una sola.

Podemos ver también que las columnas o series (que antes eran puntos de una sola serie) no aparecen correctamente centrados en sus respectivas categorías. Para solucionar esto, damos clic derecho en la gráfica, clic en Tipo de gráfico... y cambiamos el subtipo de gráfico a Columna apilada (¿por qué?). Obtenemos:

Por último, dí unos cuanto ajustes cosméticos: cambié el color del área de trazado a blanco, el tipo de las líneas de división a línea punteada, y el color de cada serie. Además cambié, en la escala del eje vertical (y), el valor de la división principal a 2 y el de la secundaria a 1. Eliminé el borde del área del gráfico y la subleyenda Valores de la leyenda principal. El resultado final:

Si en este momento cambiamos los valores, y cambia la posición de los valores máximo y mínimo, veremos que los colores se ajustarán en consecuencia.

Pie de página III

2008-04-09T12:21:00.015-05:00

Continúa de la nota anterior.

La tercera consulta es configurar Excel de forma que todo libro nuevo tenga pies de página en todas las hojas. Interesante.

La solución que propongo consiste en, primero, crear manualmete un libro que contenga pie de página en todas sus hojas. Después, guardarlo como plantilla en la carpeta de inicio de Excel, de forma que al inicializarlo se abra automáticamente. Posteriormente, crear una macro personal sencilla que abra esta plantilla. Luego, algo de personalización: asignar esta macro al comando Archivo - Nuevo, y asociarle el método abreviado Ctrl + u (utilizado por default por el comando Archivo - Nuevo). Para hacer esto aún más "invisible", guardaremos la plantilla con el nombre "Libro"; de esta forma al abrirla, Excel le cambiará el nombre a "Libro1" (cuando se abre una plantilla, Excel la convierte a libro normal y le agrega un consecutivo al final del nombre, conservando intacto el archivo original). Así pues, esta será la primera solución formal en Excel que desarrollemos.

El primer paso es abrir un libro nuevo (Ctrl + u) e insertar pies de página en las hojas, manualmente o utilizando la macro de la primera nota (sugiero que el libro tenga unas 6 hojas o más, para no tener que insertar otras después).

Posteriormente, damos Archivo - Guardar como... En nombre de archivo escribimos "Libro", y en Guardar como tipo: ponemos Plantilla. Guardamos en la ruta predifinida para plantillas o en cualquier otra ruta. Si queremos que Excel abra la plantilla al inicializarlo, guardamos una copia en la carpeta de arranque (XLSTART, pueden ubicarla realizando una búsqueda en Windows).

Ahora, la macro. Escribimos este código en un módulo del libro de macros personal:

Sub mimacro()

Workbooks.Open Filename:= _

-----"C:\Documents and Settings\leonel.quezada\Datos de programa _
-----\Microsoft\Plantillas\Libro.xlt"
End Sub

Cerramos el editor de Visual Basic. Obviamente, tienen que ajustar la ruta en la que han guardado el archivo en sus equipos.

A continuación asignamos el método abreviado Ctrl + u a la macro (utilizado actualmente por el comando Abrir - Nuevo...). Con Herramientas - Macro - Macros - clic en la macro - Opciones...

El siguiente paso es personalizar el comando Archivo - Nuevo. Para lograrlo, ejecutamos Herramientas - Personalizar... (Alt, h, z). Esto nos llevará al cuadro de diálogo Personalizar:

Con este cuadro abierto, damos clic en el menu de Excel Archivo, clic derecho en Nuevo... y clic en Asignar macro...:

En el cuadro Asignar macro seleccionamos la macro que hicimos (en el ejemplo, mimacro) y aceptamos el cuadro de diálogo. Cerramos el cuadro Personalizar.

En adelante, todos los archivos nuevos que abramos con Archivo - Nuevo... o con Ctrl + u, tendrán configurado el pie de página correctamente.

En lo personal, yo utilizaría la macro de la primera nota, ejecutándola cuando fuera requerido. Es la más sencilla de realizar y deja los archivos libres de macros. Mi segundo lugar en preferencias es la presente solución (además de que sirve para ejercitar otros temas). La desventaja es que si insertamos una hoja, ésta no contendrá pie de página. La opción de la segunda nota no la utilizaría ya que implica infestar de macros todos archivos en los que la usemos (consideremos que muchos usuarios simplemente ODIAN las macros, en algo que yo llamo macrofobia). Aparentemente lo mejor es agregar la macro de evento de la segunda nota a esta plantilla, pero esto también implicaría el uso forzoso de macros. En todo caso, la decisión corresponde al usuario final.

Pie de página II

2008-04-07T13:01:00.012-05:00

Continúa de la nota anterior.

Veamos ahora el segundo caso, agregar pie de página al insertar una hoja nueva. La única manera de lograr esto es escribiendo una macro dentro del libro mismo al que le estaremos insertando hojas. No podemos escribirla dentro del libro de macros personal, ya que no se trata de una macro "normal", sino de una macro de evento. Estas macros de evento se ejecutan automáticamente al realizarse determinada acción (el evento) por parte del usuario. En este caso, se trata del evento NewSheet. Todo el código que asociemos a este evento se ejecutará única y exclusivamente al insertar una hoja en el libro lo contiene. Es por esto que no podemos guardarlo en el libro de macros personal, ya que en ese caso solo se ejecutaría al insertar una hoja en este mismo libro de macros personal, lo cual nunca sucederá, ya que este libro solo lo utilizamos como contenedor de nuestras macros de uso general, además de que está oculto. Como dije al principio, ya habrá tiempo para profundizar en este tema.

Seguimos los siguientes pasos:

Asegúrense de tener abierto el libro al cual asignarán el código. Acto seguido, abrimos el editor de Visual Basic (Alt + F11). Identificamos el libro en el que queremos trabajar en el Explorador de proyectos y damos doble clic en él.

Abrimos la subcarpeta Microsoft Excel Objetos y doble clic en el objeto ThisWorkbook.

En la primera lista despleglable de la parte superior del Editor, seleccionamos Workbook, y en la segunda lista selecionamos NewSheet:

Con esto aparecerán en el Editor la primera y la última línea de código del evento NewSheet. En medio de estas dos líneas, escribimos el mismo código de la nota anterior, excepto la primera y la última líneas, debiendo quedar como sigue:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim ws As Worksheet

Dim nombre As String

Set ws = ActiveSheet

nombre = Application.UserName

---With ws.PageSetup

------.LeftFooter = "&11&Z&F"

------.CenterFooter = "&11&D"

------.RightFooter = "&""Arial,Normal""&11Elaboró: " & _

---------UCase(Left(nombre, 1)) & Mid(nombre, 2, InStr(1, nombre, ".") - 2) _

---------& " " & UCase(Mid(nombre, InStr(1, nombre, ".") + 1, 1)) & _

---------Mid(nombre, InStr(1, nombre, ".") + 2)

---End With

End Sub

Finalmente, hacemos una prueba. Dado que esta es una macro del evento NewSheet, simplemente insertamos una hoja en el libro. Si lo hicieron correctamente, pueden comprobar que ya contiene pie de página.

Finalizamos en la siguiente nota.

Pies de página autómaticos

2008-04-02T17:50:00.007-06:00

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

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

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

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

Sub piepagina()

Dim ws As Worksheet
Dim nombre As String

Set ws = ActiveSheet
nombre = Application.UserName

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

End Sub

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

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

Continuamos en la siguiente nota.

Navegar en el libro - tip

2008-04-01T15:10:00.010-06:00

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

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

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

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

La función NOD

2008-03-27T13:44:00.017-06:00

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

NOD()

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

=NOD() devuelve #N/A.

También es válido escribir #N/A directamente en las celdas. Esta función se proporciona por compatibilidad con otros programas para hojas de cálculo.

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

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

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

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

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

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

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

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

La tabla quedaría:

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

La ayuda on-line no aporta nada extra.

El libro de macros personal

2008-03-24T12:50:00.010-06:00

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

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

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

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

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

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

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

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

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

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

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

La función DESREF

2008-03-19T13:27:00.005-06:00

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

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

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

La ayuda de Excel proporciona la siguiente definición:

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

La sintaxis es:

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

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

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

En el siguiente ejemplo:

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

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

Otros ejemplos:

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

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

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

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

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

Link

Habilitar macros

2008-03-18T13:06:00.006-06:00

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

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

Vamos a Herramientas - Macro - Seguridad...:

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

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

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

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

Link

Gráficas combinadas

2008-03-14T16:05:00.009-06:00

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

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

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

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

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

Link.

Nombres en Excel II

2008-03-13T17:26:00.016-06:00

Continúa de la nota anterior.

Mencioné en la nota anterior que para utilizar un nombre en una fórmula, presionamos F3 en el modo edición. Continuando con la tabla del ejemplo anterior:

Para calcular las suma de las ventas anuales de los sectores norte y occidente, nos situamos en una celda vacía y presionamos el signo = seguido inmediatamente de la tecla F3. De este modo Excel nos muestra su cuadro de diálogo Pegar nombre:

Seleccionamos "norte" y damos clic en Aceptar, o damos doble clic en el nombre. La fórmula queda:

=norte

Ahora escribimos el signo + y repetimos los pasos anteriores, pero esta vez seleccionando el nombre "occidente", quedando:

=norte+occidente

Damos clic otra vez para aceptar la fórmula. Excel nos mostrará el resultado buscado.

Veamos ahora las "reglas" que debemos seguir a la hora de definir o crear un nombre. Los nombres:

1. No pueden contener espacios, aunque sí tenemos permitido usar el guión bajo (_), y la comilla ('). Cualquier otro símbolo o caracter especial no está permitido.

2. Pueden tener una extensión máxima de 255 caracteres. Sin embargo, es recomendable tenerlos lo más cortos posibles.

3. No pueden ser valores, ni pueden comenzar con un número, pero sí pueden incluir números a partir del segundo caracter. Derivado de esto, al crear un nombre, debemos tomar en cuenta lo siguiente: si la fila o columna que queremos usar como rótulos para los nombres, contiene un valor numérico, Excel no creará ningún nombre para la fila o columna correspondiente. Además, no nos alertará de esta situación.

Hemos visto como nombrar un rango o una fórmula. Sin embargo (y no sé cómo se me pasó comentarlo), también es posible nombrar constantes y constantes matriciales. Si por ejemplo, queremos usar cierta discreción al distribuir determinados datos, podemos crear un nombre que almacene estos datos y después usar este nombre en una fórmula, p. ej.: =sueldoa*factor. Aunque estamos de acuerdo que el nivel de confidencialidad es mínimo. Cualquiera que sepa abrir el cuadro Definir nombre podrá averiguar el valor de los nombres.

(En realidad, lo único que podemos nombrar en Excel son fórmulas. Si revisamos con atención nuestra lista de nombres veremos que, en todos los casos, el valor de Se refiere a: comienza con el signo =. En Excel, cualquier cosa que comienza con el signo = es una fórmula. Sin embargo, es más conveniente diferenciar entre rangos, fórmulas y valores nombrados).

Un detalle a tener en cuenta es que en el cuadro de nombres solo aparecen los nombres de rangos. Para conocer los valores de fórmulas o constantes nombradas deberemos ir al cuadro Definir nombre.

Casi hemos terminado con este tema. Para alcanzar el dominio completo del mismo, solo nos falta comprender el tema de los nombres (o rangos) dinámicos...

Link

Nombres en Excel

2008-03-11T09:53:00.025-06:00

A las cosas hay que llamarlas por su nombre. Esto es especialmente cierto cuando queremos comunicar algo con absoluta claridad, sin dejar lugar a la ambigüedad. En Excel esto también es muy importante. Quien desee llegar a elaborar fórmulas elegantes, cortas y legibles (así como dominar otras características avanzadas), por fuerza debe dominar el tema de los nombres.

Trabajar con direcciones de celda o de rango en una fórmula, puede, y de hecho es, confuso. Para evitar esta confusión, podemos simplemente nombrar estas direcciones o subfórmulas. Un nombre es simplemente un alias que le ponemos a un rango, o a una fórmula. Por ejemplo, en la siguiente tabla:

Para calcular las ventas totales del sector Norte, usamos la fórmula:

=SUMA(B2:B13)

Ahora, definamos un nombre, digamos "vtas_norte" al rango B2:B13. Para ello, simplemente seleccionemos dicho rango, y en la barra de nombres, situada a la izquierda de la barra de fórmulas,

escribimos "vtas_norte" (sin comillas) y damos Enter.

Una vez que hemos definido nuestro nombre, podemos sustituir la fórmula anterior por la siguiente:

=SUMA(vtas_norte)

Esta es una fórmula más clara que la anterior ¿No hay gran diferencia? Consideren entonces esta fórmula con la cual me topé recientemente:

=MIN(S9,U9)*0.7+BUSCARV(B27,B2:C17,8,FALSO)*BUSCARV(AG27,D2:D17,3,VERDADERO)*(1-T27)+SI(RESIDUO(REDONDEAR.MAS(MES(B4)/3,0), 3)=0, SUMA(AO9:AY9)-AI9-AJ9)

Pueden imaginar la cantidad de tiempo que perdí descubriendo que es lo que hace esta fórmula. Si el autor hubiera utilizado nombres, entonces la fórmula podría haber quedado de esta manera:

=anticipo+comision_renovacion+SI(es_fin_trimestre, pendiente-descuentos)

Además de mejorar la legibilidad, el escribir nombres en lugar de rangos de celdas, hace menos propensas a errores nuestras fórmulas. Otra de las ventajas es que podemos ir rápidamente a un rango nombrado seleccionando su nombre correspondiente en la barra de nombres.

En Excel, podemos definir o crear un nombre. La diferencia es que al definir podemos dar el nombre que queramos al rango. Al crear nombres, podemos usar texto existente (por ejemplo, encabezados de columna) como nombres. Para definir un nombre, seguimos el procedimiento indicado al principio, o bién, seleccionamos el rango en cuestión, y damos Insertar - Nombre - Definir (o Ctrl + F3), lo que nos lleva al diálogo Definir nombre:

En el cuadro Nombres en el libro, escribimos el nombre que elijamos. En el cuadro Se refiere a: nos cercioramos que el rango sea el correcto. En caso de que queramos seguir definiendo nombres, damos clic en el botón Agregar; en caso contrario, damos Aceptar. Si lo que queremos es nombrar una fórmula, solo podemos hacerlo a través de este cuadro (ya no como al principio). Para ello, en el cuadro Se refiere a: escribimos la fórmula correspondiente.

Para crear un nombre, seleccionamos el rango que queremos nombrar, junto con el rango que queremos usar como rótulos de nombre. Si usamos la tabla de al principio, seleccionamos el rango A1:E13. A continuación, vamos a Insertar - Nombre - Crear...:

Indicamos en donde están los rótulos que queremos utilizar. En nuestro caso, seleccionamos Fila superior (y columna izquierda, si lo deseamos) y aceptamos el cuadro de diálogo. Con esto, se habrán creado cuatro nombres: Norte, Sur, Occidente y Oriente (mas otros doce, uno por mes, si seleccionamos Columna izquierda).

Para utilizar nuestros nombres en fórmulas, en modo Edición presionamos F3 y seleccionamos el nombre deseado, o lo escribimos directamente en la fórmula.

Continuamos en la siguiente nota.

Link

Monster formulas are not cool

2008-03-03T11:34:00.015-06:00

Usualmente, conforme vamos progresando en el conocimiento y dominio de funciones en Excel, tenemos el pleno convencimiento de que elaborar fórmulas sumamente complejas y enormes es una forma de demostrar el "gran nivel" que tenemos en Excel. Craso error. Ya hemos hablado anteriormente de lo difícil y francamente doloroso que puede ser el analizar y entender una de estas monster formulas. Adicional a esto, si vamos a compartir nuestro archivo con otra u otras personas más, no hay absolutamente ninguna necesidad de complicarles ni complicarnos la existencia. Tampoco hay razón para perder tiempo explicando cómo funcionan o qué hacen nuestras fórmulas. Veamos por ejemplo, esta fórmula:

=SI(Y(ESNOD(COINCIDIR(D1559,Cuentas!$B$2:$B$924,0)),ESNOD(COINCIDIR(D1559,Cuentas!
$D$2:$D$698,0))),D1559,SI(ESNOD(COINCIDIR(D1559,Cuentas!$B$2:$B$924,0)),BUSCARV(
D1559,Cuentas!lista2,2,FALSO),BUSCARV(D1559,Cuentas!lista1,2,FALSO)))

Tratar de interpretar esta fórmula puede resultar más aburrido que bailar entre hermanos.

Es por esto que resulta una mucho mejor práctica utilizar unas cuantas fórmulas intermedias, que combinarlas todas en una ilegible y poco flexible fórmula jurásica. Excepto si utilizamos más de 256 columnas o más de 65 536 filas en nuestra hoja, lo cual es poco problable, agregar unas cuantas filas o columnas más no debe representar mayor problema. Si bien es cierto que las mega fórmulas reducen el tiempo de cálculo de la hoja (y del libro), para la gran mayoría de modelos esta diferencia no es relevante. Una vez que hallamos terminado de editar las fórmulas, algo que incluso puede ser más rápido que elaborar una fórmula jurásica, podremos ocultarlas para evitar saturar visualmente nuestro modelo.

Si además de lo anterior, no resultara cómodo leer los cálculos, es porque necesitamos ser un poco más organizados. Una hoja hoja de cálculo debe estar organizada de forma tal que queden delimitadas, lo más claramente posible, las áreas de resumen de información, datos de entrada, cálculos y tablas de información. Las dos primeras, resumen y datos de entrada, deben estar en la parte superior de la hoja, después nuestra tabla de cálculo y al final las tablas de datos. En la tabla de cálculo, las fórmulas deben fluir de izquierda a derecha, de arriba hacia abajo. En otras palabras, una fórmula solo debe refererirse a celdas situadas a la izquierda o por arriba de ella.

Otra posibilidad es destinar una hoja exclusivamente para cálculos y después, en una hoja de resumen, referirnos a los resultados que nos interesen. Una vez comprobadas las fórmulas, podemos ocultar esta hoja. Mejor aún, podríamos agrupar las hojas de nuestros libros en resumen, cálculos, tablas de valores y al final datos de entrada. Solo dedicaremos tiempo a la apariencia de la hoja de resumen, las demás hojas tendrán un formato sencillo (o inclusive ningún formato) para no saturar el libro.

De esta forma podemos aumentar la legibilidar y elegancia de nuestros libros. Y podríamos mejorar aún más la legibilidad utilizando nombres y fórmulas nombradas, pero eso ya es otra historia...

El extraño caso del vínculo fantasma

2008-02-27T12:17:00.008-06:00

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

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

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

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

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

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

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

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

Caso cerrado.

Desvincular una gráfica de sus datos de origen

2008-02-22T17:22:00.006-06:00

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

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

Supongamos la siguiente gráfica:

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

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

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

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

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

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

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

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

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

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

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

It's friday...

2008-02-15T10:46:00.002-06:00

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

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

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

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

It's friday...

2008-02-15T10:30:00.001-06:00

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

St. Peter ask:

-Have you ever done anything of particular merit?

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

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

St. Peter was impressed.

-When did this happen?

-Couple of minutes ago.

Resolver una fórmula paso a paso

2008-02-14T10:29:00.017-06:00

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

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

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

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

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

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

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

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

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

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

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

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