Classement des résultats par calculs dynamiques Excel

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


Sujets que vous pourriez aussi aimer :


Classer les résultats par calculs

Pour un grand nombre d'entreprises, il est important de pouvoir effectuer des classements dynamiques sur des données numériques. Il peut s'agir de comparer les ventes réalisées par produits, des articles ou pages Web les plus populaires ou encore d'évaluer les performances des commerciaux. Dans tous les cas, ces classements permettent des comparaisons salvatrices pour la pérennité de l'entreprise. Les interprétations sont souvent sans appel afin de réajuster les stratégies.



Dans l'exemple illustré par la capture ci-dessus, l'entreprise exploite un tableau Excel complètement automatisé pour comparer les performances annuelles de ses commerciaux. Une mise en forme conditionnelle permet de visualiser le classement, mois par mois, selon des indicateurs de couleur. Une synthèse globale est réalisée à la fin du cycle pour déterminer le classement général selon les résultats globaux obtenus. La société peut donc en tirer les conséquences et agir en âme et conscience.

Source et présentation de la stratégie
Pour monter une telle application, nous avons besoin de manipuler une grande quantité de données, relatant les ventes réalisées par les commerciaux d'une entreprise, tout au long de l'année.

• Télécharger le classeur classement-resultats-commerciaux.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Le classeur Excel est constitué de deux feuilles. La première feuille se nomme Synthèse. Elle résume les ventes réalisées par chacun des commerciaux de l'entreprise, tout au long de l'année. Le suivi a été effectué au coup par coup, si bien que les données méritent d'être regroupées et consolidées par vendeur pour y voir plus clair.

La seconde feuille se nomme Classement. Elle est illustrée par la capture ci-dessous. Elle propose la structure du tableau de bord dans lequel l'entreprise souhaite dans un premier temps, consolider les chiffres d'affaires réalisés chaque mois, par chacun des commerciaux. En fonction de ces résultat, l'objectif est de bâtir une synthèse dynamique et automatisée qui révèle les écarts pour permettre les comparaisons en établissant des classements notamment.

Ainsi la société pourra facilement évaluer les performances de ses salariés sur une échelle de temps suffisamment convaincante. Les interprétations et prises de décisions s'en trouveront grandement facilitées. Un graphique en Radar mettant en évidence les écarts doit venir corroborer cette synthèse Excel.



Cette application est l'occasion d'exploiter la précieuse fonction Rang qui permet de réaliser des classements en comparant chaque donnée, avec l'ensemble des valeurs de son groupe.

Consolider les chiffres d'affaires
La synthèse consiste dans un premier temps à regrouper tous les chiffres par commercial et par mois. Le premier des vendeurs proposés se nomme Céhef. Pour ce dernier, toutes les ventes qu'il a réalisées doivent être sommées pour le mois de Janvier, Frévier, etc..., jusqu'au dernier mois de l'année. Il en va de même pour les trois autres. Ce calcul doit être dynamique. Selon la philosophie du tableur Excel, il doit pouvoir être répliqué sur l'ensemble des lignes et colonnes. Les sommes dépendent de deux critères : Le nom du commercial et le mois de l'année. Il existe la fonction Excel Somme.Si.Ens qui permet de réaliser des additions selon plusieurs critères énumérés. Sa syntaxe est la suivante :

=SOMME.SI.ENS(plage_somme; plage_critère1; critère1; ... ; plage_critèreN; critèreN)

La plage de cellules sur laquelle nous devons réaliser les additions est la colonne D de la feuille Synthèse, celle des chiffres d'affaires des commerciaux. En désignant la colonne par son étiquette, nous bâtissons des calculs dynamiques, capables de considérer les potentielles nouvelles saisies qui se grefferaient à la suite du tableau.

Deux plages doivent servir à vérifier les critères permettant le déclenchement de la somme conditionnelle. Il y a tout d'abord la colonne C de la feuille Synthèse pour établir la correspondance avec le nom du commercial. Puis il y a la colonne A dans laquelle vous remarquez la présence discrète de numéros. Il s'agit de la conversion en nombre du mois de chacune des dates, pour établir la correspondance avec les mois du tableau de la feuille Classement.

C'est la fonction Excel Mois qui permet de traduire un mois en numéro. C'est ce qu'illustre la capture ci-dessous.



Les hypothèses du problème étant posées, il s'agit de réaliser le calcul dynamique à répercuter sur l'ensemble du tableau de la feuille classement.

• Cliquer sur la cellule C5 de la feuille Classement pour la sélectionner,
• Taper le symbole = pour débuter le calcul,
• Saisir la fonction d'addition multicritères suivi d'une parenthèse, soit Somme.Si.Ens(,
• Cliquer sur l'onglet Synthèse en bas de la fenêtre Excel pour activer sa feuille,
• Désigner la colonne D ou la saisir, ce qui donne Synthèse!D:D,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule (;) pour passer à la première plage de critère,
• Cliquer sur l'étiquette de colonne A ou la saisir, ce qui donne Synthèse!A:A,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule (;) pour passer à l'argument du critère à vérifier,
• Saisir la fonction convertissant une date en numéro de mois, suivi d'une parenthèse, soit Mois(,
• Cliquer sur l'onglet Classement en bas de la fenêtre Excel pour activer sa feuille,
• Désigner ou saisir la première case du mois, soit la cellule B5,
• Enfoncer trois fois la touche F4 pour la figer seulement en colonne, soit : Classement!$B5,
• Fermer la parenthèse de la fonction Mois,
• Taper un point-virgule (;) pour passer à la nouvelle plage de critère,
• Cliquer sur l'onglet Synthèse en bas de la fenêtre Excel pour activer sa feuille,
• Désigner la colonne C des vendeurs et la figer, ce qui donne Synthèse!$C:$C,
• Taper un point-virgule (;) pour passer à l'argument du critère à vérifier sur cette plage,
• Cliquer sur l'onglet Classement en bas de la fenêtre Excel pour revenir sur sa feuille,
• Sélectionner le premier commercial, soit la cellule C4,
• Enfoncer deux fois la touche F4 pour ne la figer qu'en ligne, ce qui donne Classement!C$4,
• Fermer la parenthèse de la fonction Somme.Si.Ens et valider la calcul par CTRL + Entrée,

Cette combinaison de touches permet de valider une formule tout en conservant la cellule active. L'objectif est bien sûr de l'exploiter dans la foulée.

• Tirer la poignée du calcul sur la droite jusqu'au vendeur Houda en colonne F,
• Puis tirer la poignée de la sélection jusqu'au mois de Décembre, en ligne 16,

Comme nous avons judicieusement figé les références des cellules et des plages dans ce calcul conditionnel, nous avons pu le répercuter sur l'ensemble du tableau. Nous allons revenir sur ces points de détail.



La formule que nous avons construite et qui est illustrée par la capture ci-dessus, est la suivante :

=SOMME.SI.ENS(Synthèse!$D:$D; Synthèse!$A:$A; MOIS(Classement!$B5); Synthèse!$C:$C; Classement!C$4)

Les colonnes D, A et C ont été figées pour pouvoir répliquer le calcul sur les colonnes de droite, pour les autres commerciaux, sans les déplacer. La cellule B5 désignant le mois à convertir a été figée seulement en colonne pour être déplacée vers le bas, afin de considérer tous les mois de l'année. En revanche, la cellule C4 du vendeur a seulement été figée en ligne pour être déplacée vers la droite, afin de considérer les autres vendeurs par réplication. Le préfixe Classement peut être supprimé dans cette formule. Il désigne en effet la feuille sur laquelle le calcul est bâti. Il n'est donc pas nécessaire et la formule s'en trouve simplifiée :

=SOMME.SI.ENS(Synthèse!$D:$D; Synthèse!$A:$A; MOIS($B5); Synthèse!$C:$C; C$4)

La synthèse dynamique est désormais bâtie et peut servir de modèle de suivi pour toutes les années à venir. En effet, si les données fluctuent et sont mises à jour dans le tableau de la feuille Synthèse, les résultats de celui de la feuille Classement, s'adaptent automatiquement.

Classement des résultats par indicateurs de couleur
Les résultats que nous venons d'obtenir, bien que consolidés, demeurent assez denses et donc, difficiles à interpréter du premier coup d'oeil. C'est la raison pour laquelle, nous souhaitons établir un classement visuel des commerciaux, pour chaque mois de l'année. En fonction de la légende proposée sur la droite de la feuille, nous souhaitons attribuer un code couleur à chaque commercial selon son résultat comparé à ceux des autres. C'est la mise en forme conditionnelle qui doit permettre de comparer dynamiquement ces données. Le principe consiste à vérifier un critère qui déclenche automatiquement une mise en valeur. Le critère est quadruple, pour chaque mois :

• Si le classement du commercial est la première place, il doit ressortir en vert,
• Si le classement du commercial est la deuxième place, il doit ressortir en bleu,
• Si le classement du commercial est la troisième place, il doit ressortir en jaune,
• Si le classement du commercial est la quatrième place, il doit ressortir en orange,

C'est la fonction Excel Rang qui permet d'établir ces classements. Sa syntaxe est la suivante :

=Rang(Valeur_a_classer ; tableau_de_référence ; Ordre_de_tri)

La valeur à classer est le chiffre d'affaires synthétisé du commercial, soit la cellule C5 pour le premier. Le tableau de référence correspond à l'ensemble des valeurs par rapport auxquelles établir ce classement. Il s'agit donc de l'ensemble des chiffres réalisés. Soit la plage C5:F5 pour la première comparaison. L'ordre de tri est facultatif. S'il n'est pas précisé, le classement est réalisé du plus grand au plus petit. C'est ce que nous souhaitons, donc nous l'omettrons volontairement. En figeant correctement les références impliquées dans le critère du format dynamique, nous serons à même de répercuter cette mise en valeur sur tout le tableau.

• Sélectionner l'ensemble des chiffres consolidés, soit la plage C5:F16,
• 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...,
• Puis cliquer dans la zone de saisie qui apparaît juste en dessous,
• Taper le symbole = pour débuter le calcul du critère dynamique,
• Saisir la formule suivante :

=rang(C5;$C5:$F5)=1

Le principe consiste, pour chaque mois de l'année, à comparer le chiffre de chaque commercial avec les chiffres réalisés par l'ensemble des commerciaux. C'est pourquoi nous figeons le tableau de référence seulement en colonne ($C5:$F5). Ainsi pour chaque mois de l'année, il fera référence aux lignes du dessous, tout en restant borné au tableau dans sa largeur. L'égalité ici consiste à vérifier si le résultat du commercial est le premier.

• Cliquer alors sur le bouton Format en bas de la boîte de dialogue,
• Dans la nouvelle boîte qui apparaît, activer l'onglet Remplissage,
• Dans les couleurs de fond, sélectionner un vert assez pâle,
• Valider ce choix par Ok,
• De retour sur la première boîte de dialogue, cliquer de nouveau sur Ok,

Instantanément les premières mises en valeur surgissent et les premières interprétations tombent. Tandis que le dernier commercial (Houda) n'a jamais remporté la palme du meilleur chiffre au mois, le vendeur Galls semble être celui qui s'est classé le plus souvent premier. Pour mettre en place les autres indicateurs visuels, en respectant la légende, il suffit de modifier simplement l'égalité sur la même plage de cellules, tout en adaptant les couleurs de remplissage. Le bleu pour le 2, le jaune pour le 3 et le orange pour le 4.

• Selon ce principe, bâtir les trois autres règles de mise en forme conditionnelle,

La capture ci-dessus illustre les quatre règles cumulées sur la plage de cellules du tableau des commerciaux. Désormais, pour chaque mois de l'année, apparaissent en couleurs les résultats classés dans l'ordre décroissant. Comme les sommes conditionnelles sont parfaitement dynamiques et que la mise en forme construite sur ces résultats est elle-même dynamique, si nous modifions les saisies dans le tableau de la feuille Synthèse, tous les indicateurs visuels de classement se mettent automatiquement à jour. Le modèle peut donc être répliqué et exploité pour les autres années et pour toute entreprise.

Classement final consolidé
Nous sommes parvenus à réaliser le classement des commerciaux pour chaque étape de l'année. Nous souhaitons désormais réaliser une synthèse permettant d'établir une hiérarchie sur l'ensemble des résultats consolidés. En d'autres termes, nous souhaitons établir un classement des commerciaux pour l'année écoulée. Les couleurs dynamiques correspondantes doivent être appliquées directement sur leur nom, en ligne 4. Il s'agit pour cela de savoir celui qui est ressorti le plus grand nombre de fois premier, deuxième et ainsi de suite.

C'est la raison pour laquelle figure un petit tableau de classement général entre les lignes 18 à 22. Il s'agit de comptabiliser chacun des classements pour chacun des commerciaux. Et pour cela, nous avons besoin de retranscrire les résultats de ces couleurs dynamiques en chiffres, dans un tableau de calculs intermédiaires, entre les colonnes N et Q.

• Sélectionner la cellule N5 et taper le symbole = pour débuter la formule,
• Saisir la fonction de classement suivie d'une parenthèse, soit Rang(,
• Cliquer sur le premier résultat du premier vendeur, soit la cellule C5,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de référence,
• Sélectionner la première rangée des résultats consolidés, soit la plage C5:F5,
• Enfoncer trois fois la touche F4 pour figer cette plage en colonne seulement,
• Fermer la parenthèse de la fonction Rang et valider le calcul par CTRL + Entrée,
• Tirer la poignée de la formule sur la droite, jusqu'en colonne Q,
• Puis tirer la poignée de la sélection jusqu'en ligne 16,

La formule que nous avons tapée, dont les résultats sont illustrés par la capture ci-dessous, est la suivante :

=RANG(C5;$C5:$F5)



Ces résultats corroborent parfaitement les formats dynamiques appliqués sur le tableau source et faisant ressortir les classements par couleur. Pour que chaque chiffre d'affaire soit comparé à l'ensemble des résultats du mois, nous avons laissé la plage se déplacer en ligne avec le calcul. Mais pour que nous puissions répliquer la formule sur la droite, il a été nécessaire de le figer en colonne.

Pour la représentation graphique finale qui permettra de rendre compte de la dispersion générale des résultats obtenus par les commerciaux, nous avons besoin de totaliser ces classements.

• Sélectionner la plage de cellules N5:Q17,
• Cliquer sur le bouton Somme automatique du ruban Accueil ou réaliser le raccourci ALT + =,

En incluant les cellules du résultat dans la plage des valeurs à additionner, le calcul de la somme est automatique, sans étape intermédiaire. Grâce à cette astuce, Excel sait en effet où placer les sommes.

Ce résultat est assez intéressant car il permet une interprétation qui nous avait échappée jusqu'alors. Nous savons que le meilleur commercial est celui qui obtient le score le moins élevé, puisqu'il s'agit de l'addition des classements. Grâce aux indicateurs de couleurs, nous savons aussi que le vendeur Galls est celui qui s'est placé le plus souvent à la première place. Et pourtant, le score le moins élevé est celui du commercial Hamalibou. Il semblerait donc que ce dernier ait été encore plus régulier en se classant très souvent deuxième et rarement dernier.

Il est désormais possible de réaliser la synthèse des classements, en bas du tableau source, entre les lignes 19 et 22. Il s'agit pour chaque rang, de comptabiliser le nombre de fois qu'un commercial s'y est classé. Ainsi, celui qui obtiendra le meilleur score pour le rang 1 sera considéré comme le meilleur commercial. Les autres positions en découleront. De fil en aiguille, nous pourrons bâtir une nouvelle mise en forme conditionnelle sur ces résultats, pour faire ressortir les noms des commerciaux en couleur, selon leur classement. La fonction Excel qui permet de comptabiliser un critère sur une plage de cellules est la fonction Nb.SI, selon la syntaxe suivante :

=Nb.Si(Plage_de_cellules ; Critere_à_compter)

Par exemple, pour le vendeur Céhef, la plage de cellules dans laquelle les rangs doivent être comptabilisés, est la plage N5:N16. Le premier de ces rangs à compter est la valeur 1 inscrite en B19. L'objectif, comme toujours, est de bâtir un calcul dynamique, adaptable à toutes les cellules.

• Sélectionner la cellule C19 et taper le symbole = pour débuter le calcul,
• Saisir la fonction de dénombrement suivi d'une parenthèse, soit nb.si(,
• Sélectionner tous les classements du premier vendeur, soit la plage N5:N16,
• Enfoncer deux fois de suite la touche F4 pour ne figer que les lignes,

En effet, la formule doit concerner tous les commerciaux, soit les plages des colonnes qui suivent. En revanche, ces classements sont réalisés sur une hauteur fixe correspondant aux douze mois de l'année. Les plages ne doivent pas changer en ligne (N$5:N$16).

• Taper un point-virgule (;) pour passer dans l'argument du critère à dénombrer,
• Sélectionner ou saisir les références de la cellule B19,
• Enfoncer trois fois de suite la touche F4 pour ne la figer qu'en colonne, soit $B19,

Pour chaque rang, chaque commercial est concerné lorsque la formule est répliquée sur la droite. Mais chaque rang doit bien être compté pour chaque commercial, lorsqu'elle est reproduite vers le bas.

• Fermer la parenthèse de la fonction Nb.Si,
• Valider la formule par CTRL + Entrée,
• Tirer la poignée du calcul jusqu'au vendeur Houda, soit jusqu'à la colonne F,
• Puis tirer la poignée de la sélection jusqu'au dernier rang situé en ligne 22,

Nous obtenons la synthèse de tous les classements cumulés pour chaque vendeur. La formule que nous avons créée, est la suivante :

=NB.SI(N$5:N$16;$B19)



Désormais, pour obtenir la mise en forme du classement final et global pour chaque vendeur, il s'agit de déterminer quel est le commercial qui obtient le plus grand score pour chaque rangée. C'est donc la fonction Excel Max qui va nous permettre de construire le critère de la mise en forme conditionnelle.

• Sélectionner la ligne de titre du tableau, soit la plage C4:F4,
• 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 type : Utiliser une formule pour...,
• Cliquer dans la zone de saisie du critère située juste en dessous,
• Taper la formule suivante pour le critère :

=C$19=max($C$19:$F$19)

En défigeant la colonne de la cellule C19, chaque résultat sera passé en revue sur la ligne, pour connaître celui qui a obtenu le meilleur score pour le rang 1.

• Cliquer sur le bouton Format et activer l'onglet Remplissage de la boîte de dialogue qui suit,
• Choisir un fond vert légèrement plus intense que celui de la légende,
• Valider les deux boîtes de dialogue par Ok,

Instantanément, le meilleur commercial de l'année apparaît dans la mise en valeur dynamique ainsi définie. Il s'agit de réitérer exactement le même processus pour la plage des commerciaux, sur les rangs suivants. Donc le critère doit être posé sur les lignes du dessous avec les couleurs associées, comme suit :

• =C$20=MAX($C$20:$F$20) → Fond bleu,
• =C$21=MAX($C$21:$F$21) → Fond jaune,
• =C$22=MAX($C$22:$F$22) → Fond orange.

Les décisionnaires n'ont plus qu'à se servir. D'un seul coup d'oeil les résultats tombent. Le meilleur commercial apparaît instantanément et explicitement. Celui ayant obtenu les moins bons scores ne peut pas être manqué. Les interprétations coulent de source.

Il reste à renforcer ces résultats de synthèse avec un graphique des écarts à l'appui. Le type Radar rend facilement compte de la dispersion des résultats. Et nous le verrons, le Vendeur Houda sera mis à l'honneur, malheureusement pour le desservir.

• Sélectionner les noms des commerciaux sur le tableau de droite, soit la plage N4:Q4,
• Avec la touche CTRL enfoncée, sélectionner leur sommes, soit la plage N17:Q17,
• Cliquer sur l'onglet Inserer en haut de la fenêtre Excel pour activer son ruban,
• Cliquer sur le bouton Insérer un graphique en surface ou en radar,
• Choisir le premier type dans la section Radar,
• Supprimer le titre du graphique,
• A l'aide de son ruban Format contextuel, supprimer le contour du graphique,
• De la même façon, supprimer le remplissage du graphique
• Puis le redimensionner et le placer pour qu'il tienne dans le cadre prévu à cet effet,

Vous en conviendrez, le vendeur Houda sort du lot, non pas pour l'excellence de ses résultats mais pour l'inverse. Il est celui qui est situé le plus à l'écart de l'épicentre du graphique, celui donc pour lequel, le score est le plus élevé, relatant des classements cumulés médiocres.