Refus des doublons : Solution

En fait, après de nombreux essais, j’ai enfin identifié la raison pour laquelle le problème présenté il y a quatre jours survenait.

Cela tient au fait, dont je ne comprends d’ailleurs pas la raison, que la formule de validation =estna(equiv(A13; Vérif!A:A;0)) n’est pas évaluée de la même façon dans la feuille de calcul et dans la validation de cellule !!!

Pour mettre cette différence en relief, j’ai entré dans la cellule C13 exactement la même formule que pour la validation. Si donc le contenu de la colonne C est évalué en FAUX, la saisie dans la cellule A13 devrait en toute logique être rejetée.

Eh bien, cela ne marchait pas avec =estna(equiv(A13;Vérif!A:A;0)). Je me suis dit que cela était peut-être du au fait d’avoir utilisé toute la colonne A dans l’Equiv. J’ai donc donné le nom Contrôle au bloc exact A2:A21 des données de la feuille Vérif. Et le contrôle de validation s’est enfin mis à fonctionner proprement.

J’ai ensuite modifié la définition du nom pour faire référence à A2:A31, afin de faire la place pour de nouvelles saisies, et le contrôle de validation s’est remis à ne plus marcher, alors que la formule dans la colonne C affichait bien le résultat FAUX !

J’ai redéfini Contrôle pour le bloc A2:A21, et la validation a remarché. J’ai ensuite effacé une des données du bloc A2:A21 et il s’est remis à ne plus marcher…

Conclusion – Quand il y a un ou plusieurs blancs dans le vecteur de recherche, la fonction Equiv ne donne pas le même résultat selon qu’elle est entrée dans une cellule ou dans un contrôle de validation. Allez donc savoir pourquoi !!!

Solution – Soit vous entrez comme je l’ai fait la formule de validation dans une colonne masquée de la feuille et vous remplacez la formule de validation par =C13, soit vous définissez un nom élastique pour la zone « Contrôle ». Cette dernière solution ne marchera bien que s’il n’y a pas de trou dans la zone « Contrôle »…

Refus des doublons : Problème

Une lectrice m’a demandé récemment comment, lors de la saisie d’un code dans la colonne 1 de la feuille « Saisie », vérifier si ce code était déjà présent dans la colonne 1 de la feuille « Vérif » et – dans ce cas – bloquer la saisie.

Pour cela, il faut évidemment utiliser la commande « Validation » du menu Données, une fonctionnalité très utile d’Excel que nous avons présentée de façon détaillée le 13 et le 18 janvier 2007. Si vous n’êtes pas déjà familier de la commande « Validation », nous vous conseillons de lire ces deux rubriques avant de continuer la lecture de celle-ci.

La solution qui semble naturelle est d’entrer, comme nous le voyons ci-dessous pour la cellule A13, un test vérifiant que la valeur saisie est bien différente de toutes celles du bloc « Contrôle », qui est défini par =Vérif!$A:$A et correspond donc à la première colonne de la feuille « Vérif ».

Notez que nous avons utilisé la formule =estna(equiv(A13;Contrôle;0)) au lieu de la formule =estna(equiv(A13;Vérif !A:A;0)) qui aurait été pourtant plus naturelle, en évitant la création d’un nom supplémentaire.

La raison en est que la validation de cellules ne vous permet pas de faire référence à une autre feuille, mais qu’elle autorise cependant l’utilisation d’un nom faisant référence à une autre feuille !

Eh bien, malgré cette précaution, notre solution ne marche pas : la validation laisse passer n’importe quelle saisie, que le code soit présent ou non dans la feuille « Vérif ».

Nous verrons dans quatre jours comment résoudre ce problème.

Saluer l’utilisateur lors de la saisie

Un lecteur m’a demandé s’il était possible d’afficher, quand un utilisateur modifie une cellule de la colonne 1, un message donné pendant 5 secondes dans la cellule à droite, saluant l’utilisateur. Ce message doit s’autodétruire après 5 secondes…

La solution se trouve en deux étapes : il faut créer une macro événementielle pour afficher le message et appeler cinq secondes plus tard une macro – placée, elle, dans un module normal – , qui efface ce message.

Voici la macro événementielle liée à la feuille :

Dans un module normal, il faut ensuite définir deux variables publiques et créer la macro « Efface » assurant l’effacement du message :

Public Ligne As String
Public Feuille As String
Sub Efface()
Sheets(Feuille).Cells(Ligne, 2).ClearContents
End Sub

Remarque – Il serait aisé de modifier le code pour que le message ne soit affiché que si l’on a cliqué dans une zone particulière autre que la colonne 1.

La synthèse des couleurs

Pour une fois, je me permets une rubrique ne concernant pas directement Excel. Il y a tout de même un lien avec le dernier article, dans lequel je parle des codes de couleurs dans les formats personnalisés.

Si, malgré ce faible rapport avec Excel, cet article vous a quand même intéressé, vous pouvez le signaler dans un commentaire.

Les huit couleurs de base dans Excel sont, dans l’ordre : Noir, Blanc, Rouge, Vert, Bleu, Jaune, Magenta (une sorte de rose), Cyan (une sorte de bleu-vert). Pourquoi celles-là et pas d’autres ?

Il y a deux façons de synthétiser la couleur, la synthèse additive et la synthèse soustractive.

En synthèse additive, celle que l’on obtient par exemple en éclairant une scène noire avec des spots, chaque spot que l’on allume « ajoute » une couleur. Les couleurs primaires de la synthèse additive sont le rouge, le vert et le bleu. Dans les premières télévisions couleur, il y avait un tube pour chacune des trois couleurs. En combinant l’intensité du rouge de 1 à 100, celle du bleu de 1 à 100 et celle du rouge de 1 à 100, on peut synthétiser un million de couleurs différentes. Avec 100% de rouge, de vert et de bleu, on obtient le blanc.

La synthèse soustractive correspond à l’imprimerie : on ne part plus d’une scène noire, mais d’un papier blanc. On n’éclaire plus avec des spots, mais on passe des couches d’encre. Chaque couche opacifie le fond blanc et la couleur que l’on observe au final est celle du blanc moins ce qu’ont opacifié les diverses couches d’encre. Si l’on passe du jaune, du magenta et du cyan, qui sont les couleurs primaires de l’imprimerie, il ne reste plus que du noir. Si vous posez sur une feuille de papier blanc des feuilles transparentes jaune, magenta et cyan, le résultat est par conséquent tout noir.

Ce qui est intéressant à noter, c’est que les couleurs primaires soustractives (celles de l’imprimerie) sont opposées aux couleurs primaires additives : le jaune (rouge + vert, donc opposé au bleu), le magenta (rouge + bleu, donc opposé au vert) et le cyan (vert + bleu, donc opposé au rouge).

Cela me fait plaisir de partager ces connaissances car, dans une de mes vies, j’ai travaillé dans le domaine de la synthèse de la couleur.

En conclusion, le choix des huit couleurs de base d’Excel correspond au noir et au blanc, les points de départ des deux synthèses, et aux trois couleurs primaires de chacune. Ce choix n’est donc pas dû aux préférences, en matière de couleur, d’un quelconque développeur de Seattle.…

Couleur d’un format personnalisé

Nous avons vu, dans le dernier message, que l’on pouvait sélectionner la couleur de la police, dans un format personnalisé, en utilisant la couleur entre crochets. Ceci est valable, selon l’aide d’Excel, pour seulement huit couleurs :

En fait, vous avez droit en la matière, selon Microsoft – à condition d’acheter les livres de Microsoft Press et de bien y chercher la réponse – à 16 couleurs, si vous utilisez dans votre format le code [couleurn] où n est un entier de 1 à 16.

Grâce à la macro ci-dessus, liée à une feuille dans laquelle j’ai attribué le nom « Couleur » à une cellule dans laquelle j’entre le numéro de couleur, on colore la cellule placée juste en dessous avec la couleur sélectionnée.

Mes essais m’ont permis de découvrir que, contrairement aux affirmations de Microsoft, on a en fait droit à 56 couleurs au lieu de 16. Pourquoi donc 56, qui n’est pas une puissance de 2, quand on sait que l’informatique fonctionne en base 2 ?

A mon avis, parce que – si l’on ajoute les 8 couleurs reconnues dans l’aide ci-dessus – on tombe alors sur 64, qui est bien une puissance de 2.

Les codes des formats numériques

Les lecteurs du blog me posent fréquemment des questions relatives aux formats numériques personnalisés. Voici donc quelques éléments relatifs à ces formats.

Le code anti-slash : \

Le code anti-slash « \ » précède un caractère d’imprimerie qui doit apparaître comme tel dans la cellule. Donc « \a » revient à la même chose que « ״a״ », comme nous le voyons ci-dessous dans les cellules B2 et B3.

Remarque – Il y a une particularité dans ce code, c’est qu’il reproduit comme tels les 1 ou 2 caractères qui le suivent, mais pas plus, ainsi que nous le constatons en regardant bien le format en C2. On peut donc utiliser indifféremment les formats 0,00\ \e\u\r\o\s, 0,00\ \eu\r\o\s ou 0,00\ \eu\ro\s mais le format 0,00\ euros est refusé !

Le code underscore : _

Le code underscore « _ » réserve un espace de la même taille que le caractère qui suit l’underscore. C’est bien pratique pour aligner à droite des nombres dont certains possèdent un format et d’autres pas, comme nous le voyons en B4 et B5.

La ligne 9 montre une application de ce code dans le cas où – dans la même colonne – les valeurs négatives sont présentées entourées de parenthèses. On obtient alors l’alignement à droite de la valeur avec celle de la ligne 8.

Le code arobase : @

Le code arobase « @ » est utilisé représenter le contenu texte de la cellule et lui ajouter un texte supplémentaire, ainsi que le montre la ligne 7 de notre exemple. On peut encore ajouter du texte entre guillemets après l’arobase…

Format personnalisé ou formule ?

Dans mon message du 20 octobre 2005, j’ai déjà présenté les formats personnalisés d’Excel. Et j’ai eu l’occasion d’en donner d’autres exemples à diverses reprises.

Des lecteurs me demandent régulièrement comment définir des formats personnalisés de diverses natures, soit simplement pour des questions de présentation, soit pour faire des rapprochements avec des états produits par d’autres logiciels qu’Excel.

Dans certains cas, on peut s’en tirer avec un format personnalisé. D’autres fois, il faut passer par une formule. Voici deux exemples de problèmes posés par des lecteurs du blog.

Comment avoir un format divisant par 100.000 ?

Supposons que vous ayez dans une cellule la valeur 1 234 567 et vous voulez absolument la présenter sous la forme 12.34 ce qui revient grosso modo à la diviser par 100.000 et aussi à utiliser le point à la place de la virgule pour la décimale.

La solution se trouve ci-dessous, où nous avons entré les valeurs originales en colonne A et la formule =A2 en B2.

Remarque 1 – Le format de la cellule B2 est #"."00 "". En fait, les deux derniers guillemets sont superflus, nous ne les avons mis ici que pour mettre en relief l’espace final qui opère une division par 1.000.

Comment représenter « 1.234,6 » sous la forme « 1,234.60 » ?

Là hélas, on ne peut pas – par simple format – utiliser la présentation numérique américaine. Il faut passer par la formule :

=ent(A3/1000)&","&texte(ent(mod(A3;1000));"000")&"."&texte(100*(A3-ent(A3);"00")

Remarque 2 – Comme cela donne du texte, il faut le cadrer à droite si l’on veut le présenter comme une valeur…

Noter la date de la dernière saisie

Dans un classeur, vous souhaitez noter la date de la première et de la dernière saisie d’une valeur dans une colonne donnée.

Dans l’exemple ci-dessous, on souhaite qu’à la première saisie d’une valeur dans la colonne B, la date et l’heure soient automatiquement affichés dans la colonne C, et que cela vienne dans la colonne D pour toute modification ultérieure de la valeur.

La solution est de créer une macro événementielle attachée à la feuille de calcul. Voici le code de cette macro :

Il ne vous reste plus qu’à masquer les colonnes C et D si vous ne souhaitez pas que l’utilisateur du modèle ne voie pas ces dates.