Bonjour,

Dans une colonne on a des numéros (1 numéro pour chaque tirage).

Quelles formules (pas de macro et VBA) permettraient de calculer :

- le maximum de fois qu'on retrouve chacun de ces numéros 10 numéros qui peuvent sortir aux tirages sur tant de tirages à la suite dont le nombre de tirages a la suite est en ordonnée dans le tableau ??

- le nombre de fois qu'on a retrouvé le maximum de sorties à la suite sur X tirage à la suite de chacun de ces 10 numéros en se basant aux résultats précédents dans le 1er tableau ??

Voir ce fichier : http://climatologie.pagesperso-orange.fr/tirage.xls

merci

Williams

Bonjour ,

A priori, je ne vois pas par quelle formule on pourrait arriver à ce que tu recherches.

Par macro, bien sûr, c'est plus facile.

J’imagine que tu recherches le meilleur moyen de gagner au loto.

Des centaines, voire des milliers de personnes ont fait ou font ce genre de calcul.

A mon avis, mais cela n'engage que moi, le meilleur moyen de gagner, ou de ne pas perdre, est de ne pas jouer.

Mais ce ne serait pas du goût du ministère des finances !

@ +

P.S. : Je t'envoie un message privé, hors sujet.

Salut,

J'ai un peu de mal à te suivre (dois être fatigué ce soir ). Je vais essayer de reformuler sur ton tableau.

Il y a une colonne de nombres, la colonne B. Chaque cellule de la colonne contient un nombre de 1 à 10 (10 nombres possibles donc).

Tu veux savoir en considérant un nombre de cellules consécutives (de 5 à 17), en décomptant dans chacun de ces groupes le nombre de fois où chaque nombre apparaît, établir le maximum d'apparitions dans un groupe de chaque nombre.

Exemple : la colonne est servie de la ligne 4 à la ligne 772, il y a donc 769 cellules servies. On peut y considérer 765 groupes de 5 cellules consécutives. Dans chacun de ces 765 groupes, le nombre 1 peut théoriquement apparaître de 0 à 5 fois. Il faut donc voir combien de fois il apparaît dans chaque groupe et retenir le max de son apparition dans un des groupes. Même chose pour les autres nombres. Et même chose pour les groupes de 6, 7, ... 17.

Et la 2e question serait : le maximum établi à la 1re question, dans combien de groupes a-t-il été atteint ?

Merci de confirmer ou non l'interprétation des questions.

ferrand a écrit :

Salut,

J'ai un peu de mal à te suivre (dois être fatigué ce soir ). Je vais essayer de reformuler sur ton tableau.

Il y a une colonne de nombres, la colonne B. Chaque cellule de la colonne contient un nombre de 1 à 10 (10 nombres possibles donc).

Tu veux savoir en considérant un nombre de cellules consécutives (de 5 à 17), en décomptant dans chacun de ces groupes le nombre de fois où chaque nombre apparaît, établir le maximum d'apparitions dans un groupe de chaque nombre.

Exemple : la colonne est servie de la ligne 4 à la ligne 772, il y a donc 769 cellules servies. On peut y considérer 765 groupes de 5 cellules consécutives. Dans chacun de ces 765 groupes, le nombre 1 peut théoriquement apparaître de 0 à 5 fois. Il faut donc voir combien de fois il apparaît dans chaque groupe et retenir le max de son apparition dans un des groupes. Même chose pour les autres nombres. Et même chose pour les groupes de 6, 7, ... 17.

Et la 2e question serait : le maximum établi à la 1re question, dans combien de groupes a-t-il été atteint ?

Merci de confirmer ou non l'interprétation des questions.

Oui c'est bien cela pour la 1ère question. Pour la 2ème question si par exemple dans le 1er tableau dans la cellule G5 cela nous dit que pour 5 cellules consécutives le maximum de numéro 2 à la suite est de 4 numéros alors dans le 2ème tableau à la cellule G25 il faudrait une formule permettant de savoir combien de fois cela c'est déroulé sur les 769 cellules de la colonne B.

Merci

Williams

Bien !

Première chose : nommer ta plage (colonne) de façon dynamique (pour pouvoir l'allonger) : j'ai donné le nom Tirages, qui réfère à :

=DECALER(Feuil1!$B$4;;;NBVAL(Feuil1!$B:$B);1)

Bien entendu, rien d'autre que tes nombres dans la colonne, et pas de vides à l'intérieur de la liste.

Formule de F5 : (c'est une formule matricielle...)

{=MAX(NB.SI(DECALER(Tirages;LIGNE(INDIRECT("$1:$"&NBVAL(Tirages)-$E5+1))-1;;$E5;1);F$4))}

A étendre sur F5:O17.

{=SOMME((NB.SI(DECALER(Tirages;LIGNE(INDIRECT("$1:$"&NBVAL(Tirages)-$E25+1))-1;;$E25;1);F$24)=F5)*1)}

A étendre sur F25:O37.

Suis pas assez en forme pour vérifier ce soir . Et je viens d'égarer ton fichier.

Pas retrouvé sur mon disque ! J'en ai donc refais un.

http://cjoint.com/?CLjwbmEvbso

J'essaierai de vérifier demain si je trouve un moment. Je n'ai pas les idées très nettes pour ça ce soir.

Bon ! J'ai monté une petite macro de comptage ce matin pour vérifier les calculs : résultats rigoureusement identiques.

Je te confirme donc la validité des formules.

Les calculs se mettront à jour au fur et à mesures que tu ajouteras des tirages.

ferrand a écrit :

Bon ! J'ai monté une petite macro de comptage ce matin pour vérifier les calculs : résultats rigoureusement identiques.

Je te confirme donc la validité des formules.

Les calculs se mettront à jour au fur et à mesures que tu ajouteras des tirages.

Merci bp Ferrand

Peut-on utiliser la même formule sur 5 colonnes la suite, c'est a dire qu'au lieu que la plage "tirages" soit sur une colonne qu'elle soit sur 5 colonnes et donc qu'on regarde 5 numéros par tirages au lieu de 1 numéro comme c'est le cas ici ??

Merci

Williams

Peut-on utiliser la même formule sur 5 colonnes la suite, c'est a dire qu'au lieu que la plage "tirages" soit sur une colonne qu'elle soit sur 5 colonnes et donc qu'on regarde 5 numéros par tirages au lieu de 1 numéro comme c'est le cas ici ??

Je vois pas bien ce que tu veux faire (notamment ce que tu entends par "regarder 5 numéros par tirages au lieu de 1"), mais la formule actuelle est conçue pour fonctionner sur une même colonne (pas plusieurs simultanément). Si on peut en bâtir une prenant en compte 5 colonnes, il faudrait que chaque colonne contienne le même nombre de valeur, elle ne traiterait pas 5 nombres différents à la fois, elle donnerait les résultats pour chaque nombre séparément pour l'ensemble des 5 colonnes.

Tu te spécialises dans les casse-têtes .

Oui j'ai bien vu que la formule actuelle est conçue pour fonctionner sur une même colonne.

Ce dont je veux dire c'est que par exemple quand on regarde le maximum de fois qu'on peut avoir le numéro 2 sur 10 tirages à suite alors cela regarderait sur 10 lignes x 5 nombres (5 colonnes) = 50 numéros le maximum de fois qu'il y a pu avoir le numéro 2. Dans cet exemple ceci ne peut pas dépasser 10 fois bien sur puisque sur une ligne il peut y avoir qu'une fois le même numéro.

Est-ce possible avec quelques modifications ??

Williams

Re,

S'il s'agit de faire la même opération sur 5 colonnes au lieu d'une, la formule devrait pouvoir fonctionner (car on ne modifie pas le décalage successif en lignes constituant les groupes). Si tu as un fichier sur 5 colonnes, on pourra tester la chose.

Re,

Voici justement un fichier où avec votre formule et quelques modifications j'avais tenté de faire ce dont je vous ai dis pour voir mais que je n'ai pas réussi à réaliser : http://climatologie.pagesperso-orange.fr/loto-5-nombres.xls

En modifiant le nombre dans la cellule violette B2 cela permet d'avoir des idées si oui ou non cela fonctionne.

merci

Williams

Bonjour,

Ajustement formules (et plage nommée) : http://cjoint.com/?CLnjvMceA4M

Aligné sur ta première organisation : pas d'en-têtes, rien d'autres que les nombres testés dans les colonnes concernés. (A ne pas perdre de vue.)

Plage définie sur 5 colonnes comme demandé au départ. Pour prendre en compte la 6e colonne, il suffit de remplacer 5 par 6 dans la définition de la plage.

ferrand a écrit :

Bonjour,

Ajustement formules (et plage nommée) : http://cjoint.com/?CLnjvMceA4M

Aligné sur ta première organisation : pas d'en-têtes, rien d'autres que les nombres testés dans les colonnes concernés. (A ne pas perdre de vue.)

Plage définie sur 5 colonnes comme demandé au départ. Pour prendre en compte la 6e colonne, il suffit de remplacer 5 par 6 dans la définition de la plage.

Bonjour,

J'ai regardé mais il semblerait que cela ne fonctionne pas correctement car par exemple pour le numéro 1 le résultat pour savoir sur 5 tirages à la suite le maximum de fois qu'on retrouve ce numéro est de 2 avec la formule alors qu'on en trouve au moins 3 par exemple du 31/08/2009 au 09/09/2009. Même pour 11 tirages à la suite le maximum de fois qu'on retrouve ce numéro est de 2 avec la formule alors qu'on en trouve au moins 4 à plusieurs endroits.

Ceci ne serrait il pas du que la plage nommée est que la colonne B et non de de B à F pour tenir compte des 5 colonnes en même temps lors du calcul contrairement à l'autre fichier où on se basait à 1 colonne ??

Puis à moins que je me trompe, le $E5 ne serrait il pas une erreur dans la cellule L5 et ne devrait il pas être remplacer par $K5, et donc à voir pour les autres cellules ??

merci

Williams

Puis à moins que je me trompe, le $E5 ne serrait il pas une erreur dans la cellule L5 et ne devrait il pas être remplacer par $K5, et donc à voir pour les autres cellules ??

Très juste, un qui m'avait échappé !

Mais ce n'est pas tout , 2 autres corrections nécessaires dans la formule :

{=MAX(NB.SI(DECALER(Tirages;LIGNE(INDIRECT("$1:$"&NBVAL(Tirages)/5-$K5+1))-1;;$K5;5);L$4))}

et pareil pour la seconde.

http://cjoint.com/?CLnnSHdsY1u

Ceci ne serrait il pas du que la plage nommée est que la colonne B et non de de B à F pour tenir compte des 5 colonnes en même temps lors du calcul contrairement à l'autre fichier où on se basait à 1 colonne ??

Non, la référence dynamique de la plage est fournie par la formule : =DECALER(cellule sup. gauche de la plage;;;Nb de lignes;Nb de colonnes). Le nombre de colonnes est défini par le 5 figurant en dernier argument.

Merci Ferrand,

Et si je ne me trompe pas la plage "tirage" est tout comme si on mettait $B$1:$B$999 mais avec ou sans les $ pour être sûr ??

Puis il semble y avoir un pb car par exemple si au début du tableau des données du loto vous mettez un 2 à la place d'un des numéros sur 5 lignes à la suite comme ci-dessous alors la valeur de la cellule M9 va bien passer à 5 comme il le faut mais voila que la valeur de la cellule M10 puis M11 et M12 va aussi passer à 5 alors que cela ne devrait pas modifier celles-ci puisque du 18/10/2008 jusqu'au 01/11/2008 il n'y a pas de numéro 2

06/10/2008 : 02 33 41 24 27

08/10/2008 : 24 02 32 41 22

11/10/2008 : 46 22 02 39 20

13/10/2008 : 11 10 02 37 48

15/10/2008 : 49 28 02 20 40

18/10/2008 : 44 35 04 32 39

20/10/2008 : 43 07 48 31 15

22/10/2008 : 11 28 12 48 14

25/10/2008 : 13 11 22 46 19

27/10/2008 : 46 37 44 04 06

Merci

Williams

La plage Tirages, en l'état actuel correspond à $B$5:$F$817.

M9 recueille le maximum de 2 trouvés dans une plage de 5 lignes sur 5 colonnes incluse dans Tirages.

Les cellules qui suivent, M10, M11, M12 calculent toujours le maximum de 2 mais dans des plages respectivement de 6, 7 et 8 lignes. Par définition, si la valeur en M9 est 5. M10 ne pourra avoir moins ! Ce sera 5 ou plus. Et M11 ne pourra être inférieur à M10, ainsi de suite...

Ok, merci bp pour cette aide

Il est tout de même étonnant que pour un fichier pas lourd que cela mets un certain temps à faire ces calculs.

Williams

Bonjour ,

Je viens de voir que ferrand a résolu ton problème à l'aide de quelques formules.

Je connais bien Excel, mais je n'aurais pas trouvé (dés que le pb est un peu ardu, je pense macro). Chapeau à lui !

Bonne continuation dans tes recherches sur la climatologie.

poussebois

poussebois a écrit :

Bonjour ,

Je viens de voir que ferrand a résolu ton problème à l'aide de quelques formules.

Je connais bien Excel, mais je n'aurais pas trouvé (dés que le pb est un peu ardu, je pense macro). Chapeau à lui !

Bonne continuation dans tes recherches sur la climatologie.

poussebois

Merci Poussebois,

Avec Excel on peut presque tout calculer voir même des mouvements astronomiques assez compliqués comme par exemple le mouvement (position, vitesse, accélération...) du Soleil autour du barycentre (centre centrifuge) du Système-Solaire comme ici http://la.climatologie.free.fr/soleil/soleil1.htm#cause et la page suivante on peut le voir.

Williams

williamsss a écrit :

Ok, merci bp pour cette aide

Il est tout de même étonnant que pour un fichier pas lourd que cela mets un certain temps à faire ces calculs.

Williams

Le calcul a faire reste plutôt "lourd". Ceci dit un comptage fait avec une macro (série de boucles imbriquées), vu la durée d'exécution de celle que j'avais montée pour vérifier ton fichier à une colonne, prendrait environ 8 à 10 minutes !

Je me suis repenché sur ton souci de connaître précisément la plage Tirages. Très facile en utilisant une macro toute simple :

Sub AdresseTirages()
MsgBox ([Tirages].Address)
End Sub

Avec une formule, c'est moins simple, il faut reconstruire l'adresse :

=CELLULE("adresse";Tirages)&":"&CELLULE("adresse";DECALER(Tirages;LIGNES(Tirages)-1;COLONNES(Tirages)-1;1;1))

Cela te permettra de constater, si ton fichier n'a pas changé, que Tirages correspond à : B5:F825 ! Ce qui indique qu'au-delà de la ligne 817 tu as 8 valeurs parasites (caractères invisibles) dans la colonne B ! En procédant à un effacement sur quelques dizaines de lignes, cela rétablit la situation : B5:F817. Cela n'a cependant aucune incidence sur les calculs.

Bonjour et bonne année 2014 et santé

Je reviens ici juste pour une question.

Si cette fois ce n'est pas compter le maximum de fois qu'on a tel nb sur X lignes à la suite comme la formule de Ferrand nous le permet mais que c'est plutôt le minimum de fois... et en plus si cette fois-ci on veut savoir sur tant de tirages à la suite combien de fois retrouve t'on le minimum (et nom le maximum maintenant) de fois qu'on a tel nb sur X lignes à la suite pourquoi si on remple MAXI par MINI dans la formule que Ferrand nous a donné cela ne fonctionne pas ??

Car si je remplace :

{=MAX(NB.SI(DECALER(Tirages;LIGNE(INDIRECT("$1:$"&NBVAL(Tirages)/5-$K5+1))-1;;$K5;5);L$4))}
par

{=MIN(NB.SI(DECALER(Tirages;LIGNE(INDIRECT("$1:$"&NBVAL(Tirages)/5-$K5+1))-1;;$K5;5);L$4))}

Cela ne semble pas fonctionner si on fait cette modification dans le 1er tableau et donne des faux résultat dans le 2ème tableau qui a un lien avec le 1er tableau ici http://cjoint.com/13dc/CLnnSHdsY1u_copie_de_loto-5-nombres.xls .

merci

Williams

,

Je connais la puissance d'Excel !

Mais par ma formation de programmeur, d'abord en langage assembleur, puis en divers langages "évolués", j'ai plutôt tendance à penser "macro" dés que les formules deviennent un peu tarabiscotées !

C'est pourquoi j'ai vite abandonné lorsque j'ai vu que ferrand se penchait sur le pb et trouvait une solution.

Bonne année à toi dans ton travail de recherche et à ferrand aussi.

Meilleurs voeux pour 2014

Je fais ma première apparition cette année (venant juste de retrouver du courant électrique après passage cyclone)...

Je regarderai ta question dans les jours qui viennent (j'ai pas mal de rattrapages à réaliser en matière de communications).

petit hors sujet

Ferrand Bonne année ! Pas trop souffert du cyclone ? Vu à la télé : impressionnant .....

Ok Ferrand, j'espère que tu n'as pas eu trop de dégâts chez toi avec ce cyclone de catégorie 3 qui a fait de sacrés dommages en Réunion comme on l'a vu aux infos.

Williams