Comment créer un tableau croisé dynamique dans Excel : un didacticiel pas à pas

Le tableau croisé dynamique est l'une des fonctions les plus puissantes et les plus intimidantes de Microsoft Excel. Puissant car il peut vous aider à résumer et à donner un sens à de grands ensembles de données. Intimidant car vous n'êtes pas exactement un expert d'Excel et les tableaux croisés dynamiques ont toujours eu la réputation d'être compliqués.

La bonne nouvelle : apprendre à créer un tableau croisé dynamique dans Excel est beaucoup plus facile que vous ne le pensez.

Mais avant de vous guider tout au long du processus de création d'un tableau croisé dynamique, prenons du recul et assurons-nous que vous comprenez exactement ce qu'est un tableau croisé dynamique et pourquoi vous pourriez avoir besoin d'en utiliser un.

En d'autres termes, les tableaux croisés dynamiques extraient le sens de ce fouillis apparemment sans fin de chiffres sur votre écran. Et plus précisément, il vous permet de regrouper vos données de différentes manières afin de tirer plus facilement des conclusions utiles.

La partie "pivot" d'un tableau croisé dynamique découle du fait que vous pouvez faire pivoter (ou faire pivoter) les données du tableau pour les afficher sous un angle différent. Pour être clair, vous n'ajoutez pas, ne soustrayez pas ou ne modifiez pas vos données lorsque vous effectuez un pivot. Au lieu de cela, vous réorganisez simplement les données afin de pouvoir en révéler des informations utiles.

A quoi servent les tableaux croisés dynamiques ?

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 de ces technologies qui sont beaucoup plus faciles à comprendre une fois que vous l'avez vue en action.

Le but des tableaux croisés dynamiques est d'offrir des moyens conviviaux de résumer rapidement de grandes quantités de données. Ils peuvent être utilisés pour mieux comprendre, afficher et analyser les données numériques en détail - et peuvent aider à identifier et à répondre aux questions imprévues qui les entourent.

Voici sept scénarios hypothétiques où un tableau croisé dynamique pourrait être une solution :

1. Comparer les totaux des ventes de différents produits.

Supposons que vous disposiez d'une feuille de calcul contenant des données de ventes mensuelles pour trois produits différents - produit 1, produit 2 et produit 3 - et que vous souhaitiez déterminer lequel des trois a rapporté le plus d'argent. Vous pouvez, bien sûr, parcourir la feuille de calcul et ajouter manuellement le chiffre de vente correspondant à un total cumulé chaque fois que le produit 1 apparaît. Vous pouvez ensuite faire de même pour le produit 2 et le produit 3 jusqu'à ce que vous ayez tous les totaux. Morceau de gâteau, non?

Maintenant, imaginez que votre feuille de calcul des ventes mensuelles comporte des milliers et des milliers de lignes. Les trier manuellement peut prendre toute une vie. À l'aide d'un tableau croisé dynamique, vous pouvez automatiquement agréger tous les chiffres de vente du produit 1, du produit 2 et du produit 3 - et calculer leurs sommes respectives - en moins d'une minute.

2. Affichage des ventes de produits en pourcentage des ventes totales.

Les tableaux croisés dynamiques affichent naturellement les totaux de chaque ligne ou colonne lorsque vous les créez. Mais ce n'est pas le seul chiffre que vous pouvez produire automatiquement.

Supposons que vous ayez saisi les chiffres des ventes trimestrielles de trois produits distincts dans une feuille Excel et transformé ces données en tableau croisé dynamique. Le tableau vous donnerait automatiquement trois totaux au bas de chaque colonne, après avoir additionné les ventes trimestrielles de chaque produit. Mais que se passerait-il si vous vouliez trouver le pourcentage de ces ventes de produits qui ont contribué à toutes les ventes de l'entreprise, plutôt que seulement le total des ventes de ces produits ?

Avec un tableau croisé dynamique, vous pouvez configurer chaque colonne pour vous donner le pourcentage de la colonne des trois totaux de colonne, au lieu du seul total de colonne. Si trois ventes de produits ont totalisé 200 000 $ de ventes, par exemple, et que le premier produit a rapporté 45 000 $, vous pouvez modifier un tableau croisé dynamique pour dire à la place que ce produit a contribué à 22,5 % de toutes les ventes de l'entreprise.

Pour afficher les ventes de produits sous forme de pourcentages des ventes totales dans un tableau croisé dynamique, cliquez simplement avec le bouton droit de la souris sur la cellule contenant un total des ventes et sélectionnez Afficher les valeurs en tant que > % du total général .

3. Combinaison de données en double.

Dans ce scénario, vous venez de terminer la refonte d'un blog et vous avez dû mettre à jour un tas d'URL. Malheureusement, votre logiciel de création de rapports de blog ne l'a pas très bien géré et a fini par diviser les métriques de "vue" pour les publications individuelles entre deux URL différentes. Ainsi, dans votre feuille de calcul, vous avez deux instances distinctes de chaque article de blog individuel. Pour obtenir des données précises, vous devez combiner les totaux de vue pour chacun de ces doublons.

C'est là que le tableau croisé dynamique entre en jeu. Au lieu de devoir rechercher et combiner manuellement toutes les métriques des doublons, vous pouvez résumer vos données (via un tableau croisé dynamique) par titre de publication de blog, et le tour est joué : les métriques de vue de ces publications en double seront agrégées automatiquement.

4. Obtenir un effectif d'employés pour des départements distincts.

Les tableaux croisés dynamiques sont utiles pour calculer automatiquement des éléments que vous ne trouvez pas facilement dans un tableau Excel de base. L'une de ces choses consiste à compter les lignes qui ont toutes quelque chose en commun.

Si vous avez une liste d'employés dans une feuille Excel, par exemple, et à côté des noms des employés se trouvent les départements respectifs auxquels ils appartiennent, vous pouvez créer un tableau croisé dynamique à partir de ces données qui vous montre chaque nom de département et le nombre d'employés qui appartiennent à ces départements. Le tableau croisé dynamique élimine efficacement votre tâche de trier la feuille Excel par nom de service et de compter chaque ligne manuellement.

5. Ajout de valeurs par défaut aux cellules vides.

Tous les ensembles de données que vous entrez dans Excel ne rempliront pas toutes les cellules. Si vous attendez que de nouvelles données arrivent avant de les saisir dans Excel, vous pourriez avoir beaucoup de cellules vides qui semblent déroutantes ou qui nécessitent des explications supplémentaires lors de la présentation de ces données à votre responsable. C'est là qu'interviennent les tableaux croisés dynamiques.

Vous pouvez facilement personnaliser un tableau croisé dynamique pour remplir les cellules vides avec une valeur par défaut, telle que 0 $ ou TBD (pour « à déterminer »). Pour les grands tableaux de données, pouvoir étiqueter rapidement ces cellules est une fonctionnalité utile lorsque de nombreuses personnes examinent la même feuille.

Pour formater automatiquement les cellules vides de votre tableau croisé dynamique, cliquez avec le bouton droit sur votre tableau et cliquez sur Options de tableau croisé dynamique. Dans la fenêtre qui apparaît, cochez la case intitulée Empty Cells As et entrez ce que vous souhaitez afficher lorsqu'une cellule n'a pas d'autre valeur.

Comment créer un tableau croisé dynamique

1. Entrez vos données dans une plage de lignes et de colonnes.
2. Triez vos données par un attribut spécifique.
3. Mettez en surbrillance vos cellules pour créer votre tableau croisé dynamique.
4. Faites glisser et déposez un champ dans la zone "Étiquettes de ligne".
5. Faites glisser et déposez un champ dans la zone "Valeurs".
6. Affinez vos calculs.

Maintenant que vous avez une meilleure idée de ce à quoi les tableaux croisés dynamiques peuvent être utilisés, entrons dans le vif du sujet pour savoir comment en créer un.

Étape 1. Entrez vos données dans une plage de lignes et de colonnes.

Chaque tableau croisé dynamique dans Excel commence par un tableau Excel de base, où toutes vos données sont hébergées. Pour créer ce tableau, entrez simplement vos valeurs dans un ensemble spécifique de lignes et de colonnes. Utilisez la ligne ou la colonne la plus haute pour classer vos valeurs en fonction de ce qu'elles représentent.

Par exemple, pour créer un tableau Excel des données de performance des publications de blog, vous pouvez avoir une colonne répertoriant chaque « Top Pages », une colonne répertoriant les « Clics » de chaque URL, une colonne répertoriant les « Impressions » de chaque publication, etc. (Nous utiliserons cet exemple dans les étapes qui suivent.)

Étape 2. Triez vos données par un attribut spécifique.

Lorsque vous avez toutes les données que vous souhaitez saisir dans votre feuille Excel, vous souhaiterez trier ces données d'une manière ou d'une autre afin qu'elles soient plus faciles à gérer une fois que vous les aurez transformées en tableau croisé dynamique.

Pour trier vos données, cliquez sur l'onglet Données dans la barre de navigation supérieure et sélectionnez l'icône Trier 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.

Par exemple, pour trier votre feuille Excel par "Vues à ce jour", sélectionnez ce titre de colonne sous Colonne , puis indiquez si vous souhaitez classer vos publications du plus petit au plus grand ou du plus grand au plus petit.

Sélectionnez OK en bas à droite de la fenêtre de tri et vous réussirez à réorganiser chaque ligne de votre feuille Excel en fonction du nombre de vues reçues par chaque article de blog.

Étape 3. Mettez en surbrillance vos cellules pour créer votre tableau croisé dynamique.

Une fois que vous avez entré des données dans votre feuille de calcul Excel et que vous les avez triées à votre guise, mettez en surbrillance les cellules que vous souhaitez résumer dans un tableau croisé dynamique. Cliquez sur Insérer dans la barre de navigation supérieure, puis sélectionnez l'icône de 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 que vous souhaitez inclure dans le tableau croisé dynamique.

Cela ouvrira une boîte d'options dans laquelle, en plus de définir votre plage de cellules, vous pourrez 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 y accéder et en sortir en bas de votre classeur Excel. Une fois que vous avez choisi, cliquez sur OK.

Vous pouvez également mettre en surbrillance vos cellules, sélectionner Tableaux croisés dynamiques recommandés à droite de l'icône Tableau croisé dynamique et ouvrir un tableau croisé dynamique avec des suggestions prédéfinies sur la façon d'organiser chaque ligne et colonne.

Remarque : Si vous utilisez une version antérieure d'Excel, « Tableaux croisés dynamiques » peut se trouver sous Tables ou Données le long de la navigation supérieure, plutôt que « Insérer ». Dans Google Sheets, vous pouvez créer des tableaux croisés dynamiques à partir du menu déroulant Données dans la barre de navigation supérieure.

Étape 4. Faites glisser et déposez un champ dans la zone "Étiquettes de ligne".

Après avoir terminé l'étape 3, Excel créera un tableau croisé dynamique vierge pour vous. Votre prochaine étape consiste à faire glisser et déposer un champ - étiqueté en fonction des noms des colonnes de votre feuille de calcul - dans la zone Étiquettes de ligne . Cela déterminera par quel identifiant unique (titre de l'article de blog, nom du produit, etc.) le tableau croisé dynamique organisera vos données.

Par exemple, supposons que vous souhaitiez organiser un ensemble de données de blog par titre de publication. Pour ce faire, il vous suffit de cliquer et de faire glisser le champ « Top pages » vers la zone « Row Labels ».

Remarque : Votre tableau croisé dynamique peut être différent selon la version d'Excel avec laquelle vous travaillez. Cependant, les principes généraux restent les mêmes.

Étape 5. Faites glisser et déposez un champ dans la zone "Valeurs".

Une fois que vous avez défini comment vous allez organiser vos données, votre prochaine étape consiste à ajouter des valeurs en faisant glisser un champ dans la zone Valeurs .

Pour rester dans l'exemple des données de blog, supposons que vous souhaitiez résumer les vues des articles de blog par titre. Pour ce faire, vous feriez simplement glisser le champ "Vues" dans la zone Valeurs.

Étape 6. Affinez vos calculs.

La somme d'une valeur particulière sera calculée par défaut, mais vous pouvez facilement la changer en quelque chose comme moyenne, maximum ou minimum selon ce que vous voulez calculer.

Sur un Mac, vous pouvez le faire en cliquant sur le petit i à côté d'une valeur dans la zone "Valeurs", en sélectionnant l'option souhaitée et en cliquant sur "OK". Une fois votre sélection effectuée, votre tableau croisé dynamique sera mis à jour en conséquence.

Si vous utilisez un PC, vous devrez cliquer sur le petit triangle à l'envers à côté de votre valeur et sélectionner Paramètres du champ de valeur pour accéder au menu.

Lorsque vous avez catégorisé vos données à votre convenance, enregistrez votre travail et utilisez-le comme bon vous semble.

Aller plus loin avec les tableaux croisés dynamiques

Vous avez maintenant appris les bases de la création de tableaux croisés dynamiques dans Excel. Avec cette compréhension, vous pouvez déterminer ce dont vous avez besoin à partir de votre tableau croisé dynamique et trouver les solutions que vous recherchez.

Par exemple, vous remarquerez peut-être que les données de votre tableau croisé dynamique ne sont pas triées comme vous le souhaitez. Si tel est le cas, la fonction de tri d'Excel peut vous aider. Alternativement, vous devrez peut-être incorporer des données provenant d'une autre source dans vos rapports, auquel cas la fonction RECHERCHEV pourrait s'avérer utile.

Note de l'éditeur : Ce message a été initialement publié en décembre 2018 et a été mis à jour pour plus d'exhaustivité.