match ou vlookup?

[roger44]

Bonjour
J'aimerais extraire certaine lignes d'un fichier principal à partir de valeurs se trouvant dans une colonne séparée. Une valeur peut se trouver plusieurs fois à se suivre dans la colonne séparée. Dans ces cas la même ligne doit être extraite autant de fois.

Merci d'avance
Roger

 

[job75]

Bonsoir roger44,

Sans plus de précision voyez le fichier joint et cette formule en L7 :

=RECHERCHEV($J7;$E$7:$H$15;COLONNES($L7:L7);0)

à copier à droite et vers le bas.

A+

 

[roger44]

Merci Job, ça m'a l'air parfait. Petite question, à quoi sert le ;0) en fin de formule?

 

[job75]

Petite question, à quoi sert le ;0) en fin de formule?

Faites une recherche sur le web :

0 ou FAUX => correspondance exacte

1 ou VRAI => correspondance approximative.

 

[roger44]

J'ai rencontré un 2e pb inattendu. La valeur peut se retrouver plusieurs fois dans le fichier principal. Voir fichier. Je ne vois pas trop comment faire.

 

[job75]

Bonjour roger44,

Pas inattendu, c'est pour ça que j'ai dit "Sans plus de précision", RECHERCHEV renvoie la 1ère valeur trouvée.

Si l'on veut récupérer les doublons entrez cette formule matricielle en I3 de ce fichier (2) :

=SI(LIGNES(I$3:I3)>SOMME(SIGNE(NB.SI($G$3:$G$6;$B$3:$B$13)));"";INDEX(B$1:B$13;PETITE.VALEUR(SI(NB.SI($G$3:$G$6;$B$3:$B$13);LIGNE($B$3:$B$13));LIGNES(I$3:I3))))

à valider par Ctrl+Maj+Entrée et tirer à droite et vers le bas.

Nota : sur Excel 2007 et versions suivantes on peut utiliser SIERREUR, toujours en matriciel :

=SIERREUR(INDEX(B$1:B$13;PETITE.VALEUR(SI(NB.SI($G$3:$G$6;$B$3:$B$13);LIGNE($B$3:$B$13));LIGNES(I$3:I3)));"")

Bonne journée.

 

[merinos]

Salut a tous,

@job75 : je suis toujours émerveillé par les capacités des formules matricielles. Juste une question: elles ne sont pas lentes pour des tables plus grandes?


J'ai fait le même boulot en query... cela se recalcule en un clignement d'oeil...

Merinos

 

[job75]

Bonjour merinos,

Les formules que j'ai données sont classiques et bien sûr elles prennent du temps sur un grand tableau.

Pour aller plus vite utiliser VBA et le Dictionary, ou Power Query sur les versions Excel récentes.

A+

 

[job75]

Voyez le fichier (3) joint et cette macro dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ncol%, tablo, ref, dest As Range, d As Object, i&, n&, j%
With [B2].CurrentRegion 'à adapter
    ncol = IIf(.Count = 1, 2, .Columns.Count)
    tablo = .Resize(, ncol) 'matrice, plus rapide, au moins 2 éléments
End With
ref = [G3:G6].Resize(, 2) 'colonne à adapter, au moins 2 cellules
Set dest = [I3] '1ère cellule de destination, à adapter
Set d = CreateObject("Scripting.Dictionary")
'---mémorisation des références---
For i = 1 To UBound(ref)
    d(ref(i, 1)) = ""
Next i
'---analyse du tableau source---
For i = 2 To UBound(tablo)
    If d.exists(tablo(i, 1)) Then
        n = n + 1
        For j = 1 To ncol
            tablo(n, j) = tablo(i, j)
        Next j
    End If
Next i
'---restitution---
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
If n Then
    With dest.Resize(n, ncol)
        .Value = tablo
        .Interior.ColorIndex = 6 'jaune
        .Borders.Weight = xlHairline 'bordures
    End With
End If
dest.Offset(n).Resize(Rows.Count - n - dest.Row + 1, ncol).Delete xlUp 'RAZ en dessous
Application.EnableEvents = True 'réactive les évènements
End Sub

Pour tester j'ai copié le tableau B3:E13 sur 55 000 lignes : la macro s'exécute chez moi en 0,9 seconde.

 

[BOISGONTIER]

Bonjour,

Fonction Recherchev()

Boisgontier

 

[roger44]

Bonjour Job
Revenant sur ta réponse du 18 avril et la formule
=SI(LIGNES(I$3:I3)>SOMME(SIGNE(NB.SI($G$3:$G$6;$B$3:$B$13)));"";INDEX(B$3:B$13;PETITE.VALEUR(SI(NB.SI($G$3:$G$6;$B$3:$B$13);LIGNE($B$3:$B$13));LIGNES(I$3:I3))))

J'ai voulu laisser le fichier source dans un fichier séparé, en précédant les 4 instances de B$3:B$13 de cette formule par le nom du fichier source. Ca n'a pas marché. Y a-t-il une solution?
Roger

 

[job75]

Bonjour roger44, le forum,

Attention, la formule que vous indiquez n'est pas bonne, ce n'est pas INDEX(B$3:B$13;... mais INDEX(B$1:B$13;... qu'il faut avoir.

A priori il n'y a pas de problème, téléchargez les 2 fichiers joints et ouvrez-les tous les deux.

En I3 du 1er fichier vérifiez qu'il y a bien cette formule matricielle ou entrez-la :

=SI(LIGNES(I$3:I3)>SOMME(SIGNE(NB.SI($G$3:$G$6;[Source.xls]Feuil1!$B$3:$B$13)));"";INDEX([Source.xls]Feuil1!B$1:B$13;PETITE.VALEUR(SI(NB.SI($G$3:$G$6;[Source.xls]Feuil1!$B$3:$B$13);LIGNE([Source.xls]Feuil1!$B$3:$B$13));LIGNES(I$3:I3))))

Le fichier Source.xls peut être fermé sans inconvénient, les formules fonctionnent encore.

Bonne journée.

 

[roger44]

Et rebonjour
La formule (tout sur une page)de donne pas un résultat fiable. Voir les dernières lignes du fichier joint. J'ai réproduit en bleu clair ta réponse pour m'aider.
Merci

 

[job75]

Pas de fichier joint… Mais vous n'avez pas dû faire les choses correctement, faites comme sur mon dernier fichier.

 

[roger44]

Merci
Pour ma 1ère question à 6h24 ce matin, oui, tu avais bien écrit B1, c'est moi qui l'ai converti en B3. Je vais ré-essayer.
J'avais commencé une 2e question que je n'ai pu terminée puisque le fichier joint a été rejeté, trop volumineux. Si ça se trouve, c'est le même B3/B1 cause.