Systeme par tranches, mais avec prise en compte de l'annee precedente

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

un enorme merci, je vais passer la journee de demain en etudiant tout ca, je te remercie enormement pour ton aide ! Si j'ai des questions, je ne manquerai pas de te contacter...

Merci a toi et aux experts du site, vous m'aidez a chaque fois a progresser (et m'aidez tout court aussi !),

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour Cisco et un grand, grand merci !

Voici mes premieres questions :

- dans le fichier ter, en B et C26, j'ai deux montants, mais en fait une fois mon plafond de 700 000 unites atteint une annee, le calcul devrait s'arreter, aurais tu stp une idee pour que la formule prenne cela en compte ?

Dois je rajouter par exemple un =SI(C20>B17,0,D20) quelque part dans la formule que tu proposes et le tirer d'annee en annee ?

- dans le fichier bis, comment eviter les valeurs negatives pour les annees ou je n'avais aucune unite enregistree ?

je vais reregarder comment tu as procede dans le fichier bis et reviendrai surement vers toi avec d'autres questions...

Merci beaucoup d'avance a toi et excellente journee, ainsi qu'aux personnes presentes sur le forum !

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

agnesd à dit:

- dans le fichier ter, en B et C26, j'ai deux montants, mais en fait une fois mon plafond de 700 000 unités atteint une année, le calcul devrait s'arrêter, aurais tu stp une idée pour que la formule prenne cela en compte ?

Cliquez pour agrandir...

En B37, tu as un calcul pour des entrées égales à 743657 qui donne 50 000*12+ (100 000-50 000)*11+(200 000-100 000)*10+(700 000-200 000)*5 - 0 = 4 650 000. Les entrées en plus de 700 000 ne sont pas prises en compte.
En C37, le calcul correspond à 743657+27 entrées et donne 4 650 000 - 4 650 000 = 0. Comme le plafond de 700 000 a été dépassé, toutes les autres valeurs, pour cette ligne, seront égales à 0 car le calcul donnera toujours 4 650 000 - 4 650 000 .
Si tu veux, on peut faire disparaitre ces 0 de deux manières différentes :
* Soit avec un format personnalisé qui "efface" tous les 0 du tableau
* Soit avec une condition SI en plus dans la formule.
Qu'est-ce qui t'arrange le plus ?

agnesd à dit:

- dans le fichier bis, comment éviter les valeurs négatives pour les années ou je n'avais aucune unité enregistrée ?

Cliquez pour agrandir...

Il y avait une parenthèse mal placée à la fin des formules dans les fichiers bis et ter, dans la première version envoyée avec le post #15 à 14 h 05.
Par ex, dans le bis, dans B43, il faut =SI(B32="";0;;MIN.........*$C$29-SOMME($A43:A43)) au lieu de =SI(B32="";0;MIN.........*$C$29)-SOMME($A43:A43) (la parenthèse rouge droite doit être à la fin de la formule, et pas avant le - SOMME). Avec la première version, le SOMME($A43:A43) est soustrait même pour les cellules vides (puisqu'il n'est pas inclus dans le SI(....)). Ce qui donne dans C49 SI(C38="";0;MIN....)-SOMME($A49:B49) = 0-SOMME($A49:B49) qui est négatif.
Mais j'avais corrigé les pièces jointes dès 16 h 55 . Si tu ne l'as pas déjà fait, retélécharge la dernière version. Si le problème persiste, explique le moi.

@ plus

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour et une nouvelle fois un grand merci, suis en deplacement, n'avais pas eu la nouvelle version. Vais regarder les nouveaux fichiers et faire de mon mieux pour comprendre tout ton cheminement (pas forcement evident au premier regard pour moi...), te dirai !

Merci encore,

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Pour t'aider un peu à comprendre la formule...

Imaginons que la somme des entrées sur les années précédentes et l'année en cours soit de 120 000, donc qu'elle appartient à la tranche 3 allant de 100 000 (= max tranche 2) à 200 000 (= max tranche 3).

Uniquement pour cette tranche 3, le calcul est (120 000 - 100 000) * 10, ce qu'on peut écrire sous la forme (sommeentrées - maxtranche2) * coef3.

Si la somme des entrées sur... avait été supérieure à 200 000, le calcul pour cette tranche 3 aurait été (200 000 - 100 000) *10 ce qu'on peut écrire sous la forme (maxtranche3 - maxtranche2) * coef3.

Pour prendre en compte les deux possibilités précédentes pour la tranche 3 avec une seule formule, on peut faire avec (MIN(sommeentrées;maxtranche3) - maxtranche2) * coef3.

Si la somme des entrées n'avait été que de 90 000, donc avait appartenu à la tranche 2, cette formule pour la tranche 3 aurait donné un nombre négatif : (MIN(sommeentrées;maxtranche3) - maxtranche2) * coef3 = (sommeentrées - maxtranche2) * coef3 = (90 000 - 100 000) * 10 < 0. Or, il ne fallait pas faire de calcul dans cette tranche 3 dans ce cas (puisque 90 000 appartient à la tranche 2), ou trouver 0 pour cette tranche 3.

Pour ne pas prendre en compte cette valeur négative, on transforme la formule précédente pour la tranche 3 sous la forme (MAX(0;MIN(sommeentrées;maxtranche3) - maxtranche2)) * coef3.

En fait, il faut faire cela pour chaque tranche, ce qui donne
(MAX(0;MIN(sommeentrées;maxtranche1) - 0)) * coef1
+(MAX(0;MIN(sommeentrées;maxtranche2) - maxtranche1)) * coef2
+(MAX(0;MIN(sommeentrées;maxtranche3) - maxtranche2)) * coef3
+(MAX(0;MIN(sommeentrées;maxtranche4) - maxtranche3)) * coef4.

Après, il ne reste plus qu'à rajouter une condition SI(... pour ne pas faire de calcul du tout si il ni a pas eu d'entrées pendant l'année en cours, et à soustraire les versements déjà effectués les années précédentes, avec - SOMME(....

J'ai mis les mêmes noms dans la pièce jointe. C'est le même principe que dans le fichier ter précédent, mais avec des noms. Cf. les noms dans le gestionnaire de noms.

@ plus

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour Cisco et un grand merci, non seulement pour la formule mais aussi pour tes explications detaillees et le temps que tu y as passe ! Je vais regarder ca a tete reposee et avec la plus grande attention !

Bonne journee,

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonsoir

Pour le fun

La méthode utilisée précédemment est peu pratique si on veut traiter un cas avec beaucoup de tranches, car il faudra mettre beaucoup de (MAX(0;MIN(sommeentrées;maxtrancheX) - maxtranche(X-1))) * coefX.

En pièce jointe, une formule plus polyvalente, traitant plus facilement un nombre de tranches important. Pour traiter un exemple comportant un autre nombre de tranches, il suffit de modifier la définition du nom tranches dans le gestionnaire de noms. On n'a pas à modifier les autres noms ni la formule dans le tableau du bas.

La formule dans B20 et les cellules voisines sont matricielles, il faut donc les valider avec Ctrl+maj+entrer.

@ plus

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour et un grand merci a toi !

Cela marche aussi avec excel pour mac ? Je vais essayer de ce pas !

Merci...

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonsoir

Avec un mac, je n'en sais rien, n'ayant pas pianoté sur ce type d'ordinateur depuis une quinzaine d'années !

@ plus

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Merci a toi ! Je te dirai ce qu'il en est ce week-end !

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

bonjour Cisco,

Merci beaucoup pour toute ton aide. Sur la formule matricienne, j'echoue lamentablement lorsque j'essaie de la "deplacer", peux tu stp m'eclairer sur la fonction OFFSET, avec laquelle je ne suis pas familiere ? Merci !!!

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonjour

Syntaxe de la fonction OFFSET
DECALER(celluledépart; décalage de x lignes;décalage de y colonnes;z lignes;w colonnes).

Ex : DECALER ($A$3;1;0;0;0)= DECALER($A$3;1;) donne A$4 (le ; est indispensable)
DECALER($A$3;1;1;0;0)= DECALER($A$3;1;1)=$B$4
DECALER($A$3;1;0;5;0) donne $A$4:$A$8
DECALER($A$3;10;1;5;3) donne $B$13:$D$17

Dans mon dernier fichier, tous les noms sont définis par rapport au nom tranches. Ce qui signifie que si tu utilises la même disposition que le tableau B2:C6, mais que tu places cela ailleurs, et/ou que tu as plus de tranches, tu n'as que la plage correspondant à ce nom à modifier.

@ plus

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

bonjour et merci beaucoup a toi ! En annexe ce que j'essaie de faire, avec des colonnes les unes a cote des autres, je vais m'atteler a changer les dispositions des noms. De maniere a pouvoir rajouter beaucoup de valeurs en dessous, par exemple.

As tu des conseils a me donner ? Je dois juste aller dans "inserer" puis "nom" et changer l'"adresse" ?

Je reviendrai sans doute vers toi...

Bonne soiree,

Agnes

CISCO à dit:

Bonjour

Syntaxe de la fonction OFFSET
DECALER(celluledépart; décalage de x lignes;décalage de y colonnes;z lignes;w colonnes).

Ex : DECALER ($A$3;1;0;0;0)= DECALER($A$3;1;) donne A$4 (le ; est indispensable)
DECALER($A$3;1;1;0;0)= DECALER($A$3;1;1)=$B$4
DECALER($A$3;1;0;5;0) donne $A$4:$A$8
DECALER($A$3;10;1;5;3) donne $B$13:$D$17

Dans mon dernier fichier, tous les noms sont définis par rapport au nom tranches. Ce qui signifie que si tu utilises la même disposition que le tableau B2:C6, mais que tu places cela ailleurs, et/ou que tu as plus de tranches, tu n'as que la plage correspondant à ce nom à modifier.

@ plus

Cliquez pour agrandir...

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Rebonjour Cisco,

Autant j'arrive a renommer les champs pour coef1 et maxtranche1, etc, autant pour sommeentrees c'est moins evident. Dois je me referer a la cellule pour chaque ligne ou bien a toute la colonne ? Idem pour sommesversementsprecedents ?

Merci beaucoup !

Agnes

Re : Systeme par tranches, mais avec prise en compte de l'annee precedente

Bonsoir

Pourquoi est-ce que tu as trois feuilles ? J'y perd un peu mon latin...

@ plus