Rechercher des mots clés dans le texte

La recherche de mots-clés dans le texte source est l'une des tâches les plus courantes lors de l'utilisation de données. Regardons sa solution de plusieurs manières en utilisant l'exemple suivant :

Supposons que vous et moi ayons une liste de mots clés - les noms de marques de voitures - et un grand tableau de toutes sortes de pièces détachées, où les descriptions peuvent parfois contenir une ou plusieurs de ces marques à la fois, si la pièce de rechange convient à plusieurs marque de voiture. Notre tâche consiste à rechercher et à afficher tous les mots-clés détectés dans les cellules voisines via un caractère de séparation donné (par exemple, une virgule).

Méthode 1. Power Query

Bien sûr, nous transformons d'abord nos tableaux en dynamique ("intelligent") à l'aide d'un raccourci clavier Ctrl+T ou des commandes Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau), donnez-leur des noms (par exemple Estampesи Pièces de rechange) et chargez-les un par un dans l'éditeur Power Query en sélectionnant dans l'onglet Données – À partir du tableau/de la plage (Données – À partir du tableau/de la plage). Si vous avez des versions plus anciennes d'Excel 2010-2013, où Power Query est installé en tant que complément séparé, le bouton souhaité se trouvera sur l'onglet Requête d'alimentation. Si vous avez une toute nouvelle version d'Excel 365, le bouton À partir du tableau/plage appelé là-bas maintenant Avec des feuilles (De la feuille).

Après avoir chargé chaque table dans Power Query, nous retournons dans Excel avec la commande Accueil — Fermer et charger — Fermer et charger dans… — Créer une connexion uniquement (Accueil — Fermer et charger — Fermer et charger dans… — Créer uniquement une connexion).

Créons maintenant une demande en double Pièces de rechangeen faisant un clic droit dessus et en sélectionnant Demande en double (Requête en double), puis renommez la demande de copie résultante en Les résultats et nous continuerons à travailler avec lui.

La logique des actions est la suivante :

1. Dans l'onglet Avancé Ajout d'une colonne choisir une équipe Colonne personnalisée (Ajouter une colonne — Colonne personnalisée) et entrez la formule = Marques. Après avoir cliqué sur OK nous aurons une nouvelle colonne, où dans chaque cellule il y aura un tableau imbriqué avec une liste de nos mots-clés - marques de constructeurs automobiles :

   

2. Utilisez le bouton avec des doubles flèches dans l'en-tête de la colonne ajoutée pour développer tous les tableaux imbriqués. Dans le même temps, les lignes avec des descriptions de pièces de rechange se multiplieront par un multiple du nombre de marques, et nous obtiendrons toutes les paires-combinaisons possibles de « pièce de rechange-marque » :

   

3. Dans l'onglet Avancé Ajout d'une colonne choisir une équipe Colonne conditionnelle (Colonne conditionnelle) et définissez une condition pour vérifier l'occurrence d'un mot-clé (marque) dans le texte source (description de la partie) :

   

4. Pour rendre la recherche insensible à la casse, ajoutez manuellement le troisième argument dans la barre de formule Compare.OrdinalIgnoreCase à la fonction de vérification des occurrences Texte.Contient (si la barre de formule n'est pas visible, elle peut être activée dans l'onglet Avis):

   

5. Nous filtrons le tableau résultant, en ne laissant que ceux dans la dernière colonne, c'est-à-dire les correspondances et supprimons la colonne inutile Occurrences.
6. Regroupement de descriptions identiques avec la commande Par groupe languette De La Carrosserie (Transformer — Grouper par). En tant qu'opération d'agrégation, choisissez Toutes les lignes (Toutes les lignes). En sortie, nous obtenons une colonne avec des tableaux, qui contient tous les détails pour chaque pièce de rechange, y compris les marques de constructeurs automobiles dont nous avons besoin :

   

7. Pour extraire les notes de chaque pièce, ajoutez une autre colonne calculée dans l'onglet Ajout d'une colonne - Colonne personnalisée (Ajouter une colonne — Colonne personnalisée) et utiliser une formule constituée d'un tableau (elles se trouvent dans notre colonne Détails) et le nom de la colonne extraite :

   

8. Nous cliquons sur le bouton avec des doubles flèches dans l'en-tête de la colonne résultante et sélectionnons la commande Extraire des valeurs (Extraire les valeurs)pour produire des tampons avec n'importe quel caractère délimiteur que vous voulez :

   

9. Suppression d'une colonne inutile Détails.
10. Pour ajouter au tableau résultant les pièces qui en ont disparu, où aucune marque n'a été trouvée dans les descriptions, nous effectuons la procédure de combinaison de la requête Résultat avec demande originale Pièces de rechange bouton (dans la fenêtre de contrôle qui apparaît maintenant) Combiner languette Accueil (Accueil — Fusionner les requêtes). Type de connexion - Jointure externe à droite (Jointure externe droite):

    

11. Il ne reste plus qu'à supprimer les colonnes supplémentaires et renommer-déplacer les autres - et notre tâche est résolue :

    
    

Méthode 2. Formules

Si vous avez une version d'Excel 2016 ou une version ultérieure, notre problème peut être résolu de manière très compacte et élégante en utilisant la nouvelle fonction COMBINER (JOINTURE TEXTE):

La logique derrière cette formule est simple :

• Fonction Rechercher (TROUVER) recherche tour à tour l'occurrence de chaque marque dans la description courante de la pièce et renvoie soit le numéro de série du symbole à partir duquel la marque a été trouvée, soit l'erreur #VALEUR ! si la marque n'est pas dans la description.
• Puis en utilisant la fonction IF (SI) и EOSHIBKA (ISERREUR) nous remplaçons les erreurs par une chaîne de texte vide "", et les nombres ordinaux des caractères par les noms de marque eux-mêmes.
• Le tableau résultant de cellules vides et de marques trouvées est assemblé en une seule chaîne via un caractère de séparation donné à l'aide de la fonction COMBINER (JOINTURE TEXTE).

Comparaison des performances et mise en mémoire tampon des requêtes Power Query pour l'accélération

Pour les tests de performance, prenons un tableau de 100 descriptions de pièces de rechange comme données initiales. Sur celui-ci nous obtenons les résultats suivants :

• Temps de recalcul par formules (Méthode 2) – 9 sec. lorsque vous copiez la formule pour la première fois dans la colonne entière et 2 sec. à répété (tampon affecte, probablement).
  
• Le temps de mise à jour de la requête Power Query (Méthode 1) est bien pire - 110 secondes.
  

Bien sûr, beaucoup dépend du matériel d'un PC particulier et de la version installée d'Office et des mises à jour, mais l'image globale, je pense, est claire.

Pour accélérer une requête Power Query, tamponnons la table de recherche Estampes, car il ne change pas dans le processus d'exécution de la requête et il n'est pas nécessaire de le recalculer constamment (comme le fait de facto Power Query). Pour cela on utilise la fonction Table.BufferTable.Buffer à partir du langage Power Query intégré M.

Pour cela, ouvrez une requête Les résultats et sur l'onglet Avis appuie sur le bouton Éditeur avancé (Affichage — Éditeur avancé). Dans la fenêtre qui s'ouvre, ajoutez une ligne avec une nouvelle variable Marcy 2, qui sera une version tamponnée de notre répertoire de constructeurs automobiles, et utilisera cette nouvelle variable plus tard dans la commande de requête suivante :

Après un tel raffinement, la vitesse de mise à jour de notre demande augmente de près de 7 fois - jusqu'à 15 secondes. Tout autre chose 🙂

• Recherche de texte flou dans Power Query
• Remplacement de texte en bloc avec des formules
• Remplacement de texte en bloc dans Power Query avec la fonction List.Accumulate