Utilisation de pivots et de tranches pour décrire les données avec Microsoft Excel

■■ Qu’est-ce qu’un tableau croisé dynamique?

■■ Comment puis-je utiliser un tableau croisé dynamique pour résumer les ventes d’épicerie dans plusieurs épiceries?

■■ Quelles dispositions de tableau croisé dynamique sont disponibles dans Excel ?

■■ Pourquoi un tableau croisé dynamique est-il appelé tableau croisé dynamique?

■■ Comment puis-je facilement changer le format d’un tableau croisé dynamique?

■■ Comment puis-je réduire et développer des champs?

■■ Comment puis-je trier et filtrer les champs de tableau croisé dynamique?

■■ Comment puis-je résumer un tableau croisé dynamique à l’aide d’un graphique croisé dynamique?

■■ Comment puis-je utiliser la section Filtre de rapport du tableau croisé dynamique?

■■ Comment fonctionnent les trancheurs de tableau croisé dynamique?

■■ Comment puis-je ajouter des lignes vides ou masquer des sous-totaux dans un tableau croisé dynamique?

■■ Comment puis-je appliquer une mise en forme conditionnelle à un tableau croisé dynamique?

■■ Comment puis-je mettre à jour mes calculs lorsque j’ajoute de nouvelles données?

■■ Je travaille pour une petite agence de voyages pour laquelle je dois envoyer en masse une brochure de voyage. Mes fonds sont limités, je veux donc envoyer la brochure aux personnes qui dépensent le plus en voyages. D’après des informations provenant d’un échantillon aléatoire de 925 personnes, je connais le sexe, l’âge et le montant que ces personnes ont dépensé en voyage l’année dernière. Comment puis-je utiliser ces données pour déterminer comment le sexe et l’âge influencent les dépenses de voyage d’une personne? Que puis-je conclure sur le type de personne à qui je dois envoyer la brochure?

■■ Je fais des études de marché sur les break. Je dois déterminer quels facteurs influencent la probabilité qu’une famille achète un break. D’après les informations d’un large échantillon de familles, je connais la taille de la famille (grande ou petite) et le revenu familial (élevé ou faible). Comment puis-je déterminer comment la taille de la famille et le revenu influencent la probabilité qu’une famille achète un break?

■■ Je travaille pour un fabricant qui vend des micropuces dans le monde entier. Je reçois des ventes mensuelles réelles et prévues pour le Canada, la France et les États-Unis pour la puce 1, la puce 2 et la puce 3. Je reçois également l’écart ou la différence entre les revenus réels et budgétés. Pour chaque mois et chaque combinaison de pays et de produit, je souhaite afficher les données suivantes: chiffre d’affaires réel, revenus budgétés, écart réel, revenus réels en pourcentage des revenus annuels et écart en pourcentage des revenus budgétés. Comment afficher ces informations?

■■ Qu’est-ce qu’un champ calculé?

■■ Comment puis-je utiliser un filtre de rapport ou une tranche?

■■ Comment puis-je regrouper des éléments dans un tableau croisé dynamique?

■■ Qu’est-ce qu’un élément calculé?

■■ Qu’est-ce que le «drill down»?

■■ Je dois souvent utiliser des données spécifiques dans un tableau croisé dynamique pour déterminer le profit, comme les ventes d’avril en France de la puce 1. Malheureusement, ces données se déplacent lorsque de nouveaux champs sont ajoutés à mon tableau croisé dynamique. Excel a-t-il une fonction qui me permet de toujours extraire les ventes de la puce 1 d’avril en France à partir du tableau croisé dynamique?

■■ Comment puis-je utiliser la fonctionnalité Nouvelle chronologie d’Excel  pour résumer les données sur différentes périodes?

■■ Comment puis-je utiliser un tableau croisé dynamique pour résumer les ventes totales à ce jour au cours d’une année?

■■ Comment puis-je utiliser un tableau croisé dynamique pour résumer les ventes de ce mois par rapport au même mois un an plus tôt?

■■ Comment puis-je créer un tableau croisé dynamique basé sur des données à plusieurs endroits?

■■ Comment puis-je créer un tableau croisé dynamique basé sur un tableau croisé dynamique déjà créé?

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début du chapitre.

Qu’est-ce qu’un tableau croisé dynamique?

Dans de nombreuses situations commerciales, vous devez analyser, ou découper et découper vos données pour obtenir des informations importantes. Imaginez que vous vendiez différents produits d’épicerie dans différents magasins à différents moments. Vous pourriez avoir des centaines de milliers de points de données à suivre. Les tableaux croisés dynamiques vous permettent de résumer

vos données rapidement de presque toutes les manières imaginables. Par exemple, pour vos données d’épicerie, vous pouvez utiliser un tableau croisé dynamique pour déterminer rapidement les éléments suivants:

■■ Montant dépensé par an dans chaque magasin pour chaque produit

■■ Dépenses totales dans chaque magasin

■■ Dépenses totales pour chaque année

Dans une agence de voyages, comme autre exemple, vous pouvez découper des données afin de déterminer si le montant moyen dépensé pour les voyages est influencé par l’âge ou le sexe ou par les deux facteurs. Dans l’analyse des achats d’automobiles, vous souhaitez comparer la fraction des grandes familles qui achètent un break à la fraction des petites familles qui achètent un break. Pour un fabricant de micropuces, vous souhaitez déterminer le total des ventes de la puce 1 en France, par exemple en avril, etc. Un tableau croisé dynamique est un outil incroyablement puissant que vous pouvez utiliser pour découper et découper des données. La façon la plus simple de comprendre le fonctionnement d’un tableau croisé dynamique consiste à parcourir des exemples soigneusement construits, alors commencez! Commencez par un exemple d’introduction, puis explorez de nombreuses fonctionnalités avancées de tableau croisé dynamique à travers des exemples suivants.

Comment puis-je utiliser un tableau croisé dynamique pour résumer les ventes d’épicerie dans plusieurs épiceries?

La feuille de calcul Data dans le fichier Groceriespt.xlsx contient plus de 900 lignes de données de vente. (Voir Figure 1.) Chaque ligne contient également le nombre d’unités vendues et les revenus d’un produit dans un magasin comme le mois et l’année de la vente. Le groupe de produits (fruits, lait, céréales ou crème glacée) est également inclus. Vous souhaitez voir une ventilation des ventes au cours de chaque année de chaque groupe de produits et de produits dans chaque magasin. Vous aimeriez également pouvoir montrer cette répartition au cours de n’importe quel sous-ensemble de mois dans une année donnée (par exemple, quelles étaient les ventes de janvier à juin).

FIGURE 1 Voici les données de l’exemple de tableau croisé dynamique d’épicerie.

Avant de créer un tableau croisé dynamique, vous devez avoir des en-têtes dans la première ligne de vos données. Notez que les données d’épicerie contiennent des en-têtes (année, mois, magasin, groupe, produit, unités et revenus) sur la ligne 2. Placez votre curseur n’importe où dans les données, puis cliquez sur Tableau croisé dynamique dans le groupe Tableaux de l’onglet Insertion. Excel ouvre la boîte de dialogue Créer un tableau croisé dynamique, illustrée à la figure 2, et fait une hypothèse sur votre plage de données. (Dans ce cas, Excel a correctement supposé que la plage de données était A1: G923.) En sélectionnant Utiliser une source de données externe, vous pouvez également faire référence à une base de données en tant que source pour un tableau croisé dynamique. Le modèle de données, nouveau dans Excel , est abordé à l’artilcle 44, «Le modèle de données».

FIGURE 2 Il s’agit de la boîte de dialogue Créer un tableau croisé dynamique.

Après avoir cliqué sur OK, vous voyez la liste des champs de tableau croisé dynamique illustrée à la figure 3.

FIGURE 3 Il s’agit de la liste des champs de tableau croisé dynamique.

Vous remplissez la liste des champs de tableau croisé dynamique en faisant glisser des en-têtes ou des champs de tableau croisé dynamique dans les zones ou les zones. Cette étape est essentielle pour garantir que le tableau croisé dynamique résume et affiche les données comme vous le souhaitez. Les quatre zones sont les suivantes:

■■ Étiquettes de lignes Les champs glissés ici sont répertoriés sur le côté gauche du tableau dans l’ordre dans lequel ils sont ajoutés à la zone. Par exemple, vous pouvez faire glisser les champs Année, Groupe, Produit et Magasin, dans cet ordre, vers la zone Étiquettes de lignes. Cela oblige Excel à résumer les données d’abord par année, puis pour chaque groupe de produits au cours d’une année donnée, puis par produit au sein de chaque groupe et, enfin, chaque produit par magasin. À tout moment, vous pouvez faire glisser un champ vers une autre zone ou réorganiser les champs d’une zone en faisant glisser un champ vers le haut ou vers le bas dans une zone ou en cliquant sur la flèche à droite de l’étiquette du champ.

■■ Les étiquettes de colonne Les champs glissés ici ont leurs valeurs répertoriées dans la ligne supérieure du Tableau croisé dynamique. Pour commencer cet exemple, vous n’avez aucun champ dans la zone Étiquettes de colonne.

■■ Valeurs Les champs glissés ici sont résumés mathématiquement dans le tableau. Les unités et les revenus (dans cet ordre) ont été glissés dans cette zone. Excel essaie de deviner quel type de calcul vous souhaitez effectuer sur un champ. Dans cet exemple, Excel suppose que le chiffre d’affaires et les unités doivent être résumé, ce qui se trouve être correct. Si vous souhaitez modifier la méthode de calcul d’un champ de données en une moyenne, un nombre ou autre, cliquez sur le champ de données et choisissez Paramètres du champ de valeur. Un exemple d’utilisation de la commande Paramètres du champ de valeur est donné plus loin dans le chapitre. Si vous sélectionnez un champ numérique dans la liste des champs, le champ est automatiquement ajouté à la zone Valeurs et agrégé à l’aide de Somme. Si vous sélectionnez un champ non numérique dans la liste des champs, le champ est automatiquement placé dans la zone Lignes.

■■ Filtre de rapport Depuis Excel 2007, le filtre de rapport est le nouveau nom de l’ancienne zone Champ de page. Pour les champs glissés dans la zone Filtre de rapport, vous pouvez facilement sélectionner n’importe quel sous-ensemble des valeurs de champ afin que le tableau croisé dynamique affiche des calculs basés uniquement sur ce sous-ensemble. Dans cet exemple, Mois a été déplacé vers la zone Filtre de rapport. Vous pouvez facilement sélectionner n’importe quel sous-ensemble de mois, par exemple de janvier à juin, et les calculs sont basés uniquement sur ces mois.

Les champs de tableau croisé dynamique complétés sont illustrés à la figure 4. Le tableau croisé dynamique résultant est illustré à la figure 5 et dans la feuille de calcul Tous les champs de ligne du classeur . À la ligne 5, vous pouvez voir que 243228 unités ont été vendues pour 728218,68 $ en 2005.

astuce Voici quelques conseils pour naviguer entre les feuilles de calcul:

■■ La séquence de touches Ctrl + PageUp vous fait reculer d’une feuille de calcul.

■■ La séquence de touches Ctrl + PageDn vous fait avancer d’une feuille de calcul.

■■ Un clic droit sur l’une des flèches à gauche du premier nom de la feuille de calcul fait apparaître

liste des noms de feuille de calcul à partir desquels vous pouvez passer à n’importe quelle feuille de calcul du classeur.

Remarque Pour voir la liste des champs, vous devez être dans un champ du tableau croisé dynamique. Si vous ne voyez pas la liste des champs, cliquez avec le bouton droit sur une cellule du tableau croisé dynamique et sélectionnez Afficher la liste des champs.

FIGURE 4 Voici la liste complète des champs de tableau croisé dynamique.

FIGURE 5 Voici le tableau croisé dynamique d’épicerie sous forme compacte.

Quelles dispositions de tableau croisé dynamique sont disponibles?

La disposition du tableau croisé dynamique illustrée à la figure 5 est appelée la forme compacte. Dans le formulaire compact, les champs de ligne sont affichés les uns sur les autres. Pour changer la disposition, placez d’abord votre curseur  dans le tableau. Dans l’onglet Conception, dans le groupe Disposition, cliquez sur Disposition du rapport et choisissez l’une des options suivantes: Afficher sous forme compacte (voir Figure 5), Afficher sous forme hiérarchique (voir Figure 6 et la feuille de calcul Formulaire hiérarchique) ou Afficher sous forme tabulaire (Figure 7 et la feuille de calcul sous forme tabulaire).

FIGURE 6 Voici le formulaire de contour.

FIGURE 7 Il s’agit de la forme tabulaire.

Pourquoi un tableau croisé dynamique est-il appelé tableau croisé dynamique?

Vous pouvez facilement faire pivoter les champs d’une ligne vers une colonne et vice versa pour créer une mise en page différente. Par exemple, en faisant glisser le champ Année vers la zone Étiquettes de colonne, vous créez la disposition de tableau croisé dynamique illustrée à la figure 8. (Voir la feuille de calcul de la colonne Années.)

FIGURE 8 Le champ Années est pivoté vers le champ de colonne.

Comment modifier facilement le format d’un tableau croisé dynamique?

Si vous souhaitez modifier le format d’un champ de colonne entier, double-cliquez sur l’en-tête de colonne et cliquez sur Format de nombre dans la boîte de dialogue Paramètres du champ de valeur. Appliquez le format souhaité. Par exemple, dans la feuille de calcul Formatted , le champ Revenue est formaté en devise en double-cliquant sur Somme De la rubrique Chiffre d’affaires et en appliquant un format monétaire. Vous pouvez également modifier le format d’un champ de valeur en cliquant sur la flèche à droite du champ Valeur dans les champs de tableau croisé dynamique. Sélectionnez Paramètres du champ de valeur, puis Format du nombre, puis vous pouvez reformater la colonne comme vous le souhaitez.

À partir de n’importe quelle cellule d’un tableau croisé dynamique, vous pouvez sélectionner l’onglet Conception sur le ruban pour afficher de nombreux Styles de tableau croisé dynamique.

Comment puis-je réduire et développer des champs?

Développer et réduire des champs (une fonctionnalité introduite dans Excel ) est un grand avantage dans les tableaux croisés dynamiques. Dans la figure 5, vous voyez des signes moins (-) par année, groupe et produit. Cliquer sur le signe moins réduit un champ et change le signe en signe plus (+). Cliquez sur le signe plus pour agrandir le champ. Par exemple, si vous cliquez sur le signe moins par céréale dans n’importe quelle cellule de la colonne A, vous constatez que chaque année, les céréales sont contractées sur une seule ligne et que les différents magasins de céréales ne sont plus répertoriés. Voir figure 9 et la feuille de calcul Cerealcollapse. Un clic sur le signe plus dans la cellule A6 ramène la vue détaillée ou étendue répertoriant les ventes pour chaque céréale individuelle.

FIGURE 9 L’article céréalier est effondré.

Vous pouvez également développer ou contracter un champ entier. Accédez à n’importe quelle ligne contenant un membre de ce champ et sélectionnez Analyser les outils de tableau croisé dynamique sur le ruban. Dans le groupe Active Champ, cliquez sur le bouton vert Développer le champ (étiqueté avec un signe plus) ou le bouton rouge Champ de contrat (étiqueté avec un signe moins). (Voir figure 10.)

FIGURE 10 Affiche le bouton Développer le champ et Réduire le champ.

Par exemple, supposons que vous souhaitiez simplement voir uniquement les ventes par groupe de produits pour chaque année.

Sélectionnez une cellule contenant le nom d’un groupe (par exemple, A6), sélectionnez Options des outils de tableau croisé dynamique sur le ruban, puis cliquez sur le bouton Réduire le champ. Vous voyez le résultat illustré dans la figure 11 (la feuille de calcul Groupes réduite). La sélection du bouton Développer le champ vous ramène à la vue d’origine.

FIGURE 11 Le champ Groupe est réduit.

Comment trier et filtrer les champs de tableau croisé dynamique?

Dans la figure 5, les produits sont répertoriés par ordre alphabétique dans chaque groupe. Par exemple, Cheerios est le premier type de céréales répertorié. Si vous souhaitez que les produits soient répertoriés dans l’ordre alphabétique inversé, déplacez simplement le curseur vers n’importe quelle cellule contenant un produit (par exemple, A7 contenant Cheerios dans la feuille de calcul Tous les champs de ligne) et cliquez sur la flèche déroulante à droite de Labels de ligne. en A5. Vous voyez la liste des options de filtrage illustrées à la figure 12. La sélection de Trier Z à A comme indiqué dans la figure 12 afficherait Spécial K en premier pour les céréales, le lait entier en premier pour le lait, les prunes pour les fruits, etc.

Initialement, votre tableau croisé dynamique affiche d’abord les résultats de 2005, puis de 2006, puis de 2007. Si vous souhaitez voir les données de 2007 en premier, déplacez le curseur vers n’importe quelle cellule contenant une année (par exemple, A5) et choisissez Trier du plus grand au plus petit  les options disponibles.

Au bas de la boîte de dialogue des options de filtrage, vous pouvez également sélectionner n’importe quel sous-ensemble de produits à afficher. Vous voudrez peut-être d’abord effacer Sélectionner tout, puis sélectionner les produits que vous souhaitez afficher.

FIGURE 12 Voici les options de filtrage de tableau croisé dynamique pour le champ Produit.

Pour un autre exemple de filtrage, consultez la feuille de calcul Data dans le fichier Ptcustomers.xlsx, illustrée à la figure 13. Les données de la feuille de calcul contiennent le numéro de client, le montant payé et le trimestre de l’année au cours de laquelle le paiement a été reçu pour chaque transaction client. Après avoir fait glisser Customer vers la zone Ligne, Quarter vers la zone Colonne Labels et Payé vers la zone Values, le tableau croisé dynamique illustré à la figure 14 s’affiche. (Voir la feuille de calcul Ptable dans le fichier.)

FIGURE 13 Les données du tableau croisé dynamique client.

FIGURE 14 Il s’agit du tableau croisé dynamique client.

Naturellement, vous voudrez peut-être afficher une liste de vos 10 meilleurs clients. À  pour obtenir cette disposition, cliquez sur la flèche Etiquettes de lignes et sélectionnez Filtres de valeurs. Choisissez les 10 meilleurs éléments pour obtenir la disposition illustrée à la figure 15. (Voir la feuille de calcul des 10 meilleurs clients.) Bien sûr, en sélectionnant Effacer le filtre, vous pouvez revenir à la disposition d’origine.

 

FIGURE 15 Cette figure montre les 10 principaux clients.

Supposons que vous souhaitiez simplement voir les meilleurs clients qui génèrent 50% de vos revenus. Sélectionnez l’icône de filtrage des étiquettes de lignes, sélectionnez Filtres de valeurs et Top 10 et remplissez la boîte de dialogue comme illustré à la figure 16.

FIGURE 16 Configurez la boîte de dialogue Top 10 Filtrer pour afficher les clients générant 50% des revenus.

Le tableau croisé dynamique résultant se trouve dans la feuille de calcul de la moitié supérieure et illustré à la figure 17. Comme vous pouvez le voir, les 14 premiers clients génèrent un peu plus de la moitié des revenus.

FIGURE 17 Les principaux clients génèrent la moitié des revenus.

Supposons maintenant que vous souhaitiez trier vos clients en fonction des revenus du premier trimestre. (Voir la feuille de travail Trié q1.) Cliquez avec le bouton droit n’importe où dans la colonne Trimestre 1, pointez sur Trier, puis cliquez sur Trier du plus grand au plus petit. Le tableau croisé dynamique résultant est illustré à la figure 18. Notez que le client 11 a payé le plus au premier trimestre, le client 3 a payé le deuxième, et ainsi de suite.

FIGURE 18 Les clients sont triés en fonction des revenus du premier trimestre.

Comment puis-je résumer un tableau croisé dynamique à l’aide d’un graphique croisé dynamique?

Excel facilite la synthèse visuelle des tableaux croisés dynamiques à l’aide de graphiques croisés dynamiques. La clé pour disposer les données comme vous le souhaitez dans un graphique croisé dynamique consiste à trier les données et à réduire ou à développer les champs. Dans l’exemple d’épicerie, supposons que vous souhaitiez résumer la tendance dans le temps des ventes unitaires de chaque groupe alimentaire. (Voir la feuille de calcul du graphique 2 dans le fichier .) Vous devez déplacer le champ Année vers la zone Ligne et supprimer les revenus de la zone Valeurs. Vous devez également réduire l’ensemble du champ Groupe dans la zone Étiquettes de lignes et déplacer les groupes vers la zone Colonne. Vous êtes maintenant prêt à créer un graphique croisé dynamique.

Cliquez n’importe où dans le tableau et sélectionnez Graphique croisé dynamique dans l’onglet Analyser. Choisissez maintenant le type de graphique que vous souhaitez créer. La quatrième option de graphique linéaire, qui affiche le graphique illustré à la figure 19, a été choisie ici. Le graphique montre que les ventes de lait étaient les plus élevées en 2005 et les plus faibles en 2006.

FIGURE 19 Voici le graphique croisé dynamique de la tendance des ventes du groupe de base.

 

Comment puis-je utiliser la section Filtre de rapport du tableau croisé dynamique?

Rappelez-vous que Mois a été placé dans la section Filtre de rapport du tableau. Pour savoir comment utiliser un filtre de rapport, supposons que vous souhaitez résumer les ventes pour les mois de janvier à juin. En cliquant sur l’icône Filtre dans la cellule B2 de la feuille de calcul des 6 premiers mois, vous pouvez sélectionner janvier-juin. Il en résulte le tableau croisé dynamique illustré à la figure 20, qui résume le nombre d’unités vendues par produit, groupe et année pour les mois de janvier à juin.

FIGURE 20 Un tableau croisé dynamique résumant les ventes de janvier à juin.

Comment fonctionnent les trancheurs de tableau croisé dynamique?

Le problème avec un filtre de rapport est qu’un visualiseur du tableau croisé dynamique illustré à la figure 20 ne peut pas facilement voir que le tableau résume les ventes de janvier à juin. La fonctionnalité de découpage Excel (introduite dans Excel ) résout parfaitement ce problème. Pour créer un segment pour l’une des colonnes de données utilisées pour générer votre tableau croisé dynamique, placez votre curseur n’importe où dans le tableau croisé dynamique, puis cliquez sur Slicer dans l’onglet Insertion du ruban. Dans la feuille de calcul Segment du fichier S a été sélectionné dans le menu Insertion. Les champs Mois et Produit ont été sélectionnés pour créer des tranches pour Mois et Produit. À l’aide d’un segment donné, vous pouvez sélectionner n’importe quel sous-ensemble de valeurs possibles à utiliser dans la création de votre table. Dans le segment Mois, les mois de janvier à juin ont été sélectionnés (un à la fois tout en maintenant la touche Ctrl enfoncée). Rien n’a été fait pour la trancheuse de produits, les données sont donc basées sur les ventes de janvier à juin. Les trancheuses sont illustrées à la figure 21.

Vous pouvez également ajouter un segment pour un champ en cliquant avec le bouton droit sur le champ dans le volet Champs de tableau croisé dynamique et en sélectionnant Ajouter en tant que segment.

FIGURE 21 Voici un exemple de tranches pour les champs Mois et Produit.

Si vous sélectionnez une tranche, dans l’onglet du ruban Options des outils de tranche, vous voyez des options de mise en forme qui vous permettent de changer son apparence. Par exemple, vous pouvez modifier la hauteur et la largeur ainsi que le nombre de colonnes dans la tranche. Vous pouvez également redimensionner facilement une tranche en faisant glisser ses côtés ou ses coins.

Comment puis-je ajouter des lignes vides ou masquer des sous-totaux dans un tableau croisé dynamique?

Si vous souhaitez ajouter une ligne vide entre chaque élément groupé, sélectionnez Conception d’outils de tableau croisé dynamique sur le ruban, cliquez sur Lignes vides, puis sur Insérer une ligne vierge après chaque élément. Si tu veux

masquer les sous-totaux ou les totaux généraux, sélectionnez Conception d’outils de tableau croisé dynamique, puis sélectionnez Sous-totaux ou Totaux généraux. Après avoir ajouté des lignes vides et masqué tous les totaux, vous pouvez obtenir le tableau dans la feuille de calcul Lignes vides sans totaux du classeur Grocerypt.xlsx, illustrée à la figure 22. Après avoir cliqué avec le bouton droit dans une cellule de tableau croisé dynamique, vous pouvez sélectionner Options de tableau croisé dynamique pour ouvrir la boîte de dialogue Options de tableau croisé dynamique.

FIGURE 22 Ici  est le tableau croisé dynamique d’épicerie sans totaux.

 

Comment puis-je appliquer une mise en forme conditionnelle à un tableau croisé dynamique?

Supposons que vous souhaitiez appliquer des barres de données à la colonne Unités du tableau croisé dynamique d’épicerie. Un problème que vous rencontrerez est que les sous-totaux et les totaux généraux auront de grandes barres de données et rendront les autres barres de données plus petites qu’elles ne devraient l’être. Il est préférable que les barres de données s’appliquent à toutes les ventes de produits, et non aux sous-totaux et aux totaux généraux. (Voir la feuille de calcul du formulaire Conditionnelle du classeur.) Pour appliquer les barres de données uniquement aux ventes unitaires par produit, commencez par placer le curseur dans une cellule contenant des ventes unitaires pour un produit (par exemple, lait au chocolat en B8). Sous l’onglet Accueil, cliquez sur Conditionnel

Formatage suivi de barres de données, puis choisissez Plus de règles. Vous voyez la nouvelle règle de formatage de la boîte de dialogue illustrée à la figure 23.

 

FIGURE 23 Il s’agit de la boîte de dialogue Nouvelle règle de formatage pour l’utilisation de la mise en forme conditionnelle avec les tableaux croisés dynamiques.

En sélectionnant Toutes les cellules affichant les valeurs «Somme des unités» pour «Produit» et en sélectionnant $ B $ 8: $ B $ 227, vous pouvez vous assurer que les barres de données ne s’appliquent qu’aux cellules répertoriant les ventes unitaires de produits, comme vous pouvez le voir dans la feuille de calcul du formulaire Cond et la figure 24.

FIGURE 24 Cette figure montre les barres de données d’un tableau croisé dynamique.

Comment puis-je mettre à jour mes calculs lorsque j’ajoute de nouvelles données ?

Si les données de votre ensemble de lignes d’origine changent, vous pouvez mettre à jour votre tableau croisé dynamique pour inclure les modifications de données en cliquant avec le bouton droit sur le tableau et en sélectionnant Actualiser. Vous pouvez également sélectionner Actualiser dans l’onglet Analyser.

Si vous souhaitez que les données que vous ajoutez soient automatiquement incluses dans vos calculs de tableau croisé dynamique lorsque vous actualisez-le, vous devez définir votre ensemble de données d’origine sous forme de tableau en le sélectionnant avec Ctrl + T. (Voir l’article 25, «Tables», pour plus d’informations.)

Si vous souhaitez modifier la plage de données utilisée pour créer un tableau croisé dynamique, vous pouvez toujours sélectionner Modifier la source de données sous l’onglet Analyser. Vous pouvez également déplacer le tableau vers un autre emplacement en sélectionnant Déplacer le tableau croisé dynamique.

Je travaille pour une petite agence de voyages pour laquelle je dois envoyer en masse une brochure de voyage. Mes fonds sont limités, je veux donc envoyer la brochure aux personnes qui dépensent le plus en voyages. D’après des informations provenant d’un échantillon aléatoire de 925 personnes, je connais le sexe, l’âge et le montant que ces personnes ont dépensé en voyage l’année dernière. Comment puis-je utiliser ces données pour déterminer comment le sexe et l’âge influencent les dépenses de voyage d’une personne? Que puis-je conclure sur le type de personne à qui je dois envoyer la brochure?

Pour comprendre ces données, vous devez les ventiler comme suite:

■■ Montant moyen consacré aux voyages par sexe

■■ Montant moyen consacré aux voyages pour chaque tranche d’âge

■■ Montant moyen consacré aux voyages par sexe pour chaque tranche d’âge

Les données sont incluses dans la feuille de calcul Données du fichier et un exemple est affiché dans Figure 25. Par exemple, la première personne est un homme de 44 ans qui a dépensé 997 $ en voyages.

FIGURE 25 Il s’agit des données des agences de voyages indiquant l’âge, le sexe et le montant dépensé pour les voyages.

Tout d’abord, obtenez une ventilation des dépenses par sexe. Commencez par sélectionner Insérer un tableau croisé dynamique. Excel extrait la gamme A2: D927. Après avoir cliqué sur OK, placez le curseur dans le tableau afin que la liste des champs apparaisse. Prochain, faites glisser la colonne Sexe vers la zone Étiquettes de lignes et faites glisser Montant dépensé en voyage vers les valeurs zone. Il en résulte le tableau croisé dynamique illustré à la figure 26.

Vous pouvez dire à partir de la rubrique, Somme du montant dépensé en voyage, que vous résumez le montant total dépensé en voyage, mais vous voulez en fait le montant moyen dépensé en voyage par les hommes et les femmes. Pour calculer ces quantités, double-cliquez sur somme du montant dépensé en voyage  dans le tableau croisé dynamique (et non dans la liste des champs!), Puis sélectionnez  Moyenne dans la boîte de dialogue Value Field Settings, illustrée à la figure 27.

FIGURE 26 Ce tableau croisé dynamique résume le total des dépenses de voyage par sexe.

FIGURE 27 Vous pouvez sélectionner une fonction récapitulative différente dans la boîte de dialogue Paramètres du champ de valeur.

Vous voyez maintenant les résultats, illustrés à la figure 28.

FIGURE 28 Voici les dépenses de voyage moyennes par sexe.

En moyenne, les gens dépensent 908,13 $ en voyages. Les femmes dépensent en moyenne 901,16 $, tandis que les hommes dépensent 914,99 $. Ce tableau croisé dynamique indique que le sexe a peu d’influence sur la propension à voyager. En cliquant sur la flèche Étiquettes de lignes, vous pouvez filtrer pour afficher uniquement les résultats masculins ou féminins.

Maintenant, vous voulez voir comment l’âge influence les dépenses de voyage. Basculez le sexe vers la zone Colonnes du tableau croisé dynamique en faisant glisser le sexe de la zone Étiquettes de ligne vers la zone Étiquettes de colonne. Pour ventiler les dépenses par âge, faites glisser Age vers la zone Row. Le tableau croisé dynamique apparaît maintenant comme illustré à la figure 29.

FIGURE 29 Ce tableau croisé dynamique montre les dépenses de voyage moyennes par âge et sexe.

L’âge semble avoir peu d’effet sur les dépenses de voyage. En fait, ce tableau croisé dynamique est assez inutile dans son état actuel. Vous devez regrouper les données par âge pour voir les tendances. Pour regrouper les résultats par âge, cliquez avec le bouton droit n’importe où dans la colonne Âge et choisissez Groupe. Dans la boîte de dialogue Groupement (affichée  dans la figure 30), vous pouvez désigner l’intervalle de définition d’un groupe d’âge. En utilisant des incréments de 10 ans, vous obtenez le tableau croisé dynamique illustré à la figure 31.

En moyenne, les 25 à 34 ans dépensent 935,84 $ en voyages, les 55 à 64 ans dépensent 903,57 $ en voyages, etc. Ces informations sont plus utiles, mais elles indiquent tout de même que les personnes de tous âges ont tendance à dépenser environ le même montant en voyage. Cette vue des données ne permet pas de déterminer à qui envoyer votre brochure.

FIGURE 30 Utilisez la commande Grouper et afficher les détails pour regrouper les enregistrements détaillés.

Enfin, obtenez une ventilation des dépenses de voyage moyennes par âge pour les hommes et les femmes séparément. Tout vous devez faire glisser le sexe vers la zone Étiquettes de colonne de la liste des champs, ce qui entraîne le tableau croisé dynamique illustré à la figure 31. (Voir la feuille de calcul de la table finale.)

FIGURE 31 Il s’agit de la ventilation par âge et par sexe des dépenses de voyage.

Vous cuisinez maintenant! Vous pouvez voir qu’à mesure que l’âge augmente, les femmes dépensent plus pour les voyages et les hommes dépensent moins. Vous savez maintenant qui devrait recevoir la brochure: les femmes âgées et les hommes plus jeunes. Comme l’a dit un élève: «Ce serait une sorte de croisière!»

Un graphique fournit un joli résumé de cette analyse. Déplacez le curseur à l’intérieur du tableau croisé dynamique et, dans l’onglet Analyser du ruban, sélectionnez Graphique croisé dynamique. Un menu de graphiques recommandés apparaît. Le premier choix (Colonne en cluster) a été sélectionné. Le résultat est le graphique illustré à la figure 32. Si vous souhaitez modifier davantage le graphique, sélectionnez Outils de graphique croisé dynamique. Ensuite, par exemple, si vous choisissez Disposition, vous pouvez ajouter des titres au graphique et à l’axe et apporter d’autres modifications.

FIGURE 32 Il s’agit d’un graphique croisé dynamique pour la répartition des dépenses de voyage selon l’âge et le sexe.

Chaque groupe d’âge dépense environ la même chose pour les voyages, mais à mesure que l’âge augmente, les femmes dépensent plus que les hommes. (Si vous souhaitez utiliser un autre type de graphique, vous pouvez modifier le type de graphique en cliquant avec le bouton droit sur le graphique croisé dynamique, puis en choisissant le type de graphique.)

Notez que les barres montrant les dépenses des hommes diminuent avec l’âge, et les barres représentant le montant dépensé par les femmes augmentent avec l’âge. Vous pouvez voir pourquoi les tableaux croisés dynamiques qui affichent uniquement des données de sexe et d’âge n’ont pas réussi à démasquer ce modèle. Étant donné que la moitié de la population de votre échantillon est composée d’hommes et de femmes, vous avez constaté que le montant moyen dépensé par les personnes ne dépend pas de leur âge. (Notez que la hauteur moyenne des deux barres pour chaque âge est approximativement la même.) Vous avez également constaté que le montant moyen dépensé par les hommes et les femmes était approximativement le même. Vous pouvez le voir parce que, en moyenne sur tous les âges, les barres bleues et rouges ont des hauteurs approximativement égales. Trancher et découper les données simultanément selon l’âge et le sexe permet de mieux vous montrer les vraies informations.

Notez qu’en cliquant sur la flèche déroulante associée à A, vous pouvez filtrer le graphique sur l’âge et en cliquant sur la flèche déroulante associée à sexe, vous pouvez filtrer le graphique en fonction du sexe.

Je fais des études de marché sur les break. Je dois déterminer quels facteurs influencent la probabilité qu’une famille achète un break. À partir d’informations dans un grand échantillon des familles, je connais la taille de la famille (grande ou petite) et le revenu familial (élevé ou faible). Comment puis-je déterminer comment la taille de la famille et le revenu influencent la probabilité qu’une famille achète un break?

Dans le fichier , vous pouvez trouver les informations suivantes:

■■ La famille est-elle grande ou petite?

■■ Le revenu de la famille est-il élevé ou faible?

■■ La famille a-t-elle acheté un break? Oui ou non.

Un exemple des données est illustré à la figure 33 (voir la feuille de calcul des données). Par exemple, la première famille répertoriée est une petite famille à revenu élevé qui n’a pas acheté de break.

FIGURE 33 Il s’agit de données collectées sur le revenu, la taille de la famille et l’achat d’un break.

Vous voulez déterminer comment la taille de la famille et le revenu influencent la probabilité qu’une famille achète un break. L’astuce consiste à examiner comment le revenu affecte les achats pour chaque taille de famille et comment la taille de la famille affecte les achats pour chaque niveau de revenu.

Pour commencer, choisissez Insérer un tableau croisé dynamique, puis sélectionnez les données (la plage de cellules B2: D345). En utilisant Champs de tableau croisé dynamique, faites glisser Taille de la famille vers la zone Étiquettes de ligne, Station Wagon vers les étiquettes de colonne et l’un des trois champs de la zone Valeurs. Le résultat est le tableau croisé dynamique illustré à la figure 34. (Voir la feuille de calcul du 1er tableau.) Notez qu’Excel a choisi de résumer les données de manière appropriée en comptant le nombre d’observations dans chaque catégorie. Par exemple, 34 familles nombreuses à salaire élevé n’ont pas acheté de break, tandis que 100 familles nombreuses à salaire élevé en ont acheté un.

FIGURE 34 Il s’agit d’un résumé de la possession d’un break par taille de famille et salaire.

Vous souhaitez connaître pour chaque ligne du tableau croisé dynamique le pourcentage de familles ayant acheté un break. Pour afficher les données dans ce format, cliquez avec le bouton droit sur une cellule de valeur dans les données du tableau croisé dynamique, puis choisissez Paramètres de champ de valeur, ce qui ouvre les paramètres de champ de valeur d boîte de dialogue. Dans la boîte de dialogue, cliquez sur Afficher les valeurs sous, puis sélectionnez% de ligne dans la liste Afficher les données sous. Vous obtenez maintenant le tableau croisé dynamique illustré à la figure 35. (Voir la feuille de calcul de la répartition en 1er pourcentage.)

FIGURE 35 Il s’agit de la répartition en pourcentage de la possession d’un break par revenu pour les grandes et les petites familles.

De la figure 35, vous apprenez que pour les familles nombreuses et petites, le revenu a peu d’effet sur le fait que la famille achète ou non un break. Vous devez maintenant déterminer comment la taille de la famille affecte la propension à acheter un break pour les familles à revenu élevé et à faible revenu. Pour ce faire, déplacez le salaire au-dessus de la taille de la famille dans la zone des étiquettes de ligne, ce qui donne le tableau croisé dynamique illustré à la figure 36. (Voir la feuille de calcul Répartition en pourcentage final.)

FIGURE 36 Il s’agit de la répartition de la propriété d’un break par taille de famille pour les salaires élevés et faibles.

Ce tableau vous apprend que pour les familles à revenu élevé, une grande famille est beaucoup plus susceptible d’acheter un break qu’une petite famille. De même, pour les familles à faible revenu, une grande famille est également plus susceptible d’acheter un wagon qu’une petite famille. L’essentiel est que la taille de la famille a un effet beaucoup plus important sur la probabilité qu’une famille achète un break que le revenu.

Je travaille pour un fabricant qui vend des micropuces dans le monde entier. Je reçois des ventes mensuelles réelles et prévues pour le Canada, la France et les États-Unis pour la puce 1, la puce 2 et la puce 3. Je reçois également l’écart ou la différence entre les revenus réels et budgétés. Pour chaque mois et chaque combinaison de pays et de produit, je souhaite afficher les données suivantes: revenus réels, revenus budgétés, écart réel, revenus réels en pourcentage du revenu annuel et écart en pourcentage des revenus budgétés. Comment afficher ces informations?

Dans ce scénario, vous êtes un responsable financier pour un fabricant de micropuces. Vous vendez vos produits dans différents pays et régions et à différents moments. Les tableaux croisés dynamiques peuvent vous aider à résumer vos données dans un format facilement compréhensible.

Le fichier Ptableexample.xlsx inclut les ventes mensuelles réelles et prévues en 1997 de la puce 1, de la puce 2 et de la puce 3 au Canada, en France et aux États-Unis. Le fichier contient également l’écart ou la différence entre les revenus réels et les revenus budgétés. Un échantillon des données est illustré à la figure 37. (Voir la feuille de calcul Data.) Par exemple, aux États-Unis en janvier, les ventes de la puce 1 ont totalisé 4 000 $, bien que des ventes de 5 454 $ aient été prévues. Cela a donné un écart de – 1 454 $.

FIGURE 37 Les données des puces de différents pays pour différents mois montrent les revenus réels, budgétaires et d’écart.

Pour chaque mois et chaque combinaison de pays et de produit, vous souhaitez afficher les données suivantes:

■■ Revenus réels

■■ Revenus budgétés

■■ Écart réel

■■ Revenus réels en pourcentage des revenus annuels

■■ Écart en pourcentage des revenus budgétés

Pour commencer, sélectionnez une cellule dans la plage de données avec laquelle vous travaillez (n’oubliez pas que la première ligne doit inclure des en-têtes), puis choisissez Insérer un tableau croisé dynamique. Excel détermine automatiquement que vos données sont dans la plage A1: F208.

Par exemple, si vous faites glisser Mois vers la zone Lignes, Pays vers la zone Colonnes et Revenu vers la zone Valeurs, vous obtenez le revenu total chaque mois par pays. Un champ que vous ajoutez à la zone Filtres (Produit, par exemple) vous permet de filtrer votre tableau croisé dynamique en utilisant des valeurs dans ce champ. En ajoutant un produit à la zone Filtres, vous pouvez afficher les ventes de la puce 1 uniquement par mois pour chaque pays. Donné que vous souhaitez pouvoir afficher des données pour n’importe quelle combinaison de pays et de produit, vous devez ajouter Mois à la zone Lignes du tableau croisé dynamique et Pays et Produit à la zone Filtres. Ensuite, faites glisser Var, Revenue et Budget vers la zone Values. Vous avez maintenant créé le tableau croisé dynamique illustré à la figure 38. (Voir la feuille de calcul du 1er tableau.)

FIGURE 38 Il s’agit d’un résumé mensuel des revenus, du budget et de l’écart.

Par exemple, en janvier, les revenus totaux étaient de 87 534 $ et les ventes totales budgétées étaient de 91 831 $, de sorte que les ventes réelles ont été inférieures de 4 297 $ aux prévisions.

Vous souhaitez déterminer le pourcentage des revenus gagnés au cours de chaque mois. Encore une fois, faites glisser Revenue de la liste des champs vers la zone Valeurs du tableau croisé dynamique. Cliquez avec le bouton droit dans cette colonne de valeur, puis choisissez Paramètres du champ de valeur. Dans la boîte de dialogue Paramètres du champ de valeur, cliquez sur Afficher les valeurs sous. Dans la liste Show Values ​​As, sélectionnez%  de colonne et renommez ce champ en Somme de revenu 2 , comme illustré à la figure 39.

Vous obtenez maintenant le tableau croisé dynamique illustré à la figure 40. (Voir la feuille de calcul du 2e tableau.) Les ventes de janvier ont généré 8,53% des revenus. Les revenus totaux pour l’année se sont élevés à 1 026 278 $.

FIGURE 39 Créez le pourcentage mensuel de revenus annuels.

FIGURE 40 Il s’agit de la ventilation des revenus mensuels.

Au lieu d’utiliser les paramètres des champs de valeur pour modifier l’affichage d’un champ de données, vous pouvez modifier  la façon dont un champ Valeurs est affiché en cliquant avec le bouton droit sur une cellule de valeur et en sélectionnant Afficher Va lues As.

Qu’est-ce qu’un champ calculé?

Vous voulez maintenant déterminer pour chaque mois l’écart en pourcentage du total des ventes. Pour ce faire, vous pouvez créer un champ calculé. Sélectionnez une cellule n’importe où dans la zone de données du tableau croisé dynamique, puis choisissez Éléments et ensembles de champs dans l’onglet Analyser. Choisissez Champ calculé pour ouvrir la boîte de dialogue Insérer un champ calculé. Comme le montre la figure 41, entrez un nom pour votre champ, puis entrez votre formule. La formule de cet exemple est = Var / Budget. Vous pouvez saisir la formule vous-même ou utiliser la liste des champs et le bouton Insérer un champ pour ajouter un champ à la formule. Après avoir cliqué sur Ajouter, puis sur OK, vous voyez le tableau croisé dynamique illustré à la figure42. (Voir la feuille de calcul Field Calc du fichier )

FIGURE 41 Créez un champ calculé.

FIGURE 42 Ceci montre le tableau croisé dynamique avec un champ calculé pour le pourcentage de variance.

Ainsi, en janvier, les ventes ont été de 4,7% inférieures aux prévisions budgétaires. En ouvrant l’insert calculé Boîte de dialogue de champ à nouveau, vous pouvez modifier ou supprimer un champ calculé.

Comment puis-je utiliser un filtre de rapport ou un segment?

Par exemple, pour voir les ventes de la puce 2 en France, vous pouvez sélectionner les valeurs appropriées dans les champs Produit et Pays de la zone Filtres. Avec la puce 2 et la France sélectionnées, vous verriez le tableau croisé dynamique illustré à la figure 43. La figure 44 montre comment créer le même tableau avec des tranches .

FIGURE 43 Cette figure montre les ventes de la puce 2 en France.

Dans la feuille de calcul Slicers, Slicers a été utilisé pour créer la même table. Vous pouvez cliquer dans le tableau croisé dynamique et sélectionner Trancheurs dans l’onglet Insertion. Créez les tranches illustrées à la figure 44 en sélectionnant les champs Produit et Pays. Sélection de la puce 2 dans la tranche de produit et de la France dans le pays slicer fournit les calculs pertinents pour toutes les transactions impliquant la puce 2 en France. Comme cela a été Comme mentionné précédemment, vous pouvez également créer une tranche en cliquant avec le bouton droit sur le champ dans la liste Champs.

FIGURE 44 Cette figure montre les ventes de la puce 2 en France avec des trancheuses.

Comment puis-je regrouper des éléments dans un tableau croisé dynamique?

Souvent, vous souhaitez regrouper les en-têtes dans un tableau croisé dynamique. Par exemple, vous souhaiterez peut-être combiner les ventes de janvier à mars. Pour créer un groupe, sélectionnez les éléments que vous souhaitez grouper, cliquez avec le bouton droit sur la sélection, puis choisissez Grouper et afficher les détails, Grouper. Après avoir modifié le nom Groupe 1 en janvier-mars et supprimé Mois de la section Étiquettes de lignes de la boîte de dialogue Tableau croisé dynamique, vous obtenez le tableau croisé dynamique illustré à la figure 45.

FIGURE 45 Regroupement des éléments pour janvier, février et mars.

remarques sur le regroupement Voici quelques informations supplémentaires sur l’utilisation du regroupement dans Excel:

■■ Vous pouvez dissoudre un groupe en sélectionnant Grouper et afficher les détails, puis Dissocier.

■■ Vous pouvez grouper des sélections non adjacentes en maintenant la touche Ctrl enfoncée pendant que vous sélectionnez des lignes ou des colonnes non adjacentes.

■■ Avec des valeurs numériques ou des dates dans un champ de ligne, vous pouvez regrouper par nombre ou dates à intervalles arbitraires. Par exemple, vous pouvez créer des groupes pour les tranches d’âge, puis trouver le revenu moyen pour tous les 25 à 34 ans.

Qu’est-ce qu’un élément calculé?

Un élément calculé fonctionne exactement comme un champ calculé, sauf que vous créez une ligne plutôt qu’une colonne. Pour créer un élément calculé, vous devez sélectionner un élément dans la zone de ligne du tableau croisé dynamique, pas un élément dans le corps du tableau croisé dynamique. Ensuite, dans l’onglet Analyser, sélectionnez Champs Eléments et ensembles, suivi de Elément calculé.

Pour illustrer comment créer un élément calculé, consultez le fichier . Dans les données de la feuille de calcul (voir Figure 46), vous avez des ventes de différentes marques de voitures. Vous souhaitez résumer les ventes totales par pays de l’entreprise (Japon, Allemagne ou États-Unis).

FIGURE 46 Il s’agit des données pour créer un champ calculé.

Pour commencer, créez un tableau croisé dynamique répertoriant les ventes totales par pays (voir la feuille de calcul PT1). Faites glisser Marque vers Zone Lignes et Ventes à la zone Valeurs. Vous obtenez maintenant le tableau croisé dynamique illustré à la figure 47.

FIGURE 47 Le tableau croisé dynamique récapitule les ventes par marque.

Avant de créer un élément calculé, il est judicieux de masquer le grand total en sélectionnant les grands totaux dans l’onglet Conception et en sélectionnant Désactivé pour les lignes et les colonnes. Sinon, après avoir créé l’élément calculé, les grands totaux compteront deux fois chaque voiture.

Pour créer un élément calculé pour le Japon, placez votre curseur n’importe où dans la colonne Étiquettes de ligne du tableau croisé dynamique, puis sélectionnez Éléments et ensembles de champs dans l’onglet Analyser. Après avoir sélectionné l’élément calculé, remplissez la boîte de dialogue comme illustré à la figure 48 et sélectionnez OK.

FIGURE 48 Créez un élément calculé pour le Japon.

Cette boîte de dialogue crée un article calculé pour le Japon en additionnant les ventes Honda et Nissan.

De la même manière, vous pouvez créer des champs calculés qui résument les ventes en Allemagne et aux États-Unis.

États. Le tableau croisé dynamique résultant est dans la feuille de calcul Élément Calc et est illustré à la figure 49.

Si vous souhaitez supprimer un élément calculé ou un champ calculé, sélectionnez Élément calculé ou Calc Champ à partir d’éléments et d’ensembles de champs. Dans la section Nom de la boîte de dialogue, sélectionnez le champ ou l’élément que vous souhaitez supprimer et sélectionnez Supprimer.

FIGURE 49 Il s’agit d’une liste d’éléments calculés.

Si vous le souhaitez, vous pouvez masquer les ventes de chaque marque en filtrant les étiquettes des lignes et en sélectionnant juste le Japon, l’Allemagne et les États-Unis.

Voir le problème 11 dans la section «Problèmes» de ce chapitre pour un exemple de création d’un élément calculé. Dans l’exemple de tableau croisé dynamique de fabrication de puces, vous ne pouviez pas créer un élément calculé car vous disposiez de plusieurs copies du champ Revenu.

 

Qu’est-ce que le «descendre»?

“Exploration vers le bas” consiste à double-cliquer sur une cellule d’un tableau croisé dynamique pour afficher toutes les données détaillées résumées dans ce champ. Par exemple, un double-clic sur une entrée de mars dans le scénario de micropuce affiche les données liées aux ventes de mars.

Je dois souvent utiliser des données spécifiques dans un tableau croisé dynamique pour déterminer le profit, comme les ventes d’avril en France de la puce 1. Malheureusement, ces données se déplacent lorsque de nouveaux champs sont ajoutés à mon tableau croisé dynamique. Excel a-t-il une fonction qui me permet de toujours extraire les ventes de la puce 1 d’avril en France à partir du tableau croisé dynamique?

Oui, il existe une telle fonction. La fonction LIREDONNEESTABCROISDYNAMIQUE remplit la facture. Supposons que vous souhaitiez extraire les ventes de la puce 1 en France au cours du mois d’avril du tableau croisé dynamique contenu dans la feuille de calcul Données du fichier. (Voir figure 50.) La saisie de la formule LIREDONNEESTABCROISDYNAMIQUE (A4, «Avril_France_Puce_1_ Somme_revenues») dans la cellule E2 donne la valeur correcte (37 600 $) même si des produits, des pays et des mois supplémentaires sont ajoutés ultérieurement au tableau croisé dynamique. Vous pouvez également déterminer le chiffre d’affaires résultant en pointant vers la cellule contenant les ventes de la puce 1er avril en France (cellule D26).

Le premier argument de cette fonction se trouve dans la cellule dans le coin supérieur gauche du tableau croisé dynamique (cellule A4). Vous placez les en-têtes de tableau croisé dynamique entre guillemets (séparés par des espaces) qui définissent l’entrée souhaitée. La dernière entrée doit spécifier le champ de données, mais d’autres en-têtes peuvent être répertoriés dans n’importe quel ordre.

Ainsi, la formule signifie ici «Pour le tableau croisé dynamique dont le coin supérieur gauche est dans la cellule A4, recherchez la somme du chiffre d’affaires du Puce 1 en France courant avril. » Cette formule renvoie la réponse correcte même si les données de vente de la puce 1 en France en avril se déplacent vers un emplacement différent dans le tableau croisé dynamique.

FIGURE 50 Utilisez la fonction  LIREDONNEESTABCROISDYNAMIQUE pour localiser les ventes d’avril Puce 1 en France.

Si vous souhaitez simplement renvoyer le total des revenus (1030500 $), vous pouvez saisir le LIREDONNEESTABCROISDYNAMIQUE (A4, “Somme_revenues ”) (voir cellule F2).

Pour voir la véritable puissance de la fonction LIREDONNEESTABCROISDYNAMIQUE, supposons que vous souhaitiez résumer les ventes de chaque produit par pays pour chaque mois de l’année dans un joli tableau, comme le montre la figure 51.

FIGURE 51 Utilisez LIREDONNEESTABCROISDYNAMIQUE pour extraire les ventes d’avril de chaque produit dans chaque pays.

Pour commencer, créez une liste déroulante vous permettant de saisir le mois de l’année dans la cellule J9. Entrez les pays dans I13: I15 et les produits dans J12: L12. Dans la cellule J13, copiez votre formule LIREDONNEESTABCROISDYNAMIQUE précédente de E2 et modifiez-la pour devenir = SIERREUR (LIREDONNEESTABCROISDYNAMIQUE (“Somme de revenus”, $ A $ 4, “Mois”, $ J $ 9, “Produit”, J $ 12, “Pays”, $ I13 ), 0). Copie de cette formule de J13 à J13: L15 tire les ventes de chaque produit en avril du tableau croisé dynamique. Au fur et à mesure que vous copiez, le produit tire les changements et que vous copiez vers le bas, le pays tire les changements. Dans chaque cellule, le mois choisi est extrait de la cellule J9. L’utilisation de la fonction IFERROR garantit que si aucune vente d’un produit n’a eu lieu dans un pays au cours du mois sélectionné, vous retournez un 0 au lieu d’un message d’erreur. Imaginez à quel point cette astuce serait utile si vous vendiez 1000 produits dans 200 pays et régions!

Souvent, la fonction LIREDONNEESTABCROISDYNAMIQUE est une nuisance. Dans ces cas, vous pouvez désactiver l’option pour l’utiliser. Supposons que vous souhaitiez faire référence à des données dans les cellules B5: B11 d’un tableau croisé dynamique ailleurs dans votre classeur. Vous utiliseriez probablement la formule = B5 et la copieriez dans la plage B6: B11. Espérons que cela extrait B6, B7,. . . , B11 aux cellules souhaitées. Malheureusement, si l’option GETPIVOTDATA est active, vous obtenez un tas de fonctions LIREDONNEESTABCROISDYNAMIQUE qui se réfèrent à la même cellule. Si vous souhaitez désactiver LIREDONNEESTABCROISDYNAMIQUE, cliquez sur l’onglet Fichier, puis sur Options. Sélectionnez Formules et, sous Utilisation des formules, désactivez la case à cocher Fonction LIREDONNEESTABCROISDYNAMIQUE pour les références de tableau croisé dynamique. Cela garantit que cliquer à l’intérieur d’un tableau croisé dynamique donne une formule comme = B5 plutôt qu’une fonction GETPIVOTDATA. Vous pouvez également désactiver LIREDONNEESTABCROISDYNAMIQUE dans un tableau croisé dynamique particulier en sélectionnant Tableau croisé dynamique dans l’onglet Analyser. Sélectionnez Options et, dans la liste déroulante, désactivez Générer LIREDONNEESTABCROISDYNAMIQUE.

Enfin, notez que vous pouvez également combiner les fonctions EQUIV et DECALER (expliquées respectivement à l’article 4, «Fonction EQUIV» et à l’article 21, «La fonction DECLAER») pour extraire diverses entrées de tableau croisé dynamique.

Comment puis-je utiliser la nouvelle fonctionnalité de chronologie Excel  pour résumer les données sur différentes périodes?

Excel  présente une merveilleuse nouvelle fonctionnalité, Chronologie, qui vous permet de filtrer facilement votre tableau croisé dynamique en fonction des périodes. En supposant que vos données comportent une colonne contenant des dates réelles, vous pouvez sélectionner n’importe quel sous-ensemble d’années civiles, trimestres, mois ou jours consécutifs dans vos données, et la fonctionnalité Chronologie garantit que tous les calculs de tableau croisé dynamique incluent uniquement les lignes de feuille de calcul de la période sélectionnée.

Pour illustrer l’utilisation de la fonctionnalité Chronologie, consultez le .Dans les données de la feuille de calcul, les informations suivantes sont répertoriées pour 1900 transactions de maquillage: vendeur, produit, date, unités et revenus. Dans la feuille de calcul Tableau croisé dynamique, vous voyez un tableau croisé dynamique qui résume les ventes de chaque produit par chaque vendeur. Après avoir cliqué n’importe où dans le tableau croisé dynamique, cliquez sur l’onglet Insérer sur le ruban et sélectionnez Chronologie dans le groupe Filtres. Vous voyez la chronologie illustrée à la figure 52.

FIGURE 52 Voici la chronologie des données de maquillage.

En utilisant la touche Maj, vous pouvez sélectionner des trimestres adjacents qui peuvent être utilisés pour résumer les ventes. Par exemple, la feuille de calcul Chronologie (voir figure 53) résume les ventes de 2004 et des deux premiers trimestres de 2005. Un clic sur l’entonnoir restaure un tableau croisé dynamique basé sur toutes les données.

FIGURE 53 Cette chronologie résume les ventes de 2004 et des deux premiers trimestres de 2005.

 

Comment puis-je utiliser un tableau croisé dynamique pour résumer les ventes totales à ce jour au cours d’une année?

L’utilisation des paramètres de champ de valeur facilite la synthèse des ventes totales à ce jour au cours d’une année. Pour illustrer cela, consultez le fichier. La feuille de calcul Données contient le mois, l’année et le montant d’un certain nombre de transactions de vente. Pour commencer, dans la feuille de calcul Cumul annuel, résumez les ventes mensuelles de chaque année en faisant glisser Mois vers la zone Lignes, Année vers la zone Colonnes et Revenus vers la zone Valeurs. Cela donne le tableau croisé dynamique illustré à la figure 54.

FIGURE 54 Il s’agit d’un résumé des ventes par mois et par année.

Cette figure montre un résumé des ventes ventilé par mois et par année. De n’importe où dans le tableau croisé dynamique, cliquez avec le bouton droit de la souris et sélectionnez Paramètres du champ de valeur et Afficher les valeurs sous et choisissez Exécuter le total dans. Sélectionnez un mois. Le tableau croisé dynamique résultant, illustré à la figure 55 (voir la feuille de calcul depuis le début de l’année), montre les ventes pour chaque mois pour l’année donnée tout au long de ce mois. Par exemple, les ventes jusqu’en février 2010 étaient de 58 449 $. Vous pouvez également créer les totaux cumulés en cliquant avec le bouton droit sur une cellule de valeurs et en choisissant Afficher les valeurs sous.

FIGURE 55 Voici les totaux des ventes depuis le début de l’année.

Comment puis-je utiliser un tableau croisé dynamique pour résumer les ventes de ce mois par rapport au même mois un an plutôt?

Encore une fois, vous utiliserez les données du classeur mensuel. Dans la feuille de calcul Année précédente, créez un tableau croisé dynamique en faisant glisser Mois vers la zone Ligne, Année vers la zone Colonne et Ventes vers la zone Valeurs. Après avoir cliqué avec le bouton droit n’importe où dans le tableau croisé dynamique et sélectionné Paramètres de champ de valeur, le remplissage de la boîte de dialogue comme illustré à la figure 56 crée le tableau croisé dynamique illustré à la figure 57. Ici, vous voyez comment les ventes de chaque mois se comparent au même mois de l’année précédente. Par exemple, les ventes de janvier 2010 ont augmenté de 704,15% par rapport aux ventes de janvier 2009. Encore une fois, vous pouvez également modifier l’affichage des ventes des ventes réelles en comparant un mois au même mois de l’année précédente en cliquant avec le bouton droit sur une cellule de valeurs et en sélectionnant Afficher les valeurs sous.

FIGURE 56 Ce sont les paramètres nécessaires pour comparer un mois au même mois de l’année précédente.

FIGURE 57 Comparaison des ventes au même mois de l’année précédente.

Comment puis-je créer un tableau croisé dynamique basé sur des données à plusieurs endroits?

Souvent, les données nécessaires pour créer un tableau croisé dynamique peuvent se trouver dans différentes feuilles de calcul ou différents classeurs. La clé pour créer un tableau croisé dynamique à partir de données situées à différents emplacements consiste à appuyer sur ALT + D + P et à ouvrir l’assistant classique de tableau croisé dynamique et de graphique croisé dynamique, illustré à la figure 58.

FIGURE 58 Il s’agit de la page d’ouverture classique de l’Assistant Tableau croisé dynamique et graphique croisé dynamique.

Pour illustrer la façon de créer un tableau croisé dynamique basé sur des données dans différentes plages, ouvrez les fichiers Est et Ouest et, via View Arrange Tiled, affichez-les côte à côte, comme illustré à la figure 43-59. Ces données représentent les ventes de janvier, février et mars dans l’Est et l’Ouest. Vous souhaitez produire un tableau croisé dynamique qui résume les ventes totales de chaque produit au cours de chaque mois.

FIGURE 59 Deux fichiers seront résumés avec un tableau croisé dynamique.

Pour commencer, appuyez sur ALT + D + P et sélectionnez plusieurs plages de consolidation, comme illustré à la figure 58. Après avoir sélectionné Suivant, sélectionnez Créer un champ de page unique pour moi et, à l’étape 2b de l’Assistant Tableau croisé dynamique et graphique croisé dynamique, sélectionnez, comme illustré à la figure 60, les données de vente Est et cliquez sur Ajouter pour l’ajouter à la plage de données qui sera utilisé pour créer votre tableau croisé dynamique.

FIGURE 60 Ajoutez les données Est.

Effacez les données Est de la partie Plage de la page; sélectionnez les données Ouest et ajoutez ces données à la section Toutes les plages. Après avoir cliqué sur Suivant, vous pouvez décider de placer le dernier tableau croisé dynamique dans une nouvelle feuille de calcul ou la feuille de calcul actuelle. Une nouvelle feuille de calcul  abeille  n choisi ici. Après avoir sélectionné

Terminer, vous obtenez le tableau croisé dynamique (voir figure 61) dans la feuille de calcul PT du classeur .

FIGURE 61 Ce tableau croisé dynamique résume les ventes de l’Est et de l’Ouest.

Vous constatez, par exemple, que les ventes totales du produit A en février étaient de 1317. Vous pouvez filtrer les produits en sélectionnant la flèche déroulante dans la cellule A4 et filtrer les mois en utilisant la flèche déroulante dans la cellule B3. La flèche déroulante dans la cellule B1 vous permet de filtrer le tableau croisé dynamique afin que seules les données de vente Est ou Ouest soient utilisées. Actualiser met à jour le tableau croisé dynamique en fonction des modifications des données. Les segments et les chronologies ne fonctionnent pas avec un tableau croisé dynamique créé à partir de plusieurs plages.

Si vous n’aimez pas la combinaison ALT + D + P, vous pouvez ajouter l’Assistant Tableau croisé dynamique et graphique croisé dynamique à la barre d’outils Accès rapide en sélectionnant Fichier, Options, Barre d’outils Accès rapide et Commandes non sur le ruban, puis en sélectionnant Tableau croisé dynamique et Assistant Graphique croisé dynamique.

Pour créer un tableau croisé dynamique à partir de plusieurs plages, les en-têtes (dans ce cas, janvier, février et mars) dans chaque plage doivent être identiques. Le chapitre 44 présente une nouvelle fonctionnalité d’Excel , le modèle de données, qui vous permet de créer des tableaux croisés dynamiques même lorsqu’un en-tête d’une plage source n’apparaît dans aucune des autres plages source.

Comment puis-je créer un tableau croisé dynamique basé sur un tableau croisé dynamique déjà créé?

Souvent, vous souhaitez créer un tableau croisé dynamique basé sur un tableau croisé dynamique déjà créé. Cela vous permet d’afficher plusieurs tableaux croisés dynamiques basés sur les mêmes données. Par exemple, un résumé des ventes de maquillage avec le nom de chaque vendeur répertorié en baisse et le produit répertorié en travers a été créé dans le classeur  (comme illustré dans la figure 52). Supposons que vous souhaitiez également créer un tableau croisé dynamique avec le produit répertorié descendant et vendeur traversant. Avec le curseur dans la feuille de calcul de tableau croisé dynamique du classeur Makeuptimeline.xls, appuyez sur ALT + D + P pour ouvrir l’Assistant Tableau croisé dynamique et graphique croisé dynamique. Choisissez un autre rapport de tableau croisé dynamique ou de graphique croisé dynamique. Choisissez le tableau croisé dynamique à partir duquel vous souhaitez créer votre nouvelle table. Le tableau croisé dynamique d’origine a été choisi: tableau croisé dynamique 10. La liste des champs apparaît maintenant et vous pouvez créer un nouveau tableau croisé dynamique sans déranger votre ancienne table. Le produit a été placé dans la zone Ligne, Nom dans la zone Colonne et Dollars dans la zone Valeurs. Cela crée le tableau croisé dynamique illustré à la figure 62.

FIGURE 62 Voici un tableau croisé dynamique basé sur un autre tableau croisé dynamique.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x