martes, noviembre 06, 2012

Generar muestras aleatorias en Excel

Supongamos que tenemos una lista de nombres como esta (la lista contiene 100 nombres propios)


y queremos extraer una muestra aleatoria de diez nombres.

En Excel podemos hacerlo con facilidad usando la función ALEATORIO.ENTRE para generar números aleatorios y usando este resultado como argumento en la función INDICE. Por supuesto tendremos que superar algunos inconvenientes, como, por ejemplo, números repetidos.

Empezamos por crear un nombre definido que re refiere al rango que contiene la lista (la población, en términos estadísticos)



En la hoja donde queremos que aparezca la muestra creamos una matriz que contenga los nombres (D4:E14 en nuestro ejemplo) y en el rango A5:A14 creamos una columna auxiliar con la fórmula

=ALEATORIO.ENTRE(1,CONTARA(Lista))



La fórmula que extrae los nombre en el rango E5:E14 es

=INDICE(Lista,A5)

donde "Lista" es el nombre que acabamos de definir.

Ahora basta con apretar F9 (Recalcular) para obtener una nueva muestra.

El problema con este modelo es que los números aleatorios no son únicos, es decir, pueden repetirse y por lo tanto generar nombres repetidos.

Para evitar esto creamos un mecanismo que verifique si hay números repetidos y en caso afirmativo, vuelva a calcular. Por supuesto, tendremos que usar macros.

En B5:B14 creamos un rango auxiliar con la fórmula

=CONTAR.SI($A$5:$A$14,A5)

Si el número aparece una única vez en el rango, el resultado es 1; en caso contrario será mayor que 1. Por ejemplo, en este caso el número 75 aparece dos veces y por lo tanto el nombre Cristian aparece dos veces en la muestra



En la celda B15 agregamos la fórmula " =SUMA(B5:B14)". Creamos el nombre definido " ControlValoresUnicos" que se refiere a esta celda. Si todos los números aparecen una única vez, el valor de la celda será 10; si es mayor de 10, sabemos que hay números repetidos.

Para automatizar esta operación usamos esta macro que ponemos en un módulo común del editor de Vb

Sub valores_unicos()

    Calculate
    While Range("ControlValoresUnicos") <> 10
        Calculate
    Wend
  
End Sub


La primer línea del código recalcula la hoja; luego la estructura While…Wend sigue recalculando hasta que la celda " ControlValoresUnicos" contiene el resultado 10.

El último paso es ocultar las columnas auxiliares y agregar un botón de la colección de Formularios de manera que el usuario puede generar una nueva muestra apretando este botón



El archivo con el ejemplo puede descargarse aquí

5 comentarios:

  1. Hola Jorge para crear lista sin repetir, tambien se podria hacer asi: crear una columna con la formula =ALEATORIO() la cantidad de celdas con dicha formula sera igual a la cantidad de usuarios.

    Luego crea al costado de la formula anterior =CONTAR.SI($B$4:$B$20,"<="&B4)
    donde B4:B20 tengo la lista de numeros generados por aleatorio.

    ResponderBorrar
  2. Uff, hace rato que estoy luchando con esto. Hice todo para que me de 100 ganadores de una lista de 830. Pero no logro que no repita elegidos. me da de 102 a 126 elegidos. Miro en VB y el macro esta ok, creo. Dice así:
    Sub valores_unicos()

    Calculate
    While Range("ControlValoresUnicos") <> 100
    Calculate
    Wend

    End Sub

    ¿Que estoy haciendo mal? Gracias

    ResponderBorrar
  3. Alejandra, mil disculpas por la demora en reponder. ¿A qué fórmula se refiere el nombre definido ControlValoresUnicos en tu modelo?

    ResponderBorrar
  4. Hola, perfecto todo pero cada vez que abro el documento me genera una muestra. He cambiado en formulas para que el calculo lo haga manual pero sigue generando muestras cada vez que abro el archivo. Sabeis como puedo solucionarlo? Muchas gracias

    ResponderBorrar
  5. La función ALEATORIO.ENTRE es volatil por lo que Excel la recalcula ante cada cambio en la hoja o cuando abres el cuaderno. La idea es generar los valores y copiarlos como constantes para utilizarlos.
    Otras soluciones puedes ver en esta nota de mi blog.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.