Monsieur Excel
Pour tout savoir faire sur Excel !

19 avril 2014

« Power user » en deux jours !

Suite à une demande récente, j’organise le lundi 28 et le mercredi 30 avril une formation « Modélisation avec Excel ». Avec cette formation, je garantis de transformer n’importe quel utilisateur courant d’Excel en « power user », c’est-à-dire quelqu’un se situant dans le top 5% des utilisateurs d’Excel. J’anime cette formation depuis vingt ans, et on ne m’a jamais demandé le remboursement !

Ne confondez pas avec les formations « Excel avancé » que vous trouverez sur le marché et pour lesquelles, le plus souvent, une utilisation avancée d’Excel consiste à connaître la fonction recherchev(). La grande majorité de ces formations sont animées par des personnes qui ne sont pas comme moi des utilisateurs quotidiens d’Excel.

J’ai développé plus de 1.000 modèles pour plus de 100 entreprises dans plus de 10 pays. Et c’est cette expérience rarissime, même au niveau mondial, que je partage avec les personnes qui participent à mon séminaire.

Vous y apprendrez comment créer des graphes bien meilleurs que ceux que vous utilisez couramment, comment mettre de la flexibilité dans vos modèles, comment économiser du temps de calcul et de la place mémoire, pourquoi les tableaux croisés dynamiques ne sont pas une panacée et par quoi on peut – le plus souvent – les remplacer efficacement…

Date : 28 et 30 avril.
Lieu : Finance 3.1 – 9, avenue de l’Opéra – 75001 Paris.
Confirmez votre inscription à : thiriez@hec.fr

Venez avec votre ordinateur.
Une convention de formation peut être signée pour cette formation.

Quelques-unes de mes références pour cette formation en séminaire « intra », c’est-à-dire animé dans une entreprise : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, Lilly France, Marsh, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.


14 avril 2014

Découverte de la fonction Cellule()

Nous vous avons présenté la fonction informations() il y a cinq jours, la première fonction « orpheline » d’Excel présentée dans ce blog. Aujourd’hui, nous étudions une autre fonction orpheline, la fonction Cellule().

A en croire l’aide d’Excel, voici la syntaxe de cette fonction : cell(info_type,[reference])

Microsoft arrive à faire trois erreurs rien que dans cette syntaxe !

Tout d’abord, le nom de la fonction est « cellule » et non « cell », qui est le nom originel en anglais.

Ensuite, le séparateur de fonction en France est le « ; » et non la virgule.

Enfin, si l’on écrit « =cellule(adresse;) », c’est-à-dire en gardant le « ; » et sans mettre de second argument, on est bloqué par Excel : le « ; » devrait être à l’intérieur des crochets !

La bonne syntaxe est donc cellule(info_type[;référence])

Quand le second argument est absent, les renseignements récupérés par la fonction sont relatifs à la dernière cellule modifiée.

Nous avons testé cette formule en entrant la formule =pi() en B1 et la formule =cellule(A2;$B$1) en B2, formule recopiée dans les colonnes B et D.


Nous constatons une fois de plus que le travail de Microsoft a été bâclé : les arguments de A8 et A9 engendrent des erreurs car, comme pour la fonction informations(), chaque lettre accentuée doit être remplacée par son équivalent sans accent ! Il faut donc utiliser « parentheses » et « prefixe » en A8:A9 pour obtenir le résultat attendu.

Le statut un peu particulier des fonctions informations() et cellule() tient à ce qu’elles sont en fait des fonctions du langage macro d’Excel 4 et non des fonctions « naturelles » de la feuille de calcul. C’est probablement pour cette raison qu'elles elles sont si mal suivies par Microsoft.

En dehors de cela, nous constatons que l'argument « protection » ne marche pas...

Remarque 1 – L’argument « nomfichier » est mal nommé car on récupère en fait à la fois le chemin complet du fichier, le nom du fichier, et le nom de la feuille.

Remarque 2 – Comme pour la fonction informations(), les arguments en anglais fonctionnent très bien et garantissent la transportabilité de vos modèles.

Remarque 3 – Je tiens à souligner au passage que je trouve navrant que la plupart des livres en français sur Excel que j’ai pu consulter à ce jour reproduisent bêtement les arguments indiqués dans l’aide d’Excel, même quand ils ne marchent pas : les auteurs de ces livres n’ont manifestement pas pris le temps de tester ces fonctions.

Quatre changements depuis 2003…

Il y a eu quatre changements dans l’aide depuis Excel 2003. Tout d’abord, le nom de la fonction, qui était bon avec 2003 et qui est hélas devenu « CELL » depuis, comme nous l’avons vu plus haut…

Deuxièmement, l’argument en A2 était écrit « addresse » dans l’aide d’Excel 2003 et cela ne marchait pas si l’on ne corrigeait pas en « adresse » : ce changement est le seul changement bénéfique apparu dans l’aide. 

Troisièmement, l’argument « nomfichier » marchait très bien en Excel 2003, et il marche toujours très bien encore. Mais si, conformément à la version actuelle de l’aide, on entre « nom de fichier », cela ne marche plus !

Enfin, l’argument « protégé » ne marchait pas et il fallait entrer « protege » pour que cela marche. C’est toujours vrai actuellement. Mais Microsoft suggère à présent d’utiliser « protection » qui, lui, ne marche pas du tout.

Au bilan : quatre modifications ont été apportées à l’aide de la fonction « Cellule » depuis 2003, dont trois sont erronées. Et, pour tout arranger, les deux erreurs des codes en A8 et A9 n’ont pas été réparées… Bravo, Microsoft !


09 avril 2014

La fonction Informations()

La fonction informations() est une de ces fonctions quasiment ignorées de tous sauf des professionnels du développement Excel. Elle est intéressante car elle permet de récupérer en direct des informations telles que le système d’exploitation, le mode de calcul utilisé ou même le classeur actif.

Cette fonction s’appelait info() jusqu’à Excel 2003 inclus. Elle porte désormais ce nouveau nom…

On parle en médecine de maladies orphelines quand peu de patients à travers le monde en souffrent. On pourrait parler de fonctions orphelines quand peu de gens les connaissent et encore moins les utilisent. La fonction informations() est une fonction orpheline !

L’aide en ligne d’Excel nous en dit ceci :

  
Comme pour de nombreuses fonctions orphelines d’Excel – et ce encore plus que pour les autres fonctions, si c’est possible ! –, l’aide en ligne n’a jamais été relue chez Microsoft par quelqu’un de compétent, ou même de sérieux.


Remarque 1 – Comme pour toutes les fonctions un peu spéciales d’Excel, quand on utilise un argument comportant une lettre accentuée, comme en A1, Excel renvoie une erreur. Merci, Bill ! Il faut donc entrer « repertoire » en A1 pour obtenir en B1 le même résultat qu’en E1.

Remarque 2 – Les deux exemples de références affichés en gros caractères et en gras dans la rubrique « cellule » sont des références illégales. Essayez donc d’atteindre (avec [Ctrl]-t, par exemple, ou en la tapant dans la zone « Nom ») l’adresse $A:$D$9 et vous constaterez qu’Excel vous lancera, à juste titre, un message d’erreur à la figure… Les résultats affichés en B3 et E3 n’ont donc aucun sens…

Remarque 3 – Mon conseil est de toujours utiliser les arguments en anglais. En effet, votre modèle est alors transportable dans le monde entier, ce qui ne serait pas le cas autrement car, à l’ouverture du classeur dans un autre environnement linguistique, les chaînes de caractères n’ont aucune raison d’être traduites en même temps que les noms de fonctions qui, elles, le sont naturellement.

Remarque 4 – Il y avait trois arguments bien utiles dans la fonction info(), les arguments « memavail », « memused » et « totmem ». Ces arguments ont hélas disparu lors de la transformation en informations(). Vous pouvez les voir à l’œuvre dans l’article « Découverte de la fonction Info() » du 6 décembre 2006.

04 avril 2014

Lire.Cellule (macro Excel 4)

Dans l’article  du 25 mars, nous avons vu comment afficher dans une cellule le contenu de la formule voisine à gauche. Pour cela, nous avons utilisé une macro Excel 4.

Aujourd’hui, nous nous intéressons à une autre macro Excel 4, la commande Lire.cellule, dont voici la syntaxe : LIRE.CELLULE(no_type; référence).

Le second argument, référence, fait référence à une cellule ou à un ensemble de cellules.

Si l’on en croit la documentation d’Excel 4 publiée en 1992 par Microsoft, le premier argument – no_type – peut prendre les valeurs de 1 à 52. Ce n’est pourtant pas faute de place puisque ce livre possède la particularité de se terminer par une vingtaine de pages blanches ! (pour combler les trous de ce type ?)

En réalité, il y a 66 arguments possibles, dont voici la liste exhaustive. Faute de place, je les ai décrits ici brièvement ; si vous voulez en savoir plus, consultez le site suivant :

Pour illustrer l’utilisation de cette commande, j’ai défini le nom Lire_Cell avec =lire.cellule(ligne();!$A$5).

Puis j’ai entré en B1, recopiée jusqu’en B66, la formule =lire_cell.

J’ai mis en fond jaune, en colonne D, les résultats les plus utiles. Et, en fond ocre, les deux commandes les plus étranges.



Ce qui est extraordinaire, c’est la richesse des renseignements que l’on peut ainsi obtenir sur la référence visée. On récupère non seulement son contenu mais aussi des renseignements sur sa police, son encadrement, sa couleur…

Calcul non automatique

Attention ! Certains de ces renseignements ne s’actualisent pas avec [F9]. Ainsi, quand vous modifiez la valeur de la cellule, par exemple en changeant une cellule dont elle dépend, la cellule B6 s’actualise aussitôt.

Mais, si vous modifiez son encadrement, les cellules B9 :B12 ne bougent pas, même si vous lancez alors un recalcul avec [F9].

Pour qu’elles se recalculent, il faut faire un « calcul complet » avec [Ctrl]-[Alt]-[F9]. C’est aussi le cas pour certaines des autres caractéristiques récupérées par Lire.Cellule.


30 mars 2014

La formule active en commentaire

Dans le dernier article, nous avons vu comment afficher dans une cellule le texte de la formule de la cellule voisine à gauche.

Cette solution est assez intéressante pour les personnes qui, comme moi, animent des séminaires de formation autour d’Excel.

En ce qui me concerne, j’enseigne aussi fréquemment à des clientèles internationales. Il m’arrive donc souvent d’utiliser mon Excel en français pour animer une formation en anglais. Dans ce cas, quand j’entre dans mon Excel français une formule un peu complexe dans une cellule, outre le fait que je dis en anglais ce qu’il faut entrer dans la cellule, je renforce mon message en affichant dans la cellule un commentaire avec la formule en anglais, comme vous pouvez le voir dans la copie d’écran ci-dessus.

J’ai pour cela mis dans mon classeur de macros personnelles la macro suivante, à laquelle j’ai attribué un raccourci permettant de l’appeler par [Ctrl]-f :

Sub Formule_comm()
'
' Touche de raccourci du clavier: Ctrl+f
'
    ActiveCell.ClearComments
    ActiveCell.AddComment
    ActiveCell.Comment.Text Text:=ActiveCell.Formula
    ActiveCell.Comment.Visible = True
End Sub

Remarque – Il faut commencer par effacer le commentaire actuel, au cas où il y en aurait un. En effet, quand une cellule possède déjà un commentaire, on ne peut pas lui en ajouter un…


Si l’on veut mettre en commentaire la formule en français, il suffit de remplacer « ActiveCell.Formula » par « ActiveCell.FormulaLocal ».

La seule chose que j’aurais aimé pouvoir faire aurait été de dimensionner le commentaire en fonction de la place prise par le texte… Si vous avez la solution, je suis preneur !



25 mars 2014

Formule de la cellule à gauche

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 aujourd’hui encore.

Reprenons le modèle publié dans l’article du 8 janvier 2013, en insérant une colonne avant la colonne D.

Mettons-nous en D1 et définissons le nom « Formule » comme dans la fenêtre ci-dessous, c’est-à-dire en faisant référence à la cellule placée à gauche, en relatif. Il suffit ensuite d’entrer la formule =formule dans la cellule D1 – et à la reproduire en D3, D5 et D7 – pour afficher dans la colonne D le contenu des formules de la colonne C.


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 placée juste à 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...

Remarque 3 – L'affichage de la formule ne permet hélas pas de savoir si la formule est matricielle ou non.



20 mars 2014

Validation sans formule matricielle

Comme nous l’avons vu dans l’article précédent, il y a un problème si une formule de validation de données est une formule matricielle : en effet, dans ce cas, la validation fonctionne bien au moment où elle est créée, mais elle ne fonctionne plus si le fichier est fermé, puis rouvert ensuite.

Quand elle ne fonctionne plus, on peut la réactiver en la re-définissant. Il suffit pour cela, la cellule étant active, de passer par la commande « Validations des données » de l’onglet « Données », puis de re-valider la validation par « OK ».

Pour éviter ces problèmes, une autre solution – quand elle est possible – consiste à utiliser pour la validation une formule qui n’est pas matricielle.

Dans le cas qui nous intéresse, c’est-à-dire pour ne valider que des contenus dans lesquels il n’y a aucun chiffre, on peut utiliser une formule courante, non matricielle, trouvée par Daniel Ferry.

La condition est la suivante :
=sierreur(recherche(1=1;0>--stxt(A1;ligne(decaler(A1;;;nbcar(A1)));1));1=1)

Reprenons l’exemple où « ABCD » est le contenu de A1.

LIGNE(DECALER(A1;;;NBCAR(A1))) donne {1;2;3;4}

STXT(A1;{1;2;3;4};1) donne {"A";"B";"C";"D"}

0>--{"A";"B";"C";"D"} donne {#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!}

Les deux 1=1 ne sont que des artifices pour obtenir VRAI en 3 caractères au lieu de 4… Cela me TRUE, comme aurait pu dire Coluche...

Remarque 1 – Eh oui ! Les développeurs sont une caste à part, ils trouvent un plaisir ineffable à gratter ne serait-ce qu'un caractère par rapport à ce que ferait le vulgum pecus.

RECHERCHE(vrai;{#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!}) donne #N/A

Et l’on obtient donc VRAI comme résultat.

Qu'arriverait-il s'il y avait un chiffre dans le texte saisi ?

Si l’on entrait « AB1D », avec donc la présence d’un chiffre...

0>--{"A";"B";"C";"D"} donnerait {#VALEUR!;#VALEUR!;1;#VALEUR!}

RECHERCHE(1=1;0>{#VALEUR!;#VALEUR!;1;#VALEUR!}) donnerait FAUX

Et =SIERREUR(FAUX;1=1) donnerait enfin FAUX car FAUX n’est pas une erreur !

Remarque 2 – Cet exemple est intéressant, car il nous permet de décortiquer comment fonctionne une formule certes puissante mais dont le moins que l'on puisse dire, c'est qu'elle n'est pas intuitive...