Impression dynamique ajustée à la zone utile

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


Sujets que vous pourriez aussi aimer :


Edition adaptée aux cellules remplies

Dans cette formation, nous apportons la solution pour réaliser des impressions dynamiques automatiquement ajustées au contenu du tableau. L'idée consiste à définir les réglages de mise en page en amont. Ainsi, au gré de l'évolution des informations, la zone d'impression s'adapte au contenu pour produire automatiquement et uniquement l'édition de la partie utile.



Dans l'exemple illustré par la capture, nous travaillons sur un tableau de saisie des chiffres d'affaires réalisés par article. Ces inscriptions se font au coup par coup. C'est la raison de la présence de cases vides. La donnée la plus avancée concerne le mois de Juin pour l'article Ar04. L'impression doit automatiquement s'étendre pour présenter une vue de tous les articles, jusqu'à ce mois de Juin. Toutes les colonnes suivantes doivent donc être automatiquement exclues. Mais bien sûr et vous l'avez compris, si des données sont ajoutées à la suite, l'édition doit évoluer pour intégrer les nouvelles colonnes.

Source et présentation
Pour les démonstrations des techniques, nous devons tout d'abord commencer par récupérer ce tableau de saisie.

• Télécharger le classeur impression-auto-ajustee.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 réceptionnons donc ce tableau en cours d'enrichissement. Après le mois de Juin, toutes les colonnes sont vides. En déployant la zone Nom en haut à gauche de la feuille Excel, vous notez que des intitulés ont été attribués à des plages de cellules. Celui qui nous intéresse est l'intitulé Tab. Il représente la plage de cellules B5 à N15, soit la plage maximale dédiée à l'édition.

A ce stade, si vous essayez un aperçu avant impression avec le raccourci clavier CTRL + P par exemple, vous constatez fort logiquement que les réglages de page ne sont absolument pas prêts pour une édition en bonne et due forme.

Réglages de mise en page
Avant de débuter, il est important de réaliser quelques paramétrages statiques de page. Puisque le tableau est plus large que haut, l'orientation de la feuille doit être changée par exemple. De plus, si ces réglages interviennent après la conception de la zone d'impression dynamique, ils détériorent cette dernière.

• 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 Orientation,
• Dans la liste, choisir Paysage,

Nous disposons désormais d'une feuille plus large que haute pour accueillir le tableau à l'impression.

• Cliquer sur le petit bouton d'option situé en bas à droite de la section Mise en page,

Nous affichons ainsi la boîte de dialogue Mise en page pour des réglages personnalisés et avancés.

• Activer l'onglet Marges de la boîte de dialogue,
• Dans la section Centrer sur la page, cocher les cases Horizontalement et Verticalement,

De cette manière, l'édition ajustée de notre tableau sera parfaitement centrée en largeur comme en hauteur.

• Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur la feuille,
• Dans la section Mise à l'échelle du ruban, déployer la liste déroulante Largeur,
• Dans les propositions, choisir 1 page,

De cette façon, si le tableau ajusté est trop grand à l'édition, il sera réduit pour tenir sur une page. Et s'il est plus petit que la largeur de la feuille, il sera étiré.

Ajuster la zone d'impression
Nous l'avons découvert lors de la formation précédente, en définissant une zone d'impression, nous créons une plage de cellules nommée. C'est elle que nous devons retravailler depuis le gestionnaire de noms avec la fonction Decaler. Son objectif est d'étendre la zone d'impression jusqu'à la dernière colonne portant effectivement une valeur, donc jusqu'à la dernière colonne renseignée. Pour cela, nous devons commencer par définir une zone d'impression arbitraire.

• Sélectionner le tableau sans la dernière colonne, soit la plage de cellules B5:N15,
• Dans la section Mise en page du ruban du même nom, cliquer sur le bouton ZoneImpr,
• Dans la liste, choisir Définir,

Désormais, l'édition est calée sur l'intégralité du tableau. Vous pouvez le constater en commandant l'aperçu avant impression. Mais ce n'est pas ce que nous souhaitons. Nous voulons une édition ajustée dynamiquement au contenu utile.

• 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 qui suit, sélectionner la plage Zone_d_impression,

En consultant la zone Fait référence à, vous constatez que cette plage est stricte, telle que nous l'avons définie : =Ventes!$B$5:$N$15. Nous devons la rendre dynamique pour qu'elle s'ajuste au gré des informations inscrites. Nous devons donc retravailler cette syntaxe à l'aide d'une formule.

• Supprimer le contenu de la zone Fait référence à,
• Puis, taper le symbole égal (=) pour initier la syntaxe,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner la toute première cellule du tableau, soit B5, ce qui donne : Ventes!$B$5,

Nous renseignons ainsi le point de départ de la plage variable. Nous devons maintenant ajuster celle-ci en hauteur et largeur grâce au quatrième et au cinquième arguments de la fonction.

• Taper trois points-virgules successifs, soit : ;;;,

En effet, les deux arguments suivants ne nous intéressent pas. Ils concernent respectivement le décalage en ligne et le décalage en colonne par rapport à ce point de départ. En les ignorant ainsi, ils seront fixés à zéro par la fonction Decaler. De fait, nous nous trouvons propulsés dans l'argument de la hauteur de la plage.

• Inscrire la fonction comptant les cellules non vides, suivie d'une parenthèse, soit : NbVal(,
• Puis, cliquer sur l'étiquette de la colonne B en haut de la feuille Excel,

En raison de la présence de cellules fusionnées sur la première ligne de la feuille, le résultat obtenu ne correspond pas à nos attentes : Ventes!$B:$O.

• Remplacer manuellement la lettre O par la lettre B,

Ainsi, pour définir la hauteur variable de la plage de cellules, nous comptons tous les éléments contenus dans la colonne des références. De cette manière, si de nouveaux articles sont ajoutés, ils seront intégrés dynamiquement à l'édition. Cependant, deux unités doivent être retranchées. Le lien en cellule B1 n'est pas à inclure dans le décompte. De plus, le titre fusionné en cellule B3 ne doit pas être considéré.

• Fermer la parenthèse de la fonction NbVal,
• Puis, retrancher deux unités à ce décompte, soit : -2 ,
• Taper un point-virgule (;) pour passer dans l'argument de la largeur variable,

C'est ce paramètre qui doit dénouer le problème. Nous ne connaissons pas la ligne dont la colonne enrichie est la plus grande. Nous devons raisonner sur le tableau complet nommé Tab à la recherche des cellules non vides. Lorsque le critère est satisfait nous devons prélever l'indice de colonne le plus grand sur cette plage. Pour cela, nous devons imbriquer la fonction conditionnelle Si dans la fonction Max.

• Inscrire la fonction Max suivie d'une parenthèse, soit : Max(,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner le tableau par son nom, soit : Tab,
• Puis, taper le critère d'inégalité suivant : <>'',

Il consiste à vérifier les cellules non vides sur cette plage. Comme la fonction Si est elle-même imbriquée dans la fonction Max, l'action que nous allons entreprendre sur la colonne, ressortira la valeur la plus grande pour la dernière cellule non vide.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction donnant l'indice de colonne suivie d'une parenthèse, soit : Colonne(,
• Désigner de nouveau le tableau par son nom, soit : Tab,
• Fermer la parenthèse de la fonction Colonne,
• Fermer la parenthèse de la fonction Si,
• Fermer la parenthèse de la fonction Max,
• Puis, retrancher une unité à ce résultat dynamique, soit : -1,

En effet, la fonction Colonne raisonne en valeur absolue. Or le tableau débute à partir de la deuxième colonne de la feuille, soit la colonne B. Par ce retranchement, nous excluons donc la première colonne vide pour annuler ce décalage.

• Fermer la parenthèse de la fonction Decaler,
• Puis, cliquer sur le bouton à la coche verte sur la gauche de la zone de saisie,

De cette façon, nous validons et enregistrons la syntaxe de la plage variable pour la zone d'impression.

• Cliquer sur le bouton Fermer du gestionnaire de nom pour revenir sur la feuille Excel,

Désormais, si vous commandez l'aperçu avant impression, seule la zone utile est considérée à l'édition. La zone d'impression s'arrête au mois de Juin. Si vous inscrivez une donnée dans un mois supérieur, la zone d'édition s'étend pour intégrer sa colonne. De plus, le tableau résultant est parfaitement centré dans la largeur et dans la hauteur de la page, grâce à nos réglages préliminaires. Si vous ajoutez un article supplémentaire à la suite du tableau, il est lui-même considéré, grâce à l'emploi de la fonction NbVal dans l'argument de la hauteur de la fonction Decaler. En revanche, si c'est cette nouvelle ligne qui contient l'inscription la plus éloignée, elle n'est pas considérée pour l'édition. En effet, pour la détection des colonnes, nous travaillons sur un tableau aux bornes fixes, nommé Tab.

Il s'agit dès lors de retravailler sa syntaxe depuis le gestionnaire de noms, comme suit :

=DECALER(Ventes!$B$6:$N$6;;; NBVAL(Ventes!$B:$B)-2)



Ainsi, la zone d'impression est parfaitement dynamique. Elle s'adapte à la dernière référence en ligne et à la dernière cellule portant une valeur en colonne.

Repérer la plage d'impression
Pour parfaire la solution, nous proposons de bâtir une règle de mise en forme conditionnelle. Son rôle est d'identifier sur un fond de couleur explicitement différent, la zone utile dédiée à l'édition.

• Sélectionner l'intégralité des colonnes B à N en cliquant et glissant depuis leurs étiquettes,
• 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 ...,
• Cliquer dans la zone de saisie juste en-dessous pour l'activer,
• Puis reproduire la syntaxe suivante pour la règle de mise en forme conditionnelle :

=ET(LIGNE()>5; LIGNE()-4<=NBVAL($B:$B)-2; colonne()>=3; COLONNE()<=MAX(SI(Tab<>''; COLONNE(Tab))))

Nous agissons sur l'intégralité des colonnes de manière à pouvoir considérer toutes les nouvelles données qui sont susceptibles d'intervenir dans la hauteur du tableau. De fait, nous sommes obligés de vérifier de multiples conditions croisées. Nous les recoupons grâce à la fonction Excel ET.

Le premier critère : LIGNE()>5, consiste simplement à vérifier que la ligne de la cellule en cours d'analyse est bien au moins la sixième, soit celle qui correspond au début de l'énumération des chiffres.

La deuxième condition : LIGNE()-4<=NBVAL($B:$B)-2, s'assure que la ligne en cours d'analyse ne dépasse pas la hauteur du tableau. Les quatre unités retranchées correspondent aux quatre lignes vides au-dessus du tableau. Elles ne doivent pas être considérées. Pour estimer la hauteur des données à surligner, nous exploitons la fonction NbVal. Elle compte le nombre de références dans la colonne B. Les 2 unités retranchées correspondent aux deux titres à ne pas comptabiliser au-dessus du tableau.

Le troisième critère : colonne()>=3, permet de débuter la mise en forme à partir de la colonne C, celle des chiffres, pour ne pas inclure les références en colonne B.

Enfin, la dernière condition : COLONNE()<=MAX(SI(Tab<>''; COLONNE(Tab))), est semblable à celle que nous avons utilisée dans la construction de la fonction Decaler. Elle trouve la colonne jusqu'à laquelle il convient d'appliquer le format. Et précisément, ces attributs de format doivent maintenant être définis.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle,
• Activer alors l'onglet Police de cette même boîte de dialogue,
• Avec la liste déroulante, choisir un jaune foncé pour le texte,
• Puis, valider ces attributs de format par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle rappelle les attributs à appliquer aux cellules lorsque ces dernières vérifient les conditions croisées émises par la règle. Il nous reste à vérifier que ces indicateurs mettent effectivement en valeur la plage utile à l'impression.

• Valider la création de la règle de mise en forme conditionnelle par le bouton Ok,

De retour sur la feuille, vous constatez que la zone utile est effectivement parfaitement bornée. De plus, si vous inscrivez une nouvelle donnée dans une colonne non encore utilisée, la mise en forme progresse en largeur. De la même façon, si vous ajoutez un nouvel article à la suite du tableau, la mise en forme progresse en hauteur. Elle vient donc parfaitement corroborer le fonctionnement de la zone d'impression dynamique.