Rechercher dans plusieurs sources de données

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


Sujets que vous pourriez aussi aimer :


Rechercher dans des sources multiples

Nous savons extraire de l'information de tableaux de données avec Excel. Nous avons déjà appris à exploiter la fonction RechercheV comme les fonctions Index et Equiv. Dans cette formation, la problématique se corse. A la saisie d'une référence, nous devons certes importer toutes les données attachées. Mais cette référence peut se trouver dans l'un des trois tableaux de données, gérés indépendamment. La question qui se pose est de savoir comment détecter le bon tableau de recherche.

Classeur source
Il est question de concentrer nos travaux sur la mise en oeuvre de la solution et non sur la saisie des informations. C'est pourquoi nous proposons de récupérer un classeur source existant, hébergeant les trois bases de données.

• Télécharger le classeur recherches-multiples-sources.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Comme vous le constatez, ce classeur est constitué de quatre feuilles, comme le mentionnent les quatre onglets en bas de la fenêtre Excel.



La première est donc la feuille de recherche. Une immatriculation doit être saisie en cellule B5 pour rapatrier les informations attachées sur la marque et le modèle en cellules respectives B8 et D8. Nous sommes au siège et cette société gère des parcs automobiles sur différentes plateformes. Les véhicules de ces plateformes sont importés dans les feuilles respectives Parc1, Parc2 et Parc3.

Comme vous le savez, il est possible de réaliser des actualisations automatiques de ces informations par liaisons aux sources externes. C'est ce que nous avions appris au travers de la formation Excel pour établir la connexion aux bases de données.

Nous partons donc du principe que nous travaillons sur des données mouvantes, issues des trois plateformes et constamment actualisées.

• En bas de la fenêtre Excel, cliquer sur l'onglet Parc1 pour activer sa feuille,

Nous y trouvons un tableau archivant les automobiles issues de la première plateforme. Elles sont référencées sur trois champs : Immat, Marque et Modèle. Les tableaux des feuilles Parc2 et Parc3 archivent d'autres véhicules selon la même structure, soit avec un niveau de détail sur trois colonnes.

• Sur la feuille Parc1, sélectionner la première immatriculation, soit la cellule A2,
• Puis, réaliser le raccourci clavier CTRL + MAJ + Fin ,

Cette action a pour effet de sélectionner l'intégralité des données du tableau, jusqu'à la dernière cellule. La touche Maj est aussi connue sous la désignation anglaise Shift.



En consultant la zone Nom en haut à gauche de la feuille Excel, vous notez qu'un nom a été attribué à ce tableau. Il s'agit de Parc1 soit une désignation similaire à la feuille. Il n'est d'ailleurs pas nécessaire que les deux coïncident. De la même façon, nous avons nommé les deux autres tableaux Parc2 et Parc3. Ces noms sont importants pour simplifier les recherches dynamiques.

• Revenir sur la feuille Recherche,
• En cellule B5, saisir l'immatriculation suivante : 112CSB59,
• Puis, valider par la touche Entrée du clavier,

Comme vous le constatez, les informations attachées sont automatiquement extraites. Nous connaissons instantanément la marque et le modèle du véhicule identifié par son immatriculation. Cette immatriculation est la première du tableau de la feuille Parc1. Nous pourrions penser que le travail est fait et que la solution existe déjà.

• En cellule B5, saisir désormais l'immatriculation suivante : AR998EW,

Cette fois, les cellules se vident. Plus aucune information attachée n'est fournie. Pourtant, cette immatriculation existe bien. Mais il s'agit de la première du tableau de la feuille Parc2. Elle appartient donc à une autre source de données.

• Sur la feuille Recherche, double cliquer sur la cellule B8,

Cette méthode permet d'afficher le contenu de la cellule. En l'occurrence ici, il s'agit de sa formule.



Et comme vous le constatez, cette formule exploite la fonction RechercheV. Elle cherche l'immatriculation $B$5. Mais cette recherche est réalisée seulement dans le tableau de la feuille Parc1. Le nom de ce tableau est en effet passé en deuxième argument de la fonction. Et c'est lui que nous devons rendre dynamique.

• Enfoncer la touche Echap du clavier pour abandonner la modification de la cellule,

Identifier la source de données
Nous ne souhaitons pas livrer une formule finale trop complexe. Nous proposons donc d'éviter l'imbrication des critères pour désigner la bonne source de données. La solution ne consiste donc pas à imbriquer des fonctions Si.

Sur la feuille Recherche, en ligne 5 et entre les colonnes I et K, vous notez la présence d'un petit tableau. Il rappelle les noms des sources de données. L'astuce consiste donc à réaliser une identification intermédiaire en ligne 4, soit sur la ligne du dessus. Selon l'immatriculation tapée en B5, la mention Ok doit s'inscrire dans la colonne de la source de données concernée.

Il s'agit donc de réaliser une recherche de l'immatriculation, soit de la cellule B5 dans chaque source, en commençant par celle identifiée par la cellule I5. Et pour que le calcul se réplique naturellement, nous devons exploiter les références absolues.

• En cellule I4, taper la formule suivante :

=SI(ESTERREUR(RECHERCHEV($B$5; INDIRECT(I5); 2; FAUX)); ''; 'Ok')

Le calcul est conditionnel. Il consiste à savoir si la recherche dans la source désignée conduit à une erreur ou non. C'est la raison pour laquelle nous initions la formule par la fonction Si. Et précisément en premier argument, le résultat du test est retourné par la fonction Excel EstErreur. C'est la RechercheV qui lui est passée en paramètre. Si la recherche échoue, alors nous conservons la cellule vide grâce aux deux guillemets (''). Dans le cas contraire, nous inscrivons la mention Ok.

Cette recherche est donc réalisée sur l'immatriculation saisie en B5. Nous avons figé cette cellule grâce à la touche F4 du clavier, d'où la présence des dollars encadrant ses références. En effet, il va ensuite s'agir de répliquer ce calcul sur les deux colonnes qui suivent. Et malgré ce déplacement, la recherche devra toujours être réalisée sur cette même cellule. En deuxième argument, nous désignons le tableau de recherche par la cellule I5. Cette dernière porte bien le nom de l'une des sources de données. Mais Excel considère l'information prélevée comme un texte. Pour qu'il l'interprète comme une plage de cellules, nous l'englobons dans la fonction Indirect. En retour, nous demandons l'information située en deuxième colonne, d'où l'inscription du chiffre 2 en troisième argument. Mais nous n'afficherons pas le résultat si la recherche réussit. Nous souhaitons seulement savoir si un résultat est retourné pour identifier la source de données. En dernier argument, nous terminons par le booléen Faux pour demander une recherche stricte et non approximative.

• Valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette combinaison de touches permet de conserver la cellule active. Nous allons donc pouvoir répliquer la logique de ce calcul sans devoir préalablement resélectionner la cellule.

• Cliquer et glisser la poignée du calcul sur la droite jusqu'en cellule K4,

Comme vous le remarquez, la mention s'inscrit au-dessus de la deuxième source de données, soit en cellule J4. C'est la raison pour laquelle la recherche de cette immatriculation était infructueuse. Les deux autres cellules restent vierges grâce aux deux guillemets mentionnés dans la branche Alors de la fonction Si.

Source de données interchangeable
Grâce à ce résultat intermédiaire dynamique, nous pouvons entreprendre l'extraction des données attachées à l'immatriculation inscrite en B5. Nous devons toujours exploiter la fonction RechercheV sur cette cellule B5 de l'immatriculation. Mais en deuxième argument, nous devons lui fournir un tableau de recherche qui varie, identifié par la mention Ok. En deuxième argument, nous devons donc réaliser une recherche de cette mention pour récupérer le nom de la bonne source de données. Et comme ce tableau est transposé, la fonction à utiliser est la rechercheH et non plus la rechercheV. Souvenez-vous, H signifie horizontale et V verticale.

• Sélectionner la cellule B8,
• Taper le symbole égal (=) pour initier le calcul et remplacer la précédente formule,
• Taper la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction de recherche verticale suivie d'une parenthèse, soit RechercheV(,
• Désigner l'immatriculation à rechercher en cliquant sur la cellule B5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $B$5,

En effet, la recherche sur la même immatriculation doit être réalisée pour trouver le modèle. Ainsi, nous pourrons répliquer le calcul pour ne pas le refaire.

• Taper un point-virgule (;) pour passer dans l'argument de la source de données,
• Saisir la fonction pour interpréter le contenu d'une cellule suivie d'une parenthèse, soit Indirect(,
• Taper la fonction de recherche horizontale suivie d'une parenthèse, soit RechercheH(,
• Saisir la mention cherchée entre doubles côtes, soit : 'ok',
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner le petit tableau intermédiaire, soit la plage de cellules I4:K5,
• Enfoncer la touche F4 du clavier pour figer cette dernière comme précédemment, ce qui donne : $I$4:$K$5,
• Taper un point-virgule suivi du chiffre 2, soit : ;2,

Ainsi, nous indiquons que lorsque la mention Ok est trouvée, la fonction doit retourner le nom de la source de données située en deuxième ligne de ce tableau. C'est alors la fonction Indirect qui l'interprètera comme une plage de cellules.

• Taper un point-virgule suivi du booléen Faux (;Faux), pour une recherche exacte,
• Fermer la parenthèse de la fonction RechercheH,
• Puis, fermer la parenthèse de la fonction Indirect,

De fait, nous sommes de retour dans les arguments de la fonction RechercheV d'origine. Après le critère sur l'immatriculation, nous venons de renseigner la source de données dynamique, dans laquelle la recherche doit être effectuée.

• Finir avec les mêmes arguments que la fonction RechercheH, soit : ;2;Faux,

En effet, la marque à retourner est située en deuxième colonne de chaque source de données. Et comme toujours, nous souhaitons réaliser une recherche stricte et non approximative.

• Fermer la parenthèse de la fonction RechercheV,
• Puis taper un point-virgule suivi de deux guillemets, soit : ;'',

De cette manière, nous indiquons à la fonction de gestion d'erreur de conserver la cellule vide en cas de recherche infructueuse.

• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,
• Copier cette formule (CTRL + C),
• Sélectionner la cellule D8 du modèle et la coller (CTRL + V),

Dans les deux cas, la marque Fiat est retournée. C'est déjà un très bon signe puisque précédemment, à cause de la source de recherche erronée, aucun résultat n'était fourni. Le troisième paramètre de la fonction RechercheV est statique. Il s'agit du numéro de colonne pour l'information à retourner. Nous devons l'adapter. Le modèle est placé en troisième colonne.

• Dans la barre de formule de la cellule D8, remplacer le chiffre 2 du troisième paramètre de la fonction RechercheV, par le chiffre 3,
• Puis, valider cette adaptation par le raccourci CTRL + Entrée,

Cette fois, c'est bien le nom du modèle correspondant à l'immatriculation qui est restitué. La formule que nous avons bâtie est donc la suivante :

=SIERREUR(RECHERCHEV($B$5; INDIRECT(RECHERCHEH('ok'; $I$4:$K$5; 2; FAUX)); 2; FAUX); '')

Il nous reste à la tester. Souvenez-vous de l'objectif, cette syntaxe au demeurant fort simple, doit permettre de réaliser la recherche et l'extraction sans se soucier de l'origine de la base de données.

• Copier une immatriculation de la feuille Parc1 dans la cellule B5 de la feuille Recherche,

Comme vous le constatez, le véhicule est aussitôt identifié par sa marque et son modèle.

• Copier une immatriculation de la feuille Parc3 dans la cellule B5 de la feuille Recherche,

Le constat est le même. Nous avons donc apporté la solution permettant la recherche et l'extraction d'informations sur des sources de données multiples.

Il nous reste néanmoins à apporter une précision intéressante en cellule D5. Il s'agit d'indiquer à l'utilisateur à partir de quelle source de données l'extraction a été produite. Il suffit de réaliser une simple recherche horizontale de la mention ok dans le petit tableau intermédiaire. Nous réceptionnerons ainsi le nom de la source de données placée en deuxième ligne.

• En cellule D5, taper la formule suivante :

=SIERREUR(RECHERCHEH('ok'; $I$4:$K$5; 2; FAUX); '')