Recherches Excel sans connaître la bonne base 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 :


Extraire dans l'une des sources de données

L'objectif de cette nouvelle astuce Excel est de démontrer comment extraire les informations attachées à une référence choisie, lorsque la source de données d'hébergement n'est pas connue à l'avance.



Dans l'exemple illustré par la capture, un tableau d'une seule ligne se propose. Dans la première cellule, l'utilisateur choisit une référence à l'aide d'une liste déroulante. Et aussitôt, toutes les informations attachées sont extraites dans les cellules adjacentes. Et pourtant, comme le démontre la liste des onglets en bas de la fenêtre Excel, ces informations sont réparties dans plusieurs bases de données, hébergées par des feuilles distinctes (bdd1, bdd2 et bdd3).

Classeur source
Pour la mise en place de cette nouvelle astuce Excel, nous proposons de commettre la technique à partir d'un classeur offrant déjà ces multiples sources de données et cette ligne d'extraction avec sa liste déroulante.

• Télécharger le classeur recherche-tableau-inconnu.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 débouchons sur une feuille présentant effectivement une ligne d'extraction. Une référence est déjà inscrite en cellule B4. Une liste déroulante lui est associée. Elle permet de choisir parmi l'un de ces codes référencés dans l'une des trois sources de données stockées dans les trois feuilles suivantes. Si vous cliquez par exemple sur l'onglet bdd2 en bas de la fenêtre Excel, vous remarquez que des salariés sont effectivement archivés avec diverses informations. Et c'est l'identifiant, soit la référence en première colonne, qui permet de les désigner de façon unique.



Les tableaux de ces trois feuilles annexes sont structurés de la même façon. Mais ils ne portent pas les mêmes informations. En conséquence, le salarié attaché à la référence choisie depuis la feuille d'extraction peut se trouver dans n'importe laquelle de ces trois sources. Nous ne le savons pas à l'avance.

Il est à noter que chacun de ces tableaux est reconnu par un nom, respectivement tabl1, tabl2 et tabl3. Ces noms simplifieront la construction de la syntaxe de la formule d'extraction multi-source.

On ne connaît pas la source d'extraction
Vous l'avez compris, l'enjeu paraît complexe. Il est question de pointer sur la bonne base de données hébergeant la référence choisie pour extraire les informations attachées. Mais ne dit-on pas qu'il faut apprendre de ses erreurs ? C'est exactement ce que propose de démontrer cette nouvelle astuce Excel. Nous devons engager la recherche sur la première source de données. Une fonction de recherche, lorsqu'elle ne trouve pas ce qui lui est demandé, répond par une erreur. Cette erreur peut être interceptée et interprétée par la fonction Excel de gestion des anomalies. Il s'agit de la fonction SiErreur. Si cette erreur survient, la même recherche doit être envisagée sur la deuxième source de données et puis sur la troisième le cas échéant. Bref, il est question de réaliser des recherches en cascades jusqu'à trouver la référence permettant d'extraire les informations souhaitées. Et comme cette référence est inscrite en première colonne de chaque tableau, nous pouvons exploiter la fonction Excel RechercheV. Sinon, nous aurions aussi pu exploiter les fonctions Index et Equiv.

• Sélectionner la case de la première information à extraire en cliquant sur la cellule C4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule multi-recherches,
• Inscrire la fonction de gestion des anomalies, suivie d'une parenthèse, soit : SiErreur(,

Nous allons donc tenter une première recherche de la référence choisie dans la première source de données. Si elle est infructueuse et donc qu'elle répond par une erreur, en second paramètre de la fonction SiErreur, nous allons tenter une deuxième recherche, mais elle aussi encapsulée dans une seconde fonction SiErreur pour pouvoir explorer la troisième et dernière source de données le cas échéant.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : RechercheV(,
• Désigner la référence cherchée en cliquant sur sa cellule B4,
• Puis, enfoncer la touche F4 du clavier pour la figer totalement, ce qui donne : $B$4,

En effet, nous devons extraire toutes les informations de la ligne avec une seule formule. Et nous allons répliquer cette formule sur la droite pour extraire le nom, le prénom et le solde après la civilité. Malgré ce déplacement, la recherche doit toujours être effectuée par rapport à cette cellule où réside la référence.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la première de ces sources de données par son nom, soit : tabl1,

A défaut de connaître le bon tableau de recherche, nous commençons par le premier, celui de la deuxième feuille de ce classeur.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne d'extraction,

En vue de la formule à répliquer, cet indice est amené à progresser. Il s'agit de la deuxième colonne pour la civilité, de la troisième pour le nom, de la quatrième pour le prénom etc. Pour une syntaxe unique, nous devons faire progresser cet indice en même temps que la formule sera répliquée sur les colonnes de droite. Pour cela, nous pouvons exploiter la fonction Colonne, premièrement sur une cellule de la colonne B. Elle renverra tout d'abord l'indice 2, puis le 3, le 4 etc.

• Inscrire la fonction pour l'indice de colonne, suivie d'une parenthèse, soit : Colonne(,
• Désigner une case de la deuxième colonne en cliquant par exemple sur la cellule B2,
• Fermer la parenthèse de la fonction Colonne,
• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction RechercheV,
• Inscrire le booléen Faux pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Puis, taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,

Si cette seconde branche se déclenche, cela signifie que cette première recherche a été infructueuse. En d'autres termes, nous savons que la recherche n'a pas été entreprise sur le bon tableau. Comme il existe encore deux sources de données, nous devons commencer par essayer de réaliser la même recherche mais sur le tableau de la troisième feuille, reconnu par le nom tabl2.

• Inscrire de nouveau la fonction de gestion des anomalies, suivie d'une parenthèse : SiErreur(,
• Puis, adapter la précédente syntaxe de la formule de recherche comme suit :

RechercheV($B$4; tabl2; Colonne(B2); Faux)

Si cette nouvelle recherche dans la deuxième source de données de ce classeur s'avère elle aussi infructueuse, c'est cette fois la seconde branche de la seconde fonction SiErreur qui se déclenche. Nous devons donc l'exploiter pour tenter la recherche dans la troisième et dernière source de données.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Puis, adapter la précédente syntaxe de la formule de recherche comme suit :

RechercheV($B$4; tabl3; Colonne(B2); Faux)

• Dès lors, fermer la parenthèse de la seconde fonction SiErreur,
• Puis, fermer la parenthèse de la première fonction SiErreur,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

Grâce à cette technique, nous conservons active la cellule du résultat pour l'exploiter rapidement. Comme vous pouvez l'apprécier, la civilité associée à la référence demandée est extraite.

• Cliquer et glisser la poignée du résultat sur la droite jusqu'en colonne F,

Ce sont maintenant toutes les autres informations attachées à la référence qui surgissent. Et si vous changez cette référence à l'aide de la liste déroulante en cellule B4, ce sont les nouvelles informations concordantes qui sont importées depuis l'une des trois bases de données.



Par exemple, pour la référence bdd106, la ligne des informations correspondantes est trouvée dans la quatrième feuille, celle de la troisième source de données. La syntaxe complète de la formule que nous avons construite pour exercer une recherche dans de multiples sources de données est la suivante :

=SIERREUR(RECHERCHEV($B$4; tabl1; COLONNE(B2); FAUX); SIERREUR(RECHERCHEV($B$4; tabl2; COLONNE(B2); FAUX); RECHERCHEV($B$4; tabl3; COLONNE(B2); FAUX)))

Surligner la ligne dans la source
Pour une confirmation percutante du bon fonctionnement de cette formule d'extraction multi-source, nous proposons de faire ressortir automatiquement en couleur la ligne concernée dans sa base de données. Pour cela, nous devons construire une règle de mise en forme conditionnelle sur chacun des trois tableaux. Chacune de ces règles doit vérifier la potentielle concordance entre la référence choisie depuis la feuille d'extraction et celle en cours d'analyse par la règle dans la source de données.

• En bas de la fenêtre Excel, cliquer sur l'onglet bdd1 pour activer sa feuille,
• Puis, cliquer dans l'une des cellules du tableau, par exemple B4 pour le désigner partiellement,
• Dès lors, réaliser le raccourci clavier CTRL + A pour le sélectionner intégralement,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande 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 du dessous,
• Ensuite, taper le symbole égal (=) pour initier la syntaxe de la règle,
• Désigner la première cellule du tableau en cliquant sur sa cellule B3,

Il s'agit du premier titre, mais l'analyse d'une mise en forme conditionnelle est chronologique. Toutes les cellules de chaque ligne sélectionnée vont être passées en revue tour à tour. Pour que cette analyse soit fixée sur la référence de chaque ligne, nous devons figer cette cellule en colonne et la laisser se déplacer en ligne, au gré de la progression de l'analyse de la règle.

• Pour cela, enfoncer deux fois la touche F4 du clavier, ce qui donne : $B3,
• Dès lors, taper le symbole égal (=) pour annoncer le critère à honorer,
• En bas de la fenêtre Excel, cliquer sur l'onglet accueil pour revenir sur sa feuille,
• Puis, désigner la référence choisie en cliquant sur sa cellule B4, ce qui donne : accueil!$B$4,

Lorsque ce critère est honoré, cela signifie que la ligne en cours d'analyse dans la source de données est concordante avec la référence choisie par l'utilisateur depuis la feuille d'extraction. Dans ces conditions, nous devons faire ressortir explicitement cette ligne 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 une couleur verte pour le texte,
• Puis, valider cet attribut de format en cliquant sur le bouton Ok,
• De retour sur la première boîte de dialogue, cliquer sur Ok pour créer cette règle,

Nous sommes ainsi de retour sur la deuxième feuille Excel sur laquelle aucun signal de couleur ne se déclenche pour l'instant. Ce phénomène s'explique par le fait que la dernière référence que nous avons choisie est hébergée par la troisième source de données sur la feuille bdd3. Nous devons donc répliquer ces attributs de format conditionnel sur les feuilles bdd2 et bdd3 pour les faire réagir elles aussi dynamiquement. Le tableau étant toujours sélectionné, nous pouvons enchaîner.

• A gauche du ruban Accueil, double cliquer sur le pinceau (Reproduire la mise en forme),

Grâce à cet outil, nous prélevons les attributs de format en vigueur sur la plage sélectionnée, donc la règle de mise en forme conditionnelle dans sa globalité. Le double clic permet de garder l'outil actif après la première reproduction.

• En bas de la fenêtre Excel, cliquer sur l'onglet bdd2 pour activer sa feuille,
• Sélectionner alors tout le tableau, soit la plage de cellules B3:F23,
• En bas de la fenêtre Excel, cliquer sur l'onglet bdd3 pour activer sa feuille,
• Désigner tout le tableau en sélectionnant la plage B3:F23,

Comme vous pouvez le voir, la ligne de la référence choisie depuis la feuille d'extraction surgit aussitôt.

• Enfoncer la touche Echap en haut à gauche du clavier,

Ainsi, nous libérons l'outil de reproduction de mise en forme.



Et bien sûr désormais, si vous changez de référence depuis la feuille d'extraction, vous trouvez l'enregistrement correspondant surligné dans sa propre base de données.