Somme de cellules pour atteindre une valeur cible

Le
Karina
Bonjour,
Si on a :
C3 : 15
C4 : 145
C5 : 3
C6 : 47
C7 : 78
C8 : 9
Et en A1, on a une valeur "cible" : 148

Je voudrais qu'Excel cherche quelles cellules doivent être
additionnées sur la plage C3:C8 pour trouver 148.
Dans mon exemple, j'aimerais qu'il me colorie en jaune les cellules C4
et C5.
S'il n'y a pas de solution, avoir un message du style "Votre problème
n'a pas de solution".

Bien sur, c'est un exemple basique qui ne necessite pas d'avoir Excel
pour le résoudre mais dans mon vrai cas, il y a beaucoup plus de
lignes !

Merci
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Frederic LE GUEN
Le #23132861
On Feb 16, 8:20 pm, Karina
Bonjour,
Si on a :
C3 : 15
C4 : 145
C5 : 3
C6 : 47
C7 : 78
C8 : 9
Et en A1, on a une valeur "cible" : 148

Je voudrais qu'Excel cherche quelles cellules doivent être
additionnées sur la plage C3:C8 pour trouver 148.
Dans mon exemple, j'aimerais qu'il me colorie en jaune les cellules C4
et C5.
S'il n'y a pas de solution, avoir un message du style "Votre problème
n'a pas de solution".

Bien sur, c'est un exemple basique qui ne necessite pas d'avoir Excel
pour le résoudre mais dans mon vrai cas, il y a beaucoup plus de
lignes !

Merci



Tu sais, un ordinateur c'est basique (pour ne pas dire binaire)
Il sait faire des calculs itératifs et point barre. Dans ton cas, il
faut que tu utilises le solveur pour réaliser les calculs itératifs
nécessaires pour atteindre le résultat que tu veux obtenir.
C'est le meilleur moyen que tu aies pour obtenir la solution à ton
problème

Fred MVP
h2so4
Le #23132991
After serious thinking Frederic LE GUEN wrote :
On Feb 16, 8:20 pm, Karina
Bonjour,
Si on a :
C3 : 15
C4 : 145
C5 : 3
C6 : 47
C7 : 78
C8 : 9
Et en A1, on a une valeur "cible" : 148

Je voudrais qu'Excel cherche quelles cellules doivent être
additionnées sur la plage C3:C8 pour trouver 148.
Dans mon exemple, j'aimerais qu'il me colorie en jaune les cellules C4
et C5.
S'il n'y a pas de solution, avoir un message du style "Votre problème
n'a pas de solution".

Bien sur, c'est un exemple basique qui ne necessite pas d'avoir Excel
pour le résoudre mais dans mon vrai cas, il y a beaucoup plus de
lignes !

Merci





voici une macro qui recherche toutes les solutions possibles (attention
cela peut prendre du temps s'il y a beaucoup de valeurs à prendre en
compte).

la valeur cible à trouver est en C1
les valeurs possibles à additioner sont en colonne C et commencent en
C3 et se suivent sans cellule vide.

la macro fonctionne dans les limites suivantes :
le nombre d'éléments de la somme est maximum 100.
il y a un maximum de 10000 éléments à "essayer" (en colonne C)
les éléments à additionner sont des nombres entiers.


Sub findsolution()

Dim v(100) As Integer, el(10000) As Long, p(10000) As Integer, cible As
Long, c As Integer
limite = 100
With Worksheets("Sheet1")
cible = .Cells(1, 3)
ligne = 3
While .Cells(ligne, 3) <> ""
c = c + 1
el(c) = .Cells(ligne, 3)
p(c) = ligne
ligne = ligne + 1
Wend

For i = 1 To c - 1
For j = i To c
If el(i) > el(j) Then
a = el(i)
el(i) = el(j)
el(j) = a
a = p(i)
p(i) = p(j)
p(j) = a
End If
Next j
Next i

niveau = 1
While niveau > 0
If niveau = 1 Then s = 0
If v(niveau) < c Then
v(niveau) = v(niveau) + 1
s = s + el(v(niveau))
If s = cible Then
'solution
sol = sol + 1
m = "solution " & sol & " en additionnant " & vbCrLf
For i = 1 To niveau
m = m & el(v(i)) & " in ligne " & p(v(i)) & vbCrLf
Cells(p(v(i)), 3).Interior.Color = 65535
Next i
MsgBox m

For i = 1 To niveau
Cells(p(v(i)), 3).Interior.Pattern = xlNone
Cells(p(v(i)), 3).Interior.TintAndShade = 0
Cells(p(v(i)), 3).Interior.PatternTintAndShade = 0
Next i
s = s - el(v(niveau))
ElseIf s > cible Then
s = s - el(v(niveau))
v(niveau) = c
Else
If niveau < limite Then
niveau = niveau + 1
v(niveau) = v(niveau - 1)
End If
End If
Else
v(niveau) = 0
niveau = niveau - 1
s = s - el(v(niveau))
End If
Wend
End With
MsgBox sol & " solution(s) trouvée(s)"


End Sub

--
h2so4
ca PAN
pique DORA
.
h2so4
Le #23133161
h2so4 presented the following explanation :
After serious thinking Frederic LE GUEN wrote :
On Feb 16, 8:20 pm, Karina
Bonjour,
Si on a :
C3 : 15
C4 : 145
C5 : 3
C6 : 47
C7 : 78
C8 : 9
Et en A1, on a une valeur "cible" : 148

Je voudrais qu'Excel cherche quelles cellules doivent être
additionnées sur la plage C3:C8 pour trouver 148.
Dans mon exemple, j'aimerais qu'il me colorie en jaune les cellules C4
et C5.
S'il n'y a pas de solution, avoir un message du style "Votre problème
n'a pas de solution".

Bien sur, c'est un exemple basique qui ne necessite pas d'avoir Excel
pour le résoudre mais dans mon vrai cas, il y a beaucoup plus de
lignes !

Merci





voici une macro qui recherche toutes les solutions possibles (attention cela
peut prendre du temps s'il y a beaucoup de valeurs à prendre en compte).

la valeur cible à trouver est en A1
les valeurs possibles à additioner sont en colonne C et commencent en C3 et
se suivent sans cellule vide.

la macro fonctionne dans les limites suivantes :
le nombre d'éléments de la somme est maximum 100.
il y a un maximum de 10000 éléments à "essayer" (en colonne C)
les éléments à additionner sont des nombres entiers.


Sub findsolution()

Dim v(100) As Integer, el(10000) As Long, p(10000) As Integer, cible As Long,
c As Integer
limite = 100
With Worksheets("Sheet1")
cible = .Cells(1, 1)
ligne = 3
While .Cells(ligne, 3) <> ""
c = c + 1
el(c) = .Cells(ligne, 3)
p(c) = ligne
ligne = ligne + 1
Wend

For i = 1 To c - 1
For j = i To c
If el(i) > el(j) Then
a = el(i)
el(i) = el(j)
el(j) = a
a = p(i)
p(i) = p(j)
p(j) = a
End If
Next j
Next i

niveau = 1
While niveau > 0
If niveau = 1 Then s = 0
If v(niveau) < c Then
v(niveau) = v(niveau) + 1
s = s + el(v(niveau))
If s = cible Then
'solution
sol = sol + 1
m = "solution " & sol & " en additionnant " & vbCrLf
For i = 1 To niveau
m = m & el(v(i)) & " in ligne " & p(v(i)) & vbCrLf
Cells(p(v(i)), 3).Interior.Color = 65535
Next i
MsgBox m

For i = 1 To niveau
Cells(p(v(i)), 3).Interior.Pattern = xlNone
Cells(p(v(i)), 3).Interior.TintAndShade = 0
Cells(p(v(i)), 3).Interior.PatternTintAndShade = 0
Next i
s = s - el(v(niveau))
ElseIf s > cible Then
s = s - el(v(niveau))
v(niveau) = c
Else
If niveau < limite Then
niveau = niveau + 1
v(niveau) = v(niveau - 1)
End If
End If
Else
v(niveau) = 0
niveau = niveau - 1
s = s - el(v(niveau))
End If
Wend
End With
MsgBox sol & " solution(s) trouvée(s)"


End Sub



modifié pour prendre la valeur cible en A1

--
h2so4
ca PAN
pique DORA
.
isabelle
Le #23133351
bonjour Karina,

voici un exemple,
http://cjoint.com/?2crc5MNkDNB

pour 20 nombres ce n'est pas trop long mais plus on augmente le nombre
d'élément dans la liste plus le temps d'exécution aumente
(exponentiellement)

isabelle



Le 2011-02-16 14:20, Karina a écrit :
Bonjour,
Si on a :
C3 : 15
C4 : 145
C5 : 3
C6 : 47
C7 : 78
C8 : 9
Et en A1, on a une valeur "cible" : 148

Je voudrais qu'Excel cherche quelles cellules doivent être
additionnées sur la plage C3:C8 pour trouver 148.
Dans mon exemple, j'aimerais qu'il me colorie en jaune les cellules C4
et C5.
S'il n'y a pas de solution, avoir un message du style "Votre problème
n'a pas de solution".

Bien sur, c'est un exemple basique qui ne necessite pas d'avoir Excel
pour le résoudre mais dans mon vrai cas, il y a beaucoup plus de
lignes !

Merci





isabelle
Le #23133361
je voulais dire "augmente" bien sur,
le temps d'exécution sera de quelque secondes pour 20 éléments dans la
liste et il sera d'environ 15 min. s'il y a 30 éléments .

isabelle

Le 2011-02-16 20:59, isabelle a écrit :
bonjour Karina,

voici un exemple,
http://cjoint.com/?2crc5MNkDNB

pour 20 nombres ce n'est pas trop long mais plus on augmente le nombre
d'élément dans la liste plus le temps d'exécution aumente
(exponentiellement)

isabelle



Le 2011-02-16 14:20, Karina a écrit :
Bonjour,
Si on a :
C3 : 15
C4 : 145
C5 : 3
C6 : 47
C7 : 78
C8 : 9
Et en A1, on a une valeur "cible" : 148

Je voudrais qu'Excel cherche quelles cellules doivent être
additionnées sur la plage C3:C8 pour trouver 148.
Dans mon exemple, j'aimerais qu'il me colorie en jaune les cellules C4
et C5.
S'il n'y a pas de solution, avoir un message du style "Votre problème
n'a pas de solution".

Bien sur, c'est un exemple basique qui ne necessite pas d'avoir Excel
pour le résoudre mais dans mon vrai cas, il y a beaucoup plus de
lignes !

Merci




Karina
Le #23135291
Merci pour vos réponses.
Les deux marchent nickel :-)

On 17 fév, 03:15, isabelle
je voulais dire "augmente" bien sur,
le temps d'ex cution sera de quelque secondes pour 20 l ments dans la
liste et il sera d'environ 15 min. s'il y a 30 l ments .

isabelle

Le 2011-02-16 20:59, isabelle a crit :



> bonjour Karina,

> voici un exemple,
>http://cjoint.com/?2crc5MNkDNB

> pour 20 nombres ce n'est pas trop long mais plus on augmente le nombre
> d' l ment dans la liste plus le temps d'ex cution aumente
> (exponentiellement)

> isabelle

> Le 2011-02-16 14:20, Karina a crit :
>> Bonjour,
>> Si on a :
>> C3 : 15
>> C4 : 145
>> C5 : 3
>> C6 : 47
>> C7 : 78
>> C8 : 9
>> Et en A1, on a une valeur "cible" : 148

>> Je voudrais qu'Excel cherche quelles cellules doivent tre
>> additionn es sur la plage C3:C8 pour trouver 148.
>> Dans mon exemple, j'aimerais qu'il me colorie en jaune les cellules C4
>> et C5.
>> S'il n'y a pas de solution, avoir un message du style "Votre probl me
>> n'a pas de solution".

>> Bien sur, c'est un exemple basique qui ne necessite pas d'avoir Excel
>> pour le r soudre mais dans mon vrai cas, il y a beaucoup plus de
>> lignes !

>> Merci- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -
Publicité
Poster une réponse
Anonyme