Nommer plage dynamique selon critère

[Stakov]

Bonjour à tous et à toutes,

Cela fait un certain temps que je parcours le forum pour répondre à mes interrogations concernant Excel donc je tenais tout d'abord à remercier l'ensemble de la communauté pour toute l'aide qu'elle a déjà pu m'apporter par le passé.

J'ai décidé aujourd'hui de m'inscrire car je n'ai malgré mes recherches pas trouvé de réponse à mon problème, qui paraitra peut être tout simple à certains d'entre vous, et j'espère donc que vous pourrez m'aider à nouveau !

En supposant une liste à deux colonnes A et B avec, dans chaque ligne, en A la valeur 0 ou 1, et en B un prénom. Ce qui donne par exemple :

A	B
1	Arnaud
0	Christophe
1	Patrick
1	Patrice
0	Elise
1	Amélie

Ce que je souhaite, c'est créer une plage nommée dynamique qui regrouperait les éléments de la colonne B sur critère d'une valeur en A.

Par exemple, si le critère de A est 1, je souhaite que ma plage nommée dynamique regroupe les valeurs "Arnaud;Patrick;Patrice;Amélie".

Un simple Decaler/NbVal voir éventuellement avec equiv pour répérer l'emplacement de la cellule référence est donc ici insuffisant puisque ma colonne A n'est pas triée et que les éléments de la colonne B sont donc discontinus.

Je n'ai pas pris la peine de joindre un fichier en espérant que mon illustration soit suffisante, mais si cela vous semble nécessaire je vous le fournirai dans la foulée.

Merci d'avance pour votre aide !
Stakov

 

[DoubleZero]

Re : Nommer plage dynamique selon critère

Bonjour à toutes et à tous,

Bienvenue sur XLD, Stakov !

En pièce jointe, un résultat qui peut être obtenu grâce au filtre avancé.

Pour voir le code de la macro, appuyer sur les touches du clavier "Alt" et "F11".

A bientôt

 

[Papou-net]

Re : Nommer plage dynamique selon critère

Bonsoir Stakov,
Bonsoir petite ânesse,

Puisque je l'ai fait d'une autre façon, je joins mon fichier.

Ca me donne l'occasion de refaire quelques petites bises à 00.

Cordialement.

 

[Stakov]

Re : Nommer plage dynamique selon critère

Bonsoir DoubleZero, bonsoir Papou-net,

Merci pour votre aide ! Vos deux solutions sont très efficaces et je vois déjà comment les transformer aisément pour mon besoin.

Néanmoins, il y a un léger inconvénient, c'est que pour des raisons pratiques (mes fichiers sont utilisés en interne et par des clients) j'aurais préféré éviter tout code VBA et passer par le gestionnaire des plages définies d'Excel.

Dans l'idéal, je souhaiterais donc utiliser une formule capable de reproduire ce que vous avez fait par VBA. Si cela n'est pas possible, je me résignerai et tenterai de transformer votre code pour l'adapter en formule permettant de reconstruire les plages définies comme je le souhaite.

Je vous remercie d'avance pour votre aide et votre temps

 

[Papou-net]

Re : Nommer plage dynamique selon critère

RE

Néanmoins, il y a un léger inconvénient, c'est que pour des raisons pratiques (mes fichiers sont utilisés en interne et par des clients) j'aurais préféré éviter tout code VBA et passer par le gestionnaire des plages définies d'Excel

Je crains que ce ne soit pas possible par formule. A moins qu'un spécialiste de la formulation (et il n'en manque pas ici) trouve la réponse. Pour ma part, je sèche.

Mais, de toute façon, l'usage des macros reste transparent pour l'utilisateur lambda. Et il y a toujours possibilité de protéger l'éditeur VBE avec un mot de passe.

Cordialement.

 

[Stakov]

Re : Nommer plage dynamique selon critère

Re,

Merci pour ton retour, dans ce cas je continue à espérer l'aide de quelqu'un qui pourriat fournir une formule (ou m'aiguiller sur une piste) pouvant faire ça.

En attendant, je me suis attelé au code VBA d'une formule faisant ce que je souhaite réaliser. Je suis loin d'être un expert en VBA, et étant autodidacte en la matière j'ai fait avec les quelques connaissances que j'ai.

Je commence donc par regrouper sous forme d'un Array à deux colonnes d'une part l'adresse de chaque cellule de la plage contenant les cellules à regrouper dans la plage dynamique, et d'autre part la valeur de chaque cellule de la plage contenant le critère de sélection.

Dans un second temps, je teste pour chaque ligne de l'array si la cellule critère a la bonne valeur, auquel cas j'ajoute via son adresse la cellule à sélectionner au range voulu in fine.

Tout semble fonctionner, lorsque j'entre cette formule dans le gestionnaire des noms, il me sélectionne bien (en pointillés) la zone correspondante. Seulement il m'est impossible d'utiliser ensuite cette plage définie dans des listes comme je le souhaite... Dès que le range est discontinu la plage définie est considérée comme invalide.

Voici le code :

Function DecalerSi(Plage_Dynamique As Range, Plage_Criteria As Range, Criteria As String) As Range

Dim cel_d As Range, cel_c As Range, dyn_range As Range, cell_range As Range
Dim Plage_Tab()
Dim i As Byte


If Plage_Dynamique.Columns.Count > 1 Or Plage_Criteria.Columns.Count > 1 Then Exit Function 'ne fonctionne qu'en colonne pour l'instant et pas en ligne
If Plage_Dynamique.Cells.Count <> Plage_Criteria.Cells.Count Then Exit Function 'même nombre de cellules exigé pour les deux ranges


ReDim Plage_Tab(2, Plage_Dynamique.Cells.Count) 'redimensionnement de l'array

'Remplissage de l'array Plage_Tab avec l'addresse de chaque cellule de Plage_Dynamique
i = 1
For Each cel_d In Plage_Dynamique
    Plage_Tab(1, i) = cel_d.Address
    i = i + 1
Next

'Remplissage de l'array Plage_Tab avec la valeur de chaque cellule de Plage_Criteria
i = 1
For Each cel_c In Plage_Criteria
    Plage_Tab(2, i) = cel_c.Value
    i = i + 1
Next

'Traitement de l'array et test ligne par ligne si critère respecté
For i = 1 To UBound(Plage_Tab, 2)

    If Plage_Tab(2, i) = Criteria Then
        Set cell_range = Range(Plage_Tab(1, i))
        
        If dyn_range Is Nothing Then
            Set dyn_range = cell_range
        Else
            Set dyn_range = Application.Union(dyn_range, cell_range)
        End If
    End If

Next i

Set DecalerSi = dyn_range

End Function

Je ne comprends pas pourquoi, alors que manuellement on peut parfaitement définir une plage dynamique sous forme d'une selectione discontinue de cellules :S

Y a t-il une erreur quelque part ?

PS : si cela vous semble nécessiter un second thread je peux en créer un dans la foulée

 

[Papou-net]

Re : Nommer plage dynamique selon critère

RE

Ta fonction me paraît bien compliquée et elle ne me semble pas répondre à ta demande initiale (ou alors, je n'ai rien compris).

Si tu veux que la macro n'apparaisse pas à l'utilisateur, je te propose la solution suivante qui met à jour la plage nommée chaque fois que D1 est modifiée. Tu peux le vérifier par un clic dans la fenêtre d'adresse des cellules.

Cordialement.

 

[Stakov]

Re : Nommer plage dynamique selon critère

Bonjour à tous,

Ce qui est pénible avec Excel c'est que le terme de plage peut faire référence à des choses complètement différentes. Quand je parle de plage dynamique, j'entends les plages de cellules que l'on peut nommer via le gestionnaire de noms :



Donc pour être clair, mon objectif premier est de parvenir à créer une formule qui, comme la formule DECALER, génère une plage de cellules. J'ai ensuite l'intention d'utiliser cette formule pour définir des plages dynamiques (via le gestionnaire de nom) que je pourrais ensuite exploiter soit dans des listes déroulantes, soit dans des graphiques.

Ce que je souhaite donc c'est qu'une formule, soit en combinant des formules natives d'Excel, soit en en programmant une, me génère un range que je pourrai définir comme plage dynamique via le gestionnaire de nom.

Or c'est ce que fait ma formule personnalisée DecalerSi. Elle est peut être un peu compliquée mais c'est car j'ai préféré anticiper différents cas possibles, comme le fait que la plage de référence sur laquelle sera construire la plage dynamique ne soit pas adjacente à la plage contenant le critère de sélection.

Bref au final, quand je l'entre dans le gestionnaire de noms, elle me remplit son rôle. Sauf que la plage dynamique nommée reste inutilisable ensuite dans des listes. Donc je me demande si il n'y aurait pas une propriété à respecter pour que ça puisse fonctionner.

J'espère avoir dissipé tout doute ou malentendu et vous remercie d'avance pour une quelconque aide

 

[Abel]

Re : Nommer plage dynamique selon critère

Bonjour le fil,

Je mets mon grain ...

Pour le faire uniquement avec des formules, je dirais qu'il faut passer par un tableau intermédiaire qu'on peut mettre dans un onglet masqué par la suite.
Dans ce tableau intermédiaire, on peut faire tous les tris, classements, filtres, etc qu'on veut.
La plage dynamique pourra être basée ensuite sur ce tableau intermédiaire.

Je ne suis pas assez calé en formule mais ce type de solution me parait tout à fait faisable.

Abel.

 

[JHA]

Re : Nommer plage dynamique selon critère

Bonjour à tous,

Un essai par formule et plages nommées

JHA

 

[Robert]

Re : Nommer plage dynamique selon critère

Bonjour Le fil, bonjour le forum,

Un proposition de fonction personnalisée PN (pour Plage Nommée) qui fonctionne mais mal (oui, un peu comme moi... Je sais Abel !). La plage nommée (que Stakov appelle plage dynamique) est bien crée mais uniquement si j'utilise fx (insérer une fonction), catégorie personnalisées, PN, et je clique sur une cellule contenant le critère A2 par exemple... Mais si je tape dans une cellule : =PN(, je clique sur A2, je ferme la parenthèse ) et je valide par [Entrée], là ça marche pas ???
L'autre inconvénient c'est qu'après avoir validé je me retrouve avec #VALEUR! dans la cellule et je ne sais pas comment le supprimer automatiquement... Faut dire que les fonctions personnalisées, je débute à peine...
La fonction :

Public Function PN(Target As Range)
Dim crit As String 'déclare la variable crit (CRITère)
Dim dl As Integer 'déclare la variable dl (Dernière Ligne)
Dim pl As Range 'déclare la variable pl (PLage)
Dim cel As Range 'déclare la variable cel (CELlule)
Dim pac As Range 'déclare la variable pac (Plage Avec Critère)

crit = CStr(Target.Value) 'définit la critère crit
With Sheets("Feuil1") 'prend en compte l'onglet "Feuil1"
    dl = .Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne éditée dl de la colonne 1 (=A)
    Set pl = .Range("A2:A" & dl) 'définit la plage pl
End With 'fin de la prise en compte de l'onglet "Feuil1"
For Each cel In pl 'boucle sur toutes les cellules cel de la plage pl
    If CStr(cel.Value) = crit Then 'condition 1 : si la valeur de la cellule convertie en texte est égale au critère crit
        If pac Is Nothing Then 'condition 2 : si la plage pac est vide
            Set pac = cel.Offset(0, 1) 'définit la plage pac
        Else 'sinon
            Set pac = Application.Union(pac, cel.Offset(0, 1)) 'définit la plage pac
        End If 'fin de la condition 2
    End If 'fin de la condition 1
Next cel 'prochaine cellule de la plage
pac.Name = "Plage_" & crit 'nomme la plage pac "Plage_" + le critère crit (Plage_1, Plage_0)
End Function

 

[Abel]

Re : Nommer plage dynamique selon critère

Re,

Aarghl !
Mon vieux 2003 ne veut pas voir complètement la solution de JHA !
Je tourne autour d'un truc avec des index et des petite.valeur mais sans trouver et je vois que je suis pas loin avec la formule de JHA.

Abel


ps : salut Robert !!!

 

[JHA]

Re : Nommer plage dynamique selon critère

Re,

Enlever sierreur() pour 2003

JHA

 

[Abel]

Re : Nommer plage dynamique selon critère

Re,

Ca c'est chouette.
Merci JHA !

Abel.

 

[Stakov]

Re : Nommer plage dynamique selon critère

Bonjour à tous !

Merci JHA pour ton aide c'est exactement ce que je cherchais !

Merci à tous pour votre participation,
Stakov