XL 2016 identifier chaine de caractère dans une cellule et ramener une donnée d'une table de correspondance

[pmid20]

Bonjour à tous,
J'ai recherché dans le forum, mais je ne parviens pas à trouver de réponse satisfaisante (ou adapter les formules trouvées pour mon cas précis).
Je souhaite identifier dans une liste (de 30000 à 40000 lignes) des chaines de caractères présents dans une cellule et ramener un mot clé.
Pour être plus clair je vous joins un fichier.
Je souhaiterai rechercher dans la colonne E de l'onglet "données" des chaînes de caractères que l'on retrouve dans l'onglet "table mot clés" en colonne B (la liste n'est pas exhaustive et doit pouvoir évoluer), et ramener en colonne H de l'onglet "données" la valeur associée en colonne C de l'onglet "table mot clés".
Si possible sans passer par des macros.

Merci à tous par avance pour votre aide précieuse !

 

[DJunqueira]

Bonjour,
Une formule.

=SIERREUR(INDEX('TABLE MOTS CLES'!C:C;AGREGAT(14;6;1/(1/(ESTNUM(CHERCHE('TABLE MOTS CLES'!$B$3:$B$15;données!E2))*LIGNE('TABLE MOTS CLES'!$B$3:$B$15)));1));"pas trouvé")

 

[pmid20]

Merci DJunqueira pour ce retour.
ça fonctionne, mais si je veux que ma liste de mots clé soit évolutive, il faudrait que je puisse sélectionner l'ensemble de la colonne B au lieu de la verrouiller sur 'TABLE MOTS CLES'!$B$3:$B$15. Est-ce qu'une telle modification te semble possible ?

 

[DJunqueira]

Les performances de la feuille de calcul ont tendance à ralentir si vous abusez de la sélection de colonnes entières. Il est de bonne pratique de limiter cette utilisation; au début, les feuilles de calcul ne comportaient pas beaucoup de lignes, mais il y en a désormais plus d'un million (1.048.576).

La formule s'adapte à n'importe quelle quantité tant qu'il n'y a pas d'espace vide entre les valeurs de la colonne B.

=SIERREUR(INDEX('TABLE MOTS CLES'!C:C;AGREGAT(14;6;1/(1/(ESTNUM(CHERCHE('TABLE MOTS CLES'!$B$3:$B$1800;données!E2))*LIGNE(INDIRECT("B3:B"&NBVAL('TABLE MOTS CLES'!$B$3:$B$1800)))));1));"pas trouvé")

 

[Franc58]

Salut, je sais que tu ne voulais pas de VBA mais je te le propose quand même, la macro s'adapte en fonction de la taille de la table en colonne B ou des données en colonne E, qu'il y ait des vides ou pas importe peu. Tu n'as qu'à insérer la macro dans un module standard et l'associer à un bouton ou lancer manuellement. A toi de voir.

Sub RechercheMotCle()
    Dim wsDonnees As Worksheet
    Dim wsMotsCles As Worksheet
    Dim rngDonnees As Range
    Dim rngMotsCles As Range
    Dim dict As Object
    Dim data As Variant
    Dim i As Long
    Dim mot As Variant
    Dim motTrouve As String

    ' Désactiver certaines fonctionnalités pour améliorer les performances
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' Feuilles de travail
    Set wsDonnees = ThisWorkbook.Sheets("données")
    Set wsMotsCles = ThisWorkbook.Sheets("TABLE MOTS CLES")

    ' Plages de données
    Set rngDonnees = wsDonnees.Range("E2:E" & wsDonnees.Cells(wsDonnees.Rows.Count, "E").End(xlUp).Row)
    Set rngMotsCles = wsMotsCles.Range("B2:C" & wsMotsCles.Cells(wsMotsCles.Rows.Count, "B").End(xlUp).Row)

    ' Créer un dictionnaire pour stocker les mots clés et leurs valeurs associées
    Set dict = CreateObject("Scripting.Dictionary")
    For Each mot In rngMotsCles
        dict(mot.Value) = mot.Offset(0, 1).Value
    Next mot

    ' Stocker les données dans un tableau
    data = rngDonnees.Value

    ' Parcourir chaque cellule dans le tableau
    For i = 1 To UBound(data, 1)
        motTrouve = ""
        ' Parcourir chaque mot clé dans le dictionnaire
        For Each mot In dict.Keys
            ' Si le mot clé est trouvé dans la cellule
            If InStr(1, data(i, 1), mot, vbTextCompare) > 0 Then
                ' Stocker la valeur associée
                motTrouve = dict(mot)
                Exit For
            Else
                motTrouve = "Pas trouvé"
            End If
        Next mot
        ' Copier la valeur associée en colonne H
        data(i, 1) = motTrouve
    Next i

    ' Écrire les résultats dans la colonne H
    wsDonnees.Range("H2:H" & wsDonnees.Cells(wsDonnees.Rows.Count, "E").End(xlUp).Row).Value = data

    ' Réactiver les fonctionnalités désactivées
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

 

[pmid20]

Merci beaucoup pour vos retours ... Et je vais tenter de voir ce qu'il en est avec la macro ; en plus avec le détail que tu communiques, je devrais comprendre ;-)