Extraction Excel et tableau de recherche inconnu

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


Sujets que vous pourriez aussi aimer :


Recherche sur un tableau variable

Avec cette nouvelle astuce Excel, nous allons apprendre à effectuer des recherches dans un tableau qui n'est pas défini à l'avance. Et pour cela, nous allons exploiter la fonction d'extraction la plus basique. Il s'agit de la fonction RechercheV.



Dans l'exemple finalisé illustré par la capture, nous choisissons un trimestre à l'aide d'une première liste déroulante, puis nous définissons une équipe avec une seconde liste déroulante. Instantanément, le score de l'équipe ciblée pour le trimestre mentionné est extrait. Et dans le même temps, la donnée concernée est mise automatiquement en valeur dans le bon tableau.

Source et présentation
Pour la démonstration de cette astuce, nous suggérons de récupérer un classeur offrant différents tableaux à partir desquels la recherche peut être réalisée.

• Télécharger le classeur chercher-tableau-variable.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é,

En cliquant sur les cellules B4 et C4, vous constatez la présence effective des listes déroulantes pour désigner respectivement un trimestre et une équipe. Ces trimestres représentent chacun des tableaux de données. Ils sont précisément reconnus par ces intitulés choisis avec la première liste déroulante. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

Extraire à partir d'une plage variable
C'est grâce à ces plages nommées que nous allons pouvoir faire agir indirectement la fonction RechercheV sur le bon tableau. Pour éviter de retourner une erreur lorsqu'aucune donnée de recherche n'est définie, nous devons englober le calcul dans la fonction Excel de gestion des anomalies. Il s'agit de la fonction SiErreur.

• Cliquer sur la cellule D4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire la fonction de gestion des anomalies avec une parenthèse, soit : SiErreur(,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Recherchev(,
• Désigner l'équipe cherchée par ses coordonnées, soit : C4,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

C'est à cet emplacement précis que l'astuce réside. Pour désigner le bon tableau de recherche, nous ne pouvons nous contenter de désigner le trimestre choisi par l'utilisateur à l'aide de la première liste déroulante. Excel considèrerait cette information comme un texte et non comme une plage. Pourqu'il puisse établir la correspondance, nous devons exploiter la fonction d'interprétation. Il s'agit de la fonction Indirect.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le trimestre choisi en cliquant sur la cellule B4,

De cette façon, nous désignons indirectement le tableau reconnu par son nom pour effectuer la recherche.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne à retourner,
• Saisir le chiffre 2,

De cette manière, nous indiquons d'extraire les points situés en seconde colonne du tableau choisi pour l'équipe définie dynamiquement comme élément de recherche.

• Taper un point-virgule suivi du booléen Faux, soit : ;Faux, pour une recherche exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ("") pour garder la cellule vide en cas de recherche infructueuse,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider la formule d'extraction avec la touche Entrée du clavier,

Comme vous pouvez le constater, les points pour l'équipe et le trimestre définis dynamiquement sont parfaitement extraits. Et bien sûr, si vous changez de trimestre et/ou d'équipe, le résultat s'actualise parfaitement.

Surligner la valeur extraite
Pour aboutir la solution, nous proposons de repérer en couleur la valeur extraite dans le tableau choisi. L'utilisateur pourra ainsi opérer la concordance avec une grande simplicité. Mais en raison du fractionnement des tableaux et de la nécessité des références absolues, nous devons bâtir une première règle de mise en forme conditionnelle sur le premier tableau et l'adapter manuellement sur les autres en ajustant les coordonnées impliquées.

• Sélectionner les données du premier tableau, soit la plage de cellules B8:C12,
• 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 du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme,
• Taper la fonction permettant de recouper les critères, suivie d'une parenthèse, soit : Et(,
• Désigner le titre du tableau en cliquant sur sa cellule B7, ce qui donne : $B$7,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Désigner le trimestre choisi en cliquant sur sa cellule B4, ce qui donne : $B$4,
• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Désigner la première équipe en cliquant sur sa cellule B8, soit : $B$8,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, ce qui donne : $B8,

Vous le savez, l'analyse d'une mise en forme conditionnelle est chronologique. C'est la raison pour laquelle nous débutons l'étude à partir de la première équipe afin de trouver la concordance avec celle choisie par le biais de la liste déroulante. Pour qu'elles soient toutes passées en revue ligne à ligne, nous devons libérer la cellule sur cet indice.

• Taper le symbole égal (=) pour annoncer la seconde condition à vérifier,
• Puis, désigner l'équipe choisie en cliquant sur sa cellule C4, ce qui donne : $C$4,
• Dès lors, fermer la parenthèse de la fonction Et,

Lorsque le nom du tableau est le bon et que l'équipe coïncide, nous devons faire ressortir la ligne concernée dans des attributs de format explicitement différents.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un vert assez vif pour la couleur de texte,
• Valider ce réglage avec le bouton Ok,
• De retour sur la première boîte, cliquer sur Ok pour valider la règle de mise en forme,

Et de retour cette fois sur la feuille, les agréments dynamiques ne sont pas forcément flagrants à cause du choix du trimestre avec la première liste déroulante. Le trimestre1 doit être actif pour que les données du premier tableau puissent réagir.

Dès lors, il convient d'adapter cette règle respectivement sur les trois autres tableaux en les associant à une couleur de police verte :

=ET($E$7=$B$4; $E8=$C$4)
=ET($H$7=$B$4; $H8=$C$4)
=ET($K$7=$B$4; $K8=$C$4)

Désormais, à chaque changement de trimestre et/ou d'équipe par le biais des deux listes déroulantes, vous voyez la couleur de repérage se déplacer afin de corroborer le résultat d'extraction fourni par la fonction RechercheV.