Choix aléatoire parmi les doublons d'une liste

[Bruine]

Bonjour,

Je m'arrache un peu les cheveux sur cette formule dont je n'arrive pas à trouver les fonctions pour qu'elle marche.
Le contexte : on organise un tournoi de babyfoot. Dans la première phase, chacun joue deux matchs. Les 8 premiers passent en quarts de finale (le 1er contre le 8e, le 2e contre le 7e, le 3e contre le 6e, le 4e contre le 5e).

J'ai donc un tableau récapitulant la liste des joueurs et leur classement en fonction de leurs scores. Seulement, il se pourrait que plusieurs joueurs arrivent ex æquo parmi les 8 premiers. Dans l'exemple ci-dessous : 3 joueurs sont 4e, et 2 joueurs sont 7e. Je voudrais pouvoir dans ce cas départager aléatoirement les ex aequo.

Mais je coince totalement.

Comme vous le verrez sur la 3e feuille (phase finale), ma formule actuelle est:
=INDEX(Scores!$A$4:$A$16,EQUIV(A9,Scores!$S$4:$S$16,0))

où A9=4.

Elle marche très bien tant qu'il n'y a pas de doublons. Mais dès qu'il y en a, elle m'affiche le premier de la liste, et les suivants passent à la trappe (avec, forcément, erreur N/A, puisqu'il n'existe ni 5 ni 6).

Je me suis retourné la tête sur ce problème un bon moment, j'ai fouillé internet à la recherche de bribes de réponses à mettre ensemble, mais rien de ce que j'ai essayé à base de ALEA(), ALEA.ENTRE.BORNES(), CHOISIR() n'a fonctionné.
J'avais très envie de trouver seul, mais là, je dois admettre que je bloque.

Merci d'avance !

 

[JHA]

Bonjour à tous,

Peut être en ajoutant le numéro de ligne

=(O4+R4+P4+GRANDE.VALEUR(B4:N4;1)-GRANDE.VALEUR(B$4:B$16;1))+(LIGNE()/10^6)

JHA

 

[Bruine]

Merci pour la réponse.
L'ennui de cette formule, c'est que ça n'a rien d'aléatoire, le premier joueur sera d'office désigné par ordre d'apparition dans la liste.

....

Oh. Mais merci beaucoup en fait. Je partais dans des énormes formules à rallonge pour sélectionner aléatoirement parmi le classement a posteriori, alors que le plus simple était évidemment d'ajouter une variable aléatoire à la source, au calcul du classement.
Plutôt que LIGNE(), j'ai donc ajouté +ALEA()*10^15.

C'est nickel. Encore merci.

 

[JHA]

Bonjour à tous,

Le soucis de la fonction alea(), à chaque changement dans la feuille, il y aura un calcul automatique et tu risques d'avoir des résultats différents.

JHA

 

[Bruine]

J'ai résolu ce souci avec une itération limitée :

=SI(X3>0;X3;ALEA())

qui, si la formule est tapée en X3, permet de figer la valeur aléatoire une fois qu'elle est sortie.

J'aurais volontiers préféré le code plus propre trouvé sur un autre topic du forum

=SI(CELLULE("adresse")=CELLULE("adresse";X3);ALEA();X3)

mais je travaille en fait avec une Google Sheet, qui au contraire d'Excel ne renvoie pas à la dernière cellule modifiée si on ne précise pas de référence à CELLULE(), du coup la formule ne fonctionnait pas.

D'ailleurs tant qu'à faire, pour être tout à fait complet si un futur utilisateur cherchait une solution au même problème, sur OpenOffice Calc (où j'ai voulu exporter mon document), aucune des deux formules ne fonctionne. (Plus précisément, la première renvoie une cellule vide, la seconde continue de générer un nombre aléatoire à chaque calcul.) Il faut donc remplacer par :

=SI(X3="";ALEA();X3)

Pour une raison que j'ignore (peut-être une option qu'il faudrait décocher ?), copier/coller le code renvoie la même valeur sur toutes les cases. Il faut donc le retaper case par case, ou bien utiliser cette formule à rallonge :

=SI(OU(X3="";X3=$X$3);SI(ET(CELLULE("adresse";X3)="$X$3";X3<>"");X3;ALEA());X3)

Et là, on peut copier la formule.
[Edit] Autre solution, qui n'est toujours pas la panacée mais est beaucoup plus économique : comparer le résultat avec tous les résultats de la colonne :

=SI(OU(X3="";NB.SI($X$1:$X$1048576;X3)>1);ALEA();X3)

Avec Excel (ou OpenOffice Calc d'ailleurs), une autre option, manuelle, aurait été de faire F2 - F9 sur chaque case concernée par ALEA(), mais du coup on perd la formule pour un usage futur.

Toujours est-il que tout fonctionne à présent.