IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Extraire une donnée d'une cellule par rapport à une liste


Sujet :

Excel

  1. #1
    Nouveau Candidat au Club
    Femme Profil pro
    Assistant aux utilisateurs
    Inscrit en
    décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Assistant aux utilisateurs
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : décembre 2020
    Messages : 4
    Points : 1
    Points
    1
    Par défaut Extraire une donnée d'une cellule par rapport à une liste
    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
    Fichiers attachés Fichiers attachés

  2. #2
    Expert éminent
    Avatar de jurassic pork
    Homme Profil pro
    Bidouilleur
    Inscrit en
    décembre 2008
    Messages
    2 758
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Bidouilleur
    Secteur : Industrie

    Informations forums :
    Inscription : décembre 2008
    Messages : 2 758
    Points : 6 801
    Points
    6 801
    Par défaut
    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 :
    Nom : Chevaux.PNG
Affichages : 50
Taille : 25,1 Ko

    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
    Fichiers attachés Fichiers attachés
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  3. #3
    Nouveau Candidat au Club
    Femme Profil pro
    Assistant aux utilisateurs
    Inscrit en
    décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Assistant aux utilisateurs
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : décembre 2020
    Messages : 4
    Points : 1
    Points
    1
    Par défaut
    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 !

  4. #4
    Expert éminent
    Avatar de jurassic pork
    Homme Profil pro
    Bidouilleur
    Inscrit en
    décembre 2008
    Messages
    2 758
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Bidouilleur
    Secteur : Industrie

    Informations forums :
    Inscription : décembre 2008
    Messages : 2 758
    Points : 6 801
    Points
    6 801
    Par défaut
    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
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  5. #5
    Nouveau Candidat au Club
    Femme Profil pro
    Assistant aux utilisateurs
    Inscrit en
    décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Assistant aux utilisateurs
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : décembre 2020
    Messages : 4
    Points : 1
    Points
    1
    Par défaut
    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

  6. #6
    Expert éminent
    Avatar de jurassic pork
    Homme Profil pro
    Bidouilleur
    Inscrit en
    décembre 2008
    Messages
    2 758
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Bidouilleur
    Secteur : Industrie

    Informations forums :
    Inscription : décembre 2008
    Messages : 2 758
    Points : 6 801
    Points
    6 801
    Par défaut
    Citation Envoyé par Gremlins49 Voir le message
    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 :
    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.
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  7. #7
    Nouveau Candidat au Club
    Femme Profil pro
    Assistant aux utilisateurs
    Inscrit en
    décembre 2020
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Maine et Loire (Pays de la Loire)

    Informations professionnelles :
    Activité : Assistant aux utilisateurs
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : décembre 2020
    Messages : 4
    Points : 1
    Points
    1
    Par défaut
    Merci, ça marche nickel dans mon fichier.

    Bon week-end

  8. #8
    Membre éprouvé
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    425
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur
    Secteur : Finance

    Informations forums :
    Inscription : mars 2015
    Messages : 425
    Points : 1 179
    Points
    1 179
    Par défaut
    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

  9. #9
    Expert éminent
    Avatar de jurassic pork
    Homme Profil pro
    Bidouilleur
    Inscrit en
    décembre 2008
    Messages
    2 758
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Bidouilleur
    Secteur : Industrie

    Informations forums :
    Inscription : décembre 2008
    Messages : 2 758
    Points : 6 801
    Points
    6 801
    Par défaut
    Hello,
    Citation Envoyé par Raccourcix Voir le message
    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
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  10. #10
    Membre éprouvé
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    425
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur
    Secteur : Finance

    Informations forums :
    Inscription : mars 2015
    Messages : 425
    Points : 1 179
    Points
    1 179
    Par défaut
    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

  11. #11
    Expert éminent
    Avatar de jurassic pork
    Homme Profil pro
    Bidouilleur
    Inscrit en
    décembre 2008
    Messages
    2 758
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Bidouilleur
    Secteur : Industrie

    Informations forums :
    Inscription : décembre 2008
    Messages : 2 758
    Points : 6 801
    Points
    6 801
    Par défaut
    Citation Envoyé par Raccourcix Voir le message
    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) :

    Nom : ChevauxGremlins.PNG
Affichages : 28
Taille : 49,2 Ko

    Ami calmant, J.P
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  12. #12
    Membre éprouvé
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    425
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur
    Secteur : Finance

    Informations forums :
    Inscription : mars 2015
    Messages : 425
    Points : 1 179
    Points
    1 179
    Par défaut
    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

  13. #13
    Expert éminent
    Avatar de jurassic pork
    Homme Profil pro
    Bidouilleur
    Inscrit en
    décembre 2008
    Messages
    2 758
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Bidouilleur
    Secteur : Industrie

    Informations forums :
    Inscription : décembre 2008
    Messages : 2 758
    Points : 6 801
    Points
    6 801
    Par défaut
    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
    Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

  14. #14
    Membre éprouvé
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    425
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur
    Secteur : Finance

    Informations forums :
    Inscription : mars 2015
    Messages : 425
    Points : 1 179
    Points
    1 179
    Par défaut
    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

Discussions similaires

  1. [XL-2007] Incrémentation de cellule par rapport à une autre
    Par schmitx dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 08/01/2010, 11h25
  2. [XL-2003] Recherche de cellules par rapport à une autre
    Par martinmacfly dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 16/12/2009, 14h27
  3. [XL-2007] Conditionnal formating d'une cellule par rapport à une autre
    Par rouget dans le forum Excel
    Réponses: 5
    Dernier message: 10/11/2009, 23h54
  4. selection cellule par rapport a une chaine de caractere
    Par fullmetalknet dans le forum Conception
    Réponses: 30
    Dernier message: 06/11/2009, 09h30
  5. Copier/coller d'une cellule par rapport à une autre
    Par AzelRoth dans le forum Macros et VBA Excel
    Réponses: 5
    Dernier message: 26/02/2009, 15h54

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo