Création de tableaux croisés dynamiques (PivotTables)

Description

Sommaire (Pivot Table) est un puissant outil de calcul, de synthèse et d'analyse de données qui vous permet de voir des comparaisons, des modèles et des tendances dans vos données.

Imaginez que vous ayez besoin de rédiger un court rapport analytique pour prendre une décision à partir d'un grand tableau contenant de nombreuses informations différentes. Supposons que vous disposiez de données affichant des informations sur le portefeuille de prêts de la banque, où des informations détaillées sur chaque emprunteur sont enregistrées.

Un exemple de problème

Supposons que le tableau suivant soit chargé depuis la base de données dans Excel pour analyser le portefeuille de prêts :

Dans celui-ci, chaque ligne individuelle contient des informations complètes sur un contrat de crédit :

• nom de l'emprunteur ;
• numéro d'agrément;
• date d'émission et date de remboursement du prêt;
• type de prêt ;
• montant du prêt

Naturellement, si les directeurs commerciaux connaissent leur métier et travaillent dur, alors chaque jour plusieurs dizaines de lignes seront ajoutées à ce tableau, et d'ici la fin, par exemple, d'un an ou au moins d'un trimestre, la taille du tableau deviendra monstrueux.

Cependant, une horreur encore plus grande vous obligera à créer des rapports basés sur ces données. Combien de prêts ont été émis chaque mois ? Quelle est la taille du portefeuille de prêts en termes de quantité et de montant ? Les dix principaux emprunteurs ? etc.

Les réponses à toutes les questions peuvent être obtenues plus facilement que vous ne le pensez. Environ trois minutes. Avec l'un des outils les plus étonnants Microsoft Excel — Tableaux récapitulatifs .

Allons-y...

Préparation des données

Tenez compte des conseils de préparation des données suivants avant de créer des tableaux croisés dynamiques :

• utilisez des données tabulaires propres pour de meilleurs résultats ;
• placez les données dans des colonnes et non dans des lignes ;
• assurez-vous que toutes les colonnes ont des en-têtes avec une ligne de légendes uniques non vides pour chaque colonne. Évitez les lignes d'en-tête en double ou les cellules fusionnées ;
• formater les données comme "table intelligente Excel" (cliquez n'importe où sur vos données, puis sélectionnez "Insérer" - "Tableau" ( Insert - Table ) dans le menu du ruban (ribbon);
• si vous avez des données complexes ou imbriquées (comme un tableau croisé), utilisez Power Query pour les convertir (par exemple, vous pouvez utiliser une macro pour annuler le résumé des données ) afin qu'ils soient organisés en colonnes avec une seule ligne d'en-tête.

Créer un tableau croisé dynamique

Sélectionnez l'intégralité de notre tableau de données et cliquez sur le menu Insérer - Tableau récapitulatif ( Insert - PivotTable ).

Cette boîte de dialogue apparaîtra, dans laquelle nous pouvons choisir où placer notre tableau croisé dynamique et ajuster la plage de données.

Par défaut, Excel choisit de placer le tableau croisé dynamique sur une nouvelle feuille (New Worksheet) , mais on peut, si nécessaire, sélectionner manuellement la feuille actuelle ou toute autre feuille du livre (Existing Worksheet) . Il est préférable de choisir une nouvelle feuille pour cela - il n'y a alors aucun risque que le tableau récapitulatif "se chevauche" avec la liste d'origine et que nous obtenions un tas de liens cycliques. appuie sur le bouton OK et passons à la partie la plus intéressante : l'étape de construction de notre rapport.

Travailler avec la mise en page

Ce que vous verrez après avoir appuyé sur le bouton OK appelé une mise en page (layout) sommaire:

Il est facile de travailler avec - vous devez faire glisser les noms des colonnes (champs) depuis la fenêtre avec la souris Liste des champs du tableau récapitulatif (PivotTable Field List) dans la région:

• lignes (Row Labels);
• colonnes (Column Labels);
• filtres (Report Filter);
• données de mise en page (Values).

Le seul bémol est de le faire plus précisément, ne manquez pas ! Allons-y...

Au cours du processus de glissement, le tableau consolidé commencera à changer d'apparence sous vos yeux, affichant les données dont vous avez besoin. Après avoir coché les quatre champs dont nous avons besoin dans la liste, vous devriez obtenir un rapport presque prêt. Il ne reste plus qu'à bien le formater.

Formatage du tableau croisé dynamique

Sélectionnez la mise en forme du tableau récapitulatif dans le menu PivotTables Tools - Design :

Mais on obtient le look suivant :

Ce n'est pas si compliqué, n'est-ce pas ?

Désavantages

Le seul inconvénient des tableaux récapitulatifs est le manque de mise à jour automatique (recalcul) lorsque les données de la liste d'origine changent. Pour effectuer un tel calcul, il faut cliquer sur le tableau récapitulatif avec le bouton droit de la souris et sélectionner la commande dans le menu contextuel Mise à jour (Refresh) .

Articles sur le sujet :

• Tableaux non pivotants (Unpivot)
• PowerPivot - une application pour étendre les capacités des tableaux croisés dynamiques