Calcul de com avec plusieurs conditions
Bonjour à tous
après avoir en vain essayé de trouver une solution à mon problème, je me tourne vers vous en espérant que l'un d'entre vous pourra m'aider
Je suis encore novice en excel dès que l'on rentre dans le compliqué.
actuellement, je parviens à effectuer les calculs, mais c'est long et je dois faire plusieurs saisies sources d'erreurs
Je voudrais simplifier en partant du fichier que je reçois de l'entreprise.Mais rien à faire, je ne trouve pas les bonnes formules.
Pour la compréhension, je joins un fichier réduit.
En fait, en littéraire, voici ce que je voudrais:
si G = 40 et si 0<CA EN GRAS<<1750 que vaut alors(en %) H ?
et si 1751< CA EN GRAS<2250 que vaut alors H ?
etc......
si G =60 et si 0< CA EN GRAS< 2625 que vaut alors H ?
et si 2626< CA EN GRAS< 3375 que vaut alors H ?
etc.......
si G = 80 et si 0<CA EN GRAS<3500 que vaut alors H ?
et si 3501<CA EN GRAS<4500 que vaut alors H ?
etc........
dans le fichier joint feuill1 on a la base que j'utilise actuellement
J'ai mis le CA ( somme du CA ) en gras pour différencier et éviter de devoir créer une nouvelle colonne car je voudrais juste copier fidèlement ce que je reçois.
je peux si besoin transmettre ce que je fais actuellemnt
Bonjour Loubry,
Si tu as un grand nombre de cas possibles il y a 2 solutions qui me viennent.
La première est d'utiliser "If", puis "elseif" à chaque autres conditions.
Celle qui parait la plus adéquate dans ton cas est sans doute "Select Case" dans laquelle tu énumère tes cas possibles.
merci framewarz de ta réponse, mais je n'ai pas bien saisi
je croyais que c'était relativement simple à partir de la base_com que j'avais montée.
suivant le nombre saisi en G (40,60,80) il n'y a plus que que 6 conditionsj
je te joint ce que je fais actuellement ça va peut etre te permettre de mieux voir
je ne connais pas le VBA et reste sur le excel classique.
si c'est trop compliqué , je reste comme je fais actuellement
Merci quand même de me dire ce que tu en penses
C'est quelque chose qui est très simple à faire en VBA. Le problème c'est que si tu as 6 conditions, sans passer par VBA le code va être très lourd et à vrai dire je ne sais pas comment tu pourrais faire.
Le problème littéraire que tu énonces est en fait quasiment le même que le code qu'il te faut.
bonjour,
une proposition, formule à mettre en H40 et autres cellules qui vont bien en colonne H
=RECHERCHEV(E40;DECALER(base_com!$A$18;0;((G40-40)/20)*4;7;4);3;VRAI)Merci h2s04 de t'interesser à mon affaire
J'ai essayé de travailler dessus, mais j'avoue que pour moi c'est difficile.j'ai éssayé de comprendre la formule RECHERCHEV mais c'est ardu.
et franchement, je ne suis spas certain de pouvoir y arriver.
pour te permettre de te faire ube idée plus précise, je joins un extrait du fichier que je reçois et à partie duquel je fais les calculs.Comme je le disais, j'arrive avec une feuille de calcul que j'ai bidouillée ( et en séparant les 40 ,60 et 80) mais c'est trop llong et je fais des erreurs de saisie
Donc sur le nouveau fichier que je transmets, on voit qu'en colonne G , 40, 60 et 80 s'y trouvent de façon aléatoire et je ne peux pas les modifier.(c'est sur la somme en gras colonne E qu'il faut calculer le taux)
Donc lorsqu'en colonne G on a 40 , suivant le montant correspondant (en gras)colonne E et qui est repris en base_com on détermine le taux en colonne H
IL en est de même pour 60 et 80
C'est cette fameuse formule que je voudrais si possible comprendre .Toutes les heures passées sont jusqu'à maintenant sans résultat.
A noter que le fichier reçu comporte plus de 150 lignes avec un mélange de 40,60 et 80
Le fichier transmis reprend les 2 feuilles déjà envoyées et j'y rajoute un extrait du dernier reçu
Merci de me dire si tu peux faire quelquechose
Bonjour,
pas sûr d'avoir compris ce que tu voulais
j'ai remis la formule légèrement adaptée dans ton nouveau classeur, j'ai également ajouté le calcul de la prime, basé sur une formule semblable.
la formule
=RECHERCHEV(E40;DECALER(base_com!$A$18;0;((G40-40)/20)*4;7;4);3;VRAI)recherche le taux correspondant au montant (E40) dans le tableau correspondant au nombre de contrats G40 (40 60 ou 80) le tableau est déterminé avec la fonction décaler.
on part de base_com!$A$18,
de cette base :
le tableau (pour 40) commence à la ligne 0, colonne 0 , et se termine en ligne 7,colonne 4 soit DECALER($A$18,0,0,7,4)
le tableau (pour 60) commence à la ligne 0, colonne 4 , et se termine en ligne 7,colonne 4 soit DECALER($A$18,0,4,7,4)
le tableau (pour 80) commence à la ligne 0, colonne 8 , et se termine en ligne 7,colonne 4 soit DECALER($A$18,0,8,7,4)
la formule ((G40-40)/20)*4 calcule le numéro de colonne en fonction du nombre de contrats( 40->0,60->4,80->8)
la sélection du tableau se fait donc ainsi
DECALER(base_com!$A$18;0;((G40-40)/20)*4;7;4)Bonsoir
Je n'ai pas laché le morceau;J'ai a peu près compris le fonction DECALER que je ne connaissais pas du tout
Mais j'ai modifié la base_com car il y a 2 strats de conseillers et j'ai pris la base où les primes sont différentes.l'autre strat sera plus facile à faire.
par contre, je n'ai pas bien saisi la méthode pour calculer le taux
Pour moi, en partant de la base_com$A$7( nouvelle base_com), le tableau pour 40 commence à la ligne 0 et se termine en ligne 9 colonne 4
le tableau pour 60 commence à la ligne 0 et se termine en ligne 9 colonne 8
Le tableau pour 80 commence à la ligne 0 et se termine en ligne 9 colonne 12
C'est cela que je ne comprends pas DECALER($A$7
Je vais continuer à travailler la dessus et si ça n'est pas trop demander, je te ransmettrai demain ou après demain le résultat de mes avancées.
Ne serait-il pas possible de passer par message privé si bien sur ça te convient
Bonne soirée
Bonsoir,
mes excuses, j'aurais dû formuler ainsi
de cette base :
le tableau (pour 40) commence à la ligne 0, colonne 0 , sur 7 lignes et 4 colonnes, soit DECALER($A$18,0,0,7,4)
le tableau (pour 60) commence à la ligne 0, colonne 4 , sur 7 lignes et 4 colonnes, soit DECALER($A$18,0,4,7,4)
le tableau (pour 80) commence à la ligne 0, colonne 8 , sur 7 lignes et 4 colonnes, soit DECALER($A$18,0,8,7,4)
Bonsoir
J'ai complètement modifié mon fichier
j'ai regroupe les fichier de chacun des conseillers sur un fichier global ( qui est très lourd)
j'avance tout doucement et je commence à voir le bout du tunnel , je pense que je vais y arriver
Mais là,je suis bloqué;
J'explique:
la com est calculée sur le CA global généré depuis l('ouverture du lot
Ainsi, le lot JEAN S03 2018 ouvert le 25/01/2018 a géné ré une com de 140.84
en février, le CA global passe à 6660 E avec une com globale de 911.96 dont il fau tretrancher 180.84 déjà réglé en janvier
et ainsi de suite pour lea autres lots.
Une difficulté supplémentaire il y a des lots portant le m^me N° avec juste l'année qui diffère exemple ERIC S45 2017 et ERIC S45 2018
Là je ne vois pas du tout vers où me diriger.Peut être as tu une idée
Merci si tu peux faire quelquechose
Bonsoir
je regrette de devoir une nouvelle fois devoir te fairre appel, mais je suis paumé
je ne comprends pas pourquoi la formulel ne fonctionne pas sur mon fichier
je te joins une partie de mon fichier qui est lourd (au moins 800 lignes) et le fichier que tu as rectifié et qui lui fonctionne.
Je fais certainement une erreur quelque part
Au cas où tu pourrais......
merci
Bonjour,
Je ne comprends pas pourquoi elle ne fonctionne pas sur mon fichier alors que sur celui que tu m'as transmis, ca marche
je pense que tu as tout simplement mal recopié la formule. je t'ai remis les formules dans ton fichier et ajouté une condition pour qu'elle ne s'applique que pour les lignes ayant un taux.
pourquoi la formule de la cellule J24 indique($F$7:$F$22;DROITEpuis plus loin ($F23;DROITE($F23;4)?
voici la formule complète avec la condition.
=SI(G24="";"";SOMMEPROD((SUBSTITUE($F$7:$F22;DROITE($F$7:$F22;4);"")=SUBSTITUE($F23;DROITE($F23;4);""))*($M$8:$M23)))la formule recherche dans les lignes précédentes en commençant à la ligne 7 (jusqu'à la ligne 22) donc, toutes les lignes qui contiennent la même chaine de caractères de la ligne (moins l'année, 4 derniers caractères) précédant la ligne en cours et fait la somme des montants trouvés en colonne M sur la ligne suivante.
S'il y a une formule que tu ne comprends pas tu peux essayer de la comprendre en l'exécutant en mode pas à pas. via le menu formule-> évaluer formule.
Bonsoir
Décidemment, je ne parviens toujours pas à régler ce problème
je crois avoir correctement recopié la formule que tu m'as envoyée et ça ne marche toujours pas.
pour être certain , j'ai carrémément recopié une partie de mon fichier comme il est .je crois qu'en cellule J44 si ca marchait, on devrait avoir 140.84 au lieu de# valeur.
J'ai éssayé de vérifier la formule pas a pas mais sans succès
Veux tu bien vérifier si j'ai bien repris la formule?et me dire ce qu'i en est?
Encore une fois excuses pour le dérangement
merci
Bonjour,
je ne vais pas corriger chaque fois toutes tes formules.
revérifie encore les formules en colonne J,L et M, la majorité de tes formules en colonne M sont incorrectes.
Bonjour
Bien reçu tes observations
J'ai depuis travaillé sur les fichiers et compris ce qui n'allait pas.
j'a testé sur mon véritable fichier et ça marche parfaitement.
par contre j'avais demandé de ne pas tenir compte de l'année , mais je m'aperçois qu'i faut garder l'année dans la colonne lots/conseillers
Si tu peux ( pour la dernière fois) reprogrammer la formule dans le fichier joint en conservant l'année ........
Merci
Bonsoir
je comprends parfaitement que tu commences à fatiguer avec un client comme moi absolument peu doué.
j'essaie juste de comprendre mais c'est très compliqué.
Dommage, car nous étions presque au bout.je regrette juste que tu n'aies pas voulu rectifier la formule pour conserver les 4 derniers éléments dans la colonne F et c'en était terminé.
Je vois que pour toi, c'est évidant, mais pour moi, c'est de l'hebreu
Je vais essayer de voir quelqu'un qui puisse me rectifier et si possible m'expliquer la fonction
et je vais continuer à me former
merci quand même
Cordialement
Bonsoir,
désolé de répondre tardivement. je reviens de vacances. voici le fichier adapté pour tenir compte de l'année.