La fonction DECALER est l’une des plus puissantes d’Excel pour créer des références dynamiques. Elle permet de pointer vers une cellule ou une plage en fonction d’un décalage relatif, sans jamais figer d’adresse en dur. Résultat : des formules qui s’adaptent automatiquement quand les données changent, des listes déroulantes dynamiques, des graphiques qui se mettent à jour seuls.
Qu’est-ce que la fonction DECALER ?
La fonction DECALER ne déplace pas de données. Elle renvoie une référence à une cellule ou une plage de cellules, calculée à partir d’un point de départ et d’un nombre de lignes et de colonnes à parcourir. Les données restent en place, c’est la référence qui est calculée dynamiquement.
Elle est dite volatile, ce qui signifie qu’elle se recalcule à chaque modification du classeur. Un point à garder en tête sur les très grands fichiers.
Syntaxe et arguments
La syntaxe complète est : =DECALER(Réf ; Lignes ; Colonnes ; [Hauteur] ; [Largeur])
- Réf : la cellule ou plage de départ, point d’ancrage du décalage.
- Lignes : nombre de lignes à parcourir depuis la référence. Positif = descend, négatif = remonte.
- Colonnes : nombre de colonnes à parcourir. Positif = droite, négatif = gauche.
- [Hauteur] (facultatif) : nombre de lignes que doit couvrir la plage renvoyée.
- [Largeur] (facultatif) : nombre de colonnes de la plage renvoyée.
Quand seuls les 3 premiers arguments sont renseignés, DECALER renvoie la valeur d’une cellule unique. Avec les arguments Hauteur et Largeur, elle renvoie une plage entière, idéale combinée avec SOMME, NB ou MOYENNE.
Exemples pratiques
Décalage simple : =DECALER(A1;2;2) part de A1, descend de 2 lignes et se décale de 2 colonnes vers la droite. Elle renvoie la valeur de C3.
Décalage négatif : =DECALER(B5;-2;1) part de B5, remonte de 2 lignes et va d’une colonne vers la droite. Elle renvoie la valeur de C3.
Somme d’une plage dynamique : =SOMME(DECALER(C2;1;2;3;1)) additionne les valeurs d’une plage de 3 lignes sur 1 colonne, qui commence 1 ligne sous C2 et 2 colonnes à droite.
Valeur pilotée par une cellule : =DECALER(B1;E3;0) récupère la valeur de la colonne B à la ligne indiquée par E3. Changer E3 met à jour le résultat automatiquement.
Cas d’usage avancés
Tableaux de bord glissants. Pour afficher toujours les 12 derniers mois sans modifier les formules chaque mois, DECALER permet de définir une plage glissante : =SOMME(DECALER(B2;0;0;12;1)).
Combinaison avec EQUIV. DECALER et EQUIV forment un duo redoutable pour les recherches dans des tableaux à deux entrées. EQUIV localise la position d’une valeur, et DECALER utilise ce résultat pour pointer vers la bonne cellule.
Listes déroulantes dynamiques. En nommant une plage définie par DECALER dans le gestionnaire de noms, on obtient une liste déroulante qui s’allonge automatiquement selon le contenu réel : =DECALER(Feuil1!$A$1;0;0;NBVAL(Feuil1!$A:$A);1).
Erreurs courantes et comment les éviter
- Erreur #REF! : le décalage sort des limites de la feuille. Vérifiez que les valeurs de Lignes et Colonnes ne poussent pas la référence hors du périmètre.
- Plage incorrecte avec Réf multi-cellules : quand Réf couvre plusieurs cellules, le décalage s’applique au coin supérieur gauche uniquement.
- Performance sur grands classeurs : DECALER se recalcule à chaque modification. Sur les fichiers volumineux, INDEX peut la remplacer avec de meilleures performances.
Questions fréquentes
DECALER utilise un positionnement relatif depuis un point de départ, tandis qu’INDEX utilise un positionnement absolu dans une plage définie. DECALER est plus volatile et peut ralentir les classeurs lourds.
Oui. Un nombre négatif pour Lignes remonte au-dessus de la référence, et un nombre négatif pour Colonnes se décale vers la gauche. La cellule résultante doit rester dans les limites de la feuille, sinon Excel renvoie #REF!.
L’erreur #REF! apparait quand le décalage calculé pointe en dehors de la feuille. Vérifiez les valeurs des arguments Lignes et Colonnes.
Combinez DECALER avec NBVAL : =DECALER(A1;0;0;NBVAL(A:A);1) renvoie une plage qui commence en A1 et couvre exactement autant de lignes que la colonne A en contient.
Oui. DECALER est disponible dans toutes les versions modernes d’Excel ainsi que dans Google Sheets (sous le nom OFFSET).
