Edition automatique de la période choisie dynamiquement

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


Sujets que vous pourriez aussi aimer :


Edition automatique sur période dynamique

Nous avons déjà démontré les prouesses offertes par la fonction Excel Decaler dans différents contextes. Elle permet globalement de dynamiser chaque solution et application. Ici, nous souhaitons l'utiliser pour produire l'impression automatique des ventes réalisées sur une période à définir dynamiquement.



Sur l'exemple illustré par la capture, l'utilisateur peut définir un mois de départ ainsi qu'un mois d'arrivée à l'aide de deux listes déroulantes. Aussitôt, s'il commande l'aperçu avant impression, les chiffres des articles sont édités uniquement pour la période définie, ainsi bornée. Et bien entendu, si de nouveaux articles venaient à être ajoutés à la suite du tableau, ils devraient être intégrés à l'impression, toujours pour la période choisie.

Source et présentation
Pour avancer cette solution, nous proposons tout d'abord de récupérer le tableau de ces ventes.

• Télécharger le classeur edition-automatique-plage.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 le tableau des ventes. Il est situé entre les colonnes B et O et entre les lignes 5 et 15. Pour déterminer la plage à éditer, l'utilisateur peut émettre un mois de début à l'aide d'une liste déroulante en R5 et un mois de fin, à l'aide d'une autre liste déroulante en cellule R7. A ce stade, si vous commandez l'aperçu avant impression, ce sont toutes les cellules de la feuille qui sont considérées. De plus, aucun réglage préalable n'offre pour l'instant un rendu acceptable.

Réglages de mise en page
Des préréglages sont nécessaires avant d'entamer la construction de la plage dynamique destinée à recevoir les informations strictes à éditer. S'ils interviennent en aval en effet, ils détériorent la zone d'impression dynamique créée.

• En haut de la fenêtre Excel, cliquer sur l'onglet Mise en page pour activer son ruban,
• En bas à droite de la section Mise en page de ce dernier, cliquer sur le petit bouton d'option,

Cette action a pour effet d'afficher la boîte de dialogue Mise en page.

• Dans la section Orientation de l'onglet Page, cocher la case Paysage,
• Activer ensuite l'onglet Feuille de la boîte de dialogue,
• Cliquer dans la zone Lignes à répéter en haut pour l'activer,
• Sur la feuille, cliquer sur l'étiquette de la ligne 5, ce qui donne : $5:$5,
• Cliquer maintenant dans la zone Colonnes à répéter à gauche pour l'activer,
• Sur la feuille, cliquer sur l'étiquette de la colonne B, ce qui donne : $B:$O,

Le résultat obtenu n'est pas celui escompté en raison de la fusion de certaines cellules en première ligne. La sélection est étendue jusqu'à la colonne 0.

• Remplacer la lettre O par lalettre B, ce qui donne : $B:$B,

Avec cette colonne répétée, nous allons rappeler les informations des articles à associer avec la plage dynamique des ventes que nous allons construire. La ligne répétée quant à elle, sera utile si de nouvelles références venaient à être ajoutées, faisant déborder l'édition sur de nouvelles feuilles.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider ces réglages de mise en page,

Zone d'impression ajustée à la période
Le point de départ de la plage dynamique d'impression doit être défini sur la première cellule du tableau, soit B5. Un décalage en colonne doit ensuite être observé pour déplacer ce point de départ sur la première cellule du mois correspondant au premier choix de l'utilisateur. La hauteur de cette plage doit être ajustée dynamiquement pour inclure dans l'édition tous les articles et tous les potentiels nouveaux. Enfin, la largeur de la plage doit être ajustée en fonction de l'écart existant entre le mois de début et le mois de fin, tous deux choisis par l'utilisateur. Seul le deuxième argument de la fonction Decaler, l'argument du décalage en ligne, n'est pas à définir. Mais avant de paramétrer cette zone d'impression, nous devons la créer. Dans un premier temps elle sera statique avant que nous ne modifiions sa syntaxe.

• Sélectionner tout le tableau, soit la plage de cellules B5:O15,
• Dans la section Mise en page du ruban, cliquer sur le bouton ZoneImpr,
• Dans la liste, choisir Définir,

A ce stade, la plage d'impression est statique. Elle est définie strictement sur les bornes du tableau actuel. Vous pouvez le constatez en commandant l'aperçu avant impression.

• Cliquer désormais sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans le gestionnaire de noms, cliquer sur la plage Zone_d_impression pour la sélectionner,

Il s'agit de la plage que nous venons de définir pour initier l'édition dynamique. Elle est stricte comme nous le disions et comme le rappelle sa syntaxe dans la zone Fait référence à : =Ventes!$B$5:$O$15.

• Supprimer le contenu de la zone Fait référence à,
• Taper le symbole égal (=) pour initier la nouvelle syntaxe dynamique,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner la première cellule du tableau en cliquant sur sa cellule B5, soit : Ventes!$B$5,

Nous définissons ainsi le point de départ de la plage dynamique.

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

Ainsi nous ignorons l'argument du décalage en ligne et basculons directement dans le paramètre du décalage en colonne. Nous devons déplacer ce point de départ sur la colonne du mois de début choisi par l'utilisateur. Pour cela, nous devons trouver la position du mois défini dans la ligne de titre du tableau grâce à la fonction Equiv.

• Saisir le nom de la fonction suivi d'une parenthèse, soit : Equiv(,
• Désigner l'élément cherché en cliquant sur la cellule du mois de début en R5 : Ventes!$R$5,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner la ligne de titre du tableau, soit la plage C5:N5, ce qui donne : Ventes!$C$5:$N$5,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la hauteur variable,

Celle-ci doit en effet s'ajuster aux potentielles nouvelles références ajoutées à la suite du tableau. Elles seront ainsi naturellement intégrées dans l'édition bornée par les choix de l'utilisateur. Nous devons donc exploiter la fonction NbVal pour connaître le nombre de ces références. Nous en déduirons la hauteur.

• Inscrire le nom de la fonction suivi d'une parenthèse, soit : NbVal(,
• Désigner la colonne des références en cliquant sur son étiquette B, soit : Ventes!$B:$O,
• Comme précédemment, remplacer ensuite la lettre O par la lettre B,
• Fermer la parenthèse de la fonction NbVal,
• Retrancher deux unités à ce résultat, soit : -2,

En effet, cette colonne porte deux titres respectivement placés en ligne 1 et en ligne 3. Ils ne doivent pas être inclus dans le décompte, sans quoi ils fausseraient le calcul de la hauteur variable.

• Taper un point-virgule (;) pour passer dans l'argument de la largeur variable,

Cette largeur dépend de l'espace qui existe entre le mois de début et le mois de fin. Nous devons donc chercher leurs positions respectives, comme précédemment grâce à la fonction Equiv, puis les soustraire.

• Chercher la position du mois de fin comme suit : Equiv(Ventes!$R$7; Ventes!$C$5:$N$5;0),
• Taper le symbole moins (-) pour enclencher la soustraction,
• Chercher la position du début comme suit : Equiv(Ventes!$R$5; Ventes!$C$5:$N$5;0),
• Ajouter une unité à ce résultat, soit : +1,

En effet, la position de départ est déjà chiffrée. Imaginons que le mois de début soit le mois de Janvier. Sa position est donc 1. Le mois de fin est quant à lui défini sur le mois de Mars. Sa position est donc 3. La plage doit donc s'étendre sur une largeur de 3 colonnes. Or, la différence non recalibrée entre les deux positions (3-1), conduit à une largeur de deux colonnes.

• 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,
• Modifier les mois de début et de fin avec les listes déroulantes,
• Puis, commander l'aperçu avant impression,

Comme vous pouvez le voir, l'édition est parfaitement bornée par les limites des mois définis par l'utilisateur. Et si d'aventure vous ajoutiez de nouvelles références à la suite du tableau, vous constateriez qu'elles seraient automatiquement intégrées dans l'impression bornée. C'est ce que démontre la capture ci-dessus.

La syntaxe que nous avons construite pour la zone d'impression dynamique est la suivante :

=DECALER(Ventes!$B$5; ; EQUIV(Ventes!$R$5; Ventes!$C$5:$N$5; 0); NBVAL(Ventes!$B:$B)-2; EQUIV(Ventes!$R$7; Ventes!$C$5:$N$5; 0)-EQUIV(Ventes!$R$5; Ventes!$C$5:$N$5; 0)+1)

Marquer la période à éditer
Pour une solution encore plus pertinente, nous proposons de marquer la période bornée, directement dans le tableau. Elle doit réagir exactement de la même façon que la zone d'impression dynamique en décalage, en largeur et en hauteur. Elle doit se démarquer explicitement. Nous proposons de la faire surgir sur un fond jaune pâle grâce à une règle de mise en forme conditionnelle. Cette règle doit recouper plusieurs critères.

• Sélectionner l'intégralité des colonnes B à O par les étiquettes,

C'est ainsi que la règle sera en mesure de considérer de potentielles nouvelles références.

• 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(,
• Construire la première condition suivante : LIGNE()>5,

L'analyse de la mise en forme conditionnelle est chronologique. Toutes les cellules sélectionnées vont être passées en revue tour à tour, colonne après colonne et ligne après ligne. La période à marquer est nécessairement un chiffre placé dans le tableau. La ligne de la cellule doit donc être au moins la sixième.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Construire la nouvelle condition suivante : LIGNE()<=NBVAL($B:$B)+2,

Dans le même temps en effet, la ligne de la cellule ne doit pas excéder les bornes du tableau. Elles sont variables si de nouveaux articles sont ajoutés. Nous comptons le nombre d'éléments grâce à la fonction NbVal. Nous lui ajoutons deux unités pour considérer les deux lignes vierges au-dessus du tableau.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Construire la nouvelle condition suivante : COLONNE()>2,

Les premières ventes sont inscrites en colonne 3. Les cellules situées avant ne doivent donc pas être marquées.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Construire la nouvelle condition suivante : COLONNE()>=EQUIV($R$5;$A$5:$O$5;0),

Comme précédemment, nous exploitons la fonction Equiv afin de déceler la position du mois de début dans le tableau. Dès lors que la colonne de la cellule atteint cette position, elle peut être marquée.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Construire la nouvelle condition suivante : COLONNE()<=EQUIV($R$7;$A$5:$O$5;0),

Dans le même temps bien entendu, la colonne de la cellule ne doit pas dépasser la position du mois de fin.

• Fermer la parenthèse de la fonction Et,
• 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 de texte,
• Valider ces attributs de format par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

Désormais, à chaque changement de période, la zone d'impression dynamique est marquée sur fond jaune directement sur la feuille. Et là encore, si vous ajoutez de nouveaux articles à la suite du tableau, vous constatez que le repérage s'étend pour les intégrer, exactement comme la zone d'impression les incorpore.

La syntaxe de la règle de mise en forme conditionnelle que nous avons bâtie est la suivante :

=Et(LIGNE()>5; LIGNE()<=NBVAL($B:$B)+2; COLONNE()>2; COLONNE()>=EQUIV($R$5; $A$5:$O$5; 0); COLONNE()<=EQUIV($R$7; $A$5:$O$5; 0))