Exercice Excel sur les résultats commerciaux

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


Sujets que vous pourriez aussi aimer :


Rémunérations des commerciaux : Excel débutant

Cette mise en pratique est le cinquième exercice Excel niveau débutant. Les opérations à mettre en oeuvre ne sont pas plus complexes que dans les cas précédents. Mais désormais, il n'est plus question de colonnes intermédiaires pour décomposer les calculs. Toutes les opérations doivent être regroupées dans une même formule.



Il s'agit donc d'une très bonne occasion de poursuivre l'entraînement et de consolider les acquis.

Source et présentation de l'objectif
Comme toujours, la structure du tableau est déjà conçue. Le classeur Excel est donc proposé au téléchargement.

• Télécharger le classeur resultats-des-commerciaux.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier 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 Résultats comme l'indique son unique onglet en bas à gauche de l'interface Excel.



Le tableau de cette feuille recense les commerciaux d'une entreprise. Ils sont énumérés en colonne B à partir de la ligne 5 jusqu'à la ligne 14.

Ces commerciaux sont tous rémunérés tout d'abord selon une partie de salaire fixe. Chacun de ces salaires est indiqué en colonne C. Ils jouissent tous d'une prime d'ancienneté. Cette dernière diffère logiquement selon le commercial. Leurs montants sont renseignés en colonne D.



Cette prime doit bien sûr être incluse dans le calcul final du salaire mensuel. En l'occurrence ici, il s'agit d'une simple addition.

Chaque mois, les commerciaux de cette entreprise réalisent des ventes. Leurs résultats sont mentionnés en colonne F. Une commission est attribuée proportionnellement au résultat de chacun. Il s'agit d'un pourcentage à appliquer sur le montant des ventes par commercial. Ce pourcentage de commission n'est pas le même pour tous. Il doit donc être intégré dynamiquement dans le calcul final du salaire. L'opération est une simple multiplication à ajouter au résultat précédent de l'addition.



Comme vous le savez, un calcul doit toujours être dynamique dans Excel. Cela implique qu'il n'est jamais bâti sur des constantes. Il s'agira comme toujours de construire les formules sur les références des cellules et non les valeurs qu'elles contiennent.

Ces pourcentages de commission sont inscrits en colonne E, respectivement pour chaque commercial.

Calculer la rémunération totale des commerciaux
Ce salaire total du mois doit donc être calculé à l'extrémité du tableau, soit en colonne G. Pour bien asseoir les techniques que nous avons démontrées jusqu'alors, nous proposons d'obtenir le résultat avec deux méthodes différentes. La première consiste à exploiter la poignée de la cellule pour répliquer la logique de la formule. La seconde consiste à bâtir le calcul sur la plage présélectionnée. Comme vous le savez, une formule Excel débute nécessairement par le symbole égal (=). Et elle doit être bâtie à l'endroit où son résultat est attendu, soit en cellule G5 pour le premier.

• Cliquer dans la cellule G5 pour la sélectionner,
• Taper le symbole égal (=) pour débuter la formule,
• Sélectionner le fixe mensuel de la même ligne pour intégrer sa référence C5 dans la syntaxe,
• Taper le symbole plus (+) du pavé numérique pour enclencher l'addition,
• Sélectionner la prime d'ancienneté de la même ligne pour inclure sa référence D5,
• Taper le symbole plus (+) pour poursuivre l'addition,
• Sélectionner le pourcentage de commission pour intégrer sa référence E5 dans le calcul,
• Taper le symbole de l'étoile (*) du pavé numérique pour déclencher la multiplication,
• Sélectionner le total de la même ligne pour inscrire sa référence F5 dans la formule,
• Valider enfin le calcul par le raccourci clavier CTRL + Entrée,

Souvenez-vous, cette combinaison de touches permet de valider une saisie tout en conservant la cellule active. Ainsi, nous n'aurons pas besoin de la resélectionner pour reproduire la logique de la formule dans la foulée. La touche Entrée seule aurait elle aussi validé le calcul. Mais la cellule du dessous aurait été activée, imposant à l'utilisateur de remonter ensuite sur la cellule du dessus.

Comme l'indique la capture ci-dessus, nous obtenons le premier salaire en ligne 5, donc pour le commercial Hochon. Ce résultat est inscrit en cellule G5 comme le rappelle la zone Nom, située en haut à gauche de l'interface Excel. Sur sa droite, la longue zone de saisie horizontale est la barre de formule. Cette dernière dont nous avions donné la définition, rappelle le contenu exact de la cellule. Dans le cas d'une formule, elle affiche sa syntaxe : =C5+D5+E5*F5, tandis que la cellule elle-même restitue son résultat.

Les parenthèses ne sont pas nécessaires dans cette syntaxe. Etant donné que la multiplication est prioritaire sur l'addition, le pourcentage (E5) est appliqué indépendamment sur le total des ventes (F5). Le résultat de cette opération est alors additionné aux autres valeurs du calcul.

Nous avons insisté sur cette philosophie à maintes reprises. On ne refait jamais deux fois le même calcul dans Excel. Sa logique doit être répliquée, en l'occurrence sur les lignes du dessous ici, pour les autres commerciaux. Et c'est la poignée d'une cellule portant une formule qui permet de reproduire cette dernière.



En bas à droite de la cellule active, soit G5, vous notez la présence d'un petit carré. Il s'agit de la poignée de la cellule. Si vous pointez dessus, le curseur de la souris se transforme en une petite croix noire. Cette croix indique que la poignée est prête à être exploitée.

• Pointer sur la poignée de la cellule G5 pour obtenir la croix noire,
• Puis, double cliquer à l'aide du bouton gauche de la souris,

Comme vous le constatez, la formule est instantanément répliquée sur la hauteur du tableau. Excel détecte automatiquement les bornes de ce dernier. Nous aurions aussi pu accompagner la poignée dans le mouvement en cliquant et glissant vers le bas jusqu'à la ligne 14.

Quoiqu'il en soit, cette méthode très efficace nous a permis, sur la base d'une seule formule, d'obtenir les calculs de tous les salaires des commerciaux. Et par le jeu des références relatives, ils se sont parfaitement adaptés. Chaque formule répliquée considère bien les données du commercial pointé sur sa ligne.

• Sélectionner le tout dernier résultat, soit la cellule G14,

Comme vous le constatez, le calcul d'origine en G5 : =C5+D5+E5*F5 s'est transformé 9 lignes plus bas, soit en G14 en : =C14+D14+E14*F14. Cela confirme donc que les références des cellules impliquées dans la formule ont suivi le sens du déplacement. Ici, ce sont les lignes qui ont été impactées pour suivre la réplication de ligne en ligne. En conclusion, le résultat obtenu en G14 pour le commercial Bernard, a bien pris en compte toutes les données de ce dernier, sur sa propre ligne.

Nous souhaitons atteindre ces mêmes résultats avec la seconde méthode. Nous insistons sur cette dernière à chaque exercice. Elle est particulièrement efficace. Elle consiste à présélectionner l'intégralité des cellules dans lesquelles les données doivent être calculées. Mais avant cela, les précédents calculs doivent être supprimés bien entendu.

• Sélectionner tous les précédents résultats, soit la plage de cellules G5:G14,
• Enfoncer la touche Suppr du clavier pour effacer les calculs,

La plage de cellules est toujours sélectionnée. Et comme vous le remarquez, la première, G5, n'est pas surbrillée. Elle appartient bien à la sélection pourtant. Excel indique simplement qu'elle est active par défaut dans cette plage. Et c'est ainsi que nous allons pouvoir reconstruire la formule comme si elle lui était dédiée.

• Taper le symbole égal (=) pour débuter le calcul,
• Sélectionner le fixe mensuel de la même ligne pour intégrer sa référence C5 dans la syntaxe,
• Taper le symbole plus (+) du pavé numérique pour enclencher l'addition,
• Sélectionner la prime d'ancienneté de la même ligne pour inclure sa référence D5,
• Taper le symbole plus (+) pour poursuivre l'addition,
• Sélectionner le pourcentage de commission pour intégrer sa référence E5 dans le calcul,
• Taper le symbole de l'étoile (*) du pavé numérique pour déclencher la multiplication,
• Sélectionner le total de la même ligne pour inscrire sa référence F5 dans la formule,
• Enfin, valider nécessairement le calcul par le raccourci clavier CTRL + Entrée ,

Les résultats tombent instantanément. Ils sont automatiquement répliqués sur l'ensemble des cellules sélectionnées. Et bien que nous n'ayons pas tiré la poignée, les références des cellules impliquées dans le calcul, se sont déplacées vers le bas, pour suivre la chronologie des cellules indiquées. Cette méthode se résume en deux points essentiels. Tout d'abord, la plage de cellules des calculs doit être présélectionnée. Ensuite, la formule doit obligatoirement être validée par le raccourci CTRL + Entrée.

Pour terminer les calculs, un résultat statistique sur le total des salaires versés peut s'avérer opportun. Nous proposons de l'afficher en colonne G, en laissant une ligne de séparation, soit en G16.

• Sélectionner tout d'abord la cellule F16,
• Saisir le titre : Somme , puis valider,
• Sélectionner ensuite la cellule G16,
• Tout à fait à droite du ruban Accueil, cliquer sur le bouton Somme automatique,

Ce bouton est matérialisé par la lettre grecque Sigma. Elle ressemble à la lettre M orientée à 90 degrés. Comme il s'agit d'une fonction automatique, elle inscrit elle-même le symbole égal (=) nécessaire pour débuter la formule. Et à ce stade, elle propose une plage de cellules à sommer G5:G15. Elle est en attente de validation ou de modification. Souvenez-vous, dans la syntaxe, les deux points (:) signifient : de G5 à G15.

Nous allons profiter de la temporisation d'Excel pour apporter une correction. Le calcul proposé aboutirait au bon résultat. Mais il n'est pas logique d'intégrer la cellule G15 dans la sélection. Elle est située en dehors du tableau et elle est vide.

• A la souris, sélectionner précisément les résultats des commerciaux, soit la plage G5:G14,
• Puis, valider le calcul avec la touche Entrée par exemple,

Nous obtenons le résultat de : 16398,83. Il indique instantanément le montant versé par l'entreprise en salaires.

Présentation des données et formatage
Les données que nous venons de greffer au tableau dénotent par rapport au reste de la présentation. Elles sont dénuées de mise en forme. De plus, la hauteur de la ligne n'est pas conforme à celle des autres.

Mais avant de nous soucier de cette information de synthèse, nous souhaitons commencer par afficher toutes les valeurs monétaires, en Euros. Souvenez-vous, une devise ne doit jamais être inscrite dans une cellule. Cette information textuelle transformerait la cellule elle-même en un texte, interdisant tout calcul. Nous devons appliquer un format, plus précisément le format monétaire. Et pour optimiser les opérations, nous souhaitons regrouper toutes les cellules concernées, dans une même sélection. Dans le tableau, la colonne E est à exclure puisqu'il s'agit de pourcentages. Nous devons donc sélectionner les colonnes C et D situées juste avant. Puis, nous devons ajouter dans la sélection les colonnes F et G situées juste après. Enfin, nous ne devons pas oublier d'ajouter le calcul de synthèse, soit la cellule G16. Rappelez-vous, c'est la touche CTRL du clavier qui permet de réaliser des multi-sélections.

• Sélectionner tout d'abord la plage de cellules C5:D14,
• Tout en maintenant la touche CTRL enfoncée, sélectionner la plage de cellules F5:G14,
• Toujours avec la touche CTRL enfoncée, cliquer sur la cellule G16,
• Puis, dans la section Nombre du ruban Accueil, cliquer sur Format Nombre Comptabilité,

Toutes les valeurs regroupées dans la sélection apparaissent instantanément formatées en Euros. Cette mise en forme est bénéfique pour la clarté et la compréhension du tableau.

Le résultat de synthèse, en bas du tableau, doit bien sûr être mis en évidence. Les hauteurs de lignes ont été réglées sur 21,6 pt. Nous devons reproduire ce réglage sur la ligne 16.

• Sélectionner par exemple le résultat de synthèse en G16,
• Dans la section Cellules du ruban Accueil, juste à gauche du bouton Somme automatique, cliquer sur le bouton Format,
• Dans la liste, choisir Hauteur de ligne,

• Dans la petite boîte de dialogue qui suit, taper la valeur 21,6,
• Puis, valider par Ok,

La hauteur de ligne est effectivement augmentée. Mais désormais, les données apparaissent alignées en bas de leurs cellules. De plus, d'autres attributs de mise en forme doivent être ajoutés pour les faire ressortir. Plutôt que d'enchaîner plusieurs actions, nous pouvons récupérer et répliquer ce qui existe. Le titre de la colonne G en G4 est déjà mis en forme.



Tout à fait à gauche du ruban Accueil, il existe un outil précieux. Il se nomme Reproduire la mise en forme. Il est matérialisé par un petit pinceau. Il consiste à répliquer tous les attributs de mise en valeur d'une cellule, sur une autre. Voyons comment l'exploiter :

• Sélectionner la cellule déjà correctement formatée, soit G4,
• Cliquer sur le bouton du pinceau à gauche du ruban Accueil, pour prélever sa mise en forme,

Comme vous le constatez, le curseur de la souris se transforme en une croix blanche accompagnée du symbole d'un pinceau.

• Cliquer désormais sur la cellule devant recevoir ces attributs, soit F16,

Instantanément, l'alignement est transformé. La cellule récupère une couleur de fond. Le texte est grossi et la couleur de police est adaptée. De la même manière, il s'agit de récupérer des attributs adaptés pour les appliquer au résultat de synthèse en G16. Une cellule numérique de la colonne G fera l'affaire.

• Sélectionner la cellule G14 par exemple,
• Cliquer sur le bouton du pinceau à gauche dans le ruban Accueil,
• Cliquer alors sur la cellule de synthèse en G16,

Grâce à cette précieuse technique, nous avons donc optimisé les opérations de mise en forme, de manière à ne pas devoir toutes les refaire.

Indicateurs et signaux dynamiques
Un tableau Excel est performant lorsqu'il est animé par des calculs dynamiques, comme ici. Il peut dès lors être utilisé comme un modèle pour une exploitation sur d'autres périodes ou par d'autres entreprises. La modification des données numériques implique le recalcul automatique et la mise à jour des résultats de formules.

Mais un tableau Excel est d'autant plus performant lorsqu'il est animé par des indicateurs de couleurs qui réagissent eux aussi dynamiquement. Tous les réglages de mise en forme que nous avons réalisés précédemment sont statiques. Une fois qu'ils sont placés, ils ne bougent plus. Ils ne peuvent donc pas réagir en fonction des données du tableau.

C'est pourquoi Excel propose la fonctionnalité de mise en forme conditionnelle. Elle permet de faire ressortir des cellules en fonction de leurs valeurs. De fait, si les valeurs évoluent, les couleurs peuvent changer de destinataire. Nous proposons par exemple de faire ressortir dynamiquement les deux meilleures ventes du mois d'une part et d'autre part, les deux meilleures rémunérations. Comme pour une mise en forme classique, il convient dans un premier temps de sélectionner la plage de cellules concernée.

• Sélectionner tout d'abord les ventes du mois, soit la plage de cellules F5:F14,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste qui apparaît, pointer sur Règles des valeurs de plage haute/basse,
• Dans le sous menu qui s'affiche, cliquer sur 10 valeurs les plus élevées,

Bien sûr, nous souhaitons mettre en évidence, seulement les deux meilleures ventes et non les dix meilleures. Mais il s'agit d'une règle générale à paramétrer.

• Dans la boîte de dialogue qui suit, remplacer la valeur 10 par le chiffre 2,
• Avec la liste déroulante, choisir une couleur verte à la place du rouge,

Instantanément, les effets sont retranscrits sur les cellules sélectionnées, bien que la boîte de dialogue ne soit pas encore validée. Ce procédé rend ainsi parfaitement compte des réglages dynamiques opérés.

• Cliquer sur le bouton Ok pour valider cette première règle,

Les deux meilleures ventes ressortent du lot de façon évidente. Elles attirent l'oeil. Cette mise en valeur dynamique est donc précieuse pour tirer des interprétations immédiates. Cette conclusion n'était pas évidente à atteindre aussi rapidement, à la première lecture du tableau.

• Sélectionner maintenant tous les salaires, soit la plage de cellules G5:G14,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste qui apparaît, pointer sur Règles des valeurs de plage haute/basse,
• Dans le sous menu qui s'affiche, cliquer sur 10 valeurs les plus élevées,
• Dans la boîte de dialogue qui suit, remplacer la valeur 10 par le chiffre 2,
• Avec la liste déroulante, choisir une couleur verte à la place du rouge,
• Puis, valider ces réglages dynamiques avec le bouton Ok,

Cette mise en valeur dynamique est déjà riche d'enseignements. Nous constatons en effet que le deuxième meilleur salaire ne correspond pas à la deuxième meilleure vente. En ligne 9, le vendeur Céhef bénéficie en effet d'un salaire fixe largement supérieur à la moyenne. Les résultats du vendeur Galls en ligne 10 ne suffisent pas à combler cet écart. Vérifions maintenant que ces réglages sont bien dynamiques.

• Sélectionner la cellule F7 des ventes pour le commercial Rouana,
• A la place du résultat 15100, saisir 35200 et valider,

En modifiant le résultat du vendeur Rouana, le calcul de son salaire s'est dynamiquement actualisé. De fait, la mise en forme conditionnelle a dynamiquement réagi sur ce deuxième meilleur score. La couleur s'est déplacée automatiquement. N'oublions pas l'actualisation dynamique du résultat de synthèse en bas du tableau. Nous avons donc bâti un modèle Excel parfaitement dynamique et particulièrement ergonomique quant à l'interprétation des données.