FÓRMULAS DE EXCEL

Distribuir aleatoriamente personas y grupos en Excel

Distribuir aleatoriamente personas y grupos en Excel

Funciones utilizadas: SI.ERROR, BUSCARV, SI, FILA, JERARQUIA, DESREF, COLUMNA, INDICE, ELEGIR

Suponiendo que disponemos de un rango de nombres en A1:A30 y que necesitamos construir una tabla de distribución para asignar aleatoriamente dicho rango entre un número predefinido de grupos de hasta 10 grupos en el rango con encabezado E4:N34. Podemos utilizar las celdas F1 y F2 para definir un número arbitrario de grupos y personas respectivamente, los cuales van a ser utilizados como tablero de comando para generar que se distribuyan automáticamente las personas y grupos en el rango total de la tabla de distribución. La sintaxis que debemos copiar en la celda E5 (primera celda resultado de la tabla de distribución), y que debemos arrastrar hacia la derecha (por cantidad de grupos) y hacia abajo (por cantidad de personas) a lo largo y ancho de toda la tabla es la siguiente:

DISTRIBUIR ALEATORIAMENTE PERSONAS Y GRUPOS EN EXCEL

=SI.ERROR(BUSCARV(SI(FILA()-FILA(E$5)+1>$F$2/$F$1;"";JERARQUIA(DESREF($C$1;FILA()-FILA(E$5)+(COLUMNA()-COLUMNA($E5))*($F$2/$F$1););$C$1:INDICE($C$1:$C$1000;$F$2)));ELEGIR({2};$A:$A;$B:$B);2;0);"")

Para que ésta fórmula funcione correctamente, primero deberemos completar en la columna B a lo largo de toda la columna A, una numeración secuencial comenzando en 1 hasta el último valor que será igual a la cantidad máxima de personas que analizará nuestra tabla de distribución. Por otro lado, también deberemos completar en la columna C a lo largo de toda la columna A, un valor aleatorio que puede ser obtenido con la función ALEATORIO con la sintaxis «=ALEATORIO()».

Las funciones FILA, COLUMNA, DESREF e INDICE permiten adaptar de forma dinámica a la tabla de distribución según la cantidad de grupos (columnas) y personas (filas) que necesitemos, mientras que la función BUSCARV es utilizada de forma inversa con la función ELEGIR para obtener el nombre de cada persona según el número correspondiente. Puedes conocer más acerca de la función BUSCARV inversa en el siguiente artículo: Función BUSCARV INVERSO en Excel

Es importante resaltar que la fórmula asignará la totalidad de las personas necesarias siempre que la cantidad de grupos sea un número divisible que devuelva un valor entero en la asignación de personas por grupo. Es decir que por ejemplo, si disponemos de 30 personas y las repartimos entre 1, 2 y 3 grupos, se asignarán 30, 15 y 10 personas por grupo respectivamente, devolviendo siempre un número entero de personas por grupo. Sin embargo, si repartimos 30 personas en 4 grupos, corresponderían 7,5 personas por grupo y la fórmula devolverá 7 personas por grupo, distribuyendo 28 personas sobre 30 totales.

De esta forma, logramos distribuir aleatoriamente una cantidad definida de personas y grupos de forma rápida y simple, permitiendo adaptar la tabla resultado según nuestras necesidades.

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.