Microsoft 365 Valeur Cible en Boucle

[Jordan02]

Grand nouveau sur ce site, je vous adresse à tous mes salutations!

Un grand merci par avance à ceux et celle qui prendront le temps de me lire, et de me répondre (grand niais que je suis).

Je vais très probablement passer pour un novice, mais voici mon problème actuel :

- Vous trouverez en PJ un fichier contenant un tableur excel, et mon problème est sans doute assez simple. On retrouve en colonne CZ, une colonne nommée "Différence". Dans cette colonne, je dois tout simplement faire ligne par ligne -> Valeur cible -> Cellule à définir (CZ) -> valeur à atteindre : 0 - Cellule à modifier : DA (la case juste à coté).

Je sais qu'une macro existe pour créer une "loop", problème étant que je ne m'y connais pas assez pour réaliser cette macro... (finalement je souhaite que la valeur cible avec 0 à atteindre se répète systématiquement sur toutes lignes de mon classeur en colonne CZ, et modifie la colonne DA)

Si une âme charitable pourrait m'aider dans l'établissement de cette macro, je lui en serais extrêmement reconnaissant !

Merci à vous,!

 

[sylvanu]

Bonsoir,
Un essai en PJ avec :

Sub CalculTAR()
    ' Penser à activer Références/Solver
    Dim DL%, L%
    DL = Range("A65500").End(xlUp).Row
    For L = 9 To DL
        If Cells(L, "B") <> "" And Cells(L, "B") <> 0 Then
            On Error Resume Next
            SolverOk SetCell:=Range("$CZ$" & L), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("$DA$" & L)
            SolverSolve userfinish:=True
            [DC2] = L & " / " & DL
        End If
    Next L
End Sub

Le solver cherche CZ=0 en jouant sur DA.
Le clignotement est juste du à l'affichage de la progression.
Pourra être facilement masqué par l'utilisation d'un userform de progression. mais il faut déjà que la macro marche.

 

[Deadpool_CC]

Bonjour,

Désolé d'avance mais j'ai rien compris à -> Valeur cible -> Cellule à définir (CZ) -> valeur à atteindre : 0 - Cellule à modifier : DA
Si tu peux expliquer avec des mots de que signifie tes "->" ... lol ou ce qu'il faut faire dans ce fichier de manière détaillée.
tu veux absolument du vba ? c'est plus qu'un simple calcul depuis les valeurs présentes dans les autre colonne ?

 

[Jordan02]

Bonjour,

Désolé d'avance mais j'ai rien compris à -> Valeur cible -> Cellule à définir (CZ) -> valeur à atteindre : 0 - Cellule à modifier : DA
Si tu peux expliquer avec des mots de que signifie tes "->" ... lol ou ce qu'il faut faire dans ce fichier de manière détaillée.
tu veux absolument du vba ? c'est plus qu'un simple calcul depuis les valeurs présentes dans les autre colonne ?

Heyyy

Merci de ce retour (je m'attendais pas aussi vite).

Au plus simple pour expliquer (avec mes mots en quelques sortes LOL)

Dans la colonne CZ, je dois faire ligne par ligne une valeur cible pour lui faire atteindre la valeur 0. Sauf que le faire ligne par ligne... c'est long LOL (et encore, celui la est l'un des plus courts!). La cellule à modifier pour chaque ligne est celle juste à sa droite en DA (c'est un taux en fait!).

En soi le problème ce n'est même pas l'action en elle même, mais plutôt le fait de la répéter 150/200/250 fois (j'ai encore 60 fichier comme ça à traiter LOL)

Encore merci de ton retour et de ton temps

 

[Deadpool_CC]

Donc si j'ai compris c'est ton taux en DA que tu dois mettre afin que ta valeur en CZ soit égale à 0.
le seul truc c'est qu'en CZ, la formule ne fait aucune référence à ton taux ...

Si tu le fait à la main pour la première ligne par exemple ...
=> explique nous chaque étape de ce que tu fais histoire qu'on puisse voir comment t'aider

 

[sylvanu]

Bonsoir Jordan, Deadpool,

Dans la colonne CZ, je dois faire ligne par ligne une valeur cible pour lui faire atteindre la valeur 0.

C'est impossible, tout du moins quand la valeur de la colonne B est nulle.
Par ex, cellule CZ59, le minimum possible pour CZ59 est 738.297 avec DA=5996389.431,
avec DA=5996389.432 on obtient pour CZ #Nombre. ( à cause de CX59 )
Or la valeur de DA est totalement hors specs puisqu'il oscille habituellement autour de 1.05.
Donc à mon avis le problème vient des valeurs nulles en colonne B. Vous ne pourrez pas converger dans l'état.

 

[Deadpool_CC]

a tourner et retourner ...
est-ce que c'est la formule en CZ qu'il faut repenser pour que cela te référence en CZ [la bonne colonne]$5 qui correspond à ta première donnée non nulle de la ligne ou tu fais le calcul ?

car j'ai l'impression que tu as adapté les formule de chaque ligne à la main pour taper la bonne colonne en ligne 5 à chaque fois.
bref explique nous clairement ce qu'il faut faire.

 

[Jordan02]

Bonsoir Jordan, Deadpool,

C'est impossible, tout du moins quand la valeur de la colonne B est nulle.
Par ex, cellule CZ59, le minimum possible pour CZ59 est 738.297 avec DA=5996389.431,
avec DA=5996389.432 on obtient pour CZ #Nombre. ( à cause de CX59 )
Or la valeur de DA est totalement hors specs puisqu'il oscille habituellement autour de 1.05.
Donc à mon avis le problème vient des valeurs nulles en colonne B. Vous ne pourrez pas converger dans l'état.

Il peut y avoir des "trous" dans la colonne B. Il s'agit de cotation sur la bourse parisienne entre 1870 et 1922. Il n'y avait parfois pas de cours sur un mois entier!
J'ai oublié de préciser en effet ce point, il est donc normal d'avoir ces valeurs aberrantes!

 

[Jordan02]

a tourner et retourner ...
Ast-ce que c'est la formule en CZ qu'il faut repenser pour que cela te référence en CZ [la bonne colonne]$5 qui correspond à ta première données non nulle de la ligne ou tu fait le calcul ?
Regarde la pièce jointe 1144605
car j'ai l'impression que tu as adapté les formule de chaque ligne à la main pour taper la bonne colonne en ligne 5 à chaque fois.
bref explique nous clairement ce qu'il faut faire

Je pense que je comprends l’imbroglio (et il est entièrement de ma faute!).

J'ai déjà traité la valeur cible (ici dans ton encadré en vert), mais en descendant plus bas dans cette même colonne, tu verras des valeurs différentes de 0. Ce sont celle ci que je souhaite faire passer à 0 via la valeur cible (en modifiant le taux juste à droite). Et c'est ici que je souhaite savoir si il existe une macro "loop" pour le faire automatiquement (et non pas ligne par ligne, comme j'ai commencé à le faire dans ton screen ci haut)

Pour la formule en effet, je perds un temps monstre, mais comme tu as pu le remarquer (chapeau de l'avoir remarqué d'ailleurs!), chaque 6 mois (surligné en bleu), je dois avancer d'une colonne sur la ligne 5.
( Pour faire un cours résumé, les 3 lignes du hauts représentent des paiements d’intérêt semestriels à une date fixe. A chaque fois que 6 mois passe en ligne, on doit avancer d'une colonne sur la ligne 5, dans la formule en CZ, mais c'est encore un autre problème LOL)

 

[Deadpool_CC]

RE ...
déjà pour ta formule en colonne CZ
colle en CZ9 la formule ci-dessous et tire vers le bas

=CY9-INDIRECT(ADRESSE(5;COLONNE(DECALER($C9;0;NB.SI($C9:$CX9;"=0")))))*B9

quel formule pour modifier ton taux à droite ? comment tu veux que l'on ait 0 en CZ à partir du taux ... je comprend pas la logique

(edit)
Et comment tu calcul ton taux ...en DA
je parle même pas de comment tu fais quand ton cours nu est vide ...lol

 

[Jordan02]

RE ...
déjà pour ta formule en colonne CZ
colle en CZ9 la formule ci-dessous et tire vers le bas

=CY9-INDIRECT(ADRESSE(5;COLONNE(DECALER($C9;0;NB.SI($C9:$CX9;"=0")))))*B9

quel formule pour modifier ton taux à droite ? comment tu veux que l'on ait 0 en CZ à partir du taux ... je comprend pas la logique

Et comment tu calcul ton taux ...en DA

Le taux s'ajuste automatiquement quand on fait une valeur cible dans la colonne CZ. Le taux est repris dans l'ensemble des formules de la colonne C à CX.

Si tu prends par exemple (au hasard) la cellule CZ95.

Je vais utiliser l'outil valeur cible sur cette cellule et faire :

- cellule à définir : CZ95
- valeur à atteindre : 0
- cellule à modifier DA95

Une fois que j'ai fait ça, l'ensemble de la ligne se modifie (ce sont des cashflows) ainsi que le taux en colonne DA.

Je n'ai pas de problème de formule en soi, c'est juste que l'opération expliquée au dessus doit être répété pour la ligne 96, 97, etc.... un nombre interminable de fois...

Et je sais qu'il existe une macro qui peut effectuer cette action automatiquement (répèter l'opération du dessus sur l'ensemble de la colonne, afin d'éviter que je le fasse un par un)

Je sais pas si j'ai été assez précis (j'ai l'impression que non...)

Encore merci de ta patiente

 

[Deadpool_CC]

Ya pas ... il me manque un truc pour la compréhension ... tu parles d'un outil "valeur cible" : c'est une vba existante que tu nous a pas remis dans le fichier ?
je vois toujours pas comment tu détermine ton taux en DA qui semble conditionné tout les autres formules de C à CX et à priori c'est sur ce taux qu'il faut agir pour qu'au final tes calcules en cascade ramène la colonne CZ à 0.
Mais comment tu sais comment faire varier ton taux pour que CZ soit à 0 ...

 

[Deadpool_CC]

Le taux s'ajuste automatiquement quand on fait une valeur cible dans la colonne CZ.

J'ai trouvé aucune formule qui utilise CZ ... j'ai vraiment un soucis de compréhension de la démarche à mettre en place.

Quelqu'un avec un Œil neuf pourra peut-être mieux comprendre ...
je repasse demain au cas ou ... bonne soirée

 

[Jordan02]

Sur Excel, je fais :
Données -> Analyse de Scénario -> Valeur Cible.

Un pop up apparait avec les infos plus haut :

- cellule à définir : CZ95
- valeur à atteindre : 0
- cellule à modifier DA95

Le taux est en fait repris dans chacune des colonnes de C à CX. La somme de C à CX (la colonne CY) étant lié à l'ensemble des colonnes, la colonne taux (DA) se modifie automatiquement quand on indique 0 dans la valeur souhaitée...

Je dois mal m'exprimer je pense, encore merci de ton temps en tout cas! je vais regarder à droite à gauche !

 

[Jordan02]

Bonsoir,
Un essai en PJ avec :

Sub CalculTAR()
    ' Penser à activer Références/Solver
    Dim DL%, L%
    DL = Range("A65500").End(xlUp).Row
    For L = 9 To DL
        If Cells(L, "B") <> "" And Cells(L, "B") <> 0 Then
            On Error Resume Next
            SolverOk SetCell:=Range("$CZ$" & L), MaxMinVal:=3, ValueOf:="0", ByChange:=Range("$DA$" & L)
            SolverSolve userfinish:=True
            [DC2] = L & " / " & DL
        End If
    Next L
End Sub

Le solver cherche CZ=0 en jouant sur DA.
Le clignotement est juste du à l'affichage de la progression.
Pourra être facilement masqué par l'utilisation d'un userform de progression. mais il faut déjà que la macro marche.

Yes c'est exactement ça, il me reste plus qu'à voir pour le transposer sur d'autres fichiers !!!!

Si je veux la modifier (admettons si le taux n'est pas en DA mais en EF) j'ai juste à changer le nom de la colonne dans ton code ??

Un grand MERCIIIIIII