Monsieur Excel

Internationalisation d’un modèle

2012-01-26T09:29:00.004+01:00

Comme nous l’avons vu il y a quatre jours avec nos calculs de ratios, certaines formules – en particulier celles qui utilisent des arguments texte – peuvent ne plus fonctionner normalement quand on passe d’un Excel en français à un Excel en anglais, ou vice versa.

J’ai souvent été confronté – lors de mes créations de modèles pour des multinationales – à de tels problèmes d’origine linguistique, ou à d’autres encore pour des clients dont le parc était constitué à la fois de PC sous Windows et de Macs. En effet, le passage d’un modèle sous Windows à un modèle sous Mac, ou vice versa, n’est pas une opération neutre et peut aboutir à des dysfonctionnements.

Il y a donc un certain nombre de précautions à prendre si l’on veut que le même modèle Excel marche tout aussi bien sous Windows que sous Mac, ou en français comme en anglais,…

Nous allons voir aujourd’hui comment remplacer la formule de la cellule C2 – dans le modèle présenté il y a quatre jours – par une formule qui fonctionne indifféremment avec un Excel en français ou en anglais.

Nous voyons en D1 le contenu de la formule en C1. Cette formule utilise le nom « Langue », dont nous voyons la définition dans la fenêtre « Modifier le nom ».

L’astuce repose dans l’évaluation de 1*"0.0". On a en effet, depuis une vingtaine d’années le droit dans Excel de multiplier un nombre par un texte représentant un nombre. Dans les premières versions d’Excel, il fallait utiliser 1*cnum("0.0")…

Remarque – Il est aisé de vérifier le bon fonctionnement de cette formule. Il suffit pour cela de passer dans Windows par le panneau de configuration, de lancer le programme « Options régionales et linguistiques », et de remplacer la virgule de décimalisation par le point. Vous vous retrouvez dans ce cas avec un Excel « comme en anglais », et la cellule C2 affiche alors « 0.5 :1 » !

Divers calculs de ratios...

2012-01-22T09:16:00.006+01:00

Dans le monde anglo-saxon, il est fréquent de représenter des ratios sous la forme a:b, quitte à arrondir un peu les valeurs a et b quand les deux valeurs de départ ne sont pas réellement dans un ratio simple. Là où un français, avec la culture probabiliste héritée de ses illustres ancêtres statisticiens, dira qu’il y a 75% de chances qu’un événement survienne, l’anglo-saxon parlera d’un ratio de 3:1…

C’est là toute la différence entre la théorisation de la probabilité, en France, et la maîtrise des paris des bookmakers, en Angleterre…

Les valeurs en B1 et B2, dans la copie d’écran ci-dessous, ne sont pas dans un ratio simple, mais on peut aboutir à un ratio arrondi qui n’est pas trop éloigné de la valeur réelle.

Nous allons commenter ci-dessous ce qui a été obtenu dans le bloc C1:C6…

C1 et C2 aboutissent exactement au même résultat. Dans le cas de C1, la formule est assez simple, mais il n’y a pas de format particulier. En revanche, le résultat n’est pas numérique, ce qui peut poser un problème si le résultat de cette cellule doit être utilisé dans une formule. Dans le cas de C2, la formule est simple et le résultat est numérique : c’est le format personnalisé 0,0":1" qui fait le travail à lui tout seul...

Les formules de C3, C4 et C5 montrent comment on peut spécifier le nombre de décimales du dénominateur. Excel arrondit alors de façon à trouver le résultat le plus proche.

On peut aussi, comme en C6 – et cela, peu d’utilisateurs d’Excel le savent – spécifier précisément le nombre à utiliser pour le dénominateur. Excel trouve alors le numérateur le plus proche.

Enfin, en utilisant la fonction PGCD() en C9, on trouve le ratio exact qui correspond aux simplifications possibles entre le numérateur et le dénominateur. Dans notre exemple en C9, si l’une ou l’autre des deux valeurs était modifiée d’une seule unité, aucune simplification ne serait possible !

Remarque – Dans le cas de la formule en C1, le résultat ne sera pas adapté si vous ouvrez ce fichier avec un Excel en anglais. En effet, il faudrait dans ce cas utiliser le format 0.0 à la place du format 0,0 !

Afficher la formule de la cellule

2012-01-18T08:32:00.005+01:00

Il est parfois utile – en particulier dans les applications pédagogiques – de reproduire, dans la cellule à droite d’une formule délicate, le texte de la formule.

La façon la plus simple d’atteindre cet objectif est d’utiliser les macros Excel 4 qui, contre toute attente et contrairement aux annonces de Microsoft en 1993, à la sortie d’Excel 5, continuent encore à fonctionner.

Reprenons le modèle publié dans l’article du 21 juillet 2011, en effaçant les colonnes C et suivantes pour simplifier la présentation.

Mettons-nous en C13 et définissons le nom « Formule » comme dans la fenêtre ci-dessous. Il suffit ensuite d’entrer la formule =formule dans la cellule C13 – et à la reproduire vers le bas – pour afficher en C13:C18 le contenu des formules de B13:B18.

Remarque 1 – Nous avons ôté de la définition du nom la référence à la feuille qui était venue automatiquement quand nous avons pointé sur la cellule à gauche. Nous avons aussi rendu cette référence relative. Grâce à ces deux opérations, la formule correspondra toujours à la cellule de gauche, quelle que soit la feuille active.

Remarque 2 – Si vous placez la définition de ce nom dans le classeur Perso.xls qui s’ouvre automatiquement au lancement d’Excel, vous pourrez utiliser ce nom dans n’importe quel classeur. Le seul problème est que, sur un autre ordinateur, la formule « =formule » engendrera alors une erreur #NOM ! car le nom ne sera plus reconnu...

Excellente année 2012 !

2012-01-14T11:29:00.009+01:00

Me voilà de retour après 15 jours en Inde où j’ai pu vivre le cyclone Thane : au moins 46 morts et quasiment la moitié des arbres de Pondichéry abattus. Pour en savoir plus : http://en.wikipedia.org/wiki/Cyclone_Thane

Ne partez pas en voyage avec moi ! J'étais aussi en Californie en 1989, lors du plus gros tremblement de terre depuis le fameux "earthquake" de 1906 : http://en.wikipedia.org/wiki/1989_Loma_Prieta_earthquake

Tout d’abord, mes meilleurs vœux, un peu tardifs pour l’année 2012 !

Une info : Microsoft m'a accordé le titre de MVP Excel 2012 :)
Je n'ai pas encore perdu mon troisième A...

Pour vous souhaiter une excellente année 2012, je reprends le modèle publié il y a deux ans. Il s'agit de rédiger un texte de vœux dont la somme des valeurs des caractères soit exactement de 2012. Vous en voyez le résultat affiché en A1 :

Nous avons reproduit en commentaire les formules de B1 – reproduite ensuite vers le bas – et de E4. La formule de E1 est la somme de la colonne B et celle de E3 est simplement =nbcar(A1).

Notez que la formule matricielle entrée en E4 permet d’obtenir directement le total 2012 des valeurs des caractères du texte en A1, sans avoir besoin de la colonne B. Encore un bel exemple de formule matricielle !

Remarque 1 – Je n’ai pas réussi hélas à me passer de la cellule E3 en remplaçant dans la formule matricielle le « 22 » par « nbcar(A1) ». Cela ne passe pas au niveau de la syntaxe…

Remarque 2 - La beauté de la chose, c'est que le texte de vœux ci-dessus peut être utilisé pendant 10 ans, en adaptant l'année de 2010 à 2019 : chaque fois, la somme des valeurs des caractères sera égale à la valeur de l'année !

Remarques sur l'audit de modèles

2011-12-26T11:26:00.008+01:00

Je vais être dans l’impossibilité d’alimenter ce blog durant 15 jours. A titre de compensation, je vous propose aujourd’hui un article sensiblement plus long que mon format habituel d’une à deux pages.

Pour que vous ne perdiez pas votre temps avec des redites, je vous propose de commencer par (re)lire les articles suivants puisque ce que nous allons voir aujourd’hui a trait à l’audit de modèles Excel, et que cela est lié aux soucis de taille (premier article), de re-engineering (second article) et de modélisation en général (troisième article).

« Attention à la taille de vos fichiers ! » du 23 octobre 2005

« Le re-engineering de vos modèles » du 3 mars 2008

« Réflexions générales sur la modélisation » du 28 octobre 2010

Comme nous venons – dans les deux derniers articles publiés sur ce blog – de passer en revue l’add-in Spreadsheet Detective, je vais vous parler aujourd’hui de l’audit/amélioration de modèles, en présentant trois expériences de conseil que j’ai vécues, la première en 2006, et les deux dernières durant les six derniers mois.

Un projet de 300 millions d’euros

Le plus grave problème d’audit auquel j’ai été confronté correspondait à un projet d’investissement de 300 millions d’euros, lancé en collaboration entre une grande banque française, une banque allemande et une banque italienne. Dans le cadre de ce projet, une vingtaine de projets industriels devaient être financés par le consortium.

Chacun des trois partenaires était actionnaire du projet global, mais pouvait aussi financer en plus telle ou telle partie de tel ou tel projet, ou apporter d’autres concours financiers. Un contrat de 70 pages spécifiait les différentes façons dont chacun des trois partenaires pouvait intervenir et le mode de rémunération lié à cette intervention.

Le classeur de base du projet comportait une cinquantaine d’onglets. Le problème était que – à force d’avoir été « tripoté » par de trop nombreuses personnes, il était devenu ingérable. Quand je l’ai reçu, il comportait des références circulaires non voulues, et de nombreux liens vers d’autres classeurs.

Ma mission – si je l’acceptais… – consistait à nettoyer tout cela afin d’avoir un classeur propre et facile à utiliser. Je devais en outre, et là cela dépassait de loin ce que l’on peut demander à un simple expert d’Excel, garantir que toutes les formules de tous les onglets étaient cohérentes avec toutes les stipulations des 70 pages de contrat.

C’est un projet qui a été mené en 2006, sur une durée de 3 mois, et dans lequel j’ai travaillé – pour la partie financière – en collaboration avec un de mes collègues d’HEC, le professeur de finance David Thesmar.

Une société de placement financier

J’ai commencé à travailler cet été avec une petite entreprise – moins de 10 personnes – spécialisée dans les placements financiers. Cette entreprise a une vingtaine d’agents en France qui vendent leurs produits à leurs propres clients ; les produits de son catalogue sont sélectionnés par mon client parmi des produits financiers et/ou d’assurance vendus par des compagnies financières et des sociétés d’assurance.

Il faut donc gérer la prise des commandes par les clients finaux : quel produit ils ont acheté, provenant de quel fournisseur, en passant par quel agent,…

Puis il faut gérer la facturation de tout cela, avec le calcul des commissions aux deux niveaux : la commission versée par le fournisseur du produit à mon client, la commission versée par le client à ses agents.

Quand je suis arrivé, il y avait un certain nombre de classeurs Excel qui étaient utilisés selon que la commande avait été passée avec tel ou tel agent, ou avec tel ou tel fournisseur. J’ai mis en place un classeur de saisie généralisé, et un outil de préparation de la facture.

Voici ce que dit mon client de cette intervention : « En cinq jours de travail de Monsieur Thiriez, nous avons divisé par cinq le temps de saisie d’une affaire. Dans l’étape du calcul des commissions et du contrôle, nous pouvons aujourd’hui nous passer des services du salarié que nous avions employé expressément pour cela. »

Certes, la plupart de mes clients sont de grosses entreprises car – assez logiquement – plus une entreprise est importante, plus je peux lui faire gagner de temps et d’argent. Mais il est intéressant de noter que, même pour une petite entreprise comme celle-ci, le rendement sur investissement de l’audit & amélioration des modèles a pu être extrêmement intéressant, avec un payback nettement inférieur à l’année, moins de six mois dans cet exemple précis.

Un cabinet d’avocats

Durant les six derniers mois, j’ai été appelé à assister un cabinet d’une trentaine d’avocats où il y a une trentaine de fichiers Excel de reporting, avec de multiples liens entre classeurs, et où la construction du rapport de fin d’année était devenue – de fil en aiguille, année après année – de plus en plus lourde. A telle enseigne qu’en décembre 2011, le rapport de l’année 2010 était encore loin d’être prêt !

Là, mon intervention a consisté principalement à rendre tous les classeurs plus flexibles, de façon que le passage d’une année à l’autre se révèle le plus léger et rapide possible. J’ai eu là l’occasion de réduire de façon très significative le nombre d’onglets de chaque classeur, avec des réductions parfois supérieures à 50%. Les graphes, qui étaient tous construits au rythme d’un ou plusieurs par an, ont été rendu flexibles, avec la possibilité de représenter en un seul graphe ce qui auparavant en demandait une dizaine.

A titre indicatif, il y avait dans un classeur un plan glissant sut 4 ans, avec une dizaine d’onglets : un onglet 2006-2009, un onglet 2005-2008, un onglet 2004-2007,… J’ai remplacé tout cela par un onglet unique où l’on indique l’année de départ et l’année de fin, et qui calcule le plan glissant sur toutes les années concernées. Résultat : un seul onglet, et en prime la possibilité nouvelle de consulter les plans sur un nombre variable d’années, pas forcément quatre.

Les simplifications ont été drastiques, et elles auraient même pu être plus significatives encore si je n’avais pas reçu pour instruction fondamentale, dès le départ, de changer le moins possible les habitudes de travail et processus antérieurs.

Dans ce cas, il s’est agi d’une intervention d’une quinzaine de jours, suite à laquelle le reporting est devenu à la fois beaucoup plus simple et nettement plus flexible.

En guise de conclusion...

Ces exemples montrent qu’il est possible – avec l’audit et l’amélioration de modèles existants – d’améliorer de façon très significative la qualité des modèles Excel.

Que cela peut même être très rentable pour une entreprise de petite taille (10 salariés dans le second exemple, moins de 50 dans le troisième).

Et enfin que, même avec des honoraires par journée correspondant à ceux de consultants seniors dans les meilleurs cabinets de conseil, on peut amortir très rapidement, le plus souvent en moins d’un an, l’investissement que ces améliorations requièrent.

Je vous souhaite à tous une Excellente Année 2012 !

Spreasheet Detective (suite et fin)

2011-12-23T12:45:00.003+01:00

Dans le dernier message, nous avons commencé à voir comment fonctionne l'add-in Spreadsheet Detective, mon outil préféré pour l’audit de modèles Excel. Voici son ruban complet en Excel 2007 :

Nous avons vu dans l’article précédent des copies d’écran des effets de la commande « Shade » et de la commande « Report ».

La commande « Annotate » crée – sur l’ensemble de la feuille active – des sortes d’annotations liées à chaque formule originale. C’est donc en quelque sorte un croisement entre ce que font les deux commandes « Shade » et « Report ». Pour ma part, je trouve cela moins lisible que les résultats de ces deux autres commandes.

La commande « One Cell » fait la même chose que la commande « Annotate », mais seulement pour la cellule active. Dans la copie d’écran ci-dessus, nous voyons ce qui se passe avec cette commande quand la cellule active est la cellule D7.

La commande « Map Report » crée une carte de la feuille active avec, pour chaque cellule dont la formule est recopiée depuis une autre cellule, un symbole permettant de voir si la recopie vient de la gauche, du haut, ou même d’ailleurs.

La commande « Workbook Report » crée une feuille de statistiques relatives à chaque feuille du classeur.

Notons encore la présence de commandes pour l’analyse de sensibilité, l’analyse des précédents et des successeurs, la comparaison de deux classeurs, et enfin la gestion des noms.

La gestion des noms est un domaine où Excel était très pauvre jusqu’à la version 2003 incluse. Depuis Excel 2007, il y a du progrès – relisez à ce titre l’article « Le gestionnaire des noms » du 17 juillet 2011 et les deux articles suivants – , mais on est encore loin de ce que l’add-in Name Manager permet de réaliser.

En conclusion, le Spreadsheet Detective est pour moi un utilitaire réellement utile…

Un add-in d’audit de modèle

2011-12-17T10:41:00.005+01:00

Une partie non négligeable de mon activité de consultant consiste à auditer des modèles existants afin d’atteindre un ou plusieurs des objectifs suivants :

● nettoyer et/ou réparer des modèles devenus ingérables ou erronés ;

● réduire la taille de modèles devenus énormes (plus de 20 Mo) ;

● accélérer le temps de calcul, parfois jusqu’à un facteur 100 ou plus ;

● rendre le modèle beaucoup plus flexible.

Pour en savoir plus sur ce que l’on peut gagner avec ce type d’audit, lisez l’article « Le re-engineering de vos modèles » du 3 mars 2008.

Voici des endroits où vous trouverez des add-ins d’audit :

Spreadsheet Detective : www.spreadsheetdetective.com/

RefTree Analyzer : www.jkp-ads.com/RefTreeAnalyser.asp

Reference Browser : www.mang.canterbury.ac.nz/people/jfraffen/sscom/refbrowser.htm

Pour faciliter l’audit de modèles, j’utilise pour ma part l’add-in « Spreadsheet Detective », que je vous avais présenté une première fois le 22 avril 2006. Nous allons voir aujourd’hui à quoi il ressemble dans sa version Excel 2007.

J’ai choisi un tableau où, dans trois blocs de cellules (D9:D10, D14:D16 et G6:G8), les formules avaient été détruites et remplacées par des valeurs. J’ai effacé tous les encadrements et toutes les couleurs de cellules pour mieux voir le résultat et j’ai lancé la commande « Shade ».

Voici le résultat : toutes les cellules ayant une formule originale ont reçu un quadrillage bleu, les formules recopiées depuis le haut ont des traits verticaux, et celles recopiées à droite (il n’y en a pas ici) ont des traits horizontaux. On constate ainsi qu’en I12, il y a une seconde formule originale pour la colonne I. En outre, chaque cellule avec une formule originale a reçu un commentaire explicitant la formule.

Ensuite, avec la commande « Report », j’ai créé un rapport détaillant toutes les formules originales de la feuille active. Les formules sont écrites en bleu, avec en vert l’identification de ce à quoi correspond chaque référence.

Faites un double clic sur cette image pour la voir à son échelle originale...

Nous venons de voir seulement deux des vingt et quelques commandes de ce logiciel. Nous verrons dans le prochain article certaines des autres commandes.

VBA Excel : liste de mots réservés

2011-12-12T18:55:00.004+01:00

La liste ci-dessous est une liste que j’ai construite de mots réservés du VBA Excel (cf. article précédent).

Dernière minute...

En fait, la liste complète est bien plus longue, comme vous pourrez le constater en lisant les deux commentaires ci-après.

En fouillant dans mes archives, j'ai découvert que j'avais déjà diffusé cette liste en 2002 avec le n°74 de la lettre d'Excel (que j'ai publiée de 1990 à 2005).

Cette liste est conséquente : elle contient 1.913 mots réservés soit – à peu de choses près – autant de mot que dans le vocabulaire d'un humain lambda...

Je ne peux donc pas la reproduire ici, mais vous savez à présent où la trouver...

Les mots réservés du VBA Excel

2011-12-08T10:32:00.004+01:00

Le langage VBA d’Excel comporte de nombreux mots réservés. Si on les utilise par erreur, on obtient dans le meilleur des cas une erreur de syntaxe. Là, ce n’est pas trop grave car on voit qu’il y a un problème et on peut y remédier :)

Il peut en revanche y avoir un problème quand cela ne provoque pas d’erreur de syntaxe et que la variable ne fait pas exactement ce que l’on en attend.

En me baladant sur Google dans l’espoir de trouver une liste de ces mots réservés, je n’ai quasiment trouvé que des gens déplorant que cette liste n’existe apparemment pas, en tout cas dans ce qui est diffusé par Microsoft ou par d’autres spécialistes Excel.

Comment s’aperçoit-on qu’un mot est réservé ?

Une bonne méthode consiste à taper ce mot dans une instruction. Si l’on aboutit à une erreur de syntaxe, il est clair qu’il est réservé.

Sinon, si la syntaxe passe mais que le mot se met à afficher une ou plusieurs majuscules, c’est aussi qu’il est réservé.

Nous le voyons dans les deux copies d'écran ci-dessous. Dans le premier cas,
« friend » engendre une erreur de syntaxe. Dans le second cas, l’instruction sera acceptée, mais elle affichera « Description = 3 », la majuscule indiquant que le mot était réservé.

Remarque – C’est pour cette raison que, chaque fois que je définis une variable, je m’arrange pour lui donner au moins une majuscule. Je peux ainsi vérifier, quand je l’utilise, que les bonnes majuscules apparaissent au bon endroit.

Dans le prochain article, je vous donnerai ma liste des mots réservés du VBA Excel.

Des graphes en waterfall gratuits !

2011-12-04T09:47:00.014+01:00

Les graphes en waterfall – aussi appelés bridges – sont régulièrement utilisés en finance. Ils servent à mettre en relief l’impact d’un certain nombre de recettes et de dépenses sur une situation donnée, les dépenses étant représentées en rouge et les recettes en vert.

Dans mon article du 15 août 2011, je vous ai montré comment construire un waterfall dans Excel. Tout cela prend quand même du temps à construire, même quand vous savez exactement ce que vous devez faire.

Dans les articles « UpSlide pour Excel » des 6 et 10 mai 2011, je vous ai montré comment un bouton de l’add-in UpSlide, créé par la société Finance 3.1, permettait de créer un waterfall en un seul clic.

Finance 3.1 vient de mettre au point un service gratuit de réalisation de waterfalls : vous entrez vos données sur le site www.waterfall-chart.com, vous entrez vos coordonnées et vous cliquez sur la flèche de validation. Voici ce que j’ai entré dans la zone de saisie à gauche :

Voici enfin le graphe obtenu sur un fichier Excel que j’ai reçu en PJ dans un mail –en quelques minutes ! – de la part de Finance 3.1.

Bien entendu, le tracé de waterfalls n’est que l’une des nombreuses fonctionnalités offertes par les barres d’outils UpSlide (www.upslide.fr)...

Deux façons de nettoyer du texte…

2011-11-29T20:37:00.004+01:00

Nous avons en A1 un texte comportant des « scories » : nous souhaitons le nettoyer en ôtant les espaces superflus ainsi que les caractères qui sont ni alphabétiques ni numériques.

Nous voyons ci-dessous deux solutions totalement différentes…

En A3, une formule certes efficace, mais d’une lourdeur non négligeable !

En A5, une formule faisant référence à une fonction personnalisée définie par macro…

Remarque 1 – Les contrôles effectués par les deux formules ne sont pas identiques. Vous pourrez vous amuser à vérifier quelles sont les différences entre les deux solutions. Notre propos ici est d’illustrer deux approches totalement différentes, pas de réaliser deux solutions totalement équivalentes !

Remarque 2 – Pour simplifier, on peut dire que nous éliminons dans la première solution les caractères indésirables, alors que nous spécifions dans la seconde les caractères acceptables...

Le contrôle de la validation

2011-11-25T09:07:00.006+01:00

Pour compléter le sujet précédent – La validation de cellule – il est peut-être utile de préciser ce qui se passe avec les trois options de contrôle de cette validation, spécifiées dans l’onglet « Alerte d’erreur ».

Supposons que nous appliquions le contrôle proposé dans l’article précédent – un multiple de 7 – et supposons que l’utilisateur essaye de valider une saisie erronée. Une fenêtre d’alerte apparaît alors, dont la nature dépend de l’option – Arrêt, Avertissement ou Informations – sélectionnée par le développeur dans la case « Style ».

Remarque – On ne voit d’ailleurs pas bien pourquoi Microsoft a utilisé le terme
« Style » : « Type de contrôle », par exemple, aurait été bien plus approprié…

Nous voyons ci-dessus ce que donnent les trois options lors d’une saisie erronée :

Avec l’option « Arrêt », l’utilisateur n’a d’autre choix que d’annuler ou de réessayer…

Avec l’option « Avertissement », il peut passer outre via le bouton « Oui »…

Avec l’option « Informations », il passe outre en direct via le bouton « OK ».

La validation de cellule

2011-11-22T23:46:00.004+01:00

Le dernier article de ce blog portait sur le thème de la validation par liste dynamique. Il est peut-être temps, à cette occasion, de compléter un peu les trois articles déjà publiés il y a plus de quatre ans sur le thème général de la validation (et que vous pouvez lire ou relire avec profit) :

● La validation de cellule (a), le 13 janvier 2007

● La validation de cellule (b), le 18 janvier 2007

● Une protection par validation, le 22 janvier 2007

Les écrans ci-dessous illustrent les différentes possibilités offertes par la validation soit, dans l’ordre : Nombre entier, Décimal, Liste, Date, Heure, Longueur de texte, Personnalisé.

Cette dernière est particulièrement puissante car la formule utilisée pour valider la saisie peut être aussi complexe que nécessaire.

Pour prendre un exemple simple, supposons par exemple que la cellule active soit la cellule C3 et que l’on souhaite exiger que l’utilisateur entre dans cette cellule un entier multiple de 7. Nous pourrions alors utiliser indifféremment l’une des deux formules suivantes :

● =C3=7*ent(C3/7)

● =mod(C3;7)=0

Si l’on voulait un multiple de 7 inférieur à 100, on pourrait écrire :

● =et(C3=7*ent(C3/7);C3<100)

● =et(mod(C3;7)=0;C3<100)

Une validation par liste dynamique

2011-11-17T12:54:00.004+01:00

Vous avez déjà probablement appris – surtout si vous êtes un familier de ce blog ! – comment utiliser la commande « Validation des données » de l’onglet
« Données ». Vous savez donc que l’on peut valider par une liste.

Dans la copie d’écran ci-dessous, nous avons une cellule A1 affichant actuellement « Pays » et dans laquelle nous souhaitons que l’utilisateur sélectionne un des pays listés en colonne C.

Nous avons défini le nom dynamique « Pays » (cf. première copie d’écran dans l’image ci-dessous). Puis nous avons défini une validation par liste dont la souce est « =pays » (cf. seconde copie d’écran dans l’image ci-dessous).

Il suffit alors de dérouler le menu en A1 pour voir apparaître la liste de sélection visible en haut de l’image ci-dessus.

Cette liste est totalement dynamique : si vous ajoutez un nouveau pays à la liste, le menu déroulant en A1 incorporera immédiatement ce nouveau pays !

Remarque – Un avantage collatéral – non négligeable ! – de cette utilisation d'un nom pour la liste de validation est que l'on n'est plus limité par la contrainte que la liste de validation se trouve dans la feuille active !

Un nouveau bug de 2007 & 2010

2011-11-13T07:33:00.007+01:00

Parmi mes activités actuelles, je suis en train de faire le re-engineering des fichiers de reporting d’un cabinet d’avocats réputé. Cela implique une vingtaine de classeurs Excel 2010.

J’étais en train de rendre dynamiques une bonne trentaine de graphes dans ces divers classeurs et je suis tombé plusieurs fois sur le problème suivant : la définition du nom dynamique s’effectuait sans problème, puis la commande
« Atteindre » me permettait de vérifier que le nom fonctionnait bien, et pourtant – quand je voulais remplacer dans la formule « =serie(…) » l’ancienne référence aux cellules par le nom dynamique –, rien ne se passait.

Cela signifie qu’Excel refusait de valider la formule, sans pour autant afficher le moindre message d’erreur.

Après divers recoupements, à force d’essuyer ces échecs, j’en ai découvert la cause. Excel 2007 et 2010 refusent tout simplement le remplacement dans la formule serie(...) des coordonnées de cellules par un nom dynamique si ce nom débute par « r » ou « c ».

Bien entendu, cela m’a fait penser à une immense confusion de la part de Microsoft avec les coordonnées en anglais, où « r » fait référence à « Row » – la ligne – et « c » à « Column » !

Pour le moment, je n’ai pas trouvé d’autre solution pratique que d’éviter que les noms dynamiques destinés à des graphes du même nom ne démarrent par « r » ou « c ».

Il y a bien une parade, mais elle n’est pas d’une simplicité biblique : ouvrez votre classeur en Excel 2003 (si cela n’y casse rien), transformez votre graphe en graphe dynamique sans restriction sur le nom dynamique, puis rouvrez-le avec Excel 2007 ou 2010 !

Quand on importe un graphe dynamique avec un nom démarrant soit par
« r » soit par « c », il n’y a pas de problème. Le problème ne survient qu’au moment où l’on cherche à valider la formule =serie(…) après y avoir modifié quelque chose !

Encore un bug à rajouter à la longue liste des bugs que j’ai découverts dans Excel…

Le premier bug que j’ai identifié dans notre produit favori – cela remonte aux débuts d’Excel ! – était que, quand on faisait un collage spécial avec addition dans un groupe de cellules, la valeur était bien ajoutée quand la cellule contenait déjà quelque chose, mais elle était soustraite quand la cellule était vide !

Créer un graphe dynamique...

2011-11-09T23:47:00.005+01:00

Nous avons vu, dans la dernière rubrique, comment il fallait s’y prendre pour créer un nom dynamique. Nous allons voir aujourd’hui comment créer un graphe dynamique, c’est-à-dire s’adaptant automatiquement au nombre des données présentes.

Remarque 1 – On peut aussi utiliser le terme « graphe élastique »…

Commençons par sélectionner le bloc de données en A1:A10 puis créons un graphe, en sélectionnant un simple histogramme. Bien entendu, ce graphe n’est pas dynamique : il ne s’adapte pas aux données puisqu’il continue à afficher 10 valeurs même si l’on ajoute ou détruit des données.

Pour rendre le graphe dynamique, sélectionnons-le puis, dans la formule =serie(...) dans la barre de formule, remplaçons $A$1:$A$10 par le nom dynamique « Valeurs » défini dans l’article précédent, et enfin validons par [Entrée].

Remarque 2 – Si nous n’avions pas gardé « Feuil1 ! » quand nous avons remplacé $A$1:$A$10 par « Valeurs », Excel aurait refusé notre modification.

Le graphe est à présent dynamique (ou élastique), ce que nous vérifions aisément en détruisant les trois dernières valeurs, puis en retirant la formule de A7 vers le bas jusqu’en A13, comme nous le voyons dans la copie d'écran ci-dessous.

Remarque 3 – Nous notons, si nous sélectionnons la série dans le graphe, que Feuil1!valeurs est devenu Class.xls!Valeurs, c’est-à-dire qu’Excel a remplacé le nom de la feuille par celui du classeur, ce qui est approprié, dans la mesure où un nom est par défaut défini au niveau du classeur.

Remarque 4 – Cet article et l’article précédent sont des actualisations de ce que j’avais déjà publié le 25 et le 29 novembre 2007.

Création d'un nom dynamique

2011-11-04T22:46:00.004+01:00

Une des clefs pour la création de modèles professionnels est l’utilisation de noms dynamiques, c’est-à-dire de noms qui s’adaptent automatiquement aux données présentes. Si l’on enlève ou ajoute des données, le nom s’adapte alors immédiatement…

Ici, nous avons utilisé les formules représentées dans le cartouche. Nous souhaitons créer un nom qui identifie les données de la colonne A, soit actuellement A1:A10. Mais nous voulons que ce nom soit dynamique : si l’on reproduit la formule de la cellule A10 en A11:A12, il faut que le nom identifie alors le bloc A1:A12; si l’on efface le bas de la colonne pour ne garder que les cellules A1:A7, il faut que le nom ne représente plus que ces sept cellules.

Pour cela, activez l’onglet « Formules » puis la commande Gestionnaire de noms, la commande « Nouveau… », saisissez le nom « Valeurs » et dans la zone « Fait référence à : », entrez : =Feuil1!$A$1:decaler(Feuil1!$A$1;Feuil1!$B$1-1;0).

Remarque 1 – Vous pourriez me dire qu’il est plus direct de passer par la commande « Définir un nom ». Un, cela ne gagne pas de temps car il faut appeler cette commande deux fois de suite. Deux, j’aime bien – quand je définis un nom – voir quels autres noms ont déjà été définis.

Comment vérifier que le nom est bien dynamique ?

Jusqu’à Excel 2000, la solution était simple… Il suffisait d’entrer dans une cellule la formule =somme(valeurs), puis de faire un double clic dans la cellule pour l’éditer : Excel sélectionnait alors exactement le bloc contenant les valeurs.

Malheureusement, pour les versions postérieures, cela ne marche plus. La moins mauvaise solution est d’utiliser le raccourci [Ctrl]-t (la commande
« Atteindre ») puis de taper le nom voulu et de valider par OK.

Remarque 2 – Vous noterez au passage que le nom ne se trouve pas dans la liste, et qu’il faut donc le taper. En effet, les noms dynamiques ne sont pas considérés par Excel comme de vrais noms !

Remarque 3 – Notre nom dynamique suppose que, dans la zone à nommer, il n’y ait aucune cellule vierge ou contenant du texte. Si nous avions utilisé nbval(), cela aurait résolu le problème des cellules contenant du texte mais, dans un cas tel que celui-ci, on ne souhaite de toute façon pas qu’il y ait du texte dans la zone nommée.

Remarques sur les bullet graphs

2011-10-31T23:42:00.003+01:00

Voici avec cet article la fin de notre série sur les bullet graphs: ils représentent une façon originale de rassembler en un seul graphe de multiples informations sur la position d’un indicateur par rapport à son objectif et à divers seuils de qualité.

Certes, vous trouverez sur le marché des add-ins qui prendront leur tracé en charge pour vous, mais – avec cette série d’articles – vous avez au moins la possibilité de les créer vous-même, et sans sortir votre carte de crédit…

Plutôt que le dédoublement de bullet graphprésenté dans l’article précédent, vous pouvez aussi procéder de la façon suivante, qui d’une part évitera la perte d’épaisseur consécutive au dédoublement, et d’autre part vous laissera créer autant de bullet graphs que vous le voudrez :

1 – Clonez le bullet graph vertical en le tirant à droite avec la touche [Ctrl] enfoncée. Attention, il faut que la zone de graphique soit préalablement sélectionnée (et non la zone de traçage) ; si c’est bien le cas, on voit les 8 marqueurs de sélection !

2 – Cliquez dans le clone pour obtenir le liseré bleu. Cliquez sur un des quatre bords de ce liseré pour sélectionner le bloc, puis déplacez ce bloc d’une colonne vers la droite, ce qui sélectionne uniquement la seconde série.

3 – Copiez la zone où se trouve le second bullet graph et collez l’image avec liaison, puis tournez-la de 90° vers la droite, comme nous avons appris à le faire dans l’article du 18 octobre.

Avec ce processus, tous les bullet graphs sont indépendants les uns des autres, mais on est obligé de passer par la troisième étape en plus…

Dédoublement du bullet graph

2011-10-26T15:35:00.005+02:00

Nous nous intéressons aujourd’hui à la façon de créer rapidement un second bullet graph à partir du premier, créé en utilisant les conseils des articles précédents.

Nous voyons, dans la première copie d’écran ci-dessous, un tableau de données pour deux produits, Pr. 1 et Pr. 2, avec les données originales en B3:C8 et celles que nous avons calculées pour le graphe en F3:G8, en utilisant les formules présentées dans l’article du 18 octobre.

Quand nous activons le graphe original, ses données en F3:F8 sont activées, ce qui est marqué par un liseré bleu. Tirons le marqueur du coin inférieur droit à droite d’une colonne, et nous obtenons alors le résultat de la copie d’écran ci-dessous : le graphe actif intègre à présent les données des deux produits, et sa photo dynamique en lignes 12 et 14 reflète cette modification.

Remarque 1 – Voilà une méthode remarquablement rapide et simple pour redéfinir les données d’un graphe ! Et cela marche dans les deux sens : si l’on tire le coin inférieur droit d’une colonne vers la gauche, on restaure la situation précédente…

Remarque 2 – Seul inconvénient : les deux bullet graphs sont à présent deux fois plus minces. Si donc on prévoit de dédoubler ainsi un bullet graph, il faut le créer deux fois plus gros au départ !

Le « bullet graph » horizontal

2011-10-22T11:21:00.008+02:00

Comment passer du bullet graph vertical que nous avons créé il y a quatre jours à un « bullet graph » horizontal ? C’est ce que nous allons à présent apprendre à faire.

Commençons par tirer le graphe (c) – cf. l'article d'il y a quatre jours – vers la droite au milieu d’une colonne G étroite. Sélectionnons le bloc G10:G20, qui recouvre alors entièrement le graphe (c) ainsi déplacé.

Copions ce bloc avec [Ctrl]-c puis – et c’est là toute l’astuce ! – maintenons la touche [Maj] enfoncée avant d’exécuter la commande « Coller ». Nous voyons alors la commande « En tant qu’image… » apparaître et nous sélectionnons l’option « Coller l’image avec liaison ».

Et voilà ! Nous venons de créer un objet qui est une image – par conséquent redimensionnable et déplaçable à loisir – jouant le rôle de photo dynamique du graphe.

Cliquons sur cette image pour la sélectionner. Nous voyons un rond vert au-dessus de l’image (cf. copie d'écran ci-dessus) : il suffit de le sélectionner et de le tirer dans le sens des aiguilles d'une montre pour faire tourner l’image et rendre le graphe horizontal. En utilisant la touche [Maj] durant ce process, on s’assure d’un placement exactement horizontal.

Il suffit maintenant de modifier les valeurs de B1:B6 pour constater que dans tous les graphes (a, b et c dans l'article précédent), y compris le nouveau bullet graph horizontal, la mise à jour des graphes est immédiate !

Remarque – En ce qui concerne les étiquettes des X dans la copie d’écran il y a 8 jours, j’ai triché ! J’ai en effet créé un graphe avec cet axe des X puis j’ai copié et collé cet axe en dessous de mon bullet graph. Un peu magouilleur certes, mais plus rapide que d’autres approches…

Le « bullet graph » vertical

2011-10-18T20:17:00.011+02:00

Le bullet graph le plus facile à créer est le vertical. Une première étape consiste à évaluer les pourcentages de l’objectif apportés par chacune des quatre valeurs « Mauvais », « Acceptable », « Bon » ou « Excellent ». Nous voyons dans la copie d’écran ci-dessous en colonne F une copie des formules utilisées à cet effet en E1:E4.

Pour la valeur actuelle et l’objectif, on calcule ce même ratio, sans tenir compte des quatre premiers ratios…

On construit alors un histogramme cumulé à partir des valeurs E1:E6. Le résultat de ce premier « tracé » est le graphe (a).

On sélectionne alors la série n°6, on l’associe à l’axe droit, on donne à cet axe droit la même échelle que celle de l’axe gauche (ici, de 0% à 120%), et on modifie le graphe de cette série pour prendre la courbe avec marqueurs. Il suffit alors d’effacer la courbe – en ne gardant que les marqueurs – et à sélectionner un marqueur en barre horizontale rouge de largeur 20.

Ensuite, on sélectionne la série n°5, on l’associe à l’axe droit, et on modifie son intervalle pour prendre un intervalle maximal, ce qui rétrécit la barre verticale de l’objectif.

On détruit alors l’axe droit, et on modifie les couleurs des séries 1 à 5 pour obtenir le dégradé de gris pour les quatre premières et le noir complet pour la cinquième. Vous obtenez alors le résultat du graphe (b).

Il ne reste plus qu’à ôter l’échelle verticale, le quadrillage et les bordures, et
à « bouffer » les espaces… pour obtenir enfin le résultat du graphe (c), un
« bullet graph » vertical.

Présentation du « bullet graph »

2011-10-14T10:33:00.010+02:00

Vous avez probablement déjà entendu parler de « bullet graph » ou « bullet chart ». Il s’agit d’une invention de Stephen Few, dont nous voyons ci-dessous un exemple dans les lignes 10 à 12.

Vous avez indiqué dans le bloc B3:B6 les seuils de valeurs pour lesquelles vous jugiez que le résultat était respectivement « Mauvais », « Acceptable », « Bon » ou « Excellent ».

Ces valeurs sont représentées dans le « bullet graph » par quatre fonds gris, de teintes de plus en plus claires.

Le graphe affiche aussi – via la barre noire centrale – la valeur actuelle de la variable analysée et – via une barre rouge, dans cet exemple – la valeur de l’objectif à atteindre.

On parle de « bullet graph » car le résultat fait penser à l’avancée d’une balle dans le barillet d’un pistolet.

Remarque – Il n’est pas nécessaire de représenter un axe des abscisses comme nous l’avons fait ici. Ce type de graphe est déjà très explicite sans axe des X.

Un bullet graph peut être représenté horizontalement comme ici – c’est le cas le plus fréquent – ou verticalement. Il est cependant plus aisé à construire verticalement.

La construction d’un bullet graph n’est pas simple : j’ai trouvé sur Internet des tutoriaux pour cela longs respectivement de 12 et de 16 pages, ce qui est mal adapté au format (1 à 2 pages) de ce blog. Je vous montrerai dans les deux prochains articles comment en construire un bien plus rapidement :)

Le cours d’une action, en mieux…

2011-10-10T22:48:00.004+02:00

Suite à l’article précédent, j’ai eu le plaisir de recevoir une proposition de Claude Duigou, qui résout en bonne partie le problème soulevé. Nous voyons ci-dessous le résultat obtenu : on sélectionne la zone du graphe, puis on balade le curseur le long du graphe : le titre du graphe s’adapte alors en temps réel et affiche la date et la valeur.

Comme on le voit ci-dessus, ces informations apparaissent aussi dans le graphe à la hauteur du point, mais seulement après un certain temps d’attente, pas immédiatement comme pour le titre.

Pour obtenir ce résultat, il a fallu créer – liée au classeur – la macro suivante :

Dim ClasseGraphique As New GraphiqueEvenement

Private Sub Workbook_Open()

Set ClasseGraphique.MonGraphique = ActiveSheet.ChartObjects(1).Chart

End Sub

Et en plus, dans un module de classe, la macro suivante :

Public WithEvents MonGraphique As Chart

Private Sub MonGraphique_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim lngElement As Long

Dim Arg1 As Long

Dim Arg2 As Long

MonGraphique.GetChartElement x, y, lngElement, Arg1, Arg2

If lngElement = xlSeries Then

ActiveSheet.Range("DateJour").Value = ActiveSheet.Cells(Arg2 + 1, 1).Value

ActiveSheet.Range("Valeur").Value = ActiveSheet.Cells(Arg2 + 1, 2).Value

End If

End Sub

La seule chose qui manque encore, par rapport à l’original sur Boursorama, est le marquage par un gros point rouge de la valeur active correspondante dans le graphe.

Dans la solution que je proposais il y a quatre jours, il y avait le point (mais ni gros ni rouge), mais en revanche on ne pouvait pas simplement passer la souris sur le graphe : il fallait utiliser le curseur pour passer d'un point à un autre…

Suivre le cours d’une action

2011-10-06T13:35:00.003+02:00

Sur de nombreux sites de bourse ou de conseils financiers, en particulier sur la page d’accueil de Boursorama, on dispose du graphe d’un cours qui, quand on déplace le curseur dessus, marque le point sur le graphe et indique le cours et l’heure.

Voyez donc cela sur le site : http://www.boursorama.com/bourse/

Un lecteur me demandait récemment si l’on pouvait faire cela dans Excel. Je n’ai pas trouvé de solution simple pour le faire, mais j’ai quand même une solution sympathique à vous proposer, qui atteint une bonne partie de l’objectif.

Nous créons un ascenseur attribuant à la cellule C1, nommée Pos, une valeur de 1 à 25. Pour la création de l’ascenseur, reportez-vous à l’article précédent…

Nous entrons en C2 la formule ="Valeur en "&texte(index(A:A;C1);"mmmm aaaa")&" : "&texte(index(B:B;C1);"0 €"), et nous donnons ce titre au graphe.

Il ne reste plus qu’à créer la macro événementielle suivante, liée à la feuille actuelle :

Private Sub Worksheet_Calculate()

ActiveSheet.ChartObjects("Graphique 1").Activate

ActiveChart.SeriesCollection(1).Select

ActiveChart.SeriesCollection(1).Points(Range("Pos")).Select

End Sub

Dès que l’on utilise l’ascenseur, cela modifie la valeur de C1 et active donc la macro, mettant en relief le point sélectionné. Comme nous le voyons dans l’image ci-dessus, le point à 79 € de juillet 2010 est aussitôt mis en relief.

Remarque – Le seul petit problème est que le point précédent est lui aussi – quoique un peu moins – mis en relief. Vous le verrez mieux en agrandissant cette copie d’écran à l’aide d’un double clic dessus : )

Si un lecteur peut nous proposer une macro faisant exactement comme sur le site de Boursorama, je serai ravi de publier sa solution !

Fonction économique modulable

2011-09-29T21:57:00.003+02:00

Pour terminer en beauté la série d’articles que nous venons de publier sur la programmation linéaire, et pour assister ceux parmi les lecteurs qui enseignent la modélisation, il ne reste plus qu’à créer un ascenseur grâce auquel nous pourrons déplacer la fonction économique parallèlement à elle-même.

La première étape pour cela consiste à s’assurer que les déplacements de la droite resteront parallèles. C’est une simple affaire : il suffit d’entrer en H14 la formule =G13*300/350.

La seconde étape revient à créer l’ascenseur.

Avec Excel 2007 et suivants, activons l’onglet « Développeur », commande «Insérer », bloc « Contrôles de formulaire », outil « Barre de défilement ».

Avec Excel 2003, sélectionnons l’outil « Barre de défilement » de la barre d’outils « Formulaire ».

Traçons un ascenseur vertical. Faisons un clic droit dedans puis actionnons la commande « Format de contrôle ». définissons G13 comme cellule liée et faisons varier la barre entre un minimum de 0 et un maximum de 3500, avec par exemple un pas de 5 et une page de 100.

Et voilà ! En déplaçant le curseur, vous faites évoluer la fonction économique. Vous avez là un excellent outil pédagogique pour expliquer la programmation linéaire. Comme vous le voyez ci-dessus, où nous avons ôté – pour mieux voir la fonction économique – le remplissage bleu du domaine des solutions réalisables.

Tracé de la fonction économique

2011-09-25T23:12:00.005+02:00

La fonction économique est la fonction à optimiser, ici il s'agit de la fonction 300*BR1+350*BR2.

Pour la tracer, nous sommes confrontés à un problème : sa formulation indique la pente de la fonction économique mais ne nous permet pas de tracer une droite. Pour cela, il nous faut associer une constante, sous la forme 300 BR1+350 BR2 = Constante.

Prenons, pour simplifier les calculs, la formulation 300*BR1+350*BR2 = 10*300*350, ce qui nous donne les coordonnées en G13:H14 pour l’intersection de cette droite avec l’axe des X et l’axe des Y.

Sélectionnons le bloc G13:H14, copions-le, sélectionnons la fenêtre contenant le graphe, puis utilisons la commande « Collage spécial » en prenant les options « Nouvelle série » et « Abscisses dans la première colonne ». Nous voyons le résultat ci-dessous, avec la fonction économique que nous avons décorée en rouge.

Et la résolution du programme linéaire dans tout cela ? Eh bien, il suffit de trouver quel est le point de l’hexagone par lequel on peut faire passer la droite parallèle à la droite rouge qui soit la plus haute possible (on souhaite maximiser la fonction économique).

Sur l’image ci-dessous, on constate aisément qu’il s’agit bien du point x=2000 et y=750 qui avait effectivement été identifié par le solveur (cf. article du 13 septembre).

Représentation graphique du PL

2011-09-21T12:07:00.006+02:00

Le programme linéaire (PL) que nous étudions depuis quelques articles possède une particularité tout à fait intéressante : il ne possède que deux variables, ce qui nous permet de représenter le problème graphiquement dans un plan.

Dans cet article, nous verrons comment représenter graphiquement le domaine réalisable, c’est-à-dire le tracer dans un plan. Ce domaine regroupe l'ensemble des solutions possibles.

Il faut pour cela entrer dans les coordonnées X (colonne G) et Y (colonne H) de deux points de chaque contrainte, en laissant une ligne blanche entre les contraintes. Nous sélectionnons ensuite G1:H11 puis insérons un graphe en X/Y (nuages de points) avec des segments.

Comme on le voit dans le graphe ci-dessous, on obtient le graphe représentant toutes les contraintes, où j’ai ensuite identifié le domaine réalisable en lui superposant une « forme libre » – c’est ainsi qu’Excel appelle une séquence de segments connectés – que j’ai remplie de bleu.

Le domaine réalisable est donc l’hexagone peint en bleu : chaque point à l’intérieur de l’hexagone respecte l’ensemble des contraintes, et chaque point en dehors viole au moins une des contraintes.

Les trois rapports du solveur

2011-09-17T09:08:00.004+02:00

Les trois rapports du solveur correspondent à des onglets qui viennent s’insérer juste avant l’onglet où se trouve le modèle linéaire que l’on a optimisé.

Ils portent les noms « Rapport des réponses 1 », « Rapport de la sensibilité 1 » et « Rapport des limites 1 ».

Remarque – On peut déplorer que les noms de ces trois onglets soient si longs : plus des noms d’onglets sont longs, moins on arrive à en voir à la fois dans la barre des onglets. Les noms « Réponses », « Sensibilité » et « Limites », par exemple, auraient été bien préférables ! Je conseille en règle générale d’utiliser des noms d’onglets aussi courts que possible, sans bien entendu en arriver à ce qu’ils ne soient pas significatifs.

En fait, des trois rapports, le premier et le troisième sont totalement inutiles si l’on a choisi de garder dans la feuille du modèle la solution optimale. Le seul rapport qui fournisse des informations utiles est le rapport de sensibilité.

Si ce rapport ne comporte pas toutes les colonnes ci-dessous, c’est qu’il faut recommencer l’optimisation en cochant vie la bouton « Options » l’option
« Modèle supposé linéaire », si vous utilisez Excel 2003 ou2007. Avec 2010, il faut choisir « Simplex PL » dans la zone « Sélectionner une résolution ».

La traduction de cet onglet en français a été un véritable massacre, effectué par un imbécile congénital. C’est du mot à mot, par exemple « finale Valeur » parce qu’en anglais on dit « Final value »… Le plus surréaliste est le « Shadow cost » qui – comme de bien entendu – est devenu « Ombre Coût » - ce qui ne signifie absolument rien en français, alors qu’il aurait fallu utiliser « Valeur marginale » ou « Valeur duale ». Notez que cela fait plus de 10 ans que j’en ai informé Microsoft…

Ceci dit, les renseignements fournis par cet onglet sont précieux. On apprend ainsi en E16 que tout relâchement de la contrainte d’assemblage nous ferait gagner 112,50 € par unité, jusqu’à une augmentation de 1.000 unités maximum (cf. G16). Au-delà on gagnerait soit plus rien, soit moins.

Bon, j’arrête là, je n’ai ni le temps ni la place ici de vous faire tout un cours sur la programmation linéaire, mais sachez que c’est un domaine très riche et que cela permet de bien résoudre de multiples problèmes d’optimisation des ressources dans les entreprises.

Résolution avec le solveur

2011-09-13T17:07:00.004+02:00

Pour résoudre le problème d’optimisation présenté il y a quatre jours, il faut utiliser le solveur d’Excel, un complément disponible dans l’onglet « Données ». S’il n’y apparaît pas, à droite de l’onglet, utilisez les « Options Excel », section
« Compléments », et activez-le.

Nous réglons alors le solveur en définissant ses paramètres comme dans la seconde image ci-dessous et en cochant l’option « Modèle supposé linéaire » via le bouton « Options » de ce dialogue.

Remarque 1 – Si vous oubliez de spécifier que le modèle est linéaire, les rapports que vous pourrez obtenir après l’optimisation seront moins détaillés.

Remarque 2 – Notez que vous gagnez du temps en écrivant les contraintes sous forme matricielle : ainsi, $D$4:$D$7<=$E$4:$E$7 signifie en une seule formule que vous voulez respecter les quatre contraintes $D$4<=$E$4, $D$5<=$E$5, $D$6<=$E$6 et $D$7<=$E$7 !

Excel affiche alors le dernier dialogue ci-dessus pour vous avertir que la solution optimale (cf. première image ci-dessus) a été trouvée. Les deux autres issues possibles sont « Solution infinie » – auquel cas vous avez oublié de formuler certaines contraintes – ou « Pas de solution réalisable », qui correspond au cas où il n’y a aucune solution satisfaisant à toutes vos contraintes…

Si vous êtes satisfait de la solution, sélectionnez éventuellement un ou plusieurs rapports et quittez ce dialogue en cliquant dans « OK » pour garder la solution optimale. Nous parlerons dans le prochain article de l’intérêt de chacun des trois rapports.

Programmation linéaire – Intro

2011-09-09T22:35:00.004+02:00

La programmation linéaire est la technique d’optimisation la plus utilisée au monde. Elle consiste à résoudre des problèmes formulés à l’aide d’équations ou d’inéquations à plusieurs variables, chaque formule étant exprimée au premier degré – d’où le terme « linéaire ».

Il n’y a pas plus simple que le premier degré : quand on écrit x=1, on est déjà au premier degré ! Heureusement pour les entreprises, les modèles linéaires, qui sont donc les plus simples possibles, permettent de formuler – et donc résoudre – une très grande variété de problèmes de production, logistique, finance, marketing… Toutes les branches de la gestion utilisent des modèles linéaires, jusqu’aux ressources humaines !

Le problème ci-dessous est un problème de production. Nous fabriquons deux produits, BR1 et BR2. Les quantités à fabriquer sont entrées en B2 et C2. Pour fabriquer une unité de BR1, il faut trois unités de tôlerie-peinture et une d’assemblage. On dispose de 7.500 unités de tôlerie-peinture dans la période analysée. On a la contribution par unité (B3 et C3). Le but est d’identifier les quantités optimales BR1 et BR2 qui – en respectant toutes les contraintes – permettront d’obtenir la contribution maximale.

Nous avons mis dans une zone de texte la formulation mathématique du problème. Il s’agit bien de programmation linéaire puisque la fonction économique (la fonction à optimiser) et toutes les contraintes sont linéaires.

La formule de D4 (recopiée jusqu’en D7) est =sommeprod($B$2:$C$2;B4:C4) et celle de E3 est =sommeprod(B2:C2;B3:C3). Il n’y a que cinq formules dans le modèle.

Nous verrons dans le prochain article comment utiliser le solveur d'Excel pour trouver la solution optimale.

Création du schéma précédent

2011-09-05T08:17:00.005+02:00

Une particularité du schéma publié il y a quatre jours est que les connecteurs reliant les boîtes du schéma sont dynamiques : si donc vous déplacez ou redimensionnez une des boîtes, les connecteurs s’adaptent de façon à préserver les liens antérieurs.

Nous allons prendre l’exemple du connecteur reliant la boîte « Assemblage » à la boîte « Travaux finition ». Nous traçons en premier lieu (première image ci-dessous) un arc dans le vide.

Ensuite (image ci-dessus), nous cliquons sur le connecteur, sélectionnons la poignée de sélection en haut du connecteur, et la tirons à l’intérieur de la boîte
« Assemblage ». Apparaissent alors les quatre marqueurs au milieu des bords de la boîte « Assemblage » : il suffit de lâcher la poignée sur le marqueur voulu – qui affiche aussitôt un gros rond rouge – pour ancrer la connexion à cet endroit.

Si l’on déplace ensuite la boîte « Assemblage », on constate que le connecteur s’adapte au déplacement et préserve son ancrage.

On fait de même ensuite avec la poignée basse du connecteur et la boîte
« Travaux finition » pour ancrer de la même façon le connecteur avec cette boîte. Le connecteur est à présent ancré aux deux boîtes et s’adaptera à tout déplacement ou redimensionnement de l’une ou de l’autre.

PERT – Le schéma

2011-09-01T18:57:00.006+02:00

Nous avons vu le 25 août le graphe permettant de suivre l’évolution chronologique des différentes tâches du projet.

Nous aurions aussi pu enrichir notre modèle en faisant apparaître dans le graphe, dans une autre couleur, la marge de temps dont on dispose pour les tâches non critiques avant qu’elles en deviennent critiques à leur tour.

En revanche, ce que l’on ne voit pas bien dans le graphe, c’est le lien de dépendance entre les prédécesseurs et les successeurs dans la séquence des tâches. Pour cela, il est intéressant de disposer d’un véritable schéma.

Nous avons créé le schéma ci-dessus en utilisant à la fois des formes et des connecteurs. Pour le contenu des boîtes, nous avons créé en V2 la formule =B2&car(13)&"Date : "&J2&car(13)&"Durée : "&C2, que nous avons ensuite recopiée vers le bas.

Pour associer le texte résultant de la formule de V2 à la première boîte, il a suffi – après l’avoir sélectionnée – de taper =V2 dans la barre de formule. Comme nous avons obtenu les autres boîtes par clonage, il suffisait pour chaque nouvelle boîte de modifier le numéro de ligne dans la formule.

J’ai enfin mis en fond jaune, à la main, toutes les tâches du chemin critique…

Je n’ai hélas pas trouvé de solution pour associer à chaque boîte un format conditionnel – identifiant le chemin critique – qui soit lié aux dates au plus tôt et au plus tard de chaque boîte, et tel que l'on puisse le recopier automatiquement lors du clonage des boîtes…

Remarque – Ce schéma est bien plus utile, pour visualiser les dépendances entre les tâches, que le graphe du 25 août !

PERT – Date au plus tard

2011-08-29T12:14:00.006+02:00

Dans les deux derniers articles, nous avons vu comment calculer la date au plus tôt de chaque tâche dans un projet. Nous verrons aujourd’hui comment calculer la date au plus tard, c’est-à-dire la dernière date à laquelle une tâche peut démarrer sans retarder l’ensemble du projet.

Pour la calculer, nous avons mis dans les colonnes L à O les noms des successeurs immédiats de chaque tâche. Là, je dois avouer que j’ai triché un peu…

En effet, la formule de L2 est déjà assez gratinée :

=si(estna(equiv(A2;D2:$D$12;0));si(estna(equiv (A2;F2:$F$12;0));si(estna(equiv
(A2;G2:$G$12;0));"";equiv(A2;G2:$G$12;0)+ligne()-2);equiv(A2;F2:$F$12;0)+ligne()-2);
equiv(A2;D2:$D$12;0)+ligne()-2)

J’ai donc triché pour les colonnes M à O en entrant les valeurs à la main :(

La formule de P3, reproduite jusque P11, est :

=min(index(P:P;L3+1);index(P:P;M3+1);index(P:P;N3+1);index(P:P;O3+1))-C3

Si vous trouvez des formules supportables pour le bloc L:O, dites-le moi…

Les tâches dont la date au plus tôt et la date au plus tard sont égales forment le chemin critique : si l’une quelconque des tâches du chemin critique prend du retard, c’est l’ensemble du projet qui prend du retard !

Nous avons défini un format conditionnel pour identifier le chemin critique avec en A2 et B2 la formule =$J2=$P2, cette formule ayant été ensuite recopiée jusqu’en bas.

La méthode PERT – Graphique

2011-08-25T17:31:00.002+02:00

Reprenons le modèle développé il y a quatre jours pour en faire un graphique. Nous sélectionnons à cet effet le bloc B2:B11 puis – en maintenant la touche [Ctrl] enfoncée, le bloc J2:K11. Nous insérons alors un graphe en barres indépendantes et nous obtenons le premier graphe ci-dessous, après avoir détruit la légende.

Faisons à présent un clic droit sur une barre de la série des dates au plus tôt et activons la commande « Mettre en forme une série de données » puis, dans le premier onglet – Options des séries – mettons à 50% le taux de superposition et réglons à 69% la largeur de l’intervalle. Nous voyons en premier plan la série la plus courte et, en arrière-plan, la série la plus longue.

Sélectionnez la série la plus courte, celle des dates au plus tôt et donnez-lui une couleur blanche unie. Si cette série est « devant » l’autre série, tout va bien ; sinon, modifiez l’ordre des séries pour qu’elle passe devant.

Il ne vous reste plus qu’à mettre à 50% le taux de superposition et à mettre la série qui dépasse en bleu pour obtenir le résultat du graphe ci-dessus…

… à une petite différence près : vous constaterez que la série en blanc masque le quadrillage vertical. Problème que nous avons résolu en insérant des lignes verticales pour combler les trous ! Un autre choix esthétique serait de donner, au lieu d’un fond blanc, une transparence de 10% à la série des dates au plus tôt, ce qui éviterait l’insertion de lignes de quadrillage…

La méthode PERT – Initiation

2011-08-21T23:13:00.003+02:00

La méthode PERT – pour Program Evaluation and Review Technique – est réputée pour la modélisation d’un projet contenant plusieurs étapes. Dans l’exemple ci-dessous, il y a 11 étapes depuis l’organisation initiale jusqu’à la fin d’un projet.

On connaît la durée prévue de chaque étape. Il y a en outre (cf. colonnes D, F et H) des contraintes d’ordre : ainsi, l’assemblage (tâche g) ne peut démarrer que lorsque les tâches b et d sont terminées.

Le but d’un tel modèle est de calculer (en colonne J) la date au plus tôt à laquelle chaque tâche peut démarrer et la date de fin prévue (en colonne K) pour chaque tâche. De tout cela, on peut déduire – en K12 – la date prévue pour la fin du projet.

Voici les principales formules originales utilisées dans ce modèle…

E2 (recopiée vers le bas, puis en colonne G et I) : =si(D2="";"";equiv(D2;List;0))

J2 : =max(si(E2="";0;index(P_tôt;E2)+ index (Durée;E2)); si (G2="";0; index (P_tôt;G2)+ index (Durée;G2)); si (I2="";0; index (P_tôt;I2)+ index (Durée;I2)))

K2 : =J2+C2

…où List est le nom attribué au bloc A2:A12, et où Durée et P_tôt correspondent respectivement aux blocs C2:C12 et J2:J12.

Nous verrons dans le prochain article comment illustrer ce planning par un graphique.

Un graphe en waterfall

2011-08-15T22:19:00.003+02:00

Dans l’exemple ci-dessous, nous avons l’EBITDA 2009 et les divers éléments qui permettent d’en tirer l’EBITDA 2010. Les valeurs d’origine sont en colonne B.

Pour construire le graphe, il faut créer les colonnes C à F qui respectivement correspondront aux barres bleue, invisible, verte et rouge.

Voici les formules originales…

Formule de D3 : =min(somme($B$2:B2);somme($B$2:B3))

Formule de E3 : =max(B3;0)

Formule de F3 : =-min(B3;0)

Tout cela prend du temps à construire, même quand vous savez exactement ce que vous devez faire. C’est pourquoi nous avons créé – dans le cadre de la société Finance 3.1 – un add-in (complément) Excel qui permet, entres autres fonctionnalités, de créer un waterfall en un seul clic.

Pour en savoir plus sur cet add-in, lisez les articles « UpSlide pour Excel » des 6 et 10 mai 2011,

Des contrôles de valeurs en série

2011-08-11T15:55:00.005+02:00

Nous disposons en colonne A d’une série de valeurs et, en colonne B, d’une série de seuils auxquels nous voulons comparer ces valeurs. L’objectif est d’afficher en colonne C le résultat de ces tests. Nous constatons ainsi que « 12 > 90 » est bien FAUX (en C2) alors que 82 > 61 est VRAI (en C3)…

La formule de C2, reproduite ensuite vers le bas, est assez simple :
=si(somme.si(A2;B2)>0;"Oui";"Non")

Remarque 1 – Que le test en B2 affiche « > 90 » ou « >90 » revient au même, l’espace n’ayant aucune importance. On, pourrait même en mettre plusieurs sans que cela ne pose le moindre problème.

Remarque 2 – Une formule un peu plus légère peut aussi être utilisée : =si(somme.si(A2;B2);"Oui";"Non"). Notez que je trouve souvent, quand j’audite et améliore des modèles chez mes clients, des cas de ce genre ou des tests peuvent être simplifiés…

Si vous voulez en savoir plus sur la fonction somme.si() qui, bien que peu connue, est assez intéressante, je vous conseille de lire mes articles « Les fonctions Somme.Si() et Nb.Si() » du 25 février 2006 et « La fonction Somme.Si() : des trucs en plus » du 2 mars 2006.

Nombre de valeurs différentes

2011-08-06T16:37:00.005+02:00

Dans l’exemple ci-dessous, nous souhaitons savoir combien il y a de valeurs différentes dans le bloc A1:A15.

Nous vous proposons deux solutions différentes, à l’aide des formules matricielles suivantes, donc validées avec [Ctrl]-[Maj]-[Entrée] :

Formule de D2 : =somme(1/nb.si(A1:A15;A1:A15))

Formule de D3 : =somme(1*(ligne(A1:A15)=equiv(A1:A15;A:A;0)))

Certes, la seconde formule est plus longue, mais je vous la montre aussi pour illustrer le fait – une fois de plus ! – qu’il y a souvent plusieurs solutions possibles à un problème délicat.

Dans le cas de D2, la formule nb.si(A1:A15;A1:A15) engendre le vecteur suivant : {1;3;2;2;1;1;3;2;3;1;2;1;2;2;1}. A chaque position, on a le nombre de fois où la valeur est présente dans le vecteur. Ainsi, quand la valeur est présente trois fois, on va additionner trois fois 1/3, pour un résultat de 1. Le total est donc le nombre de valeurs différentes.

Dans le cas de D3, la formule ligne(A1:A15)=equiv(A1:A15;A:A;0) engendre le vecteur {VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;VRAI;FAUX;VRAI;FAUX;VRAI;FAUX; FAUX;VRAI}, où l’on obtient VRAI quand la valeur concernée est la première occurrence de cette valeur dans la colonne. Quand on multiplie par 1, on obtient donc le nombre de valeurs « originales ».

En conclusion, voilà pour vos devoirs de vacances un excellent petit exercice d’application des formules matricielles !

Mesurez-vous à l’Everest !

2011-08-02T12:04:00.004+02:00

Pour progresser en Excel, on ne manque pas de livres, de tutoriaux de toutes sortes, de vidéos d’apprentissage, et de blogs, celui-ci n’en étant qu’un parmi de nombreux autres en toutes langues…

Sean Duffy a créé à cet égard un produit innovant – Excel Everest –, un fichier Excel qui poursuit un objectif multiple : vous aider à progresser dans votre utilisation d’Excel, quel que soit votre niveau de départ, vous permettre de vous évaluer vous-même à tout moment selon la façon dont vous avez réussi les exercices, et vous distraire tout en vous apprenant plein de choses.

Tout cela se fait à l’aide d’un classeur Excel unique faisant un peu plus de 5 Mo, comportant 45 onglets, équipé de 339 boutons de navigation, et incorporant 87 vidéos.

Nous voyons ci-dessus une copie d’une partie de l’écran d’accueil, avec une liste de thèmes de difficultés variées, avec pour chacun un bouton carré qui vous mène directement à l’onglet voulu.

L’ensemble du cours devrait occuper un utilisateur débutant durant 20 à 30 heures, mais il peut bien entendu progresser au rythme qui lui convient.

Ce produit est vendu au prix tout à fait abordable de $ 34,95.

Il ne vous reste plus qu’à aller voir : http://www.exceleverest.com/

De nouveaux livres sur Excel

2011-07-29T22:25:00.000+02:00

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.

Name Manager pour 2007/2010

2011-07-25T15:41:00.003+02:00

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.

Utiliser les noms dans les formules

2011-07-21T22:33:00.005+02:00

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 !

Le gestionnaire des noms

2011-07-17T16:23:00.009+02:00

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à !)

La commande « Noms définis »

2011-07-13T10:24:00.007+02:00

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 1^er 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.

Un nom élastique pour une liste ?

2011-07-09T17:01:00.003+02:00

Dans mon article du 1^er 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.

Définition d’un nom « local »

2011-07-05T21:57:00.003+02:00

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 !

Définition d’un nom élastique

2011-07-01T08:32:00.005+02:00

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…

Utilisation d’un nom relatif

2011-06-27T09:00:00.004+02:00

Avec l’article publié il y a quatre jours, vous vous êtes peut-être dit : « C’est bien joli le concept de nom relatif, mais à quoi cela peut-il bien servir ? ».

Nous en voyons un exemple ci-dessous. Nous avons des ventes en colonne B et nous aimerions bien avoir, en colonne D, les ventes de la veille. Certes, il suffirait pour cela d’entrer en D3 la formule relative =B2 pour faire le travail. Mais cela n’est pas une formule très parlante…

Pour la rendre plus explicite, il serait sympathique d’utiliser des noms. Nous avons attribué le nom Ventes à la colonne B. La formule à entrer en D3 est alors =Ventes préc !

Remarque 1 – Cela bien sûr en utilisant le nom préc défini dans l'article précédent...

N’est-ce pas bien plus joli ?

Pourquoi cela fonctionne-t-il ? Tout simplement parce que, à l’instar du « : » qui, dans Excel, signifie « jusqu’à » et du « ; » qui signifie « et » ; il y a un troisième caractère codé : l’espace qui, dans Excel, signifie « intersection ». Notre nouvelle formule signifie donc « intersection de la colonne Ventes avec la ligne précédente ».

Vous pouvez aisément vérifier d’ailleurs que =B2 donne exactement le même résultat que =B:B 2:2 !

C’est pour cela que vous n’avez pas le droit d’utiliser d’espace dans un nom Excel, ainsi d’ailleurs que dans plusieurs autres fonctionnalités d’Excel !

Remarque 2 – Notre formule apporte un autre avantage. Si vous êtes amené à insérer une ligne, par exemple entre la ligne 9 et la ligne 10, la formule classique sera erronée dans la cellule B11 (ex-B10) alors que la nôtre fonctionnera toujours :)

Création d’un nom relatif

2011-06-23T13:59:00.004+02:00

Par défaut, un nom est toujours défini en absolu, c’est-à-dire que les coordonnées de(s) cellule(s) concernée(s) par ce nom reçoivent automatiquement des dollars. Nous l’avons vu dans l’article publié il y a quatre jours, où le nom « Albert » était associé à la référence =Data!$A$1:$C$2; Data!$B$6:$C$7; Data!$D$13:$E$15, avec des dollars partout !

Et pourtant, rien ne vous empêche de définir un nom avec des coordonnées relatives !

Prenons l’exemple ci-dessous. Nous sélectionnons la ligne 6 et nous appelons la définition de nom en entrant le nom « préc », comme vous le voyez dans la copie d’écran ci-dessous :

Ensuite, dans la zone « Fait référence à : », remplacez le =Noms!$6:$6 (absolu) actuel par =Noms!5:5 (relatif) et validez. Et voilà, vous venez de remplacer la définition absolue faisant référence à la ligne 6 par une définition relative faisant référence à la ligne précédente.

Pour vérifier, utilisez le menu déroulant de la zone « Nom », à gauche de la barre de formules, et sélectionnez « préc » : Excel sélectionne alors immédiatement la ligne juste au-dessus de la ligne active !

Remarque 1 – Si vous faites cela lorsque la cellule active se trouve dans la première ligne, Excel sélectionne alors la toute dernière ligne de la feuille !

Remarque 2 – La définition d’un nom peut aussi être mixte, c’est-à-dire comporter à la fois des coordonnées absolues (avec le « $ ») et des coordonnées relatives (sans le « $ »)…

Définition d’un nom

2011-06-19T23:20:00.007+02:00

Dans Excel, les noms ont de multiples usages. C’est pourquoi il est bon de savoir aussi les définir de façon efficace.

La méthode traditionnelle pour nommer une cellule ou une zone – qui peut être multiple, c’est-à-dire constituée de plusieurs blocs – revient à sélectionner tout d’abord cette zone, à activer l’onglet « Formules », à utiliser le bouton « Définir un nom », puis la commande « Définir un nom », à entrer le nom en question et enfin à valider.

Tout cela est bien long avec ses cinq étapes. On arrive au même résultat bien plus rapidement en sélectionnant la zone à nommer, en cliquant dans la zone
« Nom » (à gauche de la barre de formules), en entrant le nom, puis en validant par [Entrée].

C’est ce que nous avons fait ci-dessous en sélectionnant le bloc multiple A1:C2, B6:C7 et D13:E15, puis en entrant le nom Albert dans la zone « Nom ». Dès que l’on valide ce nom, il est associé à l’ensemble des trois blocs.

Comment vérifier à quoi un nom est associé ?

Il suffit, la cellule activée étant n’importe où dans le même classeur, d’activer le menu déroulant à droite de la zone « Nom », représenté par la flèche basse, et de sélectionner le nom voulu pour qu’Excel active la feuille voulue et la zone nommée. On peut aussi, mais c’est un peu moins rapide, passer par [F5] ou par le raccourci [Ctrl]-t pour afficher la liste des noms et choisir le nom à atteindre.

Un nom identifie donc une zone précise d’une feuille donnée. On le vérifie aisément en passant par la commande « Gestionnaire de noms ». Quand on sélectionne le nom « Albert », on constate qu’il fait référence à =Data!$A$1:$C$2; Data!$B$6:$C$7; Data!$D$13:$E$15. C’est bien pratique quand on récupère un classeur venu d’ailleurs et que l’on veut vérifier la définition des noms.

Dans notre cas, nous constatons que le nom Albert fait référence à un bloc de trois matrices, l’ordre de ces matrices étant respectivement $A$1:$C$2 puis $B$6:$C$7 et enfin $D$13:$E$15. Cet ordre est particulièrement utile à connaître par exemple pour utiliser une formule telle que =index(Albert;n°lig;n°col;n°mat).

Comptage des trous dans une série

2011-06-15T13:33:00.003+02:00

Le problème suivant provident d’un lecteur du blog qui travaille dans une société financière.

On dispose, pour une série d’actions cotées en bourse, des cours – jour par jour – entre deux dates. Le but de l’opération est de calculer le nombre de valeurs manquantes entre le premier et le dernier cours cité.

Dans l’exemple ci-dessus, pour l’action A, il faut ainsi découvrir qu’entre la première cotation le 30 aoüt et la dernière le 10 septembre, il manque deux cotations.

Pour cela, nous avons calculé la position de la première valeur, celle de la dernière, et le nombre de valeurs entre ces deux positions.

Voici les formules des cellules F2 à F5 :

F2 : =min(si(estnum(B2:B19);ligne(B2:B19);10^6))

F3 : =equiv(10^6;B:B)

F4 : =nb(index(B:B;F2):index(B:B;F3))

F5 : =F3-F2+1-F4

Remarque – La formule de F2 est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée]…

Désactiver le copier/coller

2011-06-11T09:38:00.003+02:00

Parfois, vous aimeriez bien empêcher l’utilisateur d’un de vos classeurs de copier une partie de son contenu pour le coller ailleurs, dans un de ses classeurs par exemple.

Pour cela, il vous suffit d’écrire, au niveau du workbook (le classeur), la macro d’une ligne suivante :

Cette macro est toute bête, elle se contente de vider le contenu du Presse-papiers dès que vous désactivez la fenêtre du classeur.

Astucieux, n’est-ce pas ?

Mais cela n’empêchera pas l’utilisateur de votre classeur, s’il est astucieux lui aussi, d’envoyer dans le classeur de son choix une copie de la feuille active grâce à la commande « Déplacer ou copier… » qui apparaît dès que l’on fait un clic droit sur n’importe quel onglet du classeur actif.

Ou encore, il peut, à partir d'un autre classeur, écrire en relatif une formule faisant référence à la cellule A1 de la feuille active de votre classeur et la recopier partout dans sa feuille pour obtenir une copie du contenu de cette feuille...

Pour bien vous protéger, il vous faudra donc aller plus loin encore…

La propriété « Text » d’une cellule

2011-06-07T09:34:00.005+02:00

Nous avons écrit une toute petite macro, activée par le bouton ci-dessous, qui affiche le contenu de la cellule B1. Voici cette macro :

Sub Affiche()

MsgBox "Contenu de la cellule B1 : " & Range("B1").Text

End Sub

La particularité de cette macro est qu’elle utilise une propriété peu connue du
« range ». Il s’agit de la propriété « Text » qui renvoie non seulement le contenu de la formule mais aussi son format numérique, s’il s’agit d’une valeur.

C’est autrement plus léger que si la macro devait spécifier l’affichage au format « € » dans le msgbox…

Remarque 1 – Cette propriété est en read-only : on peut la consulter, mais on ne peut pas la modifier…

Remarque 2 – La propriété « Text » ne fonctionne que si l’objet du range est une cellule unique.

Déplacement avec le bouton droit

2011-06-03T08:55:00.005+02:00

Quand un bloc est sélectionné et que le curseur passe sur l’un des quatre segments délimitant la sélection, il prend la forme du curseur de déplacement, la croix fine avec ses quatre flèches.

Si l’on déplace alors le bloc en maintenant le bouton droit de la souris enfoncé, à la place du bouton gauche, on obtient l’écran suivant dès que l’on lâche le bouton (ici, avec le résultat en H2) :

On constate donc que l’on a sélectionné le bloc D14:E16, que le résultat se logera en H2:I4, et que – selon l’option que nous aurons choisie – nous pourrons le déplacer (Placer ici), le coller (Copier ici), ne coller que les valeurs (Copier ici les valeurs seules), …

Avec l’option « Créer un lien », on mettra en H2 la formule =D14, qui sera alors reproduite dans le reste du bloc.

L’option « Créer un lien hypertexte » met dans le bloc H2:I4 un lien hypertexte vers D14, seule la cellule H2 montrant de façon visible la présence de ce lien.

Je vous laisse expérimenter avec les quatre commandes du second bloc, dont l’intérêt pratique ne me semble pas évident…

Remarque 1 – Si vous « déplacez » le bloc en le remettant ensuite à sa place originale, vous pouvez – en sélectionnant alors l’option « Copier ici les valeurs seules » – remplacer le bloc par ses valeurs. C’est une façon rapide d’effectuer l’équivalent d’un [Ctrl]-c sur le bloc lui-même, suivi d’un collage spécial des valeurs…

Remarque 2 – Dans les cinq commandes contenant le verbe « copier », il faudrait utiliser le terme « Coller » à la place de « Copier ». Une fois de plus, Microsoft a oublié (ou fait l’économie) de faire relire tout cela par quelqu’un de compétent :(