Tutoriel Excel : les tableaux croisés dynamiques

En arrivant a ce stade, on suppose que vous savez comment saisir les données dans un tableau Excel et comment les manipuler. Ce tutoriel vous apprend comment créer, modifier et supprimé un tableau croisé dynamique sur Excel 2016 mais valable aussi pour Excel 2010 et 2013 .

Les tableaux croisés dynamiques et leur utilité :

Le tableau croisé dynamique est l’une des fonctions les plus puissantes de Microsoft Excel. Puissant, car il peut vous aider à résumer, comprendre et extraire la signification d’un grand ensemble de données.

Mais avant de vous guider dans le processus de création, reculons-nous et assurons-vous de bien comprendre en quoi consiste un tableau croisé dynamique et pourquoi vous pourriez en avoir besoin.

C’est quoi un tableau croisé dynamique ?

Comme son nom l’indique, un tableau croisé dynamique, est un nouveau tableau que vous allez créer sur Excel et qui va reprendre des données que vous avez déjà entré dans votre feuille de calcul et qui va les croiser de manière dynamique.

En d’autres termes, Dans sa forme la plus élémentaire, un tableau croisé dynamique extrait les données et les synthétise pour leur donner du sens, le tout sans entrer la moindre formule !

Vous allez vous demander pourquoi les utiliser ?

En fait, ce type de tableau va vous servir à obtenir très rapidement des résultats à partir de vos données sans avoir à faire, vous-même, des opérations des tris ou des filtres pour chaque résultat que vous voulez obtenir.

Lorsque vous avez beaucoup de données, il peut parfois être difficile d'analyser toutes les informations de votre feuille de calcul. Les tableaux croisés dynamiques peuvent aider à rendre vos feuilles de calcul plus faciles à gérer en résumant vos données et en vous permettant de les manipuler de différentes manières.

Maintenant que vous avez une idée sur les tableaux croisés dynamiques, je vous propose de passer à voir son utilité dans des exemples d’applications réelles.

Comment utiliser un tableau croisé :

Si vous ne savez toujours pas ce que font réellement les tableaux croisés dynamiques, ne vous inquiétez pas. C'est l'une des technologies qu'il est beaucoup plus facile de comprendre une fois que vous l'avez vue en action. Alors nous allons voir dans cette partie des exemples d’utilisation des tableau croisés dynamiques dans des cas particuliers.

Exemple 1 : cet exemple représente un tableau croisé dynamique un peu sophistiqué et qui donne les montants des dépenses par type d’achat et par acheteur.

Nous avons un tableau de départ qui contient la liste des montants correspondants à un acheteur donné dans une date donné et pour un type de produit précis. Grace à un tableau croisé dynamique nous avons pu calculer automatiquement la somme des dépenses par type d’achat at par acheteur.

Exemple 2 : Dans cet exemple nous avons un tableau qui représente les opérations d’achat ou de location effectués par un homme ou une femme dans une date donnée et les montant qui leur sont associés.

Supposons qu’on veut calculer le montant totale de ces opérations pour chaque jour pour les hommes et pour les femmes, Alors ça on peut le faire nous-même manuellement, mais si nous avons des centaines et des centaines de ligne dans notre table ça va nous prendre énormément de temps, et c’est dans ce cas-là qu’un tableau croisé dynamique va être utile puisqu’il va faire ce travail beaucoup plus rapidement comme dans le tableau ci-dessous.

Comment créer un tableau croisé dynamique :

On arrive à la partie la plus intéressante (Oui, c’est là ou vous allez voir les étapes pour créer un tableau croisé vous-même). Ici nous allons travailler sur l’exemple que nos avons vu au début et qui représente un tableau des montants par date, acheteur et type d’objet acheté.

Etape 1 : La saisie des données

La première étape consiste à entrer vos données dans une plage de lignes et de colonnes et de vérifier qu’elles soient correctement entrées et surtout, assurez-vous de l’absence de lignes en double ou de cellules vides, puisque chaque tableau croisé dynamique d'Excel commence par un tableau Excel de base, dans lequel toutes vos données sont hébergées.

Pour créer cette table, entrez simplement vos valeurs dans un ensemble spécifique de lignes et de colonnes. Utilisez la rangée la plus haute ou la colonne la plus haute pour classer vos valeurs en fonction de ce qu'elles représentent.

Etape 2 : La deuxième étape consiste à trier vos données selon un attribut spécifique.

Une fois que vous avez saisi toutes les données souhaitées dans votre feuille Excel, je vous invite à les trier de manière à ce qu'il soit plus facile à gérer une fois que vous les avez transformées en tableau croisé dynamique.

Pour trier vos données, cliquez n’importe où sur votre table puis sur l'onglet "Données" dans la barre de navigation supérieure sélectionnez l'icône "Trier" située en dessous. Dans la fenêtre qui apparaît, vous pouvez choisir de trier vos données selon la colonne de votre choix et dans n'importe quel ordre. Pour trier votre feuille Excel par "date", par exemple, sélectionnez le titre de cette colonne sous "Colonne", puis choisissez si vous souhaitez classer vos données du plus petit au plus grand ou du plus grand au plus petit.

Etape 3 : Maintenant on va créer notre tableau croisé dynamique :

Une fois que vous avez saisi et trié les données dans votre feuille de calcul Excel, sélectionnez la plage de cellules que vous souhaitez inclure dans la table croisée dynamique puis cliquez sur "Insertion" dans la barre de navigation supérieure, puis sélectionnez l'icône "Tableau croisé dynamique". Vous pouvez également cliquer n'importe où dans votre feuille de calcul, sélectionner "Tableau croisé dynamique" et entrer manuellement la plage de cellules à inclure dans le tableau croisé dynamique.

Cela ouvrira une boîte d’option où, en plus de définir votre plage de cellules, vous pouvez choisir de lancer ou non ce tableau croisé dynamique dans une nouvelle feuille de calcul ou de le conserver dans la feuille de calcul existante. Si vous ouvrez une nouvelle feuille, vous pouvez naviguer vers cette page en bas de votre classeur Excel. Une fois que vous avez choisi, cliquez sur "OK".

Remarque : pour sélectionner une plage différente de celle suggérée par Excel, cliquez sur la zone de sélection de cellule et utilisez la souris pour sélectionner une nouvelle plage.

Excel place par défaut les tableaux croisés dynamiques dans une nouvelle feuille distincte, à gauche de la feuille dans laquelle vous vous trouvez, par exemple, supposons que vos données se trouvent dans une feuille nommée « Feuil20 ». Si vous avez créé un tableau croisé dynamique Excel le place dans une nouvelle feuille, à gauche, nomme celle-ci « Feuil », puis lui attribue un numéro tel que « 21 », « 23 », etc.  Vous pouvez aussi double-cliquer sur la feuille et lui donner un autre nom ; par exemple, « Tableau croisé dynamique ».

Pourquoi le placer dans une nouvelle feuille : En fait, le nom complet d’un tableau croisé dynamique est le suivant : « Rapport de tableau croisé dynamique ». Un rapport repose sur des données, et généralement les utilisateurs préfèrent que les rapports soient séparés des données proprement dites. Cela dit, il est possible de placer les tableaux croisés dynamiques dans la même feuille que les données. Mais dans la mesure où les tableaux croisés dynamiques ont généralement besoin de beaucoup d’espace, il est préférable de les placer dans une nouvelle feuille.

Dans notre exemple j’ai choisi de le mettre sur la même feuille que notre tableau de départ juste pour une raison de visibilité et pour que je puisse vous expliquer les choses.

Etape 4 : l’étape suivante est de faire glisser les champs dans les zones correspondantes :

Recommandation : Avant de commencer un tableau croisé dynamique, tâchez de vous rappeler que vous devez d’abord créer le champ de valeur. Les champs contenant des valeurs numériques sont ceux qui conviennent le mieux à la création de champs de valeurs. Dans cet exemple, le champ Montant est celui qui convient le mieux, car les autres champs (Date, Acheteur, Type) ne contiennent pas de valeurs numériques. 

Donc après avoir terminé l’étape précédente, Excel crée un tableau croisé dynamique vierge comme celui-là :

Le tableau croisé dynamique créé contient un tableau croisé blanc à gauche de l'écran, les champs du tableau croisé (en-têtes de colonne à partir des données d'origine), en haut à droite de l'écran et les zones de tableau croisé en bas à droite de l'écran.

Ce que vous avez à faire c’est de glisser un champ (libellé d'après les noms des colonnes de votre feuille de calcul) dans la zone voulue. Cela déterminera quel identifiant unique (date, nom d’acheteur, etc.) par-lequel le tableau croisé dynamique organisera vos données. Mais avant de faire glisser les champs il nous faut d’abord préciser ce qu’on cherche à avoir comme résultats, par exemple on peut chercher à avoir le montant totale pour chaque acheteur, donc il nous faut poser la question : quels champs on veut avoir dans les lignes et quels champs dans les colonnes ? cette question revient à savoir par quels champs veut-on organiser notre résultat.

Notre objectif et d’avoir pour chaque acheteur le montant total de ses dépenses, donc on aura besoin des deux champs : Acheteur et Montant.

Et pour organiser le résultat il sera mieux d’avoir la liste des acheteurs sur la ligne et comme valeur on va calculer la somme des montants correspondants, donc ici le champ Acheteur joue le rôle d’une condition pour décomposer le champ de valeur (la moyenne), donc ce qu’on va faire maintenant c’est de glisser le champ Acheteur dans la zone « lignes » et le champ « montant » dans la zone valeurs.

Vous allez voir que votre tableau croisé dynamique se crée automatiquement en suivant l’organisation que vous avez choisi. Et comme vous avez vu La somme des montant a été calculé par défaut, mais vous pouvez facilement la changer en moyenne, maximum ou minimum en fonction de ce que vous souhaitez calculer.

Pour ce faire, vous pouvez le faire en cliquant sur la petite flèche à côté d’une valeur dans la zone "Valeurs", puis dans la liste affichée choisissez « Paramètres des champs de valeurs » puis dans la fenêtre qui apparait sélectionnez l’option de votre choix et en cliquant sur "OK". Une fois votre sélection effectuée, votre tableau croisé dynamique sera mis à jour en conséquence.

Vous allez constater que quand vous avez créé le tableau croisé dynamique un autre onglet est ajouté dans la barre d’outils d’Excel et qui est nommée « Outils de tableau croisé dynamique », si cet onglet n’est pas actif vous pouvez l’activer en plaçant le curseur sur le tableau croisé.

Voilà, vous venez de créer un tableau croisé dynamique, et bien sûr il y a plusieurs cas ou vous allez les appliquer, j’ai donné ici des exemples simples pour vous faciliter l’apprentissage.

Modifier un tableau croisé dynamique :

Excel nous donne aussi la possibilité d’apporter les modifications souhaitées à un tableau croisé dynamique. Les tableaux croisés dynamiques sont beaucoup plus dynamiques que les tableaux de données Excel 2016 standard, car ils restent très faciles à manipuler et à modifier. Pour modifier les champs utilisés dans votre tableau croisé dynamique, vous devez d'abord afficher la liste des champs de tableau croisé dynamique à partir de l’onglet « Outils de tableau croisé dynamique » que nous avons vu.

Cliquez sur l’une des cellules du tableau croisé dynamique. Excel ajoute l'onglet contextuel « Outils de tableau croisé dynamique » avec les onglets « « Analyser » et « Création ».

Cliquez sur l'onglet « Analyser » sous l'onglet contextuel « Outils de tableau croisé dynamique » pour afficher ses boutons sur le ruban.

Cliquez ensuite sur le bouton « Liste des champs » dans le groupe « Afficher ».

Excel affiche le volet de tâches Liste de champs de tableau croisé dynamique, qui présente les champs actuellement présents dans le tableau croisé dynamique, ainsi que les zones auxquelles ils sont actuellement affectés. Ce volet est généralement affiché automatiquement lors de la création ou de la sélection d'un tableau croisé dynamique, mais si vous ne le voyez pas, cliquez sur le bouton Liste des champs.

Après avoir affiché le volet de tâches Liste de champs de tableau croisé dynamique, vous pouvez apporter les modifications suivantes aux champs de la table :

1-Supprimer un champ : 

Pour supprimer un champ, faites glisser son nom en dehors de l'une de ses zones de dépôt (FILTRES, COLONNES, ROWS et VALEURS) et, lorsque le pointeur de la souris se transforme en « x », relâchez le bouton de la souris. Vous pouvez aussi le supprimer en décochant la case correspondante dans la zone des champs.

2-Déplacer un champ :

Pour déplacer un champ existant vers un nouvel emplacement de la table, faites glisser son nom de champ de sa zone de dépôt actuelle vers une nouvelle zone au bas du volet de tâches.

3-Ajouter un champ : 

Pour ajouter un champ à la table, faites glisser son nom dans la liste « Choisissez les champs à ajouter au rapport » et déposez le champ dans la zone de dépôt souhaitée.

Si vous souhaitez simplement ajouter un champ au tableau croisé dynamique en tant que champ de rangée supplémentaire, cochez la case correspondante dans le champ « Choisissez les champs à ajouter au rapport », vous n'avez pas besoin de le faire glisser vers la zone de dépôt « ligne ».