Formule ecart maxi

[choupette51]

Bonjour

Voici un exemple pour mieux comprendre la formule attendue.Regarde la pièce jointe ecartColonne.xls

merci d'avance à tous les intervenants.

 

[JeanMarie]

Re : Formule ecart maxi

Re...

Pour corriger ce petit point, il suffit de prendre en compte une longueur de plage égale au nombre de valeurs connues et celle à venir.
Ce qui donne

=MAX(FREQUENCE(LIGNE($1:$105);SI(B$3:B$106=A3;LIGNE($1:$104);)))-1

@+Jean-Marie

 

[ROGER2327]

Re : Formule ecart maxi

Re...

Je viens de modifier le classeur précédent pour y ajouter les valeurs attendues par choupette51.

(...) rester sans colonne intermédiaire (ce qui est pour moi une contrainte exagérée dans ce contexte) (...)

Il est vrai que c'est un peu lourd. Mais que faire d'autre pour atteindre le résultat souhaité par notre ami ?
Au passage, je n'ai toujours pas compris ce qu'il veut réellement : attend-il 36 comme écrit dans son classeur ou 37 comme le donne votre formule qui le satisfait ? Mystère...
Mais comme vous dites justement :

il y aura eu au moins un gain à ce loto, celui d'avoir fait travailler nos neurones à tous

Cordialement,
ROGER2327

 

[choupette51]

Re : Formule ecart maxi

Bonjour Roger

j'attend bien 36 et la formule d'Eriiiic me donne bien 36.

Regarde la pièce jointe ecartColonne.xls
petit détail constaté si je rajoute 5 sur la colonne B j'obtient 36 par Jean marie et 37 par Eriiiic.
je devrais avoir 36

 

[choupette51]

Re : Formule ecart maxi

suite

je remet un fichier avec une donnée suplementaire
ecart du n°8 est bien 18 et pas 17 comme sur le dernier envoi,un oubli de ma part

 

[eriiic]

Re : Formule ecart maxi

petit détail constaté si je rajoute 5 sur la colonne B j'obtient 36 par Jean marie et 37 par Eriiiic.
je devrais avoir 36

Oui, j'ai calculé à partir du tableau fourni. Faut faire évoluer la plage, remplace $B$106 par $B$200 ou $B$500 ou ...

eric

 

[JeanMarie]

Re : Formule ecart maxi

Re...

Attention Choupette, si tu rajoutes une valeur dans ta colonne B, tu dois aussi adapter les plages.

=MAX(FREQUENCE(LIGNE($1:$106);SI(B$3:B$107=A3;LIGNE($1:$105);)))-1

Regarde du côté de la définition des plages dynamiques avec la fonction DECALER, pour éviter de modifier les plages à chaque rajout ou enlèvement de valeurs.

PS : Dans cette formule, il n'est pas possible de spécifier une plage de 500 cellules, quand tu n'as que 115 valeurs, elle doit forcément être adaptée à la plage de valeurs.

@+Jean-Marie

 

[choupette51]

Re : Formule ecart maxi

salut Jean Marie

ok pour la modif avec décaler
je n'est pas réussi cette modif.
si tu veux transforme ta formule en conséquence Merci

 

[JeanMarie]

Re : Formule ecart maxi

Re...

Pour définir une plage dynamique, on utilise la boîte de dialogue d'insertion de nom (menu Insertion/Nom/définir...)
Dans la boîte de dialogue :
- Dans le champ "Noms dans le calsseur", tu indiques le nom (exemple PlageA)
- Dans le champ "fait référence à : ", tu colles la formule ci-dessous :

=DECALER(Feuil1!$A$1;;;NB(Feuil1!$B:$B)+1)

et tu cliques sur le bouton ajouter.

Procède de la même manière pour :
- Plage B, =DECALER(Feuil1!$B$3;;;NB(Feuil1!$B:$B))
- Plage C, =DECALER(Feuil1!$A$1;;;NB(Feuil1!$B:$B))

Ensuite tu changes la formule,

=MAX(FREQUENCE(LIGNE(PlageA);SI(PlageB=A3;LIGNE(PlageC);)))-1

@+Jean-Marie

 

[ROGER2327]

Re : Formule ecart maxi

Bonjour à tous

Le sujet ayant l'air d'intéresser du monde, je fais une proposition plus générale avec une fonction écrite en VBA :

LSeq(Plage;Valeur;[Sens])

Plage_____est une plage de cellules contigües.
Valeur____est un nombre ou une chaine de caractères.
Sens______est un paramètre optionnel prenant la valeur "V" (par défaut) ou "H".​

La fonction renvoie un entier long donnant la longueur de la plus longue séquence d'éléments de Plage ne contenant pas la valeur Valeur. Les valeurs Empty figurant dans Plage sont ignorées.
Le paramètre Sens détermine l'ordre de lecture des éléments de Plage :

V (ou omis) : lecture du haut vers le bas, puis de gauche à droite ;
H : lecture de gauche à droite, puis du haut vers le bas.​

Si la valeur Valeur ne figure pas dans la plage Plage, la fonction renvoie le nombre d'éléments non vides de la plage.​

[B][COLOR="DarkSlateGray"]Option Explicit

Function LSeq(r As Range, v, Optional s As String = "V") As Long
Dim oDat, l As Long, c As Long, w As Long
   Set r = Intersect(r, Sheets(r.Parent.Name).UsedRange) [COLOR="SeaGreen"]'pour réduire la plage aux données utiles.[/COLOR]
   ReDim oDat(1 To r.Rows.Count, 1 To r.Columns.Count)
   For l = 1 To UBound(oDat, 1) [COLOR="SeaGreen"]'parce que la définition de 'Range' n'est pas univoque.[/COLOR]
      For c = 1 To UBound(oDat, 2)
         oDat(l, c) = r.Cells(l, c)
      Next c
   Next l
   If UCase(s) <> "H" And l > 2 And c > 2 Then oDat = Application.Transpose(oDat) [COLOR="SeaGreen"]'parce que 'transpose' n'est pas un opérateur de transposition.[/COLOR]
   For l = 1 To UBound(oDat, 1)
      For c = 1 To UBound(oDat, 2)
         If Not IsEmpty(oDat(l, c)) Then If oDat(l, c) = v Then LSeq = Application.Max(LSeq, w): w = 0 Else w = w + 1
      Next c
   Next l
   LSeq = Application.Max(LSeq, w)
End Function[/COLOR][/B]

Exemples de mise en œuvre dans le fichier joint.​

Bonne journée.
ROGER2327

 

[choupette51]

Re : Formule ecart maxi

Bonjour à tous

Merci Roger pour cette nouvelle formule en VBA c'est top Bravo.
je n'y connais rien en VBA mais ça fonctionne à merveille, après plusieurs essais de rentré :Chapeau 😀
Je vais profité de votre connaissance pour vous demander le même genre de formule VBA sur 5 colonnes.

encore merci de vous creuser la tête juste pour un loisir, mais comme vous dite beaucoup de lecteur vont voir ce post....

Merci d'avance.

 

[JeanMarie]

Re : Formule ecart maxi

Bonjour tout le monde

Si je peux te donner un conseil, regarde les fils ouverts par JJ1, tu trouveras bon bonheur au niveau de l'analyse des tirages du loto et autres, et le tout en formule.

@+Jean-Marie

 

[ROGER2327]

Re : Formule ecart maxi

Bonjour choupette51

(...)
Je vais profité de votre connaissance pour vous demander le même genre de formule VBA sur 5 colonnes.
(...)

Vous l'avez, cette formule :

LSeq(Plage;Valeur;[Sens])

Plage_____est une plage de cellules contigües.
Valeur____est un nombre ou une chaine de caractères.
Sens______est un paramètre optionnel prenant la valeur "V" (par défaut) ou "H".​

Si vous voulez traiter, par exemple, la plage B10:F500, il vous suffit de l'écrire dans la fonction LSeq :

=LSeq([B]$B$10:$F$500[/B];[I]Valeur[/I];[[I]Sens[/I]])

ROGER2327

 

[choupette51]

Re : Formule ecart maxi

Bonjour Roger

Merci JeanMarie pour l'info

je vous joint un petit exemple
j'ai comme vous m'avez dit mais le résultat n'est pas bon j'ai du me planter...

 

[choupette51]

Re : Formule ecart maxi

suite
je remet un morceau de fichier maxi 48k
Regarde la pièce jointe 5.colonnes.xls

 

[ROGER2327]

Re : Formule ecart maxi

Re...

Je viens de regarder votre classeur. Pourriez-vous dire précisément les résultats qui ne vous conviennent pas ? (Par exemple, En I6, on trouve 50, mais il faudrait trouver ...)​

ROGER2327