Comment créer les graphiques pour l’analyse financière avec Microsoft Excel

■■ Comment créer des graphiques combinés?

■■ Comment créer un axe secondaire?

■■ Comment gérer les données manquantes?

■■ Comment gérer l’apparence des données cachées?

■■ Comment puis-je utiliser des images pour ajouter du bling à mes graphiques à colonnes?

■■ J’ai représenté les données de ventes annuelles dans un graphique à colonnes et les années n’apparaissent pas sous forme d’étiquettes de colonnes.

Qu’ai-je fait de mal?

■■ Comment puis-je inclure des étiquettes de données et des tableaux de données dans mes graphiques?

■■ Comment puis-je utiliser Excel  pour placer des étiquettes de données sur un graphique en fonction du contenu des cellules?

■■ Comment puis-je suivre les performances de la force de vente au fil du temps?

■■ Comment puis-je créer un graphique à bandes pour vérifier si l’inventaire se situe dans des niveaux acceptables?

■■ Comment puis-je stocker un graphique en tant que modèle?

■■ Comment puis-je utiliser un diagramme de thermomètre pour représenter la progression par rapport à une cible?

■■ Comment créer des étiquettes de graphiques dynamiques?

■■ Comment puis-je utiliser des cases à cocher pour contrôler les séries à représenter?

■■ Comment puis-je utiliser une zone de liste pour choisir la série à représenter?

■■ Comment créer un diagramme de Gantt?

■■ Comment puis-je créer un graphique basé sur des données triées?

■■ Comment puis-je créer un histogramme qui se met à jour automatiquement lorsque j’inclus de nouvelles données?

■■ Comment puis-je ajouter des couleurs conditionnelles à un graphique?

■■ Comment puis-je utiliser des graphiques en cascade pour suivre les progrès vers un objectif de vente ou décomposer les composants du prix de vente?

■■ Comment puis-je utiliser la fonction GETPIVOTDATA et la fonction Tableau Excel pour créer des tableaux de bord dynamiques?

■■ Comment puis-je créer un diagramme de Pareto?

■■ Comment puis-je insérer une ligne verticale dans un graphique pour séparer les performances avant et après la fusion?

■■ Comment puis-je utiliser un graphique radar pour montrer comment les membres de l’équipe de basket-ball diffèrent en force, en vitesse et en capacité de saut?

■■ Je sais que je peux utiliser un nuage de points pour montrer comment deux variables changent. Comment puis-je utiliser un graphique à bulles pour résumer la variation de trois variables?

Un vieux proverbe chinois dit qu’une image vaut mille mots. Excel peut créer de nombreux graphiques incroyables, et ce chapitre vous montre de nombreux exemples de trucs et astuces graphiques. Sachez que la représentation graphique dans Excel 2013 a beaucoup changé par rapport aux versions précédentes.

Comment créer des graphiques combinés?

Le fichier Combinationstemp.xlsx contient les ventes réelles et cibles de janvier à juillet. Vous souhaitez créer un graphique indiquant les ventes réelles et cibles de chaque mois. Tout d’abord, sélectionnez la plage F5: H12 et, dans l’onglet Insertion, sélectionnez Graphiques à colonnes et choisissez le premier graphique à colonnes 2D (groupé), illustré à la figure 1. L’utilisation de deux colonnes rend difficile de voir le contraste entre la valeur réelle et les ventes cibles, utilisez donc un graphique combiné dans lequel une série est représentée sous forme de ligne et l’autre sous forme de colonne. Pour créer ce graphique, cliquez avec le bouton droit sur l’une des séries et sélectionnez Modifier le type de graphique. Après avoir choisi Combo (un nouveau choix dans Excel 2013), choisissez le premier choix comme indiqué dans la Figure 2. Cela donne le graphique combiné illustré à la figure 3.

FIGURE 1 Il s’agit d’un graphique à colonnes pour l’objectif réel et l’objectif de vente.

FIGURE 2 Sélectionnez un graphique combiné.

FIGURE 3 Il s’agit d’un graphique combiné.

 

Comment créer un axe secondaire?

Lors de la cartographie de deux quantités de magnitude différente, un axe secondaire est souvent nécessaire pour donner un sens au graphique. Pour illustrer l’idée, la figure 6  montre les revenus mensuels et les unités vendues. Si vous montrez ces données sur un seul axe Y, les revenus mensuels seraient à peine visibles. Pour remédier à ce problème, commencez par sélectionner la plage (D7: F16) que vous souhaitez cartographier. Dans l’onglet Insertion, sélectionnez l’icône de graphique combiné illustrée à la figure 4.

FIGURE 4 Il s’agit de l’icône du graphique combiné.

Dans la flèche déroulante, sélectionnez Créer un graphique combiné personnalisé et remplissez la boîte de dialogue comme illustré à la figure 5.

Le graphique résultant résume les revenus sur un axe secondaire (l’axe vertical de droite) avec un graphique linéaire. Le graphique résultant en 6 montre clairement que les revenus mensuels et les unités vendues changent de façon quasiment ininterrompue.

FIGURE 5 Créez un graphique combiné avec un axe secondaire.

FIGURE 6 Un axe secondaire est utilisé pour résumer les revenus.

Comment gérer les données manquantes?

Souvent, certaines lignes d’une feuille de calcul contiennent des données manquantes. Excel vous offre trois façons de représenter graphiquement les données manquantes:

■■ Affichez les données sous forme de zéros.

■■ Affichez les données sous forme de blancs (lacunes).

■■ Remplacez un point de données manquant par une ligne joignant des points de données adjacents.

Pour illustrer le fonctionnement, le fichier contient des températures horaires, mais plusieurs valeurs sont manquantes. Après avoir tracé les données sous forme de graphique linéaire, cliquez avec le bouton droit sur les données et choisissez Sélectionner les données. Cela ouvre la boîte de dialogue illustrée à la figure 7.

FIGURE 7 Cette figure montre la boîte de dialogue Paramètres de cellule masquée et vide.

Après avoir sélectionné Connecter des points de données avec une ligne, vous obtenez le graphe  indiqué dans la figure 8. Choisissez le graphique linéaire avec des points et des lignes pour pouvoir sélectionner les données manquantes car les points de données manquants n’ont pas de marqueurs.

FIGURE 8 Les données manquantes sont remplacées par des lignes.

 

Comment gérer l’apparence des données cachées?

Souvent, nous traçons des données telles que les ventes quotidiennes et filtrons les données dans la feuille de calcul. Dans cette situation, Excel vous donne le choix de continuer à afficher toutes les données dans le graphique ou simplement afficher les données filtrées. Le fichier contient les ventes quotidiennes d’un produit pendant un an. Si vous représentez graphiquement les données sous forme de graphique linéaire, vous voyez le graphique illustré à la figure 9. Si vous cliquez avec le bouton droit sur les données et sélectionnez Afficher les données dans les lignes et colonnes cachées, illustré à la figure 7, même si vous filtrez les données, tous les points de données seront toujours affichés dans le graphique. Par exemple, comme le montre la figure 10, les données sont filtrées pour afficher uniquement les ventes de décembre, mais le graphique affiche toujours toutes les ventes quotidiennes pour l’année entière. Si vous n’avez pas sélectionné Afficher les données dans les lignes et colonnes cachées, seules les ventes de décembre seront visibles dans le graphique.

FIGURE 9 Il s’agit d’un graphique montrant les ventes quotidiennes de produits.

FIGURE 10 Les données sont filtrées, mais pas le graphique.

Comment puis-je utiliser des images pour ajouter du bling à mes graphiques à colonnes?

Habituellement, l’ampleur des ventes de produits est résumée avec des colonnes ou des barres ennuyeuses dans lesquelles la hauteur de la colonne ou la largeur de la barre est proportionnelle aux ventes de produits. Ne serait-il pas plus amusant de résumer les ventes de produits avec une image de votre produit, proportionnellement aux ventes réelles? Pour illustrer l’idée, consultez le fichier et le graphique illustré à la figure 11.

Dans cet exemple, vous supposez que votre entreprise vend du soda, vous souhaitez donc résumer les ventes mensuelles avec une bouteille de soda dont la taille reflète l’ampleur des ventes mensuelles. Pour commencer, sélectionnez le C5: D8 et dans l’onglet Insérer, sélectionnez la première option de graphique à colonnes 2D (Colonne groupée). Cliquez ensuite avec le bouton droit sur une colonne et survolez Format de la série de données. Sélectionnez Remplir, puis Image, et vous voyez la fenêtre illustrée à la figure 12. Après avoir sélectionné Soda et appuyé sur Entrée, de nombreuses photos de Soda vous sont présentées. Après avoir sélectionné la bouteille de soda souhaitée et cliqué sur Insérer, l’image est inséré dans le graphique comme le montre la figure 11, la hauteur de la bouteille de soda étant proportionnelle aux ventes réelles.

FIGURE 11 Les ventes de soda sont résumées avec une bouteille de soda.

FIGURE 12 Il s’agit de la boîte de dialogue Insérer des images.

J’ai représenté les données de ventes annuelles dans un graphique à colonnes et les années n’apparaissent pas sous forme d’étiquettes de colonnes. Qu’ai-je fait de mal?

Le fichier  contient les ventes annuelles de produits pour les années 2007 à 2010. Lorsque vous créez un graphique à colonnes avec D5: E9 comme source de données du graphique, le graphique ne parvient pas à afficher l’année sur l’axe des X car Excel pense que vous voulez l’année représentée sous forme de série. Si vous omettez l’étiquette de catégorie Année dans le coin supérieur gauche de la plage source et utilisez la plage D20: E24 comme plage pour le graphique, votre le graphique montre l’année sur l’axe des x, comme souhaité.

FIGURE 13 L’omission d’une étiquette de catégorie aide le graphique à colonnes à représenter correctement le graphique.

Comment puis-je inclure des étiquettes de données et des tableaux de données dans mes graphiques?

Souvent, vous souhaitez insérer des étiquettes de données à côté de vos colonnes ou barres ou peut-être afficher un tableau ci-dessous votre graphique. Pour illustrer ce processus, consultez le fichier L. (Voir également la figure 1.) Dans ce fichier, vous êtes indiqué les ventes au cours du mois en cours de quatre catégories de produits. Pour commencer, résumez ces données dans un graphique à colonnes et placez une étiquette contenant le nom du produit et les ventes réelles ci-dessus la colonne. Après avoir créé un graphique à colonnes de la manière habituelle, sélectionnez la série de colonnes et cliquez sur le + signe à droite du graphique. Sélectionnez Étiquettes de données et autres options et choisissez Options d’étiquette.

Remplissez maintenant les options d’étiquette comme illustré à la figure 14.

FIGURE 14 Ces paramètres sont nécessaires pour afficher les ventes et le nom de la série sur une ligne distincte.

Vous voyez maintenant le graphique illustré à la figure 15, qui montre les noms et les valeurs des catégories pour chaque colonne sur des lignes distinctes.

FIGURE 15 Les noms des catégories et les ventes sont inclus dans le tableau.

Vous voyez maintenant comment placer un tableau de données résumant les ventes sous le graphique. Sélectionnez simplement l’axe de la catégorie et cliquez sur le signe + à droite du diagramme. Après avoir cliqué sur Tables de données, vous voyez la table de données illustrée à la figure 16.

FIGURE 16 Les ventes sont résumées avec un tableau de données.

Si vous souhaitez voir plus d’options de table de données, sélectionnez le triangle à droite de la table

de données. Comment puis-je utiliser Excel  pour placer des étiquettes de données sur un graphique en fonction du contenu des cellules? Excel  permet désormais de placer les étiquettes de données des cellules directement sur les graphiques. Pour illustrer idée, examinez le fichier illustré à la figure 17.

FIGURE 17 Il s’agit d’un diagramme de dispersion avec des étiquettes de cellules.

Pour commencer à créer ce graphique, sélectionnez la plage de cellules H5: I10 et choisissez le premier choix de nuage de points dans l’onglet Insertion. (Le nuage de points est l’icône avec des points à côté du graphique à secteurs.) Thi      s crée le nuage de points de la figure 17 sans les étiquettes de ville. Pour créer les étiquettes de ville, sélectionnez le graphique. Dans l’onglet Conception, sélectionnez Ajouter un élément de graphique, choisissez Étiquettes de données, puis Autres options d’étiquette de données. Sélectionnez la valeur de premier choix dans les cellules et désactivez la valeur Y. Vous pouvez maintenant sélectionner la plage de cellules G6: G10 pour insérer les étiquettes de ville dans le graphique, comme illustré à la figure 17.

Comment suivre les performances de la force de vente dans le temps?

Le fichier  répertorie les ventes mensuelles de votre force de vente exceptionnelle de janvier à mai.(voir figure 18).

FIGURE 18 Voici les données de ventes mensuelles.

Vous souhaitez utiliser des icônes (haut, bas ou flèche plate) pour suivre au cours de chaque mois si le classement d’un vendeur s’est amélioré, a diminué ou est resté le même. Vous pouvez utiliser des jeux d’icônes Excel (décrits dans l’article 23, «Formatage conditionnel»), mais vous devrez ensuite insérer un jeu d’icônes pour chaque mois, ce qui est une tâche fastidieuse. Une façon plus efficace (mais pas aussi esthétique) de créer mangé ces icônes consiste à entrer un h lorsque vous voulez une flèche vers le haut, entrez un i lorsque vous voulez une flèche vers le bas et entrez un g lorsque vous voulez une flèche plate. Ensuite, si vous changez la police en Wingdings 3, vous avez les flèches souhaitées car les lettres de l’alphabet correspondent dans Wingdings 3 aux symboles représentés sur la figure 19.

FIGURE 19 Il s’agit de la correspondance entre les lettres et les symboles Wingdings 3.

Pour créer les icônes illustrées à la figure 20, procédez comme suit:

■■ La copie de la formule RANG(E6, E $ 6: E0,0) de J6 à J6: N20 calcule le classement des ventes de chaque personne au cours de chaque mois.

■■ Copie de la formule SI (K6 <J6, ”h”, SI (K6> J6, ”i”, ”g”)) de O6 à O7: R20 crée un h si le rang de la personne s’est amélioré, un i si le rang de la personne a baissé, et ag si le rang du vendeur est resté le même.

■■ Après avoir changé la police de la gamme O6: R20 en Wingdings 3, vous voyez les icônes affichées dans Figure 20.

L’avantage de cette approche est que vous pouvez utiliser des instructions SI pour personnaliser les conditions qui définissent les icônes.

FIGURE 20 Ce sont les icônes qui suivent l’évolution des performances du vendeur.

Comment puis-je créer un graphique à bandes pour vérifier si l’inventaire est dans des niveaux acceptables?

Souvent, vous devez suivre une quantité (inventaire, encaisse, nombre d’accidents) et vous voulez savoir si la quantité reste entre les limites supérieures et inférieures historiques. Un graphique à bandes fournit

un outil utile pour surveiller l’évolution d’un processus au fil du temps. La figure 21 montre un exemple de graphique à bandes.

FIGURE 21 Un graphique à bandes résume les niveaux de stock.

Pour créer le graphique à bandes, procédez comme suit:

  1. 1. Vous avez entré votre limite inférieure sur l’inventaire (5) dans B2 et votre limite supérieure (25) sur l’inventaire dans B3.
  1. 2. À la ligne 5, vous entrez la limite inférieure pour chaque mois en copiant la formule = $ B $ 2 de B5 à C5: G5.
  1. 3. Copie de la formule = $ B $ 3- $ B $ 2 de B6 vers C6: G6 calcule la limite supérieure – limite inférieure.

Nommez cette ligne Limite supérieure, car cette ligne sera utilisée pour générer la ligne représentant votre limite d’inventaire supérieure de 25 unités.

  1. 4. Sélectionnez la plage B5: G7 et sélectionnez un graphique de zone empilée (la deuxième option de zone 2D).
  1. 5. Sélectionnez la série de valeurs, cliquez avec le bouton droit sur Type de changement de graphique et choisissez la première option de graphique en courbes.
  1. 6. Ajoutez un axe secondaire à la série Valeurs, puis supprimez-le.
  1. 7. Cliquez avec le bouton droit sur la série Lower Limit et sélectionnez Remplir. Dans le menu Remplissage, sélectionnez Aucun remplissage.

Votre graphique à bandes montre que vous avez beaucoup de difficulté à maintenir les niveaux d’inventaire entre les limites inférieures et supérieures souhaitées.

Comment puis-je stocker un graphique en tant que modèle?

Vous venez de créer un magnifique tableau des bandes. Vous pourriez penser que chaque fois que vous souhaitez créer un graphique à bandes, vous devez répéter les étapes décrites précédemment. Ce n’est pas le cas. Vous pouvez enregistrer la bande (ou tout autre graphique) en tant que modèle et afficher les paramètres du graphique à tout moment. Pour illustrer l’idée, ouvrez le fichier, cliquez avec le bouton droit sur le graphique, sélectionnez Enregistrer en tant que modèle et donnez au graphique le nom que vous souhaitez. (La bande a été choisie ici.) Supposons maintenant que vous vouliez un graphique à bandes uniquement pour les mois de janvier à mars. Sélectionnez la plage de données A4: D7 et, dans l’onglet Insertion, sélectionnez la flèche dans le coin inférieur droit du groupe Graphique. Sélectionnez Tous les graphiques et modèles. Accédez au modèle de graphique souhaité, cliquez sur OK et vous avez terminé!

Comment puis-je utiliser un diagramme de thermomètre pour représenter la progression par rapport à une cible?

Un graphique de thermomètre affiche les valeurs réelles d’une quantité, telles que les revenus empilés dans une colonne qui affiche une valeur cible. Le graphique résultant (illustré à la figure 22) ressemble à un thermomètre, d’où son nom.

FIGURE 22 Il s’agit d’un diagramme de thermomètre.

Pour créer un diagramme de thermomètre, procédez comme suit:

  1. 1. Sélectionnez la plage A1: H3 et un graphique 2D à colonnes groupées.
  1. 2. Sélectionnez la série Revenue, puis cliquez avec le bouton droit et sélectionnez Format des séries de données. Ajoutez un axe secondaire puis supprimez-le.
  1. 3. Sélectionnez la série Revenue et cliquez de nouveau avec le bouton droit de la souris et sélectionnez Format des séries de données. Définir le chevauchement à 0% et la largeur de l’espace à 48%.
  1. 4. Sélectionnez la série cible et cliquez de nouveau avec le bouton droit et sélectionnez Formater la série de données. Définissez Chevauchement sur 0% et largeur d’écart à 261%.
  1. 5. Vous voyez maintenant le graphique illustré à la figure 22. Vous devrez peut-être ajuster la largeur des espaces pour obtenir un graphique que vous aimez. La réduction de la largeur de l’espace cible, par exemple, élargit les barres rouges; l’agrandissement de l’écart de revenu rend les barres bleues plus étroites.

Comment créer des étiquettes de graphique dynamique?

Vous avez probablement rencontré des classeurs contenant des graphiques dans lesquels vous modifiez une entrée de feuille de calcul, et les étiquettes de graphique ne changent pas. Cela crée souvent de la confusion. Découvrez maintenant comment lier des étiquettes de série et des titres de graphique à des cellules de feuille de calcul. Pour illustrer l’idée (voir le classeur DynamicLabels.xlsx et la figure 23), supposons que vous souhaitiez représenter le PIB futur aux États-Unis et en Chine. Tu veux le titre du graphique contient l’année au cours de laquelle le PIB chinois dépasse le PIB américain et l’étiquette de la série contient le taux de croissance annuel de chaque nation. En C5 et C8, vous pouvez modifier les taux de croissance estimés par rapport à leurs valeurs actuelles de 3% pour le PIB américain et de 10% pour le PIB chinois.

FIGURE 23 Créez des étiquettes dynamiques.

L’idée clé est de lier le titre et les étiquettes de votre graphique à des cellules qui changent lorsque les taux de croissance changent. Procédez comme suit:

  1. 1. Copie de la formule SI (D7> = D4,1,0) de D10 à E10: R10 entre un 1 si le PIB chinois est au moins aussi grand que le PIB américain.
  1. 2. Dans la cellule C14, déterminez l’année au cours de laquelle la Chine dépasse les États-Unis avec la formule SIERREUR (EQUIV(1, D10: R10,0), «aucune»). Notez que si la Chine ne dépasse jamais les États-Unis, cette formule renvoie «aucun».
  1. 3. Dans la cellule C17, le = SI(C14 = “aucun”, “US reste au top”, “Chinese_GDP_to_pass_US_GDP_in _Year “& TEXT (C14,” 0 ”)) crée le titre de graphique souhaité. Notez que si la Chine ne dépasse jamais les États-Unis, le titre de votre graphique sera US Stays on Top. Sinon, le titre de votre graphique est lié à C14, donc le titre du graphique contiendra l’année au cours de laquelle la Chine passe aux États-Unis. Le «0» dans la fonction TEXTE garantit que l’année est formatée sous forme d’entier.
  1. 4. Dans la cellule C18, la formule “USA (” & TEXT (C5, “0.0%”) & “annual_growth)” crée le titre du graphique pour la série USA. La partie «0,0%» de la fonction texte garantit que le taux de croissance est formaté en pourcentage.
  1. 5. Dans la cellule C19, la formule = “Chine (” & TEXTE (C8, “0,0%”) & “croissance_annuelle)” crée le titre du graphique pour la série Chine.
  1. 6. Vous êtes maintenant prêt à créer le graphique avec des étiquettes dynamiques.
  2. 7. Appuyez sur Ctrl, sélectionnez la plage non continue C2: R2, C4: R4, C7: R7 et créez un diagramme de dispersion (troisième option).
  1. 8. Sélectionnez Ajouter un élément de graphique dans l’onglet Conception et sélectionnez Titre de la superposition centrée. Dans la barre de formule, tapez un signe égal (=), pointez sur la cellule C17 et appuyez sur Entrée. Vous avez maintenant une étiquette de graphique dynamique.
  1. 9. Sélectionnez Series 1 (USA GDP series) et cliquez avec le bouton droit et choisissez Select Data. Cliquez sur Modifier et remplir dans la boîte de dialogue Modifier la série comme suit:

FIGURE 24 Créez une étiquette dynamique pour la série USA.

Cela relie l’étiquette de la série USA à la cellule C18, qui contient le taux de croissance annuel. De la même manière, vous liez l’étiquette de la série Chine à la cellule C19. Vous avez maintenant terminé un graphique avec des étiquettes dynamiques.

Comment puis-je utiliser des cases à cocher pour contrôler les séries à représenter?

Vous vous souviendrez peut-être du dans l’aticle 26, «Boutons de rotation, barres de défilement, boutons d’option, cases à cocher, zones de liste déroulante et zones de liste de groupe», qu’une case à cocher peut être utilisée pour basculer le contenu d’une cellule entre VRAI et FAUX. Il s’avère que si Excel voit une erreur # N / A dans une cellule, la cellule ne générera pas de point apparaissant dans le graphique. Par conséquent, si vous ne souhaitez pas tracer une série, utilisez simplement une formule IF pour rendre la série tracée # N / A lorsque la case à cocher met un False dans une cellule.

Pour commencer, utilisez les méthodes décrites au chapitre 26 pour créer deux cases à cocher, une pour contrôler Série 2010 et une autre pour contrôler la série 2011. La case à cocher 2010 contrôle la cellule B1 et la case à cocher 2011 contrôle la cellule C1. Les données d’origine sont en E6: L7. Copie de la formule SI ($ B $ 1, F6, NA ()) de F10 à F11: L11 renvoie simplement les données d’origine pour l’année si la case à cocher de l’année est cochée ou renvoie # N / A si la case à cocher de l’année est désactivée. Vous sélectionnez maintenant la plage de cellules E9: L11 comme données source pour un graphique Ligne avec marqueurs. Désactiver la case à cocher d’une année masque les données d’une année dans le graphique et la cocher pour afficher les données de l’année dans le graphique.

FIGURE 25 Les cases à cocher peuvent contrôler la série apparaissant dans un graphique.

 

Comment puis-je utiliser une zone de liste pour choisir la série à représenter?

Supposons que votre feuille de calcul  contienne les ventes 2007-2011 dans l’Est, l’Ouest, le Midwest et le Sud. Vous voulez un moyen simple de contrôler quelle série est cartographiée. Une zone de liste (voir Chapitre 26) permet de choisir facilement la série à représenter. Pour commencer, sous l’onglet Développeur, dans le groupe Insérer, sélectionnez Zone de liste (cinquième icône dans la ligne supérieure) dans les contrôles de formulaire. Lorsque vous voyez le pointeur, cliquez avec le bouton droit et sélectionnez Contrôle du format. Sélectionnez $ A $ 14: $ A $ 17 comme plage d’entrée et liaison cellulaire $ H $ 2. Maintenant, avec une utilisation intelligente de la fonction INDEX, vous pouvez utiliser la cellule H2 pour contrôler la série tracée.

Copie de la formule INDEX ($ H $ 16: $ K $ 21, G7, $ H $ 2) de I7 vers I8: I10 récupère la série correcte. Par exemple, dans la zone de liste illustrée à la figure 52-26, la région Est a été sélectionnée. Cela place un 1 dans la cellule H2. Votre fonction INDEX récupère la première colonne (colonne H) de données comme vous le souhaitez. Sélectionnez maintenant la plage H7: Y11 et créez un diagramme de dispersion, et vous voyez que lorsque vous cliquez sur une région dans la zone de liste, la série sélectionnée est représentée sur un graphique.

FIGURE 26 Utilisez une zone de liste pour contrôler la série tracée.

Comment créer un diagramme de Gantt?

Souvent, un projet nécessite la réalisation d’un certain nombre de projets. Un diagramme de Gantt illustre l’heure de début de chaque projet. La figure 27  est un diagramme de Gantt pour un projet composé de cinq activités. Pour créer ce graphique, procédez comme suite:

  1. 1. Sélectionnez la plage de cellules F3: H8 et créez un graphique à barres 2D empilées (deuxième option).
  1. 2. Cliquez avec le bouton droit sur la série de départ et, après avoir sélectionné Remplir, choisissez Aucun remplissage pour masquer la série de début.
  1. 3. Cliquez avec le bouton droit sur l’axe vertical et, après avoir sélectionné Format Axis, sélectionnez Catégories Inverse

Commandez pour vous assurer que la tâche 1 est répertoriée en premier au lieu de la dernière.

FIGURE  27 Il s’agit d’un diagramme de Gantt.

Comment puis-je créer un graphique basé sur des données triées?

Supposons que vous ayez des ventes dans un certain nombre d’états (voir la figure 28 et le fichier Sortedgraphx.xlsx) et que vous souhaitiez que le graphique répertorie les états par ordre décroissant de ventes, comme le montre la figure 29. Pour créer ce graphique, vous devez réorganiser les données dans les colonnes J à L afin que les données de vente soient triées par ordre décroissant. Pour atteindre cet objectif, procédez comme suite:

  1. 1. Copie de la formule RANG(F9, $ F $ 9: $ F $ 23,0) de G9 à G10: G22 calcule le classement des ventes de chaque État. Par exemple, NY se classe sixième. Notez cependant que KY, Ala et Ari ont le même niveau de ventes. Pour trier les états par ordre décroissant de ventes, vous devez associer un rang unique à chaque état.
  1. 2. La copie de la formule G9 + NB.SI ($ G $ 8: G8, G9) de H9 à H10: H22 crée un rang unique pour chaque état en augmentant le rang d’un état avec un rang lié à chaque fois qu’un rang lié se produit.
  1. 3. Copie de la formule INDEX (E $ 9: E $ 23, MATCH ($ J9, $ H $ 9: $ H $ 23,0), 1) de K9 à K9: L23 trie les États et les ventes en fonction des ventes décroissantes.
  1. 4. Maintenant, la création d’un graphique à colonnes basé sur K9: L23 donne le graphique illustré à la figure 29.

FIGURE 28 Il s’agit des données de vente pour un graphique trié.

FIGURE 29 Les ventes sont triées par ordre décroissant.

 

Comment puis-je créer un histogramme qui se met à jour automatiquement lorsque j’inclus de nouvelles données?

Dans l’article 42, «Récapitulation des données à l’aide de statistiques descriptives», vous avez appris à utiliser Analyse  pour créer un histogramme. Malheureusement, les histogrammes créés avec Analyse ne seront pas mis à jour si les données existantes sont modifiées ou si de nouvelles données sont ajoutées. En utilisant la fonction Tableau Excel Bien sûr, vous pouvez facilement créer des histogrammes qui s’adaptent automatiquement aux changements des données source. Le fichier Dynamichistograms.xlsx illustre l’idée (voir la figure 30). La colonne E contient les salaires des joueurs de la NBA en millions de dollars pour la saison 2003-2004. Pour commencer, sélectionnez la plage E5: E446 et faites de cette plage un tableau. Entrez les plages de bacs pour votre histogramme dans la plage H7: H28. Notez que la ligne 28 calculera le nombre de salaires supérieurs à 20 millions de dollars. Utilisez la fonction FRÉQUENCE pour compter le nombre de salaires dans chaque plage de casiers. La fonction FREQUENCE (voir Article 87, «Formules et fonctions de tableau») est une fonction de tableau. Pour utiliser cette fonction, vous devez d’abord sélectionner la plage que la fonction remplit (dans ce cas, I7: I28), entrer la syntaxe de la fonction, puis appuyer sur Ctrl + Maj + Entrée. Après avoir sélectionné I7: I28, saisissez la formule = FRÉQUENCE (E6: E446, H7: H27) et appuyez sur Ctrl + Maj + Entrée. Excel saisit désormais le nombre de joueurs (0) avec un salaire inférieur ou égal à 0 en I7, en I8 le nombre de salaires> 0 et <= 1 million (124),. . . , en I27 le nombre de joueurs avec un salaire> 19 et <= 20 millions (2), et en I28 le nombre de joueurs avec un salaire> 20 million. Pour créer l’histogramme dynamique, sélectionnez la plage de cellules H7: I28 et créez un graphique à colonnes. Vous avez maintenant un histogramme dynamique qui se met automatiquement à jour pour tenir compte des changements dans les données source. Pour prouver que l’histogramme se met à jour, ajoutez quelques salaires de 30 millions de dollars. Vous voyez maintenant un nouveau pic sur le côté droit du graphique.

FIGURE 30 Il s’agit d’un histogramme dynamique.

 

Comment puis-je ajouter des couleurs conditionnelles à un graphique?

Supposons que vous représentiez les ventes réelles et ciblées pour chaque mois et que vous souhaitiez des mois qui atteignent 90% de la cible ou mieux en bleu, 75% de la cible ou pire en vert et d’autres mois en rouge. Le fichier (Figure 31) montre comment cela se fait. L’astuce pour créer ce graphique consiste à placer les données de chaque couleur sur une ligne différente. Pour commencer, placez les ventes que vous voulez colorées en bleu dans la ligne 19 en copiant le = SI(F13 / F14> F15, F 13 / F14, formule «») de F19 à G19: M19. Ensuite, pour placer les ventes que vous souhaitez colorer en rouge dans la ligne 20, copiez la formule = IF (AND (G13 / G14> G16, G13 / G14 <= ​​G15), G13 / G14, “”) de F20 à G20: M20. Enfin, placez les ventes pour les mois qui doivent apparaître en vert dans la ligne 20 en copiant la formule = SI(ET(G13 / G14> G16, G13 / G14 <= ​​G15), G13 / G14, “”) de F21 à G21: M21. Sélectionnez maintenant la plage E18: M21 et créez un graphique à colonnes. Si vous souhaitez modifier la couleur d’une série, sélectionnez la série et cliquez sur le pinceau à droite du graphique; sélectionnez Couleur et choisissez la couleur de votre choix.

FIGURE 31 Le bleu indique un bon mois, le vert un mauvais mois et le rouge un mois.

Comment puis-je utiliser des graphiques en cascade pour suivre les progrès vers un objectif de vente ou décomposer les composants du prix de vente?

Les graphiques en cascade, initialement développés par la société de conseil McKinsey, sont souvent utilisés pour montrer les progrès vers une position de trésorerie finale ou une ventilation des revenus totaux d’une entreprise en composantes de coûts et en bénéfices. Le fichier Waterfallcharts.xlsx contient plusieurs exemples de graphiques en cascade. Dans la feuille de calcul Tous positifs (voir figure 32), vous créez un graphique en cascade pour une situation dans laquelle tous les flux de trésorerie sont positifs. Vous voulez que le graphique montre la progression mensuelle vers votre objectif de trésorerie de 3 270 $.

Après avoir saisi vos flux de trésorerie dans la colonne C, vous devez suivre votre flux de trésorerie cumulé dans la colonne B. Copier la formule B2 + C2 de B3 à B4: B7 calcule le flux de trésorerie cumulé jusqu’à la fin de chaque mois. Ensuite, sélectionnez la plage de cellules A1: C7 et créez un graphique à colonnes empilées. En sélectionnant la série de base et en choisissant No Fill, vous obtenez le graphique en cascade illustré à la figure 32.

FIGURE 32 Dans ce graphique en cascade, tous les flux de trésorerie sont positifs.

La figure 33 (voir la feuille de calcul Positive et Negative) montre un graphique en cascade lorsque certains flux de trésorerie sont négatifs.

Après avoir entré les flux de trésorerie positifs dans la colonne C et les flux de trésorerie négatifs dans la colonne D, calculez le flux de trésorerie total jusqu’à la fin de chaque mois en copiant la formule E1 + C2-D2 de E2 à E3: E7. Ensuite, dans la colonne B, en copiant la formule E1-D2 de B2 à B3: B7, calculez le flux de trésorerie cumulé du mois dernier, ajusté par le flux de trésorerie négatif du mois en cours. Après avoir sélectionné la plage A1: D7 et créé un graphique à colonnes, tout ce que vous avez à faire est de masquer la colonne B, et vous avez le graphique en cascade illustré à la figure 33.

FIGURE 33 Il s’agit d’un graphique en cascade lorsque certains flux de trésorerie sont positifs et certains sont négatifs.

En suivant la méthode utilisée dans la feuille de calcul Positif et Négatif, vous pouvez facilement décomposer les revenus d’une entreprise dans ses composantes de coûts (y compris les bénéfices). Voir la feuille de calcul Cascade de rentabilité et figure 34

FIGURE 34 Il s’agit d’un graphique en cascade de rentabilité.

Comment puis-je utiliser la fonction LIREDONNEESTABCROISDYNAMIQUE  et la fonction Tableau Excel pour créer des tableaux de bord dynamiques?

Souvent, vous pouvez télécharger des données de ventes mensuelles, trimestrielles ou hebdomadaires et souhaiter résumer les données de ventes dans des graphiques qui se mettent automatiquement à jour pour inclure de nouvelles données. La connaissance de la fonction Excel LIREDONNEESTABCROISDYNAMIQUE (voir l’article 43, «Utilisation des tableaux croisés dynamiques et des segments pour décrire les données») et de la fonction de tableau Excel (voir le chapitre 25, «Tables») rend cela relativement simple (voir le fichier). Supposons que vous téléchargiez des données de vente hebdomadaires pour quatre catégories de produits, Filtrate, Tape, Command et Abrasives, dans quatre magasins: Menards, Target, Lowe’s et Home Depot. Votre objectif est de configurer un tableau de bord qui vous permet de représenter rapidement les ventes hebdomadaires par rapport aux données d’un magasin donné et de vous donner le contrôle des catégories de produits qui apparaissent dans le graphique. Comme le montre la figure 35, vos données sont téléchargées dans les colonnes D – G. Faites de la gamme D4: G243 une table. Cela garantit qu’un tableau croisé dynamique basé sur ces données est automatiquement mis à jour pour inclure de nouvelles données lorsque vous actualisez la table.

FIGURE 35 Voici les données de vente des tableaux de bord dynamiques.

Vous créez maintenant un tableau croisé dynamique en faisant glisser Semaine vers la zone Lignes, Magasin et Catégorie vers la zone Colonnes et Revenus vers la zone Valeurs. Ce tableau croisé dynamique (illustré à la figure 36) résume les ventes hebdomadaires pour chaque catégorie dans chaque magasin.

FIGURE 36 Le tableau croisé dynamique résume les ventes par catégorie par magasin.

Vous êtes maintenant prêt à utiliser la fonction LIREDONNEESTABCROISDYNAMIQUE pour extraire les données nécessaires à la création des graphiques souhaités. Pour commencer, créez une liste déroulante (voir L’article 40, «Validation des données») dans la cellule AG8 qui peut être utilisée pour sélectionner un magasin. Créez des cases à cocher (voir le chapitre 26) pour chaque catégorie pouvant être utilisée pour contrôler la plage AH9: AK9. Ces cellules contrôlent les catégories qui apparaissent dans votre graphique. Copiez l’SI (AH $ 9 = FAUX, NA (), SIERREUR (TABLEAUCROISEDYNAMIQUE (“Revenue”, $ I $ 11, “WEEK”, $ AG11, “Categorie”, AH $ 10, Formule ”Magazin”, $ AG $ 8), ”“)) de AG11 à la gamme AG11: AK23. Si vous cochez la case d’une catégorie, cette formule extrait les ventes hebdomadaires de la catégorie; si la catégorie est effacée, # N / A est entré dans la cellule. De plus, si aucune vente n’a eu lieu, la partie SIERREUR  des formules garantit que les ventes de 0 sont entrés. Faites de la gamme AG10: AK24 un tableau pour que tous les graphiques soient basé  sur cette gamme se met à jour automatiquement pour inclure de nouvelles données. La figure 37 montre comment les données source de votre graphique apparaissent si vous souhaitez résumer les ventes d’abrasifs, de sécurité et de bandes chez Lowe’s.

FIGURE 37 Il s’agit des données source de votre graphique.

Maintenant, pour le gain! Sélectionnez la gamme AG10: AK24 et créez un nuage de points avec des lignes douces. Le graphique terminé est illustré à la figure 38. Ce graphique peut montrer les ventes dans n’importe quel magasin de n’importe quelle combinaison de catégories et se mettre à jour lorsque de nouvelles données sont téléchargées.

FIGURE 38 Un tableau de bord dynamique résume les ventes de Lowe’s of Abrasives, Tape, et  sécurité.

 

Comment puis-je créer un diagramme de Pareto?

Les entreprises résument souvent les problèmes signalés par les clients avec un diagramme de Pareto. Un graphique de Pareto montre une liste de problèmes sur l’axe des x et, sur l’axe des y, la fraction des problèmes associés à chaque problème et le pourcentage cumulé des problèmes causés par les problèmes répertoriés. Le fichier Pareto.xlsx (voir Figure 39) illustre l’idée. Pour commencer, triez les problèmes afin qu’ils soient répertoriés par ordre décroissant en fonction du nombre de fois que le problème a été signalé. Copie de l’E5 / Formule SOMME ($ E $ 5: $ E $ 10) de F5 à F6: F10 calcule le pourcentage de tous les problèmes pour chaque type de problème. Par exemple, la configuration a causé 31% de tous les problèmes. La copie de la formule SOMME ($ F $ 5: F5) de G5 vers G6: G10 calcule le pourcentage cumulé de problèmes. Par exemple, les trois premiers problèmes répertoriés ont causé 80% de tous les problèmes signalés. Après avoir sélectionné la plage D4: D10, maintenez la touche Ctrl enfoncée et sélectionnez la plage F4: G10. Après avoir sélectionné les paramètres du graphique combiné illustrés à la figure 40, vous disposez du graphique Pareto souhaité.

Souvent, les graphiques de Pareto peuvent être utilisés pour afficher la règle 80/20. Certaines manifestations de la règle des 80/20 sont les suivantes:

■■ 20% des plaintes de clients différents provoquent 80% des problèmes signalés.

■■ 20% des personnes ont 80% du revenu total.

■■ 20% de vos produits génèrent 80% de vos bénéfices.

■■ 20% de vos commerciaux génèrent 80% de vos ventes.

FIGURE 39 Utilisez un tableau de Pareto pour résumer les plaintes des clients.

FIGURE 40 Voici les paramètres de diagramme pour un diagramme de Pareto.

Comment puis-je insérer une ligne verticale dans un graphique pour séparer les performances avant et après la fusion?

Supposons (voir le fichier Verticalline.xlsx et la figure 41) que votre entreprise a été fusionnée avec une autre entreprise le 10 janvier 2011 et que vous calculez les ventes quotidiennes. Vous souhaiterez peut-être insérer une ligne verticale dans votre graphique pour indiquer la date de la fusion. Si vous tracez la ligne verticale avec la fonction Formes Excel et que le graphique est déplacé, la ligne sera au mauvais endroit. Pour remédier à ce problème, commencez par sélectionner la plage E10: F32; créer un nuage de points avec des lignes (troisième option). Dans la plage B15: C16, entrez la date de la fusion et les limites inférieure et supérieure sur les coordonnées y de votre ligne verticale. (Dans ce cas, la limite inférieure = 0 et la limite supérieure = 120.) Copiez la plage B15: C16 et cliquez avec le bouton droit sur votre graphique. Cliquez sur l’icône Coller pour insérer la ligne verticale à la date du 10 janvier.

FIGURE 41 La ligne verticale indique que le 10 janvier 2011 était la date de la fusion.

Comment puis-je utiliser un graphique radar pour montrer comment les membres de l’équipe de basket-ball diffèrent en force, en vitesse et en capacité de saut?

Le fichier (voir figure 42) résume quatre athlètes en termes de force, de vitesse et d’agilité. Sélectionnez la plage C2: F6 et, dans l’onglet Insertion, sélectionnez l’icône Diagramme radar illustré à la figure 43. Dans le menu déroulant, sélectionnez Diagramme radar avec marqueurs, et vous obtiendrez le diagramme radar illustré à la figure 42. Le centre du graphique radar indique un score de 0, et plus un marqueur est éloigné du centre, meilleur est le score. Le graphique permet de voir facilement, par exemple, que Max obtient de mauvais résultats sur toutes les mesures, et Christian obtient de bons résultats sur les trois mesures.

FIGURE 42 Il s’agit d’un diagramme radar avec des marqueurs.

FIGURE 43 Il s’agit d’une icône de carte radar.

Je sais que je peux utiliser un nuage de points pour montrer comment deux variables changent. Comment puis-je utiliser un graphique à bulles pour résumer la variation de trois variables?

Alors qu’un nuage de points vous montre comment deux variables varient, un graphique à bulles vous permet de résumer visuellement trois variables. Le fichier Bubble.xlsx (voir figure 44) contient, pour plusieurs pays, la variation en pourcentage des ventes par rapport au budget, la croissance annuelle des ventes et la taille du marché de chaque pays. Pour résumer ces données dans un graphique à bulles, sélectionnez la plage D9: F14, cliquez sur la flèche déroulante par Scatter Plot dans l’onglet Insertion et choisissez la première option de graphique à bulles. Après avoir ajouté (comme décrit précédemment dans le chapitre) une étiquette de données pour chaque bulle en fonction du pays (plage de cellules C10: C14) et placé les étiquettes au-dessus de chaque bulle, vous obtenez le graphique à bulles illustré à la figure 44. Les zones des bulles sont proportionnelles à la taille du marché de chaque pays. Par exemple, la bulle américaine contient 50% de surface en plus que la bulle chinoise.

FIGURE 44 Cette  est un graphique à bulles de zone.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x