Liste dynamique des modalités
La liste des pays extraite dans l’article du 30 décembre – à l’instar de toute extraction – n’est évidemment pas dynamique : si l’on modifie le contenu de la base, elle ne se met pas à jour.
Est-il possible de construire une liste de modalités dynamique ?
La réponse est « Oui », au prix cependant d’une formule que seuls 1% (et je pense que je suis ici tout à fait optimiste) des utilisateurs d’Excel sauraient imaginer…
La formule de J6 est tout simplement =F6. Ce n'est bien entendu pas celle-ci qui est difficile :)
La formule de J7, reproduite ensuite jusqu’en J30, est la formule matricielle suivante :
=index(F:F;min(si(estna(equiv(F7:F$31;$H$6:H6;0));ligne(F7:F$31);100)))
Je vous laisse découvrir par vous-même pourquoi cela fonctionne...
Remarque 1 – Restent les inesthétiques « 0 » des lignes 25 à 30. Une première solution pour les masquer est d’utiliser soit un format personnalisé qui masque les 0, par exemple « ;; » (deux points-virgules), soit un format conditionnel attribuant une police blanche aux valeurs égales à 0.
Remarque 2 – Une solution plus simple encore, et là je vous laisse découvrir aussi pourquoi elle fonctionne, revient à entrer un simple espace comme contenu de la cellule F31...
Pour une fois, vous avez deux exercices stimulants pour démarrer la nouvelle année : comprendre la formule de J7 et répondre à la question posée dans la remarque n°2 …
Est-il possible de construire une liste de modalités dynamique ?
La réponse est « Oui », au prix cependant d’une formule que seuls 1% (et je pense que je suis ici tout à fait optimiste) des utilisateurs d’Excel sauraient imaginer…
La formule de J6 est tout simplement =F6. Ce n'est bien entendu pas celle-ci qui est difficile :)La formule de J7, reproduite ensuite jusqu’en J30, est la formule matricielle suivante :
=index(F:F;min(si(estna(equiv(F7:F$31;$H$6:H6;0));ligne(F7:F$31);100)))
Je vous laisse découvrir par vous-même pourquoi cela fonctionne...
Remarque 1 – Restent les inesthétiques « 0 » des lignes 25 à 30. Une première solution pour les masquer est d’utiliser soit un format personnalisé qui masque les 0, par exemple « ;; » (deux points-virgules), soit un format conditionnel attribuant une police blanche aux valeurs égales à 0.
Remarque 2 – Une solution plus simple encore, et là je vous laisse découvrir aussi pourquoi elle fonctionne, revient à entrer un simple espace comme contenu de la cellule F31...
Pour une fois, vous avez deux exercices stimulants pour démarrer la nouvelle année : comprendre la formule de J7 et répondre à la question posée dans la remarque n°2 …
rédigé par Hervé Thiriez |
6 commentaire(s)
6 Commentaire(s):
Sauf à être un fan des "casse tête mathématiques de Sam Loyd", je préfère, dans une optique opérationnelle s'entend, utiliser la (toute) petite macro suivante, indépendante de la taille des données :
Sub Modalités()
[A5].CurrentRegion.AdvancedFilter 2, , [J5], -1
End Sub
By
cduigou, sur 1:58 PM
Cher cduigou,
Votre macro fonctionne parfaitement bien, mais elle ne répond pas à notre objectif, qui est d'obtenir une liste dynamique, c'est-à-dire s'actualisant en temps réel.
Avec votre solution, il faut exécuter la macro chaque fois que la base a été modifiée. Ou alors la lier au recalcul, ce qui pose de nombreux autres problèmes...
By
Hervé Thiriez, sur 8:59 AM
C'est vrai qu'on ne bénéficie pas de l'automatisme de la formule. Une solution consiste à utiliser l'événement Change de la feuille de calcul :
Private Sub Worksheet_Change(ByVal Target As Range)
With [A5].CurrentRegion
If Not Union(Target, .Offset(1, 0).Columns(6)) Is Nothing Then .AdvancedFilter 2, , [J5], -1
End With
End Sub
Whaoo ! me voilà même désotmais membre de la confrérie des "one-liners" ! :)))
By
cduigou, sur 10:56 AM
Mea culpa, j'ai utilisé "Union" au lieu de "Intersect". C'est vrai que les "Maths modernes" sont un peu lointaines !
Il fallait donc lire :
Private Sub Worksheet_Change(ByVal Target As Range)
With [A5].CurrentRegion
If Not Intersect(Target, .Offset(1, 0).Columns(6)) Is Nothing Then .AdvancedFilter 2, , [J5], -1
End With
End Sub
By
cduigou, sur 11:59 AM
Pour éviter les "0", mettre "&""" à la fin de la formule matricielle
=index(F:F;min(si(estna(equiv(F7:F$31;$H$6:H6;0));ligne(F7:F$31);100)))&""
By
Anonyme, sur 4:44 PM
On aurait pu faire une pivot table mais c'est vrai qu'il faut faire un refresh a chaque fois
By
Laly, sur 2:03 PM
Enregistrer un commentaire
<< Accueil