Monsieur Excel
Pour tout savoir faire sur Excel !

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...


15 mars 2014

Validation avec formule matricielle

Bill Jelen, un expert d’Excel,est tombé récemment sur un bug original que vous pouvez aisément reproduire.

En A1, activez la commande « Validations des données », onglet « Données », déroulez la commande « Autoriser » jusqu’à « Personnalisé » et entrez la formule : =nbcar(A1)-somme(--non(estnum(--stxt(A1;ligne(indirect(1&":"& nbcar(A1)));1))))<=0

Cette formule, nous verrons plus loin de quelle façon, aboutit au résultat « FAUX » si la cellule contient au moins un chiffre. S’il ne s’agit que de texte, le résultat est « VRAI » et est donc accepté par la validation.

Entrons « ABC » en A1. C'est accepté ! Entrons maintenant « A23 » et c’est refusé. Tout va donc bien.

Enregistrons le fichier et fermons-le. Ensuite, nous le rouvrons et essayons de nouveau d’entrer « ABC » en A1. Hélas, à présent, Excel refuse la saisie !

La seule façon de faire remarcher la validation est de la redéfinir, tout simplement en la revalidant…

Après diverses expérience, il semble donc que ce problème apparaît quand la formule utilisée pour la validation est une formule de type matriciel, même si elle n’a pas été saisie en tant que telle – donc avec avec [Ctrl]-[Maj]-[Entrée] – lors de la définition de la formule de validation. Ce qui ne l’avait pas empêché de fonctionner avant la sauvegarde initiale du fichier.

Comment fonctionne donc la formule de validation ?

Supposons que A1 contienne le texte « ABCD ».

LIGNE(INDIRECT(1&":"&NBCAR(A1))) donne {1;2;3;4}
STXT(A1;{1;2;3;4};1) donne {"A";"B";"C";"D"}
ESTNUM(--{"A";"B";"C";"D"}) donne {FAUX;FAUX;FAUX;FAUX}
--NON({FAUX;FAUX;FAUX;FAUX}) donne {1;1;1;1}
NBCAR(A1)-SOMME({1;1;1;1}) donne 0
Et =0<=0, enfin, donne VRAI

Si l’une des lettres était remplacée par un chiffre, il y aurait un VRAI dans la série des FAUX, et l’on obtiendrait au final 1, qui n’est pas inférieur ou égal à 0, donc un résultat FAUX pour la validation.