Name: Analysez efficacement vos données à l'aide des tableaux croisés dynamiques (3e édition)
Rating: 5 (1 reviews)

Introduction

Les notions que nous allons aborder rapidement dans ce chapitre sont le préalable à la conception de rapports de tableaux croisés dynamiques. Lorsque vous maîtriserez ces techniques, vous serez autonome dans la gestion d’une base de données Excel.

Connaissances nécessaires

Manipulations de base d’Excel

Nouveaux acquis

Filtrer une base de données

Le mode plan

Les sous-totaux

Utiliser les filtres

Préliminaires

Un tableau croisé dynamique représente une synthèse faisant intervenir plusieurs champs dont les informations sont croisées afin d’obtenir des statistiques. Vous n’aurez peut-être pas toujours besoin dans votre fonction de créer des tableaux de synthèse complexes à partir de votre base de données. Dans certains cas, vous souhaiterez seulement filtrer des informations afin d’obtenir une liste d’enregistrements répondant à un ou plusieurs critères ou bien obtenir les totaux d’une donnée numérique en fonction d’un paramètre. Nous vous présentons ci-dessous, quelques exemples d’informations qu’il est possible d’obtenir simplement à partir de bases de données Excel diverses.

Liste des factures pour une famille de clients.
Liste des employés ayant plus de 10 ans d’ancienneté et appartenant à un service.
Montant total des ventes d’une famille de produits.
Liste des abonnés dont le nom commence par la lettre L.

Pour appréhender ces notions, vous allez travailler à partir du fichier VentesWEB.xlsx téléchargeable à partir de la page Informations générales.

Ce classeur intègre la liste de toutes les ventes réalisées par un site de commerce électronique. Le responsable commercial du secteur "Grand sud est (GSE)" de la France a extrait du fichier des ventes globales les ventes relatives à ce secteur.

Les données présentes dans la feuille ListeVentes sont telles que ci-dessous.

Certaines lignes ont été placées manuellement en bleu par le responsable commercial.

Les filtres

Un filtre est défini par un critère appliqué à un champ (colonne). Il existe trois types de filtres :

Par valeur de liste : Excel recense automatiquement les valeurs présentes dans une colonne puis vous les présente dans une liste sans doublons. Il vous suffit de cocher la ou les valeur(s) souhaitée(s) pour n’afficher que les lignes dont les valeurs du champ sont égales aux éléments sélectionnés.
Par couleurs : cette option permet de définir des filtres...

Filtres avancés

Définitions

Paramétrer un filtre avancé permet de filtrer les données vers un autre emplacement que la liste en cours.

Pour effectuer cette opération, vous devez disposer de deux plages distinctes : une zone de critères et une zone d’extraction. Ces deux zones doivent comporter sur leur première ligne les champs à filtrer et/ou à afficher. Il n’est pas du tout nécessaire qu’elles comportent les mêmes champs. Par contre il n’est pas possible de copier les données filtrées vers une autre feuille que la feuille active.

Action

Pour l’exemple, nous allons considérer que vous devez faire parvenir au responsable de secteur (AUVERGNE / RHONE-ALPES) la liste des ventes relatives à son secteur dont le règlement a été effectué en 4 fois sans frais.

Notre zone de critères ne doit donc comporter que deux champs (REGION CLIENT et TYPE REGLEMENT) et la zone d’extraction les sept champs souhaités. Ces deux zones ont été préparées dans le classeur VentesWEB.xlsx (feuille ListeVentes) :

Tout d’abord, paramétrez les éléments de la zone de critères.

Cliquez ensuite sur une cellule quelconque dans la liste des données.

Onglet Données - groupe Trier et filtrer - Avancé.

Cochez l’option...

Créer des sous-totaux

Définitions

Un sous-total est un calcul de synthèse qu’Excel va insérer sur une nouvelle ligne à chaque changement de la valeur d’un champ trié.
Le mode Plan permet de regrouper/masquer et/ou afficher de manière hiérarchisée des enregistrements ayant des caractéristiques communes. Un plan peut comporter huit niveaux au maximum.
Les symboles du plan présentent les niveaux hiérarchisés. Plus le numéro est élevé, plus le niveau de détail affiché va être important.

Démarche de création

Effectuez un tri sur le champ sur lequel vous souhaitez effectuer la synthèse. Par exemple, si vous souhaitez connaître le montant total des ventes par région, triez les régions par ordre alphabétique.

Insérez les sous-totaux.

Éventuellement masquez les lignes de détails.