Calcul combinatoire VBA

Le 03/10/2017 à 18:10

Bonjour tout le monde,

Je débute totalement sur Excel, et d'habitude j'arrive à m'en sortir en naviguant sur le web et/ou forum.

Je pense que la je m'attaque peut-être à quelque chose de "balaise".

Je vous explique mon problème:

j'ai plusieurs données chiffrée:

108

119

133

158

166

174

187

Mon objectif étant d'atteindre 11 500, je souhaiterais qu'excel me combine l'ensemble de ces données chiffrés pour atteindre 11 500 sachant que mon unité maximal est 69; le tout en utilisant en priorité les valeurs les plus basse.

j'aimerais donc qu'automatiquement il me donne les résultats suivant:

(juste à titre d'exemple aucun calcul n'a été fait au préalable)

108x0: 0

119x0: 0

133x12: 1 995

158x17: 2 686

166x25: 4 150

174x10 : 1 740

186x5 : 930

Total : 11 501

Merci d'avance pour toute l'aide que vous pourrez m'apporter.

Bonne soirée à vous.

Le 03/10/2017 à 20:03

Bonjour,

utilise le solveur.

Sachant que je ne comprend pas :

sachant que mon unité maximal est 69

et

le tout en utilisant en priorité les valeurs les plus basse.

qui n'ont une signification que pour toi...

Un exemple joint qui minimise le nombre d'éléments. Ajoute les contraintes qu'il te faut.

eric

11classeur2.xlsx (70.73 Ko)
Le 03/10/2017 à 20:08

Salut François,

clair et sombre à la fois!

Une série de donnée : très bien mais je vois 3 petits points!

On peut avoir la série complète, siouplaît? Est-elle immuable ou est-elle interchangeable?

Ton unité maximum est 69 : c'est le facteur multiplicateur maximum?

Dernière chose, comme ça : c'est pour faire joujou avec nos neurones ou c'est pour quelque chose de plus sérieux?

8)

A+

Le 03/10/2017 à 20:52

Bonjour,

une solution possible, valeur cible en A1, liste des nombres de A2 à An

Dim solv
Sub combine()
    t = Range("A1") 'valeur cible
    dl = Cells(Rows.Count, 1).End(xlUp).Row 'nombre de valeurs
    v = Application.Transpose(Range("A2:A" & dl)) 'copie des valeurs dans la table v
    For nmax = 1 To UBound(v) ' on essaie toutes les combinaisons de 1 à n valeurs
    cherche t, v, nmax
    ct = 0
    For i = 1 To nmax 'on a trouvé une solution minimale
        Cells(i, 5) = v(i)
        Cells(i, 6) = solv(i)
        ct = ct + Cells(i, 5) * Cells(i, 6)
    Next i
    If ct = t Then Exit For ' on a trouvé une solution exacte avec les plus petites valeurs possibles
    Next nmax
End Sub
Sub cherche(t, v, nmax, Optional ct = 0, Optional cv = Empty, Optional n = 1, Optional minimum = 1000000000#)
'procédure récursive
    If minimum = 0 Then Exit Sub
    On Error Resume Next
    check = cv = Empty
    On Error GoTo 0
    If check Then ReDim cv(1 To UBound(v))
    nombre = v(n)
    sv = Int((t - ct) / nombre)
    For i = sv To 0 Step -1
        ct = ct + i * nombre
        cv(n) = i
        If Abs(t - ct) < minimum Then
            minimum = Abs(t - ct)
            solv = cv
        End If
        If t - ct > 0 And n < nmax Then cherche t, v, nmax, ct, cv, n + 1, minimum
        ct = ct - i * nombre
    Next i
End Sub
Le 04/10/2017 à 11:53

Bonjour à tous,

D'abord un grand merci pour vos réponses.

Pour répondre à Eric et Curulis:

  • 69 est bien le facteur multiplicateur maximum ( ce que j'entendais par unité maximal)
  • je veux dire par "en utilisant les valeurs les plus basse"; dans ton fichier Eric il prend en considération quasi uniquement la valeur 186 alors que dans mon exemple j'atteint 11501 avec uniquement 5 fois cette valeur. ( je sens que c'est pas clair mais pas simple de mettre des mots sur une idée XD ).
  • Ce n'est pas non plus pour faire joujou avec vos neurones ce problème répond à un réel besoin dans on milieu professionnel.

Je vais vous donner plus d'informations pour que ca soit peut-être plus claire:

Je dois donner des objectifs de vente à mes vendeurs; exemple 11 500€ à atteindre pour la journée 22 novembre 2017, j'estime qu'ils doivent atteindre cet objectif avec 69 ventes.

Afin qu'ils puissent au mieux atteindre leur objectifs je souhaiterais leur communiquer des prérogatives de vente.

Mon produit à une valeur variable, je fixe la valeur minimum et maximum avec plusieurs paliers.

sur une semaine ca me donnerais les éléments suivant:

20/11 21/11 22/11 23/11

Prix: 127€ Qté: ? Prix: 136€ Qté: ? Prix: 136€ Qté: ? Prix: 127€ Qté: ?

136€ ? 147€ ? 147€ ? 136€ ?

139€ ? 155€ ? 155€ ? 139€ ?

147€ ? 157€ ? 157€ ? 147€ ?

148€ ? 163€ ? 163€ ? 148€ ?

155€ ? 169€ ? 169€ ? 155€ ?

157€ ? 173€ ? 173€ ? 157€ ?

163€ ? 178€ ? 178€ ? 163€ ?

169€ ? 188€ ? 188€ ? 169€ ?

Multiplicateur max: 66 69 69 54

valeur cible: 9 500€ 11 500€ 10 800€ 7 800€

Les prix ne varient pas, ce sont des données rentrés manuellement ainsi que la caleur cible et le multiplicateur max.

J'aimerais que le fichier excel recalcule l'ensemble des possibilités dès que l'une de ces données changent.

Je me suis donné un mal de crâne rien que d'en parler... XD

Merci encore.

François.

Le 04/10/2017 à 12:00

bonjour,

j'avais zappé la limite de 69 pour le nombre d'unités. Voici le code adapté

Dim solv
Sub combine()
    t = Range("A1") 'valeur cible
    dl = Cells(Rows.Count, 1).End(xlUp).Row 'nombre de valeurs
    v = Application.Transpose(Range("A2:A" & dl)) 'copie des valeurs dans la table v
    For nmax = 1 To UBound(v) ' on essaie toutes les combinaisons de 1 à n valeurs
    cherche t, v, nmax
    ct = 0
    For i = 1 To nmax 'on a trouvé une solution minimale
        Cells(i, 5) = v(i)
        Cells(i, 6) = solv(i)
        ct = ct + Cells(i, 5) * Cells(i, 6)
    Next i
    If ct = t Then Exit For ' on a trouvé une solution exacte avec les plus petites valeurs possibles
    Next nmax
End Sub
Sub cherche(t, v, nmax, Optional ct = 0, Optional cv = Empty, Optional n = 1, Optional minimum = 1000000000#)
'procédure récursive
    If minimum = 0 Then Exit Sub
    On Error Resume Next
    check = cv = Empty
    On Error GoTo 0
    If check Then ReDim cv(1 To UBound(v))
    nombre = v(n)
    sv = Int((t - ct) / nombre)
    If sv > 69 Then sv = 69
    For i = sv To 0 Step -1
        ct = ct + i * nombre
        cv(n) = i
        If Abs(t - ct) < minimum Then
            minimum = Abs(t - ct)
            solv = cv
        End If
        If t - ct > 0 And n < nmax Then cherche t, v, nmax, ct, cv, n + 1, minimum
        ct = ct - i * nombre
    Next i
End Sub
Le 04/10/2017 à 12:30

Merci beaucoup; par contre comme évoqué je suis super novice sur les macro donc je ne sais pas ou reporté ce code pour ensuite récolter les informations et également ou je peux modifier les données de prix et de valeur cible lorsqu'elles varient.

h2so4 a écrit :

bonjour,

j'avais zappé la limite de 69 pour le nombre d'unités. Voici le code adapté

Dim solv
Sub combine()
    t = Range("A1") 'valeur cible
    dl = Cells(Rows.Count, 1).End(xlUp).Row 'nombre de valeurs
    v = Application.Transpose(Range("A2:A" & dl)) 'copie des valeurs dans la table v
    For nmax = 1 To UBound(v) ' on essaie toutes les combinaisons de 1 à n valeurs
    cherche t, v, nmax
    ct = 0
    For i = 1 To nmax 'on a trouvé une solution minimale
        Cells(i, 5) = v(i)
        Cells(i, 6) = solv(i)
        ct = ct + Cells(i, 5) * Cells(i, 6)
    Next i
    If ct = t Then Exit For ' on a trouvé une solution exacte avec les plus petites valeurs possibles
    Next nmax
End Sub
Sub cherche(t, v, nmax, Optional ct = 0, Optional cv = Empty, Optional n = 1, Optional minimum = 1000000000#)
'procédure récursive
    If minimum = 0 Then Exit Sub
    On Error Resume Next
    check = cv = Empty
    On Error GoTo 0
    If check Then ReDim cv(1 To UBound(v))
    nombre = v(n)
    sv = Int((t - ct) / nombre)
    If sv > 69 Then sv = 69
    For i = sv To 0 Step -1
        ct = ct + i * nombre
        cv(n) = i
        If Abs(t - ct) < minimum Then
            minimum = Abs(t - ct)
            solv = cv
        End If
        If t - ct > 0 And n < nmax Then cherche t, v, nmax, ct, cv, n + 1, minimum
        ct = ct - i * nombre
    Next i
End Sub
Le 04/10/2017 à 13:13

bonjour,

voici le fichier, à nouveau adapté car j'avais initialement compris que 69 était une limite par prix et non une limite totale.

Attention au plus il y a de prix et au plus petit le nombre d'unités, au plus long le temps de recherche.

14combinemultiple.xlsm (21.64 Ko)
Le 04/10/2017 à 15:04

Bonjour,

j'ai l'impression que tu te prends la tête pour pas grand chose.

Vu qu'il s'agit d'un objectif quel est l'intérêt d'atteindre pile-poil celui-ci ? Le principal est de l'approcher ou de le dépasser.

S'il a fait ses 12 ventes à 165€ il va refuser la 13ème ? Il va lui dire non j'ai vendu assez de chaussettes il faut m'acheter des gants maintenant.

Fait à la louche en priorisant les produits que tu veux mettre en avant et ajuste ton objectif final en fonction.

Enfin c'est mon avis personnel.

eric

Rechercher des sujets similaires à "calcul combinatoire vba"