Réaliser une consolidation dans un TCD à partir de plusieurs tableaux Excel

Objectif du module

• Etendre les possibilités d'analyse de données d'Excel.
• Obtenir rapidement des informations agrégées à partir d'un grand nombre de données.
• Exploiter rapidement les sources de données pour une vision synthétique et devenir un outil décisionnel.

Public et Prérequis

Public concerné : Toute personne étant amenée à traiter des bases de données dans Excel.
Niveau requis : Avoir une bonne connaissance des rapports de Tableaux Croisés Dynamique Excel.

Note: Module réalisable uniquement à partir d'Excel 2013 et en version Pro

Simplifier la saisie des données sources

Simplifier la saisie avec la mise sous forme de tableau

Réaliser deux tableaux Excel avec une Mise sous forme de tableau. Outre la mise en forme dynamique, la "Mise sous forme de tableau" va permettre aux deux tableaux Excel d'avoir une mise à jour dynamique des futures nouvelles lignes via le bouton "Actualiser" du rapport.

Exemple de deux tableaux : Clients et Commandes (dans des feuilles Excel différentes) :

Les tableaux Clients et Commandes (dans deux feuilles Excel différentes)

Todo : Par sécurité, vérifiez la présence de doublons de numéros de commande dans le tableau Commandes avec la fonction Excel "Mise en forme conditionnelle".

Analyser les données dans un rapport

Lier plusieurs tableaux Excel dans un 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". Attention, cette option n'est disponible que depuis Excel 2013 pro qui utilise le modèle de données Powerpivot.


La fenêtre Excel "Créer un tableau croisé dynamique" avec l'option "Ajouter ces données au modèle de données"


Dans la fenêtre des champs, cliquez ensuite 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.

Créer une relation entre les tables du rapports

Pour que l'on puisse créer un TCD (rapport) à partir de champs provenant de différents tableaux, les tableaux doivent êtres reliés par des "relations", c'est à dire, des colonnes qui utilisent des données communes comme, par exemple, un numéro de client unique dans un tableau de client et, ce même numéro de client dans un tableau de commandes :

Lorsque l'on crée un rapport (TCD) à partir de champs qui proviennent de différents tableaux, apparaît automatiquement, dans la fenêtre des champs, le bouton "CREER" :


Fenêtre des champs de tableau croisé dynamique avec avec bouton CRÉER une relation

Dans la fenêtre "Créer une relation" vous sélectionnez les deux champs qui vont permettre à Excel de lier les deux tableaux entre eux. Attention, cette colonne doit être unique dans les deux tableaux. Idéalement, c'est une colonne de numéro comme ci-dessus le champ "N° client" de la table Client et le champ "N° de client" de la table Commande. En langage base de données, on parle de relation entre clé primaire et clé étrangère (la clé primaire d'un autre tableau) :


Fenêtre des relations de tableau croisé dynamique

Afficher la fenêtre Relation d'un rapport

Une fois la relation réalisée, vous pouvez gérer les relations via la fenêtre "Relations" de l'onglet "Analyse du tableau croisé dynamique", bouton Relations :


Bouton de commande "Gérer les relations" de tableau croisé dynamique (permet d'ouvrir la fenêtre "Gérer les relations")


Fenêtre de gestion de relations de tableau croisé dynamique

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

Mise en application : Afficher le total des commandes par date et par client

1. Afin d'afficher la somme des commandes par nom de client, dans la fenêtre des champs, glissez le champ "Nom client" en Lignes et le champ "Total" en Valeurs. On constate que le résultat n'est pas celui attendu. Il nous faut créer une relation.
   
   
   Fenêtre Excel des champs de Tableau Croisé Dynamique avec le bouton "CREER..." (une relation)
    
2. Créez ensuite une relation entre le champ "N° client" de la table Client et le champ "N° de client" de la table Commande. Lorsque la relation est effective, Excel actualise immédiatement les calculs et afficher la somme des commandes par nom de client.
   
   
   La fenêtre Excel "Créer une Relation" (dans un Tableau Croisé Dynamique) : Plage1 = Table Commande et Plage = Table Client
    
3. Glissez ensuite le champ "N° commande" dans la zone Lignes, en dessous du champ "Nom client". On peut ainsi afficher les différents numéro de commande par client.
   
    
4. Glissez enfin le champ "Date" dans la zone Lignes, au dessus des autres champs. Excel affiche les commandes par date et par nom de client.

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 et 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.

◊ Créer un jeu d'enregistrement

Dans l'onglet Analyse, 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.

Todo : Afficher le nombre de clients qui ont fait - au moins - une commande : Dans un nouveau rapport, glissez le champ "N° de client" de la table Commande dans la zone Valeurs (pas besoin de ligne, ni de colonne) et appliquez la fonction Total Distinct. Vous devriez avoir quatre clients distinct qui ont passés une commande/

Analyse et gestion des données avec Powerpivot

◊ Activer le complément Powerpivot

PowerPivot est un complément Excel qui, par défaut, n'est pas activée. C'est Powerpivot qui gère l'ensemble des données utilisées dans un rapport de Tableau Croisé Dynamique basé sur le modèle de données. Si vous voulez modifier ces données (comme les noms des jeux de champs; "Plage", c'est pas très parlant...) ou supprimer l'historique de votre modèle de données, il faut activer "Powerpivot".

Si vous avez utilisé le modèle de données (avec vos TCD), PowerPivot qui doit-être déjà visible dans le ruban Excel (onglet Données"). Sinon, menu Fichier > Options > Compléments. Zone Gérer, cliquez sur Compléments COM > Atteindre. Cochez la case Microsoft Office PowerPivot.



Powerpivot dans Excel par Easy-Micro [Ecran Vue de données avec la fenêtre Zone de calcul en bas]

◊ Créer un rapport Excel directement depuis Powerpivot

Depuis Powerpivot, onglet Accueil, groupe "Obtenir des données externes" puis "à partir d'autres sources". Sélectionnez votre fichier Excel puis cocher la bonne feuille.

◊ Modifiez les noms des jeux de champs depuis Powerpivot

"Plage1", c'est pas très parlant... C'est mieux d'avoir "Clients" ou "Commandes"... Regardez en bas de l'écran Powerpivot, les jeux de plage sont comme des feuilles Excel. Vous pouvez donc faire Renommer, Supprimer....

◊ Créer un champ calculé dans PowerPivot

Dans Powerpivot, sélectionnez la colonne Total de la table Commandes et cliquer sur le bouton "Somme automatique" de l'onglet Accueil. La somme apparaît dans la fenêtre Zone de Calcul visible en bas de l'écran. Si la zone Calcul n'est pas visible, cliquer à l'extrême droite de l'onglet Accueil de Powerpivot, sur le bouton "Zone de calcul". Pour modifier le titre anglais, cliquez dans la barre des formules de Powerpivot. Lorsque vous reviendrai dans Excel, un nouveau champ est apparu dans la zone des champs.


Champ calculé créé depuis Powerpivot

◊ Diagramme Powerpivot

Diagramme Powerpivot par Easy-Micro [Ecran Vue de diagramme]. On se croirait dans le Modèle Conceptuel de Données d'Access