Attribuer des primes selon des quotas de vente

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 quotas de ventes

Dans ce septième exercice Excel niveau avancé, nous devons de nouveau apporter des solutions découlant d'analyses de contextes. L'objectif, selon la philosophie du tableur, est de livrer un modèle parfaitement dynamique, réagissant aux modifications des hypothèses. Tous les calculs doivent donc être bâtis sur des variables, soit les références des cellules.



La capture ci-dessus illustre le tableau modèle à partir duquel nous allons réaliser cette mise en pratique. Il est question de primes à attribuer en fonction de quotas de ventes à respecter.

Source et présentation de la problématique
Tout d'abord nous devons commencer par réceptionner le tableau proposant la structure et les données.

• Télécharger le classeur quotas-et-primes-exercice-excel.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é,

Sur la feuille de ce classeur nommée Primes_sur_quantites, un tableau résume les ventes d'ordinateurs réalisées par les représentants de l'entreprise. Ils sont énumérés en colonne B, entre les lignes 5 et 16. Tous n'officient pas sur le même secteur. Et ces départements respectifs sont indiqués en colonne C, pour les mêmes lignes. En colonne D sont listées leurs ventes respectives, en quantités. La colonne E qui suit, fixe les règles, elles aussi respectives. Il s'agit du quota d'unités vendues à dépasser pour chacun, afin de déclencher l'allocation d'une prime, à calculer en colonne F. Cette récompense est fixée à 50 Euros par ordinateur vendu au-delà du quota. Pour offrir un modèle dynamique sur lequel les leviers peuvent être actionnés, cette valeur doit être considérée comme une variable. C'est pourquoi elle est inscrite en cellule G2 de référence.

Il s'agira ensuite de livrer la proportion des primes attribuées en colonne G. Ce calcul de synthèse permettra de comparer facilement les performances des commerciaux, les uns par rapport aux autres. Pour connaître rapidement le secteur de vente le plus propice, un petit tableau est disponible entre les colonnes I et J. Son objectif est de résumer le total des ventes réalisées par département. Ces statistiques favoriseront les comparaisons et interprétations.

Enfin, d'autres calculs statistiques sont attendus entre les lignes 18 et 20 pour faire ressortir les valeurs seuilles.

Calculs conditionnels de récompenses
Vous l'avez compris, ces gratifications sont sujettes à condition. L'emploi de la fonction Si est une fois de plus inévitable. Elle permet d'analyser un critère pour engager des actions, comme des calculs à adapter :

=Si(Critère_à_vérifier; Action_Alors ; Action_Sinon)

Le raisonnement est le suivant. Si la quantité vendue est supérieure au quota fixé, 50 Euros doivent être versés par ordinateur supplémentaire vendu : (Quantité_vendue-Quota)*50. Mais rappelons-le, ce montant unitaire de prime doit être considéré comme une variable. Nous devons donc exploiter sa cellule de référence. Sans plus attendre, nous proposons de bâtir la première formule dynamique :

• Sélectionner le premier montant de prime à trouver, soit la cellule F5,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,

L'ouverture de la parenthèse appelle les arguments à renseigner. Et à ce titre, vous notez l'apparition d'une info-bulle indiquant qu'ils sont au nombre de trois, comme nous l'avons expliqué. Chacun est séparé d'un autre par un point-virgule, selon la syntaxe Excel.



Le test_logique est le critère. C'est dans cette branche que nous devons comparer la quantité vendue avec le quota.

• Sélectionner la première quantité à comparer pour intégrer sa référence D5 dans le calcul,
• Taper le symbole supérieur suivi du symbole égal, soit : >=, pour la comparaison à effectuer,
• Désigner alors le quota correspondant à atteindre pour intégrer sa référence E5,

La partie du critère est terminée : D5>=E5. Il consiste à savoir si la quantité vendue pour le commercial en cours est bien au moins égal au quota fixé. Si la condition est satisfaite, la prime doit être calculée.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Ouvrir une parenthèse pour la factorisation du calcul,

En effet, la multiplication est prioritaire sur la soustraction. Mais c'est bien le résultat de la soustraction que nous devons calculer en premier, pour le multiplier par la prime unitaire.

• Sélectionner de nouveau la quantité vendue, soit la cellule D5,
• Taper le symbole moins (-) du pavé numérique pour enclencher la soustraction,
• Puis, sélectionner le quota correspondant, soit la cellule E5,
• Fermer la parenthèse pour encapsuler cette soustraction,
• Taper le symbole de l'étoile (*) du pavé numérique pour la multiplication,
• Désigner la variable de la récompense, soit la cellule G2,
• Enfoncer la touche F4 du clavier pour la figer,

En effet, les références absolues sont nécessaires dans ce calcul. Nous allons répliquer la formule sur les lignes du dessous. De fait, les références des cellules non figées vont suivre le déplacement pour s'adapter. Concernant la quantité et le quota, c'est bien ce que nous souhaitons. Pour chaque vendeur, nous devons considérer son résultat et sa contrainte. Mais le calcul lui, doit toujours se baser sur cette cellule de référence, située uniquement en case G2. Cette dernière ne doit pas suivre le déplacement. Nous la figeons donc.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,

Dans le cas contraire, aucun versement ne doit être honoré dans la mesure où l'objectif n'est pas atteint. Nous proposons de laisser la cellule vide pour une présentation épurée.

• Taper deux guillemets ('') et fermer la parenthèse de la fonction Si,

Les guillemets servent à inscrire du texte. En l'absence d'information, ils permettent de garder la cellule vierge.

• Valider la formule par le raccourci clavier CTRL + Entrée,

Cette combinaison de touches est importante dans Excel. Elle valide le calcul tout en conservant active la cellule du résultat. L'objectif est de l'exploiter dans l'enchaînement.

Le premier résultat tombe. Il semble cohérent pour deux raisons. Premièrement, la quantité vendue dépasse l'objectif, donc la prime doit bien exister. Deuxièmement, la différence est de 60 unités. Chacune est valorisée de 50 Euros, conduisant aux 3000 Euros.

La formule que nous avons bâtie est la suivante :

=SI(D5>=E5;(D5-E5)*$G$2;'')

Elle doit être répliquée sur les lignes du dessous pour que sa logique soit appliquée à chaque vendeur. Nous devons exploiter la poignée de la cellule du résultat. Cette dernière est matérialisée, comme vous le savez, par le petit carré noir, situé en bas de la case active.

• Placer le curseur de la souris au-dessus de cette poignée,

Comme vous le constatez, son curseur se transforme en une croix noire. Excel indique qu'il est prêt à reproduire cette logique.

• Double cliquer avec le bouton gauche de la souris sur cette poignée,

Instantanément, tous les résultats sont livrés sur la hauteur du tableau. Cette technique est particulièrement efficace pour reproduire un calcul à la verticale. Dans ce contexte, Excel détecte les bornes du tableau. A l'horizontale, il est nécessaire d'accompagner la poignée en la cliquant et en la glissant.



Vous remarquez la présence fort naturelle de cellules vides, précisément lorsque le vendeur n'a pas atteint son objectif. Le raisonnement semble donc s'être parfaitement adapté. Pour la bonne compréhension de la mécanique, il est intéressant de vérifier comment Excel a réagi.

• Double cliquer sur le dernier résultat dans la rangée, soit la cellule F16,

Cette technique affiche la syntaxe du calcul répliqué et met en valeur les cellules impliquées sur des fonds en couleur. Vous remarquez que le test est bien réalisé sur la ligne du commercial en cours, soit la ligne 16. Le calcul de la prime quant à lui, considère irrémédiablement la cellule de référence G2, judicieusement figée.

• Enfoncer la touche Echap du clavier pour abandonner la modification,

Ne soyez pas tenté de cliquer dans une autre cellule pour abandonner cette syntaxe. Vous risqueriez de l'endommager en incluant une nouvelle référence.

Synthèse des ventes
Les calculs statistiques sont importants dans un tableau de synthèse. Ils rendent compte rapidement de la situation. Nous devons livrer les totaux et valeurs seuilles entre les lignes 18 et 20. Nous proposons de produire ces trois calculs pour les quantités vendues et les primes. Ce dernier résultat est d'ailleurs nécessaire pour pouvoir calculer la part des primes distribuées en colonne G.

• Sélectionner le premier total à livrer, soit la cellule D18,
• Tout à fait à droite du ruban Accueil, cliquer sur le bouton Somme automatique,

Ce bouton est matérialisé par la lettre grecque Sigma qui ressemble à la lettre M orientée à 90 degrés.



La syntaxe de la somme s'inscrit automatiquement dans la cellule désignée. Excel a ajouté le symbole égal pour nous. La formule est en attente de validation ou de modification. Et c'est ce que nous allons faire. La suggestion de la plage à sommer déborde sur la cellule vide de séparation, placée sous le tableau. Excel tente de regrouper toutes les données adjacentes qu'il est possible d'additionner. Cette cellule vide ne modifierait pas le résultat. Mais pour une maîtrise professionnelle, il n'est pas correct de l'intégrer.

• Sélectionner uniquement les quantités vendues, soit la plage de cellules D5:D16,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Copier ce résultat avec le raccourci CTRL + C,

Vous pouvez aussi cliquer sur le bouton Copier, situé dans la section Presse-papiers du ruban Accueil.

• Sélectionner la cellule F18 sous la rangée des primes,
• Puis, coller la formule par le raccourci clavier CTRL + V,

Vous pouvez aussi utiliser le bouton Coller situé au même endroit que le bouton Copier.

• Réaliser le raccourci clavier CTRL + M pour formater ce résultat en Euros,

Vous pouvez aussi cliquer sur le bouton Format Nombre Comptabilité situé dans la section Nombre du ruban Accueil.

Le copier-coller impose les déplacements au même titre que la poignée d'une cellule. Nous avons reproduit la logique deux colonnes plus à droite. En conséquence, la somme doit être réalisée sur les primes. Nous proposons de le constater.

• Double cliquer sur la cellule F18 pour afficher la syntaxe et les couleurs,

Le fond bleu repéré par Excel ne souffre aucune ambiguïté. La somme a bien été décalée pour fournir le total des primes en colonne F.

• Enfoncer la touche Echap du clavier pour abandonner la modification de la syntaxe,

Pour les valeurs seuilles, Excel offre les fonctions Max et Min. Nous pouvons saisir leur nom et renseigner leur argument, en transmettant la plage de calcul. Mais nous pouvons aussi les déclencher, comme nous l'avons fait pour la somme automatique. Il s'agit d'ailleurs de fonctions associées.

• Sélectionner la première plus grande quantité à livrer, soit la cellule D19,
• A droite du ruban Accueil, cliquer sur la flèche du bouton Somme automatique,
• Dans la liste, choisir Max,

Comme précédemment, la syntaxe de la formule s'inscrit dans la cellule. La formule est en attente. La suggestion n'est pas correcte. Elle se base sur les valeurs numériques adjacentes. Il n'y en a qu'une, celle du précédent calcul.

• A la souris, désigner toutes les quantités, soit la plage de cellules D5:D16,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Copier le résultat (CTRL + C),
• Sélectionner la cellule F19 sous la rangée des primes,
• Coller la formule (CTRL + V),
• Réaliser le raccourci clavier CTRL + M pour formater le résultat en Euros,

Une fois encore, les références des cellules ont parfaitement suivi le décalage imposé par le copier-coller. C'est bien la plus grande prime attribuée qui est extraite et affichée.

• Sélectionner la cellule D20 pour extraire la plus petite quantité vendue,
• Cliquer sur la flèche du bouton Somme automatique,
• Dans la liste, choisir la fonction Min,
• A la souris, sélectionner toutes les quantités, soit la plage de cellules D5:D16,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Copier le résultat (CTRL + C),
• Sélectionner la case de la plus petite prime à trouver, soit F20,
• Coller la formule (CTRL + V),
• Réaliser le raccourci CTRL + M pour le format monétaire,

Le résultat obtenu est de 0 Euro. Il coule de source puisque des cellules vides sont identifiées dans la colonne. A ce titre, il est intéressant de constater que ces cellules vierges ne parasitent pas l'exploitation de ces fonctions statistiques. Nous aurions pu en douter dans la mesure où elles ne renferment pas de données numériques.

Calculer les parts de primes
Ces résultats sont intéressants pour confronter les chiffres des vendeurs. A leur lecture, ils donnent immédiatement une idée générale sur les performances réalisées. Calculer la prime en pourcentage consiste à diviser la prime de chaque vendeur par la somme de toutes les primes attribuées.

• Sélectionner la première part de prime à calculer, soit la cellule G5,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Sélectionner la première prime pour intégrer sa référence F5 dans le calcul,
• Taper le symbole slash (/) du pavé numérique pour enclencher la division,
• Désigner le total des récompenses pour intégrer la cellule F18 dans la formule,
• Enfoncer la touche F4 duclavier pour la figer,

Une fois de plus, les références absolues sont incontournables. Nous allons répliquer ce calcul sur les lignes du dessous. Ainsi nous déduirons la part de prime pour chaque commercial. Chaque prime de chaque ligne doit donc être considérée. Mais chacune d'entre elles doit être divisée par le même total, situé en cellule F18, qui ne doit donc pas suivre le déplacement.

• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Puis, double cliquer sur la poignée su résultat pour répliquer la logique sur la colonne,

Des erreurs surviennent notifiées par le message : #VALEUR.



Notre calcul est manuel. Il ne s'agit plus de fonctions Excel. Les cellules vides deviennent gênantes. Il n'est en effet pas possible de diviser un texte par un nombre.

Néanmoins, nous pouvons contourner le problème grâce à la précieuse fonction Si. Dans ce contexte, elle permet de corriger un défaut et non plus d'analyser les données d'un calcul. Elle consiste à vérifier si la cellule de la prime est vide. Dans ce cas, elle ne doit pas enclencher le calcul. Dans le cas contraire, elle doit le réaliser.

• Double cliquer sur le premier résultat, soit la cellule G5 pour modifier sa syntaxe,
• Adapter la formule comme suit : =Si(F5='';'';F5/$F$18),
• Valider la modification par le raccourci CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la correction de formule,

Si vous consultez la syntaxe du dernier résultat, vous constatez que la logique s'est parfaitement répliquée. C'est bien la dernière prime qui est divisée par ce même total.

Additions conditionnelles
Dans le petit tableau de synthèse sur la droite de la feuille, nous devons désormais réaliser la somme des quantités vendues par département. Ainsi, nous en déduirons les secteurs vraisemblablement les plus dynamiques. Il ne s'agit pas d'une addition classique puisqu'elle dépend d'un paramètre variable. Ce paramètre est indiqué en colonne I. La somme des quantités vendues doit être réalisée en fonction du numéro de département. C'est pourquoi Excel propose la fonction Somme.Si. Sa syntaxe est la suivante :

=Somme.si(Plage_du_critère; Critère ; Plage_de_la_somme)

En premier argument, nous allons pouvoir lui indiquer la plage de cellules sur la colonne C. En deuxième argument, nous devons lui passer le numéro de département à trouver sur cette plage. En troisième argument, nous devons lui indiquer la plage correspondante sur laquelle la somme doit être faite. Il s'agit donc des quantités vendues en colonne D. Ainsi, à chaque numéro de département concordant sur la colonne C, la valeur numérique correspondante de la colonne D, est intégrée dans l'addition.

• Sélectionner la première somme conditionnelle à livrer, soit la cellule J5,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir le nom de la fonction suivi d'une parenthèse, soit Somme.Si(,

Naturellement, l'info-bulle apparaît aussitôt pour vous aider à renseigner les trois arguments que nous avons expliqués.

• Sélectionner tous les départements, soit la plage de cellules C5:C16,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$5:$C$16,

Cette formule est en effet destinée à être répliquée sur les lignes du dessous. Pour tout nouveau département passé en critère, la recherche doit être réalisée sur cette même plage, dont les bornes ne doivent pas bouger.

• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Sélectionner le premier département du tableau de synthèse, soit la cellule I5,

Si votre formule déborde sur cette cellule, elle empêche la sélection. Vous devez donc taper ses références (I5) au clavier. Ce critère ne doit pas être figé. Lorsque nous répliquerons la formule sur les lignes du dessous, il empruntera les valeurs respectives indiquées dans la colonne.

• Taper un point-virgule (;) pour passer dans l'argument de la plage pour l'addition,
• Sélectionner toutes les quantités vendues, soit la plage de cellules D5:D16,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $D$5:$D$16,

La raison est la même que précédemment. Lorsque la formule sera reproduite sur les lignes du dessous, l'addition devra toujours être réalisée sur cette même plage, aux bornes fixes donc.

• Fermer la parenthèse de la fonction Somme.Si,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,
• Double cliquer sur la poignée du résultat pour répliquer la logique sur les autres départements,

Instantanément, nous comprenons que le département de l'Isère (38) est le plus lucratif. Il devance légèrement celui du Rhône (69).

La formule que nous avons bâtie est la suivante : =SOMME.SI($C$5:$C$16; I5; $D$5:$D$16). Si vous sélectionnez le dernier résultat, vous remarquez que le critère a suivi le mouvement pour adapter la condition de calcul sur ces mêmes plages : =SOMME.SI($C$5:$C$16; I9; $D$5:$D$16).

Comme toujours, nous avons construit une formule parfaitement dynamique. Si vous modifiez l'un des départements d'affectation en colonne C, les résultats de synthèse s'actualisent instantanément en colonne J.

Identifier les valeurs seuilles
Pour parfaire ce modèle dynamique et simplifier l'interprétation de ses résultats, nous souhaitons enclencher des repérages visuels automatiques. Nous souhaiterions mettre en évidence la ligne du meilleur commercial et celle du moins bon. Les symboliques de couleur doivent être adaptées. La condition consiste à identifier respectivement la plus grande et la plus petite quantité vendue. Nous pourrons donc nous baser sur les résultats de synthèse offerts en lignes 19 et 20, pour effectuer la correspondance.

Excel propose des règles de mise en forme conditionnelle toutes faites qui semblent correspondre. Parmi les règles de valeurs de plage haute/basse, certaines permettent de repérer le plus grand et le plus petit d'une plage. Mais dans ces conditions, seule la cellule numérique est mise en évidence. Pour plus de clarté, nous souhaitons identifier la ligne complète du commercial. Nous devons donc bâtir nous-mêmes la syntaxe de cette règle spécifique.

• Sélectionner les données de tous les commerciaux, soit la plage de cellules B5:G16,
• 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...,
• Puis, cliquer dans la zone de saisie juste en-dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Sélectionner la première quantité à analyser pour intégrer sa référence D5 dans la règle,

Nous le savons, avec les mises en forme conditionnelles, les références des cellules apparaissent complètement figées par défaut. Or cette règle doit passer en revue toutes les quantités en suivant la chronologie de la sélection. La référence D5 doit donc être autorisée à se déplacer vers le bas, soit en ligne. Inversement, sur une même ligne, donc pour un vendeur, le critère doit nécessairement être analysé sur cette quantité qui ne doit pas bouger à l'horizontale, soit en colonne.

• Enfoncer deux fois de suite la touche F4 du clavier, ce qui donne : $D5,

Un seul dollar demeure. Il est placé devant l'indice de colonne de la cellule. Elle a interdiction de se déplacer à l'horizontale.

• Taper le symbole égal (=) pour la correspondance de la condition à satisfaire,
• Sélectionner le résultat du maximum pour intégrer la cellule D19 dans le critère,

Elle est naturellement figée comme précédemment. Et cette fois, c'est bien ce que nous souhaitons. Chaque quantité passée en revue sur les lignes du tableau, doit être comparée à cette valeur seuille, située dans une cellule de référence. Elle ne doit donc pas bouger.

Le critère est donc le suivant : =$D5=$D$19. Il consiste à repérer la quantité vendue la plus grande dans le tableau. Si la condition est vérifiée, une alerte visuelle doit se déclencher pour repérer dynamiquement le vendeur en question.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert clair,
• Puis, valider cette association en cliquant sur le bouton Ok,

De retour sur la première boîte de dialogue, l'aperçu explique qu'un fond vert se déclenchera sur la ligne du commercial ayant vendu le plus d'unités.

• Cliquer de nouveau sur Ok pour finaliser la construction de la règle,

Le commercial Bonbeur est aussitôt mis à l'honneur. Cette alerte visuelle attire instantanément l'oeil pour faciliter l'interprétation des résultats. Elle est bien sûr dynamique. Si vous modifiez sa quantité 510 par 200 par exemple, le repérage se déplace pour identifier automatiquement le nouveau meilleur vendeur.

Sur cette même plage de cellules, il reste à identifier le commercial le moins performant. Le procédé est identique. La règle doit être adaptée pour comparer chaque quantité avec le résultat du calcul du minimum, en cellule D20 : =$D5=$D$20. La symbolique doit être adaptée. Un remplissage tirant vers le rouge délivrera la bonne interprétation à la lecture.



Nous livrons donc un modèle parfaitement dynamique, aussi bien en termes de calculs que d'alertes visuelles.