Synthèses Excel sur un nombre variable de mois

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


Sujets que vous pourriez aussi aimer :


Synthèses sur des plages variables

Sommer les ventes sur des plages définies dynamiquement est un bon moyen d'obtenir des résultats de synthèse ciblés pour correspondre à la demande à l'instant T. C'est une solution que la fonction Excel Decaler permet d'atteindre facilement.



Dans l'exemple finalisé illustré par la capture, l'utilisateur choisit un numéro de mois par le biais d'une liste déroulante, située sur la droite du tableau de données. Aussitôt les synthèses des ventes par article sont dressées sur les lignes du dessous, sur deux colonnes. La première consolide les ventes sur la période concernée, du premier mois jusqu'à la limite invoquée. La seconde consolide les chiffres sur la période exclue par la limite définie.

Source et présentation
Pour la mise en place de ces techniques statistiques, nous avons besoin de travailler à partir d'une source de données.

• Télécharger le classeur synthese-plage-variable.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é,

Un tableau recense donc les ventes réalisées par article pour chaque mois de l'année. L'énumération de ces mois apparaît en ligne 5.



Si vous sélectionnez l'un d'entre eux et que vous consultez sa barre de formule, vous constatez qu'il s'agit d'une date complète. C'est un format personnalisé qui permet de restreindre son affichage. Cette astuce va nous permettre d'établir plus facilement la correspondance avec le numéro de mois désigné par l'utilisateur.

Et précisément, sur la droite du tableau figure une zone de synthèse en attente de nos calculs. En cellule S3, l'utilisateur peut actionner une liste déroulante pour choisir un numéro de mois. En fonction de ce numéro, la somme des ventes par article doit être calculée pour la période en première colonne et en dehors de la période pour la seconde colonne.

Somme sur une rangée variable
Le point de départ pour la première somme est connu pour chaque article. Il s'agit des cellules respectives placées en colonne C. L'autre extrémité est en revanche dynamique. Elle dépend du numéro de mois choisi avec la liste déroulante. Nous devons exploiter ce numéro pour définir la position de cette borne par rapport au point de départ. Et pour cela, nous devons imbriquer la fonction Excel Decaler dans la somme. Il s'agit de régler précisément son troisième argument. C'est lui qui permet de définir un décalage par rapport à une position initiale.

• Sélectionner la première somme à calculer, soit la cellule R6,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction pour l'addition suivie d'une parenthèse, soit : Somme(,
• Définir le point de départ de la plage pour le premier article en cliquant sur sa cellule C6,
• Inscrire le symbole deux points (:) pour annoncer la borne inférieure à suivre,

Cette action a pour effet de générer automatiquement la plage C6:C6. Ce n'est pas ce que nous souhaitons.

• Supprimer les coordonnées de la cellule placée après le symbole deux points,
• Inscrire la fonction pour déplacer une borne, suivie d'une parenthèse, soit : Decaler(,
• Désigner de nouveau le point de départ de la plage en cliquant sur la cellule C6,

Pour opérer un décalage et définir l'emplacement de la borne à l'autre extrémité, nous devons en effet commencer par désigner la position d'origine. C'est grâce à elle que nous allons pouvoir définir les positions à incrémenter en fonction du numéro de mois.

• Taper un point-virgule suivi du chiffre 0, soit : ;0,

En effet, nous ne souhaitons observer aucun décalage en ligne par rapport à cette position d'origine. Seul le déplacement horizontal nous intéresse ici.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en colonne,
• Sélectionner la valeur de la liste déroulante en cliquant sur sa cellule S3,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $S$3,

Cette formule est destinée à être répliquée sur les lignes du dessous afin de calculer la somme variable pour chacun des articles. Et lors de cette réplication, le déplacement à observer doit toujours être pioché dans cette cellule de référence. Elle ne doit donc pas suivre le mouvement.

• Retrancher une unité à la valeur cliquée, soit : -1,

En effet la position de départ est incluse dans le calcul. Par exemple, pour un déplacement défini avec le nombre 9, en partant du premier mois, nous pointerions sur le dixième et non le neuvième mois, sans ce retranchement.

• Fermer la parenthèse de la fonction Decaler,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons active la cellule du résultat. Nous allons donc pouvoir l'exploiter dans la foulée.

• Double cliquer sur la poignée du résultat,

Ainsi, nous répliquons automatiquement la logique du calcul sur la hauteur du tableau de synthèse. Il en résulte les sommes pour tous les articles sur des plages variables en largeur.



Si vous changez le numéro de mois à l'aide de la liste déroulante en cellule S3, les sommes s'actualisent automatiquement.



A titre de vérification, vous pouvez sélectionner les chiffres d'un article jusqu'au mois défini. En consultant la barre d'état, en bas à droite de la feuille Excel, vous constatez que la somme des valeurs désignées vient parfaitement recouper le résultat du calcul dynamique impliquant la fonction Decaler. La formule que nous avons construite est la suivante :

=SOMME(C6:DECALER(C6; 0; $S$3-1))

Somme à partir d'une borne
Désormais, nous devons consolider les résultats manquants. Il s'agit de réaliser la somme des chiffres par article, pour la période exclue. Cette fois, la cellule de fin est connue. Elle est placée en colonne N. Il s'agit du mois de décembre. C'est la cellule de départ qui varie en fonction du choix du mois. Elle doit correspondre à la cellule placée juste après le numéro de mois choisi. C'est donc cette borne initiale que nous cette fois ajuster avec la fonction Excel Decaler.

• Sélectionner la première somme à calculer en colonne S, soit la cellule S6,
• Taper le symbole égal (=) pour démarrer la construction de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Inscrire la fonction de décalage suivie d'une parenthèse, soit : Decaler(,
• Désigner la première valeur pour le premier article en cliquant sur sa cellule C6,

Ainsi, nous définissons le point de référence à partir duquel il s'agit de réajuster la position en fonction du numéro de mois défini.

• Taper un point-virgule suivi du chiffre zéro : ;0, pour n'opérer aucun décalage en ligne,
• Taper un point-virgule (;) pour passer dans l'argument du déplacement en colonne,
• Sélectionner le numéro de mois choisi en cliquant sur sa cellule S3,

Ainsi, nous déplaçons le point de départ de la plage juste après ce même mois.

• Enfoncer la touche F4 du clavier pour figer cette cellule dans le calcul, ce qui donne : $S$3,
• Fermer la parenthèse de la fonction Decaler,
• Taper le symbole deux points (:) pour annoncer la borne à l'autre extrémité,
• Sélectionner la dernière valeur de la ligne en cliquant sur sa cellule N6,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,
• Enfin, double cliquer sur la poignée du résultat pour répercuter la logique,

Nous obtenons désormais la somme dynamique sur la période variable exclue. Et pour confirmer la bonne cohérence de ces résultats, vous pouvez sélectionner les deux totaux en bas du tableau de synthèse. En consultant une fois encore la barre d'état, vous notez que le cumul conduit au même résultat que celui du tableau d'origine.

Repérer visuellement les plages sommées
Pour identifier clairement les champs d'action et recouper les résultats de synthèse obtenus par les sommes variables, nous suggérons de bâtir deux règles de mise en forme conditionnelle. Toutes deux sont très simples. Elles consistent à faire ressortir les cellules concernées dans les mêmes jeux de couleurs que ceux proposés dans le tableau de synthèse. Il suffit de faire la correspondance entre le mois de la date dans le tableau de données et le numéro choisi avec la liste déroulante.

• Sélectionner toutes les valeurs du tableau, soit la plage de cellules C6:N15,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone source du dessous, construire la syntaxe suivante : =MOIS(C$5)<=$S$3,

L'analyse opère sur les dates de la ligne 5. C'est la raison pour laquelle la ligne est figée tandis que la colonne est libérée. Ainsi, elles seront toutes passées en revue. Le test consiste à confirmer que le mois de la date en cours d'étude est bien inférieur ou égal au numéro de mois choisi. Si le critère est satisfait, il s'agit de la plage choisie pour la synthèse. Les cellules doivent réagir sur un fond vert pâle avec un texte vert foncé.

• 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 vert pâle,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un vert foncé pour le texte,
• Valider ces attributs avec le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle explicite le contexte. Toutes les cellules de la période définie doivent apparaître en texte vert sur fond vert clair.

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

De retour sur la feuille, les plages sont effectivement clairement identifiées. Et bien entendu, si vous modifiez le numéro de mois en cellule S3, les attributs s'ajustent automatiquement pour repérer la nouvelle zone associée.

En présélectionnant toutes les données du tableau et selon le même procédé, une seconde règle doit être construite. Elle consiste à identifier la zone exclue, selon la syntaxe suivante : =MOIS(C$5)>$S$3. Dès lors que le mois de la date est supérieur au numéro spécifié, les cellules doivent se remplir d'un jaune clair avec un texte jaune foncé.



Il est bien sûr possible d'étendre le principe au cas où les deux bornes de la plage seraient variables. Pour les mois, le numéro de départ serait inscrit en S3 et le numéro de fin en T3 par exemple. La syntaxe serait alors la suivante : =SOMME(DECALER(C6; 0; $S$3-1):DECALER(C6; 0; $T$3-1)). Nous obtiendrions le cumul des chiffres sur la plage variable bornée.