Excel affiche les sommes ou le dénombrement de vos valeurs par défaut dans ses tableaux croisés dynamiques.
Mais souvent, pour une analyse complète d’une activité, un budget…, nous avons besoins d’autres repères comme la part de chaque entrée (pourcentage) ou la variation sur le temps (écart).
Préparation du tableau croisé dynamique
Si vous voulez analyser la part de chaque élément ou sa variation par rapport à un second élément, il est plus facile d’abord d’afficher le(s) montant(s) en chiffres et d’un autre côté la ratio à utiliser (%, écart…).
C’est pour cela que nous allons rajouter une nouvelle colonne en valeurs.
Déplacez donc le champ Montant dans la zone Valeurs. Hé oui, on peut ajouter le même champ à plusieurs reprises dans la zone Valeurs.
Dans mon exemple, une nouvelle colonne apparaît pour chaque année.
En fait, l’ajout se fait pour chaque élément du champ mis dans la zone Colonnes. Si j’avais eu 10 années dans mes données, il y aurait 10 colonnes supplémentaires.
J’en profite pour retirer la colonne de Totaux Généraux via l’onglet Création car il n’est pas pertinent pour mon analyse
Pourcentage du total
Premier ratio que l’on peut utiliser, c’est le pourcentage du montant par rapport au montant total.
Quand je parle de montant total, il s’agit de la somme de tous les montants toutes années confondues.
- Passons aux manipulations. Cliquez sur l’une des cellules des nouvelles colonnes.
- Faites un clic-droit puis sélectionnez Paramètres des champs de valeurs…
Une nouvelle boîte de dialogue s’ouvre.
3. En premier lieu, on va personnaliser le titre de la colonne. Je vais l’appeler % / Total
4. Je laisse Somme car il va faire les sommes des ventes pour chaque mois.
5. Je me rends dans l’onglet Afficher les valeurs, et je remplace Aucun calcul de la liste déroulante par % du total général.
6. Je valide en cliquant sur OK.
Les valeurs des nouvelles colonnes sont passées en pourcentage.
Alors, peut-être que ce ratio n’est pas forcément pertinent quand il y a plusieurs colonnes (années dans mon exemple).
Ce ratio %/total est plus adapté quand il n’y a qu’une colonne de montants.
Ce n ‘est pas grave, on va changer ça.
Pourcentage de la colonne
- On retourne dans les Paramètres des champs de valeurs.
- Dans l’onglet Afficher les valeurs, on va sélectionner % du total de la colonne
- Avant de faire OK, je change le tire en % / Année.
Les pourcentages sont devenus plus cohérents. On peut dorénavant analyser ce qui s’e’st le mieux vendu chaque année.
Pourcentage du parent
Nouvelle analyse : je veux connaître les mois où chaque produit s’est bien vendu.
- On retourne dans les Paramètres des champs de valeurs.
- Dans l’onglet Afficher les valeurs, on va sélectionner % du total du parent
- En dessous, je sélectionne quel est mon champ parent, il s’agit de Produit
- Avant de faire OK, je change le tire en % / Mois.
Pour chaque mois, on a bien le pourcentage des ventes mensuelles d’un produit. Mais il y a un défaut, le pourcentage sur les produits sont à 100%.
Si vous voulez affiner encore plus, changez la valeur en % du total de la ligne parente.
Ainsi, nous gardons les parts mensuelles des ventes pour chaque produit mais nous y ajoutons la part de chaque produit sur le total des ventes.
Écart par année
Maintenant, on passe aux variations. Il est toujours intéressant de savoir si le produit se vend aussi bien qu’auparavant.
Mon premier ratio va comparer les ventes d’un mois d’une année sur l’autre (ex : janvier 2021 vs janvier 2022).
- Allez dans les Paramètres des champs de valeurs dans la colonne qui sert de ratios
- Renommez le champ en Ecart N / N-1
- Dans l’onglet Afficher les valeurs, on va sélectionner Différence en % par rapport
- En dessous, sélectionnez le champ Année dans la colonne de gauche puis comme on va comparer par rapport à l’an passé, on sélectionne (précédent) dans la colonne de droite
- Validez avec le bouton OK.
Vous pouvez constater ci-dessous que pour 2021, il n’y a aucun écart, c’est normal car nous n’avons aucune donnée de 2020.
Par contre, tout fonctionne parfaitement entre 2021 et 2022.
Écart mensuel
Autre méthode pour analyser la variation des ventes sur une période, c’est de comparer de mois en mois.
- Allez dans les Paramètres des champs de valeurs dans la colonne qui sert de ratios
- Renommez le champ en Ecart / mois précédent
- Dans l’onglet Afficher les valeurs, on va sélectionner Différence en % par rapport
- En dessous, sélectionnez le champ Période dans la colonne de gauche car Excel convertit les champs dates de ma base en mois puis sélectionnez (précédent) dans la colonne de droite
- Validez avec le bouton OK.
Il n’y a qu’un défaut à cette utilisation : pour chaque année, vous n’aurez pas la variation en janvier.
Pourquoi cela ?
Janvier étant le 1er mois de l’année, Excel ne cherche pas à comparer décembre N et janvier N+1.
Écart avec une valeur de référence
Cette fois-ci, on va s’intéresser à l’écart entre un mois référence car on considère qu’il y a eu une activité normale, face aux autres périodes. Dans notre exemple, nous prendrons juin comme référence.
- Allez dans les Paramètres des champs de valeurs dans la colonne qui sert de ratios
- Renommez le champ en Ecart / Juin
- Dans l’onglet Afficher les valeurs, on va sélectionner Différence en % par rapport
- En dessous, sélectionnez le champ Période dans la colonne de gauche puis sélectionnez juin dans la colonne de droite
- Validez avec le bouton OK.
Dans mon exemple, le mois de juin de chaque année n’a pas d’écart car il sert de référence.
On peut ainsi, analyser les fortes périodes d’activité et les périodes creuses sur l’année.
