XL 2019 Synthétiser plusieurs calculs

[ronalddop]

Bonjour,

Je vous contacte car j'ai réalisé une page Excel avec plusieurs calculs dans des cellules. Je voulais savoir s'il était possible de récupérer facilement la formule globale d'une cellule ?
Ex :
A2 = A1 *0.9
A3=A2+5
Je voudrais trouver la formule de A3 qui me donne : A3=A1*0.9+5

Merci d'avance.

Cordialement.

 

[Hasco]

bonjour,

Euh ???? Ne l'avez-vous pas A1*0.9+5 Non ???

Cordialement

 

[ronalddop]

bonjour,

Euh ???? Ne l'avez-vous pas A1*0.9+5 Non ???

Cordialement

oui ici c'est facile.
J'ai donné cet exmple pour que ce soit simple à comprendre mais en vrai il y a une vingtaine de calculs différents avec à chaque fois plusieurs références.

 

[Hasco]

Re,

Bon. Essayer de détailler un peu, car il n'y a pas de solution magique dans excel. Joignez un fichier exemple, anonymisé et sans données confidentielles, avec vos explications précises.

cordialement

 

[ronalddop]

Bonjour,

Alors voici mon fichier ci-joint.
Ce fichier permet de calculer un revenu de gérant en fonction d'un chiffre d'affaire d'une entreprise.
Il permet de faire différentes simulations et de comparer différents scénarios (société soumise à l'IR ou à l'IS, puis imposition personnelle à l'IR ou prélèvement forfaitaire,...). Bref c'est pour remettre le contexte si cela peut aider.

- Pour ma première question :
Je pars de 4 données d'entrée en gris (CA, Charges, Rémunération et taux de cotisations) qui sont entrées à la main. Ensuite divers calculs sont réalisés : CA disponible, Charges sociales, revenu chargé... jusqu'à obtenir un résultat après IS (cellule B11).
La formule en cellule B11 est B7-B10, est-il possible de récupérer via une manip sur excel la formule absolue de la cellulue B11 de façon à ce qu'elle se rapporte à mes données d'entrée ? En gros qu'il remplace B7-B10 par (Formule de B7)-(Formule de B11) et ainsi de suite pour avoir une formule générale ?

- Autre question :
L'objectif de mon fichier est de déterminer pour un CA donné, quel est la rémunération optimale à se verser pour obtenir le meilleure revenu net (rémunération + dividendes) après impôt sur le revenu.
Je voulais donc réaliser un graphique (en cellule A29) qui fait varier pour un CA donné, la rémunération tous les 1000€ et calcule le net après IR. Pour ce faire, j'ai fait un tableau (à partir de la cellule N30) en entrant tous les cas (rem=1000, rem=2000...). N'est-il pas possible de se passer de ce tableau et dire à Excel de tracer un graphique en prenant la cellule O30 qu'il fait varier de 1000 à 50 000 par pas de 1000 et de récupérer en ordonnée les cellules O43 et O47 ?

Merci d'avance.

 

[Hasco]

bonsoir,

Pour la question 1 : je ne sais pas si c'est vraiment synthétisé mais en une seule formule cela donne :
=(($B$1-$B$2)-($B$4+($B$4*$D$1/100)))- SI((($B$1-$B$2)-($B$4+($B$4*$D$1/100)))<38000;15/100*(($B$1-$B$2)-($B$4+($B$4*$D$1/100)));38000*15/100)+ SI((($B$1-$B$2)-($B$4+($B$4*$D$1/100)))>38000;((($B$1-$B$2)-($B$4+($B$4*$D$1/100)))-38000)*33/100;0)

Pour la deuxième question et conformément à la charte du forum, créez une nouvelle discussion.

Bon week-end

 

[ronalddop]

bonsoir,

Pour la question 1 : je ne sais pas si c'est vraiment synthétisé mais en une seule formule cela donne :
=(($B$1-$B$2)-($B$4+($B$4*$D$1/100)))- SI((($B$1-$B$2)-($B$4+($B$4*$D$1/100)))<38000;15/100*(($B$1-$B$2)-($B$4+($B$4*$D$1/100)));38000*15/100)+ SI((($B$1-$B$2)-($B$4+($B$4*$D$1/100)))>38000;((($B$1-$B$2)-($B$4+($B$4*$D$1/100)))-38000)*33/100;0)

Pour la deuxième question et conformément à la charte du forum, créez une nouvelle discussion.

Bon week-end

Merci pour votre retour.
Pour la formule que vous avez extrait, je voulais savoir si excel savait le faire tout seul pour retrouver cette formule ou s'il faut le faire à la main ?

 

[Hasco]

Re,

Il faut le faire à la main !

Pour alléger un peu la chose, vous avez la possibilité de nommer une formule. Par exemple la partie de votre formule qui calcul le bénéfice ($B$1-$B$2)-($B$4+($B$4*$D$1/100) : dans l'onglet 'Formules' du ruban cliquer sur le bouton 'Nouveau nom'. Ce qui nous donne :

Vous pouvez également mettre cette partie de la formule complète dans une cellule et nommer cette cellule. C'est ce qui se fait le plus souvent. Plus facile à modifier si l'on constate une erreur.

Une fois la formule nommée d'une façon ou d'une autre vous pouvez remplacer toutes les occurrences de cette formule par son nom : =Bénéfice- SI(Bénéfice<38000;15/100*Bénéfice;38000*15/100)+ SI(Bénéfice>38000;Bénéfice*33/100;0)

P.S. pour mettre une formule sur plusieurs lignes, dans la barre de formule, en cours de saisie, taperz ALT+ENTREE, étirez la barre de formule vers le bas en conséquence.


Cordialement

 

[mapomme]

Bonjour @ronalddop, @Roblochon , à tous,

Je vous renvoie vers un de mes anciens messages de 2013. Prendre la version v2b.

Aide à la construction de formules longues​

 

[mapomme]

Re-bonjour à tous,

J'ai modifié votre fichier pour y intégrer ma fonction Remplace_Ref_par_Formule2b().

Le code de la macro est dans module1.

Pour le fichier joint, voici un exemple d'utilisation :

• cliquer sur le bouton Hop!
• à la première boite de dialogue, sélectionner la cellule B11 (avec la formule à développer)
• à la deuxième boite de dialogue, sélectionner la cellule B14
• à la troisième boite de dialogue, sélectionner la plage B1:B10 (ou une plage incluant au moins les antécédents intéressants à intégrer dans le développement final)
• à la quatrième boite de dialogue, cliquez sur le bouton "Annuler"
• -> la cellule B14 contient la formule développée

nota : la cellule de destination peut être la cellule de la formule à développer. Je ne le conseille pas à moins d'avoir préalablement enregistré une version de sauvegarde du fichier.

 

[Hasco]

bonjour @mapomme,

voici qui mériterait une communication à Microsoft pour une intégration dans l'outil

Cordialement

 

[mapomme]

@Roblochon ,

La macro est imparfaite de par la méthode utilisée. Rien n'assure que tous les remplacements possibles aient été faits. Je l'avais créée pour une utilisation restreinte et à mon usage quand je construis une formule à rallonge avec l'aide de lignes ou colonnes auxiliaires pendant la phase d'élaboration de la formule. La macro permettait au bout du bout de supprimer les lignes et colonnes auxiliaires. Donc ayant fait la macro, je savais aussi comment l'utiliser correctement et ça suffisait.

En général, si on sélectionne une plage d'antécédents en ligne (où les calculs se font de gauche à droite) ou en colonne (où les calculs se font du haut vers le bas), le résultat est complet.

Maintenant, je vais sans doute essayer de la reprendre (mais rien ne presse) pour corriger ce manquement. J'ai horreur de revenir sur de vieux codes... Mais là, c'est nécessaire pour assurer un résultat correct.

La correction est peut-être simple, il s'agit (peut-être) simplement de boucler une deuxième fois. Je vais voir.

 

[Hasco]

Re,

macro peut-être imparfaite (en existe-t-il de parfaite), l'idée elle est parfaite et mérite d'être signalée à crosoft. C'est suffisamment rare même ici pour être souligné.

 

[ronalddop]

Re-bonjour à tous,

J'ai modifié votre fichier pour y intégrer ma fonction Remplace_Ref_par_Formule2b().

Le code de la macro est dans module1.

Pour le fichier joint, voici un exemple d'utilisation :

• cliquer sur le bouton Hop!
• à la première boite de dialogue, sélectionner la cellule B11 (avec la formule à développer)
• à la deuxième boite de dialogue, sélectionner la cellule B14
• à la troisième boite de dialogue, sélectionner la plage B1:B10 (ou une plage incluant au moins les antécédents intéressants à intégrer dans le développement final)
• à la quatrième boite de dialogue, cliquez sur le bouton "Annuler"
• -> la cellule B14 contient la formule développée

nota : la cellule de destination peut être la cellule de la formule à développer. Je ne le conseille pas à moins d'avoir préalablement enregistré une version de sauvegarde du fichier.

Bonjour mapomme, Roblochon,

Merci @Roblochon pour cette astuce pour nommer des formules, je ne connaissais pas. Ça va effectivement simplifier les formules.

Mais c'est exactement l'utilitaire de @mapomme que je cherchais !!
Beau travail !! C'est effectivement dommage que ça ne soit pas intégré d'office dans Excel.

Cette macro serait encore mieux si elle s'affranchissait de demander les formules intermediaires.

Je n'ai pas encore trop compris comment elle fonctionnait mais les formules resultat ne sont pas toujours ordonnées dans le même ordre. Après elles fonctionnent.

Juste une question, j'ai essayé d'utiliser la macro pour récupérer la formule de la cellule L8, et je n'ai pas réussi. J'ai testé en prenant différentes zones pour les formules intermédiaires mais sans succès, le résultat est toujours : =SOMME(L3:L6)

Mais sur les autres calculs successifs d'une même ligne, ça fonctionne parfaitement !

 

[mapomme]

Re,

Juste une question, j'ai essayé d'utiliser la macro pour récupérer la formule de la cellule L8, et je n'ai pas réussi. J'ai testé en prenant différentes zones pour les formules intermédiaires mais sans succès, le résultat est toujours : =SOMME(L3:L6)

Tout à fait normal. L3:L6 est une plage de cellules et non des cellules individuelles. La macro ne fonctionnera pas.

Elle pourra fonctionner si on remplace =SOMME(L3:L6) par = L3 + L4 + L5 + L6 et si comme plages d'antécédents, on désigne les deux plages H1:L7 et A1:27.

Dans ce cas, la formule développée donne une "monstruosité formulique " :
(et la formule fonctionne ! Vous pouvez la coller dans n’importe quelle cellule vide -> le résultat sera
bien 6795,7)

=((SI((((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))>J3;J3;(((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))))*0)+((SI((((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))>J4;J4-J3;SI((((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))>J3;(((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))-J3;0)))*K4/100)+((SI((((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))>J5;J5-J4;SI((((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))>J4;(((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))-J4;0)))*K5/100)+((SI((((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))>J6;J6-J5;SI((((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))>J5;(((B4)*0,9+((((B1-B2)-((B4*$D$1/100)+B4))-((SI(((B1-B2)-((B4*$D$1/100)+B4))>38000;(((B1-B2)-((B4*$D$1/100)+B4))-38000)*33/100;0))+(SI(((B1-B2)-((B4*$D$1/100)+B4))<38000;15/100*((B1-B2)-((B4*$D$1/100)+B4));38000*15/100)))))*0,6))-J5;0)))*K6/100)