Opérations Dans Un Tableau Croisé Dynamique
Les opérations dans un tableau croisé dynamique sont très variées. A partir des paramètres des champs de valeurs, il est possible de faire les opérations suivantes : moyenne, addition d’unités, différence, cumul de données, pourcentage, etc. Mieux encore, les opérations dans un tableau croisé dynamique permettent également de créer des champs calculés.
Soit le tableau initial
On crée un simple tableau croisé dynamique
Tout simplement, en lignes : représentants
et en somme : les ventes
Calcul de la moyenne des ventes de chaque représentant
On clique sur somme des ventes et on sélectionne « Paramètres des champs de valeurs … »
Une boite de dialogue apparaît : Paramètres des champs de valeurs
Dans l’onglet, « synthèse des valeurs« , on sélectionne MOYENNE, puis on clique sur OK
On obtient alors le tableau croisé dynamique suivant :
Calcul des unités de produits vendues par chaque représentant
On créé un nouveau tableau croisé dynamique avec :
– lignes : produits
– somme des valeurs : nombre de représentants
Tout naturellement, Excel calcule le nombre de fruits vendus par les représentants. Or, ce qui est mis en somme de valeurs, ce ne sont pas des chiffres mais des lettres, des noms : ici, les représentants.
Calcul des catégories de produits vendues par chaque représentant
On voit dans notre mini-exemple que l’on a six représentants : Blanc, Dupont, Europe, Marie, Martin et Pierre.
Imaginons maintenant que l’on souhaite avoir uniquement que le nom des représentants qui ont vendu tel ou tel produit.
Prenons l’exemple suivant : on a vu que pour le produit fruits, Excel recense 4 représentants. Qui sont-ils ? Pour ce faire, on double clique sur la cellule B4 qui correspond au chiffre 4
Une nouvelle feuille de calcul apparaît
On voit que Marie est mentionnée deux fois, Europe et Martin une seule fois.
Comment faire apparaître dans mon tableau dynamique la réponse suivante : Trois représentants ont vendu des fruits : Europe, Marie et Martin.
On va alors créer un nouveau tableau croisé dynamique : Insertion/Tableau croisé dynamique.
Une boite de dialogue apparaît
Et on coche : ajouter ces données au modèle de données
Sans surprise, on obtient le même tableau que précédemment
Mais lorsque l’on va dans « Paramètres de champs de valeurs » :
Désormais, on a l’option « total distinct« , dans l’onglet « résumer le champ de valeurs par »
on clique sur OK.
Alors, on obtient le tableau croisé dynamique suivant avec le type de produits vendus par chaque représentant.
Voyons la différence de résultat en guise de comparaison
Comment calculer des pourcentages dans un tableau croisé dynamique ?
On créé un nouveau tableau croisé dynamique : Insertion/Tableau croisé dynamique
et on sélectionne :
– en lignes : représentants
– en somme de valeurs : somme des ventes
Ici, ce qui nous intéresse, c’est de savoir la part des ventes du représentant Blanc (10480) sur le total des ventes (87220).
Pour faire apparaître notre résultat dans le tableau croisé dynamique, on rajoute une troisième colonne. On a donc :
– en somme de valeurs : somme des ventes à nouveau
Sur « somme des ventes 2 », on fait clic gauche souris pour sélectionner « Paramètres de champs de valeurs« . On clique sur l’onglet « Afficher les valeurs » et on sélectionne « % du total de la colonne« .
Puis on clique sur OK
On obtient alors le tableau dynamique suivant :
Pour répondre à notre question, le représentant Blanc contribue pour 12% au total des ventes.
On peut ensuite customiser son tableau. Par exemple :
– au lieu d’avoir somme des ventes 2, on peut inscrire dans la barre : pourcentage des ventes
– au lieu d’avoir les résultats avec deux décimales, on peut décider de ne pas en avoir du tout.
Pour ce faire, on va dans Paramètres des champs de valeurs comme précédemment, après avoir sélectionné « Pourcentage des ventes », puis, on clique sur format de nombre.
Une boite de dialogue apparaît : format de cellule. Et on sélectionne pourcentage avec un nombre de décimales zéro, puis on clique sur OK. On obtient alors le résultat suivant :
Comment calculer une différence de valeurs dans un tableau croisé dynamique ?
Comme précédemment, on ajoute une seconde fois somme des ventes dans la somme des valeurs pour inscrire le résultat correspondant recherché.
On veut ici savoir la différence de ventes entre deux représentants. Dans notre cas, on recherche la différence entre Blanc (10480) et Dupont (19400)
– on fait un clic gauche souris sur somme des valeurs 2 pour sélectionner « paramètres des champs de valeurs »
– puis on clique sur l’onglet : afficher les valeurs
– puis on sélectionne : différence par rapport
Une boite de dialogue apparaît. On veut comparer les représentants par rapport à Blanc pour voir s’ils performent mieux ou pas par rapport à Blanc.
Puis on clique sur Ok pour obtenir le tableau dynamique suivant :
Ainsi, entre Blanc et Dupont, la différence est de 8920. De même, la différence entre Blanc et Europe est de 6337.
Comme précédemment, on peut customiser son tableau en changeant le nom de la colonne : différence des ventes par rapport à Blanc. Et on peut ajouter les valeurs en monnaie.
– on clique alors sur « différence des ventes par rapport à Blanc » pour faire apparaître la boite de dialogue : paramètres des champs de valeurs.
– puis on clique sur format de nombre
– une boite de dialogue apparaît : format de cellule
On clique alors sur monétaire. On sélectionne zéro décimale, la devise, l’euro et en cas de différence négative, la couleur rouge. Puis on clique sur OK
On obtient alors le tableau dynamique suivant :
Comment calculer une différence de valeurs en pourcentage dans un tableau croisé dynamique ?
Imaginons maintenant que l’on souhaite obtenir la différence calculée ci-dessus en pourcentage. Autrement dit, dans notre exemple, on se demande quel est le pourcentage de différence entre Blanc et les autres représentants ?
– on fait clic gauche sur différence des ventes par rapport à Blanc pour faire apparaître « Paramètres de champs de valeurs »
– on sélectionne l’onglet « Afficher les valeurs »
– puis on clique sur « Différence en % par rapport »
Enfin, on clique sur OK et on obtient le tableau croisé dynamique suivant :
Ainsi, le représentant Dupont a accru ses ventes de 85% en plus par rapport au représentant Blanc. Mais ce dernier a enregistré une meilleure performance que Pierre (3,82%).
Comme précédemment, on peut customiser les pourcentages :
– pas de décimales
– en cas de résultat négatif, écrire en rouge.
On clique gauche sur « différence des ventes par rapport à Blanc » pour faire apparaître paramètres de champs de valeurs.
– on clique sur format de nombre
– et dans la boite de dialogue format de cellule, on sélectionne pourcentage avec la customisation souhaitée. Le problème est que l’on peut seulement sélectionner le nombre de décimales, mais pas la couleur du pourcentage.
Alors, on doit sélectionner « personnalisée ». Et on écrit 0% ; [rouge]- 0%
La formule personnalisée comprend ainsi deux choix :
– si le pourcentage est positif, alors on veut aucune décimale : 0%
– si le pourcentage est négatif, alors on veut que le pourcentage soit écrit en rouge. Donc, on écrit rouge entre crochets, suivi du signe moins, et comme précédemment, aucune décimale, soit 0%.
D’où le nouveau tableau croisé dynamique
Comment calculer le cumul de données au cours du temps ?
Dans notre exemple, on voit que cette société vend des produits depuis 2014. Or, ici, la question que l’on souhaite se poser, c’est comment calculer le cumul des ventes entre 2014 et 2019 ?
On créé un nouveau tableau croisé dynamique : Insertion/Tableau croisé dynamique.
- En lignes : les années
- En somme de valeurs : la somme des ventes
Puis, on créé une troisième colonne pour traiter le cumul des ventes. On a alors à nouveau en somme de valeurs : la somme des ventes 2
On obtient alors le tableau croisé dynamique suivant :
Comme d’habitude, on fait clic gauche sur somme des ventes 2 pour obtenir les paramètres de champs de valeurs.
– on clique sur l’onglet : afficher les valeurs
– on sélectionne : résultat cumulé par
puis on clique sur OK avec pour champ de base année. On obtient le tableau croisé dynamique suivant :
Bien sûr, on peut customiser le tableau en changeant, par exemple, le nom des colonnes.
Comment traiter un champ calculé dans un tableau croisé dynamique ?
Pour l’instant, nous avons vu comment faire des opérations dans un tableau croisé dynamique à partir de données existantes. Désormais, on va faire des calculs à partir des données du tableau.
Prenons le cas du calcul de la TVA, soit 20% en France, le taux général appliqué aux ventes. Dans notre exemple, on suppose alors que les ventes sont hors taxes.
On crée alors un tableau croisé dynamique : Insertion/Tableau croisé dynamique avec :
- Lignes : représentants
- Somme de valeurs : les ventes
En haut, dans analyse du tableau croisé dynamique, on clique sur champ calculé c’est-à-dire dans onglet « calcul« , puis « champ, éléments et jeux »
Une boite de dialogue apparaît : Insertion d’un champ calculé
On y inscrit ce que l’on veut y faire :
- Le nom : TVA
- La formule : = Ventes (que l’on sélectionne parmi les champs proposés en double-cliquant dessus) * 20%
Puis on clique sur Ajouter et Ok
Alors, le tableau croisé dynamique comprend maintenant trois colonnes :
Modification des champs calculés
Pourquoi est-ce un champ calculé ? Parce qu’il est possible dans les opérations d’un tableau croisé dynamique de changer la formule.
Imaginons que le taux de TVA diminue d’un point à 19%, alors il suffit de changer la formule dans le champ calculé :
– la formule : = Ventes* 19%
et de cliquer ensuite sur Modifier et OK
Et on obtient alors le tableau croisé dynamique suivant :
Imaginons maintenant que la TVA n’est appliquée que si les ventes sont d’un certain montant, par exemple, ici 15200. Sinon, l’entreprise ne paie aucune taxe.
Dans ce cas-là, on modifie à nouveau la formule avec l’utilisation de la fonction SI.
– la formule : = SI [Ventes > 15200 ; Ventes * 20% ; 0]
Si le montant de mes ventes sont supérieures à 15200 alors l’entreprise acquitte une taxe de 20%, sinon elle ne paie rien.
et on obtient alors ce tableau croisé dynamique :
Aucune taxe n’est appliquée sur les ventes des représentants Blanc et Pierre car le niveau des ventes est inférieur à 15200.
Conclusion
Ainsi, les opérations dans un tableau croisé dynamique permettent d’extraire une quantité non négligeable d’informations issues des données initiales ou calculées.
Pour approfondir les opérations dans un tableau croisé dynamique, voir : Office : calcul des valeurs
Je m'abonne !
