Tutoriel

Calcul avec les X dernières lignes

J'ai parfois besoin de faire la somme (ou autre calcul) avec les x dernières lignes d'un tableau Microsoft Excel.

Un bon exemple pourrait être le calcul en mois glissants, où je souhaite toujours calculer avec les 3 derniers mois.

Pour cela, j'ai besoin de la fonction « DECALER () ».

Soyez décalé

La fonction « DECALER () » est intéressante, en ce sens qu'elle permet de faire référence à des plages variables par rapport à une cellule de référence.

Arguments de la fonction « DECALER () »

Cellule de départ

Indique l'adresse de référence pour débuter le décalage.

Mon tableau commence en cellule [C9]. C’est donc elle que je prendrais comme référence.

Nombre de lignes de décalage

À partir de cette cellule de départ, je donne le décalage en nombre de lignes.

La première ligne, celle de la cellule de départ, possède la valeur zéro (0), c ‘est donc ma ligne de titre qui possède cet indice.

Ainsi pour lire la première ligne de données, j’indique la valeur 1, pour atteindre la deuxième ligne, la valeur 2.

Le décalage peut-être négatif à condition bien sûr de ne pas sortir des coordonnées de la feuille.

Nombre de colonnes de décalage

Toujours depuis ma cellule de référence, je fourni le nombre de colonnes vers laquelle me déplacer.

Comme pour le numéro de ligne, les indices de colonnes commencent à 0.

Si je veux la seconde colonne du tableau, je donne la valeur 1.

Comme pour les lignes, le décalage du nombre de colonnes peut-être négatif.

Hauteur de lecture

Pour cet argument, la fonction attend le nombre de lignes à lire.

Pour récupérer une seule valeur, la hauteur est égale à 1.

Largeur à lire

Il reste à donner combien de colonnes nous souhaitons lire.

Comme pour la hauteur, pour récupérer une seule colonne, on fixe cette valeur à 1.

Cellule à lire

Pour lire une seule cellule, il suffit de donner une hauteur de ligne et une largeur de colonne égale à 1.

Nous récupérons la valeur de la cellule décalée du nombre de lignes et de colonnes.

Utilisation basique

Dans le tableau suivant, je souhaite lire la cellule décalée de 6 lignes et 4 colonnes depuis ma cellule de référence [C9] (celle qui contient la mention « Client »), soit la valeur 232,00 €.

Tableau de données exemple Microsoft Excel
Figure 1 : Tableau source.

Notre formule donne :

=DECALER($C$9; 5; 3; 1; 1)

Pour mémoire, les indices de lignes et colonnes commencent à 0.

La valeur 5 représente le décalage en nombre de lignes, soit la 6° ligne du tableau.

La valeur 3 c'est le décalage en nombre de colonnes, soit la 4° colonne du tableau.

Enfin, je souhaite lire la valeur d'une seule cellule, donc hauteur et largeur prennent la valeur 1.

En soit cet exemple offre peu d’intérêt, mais je pourrais construire un tableau qui ne contiendrait qu’une partie des informations.

À quoi ça sert ?

Imaginons un tableau mensuel.

Tableau exemple mensuel Microsoft Excel
Figure 2 : Tableau mensuel complet.

Objectif

Je souhaite n’afficher que 3 mois à partir d’un mois de référence, par exemple à partir du mois 4 (avril).

Tableau exemple mensuel Microsoft Excel
Figure 3 : Tableau résultat avec décalage.

Construction du tableau

J’utilise ma fonction « DECALER () ».

Tableau construit à l'aide de la fonction Microsoft Excel decaler()
Figure 4 : Formule utilisée pour le tableau réduit.

La cellule [Q17] contient le numéro de mois de départ, soit le mois d’avril dans mon exemple.

La fonction est identique dans toutes les cellules du tableau à l’exception, du décalage en nombre de lignes et de colonnes.

Usage

Il est maintenant possible de créer un graphique ou obtenir toutes autres informations nécessaires avec comme source notre tableau dynamique.

Pour faire évoluer notre tableau, il suffit dans la cellule [Q17] de changer le numéro de mois de départ à lire, ainsi avec le mois 9, je lis les informations de septembre, octobre et novembre.
Bien sûr, puisque le tableau est dynamique, notre graphique l’est tout autant.

C’est une base nécessaire à l’élaboration d’un tableau de bord.

Calculer avec des plages dynamiques

Il suffit de rajouter sa fonction de calcul par exemple une somme, et donner la plage décalée en paramètres.

=SOMME(DECALER($C$9; 5; 3; 1; 1))

Comme nous n’avons qu’une cellule concernée, la somme donne sa valeur. Nous retrouvons comme précédemment la valeur 232,00 € de notre tableau source, ce qui est parfaitement inutile.

Utiliser hauteur et largeur

En modifiant la hauteur et/ou la largeur, nous pouvons faire la somme d’un bloc du tableau.

Tableau Microsoft Excel exemple mensuel avec bloc à calculer
Figure 5 : Tableau dont on souhaite calculer un bloc.

Je souhaite faire la somme des mois de mai, juin, juillet pour les agences ouest et sud.

Je vais faire un décalage de 4 lignes soit la valeur 3. J’arrive sur la cellule de l’agence ouest.

Décalage du nombre de lignes
Figure 6 : Décalage en lignes.

Je me déplace de 5 colonnes, soit le mois de mai.

Décalage du nombre de colonne
Figure 7 : Décalage en colonnes.

Le curseur est donc positionné sur la valeur du mois de mai pour l’agence ouest.

Définir le bloc

Lignes à calculer

À partir de cette cellule, je veux la somme de 2 lignes, c’est cette valeur que je donne comme hauteur.

La hauteur part du curseur vers le bas.

La ligne de la cellule décalée est la première (agence ouest).

La seconde ligne à sommer est la suivante soit l’agence sud.

La formule pour avoir la somme du mois de mai pour les agences ouest et sud :

=SOMME(DECALER($C$9; 3; 5; 2; 1))

Colonnes à calculer

La largeur fonctionne sur le même principe. Nous partons de la colonne 5 (mai) et nous prenons deux mois supplémentaires (juin et juillet), nous voulons 3 mois et c’est cette valeur que nous fournissons.

La formule devient :

=SOMME(DECALER($C$9; 3; 5; 2; 3))

Je retrouve bien la somme des mois de mai, juin, juillet pour les agences ouest et sud.

Précision

Comme pour les décalages, les hauteurs et largeurs peuvent être négatives, ce qui nous sera utile pour la suite.

Lire la dernière cellule d’un tableau

Pour lire cette cellule, il me suffit de compter combien de valeurs contient la colonne de mon tableau.

Fonction « NBVAL () »

La fonction « NBVAL () » compte combien de cellules sont renseignées dans une plage peu importe si la plage contient du texte ou des nombres.

Avec la formule suivante, nous avons pour résultat 10 lignes.

=NBVAL(C9:C18)

Fonction « NB () »

Elle fait la même chose que la fonction « NBVAL () », à la différence qu’elle ne compte que les valeurs numériques de la plage.

Fonction « NB.VIDE () »

Si la plage contient des cellules vides, elles ne sont pas prises en compte ni par la fonction « NBVAL() », ni par « NB() ».
Par contre, la valeur zéro est bien comptée par les deux fonctions.

Si la plage peut contenir des cellules vides, pour avoir le nombre total de lignes, il nous faut additionner « NBVAL() » ou « NB() » avec « NB.VIDE() ».

=NBVAL(C9:C18) + NB.VIDE(C9:C18)

Se décaler à la fin du tableau

Si je donne comme paramètre au décalage en nombre de lignes le résultat de disons la fonction NBVAL(), je serais toujours en fin de tableau.

=DECALER($C$9; NBVAL($C$9:$C$18) -1; 1; 1; 1)

La valeur -1 qui suit la fonction « NBVAL() » est nécessaire pour éliminer la ligne de titre.

Ce qui donne avec le tableau suivant, la valeur 3 car je décale d’une colonne.

Tableau avec décalage sur la dernière cellule
Figure 8 : Atteindre la dernière cellule.

Chaque fois que j’insère une ligne dans mon tableau, la fonction recalcule et m’affiche toujours la valeur de la dernière cellule de ma plage.

Calculer avec une hauteur négative

Si la hauteur est positive, le bloc s’étend donc vers le bas et si la largeur l’est aussi, il s’étend vers la droite.

Mais si la hauteur est négative, le bloc s’étend vers le haut et pour la largeur, il s’étend vers la gauche.

Le principe est de se déplacer comme précédemment sur la dernière ligne, et de faire la somme des cellules qui sont au-dessus en donnant une hauteur négative.

Dans le tableau suivant, je veux la somme des 3 dernières cellules. Pour ce faire, je donne la valeur -3 à l’argument hauteur.

Je me déplace donc à la fin du tableau et je remonte de 3 lignes tout en décalant d’une colonne vers la droite.

Plage décalée à calculer
Figure 9 : Utiliser les x dernières cellules.

Ma formule sera :

=SOMME(DECALER($C$9; NBVAL($C$9:$C$18) - 1; 1; -3; 1))

Quand je rajoute des lignes dans le tableau, ma somme est recalculée avec toujours les 3 dernières valeurs du tableau.

Règles pour hauteur et largeur

Hauteur : Positive

Largeur : Positive

Plage décalée hauteur et largeur positives
Figure 10 : Hauteur et largeur positives.

Hauteur : Positive

Largeur : Négative

Plage décalée hauteur positive et largeur négative
Figure 11 : Hauteur positive et largeur négative.

Hauteur : Négative

Largeur : Positive

Plage décalée hauteur négative et largeur positive
Figure 12 : Hauteur négative et largeur positive.

Hauteur : Négative

Largeur : Négative

 hauteur et largeur négatives
Figure 12 : Hauteur et largeur négatives.

Conclusion

La fonction « DECALER() », n’est pas la plus simple à comprendre, mais elle offre de nombreuses possibilités.

Télécharger

Article au format pdf.

Fichier exemple Microsoft Excel 2010.

>