Sommer les N premières valeurs avec Excel

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


Sujets que vous pourriez aussi aimer :


Additionner les N premières valeurs

Nous avons déjà utilisé la fonction Excel Decaler pour réaliser des extractions et des opérations sur des plages variables. Ici, nous allons l'exploiter à des fins statistiques pour réaliser la somme des chiffres sur les N premiers mois.



Sur l'exemple finalisé illustré par la capture, l'utilisateur définit un nombre de mois à l'aide d'une liste déroulante, au-dessus d'un petit tableau de synthèse. Aussitôt, la somme des chiffres d'affaires est consolidée et livrée pour la période demandée. Dans le même temps, une mise en forme conditionnelle surligne dynamiquement les chiffres des mois correspondants.

Source et présentation
Pour réaliser ces travaux, nous proposons tout d'abord de récupérer un fichier hébergeant ce petit tableau des ventes consolidées par mois.

• Télécharger le classeur somme-sur-les-premieres-valeurs.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 découvrons donc le tableau énumérant les CA réalisés au cours des mois de l'année entre les colonnes B et C. Sur sa droite des résultats sont attendus en cellules F7 et F9. Le premier consiste à cumuler les ventes sur les N premiers mois, en fonction du choix émis dans la liste déroulante de la cellule F5. Le second doit conduire au total sur la période exclue, soit pour les mois restants. Ces données permettront de facilement recouper les résultats avec le total fourni par le premier tableau.

Un format conditionnel personnalisé est défini sur la cellule de la liste déroulante. Il permet d'expliciter le choix. En effet, la liste déroulante est bien nourrie de valeurs numériques quant à elle. Vous pouvez consulter ce format en sélectionnant premièrement la cellule puis en choisissant l'option Autres formats numériques dans la liste des formats du ruban Accueil.



Nous avions appris à dompter ces formats conditionnels à l'occasion d'une formation dédiée.

[<=1]'Le premier';'Les '0' premiers'

Lorsque la valeur portée par la cellule ne dépasse pas le chiffre 1, elle affiche le texte Le premier. Dans le cas contraire, c'est un assemblage avec la valeur numérique (0) choisie pour réaliser l'accord.

Sommer les N premiers mois
Dans les formations précédentes, nous avons réglé les paramètres du décalage en ligne et du décalage en colonne afin d'ajuster les opérations aux plages variables.

=Decaler(Point_de_départ; Décalage_ligne; Décalage_colonne; [Hauteur]; [Largeur])

Mais ici, il s'agit de réaliser une somme sur une plage qui varie en hauteur, en fonction du choix émis par l'utilisateur. Nous allons donc exploiter le quatrième argument de la fonction Decaler. Celui-ci est facultatif. C'est la raison pour laquelle nous n'avons pas pris soin de le renseigner lors des travaux précédents.

• Sélectionner la cellule F7 et taper le symbole égal (=) pour débuter 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(,
• Cliquer sur la cellule C5 du premier chiffre d'affaires pour déterminer le point de départ,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0,

En effet et comme nous l'avons expliqué, nous ne souhaitons observer aucun décalage en ligne par rapport à ce point de départ. Seule la hauteur de la plage doit s'ajuster dynamiquement.

• Taper de nouveau un point-virgule suivi du chiffre zéro, soit : ;0,

La raison est identique. Nous ne souhaitons observer aucun décalage en colonne par rapport à ce point dedépart.

• Taper un point-virgule (;) pour passer dans l'argument de la hauteur à définir,
• Désigner alors le choix de l'utilisateur en cliquant sur sa cellule F5,
• Fermer la parenthèse de la fonction Decaler,
• Fermer la parenthèse de la fonction Somme,
• Enfin, valider la formule à l'aide de la touche Entrée du clavier,

Pour réaliser cette somme, nous n'avons pas eu besoin de définir une borne supérieure puis une borne inférieure. Nous avons simplement défini une plage de cellules de hauteur variable grâce à la fonction Decaler.

=SOMME(DECALER(C5;0; 0; F5))

Quoiqu'il en soit, le résultat tombe et il est bien sûr dynamique. Vous le constatez en changeant le nombre de mois pour modifier la période.



Et si vous sélectionnez les chiffres réalisés sur la période mentionnée, vous pouvez constater le résultat de synthèse livré par la barre d'état en bas à droite de la fenêtre Excel. Il recoupe parfaitement la somme du calcul dynamique, confirmant sa parfaite cohérence.

Additionner les mois restants
Pour sommer les chiffres sur la période exclue par le choix du nombre de mois, nous devons de nouveau entreprendre une addition sur une plage de cellules de hauteur variable. Mais son point de départ doit aussi être décalé pour débuter l'opération à partir du premier mois en dehors de la période. La hauteur se détermine en soustrayant le nombre de mois choisi aux 12 mois constituant une année.

• Sélectionner le résultat à trouver en cliquant sur sa cellule F9,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

En effet, un cas particulier doit être géré dans ce calcul. Il intervient lorsque l'utilisateur choisit de réaliser la synthèse sur les 12 mois. Dans ce cas, la période exclue n'existe pas et la somme ne peut être générée, conduisant à une erreur. Ainsi, nous allons la neutraliser.

• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Sélectionner le premier chiffre d'affaires en cliquant sur sa cellule C5,
• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Cliquer sur la cellule du nombre de mois choisi, soit la cellule F5,

Ainsi, nous pointons directement sur le premier chiffre situé juste après la période définie. Il s'agit du nouveau point de départ de la plage à sommer, dont nous devons encore définir la hauteur variable.

• Taper un point-virgule suivi du chiffre zéro : ;0, pour n'opérer aucun décalage en colonne,
• Taper un point-virgule (;) pour passer dans l'argument de la hauteur à calculer,
• Taper le nombre 12 suivi du symbole moins : 12-, pour annoncer la soustraction à opérer,
• Désigner de nouveau le choix du nombre de mois en cliquant sur sa cellule F5,

Ainsi, nous allons bien opérer sur les chiffres restants.

• Fermer la parenthèse de la fonction Decaler,
• Fermer la parenthèse de la fonction Somme,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour garder la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider la formule à l'aide de la touche Entrée du clavier,

Nous obtenons bien le résultat de la somme des chiffres d'affaires pour la période restante. D'ailleurs si vous sommez les résultats de ces deux calculs, vous constatez que le total rejoint la somme fournie en bas du tableau de données. Si vous modifiez la période, les deux calculs s'ajustent parfaitement et dynamiquement.



La formule que nous avons bâtie est la suivante :

=SIERREUR(SOMME(DECALER(C5; F5; 0; 12-F5)); '')

Surligner la période du calcul
Pour parfaire la solution et corroborer visuellement les résultats calculés, nous proposons de surligner dynamiquement les chiffres d'affaires sur la période choisie. Nous devons donc bâtir une règle de mise en forme conditionnelle. Sa construction est très simple. Elle doit comparer la ligne de chaque cellule avec l'indication numérique délivrée par la liste déroulante.

• Sélectionner tous les chiffres du tableau, soit la plage de cellules C5:C16,
• 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 de saisie du dessous, bâtir la syntaxe suivante : =LIGNE(C5)-4<=$F$5,

Nous entamons l'analyse chronologique à partir du premier chiffre d'affaires (C5) pour qu'ils soient tous passés en revue. Grâce à la fonction Ligne, nous prélevons sa position. Nous retranchons quatre unités à cette position (-4) pour annuler les quatre cellules vides au-dessus du tableau. Nous comparons cet emplacement avec la valeur désignée par la liste déroulante ($F$5), pour la période à sommer. Tant que la position retranchée de la cellule est inférieure ou égale à ce nombre, nous en concluons que le chiffre fait partie de l'intervalle à surligner.

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

Nous sommes de retour sur la première boîte de dialogue. Le résumé offert est sans équivoque. Toute cellule appartenant à la période définie doit être surlignée.

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

De retour sur la feuille, la période est effectivement mise en valeur automatiquement. Et grâce à cette règle de mise en forme conditionnelle, elle s'adapte dynamiquement au gré des choix réalisés dans la liste déroulante.