excel: ajuster automatiquement tableau&donnés Fermé

Question

Bonjour, 

Tout d'abord j'espère que je serais clair et que je ne créerai pas de doublons. Peut être ai-je utilisé les mauvais mots clé... 
J'ai vainement recherché sur le site de support office de microsoft et sur divers sites spécialisés informatique tels que ccm. 

Mon problème concerne excel, j'ai la version 2007 mais il faudrait que la solution soit compatible avec office 1997-2003. 

Données non nécessaires à la compréhension du problème (sauter ce paragraphe pour les pressés)--> 
Je suis étudiant en économie et je travaille sur la création de tableaux d'amortissement constant. j'ai trouvé pas mal de modèles de tableaux avec tous types d'amortissements mais aucun qui propose un ajustement automatique en fonction des données qu'il contient. 
Plusieurs contraintes dans la construction de mon tableau: 
- données a saisir = capital emprunté, taux annuel, durée, [périodicité (recherchev, listes, c'est à dire menu déroulant avec 1,2,4 ou 12 possible et remplissage dans la cellule de droite " annuités, semestrialités, trimestrialités, mensualités").] 
- données automatiques (calculs) = taux proportionnel par période, nombre de remboursements, ammortissement du capital. 


Ma question est donc simple (j'espère que la réponse aussi); comment ajuster automatiquement le format (sa taille) du tableau aux nombre de lignes qu'il calculera ?
Car le nombre de lignes ne sera pas le même si on emprunte pour 1 an en remboursant tous les ans (donc une ligne) ou si on emprunte 3 ans en remboursant tous les mois (3*12= 36 lignes). 


Je pencherai pour une formule en (si) lié a la case "nombre de remboursement" (qui délimitera forcement le nombre de lignes du tableau)... 
Merci de votre réponse et du temps consacré a ce problème. Je suis à votre disposition si ce n'est pas assez précis.

gege · Answer

Bonjour,
Dans l'ouvrage Excel 2003 de la collection En quelques clics aux Editions Micro Applications tu as un exemple très complet de tableau d'amortissement page 7.
A+

gégé

jujusin · Answer

Merci beaucoup pour le tuyau mais je ne dispose pas de cet ouvrage, je vais aller le consulter a ma bibliothèque universitaire, je prend note. J'espère que cela répond a ma question principale et que ca ne me redonne pas un enième exemple de tableau d'amortissement (il y en a un bon également sur ce site pour les économistes, financiers ou chefs d'entreprises: https://www.moneyvox.fr/
Et gratuit en plus!

Bon dimanche gege et encore merci.

eriiic · Answer

Bonjour,

Si par format tu entends encadrement des cellules je ne pense pas que ce soit réalisable par formule, il faudra une macro vba.
Si le fait que la cellule soit vide est suffisant tu fais tes formules en testant d'abord si le nbre de remboursements est dépassé.
Ex:
=si(numRemboursement>nbrRemboursements;"";ta_formule)
Si ton tableau ne doit pas faire apparaitre une colonne avec numRemboursement tu peux utiliser le n° de ligne où tu es avec ligne()
=si(ligne()-1>nbrRemboursements;"";ta_formule) en supposant que tu aies une ligne de titres
eric

jujusin · Answer

Oula... macro vba... Je ne suis qu'économiste, je bidouille un peu en informatique mais je ne sais pas programmer... Une macro vba est compatible office 1997 et 2003 ? Peut être est ce la solution... comment l'implanter (je suis novice en la matière j'avou...).

Pour ce qui est du numéro de remboursement j'ai utilisé la formule ci dessous:
Ligne 1 du tableau: "1"                                        ==>A1
Ligne 2 du tableau: "=SI(A2=0;"";A1+1)           ==>A2
et ainsi de suite.
Ce qui doit se conformer à ce que j'ai compris dans tes explications.

Il est quand même curieux qu'il n'existe pas de formule calculant non pas un nombre mais calculant l'encadrement des cellules... Ca ne dois pas être difficilement réalisable lorsqu'on voit la complexité des calculs réalisables et la puissance d'excel...

Merci eric ;-)

eriiic · Answer

Oulalaa, ben c'est pas gagné.
En A2 tu fais référence à A2 ce qui n'est pas permis.

Je pensais plus à ce que tu calcules dans une cellule le nombre de remboursements en fonction des critères de départ.
Imaginons que tu réserves les 9 1ères lignes à la saisie de ces critères et à l'affichage de certaines données, avec par exemple en B3 le nombre de remboursements et que tu l'aies nommée nbRemb.
En ligne 10 tes titres:
            A                    B                                C        D         E
10    N°Remb     MontantRembursement     ....    ...        ...
11        1             =si(....;...;...)                 ....     ....     ....
12
en B11    =si($A11>nbRemb;"";ta_formule1) 
en C11    =si($A11>nbRemb;"";ta_formule2) 
....
en A12    =si($A11+1>nbRemb;"";$A11+1)

Tu recopies B11:E11 (selon le nombre de colonnes utilisées) vers B12:E12
Ensuite tu recopies A12:E12 vers le bas sur 100 lignes par exemple

Si tu veux je te ferais une feuille exemple.
Pour ce qui est defaire une macro pour l'encadrement ça se fait très facilement avec l'enregistreur de macro. Le principe est de faire la présentation que tu tu veux et excel enregistre tes actions. Ensuite il reproduit ce que tu as fait lorsque tu lances la macro. Bien sûr il y a qcq astuces, des choses à faire et à ne pas faire lors de l'enregistrement pour que ça marche bien ensuite mais le plus gros problème sera la compatibilité excel97 à moins que tu n'aies cette version.
Et cette difficulté de version se retrouvera aussi au niveau des listes déroulantes de choix. Que savait faire excel 97 et comment ? Alors là...

eric

gege · Answer

Petite question :

Comment peut-on transmettre un fichier à un participant de "commentcamarche".

gege

eriiic · Answer

Bonsoir gege,

Tu peux déposer un fichier sur www.cijoint.fr et ensuite coller le lien dans un post.
Si c'est pour cette discussion il m'a contacté par MP et je lui ai retourné son fichier avec une macro adaptée à son cas.
Mais ceci dit plusieurs solutions ne nuisent pas, on apprend souvent à voir les réponses des autres.

eric

gege · Answer

Voici une autre solution : je vais voir si je peux t'envoyer le fichier
Tu as en dessous le contenu des cellules. Attention, il faut faire une recopie sur un nombre max de ligne même si celles-ci n'apparaîtront pas à l'affichage.

Tableau d'amortissement				
				
Capital	 100 000,00 € 			
Durée en années	5			
Taux annuel	5%			
Périodicité	Année	1	0,05	
Nombre total de versements 	5			
Calcul de la somme constante à rembourser :				23 097,48 €
	Reste à Rembourser	Intérêts	Amortissements	Annuités
1	 100 000,00 € 	 5 000,00 € 	 18 097,48 € 	 23 097,48 € 
2	 81 902,52 € 	 4 095,13 € 	 19 002,35 € 	 23 097,48 € 
3	 62 900,17 € 	 3 145,01 € 	 19 952,47 € 	 23 097,48 € 
4	 42 947,69 € 	 2 147,38 € 	 20 950,10 € 	 23 097,48 € 
5	 21 997,60 € 	 1 099,88 € 	 21 997,60 € 	 23 097,48 € 
	  	  	  	  


Contenu des cellules



Tableau d'amortissement				
				
Capital	100000			
Durée en années	5			
Taux annuel	0,05			
Périodicité	Année	=SI(B6="Année";1;SI(B6="Semestre";2;SI(B6="Trimestre";4;12)))	=+B5/C6	
Nombre total de versements 	=+B4*C6			
Calcul de la somme constante à rembourser :				=-VPM(D6;B7;B3)
	Reste à Rembourser	Intérêts	Amortissements	Annuités
1	=SI(A10="";"";+B3)	=SI(A10="";"";+B10*$B$5/$C$6)	=SI(A10="";"";+E10-C10)	=SI(A10="";"";+E8)
=SI(A10<$B$7;A10+1;"")	=SI(A11="";"";+B10-D10)	=SI(A11="";"";+B11*$B$5/$C$6)	=SI(A11="";"";+D10*(1+$B$5/$C$6))	=SI(A11="";"";+C11+D11)
=SI(A11<$B$7;A11+1;"")	=SI(A12="";"";+B11-D11)	=SI(A12="";"";+B12*$B$5/$C$6)	=SI(A12="";"";+D11*(1+$B$5/$C$6))	=SI(A12="";"";+C12+D12)
=SI(A12<$B$7;A12+1;"")	=SI(A13="";"";+B12-D12)	=SI(A13="";"";+B13*$B$5/$C$6)	=SI(A13="";"";+D12*(1+$B$5/$C$6))	=SI(A13="";"";+C13+D13)
=SI(A13<$B$7;A13+1;"")	=SI(A14="";"";+B13-D13)	=SI(A14="";"";+B14*$B$5/$C$6)	=SI(A14="";"";+D13*(1+$B$5/$C$6))	=SI(A14="";"";+C14+D14)
=SI(A14<$B$7;A14+1;"")	=SI(A15="";"";+B14-D14)	=SI(A15="";"";+B15*$B$5/$C$6)	=SI(A15="";"";+D14*(1+$B$5/$C$6))	=SI(A15="";"";+C15+D15)
=SI(A15<$B$7;A15+1;"")	=SI(A16="";"";+B15-D15)	=SI(A16="";"";+B16*$B$5/$C$6)	=SI(A16="";"";+D15*(1+$B$5/$C$6))	=SI(A16="";"";+C16+D16)
=SI(A16<$B$7;A16+1;"")	=SI(A17="";"";+B16-D16)	=SI(A17="";"";+B17*$B$5/$C$6)	=SI(A17="";"";+D16*(1+$B$5/$C$6))	=SI(A17="";"";+C17+D17)
=SI(A17<$B$7;A17+1;"")	=SI(A18="";"";+B17-D17)	=SI(A18="";"";+B18*$B$5/$C$6)	=SI(A18="";"";+D17*(1+$B$5/$C$6))	=SI(A18="";"";+C18+D18)
=SI(A18<$B$7;A18+1;"")	=SI(A19="";"";+B18-D18)	=SI(A19="";"";+B19*$B$5/$C$6)	=SI(A19="";"";+D18*(1+$B$5/$C$6))	=SI(A19="";"";+C19+D19)
=SI(A19<$B$7;A19+1;"")	=SI(A20="";"";+B19-D19)	=SI(A20="";"";+B20*$B$5/$C$6)	=SI(A20="";"";+D19*(1+$B$5/$C$6))	=SI(A20="";"";+C20+D20)
=SI(A20<$B$7;A20+1;"")	=SI(A21="";"";+B20-D20)	=SI(A21="";"";+B21*$B$5/$C$6)	=SI(A21="";"";+D20*(1+$B$5/$C$6))	=SI(A21="";"";+C21+D21)
=SI(A21<$B$7;A21+1;"")	=SI(A22="";"";+B21-D21)	=SI(A22="";"";+B22*$B$5/$C$6)	=SI(A22="";"";+D21*(1+$B$5/$C$6))	=SI(A22="";"";+C22+D22)
=SI(A22<$B$7;A22+1;"")	=SI(A23="";"";+B22-D22)	=SI(A23="";"";+B23*$B$5/$C$6)	=SI(A23="";"";+D22*(1+$B$5/$C$6))	=SI(A23="";"";+C23+D23)
=SI(A23<$B$7;A23+1;"")	=SI(A24="";"";+B23-D23)	=SI(A24="";"";+B24*$B$5/$C$6)	=SI(A24="";"";+D23*(1+$B$5/$C$6))	=SI(A24="";"";+C24+D24)
=SI(A24<$B$7;A24+1;"")	=SI(A25="";"";+B24-D24)	=SI(A25="";"";+B25*$B$5/$C$6)	=SI(A25="";"";+D24*(1+$B$5/$C$6))	=SI(A25="";"";+C25+D25)
=SI(A25<$B$7;A25+1;"")	=SI(A26="";"";+B25-D25)	=SI(A26="";"";+B26*$B$5/$C$6)	=SI(A26="";"";+D25*(1+$B$5/$C$6))	=SI(A26="";"";+C26+D26)
=SI(A26<$B$7;A26+1;"")	=SI(A27="";"";+B26-D26)	=SI(A27="";"";+B27*$B$5/$C$6)	=SI(A27="";"";+D26*(1+$B$5/$C$6))	=SI(A27="";"";+C27+D27)
=SI(A27<$B$7;A27+1;"")	=SI(A28="";"";+B27-D27)	=SI(A28="";"";+B28*$B$5/$C$6)	=SI(A28="";"";+D27*(1+$B$5/$C$6))	=SI(A28="";"";+C28+D28)
=SI(A28<$B$7;A28+1;"")	=SI(A29="";"";+B28-D28)	=SI(A29="";"";+B29*$B$5/$C$6)	=SI(A29="";"";+D28*(1+$B$5/$C$6))	=SI(A29="";"";+C29+D29)

gege · Answer

Pour plus de facilités le fichier excel est à l'adresse suivantre :

http://www.cijoint.fr/cij106514301231501.xls 

Merci à Eriiic pour l'info sur le transfert des fichiers

Dans le bouquin cité précedemment, l'auteur utilise la fonction MOIS.DECALER qu'il faut le cas échéant installer en utilisant Macro Complémentaires.
A+

gégé

jujusin · Answer

merci a eriiic et a gege pour leur aide précieuse, Je me suis fait aidé par un prof d'informatique, il n'a pas fait mieux ^^
Bonne continuation !

Vaucluse · Answer

Bonsoit jujusin
En attendant mieux dans toutes les options que l'on vous propose, j'ai déposé sur cijoint une variante de votre fichier incluant une modeste option pour colorier les lignes et les encadrer lorsqu'elles sont affectées
Mais bien entendu, elle reste modeste et seule, la mise en forme est affectée par cette solution

http://www.cijoint.fr/cij97502728031926.xls 
BCRDLMNT

eriiic · Answer

Bonjour Vaucluse,

Ah ben oui y'a les bordures aussi dans les formats conditionnels béta que je suis :-s
Avec la question de départ qui était 'avec formule'  je n'ai même pas réfléchi...
Bonne soirée
eric

Vaucluse · Answer

'soir Eric
On peut pas penser à tout! Mais ça me parait tellement simple que je doute que ce soit sur ce sujet que les prof d'informatiques de Jujusin se soient cassé les dents!
Ceci dit, Jujusin nous en reparleras sans doute, mais si ça lui convient, on pourra même lui proposer de colorer les pairs en jaune et les impairs en bleu
Bonne soirée, bon WE si nous ne nous croisons pas d'ici là.

jujusin · Answer

bonsoir,

Ne vous inquiettez pas, mon prof m'a donné un boulot béton a réaliser sur excel avec une jolie mise en forme etc... Je m'y met dès que j'ai le temps (car paradoxalement, week end ne rime pas avec temps libre... :s )
Ainsi, je pense que je vous resoliciterai plus tard !
Je relancerai un post dédié si ma reponse n'est pas sur le forum. Dans le cas contraire, je me permettrai de vous soliciter directement si ce n'est qu'un problème d'adaptation a mon énoncé...
En tout cas merci beaucoup de votre précieuse aide, je vous tiens au courant des futures modalités de remboursement ( ca va être plus compliqué niveau formules... Je vais devoir utiliser les formules spéciales "financières" du style "VPM, NPM, VA..."

Je devrai réaliser un sommaire comprenant plusieurs liens vers plusieurs pages:
- Calcul annuité de remboursement.
- Taux proportionnel et taux équivalent.
- Taux Effectif Global.
- Tableau d'amortissement d'emprunt.
- une page de calculs financiers au choix (pertinent forcement ;-) )
Tout ceci avec une mise en forme particulière... Je vais scaner ma feuille d'énoncé et je la poste sur cijoint (ca va passer de l'image (poids) sur ce site?...).
Ceci dit, je vais travailler dessus avant afin de maitriser excel car c'est quand meme le but, je ne veux pas dépendre de crack excel comme vous !

Aller, sur ce, bonne soirée à tous, merci.

Excel: ajuster automatiquement tableau&donnés

14 réponses