Excel - Power Query - Power Pivot - VBA

Excel - Power Query - Power Pivot - VBA

A à Z : créer un classeur d'analyse [Exemple 002]

Gonzague Ducos

Avec cette méthode, nous mettons les données et les TCD dans le même classeur, pour simplifier les explications.

Télécharger l’exercice complété :

GonzagueDUCOS_SupportExcel_Exercice002.xlsx

⁠

(Clic droit > Download)

1 | Préparer l’organisation de vos données

Objectif

Il faut organiser ses données en table et colonne, en suivant les règles suivantes :

Une table contient des “objets” de même nature.

Une table “Véhicules” contiendra une ligne par véhicule.

Une table “Salariés” contiendra une ligne par salarié.

2 types de table :

Des tables de dimension,

Une table de faits

La table de fait est reliée aux tables de dimension par des clés (codes, numéros, etc.), unique du côté des dimensions.

⁠

Chaque objet aura une colonne contenant un identifiant unique (ou clé unique)

Un véhicule = une immatriculation, un numéro de parc...

Un salarié = idéalement un matricule, sinon un nom & prénom.

Un contrat = un numéro de contrat.

L’identifiant de la table sera sans doublon.

Si un salarié quitte l’entreprise puis revient, il ne doit pas être en double dans la table. On mettra 2 lignes dans une table Contrat, reliée au salarié.

Une table sera reliée à une autre table par leur champ Clé et leur Champ de recherche.

Dans la table Véhicule, on a une colonne Immat, en saisie.

Dans une table Utilisation (du véhicule), on a une colonne Immat

Exemple

Vous pouvez dessiner ces tables et colonnes dans n’importe quel support : Excel, Word, une feuille de papier, etc.

Autre possibilité : aller dans

diagram.io,⁠

créer un “blanck diagram”, faire glisser des formes, et saisir à l’intérieur le nom de la table, et en plus petit, la colonne principale et quelques colonnes de contenu.

Exemple d’un projet de gestion d’utilisation des véhicule de société. On veut tracer l’utilisation d’un véhicule par un collaborateur, et stocker la date et la distance parcourue.

⁠

Les tables Véhicule et Collaborateur sont des tables de dimension : elles n’ont pas de listes déroulantes.

La table Utilisation est une table de faits. Elle contient les nombres, les quantités, les volumes. Ici la table contient le nombre de km, que l’on pourra ensuite additionner par véhicule et par collaborateur. Le nombre de ligne de la table nous indiquera le nombre de fois où un véhicule a été utilisé et un collaborateur a utilisé un véhicule.

2 | Créer des tables et améliorer la saisie

Cf.

Tableau dans Excel⁠

Création d’une table

Dans une feuille vide, sur A1 :

Insertion > Table > OK

Créer les colonnes :

La première colonne doit être la clé de la table (l’identifiant unique), sauf dans la table de fait (qui peut ne pas avoir de clé unique).

Éviter les noms longs, les espaces et les accents.

Nommer le tableau

Clic dans le Tableau

Création de Tableau > Zone Nom du tableau : saisir un nom sans espace ni accent, précédé de tbl : tblSalarie, tblContrat, tblCollaborateur, tblUtilisation, tblRendezVous, etc. Appuyer sur Entrée.

Nommer la feuille “Liste des “... (Liste des véhicules par exemple)

Créer des listes déroulantes

Cf.

Valider ses données⁠

Noter le nom du tableau et la colonne de ce tableau qui seront utilisés dans la liste déroulante.

Sélectionner les données de la colonne :

⁠

Données > Validation de données : saisir la formule :

=INDIRECT("NomDuTableau[NomDeLaColonne]")

⁠

Le résultat sera le suivant :

⁠

Formater les autres colonnes.

Sélectionner les données de la colonne.

Données > Validation de données

Sélectionner un élément dans la liste Autoriser : Date, Nombre entier, Décimal, etc. Il est obligatoire d’indiquer ensuite une borne, dans la liste Données. Par exemple Supérieur à, avec pour valeur 0 (zéro) ou 01/01/2000.

3 | Créer les Tableaux Croisés

Cf.

Tableaux croisées dynamiques⁠

Les TCD devront pouvoir utiliser des colonnes de différents tableaux. Il faudra donc indiquer la relation entre chaque tableau.

Cliquer dans un des Tableaux.

Insertion > Tableau croisé dynamique.

Cocher Ajouter ces données au modèle de données et cliquer sur OK.

⁠

Cliquer sur Tous (1), puis faites glisser des champs de tables différentes dans le TCD (2)

Excel constate qu’il n’y a pas de relation entre les 2 tables : dans le bandeau jaune, cliquer sur Créer ou sur Détection automatique (3) :

⁠

Dans la fenêtre Détecter automatiquement les relations, constater la création de la relation. Si la relation n’est pas détectée automatiquement, cliquer sur Gérer les relations et créer manuellement la relation.

⁠

Constater dans le TCD que les résultats sont bons.

Pour créer et gérer les relations plus facilement :

Vérifier que l’onglet Power Pivot est affiché. Si ce n’est pas le cas :

Afficher l’onglet Développeur : clic droit sur le Ruban > Personnaliser le ruban puis cocher Développeur et valider.

Onglet Développeur > Compléments COM, cocher Microsoft Power Pivot pour Excel.

Ajouter chaque Tableau au modèle de données. Pour chaque Tableau du classeur :

Cliquer sur un Tableau.

Dans l’onglet Power Pivot, cliquer sur Ajouter au modèle de données. Si un Tableau est déjà dans le Modèle de données, un message d’erreur s’affiche. Vous pouvez laisser afficher la fenêtre Power Pivot qui s’affiche à chaque ajout.

Afficher la fenêtre Power Pivot.

Données > Gérer le modèle de données (bouton vert). Power Pivot pour Excel s’ouvre.

Accueil > Vue de diagramme.

Faire glisser les champs pour créer une relation :

⁠