Easy-Micro


ESPACEMEMBRE TCD et SGBD
Rapports Excel et SGBD

Gestion des bases de données dans les Tableaux Croisés Dynamiques Excel

Gestion des bases de données dans les rapports 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 mie 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 Excel : Client et Commandes (dans des feuilles différentes) :
Tableau Client Excel pour Rapport Tableau Commande Excel pour Rapport
Les tableaux Excel Client et Commande (dans deux feuilles différentes)

Exercice : Par sécurité, vérifiez la présence de doublons de n° de commande dans le tableau Commande avec la fonction Excel "Mise en forme conditionnelle".

Exercice : Faire une liste déroulante Excel dans la colonne N° de Client du tableau Commande.

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.

Fenêtre Excel : 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"

Il faut répéter cette action autant de fois que l'on a de feuilles à consolider, il faut donc créer autant de rapport (TCD) que de feuilles, puis supprimer tout les nouveaux rapports sauf le dernier. 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.
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 cela fonctionne, vos 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 vous créez un rapport à partir de champs qui proviennent de feuilles différentes, il est nécessaire de créer une relation : dans ce cas, dans la fenêtre des champs apparaît le bouton "CREER". 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 de clé étrangère.

Exercice :
  1. Afin d'afficher la somme des factures 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 de champs de Rapport Excel
    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.

    Fenêtre Excel Relations pour Rapport
    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.

Rapport Excel avec relation Fenêtre champs Excel pour Rapport
Photo d'écran du Tableau Croisé Dynamique et du bas de la fenêtre Excel des champs de Tableau Croisé Dynamique

Afficher la fenêtre Relation d'un rapport

Une fois votre relation réalisée, vous pouvez accéder à la fenêtre Relation depuis l'onglet Analyse, groupe Relation

Changer la source de données d'un rapport qui comporte des relations

Pour changer la source de données d'un rapport qui comporte des relations, il faut aller 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", vous pourrez changer la source de données.

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

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.

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

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 l'onglet "Powerpivot" qui doit-être déjà visible dans le ruban Excel si vous avez utilisé le modèle de données. Sinon, menu Fichier > Options > Compléments. Zone Gérer, cliquez sur Compléments COM > Atteindre. Cochez la case Microsoft Office PowerPivot.

Note : Si Powerpivot est déjà dans la liste des compléments Excel (menu Fichier > Options > Compléments) et que vous ne voyez pas son onglet dans le ruban Excel, allez dans l'onglet Developpeur, Bouton "Compléments COM" et sélectionnez "Powerpivot".

Gérer le rapport et les tableaux Excel depuis Powerpivot

Allez ensuite, dans l'onglet Excel Powerpivot, bouton "Gérer". Regardez en bas de l'écran, les jeux de plage sont comme des feuilles Excel. Vous pouvez donc faire Renommer, Supprimer...

Créer un rapport Excel directement depuis Powerpivot

Allez dans l'onglet Powerpivot, bouton "Gérer"...

powerpivot Excel
Powerpivot dans Excel par Easy-Micro [Ecran Vue de données]

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

Note de mon collègue Philippe IMBERT : On peut effectivement considérer que les TCD, complétés par PowerPivot, offrent les mêmes fonctionnalités qu'Access en termes de créations de tables, relations et requêtes. Par contre, Access va plus loin en proposant la création de formulaires et d'états.


Suivez les aventures des skippers aquitains sur EasyVoile.com
Flux RSS Easy-Micro - Abonnez-vous Easy-Micro [ François Bisch ]
Mercredi 16 janvier 2019 - Semaine 03 - Saint Marcel (Demain: Sainte Roseline)
Aujourd'hui, c'est l'anniversaire de Richard Bohringer (1942-77 ans), acteur (subway, Diva, Le grand chemin...), réalisateur (2006 : C'est beau une ville la nuit, d'après son livre publié en 1988), chanteur et écrivain français. Autre 16 janvier: 1920 - L'alcool est prohibé aux Etats-Unis.
Haut de page Easy-Micro