Liste conditionnelle triple
Bonjour à tous,
Après de nombreuses recherches infructueuses avec mon ami google et dans les méandres des fonctions d'Excel, je viens vous demander votre aide.
J'ai actuellement un fichier organisé comme ceci :
Espèce TypeProduit Produit
Espèce 1 Type 1 Produit 1
Espèce 1 Type 2 Produit 2
Espèce 2 Type 2 Produit 3
Espèce 2 Type 3 Produit 4
Espèce 3 Type 1 Produit 1
Un produit correspond forcément à un unique type de produit mais potentiellement à plusieurs espèces. Une espèce peut recevoir plusieurs types de produits et donc plusieurs produits.
Je cherche à faire une liste1 : espèce, liste2 typeproduit (pas de conditions jusque là) et une liste3 qui est conditionnée par les deux précédentes pour avoir une liste des produits.
Le problème, c'est que pour éviter les doublons, j'ai créé une colonne à part pour espèce et typeproduit. La fonction =INDEX(blabla;min(blabla);..) pour éviter les doublons en sélectionnant, par exemple, tous les types de produits ne fonctionne pas et je ne sais pas pourquoi. Par ailleurs, je ne pense pas que la fonction INDIRECT ne puisse s'appliquer non plus.
Avez-vous des idées pour éventuellement me débloquer ?
Merci beaucoup par avance !!
Bonjour,
avec un fichier exemple tu auras une réponse concise.
Bonjour,
Comme on ne sait ce que tu veux faire, et en l'absence de fichier d'illustration, il est difficile de te faire des suggestions...
Tel que tu démarres, on pense spontanément aller vers un problème de listes-cascade, pour lequel ton organisation : espèce/type/produit devrait logiquement être modifiée en type/produit/espèce pour être opérationnelle.
Mais tu t'embarques alors dans une histoire de doublons difficile à saisir et de formules non explicitées !
Ce qui brouille le tout !
Cordialement.
Voici une version simplifiée de mon fichier, avec juste la base de données et à droite ce que je cherche à faire.
En gros, je voudrais qu'avec la sélection (dans le petit carré vert sur le fichier) d'une espèce et d'un type, me ramène tous les "produits-cibles" correspondant à ces derniers.
J'ai utilisé un tableau croisé dynamique pour me ramener les valeurs sans doublons, c'était la méthode la plus rapide que j'ai trouvé mais du coup il n'y a pas de lien direct entre les listes et la grosse base de données...
Je ne sais pas si je suis assez clair...
Merci d'avance !
(le fichier fait normalement 20 000 lignes, je ne sais pas si ça change la donne au niveau des formules, mais je ne pouvais pas le mettre en pièce jointe sinon...)
Après des petites recherches, j'ai finalement tenté en VBA, sans succès :
Sub validation_txt()
'macro test de liste de validation
Dim hauteur As Integer
Dim n As Long
hauteur = 0 ' hauteur du tableau de données
For n = 1 To 60000
If Cells(n, 1) <> "" Then
hauteur = hauteur + 1
Else
hauteur = hauteur
Exit For
End If
Next n
'La hauteur est définie, ce qui permet de rechercher uniquement dans le tableau, pas plus loin, pas moins loin
'Permet de gérer l'agrandissement ponctuel du tableau
Dim liste As String
'Définition de liste, qui va être une chaîne texte de tous les éléments (produit1,...)
liste = "Choisir produit"
For n = 1 To hauteur
'on check les colonnes 2 et 3, c'est-à-dire les espèces et les types
' Cells (12,12) et Cells(13,12) sont respectivement l'espèce choisie et le type choisi (chaque case est une liste déroulante)
'les colonnes 2 et 3 sont les colonnes "espèce" et "type" de la BDD entière.
If Cells(n, 2) = Cells(12, 12) Then
If Cells(n, 3) = Cells(13, 12) Then
'La colonne 4 est la colonne produit
liste = liste & "," & Cells(n, 4) 'Incrémentation de la liste
Else
liste = liste
End If
End If
Next n
'On met la liste dans la cellule concernée
With Cells(18, 12).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=liste
End With
End SubL'erreur arrive au niveau de la validation, et je pense que ça provient du fait que ma liste fait bien plus de 255 caractères (mais lorsqu'on choisit une plage de données, ce n'est pourtant pas bloquant...).
Est-ce que vous auriez une solution alternative qui me permet d'avoir cette liste ? En VBA ou en formule, peu importe...
Bonjour,
mais du coup il n'y a pas de lien direct entre les listes et la grosse base de données
tu as commencé avec un TCD, ça n'est donc pas la solution ? Il y a forcément un lien entre la base et le tcd qui en est tiré...
P.
Bonjour,
Je ne suis toujours pas sûr d'avoir bien compris ce que tu veux faire... Il me semble cependant qu'à partir de listes déroulantes espèce et type produit, tu veuille créer une 3e liste des produits-cible correspondant aux 2 sélections antérieures (?)
Tu sembles à cheval entre un système de listes-cascade (non mis en place, et une extraction selon critères...
Je me suis penché sur ta macro... En rectifiant les références de cellules qui ne correspondent pas à ton modèle, elle fonctionne !
255 est la limite de largeur en caractères moyens d'une cellule, sa limite de contenu est 32767 caractères, je n'ai pas vu de limite de longueur de liste déroulante de validation, mais elle peut être assimilée à la limite des listes déroulantes de filtres qui est 10000 (?).
Si ta macro ne fonctionne pas chez toi, cela ne provient probablement pas des limites d'Excel...
Voici ta macro juste rectifiée en ce qui concerne les références de cellules, le placement systèmatique en tête de procédure des déclarations, et indentée (pour que du code soit lisible et qu'on puisse travailler efficacement avec, il est toujours nécessaire de l'indenter...) :
Sub validation_txt()
Dim hauteur As Integer
Dim n As Long
Dim liste As String
hauteur = 0
For n = 1 To 60000
If Cells(n, 1) <> "" Then
hauteur = hauteur + 1
Else
hauteur = hauteur
Exit For
End If
Next n
liste = "Choisir produit"
For n = 1 To hauteur
If Cells(n, 3) = Cells(13, 12) Then
If Cells(n, 4) = Cells(14, 12) Then
liste = liste & "," & Cells(n, 2) 'Incrémentation de la liste
Else
liste = liste
End If
End If
Next n
With Cells(15, 12).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=liste
End With
End SubCe code fonctionne sur ton modèle (mais il n'est pas épuré de quelques inutilités...)
Suite : la même macro déjà légèrement épuré :
Sub validation_txt()
Dim hauteur As Long, n As Long, liste As String
hauteur = 0
For n = 1 To 60000
If Cells(n, 1) <> "" Then
hauteur = hauteur + 1
Else
Exit For
End If
Next n
For n = 1 To hauteur
If Cells(n, 3) = Cells(13, 12) And Cells(n, 4) = Cells(14, 12) Then _
liste = liste & "," & Cells(n, 2)
Next n
liste = Replace(liste, ",", "", 1, 1)
With Cells(15, 12).Validation
.Delete
.Add xlValidateList, , , liste
End With
End Subhauteur est rectifié en type Long car ne peut être d'un type différent de n (NB- le type Integer va jusqu'à 32767, ta base ne le dépasse donc pas pour l'instant mais déjà à 20000 elle est susceptible de dépasser la capacité Integer...)
Tu verras facilement le raccourcissement des instructions de tests...
Suppression de ton intitulé de liste, remplacé par la suppression de la première virgule.
Passage des arguments par position (au lieu de par noms) avec suppression des paramètres optionnel laissés à leur valeur par défaut.
Une version plus compacte et complétée de quelques éléments :
Sub validation_txt()
Dim n&, i&, liste$, esp$, typ$
With Worksheets("BDD_phyto")
esp = .Cells(13, 12): typ = .Cells(14, 12)
n = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To n
If Cells(i, 3) = esp And Cells(i, 4) = typ Then liste = liste & "," & Cells(i, 2)
Next i
liste = Replace(liste, ",", "", 1, 1)
With .Cells(15, 12).Validation
.Delete
On Error Resume Next
.Add xlValidateList, , , liste
End With
End With
End SubUtilisation des caractères de déclarations de type pour déclarer les variables ; substitution de n et i (plus classique...) à hauteur et n ; ajout de 2 variables String à initialiser aux valeurs cherchées.
Recherche de la dernière ligne de la façon la plus commune, avec End(xlUp)...
In troduction de qualificateurs d'objets : Référence de la feuille sur laquelle on opère mise en mémoire avec With, toutes les expressions impliquant la feuille s'y réfèrent, ce qui évitera des surprises éventuelles lors de l'évolution du fichier...
Une gestion d'erreur à l'ajout de la liste de validation, car si elle est vide il y aura erreur (et dans le modèle simplifié ce cas n'est pas rare !)
Après cet examen du code en tant que tel (dont j'espère qu'il pourra t'être utile...), je ne vois pas (comme Patrick) ce que tu gagnes avec des TCD.
De ce qu j'en vois, cela ne te sert qu'à établir des listes sans doublons pour espèces et types-produits.
Il me semblerait plus rationnel de les faire par filtre avancé : 2 lignes de codes (une par extraction de chaque liste) pour composer une macro qui t'en assure la mise à jour (+2 lignes pour supprimer les listes antérieures lors de mises à jour).
Il serait également judicieux de nommer la première colonne de ta base en dynamique : le nom suffira pour la parcourir...
Et nommer tes listes de validations...
Tes listes peuvent se trouver sur la même feuille que la base, mais il serait préférable de les placer vers la droite en les espaçant de la base et en les commençant ligne 1 (intitulé)...
Par contre tes cellules de sélection devrait se trouver sur une autre feuille de travail.
Cordialement.
Bonjour,
Tout d'abord, merci pour ces réponses rapides ! Effectivement, après ces quelques corrections le code fonctionne.
Pour ce qui est de la simplification proposée dans le dernier message, j'y travaille. Les choix d'espèce/produit/type sont déjà sur une autre feuille.
Par contre, j'ai essayé d'automatiser le tout avec une détection de changement de cellule, sur cette même nouvelle page.
Je ne rencontre presque pas de problèmes, globalement j'arrive à retrouver mes produits, sauf dans certains cas particuliers, comme pour les types de produits "fongicides" sur certaines espèces, par exemple.
Lorsque j'enlève la protection d'erreur, il me renvoie à l'erreur 1004. Pourtant, la liste, manuellement, n'est pas vide...
Une idée ?
J'ai joint le fichier excel avec les deux macros (private et module), en essayant de garder un échantillon assez conséquent pour ne pas avoir trop de listes vides.
Dans tous les cas, merci énormément, ça m'a bien débloqué et permis de bien progresser sur mon fichier !!
Il faut faire les choses jusqu'au bout ! et pas se tromper.
1) Tu vises la ligne 10 alors que c'est visiblement la 7 qui est concernée.
2) Le résultat intervient ligne 8, ligne fixe donc, inutile de faire du cinéma avec Target.Row + 1 !
3) Ta double validation est difficile à comprendre
(J'ai déjà dit aussi qu'on perdait énormément de temps avec le code mal indenté.)
Oui, je m'en suis aperçu après, mais c'est uniquement sur le fichier épuré transmis sur le forum, j'ai supprimé un petit paquet de lignes et je n'ai pas pensé à le remettre à jour.
Pour ce qui est de la ligne fixe, elle l'est pour le moment mais à terme, je pense ajouter une boucle pour pouvoir tester plusieurs lignes sur la feuilles, d'où le target.row + 1.
Je pensais corriger l'erreur en mettant une validation "Tout" et en remettant une validation "Liste" derrière, mais ça n'a rien changé, en effet...
Après correction de l'erreur sur le fichier épuré, il s'avère que ça fonctionne sur lui, mais pas sur mon fichier à 20'000 lignes. Je n'arrive toujours pas à avoir certains couples précis (alors qu'ils existent dans le tableau...).
J'ai mis un point d'arrêt pour checker d'où pouvait venir l'erreur. Il s'avère que l'élément "liste" est incrémenté correctement, c'est au niveau de la validation que ça coince... ça pourrait venir d'une des cellules de ces couples particuliers qui est trop grande ? Avec un NBCAR de toutes les cellules ciblées par la validation, je ne dépasse pourtant pas les 62 caractères de largeur...
EDIT : Après vérification, il s'avère que c'est le champ "source" de validation de données qui semble avoir un nombre limité de caractères et qui ne peut donc recevoir l'élément "liste" lorsque ce dernier est trop long... Est-ce que c'est possible ? Une idée pour contourner ce problème ?
Pour ce qui est de la ligne fixe, elle l'est pour le moment mais à terme, je pense ajouter une boucle pour pouvoir tester plusieurs lignes sur la feuilles, d'où le target.row + 1.
Dans ce cas, faut le faire tout de suite.
EDIT : Après vérification, il s'avère que c'est le champ "source" de validation de données qui semble avoir un nombre limité de caractères et qui ne peut donc recevoir l'élément "liste" lorsque ce dernier est trop long... Est-ce que c'est possible ? Une idée pour contourner ce problème ?
1) Déclarer liste non typée (enlever le $ final ou le As String). [Ce sera donc une variable de type Variant.]
Déclarer également une variable plage : , LPC As Range (par exemple)
2) Pas de changement pour constituer liste.
3) Avant mise en place validation, transformer liste en tableau :
liste = Split(liste, ",")4) Affecter à LPC une plage constituant la destination de la liste de produit-cible, par exemple :
Set LPC = Worksheets("BDD_phyto").Range("N2").Resize(UBound(liste) + 1)5) Affecter le tableau à cet emplacement en le redimensionnant :
LPC.Value = WorksheetFunction.Transpose(liste)6) Nommer cette plage :
ThisWorkbook.Names.Add "ListePC", LPC7) Modifier l'ajout de la liste de validation en substituant : "=ListePC" à liste...
Ha oui, j'avais commencé à travailler à une solution de ce genre, en mettant une macro en place à chaque mise à jour de la base de données, qui me permettait d'extraire un tableau avec une colonne par couple de espèce-type et donc sélectionner des plages et plus des valeurs.
Cette solution est néanmoins très rapide !
Problème résolu du coup, merci pour la réactivité, c'était top !