Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précédentes

[williamss]

Bonjour,

Je cherche à faire un tableau sous 2 étapes.

1°) A la colonne G je recherche dans les 33 tirages antérieurs un numéro compris à +/-5 du numéro de la colonne B (donc n°17 au dernier tirage) : donc Boule suivante à N°17 à +ou-5 donc je recherche une boule comprise entre 18 et 22. 2 tirages précédents je trouve le N°18 dans la cellule G1067.

Donc comment peut-on dans une cellule de la colonne G aller chercher dans les 33 lignes précédentes le numéro qui est à la ligne la proche de cette cellule puis qui est compris à +ou-5 du numéro de la colonne B et de sa même ligne ??

Merci

Williams

 

[williamss]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Si vous essayez de tout combiner pour obtenir une formule unique ça ne peut pas marcher.

Il faut impérativement passer comme je l'ai fait par les 4 noms définis.

Re bonne nuit.

Ok, je ne savais pas que si on remplacé les noms par leur formule que ceci pouvait faire que ça ne fonctionnerait pas car je croyais que c'était surtout utilisé pour réaliser des formules qui serrait trop longue avec toutes les formules ou voir pour d'autres choses.

J'ai tenté de modifier la formule pour les colonnes de H à M suivant la 2ème étape citée ci-dessus mais vu la complexité de la formule je n'y suis pas parvenu. La deuxième étape serrait pour la colonne H, à partir du numéro trouvé en colonne G (ici 18 en G1067) comment peut on trouver la valeur comprise à +ou-5 de la valeur de G la plus près de cette ligne dans la plage (33 lignes précédentes la ligne où a été trouvé le nouveau N° de base), donc même technique que pour la colonne G, mais sans que cela soit la valeur de G Idem pour les colonnes de I à M en se basant toujours au numéro trouvé à la colonne précédente et sans que le numéro soit un des numéros trouvés et mis dans les colonnes précédentes de G à... ???

Ce qui fait que 2 éléments interviennent en plus.

Merci

Williams

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Ben dites donc c'est une usine à gaz votre truc

Voyez quand même le fichier (2) joint, il faut des noms définis différents pour chaque colonne.

Noter que le calcul et l'ouverture prennent pas mal de temps...

A+

 

[williamss]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Je reconnais que c'est pas facile et que c'est pour cela que je n'y suis pas arrivé.

J'ai regardé le fichier malgré DECALER à la formule et il semblerait qu'il y a un pb avec la plage des 33 lignes ou..., car voici un exemple :

Au dernier tirage je me base au N°17 et à 2 ligne plus haut je trouve le 1er N° à+ou-5 de 17 qui est 18. Puis en me basant à 18 le premier n° plus haut à +ou-5 de 18 est 2 ligne plus haut 14. Ici cela mets 16 qui est aussi dans cet intervalle donc c'est tout de même bon. Puis en me basant à 16 qu'ici cela à pris cela à trouvé le N°14 à la ligne au-dessus qui est dans l'intervalle 16+ou-5. Mais après, cela à pris le N°16 qui se trouve 5 lignes plus haut alors que 3 lignes plus haut du N° au quel on se base il y a le premier numéro dans l'intervalle 16+ou-5 qui est N°13. Puis le N°16 avait déjà été pris à la colonne H donc suite à ceci ne il faudrait pas le reprendre.

Donc le pb suivant cet exemple c'est que cela reprends des N° déjà pris puis que parfois ça ne prends pas le premier numéro qui est dans l'intervalle +ou-5 du dernier numéro trouvé et dans la plage des 33 lignes au-dessus de ligne du derniers numéro.

Etonnant, la colonne M ne fonctionne pascontrairement aux autres

merci

Williams

 

[eriiic]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Bonjour,

La deuxième étape serrait pour la colonne H, à partir du numéro trouvé en colonne G (ici 19 en G1067)

J'ai du mal à saisir la logique complète de ton truc.
Donc le point de départ en G est toujours le plus proche du nombre de la colonne B. Tu ne te sers jamais de ceux en colonne C:F ?
Et tu vas ajouter beaucoup de règles au fur et à mesure ?

Sache quand même que les boules n'ont pas de mémoire. La 17 ne se rappellera jamais qu'elle est sortie la semaine dernière pour diminuer sa probabilité de sortie.
A chaque tirage chaque boule a la même probabilité de sortie. Même si la 17 est sortie 1000 fois sur les 1000 derniers tirages, elle aura toujours 1 chance/49 de sortir.
Il n'existe pas et n'existera jamais de martingale basé là-dessus...
Je pense que je vais m'arrêter là.

eric

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Je pense que je vais m'arrêter là.

Moi aussi, à bientôt.

 

[williamss]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Merci à vous deux pour votre aide même si la 2ème étape est ce qu'il faudrait encore voir .

Si non, Eric je ne rajoute pas des règles au fur et à mesure car pour la première recherche (colonne G) du fait qu'il n'y a pas eu de recherche avant celle-ci alors ça fait que les 2 éléments qui interviennent en plus pour les autres recherches ne peuvent pas avoir lieu pour la première. C'est pour cela que c'est en 2 étapes. Puis entre la recherche en colonne H et M c'est les mêmes règles.

A+

Williams

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Le fichier (2) du post #17 n'allait pas : les références relatives ne doivent être que sur la ligne.

Ce fichier (3) fait je pense ce que vous souhaitez, mais avec un gros bémol.

Chez moi sur Win 8 - Excel 2013 impossible d'entrer les formules en colonnes L et M, si l'on essaie Excel plante.

A+

 

[williamss]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Merci pour ce fichier dont les 5 numéros trouvés sur les 7 par ligne dans le fichier sont exactement cela

Je suppose que dans Excel 2013 il est impossible d'entrer les formules en colonnes L et M, avec W8 que ceci vient du poids des formules même si c'est mis que sur quelque lignes.

Williams

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Je pense que le problème du plantage vient des points suivants :

- La fonction DECALER rend les formules volatiles

- à cause des références relatives les formules sont recalculées pour toutes les lignes de la feuille.

A+

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Les solutions par formules ont montré leurs limites.

Passons donc au VBA avec cette macro dans le fichier joint :

Sub MAJ()
Dim h&, ecart&, tablo, P As Range, t(), i&, lig&
Dim j%, x, test As Boolean, i1&, j1%, k%
h = 33
With Feuil1 'CodeName
  ecart = .[P2]
  tablo = .Range("B5:F" & .Range("B" & .Rows.Count).End(xlUp).Row)
  Set P = .[G5].Resize(UBound(tablo), 7)
End With
ReDim t(1 To UBound(tablo), 1 To 7)
For i = 1 To UBound(t)
  lig = i
  For j = 1 To 7
    If j = 1 Then x = tablo(i, 1) Else x = t(i, j - 1)
    For i1 = lig - 1 To IIf(lig > h, lig - h, 1) Step -1
      For j1 = 5 To 1 Step -1
        If j = 1 Then
          test = tablo(i1, j1) <> x
        Else
          test = True
          For k = 1 To j - 1
            If tablo(i1, j1) = t(i, k) Then test = False: Exit For
          Next
        End If
        If tablo(i1, j1) >= x - ecart And tablo(i1, j1) <= x + ecart _
          And test Then t(i, j) = tablo(i1, j1): lig = i1: GoTo 1
      Next j1
    Next i1
1 Next j
Next i
'---restitution---
P = t
P.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 38 'rose
P.SpecialCells(xlCellTypeConstants).Interior.ColorIndex = xlNone
End Sub

Sur 1067 lignes la mise à jour du tableau est immédiate.

Edit : avec écart = 5 notez la cellule M896.

Bonne nuit.

 

[williamss]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re Job75,

Comme vous dites, on voit bien la limite par formules pour ceci.

Par VBA c'est excellent. Par contre comme ici ce n'est pas une macro sous un module standard comment peut-on faire qu'il y a 7 recherches semblables pour les 4 autres boules sorties aux tirages puisque pour l'instant on a fait cela sur la première boule ??

Car ne connaissant pas les macros je n'ai pas réussi à mettre en place pour les 4 autres numéros.

Merci

Williams

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Bonjour williamss, le forum,

- de quel tirage parlez-vous, du tirage B2:F2 ou de chacune des lignes ?

- joignez un fichier montrant comment vous voulez disposer les résultats.

A+

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Vous voulez peut-être ce fichier (2) avec la macro modifiée :

Sub MAJ()
Dim h&, ncol1%, ncol2%, ecart&, tablo, boule%, P As Range, t(), i&, lig&
Dim j%, x, test As Boolean, i1&, j1%, k%
h = 33 'hauteur de la plage de recherche
ncol1 = 5: ncol2 = 7 'nombres de colonnes
With Feuil1 'CodeName
  ecart = .[P2]
  tablo = .Range("B5", .Range("B" & .Rows.Count).End(xlUp)).Resize(, ncol1)
  For boule = 1 To ncol1
    Set P = .[G5].Offset(, ncol2 * (boule - 1)).Resize(UBound(tablo), ncol2)
    ReDim t(1 To UBound(tablo), 1 To ncol2)
    For i = 1 To UBound(t)
      lig = i
      For j = 1 To ncol2
        If j = 1 Then x = tablo(i, boule) Else x = t(i, j - 1)
        For i1 = lig - 1 To IIf(lig > h, lig - h, 1) Step -1
          For j1 = ncol1 To 1 Step -1
            If j = 1 Then
              test = tablo(i1, j1) <> x
            Else
              test = True
              For k = 1 To j - 1
                If tablo(i1, j1) = t(i, k) Then test = False: Exit For
              Next
            End If
            If tablo(i1, j1) >= x - ecart And tablo(i1, j1) <= x + ecart _
              And test Then t(i, j) = tablo(i1, j1): lig = i1: GoTo 1
          Next j1
        Next i1
1     Next j
    Next i
    '---restitution---
    P = t
    P.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 38 'rose
    On Error Resume Next ' si ecart = 0
    P.SpecialCells(xlCellTypeConstants).Interior.ColorIndex = xlNone
    On Error GoTo 0
  Next boule
End With
End Sub

Afin de généraliser j'ai mis des variables pour les nombres de colonnes.

A+

 

[job75]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Re,

Avec les fichiers (1) et (2), avec écart = 3, voyez la cellule M67...

La cellule à gauche est vide mais est considérée comme zéro.

Pour éviter cela j'ai ajouté le critère x <> "" dans ce fichier (3).

A+

 

[williamss]

Re : Trouver un n° sous un écart de +ou-5 suivant un autre n° dans les lignes précéde

Merci Bp Job ce dernier fichier est parfait et c'est justement ce que javais cherché à réaliser par formules mais impossible à réaliser

Car ainsi tout est bon

Williams