Monsieur Excel
Pour tout savoir faire sur Excel !

21 mai 2017

Localiser un nom élastique

Quand on a défini un nom élastique, on peut avoir envie de le sélectionner pour vérifier qu’il a été défini sans erreur. Hélas, quand on déroule la « Zone Nom », à gauche de a barre de formule, on découvre avec dépit que les noms élastiques ne sont pas complètement reconnus comme des noms par Excel et n’appartiennent donc pas à la liste.

Heureusement, Excel possède une commande géniale pour atteindre directement une zone nommée : [Ctrl]-t (pensez à Atteindre). Certes, quand vous l’activez, les noms dynamiques ne sont pas dans la liste des noms proposés, mais vous pouvez alors taper le nom concerné et cliquer dans le bouton « OK ».

Ceci dit, dans certaines circonstances, Excel reconnaît les noms élastiques comme des noms. Par exemple, quand vous créez une formule, la commande [F3] vous permet de coller un nom dans la formule. Eh bien, dans la liste des noms qui apparaît alors, les noms élastiques sont présents. Allez comprendre...

Pour ma part, je suis un fervent utilisateur de la commande « Atteindre ». Outre la possibilité que nous venons d’indiquer, cette commande vous donne accès, via son bouton « Cellules » – parmi la sélection de cellules en cours – aux cellules qui possèdent telle ou telle caractéristique :


13 mai 2017

Un bug dans les graphes

Il y a depuis Excel 2007, je crois – sinon, c’est depuis Excel 2010 – un bug dans les graphes d’Excel dont j’ai déjà d’ailleurs eu l’occasion de parler dans le passé dans ce blog. C’est qu’il n’est plus possible d’entrer directement dans la formule serie() un nom de série débutant par « L » ou « C », si celui-ci est un nom élastique (on dit aussi « dynamique »).

Supposons que vous ayez créé un second nom élastique, Ces_Y, avec exactement la même définition que dans l’article précédent. De bonne foi, vous sélectionnez la série, vous cliquez dans la barre de formule et vous remplacez le « M » de Mes_Y par « C ». Rien ne se passe, pas même un message d’erreur !

Vous cliquez alors dans « Filtres du graphique », la dernière icône à droite, vous activez la commande « Sélectionnez les données », vous cliquez sur le bouton « Modifier » et vous remplacez le « M » de Mes_Y par « C ». Et là, tout baigne, il n’y a aucun problème !

J’ai découvert ce problème chez un client auprès duquel j’avais ainsi rendu dynamique toute une série de graphes. Eh bien, ce problème survient chaque fois que le nom élastique débute par « C » ou « L » : Excel fait alors une confusion gigantesque avec Colonne et Ligne. La preuve, c’est que dans un Excel en anglais, c’est le « C » et le « R » qui sont touchés.

J’ai signalé ce bug à Microsoft depuis plusieurs années déjà, mais il n’est évidemment pas dans la liste des priorités…

08 mai 2017

Création d’un graphe élastique

Quand on crée un graphe dans Excel, on a parfois besoin que ce graphe soit « élastique », c’est-à-dire qu’il s’adapte automatiquement à l’apparition de nouvelles données. Prenons un exemple simple, nous avons saisi la formule =alea() en A1, nous l’avons recopiée vers le bas jusqu'en A10, et nous avons enfin tracé un graphe.


En ce qui nous concerne, le graphe a été défini par les valeurs de A1:A10, ce qui entraîne deux conséquences…

Si nous tirons A10 jusqu’en A13, le graphe reste bloqué sur A1:A10 et n’inclut pas les trois nouvelles valeurs.

Pire encore, si nous effaçons A8:A10 pour ne garder que les 7 premières valeurs, le graphe continue à afficher l’axe des X avec des valeurs de 1 à 7, laissant supposer que les trois dernières valeurs sont à 0.

Remarque – Certes, un observateur perspicace pourra dire que, si ces trois valeurs étaient à 0, cela serait marqué par un segment bleu sur l’axe des X. Mais, vous le savez comme moi, les gens perspicaces sont minoritaires…

Quand vous cliquez sur la série, la barre de formule affiche : =SERIE(;;Graphe!$A$1:$A$10;1).

En utilisant la technique présentée dans le dernier article, définissez le nom élastique Mes_Y par :
=Graphe!$A$1:decaler(Graphe!$A$1;nbval(Graphe!$A:$A)-1;0)

Allez alors dans la barre de formule et remplacez $A$1:$A$10  par mes_y.

Vous constatez alors que le graphe s’adapte immédiatement aux 7 valeurs restantes.

Resélectionnez le graphe et consultez la barre de formule. Vous pouvez voir que le nom de l’onglet a été remplacé par celui du classeur. Pourquoi ? Tout simplement parce qu’un nom est une propriété du classeur alors qu’une adresse (A1:A10) est une propriété de l’onglet !

Et voilà ! Vous avez à présent un graphe élastique.
Tirez A7 vers le bas et les nouvelles valeurs sont aussitôt intégrées dans le graphe.

01 mai 2017

Définition d’un nom élastique

Les noms élastiques ne sont pas souvent utilisés dans les modèles que je vois chez mes clients. J’avais déjà traité de ce sujet dans le passé, mais je pense qu’il mérite d’être revu car ces noms sont très utiles.

Un nom élastique, c’est un nom qui s’adapte automatiquement au nombre de données présentes. Supposons par exemple que nous disposions en colonne C d’une liste de pays. Nous utilisons la commande « Validation » du menu ou de l’onglet « Données » pour définir une liste de validation en A2, comme on peut le voir dans la copie d’écran ci-dessous :


Cela fonctionne très bien mais la liste est figée aux six pays du bloc C1:C6. Qu’arrive-t-il si nous souhaitons enrichir cette liste an ajoutant des pays ? Cela ne sera hélas pas répercuté dans le menu déroulant, sauf si l’on redéfinit l’adresse de la liste.

Pour éviter ce problème, il suffit de définir un nom dynamique identifiant les pays, à l’aide de la formule : =Elastique!$C$1:decaler(Elastique!$C$1; nbval(Elastique!$C:$C)-1;0) où « Elastique » est le nom de la feuille. Après avoir défini ce nom élastique, il ne vous reste plus qu’à remplacer dans la définition de la liste de validation =$C$1:$C$6 par =Pays.

Remarque 1 – Si vous oubliez le signe « = », la liste de validation ne vous donnera que « Pays » comme possibilité !

Et voilà ! Le tour est joué ! Ajoutez deux ou trois pays, déroulez le menu en A2, et constatez que les nouveaux pays sont automatiquement intégrés à la liste…

Remarque 2 – On pourrait se passer de la fonction decaler() en utilisant des tables. Mais l’utilisation de tables pose certains problèmes par ailleurs : je préfère donc les éviter dans ce type de situation. Si vous vouylez en savoir plus à ce sujet, lisez mon article du 9 juillet 2011.