Syntaxe
La fonction DECALER s’écrit de la façon suivante :
= DECALER( réf ; lignes ; colonnes ; [hauteur] ; [largeur] )Cette fonction possède jusqu’à 5 arguments :
réf– Référence d’une cellule. Ce sera le point de départ pour retrouver/construire la référence finale.lignes– Nombre de lignes à partir desquelles il faut se décaler vers le bas. Si le nombre est négatif, on se décale vers le haut.colonnes– Nombre de colonnes à partir desquelles il faut se décaler vers la droite. Si le nombre est négatif, on se décale vers la gauche.[hauteur]– Nombre de lignes à intégrer dans le résultat. Si le nombre est positif, il s’agit des cellules du dessous. Sinon, il s’agit des cellules au-dessus.[largeur]– Nombre de colonnes à intégrer dans le résultat. Si le nombre est positif, il s’agit des cellules à droite de la référence finale. Si le nombre est négatif, il s’agit des cellules à gauche.
La fonction DECALER permet de construire une référence finale à une cellule ou à une plage de cellules à partir d’une référence initiale.
Elle sera intéressante pour retrouver une valeur dans un tableau des scores ou pour construire un reporting. Pour ma part, je m’en sers régulièrement pour obtenir des tableaux de bord dynamiques et notamment les fameux tableaux sur 12 mois glissants. Ça signifie que l’on veut la somme des 12 derniers mois. Plutôt que de refaire le tableau Excel chaque mois, cette formule permet de créer un tableau dynamique.
Comment utiliser la fonction DECALER sur Excel ?
Exemple 1 – Définition du point de référence et premiers décalages
Pour vous présenter la fonction DECALER, nous allons nous servir du tableau ci-dessous.
Notre fonction prend en premier argument une référence initiale de cellule ou d’une plage de cellules. C’est à partir de cette cellule (ou plage) que nous allons initier nos changements. Nous prenons pour cellule initiale la cellule D6.
= DECALER( D6 ; ...Le second argument indiquera de combien de lignes nous décalons notre référence. Si le nombre est positif, nous nous déplaçons vers le bas. S’il est positif ce sera vers le haut.
Le troisième argument indiquera de combien de cellules nous nous déplaçons sur la gauche ou la droite. S’il est négatif, la référence se décalera vers la gauche. S’il est positif, il se décalera vers la droite.
= DECALER( D6 ; 1 ; 0 )
--> 1 cellule vers le bas
On obtient 62
= DECALER( D6 ; -1 ; 0 )
--> 1 cellule vers le haut
On obtient 42
= DECALER( D6 ; 0 ; 1 )
--> 1 cellule vers la droite
On obtient 53
= DECALER( D6 ; 0 ; -1 )
--> 1 cellule vers la gauche
On obtient 51
= DECALER( D6 ; 3 ; -2 )
--> 3 cellules vers le bas
+ 2 cellules vers la gauche
On obtient 80Exemple 2 – Générer une plage de cellules
En précisant les deux derniers arguments, il est possible de générer une référence à une plage de cellules et non plus à une cellule. Le nombre doit être supérieur à 1 (ou inférieur à -1). Comprenez alors qu’Excel vous demande le nombre à intégrer référence initiale comprise.
Le 4ème argument permet de préciser le nombre de lignes à intégrer dans la référence. Si le nombre est négatif, la plage va s’étendre vers le haut. Si le nombre est positif, la plage s’étendra vers le bas.
Le 5ème argument réalise la même opération de façon horizontale. Avec un nombre positif, la plage s’étendra vers la droite. Avec un nombre négatif, la plage s’étendra vers la gauche.
= DECALER( D6 ; 0 ; 0 ; 2 ; 1 )
--> On obtient la référence D6:D7
= DECALER( D6 ; 0 ; 0 ; 1 ; 2 )
--> On obtient la référence D6:E6
= DECALER( D6 ; 0 ; 0 ; -2 ; 1 )
--> On obtient la référence D5:D6
= DECALER( D6 ; 0 ; 0 ; 1 ; -2 )
--> On obtient la référence C6:D6
= DECALER( D6 ; -1 ; -2 ; 3 ; 2 )
--> On obtient la référence B5:D7
Exemple 3 – Travailler à partir d’une plage de cellules
Il est possible de donner une plage de cellules en tant que référence initiale. Si vous saisissez tous les arguments y compris la hauteur de lignes et de colonnes, seule la première cellule de votre plage de cellules sera utilisée.
En général, lorsqu’on saisit une plage de cellules en tant que référence initiale, on se sert que des 2ème et 3ème arguments.
= DECALER( D6:D7 ; -1 ; -2 )
--> On obtient la référence B5:B6Quelques utilisations de la fonction DECALER
Récupérer une information dans un tableau
En général, la fonction DECALER sera intéressante à utiliser avec la fonction EQUIV. Cette dernière permet de retrouver la position d’une valeur dans une plage de cellules. Elle sera utile pour récupérer le nombre de lignes ou de colonnes pour effectuer les décalages.
Dans l’exemple ci-dessous, nous cherchons à récupérer les ventes effectuées à Lille en 2021. La solution DECALER et deux fonctions EQUIV nous permettent de retrouver le résultat.
= DECALER(
B2 ;
EQUIV( B10 ; B3:B7 ; 0 ) ;
EQUIV( B11 ; C2:F2 ; 0 )
)
Créer un tableau dynamique
Pour toutes celles et ceux qui doivent réaliser les tableaux en mois glissants, l’utilisation de la fonction DECALER se révèle indispensable pour générer un tableau réutilisable chaque mois.
Dans l’exemple ci-dessous, nous réutilisons la fonction EQUIV avec la fonction DECALER pour identifier de combien de colonnes nous devons nous décaler. Une fois fait, nous précisons que nous voulons une plage de cellules en précisant notamment le nombre de colonnes à intégrer.
Nous pouvons inclure le tout dans une fonction SOMME pour obtenir le montant total sur 12 mois glissants.
= SOMME(
DECALER(
C3 ;
0 ;
EQUIV($C$10;$C$2:$T$2;0) ;
1 ;
-13 )
)
