RechercheV matricielle Excel dans de multiples tableaux

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


Sujets que vous pourriez aussi aimer :


RechercheV dans de multiples tableaux

Malgré l'abondance des nouvelles fonctions de recherche et d'extraction, l'antique fonction RechercheV est loin d'être morte. Et nous allons le prouver dans ce volet pour extraire les informations d'une référence qui peut être logée dans un tableau parmi plusieurs autres. Tout l'enjeu consiste donc à faire agir dynamiquement la fonction de recherche sur le bon tableau et ce, avec une formule unique.



Sur l'exemple illustré par la capture, des véhicules sont regroupés par marques dans trois tableaux distincts. Au-dessus des tableaux, l'utilisateur choisit une immatriculation à isoler avec une liste déroulante. Et en même temps qu'une mise en forme dynamique surligne l'automobile cherchée dans le bon tableau, ses références (Marque et Modèle), sont extraites dans les deux cellules placées sur la droite.

Classeur Excel à télécharger
Pour démontrer cette nouvelle astuce Excel, nous suggérons d'appuyer l'étude sur un classeur abritant ces tableaux d'automobiles.

• Télécharger le classeur recherchev-multi-tables.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 retrouvons bien les trois tableaux côtes à côtes entre les colonnes B et J, de la ligne 6 à la ligne 14. La cellule D4 accueille une liste déroulante pour choisir l'une de ces immatriculations. En fonction de ce choix, la marque et le modèle correspondants doivent être extraits depuis le bon tableau, en cellules respectives E4 et F4.

• En haut à gauche de la feuille Excel, déployer la zone Nom,

Quatre noms de plages sont préconstruits pour simplifier la démarche et le repérage.



La plage Marque désigne la liste des marques entre les cellules I1 et I4. Ces marques ne sont autres que les noms respectifs des tableaux hébergeant ces véhicules regroupés. Et nous le verrons, c'est un raisonnement matriciel embarqué dans la fonction RechercheV qui va permettre de les parcourir tous, pour isoler celui de la recherche.

Lancer la recherche
Il est temps d'initier l'extraction sur la recherche de l'immatriculation choisie.

• Cliquer sur la cellule E4 de la marque à trouver,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : RechercheV(,
• Désigner l'immatriculation cherchée en cliquant sur sa cellule D4,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $D$4,

Nous l'avons dit, c'est une seule et même formule qui doit être utilisée pour extraire toutes les informations. Et nous allons répliquer cette logique sur la cellule de droite pour le modèle. Comme la recherche doit toujours être faite sur cette cellule de l'immatriculation choisie, elle ne doit pas bouger.

Désigner le bon tableau
L'étape cruciale de ce calcul arrive à grands pas. En deuxième argument de la fonction RechercheV, nous devons spécifier le tableau de recherche. Et précisément, nous ne le connaissons pas à l'avance. Il est question de confronter tous les tableaux de la plage nommée Marques à la recherche de la position de l'immatriculation dans chacun d'entre eux. Celui qui répondra par une position effective sera le bon tableau de recherche.

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

Pour que ces noms de tableaux soient considérés comme des plages et non comme des textes, nous devons les interpréter.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,

La recherche de position de l'immatriculation doit être réalisée avec la fonction Equiv. Elle fournira ainsi l'emplacement des informations à la fonction Index pour l'extraction.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner tous les tableaux de la feuille par le nom de plage : Marques,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,

C'est lui qui doit être trouvé par la fonction Equiv. Mais la recherche ne va pas aboutir à tous les coups puisque seul l'un des trois tableaux porte l'immatriculation cherchée. Nous devons donc l'utiliser à contrecourant avec un test booléen, comme nous l'avons déjà fait à l'occasion de précédentes astuces. Dès lors que le booléen répondra favorablement, la fonction Index connaîtra le bon tableau ainsi que la ligne du véhicule, retournée par la fonction Equiv.

• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• En guise d'élément cherché, taper le booléen Vrai,
• Puis, taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

Bien entendu, ils sont tous concernés une fois encore et réunis sous le nom de plage Marques. Pour savoir si la position est trouvée (Vrai), le test consiste à réaliser le dénombrement de l'immatriculation dans chacun des tableaux. Seul un des tests répondra favorablement, identifiant le bon tableau.

• Inscrire la fonction de dénombrement conditionnel, suivie d'une parenthèse, soit : Nb.Si(,

Elle doit exercer sur tous les tableaux, dont les noms à interpréter sont recensés dans la liste des marques.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner la liste des marques par son nom, soit : Marques,
• Fermer la parenthèse de la fonction Indirect,
• Puis, taper un point-virgule (;) pour passer dans l'argument du critère à compter,
• Cliquer sur la cellule D4 pour désigner l'immatriculation à trouver,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $D$4,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, taper la condition suivante à honorer : >0,

En effet, si le dénombrement répond par une valeur positive, cela signifie que l'immatriculation a été trouvée dans le tableau en cours d'analyse par la formule matricielle.

• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,
• Dès lors, fermer la parenthèse de la toute première fonction Indirect,

De fait, nous sommes de retour dans les arguments de la fonction RechercheV.

Colonne d'extraction dynamique
Il est maintenant temps de spécifier le numéro de la colonne d'extraction. Pour la marque, il s'agit de la deuxième colonne. Pour le modèle, il s'agit de la troisième. Et pour conserver un calcul parfaitement dynamique, nous devons faire varier ce numéro avec la réplication de la formule.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de colonne,
• Inscrire la fonction pour l'indice de colonne d'une cellule, suivie d'une parenthèse : Colonne(,
• Désigner une cellule de la deuxième colonne en cliquant par exemple sur B3,

Cette fois, nous ne devons surtout pas figer cette cellule. Lors de la réplication sur la droite, elle doit progresser avec le calcul pour se transformer en C3. Ainsi, elle désignera la troisième colonne du tableau de recherche pour extraire le modèle.

• Fermer la parenthèse de la fonction Colonne,
• Puis, taper un point-virgule suivi du booléen Faux, soit : ;Faux, pour une recherche exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Enfin, valider la formule matricielle avec le raccourci clavier CTRL + MAJ + Entrée,

Il est à noter qu'à partir de la version 2019, ce raccourci n'est plus nécessaire. Excel comprend naturellement qu'il s'agit d'une technique matricielle. Comme vous pouvez l'apprécier, la marque est parfaitement extraite. Et si vous tirez la poignée du résultat sur la cellule de droite, vous obtenez le modèle de l'immatriculation.



Et bien sûr, si vous changez d'immatriculation, les extractions s'ajustent parfaitement bien que le tableau de recherche ne soit plus le même. Un point important est à soulever au sujet de cette formule. Même si le nombre de tableaux augmente et ce, quelle qu'en soit la quantité, à partir du moment où ils sont tous identifiés par leurs noms dans la liste des marques, la syntaxe ne change pas, elle ne grossit pas. Il s'agit donc d'une solution efficace pour réaliser des recherches dans des bases de données multiples. La formule complète :

{=RECHERCHEV($D$4; INDIRECT(INDEX(Marques; EQUIV(VRAI; NB.SI(INDIRECT(Marques); $D$4)>0; 0))); COLONNE(B3); FAUX)}