RechercheV sur plusieurs 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 sur colonnes combinées

Nous connaissons bien la fonction Excel RechercheV. En même temps qu'elle offre beaucoup de puissance pour produire des extractions, elle présente de nombreuses limitations.



Dans l'exemple finalisé illustré par la capture, nous travaillons sur les nombres de ventes réalisées par région et par produit. Sur la droite, une liste déroulante permet de désigner la combinaison d'une région et d'un produit, soit une association de données issues de deux colonnes. Sur cette combinaison, le nombre de ventes réalisées est alors extrait. La fonction RechercheV ne sait pas trouver des informations combinées. Et pourtant, c'est bien elle que nous allons utiliser.

Classeur source
Pour la démonstration de cette nouvelle astuce Excel, nous proposons de récupérer l'ébauche de ce classeur.

• Télécharger le classeur recherchev-multiple.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 le tableau de synthèse des ventes. La liste déroulante est présente en cellule F4. Elle se nourrit de l'assemblage de chaque région avec chaque produit. Cette liste source est placée dans la colonne K masquée. Le résultat de la recherche est attendu sur la droite de cette liste déroulante, en cellule G4.

Limitation de la rechercheV
Nous le savons, la fonction RechercheV requiert quatre arguments :
=RechercheV(Valeur_cherchée; Tableau_de_recherche; Colonne_de_retour; Booléen)

Le premier paramètre est donc la valeur cherchée. Ici, il s'agit de l'assemblage Région-Produit désigné en cellule F4 avec la liste déroulante. Le tableau de recherche en deuxième argument devrait donc être un assemblage de ces deux colonnes : B4:B19 & C14:C19. Mais dès lors, un problème se pose avec le troisième paramètre. Il doit désigner le numéro de la colonne, dans le tableau de recherche, pour l'extraction. Or cette colonne des ventes n'est jamais mentionnée. Donc, elle ne peut être atteinte.

Choisir la colonne d'extraction
C'est un raisonnement matriciel qui apporte la solution. La recherche doit bien être réalisée sur la combinaison des deux colonnes. Mais pour que la rechercheV puisse pointer sur la colonne d'extraction, nous allons revisiter le tableau grâce à la fonction Excel Choisir.

=Choisir(Numéro; Elément1; Elément2; ...; ElémentN)

Dans son utilisation classique, en fonction d'un numéro passé en premier paramètre, elle restitue l'un des éléments listés dans les arguments suivants. Et cet élément est celui qui correspond à la position désignée par ce numéro.

Mais dans cette analyse matricielle, ce sont deux numéros qui doivent être utilisés sous forme de matrice virtuelle horizontale {1.2}. L'objectif n'est pas de désigner l'une ou l'autre colonne, mais les trois réunies sous forme compactée. Nous allons bientôt le comprendre. Ainsi, nous pourrons désigner le premier élément comme tableau de recherche et le second comme colonne d'extraction. Comme vous le savez, dans un raisonnement matriciel, ce sont tous les éléments d'une matrice qui sont confrontés tour à tour avec tous les éléments des autres matrices.

• Sélectionner la cellule de la valeur à extraire en cliquant sur G4,
• 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 la donnée cherchée par ses coordonnées, soit : F4,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

C'est là que l'astuce réside avec l'emploi de la fonction Choisir pour pouvoir désigner à la fois le tableau de recherche et la colonne d'extraction.

• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit : Choisir(,
• En guise de numéro de choix, inscrire la matrice horizontale suivante : {1.2},

Dans la syntaxe, une matrice est nécessairement bornée par des accolades. Dans le cas d'une matrice horizontale, les valeurs sont nécessairement séparées par un point et non par un point-virgule comme pour les matrices verticales. Nous construisons donc une matrice virtuelle de deux colonnes et d'une ligne.

• Taper un point-virgule pour passer dans l'argument de la première valeur à choisir (1),

Vous l'avez compris, en guise de valeur, nous allons lui passer tout d'abord l'association des deux premières colonnes du tableau. Cet assemblage servira de tableau de recherche dans cette analyse matricielle.

• Sélectionner la colonne des régions, soit la plage de cellules B4:B19,
• Inscrire le caractère de concaténation (&) pour l'assemblage à construire,
• Taper un espace entre guillemets pour la séparation, soit : " ",
• Inscrire un nouveau caractère de concaténation (&), touche 1 en haut à gauche du clavier,
• Puis, sélectionner la colonne des produits, soit la plage de cellules C4:C19,
• Taper un point-virgule (;) pour passer dans l'argument du choix suivant,

Nous le répétons, ces choix se font sur des matrices réunies. C'est maintenant la colonne d'extraction qui est concernée.

• Sélectionner la colonne des ventes, soit la plage de cellules D4:D19,
• Fermer la parenthèse de la fonction Choisir,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la RechercheV,
• Inscrire le chiffre 2 pour pointer effectivement sur les valeurs de la colonne des ventes,
• 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 nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Le résultat tombe et il est parfaitement cohérent. Il s'agit bien du nombre de ventes réalisées pour la région et le produit sélectionnés par défaut par le biais de la liste déroulante. Et bien sûr, si vous changez de recherche, toujours avec la liste, le résultat s'actualise parfaitement et automatiquement. La syntaxe complète de la formule matricielle que nous avons bâtie est la suivante :

{=RECHERCHEV(F4; CHOISIR({1.2}; B4:B19 &" "& C4:C19; D4:D19); 2; FAUX)}



La donnée (F4) est donc cherchée dans toutes les lignes des deux matrices réunies par la fonction Choisir. Comme vous le savez, la fonction RechercheV scrute uniquement la première colonne à la recherche de correspondances. Et cette première colonne est devenue la contraction des deux premières colonnes du tableau d'origine. Sur la concordance trouvée, l'extraction est produite sur la seconde colonne de cette matrice ainsi reconstruite, soit celle des nombres de ventes. Il conviendrait d'ajouter une gestion d'erreur (SiErreur) pour éviter toute anomalie lorsqu'aucune donnée n'est mentionnée par le biais de la liste déroulante :

{=SIERREUR(RECHERCHEV(F4; CHOISIR({1.2}; B4:B19 & "" & C4:C19; D4:D19); 2; FAUX); "")}

Repérer la ligne cherchée
Pour parfaire la solution et simplifier la comparaison des résultats, nous proposons de bâtir deux règles de mise en forme conditionnelle. L'ordre est important, question de hiérarchie. La première est destinée à faire ressortir toutes les lignes du produit désigné, donc quel que soit la région. La seconde, dans une autre couleur, doit faire ressortir la ligne de la région et du produit désignés. Dès lors, un seul coup d'oeil suffira pour comparer les ventes du produit en fonction des régions.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:D19,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie du dessous, construire la syntaxe suivante : =CHERCHE($C4;$F$4),

La fonction Excel Cherche porte bien son nom. Elle permet de trouver la position d'un élément cherché et mentionné en premier argument. Ici, il s'agit du nom du produit. L'analyse d'une mise en forme conditionnelle est chronologique. C'est la raison pour laquelle nous débutons la comparaison à partir du premier d'entre eux. Sa colonne reste figée tandis que sa ligne est libérée ($C4). Le produit à chercher est forcément situé dans la colonne C. Mais au gré de l'analyse, tous les produits doivent être comparés. Donc l'indice de ligne est libéré. Cette recherche s'effectue dans la donnée sélectionnée avec la liste déroulante et resituée en cellule F4. Celle-ci ne doit pas suivre le déplacement de l'analyse, puisqu'il s'agit de la donnée de référence pour la comparaison. C'est la raison pour laquelle nous la conservons complètement figée ($F$4). Lorsqu'un élément cherché est trouvé dans un autre, même s'il s'agit d'un fragment, la fonction Cherche répond par une position, donc par un entier positif. C'est implicitement que cette règle réagit sans qu'il soit nécessaire de formuler un critère. Et dans ce cas, la ligne concernée doit surgir automatiquement en couleur.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un orange assez vif pour la couleur du texte,
• Valider cet attribut de format par le bouton Ok,
• Puis, valider la création de la règle de mise en forme, toujours par le bouton Ok,

Les lignes du produit désigné sont toutes explicitement mises en valeur. Nous devons désormais distinguer catégoriquement la ligne recoupant le produit et la région.

• Sélectionner de nouveau toutes les données du tableau, soit la plage de cellules B4:D19,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie du dessous, construire la syntaxe suivante : =$B4 & "" & $C4=$F$4,

Nous cherchons la corrélation parfaite cette fois. Donc, dans cette analyse chronologique, nous testons l'égalité entre l'assemblage de la région et du produit avec la donnée choisie en cellule F4. Cette dernière reste totalement figée pour les mêmes raisons que précédemment. Et aussi, pour les mêmes raisons que précédemment, les régions et produits étant prélevés dans leurs colonnes respectives, nous les figeons en colonnes tandis que nous libérons les lignes.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, choisir un vert assez vif pour le texte avec la seconde liste,
• Valider cet attribut de format par le bouton Ok,
• Puis, valider la création de la règle de mise en forme, toujours par le bouton Ok,

Comme cette règle est construite après l'autre, elle est placée en tête dans la hiérarchie. De fait, sa couleur dynamique prend le pas sur la précédente. Tandis que les correspondances approximatives sur le produit sont toujours repérées en orange, la correspondance exacte sur la région et le produit est surlignée en vert. La comparaison instantanée des résultats devient un jeu d'enfants.