Formule générique
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Sommaire
Pour trier de manière aléatoire les valeurs existantes avec une formule, vous pouvez utiliser une formule INDEX et MATCH avec des colonnes d'assistance, comme illustré dans la capture d'écran. Dans l'exemple illustré, la formule en E5 est:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
où "names" est la plage nommée B5: B11, "rand" est la plage nommée C5: C11 et "sort" est la plage nommée D5: D11.
Explication
Cette formule dépend de deux colonnes d'assistance. La première colonne d'assistance contient des valeurs aléatoires créées avec la fonction RAND (). La formule en C5, copiée vers le bas est:
=RAND()
La fonction RAND génère une valeur aléatoire à chaque ligne.
Remarque: RAND est une fonction volatile et générera de nouvelles valeurs à chaque changement de feuille de calcul.
La deuxième colonne d'aide contient les nombres utilisés pour trier les données, générés avec une formule. La formule en D5 est:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Voir cette page pour une explication de cette formule.
La formule en E5 est:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Ici, la fonction INDEX est utilisée pour récupérer des valeurs dans la plage nommée «noms», en utilisant les valeurs de tri dans la plage nommée «sort». Le travail réel de détermination de la valeur à récupérer est effectué par la fonction MATCH dans cet extrait de code:
MATCH(ROWS($D$5:$D5),sort,0)
À l'intérieur de MATCH, la fonction ROWS reçoit une plage d'extension en tant que valeur de recherche, qui commence par une cellule et se développe lorsque la formule est copiée dans la colonne. Cela incrémente la valeur de recherche, en commençant à 1 et en continuant à 7. MATCH renvoie ensuite la position de la valeur de recherche dans la liste.
La position est transmise à INDEX comme numéro de ligne et INDEX récupère le nom à cette position.
