Consolider les chiffres d'affaires par semaines

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


Sujets que vous pourriez aussi aimer :


Consolider les chiffres par semaines

Une astuce Excel exploitant la fonction Decaler montre comment réaliser des sommes alternées toutes les N valeurs.



Dans l'exemple finalisé illustré par la capture, nous exploitons ce procédé pour consolider tous les chiffres d'affaires réalisés par semaines ouvrées. En d'autres termes, l'enjeu consiste à cumuler les chiffres par groupes de cinq lignes correspondant aux cinq jours de la semaine.

Source et présentation
Pour la mise en place de cette astuce, nous proposons de récupérer un classeur offrant déjà ces données à manipuler.

• Télécharger le classeur sommes-ca-par-semaine.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é,

Entre les colonnes C et D, des chiffres d'affaires sont énumérés pour chaque jour de la semaine sur environ trois mois. Vous notez la présence d'une mise en forme conditionnelle permettant de repérer le premier jour de chaque semaine (Ruban Accueil / Mise en forme conditionnelle / Gérer les règles) =JOURSEM($C4;2)=1. Elle facilite le repérage des bornes pour les périodes à consolider.

Et précisément en colonne G, il est question de cumuler ces chiffres par groupes de cinq, pour fournir la synthèse des ventes effectuées chaque semaine. Il est donc question de réaliser des additions sur des plages d'une hauteur de cinq cellules et de reprendre cycliquement le processus à l'issue de chaque période. Une mise en forme conditionnelle est aussi prévue sur cette colonne : =$G4<30000. Elle est destinée à faire ressortir visuellement toutes les consolidations conduisant à un résultat médiocre.

Cumuls toutes N valeurs
Pour alterner l'addition des chiffres avec une fréquence bien déterminée, l'astuce consiste à exploiter la fonction Excel Decaler. La somme doit débuter à partir du premier chiffre recensé en colonne D. Elle doit s'étendre sur cinq lignes, consolider les résultats et recommencer sur les cinq lignes du dessous. Pour cela, nous devons être capable d'exercer un décalage en ligne progressant avec la formule répliquée en colonne G. Et c'est le deuxième argument de la fonction Excel Decaler qui permet de réaliser cette prouesse.

• Sélectionner la cellule G4 pour la première consolidation à calculer pour la semaine 2,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner le tout premier chiffre d'affaires en cliquant sur sa cellule D4,
• Enfoncer la touche F4 du clavier pour figer cette cellule, ce qui donne : $D$4,

C'est effectivement par rapport à ce point de départ immuable que nous allons devoir réaliser des décalages en ligne, en concordance avec la réplication de la formule, afin d'isoler les plages à sommer.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Ouvrir une parenthèse en prévision d'une factorisation à venir,
• Inscrire la fonction donnant la ligne d'une cellule, sans argument, soit : Ligne(),

La fonction Ligne sans argument renvoie la ligne en cours, celle du calcul qui débute à l'indice 4.

• Puis, lui soustraire quatre unités, soit : -4,

Ce retranchement permet d'annihiler le décalage observé en raison des lignes situées au-dessus du calcul.

• Fermer la parenthèse de la factorisation,
• Puis, multiplier le tout par 5, soit: *5,

Pour la première consolidation, le décalage en ligne est nul (4-4)*5=0. Donc, le cumul débute bien à partir du premier chiffre d'affaires. Pour le deuxième (5-4)*5=5, la consolidation commence bien au premier jour de la semaine suivante et ainsi de suite (6-4)*5=10 pour les autres. Il reste maintenant à définir la hauteur du calcul par rapport à chaque point de départ ainsi décalé de cinq unités. Cette hauteur est fixe. Elle est de cinq cellules pour correspondre aux cinq jours de chaque semaine.

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

Ainsi, nous ignorons l'argument du décalage en colonne. La somme doit en effet être réalisée dans la rangée des chiffres d'affaires, soit la colonne D. Ce décalage n'est pas à observer. De fait, nous sommes désormais positionnés dans l'argument facultatif de la hauteur pour la fonction Decaler.

• Inscrire le chiffre 5 pour une addition sur une hauteur de cinq cellules,
• 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 gardons active la cellule du résultat pour l'exploiter dans la foulée.

• Double cliquer sur la poignée du résultat pour répliquer la formule sur tout le tableau,

Comme vous le constatez toutes les sommes consolidées se calculent. Dans le même temps, la règle de mise en forme conditionnelle repère aussitôt les résultats peu satisfaisants. La syntaxe de la formule que nous avons construite est la suivante : =SOMME(DECALER($D$4; (LIGNE()-4)*5;; 5)).



Il est très simple de valider la cohérence de ces calculs. Il suffit de sélectionner les cinq chiffres d'affaires d'une semaine. Dès lors, en consultant l'information de la somme sur les cellules sélectionnées dans la barre d'état, vous constatez qu'elle recoupe parfaitement celle du calcul pour la semaine désignée.

Naturellement, cette technique se décline facilement pour réaliser tout type de sommes alternées avec des hauteurs variables. Il suffit d'adapter le facteur (5), exploité en deuxième et quatrième arguments de la fonction Excel Decaler.