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:
- 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.
- 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.
- 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.
- 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é.
- 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.
