Fórmula genérica
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Resumen
Para ordenar aleatoriamente los valores existentes con una fórmula, puede usar una fórmula INDICE y COINCIDIR junto con columnas auxiliares como se muestra en la captura de pantalla. En el ejemplo que se muestra, la fórmula en E5 es:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
donde "nombres" es el rango con nombre B5: B11, "rand" es el rango con nombre C5: C11 y "sort" es el rango con nombre D5: D11.
Explicación
Esta fórmula depende de dos columnas auxiliares. La primera columna auxiliar contiene valores aleatorios creados con la función RAND (). La fórmula en C5, copiada es:
=RAND()
La función RAND genera un valor aleatorio en cada fila.
Nota: RAND es una función volátil y generará nuevos valores con cada cambio de hoja de trabajo.
La segunda columna de ayuda contiene los números utilizados para ordenar los datos, generados con una fórmula. La fórmula en D5 es:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Consulte esta página para obtener una explicación de esta fórmula.
La fórmula en E5 es:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Aquí, la función INDICE se utiliza para recuperar valores en el rango con nombre "nombres", utilizando los valores de clasificación en el rango con nombre "clasificación". El trabajo real de averiguar qué valor recuperar se realiza con la función COINCIDIR en este fragmento:
MATCH(ROWS($D$5:$D5),sort,0)
Dentro de COINCIDIR, la función FILAS recibe un rango de expansión como valor de búsqueda, que comienza como una celda y se expande a medida que la fórmula se copia en la columna. Esto incrementa el valor de búsqueda, comenzando en 1 y continuando hasta 7. COINCIDIR luego devuelve la posición del valor de búsqueda en la lista.
La posición se alimenta a INDICE como el número de fila, e INDEX recupera el nombre en esa posición.
