Monsieur Excel
Pour tout savoir faire sur Excel !

26 avril 2014

Le classeur, le chemin, et l'onglet

Nous venons d’apprendre à utiliser les fonctions orphelines cellule() et informations(). Voici un exemple où nous les utilisons pour récupérer séparément le nom du classeur, le nom de la feuille, le nom du chemin du classeur et enfin le nom du chemin « actif », c’est-à-dire celui dont on voit la liste des fichiers quand on passe par la commande « Ouvrir » du menu Fichier.


Pour A1 et B6, nous avons mis les formules en commentaire, il est donc superflu de les décrire ici.

Remarque 1 – Notez l'absence du second argument dans la formule de la cellule A1 : quand cet argument est omis, A1 fait référence à la feuille et au classeur de la dernière cellule modifiée, et donc pas nécessairement à cette feuille ni à ce classeur !

Voici donc les formules utilisées dans les autres cellules :
B3 : =stxt(A1;cherche("[";A1)+1;cherche("]";A1)-cherche("[";A1)-1)
B4 : =droite(A1;nbcar(A1)-cherche("]";A1))
B5 : =gauche(A1;cherche("[";A1)-1)

Ces formules sont assez simples car elles font référence à la cellule A1. Si l’on voulait tout calculer à chaque fois en une seule cellule, ces formules deviendraient plus lourdes. C’est le cas des formules de la colonne C :
C3 : =stxt(cellule("filename");cherche("[";cellule("filename"))+1;
cherche("]";cellule("filename"))-cherche("[";cellule("filename"))-1)
C4 : =droite(cellule("filename");nbcar(cellule("filename"))-cherche("]";cellule("filename")))
C5 : =gauche(cellule("filename");cherche("[";cellule("filename"))-1)

Remarque 2 – Il est intéressant de noter que, si l'on voulait récupérer les noms du classeur, de la feuille, ou le chemin, il faudrait normalement passer par des macros en VBA. Ici, nous obtenons le même résultat directement avec de simples (si l'on peut dire) formules...

Remarque 3 – Dans les versions antérieures d’Excel, la fonction informations() s’appelait info().

Rappel – Dans toutes les formules de la colonne C, il faut remplacer cellule("filename") par cellule("filename";$A$1) si l’on veut être sûr que l’on fait référence à la feuille active et non à celle de la dernière cellule modifiée.


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.