miércoles, 11 de abril de 2012

Asignación Aleatoria por Filas


"Quiero realizar la asignación aleatoria de un número de anuncios determinado de distintas empresas en una parrilla dispuesta por bloques como aparece en la imagen:


De tal manera que la suma de los 5 bloques de cada anunciante debe resultar el número de anuncios contratados por cada empresa".

Pues nos ponemos manos a la obra. Empezamos creando la correspondiente tabla en nuestra hoja, a saber:


A continuación seleccionamos el rango E17:I23 y con dicho rango seleccionado escribimos la fórmula:

=ALEATORIO() y pulsamos Ctrl + Enter (así rellenamos todo el rango de una sola vez):


Nos situamos ahora en E5. Para que se entienda mejor vamos a realizar una primera fórmula que no es la definitiva. Escribimos (en E5) la fórmula:

=JERARQUIA(E17;$E17:$I17)

Copiamos dicha fórmula para toda la tabla (rango E5:I11). De esta manera conseguimos ordenar del 1 al 5 los resultados aleatoriamente obtenidos:


Ahora ya sólo nos queda aprovechar aquellos valores de orden que sean igual o inferiores al número de anuncios contratados por cada empresa. Por ejemplo, para el Balneario de Mondariz, que ha contratado 3, nos interesarán los valores 1, 2 y 3. A partir de aquí utilizaremos el formato condicional y la función SI para dar el formato final. Nos situamos nuevamente en la celda E5 y escribimos la fórmula:

=SI(JERARQUIA(E17;$E17:$I17)>$C5;"";1)

Nuevamente copiamos esta fórmula para el rango E5:I11. Al añadir el condicional si el número aleatorio es superior al número de anuncios, excel dejará la celda correspondiente al bloque en blanco. Pero si el número aleatorio obtenido es igual o inferior al número de anuncios contratados entonces escribirá un 1 en la celda del bloque correspondiente:


Ya sólo queda aplicar formato condicional para rematar la tarea. Seleccionamos el rango E5:I11. Dentro de la ficha Inicio vamos al módulo de Estilos y pulsamos el icono de Formato Condicional. De la lista que se abre seleccionamos nueva regla. En la ventana que se abre seleccionamos Utilice una fórmula que determine las celdas para aplicar formato (la última opción de la lista). Nos situamos en Dar formato a los valores donde esta fórmula sea verdadera y escribimos la siguiente fórmula: =1
Acabamos pulsando el Botón Formato. Ahora seleccionaremos el formato con el que queremos que excel resalte los bloques en los que sí hay anuncio. En nuestro ejemplo le pediremos relleno azul y color de fuente blanco y negrita. Aceptamos y...


Cada vez que se recalcule la hoja o que pulsemos la tecla F9 excel generará nuevos números aleatorios y, en consecuencia, un nuevo mapa como se puede ver a continuación en varios ejemplos:




Evidentemente también podemos modificar el número de anuncios contratados por cada empresa y el modelo seguirá funcionando correctamente (obviamente con un máximo, en este ejemplo, de 5 anuncios por empresa).

No hay comentarios:

Publicar un comentario