Pourcentages variables de primes avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Primes selon résultats

Avec ce nouvel exercice Excel, nous poursuivons l'entraînement sur des notions dédiées aux utilisateurs avancés. Il consiste à calculer dynamiquement les primes allouées aux commerciaux. Ces primes dépendent des contraintes de référence inscrites dans un panneau de contrôle.



C'est le chiffre d'affaires réalisé par le commercial qui doit déterminer le pourcentage à exploiter pour le calcul du montant à reverser.

Source et présentation de la problématique
Le tableau modèle pour mener à bien cet exercice est proposé au téléchargement comme toujours.

• Télécharger le classeur primes-sur-resultats.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur est constitué d'une seule feuille. Elle est nommée Calcul_primes. Les commerciaux de l'entreprise sont recensés en colonne B, entre les lignes 5 et 14. Sur la période, leur chiffre d'affaires respectif est renseigné dans la colonne voisine C. En colonne D, il s'agit de produire une formule dynamique déterminant la prime allouée à chacun.

Cette prime dépend du résultat obtenu par chaque commercial. Un tableau de bord, défini entre les colonnes F et G, fixe les règles. Si le chiffre d'affaires réalisé est inférieur à 5000 Euros, la prime doit être calculée sur la base d'un taux de 2% à appliquer sur le C.A. (CA*2%). Entre 5000 et 10000 Euros, ce pourcentage s'élève à 8%. Et puis, ce taux s'envole à 11% dès lors que le chiffre réalisé sur la période, dépasse les 10000 Euros.

Bien entendu, les contraintes comme les taux doivent être considérés comme des variables. Le fait de changer les valeurs du panneau de contrôle, doit impliquer le recalcul de toutes les primes. Cela signifie que les formules doivent faire référence à ces cellules de critère. Ainsi, nous pourrons livrer un modèle dynamique exploitable pour d'autres périodes et d'autres sociétés.

Le suivi des nouveaux chiffres d'affaires conduira aux calculs automatiques des primes respectives, en tenant compte des contraintes imposées par le panneau de référence, propre à chaque entreprise.

Le calcul de la prime en lui-même est trivial. Il consiste à multiplier le chiffre d'affaires par le taux alloué.

Calculs selon conditions
Le calcul de la prime est donc soumis à des conditions. Et qui dit calcul conditionnel, dit fonction Si. La fonction Excel Si permet d'engager une action lorsqu'un critère est vérifié et une autre le cas échéant. Cette action peut bien entendu consister en un calcul. Sa syntaxe est la suivante :

=Si(Critère_à_vérifier; Action_alors ; Action_sinon)

Le critère consiste à comparer le chiffre d'affaires du commercial avec les contraintes du panneau de contrôle, ex : CA<=5000. En d'autres termes, la question posée est la suivante : Est-ce que le chiffre d'affaires est inférieur ou égal à 5000 Euros ?

Si la réponse est Oui, alors nous engageons le deuxième paramètre de la fonction Excel Si. Il consiste donc à calculer le montant de la prime sur la base des 2% correspondant : CA*2%. Dans le cas contraire, signifiant que le CA est supérieur à 5000 Euros, une autre action doit être engagée. Mais faute d'hypothèses suffisantes, nous ne savons pas si nous devons exploiter 8 ou 11% pour le calcul.

Une fonction Si isolée ne permet de poser qu'une seule condition. Selon sa validation, elle ne permet d'engager que deux actions (Vrai ou Faux). Le cas est plus complexe ici. D'autres critères doivent être analysés. Et même si depuis la version 2019 d'Excel, il existe une fonction pour simuler les Si imbriqués, nous choisissons d'entreprendre la syntaxe classique, celle compatible avec toutes les versions d'Excel. Elle consiste à imbriquer des fonctions Si manuellement, pour analyser plusieurs critères, envisageant tous les cas. La syntaxe serait alors la suivante :

=Si(Critère1_à_valider; Alors_action1 ; Sinon_Si(Critère2_à_vérifier ; Alors_action2 ; etc...))

Il est important de veiller à ce que chaque fonction Si possède trois arguments, malgré l'imbrication. L'ordre des conditions à satisfaire est important pour procéder par élimination. Nous devons commencer les comparaisons avec les valeurs les plus hautes. Le raisonnement devient donc le suivant : Si le CA est supérieur à 10000 Euros, le calcul de la prime se base sur 11%, sinon si le CA est supérieur à 5000 Euros, le calcul se base sur 8%, sinon il doit exploiter un taux à 2%.

L'opérateur Compris Entre ne s'exprime pas dans la syntaxe Excel. Dans l'enchaînement, c'est le Sinon si qui permet de comprendre que la première condition n'est pas satisfaite quand dans le même temps la seconde est vérifiée. Si le CA n'est pas supérieur à 10000 et qu'il est supérieur à 5000, il est compris entre les deux valeurs. Le dernier Sinon sous-entend qu'aucun critère n'est vérifié. Nous en déduisons donc le dernier cas, celui du taux à 2%. Nous proposons de passer à la pratique en construisant la fonction multicritère.

• Sélectionner la première prime à calculer, soit la cellule D5,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit Si(,

Vous notez l'apparition immédiate d'une info-bulle. Sa vocation est de guider le concepteur afin de renseigner au mieux, les trois paramètres requis : test_logique, valeur_si_vrai et valeur_si_faux. En d'autres termes, il s'agit de : Critère, Action_alors et Action_sinon. Chacun est séparé du suivant par un point-virgule (;) selon la syntaxe Excel.

• Cliquer sur le premier chiffre d'affaires à comparer pour intégrer sa référence C5,
• Saisir l'opérateur Supérieur suivi de l'opérateur égal, soit : >=,

L'enchaînement des deux opérateurs se traduit par : Supérieur ou égal.

• Désigner la contrainte la plus haute du panneau de contrôle pour intégrer sa référence F5,
• Enfoncer la touche F4 du clavier pour figer cette référence, ce qui donne : $F$5,

En effet, la logique de cette formule est destinée à être reproduite sur les lignes du dessous. Chaque chiffre d'affaires, naturellement non figé, doit être comparé à cette même cellule de référence. Elle ne doit donc pas suivre le déplacement.

A ce stade, le premier critère est le suivant : C5>=$F$5. La question consiste donc à savoir si le chiffre réalisé par le commercial a dépassé la valeur plafond. Si tel est le cas (Action_alors), nous devons calculer sa prime sur la base des 11% correspondant.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner de nouveau le chiffre d'affaires pour le calcul de la prime, soit la cellule C5,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Désigner alors le pourcentage correspondant au critère, soit la cellule G5,
• Enfoncer de nouveau la touche F4 du clavier pour figer cette dernière,

En effet, chaque chiffre d'affaires passé en revue ligne à ligne et correspondant à ce critère, devra être multiplié par ce taux situé en cellule unique, à figer donc. Dans le cas contraire, tout dépend du chiffre d'affaires par rapport aux deux contraintes restantes. Donc, nous devons imbriquer une nouvelle fonction Si pour analyser un nouveau cas.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit Si(,

Nous devons une fois encore désigner le chiffre d'affaires pour bâtir le critère. Il est possible que la formule déborde sur la cellule en question, empêchant sa sélection. Mais rien n'empêche alors de saisir directement ses références au clavier.

• Saisir les références du premier chiffre d'affaires, soit C5,
• Taper le symbole supérieur suivi du symbole égal, soit >=, pour la comparaison,
• Désigner la deuxième contrainte du panneau de contrôle pour intégrer sa référence F4,
• Puis, la figer comme précédemment avec la touche F4 du clavier,

Le second critère à ce stade est le suivant : C5>=$F$4. La question consiste donc à savoir si le chiffre d'affaires du commercial est supérieur à 5000 Euros. Si Excel atteint cette seconde condition, cela signifie que le premier critère n'a pas été vérifié. Nous en déduisons que le chiffre d'affaires est compris entre 5000 et 10000 Euros. Nous devons donc engager l'action en conséquence (Action_alors) de cette seconde fonction Si. Elle consiste à calculer le chiffre d'affaires sur la base du taux correspondant à 8%.

• Taper un point-virgule (;) pour passer dans la branche Alors de la seconde fonction Si,
• Saisir les références du premier chiffre d'affaires pour l'inclure dans le calcul, soit C5,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Désigner le taux correspondant pour intégrer la cellule G4 dans le calcul,
• Pour les mêmes raisons que précédemment, la figer avec la touche F4 du clavier,
• Taper un point-virgule (;) pour atteindre la branche Sinon de ce second Si,

Ce dernier Sinon sous-entend qu'aucun critère précédent n'a été vérifié. Si le CA n'est ni supérieur à 10000, ni supérieur à 5000, c'est qu'il est nécessairement inférieur à 5000. La dernière action se déduit donc naturellement sans poser de nouvelle condition. Il s'agit de calculer la prime sur la base du plus petit taux à 2%.

• Saisir les références du premier chiffre d'affaires, soit C5,
• Taper le symbole de l'étoile (*) du pavé numérique pour la multiplication,
• Désigner le dernier taux pour intégrer sa référence G3 dans la formule,
• Enfoncer la touche F4 pour la figer,
• Fermer deux parenthèses pour les deux fonctions Si imbriquées,
• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,

Le premier résultat tombe et il est cohérent. La prime allouée est légèrement inférieure à 10%. Le chiffre d'affaires est en effet inférieur à 10000 Euros. La syntaxe de la formule multicritère que nous avons bâtie est la suivante :

=SI(C5>=$F$5; C5*$G$5; SI(C5>=$F$4; C5*$G$4; C5*$G$3))

Elle est certes relativement complexe. Elle met en jeu l'imbrication des fonctions Si et dans le même temps des références absolues. Mais elle permet d'analyser un cas difficile et assure un parfait dynamisme. Et c'est grâce aux références absolues judicieusement employées que nous allons pouvoir répliquer la logique de ce calcul. Pour cela, nous devons exploiter la poignée du résultat. Comme vous le savez, elle est matérialisée par le petit carré noir situé en bas à droite de la cellule active.

• Pointer avec la souris sur la poignée du résultat,
• Lorsque le curseur se transforme en une petite croix noire, double cliquer,

Cette méthode est à la fois souple et efficace. Excel détermine automatiquement les bornes du tableau en hauteur. De fait, il reproduit la logique du calcul jusqu'à la dernière cellule concernée. Ainsi, nous n'avons pas besoin de tirer cette poignée pour accompagner le mouvement. Mais cette technique n'est valable qu'à la verticale.

Dès la première lecture, tous les calculs de primes apparaissent parfaitement cohérents. Par exemple, pour le chiffre d'affaires de 4800 Euros, la prime accordée est de 96 Euros, soit 2% du montant. Pour le chiffre d'affaires de 8900 Euros, la prime calculée s'évalue à 712 euros, soit un peu moins de 10%. Nous en concluons que le taux de 8% lui a bien été appliqué.

Si vous double cliquez sur le dernier résultat en cellule D14, vous visualisez la syntaxe adaptée de la formule. Les jeux de couleurs déclenchés par Excel aident à identifier les cellules impliquées dans le raisonnement. Nous voyons que les critères et calculs sont tous basés sur le chiffre d'affaires du commercial, qui a donc bien suivi le déplacement. Les contraintes et taux quant à eux, sont bien restés figés dans leur cellule, celles du panneau de contrôle. N'oubliez pas d'abandonner la saisie de la formule par la touche Echap du clavier. Ainsi vous ne risquez pas d'endommager sa syntaxe.

• En cellule F5 du panneau de contrôle, monter la contrainte à 15000 puis valider,

Seul un commercial obtenait jusqu'alors la prime de 11% sur son chiffre d'affaires. La politique de la société ayant changé, le plafond a été remonté. Et comme vous le remarquez, le commercial Houda en ligne 9, se voit désormais attribuer une prime de 8% seulement.

C'est tout l'enjeu de ces formules dynamiques avec Excel. Une fois le modèle bâti, le décideur n'a plus qu'à modifier les contraintes du tableau de bord, afin de produire des résultats adaptés aux décisions.

Calculs par correspondances
La fonction Si est donc précieuse et puissante. Elle permet de poser des raisonnements en analysant des critères pour trouver des solutions dynamiques. Néanmoins, lorsque le nombre de conditions augmente, sa syntaxe s'alourdit très vite. Sa conception, son interprétation et sa modification deviennent complexes.

C'est la raison pour laquelle, nous proposons une solution de substitution par correspondance. C'est une solution remarquable que nous avions mise en oeuvre dans la formation Excel pour attribuer des taux de remise variables en fonction de la quantité achetée. Elle consiste à exploiter la fonction RechercheV à la place de la fonction Si et de ses imbrications. Sa syntaxe est la suivante :

=RechercheV(Valeur_cherchée; Tableau_de_recherche ; Colonne_de_retour ; Correspondance)

Cette fonction permet de chercher une valeur (Le chiffre d'affaires du commercial) dans un tableau de recherche (Le panneau de contrôle). Lorsque ce montant est trouvé, elle retourne la donnée située dans la colonne de retour, la deuxième ici pour le pourcentage correspondant. Mais ce panneau de contrôle ne peut prévoir tous les chiffres d'affaires. Ces derniers ne peuvent donc être trouvés, d'où l'importance du dernier paramètre booléen (Correspondance). En le réglant à Vrai, nous indiquons à la fonction de trouver la valeur inférieure la plus proche et donc de restituer le pourcentage correspondant.

Pour la mettre en oeuvre,nous devons commencer par éliminer les précédents résultats.

• Sélectionner les précédents calculs, soit la plage de cellules D5:D14,
• Enfoncer la touche Suppr du clavier pour les effacer,
• Sélectionner la première prime à calculer, soit la cellule D5,
• Taper le symbole égal (=) pour initier la formule,
• Saisir le nom de la fonction suivi d'une parenthèse, soit RechercheV(,

Une fois la parenthèse ouverte, vous notez l'apparition de l'info-bulle indiquant les paramètres à renseigner, ceux que nous avons explicités.

• Désigner la première prime soit la cellule C5 pour indiquer la valeur à chercher,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner le panneau de contrôle, soit la plage de cellules F3:G5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $F$3:$G$5,

En effet, cette formule doit être répliquée sur les lignes du dessous. Ainsi, nous chercherons l'équivalence pour chaque chiffre d'affaires. Mais cette équivalence doit être trouvée dans le panneau de contrôle, dont les bornes ne changent pas.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,

Cette donnée doit être transmise en valeur numérique. Lorsque le chiffre d'affaires inférieur le plus proche est trouvé, la fonction Excel RechercheV doit renvoyer le pourcentage de prime correspondant. Celui-ci, par rapport à la sélection, est situé dans la deuxième colonne, soit à l'indice 2.

• Saisir le chiffre 2 et taper un point-virgule (;) pour passer dans l'argument du booléen,
• Saisir le texte Vrai pour demander une correspondance approximative,
• Fermer la parenthèse de la fonction RechercheV,

Le calcul n'est pas terminé. A ce stade, nous retournons seulement le pourcentage de prime accordé. Celui-ci doit être multiplié par le chiffre d'affaires.

• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Désigner de nouveau le chiffre d'affaires pour intégrer sa référence C5 dans la syntaxe,
• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur le tableau,

Tous les calculs de primes sont livrés. Ils sont parfaitement identiques aux précédents. La syntaxe de la formule est cependant plus simple, d'autant qu'elle ne s'alourdira pas si le nombre de contraintes augmente. Dans le cas précédent, à chaque nouveau critère, une nouvelle imbrication de fonction Si est nécessaire.

=RECHERCHEV(C5;$F$3:$G$5;2;VRAI)*C5

Une contrainte doit cependant être respectée pour mener à bien l'extraction par une Recherche verticale. L'élément à trouver doit nécessairement se situer dans la première colonne du tableau de recherche. C'est bien le cas ici. Les chiffres d'affaires correspondants sont placés dans la première rangée du panneau de contrôle.



Si vous double cliquez sur le dernier résultat en cellule D14, la syntaxe livrée prouve que la logique a parfaitement été adaptée. Le chiffre d'affaires correspondant est bien exploité pour la recherche et le calcul. Par contre, cette recherche est réalisée dans un tableau aux bornes inamovibles.

• Dans le panneau de contrôle en F5, remplacer la contrainte 15000 par 10000,

Après validation, comme précédemment, vous remarquez l'actualisation immédiate de la prime pour le commercial Houda. Il se voit désormais gratifié d'une prime de 11%. Nous prouvons et confirmons le dynamisme des calculs ainsi construits.

Identifier automatiquement les commerciaux
Pour parfaire ce modèle à livrer et à exploiter, nous proposons de faire ressortir dynamiquement les lignes du meilleur et du moins bon vendeur. Ces alertes visuelles sont régies par des règles de mise en forme conditionnelle. Les couleurs de fond appliquées aux cellules à repérer doivent réagir en fonction des valeurs qu'elles portent. En d'autres termes, si les données évoluent, les couleurs changent d'emplacement automatiquement. Ces réglages dynamiques sont précieux pour offrir une interprétation efficace des résultats dès la première lecture.

Excel offre des règles préconçues pour repérer dynamiquement les meilleures ou pires valeurs d'une plage désignée. Mais ici, nous souhaitons identifier la ligne complète du commercial. Nous devons donc créer une règle spécifique.

• Sélectionner toutes les données concernées, soit la plage de cellules B5:D14,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Dans la zone de saisie du dessous, taper le symbole égal (=) pour initier la syntaxe de la règle,
• Désigner le premier chiffre d'affaires pour intégrer sa référence C5 dans le critère,

Celle cellule apparaît totalement figée. Or une règle de mise en forme conditionnelle raisonne comme un calcul répliqué. Elle va passer en revue toutes les cellules dans l'ordre où elles sont désignées. Et pour chaque ligne, nous devons vérifier si le chiffre d'affaires en cours est le plus grand dans la colonne. Cette cellule doit donc pouvoir se déplacer en ligne mais pas en colonne.

• Enfoncer deux fois de suite la touche F4 du clavier pour conserver le dollar seulement devant l'indice de colonne, soit $C5,
• Taper le symbole égal (=) pour la comparaison à effectuer,

Nous cherchons à savoir si ce chiffre est le plus grand de tous. C'est la fonction Excel Max, à appliquer sur la colonne des chiffres d'affaires, qui permet de retourner la plus grande valeur.

• Saisir le nom de la fonction suivi d'une parenthèse, soit Max(,
• Puis, sélectionner tous les chiffres d'affaires, soit la plage de cellules C5:C14,

Comme précédemment, celle-ci apparaît naturellement figée : $C$5:$C$14. Cette fois, c'est bien ce que nous souhaitons. Chaque chiffre d'affaires doit être comparé à la valeur la plus grande à rechercher dans cette plage inamovible. Ses bornes ne doivent donc pas se déplacer.

• Fermer la parenthèse de la fonction Max,

Le critère est donc ainsi défini :

=$C5=Max($C$5:$C$14)

S'il est vérifié, il indique que le chiffre d'affaires en cours d'analyse est bien le plus grand de tous. Dans ces conditions, nous devons faire réagir la ligne en lui appliquant un remplissage.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Choisir un fond vert clair dans la palette de couleurs et valider par Ok,

Nous sommes de retour sur la première boîte de dialogue qui résume l'alerte visuelle à déclencher. Un fond vert doit être appliqué sur la ligne lorsque le critère est satisfait, soit lorsque le chiffre d'affaires est bien le meilleur.

• Valider ces réglages de format dynamique en cliquant sur le bouton Ok,

De retour sur la feuille, vous constatez que le commercial Houda est automatiquement identifié. Si vous remplacez son chiffre d'affaires de 14200 Euros par 6000 par exemple, vous constatez que la couleur se déplace dynamiquement pour repérer un autre vendeur. Son chiffre n'est plus le meilleur de tous.

Il s'agit aussi d'identifier dynamiquement le moins bon résultat. La même plage de cellules doit être présélectionnée. La règle est quasiment identique, mais elle doit exploiter fort logiquement la fonction Excel Min. Comme vous le savez, cette dernière retourne la plus petite donnée numérique d'une plage de cellules désignée. Et bien entendu, il s'agit d'adapter la symbolique de couleur avec un rouge clair par exemple.



Nous remplaçons donc la règle précédente :

=$C5=MAX($C$5:$C$14)

Par la suivante :

=$C5=MIN($C$5:$C$14)

Et nous obtenons un modèle parfaitement dynamique, facile à interpréter pour l'attribution des primes selon le chiffre d'affaires réalisé.