XL 2013 Automatiser une valeur cible sur plusieurs colonnes

[zartid]

Bonjour à tous

Voilà quelques jours que je galère sur mon tableau pour créer une macro afin d'automatiser mes calculs.

Il s'agit d'une liste de produits que je souhaite vendre, mais qui ont plusieurs niveaux de taxes. (voir fichier ci joint)
Je souhaite d'ajuster le montant de la marge (ligne 13) en fonction de cellules que l'on modifie manuellement (les niveaux de taxes 1 et 2, lignes 7 et 8) afin que les prix de vente finaux (ligne 28) soient bien égaux à ceux définis (ligne 27)
Pour le moment, je les fais colonne par colonne mais c'est très long, car j'ai plus de 100 produits !

Pouvez vous m'aider svp?

 

[Dranreb]

Bonsoir.
Pas besoin de valeur cible pour ça :En D13, à propager sur 8 colonnes :

=(D27-D12-D7-D12*D8-D16*-0,02-D12*D8*-0,02-D22-D23-D24-D25)/(1+D8-D8*0,02)

Ça peut sans doute se simplifier un peu en mettant en facteurs certains termes…

 

[mapomme]

Bonsoir zartid,

Pour le fun :

Chez moi le solveur d'Excel 2010 sait trouver une solution rapidement.

Pour cela, ajouter le complément solveur d'Excel (si ce n'est pas déjà fait) :

• cliquer sur Options
• cliquer sur compléments
• dans la fenêtre qui s'ouvre, en bas à droite cliquer sur le bouton Atteindre
• dans la fenêtre qui s'ouvre, cocher Complément Solveur puis cliquer sur OK

Normalement, Excel a ajouté un élément de menu (théoriquement dans le menu Données du ruban) nommé Solveur (vers l'extrémité droite du menu Données).

Pour réaliser un scénario dans le solveur :

• rajouter les lignes 31 et 33 (voir fichier joint) avec leurs formules
• effacer les valeurs de la ligne des marges
  
• cliquer sur le menu Solveur

dans la fenêtre qui s'ouvre :

• dans la zone objectif à définir , saisir l'adresse de la cellule $G$33
• juste en-dessous, cocher l'option Valeur et indiquer 0 comme valeur à atteindre
• dans la zone Cellules variables, saisir l'adresse de la ligne des marges : $D$13:$K$13
  
• puis cliquer sur le bouton en bas Résoudre

Le solveur trouve théoriquement une solution.

edit : avec le bon fichier

 

[Dranreb]

Ma formule simplifiée, toujours en D13, à propager sur 8 colonnes :

=(D27-D7*0,98-D22-D23-D24-D25)/(1+D8*0,98)-D12

 

[zartid]

Bonjour

Merci Dranreb ca marche très bien pour cet exemple.
en revanche j'ai une deuxième hypothèse dans laquelle ta formule fait une erreur de référence circulaire
Dans le fichier joint, V2, j'ai une hypothèse qui fait que la taxe 1 est à 850 par exemple et que la taxe 2 s'applique seulement si la valeur est supérieure à la taxe 1 (voir la colonne produit 1)
Du coup je suis obligée de passer par les valeurs cibles colonne par colonne, à moins que tu vois un autre formule.
Si non, comment puis je automatiser la valeur cible de toutes les colonnes?

 

[zartid]

mapomme, le solveur marche bien dans le premier exemple mais pas dans le V2.
J'ai donc deux solutions qui marchent bien dans la V1 mais la V2 bloque pour le moment

Merci à vous

 

[Dranreb]

Bonjour.
Pourquoi n'avoir pas tout de suite présenté le problème entier ?
Il se trouve qu'aujourd'hui je n'ai plus envie d'inverser cette nouvelle formule.
Mais ça doit certainement pouvoir se faire aussi sans utiliser de valeur cible ni le solveur.
Le principe: remplacez dans une copie de la somme en ligne 29 toutes les références aux cellules pourtant des formules par leur expression, recommencez jusqu'à ce qu'il n'y ait plus que des cellules portant des valeurs, puis manipulez la pour faire passer la marge à gauche comme valeur à calculer et la valeur en 28 à droite.

 

[zartid]

Désolée j'ai cru que ça marcherait dans les 2 cas.
merci pour votre contribution cependant, qui m'a permis de résoudre la moitié du problème.

 

[mapomme]

Bonsoir à tous,

En utilisant l'analyse scénarios (options Valeur cible) mais via VBA, on obtient rapidement une solution pour chaque colonne.

Dans le fichier joint, cliquer sur le bouton noir.

Le code est dans le Module1 :

Sub MargeParColonne()
Dim dercol&, j&

  With Sheets("Exemple")  'avec la feuille  nommée Exemple
  'obtention de la dernière colonne des produits
  dercol = .Cells(11, Columns.Count).End(xlToLeft).Column
  'on efface la ligne des marges
  .Range(.Cells(13, 4), .Cells(13, dercol)).ClearContents
  'boucle sur les produits de la colonne 4 à dercol
  For j = 4 To dercol
  'chercher à rendre la cible (la cellule ligne 30 et colonne j) égale à 0
  'avec comme cellule à modifier, la cellule ligne 13 et colonne j
  .Cells(30, j).GoalSeek Goal:=0, ChangingCell:=.Cells(13, j)
  Next j
  End With
End Sub