Fonction DECALER dans Excel : plages dynamiques et références flexibles

DECALER est l'une des fonctions les plus puissantes et les plus méconnues d'Excel. Elle renvoie une référence à une cellule ou une plage décalée d'un nombre donné de lignes et de colonnes par rapport à un point de départ. Son vrai pouvoir : créer des plages qui s'adaptent dynamiquement selon la quantité de données.

Syntaxe complète

=DECALER(réf; lignes; colonnes; [hauteur]; [largeur])
  • réf : cellule de départ (point d'ancrage)
  • lignes : décalage vers le bas (positif) ou vers le haut (négatif)
  • colonnes : décalage vers la droite (positif) ou vers la gauche (négatif)
  • hauteur (optionnel) : nombre de lignes de la plage renvoyée
  • largeur (optionnel) : nombre de colonnes de la plage renvoyée

Exemples de base

=DECALER(A1; 3; 2)       → valeur de C4 (3 lignes sous A1, 2 colonnes à droite)
=DECALER(A1; 0; 0; 5; 1) → plage A1:A5 (même point, hauteur 5, largeur 1)
=DECALER(B5; -2; 1)      → valeur de C3 (2 lignes au-dessus de B5, 1 colonne à droite)
=SOMME(DECALER(A1;0;0;10;1)) → somme de A1:A10

Cas 1 — Extraire les N dernières valeurs d'une liste

Supposons que vos ventes mensuelles sont en A2:A100 et que vous ajoutez des données chaque mois. Pour toujours sommer les 6 derniers mois :

=SOMME(DECALER(A1; NBVAL(A:A)-6; 0; 6; 1))

NBVAL(A:A) compte les cellules remplies dans la colonne A. DECALER se positionne 6 lignes avant la dernière valeur, puis prend une plage de 6 lignes. Résultat : toujours les 6 derniers mois, quel que soit le nombre de lignes.

Cas 2 — Plage dynamique pour graphique ou liste déroulante

Technique classique avant les tableaux structurés : créer une plage nommée qui s'étend automatiquement avec les nouvelles données.

  1. Formules → Gestionnaire de noms → Nouveau
  2. Nom : DonnéesDynamiques
  3. Fait référence à :
    =DECALER(Feuil1!$A$2; 0; 0; NBVAL(Feuil1!$A:$A)-1; 1)

Cette plage commence en A2 et s'étend automatiquement jusqu'à la dernière valeur de la colonne A (en soustrayant 1 pour l'en-tête). Utilisez DonnéesDynamiques comme source de graphique ou de liste déroulante — elle se met à jour toute seule.

Cas 3 — RECHERCHEV avec numéro de colonne dynamique

RECHERCHEV accepte une valeur fixe pour l'index de colonne. Avec DECALER + EQUIV, on peut le rendre dynamique :

=DECALER(A1; EQUIV(D1;A:A;0)-1; EQUIV(E1;1:1;0)-1)

EQUIV(D1;A:A;0) trouve la ligne de la valeur cherchée. EQUIV(E1;1:1;0) trouve la colonne souhaitée. DECALER combine les deux pour renvoyer la valeur à l'intersection — sans INDEX/EQUIV imbriqués.

Cas 4 — Moyenne glissante

=MOYENNE(DECALER(B2; LIGNE()-LIGNE($B$2); 0; -3; 1))

Copiée sur chaque ligne, cette formule calcule la moyenne des 3 dernières valeurs en remontant depuis la ligne courante. La hauteur négative (-3) indique une plage qui "monte" vers le haut.

DECALER est volatile — attention aux performances

Comme INDIRECT, DECALER se recalcule à chaque modification du classeur, même sans lien avec ses arguments. Sur des tableaux de plusieurs milliers de lignes avec des dizaines de DECALER, les temps de recalcul peuvent devenir perceptibles.

Alternatives modernes (Excel 365) : les tableaux structurés s'étendent automatiquement — plus besoin de DECALER pour les graphiques. PREN et PRENDRE.LIGNES remplacent l'extraction des N dernières valeurs. DECALER reste utile en versions antérieures.

Déboguer un DECALER avec #REF!

#REF! apparaît quand le décalage sort de la feuille (ex : DECALER(A1;-5;0) depuis la ligne 1) ou quand la hauteur/largeur est nulle ou négative au-delà des limites. Vérifiez que les paramètres restent dans les bornes de la feuille (1 à 1 048 576 lignes, 1 à 16 384 colonnes).

Fonction INDIRECT → Nommer des plages INDEX / EQUIV