 |
 |
 |
Tableaux croisés dynamiques
- Sources multiples - Cas complexes - Solution Power Query
- Power Query, disponible en add on pour Excel 2010 et 2013, intégré à 2016, donne également la possibilité de combiner des tables ou listes de données pour réaliser des Tableaux Croisés Dynamiques.
La solution qui suit ne constitue pas une initiation à Power Query mais une illustration de la possibilité de traiter plusieurs sources.
- Power Query permet d'accéder à toutes sortes de données assimilables à des tables : plages et tableaux Excel, tables et vues de bases de données, fichiers texte exportés, tableaux Internet... et accessoirement de créer de petites tables.
- Cet outil permet de réaliser l'équivalent d'une requête union de deux tables de même structure. On peut :
- soit créer un lien vers chaque table (lien considéré comme Requête par Power Query)
puis une troisième requête combinant l'ensemble des enregistrements des tables précédentes
- soit modifier la requête initiale pour combiner directement la ou les autres tables.
Cette seconde méthode est plus complexe car le langage de Power Query est particulier.
- les Requêtes peuvent être faites soit à partir de tableaux du classeur courant (solution la plus simple illustrée ici)
- soit en accédant au fichier depuis le même classeur ou depuis un autre.
Dans ce cas Excel considère les colonnes complètes que les données soient sous forme de plage ou de tableau.
- Pour des données issues de deux tableaux similaires, comme les ventes de l'année N et celles de l'année N-1 par exemple, nous allons nous positionner dans une cellule du premier tableau nommé Ventes_N_1 et depuis l'onglet Power Query, utiliser le bouton A partir du tableau
Il est plus que conseillé de forcer le format des dates afin de ne pas avoir à le faire en aval
Il est possible de supprimer les colonnes inutiles pour le TCD, comme ici le Prix unitaire (1)
A noter que si PowerPivot est également disponible (en add on sur Excel 2010 et intégré à partir d'Excel 2013) on peut ajouter les données au modèle de données PowerPivot(2).
- Si le choix est Charger dans la feuille de calcul, un nouvel onglet est créé.
Il contient un tableau similaire au tableau source
Si, l'on a choisi Connexion seulement, la requête peut être exploitée dans les tableaux croisés dynamiques, sans avoir à l'afficher dans une feuille de calcul. - On effectue la même opération sur le tableau correspondant à l'année N.
- Pour combiner les deux tables, dans l'onglet power Query, on clique sur le bouton Ajouter puis on sélectionne les Requêtes
puis on choisit un nom pour la requête cumulée, Tout par exemple.
Si on a opté pour le choix "Charger dans la feuille de calcul", cela crée un 3ème onglet avec l'ensemble des lignes issus des deux tables.
- Il ne reste plus qu'à élaborer le TCD
- Si, à chaque étape, on a opté pour le choix "Charger dans le modèle de données", toutes les tables sont créées dans PowerPivot et c'est depuis l'interface PowerPivot qu'on lancera la création du TCD (à réserver aux versions Excel donnant accès à cet interface).
Si on a opté pour Connexion seulement, on pourra construire le TCD sur la connexion.
- Si l'on souhaite utiliser plus de deux tableaux, 3 années ou plus par exemple, après obtention des tableaux Power Query correspondant à chaque année, le plus simple est d'éditer et compléter la requête Tout manuellement :
clic droit sur le nom de la requête Tout dans le volet de droite, Modifier, puis dans l'onglet Affichage de la fenête de l'éditeur de requête, utiliser Editeur avancé :
