Extraction par fragments de textes avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Extraire des données selon des informations textuelles

Dans la formation précédente, nous avons découvert les fonctions qu'offrait Excel pour manipuler les cellules de texte. Ainsi, nous étions parvenus, en imbriquant plusieurs fonctions de calculs, à prélever dynamiquement des bouts de chaînes de textes, en fonction de la position de certains caractères remarquables.



Ici, nous proposons de pousser ces connaissances, afin de bâtir le moule d'un petit moteur de recherche, comme l'illustre la capture ci-dessus de l'application finalisée. L'utilisateur n'aura plus qu'à saisir un fragment d'information dans la zone Code chaîne, pour produire le résultat de tous les enregistrements concordants. L'objectif est de proposer de la souplesse à l'exploitation. Il n'est plus nécessaire de devoir préciser sur quel champ de la base de données la recherche doit être faite, comme ce fut le cas lors de la formation permettant d'extraire des données selon des critères recoupés.

Source et présentation du concept
En reconstituant une information de chaîne concaténée, rassemblant les éléments de référence remarquables définissant un produit, à l'instar des codes-barres, nous serons en mesure d'exercer des recherches par mots clés, capables de remonter l'ADN de chaque enregistrement concordant. Pour ce faire, nous avons besoin de manipuler des données relativement conséquentes.

• Télécharger le classeur extraction-fragments-texte.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles. La feuille bd_sorties propose une base de données des idées de sorties répertoriées notamment par département, ville et activité. Elle est composée de 998 lignes. La feuille extraire propose la structure pour la zone de recherche et la zone d'extraction. C'est à partir de cette console que nous bâtirons l'ébauche du moteur de recherche, capable de restituer les enregistrements issus de la feuille bd_sorties, en fonction des indications textuelles saisies.

Construire un code de référence par concaténation
Dans le champ Code ID2 de la feuille bd_sorties (Colonne F), nous proposons de reconstituer un code de référence, représentatif de chaque enregistrement, en fonction de chaque donnée qui le compose. Il doit rassembler un bout d'information sur la raison sociale, le département, l'activité et la ville. Nous obtiendrons ainsi une sorte de signature numérique, comparable à un code barre, représentant chaque idée de sortie. Dès lors, nous pourrons exécuter des recherches de fragments de textes sur ce code. De fait, ces fragments pourront indifféremment faire référence à l'un des quatre champs concaténés ayant permis de construire ce code. Si les fonctions Excel retournent une valeur indiquant que le bout de texte est trouvé dans la chaîne du code, nous pourrons le marquer par un repère numérique afin de pouvoir ensuite l'extraire.

Nous souhaitons créer un code qui assemble les 8 premiers caractères de la raison sociale (Fonction Gauche), avec les deux premiers caractères du département ainsi que les premiers caractères de l'activité jusqu'à l'espace s'il est présent (Fonction Cherche), avec enfin les premiers caractères de la ville jusqu'à l'espace, s'il est présent.

Si la longueur à prélever est plus importante que le texte désigné, la fonction Gauche se contente d'extraire le texte jusqu'à la fin de la chaîne, sans retourner d'erreur. Donc il n'est pas nécessaire d'inclure une gestion d'exception dans ce traitement. En revanche, si l'espace n'est pas trouvé par la fonction Cherche, une erreur est générée. Nous devrons donc l'inclure dans une fonction conditionnelle Si, pour restituer l'intégralité de la cellule le cas échéant. Pour que les fragments de données soient clairement identifiables, nous souhaitons séparer chaque information à l'aide d'un point-virgule. Pour réaliser cette concaténation complexe, nous devons bâtir un calcul faisant intervenir le symbole Et commercial (&), pour assembler chaque élément ainsi prélevé.

• Dans la feuille bd_sorties, sélectionner la première cellule du code, soit F2,
• Taper le symbole = pour débuter le calcul de la concaténation,
• Saisir la fonction permettant de prélever du texte par le début suivie d'une parenthèse, soit gauche(,
• Sélectionner la raison sociale correspondante, soit la cellule B2,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Saisir le chiffre 8 pour extraire les huit premiers caractères de la cellule de texte,
• Fermer la parenthèse de la fonction gauche,
• Taper le Et commercial (&) pour débuter l'assemblage avec les informations à suivre,
• Saisir un point-virgule entre guillemets soit ';', pour séparer les données,
• Taper de nouveau le Et commercial (&) pour poursuivre l'assemblage,
• Saisir de nouveau la fonction pour prélever du début suivie d'une parenthèse, soit gauche(,
• Désigner cette fois le département à prélever, soit la cellule C2,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur,
• Saisir le chiffre 2 pour récolter les deux premiers caractères,
• Fermer la parenthèse de la fonction Gauche,
• Poursuivre la concaténation comme précédemment, soit : & ';' &,
• Taper la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir la fonction pour prélever du départ suivie d'une parenthèse, soit gauche(,
• Désigner la cellule de l'activité comme texte à prélever, soit D2,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur,
• Taper la fonction trouvant la position d'une occurrence suivie d'une parenthèse, soit CHERCHE(,
• Saisir un espace entre guillemets (' '), pour définir le caractère cherché,
• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Sélectionner de nouveau la cellule D2 de l'activité pour indiquer l'endroit de la recherche,
• Fermer la parenthèse de la fonction Cherche,
• Retrancher une unité, soit -1, pour ne pas inclure l'espace dans le prélèvement,
• Fermer la parenthèse de la fonction Gauche,
• Taper un point-virgule (;) pour passer dans la branche de la gestion d'erreur de la fonction SiErreur,
• Sélectionner de nouveau D2 pour prélever toute l'activité lorsque l'espace n'est pas trouvé,
• Fermer la parenthèse de la fonction SiErreur,
• Poursuivre la concaténation comme précédemment, soit & ';' &,
• Taper de nouveau la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir la fonction pour prélever un texte depuis le début suivie d'une parenthèse, soit Gauche(,
• Désigner la cellule de la ville, soit E2,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Saisir la fonction pour trouver la position de l'espace suivie d'une parenthèse, soit Cherche(,
• Taper un espace entre guillemets, soit ' ', pour indiquer le caractère à chercher,
• Taper un point-virgule (;) pour passer dans l'argument de la cellule de recherche,
• Sélectionner de nouveau la cellule E2 de la ville,
• Fermer la parenthèse de la fonction Cherche,
• Retrancher une unité à cette position retournée, soit -1,
• Fermer la parenthèse de la fonction Gauche,
• Taper un point-virgule (;) pour passer dans la branche de la gestion d'erreur,
• Sélectionner de nouveau E2 pour prélever toute la ville lorsque l'espace n'est pas trouvé,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par le raccourci CTRL + Entrée pour conserver la cellule active ,
• Double cliquer sur la poignée de la cellule pour répliquer le calcul sur la hauteur de la base de données,

La formule que nous avons bâtie est la suivante :

=GAUCHE(B2;8) & ';' & GAUCHE(C2;2) & ';' & SIERREUR(GAUCHE(D2; CHERCHE(' ';D2)-1);D2) & ';' & SIERREUR(GAUCHE(E2; CHERCHE(' ';E2)-1);E2)

Bien qu'elle soit un peu longue, elle consiste en une répétition des mêmes techniques, soit pour prélever un bout de chaîne, soit pour trouver la position d'une occurrence. Le tout est assemblé par concaténation. Les parties intéressantes résident dans l'imbrication des fonctions Cherche et Gauche, elles-mêmes imbriquées dans la fonction Excel de gestion d'erreur :

SIERREUR(GAUCHE(D2; CHERCHE('';D2)-1);D2)

Dans la portion de formule illustrée ci-dessus, nous prélevons l'information sur l'activité (GAUCHE(D2), jusqu'au premier espace trouvé (CHERCHE(' ';D2)-1). Si une erreur est générée, cela signifie qu'aucun espace n'est présent dans la chaîne. Dans ce cas, l'information complète est restituée depuis le second argument de la fonction SiErreur.

Quoiqu'il en soit et comme l'illustre la capture ci-dessus, vous remarquez que tous les codes, propres à chaque enregistrement sont parfaitement construits sur la hauteur de la base de données. Nous souhaitons néanmoins apporter deux améliorations. La première consiste à homogénéiser la casse pour ne pas afficher des données tantôt en majuscules et tantôt en minuscules. Il suffit d'exploiter la fonction Excel Minuscule : =Minuscule(Texte). La seconde amélioration visuelle consiste à remplacer le symbole slash disgracieux (Hôtel/Restaurant) par un tiret (Hôtel-Restaurant). Pour cela, nous pouvons exploiter la fonction Excel Substitue, selon la syntaxe suivante :

=SUBSTITUE(Chaine_de_caracteres ; Texte_à_remplacer ; Texte_de_remplacement)

Dans les deux cas, les fonctions doivent agir sur le résultat fourni par le calcul précédent. Elles doivent donc imbriquer la formule. Nous proposons de débuter par la conversion de casse en minuscules.

• Sélectionner la première cellule du calcul, soit F2,
• Dans la barre de formule, adapter le calcul comme suit :

=MINUSCULE(GAUCHE(B2;8) & ';' & GAUCHE(C2;2) & ';' & SIERREUR(GAUCHE(D2; CHERCHE(' ';D2)-1);D2) & ';' & SIERREUR(GAUCHE(E2; CHERCHE(' ';E2)-1);E2))

La partie à ajouter est marquée en gras. La fonction Excel Minuscule doit englober le résultat de la concaténation pour la construction du code de référence. Il y a donc une parenthèse après la fonction Minuscule et à la toute fin du calcul précédent.

• Valider cette modification à l'aide du raccourci CTRL + Entrée,
• Puis, double cliquer sur la poignée de la cellule pour répercuter cette adaptation sur tous les enregistrements de la base de données,

Instantanément, les codes de référence ainsi reconstruits deviennent plus présentables dans la mesure où il n'y a plus d'alternance aléatoire de casse.

• Sélectionner de nouveau la cellule F2,
• Dans la barre de formule, après le symbole =, taper le nom de la fonction de remplacement de texte suivi d'une parenthèse, soit substitue(,
• Puis, cliquer à la toute fin de la formule pour y placer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer,
• Saisir le symbole slash entre guillemets, soit '/',
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Saisir un tiret entre guillemets, soit '-',
• Fermer la parenthèse de la fonction Substitue,
• Valider le calcul par CTRL + Entrée et le répliquer sur toute la base de données avec la poignée,

Comme l'illustre la capture ci-dessus, le remplacement de l'occurrence est un succès dans les tous les codes concaténés et uniformisés en minuscules.

Repérer les enregistrements concordants avec la recherche textuelle
Avant de pouvoir réaliser l'extraction des résultats, nous devons marquer tous les enregistrements de la base de données pour lesquels l'occurrence cherchée en cellule B5 de la feuille extraire, est trouvée dans le code de référence reconstitué. Pour cela, nous devons réaliser un calcul intermédiaire exploitant la fonction Cherche sur la colonne F de la base de données. Si le résultat retourné n'est pas une erreur, nous souhaitons marquer la ligne d'un numéro incrémenté à chaque fois qu'un enregistrement concordant est repéré. Cette incrémentation peut se réaliser grâce à la fonction Max appliquée sur la plage du calcul en mouvement, comme nous l'avons vu dans la formation Excel pour extraire tous les enregistrements d'une référence.

Vous l'avez compris les fonctions Si et SiErreur sont aussi nécessaires. La première doit permettre de tester le critère de recherche. La seconde doit permettre de gérer la recherche lorsque l'élément n'est pas trouvé. Nous proposons de bâtir ce calcul intermédiaire de repérage en colonne G de la feuille bd_sorties.

• Sélectionner la cellule G2 de la feuille bd_sorties,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de gestion d'erreur suivi d'une parenthèse, soit SiErreur(,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Taper la fonction pour trouver la position d'une occurrence suivie d'une parenthèse, soit Cherche(,
• Cliquer sur l'onglet extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule B5 de la zone de recherche,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner alors le code de recherche correspondant, soit la cellule F2,
• Fermer la parenthèse de la fonction Cherche,
• Taper l'inégalité : >0, pour vérifier si le texte cherché est trouvé,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction pour trouver le plus grand nombre suivie d'une parenthèse, soit Max(,
• Cliquer sur la cellule G1 située juste au-dessus du calcul,
• Taper le symbole deux points (:) pour générer la plage G1:G1,
• Figer seulement la première des deux références, soit $G$1:G1,
• Fermer la parenthèse de la fonction Max,
• Incrémenter le résultat d'une unité : +1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide lorsque l'information n'est pas trouvée,
• Fermer la parenthèse de la fonction Si,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur,
• Saisir de nouveau deux guillemets ('') pour ne rien inscrire en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par CTRL + Entrée,
• Double cliquer sur la poignée de la cellule pour le répliquer sur toute la base de données,

En l'absence d'indications dans la cellule B5 de la feuille extraire, tous les enregistrements sont repérés avec un numéro incrémenté, puisque tous concordent.

• Cliquer sur l'onglet extraire en bas de la fenêtre Excel pour activer sa feuille,
• En cellule B5, taper le texte : Nice, par exemple,
• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour revenir sur sa feuille,

Cette fois, comme vous le remarquez, seuls les enregistrements pour lesquels l'indication textuelle est trouvée dans le code de référence reconstitué, sont marqués par des numéros incrémentés. Il en va de même si nous avions tapé l'indication 26 pour les enregistrements de la Drôme ou le texte Club pour isoler tous les clubs de forme. Nous constatons donc que quel que soit la nature de la recherche, grâce à cette concaténation et à ce calcul intermédiaire, tous les champs de la base de données sont inclus dans la recherche. Nous apportons de la souplesse à l'utilisateur qui n'a qu'à saisir le mot clé de sa recherche. Nous nous approchons de la philosophie d'un moteur de recherche. La formule que nous avons construite est la suivante :

=SIERREUR(SI(CHERCHE(extraire!$B$5; F2)>0; MAX($G$1:G1) + 1;'');'')

Dans cette syntaxe finale, nous avons supprimé le préfixe de la feuille bd_sorties qui était systématiquement rappelé au clic sur une cellule. Il n'est en effet pas nécessaire de rappeler le nom de la feuille dans laquelle la formule est écrite. Si la position du texte cherché est trouvé dans le code de référence (CHERCHE(extraire!$B$5;F2)>0), alors nous marquons la ligne d'un numéro incrémenté (MAX($G$1:G1)+1). En effet, comme la borne inférieure de la plage de cellules sur laquelle agit la fonction Max, évolue en même temps que le calcul est répliqué, le dernier numéro inscrit est à chaque fois incrémenté.

Pour gérer le cas où la zone de recherche de la feuille extraire est vide, nous devons doubler la condition dans la zone de critère de la fonction Si, grâce à la fonction Excel ET, comme suit :

=SIERREUR(SI(ET(CHERCHE(extraire!$B$5; F2)>0; extraire!$B$5<>'');MAX($G$1:G1) + 1;'');'')

Extraire les enregistrements de la recherche textuelle
L'objectif désormais est de pouvoir parcourir toutes les lignes de la colonne G dans la feuille bd_sorties. A chaque fois qu'un numéro est repéré, l'enregistrement correspondant, champ à champ, doit être importé dans la feuille extraire. Il s'agit donc de réaliser une recherche sur un numéro initialisé à 1 et qui s'incrémente en même temps que le calcul est répliqué, grâce à la fonction Excel Ligne. La fonction Excel Index permet d'importer une information de base de données en fonction de son indice de ligne et de son indice de colonne. L'indice de colonne est connu et fixe. Il dépend de l'information de champ à récupérer. L'indice de ligne varie selon l'emplacement du numéro incrémenté. Il peut être remonté dynamiquement grâce à la recherche par le biais de la fonction Equiv de l'information retournée par la fonction Ligne.

Si vous sélectionnez intégralement la base de données de la feuille bd_sorties, sans la colonne G, vous constatez qu'un nom lui est attribué dans la Zone Nom, à gauche de la barre de formule. Cette plage de cellules est en effet nommée : bds. Nous exploiterons ce nom plus confortable pour la construction de la formule de recherche.

• Cliquer sur l'onglet extraire en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule B8 consistant à récupérer le nom de l'idée de sortie,
• Taper le symbole = pour débuter le calcul,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir la fonction d'extraction de base de données suivie d'une parenthèse, soit Index(,
• Taper le nom de la base de données de recherche, soit bds,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne à extraire,
• Saisir la fonction retournant l'indice de ligne suivie d'une parenthèse, soit Equiv(,
• Pour la valeur cherchée, saisir la fonction retournant le numéro de ligne d'une cellule suivie d'une parenthèse, soit Ligne(,
• Désigner une cellule située en première ligne, par exemple B1,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Cliquer sur l'onglet bd_sorties en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur l'étiquette de la colonne G pour la désigner intégralement, soit bd_sorties!G:G,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche selon une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne de la fonction Index,
• Saisir le chiffre 2 pour désigner en valeur relative la colonne de la raison sociale,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur,
• Saisir deux guillemets ('') pour garder la cellule vide lorsque la recherche est infructueuse,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par CTRL + Entrée puis tirer la poignée de la cellule sur plusieurs dizaines de lignes vers le bas,

Comme l'illustre la capture ci-dessus, toutes les raisons sociales correspondant au texte de recherche sont restituées, les unes sous les autres. La formule que nous avons bâtie est la suivante :

=SIERREUR(INDEX(bds; EQUIV(LIGNE(B1); bd_sorties!G:G; 0); 2); '')

L'extraction est initiée dans la base de données de la feuille bd_sorties (INDEX(bds). La recherche s'effectue sur les numéros incrémentés dans la colonne G (EQUIV(LIGNE(B1); bd_sorties!G:G; 0)). En effet, en répliquant le calcul sur les lignes du dessous, la fonction Ligne déplace les références de la cellule désignée et renvoie des indices incrémentés, correspondant aux numéros recherchés. La colonne de la raison sociale est la deuxième colonne dans la base de données bds (2). Donc, pour produire l'extraction sur les autres champs, il suffit de répliquer exactement le même calcul, en adaptant l'indice de colonne, pour le champ à retourner :

• En C8, adapter le calcul suivant :
  =SIERREUR(INDEX(bds; EQUIV(LIGNE(B1); bd_sorties!G:G; 0); 3);''),
• En D8, adapter le calcul suivant :
  =SIERREUR(INDEX(bds; EQUIV(LIGNE(B1); bd_sorties!G:G; 0); 4);''),
• En E8, adapter le calcul suivant :
  =SIERREUR(INDEX(bds; EQUIV(LIGNE(B1); bd_sorties!G:G; 0); 5);'') ,
• Puis, double cliquer sur les poignées des cellules des résultats afin de les répliquer sur la même hauteur que précédemment,
• En B5, taper le terme de recherche Restaurant,

Bien que la nature de la recherche soit complètement différente, puisqu'il s'agit d'une activité au lieu d'une ville, tous les enregistrements concordants sont immédiatement rapatriés. Cette application offre donc la possibilité de faire varier les termes et la nature de la recherche à souhait, procurant beaucoup de souplesse et d'efficacité à ses utilisateurs. Si nous tapions le terme 83, nous obtiendrions en retour, tous les enregistrements du département du Var.

Cette recherche textuelle a été rendue possible par la reconstitution d'un code de référence, assemblant les termes représentatifs de chaque champ, pour chaque enregistrement. C'est ensuite un calcul intermédiaire qui a permis de les repérer par numéro incrémenté.

Enfin, les fonctions Index et Equiv se sont chargées de l'extraction, sur la base de ce numéro incrémenté. Pour terminer, en cellule E4, nous proposons d'inscrire un résultat de synthèse indiquant le nombre d'enregistrements retournés, pour la recherche demandée. Pour cela, nous proposons de réaliser d'abord un calcul intermédiaire en colonne F, marquant numériquement chaque ligne non vide. Il ne restera plus qu'à compter ces chiffres grâce à la fonction Excel Nb en E4, afin d'obtenir le résultat.

• En cellule F8 de la feuille extraire, bâtir la formule suivante :

=SI(B8<>'';1;'')

• Après validation, double cliquer sur la poignée de la cellule pour répliquer la formule sur la hauteur de l'extraction,

Comme nous l'avions indiqué, à chaque fois qu'une extraction est produite en colonne B, signifiant que la cellule n'est pas vide (B8<>''), nous inscrivons le chiffre 1 dans la cellule du calcul pour marquer la ligne. Il ne reste plus qu'à compter ces chiffres pour en déduire le nombre d'enregistrements résultants.

• En cellule E4, taper la formule suivante :

=NB(F:F)



Pour parfaire l'application, il serait judicieux de masquer la colonne F de la feuille extraire ainsi que la colonne G de la feuille bd_sorties. De même, il serait utile de protéger les cellules du calcul, comme l'explique la formation sur la facturation automatisée avec Excel.

Quoiqu'il en soit, nous avons bien réussi à bâtir l'ébauche d'un moteur de recherche, sur la base de fragments de textes à trouver dans la source de données. Pour les améliorations à considérer, nous pourrions envisager des calculs pour que les caractères latins soient contournés, ou encore que l'utilisateur puisse saisir une succession de mots clés.

Remarque : Le calcul intermédiaire du décompte conditionnel aurait pu être évité grâce à cette formule :

=NB.SI(B:B;'>*<') - 4

Ce critère particulier permet en effet de comptabiliser toutes les cellules non vides sur la colonne B ainsi désignée. Nous retranchons quatre unités afin d'exclure les cellules de titre, présentes dans cette colonne.