Utilisation du modèle de données aux tableaux croisés dynamiques Excel

La fonctionnalité appelée le modèle de données apporte une nouvelle puissance aux tableaux croisés dynamiques. Avec le modèle de données, vous pouvez utiliser plusieurs tableaux de données dans un seul tableau croisé dynamique. Vous devrez créer une ou plusieurs “relations de table” afin que les données puissent être liées entre elles.

REMARQUE

Le modèle de données a été introduit dans Excel 2013, de sorte que les classeurs qui utilisent cette fonctionnalité ne sont pas compatibles avec les versions précédentes.

La figure ci-dessous montre des parties de trois tables qui se trouvent dans un seul classeur. (Chaque table se trouve dans sa propre feuille de calcul et s’affiche dans une fenêtre distincte.) Les tables sont nommées Commandes, Clients et Régions. Le tableau Commandes contient des informations sur les commandes de produits. La table Clients contient des informations sur les clients de la société. La table Régions contient un identifiant de région pour chaque état.

FIGURE 1 : La table region

Figure2 : la table client

Fugure3 : la table Commande

Notez que les tables Commandes et Clients ont une colonne lD_client en commun, et que les tables Clients et Régions ont une colonne Etat en commun. Les colonnes communes seront utilisées pour établir des relations entre les tables.

Ces relations sont “un-à-plusieurs”. Pour chaque ligne de la table Commandes, il existe exactement une ligne correspondante dans la table Clients, et cette ligne est déterminée par la colonne ID_client. De même, pour chaque ligne de la table Clients, il existe exactement une ligne correspondante dans la table Régions , et cette ligne est déterminée par la colonne Etat.

REMARQUE

Un tableau croisé dynamique créé à l’aide du modèle de données présente certaines restrictions, contrairement à un tableau croisé dynamique créé à partir d’un seul tableau. Plus particulièrement, vous ne pouvez pas créer de groupes. De plus, vous ne pouvez pas créer de champs calculés ou d’éléments calculés.

Pour cet exemple, l’objectif est de résumer les ventes par état, par région et par année. Notez que les informations sur les ventes et la date se trouvent dans la table Commandes, les informations sur l’état dans la table Clients et que les noms de région se trouvent dans la table Régions. Par conséquent, les trois tables seront utilisées pour générer ce tableau croisé dynamique.

Commencez par créer un tableau croisé dynamique (dans une nouvelle feuille de calcul) à partir du tableau Commandes. Suivez ces étapes:

  1. Sélectionnez n’importe quelle cellule dans le tableau et choisissez Insertion, Tableau ,tableau croisé dynamique. La boîte de dialogue Créer un tableau croisé dynamique s’affiche.
  2. Cochez la case Ajouter ces données au modèle de données. Notez que le volet Office Champs de tableau croisé dynamique est un peu différent lorsque vous travaillez avec le modèle de données. Le volet Office contient deux onglets : Actif et Tout. L’onglet Actif répertorie uniquement le tableau Commandes. L’onglet Tous répertorie toutes les tables du classeur.

 Pour faciliter les choses, cliquez sur Tout, activez le volet Office Champs de tableau croisé dynamique, cliquez avec le bouton droit sur la table Clients et choisissez Afficher dans l’onglet actif.

Ensuite, faites de même pour la table Régions.

La figure ci dessous montre l’onglet Actif du volet des tâches Champs de tableau croisé dynamique, avec les trois tableaux développés pour afficher leurs en-têtes de colonne. Notez que j’ai également modifié la configuration de ce volet Office en utilisant le contrôle Outils déroulant. J’ai choisi la section Champs et la section Zones côte à côte.

L’étape suivante consiste à configurer les relations entre les tables.

  1. Choisissez Outils de tableau croisé dynamique ensuite Analyse , Calculs et Relations. La boîte de dialogue Gérer les relations apparaît .

Excel a déterminé les relations en fonction des noms de champs. Si vos tables utilisent des noms de champ différents, vous pouvez spécifier les relations manuellement à l’aide du bouton Nouveau.

  1. Cliquez sur Fermer pour fermer la boîte de dialogue Gérer les relations.

REMARQUE

Si vous ne configurez pas les relations entre les tables à l’avance, Excel vous invitera à le faire lorsque vous ajouterez un champ au tableau croisé dynamique provenant d’une table différente de celle avec laquelle vous avez commencé.

  1. Une fois la relation de table établie, il suffit de faire glisser les noms de champ vers la section appropriée du volet des tâches Champs de tableau croisé dynamique :

■ Faites glisser le champ Total (du tableau Commandes) vers la zone Valeurs.

■ Faites glisser le champ Année (du tableau Commandes) vers la zone Colonnes.

■ Faites glisser le champ Région (du tableau Régions) vers la zone Lignes.

■ Faites glisser le champ Etat (du tableau Régions) vers la zone Lignes.

La figure ci-dessous  montre une partie du tableau croisé dynamique. J’ai ajouté deux segments pour permettre de filtrer le tableau par clients figurant sur la liste de diffusion et de filtrer par produit.

REMARQUE

Lorsque vous créez un tableau croisé dynamique à l’aide du modèle de données, vous pouvez convertir le tableau croisé dynamique en formules. Sélectionnez n’importe quelle cellule dans le tableau croisé dynamique et choisissez Outils de tableau croisé dynamique , Analyser ,Calculs ,Outils OLAP , Convertir en formules. Le tableau croisé dynamique est remplacé par des cellules qui utilisent des formules. Ces formules utilisent les fonctions MEMBRECUBE et VALEURCUBE. Bien que la plage ne soit plus un tableau croisé dynamique, les formules sont mises à jour lorsque les données changent.

S’abonner
Notifier de
1 Commentaire
le plus ancien
le plus récent le plus populaire
Inline Feedbacks
Voir tous les commentaires

Bonjour, merci pour votre article. Comment contourner le fait qu’on ne puisse pas utiliser de champs calculés ? J’ai un TCD pour lequel j’ai besoin du “total distinct”, donc du modèle de données, mais j’ai aussi besoin d’un champ calculé… Merci d’avance, Clémentine

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

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