Excel - tirage nombre aléatoires distincts

[zenxbear]

Bonjour

J'ai du mal à saisir les subtilités d'une formule sous excel.

Je veux faire afficher dans 4 cases adjacentes sur une même ligne, 4 nombre aléatoires distincts compris entre 1 et 23.
Initialement, je connais la méthode ou l'on crée 23 colonnes, peuplées de ALEA() et on utilise la fonction RANG(A1; A1:A23) pour avoir le rang des elements A1 à A4 dans la ligne A1:A23. Ensuite on pouvait cacher les colonnes inutiles, et ne faire apparaître que ce qui compte.

Mais j'avais trouvé une autre méthode. directe. Plus légère.

Case A2 vide
Case B2 contient:
=PETITE.VALEUR(SI(NB.SI($A2:A2 ,LIGNE(INDIRECT("1:23")))=0,LI GNE(INDIRECT("1:23"))),ENT( ALEA()*(24-COLONNES($A2:B2)+1)+1))

 Cliquez pour afficher

Case C2 contient:
=PETITE.VALEUR(SI(NB.SI($A2:B2 ,LIGNE(INDIRECT("1:23")))=0,LI GNE(INDIRECT("1:23"))),ENT( ALEA()*(24-COLONNES($A2:C2)+1)+1))
Case D2 contient:
=PETITE.VALEUR(SI(NB.SI($A2:C2 ,LIGNE(INDIRECT("1:23")))=0,LI GNE(INDIRECT("1:23"))),ENT( ALEA()*(24-COLONNES($A2:D2)+1)+1))
Case E2 contient:
=PETITE.VALEUR(SI(NB.SI($A2:D2 ,LIGNE(INDIRECT("1:23")))=0,LI GNE(INDIRECT("1:23"))),ENT( ALEA()*(24-COLONNES($A2:E2)+1)+1))

Je comprends que:
1/ ENT( ALEA()*(24-COLONNES($A2:B2)+1)+1) => génère un nombre aléatoire entre 1 et 24- qqchose

2/ NB.SI($A2:A2,LIGNE(INDIRECT("1 :23")))=0 retourne { composé de VRAI et de FAUX selon que les éléments de $A2:A2 soient dans la liste {1, 2, ... 23} ou pas

3/ SI(NB.SI($A2:B2,LIGNE(INDIRECT ("1:23")))=0,LIGNE(INDIRECT("1 :23"))) retourne une liste {nombres de 1, 2, ...23 , et FAUX }. Il s'agit de SI(range1, range2). Ceci n'est pas dans l'aide.

4/ Et qu'enfin PETITE.VALEUR fait ce qui lui est demandé.

Ceci marche sans problème. Et je peux vérifier avec le module d'évaluation de formule que le processus se passe correctement.

Mes 2 questions sont:

pourquoi si je remplace dans la formule précédente de B2 "ENT(...)" par un nombre > 1, le programme plante?

plus précisément, la formule s'évalue différemment:
> à l'étape NB.SI($A2:B2,LIGNE(INDIRECT("1 :23"))) on obtient NB.SI($A2:B2,1)... du coup ca n'est plus une matrice!
=> PETITE.VALEUR rend une erreur "#NOMBRE!"

pourquoi je laisse la colonne A vide?

Ce code j'ai du le garder depuis qq années. Je ne sais plus d'ou je l'avais sorti. J'aimerai comprendre les subtilités de ces commandes. Et pourquoi ce changement. Je ne trouve pas de détails dans l'aide d'excel.

En pièce jointe le mini ficher excel pour suivre pas à pas l'evaluation faite par excel, avec une ligne qui marche. Et la seconde ligne avec l'erreur que je veux comprendre. C'est sous Microsoft Excel 2010.
-----

[cherbe]

Bonsoir
La question ne serait pas : pourquoi faire simple quand on peut faire compliqué ?
Si tu avais cherché la solution de facilité, tu aurais utilisé =ALEA.ENTRE.BORNES(1;23)

[zenxbear]

1/ ALEA.ENTRE.BORNE ne suffit pas à elle seule. Il faut sortir 4 tirages differents entre 1 et 23.
2/ mettre ALEA.ENTRE.BORNES(1, 23) dans la formule de B2 à la place de ENT( ALEA()*(24-COLONNES($A2:B2)+1)+1)
donne la même erreur #Nombre!

[cherbe]

1/ ALEA.ENTRE.BORNE ne suffit pas à elle seule. Il faut sortir 4 tirages differents entre 1 et 23.
2/ mettre ALEA.ENTRE.BORNES(1, 23) dans la formule de B2 à la place de ENT( ALEA()*(24-COLONNES($A2:B2)+1)+1)
donne la même erreur #Nombre!

Ce ne serait pas ça ton erreur :
COLONNES ?

[A voir en vidéo sur Futura]

[cherbe]

1/ ALEA.ENTRE.BORNE ne suffit pas à elle seule. Il faut sortir 4 tirages differents entre 1 et 23.

Je ne sais pas ce que tu cherches exactement mais des nombres aléatoires différents de (...) Ce ne sont plus tout à fait des nombres aléatoires !
D'autre part, tu es sûr de ta virgule séparatrice d'arguments dans les fonctions ?

[Stan_94]

Hello,
l'erreur #Nombre dans la cellule E3 du fichier exemple vient de la valeur 5 qui dépasse le nombre d'élément de la "liste" à évaluer via la fonction PETITE.VALEUR.
Et si je ne me trompe pas, la liste à évaluer ne comporte qu'un nombre, qui est le retour de l'évaluation avec SI...

[Dlzlogic]

Bonjour,
Je pense que ce problème de "tirage sans remise" n'est pas si simple à résoudre.
Personnellement, je n'ai pas trouvé d'autre solution (programmation classique) que de faire des tirages "jusqu'à ce que ça marche".
Comme je n'utilise pas Excel pour ce genre de chose, je n'ai pas de solution autre que de faire une macro avec un while.