Monsieur Excel
Pour tout savoir faire sur Excel !

18 juin 2019

Liste déroulante interactive (c)

Nous avons vu dans le dernier article comment limiter notre menu déroulant aux seules villes dont le nom contient une certaine chaîne de caractères, « el » en l’occurrence.

Ce qui est particulièrement intéressant, et auquel vous avez pu ne pas penser, c’est que l’on peut utiliser dans cette chaîne de caractères les deux fameux jokers, « ? » pour un caractère unique et « * » pour une chaîne de caractères de n’importe quelle longueur.

Utilisation du joker « ? »

Avec le joker « ? », si nous entrons la chaîne « o?o », nous obtenons toutes les villes dans lesquelles on peut trouver deux « o » séparés par exactement un caractère.

Nous constatons qu’il n’y a que trois villes dans notre liste qui respectent cette condition car il n’y a pas d’ascenseur à droite :


Utilisation du joker « * »

Avec le joker « ? », si nous entrons la chaîne « o*o », nous obtenons toutes les villes dans lesquelles on peut trouver deux « o » séparés par un caractère ou par plusieurs caractères.

Le menu déroulant nous affiche les 7 premières villes respectant cette condition mais l’ascenseur à droite nous indique qu’il y en a d’autres encore…


Remarques sur les jokers

En règle générale, les deux jokers « ? » et « * » fonctionnent très bien pour localiser – et c’est vrai aussi dans Word ou dans Powerpoint – des chaînes de caractères, comme nous venons de le voir avec nos deux exemples.

On peut seulement regretter qu’il n’y ait pas en plus – quand on utilise la commande « Remplacer » – deux autres caractères signifiant que l’on veut garder les lettres concernées par le « ? » et le « * » exactement comme elles l’étaient auparavant. Hélas, Excel entre littéralement des « ? » et des « * » dans le texte remplacé.

12 juin 2019

Liste déroulante interactive (b)

Nous voyons ci-dessous à quoi notre modèle ressemble quand nous tapons « el » en E2 pour que la liste déroulante ne nous propose ensuite que les villes visibles en colonne H, celles qui contiennent cette chaîne de caractères.


Pour que cette liste déroulante fonctionne comme nous le désirons, il faut d’abord qu’elle soit alimentée par uniquement les villes retenues en colonne H. Il faut aussi que l’on ait le droit de taper « el » en E2, ce qui n’est pas possible avec une liste déroulante normale.

Alimentation de la liste déroulante

Pour alimenter la liste déroulante, il faut d’abord définir un nom dynamique.

C’est ce que nous faisons avec le nom « Ville », défini par :
=Ville!$H$2:decaler(Ville!$H$1;MAX(Ville!$G:$G);0)

…où « Ville » est le nom de notre feuille de calcul.

Il ne reste plus qu’à définir en E2 une zone de validation, en sélectionnant l’option « Liste » et en entrant =Ville dans le champ « Source ».

Libération de la liste déroulante

Il faut enfin « libérer » la liste déroulante pour qu’elle nous autorise à saisir une chaîne de caractères – telle que « el » – qui ne soit pas l’une des options de la liste déroulante.

Pour cela, lors de la définition de la zone de validation de la cellule E2, il suffit de sélectionner l’onglet « Alerte d’erreur » et de décocher l’option « Quand des données non valides sont tapées », qui est cochée par défaut :


Et voilà ! Le tour est joué, vous pouvez à présent entrer « el » en E2 et ne plus voir dans la liste déroulante que les villes dont le nom contient cette chaîne de caractères…

05 juin 2019

Liste déroulante interactive (a)

Le sujet que nous allons traiter à présent a déjà été traité dans ce blog il y a 5 ans. Je rencontre souvent des gens qui trouvent cette idée géniale et qui pourtant ne connaissaient pas cette technique. Je craque donc en vous la représentant de nouveau, mais avec un article plus approfondi.

Vous utilisez une liste déroulante mais vous rencontrez l’un des deux problèmes suivants :
la liste déroulante n’est pas triée, et il est donc difficile de localiser ce que vous recherchez ;
la liste déroulante est très longue, et il est fastidieux de trouver ce que vous recherchez.

Nous allons prendre pour exemple une liste que j’ai trouvée sur Internet dans laquelle les 120 villes les plus peuplées des Etats-Unis sont listées.

Comme le montre bien la copie d’écran, il n’est pas facile de trouver la position d’une ville – par exemple La Nouvelle-Orléans – quand on n’a aucune idée de sa population…


Un début de solution

Pour parvenir à la solution, il faut utiliser deux colonnes à droite.

En G2, la cellule G1 étant vide, nous avons entré la formule :
=G1+SI(estnum(cherche($E$2;A2));1;0)

Cette formule est alors reproduite jusqu'à la ligne 121, la dernière de la liste.

En H2, reproduite jusqu'à la ligne 121 :
=sierreur(index(A:A;equiv(ligne()-1;G:G;0));"")

Le but du jeu est de voir comment, en utilisant ces deux colonnes, vous pourrez définir une liste déroulante interactive qui, si vous entrez « el » en E2, ne liste que les villes contenant cette chaîne de caractères :



28 mai 2019

Base de données (d)

Nous avons introduit dans le dernier article la notion de critère calculé.

Un nouveau critère calculé

Supposons que nous mettions à présent en F2 la formule suivante : =E6>E7.

Cela signifie que le salaire de George est supérieur à celui d’Alper. Sans surprise, nous constatons que la cellule affiche le résultat FAUX. En effet, les deux salaires étant égaux, la condition n’est pas satisfaite.


Question – A votre avis, quelles seront les personnes qui resteront affichées dans la base ? Essayez honnêtement de répondre avant de lire la suite…

Eh bien, ce seront les personnes de la base qui gagnent strictement plus la personne qui les suit dans la base. Vous le vérifierez rapidement. Pour vérifier cela, la solution est simple :

·         Filtrez la liste sur place
·         Mettez toutes les lignes qui restent affichées en police rouge
·         Arrêtez le filtre
Vous verrez alors que les « gagnants » sont en rouge et les perdants en bleu.

Avec la condition =E6>E7, nous avons gardé tous ceux qui gagnaient strictement plus que la personne suivante dans la base.

Si la condition avait été =E6>E$7, nous aurions gardé tous ceux qui gagnaient plus de 2.200 €.

Un exemple bien réaliste

L’exemple suivant est tiré d’une application que j’ai mis au point pour un de mes clients. Il s’agissait de sélectionner dans une base tous les employés de la société qui étaient salariés pendant une année donnée.

La troisième et la quatrième colonne servaient à vérifier que l’employé en question était bien salarié par la société durant l’année en cours. Pour cela, il fallait que sa date d’entrée dans la société soit antérieure à la fin de l’année en cours, et que sa date de sortie ne soit pas renseignée, ou qu’elle soit postérieure à la fin de l’année en cours.

En guise de conclusion

Quand je vois comment mes clients utilisent les bases de données avec des zones de critères, je constate que la plupart d’entre eux ignorent les critères calculés.

En conséquence, ils sont obligés d’ajouter à la base une colonne supplémentaire qui calcule la condition à appliquer et ils créent alors un critère normal en fonction de cette nouvelle colonne.

Cette solution fonctionne, mais elle alourdit inutilement la base…

21 mai 2019

Base de données (c)

Nous avons vu dans le dernier article comment utiliser une zone de critères. Une telle zone comporte au moins deux lignes et une colonne. La ligne du haut contient le nom des champs concernés et la ligne du bas les conditions à respecter.

Quand il y a plusieurs lignes et plusieurs colonnes, chaque ligne représente un « OU » et chaque colonne un « ET ».

On peut aller plus loin en utilisant aussi des critères calculés.

Un critère calculé

Pour utiliser un critère calculé, il faut absolument respecter deux critères :
  • Un nom de champ différent de ceux des champs de la base
  •  La condition doit porter sur la ligne de la première fiche de la base
Pour respecter la seconde condition, nous devons donc la formuler en fonction de George.

C’est le fait que le nom du critère n’est pas celui d’un champ de la base qui indique à Excel qu’il s’agit d’un critère calculé et non d’un critère naturel.

Remarque 1 – Si nous voulions créer un critère calculé en fonction du salaire, nous n’avons pas le droit de lui donner le nom « Salaire » mais rien en nous empêche de lui donner un nom avec ce même texte précédé et suivi d’un espace ! Ce ne sera alors plus le nom d’un champ de la base : )

La copie d’écran suivante montre comment l’on doit créer la zone de critères, en F1:F2. Nous avons reproduit en G2 le contenu de la formule en F2.


Remarque 2 – La zone de critères en F1:F2 joue exactement le même rôle que la zone A1:B3, en étant 3 fois plus compacte avec 2 cellules au lieu de 6. 

Ceci dit, nous ne vous conseillons pas d’utiliser un critère calculé quand un critère naturel est possible. En effet, les critères calculés sont moins « lisibles » (moins facilement "auditables") et risquent donc de troubler les utilisateurs.

15 mai 2019

De l'écologie pour changer...

Je ne résiste pas à l'envie de vous proposer pour une fois quelque chose qui n'a rien à voir avec Excel, mais qui est à mes yeux un des meilleurs arguments en faveur de l'écologie que j'aie jamais vus.

C'est un dessin publié aujourd'hui dans Les Echos.

Preuve que je ne lis pas que les publications scientifiques et techniques...





09 mai 2019

Modélisation avec Excel

Je vous propose dans deux semaines une formation « Modélisation avec Excel ». Vous pouvez voir le programme détaillé de cette formation en cliquant dans le gros bouton « Prochaines formations » dans le bandeau à droite de ce blog. Comme je fais surtout des formations intra ces derniers temps, je n’ai pas actualisé ce fichier, mais la description de la formation est toujours d’actualité.

Modélisation avec Excel (2 jours) : vendredi 24 mai et mardi 28 mai.
Lieu : Logma – 12, rue d’Anjou – 78000 Versailles
Heures : 9H00-17H00

Avec la formation « Modélisation avec Excel », un utilisateur moyen d’Excel passe en deux journées au niveau des 5% des meilleurs utilisateurs Excel. J’anime cette formation depuis des années et aucun participant n’a jamais contesté cela.

Vous découvrirez plein de choses sympathiques, comme la façon des réaliser des graphes élastiques avec des titres dynamiques, comment faire des listes déroulantes hiérarchiques, ou des listes déroulantes interactives, qui se modifient en fonction de caractères déjà saisis…

Toutes mes formations peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.
Ne manquez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en plus de 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

Pour toute question, écrivez-moi à thiriez@hec.fr