Extraire toutes les données avec une seule RechercheV

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


Sujets que vous pourriez aussi aimer :


Extraire toutes les données avec une RechercheV

L'une des contraintes de la fonction RechercheV est son troisième paramètre. Il désigne la colonne d'extraction par un numéro statique. De fait, il est compliqué de répliquer la logique du calcul pour produire l'extraction des autres informations, situées dans les autres colonnes. Néanmoins, une astuce existe pour prolonger l'intérêt des techniques standards.



Cependant, une astuce triviale impliquée dans un raisonnement matriciel apporte la solution avec beaucoup de simplicité. Dans l'exemple finalisé illustré par la capture, au choix d'une référence, ce sont toutes les informations du salarié concerné qui sont importées. Et pour cela, une unique formule exploitant la fonction RechercheV est bâtie.

Source et présentation de la problématique
Pour réaliser ces démonstrations, nous devons commencer par réceptionner une base d'informations.

• Télécharger le classeur extractions-matricielles-par-recherchev.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é,

Des salariés sont recensés dans un tableau s'étendant de la colonne B à la colonne G et de la ligne 6 à la ligne 23. Un petit tableau de synthèse est proposé sur sa droite entre les colonnes I et J. Une liste déroulante permet de désigner un salarié par son matricule en cellule J5.



C'est au choix de cette référence que toutes les données associées au salarié désigné, doivent être importées juste en-dessous, entre les lignes 7 et 11. Et comme nous le disions, cette extraction doit être faite sur la base d'une formule unique.



En déployant la zone Nom en haut de la feuille Excel, vous constatez que chaque colonne du tableau est intitulée en fonction de son titre de champ. Mais surtout, le nom bdd est attribué à l'intégralité du tableau. Nous exploiterons ce nom pour simplifier la construction des formules d'extraction.

RechercheV et fonction Ligne
Au choix d'une référence avec la liste déroulante, nous devons rapatrier les informations sur le nom, le prénom, le service, la qualification et le salaire. Ces données sont situées entre les colonnes 2 et 6 de la source d'informations. Pour permettre cette extraction avec une seule formule à répliquer, il convient d'employer la fonction ligne en troisième argument de la fonction RechercheV. Pour le premier calcul, nous lui passerons une cellule de la ligne 2 comme A2 par exemple. Elle retournera donc l'indice 2 indiquant à la fonction RechercheV d'extraire la donnée sur le nom. En répliquant sa logique sur les lignes du dessous, elle retournera les indices incrémentés correspondant aux autres colonnes d'extraction.

• Choisir une référence avec la liste déroulante, par exemple : Sl003,
• Sélectionner le premier résultat à extraire, soit la cellule J7,
• Taper le symbole égal (=) pour débuter la formule,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : RechercheV(,
• Cliquer sur la référence de la liste déroulante en J5 pour indiquer la valeur cherchée,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $J$5,

Notre calcul est destiné à être répliqué sur les lignes du dessous. Mais pour ces autres informations à extraire, la recherche doit toujours se faire par rapport à cette référence. Sa cellule ne doit donc pas suivre le déplacement. C'est pourquoi nous la figeons.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la base de données par son nom, soit : bdd,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,

C'est précisément ce paramètre qui doit varier au fil de la réplication de la formule sur les lignes du dessous. Ainsi, nous produirons naturellement les autres extractions demandées.

• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Cliquer sur une cellule de la deuxième ligne, par exemple A2,

De cette manière, la première extraction se fera en colonne 2 de la base de données. Nous allons donc récupérer l'information sur le nom correspondant à la référence cherchée.

• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction RechercheV,
• Inscrire le paramètre booléen Faux pour une recherche exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Puis, valider la formule à l'aide du raccourci clavier CTRL + Entrée,

Ainsi, nous conservons la cellule du résultat active pour l'exploiter dans la foulée. Le premier résultat tombe. Le nom du salarié associé à la référence désignée est parfaitement extrait.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur les lignes du dessous,

Toutes les autres informations attachées à cette même référence sont naturellement extraites. Pour cela, nous avons bâti une seule formule avec la fonction RechercheV . L'astuce a consisté à exploiter la fonction Ligne pour fournir un indice de colonne incrémenté en troisième paramètre :

=RECHERCHEV($J$5; bdd; LIGNE(A2); FAUX)

Bien entendu, si vous choisissez une autre référence avec la liste déroulante, toutes les informations extraites s'actualisent parfaitement.

RechercheV matricielle
Pour obtenir le même résultat avec une seule formule, la technique matricielle est aussi simple de mise en oeuvre, voire plus. En guise d'indice de colonne incrémenté, elle consiste à fournir une matrice énumérant ces positions, entre accolades donc. Cette matrice doit bien sûr être passée en troisième argument de la fonction RechercheV.

• Sélectionner tous les précédents résultats, soit la plage de cellules J7:J11,
• Enfoncer la touche Suppr du clavier pour les effacer,
• Conserver la sélection active et taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : RechercheV(,
• Désigner la référence cherchée en cliquant sur sa cellule J5,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la base de données par son nom, soit : bdd,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Désigner toutes les colonnes des données à retourner sous forme de matrice: {2;3;4;5;6},

Nous désignons ainsi les champs d'extraction respectifs, du nom en deuxième colonne jusqu'au salaire situé en sixième colonne.

• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction RechercheV,
• Inscrire l'argument booléen Faux pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Enfin, valider nécessairement le calcul par le raccourci clavier CTRL + MAJ + Entrée,

C'est ainsi que nous le transformons en formule matricielle. Les colonnes sont simultanément analysées pour correspondre à la matrice des positions que nous avons fournie en troisième argument. Nous obtenons bien l'extraction de toutes les informations du salarié attaché à la référence désignée et ce, grâce à une seule formule, qui plus est triviale.



Comme vous l'avez constaté, du fait de ce raisonnement matriciel, nous n'avons pas eu besoin de figer la référence de recherche, comme nous l'avons fait dans la formule standard précédente. La logique est répliquée sur l'intégralité de la plage à validation. Ce sont les indices respectifs des matrices qui évoluent et non les cellules isolées. La formule matricielle que nous avons construite est la suivante :

{=RECHERCHEV(J5; bdd; {2;3;4;5;6}; FAUX)}

Repérer l'enregistrement extrait
Pour renforcer les résultats fournis par le calcul d'extraction, nous suggérons de repérer dynamiquement et visuellement, la ligne de l'enregistrement concerné. Pour cela, il suffit de bâtir une règle de mise en forme conditionnelle très simple.

• Sélectionner toutes les données du tableau, soit la plage de cellules B6:G23,
• 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 ...,
• Puis, cliquer dans la zone de saisie juste en-dessous pour l'activer,
• Construire alors la syntaxe suivante : =$B6=$J$5,

Une mise en forme conditionnelle raisonne chronologiquement. C'est la raison pour laquelle nous bâtissons le critère à partir de la première référence, soit la cellule B6. Nous la libérons en ligne pour qu'elles soient toutes passées en revue. Nous la figeons en colonne pour que le critère d'un même enregistrement ne soit vérifié que sur la référence. L'égalité à vérifier porte sur la référence choisie par le biais de la liste déroulante. Celle-ci ne doit pas bouger pour que toutes les comparaisons soient réalisées en fonction de cette donnée fixe. Donc, nous conservons sa cellule complètement figée.

Lorsque cette condition est honorée, nous devons faire surgir la ligne de l'enregistrement dans des attributs de format explicitement différents.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu pâle,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un bleu foncé pour la couleur du texte,
• Cliquer une première fois sur le bouton Ok pour valider ces réglages de format,
• Cliquer une seconde fois pour valider la création de la règle de mise en forme conditionnelle,

L'enregistrement correspondant à la référence choisie surgit instantanément. La couleur se déplace au gré des choix effectués par l'utilisateur avec la liste déroulante. Cet indicateur visuel permet de renforcer les résultats livrés par les extractions de la fonction RechercheV.