Qu'est-ce que la fonction DECALER ?
DECALER te sert quand une plage fixe comme A1:A10 ne suffit plus, parce que tes données s'ajoutent en bas ou que la fenêtre que tu veux regarder change de position d'un calcul à l'autre. Plutôt que de retoucher la référence à la main à chaque fois, tu la laisses se recalculer toute seule.
Concrètement, c'est elle qui permet de créer un graphique des ventes qui se met à jour automatiquement chaque semaine sans que tu aies à retoucher sa source, d'afficher toujours les 3 derniers mois de chiffre d'affaires dans un tableau de bord, de calculer une moyenne mobile qui glisse avec les nouvelles données, ou de construire un rapport interactif où l'utilisateur choisit lui-même le nombre de périodes à analyser.
Syntaxe
Clique sur un argument pour aller à son explication.
DECALER est une fonction volatile : elle recalcule à chaque modification de la feuille, même si ses cellules d'entrée n'ont pas changé. Sur de gros fichiers avec de nombreuses formules DECALER, cela peut ralentir les calculs. Si tu n'as pas besoin d'une plage qui change de taille, INDEX/EQUIV est plus performant.
Comprendre chaque paramètre de la fonction DECALER
réf
: la cellule ou plage de référence de départ, le point d'ancrage à partir duquel DECALER va calculer le décalageÇa peut être une cellule unique comme A1 ou une plage comme A1:B5. Si tu utilises une plage, DECALER prend la cellule en haut à gauche comme point de départ.
Utilise une référence absolue comme $A$1 si tu prévois de copier la formule : cela évite que ton point d'ancrage se déplace avec la copie.
lignes
: le nombre de lignes à décaler par rapport à la référenceUn nombre positif déplace vers le bas, un nombre négatif vers le haut. 2 signifie « descends de 2 lignes », -1 signifie « monte d'une ligne ». Utilise 0 si tu ne veux pas de décalage vertical.
Ce paramètre peut être le résultat d'un calcul. Par exemple, NBVAL(B:B)-3 positionne la plage 3 lignes avant la dernière valeur de la colonne B.
colonnes
: le nombre de colonnes à décalerUn nombre positif déplace vers la droite, un nombre négatif vers la gauche. 3 signifie « va 3 colonnes à droite », -2 signifie « recule de 2 colonnes ». Utilise 0 pour rester dans la même colonne.
[hauteur]
: le nombre de lignes que doit couvrir la plage résultante(facultatif)Si tu omets ce paramètre, DECALER conserve la hauteur de ta référence de départ.
C'est ici que la magie opère : en utilisant NBVAL(A:A) ou NBVAL(A:A)-1 (pour sauter l'en-tête), tu crées une plage qui s'étend automatiquement jusqu'à la dernière valeur saisie.
[largeur]
: le nombre de colonnes que doit couvrir la plage résultante(facultatif)Si tu l'omets, DECALER conserve la largeur de ta référence. Combine ce paramètre avec hauteur pour redimensionner complètement la plage de façon dynamique.
Très utile pour sélectionner automatiquement un bloc de données dont le nombre de colonnes varie selon un paramètre.
Exemples pratiques pas à pas
Comment additionner une fenêtre de 3 mois avec les fonctions DECALER et SOMME
Ton fichier de ventes s'allonge mois après mois, et on te demande le total du deuxième trimestre sans que tu ailles compter les lignes à la main. Les huit mois de l'année sont saisis les uns sous les autres, et tu veux additionner la seule tranche d'avril à juin. La fonction DECALER part de la première cellule de ventes, descend jusqu'à la bonne ligne et découpe une plage de la hauteur voulue, que la fonction SOMME additionne dans la foulée.
- 1Dans une cellule, écris
=SOMME(. - 2En 1er argument : écris la fenêtre de mois à additionner et referme-la,
DECALER(B2; 3; 0; 3; 1). L'ancre est posée sur la première valeur de ventes et non sur l'en-tête, sans quoi tout le décalage serait faussé d'une ligne. Le3puis le0déplacent le départ de trois lignes vers le bas sans changer de colonne, ce qui amène sur la ligne d'Avril. Les deux derniers chiffres donnent à la fenêtre trois lignes de haut sur une colonne de large. - 3Ferme la parenthèse de la fonction SOMME et appuie sur Entrée. Celle de la fonction DECALER a déjà été refermée à l'étape précédente. La fonction DECALER ne renvoie pas un chiffre mais une plage, et c'est la fonction SOMME qui en tire le total.
=SOMME(DECALER(B2; 3; 0; 3; 1))B2 (Janvier), descend de 3 lignes pour atterrir sur B5 (Avril), puis découpe une plage de 3 lignes sur 1 colonne, soit B5:B7. La fonction SOMME reçoit cette plage et additionne Avril, Mai et Juin : 162 + 155 + 178 = 495.
Comment créer une plage dynamique qui s'étend toute seule avec les fonctions DECALER et NBVAL
Chaque lundi, tu ajoutes une ligne de ventes à ton suivi hebdomadaire, et chaque lundi le total oublie la nouvelle semaine parce que sa plage s'arrête où tu l'avais laissée. Tu veux un total qui absorbe les lignes futures sans que tu ailles rallonger sa plage à la main. La fonction DECALER calcule la hauteur de sa plage au moment du calcul, si bien que celle-ci grandit d'elle-même à chaque semaine saisie.
- 1Dans une cellule, écris
=SOMME(. - 2En 1er argument : écris la plage qui doit grandir toute seule et referme-la,
DECALER($A$1; 1; 1; NBVAL($B:$B)-1; 1). Les deux$figent l'ancre sur le coin haut à gauche du tableau, si bien qu'une copie de la formule continue de partir du même endroit. Le premier1descend d'une ligne sous l'en-tête, le deuxième se décale d'une colonne vers la droite, et le dernier limite la plage à la seule colonne des ventes. Entre les deux,NBVAL($B:$B)-1calcule la hauteur au lieu de la figer, et c'est ce qui fera entrer les semaines à venir dans le total. - 3Ferme la parenthèse de la fonction SOMME et appuie sur Entrée. Celles des fonctions DECALER et NBVAL ont déjà été refermées à l'étape précédente.
=SOMME(DECALER($A$1; 1; 1; NBVAL($B:$B)-1; 1))-1 écarte l'en-tête, si bien que la hauteur vaut 5 : la plage couvre B2:B6 et la fonction SOMME renvoie 261. Le jour où tu saisis la semaine S6, NBVAL compte 7, la hauteur passe à 6 et le total avale la nouvelle ligne sans que tu touches à la formule.
Comment calculer une moyenne mobile avec les fonctions DECALER et MOYENNE
Le trafic de ton site monte et descend d'un mois sur l'autre, et personne autour de la table n'arrive à dire si la tendance est bonne ou mauvaise. Tu as les visiteurs mois par mois et tu veux lisser ces à-coups avec une moyenne sur les 3 derniers mois, recalculée à chaque ligne. La fonction DECALER fabrique une fenêtre de 3 lignes qui descend avec la recopie, et la fonction MOYENNE en tire le chiffre lissé.
- 1Dans une cellule, écris
=MOYENNE(. - 2En 1er argument : écris la fenêtre de trois mois à moyenner et referme-la,
DECALER(B$2; LIGNE()-4; 0; 3; 1). Le$placé devant le2fige la ligne de l'ancre, qui reste donc plantée sur Janvier quand la formule descend. Le décalage est confié au calculLIGNE()-4, qui vaut 0 sur la ligne de Mars, la quatrième de la feuille et la première à porter la formule, puis gagne un cran à chaque ligne suivante. Le0qui suit garde la fenêtre dans la colonne des visiteurs, tandis que les deux derniers chiffres lui donnent trois lignes de haut sur une colonne de large. - 3Ferme la parenthèse de la fonction MOYENNE et appuie sur Entrée, puis recopie la formule vers le bas jusqu'à la ligne de Juin. Celle de la fonction DECALER a déjà été refermée à l'étape précédente. Les lignes de Janvier et de Février restent vides à dessein, puisqu'il n'y a pas encore trois mois derrière elles.
=MOYENNE(DECALER(B$2; LIGNE()-4; 0; 3; 1))B2 grâce au $ qui en fige la ligne, et son décalage vient du numéro de la ligne courante : sur la ligne de Mars il vaut 0, la fenêtre couvre donc B2:B4 et la fonction MOYENNE renvoie 12 500. Recopiée vers le bas, la même formule fait glisser cette fenêtre d'un cran par ligne et lisse la tendance mois après mois.
Comment éviter l'erreur #REF! de la fonction DECALER sur une colonne vide
Le trimestre démarre, ton fichier de suivi est prêt, mais aucune vente n'est encore saisie et le total te sort une erreur #REF! en plein milieu de la feuille que tu voulais diffuser. Tant que la colonne reste vide, la hauteur calculée de ta plage tombe à zéro et la fonction DECALER refuse de renvoyer quoi que ce soit. En bornant cette hauteur par le bas, tu gardes un total présentable en attendant la première ligne.
- 1Dans une cellule, écris
=SOMME(. - 2En 1er argument : écris la plage dynamique et son garde-fou, puis referme-la,
DECALER($A$1; 1; 1; MAX(NBVAL($B:$B)-1; 1); 1). L'ancre figée en haut à gauche, la descente d'une ligne et le décalage d'une colonne amènent le départ sur la première cellule de ventes, encore vide aujourd'hui. Tout se joue ensuite sur la hauteur : tant que la colonne des ventes ne contient que son en-tête,NBVAL($B:$B)renvoie 1 et le-1ramène le compte à 0, une hauteur qu'aucune plage ne peut avoir. La fonction MAX compare ce compte à 1 et garde le plus grand des deux, ce qui impose une ligne au minimum quoi qu'il arrive. Le dernier1maintient la plage sur une seule colonne, ce qui n'a aucune raison de changer quand le tableau se remplira. - 3Ferme la parenthèse de la fonction SOMME et appuie sur Entrée. Celles des fonctions DECALER, MAX et NBVAL ont déjà été refermées à l'étape précédente.
=SOMME(DECALER($A$1; 1; 1; MAX(NBVAL($B:$B)-1; 1); 1))B2, encore vide, et le total affiche un 0 propre jusqu'à la première vente.
Les erreurs fréquentes avec la fonction DECALER
#REF! : référence qui sort des limites de la feuille
Le décalage ou les dimensions calculées pointent en dehors des limites de la feuille. Cela arrive souvent quand NB.VAL renvoie une valeur trop grande ou quand le décalage est négatif depuis la ligne 1.
#VALEUR! : arguments non numériques
Les paramètres lignes, colonnes, hauteur et largeur doivent être des nombres. Si une fonction de comptage comme NB.VAL retourne du texte, ou si une référence de cellule contient du texte, DECALER ne peut pas calculer.
Solution : Vérifie que tes fonctions de comptage retournent bien des entiers. Utilise ESTNUM() pour tester une valeur si tu as un doute, ou enveloppe avec CNUM() pour forcer la conversion.
Hauteur ou largeur nulle ou négative
Les paramètres hauteur et largeur doivent être des entiers positifs. Si NB.VAL renvoie 0 (colonne vide) ou 1 (seulement l'en-tête), le calcul -1 peut donner 0 ou un nombre négatif.
Solution : Enveloppe avec MAX : =DECALER(A1;0;0;MAX(NBVAL(A:A)-1;1);1). La valeur minimale garantie est 1, ce qui évite l'erreur même quand la colonne ne contient que l'en-tête.
Fichier qui ralentit à chaque frappe
DECALER est volatile : elle recalcule à chaque modification de la feuille, même si ses dépendances directes n'ont pas changé. Beaucoup de formules DECALER dans un fichier volumineux peuvent causer des ralentissements perceptibles.
Solution : Limite DECALER aux cas où tu as vraiment besoin de plages dynamiques. Pour les recherches ponctuelles de valeurs, utilise INDEX/EQUIV qui ne sont pas volatiles. Tu peux aussi passer en calcul manuel (Formules > Options de calcul > Manuel) et déclencher les calculs avec F9 uniquement quand nécessaire.
Tu cherches surtout à corriger l'erreur #REF! affichée dans ta cellule, sans passer par la fonction DECALER ? Consulte la fiche dédiée à l'erreur #REF! pour comprendre toutes ses causes et comment la corriger.
DECALER vs INDEX vs INDIRECT
DECALER est la seule des trois à pouvoir retourner une plage redimensionnable. Mais cette puissance a un coût : la volatilité. INDEX reste le meilleur choix pour extraire des valeurs ponctuelles, INDIRECT pour les références textuelles.
| Critère | DECALER | INDEX | INDIRECT |
|---|---|---|---|
| Type de retour | Référence (valeur ou plage) | Valeur | Référence (valeur ou plage) |
| Plages redimensionnables | ✅ Oui (hauteur, largeur) | ❌ Non | ❌ Non |
| Graphiques dynamiques | ✅ Parfait | ⚠️ Possible avec NBVAL | ✅ Oui |
| Volatile | ⚠️ Oui | ✅ Non | ⚠️ Oui |
| Performance | ⚠️ Ralentit sur gros fichiers | ✅ Très rapide | ⚠️ Ralentit sur gros fichiers |
| Cas d'usage principal | Plages dynamiques, moyennes mobiles | Extraction de valeurs, EQUIV | Dashboards avec sélection de feuille |
Astuces avancées avec DECALER
Nomme tes plages DECALER pour simplifier tes formules
Au lieu d'écrire =SOMME(DECALER($A$1;1;0;NBVAL($A:$A)-1;1)) partout, crée une plage nommée (Ctrl+F3) avec cette formule DECALER et appelle-la VentesDynamiques. Tes autres formules deviennent lisibles : =SOMME(VentesDynamiques), =MOYENNE(VentesDynamiques).
Si la structure de ton tableau change, tu modifies la définition de la plage nommée une seule fois, et toutes les formules qui l'utilisent se mettent à jour.
Combine DECALER avec NB.VAL pour des graphiques auto-extensibles
La combinaison =DECALER($A$1;1;0;NBVAL($B:$B);2) dans une plage nommée est la recette classique pour un graphique qui s'étend automatiquement. NBVAL($B:$B) compte les valeurs numériques dans la colonne B, et DECALER sélectionne exactement ce nombre de lignes.
Chaque fois que tu ajoutes une semaine de données, le graphique intègre la nouvelle ligne sans que tu aies à modifier quoi que ce soit.
Utilise des références absolues pour l'ancre de départ
Quand tu crées des formules DECALER que tu comptes copier vers le bas ou vers la droite, la référence de départ (réf) doit être absolue : $A$1 et non A1. Sinon, l'ancre se déplace avec la copie et DECALER calcule depuis un mauvais point de départ.
Les paramètres lignes et colonnes peuvent eux rester relatifs si leur valeur doit s'adapter à chaque ligne.
Questions fréquentes
DECALER retourne une référence de cellule ou de plage dynamique, tandis qu'INDEX retourne la valeur d'une cellule spécifique. DECALER est idéal pour créer des plages nommées dynamiques qui s'ajustent automatiquement, alors qu'INDEX est parfait pour extraire des valeurs précises d'un tableau. Utilise DECALER quand tu as besoin d'une référence qui change de taille ou de position, notamment pour des graphiques. Préfère INDEX/EQUIV pour des recherches de valeurs car il ne recalcule pas inutilement.
Utilise =MOYENNE(DECALER(B2;LIGNE()-6;0;5;1)) pour calculer la moyenne des 5 dernières valeurs. LIGNE() renvoie le numéro de la ligne courante, et la soustraction ajuste le point de départ à mesure que tu copies la formule vers le bas.
Cette technique est parfaite pour analyser les tendances dans des données de ventes ou de performance en lissant les variations ponctuelles.
DECALER recalcule à chaque modification de la feuille, même si les cellules qu'elle référence n'ont pas changé directement. Excel ne peut pas savoir à l'avance si le changement affecte le résultat, donc il recalcule par précaution. Cela peut ralentir les fichiers volumineux avec beaucoup de formules DECALER. Dans ce cas, INDEX/EQUIV offre de meilleures performances pour extraire des valeurs ponctuelles.
Non, DECALER retourne toujours une plage rectangulaire continue. Les paramètres hauteur et largeur définissent les dimensions de cette plage depuis le point de départ décalé.
Si tu as besoin de sélectionner des plages non contiguës, tu devras utiliser plusieurs formules DECALER séparées et les combiner, par exemple avec SOMMEPROD.
Utilise =SOMME(DECALER(A1;0;0;NBVAL(A:A);1)) pour créer une somme qui s'étend automatiquement jusqu'à la dernière valeur numérique de la colonne A, sans inclure les cellules vides.
C'est parfait pour des bases de données qui s'agrandissent régulièrement. Pense à soustraire 1 si la colonne a un en-tête : NBVAL(A:A)-1.
Pour aller plus loin
Continue sur ta lancée après la fonction DECALER : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
