Recherche de données hiérarchisées et séquencées 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 :


Recherches hiérarchiques

Cette nouvelle astuce Excel est l'occasion de découvrir une technique de recherche toute particulière. Elle intervient lorsque les informations sont rangées par séquences.



Dans l'exemple illustré par la capture, il est question de compléter explicitement un tableau des scores obtenus par pays. C'est le nom du pays correspondant à l'abréviation fournie en première colonne qui doit être importé en deuxième colonne. Ce nom doit être répété sur les lignes du dessous tant que l'abréviation, qui n'est fournie qu'une seule fois, n'a pas changé. La complexité consiste donc à engager une recherche sur une cellule figée qui doit automatiquement se déverrouiller au signal du changement détecté.

Classeur Excel à télécharger
Pour la mise en place de cette nouvelle astuce, nous proposons d'appuyer l'étude sur un classeur offrant ces données à manipuler.

• Télécharger le classeur recherches-avec-trous.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons un classeur constitué de deux feuilles. La première héberge le tableau à compléter. C'est dans la deuxième colonne qui est vide, que doivent être rapatriés les noms des pays correspondant à l'abréviation fournie en colonne D. Il s'agit donc d'une importation séquencée à réaliser. L'autre tableau est situé sur la seconde feuille nommée Pays.



Il s'agit du tableau de référence pour la recherche. C'est lui qui fournit les correspondances entre les abréviations et les noms des pays. Sa plage de cellules est nommée nomsPays. Vous pouvez le vérifier en déployant la zone Nom en haut à gauche de la feuille Excel.

Recherche séquencée de l'abréviation
Nous l'avons dit, la recherche doit se faire sur l'abréviation. Et nous le savons, le problème tient au fait que ce code n'est pas répété sur le nombre de lignes à importer. Pour simplifier la compréhension de la formule finale, nous proposons de produire un premier calcul qui sera à intégrer dans la syntaxe aboutie. Son rôle est de trouver quel est le code de recherche, tant que celui-ci n'a pas changé, au gré de la réplication de la formule sur les lignes du dessous. Pour cela, il suffit d'engager une astuce que nous avons déjà démontrée à plusieurs reprises. Elle consiste à chercher une information qui ne pourra jamais être trouvée car trop grande. La fonction de recherche se rabattra alors sur la plus proche dans la plage de cellules qui doit grandir avec le calcul répliqué.

• Revenir sur la première feuille nommée Résultats,
• Sélectionner la case du premier pays à importer en cliquant sur sa cellule E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction généraliste de recherche, suivie d'une parenthèse, soit : Recherche(,

Nous pourrions tout aussi bien choisir d'exploiter la fonction RechercheV mais sa syntaxe serait légèrement plus longue.

• En guise de valeur cherchée, taper le texte suivant entre guillemets : "zzz",

Aucun texte et donc aucune des abréviations cherchées ne sera aussi grande alphabétiquement. Donc, cette donnée ne sera jamais trouvée. Et la fonction Recherche aura la bonne idée de se recaler sur la dernière information textuelle trouvée dans la plage grandissante. C'est elle que nous devons précisément renseigner désormais.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Cliquer tout d'abord sur la cellule de la première abréviation, soit D4,
• Puis, taper le symbole deux points(:) pour générer la plage D4:D4,
• Cliquer entre la lettre D et le chiffre 4 de la première référence de cette plage,

Ainsi, la borne supérieure de la plage est désignée indépendamment de la borne inférieure.

• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $D$4:D4,

C'est ainsi que la plage de recherche va grandir en même temps que le calcul sera répliqué sur les lignes du dessous. La borne inférieure va effectivement suivre le déplacement de la formule tandis que la borne supérieure ne va pas bouger. Donc, les codes à rechercher vont être découverts et considérés au fur et à mesure.

• Cliquer à la fin de la syntaxe pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction Recherche,
• Puis, valider la formule avec le raccourci clavier CTRL + Entrée,

Ainsi et vous le savez, nous gardons active la cellule du résultat pour l'exploiter aussitôt.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur la hauteur du tableau,

Comme vous pouvez l'apprécier, la séquence des codes qui doivent servir de recherche aux noms des pays est parfaitement reconstruite, en fonction des changements observés en première colonne.

Importer les séquences des noms de Pays
Le plus dur est donc quasiment fait. C'est le résultat livré par ce premier calcul qui doit être utilisé comme élément de recherche à fournir en premier argument de la fonction RechercheV. Et cette recherche doit être exercée dans le tableau de la seconde feuille, reconnu sous l'intitulé nomsPays.

• Sélectionner de nouveau le premier résultat en cliquant sur sa cellule E4,
• Dans la barre de formule, cliquer juste après le symbole égal pour y placer le point d'insertion,
• Inscrire la fonction de recherche verticale suivie d'une parenthèse, soit : RechercheV(,

Ainsi, l'élément cherché est d'ores et déjà fourni grâce à notre précédent calcul.

• Cliquer à la toute fin de la syntaxe pour y placer le point d'insertion,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner ce dernier en inscrivant son nom, soit : nomsPays,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne d'extraction,
• Inscrire le chiffre 2 pour désigner la deuxième colonne du tableau,
• Taper un point-virgule suivi du booléen Faux, soit : ;Faux, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répandre la logique sur tout le tableau,

Comme vous pouvez le voir, malgré la contrainte du code de recherche non répété, nous avons réussi à réaliser cette extraction séquencée sur la base d'une seule formule, dont la syntaxe reste relativement simple : =RECHERCHEV(RECHERCHE("zzz";$D$4:D4); nomsPays; 2; FAUX).

Bien sûr, si vous modifiez l'ordre des abréviations ou encore leur fréquence d'enchaînement, les calculs d'extraction s'ajustent parfaitement et automatiquement.

Enfin, sachez qu'une autre solution aurait été possible. Elle consiste à tenter l'extraction lorsque le code est trouvé et à prélever la valeur précédemment extraite le cas échéant, soit lorsque la fonction de recherche retourne une erreur, selon la syntaxe suivante : =SIERREUR(RECHERCHEV(D4; nomsPays; 2; FAUX); E3)