Trabajar con Datos Aleatorios en Excel

Un valor aleatorio en Excel podemos considerarlo como un dato que se obtiene al azar de entre una lista de valores que forman parte de una colección de la que se nutre.

01-Sep-2016

Valores Aleatorios

En ciertos escenarios de trabajo donde se necesitan realizar simulaciones de cálculo, consultas, pruebas de macros o cualquier otro análisis de información, en los que no se disponen de datos reales de trabajo, necesitamos introducir datos inventados a los que se les suele denominar ‘Datos de carga’. Esta situación supone que la preparación de la información de trabajo suele ser larga y tediosa, más cuando se precisan muchas filas de información y de tipos bien distintos como números, fechas, información de tablas, etc. Para reducir notablemente el tiempo de preparación de los datos, podemos recurrir a la generación de valores aleatorios de trabajo.

Un valor aleatorio en Excel podemos considerarlo como un dato que se obtiene al azar de entre una lista de valores que forman parte de una colección de la que se nutre. Los valores aleatorios que podemos obtener pueden ser numéricos pertenecientes a un rango de delimitado por un valor inferior y otro superior, cronológicos comprendidos entre dos fechas,  valores de una lista datos de Excel o incluso de listas vinculadas.

Para obtener valores aleatorios en Excel se utilizan dos funciones matemáticas:

  • ALEATORIO(): Devuelve un número real aleatorio mayor o igual a 0 y menor que 1.
  • ALEATORIO.ENTRE():Devuelve un número entero aleatorio entre los números que especifique.

La principal diferencia entre ambas funciones es que ALEATORIO() devuelve números fraccionarios y ALEATORIO.ENTRE() devuelve números enteros.

La utilización de estas funciones combinadas con otras funciones de Excel, nos van a permitir generar listas aleatorias de números, fechas y de extracción.

NOTA: Las funciones para obtener valores aleatorios, trabajan con datos volátiles de tal forma que cada cambio que sufra la hoja de cálculo (introducir o eliminar datos), provoca la obtención de nuevos valores aleatorios que hacen cambiar el escenario de datos que teníamos. Debido a esta peculiaridad, una vez que hemos obtenido todos los valores aleatorios necesarios se recomienda realizar una copia de los datos y un pegado especial de ‘sólo valores’, para mantenerlos invariables.

 

Valores Aleatorios Numéricos

Números enteros aleatorios

Como ya hemos comentado para obtener números enteros aleatorios, necesitamos utilizar la función ALEATORIO.ENTRE, que devuelve un número entero aleatorio entre dos números que se indican como argumentos de la función.

ALEATORIO.ENTRE(límite_inferior, límite_superior)

Supongamos que deseamos generar una lista con las edades de personas comprendidas entre 18 y 85 años para realizar un estudio de la población. El límite inferior sería 18 y el límite superior sería el 85, quedando la función como:

=ALEATORIO.ENTRE(18;85)
Edades aleatorias

Deseamos generar una serie de muestras sobre temperaturas invernales que pueden oscilar entre los ­10ºC y los 4ºC. El límite inferior sería -10 y el límite superior sería el 4, quedando la función como:

=ALEATORIO.ENTRE(-10;4)
Temperaturas aleatorias

NOTA: Si en lugar de utilizar números, utilizamos referencias absolutas a celdas de la hoja donde indicar los límites del rango conseguiremos variar rápidamente los valores obtenidos sin tener que reescribir las fórmulas.

Números reales aleatorios

Para obtener números fraccionarios aleatorios, necesitamos utilizar la función ALEATORIO() que no precisa de argumentos, que devuelve número real aleatorio mayor o igual a 0 y menor que 1.

A la hora de indicar los límites inferior y superior del rango de números que deseamos obtener, tenemos que recurrir a la fórmula:

=ALEATORIO()*(límite_superior-límite_inferior)+límite_inferior

Supongamos que deseamos obtener una lista de precios que oscilen entre los 2,20€ y los 10,50€ para una serie de artículos. El límite inferior sería 2,20 y el límite superior sería el 10,50, quedando la fórmula como:

=ALEATORIO()*(10,5-2,2)+2,2
Precios aleatorios

En este ejemplo los valores para los límites inferior y superior se han indicado por medio de referencias absolutas a las celdas H3 y H4 respectivamente lo que nos permitirá variar el rango de precios sin tener que modificar las fórmulas.

=ALEATORIO()*($H$4-$H$3)+$H$3

Valores Aleatorios Cronológicos

Fechas aleatorias entre dos fechas

Si deseamos generar valores aleatorios de tipo fecha utilizaremos la función ALEATORIO.ENTRE(), indicando la fecha de inicio como valor inicial y la fecha de finalización como valor final. Debido a que esta función trabaja con datos de tipo numérico, si introducimos directamente las fechas como argumentos de la función, será necesario convertir las fechas a números de serie por medio de la función FECHANUMERO o escribir las fechas en celdas de la hoja y hacer referencia a las celdas lo que resulta más sencillo, práctico y efectivo.

Supongamos que deseamos obtener una serie de fechas comprendidas entre el 01/01/2016 y el 31/08/2016. Si indicamos los valores directamente en la función ALEATORIO.ENTRE, la fórmula quedaría de la siguiente manera:

=ALEATORIO.ENTRE(FECHANUMERO("01/01/2016");FECHANUMERO("31/08/2016"))

Esta forma de indicar los argumentos resulta poco práctica.

Si escribimos los valores de las fechas en celdas de la hoja la fórmula resultaría más sencilla quedando de la siguiente manera:

=ALEATORIO.ENTRE($M$3;$M$4)

Siendo $M$3 la celda donde hemos escrito 01/01/2016 y $M$4 la celda donde hemos escrito 31/08/2016.

Fechas aleatorias entre dos fechas

Fechas aleatorias del año actual

En este otro caso tendremos que construir el valor del primer y último día del año utilizando la función FECHA y posteriormente utilizarlos como argumentos de la función ALEATORIO.ENTRE() como se mostró en el apartado anterior.

Para crear las fechas pertenecientes al primer y último día del año actual utilizaremos las funciones: FECHA, AÑO y HOY.

La función FECHA genera el número de serie a partir del año, mes y día que se le indican como argumentos, los valores para el mes y el día no son ningún problema ya que para el primer día del año serán: 1 y 1, y para el último día del año serán: 12 y 31, sin embargo el valor del año lo tenemos que obtener a partir de la fecha actual (HOY) de la que extraeremos el año (AÑO), por medio de la siguiente fórmula: AÑO(HOY()). Teniendo en cuenta estas aclaraciones las fórmulas serian:

=FECHA(AÑO(HOY());1;1) -> primer día del año
=FECHA(AÑO(HOY());12;31) -> último día del año

Aplicado al ejemplo del apartado anterior:

Fechas aleatorias año actual

Valores Aleatorios vasados en Listas de Datos

Obtener valores de una lista

Quizás una de las posibilidades más interesantes de la utilización de números aleatorios es la de permitirnos extraer valores de una lista de datos de forma aleatoria.

Para realizar este proceso necesitamos utilizar las funciones:

  • INDICE: se encargará de extraer un elemento de la lista a partir de su posición de fila.
  • ALEATORIO.ENTRE: se encargará de ir cambiando de forma aleatoria el nº de fila del que se extrae el valor.
  • CONTARA: obtiene el total de elementos de la lista que utilizaremos para establecer el límite superior de la función ALEATORIO.ENTRE.

Supongamos que deseamos rellenar una columna con los nombres de distintos tipos de prendas que se encuentran en una tabla de Excel.

Lo primero que necesitaremos es la tabla que contiene los nombres de las prendas que utilizaremos para rellenar posteriormente de forma aleatoria una columna de datos. Asignaremos un nombre al rango que contiene los valores que componen la tabla, sin incluir el nombre de la columna.

Lista de extracción

En nuestro ejemplo hemos asignado el nombre ´Prendas´ al rango de valores.

A continuación utilizando la función INDICE, iremos extrayendo los valores de la tabla.

El primer argumento que le indicamos es el nombre del rango que acabamos de crear y para el nº de fila utilizaremos la función ALEATORIO.ENTRE, donde el límite inferior será siempre 1 y el límite superior el total de elementos del rango que lo obtendremos por medio de la función CONTARA del rango. La fórmula resultante quedaría de la siguiente manera:

=INDICE(Prendas;ALEATORIO.ENTRE(1,CONTARA(Prendas)))
Nombres de prendas extraidos de forma aleatoria

Valores aleatorios de listas vinculadas

Otra de las opciones del trabajo con listas, consiste en utilizar múltiples listas e ir variando la lista de la que deseamos obtener valores de forma aleatoria. Para realizar este proceso necesitamos un dato que nos sirva de índice a la hora de seleccionar la lista de extracción. Este valor índice lo tendremos en una lista principal que nos servirá de vínculo con el resto de las listas.

Supongamos que deseamos generar una lista de valores aleatorios formada por el nombre de distintos medios de transporte y el nombre algún vehículo perteneciente al medio de transporte seleccionado.

En el siguiente ejemplo tenemos una lista que contiene los nombres de distintos medios de transporte, que será nuestra lista principal de datos. Para cada medio de transporte disponemos de una lista con los nombres de distintos tipos de vehículos, que serán las listas vinculadas de las que iremos extrayendo valores de forma aleatoria.

Lista principal y listas vinculadas

Se han creado nombres de rango para cada una de las listas de vehículos, utilizando el nombre del medio de transporte al que pertenecen.

NOTA: Es importante resaltar que los nombres que asignemos a las listas vinculadas tienen que coincidir exactamente con su índice de la lista principal. Observar como la lista de vehículos terrestres tiene asignado como nombre ‘Tierra’ que es exactamente el nombre que aparece en la lista de medios de transporte, al igual que ocurre con las listas de vehículos marinos y aéreos.

 

Obtener Medios y Vehículos

Para obtener el nombre del medio de transporte procederemos de la misma forma que utilizamos en el apartado anterior del trabajo con listas, que consistía en ir extrayendo los valores por medio de las funciones: INDICE, ALEATORIO.ENTRE y CONTARA.

=INDICE(Medio;ALEATORIO.ENTRE(1,CONTARA(Medio)))
Extraer nombres de medios de forma aleatoria

Una vez que ya tenemos los valores que se utilizan cómo índice para el vínculo con el resto de las listas, que coinciden exactamente con los nombres de rango que hemos asignado a las listas vinculadas,  introduciremos la fórmula encargada de extraer los datos vinculados.

Para conseguirlo incluiremos la función INDIRECTO a nuestra fórmula que es la que encargará de cambiar la lista de extracción a partir de su nombre de rango. En nuestro caso la función INDIRECTO, devuelve todos los valores del rango cuyo nombre coincida con el que le pasemos como argumento.

=INDICE(INDIRECTO(A9);ALEATORIO.ENTRE(1,CONTARA(INDIRECTO(A9))))
Extraer vehículos de forma aleatoria para cada medio de transporte

Como podemos observar hemos obtenido una lista de datos de carga totalmente de forma aleatoria sin tener que introducir nosotros los valores manualmente uno a uno.

Adjunto a este documento os incluyo un libro de Excel con un ejemplo en el que se utilizan todos los métodos de trabajo con valores aleatorios descritos en este módulo.