formateur informatique

Tableaux croisés dynamiques d'Excel

Accueil  >  Bureautique  >  Excel  >  Excel Avancé  >  Tableaux croisés dynamiques d'Excel
Livres à télécharger


Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Inscription Newsletter    Abonner à Youtube    Vidéos astuces Instagram
Sujets que vous pourriez aussi aimer :


Tableaux croisés dynamiques

Dans cette formation, nous abordons les tableaux croisés dynamiques d'Excel. Cette fonctionnalité très puissante s'applique à des tableaux de données spécifiques. Ces données doivent présenter des redondances, des données répétitives à recouper. Il peut s'agir d'informations saisies ou récoltées au fur et à mesure et intégrées, de façon brute, dans un tableau. Par exemple, à chaque fois qu'un article est acheté dans un supermarché, il est scanné. A son code barre, sont attachées de nombreuses informations comme la date d'achat, le prix de vente, la ville d'achat du supermarché etc...

Bien sûr, cet article peut être acheté plusieurs fois le même mois, le même jour, dans le même supermarché. Mais chaque achat est saisi sur une ligne supplémentaire. A l'issue nous obtenons un tableau dense en informations, qu'il convient de synthétiser. L'objectif d'un tableau croisé dynamique, dans ce contexte, est de recouper ces informations pour fournir le bilan des ventes par article. Ainsi nous pouvons savoir d'un seul coup d'oeil quels sont les produits les mieux vendus, les supermarchés et les mois les plus rentables.

Tableaux à synthétiser - Données répétitives
Pour apprendre à exploiter les tableaux croisés dynamiques et réaliser les manipulations, nous partons d'un classeur existant. Données répétitives à recouper avec tableaux croisés dynamiques

Ce classeur est constitué de deux feuilles, une feuille Sorties et une feuille Ventes Trimestre. Le tableau de la feuille Ventes trimestre présente les ventes réalisées par des vendeurs au cours du premier trimestre de l'activité. Comme ces ventes ont été intégrées au fur et à mesure qu'elles ont été réalisées, on retrouve plusieurs fois le nom d'un même vendeur pour le même mois. Un tableau croisé dynamique doit synthétiser ces données pour fournir une interprétation explicite et immédiate. On pourra ainsi facilement savoir quel est le meilleur mois pour les ventes, quel est le vendeur qui a réalisé le meilleur chiffre, une fois toutes les données recoupées. Mais on pourra aussi savoir quelle est la personne qui a le mieux atteint les objectifs qui lui ont été fixés.

Le tableau de la feuille Sorties est très dense, 998 lignes. Aucune donnée numérique n'y figure. Il propose des idées de sorties répertoriées par activité, ville et département. L'objectif à l'issue, sera de connaître le type d'activité le plus représenté et dans quels départements l'offre est la plus importante. Bien sûr, à la lecture linéaire de ce tableau, il n'est pas possible de se faire une idée précise, sans une perte de temps considérable. Le tableau croisé dynamique aura pour objectif de croiser les informations similaires. Il devra donc recouper toutes les activités du même nom pour tous les départements identiques, et proposer leur dénombrement au centre du tableau résultant.

Recouper et dénombrer les informations de texte
Nous allons construire notre premier tableau croisé dynamique en nous basant sur les données de la feuille Sorties. Son objectif est de résumer les résultats des données répétitives. Les données répétitives sont les sorties et les départements. Il convient de les regrouper afin de les dénombrer pour les synthétiser et faciliter leur exploitation. Avant de commencer à créer un tableau croisé dynamique, il est nécessaire d'activer l'une des cellules, à l'intérieur du tableau. Ainsi vous aidez Excel à repérer les bornes du tableau qui doit être croisé et recoupé.
  • Cliquer dans l'une des cellules du tableau, par exemple B2, pour l'activer,
  • Cliquer sur l'onglet Insérer en haut de la fenêtre Excel, afin d'activer son ruban,
Créer un tableau croisé dynamique Excel avec ruban Insérer

Le groupe Tableaux du ruban Insérer propose trois boutons. Le bouton Tableaux permet simplement d'identifier les bornes d'un tableau sur la feuille de calcul afin d'automatiser certaines opérations, comme sa mise en forme, en lui appliquant des styles par exemple. Le bouton Tableaux croisés dynamiques propose des suggestions de tableaux synthétisant les données, selon la nature des données sources. Les suggestions évoluent en fonction des données à recouper. Au même titre que les graphiques, il s'agit de préférences de mise en forme et de structure, afin de faciliter la création du tableau croisé dynamique. Pour information, le support sur la création des graphiques dans Excel présente ces choix proposés par défaut pour aider à la conception. Enfin, le bouton Tableau croisé dynamique vous permet de définir vous-même la structure du tableau croisé, selon les données que vous souhaitez recouper et les informations que vous souhaitez synthétiser.
  • Cliquer sur le deuxième bouton, Tableaux croisés dynamiques,
Comme l'illustre la capture de la figure suivante, une boîte de dialogue apparaît instantanément. Grâce à la sélection préalable de l'une des cellules du tableau, Excel ne vous demande pas de confirmer ses bornes. Il analyse immédiatement la nature des données que vous lui proposez et vous suggère des tableaux croisés dynamiques simples et adaptés. Comme vous le constatez, les propositions sont pertinentes. Sa première suggestion consiste à construire un tableau permettant de regrouper toutes les activités répétitives afin de les dénombrer au centre. Sa seconde suggestion quant à elle, consiste à recouper les départements afin de dénombrer leur implication au centre. Les départements et les activités sont en effet les données les plus souvent répétées dans le tableau source. Un tableau croisé dynamique n'a de sens que s'il peut regrouper des données afin de vous offrir une vue synthétisée. Donc, son choix s'est porté sur ces deux colonnes. Comme il ne s'agit que de colonnes de texte, Excel vous propose une opération de synthèse dédiée, le dénombrement. C'est-à-dire qu'il va comptabiliser le nombre de fois que ces valeurs sont répétées afin de faire ressortir leur densité par rapport aux autres. Un graphique, construit à l'issue sur ce tableau croisé dynamique, pourrait permettre d'exploiter encore plus finement ces résultats.
Propositions automatiques de tableaux croisés dynamiques selon nature de la source de données

Comme ces suggestions sont trop simples, nous n'allons pas les accepter. Nous souhaitons en effet dénombrer les activités, mais en les recoupant par département. Nous avons donc deux niveaux de regroupement, alors que les suggestions de tableaux croisés dynamiques n'en proposaient qu'un seul.
  • Cliquer sur le bouton Tableau croisé dynamique vide en bas à gauche,
Vous basculez dans une nouvelle feuille en mode création manuelle du tableau croisé dynamique. C'est vous qui allez définir précisément les données à recouper et les opérations de synthèse à réaliser. Le fait de créer le tableau dans une nouvelle feuille facilite la conception des données à synthétiser. Si nous avions directement cliqué sur le premier bouton du ruban Insérer, Tableau croisé dynamique, nous aurions atteint cette étape, sans passer par les suggestions.
Conception des champs du tableau croisé dynamique Excel

Sur la partie gauche de la feuille, vous visualisez le tableau croisé dynamique vide en cours de conception. Le volet sur la droite, Champs de tableau croisé dynamique, vous permet de définir les champs à intégrer pour créer la synthèse. Ces champs doivent être insérés dans les zones situées en bas du volet, en fonction de la structure que vous souhaitez obtenir. Conventionnellement, les données textuelles doivent être placées en lignes et les données numériques à recouper ou sommer, en colonnes. La zone valeur correspond aux données sur lesquelles seront effectuées les opérations de synthèse, en fonction des informations recoupées en lignes et en colonnes. La zone Filtres permet d'ajouter un niveau supplémentaire de tri pour filtrer sur demande, les données déjà synthétisées, pour plus de précision.

Si vous cliquez sur un emplacement vide de la feuille, le volet Champs de tableau croisé dynamique disparaît. Si vous cliquez de nouveau dans la zone du tableau croisé dynamique en cours de construction, le volet réapparaît. Au fur et à mesure que nous allons ajouter les champs dans les zones dédiées, le tableau de synthèse se construira en temps réel sur la feuille.
  • Cocher la case du champ Département dans le volet de gauche,
Le champ, de type texte, est naturellement inséré par Excel, dans les données à synthétiser en lignes. Cette construction nous convient pour l'instant.
  • Cocher maintenant la case du champ Activité dans le volet de gauche,
Excel ajoute aussi cette valeur dans les données à recouper en lignes. Or nous souhaitons une synthèse des activités par département. Donc les activités doivent être placées en colonne. Ainsi au centre du tableau, nous pourrons comptabiliser pour chaque département en lignes, les activités regroupées en colonnes. Nous allons donc déplacer ce champ pour modifier la structure finale du tableau croisé dynamique.
  • Cliquer et glisser l'étiquette du champ Activité de la zone Lignes vers la zone Colonnes,
La structure du tableau croisé dynamique change instantanément au centre de la feuille. Vous constatez à ce stade que les départements sont en effet proposés en lignes et les activités, en colonnes. Plutôt que de cliquer et glisser l'étiquette du champ, nous aurions pu cliquer sur sa flèche dans sa zone, et choisir Déplacer dans la zone Etiquettes de colonnes ou de lignes (Cf. figure ci-dessous). Vous remarquez la présence de tris supplémentaires proposés par défaut, sur le tableau croisé dynamique, pour les champs placés en lignes et en colonnes. Ainsi, en plus des recoupements effectués par le tableau croisé dynamique, vous pourrez isoler temporairement une ou des activités, pour un ou des départements. De la sorte, vous ferez ressortir spécifiquement, des données précises à analyser.
Modifier la structure du tableau croisé dynamique, déplacer en-tête champ

Avant même la fin de la construction du tableau croisé dynamique, et en plus des niveaux de recoupements déjà définis, il est possible d'ajouter un niveau de tri supplémentaire en amont. Nous pouvons en effet choisir les données à représenter dans les champs. Nous ne souhaitons pas visualiser la synthèse pour toutes les activités. Certaines nous intéressent plus que d'autres. Les champs que nous cochons dans la partie haute du volet, pour les ajouter à la structure, proposent des filtres accessibles par une flèche.
  • Cliquer sur la flèche du champ Activité,
  • Dans la liste, décocher toutes les activités sauf Discothèque, Hôtel, Hôtel/Restaurant, Loisir/Sport, Loisirs - enfants, Parc animalier, parc d'attraction, Restaurant, Vacances et Visite/Loisir,
Excel est donc capable d'identifier toutes les valeurs répétitives d'un champ. Il vous les propose alors en filtre supplémentaire et vous permet même de les trier en amont, afin de simplifier la vue synthétisée finale, ce que nous venons de faire.
Filtrer les données à synthétiser dans le tableau croisé dynamique Excel

Maintenant que nous avons indiqué à Excel quelles sont les données répétitives à recouper, nous devons lui préciser quelle est l'opération à réaliser et à afficher au centre. En l'occurrence, ici, il s'agit de compter ces activités par département. C'est l'identifiant de la société (ID) qui permet explicitement de comptabiliser chaque activité supplémentaire selon ces niveaux de recoupement.
  • Dans la partie supérieure du volet, cocher la case Id,
Comme les données à recouper en lignes et en colonnes ont déjà été définies, Excel propose naturellement ce champ, pour synthétiser les données au centre du tableau (Valeurs). L'opération qu'il suggère par défaut est la somme, confondant les données de ce champ avec des valeurs numériques. Or il s'agit d'identifiants à comptabiliser. Nous devons donc changer l'opération proposée par défaut.
  • Cliquer sur la flèche du champ ainsi créé, Somme de Id, dans la zone Valeurs,
  • Dans la liste, choisir Paramètres des champs de valeurs,
  • Dans la boîte de dialogue qui suit, sélectionner l'opération Nombre,
  • Valider ce changement d'opération par Ok,
Changer opération de synthèse du tableau croisé dynamique

Les résultats se modifient instantanément sur le tableau croisé dynamique de la feuille. Les types de calculs comme la somme, la moyenne ou encore la recherche du maximum et du minimum, sont tout à fait adaptés pour des champs numériques. Dans le cas de champs de type texte, comme ici, la seule opération possible, est le dénombrement (Nombre). Nous parviendrons ainsi à comptabiliser le nombre d'enseignes, regroupées par activités pour chaque département.

Améliorer la présentation des données synthétisées
Le tableau croisé dynamique fournit une vue compactée, regroupée et synthétisée des données du tableau source. Selon les éléments de recoupement que nous avons choisis, il est désormais plus simple de connaître, les proportions d'enseignes par types d'activités et départements. Mais il est toujours utile de travailler la présentation afin d'améliorer la clarté des données. Lorsque le tableau croisé dynamique est activé, vous remarquez la présence de l'onglet Création, en haut de la fenêtre. Il s'agit d'un ruban qui lui est dédié afin de modifier son apparence et la disposition des données.
  • Cliquer sur le bouton Totaux généraux du groupe Disposition,
  • Dans la liste, choisir Désactivé pour les lignes et les colonnes,
La ligne et la colonne pour les totaux par département et activité disparaissent. Il s'agit d'un moyen rapide d'influer sur les options du tableau croisé dynamique. Nous avons besoin de ces données de synthèse, donc nous allons les replacer.
  • Cliquer de nouveau sur le bouton Totaux généraux,
  • Dans la liste, choisir Activé pour les lignes et les colonnes,
Les totaux réapparaissent en ligne et en colonne.
  • Sélectionner les données de la colonne Total général, colonne L,
  • Les formater en gras (CTRL + G),
Grâce à la synthèse du tableau croisé dynamique, on voit très rapidement que certaines activités sont très nettement représentées par rapport aux autres, comme Discothèque, Hôtel, Hôtel/Restaurant ou encore Restaurant. De même, certains départements proposent beaucoup plus d'activités que les autres comme le 83, le 69, le 38 ou encore le 07. Ces premières conclusions n'auraient pas pu être tirées aussi simplement avec le tableau d'origine.
  • Cliquer sur le bouton Disposition du rapport dans le ruban Création,
  • Dans la liste, choisir Afficher sous forme tabulaire,
Le quadrillage du tableau réapparaît au profit de la couleur de remplissage. Il s'agit d'un choix de présentation des données. Dans le groupe Options de style de tableau croisé dynamique du ruban, vous pouvez décocher les cases En-têtes de lignes et En-têtes de colonnes. Mais la mise en forme qui permet de faire ressortir explicitement ces titres, disparaîtra. Les cases Lignes à bandes et Colonnes à bandes permettent d'appliquer une couleur alternée aux cellules, afin de faciliter leur lecture. Là aussi, il s'agit d'un choix personnel de mise en forme. Et puis, il est possible d'appliquer des choix généraux prédéfinis. Ainsi, tout le tableau est affecté et vous obtenez rapidement une présentation satisfaisante. Le groupe Styles de tableau croisé dynamique propose un visuel de chacune de ces préférences. Au survol de chacun, un aperçu temporaire est appliqué directement à votre tableau sur la feuille.
  • Cliquer sur le Style de tableau croisé dynamique moyen 7 pour l'appliquer,
Appliquer un style de mise en forme au tableau croisé dynamique Excel

Afin d'améliorer encore l'interprétation du tableau, nous allons appliquer une mise en forme conditionnelle. Nous souhaitons un format dynamique qui permette de faire ressortir pour chaque département, l'activité la plus représentée. Le support de formation Excel sur les techniques avancées de mise en forme aborde en détail la mise en forme conditionnelle. Pour réaliser un tel format dynamique, nous devons créer une règle spécifique. Par le biais d'une formule, cette règle doit mettre en valeur, pour chaque colonne, la donnée la plus grande dans sa colonne. Nous aurons donc besoin de la fonction Max().
  • Sélectionner toutes les données du tableau croisé dynamique, sans les totaux, soit B5:K21,
  • Cliquer sur l'onglet Accueil pour activer son ruban,
  • Cliquer sur le bouton Mise en forme conditionnelle du groupe Styles dans le ruban,
  • Dans la liste, choisir Nouvelle règle,
  • Dans la boîte de dialogue qui suit, sélectionner le dernier type de règle dans la section centrale : Utiliser une formule pour déterminer...,
  • Cliquer alors dans la zone de saisie de la formule juste en dessous,
  • Puis cliquer sur la première cellule de la plage sélectionnée pour la désigner, soit B5,
Les références de la cellule apparaissent encadrées de dollars : $B$5. Cela signifie qu'elle est figée. Le support de formation pour reproduire un calcul partout dans Excel, aborde en profondeur cette notion de références absolues. Or ici, toutes les cellules doivent être passées en revue pour comparer chacune d'elles à toutes les autres valeurs de sa colonne. Elle ne doit donc pas être figée.
  • Enfoncer trois fois consécutivement la touche F4 du clavier,
Vous remarquez que les dollars se déplacent jusqu'à disparaître. La cellule est ainsi libre d'être bougée. On parle cette fois de références relatives. Il s'agit maintenant de la comparer avec toutes les autres cellules de la même colonne.
  • Poursuivre la saisie de la formule en tapant : =max(,
  • Puis, sélectionner toutes les valeurs de la première colonne : B5:B21,
Les références de la plage de cellules s'inscrivent instantanément après la parenthèse ouvrante de la fonction. Là encore, ces références sont figées. Certes, cette plage doit rester figée en ligne. Au-delà de la ligne 21, il s'agit des totaux. Mais elle doit pouvoir se déplacer en colonne afin que chaque cellule des colonnes suivantes puisse être comparée aux valeurs dans les colonnes correspondantes. Donc les dollars doivent être placés devant les indices de lignes seulement, pour que les colonnes puissent se déplacer en même temps que la cellule qui est comparée.
  • Enfoncer la touche F4 du clavier et fermer la parenthèse de la fonction,
Les dollars se placent seulement devant les indices de lignes. Il s'agit maintenant d'indiquer quelle mise en forme doit être appliquée aux cellules satisfaisant la règle.
  • Cliquer sur le bouton Format en bas de la boîte de dialogue,
  • Dans la boîte de dialogue qui suit, choisir un style gras pour la police avec une couleur rouge,
  • Valider ces attributs par Ok, pour les deux boîtes de dialogue,
Format conditionnel sur tableau croisé dynamique Excel

Chaque valeur la plus grande dans sa colonne apparaît ainsi dans une mise en forme explicitement différente des autres. Le format conditionnel associé à la synthèse du tableau croisé dynamique permet une lecture et une compréhension rapides des résultats.

Filtrer les données recoupées
Pour une exploitation plus fine, Excel propose des outils d'analyse. Il est ainsi possible d'ajouter des segments pour filtrer encore plus précisément les données des tableaux croisés dynamiques. Vous pouvez alors consulter les résultats sur des portions précises (Activités et départements) indépendamment des autres, pour plus de clarté.
  • Cliquer avec le bouton droit de la souris n'importe où dans le tableau croisé dynamique,
  • Dans le menu contextuel, choisir Masquer la liste des champs,
Vous masquez ainsi le volet Champs de tableau croisé dynamique afin d'optimiser l'espace. Vous auriez aussi pu cliquer sur sa croix en haut à droite du volet. L'une des cellules du tableau croisé dynamique étant toujours activée :
  • Cliquer sur l'onglet Analyse en haut de la fenêtre pour activer son ruban,
Ce ruban propose des fonctionnalités dont certaines sont disponibles par clic droit sur le tableau croisé dynamique. Ainsi il est possible d'afficher la liste des champs, soit le volet que nous venons de masquer. Mais il est aussi possible d'actualiser le tableau. Cela signifie que toutes les données de synthèse seront recalculées et mises à jour, si les valeurs du tableau source ont évolué. C'est pour cela qu'on parle de tableau croisé dynamique. Croisé signifie que les données sont recoupées pour être synthétisées. Dynamique signifie que les résultats restent liés à la source d'origine.
  • Cliquer sur le bouton Insérer un segment du groupe Filtrer dans le ruban,
  • Cocher les champs Département et activité puis cliquer sur Ok,
  • Dans le segment Activité, cliquer sur Hôtel,
  • Puis en maintenant la touche CTRL enfoncée, cliquer sur Hôtel/Restaurant et Restaurant,
  • De la même façon, sélectionner les départements 06, 07, 26 et 83 dans le segment Département,
Filtrer données tableau croisé dynamique avec segments pour analyse

Instantanément, les données sont filtrées sur la feuille. Le tableau croisé dynamique propose une vue extrêmement synthétisée pour une analyse précise et spécifique des résultats recoupés. Pour effacer ces filtres, il suffit de cliquer sur le petit bouton de filtre en haut à droite de chaque segment. Pour masquer les segments, il suffit de réaliser un clic droit sur chacun d'entre eux et de choisir Supprimer dans le menu contextuel.

Croiser et synthétiser des données numériques - Chiffres d'affaires
Dans cette seconde partie, nous allons croiser des données sur des chiffres d'affaires. Il s'agit d'obtenir la synthèse des résultats obtenus par vendeur au cours d'un trimestre d'activité.
  • Cliquer sur l'onglet Ventes Trimestre pour activer sa feuille,
Ventes et chiffres affaires à synthétiser dans tableau croisé dynamique

Comme l'illustre l'extrait de la capture ci-dessus, le tableau énumère les chiffres d'affaires réalisés par des vendeurs. Ils ont été enregistrés au fur et à mesure des ventes. C'est pourquoi, un même vendeur apparaît à plusieurs reprises, dès lors qu'il effectue une nouvelle vente. Ces chiffres sont comparés à des objectifs fixés. La dernière colonne du tableau, calcule donc le résultat en fonction de ces deux données, déterminant si l'objectif a été atteint ou non.

Mais avant de chercher à consolider ces données, nous devons ajouter les valeurs manquantes. La colonne Mois est en effet vide. L'objectif est de pouvoir y inscrire le mois en toutes lettres, correspondant à la date de la première colonne. Il existe la fonction Mois() d'Excel pour extraire le mois d'une date. Mais comme nous l'apprend le support de formation sur les dates dans Excel, la valeur retournée par cette fonction est numérique. Ainsi Février correspond à 2 et Juin à 6. Nous allons tout de même l'utiliser dans un premier temps et tenter de l'améliorer dans un second, afin d'obtenir la correspondance textuelle.
  • Sélectionner toutes les cellules de la colonne Mois, soit C6:C29,
  • Taper le symbole = pour débuter le calcul,
  • Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Mois(,
  • Sélectionner la première cellule de la date en colonne A, soit A6,
  • Fermer la parenthèse et valider le calcul par le raccourci CTRL + Entrée,
Comme le rappelle la formation Excel pour reproduire des calculs, le raccourci clavier CTRL + Entrée, permet de valider et répercuter une formule sur l'ensemble des cellules présélectionnées. Nous obtenons bien l'ensemble des numéros correspondant aux mois de l'année, en colonne C. Grâce à ce numéro, nous devons obtenir sa valeur en texte. Mais il n'existe pas de fonction Excel pour retourner un mois en texte, en fonction de son numéro. C'est pourquoi, il existe un petit tableau en colonnes J et K réalisant ce lien. Nous allons donc faire une recherche du mois obtenu en colonne C, dans ce petit tableau, pour récupérer son équivalent textuel. C'est la fonction RechercheV(), enseignée par le support de formation Excel pour extraire les informations d'un tableau, qui va nous le permettre. La fonction RechercheV requiert quatre arguments, dont l'un est facultatif. Sa syntaxe est la suivante :

=RechercheV(valeur_cherchee ;tableau_de_recherche ;colonne_retour ;faux)

valeur_cherchee est le numéro du mois que nous recherchons dans tableau_de_recherche. Une fois trouvée, nous demandons à la fonction Excel de retourner la valeur correspondante située dans le numéro de colonne du tableau, 2 pour la colonne 2. Il s'agit du mois en texte. Nous terminons avec l'argument facultatif Faux, pour indiquer à la recherche de ne pas tenter de se rapprocher de la valeur, si elle n'est pas trouvée.
  • Sélectionner de nouveau les résultats de la colonne C, soit C6:C29,
  • Enfoncer la touche F2 pour forcer la saisie de la première cellule de la plage,
  • Modifier la formule comme suit : =RECHERCHEV(MOIS(A6);$J$5:$K$17;2;FAUX),
  • Valider et reproduire le calcul à l'aide de la combinaison CTRL + Entrée,
RechercheV pour extraire mois à partir de dates Excel

En colonne C, nous obtenons bien les mois en textes, correspondant aux numéros des mois, rapatriés du tableau de recherche. Notez que nous avons figé la plage de cellule du tableau ($J$5:$K$17) en enfonçant la touche F4 du clavier, juste après l'avoir sélectionnée à la souris. C'est pourquoi les symboles $ encadrent les références des cellules de la plage. Si nous ne l'avions pas fait, les bornes du tableau se seraient déplacées vers le bas, pour suivre le déplacement du calcul, répercuté sur les lignes du dessous. Pour plus d'information sur les références absolues, vous pouvez consulter le support de formation Excel pour figer une cellule dans un calcul.

Maintenant que toutes les données du tableau sont présentes, nous allons pouvoir les croiser. Notre objectif est donc de consolider les résultats pour chaque vendeur et pour chaque mois du trimestre. Il s'agit de recouper les données sur le nom des vendeurs et sur chaque mois avec, au centre du tableau, la somme des écarts réalisés par rapports aux objectifs. Il paraît logique de placer le nom des vendeurs en ligne et celui des mois en colonne. Les mois sont moins nombreux que les vendeurs. Notamment pour des raisons de mise en page, il est plus simple de régler un tableau pour l'impression, lorsqu'il comporte plus de lignes que de colonnes.
  • Sélectionner n'importe quelle cellule du tableau, par exemple C6,
  • Cliquer sur l'onglet Insérer pour activer son ruban,
  • Cliquer sur le deuxième bouton du ruban, Tableaux croisés dynamiques,
Contrairement au cas précédent, comme notre tableau propose plus de données numériques, les suggestions de tableaux croisés dynamiques sont plus nombreuses. Ainsi Excel propose de faire la synthèse des chiffres réalisés par Vendeur ou par mois, mais aussi la synthèse sur les écarts et les objectifs. Dans tous les cas ces propositions restent basiques. Ces synthèses proposent de recouper les données numériques pour les mois ou pour les vendeurs. Or nous souhaitons croiser ces données par vendeur et par mois. Nous allons donc construire le tableau croisé dynamique manuellement.
  • Choisir Annuler pour fermer la boîte de dialogue,
  • Puis, cliquer sur le premier bouton du ruban, Tableau croisé dynamique,
Dans la boîte de dialogue qui suit, il ne reste qu'à confirmer les choix proposés par défaut. Comme nous avions sélectionné l'une des cellules du tableau source, Excel détecte parfaitement ses bornes. La case Nouvelle feuille de calcul est cochée par défaut. De cette façon, le tableau de synthèse sera construit sur une nouvelle feuille pour une meilleure lisibilité.
  • Valider ces choix par Ok,
Une nouvelle feuille est créée avec les outils de conception actifs pour bâtir le tableau croisé dynamique, permettant de synthétiser les résultats des ventes par vendeur et par mois.
  • Double cliquer sur l'onglet de la feuille, en bas de la fenêtre, pour activer la saisie de son nom,
  • Saisir Synthèse des ventes et valider par Entrée,
Le volet Champs de tableau croisé dynamique sur la droite, propose trois fois le champ Mois (Mois, Mois2 et Mois3). En effet, dans la feuille source, cette étiquette est désignée à trois reprises pour trois colonnes différentes. Celle qui nous intéresse est la deuxième, soit Mois2.
  • Cocher la case Mois2 dans le volet sur la droite,
Le champ est ajouté dans la zone Lignes en bas du volet. Et les trois mois du trimestre apparaissent en effet, dans le tableau croisé dynamique sur la feuille, énumérés chacun sur une ligne différente. Or nous souhaitons disposer les Vendeurs en lignes et les mois en colonnes.
  • Cliquer sur la flèche du champ Mois2 dans la zone Lignes,
  • Dans la liste, choisir Déplacer dans la zone Etiquettes de colonnes,
  • Cocher la case Vendeur,
Les informations du champ se placent naturellement en ligne.
  • Cocher la case Ecart dans le volet,
Comme il s'agit d'un champ numérique, Excel le place automatiquement dans la zone Valeurs en réalisant la somme sur les éléments recoupés, pour l'opération de synthèse. En quelques clics, le rapport du tableau croisé dynamique est prêt. Nous obtenons bien, pour chaque vendeur, le chiffre dégagé par mois, selon l'objectif fixé initialement. Bonne nouvelle pour l'entreprise, malgré quelques valeurs négatives, tous les totaux de l'exercice, par mois et par vendeur, sont positifs. Le vendeur Galls se démarque très nettement des autres avec un résultat très largement supérieur. Le mois de Mars est le meilleur de tous alors que le mois de Janvier est en très net recul par rapport aux deux autres. Voilà les premiers constats, riches en enseignements, que le tableau croisé dynamique permet de réaliser par une simple lecture. Ces conclusions auraient été plus difficiles et fastidieuses à atteindre depuis le tableau source, Ventes Trimestre. C'est donc tout l'intérêt d'un tableau croisé dynamique, qui comme vous le savez, reste lié à la source de données. C'est ce que nous allons démontrer.
Synthèse chiffres affaires et résultats recoupés dynamiquement
  • Cliquer sur l'onglet Ventes Trimestre en bas du classeur, pour activer sa feuille,
  • Sélectionner la cellule D10, pour le vendeur Houda en Janvier donc,
  • Saisir 32500 à la place de 26500 et valider par Entrée,
  • Revenir sur la feuille du tableau croisé dynamique, Synthèse des ventes,
Les données du rapport n'ont pas changé. Elles doivent être actualisées. Le ruban Analyse permet cette manipulation, comme nous l'avons vu dans le cas précédent. Mais il existe une autre méthode.
  • Cliquer avec le bouton droit de la souris dans l'une des cellules du tableau croisé dynamique,
  • Dans le menu contextuel, choisir Actualiser,
Les données se mettent à jour. Le vendeur Houda passe en deuxième position, et le chiffre final du mois de Janvier devient plus respectable. Il convient d'améliorer la présentation du tableau de synthèse. Une belle mise en valeur facilite toujours la lecture des données. Pour cela, comme nous l'avons déjà vu, le ruban Création pour les tableaux croisés dynamiques, propose des fonctionnalités. Mais avant cela, nous allons renommer les étiquettes de filtre du tableau. Elles prennent trop de place.
  • Sélectionner la cellule B3,
  • Remplacer le texte Étiquettes de colonnes par Filtre Mois,
  • Double cliquer à l'intersection des étiquettes de colonnes B et C en haut de la feuille Excel,
Le double clic à l'intersection des en-têtes de colonnes permet d'ajuster la largeur de cette dernière à son contenu. Comme nous avons réduit la taille de son texte le plus long, la colonne se raccourcit. Cette technique est l'une des astuces enseignées par le support de formation sur les trucs et astuces dans Excel.
  • Sélectionner la cellule A4,
  • Changer le texte Étiquettes de lignes par Filtre Vendeurs,
  • Double cliquer à l'intersection des étiquettes A et B pour ajuster la largeur de colonne au nouveau contenu,
  • Sélectionner la cellule A3,
  • Modifier le texte Somme de Ecart en Synthèse,
  • Activer le ruban Création du tableau croisé dynamique,
  • Appliquer le style de tableau croisé dynamique moyen 14 à l'aide de la liste visuelle des styles,
  • Cocher la case Lignes à bandes dans les options de style du ruban,
Faire ressortir les résultats de synthèse du tableau croisé dynamique Excel

Graphiques croisés dynamiques
Pour renforcer la synthèse des données recoupées par le tableau croisé dynamique, nous pouvons construire un graphique. Les résultats sont ainsi encore plus faciles à interpréter.
  • Sélectionner l'une des cellules du tableau croisé dynamique,
  • Activer le ruban Analyse en cliquant sur son onglet en haut à droite de la fenêtre,
  • Cliquer sur le bouton Graphique croisé dynamique,
La boîte de dialogue Insérer un graphique apparaît avec, sur la gauche, les types de graphiques disponibles. Nous pourrions choisir la représentation Secteurs afin de comparer les chiffres des vendeurs les uns par rapport aux autres. Mais nous obtiendrons les rapports seulement sur la vue de l'un des mois. C'est pourquoi nous allons choisir le type Histogramme, qui permet de comparer les valeurs entre elles, tout en donnant leur évolution au fil des mois, sur un second axe.
  • Dans la boîte de dialogue, sélectionner le type Histogramme,
  • Dans la partie droite de la boîte de dialogue, choisir la déclinaison Histogramme groupé 3D,
  • Valider ces choix par Ok,
Le graphique est déposé sur la feuille du tableau croisé dynamique. Vous notez l'apparition des rubans Format et Création pour le graphique. Vous pouvez le déplacer et le redimensionner à votre guise à l'aide de ses poignées d'angle.
  • Activer le ruban Création du graphique,
  • Dans la liste visuelle des styles du ruban, cliquer sur le Style 3 pour lui appliquer,
Les jeux de couleurs et préférences groupées de mise en forme sont instantanément appliqués à la représentation graphique. Nous pourrions très largement personnaliser chacun des éléments du graphique comme apprend à le faire, le support de formation Excel sur les graphiques. Mais ce n'est pas le but de la manoeuvre ici.

A la lecture du graphique, vous remarquez que le vendeur Galls est celui qui génère globalement le plus de chiffre. Cette lecture permet donc de recouper avec certitude l'interprétation que nous avions faite depuis le tableau croisé dynamique. De même, il apparaît que le vendeur Houda est le seul à avoir réalisé un gros chiffre pour le mois de Janvier. Cela est dû à la modification que nous avions réalisée dans le tableau source, pour tester l'actualisation des données du tableau croisé dynamique. C'est la légende, à droite du graphique, qui favorise cette lecture, par son jeu de couleurs. Comme il s'agit d'un graphique croisé dynamique, vous notez que les étiquettes Vendeur et Mois2 sont accompagnées de flèches. Il s'agit des données issues du tableau croisé dynamique qui peuvent être filtrées, pour étudier précisément certaines données sur une vue spécifique temporaire.
  • Cliquer sur la flèche de l'étiquette de légende Mois2 sur le graphique,
  • Dans la liste, décocher les cases Février et Mars,
Filtrer les données de tableau depuis étiquettes de graphique croisé dynamique

Instantanément, seules les données du mois de Janvier pour tous les vendeurs, sont représentées. Dans cette vue temporaire, l'écart réalisé par le vendeur Houda sur ce mois, est encore plus flagrant. Un graphique est toujours dynamiquement lié à sa source, donc si nous appliquons les filtres depuis le tableau, le graphique se met à jour.
  • Cliquer sur la flèche de la cellule Filtre Mois du tableau croisé dynamique,
  • Dans la liste, re-cocher les cases Février et Mars,
Le graphique croisé dynamique reprend instantanément sa représentation d'origine.
  • Cliquer sur la flèche de la cellule Filtre Vendeurs du tableau croisé dynamique,
  • Dans la liste, décocher les cases Céhef et Hamalibou,
Vous obtenez une représentation graphique qui permet de comparer les ventes des deux vendeurs Galls et Houda, sur l'ensemble des trois mois. Dans cette vue, il apparaît instantanément que le vendeur Galls est plus régulier et que Houda sauve son trimestre sur le mois de Janvier. Il existe d'autres façons de filtrer les données recoupées des tableaux croisés dynamiques, notamment avec les segments du ruban Analyse, comme nous l'avons appris dans le cas précédent. Mais lorsque les données s'y prêtent, une fonctionnalité, Chronologie, peut s'avérer précieuse pour interpréter les résultats. Ce filtre est forcément proposé lorsque des valeurs sont chronologiquement représentées sur des mois de l'année par exemple.
  • Cliquer sur la flèche de l'étiquette Vendeurs sur le graphique,
  • Dans la liste, cocher les cases Céhef et Hamalibou et valider par Ok,
  • Activer le ruban Analyse pour le tableau croisé dynamique,
  • Cliquer sur le bouton Insérer un segment,
  • Dans la boîte de dialogue qui suit, cocher la case Vendeur et valider par Ok,
Le segment Vendeur, listant chacun des vendeurs recensés dans le tableau croisé, apparaît sur la feuille.
  • Dans ce segment, cliquer sur Céhef,
  • Puis, tout en maintenant la touche CTRL enfoncée, cliquer sur Hamalibou,
Trier données synthétisées des CA avec les segments de tableau croisé dynamique

Instantanément, le tableau et le graphique croisés dynamiques sont filtrés sur ces deux vendeurs. Cette vue très synthétique, permet des exploitations rapides des données.
  • Cliquer sur le bouton Effacer le filtre en haut du segment pour retrouver les données d'origine,
  • Cliquer avec le bouton droit de la souris sur le segment,
  • Choisir Supprimer Vendeur dans la liste,
Le segment disparaît ainsi de l'espace de travail.
  • Sélectionner l'une des cellules du tableau croisé dynamique,
  • Activer le ruban Analyse,
  • Cliquer sur le bouton Insérer une chronologie,
  • Cocher la case Mois, la seule à être répertoriée,
Une représentation visuelle des mois de l'année apparaît dans une petite fenêtre flottante. Si vous cliquez sur l'un des mois, vous isolez les résultats sur ce mois pour l'ensemble des vendeurs, dans le tableau et le graphique croisés dynamiques. Bien sûr si vous cliquez sur un mois au-delà de Mars, toutes les représentations sont vides, faute de données.
  • Dans la chronologie, cliquer sur le segment du mois de Février,
  • Puis, tout en maintenant MAJ enfoncée, cliquer sur le segment du mois de Mars,
Vous isolez rapidement les résultats sur la période déterminée par la chronologie, aussi bien pour le tableau croisé dynamique que sur le graphique croisé dynamique.
Filtre chronologie sur données de synthèse du tableau croisé dynamique Excel

Vous notez l'apparition d'un ruban Options pour la chronologie qui permet de manipuler cette dernière, notamment au niveau de sa disposition et de sa mise en forme. Il n'y a pas que les filtres et la mise à jour des données sources qui influent sur les résultats dynamiques. L'opération de synthèse du tableau croisé dynamique, si elle est changée, influera sur les données liées, dont celles du graphique. Nous avions choisi la somme qui était proposée par défaut dans la zone Valeurs, lors de la construction du tableau croisé dynamique. Nous souhaitons afficher la moyenne en lieu et place. Pour ce faire :
  • Cliquer sur la flèche du champ Synthèse de la zone Valeurs du volet sur la droite,
  • Dans la liste, choisir Paramètres des champs de valeurs,
  • Dans la boîte de dialogue qui suit, sélectionner l'opération de synthèse Moyenne,
  • Valider ce choix par Ok,
Vous notez que les données de synthèse du tableau croisé dynamique changent. Elles affichent désormais les moyennes des ventes à la place des sommes. Le graphique croisé dynamique s'est lui aussi instantanément ajusté pour représenter ces moyennes, en adaptant l'échelle de son axe des ordonnées. Notez que pour ce changement, nous aurions aussi pu cliquer avec le bouton droit de la souris sur le tableau croisé dynamique. Il aurait alors fallu choisir Paramètres des champs de valeurs dans le menu contextuel.

Modifier la structure d'un tableau croisé dynamique
Comme vous l'avez remarqué, il est très simple de modifier les opérations de synthèse réalisées par un tableau croisé dynamique. Mais comme vous allez le voir, il est aussi très simple, de modifier l'organisation des données, pour offrir une représentation différente ou plus adaptée. Bref, les tableaux croisés dynamiques, malgré la complexité des recoupements et opérations qu'ils réalisent, restent très souples à manipuler.
  • Cliquer avec le bouton droit de la souris sur le tableau croisé dynamique,
  • Dans la liste, choisir Paramètres des champs de valeurs,
  • Dans la boîte de dialogue, sélectionner Somme et valider par Ok,
  • Dans la fenêtre chronologie, cliquer sur le bouton Effacer les filtres en haut à droite,
  • Puis, cliquer avec le bouton droit de la souris sur la chronologie,
  • Dans la liste, choisir Supprimer la chronologie,
Nous revoilà dans la configuration d'origine pour opérer les changements de structure du tableau croisé dynamique. Avec les mois de l'année, nous souhaitons ajouter la précision sur les dates exactes. Comme cette action va multiplier les colonnes, il est préférable d'inverser la disposition des données.
  • Dans le volet du tableau croisé dynamique, cliquer et glisser le champ Mois2 dans la zone Lignes,
  • De même, cliquer et glisser le champ Vendeur dans la zone Colonnes,
En deux clics, vous venez de modifier la structure du tableau croisé dynamique. Vous notez que le graphique s'est aussitôt adapté. Il a interverti les données de son axe des abscisses et de sa légende.
  • Dans le volet cocher la case Mois,
Le champ des dates s'intègre automatiquement dans la zone Lignes. Mais il amène avec lui le champ Mois3 qui ne nous intéresse pas.
  • Cliquer sur la flèche du champ Mois3 dans le volet,
  • Dans la liste, choisir Supprimer ce champ,
Modifier structure et présentation du tableau croisé dynamique Excel

Vous obtenez ainsi un tableau croisé dynamique qui offre toujours la synthèse par mois et vendeur, mais avec un niveau de précision supplémentaire sur les dates qui ont permis d'obtenir ces résultats. Le niveau de détail peut paraître trop important pour un tableau de synthèse. C'est pourquoi les tableaux croisés dynamiques offrent une autre option, afin de manipuler ces champs ensemble, tout en conservant une bonne lisibilité. Il s'agit de la zone Filtres, un tri supplémentaire qui chapote les autres.
  • Glisser le champ Mois2 dans la zone Filtres depuis le volet sur la droite,
Cette fois, le tableau croisé dynamique offre le détail pour chacune des ventes avec la somme générale par vendeur. Sa vue reste relativement complexe. Mais vous notez l'apparition d'une zone de filtre supplémentaire, sur le champ Mois2, au-dessus du tableau. Il s'agit d'une très bonne façon de scruter des résultats synthétisés sur une période spécifique.
  • Cliquer sur la flèche de ce nouveau filtre,
  • Dans la liste, décocher les cases Janvier et Mars puis valider par Ok,
Vous obtenez ainsi une vue filtrée et recoupée sur les résultats du mois de Février. Le graphique croisé dynamique en renfort, qui s'est lui aussi ajusté, montre très nettement et instantanément, que les journées du 21 et 22 Février sont largement meilleures que les autres. Voilà tout l'intérêt de ces outils croisés dynamiques. Ils permettent des recoupements pertinents pour des exploitations précises de résultats de synthèse.
Zone filtre croisé supplémentaire pour analyser résultats sur période restreinte
 
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales



Abonnement à la chaîne Youtube
Partager la formation
Partager sur Facebook
Partager sur Twitter
Partager sur LinkedIn