Calcul de commission de marge
bonjour à tous et à toutes,
habituellement j'arrive à trouver une solution pour mes tableaux, mais là je cale.
je vais essayé d'être le plus explicite.
sur une feuille excel, j'ai 2 colonnes qui correspondent respectivement à prix d'achat HT et l'autre, prix de vente HT.
la division de l'une avec l'autre me donne dans une case la marge dégagée.
Plus bas sur la même feuille j'ai fais une sorte d' abaque sur lequel apparaît 3 colonnes.
1° colonne : les %
2° colonne : le coefficient de marge ex : 40% = coefficient x1.667
pour la 3° colonne, il y a un calcul particulier ;
je touche une commission à partir de 35% de marge et pour chaque point supérieur, j'ai coefficient de 0.15%.
Donc si je suis à 44% de marge, la formule que j'applique est (( la somme de prix de vente) x ( 9 "pts" x 0.15%)) = ma commission
Le problème que j'ai, c'est qu'il faut que j' ajuste systématiquement (ou quand je le consulte) ma commission mensuelle.
Je cherche donc une sorte de formule comparative, c'est à dire si la marge (achat/vente) correspond dans mon tableau à une valeur proche, automatiquement cela me détermine la valeur de marge
exemple : somme achat 11404.39€, somme vente 20824.98€ ==> marge = 1.826
colonne 1° 2° 3°
42% - 1.724 - 218.66
43% - 1.754 - 249.90
44% - 1.786 - 281.14
45% - 1.818 - 312.37
46% - 1.852 - 343.61
47% - 1.887 - 374.85
48% - 1.923 - 406.09
Donc si je me base à l' abaque ; 1.826 correspond à une marge entre 45 et 46%, ce qui me donne une commission de 312.37€.
cette somme, je la fait apparaître dans un case qui additionnée à une autre commission, me donne ma commission mensuelle.
Ce que je recherche, c'est une formule qui me remplit automatiquement cette case en fonction de ma marge.
J'espère être suffisamment explicite même si j'ai quelques doutes sur l'éventuelle compréhension de mon problème.
Merci par avance pour l'aide apportée.
cordialement
Bonjour, une recherchev() peut le faire en précisant 1 dans la valeur proche et en triant votre abaque par ordre croissant.
bonjour et merci pour votre réponse.
malheureusement j'avais pensé à cette formule, mais il y a un paramètre que je n'arrive pas à identifier.
la formule est :
RECHERCHEV(valeur_cherchée, table_matrice, no_index_col, [valeur_proche])
valeur cherchée = la marge
table matrice = mon abaque
no index col = ? (j'ai mis 1 pour la colonne 1, mais je voudrais voir apparaître la valeur de la même ligne mais de la 3°colonne, voir mon exemple)
valeur proche = j'ai mis comme vous m'avez dit : 1
dans l'aide de excel, j'ai jeté un œil sur la formule et il est dit :
Si l’argument no_index_col est :
supérieur au nombre de colonnes dans table_matrice, RECHERCHEV renvoie la valeur d’erreur #REF!.
mais moi il apparaît : #N/A
Salut,
Je pense que si tu nous fournissais un fichier ce serait plus facile pour comprendre et réaliser ce que tu souhaites.
Sinon, de ce que j'ai compris, me concernant j'utiliserai quelque chose du style :
=RECHERCHEV(marge;colonnes 2 & 3;2;1)Normalement, tu devrais t'en sortir
"no index col " c'est justement le numéro de la colonne que l'on souhaite afficher à partir de la première colonne de la table de donnée
Dans votre exemple pour appliquer une recherchev il faudrait l'écrire comme ceci
=recherchev(la marge;la colonne marge et commission de l'abaque;2;1)La marge = votre marge recherchée
la colonne marge et commission de l'abaque : les deux colonnes de votre abaque où il y a marge et commission
2 : ce que vous souhaiter afficher, ici on a choisi 2 car la commission c'est la 2ième colonne dans la plage qu'on a renseigné juste avant
et
1 : signifie recherche valeur au plus proche.
Pour les #N/A : cela vient très certainement du fait que l'abaque est en format texte au lieu de numérique. Essayez de remplacer les "." par des "," et pour être sur que tout fonctionne correctement, faite un tri de l'abaque par ordre croissant de la colonne marge.
merci pour les renseignements
je joint le fichier à traiter, qui sera plus facile à comprendre que mes laborieuses explications
formule intégrée au fichier
chapeau Féderic38,
je ne sais pas pourquoi la formule ne voulait pas fonctionner avec moi.
mais tu m'enlèves une épine du pied.
merci encore
cordialement
De rien
a tout hasard, si je voudrais que la formule me fasse apparaître la valeur approchée la plus juste, que dois-je modifier.
exemple : marge 44% = coef 1.786, marge 45% = coef 1.818 et que ma marge réelle est de 1.802.
actuellement elle reste rattachée au point inférieur, mais si je voudrais, si c'est possible qu'elle soit plus juste, inférieure ou supérieure.
merci d'avance
je ne sais pas car je ne connais pas assez excel pour ça. Je sais qu'il y a une autre possibilité de formule qui permet de sélectionner la valeur la plus proche supérieur mais si vous voulez un paramétrage ultra précis alors il faudra peut-être combiner plusieurs fonction si pour obtenir la perfection
Salut,
Pour répondr à ta question :
girondins a écrit :a tout hasard, si je voudrais que la formule me fasse apparaître la valeur approchée la plus juste, que dois-je modifier.
exemple : marge 44% = coef 1.786, marge 45% = coef 1.818 et que ma marge réelle est de 1.802.
actuellement elle reste rattachée au point inférieur, mais si je voudrais, si c'est possible qu'elle soit plus juste, inférieure ou supérieure.
merci d'avance
Normalement, si tu utilises une RECHERCHEV et que tu mets 1 en Valeur proche, Excel va chercher la valeur la plus proche de ce que tu lui demandes (qu'elle soit supérieure ou inférieure). Donc il ne devrait même pas y avoir besoin de compliquer la formule.
Bonne soirée
Bonsoir,
Sharney a écrit :Normalement, si tu utilises une RECHERCHEV et que tu mets 1 en Valeur proche, Excel va chercher la valeur la plus proche de ce que tu lui demandes (qu'elle soit supérieure ou inférieure). Donc il ne devrait même pas y avoir besoin de compliquer la formule.
Non. Excel renvoie la valeur inférieure.
Ce que tu veux n'est pas très clair :
Dans un premier temps tu demandes la valeur inférieure,
puis
Sharney a écrit :actuellement elle reste rattachée au point inférieur, mais si je voudrais, si c'est possible qu'elle soit plus juste, inférieure ou supérieure.
Dois-je en conclure que tu veux la valeur la plus proche (qu'elle soit inférieure ou supérieure) ?
Dans l'exemple que tu donnes (1.802 avec 1.786 en inf. et 1.818 en sup.), c'est pile poil au milieu...
A mon avis ce que tu n'exprime pas clairement que que tu souhaites réellement est une extrapolation de la commission entre ces 2 valeurs.
Dis moi si je me trompe.
Si c'est bien le cas, on peut se passer de ton tableau avec cette formule :
=F93*0,0015*(65-MEDIANE(100/E95;1;65))bonjour OOoiste,
en fait, la formule de Fréderic38 me convient tout à fait.
je souhaitais juste savoir si il était possible de l'affiner pour qu'elle se rattache automatiquement à la valeur inférieure ou supérieure.
j'ai pris au hasard la valeur de 1.802, pas de bol pour moi, c'est juste la valeur intermédiaire !
je vais tester ta formule
merci encore, beau boulot tout le monde.
Il faudrait éprouver cette formule
=RECHERCHEV(SI(E95-INDEX(B95:B159;EQUIV(E95;B95:B159;1))<INDEX(B95:B159;EQUIV(E95;B95:B159;1)+1)-95;INDEX(B95:B159;EQUIV(E95;B95:B159;1)+1);INDEX(B95:B159;EQUIV(E95;B95:B159;1)));B95:D159;3)Si je ne me suis pas trompé, elle calcul d'abord les delta entre une marge inferieur et inferieur +1 avec la votre et affiche celle qui est la plus juste
Si tu ne veux pas extrapoler mais renvoyer la valeur la plus proche qu'elle soit inférieure ou supérieure, il suffit de rajouter un ARRONDI à la formule que j'ai donné précédemment :
=F93*0,0015*ARRONDI(65-MEDIANE(100/E95;1;65))
A+
merci encore pour le boulot.
bonne continuation