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

pmid20

XLDnaute Nouveau
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 !
 

Pièces jointes

  • Exemple.xlsx
    12.7 KB · Affichages: 6

pmid20

XLDnaute Nouveau
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

XLDnaute Occasionnel
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é")
 

Pièces jointes

  • Exemple.xlsx
    14.6 KB · Affichages: 5

Franc58

XLDnaute Occasionnel
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.

VB:
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
 

Discussions similaires

Statistiques des forums

Discussions
313 201
Messages
2 096 178
Membres
106 517
dernier inscrit
oubourigue