mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013 / 2016

Les Tableaux croisés dynamiques et Power Pivot

En plus des fonctions statistiques, type : Somme(), Max(), Min(), Moyenne(), ... et des graphiques, Excel dispose de deux outils très puissants pour analyser des données et faire des synthèses très poussées : les Tableaux croisés dynamiques, en abrégé TCD et le complément Power Pivot.

Dans ce cours on va voir les points qui suivent.

  1. Les tableaux croisés dynamiques
    1. Création de nouveaux tableaux croisés dynamiques
    2. Modification des sélections et des options de champ
    3. Création d'un segment
    4. Groupement d'enregistrements
    5. Utilisation de champs calculés
    6. Mise en forme des données
  2. L'outil Power Pivot
    1. Utilisation de Power Pivot
    2. Gestion des relations

À noter que ces points entrent dans les compétences à préparer pour l'examen 77-427 de la certification MOS niveau Expert (Première partie).

Recevez ce cours au format PDF en envoyant un message ici

Les tableaux croisés dynamiques

Les Tableaux Croisés Dynamiques (TCD ou rapport) permettent de croiser les différents champs des données afin de faciliter leur analyse. Ils permettent aussi de grouper, combiner, synthétiser et comparer un grand nombre d'informations. Avec un TCD, on peut aussi ajouter des champs calculés dans le but de faire des conclusions statistiques.

Excel, les Tableaux croisés dynamiques et Power Pivot

Le document ci-dessus, que vous pouvez télécharger ici, montre la capture d'écran d'un très grand tableau où les différentes informations sont enregistrées au fur et mesure que les ventes des articles sont faites, dans un magasin de vente de matériel informatique au cours de l'année 2019.

La grande quantité des informations et le stockage en vrac tel qu'il est fait dans ce tableau, ne facilitent pas la lecture des données, et encore moins leur analyse ou leur comparaison. C'est justement dans ce cas de figure que la création d'un TCD est nécessaire.

Créer un tableau croisé dynamique

L'insertion d'un Tableau Croisé Dynamique (TCD) se fait via l'onglet Insertion en suivant les étapes ci-après :

  1. Cliquez dans le tableau source de données
  2. Ouvrez l'onglet Insertion
  3. Dans le groupe Tableau, à l'extrême gauche du ruban, cliquez sur le bouton Tableau Croisé Dynamique
  4. Dans la boite de dialogue Créer un tableau croisé dynamique qui s'ouvre, dans la section Choisissez les données à analyser, vérifier si la zone sélectionnée correspond bien à ce que vous désirez, sinon vous pouvez la redéfinir.
  5. Dans la zone Choisissez l'emplacement de votre rapport de tableau croisé dynamique de cette même boite, choisissez l'emplacement où vous voulez insérer votre TCD
  6. Validez votre paramétrage en cliquant sur OK

Excel, les Tableaux croisés dynamiques et Power Pivot

La méthode décrite ci-dessus, est utilisée dans le cas où vous partez de données rangées dans une plage de cellules, non définie comme Tableau. Si vos données de départ sont contenues dans un Tableau, vous pouvez procéder d'une manière légèrement différente comme le montre l'illustration suivantes :

Excel, les Tableaux croisés dynamiques et Power Pivot

  1. Cliquez dans le tableau source de données, ce qui a pour effet de faire apparaitre le super-onglet Outils de tableau avec l'onglet Création.
  2. Activez l'onglet Création, si nécessaire
  3. Dans le groupe Outils, cliquez sur le bouton Tableau Croisé Dynamique
  4. Dans la boite de dialogue Créer un tableau croisé dynamique qui s'ouvre, vous n'avez plus qu'à choisir l'emplacement où vous voulez insérer votre TCD
  5. Validez votre paramétrage en cliquant sur OK

Important :

Pour réussir le TCD, la base de données source (tableau de départ) doit respecter certaines règles :

  • Pas de lignes vides
  • Pas de colonnes vides
  • Toutes les colonnes doivent avoir un en-tête
  • Les données d'une même colonne, doivent toutes être d'un même type
  • Pas de cellules fusionnées

Une fois le paramétrage d'insertion validé, le TCD est inséré dans l'emplacement prédéfini (Dans ce cas, une nouvelle feuille, comme c'est demandé au cours du paramétrage). Cette feuille prend un nom standard par défaut, que vous pouvez modifier en lui attribuant un nom significatif. Le TCD est matérialisé par cet encadré blanc à gauche de la feuille, mais il est encore vierge. L'apparition du super-onglet Outils de tableau croisé dynamique avec deux onglets Analyse et Création, prouve le fait.

Excel, les Tableaux croisés dynamiques et Power Pivot

Le tableau n'est pas affiché car il ne contient pas encore de données. Il faut donc lui indiquer les informations à afficher en lignes, en colonnes et en valeurs. Pour ce faire, rendez-vous dans le volet Champs de tableau croisé dynamique qui apparait à droite de l'écran et faire glisser les champs désirés vers les zones Colonnes, Lignes et Valeurs, situées juste en bas.

Excel, les Tableaux croisés dynamiques et Power Pivot

La disposition des différentes zones du volet des champs de tableau croisé dynamique, peut être personnalisée selon le goût et le besoin de chacun par le biais du bouton de configuration.

Excel, les Tableaux croisés dynamiques et Power Pivot

Ainsi, on peut choisir une disposition Empilée, une disposition Côte à côte, la liste des Champs uniquement ou bien les Zones uniquement.

Excel, les Tableaux croisés dynamiques et Power Pivot

Choisir les champs à afficher dans le TCD

L'insertion des informations dans un TCD est relativement facile, il suffit de préciser ce que l'on désir afficher dans les différentes zones du tableau. Pour mettre en œuvre ces opérations, on va procéder progressivement sous forme de travaux pratiques (TP) illustrant différentes situations.

Afficher le total des ventes pour chaque catégorie

TP 1 : Afficher le total des ventes pour chaque catégorie

Si ce n'est pas encore fait, téléchargez le document support ici

Nous souhaitons, pour une première analyse, afficher la somme des quantités vendues pour chaque catégorie de matériel, comme le montre la capture suivante.

Excel, les Tableaux croisés dynamiques et Power Pivot

Dans chaque ligne du TCD s'affiche le nom d'une catégorie et en face de chaque catégorie s'affiche le total des articles vendus pour toute la période concernée. Tout en bas du tableau, on trouve la ligne Total général qui affiche le total des articles vendus, toutes catégories confondues.

Pour cela vous devez glisser le champ Catégorie de la zone Champs en haut vers la zone Lignes située en bas, ainsi chaque catégorie sera affichée dans une ligne. Pour afficher les valeurs des quantités vendues, vous glissez le champ Quantité vers la zone Valeurs.

L'animation suivante montre comment insérer les champs.

Excel, les Tableaux croisés dynamiques et Power Pivot

Afficher le total des ventes pour chaque catégorie et pour chaque Mois

TP 2 : Afficher le total des ventes pour chaque catégorie et pour chaque Mois

Si ce n'est pas encore fait, téléchargez le document support ici

Pour afficher plus de détails, on aimerait avoir la somme des ventes pour chaque catégorie et pour chaque mois de l'année, comme le montre la capture suivante.

Excel, les Tableaux croisés dynamiques et Power Pivot

Les en-têtes des lignes affichent les catégories et les en-têtes des colonnes affichent les mois de l'années. Le corps du tableau, quant à lui, affiche les valeurs des quantités vendues. On doit donc renseigner les trois zones : Lignes, Colonnes et Valeurs, avec les informations adéquates en les faisant glisser à partir de la zone des champs.

Excel, les Tableaux croisés dynamiques et Power Pivot

Quelques remarques à faire tout de suite :

  • En insérant le champ Date, Excel ajoute automatiquement au-dessus de ce champ, un nouveau champ Mois pour grouper les différentes dates par Mois.
  • Dans la zone Valeurs, Excel additionne les valeurs Somme de Quantité, pour calculer le total des Quantités vendues pour chaque catégorie. Cette option peut être changée par un autre calcul.
  • Sur le TCD, remarquez que les noms des mois sont précédés d'un petit (+) qui permet de développer le champ et ainsi montrer les détails des ventes jour pour jour.
  • Excel, les Tableaux croisés dynamiques et Power Pivot
  • La cellule Mois est munie d'un bouton flèche pour effectuer des filtres sur les mois à afficher
  • Excel, les Tableaux croisés dynamiques et Power Pivot
  • On peut faire de même pour les catégories pour effectuer un filtre
  • Excel, les Tableaux croisés dynamiques et Power Pivot

Supprimer un champ non désiré

Pour supprimer un champ non désiré, il suffit de le décocher dans la liste, ou bien de le faire glisser hors de la zone comme le montre la capture.

Excel, les Tableaux croisés dynamiques et Power Pivot

Effectuer des affichages détaillés

TP 3 : Détailler l'affichage des ventes des articles

Si ce n'est pas encore fait, téléchargez le document support ici

Au lieu de l'affichage des valeurs globales des ventes par catégorie, on aimerait avoir les ventes détaillées des articles (par désignation) pour chaque catégorie.

Excel, les Tableaux croisés dynamiques et Power Pivot

Pour afficher les détails des ventes pour chaque catégorie, on a besoin de lignes supplémentaires pour indiquer les Désignations vendues pour chacune des catégories. Pour ce faire, on doit glisser le champ Désignation vers la zone Lignes et le placer sous le champ Catégorie déjà en place. Ainsi, on a deux informations dans la zone Lignes : la Catégorie qui est l'information "Parente" et en dessous, la Désignation qui est l'information "fille".

Excel, les Tableaux croisés dynamiques et Power Pivot

Remarquez que les informations "Parentes" (les Catégories), sont munies d'un bouton (-) qui permet d'enrouler la Catégorie et masquer les détails.

Modification des sélections et des options de champ

Si vous mettez plusieurs champs dans une même zone, veillez à ce que le champ parent soit au-dessus du champ enfant ; comme c'est montré dans le document ci-dessus, le champ Catégorie, champ parent, est placé au-dessus du champ Désignation qui est champ enfant de Catégorie. De cette manière on obtient un affichage hiérarchique.

Pour modifier l'emplacement d'un champ, il suffit de cliquer dessus et de le faire glisser vers l'endroit voulu. La suppression d'un champ se fait en le décochant dans la liste des champs ou bien en le faisant glisser hors de toute zone.

La suppression et le déplacement d'un champ peut se faire aussi par un clic sur le champ concerné puis de choisir l'opération voulue dans la liste.

Excel, les Tableaux croisés dynamiques et Power Pivot

À l'insertion d'un champ dans la zone VALEURS, Excel effectue automatiquement une opération par défaut selon la nature de l'information contenue dans ce champ. Par exemple pour un champ numérique, Excel effectue par défaut une opération Somme qui consiste à additionner les valeurs de ce champ ; pour les données de type texte ou bien Dates, Excel effectue l'opération Nombre qui a pour but de compter le nombre de ces valeurs.

Excel, les Tableaux croisés dynamiques et Power Pivot

Toutefois, selon le besoin, on peut décider de l'opération à effectuer en allant dans les paramètres du champ à modifier. Différentes méthodes sont possibles pour accéder à ces paramètres après avoir sélectionné le champ.

Méthode 1 :

Dans le TCD, cliquer dans le champ en question pour l'activer, puis sous l'onglet Analyse, groupe Champ actif, cliquer sur Paramètres de champs.

Excel, les Tableaux croisés dynamiques et Power Pivot

Méthode 2 :

Effectuer un clic droit sur le champ concerné dans le TCD, et choisir la ligne Paramètres des champs de valeurs dans le menu contextuel qui apparait

Excel, les Tableaux croisés dynamiques et Power Pivot

Méthode 3 :

Dans la zone VALEURS, on clique sur le champ dont on veut modifier les paramètres puis dans le menu contextuel qui apparait, on choisit la ligne Paramètres des champs de valeurs.

Excel, les Tableaux croisés dynamiques et Power Pivot

Ces trois méthodes aboutissent toutes à la même fenêtre Paramètres des champs de valeurs qui permet de choisir le type de calcul à effectuer sur les valeurs du champ.

Excel, les Tableaux croisés dynamiques et Power Pivot

TP 4 : Trouver la date des dernières ventes des articles

Si ce n'est pas encore fait, téléchargez le document support ici

On veut afficher les dates de réalisation de la dernière vente pour chaque article.

Excel, les Tableaux croisés dynamiques et Power Pivot

Comme c'est dit précédemment, le calcul fait par défaut sur le champ Date est Nombre, c'est à dire le comptage, une information qui n'est pas utile dans notre cas. L'information que nous aimerions avoir c'est plutôt la date de la dernière vente pour chaque article. Pour ce faire, on doit chercher la date la plus grande, soit la valeur maximale (Max) des dates de vente pour chaque désignation. L'animation suivante montre le déroulement de l'opération.

Excel, les Tableaux croisés dynamiques et Power Pivot

Remarquez au passage que nous avons modifié le Format d'affichage du champ pour obtenir des Dates.

On peut aussi modifier le mode d'affichage des valeurs d'un champ, par exemple le TCD suivant affiche dans sa dernière colonne, les quantités des unités vendues pour chaque article au cours de la période concernée (2246 Claviers DELL, 1711 Souris optiques, etc.).

Excel, les Tableaux croisés dynamiques et Power Pivot

Ce qu'on voudrait, c'est afficher ces chiffres en pourcentage sur le total général des ventes. Pour ce faire, on effectue les opérations suivantes :

  1. Cliquer dans une valeur du champ à modifier, dans notre cas la colonne Somme de Quantité
  2. Sous l'onglet Analyse, dans le groupe Champ actif, cliquer sur Paramètres de champs
  3. Dans la fenêtre Paramètres des champs de valeurs, ouvrir l'onglet Afficher les valeurs
  4. Ouvrir la liste Afficher les valeurs et choisir la ligne % du total général
  5. Valider en cliquant sur OK

Excel, les Tableaux croisés dynamiques et Power Pivot

Les nouvelles valeurs de la colonne représentent le part des ventes (en quantité) de chaque article sur l'ensemble des quantités vendues. Ne pas oublier de modifier le texte de l'étiquette de la colonne pour l'adapter au nouvel affichage. Le résultat est donné par la capture suivante :

Excel, les Tableaux croisés dynamiques et Power Pivot

TP 5 : Travail à faire :

Si ce n'est pas encore fait, téléchargez le document support ici

Afficher les valeurs du champ Somme des Prix en % sur le total général

Excel, les Tableaux croisés dynamiques et Power Pivot

Utilisation de champs calculés

Un champ calculé est un champ qui n'existe pas dans la table source et que l'on peut obtenir à partir des champs existants, par des calculs ou bien par l'utilisation de fonctions Excel.

Pour mettre en pratique la création d'un champ calculé, on va calculer le taux de retour pour chaque désignation. Le retour c'est le nombre de produits défectueux retournés par le client. Ce champ n'existe pas dans le tableau source mais on peut le déduire à partir des champs Retour et Quantité en divisant, pour chaque article, le nombre de retour par la quantité vendue.

Excel, les Tableaux croisés dynamiques et Power Pivot

Créer un champ calculé

Pour insérer un champ calculé on procède de la manière suivante :

  1. Cliquer dans le TCD pour l'activer
  2. Cliquer sur l'onglet Analyse et ouvrir le groupe Calculs si nécessaire
  3. Ouvrir le bouton Champs, éléments et jeux puis cliquer sur la ligne Champ calculé
  4. Excel, les Tableaux croisés dynamiques et Power Pivot
  5. Dans la boite de dialogue Insertion d'un champ calculé, taper un nom significatif pour le nouveau champ dans la zone Nom
  6. Dans la zone Formule, écrire l'expression de calcul, en utilisant les noms des champs présents dans la liste Champs.
  7. Valider en cliquant sur OK
  8. Excel, les Tableaux croisés dynamiques et Power Pivot
  9. Le nouveau champ apparait dans le tableau et dans la liste des champs
  10. Excel, les Tableaux croisés dynamiques et Power Pivot
  11. Se rendre dans les paramètres du nouveau champ pour choisir le format Pourcentage
  12. Excel, les Tableaux croisés dynamiques et Power Pivot

Le résultat final est visualisé par la capture ci-après. Ne pas oublier de modifier les en-têtes des colonnes.

Excel, les Tableaux croisés dynamiques et Power Pivot

Modifier ou supprimer un champ calculé

Vous vous-êtes trompé dans l'expression de calcul du champ calculé et vous voulez la corriger ou bien carrément supprimer ce champ ? Suivez ces étapes :

  1. Cliquez dans le TCD pour l'activer et de ce fait, faire apparaitre l'onglet Analyse
  2. Dans le groupe Calculs de cet onglet, ouvrez le bouton Champs, éléments et jeux puis cliquez sur la ligne Champ calculé
  3. Dans la boite de dialogue Insertion d'un champ calculé, ouvrez la zone Nom et choisissez l'intitulé du champ à modifier ou à supprimer
  4. Pour une rectification de la formule, apportez les modifications nécessaires à l'expression contenue dans la zone Formule puis cliquez sur le bouton Modifier
  5. Pour supprimer le champ indiqué dans la zone Nom, il suffit de cliquer sur le bouton Supprimer
  6. Validez votre opération en cliquant sur OK

L'animation suivante montre comment modifier un champ calculer puis comment le supprimer.

Excel, les Tableaux croisés dynamiques et Power Pivot

Filtres et segments

Pour filtrer les données dans un TCD et n'afficher que les informations qui nous intéressent, on peut procéder de plusieurs manières.

Filtrer à l'aide des en-têtes des champs

L'en-tête Étiquette des lignes est muni d'un bouton flèche qui déploie une liste de commandes permettant de filtrer les lignes. Les cases à cocher situées en bas, permettent de sélectionner les items à afficher et désélectionner les items à masquer.

Excel, les Tableaux croisés dynamiques et Power Pivot

La commande Filtres s'appliquant aux étiquettes propose un ensemble de commandes pour un paramétrage avancé du filtre. Étant donné que le contenu de la ligne, dans le cas présent, est du texte, les commandes proposées sont adaptées aux chaines de caractères.

Excel, les Tableaux croisés dynamiques et Power Pivot

Par exemple, on s'intéresse aux produits HP uniquement et on veut afficher les ventes concernant cette famille. Vu que le mot "HP" peut figurer n'importe où dans le libellé, au début, au milieu ou à la fin, la commande "Contient..." convient bien pour cette opération. L'animation suivante montre une démonstration.

Excel, les Tableaux croisés dynamiques et Power Pivot

La commande Filtres s'appliquant aux valeurs, quant à elle, permet d'appliquer un filtre en se basant sur les données affichées dans le corps du TCD (les valeurs). Dépendant de la nature des données, les filtres proposés dans la liste, dans notre cas, sont adaptés aux valeurs numériques.

Excel, les Tableaux croisés dynamiques et Power Pivot

Pour les colonnes c'est pareil, on peut les filtrer de la même manière selon un critère approprié à la nature des données. On peut choisir d'afficher les opérations de vente effectuées pendant un ou plusieurs mois et ce en cochant les cases qui leur correspondent, ou bien d'effectuer un filtre plus poussé en utilisant les Filtres chronologiques. Le contenu des titres des colonnes étant de type Date, les filtres affichés dans la liste sont tous applicables à ce type (Voir capture ci-dessous).

Excel, les Tableaux croisés dynamiques et Power Pivot

TP 6 : Travail à faire :

Si ce n'est pas encore fait, téléchargez le document support ici

En utilisant les filtres chronologiques, afficher les ventes du Trimestre 3 de l'année 2019

Excel, les Tableaux croisés dynamiques et Power Pivot

Filtrer à l'aide d'un clic droit sur le champ

Une autre façon de réaliser des filtres est de cliquer avec le bouton droit de la souris sur l'en-tête des lignes ou bien des colonnes, puis de pointer sur la ligne Filtrer et de choisir une commande dans la liste.

Excel, les Tableaux croisés dynamiques et Power Pivot

Utiliser la zone FILTRES

Cette méthode présente deux intérêts. Le premier avantage est que l'on peut utiliser un champ ne figurant pas dans le TCD, comme champ de filtre. Le deuxième avantage c'est que l'on peut utiliser plusieurs champs pour filtrer les données. La mise en œuvre de la méthode est facile, puisqu'il suffit de faire glisser les champs et les déposer dans la zone FILTRES

Excel, les Tableaux croisés dynamiques et Power Pivot

L'animation ci-dessous montre la mise en place et le fonctionnement de ces filtres. Dans ce cas on a choisi d'appliquer les filtres sur les champs Catégorie et ID_Client.

Excel, les Tableaux croisés dynamiques et Power Pivot

Les filtres sont placés au-dessus du TCD et permettent de faire des choix simples ou bien des choix multiples en activant la case Sélectionner plusieurs éléments. Le filtre ID_Client permet d'afficher les achats effectués par un (ou des) client(s) particulier(s) et le filtre Catégorie affiche les catégories concernées par les ventes.

Par exemple, le tableau filtré suivant montre tous les achats réalisés par le Client dont l'ID est 1 pour toutes les catégories et ce pour chaque mois de l'année.

Excel, les Tableaux croisés dynamiques et Power Pivot

La faiblesse de ces filtres apparait lorsqu'on décide de faire un choix multiple pour le champ ID_Client. Le TCD est bien filtré selon nos critères mais l'étiquette du filtre ne montre pas explicitement nos choix, comme le montre la capture suivante dans laquelle on a demandé au TCD d'afficher les ventes réalisées par les clients 1 et 2. L'étiquette du filtre ne donne aucune idée sur les clients concernés par l'affichage, elle signale seulement que l'affichage concerne Plusieurs éléments, chose qui manque de précision et oblige d'ouvrir la liste du filtre pour voir les éléments cochés.

Excel, les Tableaux croisés dynamiques et Power Pivot

Utiliser des segments

Les Segments, en anglais Slicers, sont des outils de filtrage des données d'un TCD, plus ergonomiques et plus pratiques que les filtres classiques. Introduits dans Excel depuis la version 2010, ils présentent les avantages suivants :

  • Ils mettent en évidence les choix multiples des éléments
  • Ils ont un rendu visuel meilleur
  • Ils peuvent agir sur plusieurs objets (TCD, Graphiques) en même temps

L'insertion d'un segment se fait en cliquant sur le bouton Insérer un segment dans le groupe Filtrer sous l'onglet Analyse, puis de cocher le champ (ou bien les champs) sur lequel on veut appliquer le segment.

Excel, les Tableaux croisés dynamiques et Power Pivot

Après validation, chacun des champs cochés est transformé en segment formé d'une barre de titre avec le nom du champ, et des boutons cliquables. Ci-dessous on voit les segments correspondant aux champs ID-Client et Catégorie.

Excel, les Tableaux croisés dynamiques et Power Pivot

  1. Boutons de sélection correspondant aux différents éléments du champ
  2. Bouton pour activer ou désactiver la sélection multiple
  3. Bouton pour annuler le filtre, autrement dit, afficher tous les items

En cliquant sur un segment, on active un nouvel onglet Outils segment avec le sous-onglet Options qui contient des outils pour modifier l'apparence du segment sélectionné. Des outils que l'on va utiliser pour mettre en forme les segments, notamment pour les redimensionner, modifier leur style et pour changer la disposition des boutons en plusieurs colonnes. La taille du segment est aussi modifiable en utilisant les poignées de redimensionnement qui apparaissent sur les bords du segment activé.

Excel, les Tableaux croisés dynamiques et Power Pivot

Les différentes manipulations de personnalisation de l'aspect des segments, sont montrées dans l'animation suivante :

Excel, les Tableaux croisés dynamiques et Power Pivot

Lors de l'insertion d'un segment, celui-ci n'agit que sur un seul TCD, mais il est possible de commander plusieurs objets (TCD, Graphiques) par l'intermédiaire d'un même segment. De cette manière on obtient des affichages cohérents, dans le cas d'un tableau de bord par exemple.

Excel, les Tableaux croisés dynamiques et Power Pivot

Sur la feuille Feuil1 ci-dessus, se trouve un segment basé sur le champ ID_Client avec un TCD qui représente la somme des Quantités vendues pour chaque Désignation et le graphique qui montre les quantités vendues pour chaque Catégorie. Le rôle du segment ici, est de filtrer les données de telle façon à ne montrer que les quantités achetées par un (des) client(s) particulier(s).

Comme le montre l'animation ci-dessous, les valeurs du TCD répondent bien au filtre (segment) alors que le graphique ne réagit pas du tout. La cause c'est que le segment communique bien avec le TCD, mais avec le graphique ça passe pas.

Excel, les Tableaux croisés dynamiques et Power Pivot

Pour mettre en communication le segment avec d'autres objets on suit les étapes suivantes :

  1. Cliquer sur le segment pour l'activer
  2. Sous l'onglet Options, cliquer sur le bouton Signaler les connexions
  3. Dans la fenêtre Connexions de rapport qui s'ouvre, cocher les objets à mettre en connexion avec le segment
  4. Valider

Excel, les Tableaux croisés dynamiques et Power Pivot

Comme vous le constatez, le TCD et le Graphique sont bien en communication avec le segment et répondent tous les deux aux filtres.

Pour se retrouver facilement dans la listes des TCD et Graphiques, il est judicieux de renommer les objets concernés en leur donnant des noms suffisamment explicites.

Pour renommer un TCD ou un GCD (Graphique Croisé Dynamique), on clique dessus d'abord puis on suit les étapes qui figurent dans la capture suivante

Excel, les Tableaux croisés dynamiques et Power Pivot

Remarque :

Lorsqu'on renomme un GCD de cette façon, en réalité ce que l'on renomme c'est le TCD sous-jacent du graphique.

Insérer une chronologie

Une Chronologie est un segment un peu particulier, du fait qu'il sert à filtrer les champs de type Date. Pour insérer ce segment, on utilise le bouton Insérer une chronologie situé dans le groupe Filtrer sous l'onglet Analyse. Dans la boite de dialogue qui apparait, on coche le(s) champ(s) désiré(s) puis on valide le choix. À remarquer que la liste de cette boite ne propose que les champs de type Date, chose qui est en parfaite harmonie avec la nature du filtre.

Excel, les Tableaux croisés dynamiques et Power Pivot

Après validation de l'insertion, le segment chronologique apparait et comporte différents éléments, comme le montre la capture ci-dessous

Excel, les Tableaux croisés dynamiques et Power Pivot

  • En-tête : indique le champ concerné par le filtre
  • Étiquette de la sélection : montre la période affichée, exemple ici porte sur la période : Février 2019
  • La barre de défilement : pour faire défiler le contenu du segment
  • Niveau de temps : c'est pour choisir les intervalles de temps à montrer (Années, Trimestres, Mois, Jours)
  • Excel, les Tableaux croisés dynamiques et Power Pivot
  • Les boutons de sélection : permettent de sélection par un clic ou bien par un cliquer-glisser la tranche de temps à afficher

Pour étendre la plage chronologique à plusieurs périodes, on clique sur l'une des extrémités du bouton sélectionné puis on la fait glisser vers la droite ou bien vers la gauche.

Excel, les Tableaux croisés dynamiques et Power Pivot

TP 7 : Travail à faire

Si ce n'est pas encore fait, téléchargez le document support ici

  1. Insérez sur la même feuille que le TCD, le GCD (Graphique Croisé Dynamique) basé sur le tableau Ventes2019
    • Catégories dans AXE
    • Quantité dans VALEURS
  2. Excel, les Tableaux croisés dynamiques et Power Pivot
  3. Insérez la chronologie liée au champ Date
  4. Créez la connexion entre la chronologie et le GCD
  5. Modifiez le niveau de temps aux TRIMESTRES

Groupement d'enregistrements

Pour plus de clarté dans un tableau TCD, il peut être utile d'organiser ses éléments en les regroupant selon un critère qui leur est commun. Ce critère peut être :

  • Les éléments sélectionnés
  • Appartenir à un même intervalle de temps
  • Compris entre les mêmes valeurs numériques
Grouper les sélections

On utilise le groupement de sélection pour mettre ensemble des enregistrements selon un critère personnalisé. Supposons par exemple que les différents produits sont vendus dans deux magasins différents : Magasin 1 et Magasin 2, et qu'on veuille les grouper selon ce critère, comme le montre la capture suivante.

Excel, les Tableaux croisés dynamiques et Power Pivot

Pour aboutir à ce résultat, on sélectionne tous les produits désirés puis dans l'onglet Analyse, choisir la ligne Grouper la sélection. Le groupe nouvellement constitué prend le nom Groupe 1, on change ce nom en Magasin 1. On procède de la même façon pour former le groupe Magasin 2.

Pensez à garder le touche Ctrl du clavier, enfoncée lors de la sélection des éléments, pour effectuer une sélection multiple.

Excel, les Tableaux croisés dynamiques et Power Pivot

Les étapes en détail sont mises en exécution dans l'animation suivante :

Excel, les Tableaux croisés dynamiques et Power Pivot

Grouper les Nombres

Grouper les nombres, nécessite que toutes les valeurs du champ en question soient reconnues par Excel comme des nombres. Pour cela, en cas où vous avez des problèmes de groupement, vérifiez que toutes vos valeurs sont bien des numériques et non pas du texte.

Pour mettre en œuvre le groupement des nombres, on va utiliser les données de la feuille Employés du classeur téléchargé. Cette feuille représente l'ancienneté, en nombre d'années, des employés des deux magasins de vente (57 employés).

Excel, les Tableaux croisés dynamiques et Power Pivot

TP 8 : Travail à faire

Commençons par créer le TCD qui affiche les années d'ancienneté avec les noms des employés. Sur une nouvelle feuille, construisez le TCD ci-après

Excel, les Tableaux croisés dynamiques et Power Pivot

Dans ce TCD, on voit que les employés sont déjà groupés par années d'ancienneté puisque tous les agents qui ont 1 année sont mis ensemble, ceux qui ont 2 années d'ancienneté etc. Ce que nous aimerions faire, c'est de les grouper davantage, par tranches de 5 ans, c'est à dire rassembler tous les employés qui ont de 1 à 5 ans dans un groupe, ceux qui ont de 6 à 10 ans et ainsi de suite.

Pour ce faire, on procède selon les étapes suivantes :

  1. Cliquer dans une cellule du champ Ancienneté
  2. Sous l'onglet Analyse, ouvrir le bouton Groupe
  3. Dans la liste, choisir la ligne Grouper le champ
  4. Dans la petite boite de dialogue qui s'ouvre, mettre la valeur de groupement dans la zone intitulée Par, dans notre cas on met la valeur 5
  5. Valider

Excel, les Tableaux croisés dynamiques et Power Pivot

On obtient alors une liste formée de trois groupes : 1-5, 6-10 et 11-15, avec chacun un petit bouton qui permet de déployer ou d'enrouler le groupe pour voir ou masquer les détails.

Excel, les Tableaux croisés dynamiques et Power Pivot

Grouper les Dates

Sous Excel 2013 les Dates ne sont pas groupées d'une façon spontanée, ce qui oblige de faire cette opération d'une façon "manuelle". Dans les versions postérieures à 2013, le groupement des dates se fait automatiquement, mais on pourrait avoir besoin de modifier ce groupement. Pour ce faire, on suit les étapes ci-après :

  1. Cliquer dans une cellule du champ à grouper
  2. Sous l'onglet Analyse, ouvrir le bouton Groupe
  3. Dans la liste, cliquer sur la ligne Grouper ce champ
  4. Dans la boite de dialogue qui s'ouvre, choisir dans la liste Par, le(s) type(s) de groupement désiré(s)
  5. Valider

Excel, les Tableaux croisés dynamiques et Power Pivot

Groupement automatique des dates

Depuis la version 2016 de Excel, les données des champs de type Date sont automatiquement groupées en Années, Mois ou bien Trimestres. Le simple fait de poser un champ Date dans la zone LIGNES ou bien COLONNES déclenche ce groupement.

Excel, les Tableaux croisés dynamiques et Power Pivot

Comme le montre cette animation, les Jours sont groupés en Mois, et on constate que le champ Mois qui n'existait pas auparavant, est ajouté à la liste des champs. Les jours étant organisés en groupes, il est possible de dérouler chacun des groupes pour montrer les détails au quotidien ou bien, si on voit que l'affichage des jours est trop détaillé, on peut supprimer le champ Date de la zone d'affichage pour ne garder que le champ Mois et ainsi on obtient une synthèse mensuelle.

Désactiver le groupement automatique des dates

Bien que ce groupement automatique des Dates effectué par Excel soit utile, il n'est pas toujours désiré et il arrive que vous cherchiez à l'éviter. Pour ce faire, si vous êtes sous la version 2016 ou supérieure, vous allez dans les Options de Excel, section Données et vous cochez la case Désactiver le groupement automatique des Dates puis validez.

Excel, les Tableaux croisés dynamiques et Power Pivot

Personnaliser le groupement des dates

Dans bien des situations, on est dans le besoin de grouper des dates en des ensembles de quelques jours, grouper tous les 7 jours par exemple.

TP 9 : Travail à faire

Un exemple concret : Dans une vue de synthèse, on veut afficher le nombre de produits vendus et le nombre de retour pour chaque semaine pendant toute l'année 2019.

Excel, les Tableaux croisés dynamiques et Power Pivot

Pour réaliser ce groupement personnalisé, on doit ouvrir la boite de dialogue Grouper et effectuer un groupement par jours puis renseigner le nombre de jours.

Excel, les Tableaux croisés dynamiques et Power Pivot

Excel, les Tableaux croisés dynamiques et Power Pivot

Une vérification s'impose et éventuellement petit réglage, pour finaliser le groupement. On vérifie si la date de début coïncide bien avec le premier jour de la semaine (Lundi), sinon on doit la modifier. Dans le cas présent le calendrier montre que la première date 01/01/2019 est un Mardi, il faudra donc mettre le lundi 31/12/2018 comme date de début.

Excel, les Tableaux croisés dynamiques et Power Pivot

Après ce réglage, on obtient un groupement qui coïncide bien avec les débuts et les fins de semaines.

Excel, les Tableaux croisés dynamiques et Power Pivot

Dissocier un groupement

Que ça soit un groupement de Dates, de Nombres ou personnalisé, l'opération est réversible. L'opposé du groupement est la Dissociation, c'est le fait d'annuler l'association entre les éléments d'un groupe. Après avoir cliqué dans une cellule du champ à dissocier, deux méthodes sont possibles :

  • Ouvrir le bouton Groupe puis cliquer sur la ligne Dissocier
  • Excel, les Tableaux croisés dynamiques et Power Pivot
  • Clic droit sur une cellule du champ puis sélectionner la ligne Dissocier
  • Excel, les Tableaux croisés dynamiques et Power Pivot

Mise en forme des données

Le TCD est à présent bien structuré et contient toutes les informations d'analyse qu'il faut, il reste à travailler son aspect pour mettre en relief les données importantes et faciliter sa lecture.

Avant d'appliquer une mise en forme au TCD, on doit d'abord décider d'afficher ou non quelques éléments tels que les Sous-totaux, les Totaux généraux, les En-têtes de colonnes ou bien les En-têtes des lignes. Les cases à cocher des options se trouvent dans les deux groupes Disposition et Options de style de tableau croisé dynamique sous l'onglet Création. Pour les dispositions, il suffit d'ouvrir les différents boutons et de choisir la disposition qui convient.

Excel, les Tableaux croisés dynamiques et Power Pivot

Toujours sous l'onglet Création, la galerie Style du tableau croisé dynamique propose un ensemble de styles prêts à être utilisés pour mettre en forme le TCD. Dans cette galerie, c'est vrai on a l'embarras du choix, mais il faudra prendre un style qui est à la fois simple et qui met en valeur les données nous voulons montrer à l'utilisateur.

Excel, les Tableaux croisés dynamiques et Power Pivot

Note :

Notez qu'on peut aussi appliquer une mise en forme conditionnelle à un TCD en passant par l'onglet Accueil, puis Mise en forme conditionnelle.

L'outil Power Pivot

Bien que Excel parait efficace comme logiciel de traitement de bases de données, il présente plusieurs faiblesses, notamment :

  • Maximum des lignes (enregistrements) traitées se limite à un peu plus que 1 million de lignes
  • On ne peut pas traiter dans un même TCD des données venant de plusieurs tableaux

Power Pivot est un outil très puissant qui est créé pour combler ces faiblesses. Il a fait sa première apparition dans la version 2010 de Excel puis devenu un complément intégré dans le logiciel depuis la version 2013.

Activer Power Pivot

A l'installation de Excel, le complément Power Pivot n'est pas activé par défaut. Pour le mettre en action, il faut aller le chercher dans la liste des Compléments COM de Excel. Les étapes listées ci-dessous ainsi que l'animation qui suit, montrent les pas à suivre pour activer le complément.

  1. Entrer dans les options de Excel
  2. Activer la section Compléments et tout en bas, dans la zone Gérer, choisir Complément COM dans la liste puis cliquer sur le bouton Atteindre
  3. Dans la fenêtre qui s'ouvre, cocher la case correspondant à Microsoft Power Pivot Excel et valider

Excel, les Tableaux croisés dynamiques et Power Pivot

Suite à cette opération, un nouveau onglet Power Pivot est ajouté au ruban et en même temps le bouton Accéder à la fenêtre Power Pivot est inséré dans le groupe Outils de données sous l'onglet Données.

Excel, les Tableaux croisés dynamiques et Power Pivot

Excel, les Tableaux croisés dynamiques et Power Pivot

Utilisation de Power Pivot

La raison d'être de Power Pivot est de traiter des données importées à partir de bases de données de grande taille (plusieurs millions d'enregistrements), et issues de sources différentes, dans le but de construire des TCD de synthèse, chose que Excel ne peut pas faire sans l'aide cet outil.

Pour mettre en œuvre Power Pivot, je vous propose de télécharger cette archive que vous dézipperez par la suite. Elle contient un fichier texte au format csv et un fichier Excel. Le fichier Excel Ventes.xlsx est formé d'une seule feuille nommée Ventes2019 qui liste toutes les ventes réalisées au cours de l'année et le fichier texte Clients.csv, quant à lui, contient la liste des clients ayant éffectué des achats.

Excel, les Tableaux croisés dynamiques et Power Pivot

Dans le fichier Excel, les informations sont stockées sous forme de lignes et des colonnes avec des en-têtes : Ordre, ID_Client, Date, Code, Désignation etc. Dans le fichier texte on voit des informations textuelles sur plusieurs lignes et séparées par un point-virgule. Sur les deux premières lignes, par exemple, sont inscrites les informations suivantes :

ID_Client ; Abrégé ; Nom_Client
1 ; SDC ; Software Development Center

TP 10 : Travail à faire

Cette activité a pour objectifs :

  1. Ouvrir Power Pivot
  2. Ajouter une table au modèle de données
  3. Importer une table à partir d'une source externe
  4. Créer des Mesures
  5. Appliquer le format adéquat aux différentes colonnes et mesures

Les fichiers de travail sont à télécharger ici.

Excel, les Tableaux croisés dynamiques et Power Pivot

Lancer Power Pivot

Le fichier Excel Ventes.xlsx téléchargé étant ouvert, pour accéder à la fenêtre de Power Pivot, on peut passer soit par l'onglet Power Pivot et cliquer sur le bouton Gérer

Excel, les Tableaux croisés dynamiques et Power Pivot

ou bien par l'onglet Données groupe Outils de données et cliquer sur le bouton Accéder à la fenêtre Power Pivot

Excel, les Tableaux croisés dynamiques et Power Pivot

Que l'on utilise l'une ou l'autre, ces deux façons de faire aboutissent à l'ouverture de la fenêtre de Power Pivot. C'est une fenêtre qui possède son propre ruban avec ses propres onglets et outils de travail. On dirait qu'il s'agit d'un autre logiciel, mais en fait c'est un complément qui reste attaché au fichier Excel.

Excel, les Tableaux croisés dynamiques et Power Pivot

En activant Power Pivot, un modèle de données est automatiquement créé, mais pour l'instant il est encore vide, comme le montre la fenêtre ouverte. Il va falloir, dans une première étape, y insérer des tables soit par ajout soit par importation à partir de sources externes.

Ajouter une table au modèle de données

L'opération d'ajout au modèle de données, se fait pour un tableau qui se trouve dans le fichier Excel sur lequel on travaille. Pour l'exécuter, on clique sur le tableau à ajouter, dans notre cas le tableau Ventes qui se trouve dans la feuille Ventes2019, puis sous l'onglet Power Pivot, on clique sur le bouton Ajouter au modèle de données.

Excel, les Tableaux croisés dynamiques et Power Pivot

La table est alors immédiatement insérée dans le modèle de données Power Pivot

Excel, les Tableaux croisés dynamiques et Power Pivot

Comme le montre explicitement son onglet dans le ruban, la table Ventes, ajoutée est une table liée ; de même, on remarque dans son onglet en bas de la fenêtre, l'existence d'une icône sous forme de chaîne signalant cette liaison. Le fait qu'une table ajoutée soit liée a pour conséquence que son contenu est mis à jour lorsque des changements sont faits dans le tableau source.

Excel, les Tableaux croisés dynamiques et Power Pivot

Importer des données

La deuxième source que nous voulons utiliser pour extraire des données est le fichier Clients.csv. Ces informations ne se trouvant pas dans le fichier Excel utilisé, on va les importer au lieu de les ajouter.

Le groupe Obtenir des données externes sous l'onglet Accueil de Power Pivot propose un ensemble de boutons permettant d'importer des données à partir de sources diverses. Nos données se trouvant dans un fichier texte (csv), on va ouvrir le bouton À partir d'autres sources, puis dans la fenêtre de l'assistant d'importation de table, qui s'ouvre, on sélectionne tout en bas de la liste Fichier texte et on clique sur Suivant

Excel, les Tableaux croisés dynamiques et Power Pivot

Dans la deuxième étape, on va renseigner le chemin du fichier à importer en suivant les étapes

  1. Cliquer sur le bouton Parcourir
  2. Dans la fenêtre de l'explorateur, indiquer le type de fichier désiré, dans le cas présent c'est le csv
  3. Naviguer dans l'explorateur jusqu'au fichier puis le sélectionner
  4. Cliquer sur le bouton Ouvrir

Excel, les Tableaux croisés dynamiques et Power Pivot

Avant de cliquer sur Terminer, il faut d'abord choisir le caractère de séparation des colonnes, le point-virgule dans notre cas, et cocher la case Utiliser la première ligne comme en-tête des colonnes

Excel, les Tableaux croisés dynamiques et Power Pivot

On obtient alors la fenêtre confirmant la réussite de l'opération, on clique sur le bouton Fermer pour voir le résultat.

Excel, les Tableaux croisés dynamiques et Power Pivot

La table Clients s'est rajoutée au modèle de données auprès de la table Ventes. Le modèle de données Power Pivot contient désormais les deux tables nécessaires pour l'extraction des informations demandées. La capture ci-dessous montre les onglets des deux tables dans le mode d'affichage Vue de données.

Excel, les Tableaux croisés dynamiques et Power Pivot

Si on bascule vers le mode d'affichage Vue de diagramme, les tables sont visualisées sous forme de diagrammes contenant seulement les en-têtes des colonnes.

Excel, les Tableaux croisés dynamiques et Power Pivot

On peut aussi utiliser les deux boutons situés en bas à droite de la fenêtre Power Pivot, pour basculer entre les modes d'affichage Vue de données et Vue de diagramme.

Excel, les Tableaux croisés dynamiques et Power Pivot

Faire des modifications et des calculs

Avant de commencer d'extraire les données, on doit d'abord effectuer quelques modifications et calculs notamment sur la table Ventes.

Masquer des colonnes

Si des colonnes, dans le modèle de données, semblent être superflues et n'ont pas une grande importance pour l'extraction comme c'est le cas ici pour les colonnes Ordre et Code, on peut les masquer.

Pour masquer une colonne, on clique droit sur son en-tête pour faire apparaitre le menu contextuel puis on clique sur la ligne Masquer dans les outils clients.

Excel, les Tableaux croisés dynamiques et Power Pivot

Cette opération a pour effet de masquer les colonnes en question et de les rendre grisées comme le montre la capture suivante.

Excel, les Tableaux croisés dynamiques et Power Pivot

Ces colonnes sont bien masquées logiquement mais visuellement sont encore là. Le bouton Afficher les éléments masqués dans le groupe Affichage de l'onglet Accueil du modèle, permet de rendre ces éléments visibles ou bien invisibles.

Excel, les Tableaux croisés dynamiques et Power Pivot

Ajouter des Mesures

Dans Power Pivot, une Mesure est l'équivalent de Champ calculé dans Excel. Elle permet de déduire, à l'aide d'expressions et de fonctions, une information à partir des données déjà existantes. Pour ce TP, les informations que l'on doit calculer pour dresser le tableau d'analyse sont : le Taux de retour et le CA pour chaque produit.

On peut créer une mesure dans une nouvelle colonne ou bien dans la zone de calcul en utilisant des expressions et/ou des fonctions DAX (Data Analysis eXpressions).

Excel, les Tableaux croisés dynamiques et Power Pivot

Le DAX est un langage dédié à l'analyse et à la modélisation des données. il est basé sur des formules et des fonctions très puissantes ce qui en fait un vrai couteau suisse pour le traitement des grandes bases de données.

Mesure dans une colonne ajoutée

La première chose à calculer c'est le Taux de retour ou bien le pourcentage de retour, c'est une donnée qu'on va calculer pour chacune des lignes de la table. Pour ce faire, on doit diviser, pour chaque ligne, la quantité retournée par la quantité achetée, c'est à dire la colonne Retour par la colonne Quantité dans la table Ventes. Le résultat, on va le mettre dans une nouvelle colonne nommée TauxDeRetour.

Pour commencer, on clique dans une cellule de la colonne Ajouter une colonne puis on écrit l'expression :

TauxDeRetour := [Retour] / [Quantité]

Excel, les Tableaux croisés dynamiques et Power Pivot

Quelques remarques à faire :

  • Le nom écrit en bleu dans la syntaxe écrite ci-dessus, doit obligatoirement être suivi de deux points puis égal (:=). C'est ce nom qui formera l'en-tête de la colonne ajoutée
  • À la différence de Excel, l'expression saisie n'apparait pas dans la cellule sélectionnée, mais uniquement dans la barre de formule.
  • Les en-têtes des colonnes sont écrits entre crochets, pour indiquer que ce sont des champs

Astuce :

Au cours de la saisie de l'expression, au lieu de taper les en-têtes des colonnes dans l'expression, on peut cliquer sur la colonne concernée pour effectuer une saisie automatique.

Avantages de cette façon de faire :

  • Les crochets sont automatiquement ajoutés
  • Éviter les erreurs de saisie

Résultat : L'en-tête de la colonne bien renseigné et des valeurs calculées pour toutes les lignes de la colonne.

Excel, les Tableaux croisés dynamiques et Power Pivot

Le format des valeurs de la colonne TauxDeRetour n'est pas le bon, on va s'en occuper dans le paragraphe qui suit.

Créer une mesure dans la zone de calcul

Dans la zone de calcul, on va calculer le chiffre d'affaire annuel CA. Cette mesure, on va la calculer de deux manières différentes en utilisant les fonctions SUM() et SUMX(), ce sont toutes les deux, des fonctions d'agrégat.

Une fonction d'agrégat est une fonction qui renvoie un seul résultat à partir de plusieurs données d'entrée comme SUM(), MAX(), AVERAGE().

Utilisation de la fonction SUM()

Puisque la table contient déjà la colonne PrixTotal qui contient le prix total pour chacune des opérations d'achat (PrixTotal = Quantité * Prix), il suffit d'additionner les valeurs de cette colonne pour obtenir le chiffre d'affaire pour toute l'année. La fonction SUM() convient bien pour ce calcul. Elle requiert un seul argument c'est le nom de la colonne à additionner. Sa syntaxe est la suivante :

= SUM([colonne à additionner])

Pour employer cette fonction, dans la zone de calcul, on clique dans une cellule, n'importe laquelle, puis on écrit la fonction en respectant la syntaxe. Il est préférable d'attribuer un nom significatif à chaque mesure qu'on crée.

Par exemple ici on écrit : CAA:= SUM([PrixTotal]) (CAA pour Chiffre d'Affaire Annuel)

Excel, les Tableaux croisés dynamiques et Power Pivot

Utilisation de la fonction SUMX()

La fonction SUMX() permet de faire ce calcul sans avoir besoin de cette colonne intermédiaire PrixTotal. SUMX calcule la somme d'une expression pour toutes les lignes d'une table. Cette expression peut être une multiplication, une division, une addition ou autre, entre deux ou plusieurs colonnes. On l'écrit selon la syntaxe suivante :

= SUMX(Table;Expression)

La syntaxe montre que la fonction requiert deux arguments : tout d'abord le nom de la table puis l'expression de calcul qu'on va appliquer aux colonnes contribuantes.

Dans le cas présent, on veut calculer la somme des multiplications de la colonne Quantité par la colonne Prix, pour toutes les lignes de la table Ventes.

CAAX:= SUMX(Ventes;[Quantité]*[Prix])

Excel, les Tableaux croisés dynamiques et Power Pivot

Excel, les Tableaux croisés dynamiques et Power Pivot

Les deux fonctions ayant donné exactement le même résultat, il convient donc de supprimer l'un des deux pour éviter la redondance. Gardons alors la mesure CAAX qui a été calculée par la fonction SUMX, dans ce cas on peut se passer de la colonne PrixTotal.

Pour supprimer une mesure dans la zone de calcul, il suffit de sélectionner sa cellule et d'appuyer sur la touche Suppr du clavier, ou bien de faire un clic droit puis de choisir la ligne Supprimer dans la liste, après quoi il faudra confirmer la suppression.

Modifier le format de données

Maintenant que les informations requises par le TP sont ajoutées à la table (le taux de retour et le CAA), il est temps de s'occuper du format des différentes colonnes.

À ne pas oublier que le changement du format n'affecte que l'affichage des nombres et des Dates, il ne touche pas à la valeur stockée.

Après sélection de la colonne concernée, le changement de format se fait avec les outils du groupe Mise en forme sous l'onglet Accueil.

Excel, les Tableaux croisés dynamiques et Power Pivot

Modifier le format des dates

La colonne Date affiche des dates au format complet Jour/Mois/Année Heure:Minute:Seconde. Comme on peut le remarquer, l'affichage des heures, des minutes et des secondes n'est pas significatif pour cette colonne, il vaudrait mieux ne pas les afficher et choisir un format plus court. Pour ce faire, cette colonne étant toujours sélectionnée, on ouvre la liste Format du groupe Mise en forme et on sélectionne un format convenable.

Excel, les Tableaux croisés dynamiques et Power Pivot

Modifier le format des nombres

Les colonnes Prix et PrixTotal indiquent des sommes d'argent. Pour bien exprimer cette information il faut lui attribuer un format avec un symbole monétaire. Le format Devise serait un bon choix puisqu'il affiche les nombres avec deux décimales avec le symbole monétaire paramétré dans le système d'exploitation.

Excel, les Tableaux croisés dynamiques et Power Pivot

Si le format utilisé par défaut n'est pas le bon, on peut le personnaliser en ouvrant le bouton $ et chercher le format adéquat soit dans la première liste qui s'ouvre, soit en allant plus loin dans Formats supplémentaires.

Excel, les Tableaux croisés dynamiques et Power Pivot

Parfois, si la devise est implicitement connue, on peut se passer du symbole monétaire et se contenter du format Décimal avec ou sans chiffres après la virgule.

Excel, les Tableaux croisés dynamiques et Power Pivot

Pour faciliter la lecture des grands nombres, on utilise un format avec séparateur des milliers. Dans les systèmes français, belge et suisse on utilise une espace comme séparateur. Dans le système américain c'est une virgule qui est utilisée, mais il semble que c'est la séparation par une espace qui est conforme avec la convention internationale. Sur un ordinateur ce séparateur dépend de la configuration du système d'exploitation et aussi des options de Excel.

Excel, les Tableaux croisés dynamiques et Power Pivot

N'oublions pas d'appliquer le format Décimal avec séparateur des milliers à la mesure CAA dans la zone de calcul

Excel, les Tableaux croisés dynamiques et Power Pivot

Lorsqu'on applique le format Pourcentage à une colonne, tous les nombres de cette colonne sont multipliés par 100 et sont affichés avec deux décimales suivies du symbole %. Le nombre de décimales est modifiable par les boutons Ajouter décimales et Réduire décimales du groupe Mise en forme.

Excel, les Tableaux croisés dynamiques et Power Pivot

TP 11 : Travail à faire

Le but de ce TP est de vous exercer.

Sur le modèle de données créé dans le TP 10, exécutez les tâches suivantes :

  1. Ajoutez la colonne TauxTVA et donnez-lui la valeur 20,60%
  2. Ajoutez la colonne TotalTTC qui est égal à PrixTotal + PrixTotal * TauxTVA
  3. Dans le zone de calcul, créez la mesure CA_Global qui vaut la Somme de la colonne TotalTTC
  4. Appliquez le format convenable aux mesures ajoutées

Excel, les Tableaux croisés dynamiques et Power Pivot

Gestion des relations

En examinant les diagrammes des deux tables en mode diagramme, on remarque qu'elles contiennent toutes les deux le champ ID_Client en commun. Dans la table Clients on dit qu'il est une clé primaire car il permet de définir un client sans ambiguïté, par conséquent il doit être sans doublons c'est à dire qu'on ne doit pas trouver un même ID_Client pour deux clients différents. Sur la table Ventes la valeur de ce champ peut paraitre plusieurs fois dans la mesure où un client peut effectuer plusieurs opérations d'achat. Sur cette table on dit que le champ ID_Client forme une clé étrangère, son rôle est de faire référence au client qui a effectué une opération d'achat donnée.

Excel, les Tableaux croisés dynamiques et Power Pivot

C'est à travers ces champs en communs que les relations seront créées entre les tables. Ces relations constitueront des "passerelles" et permettront à Power Pivot d'aller chercher les informations demandées dans les différentes tables.

Important

Les tables à relier doivent obligatoirement avoir un champ en commun. Si ce champ n'existe pas, il faudra le créer dans la table où il manque. Les données de ce champ doivent être du même type des deux côtés de la relation.

Créer une relation entre tables

Une première méthode pour créer une relation entre deux tables consiste à faire, dans le mode Vue de diagramme, un simple cliquer-glisser du champ en commun d'une table vers l'autre comme le montre l'animation suivante.

Excel, les Tableaux croisés dynamiques et Power Pivot

La deuxième façon de créer la relation se fait par le biais du bouton Créer une relation dans le groupe Relation sous l'onglet Conception.

  1. Sous l'onglet Conception, cliquer sur le bouton Créer une relation
  2. Dans la boite de dialogue qui s'ouvre, choisir la première table dans la liste Table 1 puis la deuxième table dans la liste Table 2
  3. Dans les zones Colonnes de chacune des tables, sélectionner le champ en commun
  4. Valider la création de la relation

Excel, les Tableaux croisés dynamiques et Power Pivot

La relation est symbolisée par un trait avec dans un bout le chiffre 1, c'est le côté où ce champ n'apparait qu'une seule fois (clé primaire) et de l'autre une étoile * c'est le côté où le champ peut apparaitre plusieurs fois (clé étrangère). Au milieu du trait on voit une flèche indiquant le sens de la relation. Ce type de relation est appelé 1 à plusieurs.

Excel, les Tableaux croisés dynamiques et Power Pivot

Modifier une relation

La modification d'une relation se fait à travers la fenêtre Modifier la relation dans laquelle on peut redéfinir les tables participantes à la relation ainsi que les champs à relier.

Excel, les Tableaux croisés dynamiques et Power Pivot

Cette fenêtre est accessible de plusieurs façons :

  • Par un double-clic sur le trait de la relation
  • Par un clic droit sur le trait de la relation puis choisir la ligne Modifier la relation dans le menu qui apparait
  • Excel, les Tableaux croisés dynamiques et Power Pivot

  • En passant par le bouton Gérer les relations
    1. Cliquer sur le bouton Gérer les relations du groupe Relations sous l'onglet Conception
    2. Dans la fenêtre Gérer les relations, sélectionner la relation à modifier
    3. Cliquer sur le bouton Modifier
  • Excel, les Tableaux croisés dynamiques et Power Pivot

Supprimer une relation

Le clic droit sur le trait de la relation et le bouton Gérer les relations utilisés pour modifier une relation, servent aussi à la suppression, il faut choisir la commande Supprimer au lieu de la commande Modifier.

Un moyen encore plus simple pour supprimer une relation consiste à sélectionner la relation, en cliquant sur son trait, puis appuyer sur la touche Suppr du clavier, un message de confirmation apparait, on le valide et c'est tout.

Excel, les Tableaux croisés dynamiques et Power Pivot

Créer le TCD à partir de Power Pivot

La création d'un TCD par Power Pivot se fait exactement de la même manière qu'avec Excel mais avec une différence de taille : Power Pivot est capable d'extraire des informations à partir de plusieurs tables liées.

TP 12 : Travail à faire

L'objectif de cette activité est d'obtenir un TCD qui donne une synthèse affichant les noms des clients avec chacun le nombre de chaque produit acheté, le Nombre de retour et le CA_Global. Ces différentes informations seront extraites à partir des deux Tables Ventes et Clients.

Si ce n'est pas encore fait, téléchargez les fichiers Ventes.xlsx et Clients.csv à ici.

Excel, les Tableaux croisés dynamiques et Power Pivot

Le bouton Tableau croisé dynamique sous l'onglet Accueil de la fenêtre Power Pivot, contient plusieurs commandes d'insertion d'objets croisés dynamiques (Tableaux et graphiques). Pour insérer un TCD, on sélectionne la première ligne de la liste de ce bouton comme le montre la capture ci-dessous.

Excel, les Tableaux croisés dynamiques et Power Pivot

Cette commande a pour effet d'insérer un TCD dans une nouvelle feuille ou dans une feuille existante de Excel. Après avoir défini l'endroit d'insertion, on obtient dans le volet Champs de tableau croisé dynamique, toutes les tables ajoutées au modèle de données avec leurs champs.

Excel, les Tableaux croisés dynamiques et Power Pivot

Il ne reste plus que faire glisser les champs désirés vers les différentes zones pour remplir le TCD. Les choses suivantes sont à remarquer.

  • Le champ Non_Client est extrait à partir de la table Clients
  • Les champs Désignation, Quantité, Retour et CA_Global sont extraits à partir de la table Ventes.
  • Dans la liste des champs, le champ CA_Global est précédé de l'expression fx, c'est une Mesure (un champ calculé)

Excel, les Tableaux croisés dynamiques et Power Pivot

Recevez ce cours au format PDF en envoyant un message ici

MOS Prepa