Les tableaux croisés dynamiques sont l’une des fonctionnalités les plus puissantes de Microsoft Excel. Ils permettent d'analyser et de résumer de grandes quantités de données en quelques clics de souris. Dans cet article, nous explorons les tableaux croisés dynamiques, comprenons leur nature et apprenons à les créer et les personnaliser.
Remarque: cet article est écrit avec Excel 2010 (bêta). Le concept de tableau croisé dynamique a peu changé au fil des ans, mais la méthode de création d'un tableau croisé dynamique a changé dans presque chaque itération d'Excel. Si vous utilisez une version d'Excel autre que 2010, attendez-vous à des écrans différents de ceux présentés dans cet article.
Un peu d'histoire
Aux premiers jours des tableurs, Lotus 1-2-3 faisait autorité. Sa domination était si complète que les gens ont pensé que c'était une perte de temps pour Microsoft de se donner la peine de développer son propre logiciel de tableur (Excel) pour concurrencer Lotus. Flash-forward jusqu’en 2010 et la domination d’Excel sur le marché des tableurs est plus grande que celle de Lotus, alors que le nombre d’utilisateurs exécutant encore Lotus 1-2-3 approche de zéro. Comment est-ce arrivé? Qu'est-ce qui a provoqué un retournement de situation aussi spectaculaire?
Les analystes du secteur ont attribué deux facteurs: tout d’abord, Lotus a décidé que cette nouvelle plate-forme d’interface graphique appelée «Windows» était une mode passagère qui ne décollerait jamais. Ils ont refusé de créer une version Windows de Lotus 1-2-3 (de toute façon pendant quelques années), prédisant que leur version DOS du logiciel était tout ce dont ils auraient besoin. Bien entendu, Microsoft a développé Excel exclusivement pour Windows. Deuxièmement, Microsoft a développé une fonctionnalité pour Excel que Lotus n’a pas fournie en 1-2-3, à savoir: Tableaux croisés dynamiques. La fonctionnalité de tableaux croisés dynamiques, exclusive à Excel, a été jugée tellement utile que les gens étaient prêts à apprendre un tout nouveau progiciel (Excel) au lieu de s'en tenir à un programme (1-2-3) qui ne l’avait pas. Cette caractéristique, associée au mauvais jugement du succès de Windows, a sonné le glas de Lotus 1-2-3 et a marqué le début du succès de Microsoft Excel.
Comprendre les tableaux croisés dynamiques
Alors, quel est exactement un tableau croisé dynamique?
En termes simples, un tableau croisé dynamique est un résumé de certaines données, créé pour permettre une analyse facile desdites données. Mais contrairement aux résumés créés manuellement, les tableaux croisés dynamiques Excel sont interactifs. Une fois que vous en avez créé un, vous pouvez facilement le modifier s'il n'offre pas les informations exactes que vous espériez sur vos données. En quelques clics, le résumé peut être «pivoté» - pivoté de manière à ce que les en-têtes de colonne deviennent des en-têtes de lignes, et inversement. Il y a encore beaucoup à faire. Plutôt que d’essayer de décrire toutes les fonctionnalités des tableaux croisés dynamiques, nous allons simplement en faire la démonstration…
Les données que vous analysez avec un tableau croisé dynamique ne peuvent pas être simplement tout données - il doit être brut données précédemment non traitées (non synthétisées) - généralement une liste d'un type. Un exemple de ceci pourrait être la liste des transactions de vente dans une entreprise au cours des six derniers mois.
Examinez les données ci-dessous:
Notez que c'est ne pas données brutes. En fait, c'est déjà un résumé de quelque sorte. Dans la cellule B3, vous pouvez voir 30 000 dollars, soit apparemment le total des ventes de James Cook pour le mois de janvier. Alors, où sont les données brutes? Comment en sommes-nous arrivés au chiffre de 30 000 $? Où se trouve la liste originale des transactions de vente à partir desquelles ce chiffre a été généré? Il est clair que quelque part, quelqu'un doit avoir pris la peine de rassembler toutes les transactions de vente des six derniers mois dans le résumé ci-dessus. Combien de temps pensez-vous que cela a pris? Une heure? Dix?
Très probablement, oui. Vous voyez, la feuille de calcul ci-dessus est en fait ne pas un tableau croisé dynamique. Il a été créé manuellement à partir de données brutes stockées ailleurs et sa compilation a effectivement pris quelques heures. Cependant, c’est exactement le genre de résumé que pourrait être créé à l'aide de tableaux croisés dynamiques, auquel cas cela n'aurait pris que quelques secondes. Voyons comment…
Si nous devions retrouver la liste initiale des transactions de vente, cela pourrait ressembler à ceci:
Comment créer un tableau croisé dynamique
Tout d’abord, assurez-vous que vous avez des données brutes dans une feuille de calcul Excel. Une liste de transactions financières est typique, mais il peut s'agir de n'importe quoi: coordonnées des employés, votre collection de CD ou les chiffres de consommation de carburant du parc de voitures de votre entreprise.
Nous commençons donc Excel… et nous chargeons une telle liste…
Cliquez sur l'une des cellules de la liste:
Ensuite, du Insérer onglet, cliquez sur le Tableau croisé dynamique icône:
le Créer un tableau croisé dynamique Une boîte de dialogue apparaît, vous posant deux questions: sur quelles données votre nouveau tableau croisé dynamique doit-il être basé et où doit-il être créé? Comme nous avons déjà cliqué sur une cellule de la liste (à l'étape ci-dessus), toute la liste entourant cette cellule est déjà sélectionnée pour nous (1 $: 88 $ sur le Paiements feuille, dans cet exemple). Notez que nous pourrions sélectionner une liste dans une autre région de toute autre feuille de calcul, voire même une source de données externe, telle qu'une table de base de données Access ou même une table de base de données MS-SQL Server. Nous devons également choisir si nous voulons que notre nouveau tableau croisé dynamique soit créé sur une Nouveau feuille de travail, ou sur un existant un. Dans cet exemple, nous allons sélectionner un Nouveau un:
Une autre boîte apparaît également: La Liste de champs de tableau croisé dynamique. Cette liste de champs sera affichée chaque fois que l'on cliquera sur une cellule du tableau croisé dynamique (ci-dessus):
La liste des champs dans la partie supérieure de la zone est en fait la collection d'en-têtes de colonne de la feuille de calcul de données brutes d'origine. Les quatre cases vides dans la partie inférieure de l'écran nous permettent de choisir la manière dont nous voudrions que notre tableau croisé dynamique résume les données brutes. Jusqu'à présent, il n'y a rien dans ces zones, donc le tableau croisé dynamique est vide. Tout ce que nous avons à faire est de faire glisser les champs de la liste ci-dessus vers le bas et de les déposer dans les cases inférieures. Un tableau croisé dynamique est alors automatiquement créé pour correspondre à nos instructions. Si nous nous trompons, il nous suffit de faire glisser les champs vers leur lieu d'origine et / ou de les faire glisser Nouveau champs vers le bas pour les remplacer.
le Valeurs La boîte est sans doute la plus importante des quatre. Le champ qui est glissé dans cette zone représente les données qui doivent être résumées d'une manière ou d'une autre (addition, moyenne, recherche du maximum, du minimum, etc.). C'est presque toujours numérique Les données. Un candidat idéal pour cette case dans nos exemples de données est le champ / colonne «Montant». Faisons glisser ce champ dans la Valeurs boîte:
Notez que (a) le champ «Montant» dans la liste des champs est maintenant coché et que «Somme du montant» a été ajouté à la liste. Valeurs case indiquant que la colonne du montant a été additionnée.
Si nous examinons le tableau croisé dynamique lui-même, nous trouvons bien la somme de toutes les valeurs «Montant» de la feuille de calcul des données brutes:
En nous référant à nos exemples de données, nous devons identifier un ou plusieurs en-têtes de colonne que nous pourrions utiliser pour diviser ce total. Par exemple, nous pouvons décider que nous voudrions voir un résumé de nos données où nous avons un en-tête de ligne pour chacun des différents vendeurs de notre société et un total pour chacun. Pour cela, il suffit de faire glisser le champ "Vendeur" dans le champ Étiquettes de rangée boîte:
À présentenfin, les choses commencent à devenir intéressantes! Notre tableau croisé dynamique commence à prendre forme….
Alors, que pouvons-nous faire d'autre? En un sens, notre tableau croisé dynamique est complet. Nous avons créé un résumé utile de nos données source. L'important est déjà appris! Pour le reste de l'article, nous examinerons certaines manières de créer des tableaux croisés dynamiques plus complexes et de personnaliser ces tableaux croisés dynamiques.
Premièrement, nous pouvons créer un deux-table dimensionnelle. Faisons-le en utilisant "Méthode de paiement" comme en-tête de colonne. Faites simplement glisser la rubrique "Méthode de paiement" vers le Étiquettes de colonne boîte:
Commencer à obtenir très cool!
Faisons-en un Trois-dimensional table. À quoi une telle table pourrait-elle ressembler? Voyons voir…
Faites glisser la colonne / en-tête «Package» vers le Filtre de rapport boîte:
Cela nous permet de filtrer notre rapport en fonction du «forfait vacances» acheté. Par exemple, nous pouvons voir la répartition du vendeur par mode de paiement pour tout ou, en quelques clics, changez-le pour afficher la même répartition pour le package «Sunseekers»:
S'il s'avère, disons, que nous voulons seulement voir chèque et carte de crédit transactions (c'est-à-dire aucune transaction en espèces), nous pouvons alors désélectionner le poste «Espèces» dans les en-têtes de colonne. Cliquez sur la flèche déroulante à côté de Étiquettes de colonneet décochez «Cash»:
Mise en forme
C'est évidemment un système très puissant, mais jusqu'à présent, les résultats semblent très clairs et ennuyeux. Pour commencer, les chiffres que nous additionnons ne ressemblent pas à des montants en dollars, mais simplement à de vieux chiffres. Rectifions cela.
Il peut être tentant de faire ce que nous avons l'habitude de faire dans de telles circonstances: il suffit de sélectionner tout le tableau (ou la feuille de calcul) et d'utiliser les boutons de formatage de nombre standard de la barre d'outils pour terminer le formatage. Le problème avec cette approche est que si vous modifiez un jour la structure du tableau croisé dynamique (ce qui est probable à 99%), ces formats de nombres seront perdus. Nous avons besoin d'un moyen qui les rendra (semi) permanents.
Tout d’abord, nous localisons l’entrée «Somme du montant» dans Valeurs boîte, et cliquez dessus. Un menu apparaît. Nous sélectionnons Paramètres du champ de valeur… à partir du menu:
le Paramètres du champ de valeur La boîte apparaît.
Clique le Format de nombre bouton, et la norme Format de la boîte de cellules apparaît:
Du Catégorie liste, sélectionnez (par exemple) Comptabilitéet laissez le nombre de décimales à 0. Cliquez sur D'accord quelques fois pour revenir au tableau croisé dynamique…
Comme vous pouvez le constater, les chiffres ont été correctement formatés en dollars.
Au sujet du formatage, formatez le tableau croisé dynamique dans son intégralité. Il y a quelques façons de le faire. Utilisons un simple…
Clique le Outils de tableau croisé dynamique / Conception languette:
Puis baisse la flèche en bas à droite de la Styles de tableau croisé dynamique liste pour voir une vaste collection de styles intégrés:
Autres options
Nous pouvons aussi travailler avec des dates. Maintenant, généralement, il y a beaucoup de dates dans une liste de transactions telle que celle avec laquelle nous avons commencé. Mais Excel offre la possibilité de regrouper les éléments de données par jour, semaine, mois, année, etc. Voyons comment cela se fait.
Commençons par supprimer la colonne "Mode de paiement" de la Étiquettes de colonne (faites-le simplement glisser vers le haut dans la liste des champs) et remplacez-le par la colonne «Date réservée»:
Pour résoudre ce problème, faites un clic droit sur une date et sélectionnez Groupe… depuis le menu contextuel:
La boîte de regroupement apparaît. Nous sélectionnons Mois et cliquez sur OK:
Voila! UNE beaucoup tableau plus utile:
Une autre chose intéressante à prendre en compte est que vous pouvez avoir plusieurs ensembles d’en-têtes de lignes (ou d’en-têtes de colonnes):
Pour que les choses restent simples à nouveau, voyons comment tracer en moyenne valeurs, plutôt que des valeurs totales.
Tout d’abord, cliquez sur «Somme du montant», puis sélectionnez Paramètres du champ de valeur… depuis le menu contextuel qui apparaît:
dans le Résumer le champ de valeur par liste dans le Paramètres du champ de valeur boîte, sélectionnez Moyenne:
Pendant que nous sommes ici, changeons la Nom d'usage, de «Moyenne du montant» à quelque chose d’un peu plus concis. Tapez quelque chose comme "Avg":
Cliquez sur D'accordet voyez à quoi ça ressemble. Notez que toutes les valeurs passent des totaux cumulés aux moyennes et que le titre du tableau (cellule en haut à gauche) a été remplacé par «Avg»:
Voici les étapes à suivre pour obtenir quelque chose comme ça (à partir d'un tableau croisé dynamique vierge):
- Faites glisser "Vendeur" dans le Étiquettes de colonne
- Faites glisser le champ «Montant» vers le bas Valeurs boîte trois fois
- Pour le premier champ «Montant», changez son nom personnalisé en «Total» et son format numérique en Comptabilité (0 décimale)
- Pour le deuxième champ «Montant», changez son nom personnalisé en «Moyenne», sa fonction pour Moyenne et son format numérique à Comptabilité (0 décimale)
- Pour le troisième champ «Montant», changez son nom en «Compte» et sa fonction en Compter
-
Faites glisser le créé automatiquement
Image domaine de Étiquettes de colonne à Étiquettes de rangée
Voici ce que nous finissons avec:
Conclusion
Il existe de nombreuses autres fonctionnalités et options pour les tableaux croisés dynamiques créés par Microsoft Excel - beaucoup trop pour être énumérées dans un article comme celui-ci. Pour exploiter pleinement le potentiel des tableaux croisés dynamiques, un petit livre (ou un grand site Web) serait nécessaire. Les lecteurs courageux et / ou geek peuvent explorer les tableaux croisés dynamiques plus facilement: il suffit de cliquer avec le bouton droit de la souris sur à peu près tout et de voir quelles options vous sont proposées. Il y a aussi les deux onglets du ruban: Outils de tableau croisé dynamique / Options et Conception. Peu importe si vous faites une erreur - il est facile de supprimer le tableau croisé dynamique et de recommencer - une possibilité que les anciens utilisateurs de DOS 1-2-3 n’avaient jamais eu.
Si vous travaillez dans Office 2007, vous pouvez consulter notre article sur la création d’un tableau croisé dynamique dans Excel 2007.
Nous avons inclus un classeur Excel que vous pouvez télécharger pour exercer vos compétences de tableau croisé dynamique. Cela devrait fonctionner avec toutes les versions d'Excel à partir de 97 ans.
Téléchargez notre cahier d'exercices Excel
