Formations de fin d'année

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

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

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

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

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

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

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

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

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

Couleur de fond automatique

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

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

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

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

La dernière cellule non vide

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

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

Une formule matricielle

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

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

Une formule originale

La seconde formule est doublement originale.

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

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

La formule la plus simple

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

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

Application.International(Index)

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

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

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

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

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

Pour une application multilingue...

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

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

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

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

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

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

Filtrage selon la couleur, sans 2007

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

Vous disposez alors de deux solutions simples :

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

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

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

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

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

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

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

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

Filtrage selon la couleur, avec 2007

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

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

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

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

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