Cours Excel: Tableau croisé dynamique
Cours Excel : Tableau croisé dynamique
Rappel :
Excel est un tableur, c’est-à-dire un logiciel de gestion de tableaux. Il permet de réaliser des calculs avec des valeurs numériques, mais aussi avec des dates et des textes. Ainsi résumé en trois lignes, cela peut sembler simplissime. Il n’en est rien. Inutile de faire appel à Excel pour réaliser quelques opérations dans un tableau : un logiciel de traitement de texte comme Word en est parfaitement capable.
Excel, lui, est un logiciel très puissant qui permet de réaliser des calculs complexes, et ce, quel qu’en soit le domaine : comptabilité, stock, facturation, budget, planning, prêt, portefeuille, statistique etc. Cette liste n’est nullement exhaustive ! Quel que soit votre problème de calcul, Excel peut vous apporter rapidement la solution.
A chaque démarrage d’Excel, un classeur vierge s’ouvre avec 3 feuilles.
Vous pouvez passer d’une feuille à l’autre en cliquant simplement sur le nom de la feuille
Vous pouvez ajouter des feuilles, les renommer, changer la couleur de l’onglet (Cliq Dt)
Chaque « case » représente une cellule. Ce sont les cellules que vous devrez modifier par la suite afin de créer toutes sortes de choses.
La zone « A1 » représente le nom de la cellule.
La zone « Fx » » représente le contenu de la cellule.
...
- Avant de créer un tableau
Avant de commencer, il faut une base de données. Il est possible de créer et de gérer des bases de données simples à partir d'Excel.
Dans une base de données Excel, chaque colonne représente un champ. Le nom du champ doit être sur la première ligne. Chaque ligne suivante représente un enregistrement. Afin qu'Excel soit capable de reconnaître tous les enregistrements qui composent la base de données, il est important de ne pas laisser des lignes vides. Toutes les lignes après le nom des champs doivent avoir des enregistrements. La base de données suivante conserve des données sur les employés d'une entreprise.
III. Création d'un tableau croisé dynamique
Placez le pointeur sur n'importe quelle cellule entre A1 et G16.
Du menu Données, sélectionnez l'option Rapport de tableau croisé dynamique.
Excel vous demande où est située la source des données qui vont servir à composer le tableau croisé dynamique. Ces données peuvent provenir de quatre sources différentes.
Liste ou base de données Microsoft Excel. Les données proviennent d'une base de données Excel ou d'une série de cellules située sur une feuille de calcul d'Excel.
Source de données externes Les données provient d'autres logiciels tels qu'Access, dBASE, FoxPro ainsi que plusieurs autres.
Plage de feuilles de calcul avec étiquette. Créer automatiquement un tableau après lui avoir déterminé la plage de cellules à utiliser. Il utilise le contenu de la première ligne et de la première colonne pour déterminer le nom des champs du tableau.
Autre tableau ou graphique croisé dynamique Vous permet d'approfondir des analyses sur des tableaux et graphiques dynamiques qui ont déjà été conçus.
Excel vous demande ensuite quel type de rapport que vous voulez: tableau ou graphique ? Cette version d'Excel permet non seulement de générer un tableau mais aussi un graphique dynamique.
Pour les besoins de l'exercice, utilisez les mêmes options que sur le graphique ci-dessus (Base de données Excel et tableau).
Appuyez sur le bouton Suivant.
Excel vous demande de confirmer l'endroit où sont situées les données dont vous avez besoin pour le tableau croisé dynamique.
Assurez-vous que les cellules sélectionnées soient bien entre A1 et G16.
Appuyez sur le bouton Suivant.
Excel vous demande ensuite où vous voulez conserver le tableau croisé dynamique. Est-ce sur une nouvelle feuille de calcul ou sur la même qu'en ce moment ?
Pour les besoins de l'exercice, sélectionnez l'option Nouvelle feuille.
Vous pourriez appuyer sur le bouton Terminer et commencer à concevoir le tableau croisé dynamique. Mais auparavant, voyons les autres options offertes dans cette fenêtre.
Appuyez sur le bouton Disposition.
Cette fenêtre vous permet de concevoir immédiatement le tableau croisé dynamique. Vous pouvez placer les champs dont vous avez besoin dans quatre zones différentes: page, ligne, colonne et données.
Données Cette zone affiche les résultats que vous voulez voir pour un champ. Par défaut, le tableau affiche la somme des valeurs si celui-ci est composé de chiffres. S'il est composé de texte, le tableau va afficher le nombre d'enregistrement qui répond au critère.
Il y a d'autres fonctions qui sont disponibles tel que la moyenne, l'écart type et plusieurs autres. Une liste sera mentionnée à la fin de cette page.
Colonne Affiche chacune des valeurs d'un champ dans sa propre colonne.
Ligne Affiche chacune des valeurs d'un champ sur sa propre ligne.
Page Permet de "filtrer" les valeurs du tableau par rapport aux valeurs d'un champ. Ceci permet de voir seulement les enregistrements qui répondent à un certain critère.
Cette présentation de l'option Disposition était seulement pour vous démontrer les éléments qui composent un tableau. La création du tableau et la description de toutes les options seront faites un peu plus loin sur cette page.
- Placer les champs
Excel a créé une nouvelle feuille de calcul avec la "coquille" d'un tableau croisé dynamique. Le début de la feuille démontre les quatre zones du tableau: page, ligne, colonne et données.
Il y a aussi la barre d'outils pour le tableau croisé dynamique qui devrait apparaître à côté de celui-ci. Voici ce que vous devez faire pour afficher la barre d'outils si vous ne la voyez pas.
Du menu Affichage, sélectionnez l'option Barre d'outils.
De la liste des barres d'outils disponibles, sélectionnez l'option Tableau croisé dynamique.
Il est possible aussi que vous ne voyiez pas la liste des champs qui compose la base de données. Pour l'afficher, placez le pointeur n'importe où à l'intérieur du tableau croisé dynamique.
De la barre d'outils Tableau croisé dynamique, sélectionnez le champ Salaire.
En gardant un doigt sur le bouton gauche de la souris, déplacez le champ dans la zone de données.
Relâchez le bouton de la souris dès que le carré pour le champ Salaire est par-dessus la zone de données.
...
Le champ Sexe va être automatiquement placé devant le champ Titre. À cause de la longueur du tableau, seulement une partie est affichée à l'image ci-dessus. Il est possible aussi de changer l'ordre de présentation des champs. La prochaine opération consiste à donner la priorité au champ Titre par-dessus Sexe.
Placez le pointeur par-dessus le champ Titre de la zone des colonnes du tableau croisé dynamique.
En gardant un doigt sur le bouton gauche de la souris, déplacez le champ Titre devant le champ Sexe.
Une fois devant le champ Sexe, relâchez le bouton de la souris.
Voici les mêmes informations que le tableau précédent mais affiché de manière différente. Les totaux des salaires pour les administratrices de l'entreprise sont toujours de 27 000 $ tandis que les hommes ont 126 500 $. Cependant, les informations sont maintenant regroupées par occupation et ensuite par le sexe. La prochaine opération va afficher les informations d'une manière un peu plus simple à comprendre.
Placez le pointeur par-dessus le champ Titre de la zone des colonnes du tableau croisé dynamique.
En gardant un doigt sur le bouton gauche de la souris, déplacez le champ Titre dans la zone des lignes du tableau croisé dynamique (par-dessus Somme de la ligne).
Une fois le champ est dans la zone des lignes, relâchez le bouton de la souris.
Bien qu'il s'agisse des mêmes montants que les deux tableaux précédents, les résultats sont plus clairs.
- Voir les données
Excel vous permet de voir les enregistrements qui composent les résultats du tableau. La prochaine partie consiste à voir quels sont les enregistrements du total des administrateurs (153 500 $).
Placez le pointeur sur la cellule contenant le total des administrateurs (153 500 $).
Faites un double-clic sur la cellule.
Une nouvelle feuille de calcul va être créée avec les enregistrements qui correspondent au total des administrateurs. Vous pouvez refaire la même chose pour toutes les cellules du tableau croisé dynamique.
- Option Mettre en forme le rapport
Vous avez créé un tableau croisé dynamique avec les champs et les critères dont vous avez besoin. Cette option vous permet d'améliorer la présentation de votre tableau.
Appuyez sur le bouton .
Il est possible de changer la présentation du tableau en sélectionnant l'un des formats prédéterminés. Vous pouvez changer d'avis en tout temps et prendre un format qui répond mieux à vos besoins.
Pour les besoins de l'exercice, ne changez pas la présentation. Appuyez sur le bouton Annuler.
- Option assistant tableau croisé dynamique
Cette option permet de changer la disposition des champs dans le tableau croisé dynamique. Cette partie du texte va démontrer qu'il est possible de changer la présentation en ajoutant les champs Nom et Prénom à la zone des lignes. Ceci est aussi nécessaire pour pouvoir vous démontrer le fonctionnement de la prochaine option.
Appuyez sur le bouton .
Appuyez sur le bouton Suivant.
Appuyez sur le bouton Disposition.
Déplacez le champ Nom en dessous du champ Titre de la zone des lignes.
Déplacez le champ Prénom en dessous du champ Nom de la zone des lignes.
Le résultat devrait ressembler à ceci.
Appuyez sur le bouton OK.
Appuyez sur le bouton Terminer.
Voici une partie du nouveau tableau qui affiche maintenant dans la zone des lignes les champs Titre, Nom et Prénom.
- Option Actualiser les données
Cette option vous permet de remettre à jour les données du tableau croisé dynamique après avoir fait une mise à jour dans la base de données.
Placez le pointeur dans la feuille de calcul avec la base de données.
Placez le pointeur dans la cellule F11 (salaire de Karl Lalonde).
Changer le salaire de 31 500 $ à 37 100 $.
Retourner à la feuille de calcul ayant le tableau croisé dynamique.
Appuyez sur le bouton .
La somme partielle pour les ouvriers ainsi que le total des salaires devrait avoir changé à 79 600 $ et 400 000 $ respectivement.
- Options Masquer et afficher les détails
Il est possible d'avoir dans une zone plusieurs champs pour mieux décrire les valeurs. Ces options permettent d'afficher ou de masquer les valeurs des champs qui sont à la droite du champ sélectionné. Si vous ne l'avez pas fait, ajoutez les champs Nom et Prénom à la zone des lignes.
Placez le pointeur sur le champ Nom.
Appuyez sur le bouton .
Bien que le champ Prénom reste visible, les valeurs sont masquées. Elles ne sont pas affichées.
Cette option cache les valeurs des champs qui sont à la droite de cette dernière.
Appuyez sur le bouton .
Les valeurs du champ Prénom vont réapparaître.
Placez le pointeur sur le champ Prénom.
Appuyez sur le bouton .
Excel va vous afficher la liste des champs qui ne sont pas déjà dans cette zone. Pour pouvoir afficher les valeurs, il doit premièrement avoir un champ à sa droite. C'est une autre façon d'ajouter un champ à une zone.
Appuyez sur le bouton Annuler.
Pour les besoins du prochain exercice, cachez le contenu du champ Prénom.
Appuyez sur le champ Prénom situé dans la zone des lignes.
Appuyez sur le bouton .