Primes sur chiffres d'affaire 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 :


Calculs des primes en fonction des chiffres d'affaire des commerciaux
L'exercice que nous abordons ici permet de mettre en pratique des notions abordées au travers de différents supports de formation. Ainsi nous poserons des conditions multiples que nous validerons avec des fonctions SI imbriquées. Nous réaliserons des calculs en fonction de constantes placées dans des cellules fixes à l'aide des références absolues.

Le classeur de l'exercice

• Télécharger le classeur primes-sur-chiffres-affaires-commerciaux-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

Ce tableau représente les chiffres d'affaire réalisés par les commerciaux d'une entreprise. Chaque colonne propose de réaliser un calcul différent.

Le chiffre d'affaire moyen
Pour calculer ce résultat, nous ne pouvons pas utiliser la fonction moyenne ici. En effet, la fonction moyenne permet de réaliser la moyenne des valeurs sur une plage de cellules. Or ici, nous disposons du chiffre d'affaire total et du nombre de commandes qui ont permis de le réaliser, pour chaque commercial. Le calcul du CA moyen est donc une division à l'aide du symbole / du pavé numérique. Pour réaliser ce calcul, nous allons utiliser la méthode la plus efficace :

• Sélectionner toutes les cellules du CA Moyen soit E10:E20,
• Taper le symbole = pour débuter le calcul,
• Cliquer la cellule du premier chiffre d'affaire, soit D10,
• Enfoncer la touche du symbole division (/) du pavé numérique,
• Cliquer la première cellule du nombre de commandes soit C10,
• Valider le calcul à l'aide du raccourci clavier CTRL + Entrée,

CTRL + Entrée permet de valider un calcul en le répercutant sur l'ensemble de la plage de cellules présélectionnée. Toutes les références des cellules du calcul s'adaptent à la nouvelle ligne. Ce raccourci permet d'éviter de tirer la poignée. Le support de formation sur les raccourcis clavier d'Excel présente cette astuce ainsi que de nombreuses autres.


Récompense en fonction du chiffre d'affaire réalisé
Dans cette colonne, une récompense doit être attribuée en fonction des indications données par le petit tableau situé en haut à gauche de la feuille. Il y a quatre possibilités. Lorsque le chiffre d'affaire du commercial est supérieur à 100 000 Euros, un voyage lui est offert. Entre 50 000 et 100 000, il gagne une télévision. Entre 5 000 et 50 000, on lui offre un diner au restaurant et il ne reçoit rien sous les 5 000 Euros. Les conditions et les lots peuvent évoluer à tout moment. Donc les calculs doivent être bâties en fonction des données de ce petit tableau. Ainsi si les conditions et/ou les récompenses changent, les résultats pré-calculés dans les cellules se mettent à jour automatiquement. Pour envisager tous ces cas de figure, il faut poser des conditions grâce à la fonction SI() d'Excel. Mais par défaut une fonction Si ne permet d'envisager que deux cas, celui où le critère n'est pas vérifié et celui où il l'est. Pour envisager plus de cas, il faut imbriquer les fonctions SI. D'une manière générale, pour envisager N cas, il faut N-1 fonctions Si(). Ici nous avons 4 possibilités, nous devons donc utiliser 3 (4-1) fonctions SI. La formation Excel pour poser des raisonnements sur les feuilles explique avec soin ces fonctions et leurs imbrications.

• Sélectionner toutes les cellules de récompenses soit F10:F20,
• Débuter le calcul en tapant =SI(,

Dès lors que vous ouvrez la parenthèse, la fonction Si attend en premier argument, le critère. Il consiste à comparer le montant du CA du commercial par rapport à la première valeur du tableau des récompenses.

• Cliquer sur la première cellule du CA soit D10,
• Taper le symbole supérieur (>) pour la comparaison,
• Cliquer sur la cellule du montant le plus important dans le tableau des récompenses, C5,

Lorsque le calcul sera reproduit sur les autres cellules de la colonne RECOMPENSE, chaque chiffre d'affaire de chaque ligne devra être comparé à cette valeur, toujours en même position, en cellule C5. Pour que le calcul puisse se répercuter, cette cellule isolée doit donc être figée. C'est ce qu'apprend la formation Excel sur les références absolues.

• Pour ce faire, enfoncer la touche F4 du clavier,

La cellule C5 est encadrée de dollars indiquant qu'elle est figée. Le calcul peut être reproduit, il fera toujours référence à cette cellule fixe.

• Taper un point-virgule (;) pour indiquer quoi faire lorsque le critère est vérifié,
• Cliquer ensuite la cellule A5 de la récompense correspondante,
• Puis, enfoncer de nouveau la touche F4 afin de figer cette cellule pour les mêmes raisons,
• Taper un nouveau point-virgule (;) pour indiquer quoi faire si le critère n'est pas vérifié (Sinon),

C'est précisément ici que l'imbrication de fonctions Si doit intervenir pour augmenter les critères et envisager plus de cas.

• Taper de nouveau Si(,
• Cliquer de nouveau la première cellule du CA (D10) pour poser la deuxième condition,
• Puis taper de nouveau le symbole supérieur suivi du deuxième critère à vérifier et à figer, soit D10>$C$4,

Nous vérifions que le CA est supérieur à C4 soit 50 000. Si le calcul lit cette partie de la formule, c'est que le premier critère n'a pas été vérifié et donc que le CA n'est pas supérieur à C5 soit 100 000. Si le CA n'est pas supérieur à 100 000 mais qu'il est supérieur à 50 000, c'est qu'il est compris entre les deux. C'est l'ordre logique dans l'imbrication des conditions qui permet de déduire la comparaison Compris entre.

• Ajouter un nouveau point-virgule (;) pour indiquer quoi faire dans ce cas,
• Sélectionner la cellule A4 de la récompense et la figer avec la touche F4,
• Ajouter un point-virgule (;) pour indiquer quoi faire sinon,

Même remarque que précédemment, il nous reste deux cas à envisager. Le cas où le CA est encore supérieur à 5 000 et le cas échéant. Donc il nous reste une fonction Si à poser.

• Saisir une dernière fois Si(,
• Cliquer sur le premier chiffre d'affaire D10 pour poser le dernier critère,
• Saisir le symbole > puis sélectionner la dernière récompense C3 en la figeant avec F4,
• Taper un point-virgule (;) puis cliquer la cellule de la récompense associée (A3) en la figeant,
• Taper le dernier point-virgule (;) pour indiquer quoi faire sinon,

Ce dernier Sinon implique qu'aucun critère n'a été vérifié jusqu'alors et donc, que la récompense ne doit pas être attribuée. Pour ce faire, il suffit d'indiquer un texte vide entre guillemets.

• Taper '',
• Puis fermer les trois parenthèses des trois fonctions Si imbriquées les unes dans les autres,
• Valider et reproduire le calcul à l'aide du raccourci clavier CTRL + Entrée.

La formule complète est la suivante : =SI(D10>$C$5;$A$5;SI(D10>$C$4;$A$4;SI(D10>$C$3;$A$3;'')))

Toutes les récompenses sont correctement reproduites en fonction des contraintes posées dans le panneau de contrôle. Les résultats des calculs sont tous dynamiques puisqu'il suffit de changer les conditions du problème pour que les données s'adaptent automatiquement.


C'est ce qu'illustre la figure ci-dessus. Les trois seuils ont changé ainsi que les récompenses correspondantes. Et comme vous le remarquez dans la colonne récompense, les lots attribués s'adaptent instantanément.

Prime de rendement en fonction du chiffre d'affaire
Le principe est le même. Quatre cas de figure doivent être envisagés. Sauf qu'à l'issue il ne s'agit pas d'un texte à inscrire dans la cellule mais d'une valeur numérique. Là aussi, tout doit être dynamique en fonction des critères énoncés dans le panneau de contrôle. C'est donc une fois de plus une combinaison des fonctions Si et des références absolues qui vont permettre de résoudre le problème. Si le chiffre d'affaire est supérieur à 100 000, la prime est de 10%, entre 50 000 et 100 000 elle est de 5%, entre 5000 et 50 000, elle est de 0% et dans le cas restant, inférieur à 5000, elle est de 0% aussi. Même si les deux derniers cas conduisent au même résultat, nous allons tout de même poser la condition pour le cas où les données viendraient à changer dans le panneau de contrôle.

• Réaliser le calcul des primes de rendement à l'aide des fonctions Si et des références absolues,

Les cas à envisager (Alors, Sinon) de la colonne A doivent être remplacés par ceux de la colonne D. Tout le reste du calcul ne change pas.


La fonction complète est la suivante :

=SI(D10>$C$5;$D$5;SI(D10>$C$4;$D$4;SI(D10>$C$3;$D$3;0)))

Dans le dernier Sinon, nous inscrivons 0 à la place des deux guillemets pour obtenir une cellule numérique. En effet, la colonne suivante du Montant de la prime réalise un calcul en fonction de ce résultat qui ne peut donc pas être un texte. Les résultats sont bien sûr dynamiques. Vous changez les conditions et actions depuis le panneau de contrôle, les calculs de la prime de rendement s'adaptent instantanément.

Calcul du montant de la prime
Le plus dur est fait. Le Montant de la prime consiste à appliquer le pourcentage calculé dans la colonne Prime de rendement sur le chiffre d'affaire. Il s'agit donc d'une simple multiplication.

• Sélectionner toutes les cellules de la colonne Montant de la prime soit H10:H20,
• Taper = pour débuter le calcul et sélectionner le premier chiffre d'affaire, soit D10,
• Taper le symbole de la multiplication (*) et sélectionner la première prime, soit G10,
• Valider ce calcul par CTRL + Entrée pour le reproduire sur l'ensemble des cellules,

Pas de références absolues ici puisque chaque cellule du calcul doit se déplacer une ligne plus bas à chaque cellule pour réaliser la multiplication correspondante.


Le calcul des sommes en bas du tableau peut se régler très simplement.

• Sélectionner l'ensemble des cellules à sommer en incluant les cellules dans lesquelles doivent être déposés les résultats, soit C10:H21,
• Cliquer sur le bouton Somme automatique du ruban Accueil ou bien réaliser la combinaison ALT + =,

Seule la somme sur la colonne Prime de rendement n'est pas judicieuse. Vous pouvez donc sélectionner sa cellule G21 et effacer son résultat à l'aide de la touche Suppr du clavier.

Les valeurs caractéristiques
Le premier petit tableau situé juste en-dessous propose de calculer la moyenne des chiffres d'affaire ainsi que de faire ressortir le plus petit et le plus grand des CA. Il s'agit d'employer respectivement les fonctions moyenne, min et max. Toutes trois fonctionnent de la même façon. Il suffit de leur indiquer dans les parenthèses, la plage de cellules sur laquelle elles s'appliquent, les chiffres d'affaire, soit D10:D20.

• Réaliser ces trois calculs statistiques en D23, D24 et D25,

Les calculs conditionnels
Le dernier tableau propose de réaliser des sommes et des moyennes selon des conditions. Ainsi en première colonne, il s'agit de calculer la somme des chiffres d'affaire réalisés pour le département indiqué sur la colonne de gauche. En deuxième colonne, il faut calculer la somme des nombres de commandes en fonction de ce même département, et enfin en dernière colonne, il faut réaliser la moyenne des chiffres d'affaire correspondant à ce département. Excel propose pour ce faire les puissantes fonctions Somme.Si() et Moyenne.Si(). Dans l'un et l'autre cas, le calcul est réalisé en fonction d'un critère à vérifier sur une plage de cellules. Ces deux fonctions attendent donc trois paramètres. Tout d'abord il faut indiquer la plage de cellules sur laquelle vérifier le critère, puis il faut saisir ce critère et enfin, en troisième argument, il faut indiquer la plage de cellules correspondante sur laquelle il faut faire le calcul quand le critère est vérifié sur la première plage. Ce qui donne :

=Somme.Si(Plage_Critere ;Critere ;Plage_Somme)
=Moyenne.Si(Plage_Critere ;Critere ;Plage_Somme)

Nous allons commencer par la première somme conditionnelle sur la première colonne C.A. de ce dernier tableau :

• Sélectionner les trois cellules du calcul soit D28:D30,
• Débuter le calcul par =Somme.Si(,
• Sélectionner la plage du critère soit B10:B20,
• Enfoncer la touche F4 du clavier pour la figer,

En effet, le calcul doit être reproduit sur les cellules du bas mais la plage de cellules du critère quant à elle ne doit pas bouger, ne doit pas changer de ligne. Vous remarquez que les dollars encadrent les deux cellules à la fois.

• Taper un point-virgule (;) pour passer à l'argument suivant du critère,
• Sélectionner la cellule du département pour cette somme conditionnelle soit C28,

Ici en revanche, cette cellule ne doit pas être figée. En reproduisant le calcul sur les cellules du dessous, la ligne du critère doit suivre le mouvement en prélevant le critère des cellules des lignes suivantes.

• Taper un point-virgule (;) pour passer à l'argument suivant,
• Sélectionner la plage de cellules à sommer, soit D10:D20,
• Enfoncer la touche F4 pour la figer comme la précédente,
• Fermer la parenthèse et valider cette somme conditionnelle par CTRL + Entrée,

Le calcul de la somme conditionnelle des nombres de commandes est le même. Il suffit de remplacer la plage sur laquelle la somme s'effectue. $D$10:$D$20 devient $C$10:$C$20. Et enfin pour la moyenne conditionnelle des CA, les arguments sont strictement identiques. Ce qui change c'est le nom de la fonction. Somme.si devient Moyenne.si.

• Réaliser les sommes conditionnelles sur les nombres de commandes,
• Réaliser les moyennes conditionnelles sur les chiffres d'affaire,

Au final, les trois fonctions de la première ligne pour ces résultats statistiques sont les suivantes :

=SOMME.SI($B$10:$B$20;C28;$D$10:$D$20)
=SOMME.SI($B$10:$B$20;C28;$C$10:$C$20)
=MOYENNE.SI($B$10:$B$20;C28;$D$10:$D$20)

Ces fonctions puissantes permettent d'isoler instantanément des résultats selon des critères pour tirer rapidement des interprétations et stratégies d'entreprise. Si vous modifiez les départements dans la colonne secteur du tableau des chiffres d'affaire, vous remarquez que ces résultats se recalculent automatiquement en fonction de ces nouveaux paramètres.

Mise en valeur dynamique des résultats
Pour obtenir une lecture encore plus facile et plus rapide des résultats fournis par le tableau, nous allons mettre en oeuvre une mise en forme dynamique sur certaines colonnes. Une mise en forme conditionnelle permet d'altérer l'aspect des cellules en fonction du résultat qu'elles portent. Comme cette mise en forme dépend des valeurs, si ces valeurs changent, la mise en forme évolue. Pour commencer, nous allons faire ressortir le chiffre d'affaire le plus important.

• Sélectionner tous les chiffres d'affaire soit D10:D20,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le dernier type : Utiliser une formule pour...,

L'objectif est de bâtir une formule qui compare chaque cellule de la plage à la valeur max calculée en D25. Pour ce faire :

• Cliquer dans la zone de saisie de la formule,
• Taper = et cliquer le premier chiffre d'affaire soit D10,
• Enfoncer plusieurs fois la touche F4 du clavier jusqu'à ce que tous les dollars disparaissent,

Etant donné que nous souhaitons comparer chaque chiffre d'affaire à la plus grande de ces valeurs, nous ne devons pas la figer pour que chaque cellule soit parcourue.

• Puis taper = et sélectionner le calcul du maximum en D25,

Cette fois les dollars doivent être conservés. Cette cellule est la cellule de référence pour la comparaison, elle ne doit pas bouger.

• Cliquer ensuite sur le bouton Format,
• En basculant au travers des onglets de cette boîte de dialogue, choisir une police grasse de couleur différente, avec un fond de couleur pour la cellule,
• Valider ces boîtes de dialogue par Ok.

Le chiffre d'affaire le plus important ressort ainsi de façon évidente. Et si d'aventure les données venaient à évoluer et le chiffre d'affaire max à changer de main, c'est ce dernier qui récupérerait dynamiquement cette mise en valeur au profit de l'actuel qui retrouverait son état d'origine. Enfin, pour y voir plus clair, nous allons faire ressortir dynamiquement, toutes les primes supérieures à 10 000.

• Sélectionner toutes les primes, soit H10:H20,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Pointer sur Règles de mise en surbrillance des cellules,
• Cliquer ensuite sur Supérieur à,
• Dans la boîte de dialogue, saisir la valeur 10000,
• Puis choisir un fond jaune avec texte jaune foncé à l'aide de la liste déroulante,
• Valider cette mise en forme dynamique par Ok.

Toutes les valeurs concernées ressortent d'un seul coup d'oeil. Encore une fois, et c'est tout l'intérêt, si les montants des primes venaient à changer, le format dynamique s'adapterait de façon à faire ressortir uniquement les cellules dont la valeur correspond au critère posé.