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 €.
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.
Objectif
Je souhaite n’afficher que 3 mois à partir d’un mois de référence, par exemple à partir du mois 4 (avril).
Construction du tableau
J’utilise ma fonction « DECALER () ».
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.
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.
Je me déplace de 5 colonnes, soit le mois de mai.
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.
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.
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
Hauteur : Positive
Largeur : Négative
Hauteur : Négative
Largeur : Positive
Hauteur : Négative
Largeur : Négative
Conclusion
La fonction « DECALER() », n’est pas la plus simple à comprendre, mais elle offre de nombreuses possibilités.
Télécharger
Fichier exemple Microsoft Excel 2010.
Achetez mon ouvrage !
Mon PDF « Créer un planning perpétuel sur Microsoft Excel sans macro » est disponible à la vente.
Pour plus d’informations, rendez-vous sur la page dédiée.