miércoles, 21 de agosto de 2013

Seleccionar Aleatoriamente un Valor de un Conjunto

"En una columna tengo un conjunto  de valores del tipo 1, 1, 2, 5, 6, 6, 8, 2, 8, 1, 5, etcétera, y quiero seleccionar de manera aleatoria uno de estos valores".

Para solucionar este problema utilizaremos las funciones DESREF, CONTAR y ALEATORIO.ENTRE .  Partimos del siguiente ejemplo:
Lo primero que hacemos es darle nombre al rango de valores. Seleccionamos desde B3 hasta B16, hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre: valores y pulsamos Enter. A continuación nos situamos en la celda D10 y escribimos la siguiente fórmula que explico a continuación:
=DESREF(B2;ALEATORIO.ENTRE(1;CONTAR(valores));) 

CONTAR(Valores) cuenta el número de valores que hay en dicho rango. En nuestro ejemplo el resultado será 14. Al anidar esta función dentro de la función ALEATORIO.ENTRE, estamos consiguiendo que genere un número aleatorio entre 1 y 14 (que es el número mínimo y máximo de filas de nuestro rango). El problema es que entre 1 y 14 hay valores que no se encuentran en nuestra lista, por ejemplo el 7, el 11, el 12, etcétera. Lo que hacemos ahora es utilizar el número aleatorio generado para ir a una posición de la lista que tenemos y obtener el número que se encuentre en dicha posición. Para ello utilizamos la función DESREF. Partimos de la celda B2 y, a partir de dicha celda, excel se posicionará en la fila del rango Valores que de manera aleatoria hemos generado con el resto de la fórmula ya explicada. Si, por ejemplo, el número generado es un 11, excel se desplazará 11 filas más abajo de la celda de partida (B2) y nos devolverá el valor de B13, esto es, 6. Pruebe a pulsar la tecla F9 y verá cómo se recalcula el número y siempre dentro de los existentes en la lista :

6 comentarios:

  1. Problema: Si el rango de celdas contiene alguna con dato vacío, entonces, tu operación devuelve CERO. Esto es un problema, porque en tal caso cualquier querría que siguiera buscando una celda que contenga algún dato numérico que no sea nulo. Adicionalmente, cómo harías para crear un botón que haga el cálculo independiente por cada rango de celdas?, ya que a lo mejor, solo deseo generar un aleatorio de un rango concreto dentro de un libro de Excel sin tener que pulsar F9 y volver a perder todos los demás aleatorios generados.

    Gracias por tu aportación, podrías completarla?

    ResponderEliminar
    Respuestas
    1. La solución es bastante sencilla y pasa por generar una tabla adicional, reordenar con K.ESIMO.MAYOR los números para obviar las celdas en blanco y, de esta nueva tabla, obtener el aleatorio. Por otro lado podemos grabar una macro para que, una vez generado el número aleatorio, lo copie y lo pegue como valor y así independizar rangos. Por favor mándame tu mail y te envío las dos soluciones. Un saludo

      Eliminar
    2. Podrías mandarme a mi la solución puesto que tengo el mismo problema como comenta en el comentario anterior. Tengo una columna de 29 celdas y hay varias celdas en blanco, las celdas restantes vienen de formulas como =SI(D14=6;C14;SI(D14=7;C14;SI(D14=8;C14;SI(D14=9;C14;"")))) entonces si no se cumple la condición le he puesto "" para que me la deje en blanco. Entonces en la formula si cumple la condición se queda el valor de C14 que es un número entero. Entonces yo queria generar que por ejemplo en celda J15 aparezca un número que esté dentro de los que se encuentren entre F1 y F29, pero que no tenga en cuenta las celdas en blanco, es decir, las celdas que no cumplen con la condición que yo le puse que apareciera "" Te lo agradecería muchísimo si sabes la solución y me pudieses ayudar. Me llamo Andrés. Si puedes ayudarme este es mi e-mail andreuxx@gmail.com Saludos!! ;)

      Eliminar
    3. Hola Andrés: tienes la solución en el siguiente post:
      http://lareboticadeexcel.blogspot.com.es/2015/01/seleccion-aleatoria-de-un-valor-de-un.html

      Eliminar
  2. y cuando quiero sacar valores aleatorios de campos que no tienen valores numericos cual seria la formula?..
    christian.jjr9@gmail.com

    ResponderEliminar
    Respuestas
    1. Perdón si parece demasiado obvia la respuesta pero asignándole un número a cada texto, solucionándolo como número y buscando, finalmente, el texto asociado a dicho número.

      Eliminar