Cliquer ici pour télécharger le fichier
Dans cet article je vous montre comment lier plusieurs tableaux de données dans un tableau croisé dynamique sur Excel :
Conditions à respecter :
Pour regrouper plusieurs tableaux dans un tableau croisé dynamique sur Excel il faut réunir trois conditions :
- Les tableaux doivent être dans le même classeur
- Chaque tableau doit avoir une colonne avec une clé commune
- Au moins une colonne qui contient la clé commune ne comporte pas de doublon (contient des valeurs uniques)
Dans l’exemple on a trois tableaux de données et on souhaite créer un tableau croisé dynamique à partir de ces trois tableaux :
Tableau 1 : Commandes
Tableau 2 : Détails commandes
Tableau 3 : Livraison
Les trois conditions sont bien vérifiées :
- Les trois tableaux sont dans le même classeur
- Les trois tableaux ont une colonne avec une clé commune : « N° commande »
- Les colonnes « N° commande » des tables « Commandes » et « Livraison » contiennent des valeurs uniques
1) Mettre les données sous forme de tableau
=> Pour mettre les données sous forme de tableau il faut cliquer sur une des cellules du tableau puis dans l’onglet « Accueil » il faut cliquer sur le menu déroulant « Mettre sous forme de tableau » puis choisir un tableau.
2) Renommer les tableaux
Après avoir mis les trois tables de données sous forme de tableau il faut maintenant les renommer pour faciliter la création du tableau croisé dynamique :
=> Pour renommer un tableau il suffit de cliquer sur une des cellules du tableau puis dans l’onglet « Création de tableau » il faut renommer le tableau dans la zone « Nom du tableau » : On va renommer les trois tableaux par leur nom : Commandes, détails commandes, livraison.
3) Créer le tableau croisé dynamique
=> Pour créer le tableau croisé dynamique il faut cliquer sur une des cellules du tableau puis dans l’onglet « Insertion » il faut cliquer sur « Tableau croisé dynamique » puis dans la fenêtre de création du tableau croisé dynamique il faut cocher la case « Ajouter ces données au modèle de données » puis cliquer sur « OK ».
Résultat : Dans l’outil de création du tableau croisé dynamique on voit deux onglets :
> Un onglet « Actif » dans lequel on retrouve la table à partir de laquelle on a crée le tableau croisé dynamique.
> Un onglet « Tous » dans lequel on retrouve les trois tables qu’on a mis au préalable sous forme de tableau.
À ce niveau les tableaux croisés dynamiques ne sont pas liés entre eux et si on essaye de sélectionner des champs issus de tableaux différents Excel va nous alerter sur le fait qu’aucune liaison n’est établie.
=> Par exemple si on sélectionne « Nom client » dans la table « Commandes » et « Quantités » dans la table « DétailsCommandes » :
= Excel affiche le message suivant : « Des relations entre les tables peuvent être nécessaires ».
Résultat : Sans connexion entre les tables il est impossible de combiner les données des différentes tables dans un tableau croisé dynamique.
4) Créer des relations entre les tables
Il existe deux méthodes pour créer des relations entre les tables de données :
- Créer des relations manuellement :
Après avoir sélectionné des champs des deux tables différentes (« Commandes » et « DétailsCommandes » dans l’exemple) Excel affiche le message « Des relations entre les tables peuvent être nécessaires » :
=> Il faut alors cliquer sur le bouton « CRÉER… » puis dans la zone « Table » il faut choisir la table « Commandes » avec la colonne (externe) « N° commande » et dans la zone « Table associée » il faut choisir la table « DétailsCommandes » avec la colonne associée (principal) « N° commande ».
= Une relation est désormais créée entre les tables « Commandes » et « DétailsCommandes » grâce à la clé commune « N° commande ».
Résultat : On peut désormais créer un tableau croisé dynamique en combinant les informations issues de ces deux tableaux.
Il est aussi possible de créer des relations directement depuis l’outil « Gérer les relations » :
=> Pour cela il faut cliquer sur une des cellules du tableau croisé dynamique puis dans l’onglet « Analyse » il faut cliquer sur le bouton « Relations ».
=> Une fenêtre « Gérer les relations » s’ouvre alors ; dans laquelle on peut notamment définir une nouvelle relation en cliquant sur le bouton « Nouveau… »
De la même manière que pour la création de la première relation entre les tables « Commandes » et « DétailsCommandes » on va cette fois-ci créer une relation entre les table « Commandes » et « Livraison » toujours en utilisant le « N° commande » comme colonne commune :
=> Dans la zone « Table » il faut choisir la table « Commandes » avec la colonne (externe) « N° commande » et dans la zone « Table associée » il faut choisir la table « Livraison » avec la colonne associée (principal) « N° commande ».
- Créer des relations automatiquement :
Pour bien comprendre le fonctionnement de la détection automatique de relations entres les tables on va commencer par supprimer les deux relations crées précédemment :
=> Pour cela il faut sélectionner la relation à supprimer puis cliquer sur le bouton « Supprimer » et répéter l’opération pour les deux relations.
On peut désormais créer les relations avec la détection automatique :
=> Pour créer des relations automatiquement il faut se rendre dans l’outil « Gérer les relations » comme vu précédemment (Pour rappel il faut cliquer sur une des cellules du tableau croisé dynamique puis dans l’onglet « Analyse » il faut cliquer sur le bouton « Relations ») sauf que cette fois-ci on va cliquer sur le bouton « Détection automatique… »
= Excel nous informe que deux nouvelles relations ont été créées.
= Ces deux relations ont été créées automatiquement grâce à la colonne commune « N° commande » entre les trois tables : « Commandes », « DétailsCommandes » et « Livraison ».
5) Créer un tableau croisé dynamique avec toutes les tables liées
Après avoir créé des relations entre les trois tables on peut désormais créer un tableau croisé dynamique en combinant les informations issues des trois tables de données.
Dans l’exemple on souhaite faire apparaître le total des quantités commandées par mois sur chaque ville ; Pour obtenir ce résultat il nous faut donc combiner des informations issues des trois tables de données :
=> Il faut cocher le champ « Quantité » dans la table « DétailsCommandes » pour avoir le total des quantités commandées.
=> Il faut cocher le champ « Ville » dans la table « livraison » pour retrouver la ville en fonction des commandes.
=> Il faut cocher le champ « Date commande » dans la table « Commandes » pour retrouver la date en fonction des commandes.
=> Dans le tableau croisé dynamique on place le champ « Ville » en dessous du champ « Dates commande » et on place le champ « Quantité » dans les valeurs en type de calcul « Somme ».
Résultat : On obtient un tableau croisé dynamique qui combine les données de trois tableaux différents.
=> Voir aussi comment les bases de la création d’un tableau croisé dynamique
