Comment créer des modèles de données dans Excel : guide complet

Les modèles de données permettent de charger, d'intégrer et d'analyser des données issues de multiples sources, pour une vision plus dynamique et éclairante.

Dans ce tutoriel, nous allons parcourir plusieurs façons de créer des modèles de données dans Excel : à partir de tables existantes, en important de nouvelles tables et en exploitant des outils comme Power Query, Power Pivot et les graphiques croisés dynamiques.

Allons-y !

Comment créer un modèle de données dans Excel

Voici les étapes générales pour créer un modèle de données dans Excel :

1. Mettre en forme vos données en tables.
2. Créer des relations entre les tables.
3. Utiliser Power Query pour charger et transformer les données.
4. Exploiter Power Pivot pour une modélisation avancée.
5. Utiliser les tableaux croisés dynamiques et graphiques croisés dynamiques pour visualiser vos données.

Quand utiliser un modèle de données dans Excel

Créer un modèle de données dans Excel est particulièrement utile dans plusieurs cas d'usage courants : nettoyage des données, intégration et transformation, ainsi qu'analyse complexe. Voici les principaux atouts :

• Facilite le nettoyage des données : les modèles de données aident à nettoyer et standardiser différents jeux de données pour garantir l'uniformité et la cohérence, en particulier lorsque les sources sont multiples et nécessitent un mélange et un prétraitement.
• Simplifie l'intégration et la transformation : avec un modèle en place, toutes les données disponibles peuvent être intégrées et transformées facilement, offrant aux utilisateurs une vue unifiée, simple à traiter et à analyser.
• Améliore la visualisation des données : les modèles de données prennent en charge la création de visualisations avancées comme les graphiques croisés dynamiques et les rapports Power View.
• Renforce la sécurité et la gouvernance : avec un modèle de données, vous pouvez contrôler l'accès à différentes parties de vos données. La gestion des autorisations et des données est facilitée, réduisant les risques d'accès non autorisé et de fuite d'informations.
• Fluidifie le reporting : grâce aux modèles de données, le reporting peut être largement automatisé, ce qui accélère les processus de reporting et de décision. Les rapports et tableaux de bord s'ajustent dynamiquement à l'arrivée de nouvelles données pour fournir des informations à jour à toutes les parties prenantes.
• Gère de grands volumes de données : les modèles de données permettent de travailler avec de gros jeux de données dans Excel, au-delà des limites des feuilles classiques.
• Permet des analyses complexes : la création de relations entre plusieurs tables rend possibles des analyses avancées difficiles à réaliser avec les fonctions Excel classiques.

Comment créer un modèle de données en important des données

Dans cette section, nous allons créer un modèle de données dans Excel en important des jeux d'exemples. Nous utiliserons deux jeux de données : Order et Product. J'ai créé un dépôt GitHub public nommé Excel-Data-Model pour que vous puissiez télécharger les jeux et suivre le tutoriel.

La première étape consiste à importer les données dans Excel. Ouvrez un nouveau classeur Excel et cliquez sur l'onglet Données. Sous Récupérer des données, sélectionnez Récupérer des données > À partir d'un fichier > À partir d'un classeur Excel.

Importer des données dans Excel. Image : auteur

Sélectionnez le classeur Order.xlsx, puis cliquez sur Importer. La fenêtre Navigateur s'ouvre : sélectionnez la table Sheet1.

Fenêtre Navigateur dans Excel. Image : auteur

Cliquez sur l'option Charger vers pour ouvrir la fenêtre Importer des données. Dans la boîte de dialogue, cochez Créer uniquement une connexion et Ajouter ces données au modèle de données. Cliquez sur OK.

Fenêtre Importer des données. Image : auteur

Répétez les étapes ci-dessus pour charger la feuille Product. Une fois terminé, votre classeur Excel ressemblera à ceci :

Volet Requêtes et connexions. Image : auteur

Félicitations ! Vous avez importé les deux feuilles dans le fichier Excel. L'étape suivante consiste à établir des relations entre les tables importées. Pour cela, rendez-vous dans l'onglet Outils de données et cliquez sur Relations.

Outils de données. Image : auteur 

Une nouvelle fenêtre s'affiche.

Fenêtre Gérer les relations. Image : auteur

Cliquez sur Nouveau pour créer une relation. La fenêtre contextuelle Créer une relation s'ouvre. Définissez la relation comme ci-dessous.

Fenêtre Créer une relation. Image : auteur

Cliquez sur OK. La relation est créée et vous pouvez la vérifier dans l'onglet Connexions.

Volet Requêtes et connexions. Image : auteur

L'étape suivante consiste à créer le tableau croisé dynamique. Allez dans l'onglet Insertion et cliquez sur Tableau croisé dynamique. Dans la boîte de dialogue, sélectionnez Utiliser le modèle de données de ce classeur.

Boîte de dialogue Tableau croisé dynamique. Image : auteur

Choisissez l'emplacement du tableau croisé dynamique. Cliquez sur OK. Le tableau s'ouvre et vous voyez les champs dans le volet Champs de tableau croisé dynamique.

Champs du tableau croisé dynamique. Image : auteur

Pour construire le tableau croisé dynamique, procédez comme suit :

Dans le volet des champs, faites glisser les champs vers les zones Lignes, Colonnes, Valeurs et Filtres :

• Lignes : ProductName
• Valeurs : Quantity

Le tableau croisé dynamique obtenu ressemblera à ceci :

Volet des champs du tableau croisé dynamique. Image : auteur

Vous pouvez personnaliser et mettre en forme le tableau selon vos besoins.

Comment créer un modèle de données avec Power Query

Dans cette section, nous allons utiliser les données stockées dans le fichier Excel nommé Sample_Data, disponible via le même lien que précédemment. La feuille Data contient deux tables avec les enregistrements suivants.

Tables Excel à joindre. Image : auteur

Nous allons construire des modèles de données directement dans cette feuille. Pour commencer, sélectionnez une cellule de la table Customer ID and Name puis appuyez sur Ctrl + T.

Sélection d'une cellule. Image : auteur

Une fenêtre Créer un tableau s'ouvre comme ci-dessous.

Fenêtre Créer un tableau. Image : auteur

Cliquez sur OK pour créer la table. 

Tableau croisé dynamique dans Excel. Image : auteur

Attribuons un nom à cette table. Ouvrez la fenêtre contextuelle Création de tableau et, dans Nom du tableau, renommez-la en CustomerPQ (ou un autre nom de votre choix).

Fenêtre contextuelle Création de tableau. Image : auteur

La table est maintenant nommée CustomerPQ.

Fenêtre contextuelle Création de tableau. Image : auteur

Répétez l'opération pour la seconde table. Cette fois, nommez la table PurchasePQ.

Deux tables prêtes pour le modèle de données. Image : auteur

Activez la table PurchasePQ. Allez dans Données et sélectionnez À partir d'un tableau/plage. La fenêtre suivante apparaît.

Ouverture de l'éditeur Power Query. Image : auteur

Pour créer une connexion et ajouter la table PurchasePQ au modèle de données, ouvrez le menu déroulant Fermer et charger, puis choisissez Fermer et charger vers…

Fermeture de l'éditeur Power Query. Image : auteur

La fenêtre Importer des données s'ouvre ; renseignez-la comme suit :

Fenêtre Importer des données. Image : auteur 

Après validation, vous verrez la boîte Requêtes et connexions.

Boîte Requêtes et connexions. Image : auteur

Ajoutez maintenant la table CustomerPQ à la connexion. Pour cela, cliquez sur une cellule de la table, allez dans Données, puis À partir d'un tableau/plage. La fenêtre suivante s'affiche.

Tables prîtes pour le modèle de données Excel. Image : auteur

Répétez la même procédure que pour PurchasePQ et renseignez la fenêtre Importer des données comme ci-dessous :

Fenêtre Importer des données. Image : auteur 

Cliquez sur OK. L'onglet Requêtes et connexions affiche désormais les deux tables.

Requêtes et connexions. Image : auteur

Nous pouvons maintenant établir les relations. Dans l'onglet Données, section Outils de données, cliquez sur Gérer le modèle de données.

Outils de données. Image : auteur

Cela ouvre Power Pivot for Excel.

Power Pivot for Excel. Image : auteur

Pour créer une relation entre les deux tables, cliquez sur la Vue Diagramme.

Volet Affichage. Image : auteur

Vous obtenez un écran semblable à celui-ci.

Vue Diagramme. Image : auteur

Vous voyez deux tables, encore non reliées. Pour créer la relation, allez dans l'onglet Conception et cliquez sur Créer une relation.

Onglet Conception. Image : auteur

Une nouvelle fenêtre s'ouvre. Renseignez les champs comme suit :

Fenêtre Créer une relation. Image : auteur

Cliquez sur OK. La relation est maintenant établie.

Relation établie. Image : auteur 

Enregistrez Power Pivot via l'icône de sauvegarde ou avec Ctrl + S, puis fermez l'onglet. La sortie suivante s'affiche.

Fenêtre Activer le contenu. Image : auteur

Cliquez sur Activer le contenu pour créer la connexion. La connexion est établie et vous pouvez maintenant utiliser un tableau croisé dynamique. Pour cela, allez dans Insertion, cliquez sur Tableau croisé dynamique et choisissez À partir du modèle de données.

Volet Tableau croisé dynamique. Image : auteur

Vous verrez apparaître cette fenêtre :

Tableau croisé dynamique depuis le modèle de données. Image : auteur

Cliquez sur Nouvelle feuille de calcul puis sur OK. Une nouvelle feuille nommée Sheet1 est créée.

Feuille de tableau croisé dynamique. Image : auteur 

Dans les Champs de tableau croisé dynamique, vous retrouvez les tables.

Libellés dans les champs du tableau croisé dynamique. Image : auteur

Sélectionnez Customer Name dans la table CustomerPQ, puis, dans PurchasePQ, sélectionnez Purchase. Les Champs de tableau croisé dynamique apparaissent ainsi :

Sélection d'éléments dans les champs. Image : auteur 

Le tableau croisé dynamique généré dans la feuille ressemble à ceci :

Résultat du tableau croisé dynamique. Image : auteur

Parfait ! Ainsi, vous pouvez utiliser Power Query pour créer le modèle de données et Power Pivot pour produire des rapports synthétiques.

Comment créer un modèle de données directement dans Power Pivot

Dans cette section, vous allez utiliser Power Pivot pour créer un modèle de données et générer un rapport récapitulatif.

Pour commencer, rendez-vous sur la feuille nommée Power Pivot dans les données évoquées plus haut et créez les tables pour nos deux enregistrements.

Enregistrements à utiliser. Image : auteur 

Nous avons déjà vu comment créer des tables ; reprenez ces étapes pour créer les deux tables.

Seule différence : nous allons attribuer de nouveaux noms pour les distinguer des sections précédentes :

• Nommer la première table CustomerPP.
• Nommer la seconde table PurchasePP.

Données Tables issues des enregistrements. Image : auteur

Les deux tables sont créées. L'étape suivante est de les ajouter au modèle de données. Pour ce faire, allez dans Power Pivot et sélectionnez Ajouter au modèle de données.

Ajouter au modèle de données dans PowerPivot. Image : auteur

Une fenêtre Power Pivot for Excel s'ouvre et ressemble à ceci.

Power Pivot for Excel. Image : auteur 

Enregistrez et fermez cette fenêtre. Répétez la même opération pour la table CustomerPP afin de l'ajouter au modèle.

Power Pivot for Excel. Image : auteur

Une fois les tables ajoutées au modèle, vous pouvez établir la relation entre elles dans Power Pivot. Pour cela, cliquez sur Vue Diagramme.

Vue Diagramme. Image : auteur

Ensuite, allez dans l'onglet Conception et cliquez sur Créer une relation.

Onglet Créer une relation. Image : auteur

Une nouvelle fenêtre s'ouvre. Renseignez les champs comme ci-dessous :

Créer une relation entre deux tables. Image : auteur

Cliquez sur OK. Vous constatez que la relation est établie.

Relations établies. Image : auteur

Il est ensuite facile de créer un tableau croisé dynamique, comme vu précédemment. Le résultat final devrait ressembler à ceci.

Récapitulatif du tableau croisé dynamique. Image : auteur

En suivant ces étapes, vous pouvez utiliser Power Pivot pour créer le modèle de données et réaliser une analyse synthétique.

Créer un modèle de données avec un tableau croisé dynamique ou un graphique croisé dynamique

Une fois le tableau croisé dynamique créé, l'insertion d'un graphique croisé dynamique est très simple. Avec le tableau croisé dynamique (Sheet 1) sélectionné, allez dans l'onglet Insertion.

Récapitulatif du tableau croisé dynamique. Image : auteur

Cliquez sur Graphique croisé dynamique, choisissez le type de graphique et cliquez sur OK.

Options d'insertion de graphique. Image : auteur

Le graphique croisé dynamique suivant est alors créé.

Visualisation en graphique croisé dynamique. Image : auteur

Vous pouvez personnaliser le graphique croisé dynamique en :

• Ajustant les éléments du graphique (titres, légendes, étiquettes d'axes), et
• Formatant le graphique pour améliorer son apparence et sa lisibilité.

Bravo, vous avez réalisé votre première visualisation à partir d'un tableau croisé dynamique.

Questions et problèmes courants liés au modèle de données Excel

Voici quelques questions et problèmes fréquemment rencontrés avec les modèles de données Excel :

Que faire si un problème de modèle de données empêche Microsoft Excel d'ouvrir un classeur ?

Le problème peut provenir d'une corruption du modèle ou d'incompatibilités. Voici des pistes pour le résoudre :

1. Ouvrir en mode sans échec : lancez Excel en maintenant la touche Ctrl. Cela peut parfois contourner le blocage.
2. Vérifier les mises à jour : assurez-vous qu'Excel est à jour via Fichier > Compte > Options de mise à jour.
3. Réparer Office : utilisez l'outil de réparation Office via Panneau de configuration > Programmes et fonctionnalités, sélectionnez votre installation Office puis Modifier > Réparer.
4. Désactiver les compléments : allez dans Fichier > Options > Compléments et désactivez-les tous. Redémarrez Excel puis essayez d'ouvrir le classeur.
5. Extraire les données : si l'ouverture échoue toujours, tentez un outil de récupération de données ou contactez le support Microsoft pour une aide avancée.

Comment résoudre les erreurs dans le modèle de données ?

Pour dépanner votre modèle de données :

1. Vérifier les types de données : assurez-vous que les types de données des colonnes utilisées dans les relations sont cohérents.
2. Valider les relations : contrôlez que toutes les relations sont correctement définies.
3. Revoir les calculs : vérifiez les colonnes calculées et les mesures pour détecter d'éventuelles erreurs.
4. Actualiser les données : assurez-vous que toutes les connexions de données sont à jour.
5. Messages d'erreur : consultez les messages d'erreur dans Power Pivot pour cibler les problèmes spécifiques.

En suivant ces étapes, vous gérerez et dépannerez efficacement votre modèle de données dans Excel pour une analyse fluide et performante. Microsoft Support est également une ressource utile pour le dépannage.

Pour conclure

Merci d'avoir suivi ce tutoriel ! Vous avez acquis une notion clé sur votre chemin vers la maîtrise de l'analyse de données avec Excel. Créer des modèles de données renforcera nettement vos capacités d'analyse, ainsi que celles de votre organisation, tout en vous rendant plus efficace dans la gestion de tables et d'enregistrements multiples.

Pour aller plus loin, explorez les ressources suivantes :

• Introduction to Excel : un guide complet pour bien débuter avec Excel, couvrant les fonctionnalités essentielles.
• Data Analysis in Excel : découvrez différentes techniques et outils d'analyse dans Excel pour développer vos compétences analytiques.
• Excel Fundamentals : un parcours pour couvrir les fondamentaux et bâtir des bases solides.
• Data Visualization in Excel : explorez la création de visualisations percutantes et interactives avec les outils intégrés d'Excel.
• Introduction to Power Query in Excel : comprenez comment utiliser Power Query pour importer, nettoyer et transformer efficacement vos données dans Excel.