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 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 "independiente" 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.
Validación de datos dependiente
Generar un número aleatorio - Tip
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
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).
Tipos de errores
Al estar depurando alguna fórmula, es posible que obtengamos un resultado de error, 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) develve #NOMBRE! (¿Por qué?). 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.
#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.
La entrevista en español
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 presentaciones. Después, utilizar fórmulas para jalar los datos en la presentación. (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 comando 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* deseo 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
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.
Dividir en múltiplos de 10
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.
Functions list
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
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
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.
Resultado inesperado
¿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:
=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
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í.
Adios a ese molesto GetPivotData()
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.
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. La ayuda de Excel dice:
"Devuelve el valor de error #N/A, que significa "no hay ningún valor disponible". Utilice #N/A para marcar las celdas vacías. Si escribe #N/A en las celdas donde le falta información, puede evitar el problema de la inclusión no intencionada de celdas vacías en los cálculos. (Cuando una fórmula hace referencia a una celda que contiene #N/A, la fórmula devuelve el valor de error #N/A.)
...
Debe incluir paréntesis vacíos con el nombre de la función. De lo contrario no se reconocerá como función.
También puede escribir el valor #N/A directamente en la celda. La función NOD 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.
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
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.
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, eso ya es otra historia...
Resolver una fórmula paso a paso
Frecuentemente, cuando trabajamos con fórmulas complejas, necesitamos tener un altísimo grado de concentración. El tratar de comprender o modificar una fórmula larga y compleja (sobre todo si no la hicimos nosotros), puede llegar a ser un proceso altamente frustrante, más aún si no utilizamos nombres. Consideremos la siguiente fórmula:
=SI(K9>=$B$69;$D$69;SI(K9<=$B$66;K9*BUSCARV(K9;$B$62:$C$69;2;1);(K9-1)*BUSCARV(K9;$B$62:$C$69;2;1)+1))
Por ello, es importante saber como ejecutar parcialmente una fórmula, es decir, resolviendo primero las subfórmulas interiores hasta llegar a la función más externa, para comprender más fácilmente que es lo que hace determinada fórmula.
Excel nos brinda dos opciones: la primera de ellas consiste en ejecutar las cálculos parciales directamente en la barra de fórmulas. Para ello, selecionamos con el cursor la fórmula exacta que queremos ejecutar (ni un paréntesis menos o más), en la barra de fórmulas, y presionamos F9. Siguiendo con nuestro ejemplo, seleccionaremos la fórmula más interna, es decir, el segundo BUSCARV. Desde la "B" hasta el penúltimo paréntesis de la fórmula general.
Después de presionar F9, vemos, en la barra de fórmulas, que el BUSCARV se ha convertido en su valor resultante. De acuerdo a los números que manejo en mi libro, el resultado de dicho BUSCARV es 1.68:
=SI(K9>=$B$69;$D$69;SI(K9<=$B$66;K9*BUSCARV(K9;$B$62:$C$69;2;1);(K9-1)*1.68+1))
Hacemos lo mismo con el primer BUSCARV, que en este caso es exactamente igual al segundo:
=SI(K9>=$B$69;$D$69;SI(K9<=$B$66;K9*1.68;(K9-1)*1.68+1))
Evidentemente la fórmula se vuelve un poco más manejable al ser más corta. Para terminar el proceso, debemos recordar presionar Esc para dejar la fórmula como al principio, ya que si presionamos Enter, las fórmulas se habrán convertido permenentemente en sus valores.
La segunda opción que tenemos es utilizar el Evaulador de fórmulas de Excel. Para acceder a él, ejecutamos, después de seleccionar la fórmula de interés, Herramientas - Auditoría de fórmulas - Evaluar fórmula. Lo que veremos es:
Vemos que Excel ha subrayado el primer valor o fórmula a evaluar (K9, siempre según el ejemplo). Aquí tenemos dos opciones. Si presionamos el botón Evaluar, Excel convertirá K9 a su valor resultante. En cambio, si presionamos Paso a paso para entrar, el cuadro mostrará la fórmula de K9 (en caso de que haya una fórmula en K9), y así sucesivamente hasta llegar a los valores de origen.
Esto es especialmente útil al momento de auditar un modelo.
Otra opción es agregar una o varias columnas auxiliares, cada una con la correspondiente subfórmula, tema de otra nota.
Calcular el número de trimestre
En la empresa en la que trabajo, se maneja un cierre de ventas trimestral, además de otro cierre mensual. Así que para algunos de nosotros es importante tener correctamente formulado el Q (así se dice trimestre aquí. Es una empresa cosmopolita...), al que pertenece una fecha determinada.
La fórmula que utilizo para calcular el trimestre al que corresponde una fecha es la siguiente:
=REDONDEAR.MAS(MES(A2)/3, 0)
Donde A2 es la fecha de interés. REDONDEAR.MAS redondea siempre hacia arriba el valor especificado en el primer argumento, con el número de decimales especificado en el segundo argumento.
Alternativamente, podemos usar también:
=ENTERO((MES(A2)+2)/3)
Lo cual es más corto, y por lo tanto, mejor.
ENTERO simplemente devuelve la parte entera de un número con decimales.
Prescindiendo de la función SI
La función SI es una de las funciones más utilizadas e intuitivas de Excel. Sin embargo, cuando usamos fórmulas con varios SI anidados, pueden llegar a volverse extraordinariamente confusas y "enredadas". Además, tenemos la limitante de solo poder anidar hasta un máximo de siete SIs.
Existen varias maneras de expresar situaciones lógicas que prescinden de la función SI que vuelven más legibles y fácilmente modificables nuestras fórmulas.
Frecuentemente se nos presentan problemas de este tipo: Dado cierto valor, si este es menor a cero, lo ignoramos; si es mayor, hacemos algún cálculo con él. Se vuelve completamente irresistible pensar en la función SI para formular esto. Por ejemplo:
=SI(A2<0,0,A2/G1)
Es más corto y legible utilizar la función MAX (que devuelve, lógicamente, el máximo de un grupo de valores [máximo 30]):
=MAX(0, A2/G1)
Gráficas sin gráficas
Dentro del amplio repertorio de funciones de texto de Excel, encontramos la función REPETIR. El valor devuelto es simplemente una cadena de texto dada, repetida un número especificado de veces. La sintaxis es sencilla:
=REPETIR(texto,núm_de_veces)
En el siguiente ejemplo,
tenemos esta fórmula en B2:
=REPETIR(A2,5) . En B3 tenemos: =REPETIR(A3 & " ",5)
Si queremos vernos "creativos" podemos usar una fuente Wingdings:
Al usar la imaginación y el caracter de barra vertical como texto a repetir:
el resultado semeja una barra de una gráfica de barras horizontal. De esta forma podemos usar la fórmula para simular una gráfica de barras básica y que ocupa mucho menos espacio en disco que una gráfica normal. Lo único que hay que hacer es utilizar los valores que queremos graficar como segundo argumento de REPETIR. En caso de que las "barras" sean muy cortas o largas ajustamos el argumento núm_de_veces con un factor adecuado.
Otras ideas son usar formato condicional o alinear a la derecha valores negativos y positivos a la izquierda, simulando un histograma.
La fórmula en B2 es:
=ENTERO(A2) & " " & REPETIR("-", A2/10)
con valores alineados a la derecha. En C2 tenemos:
=REPETIR("-", A2/10) & " " & ENTERO(A2)
y alineación a la izquierda. Si no encontramos el caracter de barra vertical en el teclado, escribimos:
=ENTERO(A2) & " " & REPETIR(CARACTER(124), A2/10); y
=REPETIR(CARACTER(124), A2/10) & " " & ENTERO(A2)
En esta página de juiceanalytics podemos encontrar otros varios ejemplos de como usar esta técnica.
Combinar BUSCARV y COINCIDIR
Como ya somos expertos en el uso de BUSCARV I, II y III, estamos en la posibilidad de empezar a usarla en combinación con otras funciones, por ejemplo, COINCIDIR.
COINCIDIR nos devuelve la posición de determinado valor dentro determinado rango (matriz) de datos, contando de izquierda a derecha o de arriba a abajo, comenzando en 1. Dicho rango o matriz de datos debe ser unidimensional, es decir, de una sola fila o de una sola columna. La sintaxis es:
COINCIDIR(valor_buscado,matriz_buscada,tipo_de_coincidencia)
En el siguiente ejemplo:
utilizamos la función para saber el turno de atención de un grupo de pacientes de un neurocirujano. La fórmula en E2 es: =COINCIDIR(E1,A2:A11,0)
Si seleccionamos un rango de más de una fila o columna, obtendremos el resultado de error #N/A. Forzosamente nuestro rango de búsqueda debe ser unidimensional. El último argumento, tipo_de_coincidencia, indica a Excel si debe buscar una coincidencia exacta o aproximada.
Retomando nuestro tema, consideremos el siguiente resumen de gastos:
Si queremos conocer lo que gastamos en teléfono en febrero de 2006, escribiremos esta fórmula, digamos que en B4:
=BUSCARV("telefono",A7:G19,3,FALSO)
Si queremos la cantidad de marzo, modificaremos la fórmula de esta manera:
=BUSCARV("telefono",A7:G19,4,FALSO)
Como no resulta práctico el estar modificando una y otra vez una fórmula hacemos las siguientes modificaciones a nuestro modelo:
Creamos en B3 una lista desplegable utilizando Datos - Validación... - Lista, con los encabezados de mes como argumentos:
Luego, en nuestra fórmula de B4, modificamos el tercer argumento de BUSCARV (columna) y lo formulamos utilizando COINCIDIR y la celda B3:
La fórmula de B4 es:
=BUSCARV("telefono", A7:G19, COINCIDIR(B3,B6:G6,0)+1, FALSO)
Ahora, para saber lo gastado en telefono en cada mes simplemente lo seleccionamos de la lista.
Por último, creamos otra lista desplegable en A4 utilizando los conceptos de gasto como argumentos de la lista. En la fórmula, sustituimos "telefono" por A4. De esta manera obtenemos una lista cruzada de valores "dinámica":
