Monsieur Excel
Pour tout savoir faire sur Excel !

29 janvier 2011

Une macro pour le sparkline

Au cas où vous auriez souvent besoin de réaliser des graphes en sparkline comme celui présenté dans le dernier article, voici une petite macro qui transforme un histogramme standard en histogramme tel que celui de notre graphique sparkline.

Il ne reste plus qu’à minimiser l’espace entre la zone de traçage et la zone de graphique, puis à ancrer le graphe dans une cellule pour obtenir le sparkline désiré…

Voici le macro, puis la copie d’écran du graphe original et du graphe transformé. Il faut activer le graphe avant de lancer la macro…

Sub Nettoyage()

ActiveChart.Legend.Delete

ActiveChart.Axes(xlCategory).Select

With Selection

.MajorTickMark = xlNone

.MinorTickMark = xlNone

.TickLabelPosition = xlNone

End With

ActiveChart.ChartGroups(1).GapWidth = 20

ActiveChart.Axes(xlValue).Delete

ActiveChart.SeriesCollection(1).Interior.ColorIndex = 5

ActiveChart.PlotArea.Interior.ColorIndex = 19

ActiveChart.Axes(xlValue).MajorGridlines.Delete

End Sub

25 janvier 2011

Des sparklines en Excel 2003-2007

Dans les deux derniers articles, nous avons vu comment créer des graphiques en sparkline grâce à la nouvelle fonctionnalité introduite dans Excel 2010.

En faut, rien ne vous empêche de créer vous-même de tels graphiques : il suffit pour cela de créer le graphique, de placer son coin supérieur gauche dans sa cellule de destination et de le redimensionner pour qu’il remplisse cette cellule.

Reprenons la série « Pommes » du dernier article, créons un histogramme standard, puis réalisons les opérations suivantes :

● clic sur la légende puis [Eff] (ou [Del]) pour la détruire ;

● clic sur la l’axe des Y puis [Eff] (ou [Del]) pour le détruire ;

● clic droit sur l’axe des X puis « Format de l’axe », « Motifs » et désactivation des graduations et étiquettes ;

● clic droit dans la zone de traçage, « Format… » et désactivation des bordures et aires ;

● clic sur un marqueur horizontal puis [Eff] (ou [Del]) pour les détruire tous ;

● clic droit dans la série puis « Format… », « Options » et réduction de la largeur de l’intervalle entre les barres ;

● redimensionnement de la zone de traçage pour qu’elle remplisse au mieux la zone de graphique ;

● déplacement avec la touche [Alt] enfoncée pour ancrer le bord supérieur gauche du graphe dans le bord supérieur gauche de la cellule cible ;

● agrandissement de la hauteur de la ligne à 52,5 points puis alignement de toute la ligne sur le centre (dans la dimension verticale) ;

● redimensionnement avec la touche [Alt] enfoncée de la zone de traçage pour que son bord inférieur droit s’ancre dans le bord inférieur droit de la cellule.

La copie d’écran ci-dessous montre ce que nous avons obtenu avec Excel 2003 avec, en dessous, une photo du résultat que nous avions obtenu avec Excel 2010.

Et voilà ! Vous venez de créer votre premier sparkline en « cousu main » !

Remarque 1 – Certes, vous ne pourrez pas (ou, en tout cas, pas facilement) mettre les valeurs négatives dans une différente couleur, ni associer un marqueur aux valeurs extrêmes, ou à la première et à la dernière valeur…

Remarque 2 – Si la cellule n’est pas assez haute, le bas du graphe est tronqué. C’est pourquoi nous avons attribué une hauteur de 52,5 points à la ligne.

Remarque 3 – Vous pourrez en revanche mettre dans une cellule un camembert ou tout autre type de graphe autorisé par Excel, ce qui n’est pas possible avec Excel 2010, mais – dans certains cas – possible avec des compléments (ou add-ins) sparkline du commerce.

Remarque 4 – Je n’ai pas mis ici d’exemple de camembert car mes données comportent des valeurs négatives et le bug original d’Excel, qui consiste à toujours tracer des camemberts en prenant la valeur absolue des valeurs de la série, n’a encore jamais été corrigé !

21 janvier 2011

Utilisation des sparklines

Quand on crée un graphique sparkline, on peut en créer plusieurs d’un coup. C’est ce que nous avons fait ici en sélectionnant L1:L3 puis en indiquant que le graphique prenait ses données dans le bloc B1:K3. On peut gagner ainsi du temps en créant trois graphiques d’un coup mais avec l’inconvénient que ces trois graphiques sont alors liés. Dès que l’on clique dans l’un d’entre eux, les trois sont sélectionnés.

C’est pratique dans certains cas. Ici, par exemple, nous avons demandé l’affichage de l’axe des X et celui-ci est apparu aussitôt dans les trois graphes. De même, le remplacement de la couleur par défaut de l’histogramme par un bleu ciel s’est appliqué ici dans les trois graphes.

L’inconvénient est qu’il est alors bien plus compliqué de modifier un graphe séparément. C’est pourquoi nous avons créé en colonne M trois graphes indépendants.

Notons que l’on peut modifier de très nombreux paramètres quand un sparkline est sélectionné : marquage des valeurs négatives (cf. cellules M1 et M2), modification des couleurs, marquage des points extrêmes, marquage du premier et du dernier point, ajout d’axes (cf. copie d’écran ci-dessus)…

Remarque – Notons que la commande de modification de l’épaisseur du trait n’est pas facile à trouver : il faut passer, comme nous le voyons dans la copie d’écran ci-dessus, par la commande « Couleurs sparkline ». Il aurait été plus logique d’ajouter une commande « Epaisseur du trait » au menu antérieur…



17 janvier 2011

Découverte des sparklines

Le concept de sparkline a été inventé par Edward Tufte, ainsi présenté dans Wikipedia : Edward Rolf Tufte, né en 1942 (Kansas City, Missouri, États-Unis) est un professeur de statistiques, d'informatique, de design de l'information et d'économie politique à l'Université Yale. Il a été décrit par le New York Times comme le « Léonard de Vinci des données ». Notez que, contrairement à certains auteurs français, dont un récent couronné Goncourt et un journaliste célèbre, je ne rechigne pas à citer mes sources…

Microsoft a déposé des brevets en mai 2008, au grand dam d’Edward Tufte et d’autres chercheurs et professionnels (cf. http://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0003Y1&topic_id=1). Espérons que tout cela trouvera une solution honnête…

Une sparkline, c’est un graphe qui tient dans une cellule unique de votre tableur. Au départ, il s’agissait uniquement de courbes (d’où le terme « line »). Les sparklines existent dans Excel, via divers add-ins (ou compléments) depuis plusieurs années déjà.

Ces graphiques sont intégrés dans Excel depuis la version 2010 du logiciel. Nous verrons dans un article ultérieur comment procéder avec les versions antérieures d’Excel.

Dans ce premier article, nous voyons comment créer une première sparkline avec Excel 2010 : le curseur est en D2, nous activons le ruban « Insertion » et activons l’outil « Courbes » du bloc « Graphiques sparkline ». Voici comment nous avons rempli le dialogue :

Le résultat apparaît ci-dessous, avec en outre deux nouveaux graphes avec, pour la même série de données numériques, l’histogramme correspondant et le positif/négatif associé.

Remarque 1 – Dans la version « Positif/Négatif », il n’y a aucune indication de la valeur absolue, mais seulement la distinction Positif/Nul/Négatif.

Remarque 2 – L'histogramme par défaut ne permet pas de bien voir qu'il y a des valeurs négatives dans la série, mais nous verrons comment corriger cela dans le prochain article.

12 janvier 2011

Découverte d’Excel 2010

Comme résolution de nouvelle année, j’ai décidé de tester enfin Excel 2010. Je me suis accordé, comme d’habitude, un certain temps de précaution, pour que les bugs les plus gênants de cette nouvelle version aient eu le temps d’être corrigés :)

Dans ce premier article, nous allons d’abord passer rapidement en revue les principales nouveautés d’Excel 2010.

Le menu Fichier

Le menu « Fichier » représente une des modifications les plus visibles apportées à Excel par la version 2010.

Ouf ! Fini le bouton « Office » ! Nous reparlerons de ce menu dans la deuxième partie de cet article… En tout cas, cette première nouveauté mérite d’être accueillie avec bienveillance…

Le ruban Accueil

Le seul changement visible du ruban « Accueil » est la plus grande place prise par les commandes « Renvoyer à la ligne automatiquement » et « Fusionner et centrer ». Il est malheureux d’occuper tant de place pour deux commandes dont la première sert peu souvent (et peut être accessible via la commande
« Alignement ») et dont la seconde, la fusion de cellules, est pour moi la plus mauvaise commande jamais imaginée dans Excel. En effet, la force du tableur comme outil de travail tient au fait que les lignes, les colonnes et les cellules soient toutes indépendantes de leurs congénères : cette belle indépendance est détruite dès la première fusion de cellules :(

Pour vous en convaincre, consultez mon article « Ne fusionnez plus jamais vos cellules ! » du 17 novembre 2005. Un des premiers articles de ce blog, et ce n’est pas par hasard.

Le ruban Insertion

Notons un petit changement dans le ruban « Insertion », une modification qui est en fait une régression : la commande « Tableau croisé dynamique » d’Excel 2007 est renommée « TblCroiséDynamique », ce qui représente le double inconvénient d’occuper plus de place en longueur et d’être moins lisible… En revanche, bravo pour l’apparition du bloc « Graphiques sparkline » – là, au moins une nouveauté vraiment utile – et du bloc « Filtre » avec sa commande unique « Segment ». Nous reparlerons de ces deux nouveautés un autre jour.

Le ruban « Mise en page »

Nous n’avons noté qu’un seul changement dans le ruban « Mise en page », le déploiement du dernier bloc – Organiser – avec l’affichage de toutes les commandes associées.

Le ruban « Formules »

Dans le nouveau ruban « Formules », je ne vois pas l’intérêt de bloquer une dizaine de centimètres de ruban avec toutes les familles de fonctions (Financier, Logique, Texte,…). En revanche, le fait de rendre vraiment visibles les commandes « Afficher les formules », « Vérification des erreurs » et
« Evaluation de formule » est nettement plus utile, de même pour les deux dernières commandes, « Calculer maintenant » et « Calculer la feuille ».

Les rubans « Données », « Révision » et « Affichage »

Aucune modification notable dans les rubans « Données », « Révision » ou
« Affichage », si ce n’est le déploiement de certaines commandes qui auparavant n’étaient représentées que par des icônes parfois peu explicites…

Le menu « Fichier »

Le menu Fichier est une totale nouveauté : il n’y en avait pas dans Excel 2007 et le look en était assez différent dans Excel 2003, où il y avait grosso modo les mêmes commandes (à part « Récent » et « Enregistrer et envoyer ») mais sans la partie droite désormais non seulement présente mais aussi de taille conséquente :

Notons aussi la présence de la commande « Options » qui n’a en fait rien à voir avec le thème du menu, « Fichier », mais qu’il fallait bien loger quelque part : cette commande reprend le rôle du bouton « Options Excel » disponible en Excel 2007 via le bouton « Office ».

Une première conclusion...

Le passage d’Excel 2003 à Excel 2007 avait provoqué un certain traumatisme auprès de la plupart des utilisateurs d’Excel, l’ergonomie du logiciel ayant alors été totalement modifiée.

Le passage de la version 2007 à la version 2010 est nettement plus aisé, la plupart des nouveautés – par exemple la possibilité de personnaliser les rubans – étant soit invisibles au premier coup d’œil, soit discrètes, comme par exemple la présence du bloc « Filtre ».

07 janvier 2011

Classement de matches de foot

On dispose dans les colonnes A:D du classeur d’une liste de matches de foot ayant lieu en première journée de championnat. Comme nous pouvons le voir ci-dessous, l’ordre chronologique n’est pas respecté dans cette liste.

L’objectif est d’obtenir la liste à droite, où l’ordre chronologique a été respecté et où, pour chaque journée, on respecte l’ordre du classement initial.

L’astuce que nous avons trouvée – pour alléger les formules – est d’utiliser la colonne G, masquée ci-dessus, et dans laquelle on calcule le numéro de la ligne à utiliser pour le match recherché. Voici les formules originales de ce modèle :

F5 : =petite.valeur($A$5:$A$14;ligne()-4)

G5 : =equiv(F5;A:A;0)

G6 : =si(F6=F5;equiv(F6;decaler($A$1;G5;0):$A$14;0)+G5;equiv(F6;A:A;0))

H5 : =index(B$1:B$14;$G5)

La seule formule demandant une certaine réflexion est celle de G6…
Nous vous laissons le plaisir de la décortiquer pour en comprendre le principe.

03 janvier 2011

Un film pour le VBE

Un cadeau de début d'année, la première vidéo du blog !

Pour le moment, pas encore de son car j'ai encore un bruit de fond désagréable quand j'enregistre, même avec un micro...

La vidéo ci-dessous montre comment se réalisent les opérations décrites dans les deux articles précédents : réduction de la fenêtre et divers ancrages.

Pour faire sortir une fenêtre ancrée de son ancrage, on fait un double clic sur sa bande du haut…


02 janvier 2011

Mise en place de « Propriétés »

Tout d’abord, mes meilleurs vœux pour l’année 2011 !

Une info : Microsoft m'a accordé le titre de MVP Excel 2011 :)


Et maintenant, au travail !

Supposons qu’en suivant les instructions de l’article précédent, vous ayez réussi à bien loger l’explorateur de projets sur la gauche de la fenêtre du VBE. Vous vous trouvez alors dans la situation suivante, où le prochain objectif est de loger la fenêtre « Propriétés » sous celle de l’explorateur de projets :

Masquons à présent (clic droit dans la fenêtre puis « Masquer ») la fenêtre de code, afin de ne garder que l’explorateur de projets et la fenêtre de propriétés. Déplaçons légèrement la fenêtre de propriétés vers la gauche : le liseré est alors épais (Figure 1).

Si l’on continue à tirer la fenêtre vers la gauche, on voit – en pointillés fin cette fois-ci ! – un rectangle vertical (Figure 2). Si l’on lâche le bouton de la souris à ce moment-là, la fenêtre de propriétés se loge en bandeau vertical, à droite de celui de l’explorateur de projets.

Si l’on tire en revanche la fenêtre vers le bas, on voit – en pointillés fin cette fois-ci ! – un rectangle horizontal (Figure 3). Si l’on lâche le bouton de la souris à ce moment-là, la fenêtre de propriétés se loge dans le bandeau vertical, sous l’explorateur de projets.

Et voilà ! Vous avez réussi à reconstituer le disposition du VBE à l’installation d’Excel. Il suffit maintenant de faire un double clic sur un module pour qu’il remplisse la partie droite de la fenêtre du VBE.

Remarque – Vous le savez probablement déjà, mais je le dis à tout hasard… Quand on se positionne entre les deux fenêtres du bandeau gauche jusqu’à trouver le curseur de redimensionnement de ligne, on peut monter ou descendre la séparation horizontale entre la fenêtre de l’explorateur de projets et la fenêtre de propriétés.