Analyse des données Excel - Visualisation des données

Vous pouvez afficher vos rapports d'analyse de données de différentes manières dans Excel. Toutefois, si les résultats de votre analyse de données peuvent être visualisés sous forme de graphiques mettant en évidence les points notables des données, votre public peut rapidement saisir ce que vous souhaitez projeter dans les données. Cela laisse également un bon impact sur votre style de présentation.

Dans ce chapitre, vous apprendrez à utiliser les graphiques Excel et les fonctionnalités de mise en forme Excel sur les graphiques qui vous permettent de présenter vos résultats d'analyse de données avec emphase.

Visualisation des données avec des graphiques

Dans Excel, les graphiques sont utilisés pour créer une représentation graphique de tout ensemble de données. Un graphique est une représentation visuelle des données, dans laquelle les données sont représentées par des symboles tels que des barres dans un graphique à barres ou des lignes dans un graphique en courbes. Excel vous propose de nombreux types de graphiques et vous pouvez en choisir un qui convient à vos données ou vous pouvez utiliser l'option Graphiques recommandés par Excel pour afficher des graphiques personnalisés en fonction de vos données et en sélectionner un.

Reportez-vous aux graphiques Excel du didacticiel pour plus d'informations sur les types de graphiques.

Dans ce chapitre, vous comprendrez les différentes techniques que vous pouvez utiliser avec les graphiques Excel pour mettre en évidence vos résultats d'analyse de données plus efficacement.

Création de graphiques combinés

Supposons que vous ayez l'objectif et les bénéfices réels pour l'exercice 2015-2016 que vous avez obtenus de différentes régions.

Nous allons créer un histogramme groupé pour ces résultats.

Comme vous le constatez, il est difficile de visualiser rapidement la comparaison entre les cibles et le réel dans ce graphique. Cela ne donne pas un véritable impact sur vos résultats.

Une meilleure façon de distinguer deux types de données pour comparer les valeurs consiste à utiliser des graphiques combinés. Dans Excel 2013 et les versions ci-dessus, vous pouvez utiliser des graphiques combinés dans le même but.

Utilisez des colonnes verticales pour les valeurs cibles et une ligne avec des marqueurs pour les valeurs réelles.

• Cliquez sur l'onglet CONCEPTION sous l'onglet OUTILS DE GRAPHIQUE sur le ruban.
• Cliquez sur Modifier le type de graphique dans le groupe Type. La boîte de dialogue Modifier le type de graphique s'affiche.

• Cliquez sur Combo.

• Modifiez le type de graphique de la série Réel en Ligne avec marqueurs. L'aperçu apparaît sous Combinaison personnalisée.

• Cliquez sur OK.

Votre tableau de combinaison personnalisé sera affiché.

Comme vous l'observez dans le graphique, les valeurs cibles sont dans les colonnes et les valeurs réelles sont marquées le long de la ligne. La visualisation des données s'est améliorée car elle vous montre également la tendance de vos résultats.

Cependant, ce type de représentation ne fonctionne pas correctement lorsque les plages de données de vos deux valeurs de données varient considérablement.

Création d'un graphique combiné avec un axe secondaire

Supposons que vous ayez les données sur le nombre d'unités de votre produit qui ont été expédiées et les bénéfices réels pour l'exercice 2015-2016 que vous avez obtenus dans différentes régions.

Si vous utilisez le même tableau de combinaison qu'avant, vous obtiendrez ce qui suit -

Dans le graphique, les données de No. of Units n'est pas visible car les plages de données varient considérablement.

Dans ce cas, vous pouvez créer un graphique combiné avec un axe secondaire, de sorte que l'axe principal affiche une plage et l'axe secondaire affiche l'autre.

• Cliquez sur l'onglet INSÉRER.
• Cliquez sur Combo dans le groupe Graphiques.
• Cliquez sur Créer un graphique combiné personnalisé dans la liste déroulante.

La boîte de dialogue Insérer un graphique apparaît avec Combo en surbrillance.

Pour Type de graphique, choisissez -

• Ligne avec des marqueurs pour le nombre d'unités de série

• Colonne groupée pour les bénéfices réels de la série

• Cochez la case Axe secondaire à droite du numéro de série d'unités et cliquez sur OK.

Un aperçu de votre graphique apparaît sous Combinaison personnalisée.

Votre graphique combiné apparaît avec l'axe secondaire.

Vous pouvez observer les valeurs des bénéfices réels sur l'axe principal et les valeurs du nombre d'unités sur l'axe secondaire.

Une observation importante dans le graphique ci-dessus concerne le troisième trimestre où le nombre d'unités vendues est supérieur, mais les bénéfices réels réalisés sont inférieurs. Cela pourrait probablement être attribué aux frais de promotion engagés pour augmenter les ventes. La situation s'est améliorée au quatrième trimestre, avec une légère baisse des ventes et une augmentation significative des bénéfices réels réalisés.

Axe des séries et des catégories discriminants

Supposons que vous souhaitiez projeter les bénéfices réels réalisés au cours des années 2013-2016.

Créez une colonne en cluster pour ces données.

Comme vous le constatez, la visualisation des données n'est pas efficace car les années ne sont pas affichées. Vous pouvez surmonter cela en changeant d'année en catégorie.

Supprimez l'année d'en-tête dans la plage de données.

Désormais, l'année est considérée comme une catégorie et non comme une série. Votre graphique se présente comme suit -

Éléments de graphique et styles de graphique

Les éléments de graphique donnent plus de descriptions à vos graphiques, aidant ainsi à visualiser vos données de manière plus significative.

• Cliquez sur le graphique

Trois boutons apparaissent à côté du coin supérieur droit du graphique -

• 
  Éléments du graphique
• 
  Styles de graphique
• 
  Filtres de graphique

Pour une explication détaillée de ceux-ci, reportez-vous au didacticiel sur les graphiques Excel.

• Cliquez sur Éléments du graphique.
• Cliquez sur Étiquettes de données.

• Cliquez sur Styles de graphique
• Sélectionnez un style et une couleur qui correspondent à vos données.

Vous pouvez utiliser Trendline pour afficher graphiquement les tendances dans les données. Vous pouvez étendre une courbe de tendance dans un graphique au-delà des données réelles pour prédire les valeurs futures.

Étiquettes de données

Excel 2013 et les versions ultérieures vous offrent diverses options pour afficher les étiquettes de données. Vous pouvez choisir une étiquette de données, la mettre en forme comme vous le souhaitez, puis utiliser Cloner l'étiquette actuelle pour copier la mise en forme dans le reste des étiquettes de données du graphique.

Les étiquettes de données d'un graphique peuvent avoir des effets, différentes formes et tailles.

Il est également possible d'afficher le contenu d'une cellule dans le cadre de l'étiquette de données avec le champ Insérer une étiquette de données.

Disposition rapide

Vous pouvez utiliser la mise en page rapide pour modifier rapidement la mise en page générale du graphique en choisissant l'une des options de mise en page prédéfinies.

• Cliquez sur le graphique.
• Cliquez sur l'onglet CONCEPTION sous OUTILS DE CARTE.
• Cliquez sur Disposition rapide.

Différentes dispositions possibles seront affichées. Au fur et à mesure que vous vous déplacez sur les options de mise en page, la mise en page du graphique change pour cette option particulière.

Sélectionnez la mise en page que vous aimez. Le graphique sera affiché avec la mise en page choisie.

Utilisation d'images dans les graphiques à colonnes

Vous pouvez mettre davantage l'accent sur votre présentation de données en utilisant une image au lieu de colonnes.

• Cliquez sur une colonne dans le graphique à colonnes.

• Dans la série de données de format, cliquez sur Remplir.

• Sélectionnez Image.

• Sous Insérer une image à partir de, indiquez le nom du fichier ou éventuellement le presse-papiers si vous avez copié une image précédemment.

L'image que vous avez choisie apparaîtra à la place des colonnes dans le graphique.

Graphique de bande

Vous devrez peut-être présenter les résultats d'une enquête client sur un produit de différentes régions. Le diagramme de bande convient à cet effet. Un graphique à bandes est un graphique en courbes avec une zone ombrée ajoutée pour afficher les limites supérieure et inférieure des groupes de données.

Supposons que les résultats de votre enquête auprès des clients des régions Est et Ouest, par mois sont -

Ici, dans les données, <50% est faible, 50% - 80% est moyen et> 80% est élevé.

Avec Band Chart, vous pouvez afficher les résultats de votre enquête comme suit -

Créez un graphique linéaire à partir de vos données.

Changez le type de graphique en -

• Série Est et Ouest pour s'aligner avec les marqueurs.
• Série basse, moyenne et haute à colonne empilée.

Votre graphique se présente comme suit.

• Cliquez sur l'une des colonnes.
• Modifiez la largeur de l'espace à 0% dans Format de la série de données.

Vous obtiendrez des bandes au lieu de colonnes.

Pour rendre le graphique plus présentable -

• Ajouter le titre du graphique.
• Ajustez la plage de l'axe vertical.
• Changez les couleurs des bandes en vert-jaune-rouge.
• Ajoutez des étiquettes aux bandes.

Le résultat final est le diagramme de bandes avec les limites définies et les résultats de l'enquête représentés à travers les bandes. On peut rapidement et clairement distinguer sur le graphique que si les résultats de l'enquête pour la région Ouest sont satisfaisants, ceux de la région Est ont diminué au dernier trimestre et nécessitent une attention particulière.

Graphique du thermomètre

Lorsque vous devez représenter une valeur cible et une valeur réelle, vous pouvez facilement créer un graphique de thermomètre dans Excel qui montre clairement ces valeurs.

Avec le graphique du thermomètre, vous pouvez afficher vos données comme suit -

Organisez vos données comme indiqué ci-dessous -

• Sélectionnez les données.
• Créez un histogramme en cluster.

Comme vous le voyez, la colonne de droite est Target.

• Cliquez sur une colonne dans le graphique.
• Cliquez sur Changer de ligne / colonne sur le ruban.

• Faites un clic droit sur la colonne cible.
• Cliquez sur Formater la série de données.
• Cliquez sur Axe secondaire.

As you observe the Primary Axis and Secondary Axis have different ranges.

• Right click the Primary Axis.
• In the Format Axis options, under Bounds, type 0 for Minimum and 1 for Maximum.
• Repeat the same for Secondary Axis.

Both Primary Axis and Secondary Axis will be set to 0% - 100%. The Target Column hides the Actual Column.

• Right click the visible column (Target)
• In the Format Data Series, select
  • No fill for FILL
  • Solid line for BORDER
  • Blue for Color

• In Chart Elements, unselect
  • Axis → Primary Horizontal
  • Axis → Secondary Vertical
  • Gridlines
  • Chart Title
• In the chart, right click on Primary Vertical Axis
• In Format Axis options, click on TICK MARKS
• For Major type, select Inside

• Right click on the Chart Area.
• In the Format Chart Area options, select
  • No fill for FILL
  • No line for BORDER

Resize the chart area, to get the shape of a thermometer.

You got your thermometer chart, with the actual value as against target value being shown. You can make this thermometer chart more impressive with some formatting.

• Insert a rectangle shape superimposing the blue rectangular part in the chart.
• In Format Shape options, select −
  • Gradient fill for FILL
  • Linear for Type
  • 180⁰ for Angle
• Set the Gradient stops at 0%, 50% and 100%.
• For the Gradient stops at 0% and 100%, choose the color black.
• For the Gradient stop at 50%, choose the color white.

• Insert an oval shape at the bottom.
• Format shape with same options.

The result is the Thermometer Chart that we started with.

Gantt Chart

A Gantt chart is a chart in which a series of horizontal lines shows the amount of work done in certain periods of time in relation to the amount of work planned for those periods.

In Excel, you can create a Gantt chart by customizing a Stacked Bar chart type so that it depicts tasks, task duration, and hierarchy. An Excel Gantt chart typically uses days as the unit of time along the horizontal axis.

Consider the following data where the column −

• Task represents the Tasks in the project
• Start represents number of days from the Start Date of the project
• Duration represents the duration of the Task

Note that Start of any Task is Start of previous Task + Duration. This is the case when the Tasks are in hierarchy.

• Select the data.
• Create Stacked Bar Chart.

• Right-click on Start Series.
• In Format Data Series options, select No fill.

• Right-click on Categories Axis.
• In Format Axis options, select Categories in reverse order.

• In Chart Elements, deselect
  • Legend
  • Gridlines
• Format the Horizontal Axis to
  • Adjust the range
  • Major Tick Marks at 5 day intervals
  • Minor Tick Marks at 1 day intervals
• Format Data Series to make it look impressive
• Give a Chart Title

Waterfall Chart

Waterfall Chart is one of the most popular visualization tools used in small and large businesses. Waterfall charts are ideal for showing how you have arrived at a net value such as net income, by breaking down the cumulative effect of positive and negative contributions.

Excel 2016 provides Waterfall Chart type. If you are using earlier versions of Excel, you can still create a Waterfall Chart using Stacked Column Chart.

The columns are color coded so that you can quickly tell positive from negative numbers. The initial and the final value columns start on the horizontal axis, while the intermediate values are floating columns. Because of this look, Waterfall Charts are also called Bridge Charts.

Consider the following data.

• Prepare the data for Waterfall Chart

• Ensure the column Net Cash Flow is to the left of the Months Column (This is because you will not include this column while creating the chart)

• Add 2 columns – Increase and Decrease for positive and negative cash flows respectively

• Add a column Start - the first column in the chart with the start value in the Net Cash Flow

• Add a column End - the last column in the chart with the end value in the Net Cash Flow

• Add a column Float – that supports the intermediate columns

• Compute the values for these columns as follows

• In the Float column, insert a row in the beginning and at the end. Place n arbitrary value 50000. This just to have some space to the left and right of the chart

The data will be as follows.

• Select the cells C2:H18 (Exclude Net Cash Flow column)
• Create Stacked Column Chart

• Right click on the Float Series.
• Click Format Data Series.
• In Format Data Series options, select No fill.

• Right click on Negative Series.
• Select Fill Color as Red.

• Right click on Positive Series.
• Select Fill Color as Green.

• Right click on Start Series.
• Select Fill Color as Grey.
• Right click on End Series.
• Select Fill Color as Grey.
• Delete the Legend.

• Right click on any Series
• In Format Data Series options, select Gap Width as 10% under Series Options

Give the Chart Title. The Waterfall Chart will be displayed.

Sparklines

Sparklines are tiny charts placed in single cells, each representing a row of data in your selection. They provide a quick way to see trends.

You can add Sparklines with Quick Analysis tool.

• Select the data for which you want to add Sparklines.
• Keep an empty column to the right side of the data for the Sparklines.

Quick Analysis button

appears at the bottom right of your selected data.

• Click on the Quick Analysis

  
  button. The Quick Analysis Toolbar appears with various options.

Click SPARKLINES. The chart options displayed are based on the data and may vary.

Click Line. A Line Chart for each row is displayed in the column to the right of the data.

PivotCharts

Pivot Charts are used to graphically summarize data and explore complicated data.

A PivotChart shows Data Series, Categories, and Chart Axes the same way a standard chart does. Additionally, it also gives you interactive filtering controls right on the chart so that you can quickly analyze a subset of your data.

PivotCharts are useful when you have data in a huge PivotTable, or many complex worksheet data that includes text and numbers. A PivotChart can help you make sense of this data.

You can create a PivotChart from

• A PivotTable.
• A Data Table as a standalone without PivotTable.

PivotChart from PivotTable

To create a PivotChart follow the steps given below −

• Click the PivotTable.
• Click ANALYZE under PIVOTTABLE TOOLS on the Ribbon.
• Click on PivotChart. The Insert Chart dialog box appears.

Select Clustered Column from the option Column.

Click OK. The PivotChart is displayed.

The PivotChart has three filters – Region, Salesperson and Month.

• Click the Region Filter Control option. The Search Box appears with the list of all Regions. Check boxes appear next to Regions.

• Select East and South options.

The filtered data appears on both the PivotChart and the PivotTable.

PivotChart without a PivotTable

You can create a standalone PivotChart without creating a PivotTable.

• Click the Data Table.
• Click the Insert tab.
• Click PivotChart in Charts group. The Create PivotChart window appears.
• Select the Table/Range.
• Select the Location where you want the PivotChart to be placed.

You can choose a cell in the existing worksheet itself, or in a new worksheet. Click OK.

An empty PivotChart and an empty PivotTable appear along with the PivotChart Field List to build the PivotChart.

• Choose the Fields to be added to the PivotChart

• Arrange the Fields by dragging them into FILTERS, LEGEND (SERIES), AXIS (CATEGORIES) and VALUES

• Use the Filter Controls on the PivotChart to select the Data to be placed on the PivotChart

Excel will automatically create a coupled PivotTable.