lunes, 10 de junio de 2013

Listas aleatorias (1 de 2)

El problema de hoy consiste en ordenar alatoriamente una lista de nombres. Excel dispone de dos funciones muy parecidas, ALEATORIO y ALEATORIO.ENTRE, que generan números al azar. En el primer caso, los números generados están comprendido entre 0 y 1. En el segundo, entre los dos valores que fijemos. En este ejercicio, emplearemos la función ALEATORIO.

Dada una lista de nombres (C1:C9), queremos que Excel los ordene al azar cada vez que pulsemos la tecla F9.

En la columna B, con la función ALEATORIO, creamos una serie de números aleatorios comprendidos entre 0 y 1. ¿Por qué en la columna B? Pronto comprenderemos la razón.

En B2:
=ALEATORIO()     [Extendemos la fórmula hasta la fila 9]

ALEATORIO es una función volátil, de modo que cada vez que pulsemos F9 o hagamos algún cambio en la hoja, se recalculará y los valores de la columna B cambiarán.

La lista aleatoria la crearemos en tres pasos y la pondremos en la columna D. Usaremos las columnas F y G como columnas auxiliares, que eliminaremos una vez creada la fórmula definitiva.

Primero, ordenamos los números aleatorios de la columna B de menor a mayor. Esto se hace con la función K.ESIMO.MENOR. Cuando pongamos la fórmula, los valores de la columna B se recalcularán y aparecerán otros diferentes.

En F2:
=K.ESIMO.MENOR($B$2:$B$9;FILA(A1))     [Extendemos la fórmula hasta la fila 9]

Ahora, usando la función BUSCARV, extraemos de la matriz B2:C9 los nombres de la columna C cuyo valor asociado (columna A) coincide con el de la columna F. La función BUSCARV realiza la búsqueda en la primera columna de la matriz. Ésta es la razón por la que hemos puesto los números aleatorios en esa posición, a la izquierda de los nombres, en la columna B.

En G2:
=BUSCARV(F2;$B$2:$C$9;2;FALSO)     [Extendemos la fórmula hasta la fila 9]

Excel ha hecho un nuevo recálculo. La lista de la columna G es una lista aleatoria. Si queremos otra, pulsamos F9 o modificamos alguna celda.

Finalmente, creamos la fórmula compuesta en D2 y borramos las columnas F y G.

En D2:
=BUSCARV(K.ESIMO.MENOR($B$2:$B$9;FILA(A1));$B$2:$C$9;2;FALSO)     [Extendemos la fórmula hasta la fila 9]

¿Qué ocurre si no es posible poner la columna de números aleatorios a la izquierda de la columna de nombres? La respuesta es bien sencilla: no se podrá utilizar la función BUSCARV. Habrá que idear algún truco, como crear los números aleatorios en una zona vacía de la hoja y copiar la columna de nombres a su derecha.

Otra solución es utilizar la función INDICE. Veamos cómo.

Nuestra lista de nombres está en la columna B y, por el motivo que sea, no podemos escribir nada en la columna A.

 La lista de números aleatorios la crearemos en la columna E.

En E2:
=ALEATORIO()     [Extendemos la fórmula hasta la fila 9]

Clasificamos de menor a mayor los valores de la columna E usando la función JERARQUIA.EQV (en Excel 2003 hay que usar JERARQUIA).

En G2:
=JERARQUIA.EQV(E2;$E$2:$E$9;1)     [Extendemos la fórmula hasta la fila 9]

Con INDICE ponemos en la columna H el nombre de la columna B que ocupa la posición indicada en la columna G.

En H2:
=INDICE($B$2:$B$9;G2)     [Extendemos la fórmula hasta la fila 9]

Agrupamos todas las fórmulas en la columna C y borramos las columnas G y H.

En C2:
=INDICE($B$2:$B$9;JERARQUIA.EQV(E2;$E$2:$E$9;1))     [Extendemos la fórmula hasta la fila 9]]

Son posibles otras fórmulas; por ejemplo:

En C2:
=INDICE($B$2:$B$9;COINCIDIR(K.ESIMO.MENOR($E$2:$E$9;FILA(A1));$E$2:$E$9;0))

Esta fórmula devuelve una lista diferente de la anterior, pero también es totalmente aleatoria.

Sin embargo, las fórmulas usadas aquí tienen un fallo. Aunque es poco problable en listas pequeñas como la del ejercicio, podría darse el caso de que dos números aleatorios fueran exactamente iguales. En ese caso, uno de los nombres se repetiría. Por ejemplo, consideremos que ALEATORIO ha devuelto una lista de números en los que coinciden los correspondientes a las celdas E4 y E7. El resultado será:

La columna G tendrá dos valores iguales, lo que hará que la fórmula devuelva dos veces el mismo nombre (Carlos). Otro (Iker) habrá desaparecido de la lista aleatoria (columnas C y H).

La solución en el próximo artículo.



No hay comentarios:

Publicar un comentario