Cómo generar números aleatorios con Excel

La función ALEATORIO (RAND en la versión inglesa) de Excel genera números aleatorios. Esta función no tiene argumentos y es recalculada nuevamente cada vez que se produce un cambio en la hoja, excepto que el modo de cálculo sea "manual".
Los números que produce ALEATORIO (RAND) van de del 0 al 1.
En esta hoja usamos la fórmula =ALEATORIO() en el rango A2:A11




Si queremos producir una serie de números aleatorios enteros, debemos combinar la función ALEATORIO con la función REDONDEAR (ROUND), o con alguna otra función de este tipo como REDONDEAR.MAS o REDONDEAR.MENOS.
En esta hoja, usamos la fórmula combinada =REDONDEAR(ALEATORIO()*100,0) para producir números aleatorios entre 0 y 100



Si queremos producir números aleatorios que se encuentren en un rango entre dos números, digamos entre 12 y 88, podemos usar la fórmula

=REDONDEAR(ALEATORIO()*(88-12)+12,0)


Con esta técnica podemos generar, por ejemplo, números aleatorios para la lotería. Aquí, por ejemplo, el Loto sortea 6 números entre el 1 y el 36. El problema de esta fórmula es que puede generar números duplicados, como en este ejemplo



Supongo que hay más de una técnica para superar este problema. Mencionaré aquí dos de ellas.
La primera es, simplemente, recalcular la hoja (presionar F9) hasta lograr una serie sin números repetidos.
La otra consiste en condicionar la creación de los números aleatorios. Esta técnica consiste en generar una referencia circular intencional, para lo cual es necesario que la opción Iteración en Opciones/Cálculo esté activada (pueden bajar el archivo del ejemplo con las fórmulas aqui)



La fórmula que usamos en el rango A2:A11 es

=SI(SUMA($B$2:$B$11)<>10,REDONDEAR(ALEATORIO()*(36-1)+1,0),REDONDEAR(ALEATORIO()*(36-1)+1,0))

en el rango B2:B11 usamos la fórmula =CONTAR.SI($A$2:$A$11,A2). Esta fórmula controla si hay números duplicados. Si el resultado es 1, el número aparece una sola vez.



La fórmula en el rango A2:A11 controla si todos los resultados en el rango B2:B11 no suman 10, es decir que hay algún numero duplicado; en ese caso calcula un número aleatorio. Si la condición lógica no se cumple, vuelve a calcular. Como ven hay aquí una referencia circular. Como hemos activado la opción "Iteración", Excel intentará encontrar la serie de 10 números que cumplan con la condición. Si no la encuentra, deberemos pulsar nuevamente F9 (recalcular) hasta encontrar la solución.

Esta técnica fue sugerida por John Walkenbach en su libro "Microsoft Excel 2000 Formulas".



Categorías: Funciones&Formulas_, Varios_

Técnicas para combinar funciones en Excel

La capacidad de combinar funciones en una fórmula (nesting en inglés) es una de las cualidades más importantes en Excel. Por ejemplo, en esta nota sobre como evitar valores #N/A, utilizamos esta combinación:

=SI(ESERROR(BUSCARV(D8,Lista_1,2,0))," ",BUSCARV(D8,Lista_1,2,0))

En este caso combinamos tres funciones en una misma fórmula, de manera que si el resultado de la función BUSCARV (VLOOKUP) da un resultado de #N/A, lo que es evaluado por la función ESERROR (ISERROR), la función SI (IF) dará como resultado una celda en blanco, y en caso contrario efectuará la búsqueda.

Este proceso de combinar funciones puede ser engorroso y frustrante si no conocemos bien la sintaxis de las funciones a usar. En esta nota explicaré dos técnicas para facilitar la combinación de funciones.

La primera técnica la llamaremos "Vamos por partes". Esta técnica consiste en escribir cada función en forma independiente, en celdas distintas, y luego combinarlas en otra celda.
Veamos esto aplicado al ejemplo de la nota sobre como evitar #N/A. En la tabla Lista 2 aplicamos la combinación de funciones mostrada más arriba.

Esta combinación la podemos desintegrar en tres funciones como muestro aquí:



La fórmula en la celda F3 es =ESERROR(G3); en la celda G3 es =BUSCARV(D3,Lista_1,2,0) y en la celda H3 =SI(F3="FALSO"," ",G3). Esta última es equivalente a la formula en la celda E3 =SI(ESERROR(BUSCARV(D3,Lista_1,2,0))," ",BUSCARV(D3,Lista_1,2,0))
La ventaja de esta técnica reside en que nos permite controlar el funcionamiento de cada función por separado. La desventaja de esta técnica es que crea planillas complicadas con muchas interdependencias entre celdas.

La técnica que mostraré ahora nos permitirá crear fórmulas compactas con la ayuda del asistente "insertar función"



La fórmula debe ser construida de "afuera hacia adentro", es decir que en nuestro ejemplo empezaremos por la función SI (IF)

Nos ubicamos en la celda dónde queremos escribir la fórmula y activamos el asistente de insertar función.



Elegimos la función SI (IF). En la ventanilla de "prueba lógica" queremos escribir la función ESERROR (ISERROR), pero supongamos que no conocemos su sintaxis. Si pulsamos nuevamente el asistente de funciones todo lo que lograremos es cerrar el diálogo. Lo que debemos hacer es apretar la flecha en el cuadro de nombres (en el ángulo superior izquierdo).
Al hacerlo se abrirá una lista de funciones y la opción "más funciones".



Si la función que buscamos no aparece en la lista pulsamos la opción "más funciones". Esto abrirá una nueva instancia del asistente de funciones.



Aquí buscamos la función ESERROR (ISERROR) lo que a su vez abrirá el asistente de esta función.



Nuevamente apretamos la flecha en el cuadro de nombres para ver la lista de funciones, y seleccionamos BUSCARV (o "más funciones" si ésta no aparece).



Como pueden ver en la barra de funciones, Excel va escribiendo la fórmula en el orden adecuado.
Completamos los datos de BUSCARV y apretamos "aceptar".



Aquí recibiremos un mensaje de error, ya que Excel se ha "olvidado" que estamos escribiendo una fórmula.



Apretamos "aceptar" y Excel abrirá la fórmula en la barra de fórmulas. El cursor se encuentra en el lugar de la fórmula desde donde debemos seguir, así que escribimos "," para introducir el resultado de la primera condición lógica.



A continuación escribimos otra "," para introducir la segunda condición lógica de la función SI. Volvemos a pulsar la flecha en el cuadro de nombres, elegimos la función BUSCARV (VLOOKUP), completamos los datos

Y pulsamos "aceptar". En la celda E13 tenemos ahora la fórmula combinada.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: Combinar Funciones, Funciones Excel, #N/A, VLOOKUP, BUSCARV, ISERROR, ESERROR

Categorías: Funciones&Formulas_

Cómo evitar resultados #N/A en fórmulas de Excel

Cuando Excel no logra resolver una función, da como resultado #N/A (del inglés: not available). Hay situaciones en las cuales este resultado es aceptable, es decir, no hay solución, pero no quisiéramos que aparezca en la planilla.
Hay varias formas de evitar que este resultado sea visible. Una de ellas ya la he mostrado en esta nota. Sencillamente seleccionamos los resultados #N/A con Ir A Especial, y borramos el contenido de las celdas seleccionados (Ctrl + Enter).

Otra forma es construir nuestra fórmula de tal manera que Excel no dé como resultado #N/A.

Veamos este ejemplo. Tenemos aquí dos listas, una con todos los nombres y una segunda con algunos nombres de los cuales queremos averiguar la edad

Si aplicamos la función BUSCARV (VLOOKUP) en nuestra fórmula, obtendremos dos valores #N/A en la tabla 2



Como comentario aparte, pueden ver que he utilizado un nombre (Lista_1) para señalar el rango de búsqueda. Siempre recomiendo esta técnica, como ya lo he hecho en esta nota.

Volviendo a nuestro tema, como podemos evitar estos resultados?

A la fórmula =BUSCARV(D3,Lista_1,2,0) le agregamos una condición, tal que si el resultado esperado es #N/A, la fórmula dé como resultado un blanco.
Rescribimos nuestra fórmula de esta manera:

=SI(ESERROR(BUSCARV(D8,Lista_1,2,0))," ",BUSCARV(D8,Lista_1,2,0))

El resultado será el siguiente

Nuestra fórmula funciona de la siguiente manera:
La función SI (If en la versión inglesa) evalúa en primer lugar el resultado de la función ESERROR (IsError) cuya variable es la búsqueda que queremos realizar. Si el resultado es positivo, es decir el resultado será #N/A, la función SI dará un resulta en blanco (señalado por " "). En caso contrario dará el resultado de la búsqueda BUSCARV(D8,Lista_1,2,0).

Esta técnica es útil, por supuesto, en cualquier otra fórmula que use funciones de Excel.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: #N/A, BUSCARV, VLOOKUP

Categorías: Funciones&Formulas_, Varios_