macro combinaison d'elements

[vanpop]

Bonjour,

D'abord merci pour ce forum et les aides précieuses que j'ai déjà pu y trouvées. Pour la première fois, ne trouvant pas de réponse à mon pb, je me lance.
J'ai une liste de 97 éléments. J'aimerai pouvoir recenser toutes les possibilités de combinaisons de 1 à 5 éléments max, sachant qu'un élément peut se retrouver plusieurs fois dans une même combinaison.
Il faut bien sur limiter le nombre de combinaisons : chaque élément a une valeur comprise entre 3 et 6, la combinaison doit imperativement renvoyer une valeur moyenne de 4.5
Je vous joins la liste de mes éléments avec leur valeur.

Je suis bonne utilisatrice Excel (formule), mais je n'y connais rien ni en probabilité et ni en VBA!
Je ne suis donc même pas sure que le nombre de possibilités de combinaisons ne soient pas infinies ... Mais si une macro pouvait m'aider, ce serait top !
Je vous remercie d'avance, déjà de m'avoir lu, et je croise les doigts pour que qqun m'aide un petit peu,
Anne

 

[ERIC S]

Re : macro combinaison d'elements

Bonjour
les stats et probas sont loin derrière moi mais avec un peu de logique :

pour une moyenne de 4,5 avec 2 codes il faut un total de 9 soit 6 et 3 ou 5 et 4.
6 et 3 donne 7*33 possibilités donc 231 possibilités
5 et 4 donne 21*34 donc 714 possibilités

avec 4 codes il faut un total de 18 donc les possibilités
6633 soit 7*6*33*32 = 44352 possibilités
6543 soit 7*21*34*33 = 117810
6444 soit 7*34*33*32 = 251328
5544 soit 21*20*34*33 = 471240
5553 soit 21*20*19*33 = 263340

Aie, ça pique si tu veux tout afficher

avec 3 codes il faut un total de 13,5 donc impossible
avec 5 codes un total de 22,5 donc impossible

 

[herve62]

Re : macro combinaison d'elements

Bonjour
STP Anne donne un peu plus de cas concret ; ex : j'ai ça LA ; et je veux ça ICI !
Dsl Eric mais pour 4.5 de moy avec 2 codes pour 9 , tu oublies 8 & 1 ; 7 & 2 !!
Ensuite ( moi aussi c'est loin !!) lorsque l'on parle de moyenne dans ce cas il faut invoquer la "Dispersion" donc "l'écart
type" voire "Médiane" & "Variance" , ces fonctions existent bien dans Excel , fut-il savoir comment les appliquer ?
Peut être un statisticien en activité et connaisseur d'excel peut donner un avis
J'ai pas pigé non plus ton calcul sur le Nbre de cas ? car par exemple au loto le Nbre de chance est à base de Factoriels , non ?

 

[ERIC S]

Re : macro combinaison d'elements

Re

bonjour Hervé
7, 8, 1, 2 ne sont pas entre 3 et 6 je pense

pour le loto c'est des factorielles oui. tu n'as peut-être pas tord quand à mon approche mais là, comme tu l'as dit les stats restent un métier qui n'est pas le mien.

5544 soit 21*20*34*33 = 471240

je pars du principe qu'il y a 21 possibilités d'avoir un coef 5, pour le premier choix. Il en restera 20 pour le second. 34 possibilités pour le choix du coef 4, 33 pour le second

Aux erreurs(peut-être énormes si ma logique déraille) près, on se rapproche des calculs de stat

 

[vanpop]

Re : macro combinaison d'elements

Merci à tous les deux de vos réponses/remarques.
Eric a raison, les valeurs de mes éléments vont de 3 à 6, il ne peut y avoir 8/1 et 7/2.
Pour ce qui est des statistiques, comme je l'annonçais, ce n'est pas mon domaine...
Pour plus de concret Hervé, je souhaiterai avoir en résultat de cette macro une liste de 2 à 5 éléments concaténés répondant à la contrainte "moyenne = 4.5". Par exemple "S15S79" , "S79S15"... Mais apparemment, ce ne serait pas possible avec 3 ou 5 éléments ... Peu importe où se situe la liste, pourquoi pas en feuil2 ($A:$A)
Merci en tous cas de de vous pencher sur ma question, c'est vraiment fort aimable,

Anne

 

[ERIC S]

Re : macro combinaison d'elements

Re

je viens de regarder en vba, pour 2 codes 945 combinaisons
pour 3 codes 0
je me suis arrêté là vu les chiffres que je prévois pour 4 codes (manquerait des lignes à Excel)

 

[KenDev]

Re : macro combinaison d'elements

Bonsoir à tous,

Une possibilité avec le code ci dessous adapté aux données telles que fournies dans le post #1. Le code calcule toutes les possibilités et ne sera donc pas adapté si le nombres de lignes est très supérieur aux 95 fournies. Ici les 427.455 solutions sur 75.287.519 combinaisons possibles sont passées en revue assez vite.

Cordialement

KD

Const WkName$ = "Feuil1", TitleLine As Boolean = True, TitlesCol% = 1, ValuesCol% = 2, EltMax% = 5, Cible# = 4.5
Dim Tb%(), Cb%(), Cpt&, w(1) As Worksheet, Col%

Sub CmbCdElt()
    Dim u%, n#, i%, m&, j&, k%, y#
    Application.Calculation = False: Application.ScreenUpdating = False
    Call Params: u = UBound(Tb): Cpt = 0
    Sheets.Add: Set w(1) = ActiveSheet: Col = 1
    For i = 1 To EltMax
        ReDim Cb(1 To i)
        n = WorksheetFunction.Combin(u + i - 1, i)
        For j = 1 To i: Cb(j) = 1: Next j
        If Tb(1) = Cible Then Call Writing
        For j = 2 To n
            Call NextCombin(u)
            m = 0
            For k = 1 To i: m = m + Tb(Cb(k)): Next k
            y = m / i
            If y = Cible Then Call Writing
        Next j
    Next i
    If Cpt = 0 Then Application.DisplayAlerts = False: w(1).Delete: Application.DisplayAlerts = True: MsgBox "Aucune combinaison trouvée"
    Application.Calculation = True
End Sub
Private Sub NextCombin(ByVal Mx%)
    Dim u%, i%, j%
    u = UBound(Cb): i = u
    Do While Cb(i) = Mx: i = i - 1: Loop
    For j = u To i Step -1: Cb(j) = Cb(i) + 1: Next j
End Sub
Private Sub Params()
    Dim r&, i&
    Set w(0) = Worksheets(WkName): r = w(0).Cells(Rows.Count, 1).End(xlUp).Row: ReDim Tb(1 To r + TitleLine)
    For i = 1 To UBound(Tb): Tb(i) = w(0).Cells(i - TitleLine, ValuesCol): Next i
End Sub
Private Sub Writing()
    Dim i%, t$
    Cpt = Cpt + 1
    If Cpt > Rows.Count Then Cpt = 1: Col = Col + 1
    For i = 1 To UBound(Cb): t = t & w(0).Cells(Cb(i) - TitleLine, TitlesCol): Next i
    w(1).Cells(Cpt, Col) = t
End Sub

 

[ERIC S]

Re : macro combinaison d'elements

Bonjour kendev

je vais me pencher sur ton fichier (mon écriture vba est beaucoup plus basique que la tienne mais cela devrait se faire)
quand tu écris 427455 solution, c'est en tout ou partiel : nous nous posions hier la question du nb de solutions totales et Hervé et Moi ne baignons pas dans le monde des stats....

 

[vanpop]

Re : macro combinaison d'elements

Merci à tous les 3 et tout particulièrement à Kendev !
La macro a fonctionné, mais elle ne renvoit malheureusement pas l'ensemble des résultats attendus (les permutations). Il y en aurait sans doute trop !
Je m'en contente donc. Merci encore de tous vos efforts, ce forum est magique !

Anne

 

[KenDev]

Re : macro combinaison d'elements

Bonour à tous,

@Eric,

Nb de combinaisons sans répétitions:
3&6 = 7*33 = 231
4&5 = 21*34 = 714
3&3&6&6 = 2C7*2C33 = 21*528 = 11088
3&4&5&6 = 33*34*21*7 = 164934
4&4&4&6 = 7*3C34 = 7*5984 = 41888
4&4&5&5 = 2C34*2C21 = 561*210 = 117810
3&5&5&5 = 33*3C21 = 33*1330 = 43890
TOTAL = 380555

Nb de combinaisons avec 1 répétition (double)
3&3&6&6 = 33*2C7 + 7*2C33 = 33*21 + 7*528 = 4389
4&4&5&5 = 34*2C21 + 21*2C34 = 34*210 + 21*561 = 18921
4&4&4&6 = 7*34*33= 7854
3&5&5&5 = 33*21*20 = 13860
TOTAL = 45024

Nb de combinaisons avec 2 répétitions (double)
3&3&6&6 = 7*33 = 231
4&4&5&5 = 21*34 = 714
TOTAL = 945

Nb de combinaisons avec 1 répétition (triple)
4&4&4&6 = 34*7 = 238
3&5&5&5 = 33*21 = 693
TOTAL = 931

380855 + 45024 + 945 + 931 = 427455

@Anne,

Merci de votre retour. J'ai bien relu votre premier post et y ai rencontré cinq fois le mot combinaison et zéro fois le mot permutation...

Pour chaque combinaison sans répétition à 2 éléments on aura 2 permutations
Pour chaque combinaison sans répétition à 4 éléments on aura 24 permutations
Pour chaque combinaison avec 1 répétition double on aura 12 permutations
Pour chaque combinaison avec 2 répétitions double on aura 6 permutations
Pour chaque combinaison avec 1 répétition triple on aura 4 permutations
Le nombre de permutation à afficher : 945*2 + 379610*24 + 45024*12 + 945*6 + 931*4 = 9.662.212
Etes vous certaine de vouloir toutes les afficher ?

Cordialement

KD

 

[ERIC S]

Re : macro combinaison d'elements

Merci Kendev

je vais essayer de digérer tout cela mais effectivement, j'ai beau regarder mes mains je n'ai pas assez de doigts...

il faudrait connaître le but ultime de cette question , mais au prime abord, même au plus light l'exploitation des résultats me laisse dubitatif

 

[KenDev]

Re : macro combinaison d'elements

Re,

Correction d'une erreur dans le code du post#7:

If i * Tb(1) = Cible Then Call Writing

devient

If Tb(1) = Cible Then Call Writing

Le post #7 est corrigé. Il n'y avait pas d'incidence pour les résultats des données exemples.

Cordialement
KD

 

[vanpop]

Re : macro combinaison d'elements

Bonjour à tous,
veuillez m'excuser si j'ai mal exprimé ma demande, je ne suis pas statisticienne ni habituée de ce vocabulaire. A ma décharge, l'exemple du post 5 indiquait s15s79 et s79s15... j'aurai certes du/pu être plus claire et m'instruire avant de faire ma demande.

Mon but ultime ? Je dois proposer de nouveaux cycles de travail pour du personnel. Ces cycles doivent pouvoir s'étaler sur des périodes allant de 1 à 5 semaines. Le seul critère pour le moment : les jours de travail doivent être plein (pas de demi journées) et la semaine de travail en moyenne doit durer 4.5 jours...les 97 éléments reprennent les 97 possibilités de cycle d'une semaine comportant 3 à 6 jours de travail... je ne vous ai pas fourni le détail de ces cycles pour ne pas embrouiller une demande deja compliquée à mon niveau mais je vous les transmets en PJ pour votre info.
Je comptais ensuite réaliser un tri pour que les cycles respectent d'autres conditions, notamment la fréquence de dimanche travaillé selon la catégorie d'emploi (travail 1 dimanche/2, 3 dimanche/5, aucun dimanche). Je vous ai tout dit !

Merci encore KenDev pour votre aide dejà précieuse, et encore désolée de mon manque de clarté.

Anne

 

[ROGER2327]

Re : macro combinaison d'elements

Bonjour à tous.

En pièce jointe, un code tout pourri qui donne les 9 662 212 permutations dénombrées par KenDev.
Résultat obtenus chez moi en un peu plus d'une minute (~10 secondes de calcul et ~1 minute d'affichage).
Mode d'emploi : Exécuter la procédure tata, ou la procédure vitesse si on veut un chronométrage.

Attention ! Achtung ! Caution! Taille du fichier enregistré après exécution d'une des procédures : 85,5 Mo.

Quant à exploiter le résultat... ...bon courage !​

Bonne journée.


ℝOGER2327
#7906

Mercredi 25 Merdre 142 (Apparition d’Ubu Roi - fête Suprême Seconde)
23 Prairial An CCXXIII, 5,4106h - chèvrefeuille
2015-W24-4T12:59:08Z


P.s. : cette réponse est écrite sur la base des messages #1 à #12 ; elle ne tient pas compte du message #13.

 

[ERIC S]

Re : macro combinaison d'elements

Bonjour

très fort Roger2327, le scripting.dictionary c'est puissant mais pas assez pour me faire oublier que mon PC est lent

Pour l'exploitation il va sans doute falloir se fixer des règles ... ou embaucher

le genre de casse tête pour une armée d'emplois aidés

Vanpop, tiens-nous au courant des débouchés