Regrouper et combiner des données dans un graphique Excel

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


Sujets que vous pourriez aussi aimer :


Combiner des données différentes sur un même graphique

Dans cette formation nous abordons un type de graphique particulier, les graphiques combinés d'Excel. Ces graphiques permettent de représenter sur une même vue, des données dont la nature et/ou l'échelle sont différentes. Ce type de graphique propose deux axes des ordonnées avec des échelles adaptées. Si bien que les valeurs aux échelles pourtant très différentes, apparaissent dans les mêmes proportions sur la même représentation graphique. Ces graphiques sont parfaits pour interpréter au plus juste des données recoupées et complémentaires.

Objectifs sur chiffres d'affaires - Interprétations croisées

• Télécharger le classeur combiner-sources-graphiques-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,
• Activer sa feuille Objectifs en cliquant sur son onglet en bas de la fenêtre Excel,

Il s'agit du classeur sur lequel nous avions commencé à travailler pour bâtir le deuxième volet de la formation sur les graphiques Excel. Le tableau de cette feuille présente les objectifs de chiffres d'affaire à réaliser au cours des différents mois avec en dessous, les chiffres d'affaire effectivement réalisés. Un calcul très simple, sur la ligne Ratio (/10), permet de constater rapidement si le CA réalisé a dépassé l'objectif posé (>10) ou non. Vous remarquez d'ailleurs la présence d'une mise en forme conditionnelle sur cette ligne, c'est-à-dire d'un format qui fait ressortir dynamiquement ces valeurs. La dernière ligne de ce tableau (Ratio (Obj.)) est un rappel du seuil à atteindre pour pouvoir représenter graphiquement cette ligne à franchir. L'objectif à l'issue, est de retourner une représentation graphique, incluant toutes ces valeurs pourtant très différentes. Le rendu doit fournir une vue particulièrement simple à interpréter, comme l'illustre la capture ci-dessous :


Grâce à la présence des deux axes des ordonnées (Axes verticaux), aux échelles adaptées, ce graphique est capable de regrouper et combiner ces données, très différentes sur une même vue. Si nous utilisons un graphique classique pour construire une représentation de l'ensemble de ces données, les valeurs des ratios ne seront pas visibles tellement elles sont faibles en comparaison des valeurs des chiffres d'affaires. D'ailleurs, nous allons faire le test.

• Sélectionner la ligne de titre du tableau, soit la plage de cellules B5:H5,
• Tout en maintenant la touche CTRL enfoncée, sélectionner les données à représenter, soit B7:H9,

Comme le rappelle le support de formation sur les raccourcis Excel, la touche CTRL permet de réaliser des sélections de plages de cellules qui ne se touchent pas. Ainsi nous allons pouvoir bâtir un graphique sur une multi-sélection. Nous avons inclus la ligne de titre dans la sélection, car c'est elle qui va servir à la construction de l'axe des abscisses pour représenter l'évolution au fil des mois.

• Dans le ruban Insérer, en haut de la fenêtre Excel, cliquer sur le bouton Graphiques recommandés,
• Dans l'onglet Graphiques recommandés de la boîte de dialogue, sélectionner la deuxième suggestion, le graphique en courbes, option Trait,
• Valider ce choix par Ok,

Le graphique est ainsi déposé sur la feuille. Pour l'illustration, nous avons volontairement modifié la couleur de la légende de la série Ratio (Obj.). La couleur grise proposée par défaut se confondait trop avec l'axe des abscisses.


En bleu, nous remarquons que les variations et évolutions des chiffres d'affaire sont correctement représentées, grâce à une échelle de l'axe des ordonnées qui leur est adaptée. En revanche, les deux séries des ratios, ne décollent pas et restent plaquées sur l'axe des abscisses à la valeur 0. En effet, l'ordre de grandeur de ces ratios est la dizaine tandis que celui des CA est le millier. Nous constatons donc qu'il n'est pas possible de combiner ces données très différentes sur la même vue d'un graphique classique.

Combiner les données et adapter les échelles
Pour éviter d'appliquer un facteur de multiplication à ces valeurs et les ramener dans le même ordre de grandeur, Excel propose fort heureusement, les graphiques combinés. Ils permettent de regrouper toutes ces données, sur une même vue, avec des échelles adaptées à leur ordre de grandeur.

• Sélectionner le graphique précédent et le supprimer en enfonçant la touche Suppr,
• Comme précédemment, sélectionner la ligne de titre du tableau (B5:H5), puis avec la touche CTRL, les données numériques (B7:H9),
• Dans le ruban Insérer, cliquer de nouveau sur le bouton Graphiques recommandés,
• Dans la boîte de dialogue qui suit, activer le deuxième onglet Tous les graphiques,
• Tout en bas de la liste, sélectionner la catégorie Graphique combiné,

L'aperçu proposé n'est pas encore correct. Nous devons apporter des précisions pour ce graphique avant de le valider. Notamment il s'agit d'indiquer à Excel que certaines données doivent être représentées sur un axe des ordonnées secondaire, à l'échelle adaptée. Les chiffres d'affaire sont déjà correctement représentés et liés à l'axe des ordonnées principal, représentant les ordres de grandeur en Euros.

• En bas de la boîte de dialogue, sélectionner le type de graphique Trait à l'aide de la liste déroulante, pour la série Ratio(/10) et cocher sa case Axe secondaire,
• Faire de même pour la série Ratio (Obj.),

Comme l'illustre la capture ci-dessus, les données des ratios apparaissent instantanément sur l'aperçu du graphique, dans les mêmes proportions que les CA, malgré leur différence d'ordre de grandeur. En effet, vous notez l'apparition sur la droite, d'un axe secondaire des ordonnées. Il s'est créé en calibrant sonéchelle sur la taille des données des ratios.

• Cliquer sur Ok pour valider la création du graphique combiné,

La grande force de ces graphiques combinés est de pouvoir recouper les observations grâce à des données regroupées sur une même vue. Si le graphique ne représentait que les chiffres d'affaire sous forme d'histogrammes (en bleu), nous risquerions de tirer des conclusions erronées. Ainsi, nous pourrions être satisfait de constater la nette reprise du chiffre d'affaire au mois d'Avril, deux fois supérieur à celui du mois de Mars. Pourtant le graphique combiné livre une toute autre interprétation, nous permettant de ne pas écarter le contexte ou la conjoncture de l'analyse. En effet, alors que le ratio en Orange est au-dessus de l'objectif à réaliser pour le mois de Mars, il plonge nettement en dessous de la barre grise pour le mois d'Avril. Cela signifie que le chiffre d'affaire attendu pour ce mois, aurait dû être encore supérieur. De fait la première conclusion satisfaisante, bâtie sur l'analyse d'une courbe isolée des CA, passe à décevante avec l'analyse croisée que permet le graphique combiné.

Mise en forme des graphiques combinés
Maintenant que le plus dur est fait et que le rendu livre une lecture efficace, il s'agit de personnaliser le graphique pour atteindre le résultat demandé par le modèle. Notamment, nous souhaitons donner une apparence 3D aux barres des histogrammes et courbes des ratios. Ce sont les volets, rubans et boutons contextuels qui fourmillent de fonctionnalités permettant ces réglages, comme le rappelle le support de formation sur la présentation des graphiques dans Excel.

• Cliquer sur le graphique pour le sélectionner, si ce n'est déjà fait,
• Lui appliquer un style de graphique sur fond noir (Style 6), à l'aide du ruban Création,
• Double cliquer sur les barres bleues des séries de l'histogramme,
• Dans le volet Mise enforme des séries qui apparaît, cliquer sur le petit bouton de menu Effets,
• Déployer la rubrique Format 3D,
• Cliquer sur le bouton Biseau supérieur et choisir l'option Cercle,
• Régler sa largeur ainsi que sa hauteur sur 4 pt,

• Cliquer sur le petit bouton de menu, Remplissage et ligne, en haut du volet,
• Déployer la section Remplissage et choisir une couleur de fond violette avec le pot de peinture,
• Déployer la section Bordure et choisir une couleur bleue foncée pour le contour,

Nous donnons ainsi l'illusion d'une légère ombre en arrière-plan des barres de l'histogramme. Pour la courbe en trait de la série Ration/10, nous devons choisir un autre type de représentation graphique, afin de pouvoir lui appliquer un format 3D.

• Cliquer sur la zone de graphique pour le sélectionner intégralement avec tous ses éléments,
• Dans le ruban Création, cliquer sur le bouton Modifier le type de graphique,
• En bas de la boîte de dialogue, remplacer le type Trait par Aire à l'aide de la liste déroulante de la série Ratio (/10),
• Valider cette modification par Ok,

Le type de graphique Aire remplit la surface qui part de l'axe des abscisses pour rejoindre les ordonnées de la série, référencées sur le graphique.

• Double cliquer sur cette Aire afin d'afficher les options dans son volet,
• Dans le volet, cliquer sur le petit bouton de menu Effets,
• Dans la section Format 3D, choisir un biseau supérieur Inclinaison douce avec pour largeur et hauteur, respectivement 18 pt et 14,5 pt par exemple,

• Cliquer ensuite sur le petit bouton de menu Remplissage et ligne du volet,
• Choisir un vert clair à l'aide du pot de peinture de la section Remplissage,
• Sélectionner le trait de la série Ratio (Obj.) sur le graphique,
• Lui appliquer une couleur rouge avec le pot de peinture de la section Ligne,

Au final, nous obtenons un graphique combiné semblable au modèle comme l'illustre la capture ci-dessous. Ici, nous avons néanmoins choisi un vert légèrement plus terne pour une représentation graphique plus sobre.


Variations des ventes - Recouper les analyses
Il s'agit ici de réaliser deux représentations graphiques, sur un même tableau, celui de la feuille Variations ventes, afin de bien comprendre la force et la puissance des graphiques combinés.

• Cliquer sur l'onglet Variations ventes pour activer sa feuille,

Ce tableau représente les ventes réalisées, entre les années 2008 et 2016, après avoir effectué des campagnes d'Emailing, tantôt en noir et blanc, tantôt en couleur. Les graphiques doivent nous permettre de mettre en évidence l'impact d'une stratégie marketing.

Au travers de l'un des exercices du support précédent sur l'exploitation des graphiques dans Excel, nous avions mis en évidence les limites de l'interprétation des données dans des cas spécifiques.


Nous avions tout d'abord bâti un graphique secteurs pour représenter les parts de charges consommées par différentes agences d'une entreprise. A la lecture de ce graphique, nous avions tiré une conclusion hâtive. Nous pensions qu'il fallait intervenir auprès de l'agence de Toulon pour endiguer ses fortes dépenses. La part de pourcentage en bleu, pour l'agence de Toulon, est en effet nettement supérieure aux autres. Mais en bâtissant un second graphique en courbes pour représenter l'évolution de ces charges au fil des années, nous avions révisé notre jugement. La courbe bleue, sur ce second graphique, montre en effet, que l'agence de Toulon semble réduire ses dépenses au fil des années pour au final parvenir à maîtriser ses charges. Nous comprenions alors que la forte part de pourcentage du premier graphique était essentiellement due aux fortes dépenses des premières années. Par contre le recoupement avec le second graphique avait permis de faire ressortir un autre problème. L'agence de Toulouse semble ne plus maîtriser ses charges en très nettes progressions ces dernières années.

Tout cela pour dire que sans une analyse croisée à l'aide de deux graphiques recoupés, nous aurions pu tirer de fausses interprétations et bâtir une mauvaise stratégie d'entreprise. C'est tout l'intérêt des graphiques combinés et c'est ce que nous allons encore une fois démontrer dans cet exercice. Un graphique combiné offre la possibilité de recouper l'analyse de données très différentes, avec des échelles adaptées par le biais d'un axe secondaire, pour disposer de tous les éléments nécessaires à l'interprétation, sur une même vue.

Nous allons commencer par bâtir un graphique classique, en forme de cônes 3D, pour représenter seulement l'évolution des ventes. Lorsque nous bâtirons ensuite un graphique combiné, permettant d'intégrer sur la même vue, les données des mailings envoyés, nous mettrons en lumière l'avantage indéniable que fournit l'analyse croisée des données. Ce graphique classique doit être construit sur des données de même nature donc, issue de trois plages de cellules différentes, non contiguës.

• Sélectionner la ligne de titre du tableau, B2:G2,
• Tout en maintenant la touche CTRL enfoncée, sélectionner la ligne des Ventes N.B., B4:G4,
• Toujours avec la touche CTRL, sélectionner la ligne des Ventes C, B6:G6,
• Dans le ruban Insérer, cliquer sur la flèche du bouton Histogramme,
• Dans la section Histogrammes 3D, choisir le premier, comme l'illustre la capture,

Le graphique se pose sur la feuille. La première conclusion que nous pouvons faire à la lecture de ce dernier est que les ventes en couleurs sont nettement supérieures à celles en noir et blanc. De plus, cette tendance semble s'accentuer au fil des années. Nous devons maintenant personnaliser ce graphique pour coller au modèle.

• Le sélectionner et le déplacer sous le tableau de données de la feuille,
• Lui appliquer un style de mise en forme sur fond noir (Style 9) à l'aide du ruban Création,
• Cliquer une première fois sur le titre du graphique pour activer son cadre,
• Cliquer une seconde fois à l'intérieur pour activer la saisie,
• Réaliser le raccourci CTRL + A pour sélectionner tout le texte dans son cadre,
• Saisir en lieu et place le titre : Evolution des ventes,

Nous devons maintenant positionner la légende à un endroit personnalisé sur le graphique. Mais avant cela, elle doit être disposée sur deux lignes et non sur une seule. Pour ce faire :

• Cliquer sur un emplacement vide du graphique pour le sélectionner,
• Cliquer ensuite sur sa première balise active en haut à droite, Elements de graphique,
• Dans la liste, pointer sur Légende et cliquer sur la flèche à son extrémité droite,
• Dans le sous menu qui apparaît, choisir la disposition Droite,

La légende est désormais bien écrite sur deux lignes. Nous devons la déplacer sur le graphique puis étirer ce dernier afin qu'il occupe la place gagnée.

• Cliquer une première fois sur la légende pour activer son cadre,
• Pointer la souris aux abords de son cadre et lorsque le pointeur se transforme en une flèche à quatre branches, cliquer et glisser la légende sur le graphique, comme l'illustre la capture,
• Cliquer ensuite aux abords du graphique de façon à activer le cadre de sa zone de traçage,
• Etirer ce dernier sur l'espace gagné en cliquant et glissant sa poignée d'angle supérieure droite,

Il s'agit maintenant de personnaliser l'apparence 3D et d'ajuster l'échelle de l'axe des ordonnées.

• Double cliquer sur l'une des barres de la série Ventes C pour afficher son volet sur la droite,
• Dans le menu, Options des séries du volet, cocher la case Cône complet pour changer la forme géométrique,
• Dans le menu Remplissage et ligne du volet, choisir une couleur de remplissage jaune orangée,
• Sélectionner maintenant les barres bleues des ventes N.B. directement sur le graphique,
• Leur appliquer une couleur de remplissage gris moyen,
• Puis leur attribuer la même forme géométrique Cône complet, toujours à l'aide du volet,

• Cliquer maintenant sur les valeurs de l'axe des ordonnées (Axe vertical),
• Dans la section Options d'axe du volet Format de l'axe, régler les limites maximum et minimum respectivement à 450 et 50,
• Saisir ensuite la valeur 100 dans l'unité principale,

En faisant correspondre les limites de l'échelle aux valeurs du tableau, nous optimisons la taille du graphique dans sa zone de traçage. En réglant la valeur de l'unité principale, nous modifions le pas de l'axe, pour fournir moins de détails et obtenir une vue plus sobre. Au final, nous obtenons un résultat identique à celui proposé par le modèle.


Analyses croisées des données
Il s'agit maintenant de construire le graphique combiné, en incluant sur une même vue, les données sur les nombres de mailings envoyés, sachant que leur ordre de grandeur est très différent de celui des ventes. Nous allons donc invoquer un axe secondaire des ordonnées qui adaptera son échelle sur ces ordres de grandeurs. De cette façon les données seront représentées dans les mêmes proportions, malgré leurs différences. L'analyse croisée pourra alors débuter.

• Sélectionner toutes les données du tableau, B2:G6,
• Dans le ruban Insérer, cliquer sur le bouton Graphiques recommandés,
• Dans la boîte de dialogue, activer l'onglet Tous les graphiques,
• Tout en bas de la liste, choisir la catégorie Graphique combiné,

Nous devons conserver l'affichage des ventes sous forme de barres et ajouter la représentation des nombres de mails sous forme de traits. Pour ces derniers, nous devons leur associer un axe secondaire des ordonnées adapté à leur échelle de valeurs. Pour ce faire :

• En bas de la boîte de dialogue, choisir la représentation Histogramme groupé, à l'aide des listes déroulantes pour les Ventes N.B. et les Ventes C,
• De la même façon, choisir la représentation Trait pour les deux séries des nombres de mailings,
• Pour ces deux dernières séries, cocher la case Axe secondaire comme l'illustre la capture ci-dessous,
• Valider cette construction par Ok,

• Cliquer et glisser le graphique ainsi construit à côté du précédent,
• Le redimensionner à l'aide de ses poignées d'angle pour lui donner, à peu près, les mêmes dimensions,
• Lui appliquer un style sur fond noir (Style 6) à l'aide du ruban Création,
• Modifier son titre en : Ventes/Emailing - Analyse croisée,

Le graphique ainsi conçu est déjà très proche du modèle. Il offre déjà une bonne lecture grâce à ses données regroupées. Pour plus de clarté, il convient néanmoins d'adapter les échelles et le pas des deux axes des ordonnées.

• Double cliquer sur l'axe des ordonnées des ventes, à gauche sur le graphique,
• Dans son volet, régler la limite maximum de l'axe sur 500 et l'unité principale sur 100,
• Sélectionner le deuxième axe des ordonnées, celui des nombres de mails,
• Régler sa limite maximum sur 100 et son unité principale sur 20,

Dans les deux cas, vous remarquez qu'avec des échelles adaptées, le graphique s'étire en hauteur.


Nous obtenons donc un second graphique similaire au modèle, riche en enseignements. En effet l'analyse croisée des données regroupées sur cette même vue permet deux remarques. Tout d'abord elle confirme que les ventes en couleurs sont meilleures que les ventes en noir et blanc. Mais surtout, elle indique que dans le même temps, les campagnes d'emailing qui ont permis ces ventes, ont été moins nombreuses pour les couleurs que les noir et blanc. Donc les stratégies de communications en couleurs sont d'autant plus productives. Il s'agit d'une donnée que nous ne pouvions pas prendre en compte avec le premier graphique. La stratégie à appliquer par l'entreprise semble donc limpide, après les résultats tangibles fournis par cette analyse croisée.