Regrouper les données Excel pour les synthétiser

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


Sujets que vous pourriez aussi aimer :


Plans Excel pour synthétiser les données

Au travers de diverses formations, nous avons déjà abordé de nombreuses fonctionnalités et formules qui permettent de synthétiser les données denses, de façon à faciliter leur exploitation. Ici, nous abordons un nouvel outil, le plan intiment lié aux sous-totaux qui ont la capacité de réaliser des opérations sur les données filtrées.



L'objectif d'un plan Excel est de permettre de basculer facilement entre différentes vues d'un même tableau. Il peut s'agir d'une vue de synthèse, complètement repliée, n'offrant que les informations utiles à la compréhension et à l'interprétation des détails. Il peut s'agir d'une vue de détail, complètement déployée, destinée à isoler les anomalies ou les données impliquées.

Les données Excel à grouper
La création d'un plan Excel est justifiée lorsque les données sont denses et donc compliquées à interpréter rapidement mais aussi, lorsque ces données proposent des redondances que l'on peut recouper et grouper. Pour ce faire, nous proposons de récupérer un classeur source.

• Télécharger le classeur Excel grouper-et-synthetiser-donnees.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification si nécessaire,

Ce classeur est composé de deux feuilles. La deuxième feuille, active par défaut est la feuille nommée Synthèse. Elle représente les chiffres d'affaires réalisés par les commerciaux d'une entreprise au cours du premier semestre de l'année. Ce tableau est relativement long. Il est donc difficile de se faire une idée sur les performances de chacun. Mais des données peuvent être recoupées comme les mois de l'année ou encore les noms des commerciaux. En fonction de ces regroupements, des opérations de synthèse peuvent être réalisées.

L'autre feuille nommée Sorties est un extrait d'une base de données d'un site Web professionnel. Le tableau recense des idées de sorties selon leur activité mais aussi localisation (Départements et villes). Cette source de données est constituée de 997 enregistrements. Il s'agit donc d'un tableau difficile à interpréter rapidement. Comme les activités et départements peuvent être regroupés, des synthèses peuvent être réalisés afin de connaître facilement la proportion des idées selon l'activité et/ou le département par exemple.

Plan Manuel - Regrouper les données
Dans un premier temps, nous allons apprendre à créer des vues manuellement afin de maîtriser la technique des plans. Sur la feuille Synthèse, nous souhaitons regrouper les données par vendeur afin d'identifier les chiffres de chacun. La lecture des données et la comparaison des résultats s'en trouveront nettement simplifiées. Les données similaires peuvent être groupées dans la mesure où elles se situent les unes à la suite des autres. Nous devons donc commencer par les trier sur le champ correspondant.

• Cliquer sur l'onglet Synthèse en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer avec le bouton droit de la souris sur l'onglet de la feuille Synthèse,
• Dans le menu contextuel, choisir Déplacer ou copier,
• Dans la boîte de dialogue, cocher la case Créer une copie,
• Puis sélectionner (en dernier) dans la liste et valider par Ok,

Nous obtenons ainsi une copie strictement identique de la feuille, nommée Synthèse (2). Nous y reviendrons pour découvrir d'autres techniques de plans Excel.

• Cliquer de nouveau sur l'onglet Synthèse pour revenir sur sa feuille,
• Cliquer n'importe où dans le tableau, par exemple en cellule C8,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Trier et filtrer, cliquer sur le bouton Trier,
• Avec la liste déroulante de la boîte de dialogue, choisir le champ Vendeur,
• Puis, cliquer sur le bouton Ajouter un niveau en haut à gauche de la boîte de dialogue,
• Avec la liste déroulante de la seconde ligne, choisir le champ Date,
• Conserver les autres choix par défaut et valider par Ok,

Ainsi, en même temps que nous regroupons les vendeurs triés croissant sur leur nom, nous classons leurs chiffres triés croissant sur les dates.

• Cliquer avec le bouton droit de la souris sur l'étiquette de ligne 17,
• Dans le menu contextuel, choisir Insertion,

Nous ajoutons ainsi une ligne de séparation entre les vendeurs Céhef et Galls.

• De la même façon, ajouter une ligne de séparation entre les commerciaux Hamalibou et Galls (Ligne 32), puis entre les vendeurs Houda et Hamalibou (Ligne 45),
• Sélectionner toutes les lignes du vendeur Céhef par les étiquettes, soit de la ligne 5 à la ligne 16 incluses,
• Dans la section Plan du ruban Données, cliquer sur le bouton Grouper,

Vous notez l'apparition d'un symbole - dans la marge gauche de la feuille Excel. Si vous cliquez dessus, vous repliez l'affichage des lignes groupées. En d'autres termes, vous masquez tous les résultats du vendeur Céhef. Si vous cliquez sur le symbole transformé en +, vous déployez l'affichage et le détail du vendeur Céhef réapparaît.

• Sélectionner toutes les lignes du vendeur Galls par les étiquettes (18 à 31 incluses),
• Cliquer sur le bouton Grouper du ruban Données,
• Sélectionner toutes les lignes du vendeur Hamalibou par les étiquettes (33 à 44 incluses),
• Cliquer sur le bouton Grouper du ruban Données,
• Sélectionner toutes les lignes du vendeur Houda par les étiquettes (46 à 55 incluses),
• Cliquer sur le bouton Grouper du ruban Données,

Nous obtenons ainsi un niveau de regroupement par vendeur. Un bouton - permettant de replier l'affichage figure en regard de chaque commercial. De même, vous notez la présence des boutons 1 et 2 en haut de la marge gauche. Si vous cliquez sur le premier, vous repliez complètement l'affichage. Si vous cliquez sur le second, vous déployez complètement l'affichage.



Quoiqu'il en soit, nous pouvons accéder au niveau de détail d'un commercial indépendamment des autres. Nous simplifions la vue pour interpréter plus facilement les données. Nous avons créé un plan manuel. Mais les données, même résumées, sont d'autant plus efficaces qu'elles sont accompagnées de calculs de synthèse.

• Déployer totalement l'affichage en cliquant sur le bouton 2 en haut de la marge gauche,
• En ligne 57, créer une zone de synthèse pour calculer la somme des chiffres réalisés,
• En cellule D57, taper la formule suivante :

=SOMME(D5:D55)

Nous réalisons ainsi la somme de tous les chiffres d'affaires réalisés par les commerciaux. Comme nous l'avions déjà appris, ces formules de synthèse ont la capacité d'ignorer toutes les cellules qui ne sont pas numériques.

• Cliquer sur les symboles - des vendeurs Céhéf, Galls et Hamalibou,

Ainsi, nous ne conservons que les résultats du vendeur Houda. Mais comme vous le remarquez, le calcul de la somme ne s'adapte pas en cohérence. Elle affiche toujours le résultat de la somme des CA pour l'ensemble des vendeurs, bien que trois d'entre eux soient masqués.



Comme nous l'a appris la formation pour réaliser des calculs sur les cellules filtrées, la fonction Somme notamment, considère les cellules masquées dans l'addition. C'est la raison pour laquelle nous avions mis en oeuvre la fonction Sous.Total qui a la capacité d'ignorer les cellules masquées, dans le contexte des filtres automatiques. Une autre fonction Excel peut elle aussi réaliser de nombreux calculs en indiquant en paramètre, les données à ignorer dans le calcul. Il s'agit de la fonction Agregat dont la syntaxe est la suivante :

=Agregat(calcul_a_realiser ;donnees_a_ignorer ; plage_de_calcul)

• Cliquer dans la cellule D57 pour l'activer,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse, soit agregat(,

Comme l'illustre la capture ci-dessus, une liste des opérations disponibles apparaît. Chacune correspond à un numéro. Ainsi en inscrivant le chiffre 9, nous demandons à la fonction Agregat de réaliser la somme.

• Taper le chiffre 9 suivi d'un point-virgule (;) pour passer à l'argument des options,

Une nouvelle liste apparaît pour indiquer les données à ignorer dans le calcul précédemment défini. Chaque option est associée à un chiffre. Ainsi la valeur 5 consiste à ne pas considérer les lignes masquées dans l'opération.

• Taper le chiffre 5 suivi d'un point-virgule (;) pour passer à l'argument suivant,
• Puis, indiquer la plage de cellules correspondant à tous les commerciaux, soit D5:D55,
• Fermer la parenthèse de la fonction Agregat et valider la formule par la touche Entrée,

Le résultat obtenu est cette fois cohérent, affichant la somme des chiffres réalisés par le seul commercial encore visible dans le plan (228 600 Euros). Les lignes masquées sont ignorées, comme nous l'avons indiqué en deuxième paramètre de la fonction Agregat, grâce à la valeur 5.

• Cliquer sur le symbole - du vendeur Houda pour le masquer,

Comme plus aucune ligne n'est affichée, le résultat de la somme des chiffres affiche Zéro.

• Cliquer sur le premier symbole + afin de déployer les ventes du commercial Céhef,

Il est intéressant de constater que le résultat de synthèse fourni par la fonction Agregat s'adapte au contexte. Ainsi, associée à la vue du détail des chiffres pour le vendeur Céhef, la somme s'adapte pour livrer un résultat directement exploitable (240 900 Euros).

A tout moment le plan peut être effacé en cliquant sur le bouton Dissocier puis en choisissant la commande Effacer le plan.

Sous totaux et plan automatique
Précédemment, nous avons réuni les données manuellement. Nous avons réussi à leur appliquer des résultats de synthèse adaptés. Cette méthodologie est importante pour la compréhension du processus. Mais Excel propose bien entendu d'automatiser ces actions avec des fonctionnalités ajustées.

• Cliquer sur l'onglet Synthèse (2) en bas de la fenêtre Excel pour activer sa feuille,
• Puis, sélectionner l'une des cellules du tableau, par exemple D10,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Définir un ordre de tri croissant sur le vendeur puis sur la date comme précédemment,
• Ensuite, tout à fait à droite dans la section Plan, cliquer sur la flèche du bouton Grouper,
• Dans la liste, choisir Plan automatique,

Excel interprète les données du tableau et choisit lui-même de les grouper en colonnes cette fois. Les différentes vues du plan ainsi créé sont accessibles depuis la marge située au-dessus des étiquettes de lignes. Une seule colonne de valeurs numériques est conservée dans la vue groupée. Les colonnes Réalisé et Objectif, considérées comme accessoires sont masquées. Pourtant le champ qui nous intéresse est celui des chiffres réalisés.

Vous l'avez compris, lorsque le tableau ne propose pas une structure suffisamment simple et ordonnée, le plan automatique conduit à des résultats non maîtrisés. Mais il peut s'avérer utile dans certains contextes pour gagner du temps. Heureusement Excel propose une fonctionnalité, décomposée en étapes qui permet de gérer le plan à construire.

• Sélectionner de nouveau une cellule du tableau, par exemple D10,
• Cliquer sur la flèche du bouton Dissocier du ruban Données,
• Dans la liste, choisir Effacer le plan,
• Cliquer sur le bouton Sous-total situé juste en dessous dans le ruban,
• Dans la boîte de dialogue qui suit, décocher la case Ecart puis cocher la case Réalisé,
• Dans la première liste déroulante (A chaque changement de :), choisir Vendeur,
• Conserver la fonction Somme dans la liste déroulante du dessous,
• Enfin, cliquer sur le bouton Ok pour valider ces réglages,

Comme les données ont été judicieusement triées préalablement, nous demandons d'ajouter un calcul de synthèse à chaque changement de vendeur. Nous avons défini ce calcul sur la Somme. Mais vous l'avez constaté en déroulant la liste, d'autres opérations sont proposées. Au final nous obtenons bien un plan regroupant toutes les données par vendeur et offrant la synthèse des ventes réalisées par chacun, dans une ligne automatiquement ajoutée au tableau.



Trois vues sont désormais proposées comme en atteste la présence des boutons 1, 2 et 3 en haut de la marge gauche. La vue 3 est la vue de détail ne masquant aucune information. La vue 2 replie tous les groupes de vendeurs pour ne conserver que leur résultat de synthèse. Excel nous offre donc une vue synthétisée extrêmement simplifiée qui permet de prendre connaissance instantanément des résultats qui nous intéressent. Enfin, la vue 1, encore plus simplifiée, ne conserve que le résultat de synthèse résultant des quatre précédents sous totaux ajoutés au tableau.

• Cliquer sur le bouton 3 pour déployer complètement la vue,
• Cliquer sur le symbole - du vendeur Céhef pour le masquer,
• De la même façon, masquer Hamalibou et Houda pour ne conserver que le résultat de Galls,
• Sélectionner la cellule de synthèse du vendeur Galls, soit la cellule D32,

De cette façon, nous obtenons une vue épurée, focalisée sur les détails qui nous intéressent à l'instant t pour étudier les résultats. En consultant la barre de formule de la cellule D32, vous constatez qu'Excel exploite la fonction Sous.Total plutôt que la fonction Somme. Pourtant, avec l'argument 9 passé en premier paramètre, cette fonction réalise bien une addition sur la plage de cellules désignée (D18:D31), soit l'ensemble des ventes de ce commercial. Ici le résultat est le même quelque soit la fonction. Mais comme nous l'avons dit plus haut, la fonction Sous.Total a la capacité de ne pas inclure des cellules masquées par un filtre, s'il devait intervenir, contrairement à la fonction Somme.

Plan et synthèse d'une base de données
Nous proposons désormais d'exploiter nos connaissances afin de créer un plan sur la base de données de la feuille Sorties. Comme les informations sont nombreuses et difficilement exploitables, nous souhaitons regrouper les données afin d'afficher explicitement les informations de synthèse sur le nombre d'activités recensées par département. Nous aurons ainsi une idée intéressante sur la richesse de l'offre en fonction des secteurs. Vous l'avez compris, comme ce décompte doit se réaliser à chaque changement de département, nous devons préalablement effectuer un tri sur le champ correspondant. Tant qu'à faire, nous ajouterons un deuxième ordre de tri sur les Activités par souci de clarté.

• Cliquer sur l'onglet Sorties en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner l'une des cellules du tableau, par exemple B10, pour le désigner,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Cliquer sur le bouton Trier de la section Trier et filtrer du ruban,
• A l'aide de la première liste déroulante dans la boîte de dialogue, choisir le champ Département,
• Conserver le tri croissant sur cette colonne (De A à Z),
• Cliquer sur le bouton Ajouter un niveau en haut de la boîte de dialogue,
• A l'aide de la première liste déroulante de la seconde ligne, choisir le champ Activité,
• Conserver le tri croissant et cliquer sur le bouton Ok pour valider les réglages de tri,

La base de données présente désormais les informations regroupées par département et, pour chacun d'entre eux, les activités sont triées croissant sur leur nom.

• Cliquer sur le bouton Sous-total à droite dans le ruban Données,
• Dans la première liste déroulante de la boîte de dialogue, choisir le champ Département,
• Dans la seconde liste déroulante, conserver l'opération Nombre,

Il n'existe en effet aucune donnée numérique à sommer dans cette base de données. Nous souhaitons simplement comptabiliser les activités par département. La fonction Nombre permettra de les compter par groupe, soit par département.

• Dans la zone Ajouter un sous total à, décocher la case Miniature et cocher seulement la case Activité,

Ainsi nous afficherons les résultats du décompte dans la colonne située juste à côté de celle des départements.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider ces réglages,

Le plan est instantanément créé avec toutes les opérations de synthèse pour chaque département. Vous notez la présence des boutons indiquant les niveaux de regroupement, en haut de la marge gauche de la feuille Excel.

• Cliquer sur le bouton 2 pour afficher le niveau de regroupement intermédiaire,

Ce mode d'affichage replie le détail de chacun des groupes, soit de chacun des départements pour offrir la vue synthétisée des opérations effectuées.



Alors que le tableau d'origine était quasiment impossible à interpréter, les sous-totaux, en quelques clics, ont permis de révéler des tendances intéressantes. D'un seul coup d'oeil, nous constatons que certains départements sont dépourvus d'offres de sorties, comme la Gironde, tandis que d'autres en regorgent, comme le Rhône. Nous savons donc sur quels secteurs concentrer les efforts afin d'enrichir l'offre.

Si vous cliquez sur l'une des cellules de synthèse, par exemple la cellule C240 comme sur la capture, vous remarquez qu'Excel a de nouveau exploité la fonction Sous.Total mais cette fois avec l'argument 3, pour réaliser le décompte. En effet, la fonction NbVal permet à l'origine de compter toutes les cellules non vides. En l'occurrence dans notre contexte, il s'agit du nombre d'activités par département.

Nous aurions aussi pu choisir de comptabiliser les activités par catégories pour révéler le déficit d'offre dans certains cas. Pour ce faire, nous aurions réalisé un ordre de tri croissant en priorité sur les activités. Avec les sous-totaux, nous aurions alors lancé le décompte sur les idées à chaque changement d'activité.

Quoiqu'il en soit, ces plans Excel permettent de synthétiser considérablement les données afin de les interpréter facilement. De plus, ils offrent la possibilité de basculer simplement d'une vue de détail à une vue synthétisée sans modifier le tableau d'origine. Cette énième fonctionnalité de synthèse procure donc des avantages précieux, avec une ergonomie avérée pour traiter des informations denses.