Excel : Créer un tableau croisé dynamique à partir de plusieurs feuilles de calcul

Dans ce tutoriel, nous allons vous montrer tout ce dont vous aurez besoin pour créer un tableau croisé dynamique dans Excel à partir de données dans plusieurs feuilles de calcul.

Les tableaux croisés dynamiques sont un outil très utile et puissant qui vous permet de regrouper toutes vos données dans un seul tableau. En outre, vous pourrez par la suite utiliser ce tableau pour analyser vos données et obtenir les chiffres pour des choses particulières selon les besoins. Par exemple, vous pouvez créer un rapport de ventes, afficher le produit le plus vendu, calculer la moyenne des ventes et plus encore.

Habituellement, pour créer un tableau croisé dynamique, la plupart des gens mettent toutes les données à utiliser dans une seule table. Ainsi, pour créer un tableau croisé dynamique, les gens passent des heures de travail à combiner plusieurs tables en une seule table en mélangeant manuellement les données ou, parfois, en implémentant une formule de recherche avec la fonction « RECHERCHEV ». Quoi qu'il en soit, ce n’est pas ce que nous voulons passer notre temps à faire.

Vous ne connaissez peut-être pas qu’Excel offre des moyens plus avancés pour vous permettre d’utiliser plusieurs feuilles de calcul lors de la création d’un tableau croisé dynamique. Grace à ces outils, vous pouvez garder les données ou tables d'origine intactes. Alors, si vos données se trouvent dans différents classeurs ou feuilles de calcul, vous avez différentes façons d'obtenir un tableau croisé dynamique à partir de celles-ci. Nous vous montrons ici trois méthodes que vous pouvez utiliser à cette fin.

Table des matières

Méthode 1 : utiliser l’assistant Tableau croisé dynamique et graphique croisé dynamique

Méthode 2 : utiliser une ligne commune entre les différentes tables

Méthode 3 : utiliser Microsoft Query

Méthode 1 : utiliser l’assistant Tableau croisé dynamique et graphique croisé dynamique

La première méthode pour créer un tableau croisé dynamique à partir de plusieurs feuilles de calcul consiste à utiliser l’assistant de tableaux croisés dynamiques. En fait, cet assistant vous permet d’utiliser plusieurs plages de consolidation comme source de votre tableau croisé dynamique.

Pour ce processus, vous avez besoin de données sur deux ou plusieurs feuilles de calcul. Par exemple, feuille1, feuille2, feuille3, etc. Dans notre exemple de classeur, nous disposons de trois feuilles de calcul : « Client », « Commande » et « Paiement » :

Dans la feuille de calcul « Client » nous avons les numéros de commande le nom et la vile de chaque client :

Dans la feuille de calcul « Commande » nous avons également les numéros de commande avec d’autre informations comme le mois et les produits commandés :

Dans la feuille de calcul « Paiement » nous avons les numéros de commande, le montant de chaque commande ainsi que le mode de paiement :

Vous trouverez ci-dessous le processus étape par étape pour créer un tableau croisé dynamique à partir de plusieurs feuilles de calcul. Mais, avant de commencer, ajoutons d’abord le bouton « Assistant de tableau croisé dynamique » dans le ruban :

1. Cliquez sur la flèche « Personnaliser la barre d'outils d'accès rapide» et sélectionnez « Autres commandes » comme illustré dans la capture ci-dessous :

2. La boîte de dialogue « Options Excel» s’ouvre. Sélectionnez « Toutes les commandes » dans la liste déroulante « Choisir les commandes dans les catégories suivantes » :

3. Ensuite, recherchez et sélectionnez « Assistant Tableau crois dynamique et Graphique croisé dynamique» dans la zone de liste des commandes disponibles :

4. Avec la commande sélectionnée, cliquez sur le bouton « Ajouter».
5. Finalement, cliquez sur le bouton « OK» pour fermer la boite de dialogue « Options Excel ».

 Désormais, le bouton « Assistant Tableau croisé dynamique et graphique croisé dynamique » s'affiche dans la barre d'outils Accès rapide comme illustré ci-dessous :

Maintenant, vous pouvez commencer à créer votre tableau croisé dynamique à partir de ces trois feuilles de calcul.

6. Cliquez sur le bouton « Assistant Tableau croisé dynamique».
7. La boîte de dialogue de l’assistant apparaîtra. Dans la première étape, sélectionnez l’option « Plages de feuilles de calcul avec étiquettes» et cliquez sur « Suivant » pour continuer :

8. L’assistant passe à la deuxième étape, et en cela, sélectionnez la deuxième option « Plusieurs Création manuelle» comme illustré dans la capture d’écran ci-dessous :

9. Une fois les options sélectionnées, cliquez à nouveau sur « Suivant» pour passer à la troisième étape. Dans cette étape, sélectionnez la plage de cellules dans la table de la première feuille de calcul, puis cliquez sur le bouton « Ajouter ». Et puis, faites de même pour les autres feuilles de calcul. Dans notre exemple de classeur, nous sélectionnerons la plage de cellules « A1:C13 », nous cliquons sur « Ajouter ». Nous passons ensuite à la feuille de calcul « Commande », nous sélectionnons la plage « A1:C13 », et cliquons sur « Ajouter ». Finalement, nous passons à la feuille de calcul « Paiement, nous sélectionnons la plage « A1 :C13 »  et cliquons sur « Ajouter ». La capture ci-dessous illustre le processus :

10. Maintenant, Excel va rassembler les trois tables en une seule. Toujours dans la même étape, sélectionnez « 1» dans l’option « Combien de champs de Page voulez-vous ? » :

11. Ensuite, sélectionnez la référence de la première plage de données dans la zone « Toutes les plages» et puis tapez un nom pour cette plage de données dans la zone de texte « Champ 1 ». Toutefois, il est recommandé de saisir un nom descriptif pour chaque plage de données pour faciliter l'identification de la plage de données dans le tableau croisé dynamique :

12. Sélectionnez ensuite la deuxième plage de données dans la section « Toutes les plages» et saisissez un nom descriptif :

13. Faites de même pour la dernière plage de données :

14. Lorsque vous saisissez les noms descriptifs pour toutes les plages, cliquez sur « Suivant» pour passer à la dernière étape.
15. A ce stade, sélectionner si vous souhaitez que votre tableau croisé dynamique soit placé dans une nouvelle feuille de calcul ou dans une feuille existante. Nous recommandons de l’insérer dans une nouvelle feuille de calcul pour une bonne organisation du classeur :

16. Finalement, cliquez sur « Terminer» et le tableau croisé dynamique sera créé dans une nouvelle feuille de calcul :

Comme vous pouvez le remarquer, le tableau croisé dynamique brut généré par Excel nécessitera des modifications et un peu de formatage pour répondre aux exigences de votre rapport.

17. En premier lieu, notez que par défaut, les valeurs s'affichent sous forme de « Nombre». Vous pouvez le changer en « Somme » ou en un autre calcul. Cliquez avec le bouton droit sur l'une des valeurs, sélectionnez l’option « Synthétiser les valeurs par » et choisissez « Somme » :

Cette modification affectera toutes les valeurs car elles ne peuvent pas être modifiées séparément :

18. En deuxième lieu, notez que tous les champs des données source sont inclus dans le tableau croisé dynamique de consolidation multiple. Pour rendre bien organiser les choses, vous pouvez supprimer certains champs et apporter quelques autres modifications. Dans cet exemple, les champs comme « Client», « Mode de paiement », « Produit » et « Ville » contiennent du texte ou des nombres qui n'ont pas de sens dans ce rapport, nous allons donc les supprimer. Pour supprimer des champs, cliquez sur la flèche déroulante dans l'en-tête « Étiquettes de colonne », décochez les cases des champs que vous souhaitez supprimer et cliquez sur « OK » :

19. Remarquez également que les dates ne sont pas bien affichées, nous devons alors modifier le format de la colonne « Date». Sélectionnez les cellules, et choisissez « Date courte » dans le menu « Nombre » de l’onglet « Accueil » comme illustré ci-dessous :

20. Ensuite, nous allons supprimer le total général des lignes puisqu’il n'a pas de sens dans ce rapport. Pour ce faire, faites un clic droit sur l'en-tête du total général des lignes et sélectionnez « Supprimer le total général» dans le menu contextuel :

21. Vous pouvez également renommer les champs génériques (page1, ligne, colonne) qui sont créés dans le tableau croisé dynamique pour faciliter la compréhension. Pour ce faire, cliquez simplement sur n'importe quelle étiquette dans le tableau croisé dynamique, saisissez une nouvelle étiquette, puis appuyez sur « Enter».

Il s'agit de la première méthode pour créer un tableau croisé dynamique à partir de plusieurs feuilles dans Excel. Dans ce tableau croisé dynamique, comme dans le tableau croisé dynamique normal, vous pouvez glisser et déposer les champs selon l'exigence.

Méthode 2 : utiliser une ligne commune entre les différentes tables

La deuxième méthode que nous allons aborder exige une colonne commune dans les tables que vous souhaitez utiliser pour créer le tableau croisé dynamique. Cette colonne servira de clé primaire pour la première table et de clé étrangère pour les autres tables et jouera ainsi le rôle de connecteur entre toutes les feuilles de calcul. Dans notre exemple de classeur, nous avons la colonne « N_commande » qui se trouve dans les trois feuilles de calcul, nous l’utiliserons donc comme point de connexion.

Remarque : notez qu'il n'est pas toujours nécessaire d'avoir une clé primaire, mais cela réduit le risque d'erreur.

1. Avant de procéder à la création du tableau croisé dynamique, convertissez les plages de données en des tableaux Excel dans toutes les feuilles de calcul. Pour ce faire, accédez à la première feuille de calcul et cliquez n'importe où dans la zone de données.
2. Accédez ensuite à l'onglet « Insertion» et cliquez sur la commande « Tableau » dans le groupe « Tableaux ».
3. Lorsque vous cliquez dessus, la boîte de dialogue « Créer un tableau» s’ouvre en identifiant correctement la zone de la table.
4. Assurez-vous que la zone sélectionnée est bien ce que vous voulez utiliser et puis cochez la case en bas qui indique que la première ligne du tableau est destinée aux en-têtes.
5. Cliquez sur « OK» pour terminer :

6. Lorsque vous sélectionnez le tableau, vous verrez sur le côté gauche du ruban que la zone « Nom» de la table affiche un nom temporaire comme « Tableau3 ». Remplacez ce nom par un autre nom plus descriptif comme par exemple « Client » et appuyez ensuite sur la touche « Enter » :

7. Répétez les mêmes étapes sur les autres feuilles de calcul que souhaitez utiliser dans le tableau croisé dynamique. Pour cet exemple, nous allons répéter ce processus pour les feuilles « Commandes» et « Paiement » et appelez les tableaux « Commande » et « Paiement ».

Une fois les tableaux bien créés, vous pouvez commencer à créer un tableau croisé dynamique, voici les étapes à suivre.

1. Accédez à la feuille « Paiement» et puis cliquez quelque part dans le tableau.
2. Accédez ensuite à l'onglet « Insertion» et cliquez sur la commande « Tableau croisé dynamique » dans la rubrique « tableaux » :

3. La boîte de dialogue « Créer un tableau croisé dynamique» s’ouvre. Assurez-vous que le nom du tableau est bien saisi dans la zone « Tableau/plage » et puis choisissez si vous voulez que le tableau croisé dynamique soit créé dans une nouvelle feuille ou la même feuille :

4. Ensuite, il est très important de ne pas oublier de cocher la case en regard de l’option « Ajouter ces données au modèle de données» :

5. Cliquez sur « Ok» et un tableau croisé dynamique vide sera créé. Sur ce côté droit, Excel affiche le volet « Champs de tableau croisé dynamique » :

6. Comme vous pouvez le remarquer, le volet « Champs de tableau croisé dynamique» affiche uniquement les champs de la feuille active (la feuille « paiement »). Pour afficher les champs des autres tableaux que vous avez créé, cliquez donc sur l’onglet « Tous » comme illustré dans la capture ci-dessous :

7. Maintenant, vous avez tous les champs de vos tableaux affichés. Cependant, afin de pouvoir les utiliser, vous devrez les connecter les uns aux autres en créant des relations. Pour cela, accédez à l'onglet «Analyse », cliquez sur le menu déroulant « Calculs» puis sur « Relations » :

8. La boite de dialogue « Gérer les relations » s’ouvre, cliquez sur « Nouveau » :

9. Une nouvelle boite de dialogue s’ouvre. Vous pouvez maintenant créer les relations :

10. Dans notre exemple, nous allons créer deux relations en utilisant le champ « N_commande» comme connecteur comme suit :

• Dans le premier menu déroulant « Table » sélectionnez la table actuelle, dans ce cas « Paiement »
• Dans le deuxième menu déroulant « Table associée » sélectionnez la table qui doit être ajoutée au tableau croisé dynamique. Nous choisissons « Client ».
• Dans le menu « Colonne associée » choisissez la colonne de la table actuelle qui est la même dans les deux tables et qu’on appelle également la clé primaire. Nous choisissons « N_commande ».
• Dans le menu « Colonne (externe) », choisissez la colonne qui représente la clé étrangère dans la table associée. Nous choisissons « N_commande ».

Lorsque vous cliquez sur « Ok » vous créez une relation indiquant que les tables « Paiement » et « Client » sont liées là où elles ont des numéros de commande correspondants :

11. Répétez les mêmes étapes pour créer une relation qui joint la table « Paiement» à la table « Commande ». Utilisez également le champ « N_commande » comme clé :

12. Cliquez sur « Ok» pour créer la deuxième relation :

13. Cliquez sur « Fermer».
14. A partir de là, vous pouvez glisser et déplacer les champs selon ce que vous souhaitez afficher et selon les exigences de votre rapport. Les champs peuvent être sélectionnés dans toutes les tables des tableaux croisés dynamiques.
15. Dans notre exemple, nous faisons glisser les champs dans les zones de tableau croisé dynamique comme suit :

• « Date » et « Client » dans les lignes
• « Produit » dans les colonnes
• « Montant » dans les valeurs
• « Ville » dans les filtres

Et voilà ! votre tableau croisé dynamique est bien créé, vous pouvez maintenant l’utiliser et le modifier cela comme n'importe quel autre tableau croisé dynamique.

Méthode 3 : utiliser Microsoft Query

Dans la dernière méthode, nous allons demander l’aide de Microsoft Query pour pouvoir créer le tableau croisé dynamique à partir de plusieurs feuilles de calcul. L’avantage de cette méthode est qu’elle attribue un nom à toutes les données. Nous allons illustrer cela par le même exemple de classeur utilisé dans les autres méthodes. Ci-dessous le processus étape par étape :

1. Accédez à l'onglet « Données», cliquez sur le menu « Données externes », puis sur « Autres sources » et sélectionnez « Microsoft Query » :

2. Dans la boite de dialogue « Choisir une source de donnée» qui s’ouvre, sélectionnez « Excel Files » :

3. Cliquez sur « Ok» et la boite de dialogue « Choisir un classeur » s’ouvre.

4. Cherchez et sélectionnez le classeur contenant les données à utiliser pour le tableau croisé dynamique et cliquez sur « Ok» :

5. Une autre fenêtre « Choisir les colonnes» s'ouvre et affiche les feuilles de calcul contenues dans le classeur choisi. Si par contre vous obtenez une erreur indiquant que la source ne contient pas de données, cliquez sur « Ok », puis dans la boite de dialogue « Choisir les colonnes » cliquez sur le bouton « Options » et assurez-vous que « Table » et « Table système » sont sélectionnées :

6. Les tableaux peuvent être sélectionnés selon l'exigence, comme indiqué ci-dessous :

7. Une fois terminé, cliquez sur « Suivant» ; vous obtiendrez cette boîte de dialogue et cliquez sur « OK » :

8. Lorsque vous cliquez sur « Ok», la fenêtre « Microsoft Query » apparaîtra, et dans laquelle vous verrez afficher toutes les données appartenant à toutes les tables :

9. A partir de là, vous pouvez utiliser ces données regroupées pour créer votre tableau croisé dynamique. Cliquez sur le menu « Fichier» et sélectionnez « Renvoyer les données vers Microsoft Excel » :

10. La boite de dialogue « Importation de données» s’ouvre, et comme nous voulons créer un tableau croisé dynamique, sélectionnez l'option « Rapport de tableau croisé dynamique » et sélectionnez « Nouvelle feuille de calcul » pour mettre le tableau résultant dans une nouvelle feuille, sinon sélectionnez « Feuille de calcul existante » :

11. Maintenant, le tableau croisé dynamique est prêt et vous pouvez le modifier selon vos exigences.