Calcul de commission par tranches
Résolu
Tominou64
Messages postés
19
Statut
Membre
-
Tominou64 Messages postés 19 Statut Membre -
Tominou64 Messages postés 19 Statut Membre -
Bonjour,
J'ai déjà lu plusieurs articles qui concernent cette question, mais je n'arrive pas à l'appliquer à mon cas précis.
Si quelqu'un pouvait m'aider ce serait cool.
Je cherche donc à calculer des commissions. Le taux de commission est fonction du C.A. annuel atteint. La difficulté est quand la commission est répartie sur 2 tranches de C.A.
J'explique : Quand j'ai atteint 80.000€ de CA, je change de taux de commission.
Prenons un cas, précis :
J'ai atteint à 75.000€ de C.A.
Je génère 8.000€ de C.A.
J'atteins donc 83.000€ de C.A.
Ma commission est donc calculée ainsi : 5.000€ x 22% + 3.000€ x 26%
Et c'est à ce moment là que je ne sais plus comment écrire ma formule.
De mémoire il faut mixer les fonctions recherchev et si.
Merci à celles et ceux qui pourront m'aider.
Tominou64
J'ai déjà lu plusieurs articles qui concernent cette question, mais je n'arrive pas à l'appliquer à mon cas précis.
Si quelqu'un pouvait m'aider ce serait cool.
Je cherche donc à calculer des commissions. Le taux de commission est fonction du C.A. annuel atteint. La difficulté est quand la commission est répartie sur 2 tranches de C.A.
J'explique : Quand j'ai atteint 80.000€ de CA, je change de taux de commission.
Prenons un cas, précis :
J'ai atteint à 75.000€ de C.A.
Je génère 8.000€ de C.A.
J'atteins donc 83.000€ de C.A.
Ma commission est donc calculée ainsi : 5.000€ x 22% + 3.000€ x 26%
Et c'est à ce moment là que je ne sais plus comment écrire ma formule.
De mémoire il faut mixer les fonctions recherchev et si.
Merci à celles et ceux qui pourront m'aider.
Tominou64
6 réponses
-
yg_be Messages postés 23437 Date d'inscription Statut Contributeur Dernière intervention Ambassadeur 1 588
Regarde dans https://www.cjoint.com/c/FKnrrQewCv1 onglet "yg".
Je ne sais pas si c'est clair.-
-
et ceci : https://www.cjoint.com/c/FKntRgXiRv1 ?
-
-
J'ai recopier ta formule pour l'adapter à mon cas précis, et je pense avoir fait une erreur quelque part.
Le calcul de la colonne commission est illogique.
Je me suis dit qu'en te donnant mon tableau, ce serait peut-être plus rapide.
Si tu avais la gentillesse de m'aider.
https://www.dropbox.com/s/zl2v68laqvyix6k/Exemple%20pr%C3%A9cis.xls?dl=0 -
Bonjour Tominou64,
Tu as écris : « Le calcul de la colonne commission est illogique. » ; or c'est normal
si yg_be a strictement suivi les remarques incluses dans ton fichier joint, car ces
remarques ne sont pas entièrement conformes au principe d'application d'un
barème par tranches ! La 2ème solution que je t'ai proposée applique bien ce
principe, et je pense que les résultats que j'ai obtenus sont corrects.
Je te propose de lire mon message #26, qui te sera sûrement très utile,
et je compte sur toi pour me donner ton avis.
Cordialement. 😊
-
-
yg_be Messages postés 23437 Date d'inscription Statut Contributeur Dernière intervention Ambassadeur 1 588
Envoie-nous ton fichier, nous pourrons le compléter.-
Et voilà : https://www.cjoint.com/c/FKnoYVxGwut
Merci -
-
-
-
En fait c'est déjà ce que j'ai fait.
Mais si une vente est annulée alors je dois supprimer la ligne et tout mon tableau est faux.
C'est pour ça que je cherche la formule qui le calcul tout seul.
Je sais le faire à un époque.
De mémoire, il faut faire une comparaison de la ligne "total commission", au tableau des tranches de CA et du taux correspondant avec recherchev, puis faire le calcul avec si.
-
-
Bonjour
Un exemple
http://www.cjoint.com/c/FKnn4cCTzjb
Cordialement -
Bonjour Tominou64,
Je te propose ce fichier Excel 2007.
Dis-moi ce que tu en penses, et si ça te convient.
N'hésites pas à demander plus d'infos si besoin.
Si ton problème est réglé, merci d'aller en haut de page
pour cliquer sur « Marquer comme résolu ».
Cordialement. 😊 -
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question -
Re Bonjour
Le problème de ton tableau c'est que le trimestre suivant enD2
tu vas afficher le résultat de D13 et ainsi de suite à chaque trimestre.
Est-ce bien cela? -
Bonsoir Tominou64,
La solution que je t'ai donnée précédemment était avant
de voir ton fichier joint, de ton message #5 ; je n'avais
donc pas toutes les données utiles.
J'ai refais entièrement l'exercice depuis ce fichier joint,
et je te propose donc cette seconde solution.
----------------------------------------
1) Faute d'orthographe corrigée en H1 et B2 : ATTEINT au lieu de
ATTEIND ; eh non, désolé de te décevoir, mais ce n'est pas
interchangeable comme les Dupond et Dupont ! 😏
----------------------------------------
2) J'ai amélioré la formule de G3 (recopiée jusque G12) :
=SI(E3>0;E3;D3)*F3
----------------------------------------
3) Le calcul de la commission étant plus complexe que prévu,
j'ai préféré laisser tomber l'idée de le faire via une formule,
et je l'ai fait par une fonction VBA qui est dans Module1 ;
fais < Alt >< F11 > pour la voir, puis < Alt >< F11 > pour
revenir sur ta feuille Excel. Tous les commentaires en vert
de la macro doivent t'aider à comprendre le mécanisme.
N'hésites pas à me demander plus d'infos si besoin.
Attention : même s'il est très légèrement différent de tes
indications, tu verras que le mécanisme utilisé respecte
bien celui à faire pour un barème par tranches !
----------------------------------------
Dis-moi ce que tu en penses, et si ça te convient.
N'hésites pas à demander une adaptation si nécessaire.
Si ton problème est réglé, merci d'aller en haut de page
pour cliquer sur « Marquer comme résolu ».
Cordialement. 😊
-
J'utilise Excel sur Mac, peut-être cela change-t-il quelque chose ?
J'imagine que pour utiliser du VBA tu as du beaucoup bosser, merci beaucoup.
Cependant, dans la colonne "commission" est inscrit =commission(cellule) et c'est un chiffre en dizaines de milliards qui apparait (et je ne gagne pas tant ;)
Peut-être dois-je faire une manipulation quelconque pour activer la macro ?
Merci- Je ne connais pas Excel sur Mac, seulement sur PC ; ce que tu indiques n'est
pas arrivé sur mon Excel 2007 ; places-toi sur la cellule en question, fais < F2 >
pour passer en mode modification de cellule, ne change rien et valide par
< Entrée >, puis dis-moi si tu as un résultat plus cohérent. Si oui : c'est réglé ;
sinon : c'est peut-être une histoire de format des pourcentages ? Tu peux
aussi faire < Ctrl >< F9 > pour forcer le recalcul de la feuille. À te lire.
-
D'abord, je corrige une erreur que j'ai écris : pour forcer le recalcul de la feuille
avec mon Excel 2007, ce n'est pas < Ctrl >< F9 > mais : < Ctrl >< Alt >< F9 >
Dans la colonne I "Commission", j'ai ces résultats de I3 à I12 :
12 540,00 €
15 180,00 €
16 060,00 €
17 380,00 €
18 640,00 €
23 750,00 €
27 950,00 €
Soit un total de 131 500,00 €
Comme tu as écris : « c'est un chiffre en dizaines de milliards », je pense que
tu dois avoir ces mêmes nombres × 100. Pour les 4 taux de F17 à F20, si je
met un format Standard, j'ai : 0,22 ; 0,26 ; 0,32 ; 0,35. Je crois que si toi tu
mets un format Standard, tu auras sur ton Excel Mac : 22 ; 26 ; 32 ; 35.
Donc soit tu changes ces 4 nombres pour mettre 0,22 ; 0,26 ; 0,32 ; 0,35 ;
soit il faut laisser tel que et changer ainsi la macro (supprime tout le code
précédent et fais un copier coller de ce nouveau code VBA) :
Option Explicit Function Commission(CA As Currency) As Currency Dim comX As Currency, com1 As Currency, com2 As Currency, com3 As Currency If CA < [E17] Then ' CA < 80 000 comX = CA * [F17] / 100 ' CA × 22 % Else ' CA >= 80 000 com1 = [E17] * [F17] / 100 ' 80 000 × 22 % = 17 600 If CA < [E18] Then ' CA < 90 000 ' 17 600 + (84 000 - 80 000) × 26 % comX = com1 + (CA - [E17]) * [F18] / 100 Else ' CA >= 90 000 ' (90 000 - 80 000) × 26 % = 10 000 × 26 % = 2 600 com2 = ([E18] - [E17]) * [F18] / 100 If CA < [E19] Then ' CA < 100 000 ' 17 600 + 2 600 + (95 000 - 90 000) × 32 % comX = com1 + com2 + (CA - [E18]) * [F19] / 100 Else ' CA >= 100 000 ' (100 000 - 90 000) × 32 % = 10 000 × 32 % = 3 200 com3 = ([E19] - [E18]) * [F19] / 100 ' 17 600 + 2 600 + 3 200 + (113 000 - 100 000) × 35 % ' 23 400 + (13 000 × 35%) = 23 400 + 4 550 = 27 950 comX = com1 + com2 + com3 + (CA - [E19]) * [F20] / 100 End If End If End If Commission = comX End Function
Tu verras que j'ai ajouté « / 100 » après chaque [F17] à [F20].
À te lire pour savoir si ça règle ton problème.
-
