Créer des modèles de données dans Excel : Le guide classique vs. L'approche alimentée par l'IA

Les modèles de données nous permettent de charger, intégrer et analyser des données provenant de multiples sources, facilitant une vision plus dynamique et perspicace des données. Ceci est essentiel lorsqu'une seule feuille de calcul ne suffit plus pour répondre à vos questions métier.

Dans les sections de ce tutoriel, nous explorerons les méthodes traditionnelles pour créer des modèles de données dans Excel, en utilisant des outils puissants comme Power Query, Power Pivot et les PivotCharts. Mais nous présenterons également une alternative révolutionnaire alimentée par l'IA qui atteint les mêmes objectifs en une fraction du temps.

Commençons !

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

Il existe deux principales approches pour créer un modèle de données : la méthode moderne alimentée par l'IA et la méthode traditionnelle manuelle.

L'Approche Moderne : Utiliser un Agent IA comme Excelmatic

Pour une vitesse et une simplicité maximales, Excelmatic est le meilleur choix. Excelmatic est conçu pour gérer des tâches complexes de données en utilisant des commandes en langage naturel. Le processus est incroyablement simple :

1. Téléchargez vos fichiers : Téléchargez tous vos fichiers de données liés (ex: Orders.xlsx et Products.xlsx).
2. Énoncez votre demande : Posez une question en langage naturel, comme "Combinez ces fichiers et montrez-moi la quantité totale vendue pour chaque nom de produit."
3. Obtenez votre résultat : Excelmatic comprend automatiquement les relations, fusionne les données, effectue le calcul et délivre un rapport ou un graphique final instantanément.

Cette approche contourne les processus complexes et multi-étapes des méthodes traditionnelles, que nous explorerons ensuite.

L'Approche Traditionnelle : Étapes Manuelles dans Excel

Pour ceux qui préfèrent un contrôle manuel ou souhaitent comprendre les mécanismes sous-jacents, voici les étapes générales :

1. Formatez vos données en tant que tableaux.
2. Créez des relations entre les tableaux.
3. Utilisez Power Query pour charger et transformer les données.
4. Utilisez Power Pivot pour la modélisation de données avancée.
5. Utilisez les Tableaux Croisés Dynamiques et les PivotCharts 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 scénarios courants, incluant le nettoyage de données, l'intégration et la transformation de données, et l'analyse de données complexe. Voici quelques-uns des principaux avantages.

• Facilite le Nettoyage des Données : Les modèles de données aident à nettoyer et standardiser différents ensembles de données pour maintenir l'uniformité et la cohérence. Ceci est crucial lorsqu'on traite des données de différentes sources, nécessitant un mélange et un prétraitement.
• Effectue ou Résout l'Intégration et la Transformation des Données : Avec le modèle de données en place, toutes les données disponibles de toute autre source peuvent facilement être intégrées et transformées, et les utilisateurs de données peuvent recevoir une vue unifiée facile à traiter et analyser.
• Améliore la Visualisation des Données : Les modèles de données soutiennent le développement de visualisations de données avancées telles que les PivotCharts et les rapports Power View.
• Améliore la Sécurité et la Gestion des Données : Avec un modèle de données, un accès contrôlé à différentes parties de vos données peut être réalisé. Vous aurez plus de facilité à gérer les permissions et les données au sein du modèle pour réduire les risques d'accès non autorisé et de violations de données.
• Rationalise le Reporting des Données : Grâce aux modèles de données, le reporting peut être entièrement automatisé, facilitant ainsi le processus de reporting et de prise de décision. Ceci permet aux rapports et tableaux de bord d'être ajustés dynamiquement en cas d'insertion de nouvelles données.
• Gère les Gros Volumes de Données : Les modèles de données peuvent travailler avec de grands ensembles de données dans Excel, surmontant les contraintes des feuilles de calcul Excel traditionnelles.
• Permet l'Analyse de Données Complexe : La fonctionnalité de création de relations entre plusieurs tableaux de données permet une analyse de données avancée qui pourrait ne pas être facile avec les fonctions Excel classiques.

Comment Créer un Modèle de Données en Important des Données (La Méthode Manuelle)

Dans cette section, nous allons parcourir le processus traditionnel de création d'un modèle de données dans Excel en important des ensembles de données d'exemple. Nous utiliserons deux ensembles de données d'exemple : Order et Product. Vous pouvez créer des ensembles de données similaires pour suivre.

La première étape est d'importer des données dans Excel. Commencez par ouvrir un nouveau classeur Excel, et cliquez sur l'onglet Données. Sous Obtenir des données, sélectionnez Obtenir des données > À partir d'un fichier > À partir d'un classeur Excel.

Importation de données dans Excel. Image par l'Auteur

Sélectionnez le classeur Order.xlsx, et cliquez sur Importer. Ceci ouvrira la fenêtre Navigateur : Sélectionnez la table Sheet1.

La fenêtre Navigateur dans Excel. Image par l'Auteur

Cliquez sur l'option Charger vers qui ouvrira 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 étapes ci-dessus pour charger la feuille Product. Une fois ces deux tâches terminées, votre classeur Excel ressemblera à ceci :

Volet Requêtes & Connexions. Image par l'Auteur

Félicitations ! Vous avez importé les deux feuilles dans le fichier Excel. L'étape suivante consiste à établir des relations entre les tableaux importés. Pour ce faire, 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 apparaîtra.

Fenêtre Gérer les relations. Image par l'Auteur

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

Fenêtre Créer une relation. Image par l'Auteur

Cliquez sur OK. La relation est établie, et vous pouvez la confirmer dans l'onglet Connexions.

Volet Requêtes & Connexions. Image par l'Auteur

L'étape suivante consiste à créer le Tableau Croisé Dynamique. Allez dans l'onglet Insérer et cliquez sur Tableau Croisé Dynamique. Ensuite, dans la boîte de dialogue Tableau Croisé Dynamique, sélectionnez Utiliser le Modèle de données de ce classeur.

Boîte de dialogue Tableau Croisé Dynamique. Image par l'Auteur

Sélectionnez l'emplacement pour le Tableau Croisé Dynamique. Cliquez sur OK. Le Tableau Croisé Dynamique s'ouvrira et vous pourrez voir les champs sous Champs du Tableau Croisé Dynamique.

Champs du Tableau Croisé Dynamique. Image par l'Auteur

Pour construire le Tableau Croisé Dynamique, faites glisser les champs vers les zones Lignes et Valeurs :

• Lignes : ProductName
• Valeurs : Quantity

Le Tableau Croisé Dynamique résultant ressemblera à ceci :

Volet Champs du Tableau Croisé Dynamique. Image par l'Auteur

L'Avantage Excelmatic : De 15 Clics à 1 Invite

Le processus de 15+ étapes ci-dessus est puissant, mais complexe et long. Avec Excelmatic, vous pouvez obtenir le même résultat en quelques secondes.

1. Téléchargez Order.xlsx et Product.xlsx.
2. Demandez : "En utilisant ProductID pour lier les fichiers, montrez-moi la quantité totale pour chaque ProductName."

C'est tout. Excelmatic gère la connexion, la relation et la création du Tableau Croisé Dynamique automatiquement, vous donnant la réponse finale instantanément.

Comment Créer un Modèle de Données en Utilisant Power Query (La Méthode Manuelle)

Pour cette section, nous utiliserons les données stockées dans le fichier Excel nommé Sample_Data. Ce fichier est également disponible dans le même lien fourni dans la section précédente. Il y a deux tableaux dans la feuille nommée Data.

Tableaux Excel à joindre. Image par l'Auteur

Nous allons construire des modèles de données dans cette feuille Excel existante. Pour commencer, sélectionnez n'importe quelle cellule dans le tableau Customer ID and Name puis sélectionnez Ctrl + T.

Sélection d'une cellule. Image par l'Auteur

Ceci ouvre une fenêtre contextuelle Créer un tableau.

Fenêtre contextuelle Créer un tableau. Image par l'Auteur

Cliquez sur OK. Maintenant, donnons un nom à ce tableau. Allez dans la fenêtre contextuelle Création de tableau et sous Nom du tableau, renommez le tableau en CustomerPQ.

Fenêtre contextuelle Création de tableau. Image par l'Auteur

Répétez le processus pour le deuxième tableau. Cette fois, définissez le nom sur PurchasePQ.

Deux tableaux dans Excel prêts pour le Modèle de données. Image par l'Auteur

Maintenant, avec le tableau PurchasePQ actif, allez dans Données, et sélectionnez À partir d'une plage de tableau. Ceci ouvrira l'Éditeur Power Query.

Ouverture de l'Éditeur Power Query. Image par l'Auteur

Pour créer une connexion et ajouter le tableau au Modèle de données, cliquez sur l'onglet déroulant de Fermer et charger, et sélectionnez Fermer et charger vers…

Fermeture de l'Éditeur Power Query. Image par l'Auteur

Dans la fenêtre Importer des données, sélectionnez Créer uniquement une connexion et cochez Ajouter ces données au Modèle de données.

Fenêtre Importer des données. Image par l'Auteur

Répétez tout ce processus pour le tableau CustomerPQ. Une fois terminé, l'onglet Requêtes & Connexions affichera les deux tableaux.

Requêtes et Connexions. Image par l'Auteur

Nous sommes maintenant prêts à établir des relations. Dans l'onglet Données, allez dans la section Outils de données, et cliquez sur Gérer le Modèle de données.

Outils de données. Image par l'Auteur

Ceci ouvrira Power Pivot pour Excel. Cliquez sur la Vue Schéma.

Vue Schéma. Image par l'Auteur

Vous pouvez voir qu'il y a deux tableaux, mais pas encore connectés. Allez dans l'onglet Création et cliquez sur Créer une relation.

Onglet Création. Image par l'Auteur

Une nouvelle fenêtre s'ouvrira. Remplissez les détails pour lier les tableaux par Customer ID.

Fenêtre Créer une relation. Image par l'Auteur

Cliquez sur OK. La relation est maintenant établie.

Fenêtre montrant que la relation est établie. Image par l'Auteur

Enfin, vous pouvez insérer un Tableau Croisé Dynamique à partir du Modèle de données et faites glisser Customer Name vers Lignes et Purchase vers Valeurs pour obtenir votre rapport récapitulatif.

Résultat du Tableau Croisé Dynamique. Image par l'Auteur

L'Avantage Excelmatic : Passez les 20+ Clics

Power Query est un outil fantastique, mais comme vous pouvez le voir, il nécessite de naviguer dans plusieurs menus et fenêtres. Excelmatic simplifie l'ensemble de ce flux de travail.

1. Téléchargez le fichier Sample_Data.xlsx.
2. Demandez : "Sur la base des deux tableaux dans la feuille, quel est l'achat total pour chaque nom de client ?"

Vous obtenez le même résultat sans jamais ouvrir Power Query ou Power Pivot.

Comment Créer un Modèle de Données Directement dans Power Pivot

Cette méthode est similaire à l'approche Power Query mais commence directement depuis l'onglet Power Pivot.

Pour commencer, allez dans la feuille nommée Power Pivot dans le fichier de données d'exemple, et créez des tableaux pour nos deux enregistrements, en les nommant CustomerPP et PurchasePP.

Tableaux de données des enregistrements. Image par l'Auteur

L'étape suivante consiste à ajouter ces tableaux au modèle de données. Sélectionnez le tableau CustomerPP, allez dans l'onglet Power Pivot, et sélectionnez Ajouter au Modèle de données.

Ajouter au Modèle de données sous PowerPivot. Image par l'Auteur

Ceci ouvrira une fenêtre contextuelle Power Pivot pour Excel.

Fenêtre contextuelle Power Pivot pour Excel. Image par l'Auteur

Enregistrez et fermez cette fenêtre. Répétez le même processus pour le tableau PurchasePP. Une fois les deux tableaux dans le modèle, vous pouvez établir la relation entre eux en utilisant la Vue Schéma et la boîte de dialogue Créer une relation, exactement comme nous l'avons fait dans la section Power Query.

*Les relations sont ét