Muestra aleatoria con Power Query

Para el ejemplo del post "BUSCARV o Combinar consultas" necesitaba crear una tabla con 10 productos elegidos aleatoriamente de una lista con más de 70 items. En Excel Clásico resolvemos la cuestión usando la función ALEATORIO() o la función ALEATORIO.ENTRE():

1. asignamos un número aleatorio a cada fila, 
2. cancelamos las fórmulas con Copiar--Pegar--Solo valores, 
3. ordenamos la tabla en forma ascendente o descendente 
4. elegimos las primeras 10 filas (o el número de filas que deseemos). 

¿Cómo lo haríamos con Power Query?  Contamos con la función Number.Random pero al usarla veremos que suceden cosas extrañas.

Veamos este ejemplo

Partimos de la lista completa, y creamos una consulta

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í

Números aleatorios únicos con Excel

En mi nota sobre Cómo generar números aleatorios con Excel mostraba cómo generar una serie de números aleatorios únicos, es decir, sin repeticiones. Esta técnica se basaba en un generar intencionalmente una referencia circular.
Hay, por supuesto, otras posibilidades que detallo en este archivo




1 – "Unique Numbers": Microsoft propone un macro para generar números aleatorios únicos. Esta macro siempre pone los resultados a partir de la celda A3 (en el original desde la celda A1, pero la he modificado para poder poner el comando en el encabezamiento).

2 – "RndNum": no recuerdo de que foro tome esta macro.

3 – "UDF" (user defined function): tomada del sitio de Ozgrid. Esta es una función volátil, es decir, cada vez que se produce algún cambio en la hoja, Excel recalcula la función. El resultado aparece como texto en al celda que contiene la función.

4 – "MRAND": mi favorito. Esta función forma parte del complemento desarrollado por Laurent Longre que ya he mencionado en mi nota sobre la función INDIRECTO con cuadernos Excel cerrados. Esta función tiene muchas ventajas. Al ponerla como función matricial, nos permite definir una matriz (x filas X y columnas) de números aleatorios no repetidos. Además podemos definir si la función debe ser volátil o no. La sintaxis es:
{=MRAND(máximo, inicio, cantidad, volátil o estática)}.

Este archivo contiene los ejemplos y los códigos de las macros.




Technorati Tags: MS Excel