Chercher dans plusieurs tableaux Excel

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


Sujets que vous pourriez aussi aimer :


Chercher dans plusieurs tableaux

Avec cette nouvelle astuce Excel, nous allons voir comment il est possible de trouver des informations précises, sans connaître par avance le tableau les hébergeant. De plus et nous allons le constater, ces tableaux peuvent très bien proposer des organisations et structures différentes.



Dans l'exemple finalisé illustré par la capture, nous travaillons à partir de deux tableaux. Ils pourraient être plus nombreux et disposés sur des feuilles différentes. La technique que nous allons démontrer s'adapte à tout contexte. Ces tableaux relatent les ventes réalisées par les commerciaux d'une entreprise sur certains mois de l'année. Le premier archive les résultats des Séniors tandis que le second stocke les scores des Juniors. Et comme vous pouvez le voir, le second est plus long que le premier.

L'utilisateur choisit d'abord un groupe avec une première liste déroulante (Juniors ou Seniors). Dès lors, il choisit un commercial appartenant à ce groupe avec une deuxième liste déroulante, placée sur la droite de la première. Enfin, il isole le mois souhaité à l'aide d'une troisième et dernière liste déroulante. Aussitôt, le chiffre du commercial pour le mois souhaité est extrait. Et dans le même temps, cette donnée surgit en couleur dans le bon tableau.

Classeur source et présentation
Pour la démonstration de cette nouvelle astuce, nous suggérons de récupérer un classeur hébergeant ces tableaux et articulant déjà les listes déroulantes.

• Télécharger le classeur choix-dynamique-tableau.xlsm 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écouvrons les deux tableaux des commerciaux avec une implémentation effectivement avancée.

• Déployer la liste déroulante de la cellule C3, dans le bandeau au-dessus des tableaux,
• Parmi les deux propositions, choisir le groupe Seniors,
• Déployer alors la liste déroulante de la cellule E3,

Comme vous pouvez le voir, seuls les noms du groupe précédemment choisis sont proposés. Ces deux listes déroulantes sont donc dépendantes. Nous y reviendrons.

• Choisir l'un des commerciaux suggérés par cette seconde liste déroulante,
• Puis, choisir un mois avec la troisième liste déroulante placée en cellule G3,

Comme vous pouvez le voir, le résultat cherché sur ces critères recoupés est automatiquement mis en valeur dans le tableau qui l'héberge. C'est une simple règle de mise en forme conditionnelle qui a été prédéfinie sur ces deux tableaux afin de repérer les cellules croisées.



Pour la consulter, vous devez d'abord sélectionner au moins l'une des cellules du tableau. Ensuite, dans la section Styles du ruban Accueil, vous devez cliquer sur le bouton Mise en forme conditionnelle. Puis, en bas des propositions, vous devez opter pour l'option Gérer les règles. Dans la boîte de dialogue qui suit, il ne vous reste plus qu'à cliquer sur le bouton Modifier la règle pour consulter sa syntaxe : =ET($B6=$E$3; C$5=$G$3). Lorsque le nom du commercial ($B6) correspond à celui choisi dans la deuxième liste déroulante ($E$3) et lorsque dans le même temps, le mois dans le tableau (C$5) correspond au mois choisi avec la troisième liste déroulante ($G$3), une couleur dynamique est appliquée. Pour le second tableau, c'est une seconde règle qui est construite à l'identique, mais forcément adaptée aux coordonnées de ses plages.

Nous devons maintenant poursuivre la découverte. Tous les éléments bâtis en amont permettent de comprendre la construction de la solution que nous allons amener.

• En haut à gauche de la feuille Excel, déployer la zon Nom à gauche de la barre de formule,

Comme vous pouvez le voir, cinq noms de plages de cellules ont été créés. Vous pouvez visualiser les zones qu'elles désignent en cliquant tour à tour sur ces noms. Les données numériques du premier tableau sont nommées Seniors. Elles représentent la plage de cellules C6:E11. Les données numériques du second tableau sont nommées Juniors. Elles représentent la plage de cellules H6:J14. Les plages des noms à chercher sont nommées comme les tableaux avec en préfixe l'intitulé noms pour simplifier la correspondance par calcul. Ainsi, nomsSeniors représente la plage de cellules B6:B11 tandis que nomsJuniors représente la plage de cellules G6:G14. Enfin, seule la première plage de mois est nommée Mois. Elle représente la plage de cellules C5:E5. En effet et nous le verrons, la fonction Equiv raisonne de façon relative à la sélection émise pour la recherche. Les deux tableaux ont la même largeur. Donc la position retournée coïncidera pour ces deux tableaux.



C'est grâce à ces plages nommées que nous avons premièrement créé la relation de dépendance entre la première liste déroulante et la deuxième. Dans l'outil Validation des données pour la deuxième liste déroulante, nous avons établi la syntaxe suivante : =INDIRECT("noms"&$C$3). En fonction du groupe choisi par le biais de la première liste déroulante ($C$3), nous définissons dynamiquement la plage source de la seconde. Par exemple, pour le groupe des Seniors, les noms sont prélevés sur la plage nomsSeniors.

Extraction par correspondances
Il est temps maintenant de procéder à l'extraction du chiffre d'affaires correspondant à ces trois critères recoupés. Cette extraction doit se faire dans le bandeau horizontal situé au-dessus du tableau, plus précisément en cellule J3. Il est donc question d'importer la donnée numérique située au croisement du commercial choisi et du mois désigné, dans un tableau qui n'est a priori pas connu à l'avance.

L'extraction doit se faire sur un tableau choisi dynamiquement en fonction du groupe sélectionné par l'utilisateur avec la première liste déroulante. Grâce aux plages nommées, ce choix pointe directement sur le bon tableau. Et pour extraire des données, la fonction Excel Index est dédiée. Ensuite, pour trouver les positions du commercial et du mois dans ce tableau défini dynamiquement, c'est nécessairement la fonction Excel Equiv qui est à l'honneur.

• Cliquer sur la cellule J3 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

En effet et vous l'avez sans doute constaté, au changement de groupe, la cellule du nom est automatiquement vidée, ce qui paraît logique. Nous reviendrons sur le mécanisme qui réalise cette petite prouesse automatiquement. De fait donc, plus aucune concordance ne peut être trouvée. Et dans ces conditions, les fonctions de recherche répondent par une erreur. Grâce à la fonction SiErreur englobant la suite du calcul, nous choisissons de neutraliser ces défauts.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,

En premier argument de cette fonction, nous devons désigner le tableau dans lequel la recherche doit être effectuée. Ce tableau est reconnu par son nom et ce nom est donné par le choix du groupe à l'aide de la première liste déroulante. Mais il s'agit d'une information textuelle à interpréter.

• En conséquence, inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner alors le tableau en cliquant sur la cellule C3 du choix du groupe,
• Puis, fermer la parenthèse de la fonction Indirect,
• Dès lors, taper un point-virgule (;) pour passer dans l'argument de la ligne de la fonction Index,

La ligne de l'information à extraire dépend du choix du nom réalisé par le biais de la deuxième liste déroulante. Sa position peut être cherchée et trouvée grâce à la fonction Equiv.

• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Désigner le nom cherché en cliquant sur la cellule E3,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,

Souvenez-vous, celle-ci est variable et peut se reconstruire dynamiquement. Il s'agit d'un assemblage avec le préfixe noms et le nom du groupe sélectionné et désignant le tableau. Une simple concaténation fait donc l'affaire pour pointer sur la bonne colonne des noms. Mais une fois encore, cette information textuelle doit être interprétée.

• Inscrire de nouveau la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Taper le préfixe de la colonne variable entre guillemets, soit : "noms",
• Enfoncer la touche 1 en haut à gauche du clavier pour le symbole de concaténation (&),
• Puis, désigner le tableau de recherche en cliquant sur la cellule C3,

Nous venons donc de définir dynamiquement, grâce à la fonction Indirect et aux plages nommées, la colonne des noms dans laquelle la recherche doit être effectuée.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0 pour réaliser une recherche exacte,
• Puis, fermer la parenthèse de la fonction Equiv,

De fait, nous sommes de retour dans les arguments de la fonction Index. Nous devons maintenant déceler la position de la colonne à croiser en fonction du choix du mois réalisé à partir de la troisième liste déroulante. Ces mois se recherchent sur la ligne intitulée Mois. Souvenez-vous, la position renvoyée est relative au tableau d'extraction.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Inscrire de nouveau la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Désigner le mois cherché en cliquant sur la cellule G3,
• Taper un point-virgule (;) pour passer dans l'argument de la zone de recherche,
• Désigner la ligne des mois par son nom, soit : Mois,
• Taper un point-virgule suivi du chiffre zéro pour réaliser une recherche exacte, soit : ;0,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ("") pour garder la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur et valider la formule avec la touche Entrée,

Comme vous pouvez le voir, le résultat correspondant aux trois conditions recoupées est parfaitement extrait en même temps qu'il est surligné par la règle de mise en forme conditionnelle.



Et bien entendu, si vous changez de groupe avec la première liste déroulante et de nom avec la deuxième, l'extraction est merveilleusement réalisée dans l'autre tableau. Nous sommes donc parvenus à construire le mécanisme permettant d'entreprendre des recherches dans un tableau qui n'est pas connu à l'avance.

Nous avions annoncé que nous y reviendrions pour l'explication. La cellule de la deuxième liste déroulante se vide automatiquement au changement de groupe opéré par la première liste. C'est une petite astuce VBA Excel qui scrute l'événement de ce changement pour opérer en tâche de fond.

• Réaliser le raccourci clavier ALT + F11,

Nous basculons ainsi dans l'éditeur de code VBA Excel. Dans l'explorateur de projet sur la gauche de l'écran, l'élément Feuil2 (quelTableau) doit être sélectionné. Ainsi, la feuille de code au centre de l'écran indique les actions entreprises par le VBA sur la feuille sur laquelle nous avons travaillé. Vous y trouvez la construction d'une procédure événementielle :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ligne As Integer: Dim colonne As Integer
ligne = Target.Row: colonne = Target.Column
If (ligne = 3 And colonne = 3) Then
Range("E3").Value = ""
End If
End Sub

Elle déclenche son code lorsqu'un changement est détecté dans l'une des cellules de la feuille. Mais grâce à une instruction conditionnelle (If), nous la faisons réagir seulement lorsqu'il s'agit de la cellule C3 (If (ligne = 3 And colonne = 3)), soit celle du groupe défini avec la liste déroulante. Dès lors, nous vidons automatiquement la cellule du nom (Range("E3").Value).