Monsieur Excel
Pour tout savoir faire sur Excel !

31 décembre 2005

Mes Meilleurs Voeux Pour 2006 !

Je profite de ce dernier message de l’année 2005 pour souhaiter à tous une Excellente Année 2006, pleine et heureuse.

Je tiens au passage à remercier aussi les entreprises qui m’ont fait confiance et m’ont demandé en 2005 de leur développer – ou de les aider à développer – des modèles Excel, avec ou sans Crystal Ball.

Voici les logos de ces entreprises, la seule absente (je n’ai pas réussi à trouver son logo sur Internet, mais cette entreprise est d’une discrétion notoire), étant la société Artémis.

27 décembre 2005

Le dernier jour du mois

Dans mes pérégrinations de consultant, je constate souvent que mes clients ont des difficultés pour obtenir, dans un modèle, le dernier jour d’un mois.

Nous pouvons résoudre cela d'une façon particulièrement astucieuse en utilisant la fonction date(an;mois;jour) qui crée une date correspondant à l’année, au mois et au jour indiqué.

Une particularité tout à fait intéressante de cette fonction est qu’elle n’est pas du tout gênée si vous lui demandez le 45ème jour du 27ème mois d’une année donnée.

La solution est alors d’une simplicité biblique…

Pour obtenir le dernier jour du mois relatif à une date donnée, il suffit de demander le 0ème jour du mois suivant :

La formule de B2 est : =date(annee(B1);mois(B1)+1;0)

Notez au passage que je saisis toujours les noms des fonctions en minuscules.

Ainsi, quand je fais une faute de frappe, je l’identifie très rapidement car la fonction erronée n’est pas convertie automatiquement en majuscules par Excel.

22 décembre 2005

Une bascule pour le quadrillage

Il m’arrive souvent de travailler sur des modèles développés par d’autres (clients ou élèves) et de souhaiter ôter le quadrillage, qui n’apporte pas grand-chose à Excel puisqu’il est si facile de réaliser de jolis encadrements.

Et il est vraiment fastidieux de devoir à chaque fois passer par la commande Outils – Options – Affichage – Quadrillage. On se demande d’ailleurs pourquoi aucun raccourci n’existe à cet effet alors qu’il y a de nombreux raccourcis dont on a peu l’occasion de se servir.

Je me suis donc créé un raccourci à cet effet, correspondant à une macro que j’ai logée dans le classeur de macros personnelles de chacun de mes ordinateurs. Pour cela, j’ai enregistré une macro, que j’ai demandé à Excel de loger dans le classeur de macros personnelles :

Puis j’ai utilisé le cheminement indiqué ci-dessus pour ôter le quadrillage, et enfin j’ai arrêté l'enregistrement de la macro. En consultant le résultat ainsi obtenu dans l’éditeur Visual Basic, voici ce que j’ai pu lire :

Sub Quadrillage()
'
' Quadrillage Macro
' Macro enregistrée le 22/12/2005 par Hervé M. Thiriez
'
' Touche de raccourci du clavier: Ctrl+q
'

ActiveWindow.DisplayGridlines = False
End Sub


Tout ce qu’il faut faire ensuite, c’est de remplacer la ligne exécutable unique de cette macro par :

ActiveWindow.DisplayGridlines = _
not(ActiveWindow.DisplayGridlines)

Et, bien entendu, ne surtout pas oublier de confirmer l’enregistrement du classeur de macros personnelles quand vous quitterez cette session d’Excel.

Et le tour est joué ! Vous avez maintenant une bascule pour le quadrillage, activée directement par le raccourci [Ctrl]-q !

18 décembre 2005

Calcul d'un montant TTC

Un problème récurrent dans les modèles comptables est le calcul d’un montant TTC à partir du montant HT et du taux de la TVA . Problème classique s’il en est…

Rien que pour résoudre un problème aussi simple, on peut facilement utiliser quatre approches différentes :


1. L’utilisation d’une constante cachée

C’est hélas à la fois la pire solution et une de celles que j’ai le plus souvent rencontrées dans les modèles que j’ai audités et améliorés chez mes clients.

La formule de B2 est dans ce cas : =B1*19,6%

Premier inconvénient : si le taux est modifié en A2, on peut oublier de le modifier aussi dans la formule et le modèle devient erroné.

Second inconvénient : si le taux change, il faut modifier la formule. Plus l’on est amené à modifier les formules d’un modèle, moins il est flexible…

2. Le traitement du texte placé en D2

On peut aussi utiliser une formule de traitement de texte d’Excel pour isoler la partie numérique de la cellule indiquant le taux de TVA. Ainsi, notre formule pour E2 est-elle : =E1*DROITE(D2;6).

Inconvénient : si le taux de TVA passe à 33,33%, notre article ayant été sauvagement classé dans la catégorie du luxe, seuls 3,33% seront pris en compte.

Certes, on pourrait aussi écrire =E1*STXT(D10;CHERCHE(" ";D2)+1;10) mais avouons que, là, notre recherche de flexibilité alourdit significativement la formule.

3. L’utilisation d’un format personnalisé

La solution la plus esthétique revient à utiliser un format personnalisé. Nous entrons directement en G2 la valeur 19,6%, puis nous lui attribuons le format personnalisé « "TVA "0,00% » et la formule de H2 s'écrit tout simplement =H1+H2 !

4. La solution la plus simple…

Il y a enfin une solution toute simple, même si elle est moins créative, qui consiste à expliciter le taux de TVA dans une cellule du tableau et à faire référence à cette cellule pour réaliser votre calcul…

17 décembre 2005

Leçons gratuites : WiseTrainer

Un commentaire a squatté sur mon blog pendant 24H, après mon message du 14 décembre, faisant la promotion de cours gratuits sur Internet. Par prudence, je l’ai effacé, le temps d’aller voir ce qu’il y avait sur ce site.

Il s’agit en effet de cours gratuits. La référence suivante correspond à une séquence de moins de dix minutes portant sur l’utilisation des références dans Excel :

http://www.wisetrainer.com/index.php?p=1848545846547062568771774786~86&cid=800

Le point positif, c’est que WiseTrainer vous offre des leçons gratuites sur Excel.

Le point négatif, c’est qu’il est difficile d’apprécier quelqu’un qui travaille avec Excel de la façon montrée en exemple par ce cours. En quelques minutes, le professeur a réussi à me convaincre – à plusieurs reprises – qu’il était tout sauf un utilisateur compétent d’Excel.

Pour commencer, ce professeur utilise le réglage pour les débiles de Microsoft (cf. mon message du 21 octobre 2005, un des tout premiers). Décidément, il débute mal !

Ensuite, il a recours au collage de fonction (le bouton fx) pour entrer la simple somme d’un bloc, là où l’outil Sigma fait la même chose bien plus rapidement.

Enfin, il passe par Insertion – Noms – Définir pour créer un nom. Même si c’est la façon de créer un nom prônée par l’aide d’Excel (cf. ci-dessous), c’est loin d’être la meilleure. Nous aurons l’occasion de reparler des définitions de noms dans un « post » ultérieur de ce blog.

Et, pour couronner le tout, il montre comme exemple d’une référence à une autre feuille de calcul une référence à la même feuille de calcul ! Je ne vois vraiment pas l’intérêt d’écrire « Feuil1!A1 » quand on se trouve déjà dans la feuille « Feuil1 »…

En tout cas, si ce cours ne m’a rien appris, il m’a bien fait rigoler :)

14 décembre 2005

La grand-mère de Bill Gates

La gestion des dates dans Excel est un peu particulière. Il faut savoir que – pour Excel – un jour représente une unité et une partie de jour une fraction de cette unité.

Dans le tableau ci-dessus, nous avons entré dans la colonne A, successivement, les formules :
En A1 : =2/3
En A2 : 1,25
En A3 : -1
En A4 : =aujourdhui()
En A5 : =maintenant()

Pour les formules de la colonne B, c’est tout simple. Celle de B1 est « =A1 » et elle a été recopiée jusqu’en B5.

Notez enfin que nous avons reformaté A4 et A5 avec l’outil « 000 » pour donner à ces cellules le format « # ##0,00 ».

Les cinq cellules de la colonne B ont ensuite reçu le format personnalisé indiqué en commentaire.

La première date d’Excel est celle de la date de naissance de la grand-mère de Bill Gates. On savait déjà qu’il était un peu extra-terrestre ; il n’est donc pas surprenant que sa grand-mère soit née un 0 janvier, date originale s’il en est.

On n’a pas le droit de remonter plus haut dans la généalogie de Bill, puisque (cf. A3) , toute date négative affiche une erreur non « typée » : en effet, elle n'est pas sous la forme #VALEUR !, qui aurait été nettement plus logique en la circonstance.

Notez que c’est le seul cas où vous récolterez une série de « # » sans que la largeur de la colonne ne soit en cause.

Nous allons maintenant découvrir que la grand-mère de Steve Jobs, elle, est née le 1er janvier 1904 (lui n’est donc pas extra-terrestre). Pour cela, nous passons par la séquence Outils – Options – Calcul puis nous cochons l’option « Calendrier depuis 1904 », qui nous met en mode « Excel Macintosh ».

Voici ce que devient alors notre modèle :

On constate aussitôt une autre étrangeté. Avec ce réglage, les dates négatives aboutissent à une date normale précédée du signe « - ». Allez donc savoir pourquoi !

PS – Si vous voulez surprendre un collègue, mettez discrètement son Excel en « Calendrier depuis 1904 » : il ne comprendra plus rien aux dates affichées dans ses modèles…

11 décembre 2005

Rendez vos sommes élastiques !

Mettons-nous face à une situation classique : vous avez les chiffres d’affaires de plusieurs produits – en colonne B – et vous souhaitez en faire la somme.


Tout naturellement, vous cliquez en B6 et vous utilisez l’outil Sigma pour obtenir automatiquement la formule de sommation adéquate.

Supposons à présent que vous ayez par la suite inséré deux nouveaux produits, le « 0 » et le « 5 », et nous voyons à droite le résultat ainsi obtenu...

Votre formule de sommation est devenue « =SOMME(B3:B6) », ce qui signifie que les deux nouvelles lignes n’ont pas été incluses dans la sommation. Et c'est tout à fait naturel : Excel n'avait aucune raison de prévoir cette évolution du modèle !

Comment rendre la somme élastique ?

Replaçons-nous dans la situation initiale, quand la formule de sommation devait être saisie dans la cellule B6. La formule à utiliser pour avoir une somme totalement élastique est la suivante :
=somme(B1:decaler(B6;-1;0))

On fait la somme depuis le texte en B1 – qui comptera pour zéro – jusqu’à la cellule placée une ligne au-dessus de B6, dans la même colonne.

Et le tour est joué !

Certes, votre formule est plus compliquée à élaborer, et moins lisible, mais elle est à présent d’une flexibilité totale !

08 décembre 2005

Réaliser un tri dynamique

Dans la rubrique précédente de ce blog, nous avons étudié le fonctionnement du bouton « Tri croissant » et de la commande « Trier » du menu Données

Bien entendu, le tri d'Excel n’est en aucun cas dynamique. Si vous modifiez une valeur, il vous faut refaire ce tri pour que le classement affiché respecte les nouvelles valeurs.

Dans certains cas, on aimerait bien avoir un tri dynamique, c’est-à-dire un tableau affichant le résultat, après le tri, en temps réel. Donc sans passer par la commande « Trier » du menu Données


Nous voyons le résultat de ce tri dynamique dans les colonnes D et E. Les formules de D2 et E2 utilisent une fonction relativement peu connue, la fonction petite.valeur().

Formule de D2 :
=index(A:A;equiv(E2;B:B;0))

Formule de E2 :
=petite.valeur(B:B;LIGNE()-1)

Il y a quand même un problème, c’est que la technique présentée ci-dessus suppose qu’il n’y ait pas de doublon dans la colonne B.

Quand c’est le cas, comme ici, la fonction equiv() ne ramène en effet que la première occurrence de « 60 », soit deux fois le nom « Michel ».

Il y a une façon de résoudre ce problème aussi, mais je dois avouer que c’est quand même compliqué ; et puis, il faut bien que je réserve certains secrets à mes clients :)

Voilà en tout cas un beau challenge pour vous…

05 décembre 2005

Le bouton « Tri croissant »

Nous connaissons tous les outils « Tri croissant » et « Tri décroissant » de la barre d’outils « Standard ». C’est un raccourci pour la forme la plus courante de la commande « Trier » du menu Données.

Quand une seule cellule est sélectionnée lors de l’utilisation du bouton « Tri croissant », Excel sélectionne l’ensemble du bloc rectangulaire auquel la cellule appartient et effectue un tri ascendant selon cette colonne (peu de gens le savent !).


Dans le cas ci-dessus, le curseur étant en B4, c’est un tri croissant de A1:B9 qui sera effectué, par âge et en traitant la première ligne comme des titres.

Si plus d’une cellule est sélectionnée, le tri ne s’opère que sur le bloc sélectionné, mais selon le même principe que ci-dessus, la cellule active déterminant le critère de tri.

Si l’on utilise la commande « Trier » du menu Données, on découvre des réglages supplémentaires, avec en particulier un bouton « Options… » que peu d’utilisateurs ont eu la curiosité de tester :


Ce bouton est intéressant car il nous permet de trier aussi de la gauche vers la droite, et de tenir compte ou non de la casse (Majuscules/Minuscules). Je vous laisse faire vos essais…

Vous découvrirez rapidement les problèmes liés à ces options, l’impossibilité de déclarer une colonne de titres, et le fait que le dernier réglage de ces options est mémorisé de façon automatique.

Pas de colonne de titres !

On s’attend à ce que le texte du bouton radio « Lignes de titre » se change en « Colonnes de titre » quand on a choisi de trier de gauche à droite. Que nenni !

Quand le tri s’effectue de gauche à droite, la colonne de titres n’est pas considérée comme telle. Il faut donc penser dans ce cas à sélectionner préalablement un bloc ne contenant pas ces titres !

Les options sont mémorisées

Pire encore, Excel ne réinitialise pas les options après que vous les ayez utilisées !

Ainsi, supposons que nous ayons fait un tri « de la gauche vers la droite » puis que, plus tard, nous voulions refaire un tri « normal », de haut en bas. Les deux boutons radio du bloc « Lignes de titre » sont hélas dorénavant grisés car, par défaut, vous restez bloqué sur l’option de tri de gauche à droite, la dernière utilisée.

Vous êtes donc obligé de repasser par le bouton « Options » pour rétablir le tri du haut en bas...

04 décembre 2005

Stratégie & Décisions le 8 déc.

Quatrième édition de « Stratégie & Décisions », le 8 décembre 2005 au CNIT.

La conférence « Stratégie & Décisions » est réservée en priorité aux utilisateurs de systèmes décisionnels en entreprises. Les éditeurs, cabinets de conseil et SSII peuvent demander à participer, mais leur inscription est soumise à un quota afin de préserver l'objectif premier de ce forum.

http://www.strategiedecisions.com/index.php?action=formulaire&id_form=1270

Vous pouvez essayer de vous inscrire si vous faites partie de la cible. Je ne sais pas s’il reste encore de la place ou non…

Je ferai une présentation dans l’après-midi sur l’avenir du tableur face aux décisionnels. Si certains d’entre vous sont déjà inscrits, nous aurons l’occasion de faire connaissance.

02 décembre 2005

Récupérer « 2 » depuis « '1+1 »

Suite à ma rubrique « Lire les arguments d’une fonction » du 30 novembre, un lecteur me demande comment faire pour qu’une cellule affiche « 1+1 » et qu’une autre cellule affiche la valeur « 2 » résultant de cette opération.

Tout d’abord, on pourrait penser à utiliser la fonction CNum() qui convertit en nombre une chaîne de caractères représentant un nombre. Ainsi, si la cellule F1 contient « '123 », la cellule contenant la formule = CNum(F1) affichera la valeur « 123 ».

Hélas, CNum('1+1) affiche le désagréable message #VALEUR!
Il nous faut donc trouver une autre solution.



Le tableau ci-dessus illustre cette solution. Nous avons entré « '1+1 » en A1 et C1, et « '123+45 » en A2 et C2. La seule différence entre A1 et C1 (ou B1 et D1) est que nous avons cadré à droite le bloc C1:D1, afin de plus « faire nombre ».

La formule de A2, recopiée ensuite vers la droite, est :
=gauche(A1;cherche("+";A1)-1)+stxt(A1;cherche("+";A1)+1;100)

Cette formule permet de récupérer n’importe quelle somme de deux nombres affichée en A1.