XL 2016 Extraction de cellules depuis un tableau

[grodep]

Bonjour à tous,
Je dispose d'une feuille d'import de références produits sur une feuille "Import".

1/ Je cherche dans un premier temps à extraire toutes les références correspondant à un fournisseur.
Pour ce faire, j'ai défini une liste (ListPdt) dans le gestionnaire de noms, correspondant à la plage de cellules qui m'intéressent dans la feuille import.
J'ai également défini dans le gestionnaire de noms le choix (nom "Choix") du fournisseur identifié par un numéro(ListNumFour).
Utilisant une formule matricielle qui trie sur cette liste d'import, j'espérais obtenir tous les produits, malheureusement, la formule ne retourne que la première référence de la liste, sans que je parvienne à identifier pourquoi...
Voici la formule utilisée :

=SIERREUR(INDEX(ListPdt;PETITE.VALEUR(SI(ListNumFour=Choix;LIGNE(ListNumFour);100000);LIGNE(ListNumFour)-1);4);"")

Validée par CtrlMajEntrée

2/ Dans un deuxième temps, plutot que de me retourner tous les produits de ce fournisseur, je souhaiterai ne retourner que ceux dont la quantité est inférieure à un certain seuil (déterminé en Rapport!D4)

Merci d'avance à tous les contributeurs.

 

[Robert]

Bonjour Grodep, bonjour le forum,

Par formules je ne sais pas faire, Si une proposition VBA t'intéresse, le code :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OI As Worksheet 'déclare la variable OI (Onglet Import)
Dim OD As Worksheet 'déclare la variable OD (Onglet Données)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)
Dim K As Integer 'déclare la variable K (incrément)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)


If Target.Address = "$D$4" And Target(1).Value <> "" Then Range("B4").Value = Range("B4").Value 'si la cellue modifié est D4 redéfinit la valeur de B4 (permet de relancer cette macro événementielle)
If Target.Address = "$B$4" Then 'condition 1 : si l'addresse de la cellule modifiée est B4
    If Target.Offset(0, 2).Value = "" Then 'si le seuil n'est pas renseigné
        MsgBox "Vous devez renseigner le seuil !" 'message
        Target.Offset(0, 2).Select 'sélectionne D4
        Exit Sub 'sort de la procédure
    End If 'fin de la condition
    If Target.Value = "" Then Target.Offset(0, 2).Value = "": Target.Offset(0, 3).Value = "" 'si B4 est effacée, efface D4 et E4
    Set OI = Worksheets("Import") 'définit l'onglet OI
    Set OD = Worksheets("Données") 'définit l'onglet OD
    TV = OI.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
    For I = 2 To UBound(TV, 1) 'boucle 1 : sur toutes les lignes I du tableau des valeurs TV (en partant d ela seconde)
        If CStr(TV(I, 7)) = CStr(Target.Value) Then 'condition 2 : si la donnée ligne I colonne 7 de TV (convertie en texte) est égale à B4 (convertie en texte)
            If TV(I, 5) < Target.Offset(0, 2).Value Then 'condition 3 : si la donnée ligne I colonne 5 de TV est inférieure au seuil en D4
                K = K + 1 'incrémente K
                ReDim Preserve TL(1 To 5, 1 To K) 'redimensionne le tableau des lignes TL (5 lignes, K colonnes)
                For J = 1 To 5 'boucle 2 : sur les 5 premières colonnes J de TV
                    TL(J, K) = TV(I, J) 'récupère dans la ligne J de Tl la donnée en colonne J de TV (=> Tranposition)
                Next J 'prochaine colonne de la boucle 2
            End If 'fin de la condition 3
        End If 'fin de la condition 2
    Next I 'prochaine ligne de la boucle 1
    Range("J1").CurrentRegion.ClearContents 'efface les éventuelles anciennes valeurs
    If K > 0 Then 'condition : si K est supérieur à 0
        Range("J1").Resize(K, 5).Value = Application.Transpose(TL) 'renvoie dans J1 redimensionnée le tableau TL transposé
        Range("E4").Value = K 'renvoie le K dans E4
    End If 'fin de la condition
End If 'fin de la condition 1
End Sub

Ton fichier modifié :

 

[vgendron]

Hello
En réponse pour la première question:
1) ta zone ListPdt est mal définie ==> Cf gestionnaire de nom
2) je t'ai mis la formule avec Index
l'ennui avec Petite.valeur, c'est le 0 qui est retourné à chaque fois que la ligne ne répond pas aux critères: tu as contourné avec 10000.. mais je sais qu'il y a une autre écriture qui s'en affranchie.. je ne sais jamais..

Edit: Hello Robert

 

[vgendron]

et pour la seconde question.. il "Suffit" de modifier la formule pour intégrer la condition
SI((INDEX(ListPdt;;7)=Choix)*(INDEX(ListPdt;;6)<$D$4)

 

[chris]

Bonjour à tous

Une proposition PowerQuery, intégré à Excel avec une ligne de VBA pour actualiser au changement de choix du fournisseur

 

[grodep]

Un grand merci à tous pour vos contributions aussi rapides qu'efficaces!
@chris, j'avoue que j'aime beaucoup la présentation du résultat telle que tu la proposes, mais je ne vois pas comment passer à la deuxième étape pour faire un tri supplémentaire selon la quantité de produits. Le but serait alors de ne plus afficher dans le tableau que les produits répondant aux deux conditions (numéro de fournisseur et quantité inférieure au seuil donné).

@vgendron, super, fonctionne parfaitement, merci pour les corrections sur la définition de la zone(...).

 

[chris]

Arf c'est inférieur
J'ai lu trop vite
j'inverse le filtre

Pour le tri j'ai mis l'Id produit mais on peut changer

Ouvre PowerQuery : tu verras les requêtes, surtout Import avec à droite les étapes dont on voit le détail dans la barre de formules et en double cliquant sur le rouage de la ligne d'étape

 

[grodep]

Merci Chris, j'avoue que je découvre totalement cet outil! Je vais tenter de m'y pencher dessus car ça a l'air assez puissant pour ce genre de tâches.
En tout cas, merci pour le travail effectué et le service rendu.

 

[grodep]

@chris, un petit souci en travaillant sur ton fichier: si je change le seuil sans changer le numéro de fournisseur, la liste ne s'actualise pas.
Il faut sans doute corriger cette ligne mais j'avoue mon incompétence...

If Not Intersect(Target, [T_Choix].ListObject.DataBodyRange.Cells(1, 1)) Is Nothing _
    Then ThisWorkbook.RefreshAll

 

[chris]

RE

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [T_Choix].ListObject.DataBodyRange.Cells(1, 1)) Is Nothing Or _
    Not Intersect(Target, [T_Choix].ListObject.DataBodyRange.Cells(1, 3)) Is Nothing _
    Then ThisWorkbook.RefreshAll
End Sub