RechercheV dans de multiples colonnes 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 :


RechercheV multicolonnes

Avec l'astuce précédente, nous avons réalisé la prouesse de faire travailler la fonction RechercheV dans plusieurs tableaux afin d'extraire les informations attachées à une référence choisie par l'utilisateur. Et bien que ces tableaux étaient accolés, il s'agissait bien d'une recherche multitables. Nous aurions très bien pu les disperser sur la feuille, voire même sur d'autres feuilles. Mais pour les démonstrations visuelles, ce n'était pas forcément confortable.

Ici, nous proposons de démontrer comment la fonction RechercheV est capable d'exercer une recherche dans plusieurs colonnes. En effet et comme vous le savez, son plus gros défaut est que sa recherche doit forcément s'organiser dans la première colonne d'un tableau. Mais engagée dans un raisonnement matriciel, elle change complètement la donne.



Sur l'exemple illustré par la capture, nous travaillons sur un tableau de produits destinés à la vente. Les références de ces articles sont inscrites toutes les trois colonnes. Pour des bases de données conséquentes, cette technique permet de réduire quelque peu la hauteur du tableau. En revanche, pour réaliser l'extraction des informations associées à une référence, le problème se corse car la logique n'est plus linéaire.

Pourtant, dans cette solution finalisée, l'utilisateur désigne l'une de ces références à l'aide d'une liste déroulante placée au-dessus du tableau. Et aussitôt, la désignation et le prix associés sont extraits dans les deux cellules placées sur sa droite. Et pour cela, c'est la fonction RechercheV dans un raisonnement matriciel qui est capable de scruter chaque colonne des références pour en déduire la zone d'extraction.

Classeur Excel à télécharger
Pour la démonstration de cette nouvelle astuce Excel, nous proposons d'opérer à partir d'un classeur existant et offrant cette organisation particulière des données.

• Télécharger le classeur recherchev-multicolonnes.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 ce tableau étiré en largeur. Les références des produits sont proposées en colonnes B, E et H. En B4, l'utilisateur choisit l'une de ces références avec une liste déroulante. Et c'est là que la fonction RechercheV doit intervenir pour rapatrier la désignation et le prix attachés en cellules C4 et D4 bien que la colonne de recherche ne soit pas connue à l'avance.

Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous constatez que la première colonne des références est reconnue sous le nom Depart. C'est elle que nous utiliserons en référence pour décaler l'analyse sur les autres dans un raisonnement matriciel. Ces colonnes des codes articles interviennent un coup sur trois. Elles sont espacées entre elles de deux colonnes, celles des désignations et prix.

Vous avez noté la présence du nom Marques. Il ne sert plus à rien. Il s'agit d'un stigmate de l'astuce précédente. Ce nom persistant n'a tout simplement pas été supprimé.

Lancer la recherche
Il est temps d'initier la recherche multicolonnes pour répondre au choix de la référence réalisé par le biais de la liste déroulante en cellule B4.

• Cliquer sur la cellule C4 de la désignation à extraire,
• 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'information cherchée en cliquant sur la référence en B4,
• Enfoncer la touche F4 du clavier pour figer cette cellule, ce qui donne : $B$4,

Cette formule matricielle est destinée à être répliquée sur la droite pour le prix. La recherche sur la référence ne doit pas bouger.

• Dès lors, taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

Décaler l'analyse sur les colonnes
C'est ici précisément que la portion délicate s'invite. Comme vous le savez, la fonction RechercheV est capable d'extraire des informations d'un tableau à partir du moment où l'élément cherché est situé en première colonne de ce tableau. Or ici, les références peuvent se trouver en première, comme en quatrième ou septième colonne. Il existe cependant une séquence remarquable que nous allons exploiter. Deux colonnes intermédiaires sont intercalées systématiquement entre ces références. Dans ce raisonnement matriciel, l'idée est de débuter l'analyse dans les trois premières colonnes, puis de la décaler de trois colonnes plus à droite, jusqu'à atteindre la fin du tableau. De cette manière, nous agirons à chaque fois sur des tableaux de trois colonnes où les références seront toujours placées en tête. Et pour cela, nous avons besoin de la fonction d'ajustement Decaler.

• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner la première colonne des références par son nom, soit : Depart,
• Puis, taper deux points-virgules (;;) pour atteindre l'argument du décalage en colonne,

En effet, la hauteur de l'analyse est déjà ajustée grâce à la plage nommée Depart. Donc, aucun décalage en ligne n'est à observer.

Tester la présence de la référence dans toutes les colonnes
Pour connaître le décalage à observer, nous allons réaliser un test récursif avec la fonction Equiv. Son rôle est de vérifier la présence de la référence cherchée dans chaque colonne de ces références. Il est donc question là aussi de déplacer cycliquement l'analyse de la fonction Equiv grâce à la fonction Décaler.

• Ouvrir tout d'abord une parenthèse,

Nous le verrons, nous serons effectivement contraints de multiplier le résultat pour réajuster le décalage.

• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Taper alors le booléen Vrai,

C'est une exploitation particulière de la fonction Equiv que nous entreprenons. L'idée est de lui faire chercher la référence choisie dans les colonnes à Décaler. Dès que le test que nous n'avons pas encore construit sera concluant, il répondra par Vrai. C'est ainsi qu'il fournira à la fonction Equiv le point de départ du bon tableau pour organiser l'extraction avec la fonction RechercheV.

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

C'est lui qui n'est pas défini à l'avance et que nous devons décaler de façon récursive grâce à ce raisonnement matriciel, pour déceler la position de la bonne colonne de départ.

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

Son rôle est de compter la présence de la référence dans chacune des trois colonnes. Nous devons donc ajuster son analyse grâce à la fonction Decaler de nouveau.

• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner la plage de départ par son nom, soit : Depart,
• Puis, taper deux points-virgules (;;) pour atteindre l'argument du décalage en colonne,
• Dès lors, inscrire la matrice horizontale suivante : {0.3.6},

Nous allons donc effectuer trois passages. Le premier doit se faire dans la première colonne avec un décalage de zéro unité. Les suivants doivent à chaque fois s'opérer trois colonnes plus loin, soit en quatrième et septième colonnes, celles des autres références.

• Fermer la parenthèse de la fonction Decaler,

Ainsi, nous sommes de retour dans les arguments de la fonction Nb.Si. Nous devons déterminer le critère à observer.

• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Cliquer sur la cellule B4 de la référence choisie,
• Puis, la figer comme précédemment avec la touche F4 du clavier, ce qui donne : $B$4,
• Dès lors, fermer la parenthèse de la fonction Nb.Si,
• Puis, ajouter la condition suivante : >0,

En effet, si le dénombrement répond par une valeur positive, cela signifie que la référence a été trouvée dans la colonne en cours d'analyse par la fonction Equiv. Le booléen Vrai est retourné.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer alors la parenthèse de la fonction Equiv,
• Puis, retrancher une unité à la position trouvée, soit : -1,

En effet, dans cette matrice horizontale de décalage ({0.3.6}), la fonction Equiv répond par un numéro relatif (1, 2 ou 3) pour indiquer qu'il s'agit de la première, deuxième ou troisième colonne analysée. En réponse, nous obtenons donc un décalage à observer, soit de zéro unité, soit d'une seule ou de deux (0,1 ou 2). Le premier décalage est correct (0) pour chercher la référence dans la première colonne. Les autres doivent être réajustés.

• Pour cela, fermer tout d'abord la parenthèse de la factorisation,
• Puis, multiplier la valeur par trois, soit : *3,

De cette manière, nous analyserons soit la première colonne (décalage à zéro), soit la quatrième (décalage de trois en partant de la première), soit la septième ((décalage de six en partant de la première)).

En ayant fermé les parenthèses de la fonction Equiv et celles de la factorisation avant la fonction Equiv, nous nous retrouvons dans les bornes de la première fonction Decaler. Nous devons définir la largeur du tableau pour l'analyse par la fonction RechercheV. Il est forcément constitué de trois colonnes.

• Taper deux points-virgules (;;) pour atteindre l'argument de la largeur,

Ainsi, nous sautons l'argument de la hauteur puisque celle-ci est déjà définie.

• Taper le chiffre 3 pour une largeur de 3 colonnes à partir de la rangée des références trouvées,
• Fermer la parenthèse de la fonction Decaler,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,

Définir la colonne d'extraction dynamiquement
Cette colonne de retour indique à la fonction rechercheV quelle est l'information à restituer selon la référence trouvée sur la même ligne. Cette information sur le numéro de colonne doit s'adapter avec la réplication de la formule pour extraire naturellement le prix après la désignation. Et pour cela, nous pouvons exploiter la fonction Colonne.

• Inscrire la fonction pour l'indice de colonne suivie d'une parenthèse, soit : Colonne(,
• Désigner une cellule de la deuxième colonne en cliquant par exemple sur B5,

Dans un premier jet, cette fonction va retourner le chiffre 2 pour opérer l'extraction des désignations sur la deuxième colonne du tableau réajusté, puis le chiffre 3 pour les prix sur la troisième colonne.

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

Comme vous pouvez le voir, la première désignation est parfaitement rapatriée, quel que soit l'emplacement de la référence demandée.

• Cliquer et glisser la poignée du résultat une cellule sur la droite pour le prix,

Lui aussi est parfaitement extrait grâce à la technique de la colonne dynamique d'extraction.



Il convient d'exploiter la petite balise active qui se déclenche en bas à droite de la cellule pour ne pas répliquer la mise en forme et ainsi conserver le format monétaire du prix.

Maintenant, à chaque fois que vous changez de référence avec la liste déroulante, les informations attachées sont extraites bien que et rappelons-le, l'emplacement en colonne de la référence cherchée ne soit pas connu à l'avance. Nous avons donc réussi la prouesse de bâtir une formule permettant à la fonction RechercheV de chercher l'information dans n'importe quelle colonne et selon la syntaxe suivante :

{=RECHERCHEV($B$4; DECALER(Depart; ; (EQUIV(VRAI; NB.SI(DECALER(Depart; ; {0.3.6}); $B$4)>0;0)-1)*3; ; 3); COLONNE(B5); FAUX)}

Enfin et souvenez-vous, à partir de la version 2019, Excel comprend naturellement que la nature du calcul engagé est matricielle. Dans ce contexte, le raccourci CTRL + MAJ + Entrée n'est plus nécessaire.