Formula generica
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Sommario
Per ordinare in modo casuale i valori esistenti con una formula, puoi utilizzare una formula INDICE e CONFRONTA insieme alle colonne helper come mostrato nello screenshot. Nell'esempio mostrato, la formula in E5 è:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
dove "nomi" è l'intervallo denominato B5: B11, "rand" è l'intervallo denominato C5: C11 e "ordinamento" è l'intervallo denominato D5: D11.
Spiegazione
Questa formula dipende da due colonne helper. La prima colonna helper contiene valori casuali creati con la funzione RAND (). La formula in C5, copiata è:
=RAND()
La funzione RAND genera un valore casuale in ogni riga.
Nota: RAND è una funzione volatile e genererà nuovi valori con ogni modifica del foglio di lavoro.
La seconda colonna helper contiene i numeri usati per ordinare i dati, generati con una formula. La formula in D5 è:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Vedi questa pagina per una spiegazione di questa formula.
La formula in E5 è:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Qui, la funzione INDICE viene utilizzata per recuperare i valori nell'intervallo denominato "nomi", utilizzando i valori di ordinamento nell'intervallo denominato "ordinamento". Il lavoro effettivo per capire quale valore recuperare viene svolto dalla funzione MATCH in questo frammento:
MATCH(ROWS($D$5:$D5),sort,0)
All'interno di MATCH, alla funzione ROWS viene assegnato un intervallo in espansione come valore di ricerca, che inizia come una cella e si espande quando la formula viene copiata nella colonna. Ciò incrementa il valore di ricerca, iniziando da 1 e continuando fino a 7. CONFRONTA, quindi restituisce la posizione del valore di ricerca nell'elenco.
La posizione viene inviata a INDICE come numero di riga e INDICE recupera il nome in quella posizione.
