Les tableaux croisés dynamiques avec Excel E-Book

Rémy Lentzner

• Herausgeber: Éditions Remylent
• Kategorie: Fachliteratur
• Sprache: Französisch
• Veröffentlichungsjahr: 2018

Seitenzahl: 111

Beschreibung

Découvrez de multiples astuces pour optimiser l'analyse de vos données

Grâce à Excel, vous pouvez à tout instant calculer, filtrer et organiser vos informations de mille manières. Le tableau croisé dynamique permet de produire des statistiques et d’analyser plus finement des données. Accessible à tous, ce manuel vous donne des clefs pour modifier et manipuler cet outil. Vous y trouverez aussi de multiples astuces pour améliorer vos analyses. Cet ouvrage s’adresse aux curieux qui cherchent à repousser leurs limites en bonne compagnie.  Les exercices proposés dans ce manuel peuvent être effectués sur toutes les versions d’Excel.

Un guide pratique essentiel pour perfectionner votre maîtrise d'Excel, outil indispensable de votre travail quotidien

À PROPOS DE L'AUTEUR

Rémy Lentzner est formateur en informatique depuis 1985. Spécialisé dans la maîtrise des outils bureautiques, il accompagne les entreprises dans la formation professionnelle de leurs salariés. Autodidacte, il a une quinzaine d'ouvrages informatiques à son actif.

Leseprobe

REMY LENTZNER

LES TABLEAUX CROISéS DYNAMIQUES AVEC EXCEL

Collection : Informatique du quotidien

EDITIONS REMYLENT, Paris, 1ère édition, 2016.

R.C.S. 399 397 892 Paris

25 rue de la Tour d’Auvergne - 75009 Paris

[email protected]

www.REMYLENT.FR

Excel est une marque déposée de la société Microsoft

ISBN EPUB : 978-2-955-7694-23

Le Code de la propriété intellectuelle interdit les copies ou reproductions destinées à une utilisation collective. Toute représentation ou reproduction intégrale ou partielle faite par quelque procédé que ce soit, sans le consentement de l’auteur ou de ses ayant droit ou ayant cause, est illicite et constitue une contrefaçon, aux termes des articles L.335-2 et suivants du Code de la propriété intellectuelle.

Aux personnes qui aiment jongler avec les détails

INTRODUCTION

Grâce à Excel, vous pouvez à tout instant calculer, filtrer et organiser vos informations de mille manières. Le tableau croisé dynamique (nous l’appellerons TCD) permet de produire des statistiques et d’analyser plus finement des données.

Toute personne qui manipule Excel est capable de créer des tableaux croisés dynamiques : un(e) commercial(e) peut analyser des données marketing, un(e) ingénieur(e) peut gérer des informations de résistance de matériaux. Il n’y pas de limite dans le choix des informations à analyser.

Excel est un produit populaire qui a fait l’objet de plusieurs versions (2007, 2010, 2013 et 2016). Chaque version change environ tous les deux ans et demi et apporte son lot d’améliorations et de nouveautés. Ces changements interviennent aussi au sein des TCD et cette frénésie de nouvelles versions est une des raisons pour lesquelles Microsoft propose maintenant d’acheter le logiciel à l’année. Au moment de l’écriture de ce livre, la dernière version est celle de 2016 et permet plus facilement de croiser des informations en provenance de sources externes diversifiées grâce à un nouveau concept appelé modèle de données. Ce livre est issu d’une longue expérience de cours en entreprise sur le sujet des tableaux croisés dynamiques. Il s’intéresse aux détails, aux options diverses et aux dispositifs plus ou moins cachés qui facilitent le travail quotidien.

La plupart des exemples de cet ouvrage sont reproductibles avec toutes les versions d’Excel en français, mais à partir d’Excel 2007. Ce livre est structuré en deux parties.

La partie 1 s’intéresse à la mise en place et à la manipulation du tableau croisé dynamique créé à partir de données issues d’une seule feuille de calcul. Vous y apprendrez comment faire des calculs simples et complexes, la manière de réaliser des pourcentages automatiques, les techniques de comptage de valeurs identiques et la façon de présenter les résultats la plus précise possible. De nombreux paramètres vous aideront à mieux organiser les résultats. Les options générales et locales seront toutes étudiées ainsi que la fonction spécifique LIREDONNEESTABCROISDYNAMIQUE qui permet de récupérer des informations à partir du tableau croisé. Pour terminer cette première partie, vous ferez un petit tour au cœur d’un fichier Excel pour découvrir comment sont structurées en interne les données grâce au langage XML.

La Partie 2 porte sur l’origine des données. Vous y découvrirez le dispositif Mise sous forme de tableau qui évite les problèmes d’ajout de nouvelles lignes ou colonnes dans le tableau source. Vous étudierez la création de tableaux croisés à partir d’informations issues de plusieurs feuilles de calcul grâce aux fonctions RechercheV, Index/Equiv et par l’intermédiaire du dispositif Query. Puisque chaque version d’Excel apporte son lot de nouveautés, vous examinerez le dispositif Modèle de données qui facilite la relation entre les feuilles.

J’espère que la lecture de ce livre vous permettra de progresser dans la maîtrise de cet outil statistique. N'hésitez pas à me contacter à l’adresse [email protected] si vous avez des remarques sur ce livre ou bien des questions sur les tableaux croisés dynamiques. Je ne manquerai pas de vous répondre.

Bonne lecture.

L’auteur

Sommaire

Partie 1 Les fondamentaux

Chapitre 1 Créer un tableau croisé dynamique

1.1 Un TCD, à quoi ça sert ?

1.2 Structure du tableau source

1.3 Créer un tableau croisé dynamique

1.4 Mise à jour du tableau croisé dynamique

1.5 Voir les lignes à la source du résultat

1.6 Un exemple horizontal

1.7 Le filtre du rapport

1.8 Le menu Création

Chapitre 2 Paramétrer les calculs

2.1 Utiliser des fonctions de calculs

2.2 Compter des acronymes

2.3 Dénombrer à partir d’un groupe de dates

2.3.1 Regroupement par année

2.3.2 Regroupement multiple

2.4 Segments et chronologie

2.4.1 Les segments

2.4.2 Une chronologie visuelle

2.5 Calculs automatiques avec des %

2.5.1 Faire apparaître le % du total général

2.5.2 Apparition des totaux en plus du % du total général

2.5.3 % du total général avec regroupement

2.5.4 Calcul du % intermédiaire

2.6 Autres calculs de pourcentage

2.6.1 Affichage du % du total de la colonne

2.6.2 Affichage du % du total de la ligne

2.6.3 Affichage d’un calcul % de

2.6.4 Affichage d’un % du total de la ligne parente

2.6.5 Affichage d’un % du total de la colonne parente

2.6.6 Affichage d’un % du total du parent

2.7 Calculs de différence entre plusieurs valeurs

2.7.1 Affichage d’une différence par rapport

2.7.2 Affichage d’une différence en pourcentage par rapport

2.8 Calculs de cumuls

2.8.1 Affichage d’un résultat cumulé

2.8.2 Affichage d’un résultat cumulé en pourcentage

2.9 Classer des valeurs

2.10 Indexer des valeurs dans un TCD

Chapitre 3 Manipuler plusieurs champs

3.1 Création d’un champ calculé

3.2 Suppression d’un champ calculé

3.3 Un calcul de variation pour la force de vente

3.4 Une fonction SI dans un champ calculé

3.5 Deux fonctions SI dans un champ calculé

3.6 Ajouter un élément calculé

3.7 Afficher une liste des formules

Chapitre 4 Aller plus loin avec les options

4.1 Les options générales

4.1.1 La fonction LIREDONNEESTABCROISDYNAMIQUE

4.1.2 Désactivation de LIREDONNEESTABCROISDYNAMIQUE

4.1.3 Actualiser un tableau à l’ouverture

4.2 Les autres options locales

4.2.1 Les options de disposition et mise en forme

4.2.2 Les options Totaux et Filtres

4.2.3 Filtrer en enlevant les valeurs nulles dans une colonne

4.2.4 Les options d’affichage

4.2.5 Les options d’impression

4.2.6 Les options de données et la mémoire cache

4.3 Au cœur d’un tableau croisé dynamique

Partie 2 Les sources de données

Chapitre 5 La mise sous forme de tableau

5.1 Un problème de lignes et de colonnes

5.2 La mise sous forme de tableau

5.3 Une ligne de formule automatique

5.4 Mise sous forme de tableau et rapport croisé

5.5 Un tableau croisé consolidé

5.5.1 Le dispositif Consolider

5.5.2 Consolidation avec l’Assistant tableau croisé dynamique

Chapitre 6 Fonctions de recherche et modèle relationnel

6.1 La fonction RECHERCHEV

6.2 Les fonctions INDEX/EQUIV

6.3 L’utilitaire QUERY facilite la relation

6.4 Le modèle de données

Partie 1 Les fondamentaux

Cette première partie s’intéresse à la mise en place du tableau croisé dynamique à partir de données issues d’une seule feuille de calcul. Vous découvrirez ici la manière de réaliser toutes sortes de calculs statistiques ainsi que les techniques de présentation d’informations. Vous étudierez également les paramètres des options qui peuvent améliorer le travail quotidien.

CHAPITRE 1 : Créer un tableau croisé dynamique

CHAPITRE 2 : Paramétrer les calculs

CHAPITRE 3 : Manipuler plusieurs champs

CHAPITRE 4 : Aller plus loin avec les options

Chapitre 1 Créer un tableau croisé dynamique

Tout en rappelant les obligations liées à la présentation des données, ce chapitre expose les principes de base qui régissent la création d’un tableau croisé dynamique.

1.1 Un TCD, à quoi ça sert ?

Dès que vous employez la notion de catégorie ou de groupe d’informations, vous pouvez utiliser ce type de dispositif pour regrouper, calculer ou synthétiser des informations.

On parle de tableau croisé dynamique lorsqu’on vise la production d’un rapport. Les informations sont issues d’une liste de données organisées en colonnes puis croisées. Les éléments de base peuvent être stockés dans une ou plusieurs feuilles de calcul, provenir de plusieurs classeurs ou d’une source externe. La partie 2 de cet ouvrage présentera plus en détail le modèle de données qui permet d’établir des relations entre les tableaux.

La figure 1.1 est un exemple de tableau Excel qu’on appelle Base de données. Chaque colonne indique clairement des groupes de données identiques (mois, numéro, région) et la colonne quantité sert au calcul. Les versions d'Excel mettent à votre disposition 1 048 576 lignes et 16384 colonnes.

Vous pouvez ainsi manipuler de grandes quantités d’informations mais dans la pratique, il est assez rare d’employer toutes les lignes et toutes les colonnes. Pour bien travailler avec Excel et ses tableaux croisés, privilégiez un ordinateur rapide avec beaucoup de mémoire.

Figure 1.1 : Un tableau Excel sous la forme d'une base de données.

La structure de ce tableau implique le respect de certaines règles :

Dans la colonne A ne mettez que des mois.Dans la colonne C ne mettez que des régions.Dans la colonne D ne mettez que des valeurs numériques.N’insérez ni lignes vides, ni colonnes vides à l’intérieur du tableau.Nommez chaque en-tête de colonne.

Lorsque les données sont bien enregistrées, vous pouvez créer très facilement des statistiques. Par exemple, vous cherchez le total des quantités vendues (colonne D) par mois (colonne B ou A). Pour janvier, le total correspondrait à 22, pour février à 7, et pour mars à 13. Ici, le calcul reste facile puisqu’il n’y a que trois mois. Mais imaginez un tableau avec des centaines de lignes. Excel et son tableau croisé dynamique réaliseront les calculs aisément.

Dans ce tableau, la colonne B indique le mois en chiffres pour faciliter les tris ultérieurs. Quand il s’agit de regrouper des données et de calculer, Excel est excellent. Il sait regrouper des informations puis calculer des totaux en relation avec ces groupes. Le but d’un tableau croisé dynamique est donc de regrouper des valeurs communes puis de proposer un résultat numérique. Excel se base sur les données en colonnes et les croise.

En changeant la position des lignes et des colonnes, les calculs changent dynamiquement, d’où le nom de tableau croisé dynamique.

Vous verrez plus loin dans ce chapitre qu’il est tout à fait possible de choisir plusieurs regroupements, tout en changeant le mode de calcul du résultat par l’utilisation d’autres fonctions. Ainsi vous pourrez choisir de calculer la moyenne des quantités grâce à la fonction Moyenne, la quantité la plus grande grâce à la fonction Max ou la plus petite quantité grâce à la fonction Min.

Ces fonctions sont mises en place au moment de la création du rapport de tableau croisé dynamique. La figure 1-2 montre un résultat possible.

Figure 1-2 : Un tableau croisé dynamique.

La colonne A contient les mois et les autres colonnes les résultats statistiques. Au centre du tableau se placent les calculs trouvés. Une ligne Total général effectue la somme des valeurs. Les données sont dynamiques parce qu’elles changent dans le cas où les colonnes seraient différentes. Les sections suivantes détailleront la méthode à utiliser pour cette opération.

Revenons une dernière fois sur les obligations liées à la structure du tableau source.

1.2 Structure du tableau source

Pour réussir votre tableau croisé et ses statistiques, respectez les consignes suivantes en ce qui concerne la structure des données d’origine.

Chaque colonne du tableau source possède un nom unique. Vous pouvez indiquer celui-ci sur plusieurs lignes dans une même cellule (avec les touches Alt Entrée). Préférez malgré tout un nom simple.Ne fusionnez pas les cellules où se trouvent les noms des colonnes.N’insérez ni lignes ni colonnes vierges dans le tableau. La dernière ligne vide termine le tableau. De même pour la dernière colonne.Des cellules vierges sont possibles dans le tableau, elles ne nuisent en aucune manière à la structure.

Restez cohérent. Dans une colonne numérique, n’introduisez que des valeurs avec des nombres. Dans une colonne de dates, ne faites figurer que des dates. Dans une colonne texte, tout est permis.

Contrairement à une base de données comme Microsoft Access, SQL Server, FileMakerPro sur Mac ou encore MySQL sur Internet, Excel vous permet de saisir ce que vous souhaitez dans n’importe quelle colonne. Si vous le désirez, vous pouvez entrer du texte là où il ne devrait y avoir que des dates ou des nombres.

Cette permissivité dans la saisie des informations ne fait cependant pas bon ménage avec la réalisation d’un tableau croisé dynamique. Les données doivent absolument être cohérentes. Si une colonne regroupe des noms de villes, n’y mettez pas des codes postaux. Excel regroupera toujours les informations en fonction de ce que vous lui demandez, et grâce à cette bonne organisation, vous réussirez toujours vos objectifs.

Ces restrictions peuvent vous sembler étonnantes mais elles vous permettront, plus tard, de réaliser toutes vos statistiques sans surprise. Voyons maintenant la manière de créer un tableau croisé.

1.3 Créer un tableau croisé dynamique

Une fois le tableau source précisément défini et correctement rempli, vous pouvez commencer la mise en place du TCD. Respectez simplement les étapes suivantes.

Placez votre pointeur dans le tableau source. Il n’est pas nécessaire de sélectionner tout le tableau.Cliquez sur le menu Insertion / Tableau Croisé Dynamique.Terminez par le bouton OK.

Excel affiche alors une boîte de dialogue (figure 1.3) qui montre, d’une part, la zone complète du tableau source dans la zone Tableau/Plage et d’autre part, la possibilité de créer le tableau croisé dans une nouvelle feuille ou dans une feuille existante.

Figure 1.3 : Choix de l’emplacement du TCD.

Si vos données sont issues d’une source externe, vous devrez cocher Utiliser une source de données externes puis renseigner la connexion. La dernière option Ajouter ces données au modèle de données