Utilisation de tableaux croisés dynamiques dans Microsoft Excel

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 ce qu’ils sont 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 pensaient 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 utilisant 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. Naturellement, 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) plutôt que de s'en tenir à un programme (1-2-3) qui ne l’avait pas. Cette caractéristique, associée à une erreur de jugement sur le 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 un tableau croisé dynamique, exactement?

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 beaucoup plus qui peut être fait, aussi. Plutôt que d'essayer de décrire toutes les fonctionnalités des tableaux croisés dynamiques, nous les démontrerons simplement…

Les données que vous analysez à l'aide d'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, ce qui correspond apparemment au 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:

Vous serez peut-être surpris d'apprendre que, à l'aide de la fonctionnalité de tableau croisé dynamique d'Excel, nous pouvons créer un récapitulatif des ventes mensuel similaire à celui ci-dessus en quelques secondes, en quelques clics de souris. Nous pouvons le faire - et beaucoup plus aussi!

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…

Une fois la liste ouverte dans Excel, nous sommes prêts à créer le tableau croisé dynamique..

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:

La nouvelle feuille de calcul est créée pour nous et un tableau croisé dynamique vierge est créé sur cette feuille de calcul:

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 des en-têtes de colonne de la feuille de calcul des 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 déplacé dans cette zone représente les données qui doivent être résumées d'une manière ou d'une autre (sommation, 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 le 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:

Nous avons créé notre premier tableau croisé dynamique! Pratique, mais pas particulièrement impressionnant. Il est probable que nous avons besoin d'un peu plus de perspicacité dans nos données..

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ésent, enfin, les choses commencent à devenir intéressantes! Notre tableau croisé dynamique commence à prendre forme… .

En quelques clics, nous avons créé un tableau qui aurait pris beaucoup de temps à faire manuellement.

Alors, que pouvons-nous faire d'autre? Eh bien, dans 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 cela 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:

Qui ressemble à ceci:

Commencer à obtenir très cool!

Faisons-en un Trois-table dimensionnelle. À quoi une telle table pourrait-elle ressembler? Voyons voir…

Faites glisser la colonne / en-tête «Package» vers le Filtre de rapport boîte:

Remarquez où ça se termine… .

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»:

Et donc, si vous y réfléchissez correctement, notre tableau croisé dynamique est désormais en trois dimensions. Continuons à personnaliser…

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» des en-têtes de colonne. Cliquez sur la flèche déroulante à côté de Étiquettes de colonne, et décochez "Cash":

Voyons à quoi ça ressemble… Comme vous pouvez le voir, “Cash” est parti.

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. Corrigeons cela.

Une tentation pourrait être 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 nombres ont été correctement formatés en dollars.

Pendant que nous sommes sur le formatage, formatez le tableau croisé dynamique dans son ensemble. 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:

Choisissez celui qui fait appel et regardez le résultat dans votre tableau croisé dynamique:

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..

Tout d’abord, supprimons la colonne «Mode de paiement» de la Étiquettes de colonne (faites-le simplement glisser dans la liste des champs) et remplacez-le par la colonne «Date réservée»:

Comme vous pouvez le constater, cela rend notre tableau croisé dynamique instantanément inutile, nous donnant une colonne pour chaque date à laquelle une transaction a eu lieu - un très grand tableau.!

Pour résoudre ce problème, cliquez avec le bouton 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:

(Incidemment, ce tableau est pratiquement identique à celui présenté au début de cet article, à savoir le récapitulatif des ventes d'origine créé manuellement.)

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):

… Qui ressemble à ceci… .

Vous pouvez faire la même chose avec les en-têtes de colonnes (ou même les filtres de rapport).

Garder les choses simples à nouveau, voyons comment tracer en moyenne des valeurs plutôt que des valeurs additionnées.

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 le Nom d'usage, de «Moyenne du montant» à quelque chose d'un peu plus concis. Tapez quelque chose comme "Avg":

Cliquez sur D'accord, et voir à 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»:

Si nous voulons, nous pouvons même avoir des sommes, des moyennes et des comptes (comptes = combien de ventes il y a eu), tous sur le même tableau croisé dynamique!

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 de numéro en Comptabilité (0 décimale)
Pour le deuxième champ «Montant», changez son nom personnalisé en «Moyenne», sa fonction en 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 domaine de Étiquettes de colonne à Étiquettes de rangée

Voici ce que nous finissons avec:

Total, moyenne et compte sur le même tableau croisé dynamique!

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 offertes. Il y a aussi les deux onglets du ruban: Outils de tableau croisé dynamique / Options et Conception. Peu importe que vous commettiez 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 sous DOS 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. Il devrait fonctionner avec toutes les versions d'Excel à partir de 97 ans..

Téléchargez notre cahier d'exercices Excel