TCD et SGBDRapports Excel et SGBD
Gestion des bases de données dans les Tableaux Croisés Dynamiques Excel
Créer un TCD à partir de plusieurs tableaux Excel
Exemple de deux tableaux : Clients et Commandes (dans des feuilles Excel différentes) :
Les tableaux Clients et Commandes (dans deux feuilles Excel différentes)
1. Transformer vos tableaux en tableau de données
Pour pouvoir lier plusieurs tableaux Excel, ils doivent tous être au format Tableau de données.2. Créer une relation entre vos tableaux
Malgré ce que l'on peut voir sur les tutos, il est beaucoup plus simple de créer une relation AVANT de créer des TCD via la commande "Relations" de l'onglet "Données".Pour que cela soit possible, n'oubliez pas de transformer vos tableaux en tableaux de données. Avant de créer votre relation, pensez aussi à renommer vos tableaux de données (onglet Création de Tableaux)
3. Lier plusieurs tableaux Excel dans un TCD (rapport)
Pour pouvoir lier plusieurs tableaux qui proviennent de plusieurs feuilles Excel, il faut, lors de l'insertion du Rapport de Tableau Croisé Dynamique (TCD), cocher la petite case "Ajouter ces données au modèle de données" visible en bas de la fenêtre "Créer un tableau croisé dynamique".
La fenêtre Excel "Créer un tableau croisé dynamique" avec l'option "Ajouter ces données au modèle de données"
4. Afficher les champs de tous vos tableaux
Dans la fenêtre des champs, cliquez sur le nouvel onglet "TOUS" et vous devriez voir les différents champs des différents tableaux :
Fenêtre des champs de tableau croisé dynamique avec l'onglet "Tous" qui affiche les différentes tables
Pour info, le modèle de données est une table d'index créée dans PowerPivot. Le rapport Excel est donc liée à cette table du modèle de données Powerpivot
4.1 Afficher la fenêtre Relation d'un rapport
On retrouve la fenêtre "Relation" dans l'onglet "Analyse du tableau croisé dynamique" :
Bouton de commande "Gérer les relations" de tableau croisé dynamique (permet d'ouvrir la fenêtre "Gérer les relations")
4.2 Changer la source de données d'un rapport qui comporte des relations
Vous ne pouvez pas modifier la source de données d'un rapport qui comporte des relations, mais vous pouvez toujours afficher les propriétés de connexions dans l'onglet Analyse, groupe Données, Changer la source de données et cliquer sur "Propriétés de connexion". Via le bouton "Propriétés", ou en double cliquant sur une connexion, vous pourrez afficher les propriétés de connexions.
Menu requêtes et connexions de tableau croisé dynamique
Total des commandes par date et par client
Glissez le champ "Nom client" dans la zone "Lignes" et le champ "Total" dans la zone "Valeurs"
Fenêtre Excel des champs de Tableau Croisé Dynamique avec le bouton "CREER..." (une relation)
Numéro de commande par client
Glissez le champ "N° commande" dans la zone Lignes, en dessous du champ "Nom client"Commandes par date et par nom de client
Glissez le champ "Date" dans la zone Lignes, au dessus des autres champs.
Photo d'écran du Tableau Croisé Dynamique et du bas de la fenêtre Excel des champs de Tableau Croisé Dynamique
Utiliser les outils de base de données du rapport
◊ Utiliser les outils OLAP (Online Analytical Processing) ; Formule CUBEVALUE et CUBEMEMBER
Onglet "Analyse", groupe Calculs, Outils OLAP, option "Convertir en formules"En cliquant sur cette option, votre tableau croisé dynamique sera converti en formules :
CUBEVALUE pour le traitement des valeurs
CUBEMEMBER pour le traitement des titres de lignes et de colonnes.
Cela permet de disposer les données comme bon vous semble et agir sur le format des cellules. De plus cela permet d'ajouter d'autres calculs à votre rapport. Notez enfin que les Segments continuent de fonctionner.
Note : Les fonctions CUBE d'Excel permettent la gestion des bases de données multidimensionnelles. Plutôt que de représenter les données sous forme de tableaux (SGBDR), ce modèle travaille avec plusieurs "axes", le plus souvent 3, formant ce qu'on appelle un hypercube ou Cube. Source : lecfomasque.com et Developpez.com
◊ Créer un jeu d'enregistrement
Dans l'onglet "Analyse du Tableau Croisé Dynamique", groupe "Calculs, Champs, éléments et jeux", option "Créer un jeux basé sur des éléments de ligne", vous pouvez créer un jeu d'enregistrement basé sur des éléments de ligne
Jeux d'enregistrement basé sur des éléments de ligne de tableau croisé dynamique
◊ Utiliser le concepteur de requêtes MDX
Il est aussi possible de créer un jeu d'enregistrement avec le concepteur de requêtes MDX (MultiDimensional Expressions) : option "Gérer les jeux"... et bouton Nouveau....◊ Utiliser la fonction Total Distinct
Elle permet de compter le nombre de clients qui ont fait - au moins - une commande. Soit le nombre de client distinct. Cette fonction Total Distinct est disponible uniquement dans les rapports qui utilisent le modèle de données Powerpivot. Dans la fenêtre des champs, cliquez sur le champ qui se trouve dans la zone valeurs puis sur "Paramètres des champs de valeurs" et sélectionnez, en bas, la fonction Total distinct.◊ Analyse et gestion des données avec Powerpivot
Voir l'exemple Easy-Micro de gestion des données avec PowerpivotListe déroulante dynamique
Insérer une liste déroulante Excel dans la colonne "N° de Client" du tableau "Commandes" avec la fonction Excel DECALER() :La fonction DECALER() renvoie la valeur d'une cellule correspondant au nombre de lignes ou de colonnes de décalage par rapport à la cellule de départ.
Syntaxe :
=DECALER(réf, lignes, colonnes, [hauteur], [largeur])
Exemple, si le nom des clients se trouve dans la colonne C, la fonction :
=DECALER(A1;0;2)
donne le premier nom. Décalage depuis A1 de 0 ligne et de 2 colonnesVoici la fonction DECALER à mettre dans la zone source de votre liste déroulante (onglet "Données", groupe "Outils de données", commande "Validation des données") :
=DECALER('Client'!$A$2:$A$6;0;1)
