Monsieur Excel
Pour tout savoir faire sur Excel !

29 juillet 2011

De nouveaux livres sur Excel

Cela fait quelque temps que je ne vous ai pas présenté d’ouvrage sur Excel. En voici trois qui vous donneront l’occasion de mieux maîtriser Excel…

Applications financières sous Excel en Visual Basic

Cet ouvrage de Fabrice Riva, chez Economica, au prix de 23 €, en est à sa troisième édition, c’est dire qu’il rencontre un certain succès. Cela est dû aussi probablement à ce qu’il sert de base au Master 104 – Finance – de Dauphine.

Une première partie de 66 pages introduit le lecteur à l’utilisation du VBA Excel. La seconde partie, comportant 232 pages, s’intéresse aux applications financières, avec les chapitres suivants : Propriétés des taux de rentabilité, Gestion de portefeuille (deux chapitres), Efficience des marchés, et enfin Evaluation d’options.

Cet ouvrage n’est pas pour les touristes, il contient sa dose de formules financières et, comme le titre l’indique, de code VBA. Mais c’est un solide livre de support de cours…

On peut récupérer sur Internet les fichiers relatifs au livre. Il est regrettable à cet égard qu’il faille les récupérer un par un au lieu de télécharger un fichier compressé les comprenant tous.

VBA Excel 2010 – Programmer sous Excel : Macros et Langage VBA

Cet ouvrage de Michèle Amelot, chez ENI Editions, a déjà été présenté dans ce blog le 22 mai 2010. Si j’en parle ici, c’est qu’il est à présent vendu en coffret à 53,90 € avec l’ouvrage suivant.

Excel 2010 – Macros et Programmation en VBA

Cet ouvrage de Pierre Rigollet, chez ENI Editions, au prix de 22,80 € est un recueil de 84 exercices VBA avec corrigés. Tous les fichiers peuvent être téléchargés depuis le site d’ENI Editions. Au début de chaque chapitre, les fonctions ou instructions macros utilisées dans ce chapitre sont indiquées dans un tableau récapitulatif.

Les thèmes traités dans les différents chapitres sont les suivants : Macros enregistrées, Fonctions personnalisées, Structures conditionnelles, Boucles, Saisie de données et affichage de résultats, Travaux sur plages, Travaux sur feuilles et classeurs, Procédures événementielles, UserForms.

25 juillet 2011

Name Manager pour 2007/2010

Name Manager est un add-in – ou complément, pour parler Excel 2007 – vraiment génial, créé il y a longtemps déjà par Jan Karel Pierterse, un autre MVP Excel, pour combler les nombreuses lacunes d’Excel en matière de gestion de noms.

Si vous utilisez une version d’Excel antérieure à 2007, lisez donc les deux articles que j’ai publiés le 21 et le 25 mai 2006. Dans cet article, je vous présente la version de Name Manager compatible avec Excel 2007 et 2010.

Nous constatons en premier lieu dans la fenêtre ci-dessous que la définition, ou la modification, des noms faisant référence à de multiples blocs ne pose plus de problème : la grande fenêtre en bas de l’écran nous permet de consulter l’ensemble de la définition de Bloc_1, ce qui était encore impossible avec Excel 2007.

Nous notons dans la partie droite de l’écran, que les possibilités de sélection des noms sont beaucoup plus riches que celles offertes par le bouton « Filtrer » du gestionnaire de noms d’Excel 2007.

A cela il faut ajouter toutes les possibilités offertes par les nombreux boutons placés en haut de la fenêtre, pour respectivement : masquer, ajouter, détruire, créer une liste, rendre des noms globaux, évaluer un nom (les lunettes), analyser, surligner, dé-surligner, vérifier l’usage des noms, rafraîchir la liste (!), aller au nom actif, revenir au nom précédent, …

Une mine d’or à explorer ! En plus, cet add-in est gratuit, et – en remerciement pour l’auteur – j’en ai fait gratuitement la traduction en français.

Cerise sur le gâteau, cet add-in fait quelque chose que je n’ai vu sur aucun autre add-in, il vous apprend les langues : en effet, le bouton « Auto », en bas à droite, vous permet de choisir en temps réel n’importe laquelle parmi sept langues possibles pour Name Manager.

21 juillet 2011

Utiliser les noms dans les formules

Prenons l’exemple d’un petit modèle de calcul de profit en fonction du prix de vente représenté par le tableau suivant :

Vous pourrez analyser ce modèle dans la partie haute de l’image suivante, qui montre les formules utilisées. Vous pouvez vous dire que la formule de B13 est bien compliquée. Il y a une raison à cela. Si l’on avait choisi des formules plus simples, il y aurait une formule simple en B13 et une autre, simple aussi, en C13 que l’on aurait ensuite recopiée à droite.

L’avantage de notre formule plus compliquée en B13 est qu’il suffit de recopier tout le bloc B12:B18 vers la droite pour obtenir les quatre colonnes suivantes.

Nous allons maintenant transformer ce modèle courant en modèle sensiblement amélioré.

Définition des noms

La première étape consiste à donner des noms explicites aux différents éléments du modèle. Pour cela, sélectionnons le bloc A2:B9 et en maintenant la touche [Ctrl] enfoncée le bloc B13:F17, puis utilisons la commande « Créer à partir de la sélection ». Nous constatons avec plaisir que seule « Colonne de gauche » est cochée ; c’est parfait, validons par « OK ».

Il suffit de dérouler le menu « Nom », à gauche de la barre de formule pour constater que tout s’est très bien passé : les cellules B3 à B9 sont nommées à partir du contenu de la colonne A dans la même ligne et les blocs horizontaux de B13:F17 sont aussi nommés à partir du contenu de la colonne A.

Affectation des noms

La seconde étape est simple : utilisez la commande « Définir un nom … Appliquer les noms » et validez sans rien toucher…

Et le miracle, comme vous pouvez le constater dans la seconde image ci-dessous : les formules antérieures, peu conviviales car constituées uniquement d’adresses de cellules, ont été remplacées par des formules totalement explicites, utilisant des noms réellement descriptifs !

17 juillet 2011

Le gestionnaire des noms

Jusqu’à la version 2003 incluse, la gestion des noms dans Excel était très mal conçue. Dès que la zone à laquelle le nom faisait référence était longue, il était très difficile, parfois même quasiment impossible, de la consulter ou de la modifier.

Il y a un léger progrès à cet égard dans Excel 2007. Comme nous le voyons dans le coin inférieur droit de la copie d’écran ci-dessous, la fenêtre du dialogue peut être agrandie. Quand le nom est long, comme dans le cas de Bloc_1 ci-dessous, on peut alors donner toute la largeur de l’écran au dialogue – ce qui n’était pas possible auparavant – afin de mieux identifier la zone référencée par le nom.

Remarque 1 – Tant qu’à faire, on se demande pourquoi nos amis de Microsoft n’ont pas pensé à donner à l’utilisateur la possibilité d’agrandir vers le bas la zone « Fait référence à : ». En effet, quand le nom est très long, comme c’est ici le cas, même la largeur de l’écran, si grand soit-il, est totalement insuffisante pour afficher toute sa définition.

Le bouton « Filtrer » représente la plus grande nouveauté de ce dialogue. Il sert à réduire la liste des noms en ne sélectionnant que les noms ayant telle ou telle caractéristique. On a le droit de cocher une option par bloc. On peut ainsi par exemple n’afficher que les noms sans erreur (5ème option) et définis (6ème option).

Remarque 2 – Encore une fois, je ne suis pas satisfait du tout des termes utilisés par Microsoft. Ainsi, dans le second bloc, je préfèrerais « Noms relatifs à une feuille » ou « Noms locaux » à « Noms inclus dans l’étendue de la feuille de données », qui ne veut pas dire grand-chose. C’est quoi donc une feuille de données ? Cela exclue-t-il les feuilles de résultat ? De même, le terme « Noms définis » n’est pas clair non plus, les noms étant bien évidemment tous définis (sinon ils ne seraient pas là !)

13 juillet 2011

La commande « Noms définis »

Dès que l’on veut travailler avec les noms dans les versions Excel 2007 et ultérieures, on est amené à utiliser le bloc « Noms définis » de l’onglet
« Formules ». Ce bloc comporte quatre commandes principales.

Le « Gestionnaire de noms » est un outil très puissant, sans aucune comparaison avec ce qu’il était possible de réaliser avec les versions antérieures d’Excel. En fait, il comporte de nombreuses fonctionnalités inspirées par l’excellent add-in « Name Manager, présenté en détail dans les articles du 21 et du 25 mai 2006. Nous analyserons le gestionnaire de noms dans notre prochain article.

La commande « Définir un nom » sert, comme son nom l’indique, à définir un nom. Ce qui peut aussi se faire de façon bien plus directe, comme nous l’avons vu dans l’article « Définition d’un nom » du 19 juin dernier. En revanche, pour définir un nom élastique (article du 1er juillet), ou un nom local (article du 5 juillet), ou un nom défini par une formule – comme c’était le cas pour le nom élastique –, on est obligé de passer par elle.

Remarque – Cachée derrière la commande « Définir un nom », vous trouverez aussi la commande « Appliquer les noms » dont nous parlerons ultérieurement. Pour ma part, je ne vois pas bien l’intérêt de la cacher là : il m’a fallu bien du temps pour la trouver la première fois que je l’ai cherchée dans Excel 2007 !

La commande « Utiliser dans la formule » affiche la liste des noms et, en bas, la commande « Coller des noms » dont je ne vois pas l’utilité puisqu’il suffit de sélectionner un des noms de la liste pour le coller. En outre, pourquoi appeler ainsi cette commande alors que « Coller un nom » serait bien plus approprié puisqu’on ne peut en sélectionner qu’un à la fois. En conclusion, une commande à la fois totalement inutile et en outre très mal nommée !

L'aide d'Excel , que j'ai consultée en entrant « Utiliser dans la formule », pour voir si je n'aurais pas par hasard loupé une pépite, n'a rien trouvé à ce sujet précis...

La commande « Créer à partir de la sélection », enfin, dont nous parlerons aussi ultérieurement, sert à créer en rafale toute une série de noms.

09 juillet 2011

Un nom élastique pour une liste ?

Dans mon article du 1er juillet, « Définition d’un nom élastique », j’expliquais qu’un nom élastique pouvait servir – entre autres – à créer une liste de validation dynamique.

Avant d’aller plus loin dans cet article, je vous propose de lire – éventuellement – l’article en question, mais – surtout ne les loupez pas ! – de lire les trois premiers commentaires qui en ont été inspirés.

En suivant les instructions de cduigou, j’ai créé deux tableaux Excel 2007 pour engendrer ensuite une liste de validation pour les pays et une autre pour les départements.

Certes, cela fonctionne bien, les deux listes sont effectivement élastiques, et ce sans la formule dont j’avais besoin pour définir le nom élastique.

Mais le prix à payer pour cette « simplicité » est cher ! Les tableaux d’Excel 2007 ont ceci de détestable qu’ils reviennent grosso modo à fusionner les cellules qui les composent et que votre tableau préféré perd alors sa qualité fondamentale et essentielle, l’indépendance des cellules.

Vous le voyez dans l’image ci-dessous : si l’on sélectionne les lignes 4 et 5 pour insérer deux lignes au-dessus de la ligne 4, on constate que l’insertion n’est plus possible !

Si les tableaux avaient été l’un au-dessus de l’autre, ce problème n’existerait pas… Cela est dû au fait que, s’il y a au moins deux tableaux partageant des lignes, on ne peut plus insérer de ligne dans la partie « commune ». De même, on ne peut plus insérer de colonne là ou au moins deux tableaux Excel 2007 ont des colonnes en commun. Les tableaux Excel 2007 sont à proscrire !

Remarque – Les spécialistes des « bases de données » vous diront que cela est utile pour garantir l'intégrité des données. Cela, je veux bien le comprendre, mais pourquoi alors n'avoir mis dans Excel 2007 que les tableaux pour remplacer le format automatique d'Excel 2003 ?

Pour terminer, je vous conseille de lire aussi l’article « Convertir en plage, en VBA 2007 » du 26 octobre 2009.

05 juillet 2011

Définition d’un nom « local »

Par défaut, un nom dans Excel fait référence à des coordonnées données dans une feuille donnée du classeur actif : ainsi, le nom « Toto » qui aurait été défini par =Elastique!$B$3 fait référence à la cellule B3 de la feuille « Elastique ». Un nom est par défaut « global » ; il s’agit d’une propriété du classeur.

En fait, rien ne vous oblige à définir un nom de cette façon. Si vous remplacez la référence du nom de =Elastique!$B$3 à =!$B$3, le nom devient « local », donc relatif à la feuille active. Si vous êtes dans une cellule quelconque d’une feuille quelconque, l’appel du nom (par [F5] ou [Ctrl]-t) vous amène alors à la cellule B3 de cette même feuille. Quand vous passez à une autre feuille, il vous amène à la cellule B3 de cette autre feuille.

Remarque 1 – Une des particularités des noms locaux est que – à l’instar des noms élastiques – on ne peut pas les atteindre directement à l’aide du menu déroulant de la zone « Nom ». En effet, ils ne sont pas présents dans la liste…

Pour atteindre un nom local ou un nom élastique, il faut donc utiliser [F5] ou [Ctrl]-t, puis taper le nom en question – puisqu’il n’apparaît pas dans la liste –, et enfin valider.

Un problème grave pour les noms locaux

J’ai constaté un problème grave qui survient parfois – mais pas toujours… – quand on utilise des noms locaux : le calcul par [F9] peut alors ne plus fonctionner normalement.

Si cela arrive, il faut utiliser une forme de calcul nettement plus « vigoureuse », soit [Ctrl]-[Alt]-[F9] au lieu de tout simplement [F9].

Remarque 2 – En VBA, c’est tout simplement la différence entre les deux commandes Application.CalculateFull et Application.Calculate !

01 juillet 2011

Définition d’un nom élastique

Dans cette série sur les noms, je ne pourrais pas oublier les noms élastiques ! Ils sont bien trop utiles…

Un nom élastique, c’est un nom qui s’adapte automatiquement au nombre de données présentes. Supposons par exemple que nous disposions en colonne D d’une liste de pays. Nous utilisons la commande « Validation » du menu ou de l’onglet « Données » pour définir une liste de validation en B2, comme on peut le voir dans la copie d’écran ci-dessous :

Cela fonctionne très bien mais la liste est figée aux sept pays du bloc D1:D7. Qu’arrive-t-il si nous souhaitons enrichir cette liste an ajoutant des pays ? Cela ne sera hélas pas répercuté dans le menu déroulant, sauf si l’on redéfinit l’adresse de la liste.

Pour éviter ce problème, il suffit de définir un nom dynamique identifiant les pays, à l’aide de la formule : =Elastique!$D$1:decaler(Elastique!$D$1;nbval(Elastique!$D:$D)-1;0) où « Elastique » est le nom de la feuille. Après avoir défini ce nom élastique, il ne vous reste plus qu’à remplacer dans la définition de la liste de validation =$D$1:$D$7 par =pays.

Remarque – Si vous oubliez le signe « = », la liste de validation ne vous donnera que « pays » comme possibilité !

Et voilà ! Le tour est joué ! Ajoutez deux ou trois pays, déroulez le menu en B1, et constatez que les nouveaux pays sont automatiquement intégrés à la liste…