Alea entre bornes avec exclusions.

[JJ1]

Bonjour,

J'ai cherché dans le Forum sans solution apparente, une méthode de créer x combinaisons de 20 nombres (A:T) (sans doublon sur la ligne en recopiant vers le bas), hormis une liste d'exclusion de 15 nombres (rose). Est-ce possible?
merci
Bon AM à tous.

 

[Tibo]

Re : Alea entre bornes avec exclusions.

Bonjour,

Un essai vite fait avec une formule matricielle :

=PETITE.VALEUR(SI(NON(NB.SI($W$1:$AK$1;LIGNE(INDIRECT("1:70"))))*LIGNE(INDIRECT(
"1:70"))<>0;NON(NB.SI($W$1:$AK$1;LIGNE(INDIRECT("1:70"))))*LIGNE(INDIRECT("1:70"
)));ENT(ALEA()*55)+1)

Formule matricielle à valider par CTRL + MAJ + ENTREE

Je n'ai pas approfondi, la formule doit pouvoir être simplifiée

Je te laisse faire les tests

@+

 

[job75]

Re : Alea entre bornes avec exclusions.

Bonjour JJ1,

Dans le code de la feuille :

Dim flag As Boolean

Private Sub Worksheet_Calculate()
If flag Then Exit Sub
Dim cel As Range, i&, plage As Range
flag = True
For Each cel In [W1:AK1]
  For i = 0 To [A65536].End(xlUp).Row - 1
    Set plage = [A1:T1].Offset(i)
    While Not plage.Find(cel, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
      plage.Calculate
    Wend
  Next
Next
flag = False
End Sub

Fichier joint.

Edit : salut Tibo, content de te croiser

A+

 

[JJ1]

Re : Alea entre bornes avec exclusions.

Bonsoir Tibo, Job75
Merci pour vos solutions (macro+formule)
Pour un essai "vite fait" quelle formule!
merci bonne soirée

 

[job75]

Re : Alea entre bornes avec exclusions.

Re,

Joli flop, ma solution ne fonctionne pas.

A+

 

[job75]

Re : Alea entre bornes avec exclusions.

Re,

Cette macro doit donner un résultat correct :

Sub Alea()
Dim cel As Range, r As Byte
Randomize
Application.ScreenUpdating = False
For Each cel In [Tableau]
1 r = Int(70 * Rnd) + 1
  If IsNumeric(Application.Match(r, Intersect(cel.EntireRow, [Tableau]), 0)) _
    Or IsNumeric(Application.Match(r, [Exclusion], 0)) Then GoTo 1
  cel = r
Next
End Sub

Elle se lance par Ctrl+A.

Il n'y a plus de formules dans la feuille.

Edit : avec la macro dans un Module, il vaut mieux nommer toutes les plages.

A+

 

[ROGER2327]

Re : Alea entre bornes avec exclusions.

Bonjour à tous

Une autre proposition (à vitesse raisonnable) :

[COLOR=DarkSlateGray][B]Sub toto()
Dim i&, j&, k&, l&, m&, n&, Dat(1 To 25, 1 To 20), oColl As New Collection
  For i = 1 To 25
    Set oColl = Nothing
    On Error Resume Next
    With Range("W1:AK1")
      For j = 1 To .Count
        oColl.Add .Cells(1, j).Value, CStr(.Cells(1, j).Value)
      Next j
    End With
    l = oColl.Count
    For j = 1 To 70
      oColl.Add j, CStr(j)
    Next j
    On Error GoTo 0
    m = oColl.Count
    Randomize
    For k = 1 To 20
      n = l + 1 + Int((m - k - l + 1) * Rnd)
      Dat(i, k) = oColl(n)
      oColl.Remove n
    Next k
  Next i
  Range("A1").Resize(25, 20).Value = Dat
End Sub[/B][/COLOR]

ROGER2327
#4686

Vendredi 6 Sable 138 (Saint Navet et Perruque, humanistes, SQ)
16 Frimaire An CCXIX
2010-W49-1T17:53:27Z

 

[CISCO]

Re : Alea entre bornes avec exclusions.

Bonsoir à tous, bonsoir Roger2327, Job75, Tibo et JJ1

Une solution avec trois formules différentes. On peut peut être faire plus simple...

A vérifier qu'en même.

@ plus

P.S : Je me suis inspiré de ce fil :
https://www.excel-downloads.com/threads/placement-de-chiffres.126234/

 

[CISCO]

Re : Alea entre bornes avec exclusions.

Bonsoir

Une variante en pièce jointe.

@ plus

 

[JJ1]

Re : Alea entre bornes avec exclusions.

Bonsoir à tous,
J'ai testé en fin d'après-midi la formule de Tibo et la macro de Job75, j'ai eu un problème de nombreux doublons avec la formule et pas d'exclusion avec la macro.
Je vais tester les différentes solutions proposées-merci à tous.

Bonne soirée

 

[job75]

Re : Alea entre bornes avec exclusions.

Bonjour le fil, le forum,

J'ai testé en fin d'après-midi la formule de Tibo et la macro de Job75, j'ai eu un problème de nombreux doublons avec la formule et pas d'exclusion avec la macro.

Je ne comprends pas, la macro fonctionnait chez moi...

Je viens tout de même d'éditer le post #6 en nommant les 2 plages.

A+

 

[job75]

Re : Alea entre bornes avec exclusions.

Re,

Si l'on préfère une fonction macro, on peut utiliser (dans Module1) :

Function AleaExclu&(BorneInf&, BorneSup&, Exclusion1 As Range, Exclusion2 As Range)
Dim r&
Application.Volatile
Randomize
1 r = BorneInf + Int(Rnd * (BorneSup - BorneInf + 1))
If IsNumeric(Application.Match(r, Exclusion1, 0)) _
  Or IsNumeric(Application.Match(r, Exclusion2, 0)) Then GoTo 1
AleaExclu = r
End Function

Formule en B1 à copier sur tout le tableau :

=AleaExclu(1;70;$A1:A1;$X$1:$AL$1)

Attention : ne plus toucher au tableau ensuite pour ne pas modifier l'ordre de calcul des cellules...

A+

 

[ROGER2327]

Re : Alea entre bornes avec exclusions.

Bonjour job75

(…)

Je ne comprends pas, la macro fonctionnait chez moi...

(…)

Même constat chez moi. La version d'hier fonctionnait correctement. (Celle de ce matin, un peu plus rapide, aussi).
Bizarre…
​

ROGER2327
#4693

Samedi 7 Sable 138 (Saint Birbe, juge, SQ)
17 Frimaire An CCXIX
2010-W49-2T10:02:01Z

 

[job75]

Re : Alea entre bornes avec exclusions.

Salut Roger,

Et merci d'avoir confirmé mes dires.

Je crains de n'avoir pas été assez explicite au post #12.

Pour entrer la formule il ne faut pas la tirer à partir de B1, mais l'entrer d'un coup :

- sélectionner tout le tableau B1:U25

- entrer la formule =AleaExclu(1;70;$A1:A1;$X$1:$AL$1) dans la barre de formule

- valider par Ctrl+Entrée.

Et ne plus toucher au tableau (sinon on risque le bouclage).

On peut cependant l'agrandir en tirant la dernière ligne vers le bas.

A+