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

Introduction

Dans le chapitre précédent, nous avons conçu des tableaux croisés dynamiques dans lesquels deux champs ont été croisés. Nous allons maintenant aborder la conception de tableaux croisés avancés. Ce chapitre va vous permettre de consolider les acquis du chapitre précédent et aussi d’acquérir les techniques de personnalisation de vos tableaux croisés dynamiques.

Connaissances nécessaires

Conception et mise en forme des tableaux croisés dynamiques

Nouveaux acquis

Filtrage des informations

Gestion de l’affichage/masquage de certaines données

Appliquer, modifier, créer des styles de mise en forme

Appliquer une mise en forme conditionnelle

Utiliser les segments

Insérer une chronologie

Croiser plus de deux informations

Les données sources

Afin de disposer d’informations variées, nous allons utiliser le fichier LocationsMateriels.xlsx téléchargeable à partir de la page Informations générales. Les données présentes dans la feuille Locations sont telles que ci-après. La dernière ligne saisie est la ligne 305.

Le tableau représente la liste des locations effectuées par une entreprise de location de matériels de réception.

Les factures sont émises selon les règles présentées ci-après :

Particulier : facturation le jour du retour des matériels
Autres clients : facturation le dernier jour du mois

Si le matériel rendu nécessite une réparation ou une remise en état, les frais de réparation peuvent être facturés au client. Dans ce cas, le montant des frais est rajouté au montant de la location pour obtenir le montant total facturé. Tous les montants affichés sont hors taxes.

Les types de matériels loués sont :

ESTRADES
PLANCHERS
TABLES
TENTES

Les types de clients sont :

PARTICULIER
TRAITEUR
ENTREPRISE
CONFRERE
HOTEL-RESTAURANT
ASSOCIATION

Les commerciaux sont :

SANDRINE
THIERRY
NICOLAS

Conception du tableau croisé dynamique

Notre premier objectif est de calculer le montant total des locations par type de matériel et par type de client. Nous souhaitons pouvoir affiner notre statistique en visualisant aussi (par type de matériel et par type de client) le chiffre d’affaires réalisé par commercial.

Ouvrez le classeur LocationMateriels.xlsx.

Positionnez le curseur dans une cellule de la source, A1 par exemple.

Dans l’onglet Insertion - groupe Tableaux, cliquez sur le bouton Tableau croisé dynamique.

La fenêtre Créer un tableau croisé dynamique apparaît :

Cliquez sur OK.

Faites glisser le champ TYPE CLIENT dans la zone LIGNES.

Faites glisser le champ TYPE MATERIEL dans la zone COLONNES puis procédez de même avec le champ COMMERCIAL pour l’amener sous TYPE CLIENT dans la zone LIGNES.

Enfin, placez le champ TOTAL FACTURE dans la zone ∑ VALEURS.

Au fur et à mesure que vous placez les champs dans les différentes zones, le tableau croisé dynamique se construit...

Mettre en forme un tableau croisé

Modifier la disposition du tableau croisé dynamique

Nous avons déjà abordé au chapitre précédent les techniques simples de mise en forme des rapports de tableaux croisés dynamiques. Vous allez maintenant approfondir la personnalisation de votre rapport.

Vous aurez peut-être besoin parfois de modifier l’affichage des champs dans le tableau croisé. Actuellement notre tableau affiche sous chaque type de clients les commerciaux. Une autre présentation envisageable est de placer les commerciaux sous les familles de produits.

Le risque est ici d’obtenir une lisibilité du tableau moins aisée, mais nous pouvons tout de même essayer pour tester la manipulation.

Cliquez dans le tableau croisé.

Dans la liste des champs, déplacez le champ COMMERCIAL dans la zone COLONNES sous le champ TYPE MATERIEL :

Votre tableau comporte maintenant plus de colonnes que de lignes, ce qui n’améliore pas la lisibilité dans notre cas.

L’écran ci-dessous présente les premières colonnes de notre tableau croisé.

Modifiez la disposition des en-têtes de colonnes : effectuez un clic droit dans le tableau, puis cliquez sur Options du tableau croisé dynamique. Dans l’onglet Disposition et mise en forme, cochez Fusionner et centrer les cellules avec les étiquettes.

Validez en cliquant sur OK.

Le tableau croisé dynamique est réorganisé. Lorsque tout est développé, les types de matériels sont centrés par rapport aux commerciaux.

Afficher/masquer certaines informations

En fonction du type de présentation que vous souhaitez obtenir, il est possible de masquer certaines composantes du tableau croisé dynamique, les boutons d’agrandissement et de réduction ainsi que les en-têtes de champ.

Masquer les boutons de développement et de réduction :

Cliquez dans une cellule du tableau croisé.

Onglet Outils de tableau croisé dynamique - Analyse - groupe Afficher, cliquez sur le bouton Boutons +/-.

Les boutons de développement et de réduction ont disparu.

Pour masquer/afficher le détail d’un type de matériel, il suffit maintenant de double cliquer sur le nom du matériel.

Masquer...

Les segments : rendre vos tableaux croisés interactifs

Intérêts

Les segments sont des éléments visuels qui permettent de filtrer les données des tableaux croisés dynamiques de façon interactive et intuitive. En insérant un segment, vous pourrez utiliser des boutons pour filtrer et segmenter rapidement des données afin d’afficher uniquement les informations souhaitées. En cumulant les segments, vous pourrez affiner vos analyses.

Technique

Nous allons utiliser notre dernier tableau croisé pour mettre en pratique la technique des segments.

Cliquez dans le tableau croisé.

Dans l’onglet Outils de tableau croisé dynamique - Analyse - groupe Filtrer (Trier et filtrer sous Excel 2010), cliquez sur le bouton Insérer un segment.

La fenêtre Insérer des segments est affichée.

Cochez uniquement TYPE MATERIEL puis validez par OK.

Le segment défini sur le type de matériel apparaît à côté du tableau croisé. Par défaut toutes les rubriques sont sélectionnées et le tableau croisé n’est pas filtré.

Appliquer un filtre au tableau croisé est très simple, il suffit de sélectionner l’élément souhaité.

Pour obtenir les statistiques sur les locations de tentes, cliquez sur le bouton correspondant.

Le tableau croisé...

Les chronologies : filtrer vos tableaux croisés par période

Cette fonctionnalité est disponible depuis la version 2013 d’Excel.

Intérêts

Plutôt que de concevoir des filtres multiples sur vos champs dates, Excel vous permet d’ajouter à votre tableau croisé une plage chronologique dont les éléments représentent chacun une unité de période. Les éléments de la chronologie sont représentés sur un axe horizontal.