» Trucs et astuces conversion vidéo, diaporama photo, excel 2007

Si vous avez suivi mes tutos excel depuis le début, vous avez appris à changer la couleur des nombres en fonction de leur valeur et également à réaliser de jolis graphiques. Entre ces deux tutoriels, vous aurez sûrement remarqué que j’étais passé d’excel 2003 à excel 2007… Pour l’instant tout ce que j’explique sur le 2007 fonctionne aussi sur le 2003 (les fonctionnalités sont les mêmes mais situées à d’autres endroits).

Aujourd’hui je vous propose un tutoriel qui fera la synthèse de mes deux précédents tutoriels:

• Attribuer une couleur à un nombre en fonction de sa valeur, avec Excel
• Excel 2007, réaliser un graphique à bulles en toute simplicité

En effet, ce nouveau tuto va vous permettre de réaliser de beaux graphiques qui réagiront en fonction des valeurs de vos séries et des noms de vos séries !

Si j’ai une série qui s’appelle “blog“, je pourrai faire en sorte que la courbe liée à cette série soit toujours bleu sans avoir à le paramétrer à chaque fois à la mano.

» Avec ce tutoriel, vous allez pouvoir attribuer des couleurs à des séries en fonction de leur valeur

Si j’ai une série qui oscille entre 0 et 70, je pourrai faire en sorte que toutes les valeurs comprises entre 0 et 10 soient vertes et toutes celles comprises entre 60 et 70 soient de couleur rouge

» A quoi cela sert d’avoir des rendus graphiques automatisés ?

1. A chaque fois que vous mettrez à jour vos données (et quelques soient vos options de tri !) vous verrez en un coup d’oeil où se situent vos séries et à quels moments elles sur performent ou sous performent
2. Vous allez gagner du temps, plus besoin de retoucher au graphique pour changer la couleur d’une série
3. Vous allez créer des graphiques qui parlent (à vous de choisir les bonnes couleurs)

» Organisation du tutoriel

Pour que tout le tutoriel ne soit pas trop difficile à digérer, j’ai décidé de le décomposer en 5 parties (la 5ème partie faisant la synthèse des parties 3 et 4):

1. Avant de se lancer… Prérequis et paramètrage de VBA
2. Identifier visuellement les creux et les pics sur une série de données
3. Attribuer une couleur à un graphique en fonction de sa valeur
4. Attribuer “automatiquement” une couleur à une courbe en fonction de son nom
5. Attribuer “automatiquement” une couleur à un graphique en fonction de sa valeur

1/ Avant de se lancer dans le vif du sujet…

» Bien comprendre les fonctionnalités les plus communes d’Excel

Ce tutoriel implique que vous ayez une connaissance assez avancée des différentes fonctionnalités que vous propose Excel…. Avant de commencer, soyez sûr de maîtriser ces différentes notions:

• création d’un graphique à partir d’une ou plusieurs séries de données
• utilisation d’opérandes (ex: + - = * /) et de fonctions dans les cellules (ex: moyenne(), somme(), si(), max(), min())
• utilisation de la poignée de recopie incrémentée
• compréhension des adressages relatifs et absolus (utilisation du signe $ quand on fait référence à une cellule pour figer une colonne ou une ligne)

Un bon site qui aborde la chose très simplement: Excel-Pratique

Autrement on verra aussi un peu de VBA… là je vous expliquerai comment ça fonctionne, donc pas de souci si c’est la première fois que vous vous y mettez !

» Activer l’onglet Développeur et accepter les macros sous Excel 2007

Dans les parties 3 et 5, nous aurons besoin d’utiliser VBA… Il faut donc que vous activiez l’onglet Développeur et autoriser l’utilisation des macros.

Ouvrez Excel, et cliquez sur le bouton Office (en haut à gauche), puis descendez tout en bas dans le menu dérouant et cliquez sur “Options Excel”

Dans la fenêtre qui s’affiche, cochez la case “Afficher l’onglet Développeur dans le ruban”

Allez ensuite dans l’onglet “Développeur” et cliquez sur “Sécurité des Macros”… dans la fenêtre qui s’affiche, cochez la case “activer toutes les macros”

On peut maintenant commencer le tutoriel !

2/ Identifier visuellement les creux et les pics sur une série de données

Pour réaliser le graphique ci-dessus, munissez vous d’une série de données, ici il s’agit du nombre de visites par tranche horaire du site jeanviet.info pour la journée du 31/11/2006:

tableau 1

Pour reproduire le graphique ci-dessus, il va falloir décomposer la série visites en 3 series:

• Visites (cette série reste telle quelle)
• Mini, on garde dans cette série la ou les plus petites valeurs de la série visites
• Max, on garde dans cette séries la ou les plus grandes valeurs de la série visites

Dans les celulles C13 et D13 nous allons ajouter une formule qui va nous permettre d’afficher pour chaque ligne soit la valeur vide (#N/A) soit la valeur (Mini ou Max si elles existent).

Ma série Visites allant de la cellule B13 à la cellule B36, voici ce que je vais rentrer:

dans C13 =SI(B13=MIN($B$13:$B$36);B13;#N/A)
dans D13 =SI(B13=MAX($B$13:$B$36);B13;#N/A)

Ensuite sélectionnez C13:D13 avec la poignée de recopie incrémentée et déroulez jusqu’à C36:D36 (comme ci-dessous)

On a maintenant toutes les séries dont nous avons besoin (on voit qu’à 20h la valeur 68 est la valeur la plus haute de notre série Visites et que c’est donc l’unique champ numérique qui apparaît dans notre série MAX)… Il faut sélectionner notre tableau entier de A12 à D36, et créez ensuite un Graphique à Courbe avec marqueurs (Onglet Insertion -> Courbe avec marques)

Sélectionnez votre graphique, nous allons tout de suite changer son style. (style 2, dans Outils de Graphique, Onglet Créer)

Voici ce que ça nous donne:

On est presque bon: les couleurs son là (rouge pour la valeur mini, vert pour la valeur max), reste à retravailler, les formes (retirer les marqueurs pour la série visites et mettre du noir à la place du bleu, retirer le trait entre les deux marqueurs pour la série Mini et mettre des triangles à la place du carré)… Pour effectuer ces modificiations, dans la légende où apparaissent nos 3 séries, faîtes clic droit sur le symbole de la série que vous souhaitez modifier et dans le menu déroulant choississez “Mise en forme des séries de données”…Après vous pouvez paramétrer toutes vos options de forme comme bon vous semble:

Un petit bémol tout de même, la partie Remplissage des marqueurs (où on choisit la couleur) chez moi est buggée (Excel 2007 Bétâ 2)… Comme j’ai choisi au départ les bonnes couleurs (choisies dans style 2) ça ne me pose pas trop de problèmes heureusement.

Après nos modifs de forme, on a notre graphique finale + la matrice de séries qui va avec…. Un petit test pour nous assurer que ça fonctionne bien quelques soient les valeurs que l’on rentre dans la matrice:

Ici j’ai bien mon triangle rouge pour mon creux de 4h et mon triangle vert pour mon pic de 17h ! Donc quelques soient les creux et les pics le graphique s’adapte bien à mes données !

» Si vous voulez allez plus vite… télécharger le fichier excel suivant:

graphique avec creux et pics

» Même avec ça vous n’y arrivez pas ? refaîtes un tour ici !

3/ Attribuer une couleur à un graphique en fonction de sa valeur

Si précédemment, on a pu mettre en relief les valeurs hautes et les valeurs basses de notre série visites, pourquoi ne pas le faire aussi sur des échelles de valeur plus larges ?

Ainsi si on reste dans le même esprit que la précédente partie, on pourrait décomposer (à l’aide d’une fonction de calcul) notre série visites en plusieurs sous-séries, par exemple on pourrait avoir:

1. sous-série 1 comprise entre 0 et 10, cette série sera blanche
2. sous-série 2 comprise entre 10 et 20, cette série sera jaune
3. sous-série 3 comprise entre 20 et 40, cette série sera orange clair
4. sous-série 4 comprise entre 40 et 60, cette série sera orange foncé
5. sous-série 5 comprise entre 60 et 70, cette série sera rouge

Il faudrait construire sur excel le tableau de correspondance suivant:

tableau 2

Les lignes 1 et 2 nous serviront à indiquer les délimitations de notre échelle, on ira puiser nos valeurs dans la colonne c…. on confrontera ensuite chaque valeur de c avec les intervalles de valeurs des colonnes d, e, f, g, h. Par exemple, c4 (16) ira se ranger dans e4 (intervalle compris entre 10 et 20)

J’espère que vous me suivez encore, si vous êtes largué, contentez vous de faire les choses sans chercher à les comprendre au début.

Donc maintenant, nous allons complété les cellules vides…. Pour se faire, vous n’aurez que la cellule d4 à déclarer…. vous la recopierez ensuite incrémentalement sur la droite et en bas, de sorte que toutes les cellules de correspondance soient renseignées.

Voici le code à rentrer pour d4:

=SI(ET($c4 >= d$1;$c4 < d$2);$c4;#N/A)

Décryptage: si c4(16) est compris entre 0 et 10 (supérieure ou égale à 0 et strictement inférieure à 10), afficher la valeur de c4, sinon afficher la valeur vide. Ici on aura donc la valeur vide.

En recopiant incrémentalement sur la droite cette formule, on verra 16 apparaître en e4. Quand vous aurez tout recopié, si on se base sur le tableau 2 sélectionnez les cellules b3:b12 (de “Période” à “8h”, cellules en vert) et en maintenant la touche Control (Ctrl) enfoncée sélectionnez d3:h12 (on n’a plus besoin de la colonne c, uniquement les cellules en bleu dans tableau 2)

Quand nos 5 sous-séries (valeurs qui seront en abscisse sur notre graphique) et notre échelle de temps (en ordonnée) sont sélectionnées, nous pouvons choisir un graphique (Onglet Insertion -> Barres)

Voici ce que ça donne:

On a bien notre nuance de couleur par intervalle de valeur…. Il ne vous reste plus qu’à retravailler les couleurs et les propriétés des barres si besoin….. Pour avoir quelque chose qui parle plus, comme ci-dessous:

» Si vous voulez allez plus vite… télécharger le fichier excel suivant:

graphique qui change de couleur en fonction de la valeur de la série

» Même avec ça vous n’y arrivez pas ? refaîtes un tour ici !

4/ Attribution “automatique” d’1 couleur à une courbe avec son nom

Allez ! on attaque maintenant la partie la plus diffcile de notre tutoriel, il va falloir utiliser VBA (Visual Basic for Applications). VBA est un langage de programmation disponible sous excel qui permet d’automatiser des tâches et donc de personnaliser un peu plus son utilisation d’Excel. Le principe: on crée une fonction (Macro VBA) dans l’éditeur Visual Basic et on fait appel à cette fonction lorsqu’on se retrouve sur notre de feuille de calcul Excel. Ainsi je vais vous apprendre ici, a créer et à manipuler une fonction VBA qui nous permettra d’attibuer des couleurs à des series en fonction de leur noms !

Graphique de base sans couleurs

En utilisant ces deux boutons (éditeur VBA + lecture de la Macro créée)

On arrive à un graphique qui donne les bonnes couleurs aux bons intitulés

On va partir de ces 4 séries

tableau 3

Sélectionnez ce tableau en entier, et créez ensuite un Graphique à Courbe avec marqueurs (Onglet Insertion -> Courbe avec marques). Cette manipulation vous donnera le graphique que je vous ai montré plus haut (graphique de base sans couleurs).

Allez maintenant dans l’onglet Développeur:

Cliquez ensuite sur l’icône Visual Basic:

2 fenêtres vont s’afficher, le code est à rentré dans la fenêtre de droite:

» Qu’est-ce qu’on va bien pouvoir rentrer dans cette fenêtre de droite ?

On va créer une fonction [CouleurSeries()] qui agira sur notre graphique actif (celui qui est sélectionné, ActiveChart). Dans cette fonction, on va indiquer que pour chaque nom de série [For Each MesSeries In .SeriesCollection], on aura, en fonction des noms de séries, des couleurs de ligne et de marqueur différentes. Si c’est la série “blog” [Case “blog”], je veux :

• une couleur de trait bleu clair [MesSeries.Border.ColorIndex = 33]
• une largeur de trait épaisse [MesSeries.Border.Weight = xlThick]
• un type de marqueur de forme carrée [MesSeries.MarkerStyle = xlMarkerStyleSquare]
• que la couleur de fond de mon marqueur soit bleu clair [MesSeries.MarkerBackgroundColorIndex = 33]
• la même couleur pour le contour du marqueur [MesSeries.MarkerForegroundColorIndex = 33]
• et un marqueur de taille 10 [MesSeries.MarkerSize = 10]

» Voici donc le code complet que nous allons rentrer dans notre fenêtre de droite:

Sub CouleurSeries()
Dim MesSeries As Series
With ActiveChart

For Each MesSeries In .SeriesCollection
Select Case MesSeries.Name

Case "astuces"
MesSeries.Border.ColorIndex = 9
MesSeries.Border.Weight = xlThick
MesSeries.MarkerStyle = xlMarkerStyleSquare
MesSeries.MarkerBackgroundColorIndex = 9
MesSeries.MarkerForegroundColorIndex = 9
MesSeries.MarkerSize = 10

Case "blog"
MesSeries.Border.ColorIndex = 33
MesSeries.Border.Weight = xlThick
MesSeries.MarkerStyle = xlMarkerStyleSquare
MesSeries.MarkerBackgroundColorIndex = 33
MesSeries.MarkerForegroundColorIndex = 33
MesSeries.MarkerSize = 10

Case "autres"
MesSeries.Border.ColorIndex = 16
MesSeries.Border.Weight = xlThick
MesSeries.MarkerStyle = xlMarkerStyleSquare
MesSeries.MarkerBackgroundColorIndex = 16
MesSeries.MarkerForegroundColorIndex = 16
MesSeries.MarkerSize = 10

Case "global"
MesSeries.Border.ColorIndex = 46
MesSeries.Border.Weight = xlThick
MesSeries.MarkerStyle = xlMarkerStyleSquare
MesSeries.MarkerBackgroundColorIndex = 46
MesSeries.MarkerForegroundColorIndex = 46
MesSeries.MarkerSize = 10
End Select
Next

End With
End Sub

» Adaptons ce code à vos séries et à vos envies

Le nom des séries et les couleurs qui vont avec ne sont pas figés fort heureusement:

Vous avez une palette de 64 couleurs disponibles sous Excel, à chaque couleur correspond un numéro. Pour le rouge c’est 3, l’orange c’est 46, etc… Toutes les couleurs excel se trouvent ici. Donc si dans Case “blog” à la place de 33, j’avais mis 3. Mon graphique aurait été rouge.

J’aurais également pu mettre à la place de Case “blog” => Case “Chiffre d’Affaires”…

Maintenant que la fonction est créée… retour à notre bonne vieille feuille Excel (Alt+F11 ou clic sur la petite icone excel en haut à gauche):

Sélectionnez votre graphique de base sans couleurs (il doit être activé). Puis cliquez sur le bouton “joue la macro”:

Sélectionnez votre Macro et appuyez sur “Exécuter”:

Et la bonne surprise à la fin, les couleurs s’adaptent aux noms de nos séries:

» Si vous voulez allez plus vite… télécharger le fichier excel suivant:

une couleur selon le nom de la série

» Même avec ça vous n’y arrivez pas ? refaîtes un tour ici !

5/ Attribuer “automatiquement” une couleur à un graphique avec sa valeur

On peut maintenant faire un mix des parties 3 et 5: les couleurs seront basées sur l’importance de la valeur de la série (5 sous-séries) et elles seront définies sous VBA.

Il faudrait partir de ce qui a déjà été fait en 3 au niveau du tableau de sous-séries:

Pour varier les plaisirs, on va en extraire un autre graphique (Onglet Insertion -> Colonnes)

Puis on va affecter à chaque échelle de valeur d’autres couleurs (au lieu des couleurs chaudes de tout à l’heure on va prendre des couleurs plus froides)… On va rester dans les bleus, mais contrairement à ce qu’on voit ci-dessus: plus la valeur sera faible plus la colonne sera claire, plus la valeur sera élevée plus la colonne sera sombre…

Retour à VBA… Cette fois, on n’a plus le même genre de graphique à gérér, attention ! L’info qu’on va faire varier sera la couleur intérieure de colonne [MesSeries.Interior.ColorIndex]

» Voici le code complet qu’il faudra rentrer dans l’éditeur VBA:

Sub CouleurValeurSeries()
Dim MesSeries As Series
With ActiveChart
For Each MesSeries In .SeriesCollection
Select Case MesSeries.Name

Case "entre 0 et 10"
MesSeries.Interior.ColorIndex = 35

Case "entre 10 et 20"
MesSeries.Interior.ColorIndex = 42

Case "entre 20 et 40"
MesSeries.Interior.ColorIndex = 33

Case "entre 40 et 60"
MesSeries.Interior.ColorIndex = 23

Case "entre 60 et 70"
MesSeries.Interior.ColorIndex = 49

End Select
Next
End With
End Sub

Un petit décryptage s’impose:

Bonne surprise, le code est moins long… En effet, il n’y a plus que l’information couleur intérieure à faire varier selon l’intitulé de la série. Dans Sub, j’ai donné un nom différent à ma fonction [CouleurValeurSeries()], sinon le principe est identique à ce que l’on a vu dans la 4ème partie.

» Le résultat: pas mal, non ?

Pour les manipulations qui permettent de générer le résultat c’est comme on a vu dans la 4ème partie: on joue la macro, on sélectionne la macro “CouleurValeurSeries” (cette fois), et on l’exécute !

» Si vous voulez allez plus vite… télécharger le fichier excel suivant:

une couleur selon le nom de la série (auto)

» Même avec ça vous n’y arrivez pas ? refaîtes un tour ici !

Et si on allait plus loin sur le Wiki des Janusiens ?

Participez au Wiki Graphiques Excel

Si vous avez lu ce tutoriel jusqu’à la fin… c’est sûrement que vous avez envie de devenir un pro des graphiques Excel. Je suis sûr que vous avez déjà quelques connaissances, j’ai créé un Wiki où on peut les partager… Ca serait vraiment chouette, si chacun pouvait expliquer ses techniques pour réaliser des graphiques sympas sous Excel. Vous pouvez également poster dans les commentaires les captures de graphiques que vous aurez pu réaliser avec ce tutoriel (vous pouvez utiliser Zimagez pour cela)

(8 votes, moyenne: 4.63 sur 5)

 Loading ...

Dans le même genre

• Ecoutez vos CD sur tous les lecteurs mp3 du marché
• Les Raccourcis Clavier pour gagner en rapidité
• Création et impression de carte de visite pro en moins de 5 minutes
• Optimiser la taille d'une video avec VirtualDub et le codec DivX
• Réaliser un diaporama photo sympa avec musique, images, et texte