Imaginez que nous ayons des chiffres de ventes pour une entreprise :
Et que l'on souhaite retrouver les chiffres du Total pour l'année à ce jour. Nous pouvons ajouter une liste déroulante comme ceci :
Pour que nous puissions spécifier le mois en cours. Par conséquent, nous voulons maintenant calculer l'année à ce jour pour mars. Le format le plus simple serait d'avoir des formules qui s'étendent sur toute la gamme :
Et puis nous changerions simplement les formules tous les mois.
Cependant Excel permet une autre approche. Nous pourrions configurer une plage dynamique dont la taille varie selon le mois dans lequel nous nous trouvons. Lorsque nous changeons le mois dans la liste déroulante, la taille de la plage change.
Ainsi, pour le mois de mars, la plage est longue de 3 colonnes, et pour le mois de juin, elle durerait 6 mois.
La taille de la plage est régie par le mois. Une façon de formuler cela est d'utiliser la fonction Mois :
=Mois(c8)
Où c8 est l'adresse de la cellule de notre liste déroulante. Cependant la méthode qui est préférée est d'utiliser la fonction MATCH pour déterminer la position des mois en cours dans tous les mois de notre rapport :
MATCH(c8,$c$3:$j$3,0)
Où:
• c8 est l'adresse de la cellule du mois en cours
• C3:J3 est l'adresse de tous nos mois
• 0 est d'assurer une correspondance exacte
Nous pouvons maintenant spécifier la taille de notre plage dynamique par la fonction OFFSET qui a 5 arguments :
=OFFSET(référence, lignes, cols, hauteur, largeur)
Où:
• La référence est le coin supérieur gauche de notre plage dynamique - cellule C5 - la première cellule que nous voulons additionner
• Lignes - le nombre de lignes en dessous de notre cellule de base - c'est 0
• Cols - le nombre de cols en face de notre appel de base - c'est 0
• La largeur de notre plage dynamique - qui est de 3 dans ce cas. Cependant comme nous souhaitons que la gamme varie selon les mois nous mettrons ici nos formules MATCH
• C'est la hauteur de notre plage dynamique qui est de 1
Donc nos formules OFFSET sont :
= DÉCALAGE(c5,0,0,MATCH(c8,$c$3:$j$3,0),1)
Enfin, nous devons dire à Excel de SUM pour donner les formules complètes telles que :
= SOMME(OFFSET(c5,0,0,MATCH(c8,$c$3:$j$3,0),1))
Nous avons:
Maintenant, si nous changeons le mois dans la liste déroulante, le chiffre correct de l'année à ce jour passe par :
Comme il s'agit d'une mise à jour automatique, cette approche présente les avantages suivants :
• Il n'est pas nécessaire de changer les formules chaque mois
• Comme il y a moins de changements de formules, moins de risques d'erreur
• La feuille de calcul peut être utilisée par quelqu'un qui a une connaissance limitée d'Excel - ils peuvent simplement modifier la liste déroulante et ne pas être dérangé par les formules
