La función NOD

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

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

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

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

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

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

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

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