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

 

[MANTALO]

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

Bonjour

Je ne vois pas comment en ligne 1067, à partir du 17, tu trouves 18? une explication merci

 

[williamss]

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

Bonjour

Je ne vois pas comment en ligne 1067, à partir du 17, tu trouves 18? une explication merci

Bonjour Mantalo,

Pour trouver les 18 suivant les 17 voici comment c'est fait :

A partir du premier numéro de ce ce tirage je regarde aux 33 tirages précédents (de bas en haut) le premier numéro qui serrait compris à +ou-5 de 17 donc entre 12 et 22. Voilà que 2 lignes plus haut de ce tirage (ligne 1065) il y a le N°18 ce qui est compris entre 12 et 22 donc je prends ce numéro.

Pour le Numéro 5 à la cellule G1066 c'est idem en se basant au numéro 4 donc en cherchant un numéro entre 1 et 9, et justement au tirage précédent il y a 5.

Avec des formules je n'ai pas réussi à faire ceci, puis je ne connais pas les macros donc avec ceci je ne pourrais pas faire la suite c'est pour cela que je tente de réaliser ceci avec une ou des formules, mais comment ??

Merci

Williams

 

[eriiic]

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

Bonjour,

Et pourquoi ça ne serait pas 19 qui apparait juste avant 18 ? Tu n'as aucune règle qui l'interdit.
Et si en ligne 1066 tu as 20, tu gardes le 20 et tu ne regardes pas au-dessus s'il n'y a pas de valeur plus proche ?

En attendant le principe d'une telle fonction, à mettre dans un module standard :

Function valProche(cellule As Range, plage As Range, Optional ecart As Long = 5) As Long
    Dim datas, c As Range, lig As Long, col As Long
    datas = plage.Value
    For lig = UBound(datas, 1) To 1 Step -1
        For col = 1 To UBound(datas, 2)
            If Abs(datas(lig, col) - cellule) <= ecart Then
                valProche = datas(lig, col)
                Exit Function
            End If
        Next col
    Next lig
End Function

Tu peux la compléter avec plus de tests si tu veux la faire évoluer
Ex syntaxe1 :
En G1067 : =valProche(B1067;$B1057:$F1066)
ici regarde les 10 lignes précédentes. Changer $B1057:$F1066 selon le nombre de lignes voulues.
A tirer vers la droite, puis vers le haut (je ne teste pas si ta plage se retrouve au-dessus de la ligne 1, erreur)
Ex syntaxe2 :
En G1067 : =valProche(B1067;$B1057:$F1066;3)
Idem que précédent mais pour un écart de 3

Sur le même principe tu peux faire une macro à lancer au coup par coup qui te traite une plage complète. Ca sera plus rapide, tout dépend du fonctionnement voulu.
eric

 

[williamss]

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

Bonjour Eriic,

Si en ligne 1066 il y aurait le numéro 20 comme vous dites, alors oui je garderais le numéro 20 comme cela serrait le premier numéro à être compris a 17+ou-5 en partant de la ligne 1067 où se trouve le numéro de base 17.

Je n'ai par contre pas réussi à mettre en place votre macro car je ne connais pas ceci malheureusement.

Donc avec des formules ceci est-ce possible d'être réaliser ?? Si non, comment mettez vous en place votre macro ??

Merci

Williams

 

[eriiic]

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

Alt+F11 pour aller dans VBE.
Le code est dans le module1.
Il faut insérer un module dans ton projet et copier-coller le code.

eric

 

[williamss]

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

Alt+F11 pour aller dans VBE.
Le code est dans le module1.
Il faut insérer un module dans ton projet et copier-coller le code.

eric

Merci Eriic,

cette première étape est bonne.

La deuxième étape serrait pour la colonne H, à partir du numéro trouvé en colonne G (ici 19 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 même 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 à... ???

Merci

Williams

 

[eriiic]

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

Je t'ai fait une fonction souple qui reçoit tout les paramètres.
Donc écrit là avec les paramètres qui t'intéressent.
Function valProche(cellule As Range, plage As Range, Optional ecart As Long = 5)
cellule : valeur à rechercher, donc G19 dans ton cas
plage : où rechercher
ecart : écart accepté, =5 si absent.
C'est bon ?
eric

 

[job75]

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

Bonsoir williamss, MANTALO, eriiiic,

Une solution par formules dans le fichier joint.

Faire bien attention où placer les signes $...

A+

 

[job75]

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

Re,

La définition de Plage1 avec DECALER rendait la formule volatile.

Il vaut mieux la définir simplement par ='+ou-5'!$B1034:$F1066

Fichier (2).

Bonne nuit.

 

[williamss]

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

Je t'ai fait une fonction souple qui reçoit tout les paramètres.
Donc écrit là avec les paramètres qui t'intéressent.
Function valProche(cellule As Range, plage As Range, Optional ecart As Long = 5)
cellule : valeur à rechercher, donc G19 dans ton cas
plage : où rechercher
ecart : écart accepté, =5 si absent.
C'est bon ?
eric

Bonsoir Eric,

J'ai très bien compris ta fonction souple, mais pour la 2ème étape qui est assez semble à la première sauf qu'il ne faut pas que le ou les premiers numéros trouvés mis entre la colonne G et... ne soient pas à nouveau repris. Puis la plage des 33 lignes commence au-dessus de la ligne du nouveau numéro de base donc un peu plus haut que la plage précédente. Ce qui fait que 2 éléments intervient en plus si non le numéro qui serrait trouvé à la colonne H serrait le même que le celui de la colonne G.

Bonsoir williamss, MANTALO, eriiiic,

Une solution par formules dans le fichier joint.

Faire bien attention où placer les signes $...

A+

Bonsoir Job,

J'ai regardé ton fichier, et quand tu écris "Sélectionner G1067 et définir les 4 noms :" je suppose que tu veux dire que pour avoir la formule complète il faut que je remplace les noms dans les formules par leur formule même si ces bous de formules sont nommés. Donc j'ai fais ceci pour mieux voir, se qui me donnerait ceci comme formule, sauf erreur :

=INDEX($A:$F;MAX((DECALER($B1067;-33;;33;5)>=B1067-5)*(DECALER($B1067;-33;;33;5)<=B1067+5)*LIGNE(DECALER($B1067;-33;;33;5)));MAX((INDEX($B:$F;MAX((DECALER($B1067;-33;;33;5)>=B1067-5)*(DECALER($B1067;-33;;33;5)<=B1067+5)*LIGNE(DECALER($B1067;-33;;33;5)))>=B1067-5)*(INDEX($B:$F;MAX((DECALER($B1067;-33;;33;5)>=B1067-5)*(DECALER($B1067;-33;;33;5)<=B1067+5)*LIGNE(DECALER($B1067;-33;;33;5)))<=B1067+5)*COLONNE(INDEX($B:$F;MAX((DECALER($B1067;-33;;33;5)>=B1067-5)*(DECALER($B1067;-33;;33;5)<=B1067+5)*LIGNE(DECALER($B1067;-33;;33;5))))))

Il semble que j'ai fais une erreur comme cela me dit #valeur, mais je ne vois pas où

Pour la 2ème étape citée ci-dessus la formule ne semble pas aller suite aux 2 autres conditions.

Merci

Williams

 

[job75]

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

Re,

Définir un nom c'est utiliser (sur Excel 2003) la commande Insertion => Nom => Définir.

Avec comme je l'ai dit la cellule G1067 sélectionnée (à cause des références relatives).

A+

 

[job75]

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.

 

[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,

Dans ce fichier (3) :

- pour nlig1 j'utilise EXP(LN(xxx)) afin d'éliminer éventuellement la valeur zéro renvoyée

- la 2ème feuille est traitée.

Bonne journée.

 

[job75]

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

Re,

Dans ce fichier l'écart est paramétré de 0 à 10 en cellule P2.

Voyez ce que ça donne pour 0, c'est instructif.

A+