Discussion :

[Gremlins49]

Bonjour,

J'ai besoin d'extraire une donnée d'une cellule (nom de cheval) par rapport à une liste afin de ramener la donnée si elle est trouvée.
Je m'explique :
J'ai une liste de chevaux dans un onglet "chevaux" en colonne A (liste qui peut contenir près d'une centaine de noms)
J'ai un détail d'opérations liées à ces chevaux dans un onglet "opérations". Le nom du cheval apparaît dans la colonne B. J'aimerais, qu'en colonne E, le nom du cheval soit extrait en testant par rapport à la liste de l'onglet "cheval".

J'ajoute un fichier pour une meilleure compréhension.
J'ai évidement essayé de me débrouiller seule, mais je n'arrive pas extraire le nom du cheval en testant toutes les cellules de la liste.

Merci de votre aide

[jurassic pork]

Hello,
comme ta demande ne m'a pas l'air si facile que cela à faire et comme je ne suis pas un spécialiste des formules, j'ai créé une fonction personnalisée en VBA qui m'a l'air de réaliser ce que tu veux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Public Function SearchWhoIsIn(Cellule, tabChevaux As Range) As String
Debug.Print Cellule
SearchWhoIsIn = ""
For Each cheval In tabChevaux
If InStr(1, Cellule, cheval) > 0 Then
    SearchWhoIsIn = cheval
End If
Next
End Function

La fonction a comme premier argument la cellule où se trouve le texte dans lequel on doit chercher un cheval. Le deuxième argument est la plage qui correspond aux noms de chevaux de la première feuille.
Voici ce que cela donne :


Chevaux dans la formule correspond à la plage nommée englobant les noms des chevaux de la première feuille.

Le classeur exemple en pièce jointe.

[EDIT] oops à mettre en commentaire le Debug.print du code VBA
Ami calmant, J.P

[Gremlins49]

Trop top !
Rien n'est impossible avec Excel et avec ceux qui savent.... et qui partagent...

Je te remercie d'avoir solutionner ce problème ; la solution me va tout à fait !

[jurassic pork]

Une version plus optimisée de la fonction personnalisée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Public Function SearchWhoIsIn(Cellule, tabChevaux As Range) As String
SearchWhoIsIn = ""
For Each cheval In tabChevaux
  If InStr(1, Cellule, cheval) > 0 Then
     SearchWhoIsIn = cheval
     Exit Function
  End If
Next
End Function

On sort de la boucle dès que le cheval a été trouvé comme cela on ne balaie pas toute la liste à chaque fois

[Gremlins49]

J'ai quand même une petite question : dans ton code VBA, tu mets en ligne 3 "For Each cheval In tabChevaux". Mais comment est défini "cheval" ?

Merci

[jurassic pork]

J'ai quand même une petite question : dans ton code VBA, tu mets en ligne 3 "For Each cheval In tabChevaux". Mais comment est défini "cheval" ?

C'est vrai que j'aurai dû définir la variable Cheval au début de la fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Dim Cheval

Si il y a Option Explicit au début du module VBA, il y a une erreur de compilation si la variable n'est pas déclarée. Mais ne je n'avais pas mis cette option.

[Gremlins49]

Merci, ça marche nickel dans mon fichier.

Bon week-end

[Raccourcix]

Bonjour

c'est également possible sans VBA avec cette formule en E2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX(Chevaux!$A$2:$A$4;EQUIV(1;EQUIV("*"&Chevaux!$A$2:$A$4&"*";Opérations!B2;0);0))

Stéphane

[jurassic pork]

Hello,

c'est également possible sans VBA avec cette formule en E2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX(Chevaux!$A$2:$A$4;EQUIV(1;EQUIV("*"&Chevaux!$A$2:$A$4&"*";Opérations!B2;0);0))

Quelle version d'Excel utilises-tu ? Parce qu'en Excel 2016 chez moi cela ne fonctionne pas ce qui est logique puisque dans cette version le premier argument de la fonction EQUIV est une valeur ( pas une plage ou une matrice).

Ami calmant, J.P

[Raccourcix]

Bonjour
Je suis sur 365.
C'est en effet une fonction matricielle à valider avec CTRL+MAJ+ENTREE sur les versions antérieures

EQUIV(*nom*;texte;0) va tester la présence du nom dans un texte
Et renverra 1 s'il trouve
EQUIV(1;... ) Renverra la position du premier 1
INDEX renverra le nom
Stephane

[jurassic pork]

Je suis sur 365.
C'est en effet une fonction matricielle à valider avec CTRL+MAJ+ENTREE sur les versions antérieures

OK cela fonctionne avec des formules matricielles.
Et pour clore le chapitre voici une solution en utilisant Power Query :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
let    Source = Excel.CurrentWorkbook(),
    #"TabChevaux" = Source{[Name="TabChevaux"]}[Content],
    #"TabOpérations" = Source{[Name="TabOpérations"]}[Content],
    #"TabOpModifiée" = Table.TransformColumnTypes(TabOpérations,{{"Date", type date}, {"Libellé", type text}, {"Code", type text}, {"Montant", Int64.Type}}),
    #"ColVerifCheval" = Table.AddColumn(#"TabOpModifiée", "VerifCheval", each List.Transform(#"TabChevaux"
[Liste chevaux] , (x) => Text.Contains([Libellé], x) )),
    #"ColFindCheval" = Table.AddColumn(#"ColVerifCheval", "Contient_cheval", each List.AnyTrue([VerifCheval]) ),
    #"ColCheval" = Table.AddColumn(#"ColFindCheval", "Cheval", each try #"TabChevaux" {List.PositionOf([VerifCheval], true)} 
[Liste chevaux] otherwise null),
    #"Colonnes supprimées" = Table.RemoveColumns(ColCheval,{"VerifCheval", "Contient_cheval"})
   in
     #"Colonnes supprimées"

Voici une capture d'écran où il y a les trois solutions (solution Fonction personnalisée VBA en colonne E, solution Formules en colonne F, solution Power Query dans la table du bas) :



Ami calmant, J.P

[Raccourcix]

Bonjour
Très bonne idée JP de passer par Power Query

Je propose une solution qui me semble plus simple à mettre en œuvre

On charge les deux tables
On ajoute une nouvelle colonne à la table des opérations = tables des chevaux
on développe cette table
on filtre sur les opérations qui contiennent le nom du cheval (subtilité : comme on ne peut pas sélectionner directement une colonne, on écrit en dur un nom puis dans la barre de formule on indique le nom du champ

TabChevaux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
let
    Source = Excel.CurrentWorkbook(){[Name="TabChevaux"]}[Content]
in
    Source

TabOpérations :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
let
    Source = Excel.CurrentWorkbook(){[Name="TabOpérations"]}[Content],
    #"Personnalisée ajoutée" = Table.AddColumn(Source, "Personnalisé", each TabChevaux),
    #"Personnalisé développé" = Table.ExpandTableColumn(#"Personnalisée ajoutée", "Personnalisé", {"Liste chevaux"}, {"Liste chevaux"}),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisé développé", each Text.Contains([Libellé], [Liste chevaux]))
in
    #"Lignes filtrées"

Stéphane

[jurassic pork]

Hello,
bravo ! Effectivement cela fonctionne et simplifie la requête. Par contre l'inconvénient de Power Query c'est qu'on a en fait une table résultante et donc on ne travaille pas directement sur la table de départ. Si on fait une modif dans la table de départ il faut actualiser la requête pour que cela se répercute sur la table de destination. Avec les formules on a directement le résultat sur la table de départ.
J'ai une question concernant Power Query. Quel est l'inconvénient d'utiliser des variables tables dans une requête pour éviter de définir d'autres requêtes.
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
let    Source = Excel.CurrentWorkbook(),
    TabOpérations = Source{[Name="TabOpérations"]}[Content],
    TabChevaux = Source{[Name="TabChevaux"]}[Content],
    #"Personnalisée ajoutée" = Table.AddColumn(TabOpérations, "Personnalisé", each TabChevaux),
    #"Personnalisé développé" = Table.ExpandTableColumn(#"Personnalisée ajoutée", "Personnalisé", {"Liste chevaux"}, {"Liste chevaux"}),
    #"Lignes filtrées" = Table.SelectRows(#"Personnalisé développé", each Text.Contains([Libellé], 
[Liste chevaux]))
in
    #"Lignes filtrées"

Cela évite de définir la requête TabChevaux.

Ami calmant, J.P

[Raccourcix]

Pour l'actualisation automatique, on peut mettre une ligne de code dans Worksheet_Change

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveWorkbook.Connections("Requête - xxxx").Refresh

Concernant la requête PQ, il y a plein de façons de faire et, si les temps de réponses sont longs, il faut tester différentes possibilités

Personnellement sur le forum je préfère mettre le code le plus proche possible de ce qui est généré par l'outil afin de me rapprocher du "débutant" en touchant peu au code.
D'autre part, ce tableau "chevaux" peut servir dans différentes requêtes, donc, autant le distinguer.

Stéphane