Choisir dynamiquement le tableau Excel à imprimer

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


Sujets que vous pourriez aussi aimer :


Choix dynamique du tableau à imprimer

Lors des formations précédentes, nous avons domestiqué la fonction Excel Decaler pour adapter la zone d'impression en fonction de contraintes dynamiques définies par l'utilisateur.



Dans l'exemple illustré par la capture, l'utilisateur émet trois conditions. Il fait son choix sur le continent et définit un mois de départ ainsi qu'un mois d'arrivée. Aussitôt, la période concernée est mise en valeur dans le tableau correspondant au continent désigné. Et s'il choisit d'afficher l'aperçu avant impression, c'est précisément cette période pour le bon tableau qui est proposée automatiquement à l'édition.

Source et présentation
Pour mener à bien cette étude, nous proposons de récupérer tout d'abord ces tableaux.

• Télécharger le classeur choix-dynamique-tableau.xlsx 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é,

La feuille du classeur héberge trois tableaux représentant trois continents : l'Europe, les Amériques et l'Asie. Chacun dresse les ventes de billets enregistrées par destination pour les mois de Juin à Décembre. L'utilisateur peut choisir l'un de ces continents avec une liste déroulante en cellule K7. Afin de restreindre la période à analyser, il peut ensuite définir un mois de début et un mois de fin, avec des listes déroulantes en cellules respectives K10 et L10. Comme nous l'avons dit, en fonction de ces trois critères recoupés, la plage doit être mise en valeur automatiquement dans le bon tableau, celui du continent choisi. De plus, la zone d'impression doit s'adapter à cette plage pour proposer l'édition de la période affinée uniquement pour le continent spécifié.

Des réglages de mise en page ont été prédéfinis. Vous pouvez le constater en affichant la boîte de dialogue Mise en page. L'orientation de la feuille est définie en Paysage. Ainsi, elle peut accueillir plus facilement ces tableaux plus larges que hauts. L'édition est paramétrée pour centrer la plage résultante dans la largeur et dans la hauteur de la feuille. Enfin, la colonne B, celle des destinations est définie comme la colonne à répéter sur chaque page de l'impression. En effet, nous allons travailler sur une plage dynamique coïncidant avec la période définie par les mois choisis. Elle ne va donc pas intégrer ces destinations qui sont fondamentales à l'impression pour comprendre les données.

Ensuite, si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous constatez la présence des intitulés correspondants aux continents. Chacune de ces plages nommées ne désigne qu'une seule cellule : B5, B12 et B19. Chacune est le point de départ de son propre tableau. C'est grâce à ces noms que nous allons pouvoir établir la correspondance dynamique avec le choix de l'utilisateur dans la liste déroulante.

Plage variable du tableau variant
Pour définir dynamiquement la zone d'impression en fonction des trois critères émis, nous devons exploiter la fonction Excel Decaler. Ici, tous ses arguments sont nécessaires. Il s'agit de définir le point de départ en fonction du choix du continent. Ensuite, nous devons exercer des décalages en ligne et en colonne pour débuter sur la bonne période. Puis, nous devons ajuster la hauteur ainsi que la largeur de la plage. La hauteur est fixe ici. Chaque tableau est constitué de 5 lignes. Mais si ce n'était pas le cas, nous exploiterions la fonction Excel NbVal pour la calculer. La largeur quant à elle dépend de l'écart existant entre le mois de début et le mois de fin. Pour retravailler une zone d'impression, nous devons commencer par la définir, certes sur une plage statique premièrement.

• Sélectionner par exemple le premier tableau, soit la plage de cellules B6:I10,
• En haut de la fenêtre Excel, cliquer sur l'onglet Mise en page pour activer son ruban,
• Dans la section Mise en page du ruban, cliquer sur le bouton ZoneImpr,
• Dans la liste, choisir la commande Définir,

Nous venons de calibrer la zone d'impression sur une plage fixe, celle du premier tableau. Vous pouvez le constater en affichant l'aperçu avant impression. Le premier tableau y apparaît seul, parfaitement centré dans la largeur et dans la hauteur de la feuille.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue du gestionnaire de noms, sélectionner la plage Zone_d_impression,

Il s'agit de la plage d'édition que nous venons de définir. Elle est effectivement fixe et bornée comme le rappelle sa syntaxe dans la zone Fait référence à.

• Supprimer cette syntaxe,
• Puis, taper le symbole égal (=) pour initier la nouvelle destinée à ajuster la plage d'édition,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,

Nous devons commencer par désigner le point de départ de la plage variable. Comme vous le savez, il dépend du choix du continent. Pour que ce choix soit traduit en cellule, du fait des noms attribués, nous devons exploiter la fonction Excel Indirect.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le continent choisi en cliquant sur sa cellule K7, ce qui donne : CA!$K$7,
• Fermer la parenthèse de la fonction Indirect,
• Puis, taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Saisir le chiffre 1,

En effet, par rapport au point de départ défini sur le nom du continent, l'édition doit débuter une ligne plus bas, correspondant à la ligne des mois et ce, pour chaque tableau.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en colonne,

Pour positionner correctement le point de départ en colonne, nous devons rejoindre la position du mois de début choisi avec la première liste déroulante. Pour trouver cette position, nous devons chercher la présence de ce mois dans la ligne de titre avec la fonction Excel Equiv.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner le mois cherché en cliquant sur sa cellule K10, ce qui donne : CA!$K$10,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner par exemple la première ligne de titre, ce qui donne : CA!$C$6:$I$6,
• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la hauteur pour la fonction Decaler,
• Saisir le chiffre 5 pour considérer les 5 lignes, quel que soit le tableau,
• Taper un point-virgule (;) pour passer dans l'argument de la largeur variable,

La largeur de cette plage dépend de la distance séparant le premier mois du deuxième. Il suffit de calculer la différence entre les deux. Ces deux positions se trouvent par la fonction Equiv comme nous l'avons fait précédemment.

• Effectuer la recherche du mois de fin comme suit : Equiv(CA!$L$10; CA!$C$6:$I$6; 0),
• Inscrire le symbole moins (-) pour enclencher la soustraction,
• Effectuer la recherche du mois de début comme suit : Equiv(CA!$K$10; CA!$C$6:$I$6; 0),
• Puis, ajouter une unité à ce résultat, soit : +1,

Imaginons les mois de Janvier et Mars correspondant aux positions respectives 1 et 3. La soustraction de 3 par 1 conduit au résultat de 2 alors que la plage représente 3 mois. L'unité permet donc de corriger ce décalage.

• Fermer la parenthèse de la fonction Decaler,
• A gauche de la syntaxe, cliquer sur le bouton à la coche verte pour la valider,
• Puis, cliquer sur le bouton Fermer du gestionnaire de noms pour revenir sur la feuille Excel,

Si vous commandez l'aperçu avant impression après avoir choisi un continent et après avoir défini les deux mois, vous obtenez l'édition précise de la période uniquement pour le tableau correspondant.



La syntaxe complète de la zone d'impression dynamique est la suivante :

=DECALER(INDIRECT(CA!$K$7); 1; EQUIV(CA!$K$10; CA!$C$6:$I$6; 0); 5; EQUIV(CA!$L$10; CA!$C$6:$I$6; 0)-EQUIV(CA!$K$10; CA!$C$6:$I$6; 0)+1)

Surligner la plage variable à éditer
Comme nous l'annoncions, nous souhaitons maintenant faire ressortir explicitement les cellules de la période désignée pour le bon tableau. Nous appuierons ainsi visuellement l'impact des choix utilisateur pour produire la zone d'impression variable. Nous devons créer une règle de mise en forme conditionnelle. Celle-ci doit recouper plusieurs critères destinés à vérifier les positions en ligne et en colonne des cellules passées en revue.

• Sélectionner l'intégralité des colonnes C à I par les étiquettes,

Chaque tableau est potentiellement concerné. Donc, nous allons créer une règle sur l'ensemble des cellules qui les croisent. Mais les conditions que nous allons recouper permettront la mise en valeur précise de la période pour le tableau désigné. La mise en forme sera donc variable et dynamique.

• 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 pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire la fonction pour recouper les critères suivie d'une parenthèse, soit : Et(,
• Inscrire la première condition suivante : COLONNE()>=EQUIV($K$10; $A$6:$I$6; 0),

Souvenez-vous d'un point important, l'analyse de la mise en forme conditionnelle est chronologique. Toutes les cellules vont être passées en revue tour à tour pour tester leurs concordances avec les critères émis. Ici, nous imposons que la position en colonne de la cellule soit nécessairement supérieure ou égale à celle du mois de début désigné par l'utilisateur. Il s'agit en effet de pointer sur la bonne période.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Puis, construire la deuxième condition à croiser : COLONNE()<=EQUIV($L$10;$A$6:$I$6; 0),

Comme précédemment, c'est la fonction Equiv qui renseigne sur la position du mois défini, ici le mois de fin. Cette position horizontale ne doit pas être dépassée. Avec ces deux premiers critères recoupés, nous venons de borner la plage en largeur, précisément sur la période choisie.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Puis, construire la nouvelle condition suivante : LIGNE()>LIGNE(INDIRECT($K$7))+1,

La position en ligne doit utiliser le point de référence donné par le nom du continent. Grâce à la fonction Indirect, nous pointons sur la cellule ainsi nommée dans le tableau. Nous l'incrémentons d'une unité. Et comme nous utilisons l'inégalité stricte (>), nous nous retrouvons deux lignes plus bas, précisément sur les valeurs numériques qu'il s'agit de surligner.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Inscrire alors cette dernière condition : LIGNE()<=LIGNE(INDIRECT($K$7))+5,

Nous l'avons dit, les tableaux sont tous constitués de 5 lignes. L'indice de ligne pour la cellule en cours d'analyse ne doit pas déborder du tableau. Si les hauteurs avaient été variables, comme nous l'avons évoqué plus tôt, nous aurions exploiter la fonction Excel NbVal pour dénombrer les lignes.

• Fermer la parenthèse de la fonction Et pour terminer l'énumération,
• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un jaune foncé pour la couleur du texte,
• Valider ces attributs de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle avec le bouton Ok,

Au gré des modifications enclenchées sur les trois critères, vous constatez que la plage correspondante est parfaitement surlignée dans le tableau du continent choisi. Et bien entendu, elle recoupe la zone d'édition dynamique.



La syntaxe complète de la règle que nous avons construite est la suivante :

=Et(COLONNE()>=EQUIV($K$10; $A$6:$I$6; 0); COLONNE()<=EQUIV($L$10; $A$6:$I$6; 0); LIGNE()>LIGNE(INDIRECT($K$7))+1; LIGNE()<=LIGNE(INDIRECT($K$7))+5)