Monsieur Excel
Pour tout savoir faire sur Excel !

29 octobre 2014

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 !


23 octobre 2014

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.



17 octobre 2014

+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.



12 octobre 2014

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.


07 octobre 2014

Liste déroulante à deux étages

Dans la cellule B1, vous avez créé, grâce à la commande DonnéesValidationListe, 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.



30 septembre 2014

La validation par liste

Une commande particulièrement utile d’Excel est la validation par liste, à laquelle on parvient par la commande « Validation des données » du bloc « Outils de données » du ruban « Données ».

Il suffit pour l’utiliser de choisir l’option « Liste » et d’indiquer la source. Dans notre exemple, nous avons ainsi défini la cellule C2 en indiquant la source A2:A1201.

Quand on sélectionne la cellule C2, on obtient alors une liste déroulante dans laquelle on peut sélectionner l’option voulue. Cette liste (cf. copie d’écran ci-dessous) affiche huit valeurs et – s’il y en a d'autres qui ne sont pas encore visibles – un ascenseur.


Limite de la liste déroulante

Jusqu’à la version 2007 incluse (à moins que ce ne soit 2003), il y avait un bug qui limitait la liste aux 1.000 premières valeurs de la liste. Le même bug apparaissait dans les listes déroulantes des tables et des bases de données.

Ce bug a heureusement été résolu depuis, et cette limite a enfin disparu.

Où sont les listes déroulantes ?

Il est parfois utile de retrouver dans une feuille de calcul, ou à l’intérieur d’une sélection, quelles sont les cellules comportant une liste déroulante.

Pour cela, utilisez le raccourci  [Ctrl]-t qui lance la commande « Atteindre », activez la commande « Cellules… », cochez l’option « Validation des données » et validez. Vous avez même la possibilité de ne sélectionner que les cellules dont la validation des données est identique, ce qui peut se révéler bien pratique.

Une liste déroulante dynamique

Supposons qu’une liste déroulante affiche tous les pays dans lesquels se trouvent vos clients. Vous souhaitez bien évidemment pouvoir l’actualiser quand un nouveau client vient d’un pays qui n’est pas encore dans votre liste.

Créons une liste de pays par exemple à partir de la cellule E1.

Définissons ensuite le nom dynamique « Pays » avec la formule : =decaler(Noms!$E$1;;;NBVAL(Noms!$E:$E))

Nous pouvons vérifier que ce nom correspond bien à la liste des pays en utilisant [Ctrl]-t et le nom « Pays » : nous constatons que la liste des pays est bien atteinte…

Il ne reste plus qu’à définir la zone de validation par liste avec « =pays » pour obtenir une liste déroulante dynamique des pays. Ajoutez un pays puis déroulez la liste : vous constaterez qu’il a été aussitôt intégré à la liste :)



24 septembre 2014

Nombre de modalités - Autre

Nous avons déjà publié il y a quelques jours un article intitulé « Calcul du nombre de modalités » où nous montrions comment calculer le nombre de valeurs différentes dans une colonne.

Cette fonction est en effet une fonction qui manque cruellement à la série des fonction BD…().

Aujourd’hui, nous rouvrons le sujet pour vous montrer deux nouvelles façons de définir cette fonction.

Formule de D1 (cf. article précédent) :
=somme(si(equiv(A2:A15;A:A;0)=ligne(A2:A15);1;0))
Formule de D2 (cf. article précédent) :
=somme(1*(equiv(A2:A15;A:A;0)=ligne(A2:A15)))
Formule de D3 : =sommeprod(1/nb.si(A2:A15;A2:A15))

Les deux premières formules sont matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée].


La première formule est assez compréhensible : quand le numéro de la ligne est le numéro de la première ligne où l’on trouve la valeur considérée, c’est la première occurrence de cette valeur et cela compte donc pour 1.

La seconde formule montre que l’on peut très bien remplacer le « si », dans une telle situation, par un simple produit par « 1 », qui a la vertu de transformer les « VRAI » en « 1 ».

La troisième formule présente le double avantage d’être plus compacte et de ne pas être une formule matricielle. Nous y notons une particularité : le second argument, au lieu d’être un élément unique, comme c’est normalement prévu dans le mode d’emploi de la fonction, est en fait un vecteur.

Quand nous évaluons la partie nb.si(A2:A15;A2:A15) de la formule, nous obtenons le vecteur {2;1;4;3;1;2;4;1;4;1;1;4;3;3}. On va donc compter 1/ 2 pour chaque élément apparaissant deux fois, 1/3 pour chaque élément apparaissant trois fois,… Au total, nous aurons donc le nombre de modalités.

Remarque 1 – Ce n’est pas la première fois que nous découvrons des ressources inespérées d’Excel en « détournant » le mode d’emploi officiel d’une fonction…

Remarque 2 – Il y a enfin, assez logiquement, une formule encore plus courte, mais de nouveau matricielle, avec =somme(1/nb.si(A2:A15;A2:A15)). Ce n'est pas surprenant car, comme nous l'avons déjà expliqué à plusieurs reprises, la fonction sommeprod() sert souvent à « éviter » une formule matricielle puisqu'elle génère naturellement une boucle...