Les tableaux croisés dynamiques, c’est facile !

Le tableau croisé dynamique, souvent appelés TCD (ou Pivot Table en anglais) est une des fonctionnalités les plus puissantes d’Excel. Elle permet de synthétiser des données brutes sous la forme d’un tableau élaboré et personnalisé. Nous allons voir dans cet article que son utilisation est beaucoup plus simple qu’il n’y paraît, et qu’elle s’avère vite indispensable lorsqu’il s’agit d’analyser des masses importantes d’informations avant de prendre une décision.

Tutoriel Vidéo

Partie 1 : Mon TCD en 6 clics (voir le tutoriel)

Partie 2 : Mon TCD enrichi : Champs calculés et Tri de colonne (voir le tutoriel)

Partie 3 : Regrouper des données dans mon TCD (voir le tutoriel)

Partie 4 : Séléctionner les données à afficher dans le TCD (voir le tutoriel)

Partie 5 : Comment utiliser les segments pour filtrer le TCD d'un seul clic (voir le tutoriel)

Partie 6 : Comment utiliser le filtre chronologique pour filtrer une date sur un TCD d'un seul clic (voir le tutoriel)

Partie 7 : Comment regrouper des données de même nature au sein d'un TCD (voir le tutoriel)

Partie 8 : Comment modifier ou ajouter des informations dans un TCD (voir le tutoriel)

Partie 9 : Comment personnaliser l'apparence d'un TCD (voir le tutoriel)

Partie 10 : Comment personnaliser l'apparence d'un TCD (voir le tutoriel)

Partie 11 : Des TCD inscroyables avec la mise en forme conditionnelle (voir le tutoriel)

Partie 12 : Alimenter un TCD à partir de plusieurs sources (voir le tutoriel)

Partie 13 : En combinant automatiquement les bases sources (voir le tutoriel)

Partie 14 : Les graphiques crosiés dynamiques (voir le tutoriel)

1. Les tableaux croisés dynamiques, pour quoi faire ?

Comme nous venons de le voir en préambule, un tableau croisé dynamique permet de représenter sous une forme synthétique et claire une table de données. Les bénéfices apportés par le tableau croisé dynamique sont nombreux :

• Regroupement de données suivant des catégories,
• Faire des opérations sur les données (sommes, comptages, …)
• Automatisation de totaux et de sous-totaux,
• Mises-en-forme élaborée,
• Actualisation simplifiée des données sources,
• Connexion à une base de données externe

 Commander le livre et les ressources

2. Votre premier TCD en six clics seulement

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

La création et l’utilisation d’un tableau croisé dynamique suppose que vous disposiez au préalable d’une table de données dans laquelle Excel puisera les données sources qui vont lui permettre d’alimenter le tableau croisé dynamique. La logique des tableaux croisés dynamiques est donc de séparer d’un côté les données en elles-mêmes et d’un autre côté leur présentation :

• Les données sont saisies sous la forme d’une table des données, c’est-à-dire un simple tableau en une seule dimension dans lequel chaque ligne représente un enregistrement. Pour être correctement utilisée comme source de données du TCD, chaque colonne doit posséder un en-tête unique (titre de colonne), et il ne doit pas y avoir de ligne ou colonne vide pour qu’Excel puisse identifier simplement les limites du tableau de données. Enfin, il ne faut pas mélanger les types de données contenues dans une même colonne, en d’autres termes, si une colonne composée de données numériques contient également des données textuelles, Excel va considérer que l’ensemble de la colonne est de type textuel et de pourra pas effectuer de calcul sur celle-ci.
• Le TCD qui peut prendre un grand nombre de dimensions et qui ordonne les données puisées dans la table de données.

Pour notre exemple, nous allons partir d’une liste simple qui reprend l’ensemble des ventes réalisés par les commerciaux (nombre de ventes, Chiffre d’affaires réalisé et montant des commissions) :

Nous allons maintenant construire notre premier TCD en six clics seulement :

• Clic 1 : Sélectionner n’importe quelle cellule du tableau

• Clics 2 et 3 : Cliquer sur l’onglet « Insérer » dans le Ruban, puis sur « Tableau croisé dynamique » 

• Clic 4 : Dans la fenêtre de création de tableau dynamique qui s’affiche à l’écran, cliquer simplement sur le bouton « OK ». Si votre table de donnée est correctement construite, c’est-à-dire avec un titre pour chaque colonne, et sans ligne vide, Excel détermine automatiquement la plage de données qui doit lui servir de source pour la construction du TCD (ici la plage $A$1:$E$19). Si ce n’est pas le cas, cliquer sur « Annuler », sélectionner la bonne plage puis cliquer à nouveau sur le bouton « Tableau croisé dynamique » du menu « Insérer ».

Par défaut, Excel va proposer de créer le TCD dans une nouvelle feuille, afin que le TCD n’influe pas avec des données déjà existantes. Pour utiliser la même feuille, cliquer sur « Feuille de calcul existante », puis choisir une cellule à partir de laquelle Excel va venir intégrer le TCD.

• Clics 5 et 6 : le TCD est maintenant créé, mais il est vide de toute information. Pour le remplir de manière automatique, il suffit simplement de cliquer sur les champs à ajouter, dans la zone « Champs de tableau croisé dynamique » située à droite de l’espace de travail.

De manière totalement transparente, Excel a analysé les données, et a compris que le champs « Commercial » correspond à un libellé de champs, et que le « CA » est l’élément de données à synthétiser. Lors de cette analyse, les valeurs textuelles sont automatiquement insérées dans la zone « Ligne », les dates et les heures dans la zone « Colonne » et enfin, les valeurs numériques sont insérées dans « Valeurs ».

Il est également possible d’aller encore plus vite en utilisant la suggestion de tableaux croisés dynamiques d’Excel. Pour cela, procédez comme nous venons de le voir ci-dessus, mais au niveau du Clic 3, choisissez plutôt le bouton « Tableaux croisés dynamiques ». Excel va alors analyser les données pour proposer une série de tableaux croisés dynamiques prêts à l’emploi. Choisissez celui qui convient le mieux à vos données, puis cliquez sur « OK ».

Si vous regardez attentivement le TCD crée, vous constaterez que l’intitulé de la colonne représentant le montant des ventes est « Somme des CA », et non pas simplement « CA ». Cela permet de savoir d’un seul coup d’œil l’opération qu’Excel effectue sur les données. Nous verrons plus bas dans cet article l’ensemble de ces opérations.

 Commander le livre et les ressources

3. Champs calculés et tris

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Le TCD très sommaire que nous venons de construire en seulement quelques clics nous affiche désormais le montant des ventes réalisées par chaque commercial avec une ligne de totalisation. Nous n’avons ici qu’à peine effleuré la puissance de l’outil de création de tableaux croisés dynamiques.

Nous allons maintenant voir comment aller encore plus loin.

Pour commencer, nous désirons pouvoir consulter les ventes réalisées par chaque commercial (comme c’est déjà le cas ici), mais également par pays. Pour cela, il est nécessaire d’utiliser un tableau à deux dimensions (les commerciaux sur les lignes, les pays sur les colonnes).

Pour cela, il convient de procéder de la manière suivante :

• Mettre le curseur sur le champs « Pays »,
• Le glisser dans la zone « Colonnes »

Notre TCD présente désormais les ventes réalisées par chaque commercial et par pays, avec les totaux respectifs.

L’objectif à présent est de connaître la marge nette dégagée par chaque commercial. Pour ce faire, il convient de procéder de la manière suivante :

• Décocher la case « Pays » si elle est encore sélectionnée après l’exemple précédent,
• Faire glisser le champs « Commission » dans la zone « Valeurs »

• Une nouvelle colonne apparaît dans le TCD avec le montant des commissions prélevées par chaque commercial 

• Maintenant nous voulons connaître ce que chaque commercial dégage comme bénéfice en calculant la différence (c’est-à-dire en effectuant la soustraction) entre le montant des ventes réalisées et le montant des commissions que nous allons lui reverser. Pour cela, sélectionner le menu spécial « Analyse » qui apparaît lorsqu’un TCD est sélectionné, puis dans le groupe « Outils », cliquer sur « Calculs » / « Champs, éléments et jeux » / « Champ calculé ». 

• Dans la nouvelle fenêtre qui apparaît à l’écran, remplir les champs comme montré ci-dessous :
  • Zone Nom : saisir le nom à donner au champs calculé (« Bénéfice »)
  • Zone Formule : saisir la formule désirée, en l’occurrence ici nous voulons connaître la différence entre les ventes réalisées par les commerciaux, et le montant des commissions que nous allons devoir leur reverser (donc la formule « =CA-Commission »). Pour saisir les champs sans erreur, il est possible de double-cliquer sur l’un d’entre eux pour l’insérer automatiquement. Si un champ est constitué de plusieurs mots, il est à encadrer par des apostrophes.
  • Cliquer sur le bouton « OK » 

Voici le résultat auquel vous devriez arriver :

Pour trier les commerciaux en fonction de leur performance, cliquer sur le menu déroulant de l’en-tête « Etiquettes de lignes », puis choisissez « Options de tri supplémentaires… ».

Dans la fenêtre qui s’affiche, choisir l’option « Descendant (de Z à A) par : », puis dans le menu déroulant sélectionner le champs « Bénéfice » que nous venons juste d’ajouter au TCD, et valider en cliquant sur le bouton « OK »         :

 Commander le livre et les ressources

4. Les différents types d’opérations

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Comme nous l’avons déjà abordé un peu plus haut, par défaut lorsqu’Excel détecte qu’une colonne de données est de type numérique, il va présenter ses données en effectuant une somme.

Pour les besoins d’une analyse plus poussée, il peut être utile de réaliser d’autres types d’opérations.

Pour cela, il faut effectuer un double-clic sur un en-tête de colonne du TCD pour afficher la fenêtre suivante :

Voici le détail des opérations disponibles :

• Somme : consiste à additionner toutes les valeurs de la colonne qui répondent aux différents critères du TCD. C’est l’opération la plus courante et celle que nous utilisons dans nos exemples depuis le début,
• Nombre : consiste à compter le nombre d’enregistrements de la colonne qui répondent aux différents critères du TCD (permet par exemple de connaître le nombre de pays dans lesquels un commercial réalise des affaires),

• Moyenne : consiste à présenter la moyenne des enregistrements de la colonne qui répondent aux différents critères du TCD (par exemple le CA moyen réalisé par chaque commercial),

• Max : consiste à présenter l’enregistrement le plus élevé de la colonne en fonction des différents critères du TCD,
• Min : consiste à présenter l’enregistrement le moins élevé de la colonne en fonction des différents critères du TCD,
• Produit : consiste à multiplier entre eux l’ensemble des enregistrements de la colonne en fonction des différents critères du TCD. Les nombres représentés peuvent très rapidement atteindre des niveaux très élevés, au point qu’Excel puisse être dans l’impossibilité de les afficher (Excel stock les valeurs numériques sur 15 chiffres uniquement, et arrondi au besoin, voir cet article pour plus d’informations). Ce type de calcul n’a pas grand intérêt dans nos exemples,

• Les autres opérations disponibles permettent de répondre à des besoins plus pointus (« Chiffre » permet de compter le nombre d’enregistrements comme le ferait l’opération « Nombre » mais en ne considérant que les valeurs numériques, « Ecartype » pour calculer l’écart type d’un échantillon, « Ecartypep » l’écart type d’une population, « Var » la variance d’un échantillon, et enfin « Varp » la variance d’une population).

 Commander le livre et les ressources

5. Sélectionner les données

   5.1 - Les filtres classiques

[Contenu disponible dans l'eBook uniquement]

 Commander le livre et les ressources

   5.2 - Les segments

[Contenu disponible dans l'eBook uniquement]

 Commander le livre et les ressources

   5.3 - Le filtre chronologique (filtre de dates sur une frise)

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

_____________________________________________

Dans la partie précédente de cette formation sur la création de tableaux croisés dynamiques, nous avions découvert comment utiliser les segments pour filtrer très rapidement les données affichées dans le TCD. Cette méthode très puissante n’est en revanche pas adapté à la saisie de dates sur lesquelles les critères peuvent être variés (années, mois, jours, intervalles,…). Mais heureusement, un autre outil dédié au filtre dynamique des dates est directement proposé par Excel, il s’agit de l’outil de filtre chronologique !

Pour illustrer l’utilisation du filtre chronologique, nous allons utiliser une nouvelle base de données, que voici :

Comme vous pouvez le constater, cette base de données reprend les ventes réalisées par Émily au Japon au cours des cinq dernières années.

Base reprend donc un nombre important de lignes, qu’il peut être intéressant d’analyser sur des périodes définies.

Pour cela, nous créons rapidement un tableau croisé dynamique, que nous choisissons d’insérer directement sur la feuille de calcul :

Pour insérer le tableau croisé dynamique sur la feuille de calcul en cours, choisissez l’option « Feuille de calcul existante », puis cliquez sur « Emplacement » pour choisir une cellule de la feuille de calcul (ici la cellule F7) :

Ensuite, nous pouvons valider la création du Tableau croisé dynamique en appuyant sur le bouton [OK] :

Le tableau croisé dynamique est maintenant inséré juste à côté de la base de données qui reprend les ventes réalisées par Émily au Japon.

Pour alimenter ce TCD en données, nous glissons simplement les champs dans les zones correspondantes depuis le menu Champs de tableau croisé dynamique situé sur la droite de la fenêtre Excel :

• Glissez le champs Date dans la zone Lignes,

• Glissez le champs Quantités dans la zone Valeurs :

• Glissez le champs CA dans la zone Valeurs, juste en dessous de Quantités :

Vous pourrez noter au passage que l’insertion du Champs Date dans la zone ligne a eu pour effet de créer deux champs calculés (Trimestres et Années) afin de regrouper les dates. Vous pouvez choisir de conserver ou de retirer ces regroupements en décochant simplement les cases correspondantes (par exemple ici, nous retirons le regroupement trimestre) :

Il est également possible de ne récupérer que les dates sans aucun regroupement, pour cela, rendez-vous dans Analyse > Dissocier :

À présent, nous pouvons en venir au sujet qui nous intéresse : l’insertion d’une chronologie reliée à un tableau croisé dynamique.

Celle-ci est analogue à l’insertion des segments que nous venons de découvrir dans la partie précédente : nous commençons par sélectionner une cellule du tableau croisé dynamique, puis nous nous rendons dans le menu Analyse (qui ne s’affiche que lorsqu’une cellule du tableau croisé dynamique est sélectionnée), puis nous choisissons Insérer une chronologie :

Ici, nous n’avons qu’un seul champ de type date sur lequel Excel peut insérer la chronologie :

Enfin, il suffit de valider en appuyant sur le bouton [OK] pour que la chronologie soit effectivement insérée et liée automatiquement au tableau croisé dynamique :

À présent, il suffit de cliquer à un endroit de la frise chronologique pour filtrer dynamiquement les données du tableau croisé dynamique :

Vous pouvez alors constater que seuls les ventes du mois de janvier 2019 sont maintenant affichées à l’écran !

Par défaut, chaque intervalle de la frise chronologique correspond à un mois, pour utiliser un espace de temps plus important, il est possible de sélectionner plusieurs intervalles en même temps, en déplaçant les curseurs situés aux extrémités :

Ici nous avons sélectionné l’intervalle qui correspond aux mois de janvier à mars 2019.

Nous pouvons également sélectionner directement plusieurs intervalles en faisant glisser le cureur au moment de la sélection :

Pour modifier la période (mensuelle) définie par défaut, nous utilisons le menu déroulant situé au-dessus de la frise, sur la droite :

Cela nous permet d’afficher les données annuelles, trimestrielles, mensuelles ou encore quotidiennes.

Pour effacer un filtre mis en place, cliquez sur le bouton Effacer le filtre :

Et pour supprimer complètement le filtre chronologique, sélectionnez ce dernier, puis appuyez sur la touche [Suppr] du clavier (ou clic-droit > Supprimer la chronologie)

Attention toutefois, comme nous l’avons déjà vu pour la suppression d’un segment, lorsque nous supprimons un filtre chronologique, cela n’efface pas pour autant les filtres mis en place. Pensez à les effacer au préalable !

Tout comme pour les segments également, il est possible de personnaliser l’apparence donnée à la frise chronologique :

• Il est possible de la déplacer :

• Il est possible de la redimensionner :

• Il est possible de modifier l’apparence de la chronologie depuis le menu Option :

Sachez également que depuis le menu Options, nous pouvons également choisir les éléments à afficher ou masquer :

• Afficher ou masquer l’En-tête
• Afficher ou masquer l’Étiquette de la sélection
• Afficher ou masquer la Barre de défilement
• Afficher ou masquer le Niveau des temps

Pour finir sur cette partie consacrée au filtre des données via la frise chronologique, sachez qu’il est possible de combiner celle-ci avec segment sur un même champs :

En revanche, comme vous pouvez le constater sur la capture ci-dessus, ces deux éléments ne sont reliés, ni mis à jour en temps réels pour tenir des changements opérés sur l’un ou l’autre.

 Commander le livre et les ressources

6. Grouper les données

[Contenu disponible dans l'eBook uniquement]

 Commander le livre et les ressources

7. Insérer de nouveaux enregistrements

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

_____________________________________________

Comme nous venons juste de le voir, le TCD met en forme des données issues d’une base de données source.

Mettre à jour le tableau en temps réel lors de chaque changement dans cette base demanderait trop de ressources à Excel pour maintenir un fonctionnement agréable.

Pour ne pas multiplier les ralentissements, Excel duplique les données dans une zone de cache qui nécessitera d’être réactualisée manuellement à chaque fois que des nouvelles informations seront saisies, supprimées ou modifiée.

Cette actualisation peut s’effectuer de deux manières différentes :

• Soit en cliquant sur le bouton « Actualiser » présent dans l’onglet « Analyse » du ruban (cet onglet n’est visible que lorsqu’un tableau croisé dynamique n’est sélectionné, il convient donc au préalable de cliquer sur une des cellules de ce TCD),

• Soit en cliquant ce même bouton « Actualiser », mais cette fois-ci au sein du menu contextuel qui s’affiche lorsque l’on effectue un clique-droit avec le bouton droit de la souris sur une cellule du TCD 

Pour ajouter une ligne de données au TCD, il existe trois façons de faire.

Les deux premières sont lourdes et pas vraiment intuitives, nous ne ferons que de les citer, tandis que la troisième est bien plus pratique à utiliser et plus puissante :

• La première façon de faire consiste à insérer une ligne sous le tableau, puis de redéfinir la plage de cellules à utiliser (onglet « Analyse » du ruban / « Changer la source de données » / « Changer la source de données… »).

• La seconde façon de faire consiste à insérer une nouvelle ligne au milieu du tableau pour insérer des données (clic-droit sur un libellé de ligne, par exemple le numéro de la ligne « 10 », puis cliquer sur « Insertion »). 

Si les données du tableau suivent un ordre défini, il faudra alors les trier manuellement après avoir insérer cet enregistrement.

• Enfin, la troisième et meilleure façon de faire consiste à transformer la base de données source en un tableau de données Excel. Pour plus d’informations, je vous invite vivement à consulter cet article qui aborde la notion de tableau de données dans le détail.

Pour transformer notre plage de données en tableau de données Excel, procédez de la manière suivante :

• Sélectionner une cellule du tableau
• Cliquer sur « Insérer » puis « Tableau » dans le ruban
• Dans la fenêtre qui s’affiche, laisser cochée la case « Mon tableau comporte des en-têtes » et cliquez sur le bouton « OK »

Avec cette manière de procéder, il vous suffira d’ajouter un enregistrement sous le tableau pour automatiquement redéfinir la plage de cellule utilisée pour alimenter le tableau croisé dynamique.

 Commander le livre et les ressources

8. Mettre en forme le TCD

   8.1. Mise en forme assistée

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

_____________________________________________

Dans la plupart des cas, la mise-en-forme par défaut conviendra largement.

Mais il est évidemment possible de personnaliser complètement son apparence à partir du menu « Création » qui s’affiche dans le ruban lorsqu’un TCD est sélectionné.

Voyons quels en sont les principales options de personnalisations :

• Sous-totaux : permet d’activer ou non la présence de sous-totaux lorsqu’il y a des changements de catégories,
• Totaux généraux : permet d’activer ou non la présence des totaux généraux du tableau. Les totaux de lignes s’affichent à droite du tableau, tandis que les totaux des colonnes s’affichent en bas, sur la dernière ligne du tableau,
• Disposition du rapport : permet de personnaliser le type de tableau voulu, c’est-à-dire sous la « forme compactée » (qui est la forme par défaut sur les dernière versions d’Excel) avec les étiquettes de lignes disposées dans une seule et même colonne, en « mode plan » qui ressemble fortement à la forme compactée, à la différence que chaque étiquette de ligne se retrouve dans une colonne différente et enfin la « forme tabulaire » qui est un tableau plus classique. Pour ces deux derniers modes d’affichage uniquement, il est également possible de choisir de répéter les étiquettes d’éléments, lorsque ceux-ci possèdent plusieurs éléments

La forme compactée :

Le mode plan :

La forme tabulaire :

La forme tabulaire avec une répétition des étiquettes d’éléments :

• Le groupe « Options de style de tableau croisé dynamique » permet d’automatiser la mise en évidence des en-têtes de lignes et de colonnes du TCD et de choisir changer le fond d’une ligne sur deux (ou d’une colonne sur deux) afin d’en faciliter la lecture,

• Pour changer rapidement l’apparence du TCD, vous pouvez choisir parmi les nombreux styles proposés par défaut par Excel. Il suffit de les survoler avec le curseur de la souris pour afficher en temps réel un aperçu du rendu sur votre tableau croisé dynamique. 

 Commander le livre et les ressources

   8.2. Créer un style personnalisé

[Contenu disponible dans l'eBook uniquement]

 Commander le livre et les ressources

   8.3. Des TCD incroyables avec la mise en forme conditionnelle

[Contenu disponible dans l'eBook uniquement]

 Commander le livre et les ressources

9. Alimenter un TCD à partir de plusieurs sources

Continuons sur notre lancée d’analyse des résultats des ventes réalisées par les commerciaux d’une entreprise.

Ici nous allons souhaitez fusionner les données présentes dans plusieurs tables sources, afin de combiner les résultats dans un seul et unique tableau croisé dynamique !

   9.1. En utilisant des formules

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Pour ce premier exemple, nous souhaitons faire apparaître une information supplémentaire dans nos tableaux d’analyse qui est : le nom du produit vendu par chaque commercial.

Et pour cela, nous disposons d’une table supplémentaire (que vous retrouvez juste en dessous) dans laquelle chaque produit commercialisé par l’entreprise est rattaché avec le commercial chargé d’en réaliser les ventes :

Nous voyons ainsi que François est en charge de la vente des régulateurs, Émily des alternateurs, etc…

L’idée va alors être de faire ressortir dans un nouveau tableau croisé dynamique les montants de ventes réalisés, non plus par commercial comme nous en avions l’habitude jusqu’à maintenant, mais cette fois-ci en fonction du produit vendu !

Et pour cela, nous allons insérer une nouvelle colonne dans le tableau source dans laquelle nous viendrons récupérer le produit lié au commercial.

Mais avant cela, nous allons transformer la table de correspondances que nous avons ici, en un tableau de données structurées.

Comme nous l’avons déjà abordé dans le tutoriel dédié (que je vous invite à consulter en cliquant ici pour tout savoir de cette fonctionnalité extrêmement puissante d’Excel), le tableau de données structurées permet en autres choses de simplifier la construction des formules Excel en affectant un nom clair et explicite à chaque élément du tableau (nom de table, nom de colonne, etc…).

Pour transformer cette simple liste de correspondance en un tableau structuré :

• Nous commençons par sélectionner l’une des cellules :

• Puis nous nous rendons dans le menu Insertion > Tableau (depuis le groupe Tableaux), nous pouvons également utiliser le raccourci clavier [Ctrl]+[L] :

• Dans la fenêtre qui s’affiche à l’écran, nous commençons par vérifier que les cellules sélectionnées automatiquement correspondent bien aux cellules du tableau (les cellules utilisées par Excel sont encadrées par des pointillés), et nous cochons la case Mon tableau comporte des en-têtes si celle-ci n’est pas automatiquement activée. Cette option permet en effet de considérer que la première ligne du tableau est constituée des titres de colonnes ! Enfin, nous validons la transformation en tableau de données structurées en appuyant sur le bouton [OK] :

• Une fois le tableau de données structurées crée, il suffit de sélectionner une des cellules qui le composent pour faire apparaître un nouveau menu dans le Ruban : il s’agit du menu Création. À partir de celui-ci, nous pouvons par exemple choisir un style différent en cliquant simplement sur celui qui nous intéresse :

• Nous allons surtout pouvoir affecter un nom explicit à ce tableau, ce qui permettra de construire ensuite des formules facilement (ici, j’ai décidé de l’appeler « _tableCommercialProduit ») :

Cela étant fait, nous pouvons maintenant créer une nouvelle colonne dans la base de données d’origine, laquelle est déjà construite sous la forme d’un tableau de données. Il suffit donc d’ajouter un nouveau nom de colonne sur la colonne qui se trouve juste après le calcul de la Commission :

Et maintenant pour récupérer le nom du produit qui correspond, nous avons le choix entre plusieurs solutions, et nous allons faire le choix d’utiliser la fonction Excel INDEX(EQUIV) qui permet d’extraire rapidement une donnée en fonction d’une autre. Vous trouverez un article dédié à cette fonction en suivant ce lien, je ne vais pas en détailler le fonctionnement ici.

Voici donc la formule Excel :

 =INDEX(_tableCommercialProduit[Produit vendu];EQUIV([@Commercial];_tableCommercialProduit[Commercial];)) 

Et le résultat :

Vous constaterez au passage qu’Excel a étendu automatiquement la formule sur l’ensemble des lignes de la colonne Produit, merci au tableau de données structurées !!

Ensuite, nous pouvons créer un nouveau tableau croisé dynamique, dans lequel nous pourrons exploiter directement cette nouvelle colonne.

Si vous avez suivi les chapitres précédents, vous connaissez évidement la méthode, nous passons donc rapidement là-dessus :

• Insertion > Tableau croisé dynamique,
• La base est sélectionnée automatiquement,
• Nous choisissons d’insérer le tableau croisé dynamique sur une feuille de calcul existante

Ensuite il ne reste plus qu’à construire le tableau simplement pour faire apparaître le montant des ventes réalisées, par produit :

(Après avoir modifié le style du TCD le format des nombres et appliqué un filtre décroissant sur les ventes)

Et voilà, donc comme vous pouvez le constater, nous pouvons simplement ajouter des données externes sur une base de données qui va ensuite servir à alimenter un tableau croisé dynamique. Cette solution présente l’avantage de pouvoir être mise en place rapidement, sans avoir effectuer trop de manipulations. En revanche, dès lors que le nombre d’informations à combiner va commencer à être plus important, il va être préférable d’utiliser une seconde méthode pour combiner automatiquement les informations contenues dans plusieurs tables ! Et c’est cette méthode que nous découvrirons lundi prochain dans la partie suivante de cette formation sur l’utilisation des tableaux croisés dynamique.

 Commander le livre et les ressources

   9.2. En combinant automatiquement les bases sources

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Dans la partie précédante de la formation sur l’utilisation des tableaux croisés dynamiques, nous avons découvert comment procéder pour fusionner les données contenues dans plusieurs tableaux sources afin de les exploiter simplement dans un tableau croisé dynamique unique.

Nous avions mis en place une méthode qui repose sur l’utilisation de la formule Excel INDEX(EQUIV). Cette méthode permet de récupérer une information rapidement, mais peut s’avérer inadaptée lorsque nous souhaitons récupérer plusieurs informations, ou lorsque les données à combiner sont des données numériques.

Dans cette seconde partie, nous allons découvrir une nouvelle méthode pour combiner des données sources, cette fois-ci de manière totalement automatisée !

Pour ce nouvel exemple, nous disposons du tableau suivant :

Dans ce tableau sont regroupés tous les frais de déplacement des commerciaux que l’entreprise va devoir rembourser.

Nous allons vouloir créer un nouveau TCD dans lequel nous ferons apparaître le montant du CA réalisé par chaque commercial, ainsi que les coûts afférents (commission et coûts de déplacement).

Or ces informations sont issues de sources différentes, nous allons donc devoir combiner les bases de données, afin que celles-ci puissent être utilisées dans un seul et même TCD.

Si dans la partie précédente, j’avais bien insisté sur l’intérêt de transformer les bases de données en tableaux de données structurées, ici la question ne se pose même pas car nous ne pourrons combiner que des données agencées suivant ce format !

Ici le tableau porte le nom de _baseFraisDeplacements.

Comme nous venons de le voir, nos bases d’origine doivent impérativement être sous la forme d’un tableau de données structurées, mais il ne s’agit pas de la seule condition qui doit absolument être remplie :

• Chaque base source doit posséder une colonne similaire dans laquelle nous retrouverons les mêmes informations et qui permettrons à Excel de faire le lien entre les différentes tables. Évidemment, ces colonnes communes doivent porter le même nom et posséder les mêmes informations (ici chaque base possède une colonne Commercial dans laquelle nous retrouvons le nom de chacun d’entre eux, avec une correspondance exactement)
• Dans une des deux bases cette colonne doit être composée de clés uniques d’identification (c’est-à-dire sans doublon, ce qui est le cas ici car les frais de déplacements sont cumulés annuellement, nous ne pourrions pas avoir dans cette base le détail de chacun d’entre eux par exemple),
• Les deux bases sources doivent être enregistrées dans un même classeur, mais pas forcément sur la même feuille de calcul (ici, chaque base est saisie dans son propre onglet)

Une fois que nous avons effectué ces contrôles, nous pouvons créer une relation entre ces deux bases de données qu’Excel va ensuite ajouter dans le modèle des données de la feuille, c’est-à-dire l’endroit dans lequel toutes les sources de données sont enregistrées.

Pour créer la relation, nous nous rendons dans le menu Données du ruban, puis nous cliquons sur le bouton Relations :

Excel nous affiche alors la fenêtre de gestion des relations, qui pour le moment est totalement vide.

Nous cliquons sur le bouton Nouveau… :

Puis dans la fenêtre qui apparaît, nous créons la relation en choisissant chacune des tables (baseDeDonnees et _baseFraisDeplacements) en choisissant comme colonne relationnelle la colonne Commercial :

La colonne associée correspond à la colonne dans laquelle les données ne se trouvent saisies qu’une seule fois !

Puis nous validons en appuyant sur le bouton [OK].

Ensuite, nous créons un nouveau tableau croisé dynamique, au sein duquel nous demandons à Excel d’utiliser le modèle de données du classeur en tant que données sources :

Une fois le TCD créé, lorsque nous analysons les données disponibles pour alimenter celui-ci, nous retrouvons les champs de chaque tableau source, que nous pouvons utiliser librement pour construire le tableau croisé dynamique

Attention : la colonne Commercial que nous utilisons dans le TCD doit impérativement être la colonne issue de la base de données dans laquelle les lignes sont uniques (table de synthèse des frais de déplacement).

 Commander le livre et les ressources

10. Les graphiques croisés dynamiques

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

   10.1. Qu’est ce qu’un graphique croisé dynamique

Comme son nom l’indique, un graphique croisé dynamique (ou GCD) permet de représenter les données issues d’un tableau croisé dynamique classique sous une forme d’un graphique.

Cela permet d’ajouter une représentation visuelle aux données afin de rendre les informations synthétisées plus parlantes.

   10.2. Comment créer un graphique croisé dynamique

Le graphique croisé dynamique repose sur un tableau croisé dynamique, qui doit donc au préalable déjà avoir été mis en place.

Comme nous l’avons abordé dans les chapitres précédents, la construction du tableau croisé dynamique est très simple, et peu s’exécuter.

Repartons donc de notre base habituelle pour créer le dit tableau croisé dynamique (sélectionner le tableau de base / Menu Insertion > Tableau croisé dynamique) que nous insérons sur une nouvelle feuille.

Pour l’exemple, nous créons un TCD très simple, pour cela, nous glissons :

• Le champs « Commercial » sur la zone « Lignes »,
• Le champs « Valeurs » sur la zone « CA »,
• Le champs « Pays » sur la zone « CA »,

p>Ensuite, nous sélectionnons l’une des cellules de ce TCD fraîchement ajouté, puis nous choisissons d’insérer un Graphique Croisé dynamique depuis le menu Insertion du Ruban :

Excel présente alors la fenêtre d’insertion de graphique classique, qui permet de choisir le type de graphique à insérer.

Il est possible de visionner la prévisualisation de chaque type de graphique en cliquant simplement sur celui qui nous intéresse.

Pour notre exemple, nous choisissons par exemple d’insérer un graphique camembert pour illustrer le poids des ventes réalisées par chaque commercial.

Nous retrouvons ce type de graphique dans la catégorie Secteurs.

Une fois le graphique choisi, il suffit de cliquer sur le bouton [OK] pour valider l’insertion de celui-ci dans la feuille de calcul en cours d’analyse.

 Commander le livre et les ressources

   10.2. Fonctionnement du Graphique Croisé dynamique

Contrairement à un graphique classique, les différents éléments du GCD peuvent être modifiés directement sur celui-ci !

Par exemple, pour sélectionner un pays, il suffit de d’utiliser le champ de filtre :

A partir de là, nous pouvons donc déplacer le GCD directement au-dessus du tableau croisé dynamique (en cliquant sur celui-ci, puis en déplaçant le curseur de la souris sans relâcher le bouton ) !

Il est évidemment possible d’actualiser les données en effectuant un clic-droit sur la zone de traçage du graphique > Actualiser les données :

Il est également possible de personnaliser le graphique croisé dynamique directement en modifiant l’affichage du tableau croisé dynamique.

Ici, nous avons simplement remplacé l’affichage du montant du CA par l’affichage du nombre de ventes réalisées en Allemagne.

   10.3. Un GCD vraiment dynamique avec les des segments

Pour sélectionner les éléments à afficher sur le GCD, il est également possible d’utiliser les segments.

Pour cela, nous procédons exactement la méthode que nous avons déjà étudié dans une des parties précédentes.

Nous commençons par sélectionner le graphique, si ce n’est pas déjà fait.

Puis nous nous rendons dans le menu Analyse du Ruban (ce menu n’apparaît que lorsqu’un graphique ou un tableau croisé dynamique n’est sélectionné) afin de cliquer sur le bouton Insérer un segment.

Il suffit ensuite de choisir le ou les segments que nous souhaitons utiliser.

Par exemple, ici, nous allons vouloir être en mesure de sélectionner le pays sur lequel nous souhaitons limiter l’analyse, ainsi que le produit concerné.

Attention : il est possible de sélectionner le champs Produit uniquement parce que nous avons au préalable créé une liaison entre les tables « _baseDeDonnees » et « _tableCommercialProduit » (voir le point 9. vu juste avant).

À présent, il suffit de cliquer sur les éléments des segments pour pouvoir facilement définir les éléments à présenter dans le GCD.

Nous pouvons même retirer le filtre Pays que nous avions installé dans la zone de filtre, ce dernier faisant doublons avec le segment.

Pour cela, nous sélectionnons une fois encore le graphique croisé dynamique, puis nous désélectionnons le champ correspondant depuis le menu de paramétrage :

 Commander le livre et les ressources

   10.4. Créer un GCD sans TCD

Enfin, sachez qu’il est possible de créer un graphique croisé dynamique sans avoir au préalable créer de TCD.

Pour cela il suffit de procéder exactement comme ce que nous venons de voir à l’instant, à la différence que sur la première étape, nous ne sélectionnons évidemment pas une cellule du TCD (qui n’existe pas), mais une cellule du tableau d’origine.

Cela aura alors pour effet de créer les deux objets en une seule étape (le tableau croisé dynamique ainsi que le graphique croisé dynamique) :

Il suffit alors d’alimenter l’un de ces objets pour que les deux soient générés automatiquement.

 Commander le livre et les ressources

Pour résumer

Voici quelques rappels importants concernant la construction des tableaux croisés dynamiques :

• Un TCD permet de synthétiser et de mettre en forme des données issue d’une table de données,
• Les colonnes de la table des données doivent toutes posséder un titre, et il ne doit pas y avoir de ligne vide, ni de colonne vide,
• Utiliser les tableaux de données Excel simplifie l’ajout de données (nouvelles lignes ou nouvelles colonnes) en redéfinissant automatiquement la plage des cellules sources. Sinon, cette plage de cellule sera à modifier manuellement après chaque enregistrement

Article initialement publié le 19 mars 2018, puis modifié le 20 avril 2020