Monsieur Excel
Pour tout savoir faire sur Excel !

29 novembre 2011

Deux façons de nettoyer du texte…

Nous avons en A1 un texte comportant des « scories » : nous souhaitons le nettoyer en ôtant les espaces superflus ainsi que les caractères qui sont ni alphabétiques ni numériques.

Nous voyons ci-dessous deux solutions totalement différentes…

En A3, une formule certes efficace, mais d’une lourdeur non négligeable !

En A5, une formule faisant référence à une fonction personnalisée définie par macro…

Remarque 1 – Les contrôles effectués par les deux formules ne sont pas identiques. Vous pourrez vous amuser à vérifier quelles sont les différences entre les deux solutions. Notre propos ici est d’illustrer deux approches totalement différentes, pas de réaliser deux solutions totalement équivalentes !

Remarque 2 – Pour simplifier, on peut dire que nous éliminons dans la première solution les caractères indésirables, alors que nous spécifions dans la seconde les caractères acceptables...

25 novembre 2011

Le contrôle de la validation

Pour compléter le sujet précédent – La validation de cellule – il est peut-être utile de préciser ce qui se passe avec les trois options de contrôle de cette validation, spécifiées dans l’onglet « Alerte d’erreur ».

Supposons que nous appliquions le contrôle proposé dans l’article précédent – un multiple de 7 – et supposons que l’utilisateur essaye de valider une saisie erronée. Une fenêtre d’alerte apparaît alors, dont la nature dépend de l’option – Arrêt, Avertissement ou Informations – sélectionnée par le développeur dans la case « Style ».

Remarque – On ne voit d’ailleurs pas bien pourquoi Microsoft a utilisé le terme
« Style » : « Type de contrôle », par exemple, aurait été bien plus approprié…

Nous voyons ci-dessus ce que donnent les trois options lors d’une saisie erronée :

Avec l’option « Arrêt », l’utilisateur n’a d’autre choix que d’annuler ou de réessayer…

Avec l’option « Avertissement », il peut passer outre via le bouton « Oui »…

Avec l’option « Informations », il passe outre en direct via le bouton « OK ».

22 novembre 2011

La validation de cellule

Le dernier article de ce blog portait sur le thème de la validation par liste dynamique. Il est peut-être temps, à cette occasion, de compléter un peu les trois articles déjà publiés il y a plus de quatre ans sur le thème général de la validation (et que vous pouvez lire ou relire avec profit) :

La validation de cellule (a), le 13 janvier 2007

La validation de cellule (b), le 18 janvier 2007

Une protection par validation, le 22 janvier 2007

Les écrans ci-dessous illustrent les différentes possibilités offertes par la validation soit, dans l’ordre : Nombre entier, Décimal, Liste, Date, Heure, Longueur de texte, Personnalisé.

Cette dernière est particulièrement puissante car la formule utilisée pour valider la saisie peut être aussi complexe que nécessaire.

Pour prendre un exemple simple, supposons par exemple que la cellule active soit la cellule C3 et que l’on souhaite exiger que l’utilisateur entre dans cette cellule un entier multiple de 7. Nous pourrions alors utiliser indifféremment l’une des deux formules suivantes :

=C3=7*ent(C3/7)

=mod(C3;7)=0

Si l’on voulait un multiple de 7 inférieur à 100, on pourrait écrire :

=et(C3=7*ent(C3/7);C3<100)

=et(mod(C3;7)=0;C3<100)

17 novembre 2011

Une validation par liste dynamique

Vous avez déjà probablement appris – surtout si vous êtes un familier de ce blog ! – comment utiliser la commande « Validation des données » de l’onglet
« Données ». Vous savez donc que l’on peut valider par une liste.

Dans la copie d’écran ci-dessous, nous avons une cellule A1 affichant actuellement « Pays » et dans laquelle nous souhaitons que l’utilisateur sélectionne un des pays listés en colonne C.

Nous avons défini le nom dynamique « Pays » (cf. première copie d’écran dans l’image ci-dessous). Puis nous avons défini une validation par liste dont la souce est « =pays » (cf. seconde copie d’écran dans l’image ci-dessous).

Il suffit alors de dérouler le menu en A1 pour voir apparaître la liste de sélection visible en haut de l’image ci-dessus.

Cette liste est totalement dynamique : si vous ajoutez un nouveau pays à la liste, le menu déroulant en A1 incorporera immédiatement ce nouveau pays !

Remarque – Un avantage collatéral – non négligeable ! – de cette utilisation d'un nom pour la liste de validation est que l'on n'est plus limité par la contrainte que la liste de validation se trouve dans la feuille active !

13 novembre 2011

Un nouveau bug de 2007 & 2010

Parmi mes activités actuelles, je suis en train de faire le re-engineering des fichiers de reporting d’un cabinet d’avocats réputé. Cela implique une vingtaine de classeurs Excel 2010.

J’étais en train de rendre dynamiques une bonne trentaine de graphes dans ces divers classeurs et je suis tombé plusieurs fois sur le problème suivant : la définition du nom dynamique s’effectuait sans problème, puis la commande
« Atteindre » me permettait de vérifier que le nom fonctionnait bien, et pourtant – quand je voulais remplacer dans la formule « =serie(…) » l’ancienne référence aux cellules par le nom dynamique –, rien ne se passait.

Cela signifie qu’Excel refusait de valider la formule, sans pour autant afficher le moindre message d’erreur.

Après divers recoupements, à force d’essuyer ces échecs, j’en ai découvert la cause. Excel 2007 et 2010 refusent tout simplement le remplacement dans la formule serie(...) des coordonnées de cellules par un nom dynamique si ce nom débute par « r » ou « c ».

Bien entendu, cela m’a fait penser à une immense confusion de la part de Microsoft avec les coordonnées en anglais, où « r » fait référence à « Row » – la ligne – et « c » à « Column » !

Pour le moment, je n’ai pas trouvé d’autre solution pratique que d’éviter que les noms dynamiques destinés à des graphes du même nom ne démarrent par « r » ou « c ».

Il y a bien une parade, mais elle n’est pas d’une simplicité biblique : ouvrez votre classeur en Excel 2003 (si cela n’y casse rien), transformez votre graphe en graphe dynamique sans restriction sur le nom dynamique, puis rouvrez-le avec Excel 2007 ou 2010 !

Quand on importe un graphe dynamique avec un nom démarrant soit par
« r » soit par « c », il n’y a pas de problème. Le problème ne survient qu’au moment où l’on cherche à valider la formule =serie(…) après y avoir modifié quelque chose !

Encore un bug à rajouter à la longue liste des bugs que j’ai découverts dans Excel…

Le premier bug que j’ai identifié dans notre produit favori – cela remonte aux débuts d’Excel ! – était que, quand on faisait un collage spécial avec addition dans un groupe de cellules, la valeur était bien ajoutée quand la cellule contenait déjà quelque chose, mais elle était soustraite quand la cellule était vide !

09 novembre 2011

Créer un graphe dynamique...

Nous avons vu, dans la dernière rubrique, comment il fallait s’y prendre pour créer un nom dynamique. Nous allons voir aujourd’hui comment créer un graphe dynamique, c’est-à-dire s’adaptant automatiquement au nombre des données présentes.

Remarque 1 – On peut aussi utiliser le terme « graphe élastique »…

Commençons par sélectionner le bloc de données en A1:A10 puis créons un graphe, en sélectionnant un simple histogramme. Bien entendu, ce graphe n’est pas dynamique : il ne s’adapte pas aux données puisqu’il continue à afficher 10 valeurs même si l’on ajoute ou détruit des données.

Pour rendre le graphe dynamique, sélectionnons-le puis, dans la formule =serie(...) dans la barre de formule, remplaçons $A$1:$A$10 par le nom dynamique « Valeurs » défini dans l’article précédent, et enfin validons par [Entrée].

Remarque 2 – Si nous n’avions pas gardé « Feuil1 ! » quand nous avons remplacé $A$1:$A$10 par « Valeurs », Excel aurait refusé notre modification.

Le graphe est à présent dynamique (ou élastique), ce que nous vérifions aisément en détruisant les trois dernières valeurs, puis en retirant la formule de A7 vers le bas jusqu’en A13, comme nous le voyons dans la copie d'écran ci-dessous.

Remarque 3 – Nous notons, si nous sélectionnons la série dans le graphe, que Feuil1!valeurs est devenu Class.xls!Valeurs, c’est-à-dire qu’Excel a remplacé le nom de la feuille par celui du classeur, ce qui est approprié, dans la mesure où un nom est par défaut défini au niveau du classeur.

Remarque 4 – Cet article et l’article précédent sont des actualisations de ce que j’avais déjà publié le 25 et le 29 novembre 2007.

04 novembre 2011

Création d'un nom dynamique

Une des clefs pour la création de modèles professionnels est l’utilisation de noms dynamiques, c’est-à-dire de noms qui s’adaptent automatiquement aux données présentes. Si l’on enlève ou ajoute des données, le nom s’adapte alors immédiatement…

Ici, nous avons utilisé les formules représentées dans le cartouche. Nous souhaitons créer un nom qui identifie les données de la colonne A, soit actuellement A1:A10. Mais nous voulons que ce nom soit dynamique : si l’on reproduit la formule de la cellule A10 en A11:A12, il faut que le nom identifie alors le bloc A1:A12; si l’on efface le bas de la colonne pour ne garder que les cellules A1:A7, il faut que le nom ne représente plus que ces sept cellules.

Pour cela, activez l’onglet « Formules » puis la commande Gestionnaire de noms, la commande « Nouveau… », saisissez le nom « Valeurs » et dans la zone « Fait référence à : », entrez : =Feuil1!$A$1:decaler(Feuil1!$A$1;Feuil1!$B$1-1;0).

Remarque 1 – Vous pourriez me dire qu’il est plus direct de passer par la commande « Définir un nom ». Un, cela ne gagne pas de temps car il faut appeler cette commande deux fois de suite. Deux, j’aime bien – quand je définis un nom – voir quels autres noms ont déjà été définis.

Comment vérifier que le nom est bien dynamique ?

Jusqu’à Excel 2000, la solution était simple… Il suffisait d’entrer dans une cellule la formule =somme(valeurs), puis de faire un double clic dans la cellule pour l’éditer : Excel sélectionnait alors exactement le bloc contenant les valeurs.

Malheureusement, pour les versions postérieures, cela ne marche plus. La moins mauvaise solution est d’utiliser le raccourci [Ctrl]-t (la commande
« Atteindre ») puis de taper le nom voulu et de valider par OK.

Remarque 2 – Vous noterez au passage que le nom ne se trouve pas dans la liste, et qu’il faut donc le taper. En effet, les noms dynamiques ne sont pas considérés par Excel comme de vrais noms !

Remarque 3 – Notre nom dynamique suppose que, dans la zone à nommer, il n’y ait aucune cellule vierge ou contenant du texte. Si nous avions utilisé nbval(), cela aurait résolu le problème des cellules contenant du texte mais, dans un cas tel que celui-ci, on ne souhaite de toute façon pas qu’il y ait du texte dans la zone nommée.