On a tous vécu ce moment en fin de mois : le tableau de suivi commercial est rempli, les chiffres de vente sont là, mais impossible de savoir en un coup d’œil si l’équipe a atteint ses objectifs. Le réflexe, c’est de sortir la calculatrice. La bonne pratique, c’est de calculer un pourcentage sur Excel directement dans le fichier de suivi, avec des formules qui se mettent à jour automatiquement.
La formule de base est simple : diviser le réalisé par l’objectif. Là où ça se complique, c’est quand on gère plusieurs lignes, plusieurs mois, ou que l’objectif change en cours de route. Cet article couvre les cas concrets que les tutoriels classiques survolent.
Lire également : Formule calcul âge Excel pour tableaux de bord : fiabilisez vos indicateurs RH
Référence absolue et formule pourcentage : le piège du copier-coller
Quand on calcule le pourcentage d’atteinte d’un objectif sur une seule ligne, la formule =B2/C2 suffit. On obtient un ratio, on formate en pourcentage, c’est réglé.
Le problème apparaît dès qu’on tire la formule vers le bas pour plusieurs commerciaux ou plusieurs produits, alors que l’objectif global se trouve dans une seule cellule (par exemple C1). Excel décale automatiquement la référence, et la formule pointe vers une cellule vide ou erronée.
A lire aussi : Comment ajouter un raccourci sur le bureau pour vos applis préférées ?
La solution tient en une touche : F4 pour verrouiller la référence avec le signe $. En écrivant =B2/$C$1, la cellule C1 reste fixe quand on copie la formule sur toute la colonne. On verrouille la ligne, la colonne, ou les deux selon le cas.
- Référence relative (
C1) : se décale à chaque ligne, utile quand chaque ligne a son propre objectif. - Référence absolue (
$C$1) : reste fixe, adaptée quand toutes les lignes partagent le même objectif global (chiffre d’affaires cible, seuil de rentabilité annuel). - Référence mixte (
$C1ouC$1) : verrouille uniquement la colonne ou la ligne, pratique dans les tableaux croisés manuels où on compare par mois en colonnes et par équipe en lignes.
Sans cette distinction, on se retrouve avec des pourcentages aberrants. C’est l’erreur la plus fréquente dans les fichiers de suivi d’objectifs, et la moins documentée dans les tutoriels grand public.
Feuille Paramètres Excel : centraliser vos objectifs mensuels
Quand les objectifs changent chaque mois (quota commercial, seuil de ventes, cible de chiffre d’affaires), les inscrire directement dans le tableau de suivi crée un fichier fragile. Il suffit d’une modification oubliée pour fausser tout le rapport.
L’approche recommandée consiste à créer une feuille dédiée « Paramètres » dans le classeur Excel. On y inscrit les objectifs par mois, par équipe ou par produit. Le tableau de suivi principal va chercher la bonne valeur avec une fonction de recherche comme RECHERCHEV (VLOOKUP).
Concrètement, si la feuille Paramètres contient en colonne A les mois et en colonne B les objectifs de vente, la formule dans le tableau de suivi ressemble à ceci : =RECHERCHEV(A2;Paramètres!A:B;2;FAUX). Cette cellule renvoie l’objectif du mois correspondant, et la formule de pourcentage =Réalisé/Objectif se met à jour automatiquement.
L’avantage est double. D’abord, on modifie un objectif à un seul endroit. Ensuite, le modèle reste utilisable d’une année sur l’autre en changeant simplement les valeurs dans la feuille Paramètres, sans toucher aux formules du tableau principal.
Pourcentage d’atteinte dans un tableau croisé dynamique Excel
Pour des données volumineuses (ventes par commercial, par région, par trimestre), le tableau croisé dynamique est l’outil naturel. Ce que beaucoup ignorent, c’est qu’on peut y afficher directement des pourcentages sans écrire de formule.
Dans un tableau croisé dynamique, un clic droit sur une valeur permet d’accéder à « Afficher les valeurs ». Les options les plus utiles pour vérifier des objectifs sont :
- « % du total général » : montre la contribution de chaque ligne au total, utile pour identifier quel commercial ou quel produit pèse le plus dans le chiffre d’affaires.
- « % du total de la colonne » : compare chaque valeur au total de sa colonne, pratique pour un suivi mensuel où chaque colonne représente un mois.
- « % du total de la ligne » : répartit le poids entre colonnes pour une même ligne, par exemple pour voir comment les ventes d’un produit se distribuent sur l’année.
Pour comparer le réalisé à un objectif fixe dans un tableau croisé dynamique, on ajoute un champ calculé. La manipulation se fait via l’onglet « Analyse du tableau croisé dynamique », puis « Champs, éléments et jeux ». On y définit une formule du type =Ventes/Objectif, et Excel calcule le pourcentage d’atteinte pour chaque croisement de données.
Les retours varient sur la lisibilité de cette méthode avec de très grands jeux de données, mais pour un suivi d’objectifs trimestriel ou mensuel classique, c’est la solution la plus rapide à maintenir.
Erreurs courantes dans le calcul de pourcentage sur Excel
Deux erreurs reviennent dans la majorité des fichiers de suivi qu’on rencontre en entreprise.
Mélange entre format pourcentage et valeur décimale
Quand on tape « 50 » dans une cellule puis qu’on applique le format pourcentage, Excel affiche 5000 %. Le logiciel interprète 50 comme 50 unités, soit 5000/100. Pour obtenir 50 %, il faut saisir 0,5 ou taper directement « 50 % » dans la cellule. Formater une cellule en pourcentage ne divise pas la valeur par 100, c’est un simple affichage.
Division par zéro quand l’objectif n’est pas encore renseigné
En début de période, certaines cellules d’objectif restent vides ou à zéro. La formule de pourcentage renvoie alors l’erreur #DIV/0!, qui se propage dans les graphiques et les totaux. On contourne le problème avec =SI(C2=0;"";B2/C2), qui affiche une cellule vide au lieu d’une erreur. Le rapport reste propre même quand toutes les données ne sont pas encore saisies.
Maîtriser le calcul de pourcentage sur Excel pour vérifier des objectifs ne demande pas de compétences avancées, mais exige de la rigueur sur trois points : verrouiller les références, centraliser les objectifs dans une feuille dédiée, et gérer les cas limites comme la division par zéro. Un fichier bien structuré dès le départ évite de reconstruire le tableau chaque trimestre.
