Tableau croisé dynamique sur plusieurs plages de données

Formulation du problème

Les tableaux croisés dynamiques sont l'un des outils les plus étonnants d'Excel. Mais jusqu'à présent, malheureusement, aucune des versions d'Excel ne peut faire une chose aussi simple et nécessaire à la volée que de créer un résumé pour plusieurs plages de données initiales situées, par exemple, sur différentes feuilles ou dans différents tableaux :

Avant de commencer, clarifions quelques points. A priori, je pense que les conditions suivantes sont remplies dans nos données :

• Les tableaux peuvent avoir n'importe quel nombre de lignes avec n'importe quelles données, mais ils doivent avoir le même en-tête.
• Il ne doit pas y avoir de données supplémentaires sur les feuilles avec les tables source. Une feuille – un tableau. Pour contrôler, je vous conseille d'utiliser un raccourci clavier Ctrl+Fin, qui vous amène à la dernière cellule utilisée dans la feuille de calcul. Idéalement, il devrait s'agir de la dernière cellule du tableau de données. Si lorsque vous cliquez sur Ctrl+Fin toute cellule vide à droite ou en dessous du tableau est mise en surbrillance - supprimez ces colonnes vides à droite ou les lignes sous le tableau après le tableau et enregistrez le fichier.

Méthode 1 : Créer des tables pour un pivot à l'aide de Power Query

À partir de la version 2010 pour Excel, il existe un complément Power Query gratuit qui peut collecter et transformer toutes les données, puis les donner comme source pour créer un tableau croisé dynamique. Résoudre notre problème à l'aide de ce complément n'est pas difficile du tout.

Tout d'abord, créons un nouveau fichier vide dans Excel - l'assemblage y aura lieu, puis un tableau croisé dynamique y sera créé.

Puis sur l'onglet Données (si vous avez Excel 2016 ou version ultérieure) ou sur l'onglet Requête d'alimentation (si vous avez Excel 2010-2013) sélectionnez la commande Créer une requête - À partir d'un fichier - Excel (Obtenir des données - À partir d'un fichier - Excel) et précisez le fichier source avec les tables à collecter :

Dans la fenêtre qui apparaît, sélectionnez n'importe quelle feuille (peu importe laquelle) et appuyez sur le bouton ci-dessous Modifier (Edit):

La fenêtre de l'éditeur de requête Power Query doit s'ouvrir au-dessus d'Excel. Sur le côté droit de la fenêtre sur le panneau Paramètres de demande supprimer toutes les étapes créées automatiquement sauf la première - Identifier (La source):

Nous voyons maintenant une liste générale de toutes les feuilles. Si, en plus des fiches techniques, il existe d'autres feuilles annexes dans le fichier, alors à cette étape, notre tâche consiste à sélectionner uniquement les feuilles à partir desquelles les informations doivent être chargées, en excluant toutes les autres en utilisant le filtre dans l'en-tête du tableau :

Supprimer toutes les colonnes sauf la colonne Donnéesen cliquant avec le bouton droit sur un en-tête de colonne et en sélectionnant Supprimer les autres colonnes (Retirer autres colonnes):

Vous pouvez ensuite dérouler le contenu des tableaux collectés en cliquant sur la double flèche en haut de la colonne (case à cocher Utiliser le nom de la colonne d'origine comme préfixe tu peux l'éteindre):

Si vous avez tout fait correctement, à ce stade, vous devriez voir le contenu de tous les tableaux collectés les uns en dessous des autres :

Il reste à remonter la première ligne jusqu'à l'en-tête du tableau avec le bouton Utiliser la première ligne comme en-tête (Utilisez la première ligne comme en-têtes) languette ACCUEIL (Accueil) et supprimez les en-têtes de tableau en double des données à l'aide d'un filtre :

Enregistrez tout ce qui a été fait avec la commande Fermer et charger – Fermer et charger en… (Fermer et charger — Fermer et charger dans…) languette ACCUEIL (Accueil), et dans la fenêtre qui s'ouvre, sélectionnez l'option Connexion uniquement (Connexion uniquement):

Tout. Il ne reste plus qu'à construire un résumé. Pour cela, rendez-vous dans l'onglet Insérer – Tableau croisé dynamique (Insérer - Tableau croisé dynamique), choisissez l'option Utiliser une source de données externe (Utiliser une source de données externe)puis en cliquant sur le bouton Sélectionnez la connexion, notre demande. La création et la configuration ultérieures du pivot se produisent de manière tout à fait standard en faisant glisser les champs dont nous avons besoin dans la zone des lignes, des colonnes et des valeurs :

Si les données source changent à l'avenir ou que quelques fiches de magasin supplémentaires sont ajoutées, il suffira alors de mettre à jour la requête et notre résumé à l'aide de la commande Rafraîchir tout languette Données (Données — Actualiser tout).

Méthode 2. Nous unissons les tables avec la commande UNION SQL dans une macro

Une autre solution à notre problème est représentée par cette macro, qui crée un ensemble de données (cache) pour le tableau croisé dynamique à l'aide de la commande UNITÉ Langage de requête SQL. Cette commande combine les tables de toutes celles spécifiées dans le tableau NomsFeuilles feuilles du livre en un seul tableau de données. C'est-à-dire qu'au lieu de copier et coller physiquement des plages de différentes feuilles vers une seule, nous faisons la même chose dans la RAM de l'ordinateur. Ensuite, la macro ajoute une nouvelle feuille avec le nom donné (variable NomFeuilleRésultats) et crée un résumé complet (!) basé sur le cache collecté.

Pour utiliser une macro, utilisez le bouton Visual Basic de l'onglet promoteur (Développeur) ou raccourci clavier autre+F11. Ensuite, nous insérons un nouveau module vide via le menu Insertion – Module et copiez-y le code suivant :

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'nom de la feuille où le pivot résultant sera affiché ResultSheetName = "Pivot" 'un tableau de feuilles noms avec les tables source SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'nous formons un cache pour les tables des feuilles de SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With 'recréer la feuille pour afficher le tableau croisé dynamique résultant On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'affiche le résumé du cache généré sur cette feuille Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3").Select End With End Sub    

La macro terminée peut ensuite être exécutée avec un raccourci clavier autre+F8 ou le bouton Macros de l'onglet promoteur (Développeur — Macros).

Inconvénients de cette approche :

• Les données ne sont pas mises à jour car le cache n'a aucune connexion avec les tables source. Si vous modifiez les données source, vous devez réexécuter la macro et créer à nouveau le récapitulatif.
• Lors de la modification du nombre de feuilles, il est nécessaire d'éditer le code macro (tableau NomsFeuilles).
  

Mais au final on obtient un vrai tableau croisé dynamique à part entière, construit sur plusieurs plages de feuilles différentes :

Voilà!

Note technique: Si vous obtenez une erreur telle que "Fournisseur non enregistré" lors de l'exécution de la macro, vous avez très probablement une version 64 bits d'Excel ou une version incomplète d'Office est installée (pas d'accès). Pour résoudre le problème, remplacez le fragment dans le code de la macro :

	 Fournisseur = Microsoft.Jet.OLEDB.4.0;  

à:

	Fournisseur=Microsoft.ACE.OLEDB.12.0 ;  

Et téléchargez et installez le moteur de traitement de données gratuit à partir d'Access depuis le site Web de Microsoft - Microsoft Access Database Engine 2010 Redistributable

Méthode 3 : Consolider l'assistant de tableau croisé dynamique à partir d'anciennes versions d'Excel

Cette méthode est un peu dépassée, mais mérite quand même d'être mentionnée. Formellement parlant, dans toutes les versions jusqu'à 2003 inclus, il y avait une option dans l'assistant de tableau croisé dynamique pour "construire un pivot pour plusieurs plages de consolidation". Cependant, un rapport construit de cette manière ne sera malheureusement qu'un pitoyable semblant d'un véritable résumé à part entière et ne supporte pas beaucoup des "puces" des tableaux croisés dynamiques conventionnels :

Dans un tel pivot, il n'y a pas d'en-têtes de colonne dans la liste des champs, il n'y a pas de paramètre de structure flexible, l'ensemble des fonctions utilisées est limité et, en général, tout cela n'est pas très similaire à un tableau croisé dynamique. C'est peut-être la raison pour laquelle, à partir de 2007, Microsoft a supprimé cette fonction de la boîte de dialogue standard lors de la création de rapports de tableau croisé dynamique. Désormais, cette fonctionnalité n'est disponible que via un bouton personnalisé Assistant de tableau croisé dynamique(Assistant Tableau croisé dynamique), qui, si vous le souhaitez, peut être ajouté à la barre d'outils d'accès rapide via Fichier - Options - Personnaliser la barre d'outils d'accès rapide - Toutes les commandes (Fichier — Options — Personnaliser la barre d'outils d'accès rapide — Toutes les commandes):

Après avoir cliqué sur le bouton ajouté, vous devez sélectionner l'option appropriée à la première étape de l'assistant :

Et puis dans la fenêtre suivante, sélectionnez tour à tour chaque plage et ajoutez-la à la liste générale :

Mais, encore une fois, ce n'est pas un résumé à part entière, alors n'en attendez pas trop. Je ne peux recommander cette option que dans des cas très simples.

• Création de rapports avec des tableaux croisés dynamiques
• Configurer des calculs dans des tableaux croisés dynamiques
• Que sont les macros, comment les utiliser, où copier le code VBA, etc.
• Collecte de données de plusieurs feuilles à une (module complémentaire PLEX)