Explications filtre élaboré en cascade + listes déroulantes

Question

En recherchant sur internet un moyen de mettre en place un système de 3 listes déroulante en cascade, je suis tombé sur ce site : Mon objectif étant de faire en sorte qu'une fois un premier choix effectué sur la première liste, cela impact le contenu de la seconde, qui impactera celui de la troisième. L'ennui, c'est que malgré avoir téléchargé les différents fichiers explicatifs, je ne comprend pas comment mettre en place ces listes déroulantes dynamiques. Par quoi faut-il commencer? Dans son exemple, il y a 4 feuilles : recherche resultats critères données je souhaite faire la même chose sur 1 voire 2 feuilles (les critères doivent peut être se trouver sur une feuille à part ?) J'ai essayé d'adapter ce code à mon fichier mais je n'y parviens pas, je ne comprend pas l'intégralité de son fonctionnement. Voici mon fichier sans les données : Mon objectif est de sélectionner d'une copropriété avec en premier lieu la sélection d'un code commune, puis d'un code SC (section cadastrale) et enfin d'un code parcelle. Actuellement, mon fichier fonctionne grâce à une saisie manuelle dans les cellules en jaune par l'utilisateur, donc peu pratique. Ce que je voudrai obtenir, c'est 3 listes déroulantes à la place et que lorsque l'on sélectionne une commune, les codes SC de la seconde liste correspondent à ceux de cette commune. Et de même pour les codes parcelles en fonction du code SC sélectionné. J'ai plusieurs interrogations, concernant le fonctionnement du fichier exemple trouvé sur internet( ) : la ligne temp = Target, quand j'essaye d'adapter ce code au mien, j'ai une erreur comme quoi cette variable n'est pas déclarée. De plus, je ne comprend pas sa fonction. Dans la feuille criètere, il y a les diférentes colonnes qui y apparaissent mais en ayant les occurences filtrées de façon à ne pas avoir de doublon et elles sont aussi triées par ordre alphabétique. Il y a également dans les cellules à leur droite le texte explicatif : Noms de champ Catégorie =DECALER(critères!$B$3;;;NBVAL(critères!$B$3:$B$20)+1) exposition =DECALER(critères!$D$3;;;NBVAL(critères!$D$3:$D$20)+1) persistance =DECALER(critères!$C$3;;;NBVAL(critères!$C$3:$C$20)+1) J'aimerai savoir reproduire ce fonctionnement dans mon fichier mais je ne comprend pas comment le reproduire. Quelles sont les étapes à respecter? Les listes déroulantes sont-elles créées à partir de l'onglet "Données" ==&gt; "Outils de données" ==&gt; "Validation des données" ? J'espère que vous pourrez m'aider. Merci d'avance pour votre aide...

MFerrand · Accepted Answer

.Resize(, 12) Le tri prend en compte 12 colonnes. remplace 12 par le nombre de colonnes de ta base... NB- le code cité comporte : 2 effacements, 1 tri, 2 filtrages avancés...

DjiDji59430 · Answer

Un essai...

MFerrand · Answer

Le principe des listes-cascades n'est pas très compliqué à saisir et mettre en place : un peu d'ordre et de méthode, une connaissance suffisante pour manipuler certaines fonctions d'Excel telles que DECALER, NB.SI..., être capable de définir des noms de plages dynamiques, un peu de VBA pour rendre confortable le fonctionnement (effacer les données sélectionnées en cas de modification de la sélection antérieure, assurer la mise à jour des listes), et surtout une base de données organisée de façon rigoureuse pour permettre de générer aisément des listes par filtrage... Dans la mesure où l'organisation de tes données est l'élément essentiel et que tu fournis un classeur dépourvue de données, il est un peu difficile de te dire comment tu dois l'organiser ou procéder pour rendre le système fonctionnel... Si l'on ne s'occupe que des éléments utiles aux listes de choix, tu as 3 listes donc les 3 premières colonnes de tes données contiennent respectivement les éléments qui seront susceptibles d'être sélectionnés dans les choix successifs : la 3e colonne : tous les éléments pouvant figurer dans une liste de choix 3 sont dans cette colonne, ils sont en principe unique (sauf recoupement avec des éléments de choix des niveaux antérieurs, mais cela ne semble pas être ton cas) ; la 2e colonne : tous les éléments pouvant figurer dans une liste de choix 2 y sont, et ils sont nécessairement répétés chacun pour tous les choix 3 possibles qui en dépendent ; la 1re colonne : les éléments de choix 1, répétés pour tous les choix qui en dépendent, de la même façon. Ces colonnes doivent avoir des intitulés d'en-tête. La base doit être triée sur les 3 colonnes. De la 1re colonne, on tire par filtrage avancé une liste d'éléments uniques qui constituera la liste de choix 1 Des 2 premières colonnes, on tire par filtrage avancé une liste de duos choix1-choix2 uniques (2 colonnes) sur laquelle on calculera la liste de choix 2 selon le choix 1 au moyen d'une formule. Le calcul de la liste de choix 3 dépendante des 2 premiers choix se fait en principe directement sur la base (également par formule). A chaque modification dans ta base, il faut mettre à jour l'ensemble, c'est pourquoi on met en place une procédure qui assure la mise à jour (après ajout ou retrait d'éléments dans la base) : tri, effacement des anciennes listes, filtrage avancé pour les remplacer. On met également en place une procédure évènementielle pour que lors d'un changement de choix 1 ou 2 les choix dépendants antérieurs (qui ne disparaissent pas automatiquement) devenus caducs soient effacés. Voilà la base minimale pour un système fonctionnel. Maintenant, pour des avis ou conseils plus circonstanciés, il est indispensable de se pencher sur tes données et leur organisation. Cordialement...

MFerrand · Answer

Bien !

Il s'agit de mettre en place une feuille pour les données !

Tes colonnes B D E doivent s'y retrouver côte à côte (A B C ou B C D en n'utilisant pas A) autant que possible premières colonnes de la base, les autres infos (qui n'interviennent pas dans les choix) dans l'ordre que tu veux à la suite.

Ne pas oublier un libellé de nom de champ.

Autant que possible pas de mise en forme particulière (qui ne sert à rien et ne peu que perturber (juste une mise en forme distinctive des libellés pour les distinguer...)

Et ensuite on pourra attaquer la constitution : tri et flitrage (en mettant des noms pour simplifier le code...)

Cordialement.

MFerrand · Answer

Données Plages nommées : ComBase : la première colonne de la base, en-tête incluse. =DECALER(Données!$A$1;;;NBVAL(Données!$A:$A)) Commune : l'extraction filtrée de la 1re colonne de la base. =DECALER(Données!$Q$2;;;NBVAL(Données!$Q:$Q)-1) ComSC : la 1re colonne de l'extraction filtrée des 2 premières colonnes de la base. =DECALER(Données!$T$2;;;NBVAL(Données!$T:$T)-1) Les formules permettent d'avoir des noms dynamiques couvrant les variations des plages concernées. On n'a plus à se préoccuper de les modifier lors d'ajouts ou suppressions. Une procédure de mise à jour : Sub MajDonnées() &#91;Commune&#93;.Offset(1).ClearContents &#91;ComSC&#93;.Offset(1).Resize(, 2).ClearContents With &#91;ComBase&#93; .Resize(, 12).Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 2), _ order2:=xlAscending, key3:=.Cells(1, 3), order3:=xlAscending, Header:=xlYes .AdvancedFilter xlFilterCopy, , .Cells(1, 17), True .Resize(, 2).AdvancedFilter xlFilterCopy, , .Cells(1, 20).Resize(, 2), True End With End Sub On efface les plages issues de filtrage (une ligne est laissée pour éviter que les plages n'aient plus de définition et que la référence puisse se perdre en cas d'incident &#91;rare mais cela m'est arrivé une fois...&#93;). On trie la base. On opère les filtrages avancés avec extraction d'éléments uniques. Ces opérations sur la base se font toutes à partir du nom ComBase. Ce dernier ne recouvre qu'une colonne mais par décalage et redimensionnement on peut définir n'importe quelle plage... Un bouton Mise à jour pour lancer la procédure lorsqu'on a opéré des modifications...

MFerrand · Answer

Recherche coprop. A4 (sous liste déroulante) : liste nommée choix1 directe =Commune B4 (sous liste déroulante) : calcul niveau et dimension sur 1re colonne, partie renvoyée sur la 2e colonne =DECALER(ComSC;EQUIV(A4;ComSC;0)-1;1;NB.SI(ComSC;A4)) C4 (sous liste déroulante) : calcul sur la base (méthode pour tenir compte des redondances d'éléments de la 2e colonne) =DECALER(ComBase;SOMMEPROD(EQUIV(1;(ComBase=A4)*(DECALER(ComBase;;1)=B4);0))-1;2;SOMMEPROD((ComBase=A4)*(DECALER(ComBase;;1)=B4))) Une formule nommée : Lgn (on peut aussi nommer des formules...) =SOMMEPROD(EQUIV(1;(ComBase='Recherche coprop.'!$A4)*(DECALER(ComBase;;1)='Recherche coprop.'!$B4)*(DECALER(ComBase;;2)='Recherche coprop.'!$C4);0)) Du même type que la formule de validation renvoyant la liste de choix3, cette formule calcule la ligne unique de la base répondant aux 3 critères sélectionnés. Ce qui allègera d'autant la formule à mettre dans les colonnes D à L de la base. Formule en D4 : =SIERREUR(DECALER(ComBase;Lgn-1;COLONNE()-1;1);"") Comme on peut voir, l'utilisation de Lgn raccourcit sérieusement la formule. Cette formule se tire pour la recopier, sur la ligne jusqu'à la colonne L Pour inhiber le renvoi de 0 si cellule vide dans la base : format de cellule personnalisé : Standard;; En sélectionnant la ligne de A à L, on peut la tirer d'un bloc vers le bas, ce qui recopiera les validations et les formules... Procédure Change (si modification dans les colonnes A ou B) : Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row &lt; 4 Or Target.Count &gt; 1 Then Exit Sub If Target.Column = 1 Then Target.Offset(, 1).Resize(, 2).ClearContents ElseIf Target.Column = 2 Then Target.Offset(, 1).ClearContents End If End Sub Cordialement...

MFerrand · Answer

ComSC est le nom de la plage (variable) T2:T... (qui résulte de l'extraction filtrée de la base d'éléments uniques des 2 colonnes Communes et Code SC). La colonne voisine, autre éléments des couples extraits contient les valeurs à proposer comme choix 2 (code SC) lorsque le choix 1 (commune) est fait. Pour déterminer quelle plage doit être renvoyée, on la définit en décalant ComSC. Décalage en ligne : on y recherche avec EQUIV la première occurrence de la commune choisie (on retranche 1, si EQUIV renvoie 1 le décalage est 0). Décalage colonne : 1 (c'est la colonne voisine qui contient les valeurs à renvoyer). Nombre de lignes : on le définit par le nombre de fois ou la commune apparaît dans la plage testée, avec NB.SI (les valeurs étant triées, les communes sont regroupées...) On ne se préoccupe pas du nombre de colonnes qui ne varie pas. Voilà pour la première formule citée, qui renvoie la liste de validation de choix2 après choix1 effectué. Pour la 2e, qui doit renvoyer la liste de choix3 après choix2 effectué, on la définit à partir de la base (qui est triée bien sûr...). La colonne des valeurs à renvoyer pour le choix 3 est la 3e. ComBase nomme la 1re colonne de la base. On renverra donc la liste de choix3 en la décalant à partir de ComBase. Décalage ligne : il faut rechercher la première occurrence du couple commune choisie en A4 - code SC choisi en B4. (ComBase=A4) renvoie une matrice de valeurs VRAI (valeur dans 1re colonne = A4) ou FAUX (sinon). (DECALER(ComBase;;1)=B4) renvoie une autre matrice de VRAI (valeur dans la 2e colonne = B4) ou FAUX (sinon). Le produit de ces deux matrices renvoie une nouvelle matrice composée de 1 et 0, 1 si on a VRAI pour la 1re et la 2e, 0 dans tous les autres cas. On applique EQUIV en recherchant 1, ce qui donnera la première occurrence cherchée (on retranche naturellement 1 pour décaler. Le SOMMEPROD est inutile pour cette partie de la formule, EQUIV ne renvoyant qu'une valeur... Réflexe non maîtrisé de ma part, tu m'en excuseras, tu peux donc supprimer la mise sous SOMMEPROD, qui ne modifiera pas le résultat. Décalage colonne : il est ici de 2... Nombre de lignes : on utilise les 2 mêmes matrices multipliées, mais cette fois on compte les 1 avec SOMMEPROD (justifié cette fois !), ce qui fournit le nombre de lignes. On ne s'occupe pas non plus du nombre de colonne qui reste à 1. =DECALER(ComBase;EQUIV(1;(ComBase=A4)*(DECALER(ComBase;;1)=B4);0)-1;2;SOMMEPROD((ComBase=A4)*(DECALER(ComBase;;1)=B4))) La macro Change quant à elle, intervient lorsqu'une modification intervient dans les colonnes A ou B à partir de la ligne 4. Si en A, elle efface B et C sur la même ligne. Si en B, elle efface C.....

MFerrand · Answer

Faut juste pas se gourrer dans les colonnes...

A ce sujet, j'ai dans mes archives des fonctions de conversions des lettres de colonnes en numéros et inversement. Si cela peut être utile :

'Conversion d'un nombre de 1 à 18278 en code littéral de A à ZZZ.
'Argument optionel pour prendre en compte limitations du nombre de colonnes
' Excel : 16384 = XFD [Excel 2007 et +, 256 = IV [Excel 2003 et -].
Function CONVERTNUMCOL(n As Integer, Optional lim As Integer = 0) As String
    Dim a, b, c
    Application.Volatile
    Select Case lim
        Case 1
            lim = 16384
        Case 2
            lim = 256
        Case Else
            lim = 18278
    End Select
    If n < 1 Or n > lim Then
        CONVERTNUMCOL = CVErr(xlErrNA)
        Exit Function
    End If
    a = (n - 1) \ 26
    If a > 0 Then
        b = (a - 1) Mod 26
        b = Chr(b + 65)
        a = (a - 1) \ 26
        a = IIf(a > 0, Chr(a + 64), "")
    Else
        a = ""
        b = ""
    End If
    c = (n - 1) Mod 26
    c = Chr(c + 65)
    CONVERTNUMCOL = a & b & c
End Function

'Fonction inverse...
Function CONVERTCOLNUM(col As String) As Integer
    Dim a, b, c, inval As Boolean
    Application.Volatile
    col = Trim(col)
    If Len(col) > 3 Or Len(col) < 1 Then inval = True
    If Len(col) > 2 Then
        a = UCase(Left(col, 1))
        If a Like "[A-Z]" Then
            a = Asc(a) - 64
            a = 676 * a
        Else
            inval = True
        End If
    Else
        a = 0
    End If
    If Len(col) > 1 Then
        b = UCase(Mid(col, Len(col) - 1, 1))
        If b Like "[A-Z]" Then
            b = Asc(b) - 64
            b = 26 * b
        Else
            inval = True
        End If
    Else
        b = 0
    End If
    c = UCase(Right(col, 1))
    If c Like "[A-Z]" Then
        c = Asc(c) - 64
    Else
        inval = True
    End If
    If inval Then
        CONVERTCOLNUM = CVErr(xlErrNA)
    Else
        CONVERTCOLNUM = a + b + c
    End If
End Function

Bon weekend.

molipette · Answer

Je travaille actuellement sur l'excel en pièce jointe (J'ai du modifier quelques données donc c'est normale si rien n'est logique). J'ai besoin de faire un peu de VBA pour des listes déroulantes en cascade. Pour résumé, Dans l'onglet APR, j'écris manuellement ma tâche (colonne A), puis j'écris manuellement l’événement redouté (colonne B), par liste déroulante je choisis la famille de risques (colonne C), par liste déroulante l'intitulé de la gravité en fonction de la famille de risques choisis (colonne D) et en automatique s'inscrit le score de la gravité correspondant aux 2 autres choix précédents. J'aimerai bien faire pareil pour: "intitulé fréquence"colonne G, "score fréquence" colonne H "intitulé probabilité" colonne I et "score proba"colonne J à partir de la BD P & F J'ai essayé de reproduire ce TUTO : https://forum.excel-pratique.com/excel/explications-filtre-elabore-en-cascade-listes-deroulantes-t79815.html et de suivre les indications de la discussion mais je n'y arrive pas .. J'ai : créer les plages dynamiques recopier la fonction de Majdonnées () mais je pense qu'il y a des erreurs dans les numérations recopier la procédure change Est ce que vous pensez pouvoir m'aider facilement ? Merci par avance pour votre aide, cela m'aiderait grandement ....