viernes, 30 de enero de 2015

Selección Aleatoria de un Valor de un Rango (2)

"Necesito seleccionar aleatoriamente un número de un rango determinado. He visto una solución en tu blog en el post Seleccionar Aleatoriamente un Valor de un Conjunto. El problema que me encuentro es que si el rango en cuestión contiene celdas en blanco, entonces, la solución que propones devuelve valores cero. Me gustaría saber si existe la forma de que evite dichas celdas en blanco y elija un valor entre aquellas que contienen número".

Para solucionar esta variación sobre el caso visto en el post Seleccionar Aleatoriamente un Valor de un Conjuntovamos a generar una tabla auxiliar que nos permita "apartar" las celdas que se encuentran vacías para que no se puedan generar valores cero en el resultado. Partimos del siguiente ejemplo:
Queremos obtener aleatoriamente un valor de esta lista pero sin considerar las celdas en blanco. En la solución proporcionada en la primera versión de este problema, generamos un número aleatorio de posición para que excel me devuelva el valor existente en dicha celda. Es decir, si mi lista tiene 20 valores, genero un número aleatorio entre el 1 y el 20 y le pido a excel que vaya a la celda del número obtenido, por ejemplo la 4, y me devuelva el valor de dicha celda. Aplicando dicha solución a esta lista, nos podríamos encontrar con que en la cuarta celda de la lista la celda esté vacía (obtendríamos valor cero).

Lo primero que hacemos es seleccionar el rango B5:B18 y le damos el nombre de Valores. A continuación generamos una nueva lista auxiliar de dos columnas, cuya primera columna es una serie de número de orden de menor a mayor (en este caso del 1 al 14, ya que tenemos 14 datos):
Nos situamos en la celda G5 y escribimos la fórmula:
=K.ESIMO.MAYOR(valores;F5)  y copiamos hasta G18
De esta manera hemos conseguido generar una nueva lista ordenada de mayor a menor con los valores de nuestra lista original pero ahora ya no tenemos celdas en blanco por el medio del rango, ya que hemos conseguido que se "vayan al final" de nuestra nueva lista y que se muestren como error del tipo #¡NUM!
Esto nos permite ahora, aplicando la función CONTAR, calcular cuántas celdas de mi nueva lista contienen un valor númerico. Si calculamos  =CONTAR(G5:G18) nos devolverá el resultado 10, porque es el número de valores existentes en dicho rango de 14 celdas (las otras 4 contienen el error #¡NUM!). De esta manera ya sé que debo generar un número aleatorio entre 1 y 10. Por seguir trabajando con nombres de rango, seleccionamos G5:G18 y le creamos el nombre NewLista.
Nos situamos en la celda D10 y escribimos la fórmula definitiva:
=DESREF(G4;ALEATORIO.ENTRE(1;CONTAR(NewLista));)
Cada vez que pulsemos la tecla F9, excel recalculará un valor aleatorio y lo mostrará en la celda D10. Una fórmula alternativa en D10 sería utilizar la función INDICE:
=INDICE(NewLista;ALEATORIO.ENTRE(1;CONTAR(NewLista))) 

2 comentarios:

  1. Enhorabuena por el blog Kiko, excelente trabajo. Este ejemplo me ayuda mucho con lo que quiero hacer, pero con la diferencia de que tú has puesto sólo una celda para generar el aleatorio donde aparece el número 5 y yo necesitaria más celdas, entonces tengo un rango de celdas en la cuál no se repite ningún valor y lo he llamado NewLista, he probado en poner 6 celdas con la fórmula =INDICE(NewLista;ALEATORIO.ENTRE(1;CONTAR(NewLista))) para generar el aleatorio desde los valores de mi rango NewLista, pero al pulsar F9 para generar el aleatorio se repiten los valores de varias celdas y yo quiero que no se repitan en ninguna de las seis celdas. ¿Sabrias hacerlo? Gracias de antemano. Saludos!

    ResponderEliminar
  2. Buenas tardes Andrés. Si me das un mail te mando la solución. Un saludo

    ResponderEliminar