Extraire tout sur la recherche d'un fragment d'adresse

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


Sujets que vous pourriez aussi aimer :


Recherches par fragments d'adresses

Lorsque de multiples extractions doivent être entreprises sur des recherches connues et précises, Excel offre de nombreuses solutions. Et d'ailleurs, nous en avons démontrées un certain nombre. Mais dans le cas où la recherche doit être faite par fragments de textes lorsque l'adresse postale n'est pas précisément connue, il devient compliqué de restituer tous les enregistrements. Pourtant, ce sont eux qui pourraient livrer la personne cherchée au beau milieu des résultats. Et c'est bien la prouesse que propose de démontrer cette nouvelle astuce.



Sur l'exemple illustré par la capture, nous travaillons à partir d'une petite base de données de clients référencés avec leurs adresses postales. L'utilisateur recherche l'un d'entre eux sur la base de son adresse dont il sait seulement qu'elle contient le nom De Gaulle. Il saisit ce fragment dans une cellule placée sur la droite de la base de données. Et aussitôt, toutes les informations pour lesquelles ces fragments ont été trouvés dans l'adresse, sont restituées dans un tableau placé juste en-dessous. L'utilisateur n'a plus qu'à analyser cette liste et piocher pour sortir le client convoité.

Classeur Excel à télécharger
Pour simplifier la mise en place de cette astuce Excel, nous suggérons d'appuyer l'étude sur un classeur offrant cette petite base de données des clients avec ces adresses.

• Télécharger le classeur adresse-partielle.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier résultant pour l'ouvrir dans Excel,
• Cliquer sur le bouton Activer la modification du bandeau de sécurité,
• Tout de suite, déployer la zone Nom en haut à gauche de la feuille,

Comme vous pouvez le voir, le nom tab désigne l'intégralité des informations de la base de données. Le nom adr désigne quant à lui toutes les adresses de la colonne Adresse. Nous utiliserons ces noms pour simplifier la construction de la formule matricielle. Et c'est bien une unique formule qu'il s'agit de construire. Sa réplication en lignes comme en colonnes doit fournir toutes les adresses, tous les téléphones et noms des clients attachés à la recherche partielle.

Pour finir les présentations, l'utilisateur doit donc fournir un bout d'adresse en cellule G4. Les extractions concordantes doivent alors être livrées à partir de la ligne 7 entre les colonnes G et I.

Initier l'extraction
Pour réaliser ces extractions multiples, nous avons classiquement besoin d'employer la fonction Excel Index. Comme le nombre des résultats peut varier et pour éviter tout message d'erreur lorsqu'il n'y a plus rien à extraire, nous allons l'embarquer dans la fonction SiErreur.

• Sélectionner la cellule de la première extraction à livrer en cliquant sur la case G7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Pour la matrice de recherche, désigner le tableau des clients par son nom, soit : tab,
• Taper un point-virgule (;) pour passer dans l'argument du numéro de ligne pour l'extraction,

Les lignes dans l'ordre
Dans ce raisonnement matriciel conditionnel à entreprendre, ces lignes concordantes avec le bout d'information, nous souhaitons les extraire dans l'ordre, les unes sous les autres, au gré de la réplication de la formule. Et pour les classer, nous allons exploiter la fonction Petite.Valeur. Nous le verrons, avec une matrice virtuelle de chiffres dans l'argument de son rang, elle permettra de les désigner les unes après les autres.

• Taper le nom de la fonction suivie d'une parenthèse ouvrante, soit : Petite.Valeur(,

La recherche du fragment
Ce ne sont pas toutes les lignes de la base de données qui sont concernées par l'extraction à réaliser dans l'ordre. Il s'agit d'isoler les "petites valeurs" des lignes pour lesquelles l'adresse comporte le fragment de recherche tapé. Donc, en guise de matrice, nous allons fournir à cette fonction Petite.Valeur, une matrice conditionnelle. Son rôle est de repérer et d'isoler toutes les lignes dans lesquelles ces termes partiels sont détectés. Il est donc question d'opérer un test pour minimiser les enregistrements résultants et donc d'appeler la fonction conditionnelle à la rescousse.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,

Le test consiste à vérifier que le fragment a été trouvé et ce pour chaque ligne analysée dans la matrice. La fonction Excel Cherche renseigne sur la position de l'occurrence quand elle existe. Donc, quand le résultat est un succès, elle retourne une valeur numérique. C'est ainsi que nous devons utiliser la fonction logique EstNum pour tester si la valeur renvoyée est bien un nombre, marquant la position trouvée.

• Inscrire la fonction de test logique suivie d'une parenthèse, soit : EstNum(,
• Puis, inscrire la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Cliquer sur la cellule G4 du fragment de recherche pour l'argument du texte cherché,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $G$4,

En effet, nous allons répliquer cette logique matricielle aussi bien en hauteur qu'en largeur. Et malgré ce déplacement, le fragment à rechercher ne doit pas bouger. Il est toujours situé en cellule G4.

• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,

En guide de texte dans lequel chercher, c'est une matrice que nous allons lui fournir. Il s'agit de celle de toutes les adresses.

• Désigner la colonne des adresses par son nom, soit : adr,
• Fermer la parenthèse de la fonction Cherche,
• Puis, fermer la parenthèse de la fonction EstNum,
• Taper ensuite un point-virgule (;) pour passer dans la branche Alors de la fonction Si,

Les numéros de ligne concordants
A chaque fois que ce critère est honoré, donc à chaque fois que le fragment de recherche est trouvé dans la colonne des adresses, nous devons conserver les numéros des lignes concernées. Ce sont ces numéros que la fonction Index exploitera pour fournir les extractions de toutes les concordances les unes en-dessous des autres. Et pour cela, l'astuce consiste à passer à la fonction Ligne, une matrice virtuelle des numéros de lignes sur la même hauteur que le tableau. Rappelons-le, seules les concordantes seront retenues grâce au test.

• Inscrire la fonction donnant la ligne d'une cellule, soit : Ligne(,

Comme cette matrice à construire est virtuelle, nous devons penser à l'interpréter.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Puis, inscrire le chiffre 1 suivi du symbole deux points entre guillemets, soit : "1:",

De cette manière, nous renseignons le point de départ de cette matrice. Elle débute à partir de la première ligne du tableau d'extraction.

• Inscrire le symbole de concaténation (&) pour annoncer la borne inférieure à suivre,

Il est question de considérer toutes les lignes potentielles du tableau. C'est la fonction Lignes (Au pluriel cette fois) qui renseigne sur la quantité de lignes d'une plage.

• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Lignes(,
• Désigner la colonne des adresses par son nom, soit : adr,
• Fermer la parenthèse de la fonction Lignes,
• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Puis, fermer la parenthèse de la fonction Si.

Les lignes d'extractions
De fait, nous sommes de retour dans les bornes de la fonction Petite.Valeur. Nous devons renseigner son argument du rang pour indiquer que les lignes concordantes énumérées par la matrice virtuelle, doivent être prélevées dans l'ordre, au fil de la réplication de la formule sur les lignes du dessous. Nous devons extraire la ligne à l'indice le plus petit puis celle qui suit et la suivante et ainsi desuite. Donc les lignes classées 1, 2, 3 etc... par la fonction Petite.Valeur. Pour cela, nous proposons d'engager la fonction Ligne sur une cellule de la première ligne (comme A1). Ainsi au gré de la réplication, après avoir demandé la numéro 1, elle demandera la 2 puis la 3 etc...

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,
• Puis, donner le numéro d'une cellule de la première ligne, comme suit : Ligne(A1),
• Dès lors, fermer la parenthèse de la fonction Petite.Valeur,

Les colonnes d'extractions
Nous sommes maintenant de retour dans les bornes de la fonction Index. Dans ce raisonnement matriciel, nous venons d'indiquer les lignes à extraire selon la logique de la réplication de la formule. Mais nous ne souhaitons pas extraire seulement les adresses. Il est question de restituer aussi les numéros de téléphone et les noms des clients, sur les deux colonnes de droite. Donc, dans l'argument du numéro de colonne de la fonction Index, nous devons déplacer naturellement la plage d'extraction des mêmes unités, lorsque la formule sera répliquée sur la droite.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Transmettre un numéro de la deuxième colonne comme suit : Colonne(B1),

En effet, l'extraction des adresses débute à partir de la deuxième colonne du tableau d'extraction.

• Fermer alors la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Taper deux guillemets ("") pour garder la cellule vide lorsque la fin des extractions est atteinte,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

A partir de la version 2019, la formule peut être validée naturellement par le touche Entrée. Excel comprend désormais naturellement lorsqu'une logique matricielle est engagée.

Comme vous pouvez le voir, la première adresse tombe. Et il s'agit bien de celle de la première ligne concordante, d'ailleurs surlignée par la règle de mise en forme conditionnelle qui était préconstruite sur cette feuille.

• Tirer la poignée de la cellule du résultat vers le bas jusqu'en G14,
• Puis, tirer la poignée de la sélection vers la droite jusqu'en I14,

Comme vous pouvez l'apprécier, ce sont toutes les informations des clients dont l'adresse abrite le terme Carnot qui sont extraites. Et si vous le remplacez par le mot Place par exemple, ce sont toutes les informations des clients qui habitent sur une place qui sont restituées.