Recherche et classement de combinaisons identiques

Question

Bonjour, Une amie m’a conseillé de m’inscrire sur ce forum en m’indiquant que c’était le meilleur endroit avec les personnes les plus compétentes pour trouver une solution à mon problème de formule Excel. N’ayant malheureusement pas beaucoup de connaissance dans Excel et vu le nombre impressionnant de fonctions, je n’arrive pas à trouver la bonne formule pour résoudre mon problème. Mais, ayant parcouru l’aide proposée dans Excel pendant des heures et vu la puissance de ce programme, je suis persuadée qu’il existe une solution ? Voici ce que je recherche, j’ai un tableau (j’ai fait un exemple ci-joint avec 4 lignes, mais il y aura environ 40 lignes dans le tableau final). Chaque ligne correspond à des résultats d’analyse de tests (il peut y avoir des nombres entre 1 et 80 mais jamais 2 fois le même sur la même ligne). Ce que je cherche à connaître : Quelles sont les combinaisons de 5 chiffres qui apparaissent le plus souvent avec un classement de celle-ci. J’espère que je me suis exprimée correctement et que mon exemple est assez parlant ? En vous remerciant d’avance pour votre précieuse aide, car je perds des heures chaque jour à vérifier ces combinaisons avec ma pauvre petit tête. Et si je laisse passer quelques jours sans les trier, je suis obligée d’y passer la nuit… Car je dois rendre ces résultats tous les jours dans le cadre de mon travail. Merci encore. Raffaella...

MFerrand · Answer

Que tu ne trouves pas de formule pour calculer ça, rien de bien étonnant ! Le nombre de combinaisons de 5 nombres de 1 à 80 =&gt; 24 040 016 Sur une ligne qui comprend 20 nombres : 15 504 combinaisons. L'examen de 40 lignes conduit à examiner : 620 160 combinaisons. C'est bien moins que 24 millions mais cela reste toutefois assez conséquent. Comment peux-tu être sûr de les examiner toutes en les comptant manuellement ? Recours à VBA me paraît indispensable. Et la procédure pourrait être un peu longue à s'éxécuter. On verra ça aux tests. Précise les résultats que tu veux obtenir ? Les combinaisons dont le nombre d'apparitions est supérieur à 5 ? 10 ? 20 ?... Les 10 combinaisons qui apparaissent le plus souvent ? ou 20 ? ou 100 ?... Pas de cumul d'un jour sur l'autre ? Cordialement...

MFerrand · Answer

Petit complément :

Le traitement de 4 lignes prend environ 8 secondes. Si l'extrapolation est valide, on est au niveau d'une minute et demi pour traiter 40 lignes. C'est long... mais !

Après élimination des combinaisons uniques : 3248 réponses, dont :

1 apparaît 4 fois

326 apparaissent 3 fois

2921 apparaissent 2 fois

Il serait peut-être bon d'être plus sélectif sur les résultats à produire...

Peut-être en se basant sur le nombre de lignes n :

éliminer les résultats inférieur à n-1

ce qui ferait pour 40 lignes les combi répétées 39 ou 40 fois.

Si le nombre apparaît trop réduit, on pourra remonter le curseur en éliminant les résultats inférieurs à n-2...

Cordialement.

MFerrand · Answer

J'ai le sentiment que tu ne m'as pas lu ou pas compris, et aussi que tu n'as pas une idée précise de ce que tu veux accomplir ! D'abord, tu fournis 4 lignes en exemple : sur chaque ligne, une combinaison ne peut apparaître qu'une fois, donc sur l'ensemble des 4 lignes elle apparaît au maximum sur toutes les lignes, donc au maximum 4 fois. Ton tableau de résultats listant les combinaisons apparaissant 2 fois, 3 ,4, 5, 6, 7 ou 8 fois est hors des possibilités : avec 4 lignes le maximum d'apparitions est de 4 ! En outre tu n'y prévois qu'une combinaison par nombre d'apparitions, alors qu'il peut y en avoir plusieurs... Ce n'est pas non plus réaliste. Tu n'as pas l'air de prendre en compte le fait que pour une seule ligne de 20 nombres, lister les combinaisons de 5 nombres qui y apparaissent, chacune une seule fois, c'est lister 15504 combinaisons différentes . Sur 4 lignes, on doit donc examiner 62016 combinaisons de 5 nombres pour définir celles qui apparaissent plus d'une fois et combien. Je t'ai founi les résultats de l'examen des 62016 combinaisons de ton exemple. Dans l'exemple, 3248 combinaisons de 5 nombres apparaissent plus d'une fois. On n'est donc plus à la même échelle que ton tableau de résultats de 3 ou 4 lignes ! Pour 40 lignes, ce sont 10 fois plus de combinaisons à examiner : 620160. Si tu imagines encore pouvoir faire ce genre de calculs avec des formules... ! Pour lister les 62016 combinaisons de tes 4 lignes d'exemple, cela a pris 8 secondes environ... Ne considère pas que c'est peu ! C'est très long car en extrapolant on multiplie par 10. A ce stade, c'est sans doute encore faisable avant qu'on ne se heurte à la capacité mémoire et à la puissance de calcul. Il ne servirait pas à grand chose de produire plusieurs dizaines de milliers de lignes de résultats... ce pourquoi j'ai suggéré de les réduire à un niveau raisonnable et exploitable. C'est à dire tester sur 40 lignes en ne retenant que les combinaisons qui apparaissent 40 ou 39 fois, puis selon le nombre y adjoindre celles qui apparaissent 38 fois, etc. J'espère que tu as compris que le problème n'est pas de faire apparaître les 3 résultats de ton exemple, définis manuellement, mais que l'on en fait apparaître 3248 au total, donc 3245 que tu n'as pas vu !!! Cordialement...

MFerrand · Answer

La procédure se trouve dans Module1. Tu peux la lancer avec le bouton Test. Elle insère une feuille pour fournir les résultats sous forme de tableau. Elle est ajustée pour ne garder que les résultats de combinaisons apparaissant n fois ou n-1 fois (n étant le nombre de lignes testées). Si tu la relances, elle produira donc un tableau limité à 328 lignes. Tu peux ajouter progressivement des lignes pour voir comment cela évolue... (rien dans la colonne A en dessous du tableau de lignes). Le tri est fait sur le nombre d'apparition, mais pas sur les combinaisons. On peut l'introduire, cela allongera le temps d'exécution, mais en réduisant le nombre de combinaisons prises en compte cela devrait compenser. Cordialement...

MFerrand · Answer

Le tri ne modifie pas les résultats, il les met dans l'ordre.

Le tri par fréquence ne pose pas problème, surtout qu'il y en a peu. Par contre le tri pour ordonner les combinaisons dans l'ordre croissant des numéros pose un petit problème. Lors de l'examen, elles sont mémorisées sous forme de chaînes, type :

2|22|23|28|29

On redissocie à la fin pour retrouver chaque numéro. Si on trie à ce moment là, le tri se fait sur 5 colonnes. Si on le fait à partir de la chaîne, pour que la mise en ordre soit effective, il faut faire en sorte que tous les nombres soient à 2 chiffres :

02|22|23|28|29

Ou peut-être trier sur la feuille après affectation du tableau.

Je verrai après test sur plus de lignes, pour voir ce qui prend le moins de temps.

Tu patienteras un peu, j'ai aussi quelques autres problèmes à régler...

MFerrand · Answer

J 'ai vu ton dernier fichier. Je ne sais comment tu as obtenu des nombres à 2 chiffres en laissant les cellules au format Standard, mais en ne forçant pas l'alignement, ces nombres vont se cadrer à gauche dans la cellules, au lieu de se cadrer à droite. On doit donc en conclure qu'il s'agit de données de type texte ! Il est préférable que les nombres demeurent des valeurs numériques, je laisse donc ce fichier de côté. Faire apparaître un nombre dans une cellule avec un nombre de chiffres prédéterminés s'obtient par un format de cellule, format qui ne change en rien la valeur contenue dans la cellule. Je pouvais forcer le format lors de la conversion en texte par concaténation des nombres d'une combinaison intercalant un séparateur entre eux... Mais cela s'avère de toute façon inutile. Autre chose : ta présentation de départ, dans ton premier fichier était satisfaisante, donc il n'y a pas de raison de la modifier : une colonne d'en-tête de tes lignes d'analyse, 20 colonnes de nombres de B à U dans chaque ligne, et on commence ligne 1. Si tu t'amuses à modifier, il te faudra à chaque fois modifier la macro pour la réadapter à une nouvelle organisation des données ! Cela n'est pas a-priori souhaitable ! Si dans tes 4 lignes du début on avait une combinaison répétée 4 fois, il n'en est pas du tout de même présentement avec tes 10 lignes où le maximum de répétitions n'est que de 3, pour 3 combinaisons. J'ai donc dû modifier la méthode de réduction du nombre de combinaisons recensées : on va éliminer d'abord les combinaisons n'apparaissant qu'une fois, puis successivement les combinaisons apparaissant 2 fois, 3 fois, etc. jusqu'à ce que le nombre de combinaisons conservées devienne inférieur à 1000. On constitue alors le tableau final (6 colonnes, sans colonne supplémentaire temporaire comme auparavant) qu'on affecte. Et on procède au tri global (impliquant toutes les colonnes) à la fin, sur la feuille. Le temps d'exécution de la procédure modifiée par rapport à sa version antérieure est légèrement augmenté, moins d'une seconde. La durée ressort à 18 secondes pour 10 lignes, on reste donc dans le même ordre de grandeur qu'auparavant (8 secondes pour 4 lignes). Il semble qu'à ce stade, on en est à la version définitive (au moins du point de vue des combinaisons à prendre en considération). Les combinaisons se répétant le même nombre de fois doivent être soit retenues toutes, soit rejetées toutes. On ne peut tronquer... Ainsi pour ces 10 lignes : 3 combi. répétées 3 fois, 714 répétées 2 fois, le résultat à présenter ne pouvait être que soit 3 combinaisons, soit 717, il ne peut y avoir de nombre intermédiaire... Cordialement...

MFerrand · Answer

Si je relis ce que j'ai dit dans mon dernier post, la version est définitive (sauf élément nouveau qui apparaîtrait dans les résultats obtenus. Tu étends ton tableau en respectant la structure : les séries de nombres dans les colonnes B à U, la colonne A doit comporter une mention (le contenu est sans incidence sur l'exécution mais il doit y avoir quelque chose), il ne doit rien y avoir en colonne A en dessous de ton tableau de lignes à tester. Rien à modifier dans la procédure. Elle prendra un peu plus de temps au fur et à mesure que tu augmentes le nombre de lignes. La procédure recense toutes les combinaisonsde chaque ligne en comptabilisant le nombre de répétitions de celles qui le sont. Ceci fait, elle élimine le nombre de combinaisons uniques (non répétées). Ensuite si le nombre de résultats est inférieur à 1000, elle les produira tous, sinon elle va éliminer les combinaisons répétées 2 fois, puis s'il y en a encore trop celles répétées 3 fois, ainsi de suite. Ele s'arrête donc d'éliminer lorsque le nombre de résultats est au maximum de 999. Ce qui devrait permettre un nombre de résultats suffisants... Elle produit toujours les résultats sur une nouvelle feuille, sous forme de tableau trié : classement par nombre de répétitions et dans l'ordre croissant des combinaisons. Cordialement...

MFerrand · Answer

Avec 6, tu augmentes le nombre de combinaisons par ligne de 2,5 fois. On peut donc considérer globalement que cela va prendre jusqu'à 2fois et demi plus de temps. La puissance n'est pas le problème majeur, elle conditionne essentiellement le temps. La limite se fera éventuellement sentir au niveau capacité mémoire. Je n'ai pas idée... Il faut tester pour savoir si ça plante ou si tu arrives à absorber... Cordialement...

MUSIQUE · Answer

Ce sujet des combinaisons a été abordé plusieurs fois dans le cadre du Kéno national. En faisant une recherche sur Go...gle, on trouve facilement x méthodes mais le temps d'exécution des macros est exponentiel avec des combinaisons &gt; à 6....sauf à faire des restitutions texte dans une seule cellule avec les nombres séparés par des , ou ; Bon dimanche...

MFerrand · Answer

Le nombre de combinaisons augmente certes en accroissant le nombre de nombres par combinaisons, mais jusqu'à 10 (la moitié), ensuite il diminue... Tu peux modifier toi-même la macro, il faut ajouter une nouvelle boucle imbriquée pour le 6e nombre, une colonne au tableau de résultats, et réviser aussi le tri. La voici ci-dessous. Faut pas louper de modification, j'espère que c'est le cas ! Sinon tu vas vite le voir !! Je n'ai pas testé ! Sub Combi6Max() Dim a%, b%, c%, d%, e%, f%, m%, n&amp;, i&amp;, dc As Object, k, T() Set dc = CreateObject("Scripting.Dictionary") 'Recueil combinaisons With ActiveSheet n = .Cells(.Rows.Count, 1).End(xlUp).Row For i = 1 To n For a = 2 To 16 For b = a + 1 To 17 For c = b + 1 To 18 For d = c + 1 To 19 For e = d + 1 To 20 For f = e + 1 To 21 k = .Cells(i, a) &amp; "|" &amp; .Cells(i, b) &amp; "|" &amp; .Cells(i, c) &amp; "|" _ &amp; .Cells(i, d) &amp; "|" &amp; .Cells(i, e) &amp; "|" &amp; .Cells(i, f) If dc.exists(k) Then m = CInt(dc(k)) + 1: dc(k) = m Else dc(k) = 1 End If Next f Next e Next d Next c Next b Next a Next i End With 'Epuration combi (élimination combi uniques et réduction à moins de 1000) For i = 2 To n - 1 For Each k In dc.keys If CInt(dc(k)) &lt; i Then dc.Remove (k) Next k If dc.Count &lt; 1000 Then Exit For Next i 'Mise en tableau ReDim T(dc.Count, 6): n = 0 For Each k In dc.keys n = n + 1 T(n, 6) = CInt(dc(k)) For i = 0 To 5 T(n, i) = Split(k, "|")(i) Next i Next k dc.RemoveAll T(0, 0) = "Combinaisons": T(0, 6) = "Nombre" 'Affectation et mise en forme, tri Application.ScreenUpdating = False With Worksheets.Add(after:=Worksheets(ActiveSheet.Name)) With .Range("A1").Resize(n + 1, 7) .Value = T .Columns("A:F").ColumnWidth = 5 .Columns("G").ColumnWidth = 10 .HorizontalAlignment = xlCenter With .Borders .LineStyle = xlContinuous .Weight = xlThin End With .Range("A1:F1").Merge End With With .Range("A2").Resize(n, 7) .Sort key1:=.Cells(1, 4), order1:=xlAscending, key2:=.Cells(1, 5), order2:=xlAscending, _ key3:=.Cells(1, 6), order3:=xlAscending, Header:=xlNo .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 2), order2:=xlAscending, _ key3:=.Cells(1, 3), order3:=xlAscending, Header:=xlNo .Sort key1:=.Cells(1, 7), order1:=xlDescending, Header:=xlNo End With End With End Sub Tu auras peut-être une autre méthode à tester si eriiic se décide à tenter une autre approche... Avec la présente méthode, je ne pense pas que cela bloquera déjà mais tu auras au moins une augmentation sensible du temps d'exécution. Cordialement...