Préparez votre certification
Microsoft Office Specialist

Les Tableaux croisés dynamiques et Power Pivot

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.

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

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 :

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

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.

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.

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.

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

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

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.

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

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.

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.

• La cellule Mois est munie d'un bouton flèche pour effectuer des filtres sur les mois à afficher

• On peut faire de même pour les catégories pour effectuer un filtre

Effectuer des affichages détaillés

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

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.

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

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.

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

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.

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.

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.

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

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

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 :

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.

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. Dans la boite de dialogue Insertion d'un champ calculé, taper un nom significatif pour le nouveau champ dans la zone Nom
5. Dans la zone Formule, écrire l'expression de calcul, en utilisant les noms des champs présents dans la liste Champs.
6. Valider en cliquant sur OK

7. Le nouveau champ apparait dans le tableau et dans la liste des champs

8. Se rendre dans les paramètres du nouveau champ pour choisir le format Pourcentage

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

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.

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.

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.

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.

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.

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

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.

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

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.

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.

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.

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.

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.

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

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

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.

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.

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

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

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.

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

• 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)

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

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.

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.

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

Grouper les Nombres

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

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

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.

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

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.

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.

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.

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.

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.

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

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

• Clic droit sur une cellule du champ puis sélectionner la ligne Dissocier

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.

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.

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

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.

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.

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

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

ou bien par l'onglet Données groupe Outils de données et cliquer sur le bouton Accéder à la fenêtre 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.

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.

La table est alors immédiatement insérée dans le modèle de données 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.

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

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

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

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.

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.

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.

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.

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.

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

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.

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

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é]

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

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.

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)

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])

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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



• 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

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.

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.

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.

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.

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é)

Recevez ce cours au format PDF en envoyant un message ici