Tableaux de bord Excel - Guide rapide

Pour ceux qui découvrent les tableaux de bord, il serait idéal de commencer par comprendre les tableaux de bord. Dans ce chapitre, vous apprendrez à connaître la définition du tableau de bord, comment il tire son nom, comment il est devenu populaire dans l'informatique, les indicateurs clés, les avantages des tableaux de bord, les types de tableaux de bord, les données et formats de tableau de bord et les données en direct sur les tableaux de bord.

En technologie de l'information, un tableau de bord est une interface utilisateur en temps réel facile à lire, souvent sur une seule page, montrant une présentation graphique de l'état actuel (instantané) et des tendances historiques des indicateurs de performance clés d'une organisation ou d'un service pour permettre des décisions instantanées et éclairées. à réaliser en un coup d'œil.

Les tableaux de bord tirent leur nom de automobile dashboards. Sous le capot de votre véhicule, des centaines de processus peuvent avoir un impact sur les performances de votre véhicule. Votre tableau de bord résume ces événements à l'aide de visualisations afin que vous ayez l'esprit tranquille pour vous concentrer sur la conduite en toute sécurité de votre véhicule. De la même manière, les tableaux de bord métier sont utilisés pour visualiser et / ou surveiller facilement les performances de l'organisation.

L'idée de digital dashboardsest issue de l'étude des systèmes d'aide à la décision dans les années 1970. Les tableaux de bord d'entreprise ont été développés pour la première fois dans les années 1980, mais en raison des problèmes d'actualisation et de traitement des données, ils ont été mis sur l'étagère. Dans les années 1990, l'ère de l'information s'est accélérée et l'entreposage des données s'est accéléré, et le traitement analytique en ligne (OLAP) a permis aux tableaux de bord de fonctionner correctement. Cependant, l'utilisation des tableaux de bord n'est devenue populaire qu'avec la montée en puissance des indicateurs de performance clés (KPI) et l'introduction du Balanced Scorecard de Robert S. Kaplan et David P. Norton. Aujourd'hui, l'utilisation de tableaux de bord constitue une partie importante de la prise de décision.

Dans l'environnement commercial actuel, la tendance est au Big Data. La gestion et l'extraction de la valeur réelle de toutes ces données sont la clé du succès d'une entreprise moderne. Un tableau de bord bien conçu est un outil de gestion de l'information remarquable.

Tableau de bord - Définition

Stephen Few a défini un tableau de bord comme «un affichage visuel des informations les plus importantes nécessaires pour atteindre un ou plusieurs objectifs qui s'adapte entièrement sur un seul écran d'ordinateur afin de pouvoir être surveillé en un coup d'œil».

Dans les termes actuels, un tableau de bord peut être défini comme un outil de visualisation de données qui affiche l'état actuel des métriques et des indicateurs de performance clés (KPI) simplifiant les ensembles de données complexes pour fournir aux utilisateurs une connaissance rapide des performances actuelles.

Les tableaux de bord regroupent et organisent les chiffres et les mesures sur un seul écran. Ils peuvent être adaptés à un rôle spécifique et afficher les métriques d'un département ou d'une organisation dans son ensemble.

Les tableaux de bord peuvent être statiques pour une vue unique ou dynamiques affichant les résultats consolidés des modifications de données derrière l'écran. Ils peuvent également être rendus interactifs pour afficher les différents segments de données volumineuses sur un seul écran.

Mesures clés pour le tableau de bord

Le cœur du tableau de bord réside dans les indicateurs clés nécessaires à la surveillance. Ainsi, selon que le tableau de bord est pour une organisation dans son ensemble ou pour un département tel que les ventes, les finances, les ressources humaines, la production, etc., les indicateurs clés nécessaires à l'affichage varient.

En outre, les indicateurs clés d'un tableau de bord dépendent également du rôle des destinataires (public). Par exemple, Executive (CEO, CIO, etc.), Operations Manager, Sales Head, Sales Manager, etc. Cela est dû au fait que l'objectif principal d'un tableau de bord est de permettre la visualisation des données pour la prise de décision.

Le succès d'un tableau de bord dépend souvent des métriques qui ont été choisies pour la surveillance. Par exemple, les indicateurs de performance clés, les tableaux de bord équilibrés et les chiffres de performance des ventes peuvent être le contenu approprié dans les tableaux de bord d'entreprise.

Avantages du tableau de bord

Les tableaux de bord permettent aux managers de suivre la contribution des différents départements de l'organisation. Pour surveiller les performances globales de l'organisation, les tableaux de bord vous permettent de capturer et de signaler des points de données spécifiques de chacun des services de l'organisation, en fournissant un aperçu des performances actuelles et une comparaison avec les performances antérieures.

Les avantages des tableaux de bord sont les suivants:

• Présentation visuelle des mesures de performance.

• Capacité d'identifier et de corriger les tendances négatives.

• Mesure des efficiences / inefficacités.

• Possibilité de générer des rapports détaillés montrant les nouvelles tendances.

• Capacité à prendre des décisions plus éclairées sur la base des données collectées.

• Alignement des stratégies et des objectifs organisationnels.

• Visibilité instantanée de tous les systèmes au total.

• Identification rapide des données aberrantes et des corrélations.

• Gain de temps grâce à la visualisation complète des données par rapport à l'exécution de plusieurs rapports.

Types de tableaux de bord

Les tableaux de bord peuvent être classés en fonction de leur utilité comme suit -

• Tableaux de bord stratégiques
• Tableaux de bord analytiques
• Tableaux de bord opérationnels
• Tableaux de bord informatifs

Tableaux de bord stratégiques

Les tableaux de bord stratégiques aident les gestionnaires à tous les niveaux d'une organisation à prendre des décisions. Ils fournissent un instantané des données, affichant la santé et les opportunités de l'entreprise, en se concentrant sur les mesures de haut niveau des performances et des prévisions.

• Les tableaux de bord stratégiques nécessitent d'avoir des instantanés périodiques et statiques des données (par exemple, quotidien, hebdomadaire, mensuel, trimestriel et annuel). Ils n'ont pas besoin de changer constamment d'un moment à l'autre et nécessitent une mise à jour aux intervalles de temps spécifiés.

• Ils ne présentent que les données de haut niveau sans nécessairement donner les détails.

• Ils peuvent être interactifs pour faciliter les comparaisons et différentes vues en cas de grands ensembles de données en un seul clic. Mais, il n'est pas nécessaire de fournir des fonctionnalités plus interactives dans ces tableaux de bord.

La capture d'écran suivante montre un exemple de tableau de bord exécutif, affichant les objectifs et la progression.

Tableaux de bord analytiques

Les tableaux de bord analytiques incluent plus de contexte, de comparaisons et d'historique. Ils se concentrent sur les différentes facettes des données nécessaires à l'analyse.

Les tableaux de bord analytiques prennent généralement en charge les interactions avec les données, telles que l'exploration des détails sous-jacents et doivent donc être interactifs.

Des exemples de tableaux de bord analytiques incluent le tableau de bord Finance Management et le tableau de bord Sales Management.

Tableaux de bord opérationnels

Les tableaux de bord opérationnels permettent un suivi constant des opérations. Ils sont souvent conçus différemment des tableaux de bord stratégiques ou analytiques et se concentrent sur la surveillance des activités et des événements qui changent constamment et peuvent nécessiter une attention et une réponse à tout moment. Ainsi, les tableaux de bord opérationnels nécessitent des données en direct et à jour disponibles à tout moment et doivent donc être dynamiques.

Un exemple de tableau de bord d'opération pourrait être un support-system dashboard, affichant des données en direct sur les tickets de service qui nécessitent une action immédiate du superviseur sur les tickets de haute priorité.

Tableaux de bord informatifs

Les tableaux de bord informatifs servent uniquement à afficher des chiffres, des faits et / ou des statistiques. Ils peuvent être statiques ou dynamiques avec des données en direct, mais pas interactifs. Par exemple, le tableau de bord des informations d'arrivée / départ des vols dans un aéroport.

Données et formats du tableau de bord

Les données requises pour un tableau de bord dépendent de sa catégorie. La prémisse pour les données est qu'elles doivent être pertinentes, sans erreur, à jour et actives si nécessaire. Les données peuvent éventuellement provenir de sources et de formats divers et différents (feuilles de calcul, fichiers texte, pages Web, base de données organisationnelle, etc.).

Les résultats affichés sur un tableau de bord doivent être authentiques, corrects et aptes. Ceci est crucial car les informations sur un tableau de bord conduiraient à des décisions, des actions et / ou des inférences. Ainsi, avec les données affichées, le support choisi pour l'affichage est tout aussi important car il ne doit pas donner une impression erronée dans la représentation des données. L'accent devrait être mis sur la capacité de visualisation des données qui projetterait sans ambiguïté les conclusions.

Données en direct sur les tableaux de bord

Comme indiqué précédemment dans ce chapitre, l'entreposage de données et le traitement analytique en ligne (OLAP) permettent d'actualiser instantanément les tableaux de bord dynamiques avec des données en direct. Cela permet également à ceux qui conçoivent les tableaux de bord d'être indépendants du service informatique de l'organisation pour l'obtention des données.

Ainsi, les tableaux de bord sont devenus le support le plus recherché du top management à un utilisateur régulier.

Vous pouvez créer un tableau de bord dans Excel à l'aide de diverses fonctionnalités qui vous aident à mettre en évidence la visualisation des données, ce qui est la principale caractéristique de tout tableau de bord. Vous pouvez afficher les données dans des tableaux avec une mise en forme conditionnelle pour mettre en évidence les bons et les mauvais résultats, vous pouvez résumer les données dans des graphiques et des tableaux croisés dynamiques, vous pouvez ajouter des contrôles interactifs, et vous pouvez définir et gérer les KPI, etc.

Dans ce chapitre, vous apprendrez à connaître les principales fonctionnalités d'Excel qui vous sont utiles lorsque vous créez un tableau de bord. Ces fonctionnalités vous aident à arriver aux éléments du tableau de bord qui simplifient les données complexes et fournissent un impact visuel sur l'état actuel ou les performances en temps réel.

Tableaux Excel

Le composant le plus important de tout tableau de bord est son data. Les données peuvent provenir d'une seule source ou de plusieurs sources. Les données peuvent être limitées ou s'étendre sur plusieurs lignes.

Les tableaux Excel sont bien adaptés pour obtenir les données dans le classeur, dans lequel vous souhaitez créer le tableau de bord. Il existe plusieurs façons d'importer des données dans Excel, en établissant des connexions à diverses sources. Cela permet d'actualiser les données de votre classeur chaque fois que les données source sont mises à jour.

Vous pouvez nommer les tableaux Excel et utiliser ces noms pour référencer vos données dans le tableau de bord. Ce serait plus facile que de référencer la plage de données avec des références de cellule. Ces tableaux Excel sont vos tableaux de travail contenant les données brutes.

Vous pouvez arriver à un résumé de l'analyse des données et les présenter dans un tableau Excel qui peut être inclus dans un tableau de bord.

Sparklines

Vous pouvez utiliser des graphiques Sparklines dans vos tableaux Excel pour afficher les tendances sur une période donnée. Les graphiques sparkline sont des mini graphiques que vous pouvez placer dans des cellules individuelles. Vous pouvez utiliser des graphiques en courbes, des histogrammes ou des graphiques gains-pertes pour illustrer les tendances en fonction de vos données.

Mise en forme conditionnelle

La mise en forme conditionnelle est un gros atout pour mettre en évidence les données dans les tableaux. Vous pouvez définir les règles selon lesquelles vous pouvez faire varier les échelles de couleur, les barres de données et / ou les jeux d'icônes. Vous pouvez utiliser les règles définies par Excel ou créer vos propres règles, en fonction de l'applicabilité à vos données.

Vous apprendrez ces techniques de mise en forme conditionnelle dans le chapitre ─ Conditional Formatting for Data Visualization.

Graphiques Excel

Les graphiques Excel sont les composants de visualisation de données les plus utilisés pour les tableaux de bord. Vous pouvez amener le public à voir les modèles de données, les comparaisons et les tendances dans des ensembles de données de toute taille en ajoutant de manière frappante des couleurs et des styles.

Excel dispose de plusieurs types de graphiques intégrés tels que ligne, barre, colonne, dispersion, bulle, tarte, anneau, zone, stock, surface et radar si vous avez Excel 2013.

Vous comprendrez comment utiliser efficacement ces graphiques et les éléments de graphique dans votre tableau de bord dans le chapitre - Excel Charts for Dashboards.

En plus des types de graphiques mentionnés ci-dessus, il existe d'autres types de graphiques largement utilisés qui sont utiles pour représenter certains types de données. Il s'agit d'un diagramme en cascade, d'un diagramme à bandes, d'un diagramme de Gantt, d'un diagramme de thermomètre, d'un histogramme, d'un diagramme de Pareto, d'un diagramme en entonnoir, d'un diagramme en boîte et moustache et d'un diagramme à gaufres.

Vous en apprendrez davantage sur ces graphiques dans le chapitre - Graphiques Excel avancés pour tableaux de bord.

Appareil photo Excel

Une fois que vous avez créé des graphiques, vous devez les placer dans votre tableau de bord. Si vous souhaitez rendre votre tableau de bord dynamique, les données étant actualisées à chaque fois que les données sources changent, ce qui est le cas de la plupart des tableaux de bord, vous souhaitez fournir une interface entre les graphiques de votre tableau de bord et les données du backend . Vous pouvez y parvenir avec la fonction Appareil photo d'Excel.

Tableaux croisés dynamiques Excel

Lorsque vous avez de grands ensembles de données et que vous souhaitez résumer les résultats de manière dynamique en affichant diverses facettes des résultats de l'analyse, les tableaux croisés dynamiques Excel sont pratiques à inclure dans votre tableau de bord. Vous pouvez utiliser les tableaux Excel ou les tableaux de données les plus puissants du modèle de données pour créer des tableaux croisés dynamiques.

Les principales différences entre les deux approches sont -

Tableaux Excel	Tableaux de données
Les données d'une seule table peuvent être utilisées pour créer un tableau croisé dynamique.	Les données de plusieurs tables peuvent être utilisées pour créer un tableau croisé dynamique, définissant les relations entre les tables.
Lorsque les tables augmentent dans le pas. de lignes, la gestion de la mémoire et le stockage ne seront pas optimistes.	Peut gérer d'énormes ensembles de données avec des milliers de lignes de données avec une optimisation de la mémoire et une taille de fichier réduite.

Si vous essayez de créer un tableau croisé dynamique avec plusieurs tableaux Excel, vous serez invité à créer une relation et les tables avec la relation sont ajoutées au modèle de données.

Vous en apprendrez davantage sur les tableaux croisés dynamiques dans le chapitre - Excel PivotTables for Dashboards.

Si vous avez des données dans le modèle de données de votre classeur, vous pouvez créer des tableaux croisés dynamiques Power et des graphiques croisés dynamiques Power qui couvrent les données sur plusieurs tables de données.

Vous en apprendrez plus à ce sujet dans le chapitre - Excel Power PivotTables and Power PivotCharts for Dashboards.

Éléments de tableau de bord dynamiques avec contrôles interactifs

Vous pouvez rendre les éléments de votre tableau de bord interactifs avec des commandes faciles à utiliser telles que des barres de défilement, des boutons radio, des cases à cocher et des étiquettes dynamiques. Vous en apprendrez plus à ce sujet dans le chapitre -Interactive Controls in Excel Dashboards.

Barres de défilement

Boutons radio

Cases à cocher

Excel Power PivotTables et Power PivotCharts

Les tableaux croisés dynamiques Excel et les graphiques croisés dynamiques Power sont utiles pour résumer les données de plusieurs ressources, en créant un modèle de données optimisé en mémoire dans le classeur. Les tables de données du modèle de données peuvent traiter plusieurs milliers de données dynamiques, ce qui permet une synthèse avec moins d'effort et de temps.

Vous découvrirez l'utilisation des Power PivotTables et des Power PivotCharts dans les tableaux de bord dans le chapitre - Excel Power PivotTables et Power PivotCharts pour les tableaux de bord.

Modèle de données Excel

Tableau croisé dynamique Excel et graphique croisé dynamique Power

Rapports Excel Power View

Les rapports Excel Power View fournissent une visualisation interactive des données de grands ensembles de données, faisant ressortir la puissance du modèle de données et la nature interactive des visualisations dynamiques Power View.

Vous apprendrez comment utiliser Power View comme canevas de tableau de bord dans le chapitre - Rapports Excel Power View pour tableaux de bord.

Rapport Power View

Indicateurs clés de performance (KPI)

Les indicateurs clés de performance (KPI) font partie intégrante de nombreux tableaux de bord. Vous pouvez créer et gérer des KPI dans Excel. Vous en apprendrez davantage sur les KPI dans le chapitre -Key Performance Indicators in Excel Dashboards.

Indicateurs de performance clés

Mise en forme conditionnelle pour la visualisation des données

Si vous avez choisi Excel pour créer un tableau de bord, essayez d'utiliser des tableaux Excel s'ils répondent à cet objectif. Avec la mise en forme conditionnelle et les graphiques sparkline, les tableaux Excel sont le meilleur choix simple pour votre tableau de bord.

Dans Excel, vous pouvez utiliser la mise en forme conditionnelle pour la visualisation des données. Par exemple, dans un tableau contenant les chiffres des ventes du dernier trimestre par région, vous pouvez mettre en évidence les 5% les plus élevés.

Vous pouvez spécifier un nombre illimité de conditions de mise en forme en spécifiant des règles. Vous pouvez choisir les règles intégrées Excel qui correspondent à vos conditions à partir des règles de mise en évidence des cellules ou des règles du haut / du bas. Vous pouvez également définir vos propres règles.

Vous choisissez les options de mise en forme appropriées pour votre visualisation de données: barres de données, échelles de couleurs ou jeux d'icônes.

Dans ce chapitre, vous apprendrez les règles de mise en forme conditionnelle, les options de mise en forme et l'ajout / la gestion de règles.

Mettre en évidence les cellules

Vous pouvez utiliser les règles de mise en surbrillance des cellules pour attribuer un format aux cellules contenant les données répondant à l'un des critères suivants:

• Nombres dans une plage numérique donnée: supérieur à, inférieur à, compris entre et égal à.

• Valeurs dupliquées ou uniques.

Considérez le résumé suivant des résultats que vous souhaitez présenter -

Supposons que vous souhaitiez mettre en évidence les valeurs du montant total supérieures à 1000000.

• Sélectionnez la colonne - Montant total.
• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.
• Cliquez sur Mettre en évidence les règles des cellules dans la liste déroulante.
• Cliquez sur Supérieur à dans la deuxième liste déroulante qui apparaît.

La boîte de dialogue Supérieur à s'affiche.

• Dans la zone Format des cellules supérieures à:, spécifiez la condition comme 1000000.

• Dans la zone avec, sélectionnez l'option de mise en forme comme Remplissage vert avec texte vert foncé.

• Cliquez sur le bouton OK.

Comme vous pouvez le constater, les valeurs satisfaisant à la condition spécifiée sont mises en surbrillance avec le format spécifié.

Règles haut / bas

Vous pouvez utiliser les règles Top / Bottom pour attribuer un format aux valeurs répondant à l'un des critères suivants:

• Top 10 Items - Cellules classées parmi les N premiers, où 1 <= N <= 1000.

• Top 10% - Cellules classées parmi les n% supérieurs, où 1 <= n <= 100.

• Bottom 10 Items - Cellules qui se classent dans les N inférieurs, où 1 <= N <= 1000.

• Bottom 10% - Cellules qui se classent dans les n% inférieurs, où 1 <= n <= 100.

• Above Average - Cellules supérieures à la moyenne pour la plage sélectionnée.

• Below Average - Cellules inférieures à la moyenne pour la plage sélectionnée.

Supposons que vous souhaitiez mettre en évidence les valeurs du montant total qui se trouvent dans les 5% supérieurs.

• Sélectionnez la colonne - Montant total.
• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.
• Cliquez sur Top / Bottom Rules dans la liste déroulante.
• Cliquez sur Top Ten% dans la deuxième liste déroulante qui apparaît.

La boîte de dialogue Top Ten% s'affiche.

• Dans les cellules de format classées dans la zone TOP:, spécifiez la condition sur 5%.

• Dans la zone avec, sélectionnez l'option de mise en forme comme Remplissage vert avec texte vert foncé.

• Cliquez sur le bouton OK. Les 5% supérieurs seront mis en évidence avec le format spécifié.

Barres de données

Vous pouvez utiliser des barres de données colorées pour voir la valeur par rapport aux autres valeurs. La longueur de la barre de données représente la valeur. Une barre plus longue représente une valeur plus élevée et une barre plus courte représente une valeur inférieure. Vous pouvez utiliser des couleurs unies ou des couleurs dégradées pour les barres de données.

• Sélectionnez la colonne - Montant total.

• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.

• Cliquez sur Barres de données dans la liste déroulante.

• Cliquez sur Blue Data Bar sous Gradient Fill dans la deuxième liste déroulante qui apparaît.

Les valeurs de la colonne seront mises en surbrillance avec des valeurs petites, intermédiaires et grandes avec des barres de remplissage dégradées de couleur bleue.

• Sélectionnez la colonne - Montant total.

• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.

• Cliquez sur Barres de données dans la liste déroulante.

• Cliquez sur Orange Data Bar sous Solid Fill dans la deuxième liste déroulante qui apparaît.

Les valeurs de la colonne seront mises en surbrillance avec des valeurs petites, intermédiaires et grandes par hauteur de barre avec des barres de couleur orange.

Supposons que vous souhaitiez mettre en évidence les ventes par rapport à un objectif de vente, disons 800000.

• Créez une colonne avec des valeurs = [@ [Total Amount]] - 800000.

• Sélectionnez la nouvelle colonne.

• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.

• Cliquez sur Barres de données dans la liste déroulante.

• Cliquez sur Green Data Bar sous Gradient Fill dans la deuxième liste déroulante qui apparaît.

Les barres de données commenceront au milieu de chaque cellule et s'étireront vers la gauche pour les valeurs négatives et vers la droite pour les valeurs positives.

Comme vous pouvez le constater, les barres s'étendant vers la droite sont de couleur verte indiquant des valeurs positives et les barres s'étendant vers la gauche sont de couleur rouge indiquant des valeurs négatives.

Échelles de couleurs

Vous pouvez utiliser les échelles de couleur pour voir la valeur d'une cellule par rapport aux valeurs des autres cellules d'une colonne. La couleur indique où chaque valeur de cellule se situe dans cette plage. Vous pouvez avoir une échelle à 3 couleurs ou une échelle à 2 couleurs.

• Sélectionnez la colonne - Montant total.

• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.

• Cliquez sur Échelles de couleurs dans la liste déroulante.

• Cliquez sur Échelle de couleur vert-jaune-rouge dans la deuxième liste déroulante qui apparaît.

Comme dans le cas des règles de mise en évidence des cellules, une échelle de couleurs utilise un ombrage de cellule pour afficher les différences de valeurs de cellule. Comme vous pouvez le constater dans l'aperçu, les différences de teinte ne sont pas visibles pour cet ensemble de données.

• Cliquez sur Plus de règles dans la deuxième liste déroulante.

La boîte de dialogue Nouvelle règle de mise en forme s'affiche.

• Cliquez sur Formater toutes les cellules en fonction de leurs valeurs dans la zone Sélectionnez un type de règle.

• Dans la zone Modifier la description de la règle, sélectionnez ce qui suit -

  • Sélectionnez Echelle 3 couleurs dans la zone Style de format.

  • Sous Milieu, pour Valeur - tapez 75.

Cliquez sur le bouton OK.

Comme vous pouvez le constater, avec l'échelle de couleurs définie, les valeurs sont clairement ombrées représentant la plage de données.

Jeux d'icônes

Vous pouvez utiliser des jeux d'icônes pour visualiser les différences numériques. Dans Excel, vous avez une gamme de jeux d'icônes -

Type de jeu d'icônes	Jeux d'icônes
Directionnel
Formes
Indicateurs
Évaluations

Comme vous pouvez le constater, un jeu d'icônes se compose de trois à cinq symboles. Vous pouvez définir des critères pour associer une icône aux valeurs d'une plage de cellules. Par exemple, une flèche rouge vers le bas pour les petits nombres, une flèche verte vers le haut pour les grands nombres et une flèche horizontale jaune pour les valeurs intermédiaires.

• Sélectionnez la colonne - Montant total.

• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.

• Cliquez sur Jeux d'icônes dans la liste déroulante.

• Cliquez sur 3 flèches (colorées) dans le groupe Directionnel dans la deuxième liste déroulante qui apparaît.

Des flèches colorées apparaissent dans la colonne sélectionnée en fonction des valeurs.

Utilisation de règles personnalisées

Vous pouvez définir vos propres règles et formater une plage de cellules satisfaisant à une condition particulière.

• Sélectionnez la colonne - Montant total.
• Cliquez sur Mise en forme conditionnelle dans le groupe Styles sous l'onglet Accueil.
• Cliquez sur Nouvelle règle dans la liste déroulante.

La boîte de dialogue Nouvelle règle de mise en forme s'affiche.

• Cliquez sur Utiliser une formule pour déterminer les cellules à mettre en forme, dans la zone Sélectionner un type de règle.

• Dans la zone Modifier la description de la règle, procédez comme suit -

  • Tapez une formule dans la zone - Mettre en forme les valeurs où cette formule est vraie. Par exemple, =PercentRank.INC($E$3:$E$13,E3)>=0.7

  • Cliquez sur le bouton Format.

  • Choisissez le format. Par exemple, Police - gras et Remplir - orange.

  • Cliquez sur OK.

• Vérifiez l'aperçu.

Cliquez sur OK si l'aperçu est correct. Les valeurs de l'ensemble de données qui satisfont à la formule seront mises en surbrillance avec le format que vous avez choisi.

Gestion des règles de mise en forme conditionnelle

Vous pouvez gérer les règles de mise en forme conditionnelle à l'aide de la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle.

Cliquez sur Conditional Formattingdans le groupe Styles sous l'onglet Accueil. Cliquez sur Gérer les règles dans la liste déroulante.

La boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle s'affiche. Vous pouvez afficher toutes les règles existantes. Vous pouvez ajouter une nouvelle règle, supprimer une règle et / ou éditer une règle pour la modifier.

Si vous choisissez des graphiques pour l'affichage visuel des données, les graphiques Excel vous aident à sélectionner et à modifier les différentes vues. Excel fournit plusieurs types de graphiques qui vous permettent d'exprimer le message que vous souhaitez transmettre avec les données disponibles dans votre tableau de bord avec une représentation graphique de tout ensemble de données.

En outre, il existe certains graphiques sophistiqués qui sont utiles à des fins spécifiques. Certains d'entre eux sont disponibles dans Excel 2016. Mais ils peuvent également être créés à partir des types de graphiques intégrés dans Excel 2013.

Dans ce chapitre, vous découvrirez les types de graphiques dans Excel et quand utiliser chaque type de graphique. N'oubliez pas que dans un graphique du tableau de bord, vous ne devez insérer qu'un seul message. Sinon, cela peut prêter à confusion dans l'interprétation. Vous pouvez dimensionner les graphiques de manière à pouvoir accueillir un plus grand nombre de graphiques dans le tableau de bord, chacun véhiculant un message particulier.

Outre les types de graphiques abordés dans ce chapitre, certains graphiques avancés sont largement utilisés pour représenter les informations avec des repères visuels. Vous découvrirez les types de graphiques avancés et leur utilisation dans le chapitre -Advanced Excel Charts for Dashboards.

Types de graphiques

Vous pouvez trouver les principaux types de graphiques suivants si vous avez Excel 2013 -

Graphiques à colonnes

Graphiques linéaires

Camemberts

Graphique en anneau

Diagramme à barres

Graphiques de zone

Graphiques XY (Scatter)

Graphiques à bulles

Graphiques boursiers

Graphiques de surface

Cartes radar

Pour en savoir plus sur ces graphiques, reportez-vous au didacticiel - Graphiques Excel .

Graphiques combinés

Lorsque vous avez des types de données mixtes, vous pouvez les afficher avec des graphiques combinés (combinaison). Les graphiques peuvent avoir uniquement l'axe vertical principal ou une combinaison d'axe vertical principal et d'axe secondaire. Vous en apprendrez davantage sur les graphiques combinés dans une section ultérieure.

Sélection du type de graphique approprié

Pour afficher les données sous forme de graphique dans votre tableau de bord, identifiez d'abord l'objectif du graphique. Une fois que vous avez clairement défini ce que vous souhaitez représenter par un graphique, vous pouvez sélectionner le meilleur type de graphique qui représente votre message.

Voici quelques suggestions sur la sélection d'un type de graphique -

• Si vous souhaitez comparer des valeurs de données, vous pouvez choisir un graphique à barres, un graphique à secteurs, un graphique en courbes ou un graphique en nuage de points.

• Si vous souhaitez afficher la distribution, vous pouvez le faire avec un histogramme, un nuage de points ou un graphique en courbes.

• Si vous souhaitez afficher les tendances au fil du temps, vous pouvez utiliser un graphique en courbes.

• Si vous souhaitez représenter des parties d'un tout, un graphique à secteurs peut être une option. Mais, lorsque vous utilisez un graphique à secteurs, n'oubliez pas que seuls deux à trois points de données différents avec des valeurs de données très différentes peuvent être représentés efficacement avec les différentes tailles des tranches de secteurs. Si vous essayez de représenter plus de points de données dans un graphique à secteurs, il peut être difficile de dériver la comparaison.

• Vous pouvez utiliser le diagramme de dispersion si l'un des objectifs suivants est le suivant:

  • Vous souhaitez afficher les similitudes entre les grands ensembles de données au lieu des différences entre les points de données.

  • Vous souhaitez comparer de nombreux points de données sans tenir compte du temps. Plus vous incluez de données dans un graphique en nuage de points, meilleures sont les comparaisons que vous pouvez effectuer.

• Les graphiques recommandés dans Excel vous aident à trouver un type de graphique adapté à vos données.

Dans Excel, vous pouvez créer un graphique avec un type de graphique et le modifier ultérieurement à tout moment facilement.

Affichage des tendances avec des graphiques sparkline dans les tableaux

Les graphiques sparkline sont de minuscules graphiques placés dans des cellules uniques, chacun représentant une ligne de données dans votre sélection. Ils fournissent un moyen rapide de voir les tendances. Dans Excel, vous pouvez avoir des graphiques sparkline de ligne, des graphiques de colonne ou des graphiques de gain / perte.

Vous pouvez ajouter rapidement des Sparklines à votre tableau avec l'outil d'analyse rapide.

• Identifiez les données pour lesquelles vous souhaitez ajouter des Sparklines.

• Gardez une colonne vide à droite des données et nommez la colonne. Les graphiques sparkline seront placés dans cette colonne.

• Sélectionnez les données.

Le bouton de l'outil Analyse rapide

apparaît dans le coin inférieur droit de vos données sélectionnées.

• Cliquez sur le

  
  bouton Analyse rapide . L'outil d'analyse rapide apparaît.

• Cliquez sur SPARKLINES. Les options de graphique s'affichent.

• Cliquez sur Ligne. Les graphiques linéaires seront affichés pour chaque ligne dans les données sélectionnées.

• Cliquez sur Colonne. Les graphiques en colonnes seront affichés pour chaque ligne des données sélectionnées.

Les graphiques Win / Loss ne conviennent pas pour ces données. Tenez compte des données suivantes pour comprendre à quoi ressemblent les graphiques des gains / pertes.

Utilisation de graphiques combinés pour les comparaisons

Vous pouvez utiliser des graphiques combinés pour combiner deux ou plusieurs types de graphiques afin de comparer les valeurs de données de différentes catégories, si les plages de données varient considérablement. Avec un axe secondaire pour représenter l'autre plage de données, le graphique sera plus facile à lire et à saisir rapidement les informations.

Affiner rapidement les graphiques

Vous pouvez affiner les cartes de syntoniser rapidement en utilisant les trois boutons

, et qui apparaissent à côté du coin supérieur droit du graphique.

• Avec les

  
  éléments de graphique, vous pouvez ajouter ou supprimer des axes, des titres d'axes, une légende, des étiquettes de données, des quadrillages, des barres d'erreur, etc. au graphique.

• Avec

  
  Styles de graphique, vous pouvez personnaliser l'apparence du graphique en mettant en forme le style et les couleurs du graphique.

• Avec les

  
  filtres de graphique, vous pouvez modifier dynamiquement les points de données (valeurs) et les noms qui sont visibles sur le graphique affiché.

• Vous pouvez sélectionner / désélectionner les éléments du graphique.

• Vous pouvez formater le quadrillage pour afficher l'axe de profondeur.

• Vous pouvez définir un style de graphique.

• Vous pouvez choisir une palette de couleurs pour votre graphique.

• Vous pouvez sélectionner dynamiquement des valeurs et des noms à afficher.

  • Les valeurs sont les séries de données et les catégories.

  • Les noms sont les noms des séries de données (colonnes) et des catégories (lignes).

Utilisation d'étiquettes de données esthétiques

Vous pouvez avoir des étiquettes de données esthétiques et significatives.

Vous pouvez placer des étiquettes de données à n'importe quelle position par rapport aux points de données.

Vous pouvez formater les étiquettes de données avec diverses options, y compris des effets.

Vous pouvez changer les étiquettes de données en n'importe quelle forme.

Les étiquettes de données peuvent être de différentes tailles. Vous pouvez redimensionner chaque étiquette de données afin que le texte qu'il contient soit visible.

Vous pouvez inclure du texte à partir de points de données ou tout autre texte pour l'une des étiquettes de données afin de les rendre actualisables et donc dynamiques.

Vous pouvez connecter les étiquettes de données à leurs points de données avec des lignes de repère.

Vous pouvez placer des étiquettes de données avec des lignes de repère à n'importe quelle distance des points de données en les déplaçant.

Vous pouvez formater Leader Line pour les rendre visibles.

Vous pouvez choisir l'une de ces options pour afficher les étiquettes de données sur le graphique en fonction de vos données et de ce que vous souhaitez mettre en évidence.

Les étiquettes de données restent en place, même lorsque vous passez à un autre type de graphique. Mais, finalisez le type de graphique avant de mettre en forme les éléments du graphique, y compris les étiquettes de données.

Utilisation des courbes de tendance dans les graphiques

Vous pouvez représenter les prévisions des résultats dans un graphique à l'aide des courbes de tendance.

Utilisation de formes dans les graphiques

Vous pouvez insérer différents types de formes dans votre graphique. Après avoir inséré une forme, vous pouvez y ajouter du texte, avec Modifier le texte. Vous pouvez modifier la forme avec Modifier la forme et / ou Modifier les points.

Vous pouvez modifier le style de la forme, choisir une couleur de remplissage de la forme, mettre en forme le contour de la forme et ajouter des effets visuels à la forme.

Utilisation de cylindres, de cônes et de pyramides

Dans les graphiques à colonnes 3D, par défaut, vous aurez des boîtes.

Pour rendre vos graphiques plus visibles dans les tableaux de bord, vous pouvez choisir d'autres formes de colonnes 3D telles que des cylindres, des cônes, des pyramides, etc. Vous pouvez sélectionner ces formes dans le volet Format de la série de données.

Colonnes en forme de pyramide

Colonnes en forme de cylindre

Colonnes en forme de cône

Utilisation d'images dans les graphiques

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

Si vous avez plus de données à afficher sur le tableau de bord qui ne rentrent pas dans un seul écran, vous pouvez opter pour l'utilisation des contrôles Excel qui font partie d'Excel Visual Basic. Les contrôles les plus couramment utilisés sont les barres de défilement, les boutons radio et les cases à cocher. En les incorporant dans le tableau de bord, vous pouvez le rendre interactif et permettre à l'utilisateur de visualiser les différentes facettes des données par des sélections possibles.

Vous pouvez fournir des commandes interactives telles que des barres de défilement, des cases à cocher et des boutons radio dans vos tableaux de bord pour permettre aux destinataires de visualiser dynamiquement les différentes facettes des données affichées en tant que résultats. Vous pouvez décider d'une mise en page particulière du tableau de bord avec les destinataires et utiliser la même mise en page ensuite. Les contrôles interactifs Excel sont simples à utiliser et ne nécessitent aucune expertise d'Excel.

Les contrôles interactifs Excel seront disponibles dans l'onglet DÉVELOPPEUR du ruban.

Si vous ne trouvez pas l'onglet DÉVELOPPEUR sur le ruban, procédez comme suit -

• Cliquez sur Personnaliser le ruban dans la zone Options Excel.
• Sélectionnez Onglets principaux dans la zone Personnaliser le ruban.
• Cochez la case Développeur dans la liste des onglets principaux.

• Cliquez sur OK. Vous trouverez l'onglet DÉVELOPPEUR sur le ruban.

Barres de défilement dans les tableaux de bord

L'une des caractéristiques de tout tableau de bord est que chaque composant du tableau de bord est aussi compact que possible. Supposons que vos résultats se présentent comme suit -

Si vous pouvez présenter ce tableau avec une barre de défilement comme indiqué ci-dessous, il serait plus facile de parcourir les données.

Vous pouvez également avoir une ligne cible dynamique dans un graphique à barres avec barre de défilement. Lorsque vous déplacez la barre de défilement de haut en bas, la ligne cible se déplace de haut en bas et les barres qui traversent la ligne cible seront mises en surbrillance.

Dans les sections suivantes, vous apprendrez à créer une barre de défilement et à créer une ligne cible dynamique liée à une barre de défilement. Vous apprendrez également à afficher des étiquettes dynamiques dans les barres de défilement.

Créer une barre de défilement

Pour créer une barre de défilement pour un tableau, copiez d'abord les en-têtes des colonnes dans une zone vide de la feuille comme indiqué ci-dessous.

• Insérez une barre de défilement.

  • Cliquez sur l'onglet DÉVELOPPEUR sur le ruban.

  • Cliquez sur Insérer dans le groupe Contrôles.

  • Cliquez sur l'icône de la barre de défilement sous Contrôles de formulaire dans la liste déroulante des icônes.

• Amenez le curseur sur la colonne I et tirez vers le bas pour insérer une barre de défilement verticale.

• Ajustez la hauteur et la largeur de la barre de défilement et alignez-la sur le tableau.

• Faites un clic droit sur la barre de défilement.

• Cliquez sur Contrôle du format dans la liste déroulante.

La boîte de dialogue Contrôle du format s'affiche.

• Cliquez sur l'onglet Contrôle.

• Tapez ce qui suit dans les cases qui s'affichent.

• Cliquez sur le bouton OK. La barre de défilement est prête à être utilisée. Vous avez choisi la cellule O2 comme lien de cellule pour la barre de défilement, qui prend les valeurs 0 - 36, lorsque vous déplacez la barre de défilement vers le haut et vers le bas. Ensuite, vous devez créer une copie des données dans le tableau avec une référence basée sur la valeur de la cellule O2.

• Dans la cellule K3, tapez ce qui suit -

  = OFFSET (Résumé [@ [S. No.]], $ O $ 2,0).

• Appuyez sur le bouton Entrée. Remplissez les cellules de la colonne en copiant la formule.

• Remplissez les cellules des autres colonnes en copiant la formule.

Votre tableau dynamique et déroulant est prêt à être copié dans votre tableau de bord.

• Déplacez la barre de défilement vers le bas.

Comme vous pouvez le constater, la valeur de la cellule - lien de cellule de la barre de défilement change et les données du tableau sont copiées en fonction de cette valeur. À la fois, 12 lignes de données sont affichées.

• Faites glisser la barre de défilement vers le bas.

Les 12 dernières lignes des données sont affichées car la valeur actuelle est 36 (comme indiqué dans la cellule O2) et 36 est la valeur maximale que vous avez définie dans la boîte de dialogue Contrôle de formulaire.

Vous pouvez modifier la position relative du tableau dynamique, modifier le nombre de lignes à afficher à la fois, le lien de la cellule vers la barre de défilement, etc. en fonction de vos besoins. Comme vous l'avez vu ci-dessus, ceux-ci doivent être définis dans la boîte de dialogue Contrôle du format.

Création d'une ligne cible dynamique et interactive

Supposons que vous souhaitiez afficher les ventes par région au cours des 6 derniers mois. Vous avez également défini des objectifs pour chaque mois.

Vous pouvez faire ce qui suit -

• Créez un histogramme affichant toutes ces informations.
• Créez une ligne cible à travers les colonnes.
• Rendez la ligne cible interactive avec une barre de défilement.
• Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données.
• Mettez en surbrillance les valeurs qui atteignent la cible.

Créez un histogramme affichant toutes ces informations

Sélectionnez les données. Insérez un histogramme groupé.

Créer une ligne cible dans les colonnes

Changez le type de graphique en combo. Sélectionnez le type de graphique Ligne pour la série cible et Colonne groupée pour le reste de la série.

Créez une table de base pour la ligne cible. Vous rendrez cette dynamique plus tard.

Remplacez les valeurs de la série de données de la ligne cible par la colonne Cible du tableau ci-dessus.

Cliquez sur le bouton OK.

Modifiez le jeu de couleurs de la colonne groupée. Changez la ligne cible en une ligne pointillée verte.

Rendre la ligne cible interactive avec une barre de défilement

• Insérez une barre de défilement et placez-la sous le graphique et redimensionnez-la pour qu'elle s'étale de janvier à juin.

• Entrez les paramètres de la barre de défilement dans la boîte de dialogue Contrôle du format.

• Créez un tableau avec deux colonnes - Mois et Objectif.

• Entrez les valeurs basées sur le tableau de données et le lien de cellule de la barre de défilement.

Ce tableau affiche le mois et la cible correspondante en fonction de la position de la barre de défilement.

Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données

Vous êtes maintenant prêt à rendre votre ligne cible dynamique.

• Modifiez les valeurs de la colonne cible dans la table de base que vous avez créée pour la ligne cible en tapant = $ G $ 12 dans toutes les lignes.

Comme vous le savez, la cellule G12 affiche la valeur cible de manière dynamique.

Comme vous pouvez le constater, la ligne cible se déplace en fonction de la barre de défilement.

Mettre en évidence les valeurs qui répondent à la cible

Ceci est la dernière étape. Vous souhaitez mettre en évidence les valeurs répondant à la cible à tout moment.

• Ajoutez des colonnes sur le côté droit de votre tableau de données - East-Results, North-Results, SouthResults et West-Results.

• Dans la cellule H3, entrez la formule suivante -

  = SI (D3> = $ G $ 12, D3, NA ())

• Copiez la formule dans les autres cellules du tableau. Redimensionnez le tableau.

Comme vous pouvez le constater, les valeurs dans les colonnes - East-Results, North-Results, SouthResults et West-Results changent dynamiquement en fonction de la barre de défilement (c.-à-d. Valeur cible). Les valeurs supérieures ou égales à la cible sont affichées et les autres valeurs sont simplement # N / A.

• Modifiez la plage de données du graphique pour inclure les colonnes nouvellement ajoutées dans la table de données.

• Cliquez sur Modifier le type de graphique.

• Faites en sorte que la série cible soit Line et le reste Clustered Column.

• Pour la série de données nouvellement ajoutée, sélectionnez Axe secondaire.

• Mettez en forme les séries de données de manière à ce que les séries Est, Nord, Sud et Ouest aient une couleur de remplissage orange et que les séries East-Results, North-Results, South-Results et WestResults aient une couleur de remplissage verte.

• Entrez une étiquette de données pour la ligne cible et rendez-la dynamique avec la référence de cellule à la valeur Mois dans la table de données dynamiques.

Votre graphique avec ligne cible dynamique est prêt à être inclus dans le tableau de bord.

Vous pouvez effacer l'axe secondaire car il n'est pas nécessaire. Lorsque vous déplacez la barre de défilement, la ligne cible se déplace et les barres sont mises en surbrillance en conséquence. La ligne cible aura également une étiquette indiquant le mois.

Boutons Excel Option (Radio)

Les boutons radio sont normalement utilisés pour sélectionner une option dans un ensemble d'options donné. Il est toujours représenté par un petit cercle, qui contiendra un point lorsqu'il est sélectionné. Lorsque vous disposez d'un ensemble de boutons radio, vous ne pouvez en sélectionner qu'un seul.

Dans Excel, les boutons radio sont appelés boutons d'options.

Vous pouvez utiliser les boutons d'option Excel dans les graphiques pour choisir les données spécifiques que le lecteur souhaite consulter. Par exemple, dans l'exemple de la section précédente, vous avez créé une barre de défilement pour obtenir une ligne cible dynamique avec des valeurs cibles basées sur le mois. Vous pouvez utiliser les boutons d'option pour sélectionner un mois et donc la valeur cible, et baser la ligne cible sur la valeur cible. Voici les étapes -

• Créez un histogramme affichant toutes ces informations.
• Créez une ligne cible à travers les colonnes.
• Rendez la ligne cible interactive avec les boutons d'option.
• Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données.
• Mettez en surbrillance les valeurs qui atteignent la cible.

Les étapes 1 et 2 sont les mêmes que dans le cas précédent. À la fin de la deuxième étape, vous aurez le tableau suivant.

Rendre la ligne cible interactive avec les boutons d'option

• Insérez un bouton d'option.

  • Cliquez sur l'onglet DÉVELOPPEUR sur le ruban.

  • Cliquez sur Insérer dans le groupe Contrôles.

  • Cliquez sur l'icône du bouton d'option sous Contrôles de formulaire dans la liste déroulante des icônes.

Placez-le dans le coin supérieur droit du graphique.

Faites un clic droit sur le bouton Option. Cliquez sur l'option Contrôle du format dans la liste déroulante.

Entrez les paramètres du bouton d'option dans la boîte de dialogue Format de l'objet, sous l'onglet Contrôle.

La cellule F10 est liée au bouton d'option. Faites 5 copies du bouton d'option verticalement.

Comme vous pouvez le constater, tous les boutons d'options portent le même nom, appelé noms de légende. Mais, en interne, Excel aura des noms différents pour ces boutons d'options, que vous pouvez consulter dans la zone Nom. En outre, comme le bouton d'option 1 a été défini pour se lier à la cellule F10, toutes les copies font également référence à la même cellule.

Cliquez sur l'un des boutons d'options.

Comme vous pouvez le constater, le numéro de la cellule liée devient le numéro de série du bouton d'option. Renommez les boutons d'options en janvier, février, mars, avril, mai et juin.

Créez un tableau avec deux colonnes - Mois et Objectif. Entrez les valeurs basées sur le tableau de données et le lien de cellule de la barre de défilement.

Ce tableau affiche le mois et la cible correspondante en fonction du bouton d'option sélectionné.

Rendre la ligne cible dynamique en définissant les valeurs cibles dans vos données

Vous êtes maintenant prêt à rendre votre ligne cible dynamique.

• Modifiez les valeurs de la colonne cible dans la table de base que vous avez créée pour la ligne cible en tapant = $ G $ 12 dans toutes les lignes.

Comme vous le savez, la cellule G12 affiche la valeur cible de manière dynamique.

Comme vous pouvez le constater, la ligne cible s'affiche en fonction du bouton d'option sélectionné.

Mettre en évidence les valeurs qui répondent à la cible

Ceci est la dernière étape. Vous souhaitez mettre en évidence les valeurs répondant à la cible à tout moment.

• Ajoutez des colonnes sur le côté droit de votre tableau de données - East-Results, North-Results, SouthResults et West-Results.

• Dans la cellule H3, entrez la formule suivante -

  = SI (D3> = $ G $ 12, D3, NA ())

• Copiez la formule dans les autres cellules du tableau. Redimensionnez le tableau.

Comme vous pouvez le constater, les valeurs dans les colonnes - East-Results, North-Results, SouthResults et West-Results changent dynamiquement en fonction de la barre de défilement (c.-à-d. Valeur cible). Les valeurs supérieures ou égales à la cible sont affichées et les autres valeurs sont simplement # N / A.

• Modifiez la plage de données du graphique pour inclure les colonnes nouvellement ajoutées dans la table de données.

• Cliquez sur Modifier le type de graphique.

• Faites en sorte que la série cible soit Line et le reste Clustered Column.

• Pour la série de données nouvellement ajoutée, sélectionnez Axe secondaire.

• Mettez en forme les séries de données de manière à ce que les séries Est, Nord, Sud et Ouest aient une couleur de remplissage orange et que les séries East-Results, North-Results, South-Results et WestResults aient une couleur de remplissage verte.

• Ajoutez une étiquette de données dynamique à la ligne cible avec la valeur de la cellule $ G $ 12.

• Effacez l'axe secondaire car il n'est pas nécessaire.

• Sous l'onglet VUE sur le ruban, décochez la case Quadrillage.

• Modifiez l'option Étiquette sur Élevé dans les options Format de l'axe. Cela décale les étiquettes de l'axe vertical vers la droite, ce qui rend votre étiquette de données de ligne cible bien visible.

Votre graphique avec la ligne cible dynamique et les boutons d'option est prêt à être inclus dans le tableau de bord.

Lorsque vous sélectionnez un bouton d'option, la ligne cible s'affiche selon la valeur cible du mois sélectionné et les barres sont mises en surbrillance en conséquence. La ligne cible aura également une étiquette de données indiquant la valeur cible.

Cases à cocher Excel

Les cases à cocher sont normalement utilisées pour sélectionner une ou plusieurs options à partir d'un ensemble d'options donné. Les cases à cocher sont toujours représentées par de petits carrés, qui auront une coche lorsqu'ils sont sélectionnés. Lorsque vous disposez d'un ensemble de cases à cocher, il est possible d'en sélectionner n'importe quel nombre. Par exemple,

Vous pouvez utiliser les cases à cocher Excel dans les graphiques pour choisir les données spécifiques que le lecteur souhaite consulter. Par exemple, dans l'exemple de la section précédente, vous avez créé un histogramme qui affiche les données de 4 régions - Est, Nord, Sud et Ouest. Vous pouvez utiliser les cases à cocher pour sélectionner les régions pour lesquelles les données sont affichées. Vous pouvez sélectionner n'importe quel nombre de régions à la fois.

Vous pouvez commencer par la dernière étape de la section précédente -

• Insérez une case à cocher.

  • Cliquez sur l'onglet DÉVELOPPEUR sur le ruban.

  • Cliquez sur Insérer dans le groupe Contrôles.

  • Cliquez sur l'icône Case à cocher sous Contrôles de formulaire dans la liste déroulante des icônes.

• Placez-le dans le coin supérieur gauche du graphique.

• Modifiez le nom de la case à cocher en Est.

• Cliquez avec le bouton droit sur la case à cocher. Cliquez sur Contrôle du format dans la liste déroulante.

• Entrez les paramètres de la case à cocher dans la boîte de dialogue Contrôle du format, sous l'onglet Contrôle.

• Cliquez sur le bouton OK. Vous pouvez observer que dans la cellule liée C19, TRUE sera affiché si vous cochez la case et FALSE s'affichera si vous désélectionnez la case à cocher.

• Copiez la case à cocher et collez-la 3 fois horizontalement.

• Changez les noms en Nord, Sud et Ouest.

Comme vous pouvez le constater, lorsque vous copiez une case à cocher, la cellule liée reste la même pour la case à cocher copiée également. Cependant, comme les cases à cocher peuvent avoir plusieurs sélections, vous devez rendre les cellules liées différentes.

• Remplacez les cellules liées pour le Nord, le Sud et l'Ouest par 20 USD, 21 USD et 22 USD.

L'étape suivante consiste à n'avoir que les données des régions sélectionnées dans le graphique.

• Créez une structure de table comme suit -

• Tapez = IF ($ C $ 19, H3, NA ()) dans la cellule C21.
• Tapez = IF ($ D $ 19, I3, NA ()) dans la cellule D21.
• Tapez = IF ($ E $ 19, J3, NA ()) dans la cellule E21.
• Tapez = IF ($ F $ 19, K3, NA ()) dans la cellule F21.
• Remplissez les autres lignes du tableau.

• Ajoutez la colonne Cible.

• Remplacez les données du graphique par cette table.

Le graphique affiche les données des régions sélectionnées qui dépassent la valeur cible définie pour le mois sélectionné.

Vous savez que les graphiques sont utiles pour transmettre visuellement votre message de données. Outre les types de graphiques disponibles dans Excel, il existe des graphiques d'application largement utilisés qui sont devenus populaires. Certains d'entre eux sont également inclus dans Excel 2016.

Si vous utilisez Excel 2013 ou des versions antérieures, veuillez vous référer au didacticiel - Graphiques Excel avancés pour en savoir plus sur ces graphiques et comment les créer avec les types de graphiques intégrés.

Types de graphiques Excel avancés

Les types de graphiques Excel avancés suivants seront utiles à inclure dans vos tableaux de bord -

Graphique en cascade

Les graphiques en cascade sont idéaux pour montrer comment vous êtes arrivé à une valeur nette telle que le revenu net, en décomposant l'effet cumulatif des contributions positives et négatives.

Graphique de bande

Le diagramme de bande convient pour représenter graphiquement les données sur une période de temps, en confiant chaque point de données à un intervalle défini. Par exemple, les résultats d'une enquête client sur un produit de différentes régions.

Diagramme de Gantt

Un diagramme de Gantt est un diagramme dans lequel une série de lignes horizontales montre la quantité de travail effectué dans certaines périodes de temps par rapport à la quantité de travail prévue pour ces périodes.

Graphique du thermomètre

Lorsque vous devez représenter une valeur cible et une valeur réelle, vous pouvez afficher ces valeurs de manière catégorique avec un graphique Thermomètre.

Tableau de jauge

Un graphique à jauge montre le minimum, le maximum et la valeur actuelle indiquant à quelle distance du maximum vous vous trouvez.

Graphique à puces

Le graphique à puces peut être utilisé pour comparer une mesure à une ou plusieurs mesures connexes et relier la mesure à des plages quantitatives définies qui déclarent son état qualitatif, par exemple, bon, satisfaisant et médiocre. Vous pouvez également utiliser le graphique à puces pour afficher les KPI.

Graphique en entonnoir

Le graphique en entonnoir permet de visualiser la réduction progressive des données lors du passage d'une phase à une autre. Par exemple, le pipeline des ventes.

Tableau des gaufres

Le graphique gaufré est un bon choix pour afficher la progression du travail sous forme de pourcentage d'achèvement, objectif atteint par rapport à la cible, etc.

Carte de chaleur

Une carte thermique est une représentation visuelle des données dans un tableau pour mettre en évidence les points de données importants.

Tableau des étapes

Si vous devez afficher les modifications qui se produisent à des intervalles irréguliers qui restent constants entre les modifications, le diagramme en étapes est utile.

Graphique en boîte et moustache

Les graphiques Box et Whisker sont couramment utilisés dans l'analyse statistique. Par exemple, vous pouvez utiliser un graphique Box et Whisker pour comparer les résultats expérimentaux ou les résultats d'un concours.

Histogramme

Un histogramme est une représentation graphique de la distribution des données numériques et est largement utilisé dans l'analyse statistique.

Diagramme de Pareto

Le graphique de Pareto est un autre graphique largement utilisé dans l'analyse statistique pour la prise de décision. Il représente l'analyse de Pareto, également appelée règle des 80/20, ce qui signifie que 80% des résultats sont dus à 20% des causes.

Affichage des performances trimestrielles avec des graphiques à puces

Supposons que vous deviez afficher les performances de l'équipe commerciale chaque trimestre sur le tableau de bord. Les données peuvent être comme indiqué ci-dessous.

Vous pouvez afficher ces informations sur le tableau de bord en utilisant le graphique à puces comme suit -

Comme vous pouvez le constater, cela occupe moins d'espace, mais transmet beaucoup d'informations.

Affichage du pourcentage de profit selon la région avec des graphiques gaufres

Supposons que vous deviez afficher le pourcentage de profit pour les régions - Est, Nord, Sud et Ouest.

Vous pouvez afficher ces informations avec emphase sur votre tableau de bord avec des graphiques Waffle comme indiqué ci-dessous.

Cet affichage représente non seulement les valeurs, mais également une bonne comparaison.

Si vous avez vos données dans un seul tableau Excel, vous pouvez résumer les données de la manière requise à l'aide des tableaux croisés dynamiques Excel. Un tableau croisé dynamique est un outil extrêmement puissant que vous pouvez utiliser pour découper et découper des données. Vous pouvez suivre, analyser des centaines de milliers de points de données avec une table compacte qui peut être modifiée dynamiquement pour vous permettre de trouver les différentes perspectives des données. C'est un outil simple à utiliser, mais puissant.

Excel vous offre un moyen plus puissant de créer un tableau croisé dynamique à partir de plusieurs tables, de différentes sources de données et de sources de données externes. Il est nommé Power PivotTable qui fonctionne sur sa base de données appelée modèle de données. Vous apprendrez à connaître Power PivotTable et d'autres outils électriques Excel tels que Power PivotChart et Power View Reports dans d'autres chapitres.

Les tableaux croisés dynamiques, les tableaux croisés dynamiques Power, les graphiques croisés dynamiques Power et les rapports Power View sont utiles pour afficher les résultats résumés des ensembles de données volumineux sur votre tableau de bord. Vous pouvez maîtriser le tableau croisé dynamique normal avant de vous aventurer dans les outils électriques.

Créer un tableau croisé dynamique

Vous pouvez créer un tableau croisé dynamique à partir d'une plage de données ou d'un tableau Excel. Dans les deux cas, la première ligne des données doit contenir les en-têtes des colonnes.

Vous pouvez commencer avec un tableau croisé dynamique vide et le créer à partir de zéro ou utiliser la commande Tableau croisé dynamique recommandé par Excel pour prévisualiser les tableaux croisés dynamiques personnalisés possibles pour vos données et en choisir un qui convient à votre objectif. Dans les deux cas, vous pouvez modifier un tableau croisé dynamique à la volée pour obtenir des informations sur les différents aspects des données disponibles.

Considérez la plage de données suivante qui contient les données de ventes pour chaque commercial, dans chaque région et au cours des mois de janvier, février et mars -

Pour créer un tableau croisé dynamique à partir de cette plage de données, procédez comme suit:

• Assurez-vous que la première ligne contient des en-têtes. Vous avez besoin d'en-têtes, car ils seront les noms de champ dans votre tableau croisé dynamique.

• Nommez la plage de données comme SalesData_Range.

• Cliquez sur la plage de données - SalesData_Range.

• Cliquez sur l'onglet INSÉRER sur le ruban.

• Cliquez sur Tableau croisé dynamique dans le groupe Tables.

La boîte de dialogue Créer un tableau croisé dynamique s'affiche.

Comme vous pouvez le constater, dans la boîte de dialogue Créer un tableau croisé dynamique, sous Choisir les données que vous souhaitez analyser, vous pouvez sélectionner une table ou une plage dans le classeur actuel ou utiliser une source de données externe. Par conséquent, vous pouvez utiliser les mêmes étapes pour créer un formulaire de tableau croisé dynamique soit une plage ou un tableau.

• Cliquez sur Sélectionner une table ou une plage.

• Dans la zone Table / Plage, saisissez le nom de la plage - SalesData_Range.

• Cliquez sur Nouvelle feuille de calcul sous Choisissez où vous souhaitez placer le rapport de tableau croisé dynamique.

Vous pouvez également observer que vous pouvez choisir d'analyser plusieurs tables en ajoutant cette plage de données au modèle de données. Le modèle de données est la base de données Excel Power Pivot.

• Cliquez sur le bouton OK. Une nouvelle feuille de calcul sera insérée dans votre classeur. La nouvelle feuille de calcul contient un tableau croisé dynamique vide.

• Nommez la feuille de calcul - Range-PivotTable.

Comme vous pouvez le constater, la liste des champs de tableau croisé dynamique apparaît sur le côté droit de la feuille de calcul, contenant les noms d'en-tête des colonnes de la plage de données. En outre, sur le ruban, les outils de tableau croisé dynamique - ANALYSE et CONCEPTION apparaissent.

Vous devez sélectionner les champs de tableau croisé dynamique en fonction des données que vous souhaitez afficher. En plaçant les champs dans les zones appropriées, vous pouvez obtenir la mise en page souhaitée pour les données. Par exemple, pour résumer le montant de la commande par vendeur pour les mois de janvier, février et mars, vous pouvez effectuer les opérations suivantes:

• Cliquez sur le champ Vendeur dans la liste Champs du tableau croisé dynamique et faites-le glisser vers la zone ROWS.

• Cliquez sur le champ Mois dans la liste Champs de tableau croisé dynamique et faites-le glisser également vers la zone ROWS.

• Cliquez sur Montant de la commande et faites-le glisser vers la zone ∑ VALEURS.

Votre tableau croisé dynamique est prêt. Vous pouvez modifier la disposition du tableau croisé dynamique en faisant simplement glisser les champs sur les zones. Vous pouvez sélectionner / désélectionner des champs dans la liste Champs de tableau croisé dynamique pour choisir les données que vous souhaitez afficher.

Filtrage des données dans un tableau croisé dynamique

Si vous devez vous concentrer sur un sous-ensemble de données de votre tableau croisé dynamique, vous pouvez filtrer les données du tableau croisé dynamique en fonction d'un sous-ensemble des valeurs d'un ou plusieurs champs. Par exemple, dans l'exemple ci-dessus, vous pouvez filtrer les données en fonction du champ Plage afin de ne pouvoir afficher les données que pour la ou les régions sélectionnées.

Il existe plusieurs façons de filtrer les données dans un tableau croisé dynamique -

• Filtrage à l'aide de filtres de rapport.
• Filtrage à l'aide de segments.
• Filtrer les données manuellement.
• Filtrage à l'aide de filtres d'étiquettes.
• Filtrage à l'aide de filtres de valeurs.
• Filtrage à l'aide de filtres de date.
• Filtrage à l'aide du filtre Top 10.
• Filtrage à l'aide de la chronologie.

Vous apprendrez à connaître l'utilisation des filtres de rapport dans cette section et des segments dans la section suivante. Pour d'autres options de filtrage, reportez-vous au didacticiel sur les tableaux croisés dynamiques Excel.

Vous pouvez affecter un filtre à l'un des champs afin de pouvoir modifier dynamiquement le tableau croisé dynamique en fonction des valeurs de ce champ.

• Faites glisser le champ Région vers la zone FILTRES.
• Faites glisser le champ Vendeur vers la zone ROWS.
• Faites glisser le champ Mois vers la zone COLONNES.
• Faites glisser le champ Order Amount vers la zone ∑ VALUES.

Le filtre avec l'étiquette en tant que région apparaît au-dessus du tableau croisé dynamique (dans le cas où vous n'avez pas de lignes vides au-dessus de votre tableau croisé dynamique, le tableau croisé dynamique est poussé vers le bas pour faire de la place pour le filtre).

Comme vous pouvez le constater,

• Les valeurs du vendeur apparaissent dans les lignes.

• Les valeurs du mois apparaissent dans les colonnes.

• Le filtre de région apparaît en haut avec la sélection par défaut comme TOUT.

• La valeur récapitulative est la somme du montant de la commande.

  • La somme du montant de la commande par vendeur apparaît dans la colonne Total général.

  • La somme du montant de la commande par mois apparaît dans la ligne Total général.

• Cliquez sur la flèche dans le filtre de région.

La liste déroulante avec les valeurs du champ Région apparaît.

• Cochez la case Sélectionner plusieurs éléments. Des cases à cocher apparaîtront pour toutes les valeurs. Par défaut, toutes les cases sont cochées.

• Décochez la case (Tout). Toutes les cases seront décochées.

• Cochez les cases - Sud et Ouest.

• Cliquez sur le bouton OK. Les données relatives aux régions du Sud et de l'Ouest uniquement seront résumées.

Comme vous pouvez le constater, dans la cellule à côté du filtre de région - (Plusieurs éléments) s'affiche, indiquant que vous avez sélectionné plus d'une valeur. Mais combien de valeurs et / ou quelles valeurs ne sont pas connues du rapport qui est affiché. Dans un tel cas, l'utilisation de Slicers est une meilleure option pour le filtrage.

Utilisation de segments dans un tableau croisé dynamique

Le filtrage à l'aide de slicers présente de nombreux avantages -

• Vous pouvez avoir plusieurs filtres en sélectionnant les champs des segments.

• Vous pouvez visualiser les champs sur lesquels le filtre est appliqué (un segment par champ).

• Un Slicer aura des boutons indiquant les valeurs du champ qu'il représente. Vous pouvez cliquer sur les boutons du Slicer pour sélectionner / désélectionner les valeurs dans le champ.

• Vous pouvez visualiser les valeurs d'un champ utilisées dans le filtre (les boutons sélectionnés sont mis en surbrillance dans le segment).

• Vous pouvez utiliser un segment commun pour plusieurs tableaux croisés dynamiques et / ou graphiques croisés dynamiques.

• Vous pouvez masquer / afficher un slicer.

Pour comprendre l'utilisation des segments, considérez le tableau croisé dynamique suivant.

Supposons que vous souhaitiez filtrer ce tableau croisé dynamique en fonction des champs - Région et Mois.

• Cliquez sur l'onglet ANALYSER sous OUTILS PIVOTABLES sur le ruban.
• Cliquez sur Insérer un segment dans le groupe Filtre.

La boîte de dialogue Insérer des segments s'affiche. Il contient tous les champs de vos données.

• Cochez les cases Région et Mois.

• Cliquez sur le bouton OK. Les segments pour chacun des champs sélectionnés apparaissent avec toutes les valeurs sélectionnées par défaut. Les outils de segment apparaissent sur le ruban pour travailler sur les paramètres, l'apparence et la convivialité du segment.

Comme vous pouvez le constater, chaque segment a toutes les valeurs du champ qu'il représente et les valeurs sont affichées sous forme de boutons. Par défaut, toutes les valeurs d'un champ sont sélectionnées et donc tous les boutons sont mis en surbrillance.

Supposons que vous souhaitiez afficher le tableau croisé dynamique uniquement pour les régions du Sud et de l'Ouest et pour les mois de février et mars.

• Cliquez sur Sud dans le segment de région. Seul le sud sera mis en évidence dans le Slicer - Region.

• Maintenez la touche Ctrl enfoncée et cliquez sur Ouest dans le segment de région.

• Cliquez sur Février dans le segment de mois.

• Maintenez la touche Ctrl enfoncée et cliquez sur Mars dans le segment de mois. Les valeurs sélectionnées dans les segments sont mises en évidence. Le tableau croisé dynamique sera résumé pour les valeurs sélectionnées.

Pour ajouter / supprimer des valeurs d'un champ du filtre, maintenez la touche Ctrl enfoncée et cliquez sur ces boutons dans le segment respectif.

Lorsque vos ensembles de données sont volumineux, vous pouvez utiliser Excel Power Pivot qui peut gérer des centaines de millions de lignes de données. Les données peuvent se trouver dans des sources de données externes et Excel Power Pivot crée un modèle de données qui fonctionne sur un mode d'optimisation de la mémoire. Vous pouvez effectuer les calculs, analyser les données et arriver à un rapport pour tirer des conclusions et des décisions. Le rapport peut être sous la forme d'un tableau croisé dynamique ou d'un graphique croisé dynamique ou d'une combinaison des deux.

Vous pouvez utiliser Power Pivot en tant que solution de reporting et d'analyse ad hoc. Ainsi, il serait possible pour une personne ayant une expérience pratique avec Excel d'effectuer l'analyse de données et la prise de décision haut de gamme en quelques minutes et constituent un atout précieux à inclure dans les tableaux de bord.

Utilisations de Power Pivot

Vous pouvez utiliser Power Pivot pour ce qui suit -

• Pour effectuer une analyse de données puissante et créer des modèles de données sophistiqués.
• Pour combiner rapidement de gros volumes de données provenant de plusieurs sources différentes.
• Pour effectuer une analyse des informations et partager les informations de manière interactive.
• Pour créer des indicateurs clés de performance (KPI).
• Pour créer des tableaux croisés dynamiques dynamiques.
• Pour créer des graphiques croisés dynamiques.

Différences entre le tableau croisé dynamique et le tableau croisé dynamique Power

Power PivotTable ressemble à PivotTable dans sa disposition, avec les différences suivantes -

• Le tableau croisé dynamique est basé sur des tableaux Excel, tandis que le tableau croisé dynamique Power est basé sur des tableaux de données qui font partie du modèle de données.

• Le tableau croisé dynamique est basé sur un seul tableau Excel ou plage de données, tandis que le tableau croisé dynamique Power peut être basé sur plusieurs tableaux de données, à condition qu'ils soient ajoutés au modèle de données.

• Le tableau croisé dynamique est créé à partir de la fenêtre Excel, tandis que le tableau croisé dynamique Power est créé à partir de la fenêtre PowerPivot.

Création d'un tableau croisé dynamique dynamique

Supposons que vous ayez deux tables de données - Vendeur et Ventes dans le modèle de données. Pour créer un Power PivotTable à partir de ces deux tables de données, procédez comme suit:

• Cliquez sur l'onglet Accueil du ruban dans la fenêtre PowerPivot.

• Cliquez sur Tableau croisé dynamique sur le ruban.

• Cliquez sur Tableau croisé dynamique dans la liste déroulante.

La boîte de dialogue Créer un tableau croisé dynamique s'affiche. Cliquez sur Nouvelle feuille de travail.

Cliquez sur le bouton OK. Une nouvelle feuille de calcul est créée dans la fenêtre Excel et un tableau croisé dynamique vide apparaît.

Comme vous pouvez le constater, la disposition du Power PivotTable est similaire à celle du PivotTable.

La liste des champs de tableau croisé dynamique apparaît sur le côté droit de la feuille de calcul. Ici, vous trouverez quelques différences par rapport au tableau croisé dynamique. La liste Champs du tableau croisé dynamique Power comporte deux onglets - ACTIF et TOUS, qui apparaissent sous le titre et au-dessus de la liste des champs. L'onglet TOUT est mis en surbrillance. L'onglet TOUT affiche toutes les tables de données dans le modèle de données et l'onglet ACTIVE affiche toutes les tables de données qui sont choisies pour le tableau croisé dynamique actuel.

• Cliquez sur les noms de table dans la liste Champs de tableau croisé dynamique sous TOUT.

Les champs correspondants avec des cases à cocher apparaîtront.

• Chaque nom de table aura le symbole

  
  sur le côté gauche.

• Si vous placez le curseur sur ce symbole, la source de données et le nom de la table modèle de cette table de données seront affichés.

• Faites glisser le vendeur de la table des vendeurs vers la zone ROWS.
• Cliquez sur l'onglet ACTIVE.

Le champ Vendeur apparaît dans le tableau croisé dynamique Power et le tableau Vendeur apparaît sous l'onglet ACTIF.

• Cliquez sur l'onglet TOUS.
• Cliquez sur Mois et montant de la commande dans le tableau Ventes.
• Cliquez sur l'onglet ACTIVE.

Les deux tableaux - Ventes et Vendeur apparaissent sous l'onglet ACTIF.

• Faites glisser le mois vers la zone COLONNES.
• Faites glisser la région vers la zone FILTRES.

• Cliquez sur la flèche à côté de TOUT dans la zone de filtre Région.
• Cliquez sur Sélectionner plusieurs éléments.
• Cliquez sur Nord et Sud.

• Cliquez sur le bouton OK. Triez les étiquettes de colonne dans l'ordre croissant.

Power PivotTable peut être modifié dynamiquement pour explorer et rapporter des données.

Création d'un graphique croisé dynamique

Un graphique croisé dynamique est un graphique croisé dynamique basé sur un modèle de données et créé à partir de la fenêtre Power Pivot. Bien qu'il présente certaines fonctionnalités similaires à Excel PivotChart, il existe d'autres fonctionnalités qui le rendent plus puissant.

Supposons que vous souhaitiez créer un Power PivotChart basé sur le modèle de données suivant.

• Cliquez sur l'onglet Accueil sur le ruban dans la fenêtre Power Pivot.
• Cliquez sur Tableau croisé dynamique.
• Cliquez sur Graphique croisé dynamique dans la liste déroulante.

La boîte de dialogue Créer un graphique croisé dynamique s'affiche. Cliquez sur Nouvelle feuille de calcul.

• Cliquez sur le bouton OK. Un graphique croisé dynamique vide est créé sur une nouvelle feuille de calcul dans la fenêtre Excel. Dans ce chapitre, lorsque nous parlons de graphique croisé dynamique, nous faisons référence à Power PivotChart.

Comme vous pouvez le constater, toutes les tables du modèle de données sont affichées dans la liste Champs de graphique croisé dynamique.

• Cliquez sur le tableau Vendeur dans la liste Champs de graphique croisé dynamique.
• Faites glisser les champs - Vendeur et Région vers la zone AXIS.

Deux boutons de champ pour les deux champs sélectionnés apparaissent sur le graphique croisé dynamique. Ce sont les boutons de champ Axis. L'utilisation des boutons de champ consiste à filtrer les données affichées sur le graphique croisé dynamique.

• Faites glisser TotalSalesAmount de chacune des 4 tables - East_Sales, North_Sales, South_Sales et West_Sales vers la zone ∑ VALUES.

Comme vous pouvez le constater, les éléments suivants apparaissent sur la feuille de calcul -

• Dans le graphique croisé dynamique, le graphique à colonnes est affiché par défaut.
• Dans la zone LÉGENDE, ∑ VALUES est ajouté.
• Les valeurs apparaissent dans la légende du graphique croisé dynamique, avec les valeurs de titre.
• Les boutons de champ de valeur apparaissent sur le graphique croisé dynamique.

Vous pouvez supprimer la légende et les boutons de champ de valeur pour une apparence plus nette du graphique croisé dynamique.

• Cliquez sur le

  
  bouton dans le coin supérieur droit du graphique croisé dynamique.

• Désélectionnez Légende dans les éléments du graphique.

• Cliquez avec le bouton droit sur les boutons du champ de valeur.

• Cliquez sur Masquer les boutons de champ de valeur sur le graphique dans la liste déroulante.

Les boutons de champ de valeur sur le graphique seront masqués.

Notez que l'affichage des boutons de champ et / ou de la légende dépend du contexte du graphique croisé dynamique. Vous devez décider de ce qui doit être affiché.

Comme dans le cas de Power PivotTable, la liste des champs de Power PivotChart contient également deux onglets - ACTIVE et TOUS. De plus, il y a 4 domaines -

• AXE (Catégories)
• LÉGENDE (Série)
• ∑ VALEURS
• FILTERS

Comme vous pouvez le constater, la légende est remplie avec des valeurs ∑. En outre, les boutons de champ sont ajoutés au graphique croisé dynamique pour faciliter le filtrage des données affichées. Vous pouvez cliquer sur la flèche d'un bouton de champ et sélectionner / désélectionner les valeurs à afficher dans le Power PivotChart.

Combinaisons de tableaux et de graphiques

Power Pivot vous propose différentes combinaisons de Power PivotTable et de Power PivotChart pour l'exploration, la visualisation et la création de rapports de données.

Considérez le modèle de données suivant dans Power Pivot que nous utiliserons pour les illustrations -

Vous pouvez avoir les combinaisons de tableaux et de graphiques suivantes dans Power Pivot.

• Graphique et tableau (horizontal) - vous pouvez créer un Power PivotChart et un Power PivotTable, l'un à côté de l'autre horizontalement dans la même feuille de calcul.

Graphique et tableau (vertical) - vous pouvez créer un Power PivotChart et un Power PivotTable, l'un sous l'autre verticalement dans la même feuille de calcul.

Ces combinaisons et bien d'autres sont disponibles dans la liste déroulante qui apparaît lorsque vous cliquez sur Tableau croisé dynamique sur le ruban dans la fenêtre Power Pivot.

Hiérarchies dans Power Pivot

Vous pouvez utiliser les hiérarchies dans Power Pivot pour effectuer des calculs et pour explorer vers le haut et vers le bas les données imbriquées.

Considérez le modèle de données suivant pour les illustrations de ce chapitre.

Vous pouvez créer des hiérarchies dans la vue de diagramme du modèle de données, mais sur la base d'une seule table de données.

• Cliquez sur les colonnes - Sport, DisciplineID et Event dans le tableau de données Medal dans cet ordre. N'oubliez pas que l'ordre est important pour créer une hiérarchie significative.

• Faites un clic droit sur la sélection.

• Cliquez sur Créer une hiérarchie dans la liste déroulante.

Le champ de hiérarchie avec les trois champs sélectionnés lors de la création des niveaux enfants.

• Cliquez avec le bouton droit sur le nom de la hiérarchie.
• Cliquez sur Renommer dans la liste déroulante.
• Tapez un nom significatif, par exemple EventHierarchy.

Vous pouvez créer un tableau croisé dynamique puissant à l'aide de la hiérarchie que vous avez créée dans le modèle de données.

• Créez un tableau croisé dynamique puissant.

Comme vous pouvez le constater, dans la liste Champs de tableau croisé dynamique, EventHierarchy apparaît sous la forme d'un champ dans la table des médailles. Les autres champs de la table des médailles sont réduits et affichés en tant que champs supplémentaires.

• Cliquez sur la flèche
  
  devant EventHierarchy.
• Cliquez sur la flèche
  
  devant Plus de champs.

Les champs sous EventHierarchy seront affichés. Tous les champs du tableau Médailles seront affichés sous Plus de champs.

Ajoutez des champs au Power PivotTable comme suit -

• Faites glisser EventHierarchy vers la zone ROWS.
• Faites glisser Medal vers la zone ∑ VALUES.

Comme vous pouvez le constater, les valeurs du champ Sport apparaissent dans le Power PivotTable avec un signe + devant elles. Le nombre de médailles pour chaque sport est affiché.

• Cliquez sur le signe + avant Aquatics. Les valeurs du champ DisciplineID sous Aquatiques seront affichées.

• Cliquez sur l'enfant D22 qui apparaît. Les valeurs du champ Événement sous D22 seront affichées.

Comme vous pouvez le constater, le nombre de médailles est donné pour les épreuves, qui sont résumées au niveau des parents - DisciplineID, qui se résument davantage au niveau des parents - Sport.

Calculs à l'aide de la hiérarchie dans les tableaux croisés dynamiques Power

Vous pouvez créer des calculs à l'aide d'une hiérarchie dans un tableau croisé dynamique Power. Par exemple, dans la hiérarchie des événements, vous pouvez afficher le no. de médailles au niveau des enfants en pourcentage du non. de médailles au niveau de ses parents comme suit -

• Cliquez avec le bouton droit de la souris sur la valeur du nombre de médailles d'un événement.
• Cliquez sur Paramètres du champ de valeur dans la liste déroulante.

La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Cliquez sur l'onglet Afficher les valeurs sous.
• Cliquez sur la case Afficher les valeurs sous.
• Cliquez sur% du total de la ligne parent.

• Cliquez sur le bouton OK.

Comme vous pouvez le constater, les niveaux enfants sont affichés sous forme de pourcentage des totaux parents. Vous pouvez vérifier cela en additionnant les valeurs en pourcentage du niveau enfant d'un parent. La somme serait de 100%.

Exploration et exploration d'une hiérarchie

Vous pouvez rapidement explorer les niveaux d'une hiérarchie vers le haut et vers le bas dans un tableau croisé dynamique puissant à l'aide de l'outil d'exploration rapide.

• Cliquez sur une valeur du champ Événement dans le tableau croisé dynamique Power.

• Cliquez sur l'outil Exploration rapide -

  
  qui apparaît dans le coin inférieur droit de la cellule contenant la valeur sélectionnée.

La boîte EXPLORER avec l'option Explorer vers le haut apparaît. En effet, à partir de l'événement, vous ne pouvez explorer que vers le haut car il n'y a aucun niveau enfant en dessous.

• Cliquez sur Explorer vers le haut. Les données du tableau croisé dynamique puissant sont analysées jusqu'au niveau de la discipline.

• Cliquez sur l'outil Exploration rapide -

  
  qui apparaît dans le coin inférieur droit de la cellule contenant une valeur.

La boîte EXPLORER apparaît avec les options d'exploration vers le haut et vers le bas affichées. En effet, à partir de la discipline, vous pouvez accéder au sport ou descendre aux niveaux d'événements.

De cette façon, vous pouvez rapidement monter et descendre la hiérarchie dans un tableau croisé dynamique.

Utilisation d'un segment commun

Vous pouvez insérer des segments et les partager entre les Power PivotTables et Power PivotCharts.

• Créez un Power PivotChart et un Power PivotTable côte à côte horizontalement.

• Cliquez sur Power PivotChart.

• Faites glisser Discipline du tableau Disciplines vers la zone AXIS.

• Faites glisser Médaille du tableau Médailles vers la zone ∑ VALEURS.

• Cliquez sur Power PivotTable.

• Faites glisser Discipline du tableau Disciplines vers la zone ROWS.

• Faites glisser Médaille du tableau Médailles vers la zone ∑ VALEURS.

• Cliquez sur l'onglet ANALYSER dans OUTILS PIVOTABLES sur le ruban.
• Cliquez sur Insérer un segment.

La boîte de dialogue Insérer des segments s'affiche.

• Cliquez sur NOC_CountryRegion et Sport dans le tableau des médailles.
• Cliquez sur OK.

Deux slicers - NOC_CountryRegion et Sport apparaissent.

• Disposez-les et redimensionnez-les pour les aligner correctement à côté du Power PivotTable comme indiqué ci-dessous.

• Cliquez sur USA dans le NOC_CountryRegion Slicer.
• Cliquez sur Aquatics dans le Sport Slicer.

Le Power PivotTable est filtré sur les valeurs sélectionnées.

Comme vous pouvez le constater, le Power PivotChart n'est pas filtré. Pour filtrer Power PivotChart avec les mêmes filtres, vous pouvez utiliser les mêmes segments que vous avez utilisés pour le Power PivotTable.

• Cliquez sur NOC_CountryRegion Slicer.
• Cliquez sur l'onglet OPTIONS dans SLICER TOOLS sur le ruban.
• Cliquez sur Report Connections dans le groupe Slicer.

La boîte de dialogue Connexions de rapport s'affiche pour le segment NOC_CountryRegion.

Comme vous pouvez le constater, tous les Power PivotTables et Power PivotCharts du classeur sont répertoriés dans la boîte de dialogue.

• Cliquez sur le Power PivotChart qui se trouve dans la même feuille de calcul que le Power PivotTable sélectionné.

• Cliquez sur le bouton OK.

• Répétez pour Sport Slicer.

Le Power PivotChart est également filtré sur les valeurs sélectionnées dans les deux segments.

Ensuite, vous pouvez ajouter plus de détails au Power PivotChart et au Power PivotTable.

• Cliquez sur le Power PivotChart.
• Faites glisser le sexe vers la zone LÉGENDE.
• Cliquez avec le bouton droit sur le Power PivotChart.
• Cliquez sur Modifier le type de graphique.
• Sélectionnez Colonne empilée dans la boîte de dialogue Modifier le type de graphique.
• Cliquez sur le Power PivotTable.
• Faites glisser l'événement vers la zone ROWS.
• Cliquez sur l'onglet CONCEPTION dans OUTILS PIVOTABLES sur le ruban.
• Cliquez sur Présentation du rapport.
• Cliquez sur Formulaire de plan dans la liste déroulante.

Rapports esthétiques pour les tableaux de bord

Vous pouvez créer des rapports esthétiques avec Power PivotTables et Power PivotCharts et les inclure dans des tableaux de bord. Comme vous l'avez vu dans la section précédente, vous pouvez utiliser les options de mise en page du rapport pour choisir l'aspect et la convivialité des rapports. Par exemple, avec l'option - Afficher sous forme de contour et avec les lignes en bandes sélectionnées, vous obtiendrez le rapport comme indiqué ci-dessous.

Comme vous pouvez le constater, les noms de champ apparaissent à la place des libellés de ligne et des libellés de colonne et le rapport semble explicite.

Vous pouvez sélectionner les objets que vous souhaitez afficher dans le rapport final dans le volet Sélection. Par exemple, si vous ne souhaitez pas afficher les segments que vous avez créés et utilisés, vous pouvez simplement les masquer en les désélectionnant dans le volet Sélection.

Excel Power View permet une visualisation interactive des données qui encourage l'exploration intuitive des données ad hoc. Les visualisations de données sont polyvalentes et dynamiques, facilitant ainsi l'affichage des données avec un seul rapport Power View.

Vous pouvez gérer de grands ensembles de données couvrant plusieurs milliers de lignes à la volée en passant d'une visualisation à une autre, en explorant et en descendant les données et en affichant l'essence des données.

Les rapports Power View sont basés sur le modèle de données que l'on peut appeler la base de données Power View et qui optimise la mémoire permettant des calculs et des affichages plus rapides des données. Un modèle de données typique sera comme indiqué ci-dessous.

Dans ce chapitre, vous comprendrez les principales fonctionnalités des rapports Power View que vous pouvez incorporer dans votre tableau de bord.

Visualisations Power View

Power View fournit différents types de visualisations de données -

Table

La visualisation de table est la visualisation la plus simple et par défaut. Si vous souhaitez créer une autre visualisation, la première table sera créée que vous devez convertir vers la visualisation requise par les options de visualisation de commutation.

Matrice

Carte

Graphiques

Power View a les types de graphiques suivants dans les visualisations -

• Graphique en ligne
• Diagramme à bandes
• Graphique à colonnes
• Diagramme de dispersion
• Graphique à bulles
• Diagramme circulaire

Line Chart

Bar Chart

Column Chart

Scatter Chart and Bubble Chart

Pie Chart

Map

Map with Pie Charts

Combinaison de visualisations Power View

Contrairement aux graphiques Excel, les visualisations Power View sont puissantes car elles peuvent être affichées sous forme de combinaison, chacune représentant et / ou mettant en évidence des résultats significatifs.

Par exemple, vous pouvez avoir trois visualisations dans Power View -

• Table visualization - affichage du nombre de pays, de médailles et de médailles.

• Stacked Column chart visualization - affichage du pays, du sexe et du nombre de médailles.

• Pie chart visualization - affichage des médailles, du sexe et du nombre de médailles.

Nature interactive des graphiques dans les visualisations Power View

Supposons que vous cliquiez sur une tranche de tarte dans Power View ci-dessus. Vous observerez ce qui suit -

• La tranche de tarte sur laquelle vous avez cliqué sera mise en surbrillance tandis que le reste des tranches de tarte sera grisée.

• Le tableau affichera uniquement les données correspondant à la tranche en surbrillance.

• La colonne Clustered mettra en évidence les données correspondant à la tranche en surbrillance et le reste du graphique sera grisé.

Cette fonctionnalité vous aide à permettre à votre public de visualiser les résultats de grands ensembles de données pour explorer les points de données importants.

Trancheurs dans Power View

Vous pouvez utiliser des segments communs dans Power View pour filtrer les données affichées par toutes les visualisations dans Power View.

Par exemple, dans Power View suivant, vous avez 2 visualisations -

• Graphique à barres empilées affichant le nombre de médailles par pays et par médaille.

• Diagramme à colonnes empilées affichant le nombre de médailles par sport et par médaille.

Supposons que vous ayez deux segments - un pour le sexe et un pour la saison, les données des deux graphiques seront filtrées vers les champs sélectionnés dans les segments.

Tuiles dans Power View

Dans Power View, les vignettes vous aident à sélectionner un point de données d'un champ et à afficher les valeurs correspondantes. Les tuiles peuvent être utilisées dans les visualisations Tableau, Matrice, Carte, Graphique à barres empilées et Carte.

Mosaïques dans la visualisation de tableau

Mosaïques dans la visualisation matricielle

Tuiles dans la visualisation de cartes

Mosaïques dans la visualisation de graphique à barres empilées

Tuiles dans la visualisation de carte

Les tuiles peuvent également être utilisées avec une combinaison de visualisations.

Vous pouvez également utiliser la nature interactive des graphiques dans de telles visualisations.

Rapports Power View

Vous pouvez produire des rapports Power View esthétiques que vous pouvez inclure dans votre tableau de bord.

Cela pourrait être fait en choisissant un arrière-plan approprié, en choisissant la police, la taille de la police, les échelles de couleur, etc.

Les indicateurs clés de performance (KPI) sont des mesures quantifiables pour évaluer ce qui est réalisé par rapport aux buts / cibles / objectifs commerciaux fixés. Dans les tableaux de bord, les KPI auront nécessairement une position pour afficher visuellement où se trouve une personne / un service / une organisation par rapport à ce qu'elle est censée être.

Voici quelques exemples d'indicateurs de performance clés:

• Le service commercial d'une organisation peut utiliser un indicateur de performance clé pour mesurer la marge brute mensuelle par rapport à la marge brute projetée.

• Le service de comptabilité peut mesurer les dépenses mensuelles par rapport aux revenus pour évaluer les coûts.

• Le service des ressources humaines peut mesurer le roulement du personnel trimestriel.

• Les professionnels utilisent fréquemment des indicateurs de performance clés regroupés dans un tableau de bord d'entreprise pour obtenir un résumé historique rapide et précis du succès commercial ou pour identifier les tendances.

Les tableaux de bord consultés publiquement ou de manière sélective présentent des KPI surveillés en permanence et sont donc choisis comme les meilleurs outils de surveillance et de reporting.

Composants d'un KPI

Un KPI contient essentiellement trois composants -

• Valeur de base
• Valeur cible / objectif
• Status

Bien que ce soit le statut qui intéresse, la valeur de base et la valeur cible sont également tout aussi importantes car un indicateur de performance clé n'a pas besoin d'être statique et peut subir des modifications au fil du temps.

Dans Excel, la valeur de base, la valeur cible et l'état sont définis comme indiqué dans les sections suivantes.

Valeur de base

Une valeur de base est définie par un champ calculé qui se résout en une valeur. Le champ calculé représente la valeur actuelle de l'élément dans cette ligne du tableau ou de la matrice. Par exemple, agrégat des ventes, bénéfice pour une période donnée, etc.

Valeur cible

Une valeur cible (ou objectif) est définie par un champ calculé qui se résout en une valeur ou par une valeur absolue. C'est la valeur par rapport à laquelle la valeur actuelle est évaluée. Cela pourrait être l'un des suivants -

• Un nombre fixe qui est l'objectif que toutes les lignes doivent atteindre. Par exemple, objectif de vente pour tous les vendeurs.

• Un champ calculé qui peut avoir un objectif différent pour chaque ligne. Par exemple, Budget (champ calculé), par département dans une organisation.

Seuils d'état et état

Le statut est l'indicateur visuel de la valeur. Excel propose différentes manières de visualiser l'état par rapport à la valeur cible.

• Vous pouvez utiliser un graphique à puces pour visualiser les KPI. Vous pouvez illustrer les seuils d'état par des régions ombrées d'une colonne et l'état sous forme de colonne superposée aux seuils d'état.

• Vous pouvez également définir et visualiser les KPI dans Power View.

Définition des KPI dans Excel

Pour définir un KPI, vous devez disposer des éléments suivants:

• Valeur de base
• Valeur cible
• Seuils d'état (par exemple, mauvais, bon, excellent)

Par exemple, pour définir un KPI afin de surveiller les performances des ventes, vous devez effectuer les opérations suivantes:

• Identifiez la ou les cellules contenant les valeurs calculées des ventes totales. Ceci est pour la valeur de base.

• Définissez une valeur cible qui peut être absolue ou variable.

• Définissez les seuils d'état qui vous aident à visualiser l'état.

Visualiser les KPI avec des graphiques à puces

Vous pouvez visualiser les KPI avec des graphiques à puces, dans lesquels les éléments suivants seront clairement représentés.

• La cible
• Seuils d'état
• La valeur (statut)

Visualisation des KPI avec Power View

Vous pouvez visualiser les KPI définis dans Power View à l'aide d'icônes.

Vous pouvez également produire un rapport esthétique dans Power View avec les indicateurs de performance clés pouvant être inclus dans votre tableau de bord.

Comme vous pouvez le constater, dans Power View, vous pouvez présenter les résultats comme suit -

• Visualisation de tableau avec des icônes pour afficher l'état des KPI.

• Visualisation de graphique à barres empilées à 100% illustrant le pourcentage atteint par rapport à l'objectif. Vous pouvez également remarquer qu'il donne une comparaison claire des performances de tous les vendeurs.

• Visualisation de la carte illustrant le statut KPI des vendeurs ainsi que la région à laquelle ils appartiennent. Vous pouvez faire défiler de manière interactive les vignettes pour afficher les résultats pour différentes régions, ce qui permettrait également d'évaluer les performances par région.

Dans les chapitres précédents, vous avez découvert diverses fonctionnalités d'Excel qui vous sont utiles pour configurer votre tableau de bord. Dans ce chapitre, vous apprendrez à créer un tableau de bord, c'est-à-dire les étapes nécessaires pour mettre le tableau de bord en place. Vous apprendrez également à connaître les choses à faire et à ne pas faire concernant les tableaux de bord.

Comme tout tableau de bord est basé sur l'intention spécifique de ce qui intéresse le plus le public, les composants du tableau de bord et la disposition du tableau de bord varient d'un cas à l'autre.

Préparation initiale

La première étape de la création d'un tableau de bord est la préparation initiale. Prenez le temps de comprendre ce qui suit -

• Why do you need the dashboard?- Ce tableau de bord est-il destiné à une tâche spécifique, comme montrer l'état d'un projet, ou doit-il atteindre un objectif plus large, comme mesurer les performances de l'entreprise? Comprendre pourquoi vous créez le tableau de bord vous guidera dans la conception.

• What purpose the dashboard will serve?- Votre tableau de bord ne doit mettre en évidence que les données qui ajoutent de la valeur. Vous devez comprendre les données requises. Tout ce qui est en dehors de cela est inutile.

• What is the source of data?- Vous devez comprendre d'où proviennent les données. Il peut s'agir simplement d'une feuille de calcul Excel ou via des connexions de données à votre classeur Excel à partir de diverses sources de données dynamiques.

• Who is the audience for the dashboard?- Est-ce pour un manager, un cadre, une partie prenante, un fournisseur externe ou un grand public? Comprenez leurs exigences et leurs préférences, telles que le temps dont ils disposent pour consulter le tableau de bord, le niveau de détail auquel ils s'attendent et la manière dont ils souhaitent digérer les informations. Par exemple, lors du choix d'un type de graphique, connaître le public vous aide à décider si vous devez afficher les relations entre les valeurs ou si vous devez établir une comparaison spécifique.

• Does the dashboard need to be static or dynamic?- Le tableau de bord peut-il être mis à jour périodiquement, par exemple, chaque semaine ou chaque mois, ou doit-il être mis à jour pour rationaliser en permanence les modifications de données qui se produisent au niveau du backend? Ce choix changera la façon dont vous construisez le tableau de bord.

• Does the dashboard need to be just a display or is it to be interactive?- Le tableau de bord peut-il avoir un accès en lecture seule ou devez-vous fournir des commandes / fonctionnalités interactives qui permettent à certaines personnes d'explorer les données selon les besoins? Ce choix changera également la façon dont vous construisez le tableau de bord.

Une fois que vous avez des réponses à ces questions, décidez des fonctionnalités d'Excel dont vous avez besoin et dont vous n'avez pas besoin. En effet, votre objectif et votre expertise sont de produire un tableau de bord efficace et adapté à votre objectif.

Ensuite, identifiez les composants du tableau de bord. Il peut s'agir de texte, de tableaux, de graphiques, de contrôles interactifs, etc. Décidez de la disposition du tableau de bord avec ces composants.

Maquette de votre tableau de bord Excel sur une diapositive PowerPoint. Dessinez des boîtes pour chaque composant pour avoir une idée de la disposition et ajoutez des croquis rapides des composants que vous souhaitez inclure. Vous pouvez également le faire sur une feuille de papier. Obtenez l'approbation de cette maquette de la direction et / ou du public clé avant de commencer à travailler sur le tableau de bord réel. Cela vous fera gagner du temps lors des retouches. Cependant, il est fort possible que vous deviez modifier certaines modifications du tableau de bord à mesure que le tableau de bord entre en service et que vous recevez des commentaires. Mais la maquette de tableau de bord approuvée est un très bon début pour votre travail.

Organiser la source de données pour le tableau de bord Excel

Avant de créer le tableau de bord dans Excel, vous devez organiser la source de données. Dans Excel, cela est possible de différentes manières -

• Si les données ne sont qu'un tableau Excel, établissez un lien vers votre classeur à partir du classeur où les données seront mises à jour.

• Si les données proviennent de plusieurs tableaux Excel ou si elles proviennent de différentes sources de données, il est judicieux de créer le modèle de données dans votre classeur.

Vous pouvez importer périodiquement les données dans le classeur ou établir des connexions de données afin d'actualiser les données au moment de leur mise à jour, selon que le tableau de bord doit être statique ou dynamique.

Configurer le classeur du tableau de bord Excel

Une fois que vous avez organisé les données, vous devez structurer votre classeur. Insérez deux à trois feuilles de calcul dans le classeur - une feuille de calcul pour votre tableau de bord et une ou deux feuilles de calcul pour les données (données ou tableau croisé dynamique / graphiques croisés dynamiques ou rapports Power View, que vous pouvez masquer). Cela vous aidera à organiser et à maintenir votre classeur Excel.

Préparer les données pour le tableau de bord Excel

Sur la base des choix que vous avez, c'est-à-dire des réponses que vous avez aux questions de l'étape de préparation initiale, préparez les données pour le tableau de bord Excel. Les données peuvent être l'une des suivantes -

• Résultats de l'analyse des données
• Résultats de l'exploration des données
• Données issues de calculs sur les données d'entrée
• Récapitulation des données à partir de tableaux croisés dynamiques ou de tableaux PowerPivot

Sélectionnez les composants du tableau de bord

Vous avez découvert les différentes fonctionnalités d'Excel que vous pouvez utiliser dans un tableau de bord. En fonction de vos besoins pour le tableau de bord disponible, sélectionnez l'une des fonctionnalités Excel suivantes pour les composants du tableau de bord.

• Tables
• Sparklines
• Mise en forme conditionnelle.
• Charts
• Slicers
• Contrôles interactifs
• PivotTables
• PivotCharts
• Tableaux PowerPivot
• Graphiques PowerPivot
• Rapports Power View
• KPIs

La sélection des composants du tableau de bord vous aidera à vous aligner sur votre disposition de maquette de tableau de bord approuvée.

Identifiez les composants statiques et dynamiques et les composants qui doivent être regroupés pour les segments, le cas échéant.

Identifier les parties du tableau de bord pour la mise en évidence

Identifiez les parties du tableau de bord qui nécessitent une attention immédiate, comme le% achevé ou l'état actuel. Vous pouvez utiliser une police plus grande et une police et une couleur de police frappantes pour ceux-ci.

Décidez de la quantité de couleur que vous souhaitez incorporer dans votre tableau de bord. Cette décision peut être basée sur l'audience du tableau de bord. Si le tableau de bord est destiné aux cadres et / ou aux managers, choisissez des couleurs ayant un impact sur la visualisation des résultats affichés. Vous pouvez ajouter une couleur d'arrière-plan de tableau de bord pour faire ressortir les composants du tableau de bord. Vous pouvez utiliser le même code couleur pour des graphiques similaires ou des résultats associés. Vous pouvez également utiliser la mise en forme conditionnelle.

Une sélection rigoureuse des parties du tableau de bord à mettre en évidence le rend efficace.

Construire le tableau de bord

Il s'agit de l'étape cruciale et finale de la création de votre tableau de bord Excel. Cette étape consiste à assembler les composants de votre tableau de bord que vous pouvez effectuer de manière efficace et efficiente à l'aide de la caméra Excel. Vous apprendrez à utiliser la caméra Excel dans la section suivante.

Une fois les composants du tableau de bord assemblés, donnez-lui une touche finale -

• Donnez un titre au tableau de bord.
• Incorporez un horodatage.
• Incluez les informations de copyright, si nécessaire.

Dans les prochains chapitres, vous apprendrez comment utiliser ces étapes pour créer un tableau de bord basé sur quelques exemples. Il n'y a pas de règle ou de disposition universelle pour un tableau de bord en dehors de quelques points communs. Tout dépend de vos besoins. Et votre objectif est de produire un tableau de bord efficace.

Utilisation de la caméra Excel

La caméra Excel vous aide à capturer des instantanés à partir de vos feuilles de calcul et à les placer dans une feuille de calcul différente. Par exemple, vous pouvez capturer un tableau avec une mise en forme conditionnelle sur une feuille de calcul et le placer sur votre tableau de bord. Chaque fois que les données sont mises à jour, le tableau de bord est actualisé pour afficher les données modifiées.

Vous pouvez inclure la caméra Excel dans votre barre d'accès rapide comme suit -

• Cliquez avec le bouton droit de la souris sur la petite flèche de la barre d'outils d'accès rapide.
• Cliquez sur Plus de commandes dans la liste Personnaliser la barre d'outils d'accès rapide.

La boîte de dialogue Options Excel s'affiche.

• Cliquez sur la barre d'outils d'accès rapide.
• Sélectionnez Toutes les commandes sous Choisir les commandes dans.
• Cliquez sur Caméra dans la liste des commandes.

• Cliquez sur le bouton Ajouter ». L'appareil photo apparaît dans la liste de droite.

• Cliquez sur le bouton OK. L'icône de l'appareil photo apparaît dans la barre d'outils d'accès rapide de votre classeur.

Vous pouvez utiliser l'appareil photo Excel comme suit -

• Sélectionnez la plage de cellules à capturer.

• Cliquez sur la caméra dans la barre d'outils d'accès rapide.

La plage de cellules apparaît avec une bordure en pointillés.

• Cliquez sur la feuille de calcul où vous souhaitez placer la région capturée. Cela peut être votre feuille de tableau de bord.

• Cliquez à l'endroit où vous souhaitez le placer.

La région capturée apparaît à ce stade.

Chaque fois que vous apportez des modifications aux données d'origine, les modifications seront reflétées dans le tableau de bord.

Date et heure sur le tableau de bord Excel

Vous pouvez incorporer une date ou un horodatage sur votre tableau de bord pour afficher la date de la dernière mise à jour des données. Vous pouvez le faire en utilisant les fonctions Excel AUJOURD'HUI () et MAINTENANT ().

Pour incorporer un horodatage, entrez = AUJOURD'HUI () dans la cellule où vous souhaitez placer le timbre date sur votre feuille de calcul de données.

Cela affichera la date actuelle chaque fois que le classeur est mis à jour.

• Assurez-vous que la cellule dans laquelle vous avez entré la fonction AUJOURD'HUI () est formatée au format de date que vous souhaitez afficher.

• Capturez l'écran avec la caméra et placez-le sur le tableau de bord.

La date sur le tableau de bord reflète la date de la dernière mise à jour du classeur.

Vous pouvez intégrer une date et un horodatage sur votre tableau de bord de la même manière avec la fonction NOW ().

• Entrez = MAINTENANT () dans la cellule où vous souhaitez placer la date et l'heure sur votre feuille de calcul de données.

• Assurez-vous que vous avez le bon format pour la date et l'heure.
• Capturez l'écran avec la caméra et placez-le sur le tableau de bord.

L'horodatage sera incorporé au tableau de bord et reflétera la date et l'heure de la dernière mise à jour du classeur.

Tester, échantillonner et améliorer le tableau de bord

Vous devez tester votre tableau de bord pour vous assurer qu'il affiche les données avec précision.

• Testez-le dans différents scénarios possibles.
• Testez les mises à jour précises (statiques ou dynamiques comme c'est le cas).
• Testez les commandes interactives, le cas échéant.
• Testez l'apparence et la sensation.

Vous devrez peut-être faire quelques essais pour vous assurer que votre tableau de bord est comme vous le souhaitez.

L'étape suivante consiste à faire évaluer le tableau de bord par un échantillon d'audience, en particulier ceux qui ont approuvé votre tableau de bord de maquette. Comme ils utiliseront le tableau de bord, ils auront sans aucun doute leur avis sur son utilisation et son efficacité. Ces commentaires vous aident à vous assurer que votre tableau de bord est efficace. N'hésitez pas à demander des commentaires.

Une fois que vous avez reçu les commentaires, améliorez-les avec les modifications requises, le cas échéant. Votre tableau de bord Excel est prêt à être utilisé.

Partagez le tableau de bord

Vous devez mettre le tableau de bord Excel à la disposition du public visé. Vous pouvez le faire de plusieurs manières.

• Envoyez le classeur de tableau de bord Excel par courrier électronique (vous devez masquer les feuilles de calcul autres que la feuille de calcul du tableau de bord. Vous pouvez également protéger le classeur.).

• Enregistrez le classeur de tableau de bord Excel sur un lecteur réseau partagé.

• Partagez le tableau de bord en ligne.

Si le tableau de bord Excel est statique, vous pouvez l'envoyer par courrier, mais s'il est dynamique ou possède des contrôles interactifs, il doit avoir une connexion aux données du backend et doit donc être partagé en ligne.

Vous pouvez partager un tableau de bord Excel en ligne avec l'une des options suivantes -

• Microsoft OneDrive.

  • Avec votre compte Windows Live, vous aurez accès à OneDrive où vous pourrez publier et partager des documents.

• Nouveau Microsoft Office Online.

• Microsoft SharePoint.

Vous pouvez également enregistrer le fichier du classeur Excel en tant que fichier Acrobat Reader (.pdf) et le publier sur le Web. Mais, encore une fois, cette option ne concerne que les tableaux de bord statiques.

Conseils pour des tableaux de bord Excel efficaces

Pour rendre un tableau de bord Excel efficace, vous devez faire certaines choses et en éviter certaines. Voici quelques conseils pour des tableaux de bord Excel efficaces:

• Rester simple.

  • Un tableau de bord simple et facile à comprendre est bien plus efficace qu'un tableau de bord jazzy. N'oubliez pas que ce sont les données qui doivent être mises en évidence.

  • Selon Glenna Shaw, vous devez trouver un équilibre entre rendre votre tableau de bord suffisamment attrayant pour retenir l'intérêt de votre public, mais pas trop stylisé pour masquer les informations affichées.

  • Mieux vaut éviter les effets 3D, les dégradés, les formes supplémentaires et les gadgets inutiles.

  • Si vous pouvez obtenir un affichage emphatique avec une mise en forme conditionnelle ou des graphiques sparkline, préférez utiliser des tableaux aux graphiques.

• Utilisez la mise en forme conditionnelle Excel.

  • Utilisez la mise en forme conditionnelle Excel qui fournit plusieurs options pour mettre à jour automatiquement en fonction des valeurs de votre tableau.

• Sélectionnez les types de graphiques appropriés.

  • N'oubliez pas qu'il n'y a pas de règle générale pour l'utilisation d'un type de graphique. Parfois, les types de graphiques conventionnels tels que les histogrammes, les graphiques à barres, les graphiques en anneau, etc. transmettent le message avec emphase plus que les graphiques sophistiqués qui commencent à exister.

  • Vous pouvez utiliser la commande Excel Recommander les graphiques pour évaluer initialement les types de graphiques appropriés.

  • Comme vous pouvez changer le type de graphique avec une seule commande Excel - Modifier le type de graphique, vous pouvez jouer pour visualiser l'affichage et choisir le graphique approprié.

• Utilisez des commandes interactives.

  • Utilisez des commandes interactives telles que des barres de défilement, des boutons d'option (radio) et des cases à cocher qui aident l'utilisateur à visualiser facilement et efficacement les différents aspects des données.

• Utilisez le modèle de données Excel pour le Big Data.

  • Si vous disposez de grands ensembles de données provenant de diverses sources de données, il est préférable d'utiliser le modèle de données Excel qui peut gérer des milliers de lignes de données avec l'optimisation de la mémoire et peut regrouper des tables de données avec des relations.

• Choisissez les couleurs appropriées.

  • Soyez prudent dans le choix des couleurs. Utilisez les couleurs judicieusement pour fournir un impact suffisant, mais ne remplacez pas leur objectif. De plus, si le public est susceptible d'inclure des daltoniens, évitez d'utiliser les couleurs rouge et verte. Dans de tels cas, bien que les symboles de feux de signalisation semblent efficaces pour les données affichées, ils ne sont pas adaptés au tableau de bord. Utilisez plutôt une échelle de gris.

• Utilisez des slicers.

  • Les segments sont plus efficaces que les listes déroulantes car ils ont un impact visuel.

  • Vous pouvez regrouper des graphiques, des tableaux croisés dynamiques et des graphiques croisés dynamiques pour utiliser un segment commun.

• Regroupez les composants du tableau de bord Excel.

  • Vous pouvez ajouter un intérêt visuel à votre tableau de bord en insérant une forme, telle qu'un rectangle, et en plaçant les composants de votre tableau de bord qui peuvent être regroupés au-dessus de cette forme. Par exemple, si vous utilisez un segment commun, vous pouvez regrouper tous les composants du tableau de bord qui partagent le segment.

• Utilisez les hiérarchies de données Excel.

  • Si vos données ont des hiérarchies de données innées, définissez-les dans le modèle de données et utilisez-les pour explorer et explorer de manière interactive les données.

• Évitez la mise en page du tableau de bord encombrée.

  • N'oubliez pas que l'affichage de plus d'informations que ce qui est nécessaire submergera le public et battra l'attention sur le but réel.

  • N'incluez aucune donnée ou graphique dans votre tableau de bord, si vous pouvez vous en débarrasser.

  • Il s'agit d'un point de contrôle important lors du test de votre tableau de bord. Évaluez chaque composant du tableau de bord si cela est nécessaire et suffisant.

  • Les composants du tableau de bord et la mise en page doivent prendre en charge l'objectif unique de votre tableau de bord.

Il existe plusieurs types de tableaux de bord possibles. Il n'y a pas d'ensemble de normes pour les tableaux de bord, sauf pour certaines choses à faire et à ne pas faire. Vous pouvez comprendre le but et utiliser votre imagination pour sélectionner les composants et la disposition de votre tableau de bord. Cependant, vous devez être sur la même page que les spectateurs du tableau de bord et donc leurs préférences doivent être prises en compte pour le rendre efficace. Un tableau de bord peut être modifié au fil du temps en fonction du contexte et des besoins changeants.

Comme vous l'avez appris dans les sections précédentes, le but d'un tableau de bord est d'afficher efficacement les données nécessaires et suffisantes avec un impact visuel supplémentaire selon les besoins du public potentiel. La disposition du tableau de bord et de ses composants varie selon les différents utilisateurs en fonction de leurs préférences.

Exemple ─ Tableau de bord exécutif

Dans ce chapitre, vous découvrirez un exemple de tableau de bord exécutif. Encore une fois, ce tableau de bord peut varier d'une entreprise à l'autre en fonction des exigences et des préférences.

Indicateurs clés

Les tableaux de bord exécutifs se concentrent normalement sur les performances de l'entreprise, les revenus, les bénéfices, les nouveaux clients, etc. Par conséquent, les tableaux de bord exécutifs affichent essentiellement les KPI. L'impact visuel nécessaire dans ce cas serait de fournir des résultats en un coup d'œil, avec peu de détails car les cadres n'auront normalement pas beaucoup de temps pour s'attarder sur les détails à moins que cela ne soit absolument nécessaire.

Un cadre peut être chef d'entreprise ou chef d'un service spécifique dans une grande entreprise. Le chef d'entreprise peut être un CEO (Chief Executive Officer) ou un MD (Managing Director). L'exécutif serait surtout intéressé par une brève description des KPI opérationnels des départements et de la performance de l'entreprise dans son ensemble.

KPI opérationnels par département

Voici quelques exemples de départements et de leurs indicateurs de performance clés opérationnels -

• Finance
  • Revenue
  • Expenses
  • Profits
• Ventes
  • Performances régionales
  • nouveaux clients
• Ressources humaines (RH)
  • Recruitments
  • Attrition

Structure du tableau de bord Excel

Pour l'affichage des KPI, la plupart des dirigeants préfèrent toujours les graphiques à jauge aux graphiques à puces. Vérifiez les préférences avant de commencer à concevoir le tableau de bord.

Un exemple de tableau de bord Excel Executive simple peut être présenté ci-dessous.

Exemple - Tableau de bord de gestion de projet

Le but d'un tableau de bord de gestion de projet est de fournir l'état d'exécution du projet et les principales fonctionnalités du projet à être disponibles à un instantané. Un chef de projet doit utiliser ces données représentées non seulement pour le suivi du projet, mais également pour rendre compte à la direction et au client.

Indicateurs clés

Les paramètres clés de la gestion de projet seraient les suivants:

• État d'achèvement de la tâche
• État des risques
• État des problèmes
• Budget du projet par rapport aux chiffres réels

Composants pour l'aperçu du projet

Pour obtenir un instantané du projet, les principaux composants qui seraient utiles seraient les suivants:

• Aperçu du plan de projet
• Statut des tâches par pourcentages
• Un aperçu des risques ouverts

Structure du tableau de bord Excel

Un exemple de tableau de bord de gestion de projet Excel se présente comme indiqué ci-dessous.

Exemple - Tableau de bord de gestion des ventes

La gestion des ventes implique un examen de la région de données et une analyse trimestrielle afin d'obtenir un aperçu de la tendance des ventes et des prévisions des ventes. Cela aiderait à comparer les performances de vente avec les concurrents, à évaluer l'équipe de vente, à identifier les forces et faiblesses potentielles et à planifier l'avenir.

Indicateurs clés

Les indicateurs clés impliqués dans la gestion des ventes sont les suivants:

• Ventes totales par région et par mois
• Tendance des ventes sur le trimestre
• Les prévisions de vente

Composants pour la gestion des ventes

Pour représenter les métriques données ci-dessus dans un tableau de bord, les fonctionnalités Excel suivantes peuvent être utilisées -

• Tableau Excel pour afficher les valeurs de vente et les tendances (Sparklines).
• Ventes avec histogramme groupé.
• Tendance des ventes avec un graphique linéaire et une courbe de tendance - Linéaire.
• Prévisions de ventes avec un graphique en courbes et une ligne de tendance - Prévision linéaire.

Structure du tableau de bord Excel

Un exemple de tableau de bord de gestion des ventes sera comme indiqué ci-dessous -

Exemple ─ Tableau de bord de gestion de la formation

La direction de la formation serait normalement intéressée d'avoir une vue d'ensemble de l'argent dépensé et de la couverture de la formation, afin de se comparer avec les concurrents. En plus de cela, le retour d'information sur la formation donné par les stagiaires aiderait la direction à décider où tirer parti des ressources de formation.

Indicateurs clés

Les paramètres clés de la gestion de la formation seraient les suivants:

• Budget vs dépenses.

• Temps de formation - planifié vs réel.

• Couverture de la formation - Nombre de personnes ciblées par rapport au nombre de personnes réellement formées. Cela peut être exprimé en pourcentage.

• Commentaires sur la formation - Pour chacune des formations organisées, le feedback moyen des stagiaires, sur l'échelle 1 - 5 (1 - le plus bas, 5 - le plus élevé)

Composants du tableau de bord de gestion de la formation

Vous pouvez choisir les fonctionnalités Excel suivantes en tant que composants du tableau de bord, en fonction des métriques données ci-dessus.

• Diagramme à barres groupées pour - Budget vs dépenses.

• Graphique en aires pour - Temps prévu par rapport au temps réel pour la formation.

• Graphique du thermomètre pour - Couverture de la formation - en pourcentage réel par rapport à l'objectif de 100%.

• Diagramme à colonnes groupées pour - Commentaires sur la formation pour les formations menées.

Structure du tableau de bord Excel

Un exemple de tableau de bord Excel de gestion de la formation peut être comme indiqué ci-dessous -

Exemple ─ Tableau de bord de gestion des services / support

La gestion des services ou le support ou le service d'assistance implique la réception des tickets de service et la résolution des problèmes le plus rapidement possible. Ainsi, un tableau de bord mis à jour quotidiennement permettrait d'améliorer la gestion des services et ainsi d'améliorer la satisfaction client.

Indicateurs clés

Les indicateurs clés pour la gestion des services seraient les suivants:

• Nombre de billets reçus - soutien par personne.
• Nombre de tickets résolus - assistance par personne
• Moy. Rapidité de résolution - soutenir la personne - en supposant 8 heures de travail.
• Résolution% - soutenir la personne.
• Nombre total de billets reçus et nombre total de billets résolus.
• Résolution%.
• Moy. Scores de satisfaction reçus - soutenir la personne.
• Score de satisfaction global.

Composants du tableau de bord de gestion des services

Les fonctionnalités Excel qui peuvent être utilisées pour représenter les métriques données ci-dessus en tant que composants d'un tableau de bord seraient les suivantes:

• Tableau Excel pour 1 à 5 avec mise en forme conditionnelle appliquée à 5.
• Graphique à puces pour 6.
• Graphique à barres groupées pour 7.
• Graphique à puces pour 8.

De plus, vous pouvez inclure la date qui correspond aux données du tableau de bord, avec la fonction Excel - AUJOURD'HUI ().

Structure du tableau de bord Excel

Un exemple de tableau de bord de gestion des services peut être comme indiqué ci-dessous -

Tableaux de bord - Plus d'exemples

Le tableau de bord peut littéralement être utilisé pour afficher tous les résultats nécessitant une attention particulière. Les exemples que vous avez vus dans les chapitres précédents ne sont qu'une sélection manuelle de diverses applications où des tableaux de bord sont utilisés. En outre, les composants du tableau de bord et la disposition du tableau de bord dans le même but peuvent également varier en fonction des préférences de l'utilisateur. Il n'y a donc pas de formats de tableau de bord standard.

Dans ce chapitre, vous aurez un bref aperçu de certains autres domaines dans lesquels les tableaux de bord sont utilisés. Vous pouvez utiliser votre propre imagination pour créer un tableau de bord basé sur les données dont vous disposez et l'objectif qu'il doit servir.

Tableau de bord olympique

Vous pouvez avoir un tableau de bord affichant les résultats de l'analyse des données des données olympiques. Voici un exemple de tableau de bord créé à partir de plus de 35000 lignes de données à l'aide du modèle de données Excel et d'Excel Power View.

Tableau de bord du tourisme

Un exemple de tableau de bord du tourisme concernant le nombre de visiteurs sur un site spécifique peut être présenté ci-dessous.

Tableau de bord de gestion hospitalière

Un tableau de bord de gestion hospitalière est une forme de tableau de bord exécutif avec le niveau de détail requis par le gestionnaire spécifique. Un échantillon utilisé dans un hôpital est présenté ci-dessous.

Tableau de bord du restaurant

Un exemple de tableau de bord utilisé dans un restaurant est présenté ci-dessous.

Tableau de bord des sports

L'arène sportive est l'endroit le plus recherché pour les tableaux de bord. Chaque sport aura un tableau de bord en direct affichant les statistiques requises pour le jeu en cours. Un exemple de tableau de bord est illustré ci-dessous.