Table des matières
Pivot tables est l'un des outils les plus puissants d'Excel. Ils vous permettent d'analyser et de résumer divers résumés de grandes quantités de données en quelques clics de souris. Dans cet article, nous allons nous familiariser avec les tableaux croisés dynamiques, comprendre ce qu'ils sont, apprendre à les créer et à les personnaliser.
Cet article a été écrit à l'aide d'Excel 2010. Le concept de tableaux croisés dynamiques n'a pas beaucoup changé au fil des ans, mais la façon dont vous les créez est légèrement différente dans chaque nouvelle version d'Excel. Si vous avez une version d'Excel non 2010, préparez-vous à ce que les captures d'écran de cet article diffèrent de ce que vous voyez sur votre écran.
Un peu d'histoire
Aux débuts des logiciels de tableur, la boule de règles Lotus 1-2-3. Sa domination était si complète que les efforts de Microsoft pour développer son propre logiciel (Excel) comme alternative à Lotus semblaient être une perte de temps. Avance rapide jusqu'en 2010 ! Excel domine les feuilles de calcul plus que le code Lotus ne l'a jamais fait dans son histoire, et le nombre de personnes qui utilisent encore Lotus est proche de zéro. Comment cela pourrait-il arriver? Quelle était la raison d'une telle tournure dramatique des événements?
Les analystes identifient deux facteurs principaux :
- Tout d'abord, Lotus a décidé que cette nouvelle plate-forme d'interface graphique appelée Windows n'était qu'une mode passagère qui ne durerait pas longtemps. Ils ont refusé de construire une version Windows de Lotus 1-2-3 (mais seulement pendant quelques années), prédisant que la version DOS de leur logiciel serait tout ce dont les consommateurs auraient besoin. Microsoft a naturellement développé Excel spécifiquement pour Windows.
- Deuxièmement, Microsoft a introduit un outil dans Excel appelé PivotTables qui n'était pas disponible dans Lotus 1-2-3. Les tableaux croisés dynamiques, exclusifs à Excel, se sont révélés si utiles que les gens avaient tendance à s'en tenir à la nouvelle suite logicielle Excel plutôt qu'à continuer avec Lotus 1-2-3, qui n'en avait pas.
Les tableaux croisés dynamiques, en plus de sous-estimer le succès de Windows en général, ont joué la marche de la mort pour Lotus 1-2-3 et inauguré le succès de Microsoft Excel.
Que sont les tableaux croisés dynamiques ?
Alors, quelle est la meilleure façon de caractériser ce que sont les tableaux croisés dynamiques ?
En termes simples, les tableaux croisés dynamiques sont des résumés de certaines données, créés pour faciliter l'analyse de ces données. Contrairement aux totaux créés manuellement, les tableaux croisés dynamiques Excel sont interactifs. Une fois créés, vous pouvez facilement les modifier s'ils ne donnent pas l'image que vous espériez. En quelques clics de souris, les totaux peuvent être inversés afin que les en-têtes de colonne deviennent des en-têtes de ligne et vice versa. Vous pouvez faire beaucoup de choses avec les tableaux croisés dynamiques. Au lieu d'essayer de décrire avec des mots toutes les fonctionnalités des tableaux croisés dynamiques, il est plus facile de le démontrer en pratique...
Les données que vous analysez avec les tableaux croisés dynamiques ne peuvent pas être aléatoires. Il devrait s'agir de données brutes brutes, comme une liste quelconque. Par exemple, il peut s'agir d'une liste des ventes réalisées par l'entreprise au cours des six derniers mois.
Regardez les données présentées dans la figure ci-dessous :
Notez qu'il ne s'agit pas de données brutes brutes, car elles ont déjà été résumées. Dans la cellule B3, nous voyons 30000 30000 $, ce qui est probablement le résultat total que James Cook a obtenu en janvier. Où sont les données d'origine alors? D'où vient le chiffre de XNUMX XNUMX $ ? Où se trouve la liste originale des ventes à partir de laquelle ce total mensuel a été dérivé ? Il est clair que quelqu'un a fait un excellent travail d'organisation et de tri de toutes les données de vente des six derniers mois et de les transformer en tableau des totaux que nous voyons. Combien de temps pensez-vous que cela a pris? Heure? Dix heures?
Le fait est que le tableau ci-dessus n'est pas un tableau croisé dynamique. Il a été fabriqué à la main à partir de données brutes stockées ailleurs et a pris au moins quelques heures à traiter. Un tel tableau récapitulatif peut être créé à l'aide de tableaux croisés dynamiques en quelques secondes seulement. Découvrons comment…
Si nous revenons à la liste des ventes d'origine, cela ressemblerait à ceci :
Vous pourriez être surpris qu'à partir de cette liste de métiers à l'aide de tableaux croisés dynamiques et en quelques secondes seulement, nous puissions créer un rapport de ventes mensuel dans Excel, que nous avons analysé ci-dessus. Oui, nous pouvons faire cela et plus encore !
Comment créer un tableau croisé dynamique ?
Tout d'abord, assurez-vous d'avoir des données sources dans une feuille Excel. La liste des transactions financières est la plus typique qui se produit. En fait, il peut s'agir d'une liste de n'importe quoi : les coordonnées des employés, une collection de CD ou les données de consommation de carburant de votre entreprise.
Alors, on démarre Excel… et on charge une telle liste…
Après avoir ouvert cette liste dans Excel, nous pouvons commencer à créer un tableau croisé dynamique.
Sélectionnez n'importe quelle cellule dans cette liste :
Puis sur l'onglet Insertion (Insérer) sélectionner la commande Tableau croisé dynamique (tableau croisé dynamique) :
Une boîte de dialogue apparaîtra Créer un tableau croisé dynamique (Création d'un tableau croisé dynamique) avec deux questions pour vous :
- Quelles données utiliser pour créer un nouveau tableau croisé dynamique ?
- Où mettre le tableau croisé dynamique ?
Étant donné qu'à l'étape précédente, nous avons déjà sélectionné l'une des cellules de la liste, toute la liste sera automatiquement sélectionnée pour créer un tableau croisé dynamique. Notez que nous pouvons sélectionner une plage différente, une table différente et même une source de données externe telle qu'une table de base de données Access ou MS-SQL. De plus, nous devons choisir où placer le nouveau tableau croisé dynamique : sur une nouvelle feuille ou sur l'une des feuilles existantes. Dans cet exemple, nous choisirons l'option - Nouvelle feuille de travail (vers une nouvelle feuille):
Excel créera une nouvelle feuille et y placera un tableau croisé dynamique vide :
Dès que nous cliquons sur une cellule du tableau croisé dynamique, une autre boîte de dialogue apparaît : Liste des champs de tableau croisé dynamique (Champs de tableau croisé dynamique).
La liste des champs en haut de la boîte de dialogue est une liste de tous les titres de la liste d'origine. Les quatre zones vides en bas de l'écran vous permettent d'indiquer au tableau croisé dynamique comment vous souhaitez résumer les données. Tant que ces zones sont vides, il n'y a rien non plus dans le tableau. Tout ce que nous avons à faire est de faire glisser les titres de la zone supérieure vers les zones vides ci-dessous. En parallèle, un tableau croisé dynamique est généré automatiquement, conformément à nos instructions. Si nous commettons une erreur, nous pouvons supprimer les titres de la zone inférieure ou en faire glisser d'autres pour les remplacer.
Région Valeurs (Significations) est probablement le plus important des quatre. L'en-tête placé dans cette zone détermine quelles données seront résumées (somme, moyenne, maximum, minimum, etc.). Il s'agit presque toujours de valeurs numériques. Un excellent candidat pour une place dans ce domaine sont les données sous la rubrique Montant (Coût) de notre table d'origine. Faites glisser ce titre vers la zone Valeurs (Valeurs):
Veuillez noter que le titre Montant est maintenant marqué d'une coche, et dans la zone Valeurs (Valeurs) une entrée est apparue Somme du montant (Champ Montant Montant), indiquant que la colonne Montant résumé.
Si nous regardons le tableau croisé dynamique lui-même, nous verrons la somme de toutes les valeurs de la colonne Montant tableau d'origine.
Ainsi, notre premier tableau croisé dynamique est créé ! Pratique, mais pas particulièrement impressionnant. Nous voulons probablement obtenir plus d'informations sur nos données que nous n'en avons actuellement.
Revenons aux données d'origine et essayons d'identifier une ou plusieurs colonnes pouvant être utilisées pour diviser cette somme. Par exemple, nous pouvons former notre tableau croisé dynamique de manière à ce que le montant total des ventes soit calculé pour chaque vendeur individuellement. Ceux. des lignes seront ajoutées à notre tableau croisé dynamique avec le nom de chaque vendeur de l'entreprise et le montant total de ses ventes. Pour obtenir ce résultat, il suffit de faire glisser le titre vendeur (représentant commercial) à la région Étiquettes de ligne (Cordes):
Cela devient plus intéressant ! Notre tableau croisé dynamique commence à prendre forme…
Voir les avantages ? En quelques clics, nous avons créé un tableau qui aurait pris beaucoup de temps à créer manuellement.
Que pouvons-nous faire d'autre? Eh bien, dans un sens, notre tableau croisé dynamique est prêt. Nous avons créé un résumé utile des données originales. Information importante déjà reçue! Dans le reste de cet article, nous examinerons quelques façons de créer des tableaux croisés dynamiques plus complexes, ainsi que d'apprendre à les personnaliser.
Configuration du tableau croisé dynamique
Tout d'abord, nous pouvons créer un tableau croisé dynamique à deux dimensions. Faisons cela en utilisant l'en-tête de colonne Mode de paiement (Mode de paiement). Faites simplement glisser le titre Mode de paiement à la région Étiquettes de colonne (Colonnes):
On obtient le résultat :
Ça a l'air très cool!
Faisons maintenant un tableau en trois dimensions. A quoi ressemblerait un tel tableau ? Voyons voir…
Faire glisser l'en-tête Emballage Papier (Complexe) à la zone filtres de rapport (Filtres):
Notez où il se trouve…
Cela nous donne la possibilité de filtrer le rapport sur la base de "Pour quel complexe de vacances a été payé". Par exemple, nous pouvons voir une répartition par vendeurs et par modes de paiement pour tous les complexes, ou en quelques clics de souris, changer la vue du tableau croisé dynamique et afficher la même répartition uniquement pour ceux qui ont commandé le complexe Chercheurs de soleil.
Donc, si vous comprenez bien cela, alors notre tableau croisé dynamique peut être qualifié de tridimensionnel. Continuons la mise en place…
S'il s'avère soudainement que seul le paiement par chèque et carte bancaire (c'est-à-dire le paiement sans numéraire) doit être affiché dans le tableau croisé dynamique, alors nous pouvons désactiver l'affichage du titre Argent liquide (En espèces). Pour cela, à côté Étiquettes de colonne cliquez sur la flèche vers le bas et décochez la case dans le menu déroulant Argent liquide:
Voyons à quoi ressemble notre tableau croisé dynamique maintenant. Comme vous pouvez le voir, la colonne Argent liquide a disparu d'elle.
Formatage des tableaux croisés dynamiques dans Excel
Les tableaux croisés dynamiques sont évidemment un outil très puissant, mais jusqu'à présent, les résultats semblent un peu simples et ennuyeux. Par exemple, les chiffres que nous additionnons ne ressemblent pas à des montants en dollars – ce ne sont que des chiffres. Réglons ça.
Il est tentant de faire ce à quoi vous êtes habitué dans une telle situation et de sélectionner simplement le tableau entier (ou la feuille entière) et d'utiliser les boutons de formatage des nombres standard de la barre d'outils pour définir le format souhaité. Le problème avec cette approche est que si vous modifiez la structure du tableau croisé dynamique à l'avenir (ce qui se produit avec 99 % de chances), la mise en forme sera perdue. Ce dont nous avons besoin, c'est d'un moyen de le rendre (presque) permanent.
Trouvons d'abord l'entrée Somme du montant in Valeurs (Valeurs) et cliquez dessus. Dans le menu qui s'affiche, sélectionnez l'élément Paramètres du champ de valeur (Options de champ de valeur) :
Une boîte de dialogue apparaîtra Paramètres du champ de valeur (Options de champ de valeur).
Cliquez sur le bouton Nombre Format (Format numérique), une boîte de dialogue s'ouvre. Format de cellule (format cellule) :
De la liste Catégories (Formats numériques) sélectionnez Comptabilité (Finance) et réglez le nombre de décimales sur zéro. Maintenant, appuyez plusieurs fois OKpour revenir à notre tableau croisé dynamique.
Comme vous pouvez le voir, les nombres sont formatés en montants en dollars.
Pendant que nous y sommes avec le formatage, configurons le format pour l'ensemble du tableau croisé dynamique. Il y a plusieurs moyens de le faire. On utilise celui qui est le plus simple…
Cliquez Outils de tableau croisé dynamique : conception (Travailler avec des tableaux croisés dynamiques : constructeur) :
Ensuite, développez le menu en cliquant sur la flèche dans le coin inférieur droit de la section Styles de tableau croisé dynamique (Styles de tableau croisé dynamique) pour voir la vaste collection de styles en ligne :
Choisissez n'importe quel style approprié et regardez le résultat dans votre tableau croisé dynamique :
Autres paramètres de tableau croisé dynamique dans Excel
Parfois, vous devez filtrer les données par dates. Par exemple, dans notre liste de métiers, il y a beaucoup, beaucoup de dates. Excel fournit un outil pour regrouper les données par jour, mois, année, etc. Voyons comment c'est fait.
Supprimez d'abord l'entrée. Mode de paiement de la région Étiquettes de colonne (Colonnes). Pour ce faire, faites-le glisser vers la liste des titres, et à sa place, déplacez le titre Date de réservation (date de réservation):
Comme vous pouvez le voir, cela a temporairement rendu notre tableau croisé dynamique inutile. Excel a créé une colonne distincte pour chaque date à laquelle une transaction a été effectuée. En conséquence, nous avons eu une table très large!
Pour résoudre ce problème, faites un clic droit sur n'importe quelle date et sélectionnez dans le menu contextuel GROUPE (Groupe):
La boîte de dialogue de regroupement apparaît. Nous choisissons Mois (Mois) et cliquez sur OK:
Voila ! Ce tableau est bien plus utile :
Soit dit en passant, ce tableau est presque identique à celui présenté au début de l'article, où les totaux des ventes ont été compilés manuellement.
Il y a un autre point très important que vous devez savoir ! Vous pouvez créer non pas un, mais plusieurs niveaux d'en-têtes de lignes (ou de colonnes) :
… et ça ressemblera à ça …
La même chose peut être faite avec les en-têtes de colonne (ou même les filtres).
Revenons à la forme originale du tableau et voyons comment afficher des moyennes au lieu de sommes.
Pour commencer, cliquez sur Somme du montant et dans le menu qui apparaît, sélectionnez Paramètres du champ de valeur (Options de champ de valeur) :
La liste Récapituler le champ de valeur par (Opération) dans la boîte de dialogue Paramètres du champ de valeur (Options de champ de valeur) sélectionnez Moyen (Moyen):
En même temps, tant qu'on est là, changeons Nom d'usage (Nom personnalisé) avec Moyenne du montant (Champ Montant Montant) à quelque chose de plus court. Entrez dans ce champ quelque chose comme Moy:
Presse OK et voyez ce qui se passe. Notez que toutes les valeurs sont passées des totaux aux moyennes et que l'en-tête du tableau (dans la cellule supérieure gauche) est devenu Moy:
Si vous le souhaitez, vous pouvez immédiatement obtenir le montant, la moyenne et le nombre (ventes) placés dans un tableau croisé dynamique.
Voici un guide étape par étape sur la façon de procéder, en commençant par un tableau croisé dynamique vide :
- Faire glisser l'en-tête vendeur (représentant commercial) à la région Étiquettes de colonne (Colonnes).
- Faites glisser le titre trois fois Montant (Coût) à la zone Valeurs (Valeurs).
- Pour le premier champ Montant changer le titre en Total (Montant), et le format numérique dans ce champ est Comptabilité (Financier). Le nombre de décimales est zéro.
- Deuxième champ Montant prénom Moyennee, définissez l'opération pour cela Moyen (Moyenne) et le format des nombres dans ce champ changent également en Comptabilité (Finance) sans décimales.
- Pour le troisième champ Montant définir un titre que vous avez et une opération pour lui - que vous avez (Quantité)
- Dans le Étiquettes de colonne (Colonnes) champ créé automatiquement Valeurs Σ (Σ Values) – faites-le glisser vers la zone Étiquettes de ligne (Lignes)
Voici ce à quoi nous aboutirons :
Montant total, valeur moyenne et nombre de ventes - le tout dans un seul tableau croisé dynamique !
Conclusion
Les tableaux croisés dynamiques dans Microsoft Excel contiennent de nombreuses fonctionnalités et paramètres. Dans un si petit article, ils ne sont même pas près de tous les couvrir. Il faudrait un petit livre ou un grand site Web pour décrire complètement toutes les possibilités des tableaux croisés dynamiques. Les lecteurs audacieux et curieux peuvent poursuivre leur exploration des tableaux croisés dynamiques. Pour ce faire, faites un clic droit sur presque n'importe quel élément du tableau croisé dynamique et voyez quelles fonctions et quels paramètres s'ouvrent. Sur le ruban, vous trouverez deux onglets : Outils de tableau croisé dynamique : options (analyse) et Conception (Constructeur). N'ayez pas peur de faire une erreur, vous pouvez toujours supprimer le tableau croisé dynamique et recommencer. Vous avez une opportunité que les utilisateurs de longue date de DOS et de Lotus 1-2-3 n'ont jamais eue.