les modèle de données dans Microsoft Excel

■■ Qu’est-ce que la fonctionnalité de modèle de données et pourquoi dois-je en savoir plus?

■■ Comment puis-je ajouter des données au modèle de données?

■■ Comment puis-je créer des relations dans le modèle de données?

■■ Comment puis-je utiliser le modèle de données pour créer des tableaux croisés dynamiques?

■■ Comment fonctionne l’option d’exploration rapide?

■■ Comment puis-je ajouter de nouvelles données à un modèle de données?

■■ Comment puis-je supprimer des données du modèle de données?

■■ Comment puis-je utiliser le modèle de données pour créer un nouveau tableau croisé dynamique?

■■ Comment puis-je modifier ou supprimer des relations?

■■ Comment fonctionne la fonction DISTINCT COUNT?

Réponses aux questions de ce chapitre

Cette section fournit les réponses aux questions répertoriées au début du chapitre.

 

Qu’est-ce que la fonctionnalité de modèle de données et pourquoi dois-je en savoir plus?

 

La fonctionnalité de modèle de données est nouvelle dans Excel . (C’était un complément dans Excel .) Le modèle de données fournit un moyen facile de charger des données au-delà des capacités ordinaires d’Excel (1 048 576 lignes de données) et d’utiliser ces données pour créer des tableaux croisés dynamiques. Le modèle de données vous permet également de combiner des données provenant de sources extérieures à Excel (y compris Access et SQL Server) avec des données d’Excel. Enfin, une compréhension du modèle de données vous permettra de saisir plus facilement les incroyables capacités de PowerPivot (voir l’artilce 45, «PowerPivot»), qui est disponible si vous avez accès à Excel 2013 Pro Plus ou Office 365.

A l’article 43, «Utilisation de tableaux croisés dynamiques et de segments pour décrire les données», vous avez appris à utiliser la fonction de tableau croisé dynamique Excel pour résumer les données. Vous avez vu comment créer des tableaux croisés dynamiques basés sur des données dans des emplacements disparates, mais cette approche exigeait que les données de chaque emplacement aient les mêmes en-têtes de colonne.

Ce n’est souvent pas le cas. Par exemple, le classeur Datamodeltemp.xlsx contient deux feuilles de calcul. La feuille de travail des représentants (illustrée à gauche sur la figure 1) contient une liste des numéros d’identification des vendeurs et l’état où ils vendent vos produits. La feuille de travail des ventes (illustrée à droite sur la figure 1) montre les ventes unitaires générées par chaque employé. Naturellement, vous souhaitez utiliser un tableau croisé dynamique pour résumer les ventes par état. Le problème est que les données de la feuille de calcul Ventes ne connaissent pas l’état de chaque vendeur. Vous pouvez ajouter une colonne en utilisant des formules RECHERCHEV pour insérer les états dans la feuille de calcul Ventes. Cependant, si vous aviez plusieurs centaines de milliers de lignes de données, ces RECHERCHEV ralentiraient considérablement les performances de votre feuille de calcul. Le modèle de données Excel vous permet de contourner les RECHERCHEV en créant facilement une relation qui indique à Excel l’état de chaque vendeur sans avoir besoin de RECHERCHE lentes.

Les données doivent être incluses dans le modèle de données pour créer des graphiques incroyables avec Power View. (Voir l’article 46, «Power View», pour une discussion sur Power View.) Enfin, si vous souhaitez mélanger des données provenant de différentes sources telles que des bases de données, le Web, des fichiers texte et des feuilles de calcul Excel, vous souhaitez utiliser PowerPivot ( discuté au chapitre 45). Lorsque vous comprenez le modèle de données, PowerPivot devient beaucoup plus facile à comprendre. Power View et PowerPivot ne sont disponibles que si vous disposez de l’édition Pro Plus ou Office 365 d’Excel , mais le modèle de données est inclus dans toutes les éditions d’Excel .

FIGURE 1 : Il s’agit des données utilisées dans l’exemple de modèle de données.

Comment puis-je ajouter des données au modèle de données?

Avant d’ajouter des données au modèle de données, vous devez créer les données sous forme de tableau (voir Chapitre 25, «Tables»). Tout d’abord, sélectionnez les données, y compris les en-têtes de colonne, dans la feuille de calcul Reps et utilisez Ctrl + T pour transformer les données en tableau. Dans Outils de tableau, nommez les représentants de table. Ensuite, comme illustré dans la figure 2, sélectionnez Tableau croisé dynamique dans l’onglet Insertion et cochez la case Ajouter ces données au modèle de données. Cela garantit que les données de la feuille de calcul Reps sont incluses dans le modèle de données. De la même manière, sélectionnez les données dans la feuille de calcul Ventes et nommez la table Ventes. Ajoutez la table Sales au modèle de données.

FIGURE :2 Ajoutez des données au modèle de données.

Comment puis-je créer des relations dans le modèle de données?

Si vous souhaitez résumer les ventes par état, vous avez un problème. À l’heure actuelle, il n’y a aucun moyen pour Excel pour connaître l’état de chaque vendeur. D’une manière ou d’une autre, vous devez créer une relation qui permet à Excel de déterminer l’état de chaque ligne de données dans la table Sales. Après avoir ajouté la table Sales au modèle de données, vous pouvez créer la relation requise en sélectionnant Relations dans l’onglet Données. Après avoir cliqué sur Relations, sélectionner Nouveau ouvre la boîte de dialogue Créer une relation illustrée dans la figure 3. La colonne principale doit être la colonne de données qui implique une relation un à un qui peut être mappée dans la colonne étrangère. Étant donné que la table Reps a un état pour chaque représentant commercial dans votre exemple, la colonne principale doit être la colonne ID dans la table Reps. Faire la colonne étrangère la colonne ID de la table Sales. Ces paramètres sont illustrés à la figure 3. Maintenant, si vous essayez de créer un tableau croisé dynamique qui résume les ventes par état, tout ira bien car, pour chaque numéro d’identification  dans la table Sales, Excel saura extraire l’état correct de la table Reps.

FIGURE 3: Créez une relation entre les tables.

Comment puis-je utiliser le modèle de données pour créer des tableaux croisés dynamiques?

Après avoir ajouté la table Sales au modèle de données, vous voyez les champs de tableau croisé dynamique. Après avoir sélectionné Tout, vous voyez toutes les tables ajoutées au modèle de données, comme le montre la figure 4.

FIGURE : 4 Il s’agit de la liste des champs pour l’exemple de modèle de données.

Après avoir cliqué sur les triangles à gauche des tables de représentation et de vente, vous voyez toutes les colonnes des tables de représentation et de vente. Pour créer un tableau croisé dynamique qui calcule les ventes totales dans chaque état, faites glisser States vers la zone Lignes et Sales vers la zone Valeurs, comme illustré à la figure 5. Vous obtenez le tableau croisé dynamique illustré à la figure 6.

FIGURE 5 : Il s’agit de la création du tableau croisé dynamique pour déterminer les ventes dans chaque état.

Par exemple, 10846 unités ont été vendues en Alaska; 24 147 unités ont été vendues en Géorgie, etc

Si vous aviez essayé de créer le tableau croisé dynamique avant de créer la relation illustrée à la figure 3, Excel n’aurait eu aucun moyen de déterminer l’état de chaque ligne de la table Sales. Excel vous aurait donc demandé de créer la relation nécessaire.

FIGURE :6 Cette figure montre les ventes unitaires dans chaque état.

Comment fonctionne l’option d’exploration rapide?

Si vous cliquez sur le total des ventes pour n’importe quel état (Alaska ici), vous voyez l’icône d’exploration rapide illustrée dans la figure 7. Cliquez sur la flèche déroulante à droite de l’icône pour ouvrir la boîte illustrée à la figure 8. L’Exploration rapide suggère d’analyser par ID. Cliquer sur ID de forage pour afficher les ventes de tous les vendeurs associés aux ventes en Alaska (voir la figure 9).

FIGURE  7 Voici à quoi ressemble l’icône d’exploration rapide.

FIGURE 8, Exploration rapide, suggère de descendre dans l’ID du vendeur.

FIGURE 9 Cette figure montre les vendeurs et les ventes de l’Alaska.

 

Comment puis-je ajouter de nouvelles données à un modèle de données?

Si vous souhaitez ajouter un nouvel ensemble de données au modèle de données, créez les données sous forme de tableau et sélectionnez Insérer un tableau croisé dynamique. Vous pouvez sélectionner Ajouter ces données au modèle de données et, si vous sélectionnez Tout (au lieu d’Actif), vous pouvez désormais utiliser les nouvelles données dans votre tableau croisé dynamique.

Vous pouvez également ajouter un tableau Excel nouvellement créé à un modèle de données en cliquant sur l’onglet Données du ruban et en sélectionnant Connexions. La boîte de dialogue Connexions au classeur s’ouvre. Après avoir sélectionné Ajouter, la boîte de dialogue Connexions existantes s’ouvre. Après avoir choisi Tables, vous pouvez ajouter votre nouvelle table au modèle de données.

Si l’ajout de nouvelles données signifie l’ajout de nouvelles lignes aux tables déjà incluses dans le modèle de données, sélectionnez Actualiser dans la boîte de dialogue Connexions au classeur après avoir sélectionné Connexions dans l’onglet Données pour vous assurer que vos tableaux croisés dynamiques seront mis à jour pour inclure les nouvelles données.

 

Comment supprimer des données d’un modèle de données?

Pour supprimer des données d’un modèle de données, cliquez sur l’onglet Données du ruban, puis sélectionnez Connexions.

Comme le montre la figure 10, vous pouvez sélectionner une table (Reps est sélectionné) et choisissez Supprimer pour supprimer la table du modèle de données.

Comment puis-je utiliser le modèle de données pour créer un nouveau tableau croisé dynamique?

Pour utiliser les données existantes pour créer un nouveau tableau croisé dynamique, procédez comme suit:

  1. 1. Ouvrez la boîte de dialogue Créer un tableau croisé dynamique en sélectionnant Tableau croisé dynamique dans l’onglet Insérer.
  1. 2. Sélectionnez Utiliser une source de données externe, puis sélectionnez Choisir une connexion.
  1. 3. Dans l’onglet Connexions existantes, sélectionnez Tables, puis choisissez Tables dans le classeur

Modèle de données. Vous voyez la boîte de dialogue illustrée à la figure 11.

  1. 4. Si vous sélectionnez maintenant Ouvrir, vous revenez à la boîte de dialogue Créer un tableau croisé dynamique et vous pouvez maintenant créer un tableau croisé dynamique.

FIGURE 10 La boîte de dialogue Connexions au classeur est définie pour supprimer la table Reps du modèle de données.

FIGURE11 Les tableaux des ventes et des représentants sont maintenant disponibles pour un tableau croisé dynamique.

Comment puis-je modifier ou supprimer des relations?

Pour modifier ou supprimer des relations, sélectionnez Relations dans le groupe Outils de données sous l’onglet Données. Cela ouvre la boîte de dialogue Gérer les relations illustrée à la figure 12. Dans cette boîte de dialogue, vous pouvez modifier ou supprimer des relations ou créer de nouvelles relations.

FIGURE : 12 Il s’agit de la boîte de dialogue Gérer les relations.

Comment fonctionne la fonction COMPTE DISTINCT?

Dans le fichier , vous recevez (comme illustré à la figure 13) une liste des athlètes (1 à 100) et les paiements de salaire reçus. Étant donné que de nombreux athlètes ont reçu plus d’un paiement, ils figurent plusieurs fois sur la liste. On vous donne également le sport que joue chaque athlète. Supposons que vous souhaitiez déterminer le nombre d’athlètes pratiquant chaque sport. Tout d’abord, créez les données dans E3: F466 une table nommée Money et les données dans la plage K6: L106 une table nommée Activity; ajoutez chaque table au modèle de données. Créez une relation avec la clé primaire en tant que personne dans la table Activity et la clé étrangère en tant que personne dans la table Money. Créez un tableau croisé dynamique (voir la feuille de calcul Nombre de personnes) en faisant glisser Sport vers la zone Ligne et en faisant glisser Personne du tableau Money vers la zone Valeurs. Cela donne le tableau croisé dynamique illustré à la figure 44-14. Parce qu’il n’y a que 100 athlètes, vous voyez que chaque personne est compté   plusieurs fois. Pour résoudre ce problème, cliquez avec le bouton droit sur une cellule de valeur dans le tableau croisé dynamique et choisissez Paramètres de champ de valeur. Si vous faites défiler vers le bas, vous constatez, comme le montre la figure 15, que le dernier (mais non le moindre!) Choix est le nombre distinct. Ce choix garantit que chaque athlète n’est compté qu’une seule fois et donne le tableau croisé dynamique illustré à la figure 16 et la feuille de calcul Nombre distinct. Ce tableau croisé dynamique montre exactement combien d’athlètes pratiquent chaque sport!

FIGURE 13  Voici les données de l’exemple NB DISTINCT.

FIGURE 14  Ce tableau croisé dynamique compte chaque athlète plusieurs fois.

FIGURE :15 Choisissez Nombre distinct pour obtenir une liste unique du nombre d’athlètes pratiquant chaque sport.

FIGURE 16 : Il s’agit du nombre distinct d’athlètes pratiquant chaque sport.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x