Muestras aleatorias sin repetición con Excel

La extracción de muestras aleatorias sin repetición de un tamaño determinado a partir de una muestra original mayor se puede hacer en hoja de cálculo a través de varios métodos y aunque ninguno de ellos está completamente exento de la posibilidad de fallo, es verdad que podemos reducir esta posibilidad hasta hacerla insignificante. Para examinar las dos posibilidades extremas imaginemos que tenemos una lista de veinte alumnos de los que queremos extraer cinco nombres.

Primera aproximación al sorteo

En primer lugar podemos asignar un número de orden del 1 al 20 a cada uno de los alumnos y luego generar 5 cinco números aleatorios entre el 1 y el 20 a través de la fórmula extendida a cinco celdas contiguas:

=ALEATORIO.ENTRE(1;20)

Pero en este caso la probabilidad de que se nos genere al menos un número repetido es de:

5/20=0.25

Es decir, si queremos que los valores sean únicos, es muy probable que una de cada cuatro veces no seamos capaces de conseguirlo, y por tanto el método es muy poco fiable.

Evidentemente, si la muestra original es muy grande, digamos de mil elementos, y la muestra aleatoria a obtener es muy pequeña, digamos de tamaño 5, la fiabilidad del método aumenta en proporción, resultando en este caso que la probabilidad de que obtengamos algún valor repetido se reduce a:

5/1000=0,005

Es decir, solo una de cada doscientas veces obtendremos valores repetidos, lo cual, dependiendo del caso, puede ser ya suficiente.

Segunda aproximación más fiable

En segundo lugar podemos generar 20 números aleatorios puros (o pseudoaleatorios, como se debería decir siempre) a través de la función volátil y sin argumentos:

=ALEATORIO()

Cada uno de estos números está asociado a uno de los nombres y a cada uno le asignaremos un número de orden del 1 al 20, por ejemplo de forma manual, o quizás a través de la función:

=FILAS($A$1:$A1)

Después necesitaremos ordenar esos aleatorios, por ejemplo con la función:

=K.ESIMO.MENOR

En la que usaremos como número de orde k-ésimo precisamente esos que van del 1 al 20.

Finalmente usaremos la combinación de las funciones:

=COINCIDIR

=INDICE

O si hemos situado la columna de ALEATORIO a la izquierda de los nombres de los alumnos también podremos usar la función:

=BUSCARV

Para extenderla a las cinco celdas que contienen los resultados y recuperar así nuestros cinco valores.

La probabilidad de que nos salgan valores repetidos aquí es la probabilidad de que se repita alguno de los aleatorios generados, que ahora ya no son entre el 1 y el 20, sino valores entre 0 y 1 con los 15 dígitos de precisión de la hoja de cálculo, es decir:

20/1.000.000.000.000.000=2x10-14

Lo que a efectos prácticos quiere decir cero.

Imagen del generador de muestas aleatorias para Excel
Ventana flotante del generador de muestras aleatorias para Excel

Generador de muestras aleatorias para Excel

Para mayor comodidad en la generación de este tipo de muestras, ofrezco a mis lectores la descarga de un complemento para la hoja de cálculo Excel, en sus dos versiones de archivo con macros o complemento instalable, que es capaz de extraer muestras aleatorias de valores únicos de forma limpia y eficaz.

La utilidad se puede arrancar desde su botón de inicio en la pestaña de DATOS o usarse como fórmula matricial con argumento único que es la muestra original.

Hay varios tutoriales en mi canal de video que hacen referencia a mi antigua web IdeasExcel que he reunido en una lista junto a este nuevo y último en el que le doy un repaso general al tema, con la instalación del complemento e instrucciones para su descarga.


Comments

So empty here ... leave a comment!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Sidebar



Si continuas utilizando este sitio, significa que aceptas el uso de cookies. más información

Los ajustes de cookies de esta web están configurados para "permitir cookies" y así ofrecerte la mejor experiencia de navegación posible. Si sigues utilizando esta web sin cambiar tus ajustes de cookies o haces clic en "Aceptar" estarás dando tu consentimiento a esto.

Cerrar