Choisir le tableau à importer avec une case à cocher

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


Sujets que vous pourriez aussi aimer :


Choisir un tableau avec une case à cocher

A l'occasion de précédentes astuces Excel, nous avons montré comment engager des interactions entre les données d'une feuille et des barres de défilement. Ici, nous allons voir comment faire réagir les données de tableaux grâce à des groupes de case à cocher que l'on appelle plus communément des cases d'option.



Sur l'exemple illustré par la capture, l'utilisateur coche l'une des cases d'option située sur la droite du tableau. Et aussitôt, les données de la feuille correspondante sont importées au centre de l'écran faisant automatiquement réagir la représentation graphique du dessous. Il s'agit d'une bonne solution pour analyser les informations émanant de plusieurs sources.

Nous pourrions d'ailleurs envisager d'exploiter deux groupes de cases d'option pour importer deux tableaux et les comparer instantanément grâce à des règles de mise en forme conditionnelle prédéfinies.

Classeur source
Pour la mise en place de cette nouvelle astuce, nous proposons de récupérer un classeur Excel offrant la console sur une première feuille et les données des douze mois de l'année sur les suivantes.

• Télécharger le classeur case-cocher-tableau.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 la première feuille de ce classeur. Son tableau est vide. Il est en attente des données à importer dynamiquement. En conséquence, la zone du graphique reste muette. Sur la droite de cette feuille, vous notez la présence des douze cases d'option. Comme elles appartiennent au même groupe, le fait de cocher l'une d'entre elles décoche automatiquement le précédent choix. Ce sont les cellules voisines en colonne J qui enregistrent leur état. La case Décembre étant cochée, elle est sanctionnée par le booléen Vrai en cellule J14. Toutes les autres sont identifiées par le booléen Faux. C'est cette valeur remarquable que nous allons pouvoir exploiter pour faire la correspondance entre la case cochée et la feuille du classeur hébergeant le tableau à importer. Nous y reviendrons.

En bas de la fenêtre Excel, vous notez la présence des douze autres onglets. Chacun héberge un tableau relatant les ventes réalisées sur les quatre semaines du mois correspondant.



C'est chacun de ces tableaux qui doit pouvoir être importé au clic sur l'une des cases d'option.

Les cases d'option
Les cases d'option que nous avons préparées sont des contrôles ActiveX qui offrent de nombreuses propriétés faciles à régler.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,

S'il n'est pas disponible dans votre environnement, vous pouvez l'activer facilement. Pour cela, vous devez cliquer droit n'importe où sur le ruban actif. Dans le menu contextuel, vous devez choisir la commande Personnaliser le ruban. Dans la liste de droite de la boîte de dialogue qui s'affiche, vous devez cocher la case Développeur et valider par le bouton Ok.

• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Sur la première feuille, cliquer sur l'une des cases d'option par exemple celle de Janvier,

Cette action a pour effet non pas de cocher la case mais de la sélectionner, grâce au mode création.

• Dès lors, cliquer sur le bouton Propriétés, toujours dans la section Contrôles du ruban,

La fenêtre propriétés apparaît. Elle permet entre autres de donner un nom et un intitulé à l'objet sélectionné sur la feuille. Elle permet aussi de modifier l'apparence du contrôle avec les couleurs de police et de fond. Mais les deux attributs qui nous intéressent se nomment respectivement GroupName et LinkedCell. Toutes les cases de la feuille sont associées au groupe du même nom. C'est la raison pour laquelle elles réagissent ensemble. Le fait de cocher l'une d'entre elles, décoche toutes les autres. De plus chaque case d'option est liée à une cellule qui lui est propre comme ici la cellule J3 pour la première case par le biais de sa propriété LinkedCell. C'est cette cellule qui mémorise sous forme de booléen l'état de la case.

• Cliquer sur la croix de la fenêtre Propriétés pour la masquer,
• Dans le ruban, cliquer de nouveau sur le bouton Mode création pour désactiver la conception,

Position de la case cochée
Pour débuter et pour procéder par étape, le premier enjeu consiste à déceler la position de la case cochée dans la colonne des booléens. Cette indication fournira le numéro incrémenté qui est utilisé en suffixe du nom de la feuille Cible. Dès lors, nous pourrons envisager de pointer dessus dynamiquement en reconstruisant son nom. Il ne restera plus qu'à importer les données qu'elle héberge, correspondant au choix de l'utilisateur, par le biais de la case d'option.

Nous devons donc exploiter la fonction Equiv pour trouver la position du booléen Vrai dans la plage de cellules J3:J14.

• Sélectionner la première case du tableau en cliquant sur sa cellule C3,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit Equiv(,
• Taper le booléen Vrai pour désigner la valeur cherchée,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la plage des cellules liées aux cases d'option, soit : J3:J14,
• Puis, enfoncer la touche F4 du clavier pour la figer totalement, ce qui donne : $J$3:$J$14,

Certes, nous ne sommes pas en train de bâtir la formule finale pour importer les données du tableau choisi par la case cochée. Mais ce fragment fera bien partie de la syntaxe aboutie. Donc, nous figeons cette plage pour que la recherche soit toujours réalisée dans ces valeurs booléennes tandis que nous répliquerons la formule sur les autres cellules du tableau.

• Taper un point-virgule suivi du chiffre zéro, soit ;0 pour réaliser une recherche exacte,
• Fermer alors la parenthèse de la fonction Equiv,
• Puis, valider la formule avec la touche Entrée du clavier,

Désormais et comme vous pouvez le constater, à chaque clic sur une case à cocher, nous récoltons sa position dans l'organisation. Et cette position est aussi le numéro incrémenté de la feuille sur laquelle nous devons pointer pour importer les données du tableau choisi.

Reconstruire le nom de la feuille
Il est maintenant question de reconstruire l'adresse de la plage de cellules à prélever. Et comme vous le savez, celle-ci doit être préfixée du nom de la feuille dont nous connaissons déjà le préfixe statique (Mois_) et désormais le suffixe numérique dynamique. Dès lors, c'est le point d'exclamation qui doit être utilisé pour descendre dans la hiérarchie des objets et atteindre les cellules de la feuille ainsi mentionnée.

• Cliquer de nouveau sur la cellule C3 pour la resélectionner,
• Dans sa barre de formule, cliquer après le symbole égal (=) pour y placer le point d'insertion,
• Inscrire le préfixe des noms de feuille entre guillemets, soit : "Mois_",
• Puis, taper le symbole de concaténation (&) pour associer le numéro dynamique qui suit,
• Dès lors, cliquer après la parenthèse fermante de la fonction Equiv,

Nous venons de reconstituer le nom de la feuille choisie dynamiquement. Nous devons maintenant lui associer la plage de cellules à utiliser comme source d'extraction. Il s'agit toujours de la même (B2:E6). Les tableaux ont tous la même structure.

• Taper de nouveau le symbole de concaténation (&),
• Puis inscrire entre guillemets la plage de cellules, soit : "!$B$2:$E$6",

Il ne faut pas oublier de la figer pour que les bornes du tableau d'extraction ne varient pas au gré de la réplication de la formule. Il ne faut pas oublier non plus de préfixer la plage du point d'exclamation, comme nous l'avons expliqué précédemment. Pour que la touche F4 des références absolues fonctionne, il faut l'utiliser sur la plage avant de fermer les guillemets. Ces derniers transforment en effet les références en texte.

• Enfin, valider la syntaxe avec la touche Entrée du clavier,

Désormais et comme vous pouvez l'apprécier, à chaque clic sur une case d'option, l'adresse de la plage cible est parfaitement recomposée. Et la syntaxe de notre formule est relativement simple :

="Mois_"& EQUIV(VRAI; $J$3:$J$14; 0) & "!$B$2:$E$6"

Importer les données de la feuille cochée
Maintenant, il ne nous reste plus qu'à procéder à l'extraction des données. Pour cela, nous devons exploiter la fonction Index sur la plage de cellules reconstruite dynamiquement.

• Cliquer de nouveau sur la cellule C3 pour la resélectionner,
• Dans sa barre de formule, cliquer après le symbole égal pour y placer le point d'insertion,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,

En premier argument, nous devons lui fournir la plage de cellules à partir de laquelle la recherche doit être effectuée pour réaliser l'extraction. Cette plage est déjà précisément donnée par la syntaxe qui suit et que nous avons construite précédemment. Mais à ce stade, elle est considérée comme un texte par Excel. Nous devons lui demander de l'interpréter pour considérer les coordonnées.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Cliquer alors à la toute fin de la syntaxe pour y placer le point d'insertion,
• Puis, fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne de la fonction Index,

Le premier indice de chaque tableau est situé sur la deuxième ligne de chaque feuille. Mais attention, la fonction Index raisonne de façon relative par rapport à la plage d'extraction qui lui est passée en premier paramètre. Il s'agit donc de la première ligne. Pour que toutes les cellules soient correctement pointées au fil de la réplication de la formule, nous devons retrancher deux unités à l'indice de ligne en cours (3-2) pour la formule écrite. Et la fonction qui renvoie ce numéro est la fonction Excel Ligne().

• Inscrire la fonction sans argument pour la ligne en cours, soit : Ligne(),
• Puis, retrancher deux unités, soit : -2,
• Dès lors, taper un point-virgule(;) pour passer dans l'argument de l'indice de colonne,

Le principe est le même. La colonne C est la troisième colonne. Il faut la ramener sur la première.

• Inscrire la fonction sans argument pour la colonne en cours, soit : Colonne(),
• Puis, lui retrancher de nouveau deux unités, soit : -2,
• Fermer la parenthèse de la fonction Index,
• Puis valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi et comme vous le savez, nous conservons active la cellule du résultat. La première information tombe et elle correspond bien au nom du mois de la case cochée. Cette donnée est effectivement inscrite en première cellule du tableau sur chaque feuille. Pour reproduire la formule sur tout le tableau sans dégrader les attributs de mise en forme prédéfinis, nous proposons une méthode astucieuse.

• Sélectionner toutes les données du tableau, soit la plage de cellules C3:F7,
• Enfoncer la touche F2 du clavier pour entrer en mode modification de la cellule,
• Puis, valider avec le raccourci clavier CTRL + Entrée,

La logique de la formule est ainsi répliquée sur l'intégralité du tableau. Toutes les données du mois coché sont effectivement rapatriées et dans le même temps, le graphique surgit pour représenter ces informations avec plus de clarté. Désormais, à chaque clic sur une case d'option, le tableau change dynamiquement et sa représentation en histogramme s'adapte automatiquement. La syntaxe complète de la formule d'importation que nous avons construite est la suivante :

=INDEX(INDIRECT("Mois_" & EQUIV(VRAI; $J$3:$J$14; 0) & "!$B$2:$E$6"); LIGNE()-2; COLONNE()-2)