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

Les modèles de données permettent de charger, intégrer et analyser des données issues de multiples sources, pour une lecture plus dynamique et plus riche des informations. 

Dans ce tutoriel, nous verrons plusieurs façons de créer des modèles de données dans Excel, notamment à partir de tableaux existants, en important de nouveaux tableaux et en tirant parti d’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 grandes étapes à suivre pour créer un modèle de données dans Excel :

\n
1. Mettez en forme vos données en tableaux.
\n
2. Créez des relations entre les tableaux.
\n
3. Utilisez Power Query pour charger et transformer les données.
\n
4. Exploitez Power Pivot pour une modélisation avancée.
\n
5. Créez des tableaux et graphiques croisés dynamiques pour visualiser vos données.
\n

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 courants : nettoyage des données, intégration et transformation, analyses complexes. Voici les principaux bénéfices.

\n
• 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, notamment lorsqu’il faut fusionner et prétraiter des sources hétérogènes. 
\n
• Simplifie l’intégration et la transformation des données : avec un modèle en place, toutes les données disponibles depuis d’autres sources peuvent être intégrées et transformées facilement, offrant aux utilisateurs une vue unifiée, prête à analyser.
\n
• Améliore la visualisation : les modèles de données permettent de créer des visualisations avancées comme les graphiques croisés dynamiques et les rapports Power View. 
\n
• Renforce la sécurité et la gestion : avec un modèle de données, vous pouvez contrôler l’accès à différentes parties de vos informations, gérer plus simplement les droits et réduire les risques d’accès non autorisé ou de fuite.
\n
• Fluidifie le reporting : grâce aux modèles de données, la production de rapports peut être automatisée, ce qui facilite le suivi et la prise de décision. Rapports et tableaux de bord s’actualisent dynamiquement lors de l’ajout de nouvelles données, pour fournir aux parties prenantes des informations à jour.
\n
• Gère de grands volumes : les modèles de données prennent en charge de gros jeux de données dans Excel, au-delà des limites des feuilles classiques.
\n
• Permet des analyses complexes : la création de relations entre plusieurs tables ouvre la voie à des analyses avancées difficiles à réaliser avec les fonctions Excel classiques.
\n

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

Dans cette partie, nous allons parcourir la création d’un modèle de données dans Excel en important des jeux d’essai. Nous utiliserons deux jeux : Order et Product. J’ai créé un dépôt public GitHub nommé Excel-Data-Model pour télécharger les données et vous permettre de suivre le tutoriel.

La première étape consiste à importer les données dans Excel. Ouvrez un nouveau classeur, puis cliquez sur l’onglet Données. Sous Obtenir des données, sélectionnez Obtenir des données > Depuis un fichier > Depuis un classeur Excel.

Importation de données dans Excel. Image par l’auteur

Sélectionnez le classeur Order.xlsx et cliquez sur Importer. La fenêtre Volet de navigation s’ouvre : choisissez la table Sheet1.

Fenêtre de navigation dans Excel. Image par l’auteur

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

Fenêtre Importer des données. Image par l’auteur

Répétez les mêmes étapes pour charger la feuille Product. Une fois terminé, votre classeur Excel ressemblera à ceci :

Volet Requêtes et connexions. Image par l’auteur

Bravo ! Vous avez importé les deux feuilles dans le fichier Excel. L’étape suivante consiste à créer des relations entre les tables importées. Pour cela, allez dans l’onglet Outils de données et cliquez sur Relations.

Onglet Outils de données. Image par l’auteur 

Une nouvelle fenêtre s’ouvre.

Fenêtre Gérer les relations. Image par l’auteur

Cliquez sur Nouveau pour créer une relation. La boîte Créer une relation s’ouvre. Définissez la relation comme ci-dessous. 

Fenêtre Créer une relation. Image par l’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 par l’auteur

L’étape suivante est de 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 par l’auteur

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

Champs de tableau croisé dynamique. Image par l’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 :

\n
• Lignes : ProductName
\n
• Valeurs : Quantity
\n

Le tableau croisé obtenu ressemblera à ceci :

Volet des champs. Image par l’auteur

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

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

Pour cette section, nous utiliserons les données du fichier Excel Sample_Data, disponible via le même lien que précédemment. La feuille Data contient deux tableaux avec les enregistrements suivants.

Tables Excel à joindre. Image par l’auteur

Nous allons construire le modèle de données dans cette feuille existante. Pour commencer, sélectionnez une cellule du tableau Customer ID and Name, puis appuyez sur Ctrl + T.

Sélection d’une cellule. Image par l’auteur

Une boîte Créer un tableau s’ouvre comme ci-dessous.

Fenêtre Créer un tableau. Image par l’auteur

 Cliquez sur OK : le tableau est prêt. 

Tableau croisé dynamique dans Excel. Image par l’auteur

Nommez maintenant ce tableau. Allez dans le ruban contextuel Création de tableau et, sous Nom du tableau, renommez-le en CustomerPQ (ou un autre nom de votre choix). 

Fenêtre contextuelle Création de tableau. Image par l’auteur

Le tableau porte désormais le nom CustomerPQ.

Fenêtre contextuelle Création de tableau. Image par l’auteur

Répétez l’opération pour le second tableau. Cette fois, nommez-le PurchasePQ.

Deux tableaux prêts pour le modèle de données. Image par l’auteur

Le tableau actif est maintenant PurchasePQ. Allez dans Données et sélectionnez Depuis une plage de tableaux. L’éditeur suivant apparaît.

Ouverture de l’Éditeur Power Query. Image par l’auteur

Pour créer une connexion et ajouter la table PurchasePQ au modèle de données, cliquez sur la flèche de Fermer & charger, puis sélectionnez Fermer & charger vers…  

Fermeture de l’Éditeur Power Query. Image par l’auteur

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

Fenêtre Importer des données. Image par l’auteur 

Après ces étapes, vous verrez le volet Requêtes et connexions.

Boîte Requêtes et connexions. Image par l’auteur

Ajoutez maintenant la table CustomerPQ à la connexion. Pour cela, cliquez sur une cellule du tableau, allez dans Données, puis Depuis une plage de tableaux. La feuille suivante s’ouvre.

Tables prêtes pour le modèle de données Excel. Image par l’auteur

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

Fenêtre Importer des données. Image par l’auteur 

 Cliquez sur OK. L’onglet Requêtes et connexions affiche maintenant les deux tables.

Requêtes et connexions. Image par l’auteur

Nous pouvons maintenant créer les relations. Dans l’onglet Données, rubrique Outils de données, cliquez sur Gérer le modèle de données.

Outils de données. Image par l’auteur

Cela ouvre Power Pivot for Excel.

Power Pivot for Excel. Image par l’auteur

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

Volet Affichage. Image par l’auteur

 Vous voyez alors deux tables non encore liées. 

Affichage Diagramme. Image par l’auteur

Pour créer la relation, allez dans l’onglet Conception et cliquez sur Créer une relation.

Onglet Conception. Image par l’auteur

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

Fenêtre Créer une relation. Image par l’auteur

Cliquez sur OK. Vous constatez que la relation est créée.

Fenêtre indiquant que la relation est établie. Image par l’auteur 

Enregistrez Power Pivot (onglet Enregistrer ou Ctrl + S), puis fermez l’onglet. La boîte suivante s’affiche.

Fenêtre Activer le contenu. Image par l’auteur

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

Volet Tableau croisé dynamique. Image par l’auteur

Vous obtenez une boîte comme celle-ci :

Tableau croisé à partir du modèle de données. Image par l’auteur

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

Feuille avec tableau croisé. Image par l’auteur 

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

Tables sous Champs de tableau croisé. Image par l’auteur

Cochez Customer Name dans la table CustomerPQ. Puis, dans PurchasePQ, cochez Purchase. Les Champs de tableau croisé apparaissent ainsi :

Sélection d’éléments dans les champs du tableau croisé. Image par l’auteur 

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

Rendu du tableau croisé. Image par l’auteur

Parfait ! Vous pouvez ainsi utiliser Power Query pour créer le modèle de données et Power Pivot pour produire des synthèses.

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

Dans cette section, vous utiliserez Power Pivot pour créer un modèle de données et préparer un rapport de synthèse. 

Pour commencer, allez sur la feuille nommée Power Pivot dans les données mentionnées ci-dessus et créez les tableaux pour nos deux enregistrements.

Enregistrements à utiliser. Image par l’auteur 

Nous avons déjà vu comment créer des tableaux ; suivez les mêmes étapes pour créer ces deux tables.

Seule différence : nous donnerons de nouveaux noms pour les distinguer des précédents : 

\n
• Nommer le premier tableau CustomerPP.
\n
• Nommer le second tableau PurchasePP.
\n

Données Tables issues des enregistrements. Image par l’auteur

Les deux tables sont créées. Ajoutons-les au modèle de données : allez dans Power Pivot, puis cliquez sur Ajouter au modèle de données.

Ajouter au modèle de données (Power Pivot). Image par l’auteur

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

Fenêtre Power Pivot for Excel. Image par l’auteur 

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

Fenêtre Power Pivot for Excel. Image par l’auteur

Une fois ces tables ajoutées au modèle, créez la relation entre elles dans Power Pivot via l’Affichage Diagramme.

Affichage Diagramme. Image par l’auteur

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

Onglet Créer une relation. Image par l’auteur

Une nouvelle fenêtre s’ouvre. Renseignez les détails comme ci-dessous :

Créer une relation entre deux tables. Image par l’auteur

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

Relations établies. Image par l’auteur

Il est facile de créer un tableau croisé à partir d’ici, comme dans la section précédente. Le résultat final devrait ressembler à ceci.

Synthèse en tableau croisé. Image par l’auteur

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

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

Une fois le tableau croisé dynamique créé, il est facile d’insérer un graphique croisé dynamique. Avec le tableau croisé dynamique (Sheet 1) sélectionné, allez dans l’onglet Insertion.

Synthèse en tableau croisé. Image par l’auteur

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

Options d’insertion de graphique. Image par l’auteur

Cela crée le graphique croisé dynamique suivant.

Visualisation en graphique croisé dynamique. Image par l’auteur

Vous pouvez personnaliser le graphique croisé en :

\n
• Ajustant les éléments (titres, légendes, étiquettes d’axes)
\n
• Formatant le graphique pour améliorer le rendu et la lisibilité.
\n

Parfait, vous venez de réaliser votre première visualisation à partir d’un tableau croisé dynamique.

Questions et problèmes fréquents avec les modèles de données Excel

Voici quelques questions et écues courants liés aux 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 ?

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

\n
1. Ouvrir en mode sans échec : lancez Excel en maintenant Ctrl au démarrage pour contourner temporairement le souci.
\n
2. Vérifier les mises à jour : assurez-vous qu’Excel est à jour via Fichier > Compte > Options de mise à jour.
\n
3. Réparer Office : utilisez l’outil de réparation (Panneau de configuration > Programmes et fonctionnalités), sélectionnez votre installation Office, puis Modifier > Réparer.
\n
4. Désactiver les compléments : allez dans Fichier > Options > Compléments, désactivez-les, redémarrez Excel et rouvrez le classeur.
\n
5. Extraire les données : si l’ouverture est impossible, utilisez un outil de récupération ou contactez le support Microsoft pour une aide avancée.
\n

Comment dépanner les erreurs dans le modèle de données ?

Pour dépanner :

\n
1. Vérifier les types de données : assurez-vous que les types des colonnes utilisées dans les relations sont cohérents.
\n
2. Valider les relations : vérifiez que toutes les relations sont correctement définies.
\n
3. Réviser les calculs : contrôlez les colonnes calculées et les mesures.
\n
4. Actualiser les données : vérifiez que toutes les connexions sont à jour.
\n
5. Messages d’erreur : lisez les messages dans la fenêtre Power Pivot pour identifier les problèmes précis.
\n

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. 

En conclusion

Merci d’avoir suivi ce tutoriel ! Vous avez acquis une compétence clé pour maîtriser l’analyse de données avec Excel. La création de modèles de données renforce considérablement vos capacités d’analyse, ainsi que celles de votre organisation, tout en améliorant l’efficacité de la gestion de plusieurs tables et enregistrements. 

Pour aller plus loin, consultez les ressources suivantes :   

\n
• Introduction to Excel : un guide complet pour bien démarrer avec Excel, couvrant les fonctionnalités de base.
\n
• Data Analysis in Excel : apprenez diverses techniques et outils d’analyse disponibles dans Excel pour développer vos compétences.
\n
• Excel Fundamentals : une formation sur les concepts et opérations fondamentaux d’Excel pour poser des bases solides.
\n
• Data Visualization in Excel : explorez la création de visualisations percutantes et interactives avec les outils intégrés d’Excel.
\n
• Introduction to Power Query in Excel : comprenez comment utiliser Power Query pour importer, nettoyer et transformer efficacement vos données dans Excel.
\n