martes, 1 de diciembre de 2015

Búsqueda aleatoria condicionada.

Planteaba un lector del blog la posibilidad de obtener un número de un a lista, de manera aleatoria, según una condición dada sobre un campo de nuestra tabla.


La idea es por tanto, según un Color elegido, obtener un valor aleatorio de alguno de los tres tramos (uno por día) que corresponda con ese color.
En el ejemplo tenemos tres tramos / tres días, y dentro de cada día cinco colores y un valor diferentes para cada día y color.
A partir de un color deseamos conseguir uno de los tres importes asociados a dicho color, de manera aleatoria.


Comenzamos indicando en una celda un color, por ejemplo en I4 escribimos 'amarillo' y en la celda de al lado, en J4 la siguiente fórmula matricial:
=BUSCARV(I4;DESREF(INDIRECTO("b"&K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)));0;0;5;2);2;0)

Búsqueda aleatoria condicionada.



El resultado obtenido en J4 es un valor aleatorio del campo 'Cantidad', pero sólo de entre los 'Colores' amarillo, tal y como perseguíamos.

La clave o claves de esta fórmula es por un lado nuestra función ALEATORIO.ENTRE que incorpora el componente aleatorio, y nos dirige a uno de los tres tramos:
ALEATORIO.ENTRE(1;3)

Por otra parte con la función K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)) obtenemos un número de entre los indicados en la constante matricial {2;7;12}, que representa la fila de inicio de cada tramo por fecha.
Esta fila inicial es importante ya que a partir de ella construiremos un rango de cinco filas x una columna; esto lo conseguimos con la función DESREF en su forma matricial:
DESREF(INDIRECTO("b"&K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)));0;0;5;2)
me he apoyado en la función INDIRECTO para señalar el ancla.


El resto es sencillo, ya que sobre esa matriz de búsqueda, asociada aleatoriamente al rango o tramo por fecha, forzamos una BUSCARV para retornar la Cantidad asociada al color indicado en I4:
=BUSCARV(I4;DESREF(INDIRECTO("b"&K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)));0;0;5;2);2;0)

2 comentarios:

  1. Hola Ismael,

    Te propongo otra fórmula matricial, más horrorosa que la tuya si cabe, para un caso general. Para hacerla más digerible (sólo un poco, no te creas), usaré nombres de rangos.

    "RangoFiltro" sería tu rango "detalle"

    "RangoValores" sería tu rango "cantidad"

    "Filtro" es una celda que contiene el valor a usar como fitro de los valores de "RangoFiltro"

    En el administrador de nombres habría que poner los siguientes nombres formulados

    Ocurrencias =CONTAR.SI(RangoFiltro;Filtro)

    FilaEnRango =FILA(RangoFiltro)-FILA(INDICE(RangoFiltro;1))+1

    FueraDeRango =FILAS(RangoFiltro)+1

    La fórmula para obtener el resultado sería esta (como matricial):

    =INDICE(RangoValores;INDICE(K.ESIMO.MENOR(SI(RangoFiltro=Filtro;FilaEnRango;FueraDeRango);FilaEnRango);ALEATORIO.ENTRE(1;Ocurrencias)))

    Se puede hacer de una tacada, sin utilizar nombres de rango, pero resulta infumable.

    Lo que hace: genera una matriz con las filas de los datos del RangoFiltro que cumplen el criterio (Filtro). Para los datos que no lo cumplen, asigna un valor igual al recuento de valores de RangoFiltro más uno.

    Ordena la matriz generada de forma ascendente con K.ESIMO.MENOR.

    Genera un número aleatorio entre 1 y Ocurrencias, que es el recuento de los valores de RangoFiltro que cumplen el criterio (Filtro), y obtiene con INDICE el elemento de la matriz generada que ocupa esta posición.

    Este elemento es una de las filas de la matriz "RangoFiltro" que cumple el criterio (Filtro) y se aplica con una fórmula INDICE sobre la matriz "RangoValores".

    Debería funcionar, pero no me juego nada ;-)

    Un cordial saludo,

    Daniel

    ResponderEliminar
    Respuestas
    1. Gracias Daniel
      ;-)
      la probaré.. pero es verdad que a priori parece más 'infumable' :DDD

      Gracias por el aporte!!

      Eliminar