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 !

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.

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

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.

Proposition de conseil gratuit

Dans votre activité professionnelle, vous avez certainement des modèles Excel que vous utilisez souvent et dont vous aimeriez améliorer les fonctionnalités, les rendre plus flexibles, plus rapides, plus conviviaux…

Ou alors vous avez besoin d’un modèle Excel particulier, mais vous ne savez pas trop comment le concevoir pour qu’il soit bien performant.

Ou enfin vous avez des modèles qui font à peu près le travail que l’on attend d’eux, mais qui sont trop lourds (grosse taille), trop lents, pas assez flexibles.

Dans chacun de ces trois cas, vous ne savez pas trop comment faire…

Je vous propose de vous rendre visite pour étudier avec vous le problème et vous aider à préciser vos objectifs. Je pourrai même, durant la réunion, vous montrer des exemples – externes ou sur votre modèle – de ce à quoi on peut arriver rapidement.

Mon intervention sera gratuite, dans la mesure où vous serez seul(e) avec moi : en effet, le but de cette opération n’est pas de faire de la formation gratuite à Excel.

Après notre réunion, je pourrai vous faire une proposition (temps et délais) de réalisation. Les développements que j’aurai effectués sur vos modèles durant cette réunion resteront ma propriété, sauf si vous décidez de les acquérir en rémunérant cette intervention à mon tarif habituel de conseil.

Si vous ne vous trouvez pas en région parisienne, nous pouvons prendre un premier contact avec WebEx ou un système de téléconférence.

Vous serez surpris de voir à quelle vitesse, et jusqu’à quel point, on peut améliorer des modèles existants. N’hésitez pas à me contacter : thiriez@hec.fr

Je vous conseille vivement la lecture des quatre articles suivants, qui vous montreront, dans l’ordre :

• ce que vous pouvez économiser dans la taille, et donc la performance, de vos fichiers ;
• le bénéfices impressionnants que vous pouvez obtenir avec le re-engineering de vos modèles ;
• tout ce qui touche à la modélisation, en particulier la modélisation avec Excel ;
• le potentiel surprenant de ce à quoi peut parvenir un audit efficace de modèles existants. 

« Attention à la taille de vos fichiers ! », le 23 octobre 2006

« Le re-engineering de vos modèles », le 3 mars 2009

« Réflexions générales sur la modélisation », le 28 octobre 2010

« Remarques sur l'audit de modèles », le 26 décembre 2011

RAZ de la mémoire des TCDs

Dans l’article précédent, nous avons vu comment – en quatre opérations – effacer la mémoire qu’un TCD a gardé de modalités anciennes pour un champ.

Cette solution présente deux inconvénients. En premier lieu, elle doit être reconduite si le problème se présente à nouveau. En second lieu, elle n’a pas d’effet général, car elle ne concerne que le champ (ici, le nom) pour lequel le problème est survenu.

Pour résoudre ce problème de façon définitive et empêcher le TCD de garder à l’avenir la mémoire des modalités disparues, il y a heureusement une solution, comme l'a dit Anne-Sophie en commentaire dans l'article précédent, devançant ainsi cet article que j'avais déjà rédigé...

Faites un clic droit dans le TCD et activez la commande « Options du tableau croisé dynamique ». Activez l’onglet « Données » de cette commande. Il y a un menu déroulant appelé « Nombre d’éléments à retenir par champ », qui propose trois options avec « Automatique » sélectionné par défaut.

Il suffit de remplacer cette option par « Aucun » pour résoudre le problème de façon définitive. Dorénavant, toutes les modalités disparues de tous les champs du TCD disparaîtront automatiquement de tous les menus déroulants du TCD.

Voici le sens des trois options possibles :

• Automatique : Le nombre par défaut des éléments uniques pour chaque champ.
• Aucun : Aucun élément unique par champ.
• Max. : Le nombre maximal d’éléments uniques pour chaque champ, ce qui marche jusqu'à concurrence de 1.048.576 éléments.