Séries logiques Excel incrémentées par paliers

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


Sujets que vous pourriez aussi aimer :


Suites logiques incrémentées par paliers

Dans cette formation Excel, nous proposons d'apporter la solution pour créer des séries incrémentées respectant des paliers. Il s'agit de suites logiques numériques en escalier. Pour parler d'un cas concret simple, à partir du chiffre 1, nous souhaitons obtenir naturellement le chiffre 2 puis le 3 et le 4 etc.., en répétant chacun d'eux 3 fois par exemple, avant de réaliser l'incrémentation. La série créée serait donc : 1 1 1, 2 2 2, 333 etc...

Un exemple d'applicatif direct est la construction automatique de plannings raisonnant sur l'énumération des numéros de semaine, en débutant depuis une date définie par le concepteur.



Comme l'illustre la capture ci-dessus de l'application Excel finalisée, l'utilisateur indique la date à partir de laquelle le planning doit être construit. Puis, grâce à une liste déroulante, il choisit le nombre de jours travaillés à matérialiser, pour chaque semaine à représenter dans la construction.

Ainsi, à partir du premier numéro de semaine retranscrit, l'énumération se poursuit en respectant un palier d'une longueur variable. Cette longueur en nombre de lignes équivaut au choix numérique réalisé dans la liste. C'est seulement après que survient l'incrémentation du numéro de semaine.

Source et présentation du concept
Comme toujours, nous proposons de débuter les travaux depuis un classeur source. Il offre la structure du planning. Ce dernier doit bien entendu être implémenté des numéros de semaines incrémentés, tout en respectant un palier variable correspondant aux nombres de jours travaillés.

• Télécharger le classeur suites-logiques-par-paliers.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur n'est constitué que d'une seule feuille nommée Series-auto. Le planning, en attente des numéros de semaine, propose déjà l'énumération des heures de travail pour chaque journée. Une liste déroulante en B5 est préconçue. Elle permet de définir le nombre de jours travaillés qui composent chaque semaine. Il s'agit du fameux palier à respecter dans l'incrémentation. L'énumération des numéros de semaines doit intervenir en colonne D, en fonction d'une date de départ à définir en cellule B8.

Nous proposons de débuter par une démonstration simple pour mieux appréhender la logique du système à bâtir.

• Ajouter une nouvelle feuille à ce classeur,
• Sur cette nouvelle feuille, inscrire le chiffre 1 en cellule C1 par exemple,
• Sélectionner alors la cellule C2 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Saisir la fonction donnant la ligne d'une cellule suivie d'une parenthèse, soit Ligne(,
• Cliquer sur la cellule C1 et fermer la parenthèse de la fonction Ligne,
• Taper le symbole = pour l'égalité à vérifier,
• Cliquer de nouveau sur la cellule C1,
• La multiplier par 3, soit : *3,
• Puis taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Cliquer sur la cellule C1 et lui ajouter une unité pour l'incrémentation, soit : C1+1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Cliquer sur la cellule C1 et fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée pour conserver la cellule active,
• Tirer la poignée du résultat sur une trentaine de lignes en dessous,

Comme vous le constatez, nous avons créé une suite numérique logique qui s'incrémente en respectant un palier de 3 unités. Il s'agit du facteur de multiplication que nous avons exploité dans la formule. Chaque chiffre est donc répété trois fois avant de subir l'incrémentation. Le calcul que nous avons créé est le suivant :

=SI(LIGNE(C1)=C1*3; C1+1; C1)

Explication de la formule : Nous réalisons un test grâce à la fonction Excel conditionnelle Si. Ce test consiste à vérifier si l'indice de ligne de la cellule précédente a atteint trois fois la valeur précédente (C1*3). C'est la fonction Excel Ligne qui renseigne sur le numéro de ligne d'une cellule. Si cette condition est vérifiée, cela signifie que le palier des 3 unités est atteint. Il est donc temps d'incrémenter la valeur d'une unité (C1 + 1). Sinon cela signifie que la marche de l'escalier n'est pas terminée. Donc nous conservons la même valeur pour finir le palier.

Incrémentation des numéros de semaine
Nous devons appliquer le raisonnement précédent pour permettre une construction automatique du planning. Le contexte est néanmoins plus complexe dans la mesure où la série ne débute pas à partir du chiffre 1. La suite incrémentée par paliers doit commencer à partir du nombre correspondant au numéro de semaine de la date de départ. L'astuce consiste à ramener ce numéro de semaine au chiffre 1 par soustraction, pour simplifier la comparaison. N'oublions pas non plus que le palier devient variable. Il ne s'agit plus du chiffre statique 3. La longueur de la répétition doit dépendre du choix numérique émis par l'utilisateur, par le biais de la liste déroulante.

• Revenir sur la feuille Series-auto,
• En cellule B8, réaliser le raccourci clavier CTRL + ;

La date du jour apparaît aussitôt. Il s'agit d'une date statique. Cette astuce comme tant d'autres est enseignée par la formation sur les trucs et astuces dans Excel.

• Valider cette saisie automatique en enfonçant la touche Entrée,

En cellule D8, nous devons restituer le numéro de semaine de cette date. Il s'agira du point de départ pour la construction du planning sur la base d'une série incrémentée par paliers. C'est la fonction Excel No.Semaine.Iso qui permet d'indiquer le numéro de semaine d'une date.

• Sélectionner la cellule D8 et taper le symbole = pour débuter le calcul,
• Saisir la fonction retournant le numéro de semaine, suivie d'une parenthèse, soit No.Semaine.Iso(,
• Cliquer sur la cellule B8 pour lui transmettre la date du calcul,
• Fermer la parenthèse de la fonction et valider la formule par la touche Entrée,

Le numéro de semaine correspondant s'affiche en effet. Ce calcul est bien dynamique. Si vous modifiez la date, le numéro de semaine s'adapte automatiquement. Le calcul réalisé est très simple : =NO.SEMAINE.ISO(B8).

C'est à partir de la cellule suivante (D9) que nous devons reproduire la série logique incrémentée, respectant le palier défini en B5 par la liste déroulante.

• Cliquer sur la cellule D9 pour la sélectionner,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Saisir la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit Ligne(,
• Sélectionner une cellule de la première ligne, par exemple A1,
• Fermer la parenthèse de la fonction Ligne,
• Taper le symbole = pour l'égalité à vérifier,
• Ouvrir une parenthèse et sélectionner le premier numéro de semaine, soit : (D8,
• Taper le symbole moins (-) pour enclencher la soustraction,
• Cliquer de nouveau sur le premier numéro de semaine, soit D8,
• Enfoncer la touche F4 du clavier pour figer cette cellule, ce qui donne : $D$8,
• Ajouter une unité puis fermer la parenthèse, soit : +1),
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Sélectionner le palier à respecter en cliquant sur la cellule B5,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul, ce qui donne : $B$5,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Sélectionner le premier numéro de semaine et lui ajouter une unité, soit : D8+1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Sélectionner de nouveau le premier numéro de semaine, soit D8,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Tirer la poignée du résultat jusqu'en bas du planning, soit jusqu'en ligne 37,

Comme vous le constatez et comme l'illustre la capture ci-dessus, les numéros de semaines sont parfaitement incrémentés après avoir subi une répétition égale au chiffre défini en B5 par la liste déroulante. La formule que nous avons bâtie est la suivante :

=SI(LIGNE(A1) = (D8-$D$8+1)*$B$5; D8+1; D8)

Explications : Nous comparons l'indice de ligne en partant de la première (LIGNE(A1)), au numéro de semaine ramené à 1 (D8-$D$8+1). Nous figeons la référence utilisée pour la soustraction afin de reproduire artificiellement le raisonnement d'une suite logique partant du chiffre 1, et progressant d'une unité à chaque fois. Nous multiplions cette différence par le pas à respecter (*$B$5). Si la progression de l'indice de ligne, du fait de la réplication du calcul vers le bas, est équivalente à ce facteur de multiplication, cela signifie que le numéro de semaine a bien été répété le nombre de fois indiqué. Dans ce cas, nous enclenchons l'incrémentation (D8+1). Dans le cas contraire, nous poursuivons la répétition (D8), pour respecter le palier à franchir.

• Sélectionner le chiffre 3 à l'aide de la liste déroulanteen B5,

Comme vous le constatez, la suite logique se reconstruit automatiquement. Désormais le planning énumère les numéros de semaines en les répétant trois fois chacun avant de passer à l'incrémentation. Nous réalisons bien une construction automatique du planning sur la base d'un nombre de jours travaillés variable. C'est la construction d'une série logique incrémentée, qui plus est dynamique, qui permet cette automatisation.

Construire les numéros de jours
La restitution des numéros de jours en colonne E est très simple. Nous devons indiquer s'il s'agit du premier, du deuxième jour de la semaine etc... Cette construction peut être réalisée sur la reconnaissance de la répétition des jours de semaines. Si le numéro a été repéré une seule fois, il s'agit du premier jour. S'il a été identifié deux fois, il s'agit du deuxième jour et ainsi de suite. Il suffit de compter le nombre de fois qu'un numéro de semaine a déjà été identifié, sur une plage de cellules qui évolue en même temps que le calcul est répliqué. C'est la fonction Excel Nb.Si qui permet de réaliser ce type de dénombrement. Nous produirons ainsi naturellement l'énumération et l'incrémentation des jours. La syntaxe de la fonction Nb.Si est la suivante :

=Nb.Si(Plage_ou_compter; Valeur_à_dénombrer)

La plage où compter ne peut pas être initialisée sur l'intégralité de la colonne D. Les numéros de semaines y sont tous répétés et la technique ne fonctionnerait pas. Elle doit débuter sur la même ligne que le calcul, soit D8. Et en partant de cet emplacement de référence à figer, elle doit progresser en même temps que la formule est répliquée sur les lignes du dessous. Il s'agit donc de la plage $D$8:D8. C'est une technique que nous avions mise en oeuvre pour repérer les doublons dans une plage de cellules qui progresse.

La valeur à compter sur cette plage est le numéro de semaine lui-même. Le nombre de répétitions indiquera le numéro du jour.

• Sélectionner la première cellule du calcul, soit E8,
• Saisir la fonction de dénombrement suivie d'une parenthèse, soit Nb.si(,
• Cliquer sur la cellule D8 pour indiquer le point de départ de la plage du décompte,
• Taper le symbole deux points (:) pour générer la plage D8:D8,
• Figer les références de la première des deux, ce qui donne : $D$8:D8,

Ainsi, la borne inférieure de la plage du décompte progressera en même temps que la formule est répliquée sur les lignes du dessous. Les répétitions seront repérées au fur et à mesure. Dans le même temps, la borne supérieure ne bouge pas pour que ces repérages s'additionnent.

• Taper un point-virgule (;) pour passer dans l'argument de la valeur à compter,
• Sélectionner de nouveau le premier numéro de semaine ou saisir ses références, soit D8,
• Fermer la parenthèse de la fonction Nb.Si,
• Valider le calcul par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Double cliquer sur la poignée du résultat pour répliquer la logique sur la hauteur du planning,

La balise active qui apparaît en bas du calcul peut être nécessaire pour corriger les défauts de la mise en forme répercutée.

Comme l'illustre la capture ci-dessous, les numéros de jours sont parfaitement reproduits. Ils sont incrémentés au gré des répétions identifiées. Dès que le numéro de semaine change, l'énumération repart fort logiquement du chiffre 1.



Tous ces calculs étant liés, une modification du palier réorganise la structure globale du planning.

• Sélectionner le chiffre 5 avec la liste déroulante,

Les numéros de semaines sont répétés cinq fois chacun. De fait, pour chacun d'entre eux, les numéros de jours subissent l'incrémentation jusqu'au cinquième avant de redémarrer. En conséquence, nous construisons automatiquement un planning considérant des semaines de 5 jours travaillés chacune.

Repérer dynamiquement chaque nouvelle semaine
Pour parfaire l'élaboration du planning, nous souhaitons réaliser des démarcations visuelles à chaque changement de semaine. Bien entendu, cette mise en forme ne doit pas être manuelle. Elle doit réagir et s'adapter aux variantes du planning. Sa lecture s'en trouvera simplifiée.

Nous devons donc bâtir une règle de mise en forme conditionnelle, déclenchant une couleur de remplissage à chaque changement de semaine. Le critère est simple, il consiste à vérifier si le numéro de semaine en cours est supérieur d'une unité au précédent.

• Sélectionner toutes les cellules du planning, soit la plage D8:Q37,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste qui apparaît, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie située juste en dessous pour l'activer,
• Taper le symbole = pour débuter la syntaxe du critère,
• Sur la feuille, sélectionner le premier numéro de semaine soit D8,
• Enfoncer deux fois la touche F4 du clavier pour ne figer ses références qu'en colonne, soit $D8,

En effet, pour toutes les colonnes d'une même ligne, le critère doit toujours être vérifié sur le numéro de semaine, donc en colonne D figée. Mais pour chaque ligne, il s'agit de tester la condition par rapport au numéro de semaine correspondant, soit de la ligne en cours, qui doit suivre le déplacement. Une mise en forme conditionnelle raisonne en effet comme un calcul répliqué. Elle passe en revue les cellules chronologiquement en partant de la première.

• Taper le symbole = pour l'égalité de la condition à satisfaire,
• Sélectionner la cellule située au-dessus de celle de la semaine en cours, soit D7,
• Enfoncer deux fois la touche F4 du clavier pour les mêmes raisons, ce qui donne : $D7,
• Ajouter une unité, soit : +1,

La cellule D7 ne contient aucun numéro de semaine. Mais elle évoluera au fur de la vérification organisée par la mise en forme conditionnelle sur chacune des lignes. Le critère que nous venons de bâtir consiste à vérifier si le numéro de semaine pour la ligne en cours est nouveau. S'il est répété, il ne peut pas vérifier cette incrémentation (+1). Lorsque la semaine change, nous devons associer une couleur de fond dynamique.

• Cliquer sur le bouton Format placé en bas de la boîte de dialogue,
• Dans la nouvelle boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu-gris assez pâle,
• Valider ce réglage en cliquant sur le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle indique quelle mise en valeur sera déclenchée dynamiquement selon le critère spécifié juste au-dessus.

• Valider cette règle de mise en forme conditionnelle en cliquant sur le bouton Ok,

Chaque ligne de chaque début de semaine apparaît dans une mise en forme, désormais explicitement différente. Si vous changez le palier grâce à la liste déroulante, la mise en valeur s'adapte dynamiquement, repérant parfaitement chaque changement. Nous avons donc réussi la construction automatique du planning, sur la base de séries incrémentées respectant des paliers variables.