Mes meilleurs vœux pour 2009 !

Après une année 2008 qui a vu la crise financière puis la crise économique succéder à la crise des subprimes, il ne me reste plus qu’à vous souhaiter – en espérant ne pas trop forcer le destin – une année 2009 de bien meilleure qualité !

Je vous adresse mes meilleurs vœux pour 2009 !

Le message de ce jour est un peu particulier pour moi : il est en effet le 300ème message du blog « Monsieur Excel », dont le premier message est paru le 1er octobre 2005. En outre, c'est le jour où mon blog va passer le cap des 222.222 visites cumulées. Durant ces trois années un quart, j’ai tenu le rythme d’un message tous les quatre jours : l’écart moyen entre deux messages a été de 3,97 jours !

Ce blog reçoit actuellement en moyenne 300 visites par jour, soit près de 10.000 visites par mois…

Je tiens à remercier au passage les entreprises qui m’ont fait confiance en 2008 en me demandant de leur développer des modèles Excel et/ou d’animer chez elles, en intra, des formations avancées : Arianespace, ChateauForm, Coda France, Dassault Systèmes, Eramet, Gaz de France, RTE et SKF, pour ne citer que les plus connues.

Il est juste de remercier mes clients. En effet, le blog par lui-même n’est pas rentable et c’est mon activité de consultant qui, objectivement, finance de fait la réalisation de ce blog… Et qui l'alimente aussi, car de nombreux conseils que je vous donne sont tirés de cette expérience.

Calcul du nombre de commentaires

Un lecteur me demandait récemment comment calculer facilement le nombre de cellules – dans une sélection – qui contenaient un commentaire.

Il souhaitait en outre – pourquoi faire simple quand on peut faire compliqué ? – calculer ce nombre soit par macro-fonction soit par macro…

Une macro-fonction

Nous voyons en premier, dans la copie d’écran ci-dessous, la macro-fonction permettant de résoudre le problème. Pour l’utiliser, il suffit d’entrer dans une cellule la formule =nbcomm(adresse) où adresse est l’adresse de la sélection dans laquelle on veut connaître le nombre de commentaires :

La macro Comm

La macro Comm ci-dessus est pour sa part bien plus compacte, avec sa ligne unique !

Elle présente un autre avantage : elle fonctionne aussi bien avec une sélection simple qu’avec une sélection multiple – obtenue avec la touche [Ctrl] enfoncée –, ce qui n’est pas le cas de la macro-fonction.

Remarque – Comme dans un long mot allemand, il faut lire les mots de droite à gauche. Ainsi, nous demandons au VBA, dans ce cas, de compter (count) le nombre de cellules (cells) de type commentaire (typecomments) de la sélection active (selection)...

Deux livres sur Excel 2007

Je souhaite tout d’abord à toutes mes lectrices et à tous mes lecteurs un Joyeux Noël !

Voici un dessin de circonstance que j’ai trouvé sur le site : www.crazy-jokes.com/Christmas-Cartoons/xmas_22.shtml :

Excel 2007 – Initiation (220 pages)

Excel 2007 – Avancé (240 pages)

Editions Eyrolles. Guides de formation Tsoft.
Auteurs : Philippe Moreau et Patrick Morié.
Prix de chaque ouvrage ; 16 €.
Fichiers des livres disponibles sur le Web.

Excel 2007 - Inititation

Le premier livre se compose de trois parties : Guide d’utilisation, Exercices de prise en main, et Cas pratiques.

Toutes les notions de base d’Excel 2007 y sont clairement présentées avec seulement le problème – pour les gens qui, comme moi, ont de petits yeux fatigués – de la taille des copies d’écran qui, à l’instar des icônes de la barre d’accès rapide d’Excel 2007, sont trop petites.

Excel 2007 – Avancé

Bien naturellement, c’est cet ouvrage qui a le plus retenu mon attention. Il comporte deux parties, Manuel utilisateur et Cas pratiques.

Dans le manuel utilisateur, les sept chapitres sont baptisés Calculs et simulations, Pour améliorer votre efficacité, Dessins, images et objets graphiques, Représentation graphique des données, Gestion et analyse des données, Contrôle, échange et collaboration et enfin Annexes.

Dans les cas pratiques, 22 cas sont analysés, couvrant donc un large éventail d’applications.

Conclusion

Dans l’ensemble ces deux livres sont clairs et bien présentés, à part ma réserve sur la taille des copies d’écran.

On appréciera en particulier l’annexe « Où sont passées les commandes d’Excel 2003 » du second ouvrage…

Avec Excel 2007, c’est parfois pire !

Chaque fois qu’une nouvelle version d’Excel sort, j’appréhende un peu… En effet, pour moi, si 50% des nouveautés de chaque version n+1 représentent un progrès par rapport à la version n, les autres 50% correspondent à une régression. Dans cet article, nous allons comparer à ce titre les modèles de graphe, entre les versions 2003 ou XP et la version 2007.

Taille de l'image

Dans l’ancienne version, dès que l’on clique sur le nom d’un modèle, on voit une grande image représentant ce que donne ce modèle pour les données actives.

Avec Excel 2007, l’image est toute petite, le graphe ne représente plus les données actives, et on perd le fond d’image (la secrétaire a disparu !) :

Création d'un modèle

Avec 2003 ou XP, la commande « Type de graphique » - après sélection d’un onglet et un clic dans un bouton, donne directement accès à la création d’un modèle.

Avec 2007, il faut en revanche aller chercher le ruban « Création », ce qui représente un accès indirect et, au moins en ce qui me concerne, peu intuitif.

Suppression d'un modèle

Avec 2003 ou XP, pour supprimer le modèle sélectionné, il suffit de cliquer dans le bouton « Supprimer ».

Avec Excel 2007, ce bouton n’existe plus, il faut penser à utiliser à cet effet la touche [Suppr].

Bilan de la comparaison

Sur chacun de ces trois points (taille et fidélité de l’image, facilité de création, facilité de suppression), l’avantage va clairement à Excel 2003 ou XP, les anciennes versions…

Comme quoi le « progrès » est parfois une régression !

Remarque - J'espère qu'avec cet exemple vous comprendrez mieux pourquoi je trouve que chaque nouvelle version d'Excel apporte 50% de changements antiproductifs...

Un modèle de graphe avec 2007

Supposons que nous soyons emballés par le graphe que nous avons personnalisé le 3 décembre dernier, l'histogramme avec en fond d'écran l'image d'une personne devant son PC. Nous souhaitons pouvoir rapidement créer un autre graphe avec cette présentation. Pour cela, il suffit de créer un, modèle de graphe.

Création d’un modèle de graphe

Cliquons dans le graphe pour l'activer, activons le ruban « Création », puis cliquons dans le bouton « Enregistrer comme modèle », attribuons le nom
« Secrétaire » à ce modèle et validons enfin en faisant un clic dans le bouton « Enregistrer ».

Utilisation d'un modèle de graphe

Pour appliquer un modèle à un graphe existant, faites un clic droit sur ce graphe, puis sélectionnez la commande « Modifier le type de graphique », cliquez sur le dossier « Modèles », choisissez le modèle désiré et validez en cliquant sur « OK » ou plus simplement encore avec [Entrée].

Remarque 1 – L'icône pour chaque modèle montre seulement le type général du graphique. Dans notre cas, l'icône montre bien un histogramme, mais pas l'image de fond.

Remarque 2 – Quand le curseur reste sur l'icône d'un modèle, le nom de ce modèle apparaît.

Destruction d'un modèle

Pour détruire un modèle, cliquez sur le bouton « Gérer les modèles » puis sélectionnez le modèle à détruire, et appuyez sur la touche [Suppr]. Le modèle disparaît de la liste. Tout semble OK jusqu'au moment où l'on ferme la fenêtre : le modèle réapparaît alors !

Rassurez-vous, c'est un petit bug... La prochaine fois que vous appellerez les modèles, le modèle « supprimé » aura vraiment disparu de la liste !

Un modèle de graphe en 2003-XP

Après quatre articles sur la version 2007, enfin un article pour les utilisateurs des versions 2003 et XP d'Excel !

Création d’un modèle de graphe

Pour créer un modèle de graphique, il suffit de faire un clic droit dans une zone de graphique, comme on le voit dans l'image ci-dessous, puis d'utiliser la commande « Types de graphique », l'onglet « Types personnalisés », puis le bouton « Types personnalisés », et enfin de cliquer dans « Ajouter ».

Vous donnez par exemple le nom « Secrétaire » à ce nouveau modèle. Dès que vous validez, ce nouveau modèle s’ajoute aux modèles antérieurs.

Remarque 1 – Microsoft a encore fait très fort dans la traduction, en inventant un « type personnalisé » de « type personnalisé » !

Utilisation d’un modèle de graphe

Pour appliquer un modèle donné au graphe actif, il suffit de sélectionner le modèle et de valider par « OK ».

Si vous cliquez dans le bouton « Par défaut », ce modèle devient le modèle par défaut, c’est-à-dire celui que vous obtenez quand vous cliquez dans l’assistant graphique puis validez immédiatement par « OK ».

Remarque 2 – Pour ne pas perdre l’ancien modèle par défaut d’Excel lors de la définition d’un nouveau modèle par défaut, créez préalablement un modèle avec ce format, que vous baptiserez par exemple « Standard ». Cela vous permettra, par la suite, de redéfinir si nécessaire « Standard » comme étant le nouveau modèle par défaut.

Les changements dans les graphes

Les graphes d'Excel ont subi des changements significatifs lors du passage à Excel 2007. Voici la liste des principales modifications...

Disparition de l'échelle automatique par défaut

Un des premiers changements significatifs dans les graphes d'Excel 2007 est que Microsoft a enfin – après tant d'années ! – éliminé le réglage totalement débile selon lequel les polices utilisées dans un graphe étaient par défaut "à échelle automatique", c'est-à-dire qu'elles changeaient de taille dès que l'on redimensionnait le graphe.

Des possibilités de décoration plus poussées

Le format de la zone graphique ouvre la porte à de multiples possibilités de décoration de la zone, avec par exemple les réglages "Ombre" et "3D", comme on le voit dans l'écran ci-dessous. Il est en outre très facile de créer, pour le fond comme pour les séries, des effets de dégradé entre deux couleurs.

Remarque 1 – Heureusement, une commande "Rétablir le style d'origine" permet – si l'on a joué abusivement avec tous ces formats – de revenir au graphe initial.

Remarque 2 – Il reste toujours – heureusement ! – l'exceptionnelle possibilité de copier un graphe et d'en faire un "Collage spécial" des formats sur un autre graphe pour le décorer, d'un coup d'un seul, comme le premier.

La commande "Sélectionner des données..."

Cette commande – très mal nommée par ailleurs – remplace l'ancienne commande "Série de données". Elle permet – enfin ! – d'un clic sur le bouton "Changer de ligne ou de colonne", très mal nommé lui aussi, de permuter rapidement les lignes et les colonnes, c'est-à-dire la légende et l'axe des abscisses.

Notons aussi – enfin encore ! – la présence du bouton "Cellules masquées et cellules vides" qui permet de choisir ce que l'on fait dans l'un ou l'autre de ces deux cas. Ces deux réglages, dans les versions récentes d'Excel, avaient été honteusement cachés dans l'onglet "Graphique" de la commande Outils Options.

Mise en forme d'un d'un graphe

La façon la plus rapide de mettre en forme un graphe reste le clic droit sur ce que l'on souhaite modifier - zone de traçage, axe, légende, série,... - suivi d'un choix dans le menu contextuel qui apparaît alors.

Sauf si l'on souhaite modifier la police, sa taille ou la couleur de fond ou de police, auquel cas un simple clic gauche suffit - pour sélectionner l'objet concerné -, suivi d'une action directe dans le bloc Police du ruban Accueil.

Remarque 1 - Encore une incohérence dans la traduction d'Excel ! Selon l'objet que l'on sélectionne, la commande de mise en forme s'intitule : "Format de la...", "Mise en forme de...", "Mettre en forme une...". Il
aurait été plus logique d'utiliser toujours la même formule.

On peut personnaliser un graphe à loisir. Dans le graphe ci-dessus, nous avons mis une image de fond, donné un fond blanc à la légende et aux axes, modifié les couleurs des séries pour qu'elles ressortent mieux, et ajouté un filet noir aux barres de l'histogramme.

Remarque 2 - Certes, le résultat final est plus original mais, malgré tous nos efforts, il reste moins lisible que le graphe initial. Les seules modifications qui peuvent être sans discussion considérées comme bénéfiques sur le plan de la lisibilité sont les couleurs plus franches des séries et leur encadrement par un filet noir.

Création d'un graphe en Excel 2007

Pour qui vient d'Excel 2003 ou XP, dès le début de la création d'un graphe avec 2007, c'est différent : il n'y a plus d'Assistant Graphique.

Pour ma part, cela ne change pas grand chose car je ne m'en servais qu'une fois, en cliquant dessus pour demander un graphe et en validant aussitôt. En effet, tous les réglages s'effectuent bien plus vite en direct qu'en passant par l'assistant.

Pour créer un graphe, placez le curseur n'importe où dans le bloc de données (ici; en C2), activez le ruban Insertion et cliquez dans le type de graphique souhaité. Le graphe apparaît aussitôt.

Remarque 1 - Il y a des incohérences dans le vocabulaire, ainsi la famille des histogrammes s'appelle "Colonne" au lieu de "Histogramme" ou "Histog." si l'on veut tenir dans la largeur. Il y a aussi la série des graphes boursiers qui est baptisée tantôt "Boursier" tantôt "Stock". Le jour n'est toujours pas venu où Microsoft demandera à un professionnel de valider sa traduction d'Excel en français...

Remarque 2 - J'aurais préféré une icône initiale donnant directement accès à la commande "Tous types de graphiques". Please, Microsoft !

Remarque 3 - Pour une fois, je peux donner un bon point à Microsoft ! En effet, ils ont enfin - on a dû attendre plus de 20 ans ! - annulé le ridicule réglage par défaut selon lequel la taille de la police d'un graphe était automatique, c'est-à-dire changeait quand on modifiait la dimension du graphe. Dorénavant, on peut redimensionner un graphe sans voir la police changer de taille !

Remarque 4 - Certains puristes m'ont déjà dit que je devais dire "graphique" et non "graphe" pour traiter de ce thème. Le terme me semble plus léger une fois "équeuté", je persisterai donc dans mon erreur... Je dois avouer que l'utilisation de termes tels qu'auteure ou procureure me semble être un péché bien plus grave...

Un livre sur les TCD d'Excel 2007

“Excel 2007 : Tableaux croisés dynamiques”

Ce livre écrit par Pierre Rigollet, publié aux Editions ENI (cf. « Autres liens » dans le bandeau à droite du blog), pourra servir de livre de chevet à tous ceux qui souhaitent utiliser des TCD avec Excel 2007.

Il présente de façon claire tout ce qui est nécessaire à la création à l’utilisation d’un TCD, y compris la définition dynamique de la base de référence.

Il sera en particulier utile à ceux qui – en provenance d’Excel 2003 ou XP – souhaitent appréhender rapidement les nouveautés de la version 2007 en la matière.

Le seul reproche que l’on puisse faire à cet ouvrage est l’absence d’un chapitre final mettant en relief les lacunes des TCD et les problèmes pouvant survenir de la mauvaise gestion par Excel du masque des TCD.

En effet, pour ma part et en tant que consultant, j’utilise peu les TCD, préférant les remplacer – dans un seul souci d’efficacité – par une combinaison astucieuse de fonctions BD…() et de tables d’hypothèse à une ou deux entrées.

Pour une présentation ou une révision des tables, le lecteur pourra consulter mes messages du 17, du 21 et en particulier du 25 octobre 2006, où je présente leur application en lien avec une base de données.

Création d’un « splash screen »

Un splash screen, c’est un message qui apparaît automatiquement à l’ouverture d’un classeur puis – tous aussi automatiquement – disparaît ensuite après quelques secondes.

Supposons que nous souhaitions afficher le message ci-dessous à l’ouverture d’un classeur, et ce pendant trois secondes. Pour cela, il faut d’abord insérer une UserForm en VBA, puis créer la zone de texte voulue (le message en bleu ci-dessous).

Il ne reste plus qu’à ajouter une macro liée à ThisWorkbook :

Private Sub Workbook_Open()
UserForm1.Show
End Sub

… à créer une macro (cf. image ci-dessus) associée à l’événement Initialize de la UserForm …

… et enfin à créer un module dans lequel on loge la macro suivante :

Sub Fermer()
Unload UserForm1
End Sub

L’aperçu des sauts de page (b)

Nous avons découvert la commande « Aperçu des sauts de page » il y a quatre jours. Une autre particularité de cette commande est qu’elle modifie le menu contextuel Cell, celui qui correspond au clic droit dans une sélection de cellules :

Ce menu contient maintenant un dernier bloc – spécifique à l’impression – dont en particulier la commande « Ajouter à la zone d’impression ». Si, comme ci-dessus, je sélectionne A7:C10, puis j’active la commande, je crée une troisième page d’impression avec ce bloc.

Remarque – Dès que l’on active une feuille qui n’est pas en mode « Aperçu des sauts de page », on récupère le menu contextuel Cell habituel.

L’aperçu des sauts de page (a)

La commande « Aperçu des sauts de page » du menu Affichage est probablement la commande utile d’Excel la plus méconnue, même des spécialistes.

Supposons que nous ayons défini la zone d’impression comme étant deux blocs disjoints, A1:E4 et A12:F16, qui vont donc s’imprimer sur deux pages séparées.

Quand on active cette commande, toute la feuille est grisée, sauf la zone à imprimer dans laquelle le numéro de page est indiqué en filigrane :

Le miracle, c’est que l’on peut alors, en plaçant le curseur sur un des quatre coins d’un bloc, redimensionner ce bloc. Par exemple, nous pourrions ici tirer le bord inférieur droit du premier bloc, de E4 vers F5, pour incorporer les totaux.

Une zone d’impression au choix

Nous reprenons l’exemple présenté il y a quatre jours, mais cette fois-ci, nous avons en B19 une cellule avec une validation prenant ses valeurs dans le bloc D18:D20, et en B20 la formule =RECHERCHEV(B19;D18:E20;2;FAUX) qui ramène la description de la zone à imprimer.

La macro suivante définit la zone d’impression et affiche l’aperçu avant impression correspondant :

Sub Zone()
ActiveSheet.PageSetup.PrintArea = Range("B20").Value
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Remarque 1 – Pour définir une zone d’impression multiple, comme nous l’avons fait en E20, n’oubliez pas de séparer les blocs par des « , » ou lieu des « ; » ; en effet, Visual Basic parle en anglais !

Remarque 2 – Si les zones à imprimer se trouvent sur plusieurs feuilles du classeur, il suffit d'ajouter en F18:F20 le nom de la feuille concernée, de récupérer en B21 la feuille sélectionnée et de modifier la macro pour qu'elle sélectionne la bonne feuille.

Définir la zone d’impression

Supposons que vous ayez sélectionné, avec la touche [Ctrl] enfoncée, les blocs A1:E5 et A7:F16. Votre écran se présente alors comme suit :

Utilisez ensuite la commande « Zone d’impression », option « Définir », du menu Fichier. Cette commande définit la zone d’impression comme étant deux blocs qui seront chacun imprimés sur une page séparée, comme on le vérifie aisément en effectuant un aperçu avant impression, soit en direct via son bouton éponyme, soit par la commande correspondante du menu Fichier.

Cette option « Définir » attribue tout simplement le nom (réservé pour Excel) Zone_d_impression à la sélection courante.

Remarque – On aurait très bien pu définir ce nom directement via la séquence Insertion – Nom – Définir ou, plus directement encore, en sélectionnant la zone multiple, puis en tapant le nom dans la zone « Nom » de la barre de formule, et enfin en validant par [Entrée].

Mettre sous forme de tableau (b)

Dès que l’on joue un peu avec les tableaux mis en forme, on arrive tôt ou tard à se poser la question suivante : « Comment puis-je effacer le format de tableau et revenir à la situation antérieure ? »

On cherche alors en vain parmi les options de la commande « Mettre sous forme de tableau » la sous-commande « Aucun format de tableau » qui sauverait la mise.

Un utilisateur astucieux va se dire : « C’est tout simple : je clique dans une cellule en dehors du tableau, un petit coup de pinceau, puis je peins mon tableau ! ». Eh oui, c’est tout à fait astucieux et cela marche avec toutes les versions antérieures d’Excel, mais pas avec Excel 2007 !

La solution – partielle – que j’ai trouvée consiste à utiliser en bas de l’écran des formats de tableaux la commande « Nouveau style de tableau » : j’ai créé un style « Vierge » dans lequel je n’ai rien coché. Ce style apparaît maintenant au début de la liste :

Remarque 1 – Un avantage : si je commence par donner ce format à un tableau, je peux ensuite modifier ce format en voyant immédiatement ce que cela donne sur mon tableau, dès que le curseur passe sur un autre format.

Remarque 2 – Hélas, ce nouveau format ne s’applique qu’au classeur actif. J’aurais tant aimé pouvoir le sauvegarder avec Excel !

Remarque 3 – Ce nouveau format ne résout pas tous les problèmes. En effet, le tableau n’a pas dans ce cas un format réellement vierge : il a toujours un format de tableau, dont seule l’apparence est vierge.

La solution, enfin !

Après de multiples essais, j’ai fini par découvrir la solution !

Il faut faire un clic droit dans une cellule du tableau puis utiliser la commande « Table », sous-commande « Convertir en plage » et enfin confirmer. Moi qui croyais que « Convertir en plage », c’est ce que faisait Delanoë tous les étés sur les rives de la Seine…

Bravo ! Microsoft a encore réussi à rendre compliquée une tâche qui aurait été évidente avec la présence parmi les formats de tableaux d’une option
« Aucun format de tableau »…

Mettre sous forme de tableau (a)

Il y a beaucoup à dire sur la commande « Mettre sous forme de tableau » du ruban « Accueil », donc avec Excel 2007. Cette commande est sensée être la nouvelle version – on suppose bien entendu « améliorée » – de la commande « Format automatique » présentée il y a quatre jours. Pour ma part, et vous allez voir pourquoi, je suis sceptique…

Pour démarrer, faisons comme avec les versions antérieures d’Excel : nous cliquons n’importe où dans le tableau puis appelons la commande.

Apparaît alors un écran avec 52 formats de tableau (ils se sont inspirés d’un jeu de cartes ?) dans lequel on doit choisir un format sans voir ce que cela donnera sur le tableau. S’affiche ensuite un dialogue où il suffira de valider pour voir enfin le résultat :

Remarque 1 – On ne peut pas déplacer la fenêtre affichant la liste des formats, on se demande pourquoi…

Remarque 2 – Par défaut, et sans que l’on ne demande rien, Excel ajoute automatiquement un filtre automatique que l’on perd du temps à effacer en cliquant dans « Trier et filter » puis « Filtrer » (alors que l’on veut dé-Filtrer…). Ce filtre par défaut est nul : j’estime être assez grand pour demander un filtre quand j’en veux un !

Remarque 3 – Si par la suite, je réutilise la commande pour modifier la présentation du tableau, je vois en temps réel mon tableau qui se modifie dès que le curseur passe sur un format. Il serait préférable que cette fonctionnalité soit déjà présente lors du premier formatage !

La mise en forme automatique

La commande « Mise en forme automatique » du menu Format est utile pour décorer rapidement tout un tableau : il suffit de cliquer n’importe où dans un tableau – ici, nous avions cliqué en B2 – puis d’exécuter cette commande, pour obtenir le dialogue suivant :

Cette commande donne en fait accès à seize formats de tableaux, qui modifieront d’un coup de baguette magique les polices, couleurs, motifs et encadrements des cellules concernées.

Remarque 1 – Ces formats ne marcheront bien que si le tableau à décorer est isolé du reste de la feuille. Dans notre cas, si la cellule B6 n’était pas vide, c’est le bloc A1:F10 qui serait décoré au lieu du bloc A1:F5 !

Remarque 2 – Ne prenez pas un format pour de seules raisons esthétiques ! Certains formats (pas exemple « Couleur 2 ») mettent mieux en relief le total à droite, d’autres (par exemple « Comptabilité 3 ») le total du bas, d’autres encore (par exemple « Couleur 3) aucun des deux. Tenez-en compte lors de votre choix.

Remarque 3 – Il est impossible d’ajouter des formats ou d’en modifier les caractéristiques, sauf à passer par une macro que vous lancerez après avoir choisi un format…

Remarque 4 – Le bouton « Options » donne accès à la possibilité de ne pas modifier la police, ou le format numérique, ou la bordure, ou … Voilà une excellente idée au départ, rendue totalement inutile par le fait que les options que l’on décoche, par exemple pour ne pas modifier la largeur des colonnes et la hauteur des lignes, reviennent cochées par défaut dès que l’on veut décorer un nouveau tableau. Si l’on ne souhaite pas les modifier pour un tableau, on ne voudra très probablement pas le faire pour le tableau suivant : il aurait donc été intelligent de mémoriser les options au lieu de les remettre à zéro à chaque fois.

Création d’un second code barre

Il existe de nombreux systèmes de codes barre, linéaires ou à deux dimensions, comme vous pourrez le voir dans les articles suivants sur Wikipédia : http://fr.wikipedia.org/wiki/Code_barre en version française et http://en.wikipedia.org/wiki/Barcode en anglais.

Avec le rappel en colonne B de notre premier code barre – cf. l’article du 15 octobre – nous voyons à présent en colonne C ce que donne un code barre en deux dimensions – il s’agit du code QR – auquel on peut aussi avoir accès via Internet :

Pour obtenir l’image de ce code, il a fallu utiliser cette fois-ci la ligne : ActiveSheet.Pictures.Insert ("http://qrcode.kaywa.com/img.php?s=8&d=" & UCase(ActiveCell.Offset(0, -2).Value)) .

Remarque 1 – Ce second code admet les lettres accentuées mais, quand il y en a au moins une, l’image est parfois 15% plus haute et 15% plus large. C’est le cas pour « Hervé Thiriez » contre « Herve Thiriez », mais pas pour
« Hervé » contre « Herve ». C’est aussi le cas si l’on accentue une des lettres de « Monsieur Excel ». Allez comprendre pourquoi…

Remarque 2 – L’avantage de ce second code est qu’il est lisible par certains téléphones portables, comme celui d’Yves Petit, qui m’a indiqué l’adresse ci-dessus pour le code QR.

Création d'un premier code barre

Si vous souhaitez obtenir le code barre correspondant à un texte donné, rien de plus simple ! Il suffit de faire tourner la macro ci-dessous qui boucle à partir de A1 (en fait, depuis la cellule nommée « Début ») et met à droite le code barre correspondant au contenu de la colonne A.

En fait, on va directement interroger un site qui fournit les codes barre.

Je remercie mon collègue Yves Petit qui m’a montré ce service et a créé la macro ayant servi de point de départ à la macro ci-dessus.

Remarque 1 – Ne mettez ni lettre accentuée ni caractère spécial (apostrophe, parenthèse,…) dans le texte, faute de quoi l’image résultante sera vide…

Remarque 2 – Je convertis le texte en majuscules avec UCase() car ces codes barre ne fonctionnent pas avec des minuscules.

La commande « Gérer les règles »

Il y a deux inconvénients à passer par les commandes présentées dans les deux articles précédents pour les formats conditionnels d’Excel 2007.

Le premier est que, quand on active ces commandes pour une cellule qui a déjà un format conditionnel, on ne voit pas à quelles conditions ce format correspond. Le second est que l’on ne voit pas à quoi ressemble la cellule mise en relief par ce format.

C’est pourquoi je conseille aux utilisateurs soucieux de leur efficacité de ne jamais les utiliser. La seule commande réellement efficace est la commande Gérer les règles :

Cette commande est plus proche de ce que l’on avait dans les versions antérieures et bien plus efficace. En effet :

■ Le dialogue montre à quoi ressemblera la cellule une fois formatée.

■ Le dialogue affiche clairement toutes les conditions.

■ On modifie aisément l’ordre d’application des conditions. Par défaut – à l’opposé de ce qui se passait avec les versions antérieures – c’est la condition définie en dernier qui se place en premier. Là, je vote un blâme à Microsoft, ce choix me paraissant totalement illogique.

■ Heureusement, on peut modifier l’ordre des conditions à l’aide des flèches haute et basse.

■ Il n’y a plus de limite à trois conditions. Sachez toutefois que, si vous en avez plus de cinq, vous n’aurez plus de vision d’ensemble des conditions : la fenêtre n’en affiche que cinq à la fois et ne peut être redimensionnée.

Remarque – Une innovation notable, celle des cases à cocher « Interrompre si Vrai ». Cette option était toujours le cas dans les versions antérieures ; maintenant, l’utilisateur a le choix.

Le format conditionnel de 2007 (b)

Pour la commande « Règles de mise en surbrillance des cellules », il n’y a rien de spécial à dire quant aux options « Inférieur à… », « Entre… », « Egal à… » ou « Texte qui contient… ».

Remarque 1 – On se demande pourquoi il y a « Texte qui contient… » mais pas par exemple « Texte commençant par… » qui peut se révéler tout aussi utile.

Format conditionnel selon les dates

La commande « Une date se produisant… », en revanche, est tout à fait originale car elle permet de rapidement formater toutes les dates correspondant à la semaine suivante, au mois dernier,… Tout ceci est dynamique : les cellules en relief demain ne seront pas les mêmes que celles qui étaient en relief hier !

Doublons et singletons

Enfin, la commande « Valeurs en double » sert à mettre en relief, au choix de l’utilisateur, soit les doublons, soit les valeurs uniques.

Remarque 2 – Ces formats personnalisés sont cumulables : on peut ainsi mettre dans une couleur les doublons et dans une autre les singletons, bien que cela ne représente en fait aucun intérêt puisque l’un des deux peut être le format par défaut des cellules.

Le format conditionnel de 2007 (a)

Les formats conditionnels ont été sérieusement enrichis avec la version 2007 d’Excel. Nous allons vous en présenter les principaux éléments dans cet article et ceux qui vont suivre.

Bien entendu, la compatibilité ascendante fonctionne bien : tous les formats conditionnels définis dans les modèles construits avec les versions 2003 ou XP d’Excel demeurent valides.

La commande « Mise en forme conditionnelle » est présente au milieu du ruban « Accueil », comme on peut le voir dans la copie d’écran suivante où l’on a choisi la commande « Règles de mise en surbrillance des cellules », puis « Supérieur à … » pour obtenir la seconde copie d’écran.

On note ci-dessus que, dorénavant, plusieurs types de mise en forme préétablis apparaissent, avant la commande « Format personnalisé », qui mène à un écran de formatage complet avec les onglets Nombre, Police, Bordure et Remplissage.

Remarque – La grande nouveauté d’Excel 2007, à cet égard, est que l’on peut désormais modifier le format numérique (onglet Nombre) dans un format conditionnel, ce qui était auparavant impossible.

Formations de fin d'année

Vous trouverez ci-dessous la liste des formations que je propose pour la fin de l'année. Chacune de ces formations est limitée à huit participants, avec un ordinateur par personne. Vous pourrez télécharger la description de ces formations en cliquant sur leur intitulé :

■ La modélisation du risque (Crystal Ball) : le jeudi 4 décembre 2008.

■ Découverte de Visual Basic (2 jours) : jeudi 13 et lundi 17 novembre 2008.

■ Modélisation avec Excel (2 jours) : mercredi 19 et lundi 24 novembre 2008.

■ Transition optimisée vers Office 2007 : le vendredi 5 décembre 2008.

Le séminaire Transition optimisée vers Office 2007 est une nouveauté. Le but de cette formation est de permettre aux cadres passant d’Office 2003 ou Office XP à faire – dans les meilleures conditions – la transition vers Office 2007.

En effet, l’ergonomie d’Office 2007 est complètement différente de celle des versions antérieures, avec la disparition des menus et des barres d’outils. Quand on passe à 2007, si l’on n’y a pas été préparé, on perd énormément de temps à rechercher telle ou telle commande.

En une journée de formation, on apprend ainsi à basculer efficacement vers Excel 2007, Word 2007 et Powerpoint 2007. Cette journée a été animée pour la première fois en juillet, en intra, à la satisfaction totale des participants (références disponibles).

Les trois premières formations sont organisées en association avec la société EuroDécision (cf. référence dans le bandeau droit du blog).

Couleur de fond automatique

Dans la colonne B d’un modèle, vous souhaitez saisir le nom d’un matériau, en récupérant automatiquement un fond de cellule rappelant la couleur du matériau. Comme il y a plus de quatre couleurs possibles, vous ne pouvez hélas pas résoudre le problème à l’aide d’un format conditionnel.

La solution est de passer par une macro, comme nous le voyons ci-dessous, où nous avons défini une macro événementielle liée à la feuille de calcul :

Notez que la macro n’agit que s’il y a une intersection non vide entre la sélection et la colonne B, où le nom du matériau doit apparaître.

Si par exemple on sélectionne le bloc A8:C8 pour y entrer béton, seule la cellule B8 portera la couleur de fond du béton.

La dernière cellule non vide

Il est parfois nécessaire, dans un bloc, de récupérer la valeur de la dernière cellule non vide du bloc. Nous prenons ici le bloc à fond bleu ciel A1:J1 et nous voulons récupérer – dans les cellules à fond jaune – la valeur de la dernière cellule non vide de ce bloc.

Comme c’est fréquemment le cas en modélisation, il y a de multiples solutions possibles. Nous vous en montrons trois ici, chacune ayant un caractère particulier. Les formules de la colonne A sont reproduites en colonne B.

Une formule matricielle

La première solution est une formule matricielle, donc validée par [Ctrl]-[Maj]-[Entrée]. Elle récupère par la fonction Index() la valeur du vecteur dont l’indice est le numéro de la dernière colonne dans laquelle on a trouvé une cellule non vide.

Cette formule nous montre – une fois de plus – que l’on peut pratiquement résoudre tous les problèmes d’Excel à l’aide de formules matricielles.

Une formule originale

La seconde formule est doublement originale.

Tout d’abord car elle crée un vecteur grâce à la formule 1/(A1 :J1<>"") : quand on évalue cette formule, on obtient {1.#DIV/0!.1.1.#DIV/0!.1.1.#DIV/0!.1.#DIV/0!}, donc un vecteur contenant un 1 pour chaque position non vide et la valeur d’erreur #DIV/0! pour chaque position vide.

La seconde originalité vient de ce que le troisième argument est le vecteur lui-même alors qu’intuitivement on l’aurait imaginé en deuxième argument plutôt qu’en troisième.

La formule la plus simple

La troisième solution est la plus simple que j’aie pu imaginer mais elle exige que les cellules concernées ne reçoivent que des valeurs positives, ce qui est quand même fréquemment le cas dans des tableaux de données Excel.

Si l’on remplace le premier argument « -1 » par « -10^33 », cette formule fonctionnera aussi si le tableau contient des nombres quelconques supérieurs à -10^33.

Application.International(Index)

Nous avons découvert dans le dernier article la commande Application. International(Index) que nous avons mise à profit pour formuler un message dans la langue de l’utilisateur.

Cette commande renvoie de multiples informations concernant les réglages régionaux et internationaux. Au total, il y a 45 paramètres possibles répartis en 6 familles : Brackets and Braces, Country and Region Settings, Currency, Measurement Systems, Date and Time, et Separators :

Vous avez donc là une commande qui vous donne de multiples indications sur l’environnement de travail de l’utilisateur, ce qui vous permet de mieux personnaliser votre modèle.

Pour avoir tous les détails sur la syntaxe ainsi que des commentaires relatifs aux arguments, sélectionnez la commande Application.International dans votre code VBA et appuyez sur la touche [F1] . Vous trouverez aussi des renseignements intéressants en suivant le lien :

http://support.microsoft.com/search/default.aspx?catalog=LCID%3D1033&query=application.international&mode=r

Pour une application multilingue...

Un programme destiné à être utilisé dans plusieurs pays est beaucoup plus sympathique s’il « parle » la langue du pays. Heureusement pour vous, Excel sait reconnaître la langue de l’utilisateur, ce qui vous permet d’en tenir compte pour choisir ce que vous allez lui dire.

La macro reproduite ci-dessus affiche une phrase dans la langue de l'utilisateur, à condition pour le moment qu'il soit américain, français, espagnol ou allemand :

Le code pays, l’argument « xlCountryCode », est tout simplement le préfixe téléphonique de ce pays.

Remarque 1 – Le Visual Basic, avec la police que j’utilise ici, ne reconnaît pas certains caractères, tels que le « ù » de la phrase en français ou le « ¿ » de la phrase en espagnol. Ce n’est pas gênant car – sur l’écran – la phrase sortira correctement, pour peu que vous ayez utilisé à cet effet une police adéquate.

Remarque 2 – Bien évidemment, si l’on utilise cette solution dans une application réelle, on remplacera le bloc du Select Case par l’unique instruction MsgBox Phrase(n°,pays) faisant appel à la phrase numéro n° de la langue concernée.

Remarque 3 – Quand la phrase est – comme dans cet exemple – une question, c'est évidemment InputBox qu'il faudra utiliser à la place de MsgBox, afin de pouvoir exploiter la réponse :)

Filtrage selon la couleur, sans 2007

Reprenons le problème étudié dans le dernier article : nous souhaitons effectuer un filtrage selon la couleur de fond des cellules dans une base de données Excel mais, cette fois-ci, nous ne disposons pas d’Excel 2007, dans lequel cette fonctionnalité est présente.

Vous disposez alors de deux solutions simples :

1) Vous disposez d’un add-in dans lequel cette fonctionnalité a été intégrée. Il y en a plusieurs, dont EasyFilter, que nous avons présenté le 16 et le 20 juin 2006, ou encore ASAP Utilities.

2) Vous ajoutez à votre base une colonne dans laquelle vous aurez par exemple entré le nom de la couleur mais – heureusement – sans pour autant devoir saisir ces noms cellule par cellule.

Nous allons voir aujourd’hui comment effectuer cela de la façon la plus légère possible, sans VBA. Bien entendu, on pourrait aussi écrire une macro en VBA pour résoudre ce problème…

Nous dupliquons la colonne D en G, puis nous sélectionnons la colonne G, appelons la commande « Remplacer » du menu Edition, entrons « * » dans le champ « Rechercher : » et « rose » dans le champ « Remplacer par : ».

Nous cliquons alors dans le bouton « Options » puis déroulons le menu
« Format » de la ligne « Rechercher : » et sélectionnons la commande
« Choisir le format à partir de la cellule », puis nous cliquons dans la cellule G2 pour sélectionner le fond rose.

C’est d’ailleurs après cela seulement que le champ à gauche du menu déroulant affiche l’aperçu. Il ne reste plus qu’à utiliser le bouton « Remplacer tout » pour remplacer tous les âges en fond rose de la colonne G par la valeur « rose ».

Vous procédez de même pour chaque couleur de fond, après quoi vous pourrez aisément filtrer dans la colonne G en fonction des couleurs de fond.

Remarque – Attention, c’est l’ensemble du formatage de la cellule sélectionnée qui est pris comme modèle. Si nous avions gardé les encadrements de notre dernier article, cela n’aurait fonctionné ni pour la première ni pour la dernière fiche de la base, dont l’encadrement (un gros trait rouge en haut ou en bas) n’était pas le même que pour les fiches intermédiaires ! C’est pour cela que nous avons – dans cet exemple – utilisé le même quadrillage noir fin pour toutes les cellules de la base.

Filtrage selon la couleur, avec 2007

Excel 2007 autorise le filtrage d’une base de données selon la couleur de fond des cellules. Nous avons pris, pour illustrer cette fonctionnalité, une petite base dans laquelle nous avons coloré le fond des cellules.

Sélectionnons la colonne « Age » puis utilisons la commande « Filtrer » du ruban Données. Nous pouvons à présent faire appel à la commande « Filtrer par couleur » puis « Filtrer par couleur de cellule » pour obtenir un filtrage selon la couleur de fond de la cellule.

Dans le cas représenté par la copie d’écran ci-dessus, nous filtrons pour ne retenir que les cellules dont le fond est bleu ciel.

Remarque 1 – Si, avant de lancer le filtre, on désactive certaines valeurs grâce aux cases à cocher placées dans le bas de la fenêtre, cette désactivation sera ignorée lors du filtrage.

Remarque 2 – Un filtrage personnalisé ne peut utiliser le fond de couleur de la cellule pour aucun des deux critères.

Un livre sur les graphiques 2007

Les Editions ENI ont sorti récemment un livre baptisé « Les cahiers d’exercice – Excel 2007 – Les graphiques », écrit par Pierre Rigollet.

Ce livre de 226 pages, au prix de 22,87 €, comporte 37 exercices corrigés, dont les fichiers initiaux peuvent être téléchargés depuis le site ENI. Le tout est regroupé en quatre chapitres : Graphiques simples – Graphiques évolués – Graphiques complexes – Les modèles.

On apprend dans ce livre comment réaliser toutes les opérations courantes sur des graphiques Excel. On y trouve aussi la façon de réaliser des graphiques à deux axes, des plans de terrain, des diagrammes de Gantt,…

L’auteur montre aussi comment utiliser les outils de la barre d’outils « Formulaires » pour faire dépendre l’affichage d’un graphique de choix effectués à l’aide de boutons radio ou d’une barre de défilement.

La dernière partie concerne la création de modèles de graphiques auxquels on accède – dans les versions antérieures d’Excel – par la commande « Type de graphique » puis par l’onglet puis le bouton « Types personnalisés ».

Dans l’ensemble, voici un livre qui permettra à tous de bien se débrouiller avec les graphiques d’Excel 2007.

Commande « Options avancées »

La commande « Options avancées » est de loin la commande d’Options Excel qui autorise le plus grand nombre de réglages. On y trouve onze blocs : Options d’édition – Couper, copier et coller – Imprimer – Afficher – Afficher les options pour ce classeur – Afficher les options pour cette feuille de calcul – Formules – Lors du calcul de ce classeur – Général – Compatibilité avec Lotus – Paramètres de comptabilité avec Lotus.

Nous ne commentons ci-dessous que les commandes nouvelles ou les plus intéressantes de ces onze blocs.

Le bloc « Options d’édition »

Notons la présence dans ce bloc de la nouvelle commande « Avertir l’utilisateur lorsqu’une opération potentiellement longue est lancée », dans laquelle on peut indiquer le seuil (en nombre de cellules concernées) d’activation de cette commande.

Autre nouveauté : la possibilité, à la fin de ce bloc, de modifier le séparateur des milliers et le séparateur de décimale.

Le bloc « Imprimer »

Ce bloc ne comporte qu’une commande unique et tout à fait inédite, « Mode haute qualité pour les graphiques », que je ne peux pas tester en ce moment, n’ayant ici qu’une vieille imprimante sous la main.

Le bloc « Afficher »

La première commande de ce bloc, « Afficher ce nombre de documents récents » nous permet enfin de dépasser la limite antérieure qui était l’affichage – au maximum – des noms des neuf derniers classeurs utilisés.

Le bloc « Formules »

Ce bloc représente une autre nouveauté, avec sa commande unique
« Permet des calculs multithread » dont je ne peux pas vous dire grand-chose pour le moment…

Conclusion

Et voilà la fin de notre présentation – en trois articles – de la commande
« Options d’Excel ». Entre les possibilités offertes par la barre d’accès rapide – si bien entendu on la personnalise – et les réglages proposés par cette commande, on peut déjà bien contrôler son environnement de travail avec Excel 2007.

« Options Excel » (suite...)

Nous poursuivons ici l’article présenté il y a trois jours sur la commande
« Options Excel » du bouton Office.

La commande « Formules »

Une innovation notable, dans le bloc « Manipulation de formules », la présence de la commande « Utiliser les fonctions LIREDONNEESTABCROIS- DYNAMIQUE pour les références à des tableaux croisés dynamiques ».

Pour en savoir plus sur cette fonction – on se demande d’ailleurs pourquoi Microsoft utilise le terme « les fonctions » plutôt que « la fonction » – lisez mes articles des 6, 9 et 13 juillet 2006.

La commande « Vérification »

La commande « Vérification » ne concerne que la vérification orthographique et ne mérite donc pas de commentaire particulier.

La commande « Enregistrement »

La commande « Enregistrement » ne requiert pas de commentaire particulier. Notons cependant la présence du bloc « Options d’édition en mode hors connexion pour les fichiers du serveur de gestion de documents » : le travail collaboratif de vient de plus en plus d’actualité…

La commande « Options avancées »

Nous commenterons cette commande dans le prochain article. En effet, il y a plusieurs choses à dire à ce sujet…

La commande « Personnaliser »

On ne voit pas bien l’intérêt de cette commande étant donné qu’tant donné qu’elle ne sert à personnaliser que la barre d’accès rapide, ce que l’on peut faire directement en utilisant son propre bouton de personnalisation.

La commande « Compléments »

C’est ici que l’on gère les add-ins ou macros complémentaires, désormais appelés « compléments Excel » (pour ajouter à la confusion ?) ainsi que diverses DLL. Nous en reparlerons dans un article ultérieur.

La commande « Centre de gestion de la confidentialité »

Nous vous laissons pour le moment découvrir cette commande par vous-même. Je ne sais pas encore si j’aurai l’occasion de vous en parler plus en détail.

La commande « Ressources »

Cette commande vous donne accès à des liens Internet où vous trouverez diverses ressources Microsoft censées vous aider à tirer un meilleur profit de votre usage d’Office :

La commande « Options Excel »

Pour l’habitué des versions antérieures d’Excel, la disparition dans Excel 2007 de la commande Outils Options ne simplifie pas la vie, même si cette commande – cf. mon article « La commande Outils Options, la poubelle d’Excel » du 22 février 2006 – était devenue un peu « foireuse » dans les versions postérieures à Excel 2000.

Heureusement, un certain nombre des réglages de cette commande sont accessibles – dans Excel 2007 – via le bouton Office, par la commande
« Options Excel ». Voici ce qui apparaît quand on active cette commande :

Remarque 1 – La troisième case à cocher permet – comme nous l’avons indiqué dans l’article du 21 novembre 2007, « La barre d’outils "Formulaires" d’Excel 2007 » – d’afficher l’onglet Développeur, bien utile pour l’utilisateur avancé d’Excel.

Remarque 2 – Je ne vois pas bien l’intérêt de préserver l’ancien réglage par défaut attribuant trois feuilles à chaque nouveau classeur. Pour ma part, déjà avec les versions antérieures, j’ai toujours préféré créer un classeur avec une seule feuille, quitte à en ajouter d’autres par la suite si nécessaire.

Remarque 3 – Toutes les commandes suivies du logo Information i affichent des explications dès que le pointeur de la souris survole la commande : merci Microsoft, voilà une fonctionnalité utile !

Dans mes prochains articles, je vous indiquerai les réglages les plus intéressants des autres commandes de la colonne de gauche : Formules, Vérification,…

Créer une animation pour le chien

Aujourd’hui, nous allons repartir du modèle précédent, dans lequel nous avions dessiné un chien en utilisant un graphe en nuage de points (donc un graphe X/Y) lissé.

Nous souhaitons mettre en place une barre de défilement qui, quand on déplace son curseur, modifie la forme de la gueule du chien ainsi que la position de sa queue.

Pour cela, affichez la barre d’outils Formulaires (si vous avez Excel 2007, lisez mon message du 21 novembre 2007), cliquez sur l’outil « Barre de défilement », tracez une barre puis liez-la à la cellule A26 – la première cellule vide de la colonne A – en gardant le réglage par défaut de la barre, c’est-à-dire les valeurs de 0 à 100.

Remplacez alors la valeur 1 de la cellule A5 (l’abscisse de la gueule du chien) par la formule =A26/100 et la valeur 13 de la cellule A16 (l’abscisse du haut de la queue du chien) par la formule =9+A26/25.

Désormais, quand vous déplacez le curseur de la barre de défilement, vous modifiez de façon continue la forme de la gueule et la position de la queue du chien entre les deux positions extrêmes ci-dessus. Ce n’est pas plus difficile que cela de faire du dessin animé dans Excel…

Amélioration du look du chien…

Il y a quatre jours, nous avons dessiné un chien. Il est vrai que ce dessin ressemblait un peu à un dessin d’enfant.

Pour améliorer ce dessin, il suffit tout simplement de modifier le type du graphique, en prenant l’option « dessin lissé ». La copie d’écran ci-dessous montre à quoi ressemblent chacun des deux dessins avec – à droite – le choix de l’option graphique :

Remarque 1 – Dans le même graphique, il est possible de mélanger des séries représentées sans lissage et d’autres avec lissage. Vous pouvez ainsi représenter un personnage (avec lissage) dans un cadre représentant une pièce et du mobilier (sans lissage).

Remarque 2 – Bien entendu, ce lissage s'applique aussi aux séries numériques normales : vous pouvez ainsi lisser vos graphiques X/Y professionnels.

Avec ces graphiques lissés, votre créativité sera encore mieux stimulée !

Dessiner une image point par point

Pour tracer une image point par point, il suffit de reprendre en colonnes A et B les coordonnées (x,y) de chaque point, de sélectionner cet ensemble de points, puis de demander un graphique en nuage de points. Voici ci-dessous le dessin – un peu schématique certes – d’un chien, avec en bas de l’image la copie d’écran de la sélection du type de graphique :

Nous avons sélectionné le tracé d’un graphique en nuage de points avec des segments de droite et sans marquage de points.

Ainsi, le début de la première patte du chien correspond au point x=4, y=0. Le haut de sa patte se situe en x=4 et y=3. Le bas de sa gueule est en x=2 et y=5. Et ainsi de suite…

Il ne vous reste plus qu’à dessiner en vous aidant si nécessaire d’une feuille de papier quadrillé transparent pour repérer les coordonnées. Envoyez-moi vos dessins, je publierai les meilleurs !

Restaurer la barre d’accès rapide

La barre d’accès rapide d’Excel est enregistrée sur votre disque C: avec le suffixe .qat pour Quick Access Toolbar.

Si vous désirez mémoriser cette barre, il suffit de lui donner un autre nom. Le jour où vous souhaiterez restaurer cette barre, il vous suffira de la renommer pour lui donner le nom de la barre d’Excel.

Sur mon PC équipé de Vista, la barre que j’avais personnalisée se nomme par défaut Excel.qat. Dès que je l’ai renommée autrement puis redémarré Excel, Excel a récupéré sa barre d’accès rapide par défaut.

Dès que j’ai détruit ce fichier – après avoir fermé Excel, bien entendu – puis renommé mon fichier en Excel.qat et relancé Excel, j’ai bien récupéré la barre que j’avais personnalisée.

Emplacement du fichier de la barre d’accès rapide

Selon un site que j’ai consulté, voici les emplacements théoriques de ce fichier de barre d’accès rapide…

Dans Windows XP :
\Local Settings\Application Data\Microsoft\Office C:\Documents et settings\nom d’utilisateur

Dans Windows Vista :
AppData\Local\Microsoft\Office C:\Users\nom d’utilisateur

Reste à franciser éventuellement et à vérifier si c’est bien exact…

Sur mon Vista, j’ai eu du mal à le trouver : il m’a fallu faire une recherche avancée en cherchant un fichier de nom *.qat avec « Partout » comme emplacement (cf. écran ci-dessous). Le chemin affiché comme résultat est Office (C:\Utilisateurs\Hervé\AppData\Local\Microsoft).

Si par exemple on ne sélectionne pas « Tous » dans la rubrique « N’afficher que : » , on ne trouve pas le fichier…

La barre d’accès rapide

Un des plus gros changements, pour l’amateur éclairé qui passe d’Excel 2003 à Excel 2007 est la perte des deux barres d’outils qu’il avait aisément personnalisées pour rendre son Excel plus performant. Ceci, ajouté à la disparition de ses menus habituels, fait que de nombreuses commandes qui étaient auparavant en accès immédiat (par un bouton) ou rapide (par une commande de menu) ne sont plus aussi rapidement accessibles.

La clef, pour résoudre ce problème, est de personnaliser la barre d’outils
« Accès rapide », en haut de l’écran. En effet, je trouve pour ma part totalement inefficace de devoir passer par le bouton Office pour avoir accès à de nombreuses commandes qui étaient – et devraient toujours être – directement accessibles par un bouton.

Cliquez donc dans le bouton à droite de la barre d’outils « Accès rapide », le bouton « Personnaliser la barre d’outils Accès rapide », sélectionnez « Autres commandes … », puis dans le premier menu déroulant, sélectionnez l'option
« Toutes les commandes ».

Il ne vous reste alors plus qu’à ajouter toutes les commandes qui vous intéressent. Dans ce premier exemple, je me suis contenté de mettre les outils de base d’ouverture, aperçu, impression (avec le dialogue), enregistrement et – pour m’amuser – l’appareil photo.

On peut aisément, dans ce dialogue de personnalisation, modifier l’ordre des boutons. Voici le résultat avec le dialogue de personnalisation ouvert :

Remarque 1 – Avec mes petits yeux fatigués, je regrette que l’on ne dispose pas d’une commande permettant d’afficher les icônes de la barre d’outils
« Accès rapide » dans une plus grande taille.

Remarque 2 – Il ne semble hélas pas y avoir la possibilité – comme nous l’avions sous 2003 – d’insérer de petites barres de séparation verticale entre les familles d’outils.

Remarque 3 – Quand je me serai bien rôdé à Excel 2007, j’aurai certainement ajouté d’autres outils à cette barre. Je vous montrerai alors le résultat de ce travail.

Mes débuts avec Excel 2007…

En règle générale, je ne me presse jamais pour utiliser la toute nouvelle version d’un logiciel grand public, laissant à d’autres la tâche délicate, et ingrate, de découvrir pour le plus grand bénéfice de son éditeur – qui aurait dû mieux faire son travail en amont – une partie des nombreux bugs encore présents dans cette version. Pour les mêmes raisons, la plupart de mes clients – dans mon activité de conseil – en font autant.

C’est bien pourquoi, à quelques rares exceptions près, je n’avais encore jamais travaillé avec Excel 2007 avant la semaine dernière où – pour la première fois – un de mes clients a exprimé sa volonté expresse d’utiliser Office 2007.

Certes, cette dernière année, j’ai eu – dans mes cours à HEC – certains élèves (jamais plus de 15% dans un groupe) piégés avec Excel 2007 qui n’arrivaient souvent pas – avec cette version – à reproduire ce que je leur montrais avec Excel 2003. Parfois, je parvenais à trouver la solution en temps réel, parfois il me fallait attendre la séance suivante pour la trouver et la leur montrer…

Donc, là, le pas est franchi. Je sais à présent utiliser Excel 2007 et vais vous faire profiter de mes remarques et suggestions à cet égard. Attendez-vous par conséquent à un certain nombre de livraisons, dans ce blog, relatives à la version 2007 et à Vista puisque, tant qu’à faire, j’ai à présent les deux à la fois sur un nouveau portable que je me suis acheté, afin de pouvoir quand même continuer à travailler « normalement », sur mon ancien portable, en Excel 2003.

En effet, force a été de constater que la cohabitation – sur le même PC– d’Office 2003 et Office 2007 ne se passe pas bien, sauf à créer – ce que je ne pouvais pas faire pour diverses raisons – deux boots (systèmes de démarrage) différents.

Message à l’adresse de Microsoft – Cela serait si sympa si un jour vous nous permettiez – comme cela a été le cas dans le temps – de faire coexister deux versions d’Excel (par exemple 2003 et 2007, ou française et anglaise) sur le même système d’exploitation.

Explosion depuis une base

Le 5 octobre 2006, je vous ai présenté un add-in appelé « Data explosion » dont une des fonctionnalités était de créer rapidement une série de feuilles, chacune d’entre elles étant relative à une modalité d’un champ donné dans une base de données.

Cette fonctionnalité peut être utilisée en direct dans Excel, sans cet add-in, à partir du moment où l’on dispose d’un tableau croisé dynamique (TCD) relatif à la base.

Prenons comme exemple la base ci-dessous dans laquelle, par souci de compacité, j’ai collé l’image d’un TCD créé dans une autre feuille, avec l’âge et le sexe en critère de page.

Pour effectuer l’éclatement de la base selon l’un de ces deux critères de page, il suffit de mettre le curseur dans le TCD (pour l’activer), de cliquer dans l’outil « Tableau croisé dynamique » de la barre d’outils de même nom et de sélectionner la dernière commande, très mal nommée d’ailleurs :
« Afficher les pages… ». Choisissez le critère de page selon lequel vous souhaitez obtenir une explosion, puis validez.

Aussitôt, Excel crée une feuille pour chacune des modalités du critère de page retenu avec, dans chacune de ces feuilles, le TCD correspondant à cette modalité.

La même chose, sans VBA !

Pour ceux d’entre vous qui sont allergiques au VBA, nous allons voir aujourd’hui comment obtenir le même résultat que dans le message du 12 juillet – c’est-à-dire la mise en relief de toutes les cellules contenant une formule –, sans recours au VBA.

Sans recours au VBA, certes, mais pas sans recours à la programmation. La solution que je vous propose utilise en effet le langage macro d’Excel 4 – ce qui nous ramène à l’année 1992 ! Cette solution est un peu plus compliquée, mais c’est le prix à payer pour se passer du VBA…

Voici – toujours avec C15 comme cellule active – l’écran définissant le format conditionnel de la cellule ainsi que la définition du nom utilisé dans cette formule :

La fonction Lire.Cellule(41;adresse) du langage macro Excel 4 récupère la formule utilisée comme définition de la cellule logée en adresse. Le nom
« Formule » identifie donc le premier caractère de la formule de C15. Quand ce premier caractère est un signe « = », cela signifie que nous avons affaire à une formule. CQFD !

Remarque 1 – Notez que la définition du nom est relative : il est formulé en fonction de « C15 » et non de « $C$15 », grâce à quoi il fera toujours référence à la formule de la cellule active.

Remarque 2 – Nous aurions aussi pu définir le nom avec =lire.cellule(41;Feuil1!C15) et mettre =gauche(Form;1)="=" comme formule pour le format conditionnel.

Un format pour les formules

Peut-on utiliser un format conditionnel pour identifier d’un coup d’œil dans une feuille toutes les cellules contenant des formules ?

Certes, on peut déjà sélectionner d’un coup toutes les cellules contenant une formule grâce à la commande Edition – Atteindre – Cellules – Formules. Il est même alors possible, parmi les cellules contenant des formules, de ne garder que celles contenant des nombres, des valeurs logiques, des textes et/ou des erreurs…

Mais, pour mettre en relief de façon durable toutes les cellules d’une feuille qui contiennent une formule, il faut utiliser un format conditionnel. Voici la macro utilisée par ce format ainsi que le format lui-même, la cellule active étant la cellule C15 :

Remarque – Les parenthèses après « VRAI » sont facultatives. Je ne les ai mises que pour mettre en relief l’existence de la fonction vrai().

Date et heure d’après du texte

Un lecteur m'a posé le problème suivant : il a en B1 une date écrite sous forme de texte et en B2 une heure écrite elle aussi sous forme de texte. Ces deux éléments proviennent d’imports effectués depuis des bases de données externes. Il souhaite obtenir en B3 la date et l’heure actuelle, sous forme numérique, afin de pouvoir ensuite effectuer des opérations dans lesquelles cette date interviendrait.


La formule de B3 est la suivante : =date(droite(B1;4);stxt(B1;4;2);gauche(B1;2))+temps(gauche(B2;2);stxt(B2;4;2);droite(B2;2))

Remarque 1 – On voit bien que les cellules B1 et B2 contiennent du texte car il est affiché à gauche de la cellule.

Remarque 2 – On a le droit faire la somme des deux fonctions : en effet, la fonction date() renvoie un jour (donc une valeur entière) et la partie heure une partie décimale uniquement.

Remarque 3 – La cellule B3 peut bien entendu être formatée comme vous le souhaitez…

Remarque 4 – Pour en savoir plus sur les dates dans Excel, ne manquez pas
« La grand-mère de Bill Gates », du 14 décembre 2005. Pour en savoir plus sur les fonctions de date et heure, lisez mes messages du 13 et du 17 août 2007.

Lien hypertexte dans le dossier

Nous avons vu comment effectuer la « Création d’un lien hypertexte stable » dans notre message du 28 novembre 2006, à l’aide de la fonction lien_ hypertexte().

Parfois, on souhaite créer un lien hypertexte vers un classeur qui ne sera pas dans un dossier précis du disque dur, mais uniquement dans le dossier actif.

L’avantage est que, quand on déplacera à la fois le classeur « appelant » et le classeur « appelé », le lien hypertexte fonctionnera immédiatement, sans que la moindre manipulation ne se révèle nécessaire.

Voici un exemple, la formule de la cellule A1 étant reproduite dans son commentaire :


Pour vous aider à mieux comprendre cette formule, nous avons entré en A3 la formule =cellule("filename"). Cette fonction a été présentée en détail dans notre message du 14 décembre 2006.

Remarque – N’oubliez pas d’ajouter le second argument « $B$1 » à la fonction cellule() si vous voulez être sûr qu’il s’agit bien du dossier du classeur actif et non du dossier où se trouve le classeur dont on vient de modifier une cellule !

Insertion automatique de lignes

Une lectrice m’a demandé récemment comment, dans un tableau tel que ci-dessous – mais qui en réalité comporte des centaines de lignes représentant des mesures automatiques – insérer automatiquement une ligne chaque fois qu’il manque une mesure, celles-ci devant normalement être prises toutes les 15 minutes :

Bien entendu, la seule façon d’assurer une telle tâche est de passer par une macro. Voici la macro que j’ai écrite à cet effet, ainsi que son résultat :