Liste à 3 étages et même plus

Les solutions que je vous ai proposées dans les articles précédents fonctionnent très bien pour les listes à deux étages, et représentaient une excellente occasion pour vous de pratiquer la fonction decaler() ! L’inconvénient, c’est que ces solutions ne s’étendent pas facilement à la construction de listes à 3 étages, 4 étages ou même plus !

Pour obtenir une solution totalement générique, il faut ajouter à notre exemple une première ligne de titres (cf. D1 à K1 sur la copie d’écran) puis, pour chacune de des colonnes, donner le nom de la première cellule au bloc (un nom élastique, bien entendu !) formé par les éléments suivants de la colonne.

Dans notre exemple, nous avons donc défini les références suivantes pour les listes déroulantes :

B1 : =continents

B2 : =indirect(B1)

B3 : =indirect(B2)

Cela simplifie aussi le format conditionnel introduit dans le dernier article. De fait, la condition pour remplir en noir B2 devient alors : =estna(equiv(B2; indirect(B1);0)). Notez que ce format conditionnel fonctionne pour les deux cellules du bloc B2:B3 !

En conclusion, nous avons avec cette nouvelle solution la responsabilité de créer un nom pour chaque colonne, mais nous y avons gagné l’existence de listes hiérachiques à autant d’étages  que nécessaire !

Liste à deux étages améliorée

Un problème de la liste déroulante à deux étages est illustré par la copie d’écran ci-dessous …

Vous avez sélectionné Europe en B1 puis France en B2. Tout va bien !

Puis vous modifiez B1 pour choisir l’Amérique…

Le problème est que vous avez l’Amérique en continent et la France en pays, ce qui pose un problème et devrait – si votre modèle est bien conçu – alerter l’utilisateur.

Certes, vous pourriez ajouter une macro événementielle qui, dès que l’on modifie B1, efface B2.

Pour ma part, je n’utilise les macros événementielles que dans les situations où je n’ai pas d’autre choix. Or, ici, nous avons une autre solution, bien plus simple.

Il suffit en effet de donner à la cellule B2 un format conditionnel affichant un fond noir dès que le pays en question n’appartient pas au continent affiché en B1. Nous voyons en bas de la copie d'écran comment ce format conditionnel a été défiini.

Maintenant, la cellule B2 est toute noire, on voit immédiatement l’erreur et on la corrige, soit en effaçant B2, soit en sélectionnant un pays correspondant bien au nouveau continent.

+6,25 % de productivité !

Récemment, je travaillais dans une petite PME (1,5 M€ de CA) avec une attachée de direction qui avait un souci : tous les jours, elle passe 15 minutes en moyenne à effectuer à la main une répartition comptable des factures de la société. Il faut éclater les factures, les répartir dans une dizaine d’onglets et – à l’intérieur de chacun de ces onglets – effectuer un tri, puis insérer une ligne de rupture entre chaque bloc de factures.

D’une part, toutes ces opérations étaient effectuées à la main, ce qui non seulement prenait du temps mais – surtout ! – présentait un risque d’erreur dans la mesure où des interruptions pour répondre au téléphone n’étaient pas rares et pouvaient ensuite provoquer des distractions.

Il m'a fallu moins de deux heures pour analyser le problème en détail, en mesurer tous les tenants et aboutissants, et réaliser une macro qui effectue la totalité du travail de façon automatique et totalement sécurisée en moins d’une seconde.

Sachant que cette personne travaille à mi-temps, cette macro lui économise un quart d’heure par jour de 4 heures, soit 1/16ème de son temps. En conclusion, mon intervention a permis une augmentation de productivité de 6,25% en deux heures, sans parler de la sécurité accrue.

Ce petit exemple illustre l’apport extraordinaire que peuvent apporter à votre travail quelques macros bien choisies. En outre, il montre qu’un consultant peut être utile et rentable pour des entreprises de toute taille. Même si, et c’est bien logique, plus l’entreprise est grosse, plus il peut lui faire gagner – ou économiser – de l’argent…

Certes, la grande majorité de mes clients sont de grandes entreprises. Pour ne citer que quelques exemples : Aéroports de Paris, BNP, Dassault Systèmes, EADS, Electricité de France, France Telecom, Gaz de France, Sanofi, Total… Mais j’ai quand même trois clients avec chacun moins de 10 salariés.

Si vous voulez en savoir plus sur les gains que l’on peut obtenir avec de bons conseils, lisez donc les deux articles suivants dans ce blog :

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

Vous y trouverez divers exemples dans trois domaines : les économies possibles dans les temps de développement des modèles, les économies possibles dans les temps de calcul des modèles, et enfin les réductions possibles dans les tailles des modèles.

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

Dans ce second article, vous trouverez la description de trois exemples vécus d’audit et amélioration de modèles existants.

Une autre liste à deux étages

Dans le dernier article, nous avons vu comment créer une liste déroulante à deux étages à l’aide d’une macro événementielle.

Comme nous l’avons souligné dans cet article, cela présente l’inconvénient de devoir modifier la macro dès que les données de la liste doivent être mise à jour. Cela n’est pas souhaitable, a fortiori pour une utilisation en entreprise avec d’autres utilisateurs potentiels.

Aujourd’hui, nous voyons comment créer une liste déroulante à deux étages ne nécessitant aucune programmation. En revanche – on n’a rien sans rien ! – il va quand même falloir définir des noms qui ne sont pas piqués des hannetons…

Pour commencer, nous avons entré en C1 la formule =equiv(B1;D:D;0), qui renvoie le numéro du continent sélectionné dans la liste déroulante en B1.

La cellule B1 a été définie comme une zone de validation par liste avec la formule « =continent ».

La cellule B2 a été définie comme une zone de validation par liste avec la formule « =pays ».

La copie d’écran ci-dessous vous montre comment les noms continent et pays ont été définis.

Remarque – Les deux noms définis ci-dessus sont élastiques : vous pouvez ajouter un continent et il apparaîtra aussitôt dans la liste déroulante. S’il y a un nouveau continent, il suffit d’ajouter une colonne à droite pour lister ses pays. De même, tout ajout d’un nouveau pays est immédiatement répercuté dans la liste déroulante de son continent.

Liste déroulante à deux étages

Dans la cellule B1, vous avez créé, grâce à la commande Données – Validation – Liste, un menu déroulant avec les deux options « Fruit » et
« Légume ». Vous souhaitez définir en B2 une liste de validation différente selon le choix effectué en B1, comme dans l'exemple ci-dessous :

Vous pouvez résoudre ce problème de façon tout à fait esthétique grâce à la macro événementielle ci-dessous, attachée à la feuille de calcul concernée.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Liste As String

On Error Resume Next

    If Target = Range("B1") Then

        Application.EnableEvents = False

        Select Case Range("B1").Value

        Case "Légume"

            Liste = "Carotte,Tomate,Asperge"

        Case "Fruit"

            Liste = "Pomme,Fraises,Pêche,Abricots"

        End Select

        Range("Sélection").Validation.Modify Formula1:=Liste

        Range("Sélection").ClearContents

        Application.EnableEvents = True

    End If

End Sub

Remarque 1 – Notez, dans les chaînes de validation, l’utilisation de la virgule au lieu du point-virgule : en effet, le VBA est en anglais, langue dans laquelle c’est la virgule qui doit être utilisée à cet effet !

Remarque 2 – La cellule B2 porte le nom "Sélection", ce qui rend la macro stable même si la position de cette cellule est modifiée par la suite...

Remarque 3 – La solution proposée dans cet article fonctionne très bien mais présente deux inconvénients, la rigidité du code et la limitation à deux niveaux.

La rigidité du code tient à ce que les options sont inscrites dans le code VBA : il faut donc modifier le code si l’on désire renommer les options ou modifier leur nombre. Ce n’est pas très flexible, et tout le monde ne maîtrise pas le VBA…

Cette macro fonctionne pour une liste à deux niveaux. Cela deviendrait bien plus délicat avec une liste déroulante à trois niveaux.

Nous résoudrons ces problèmes dans des articles ultérieurs.