Fonction DECALER Excel : plages dynamiques + 7 exercices
- 5 août
- 10 min de lecture
La fonction DECALER (OFFSET en anglais) ne déplace rien du tout : elle renvoie une référence construite à partir d'une cellule de départ, d'un décalage en lignes et en colonnes, et — c'est là sa vraie force — d'une hauteur et d'une largeur ajustables. Sa syntaxe compte cinq arguments : =DECALER(réf;lignes;colonnes;[hauteur];[largeur]).
Dans ce tutoriel, nos formateurs vous montrent pas à pas comment vous décaler dans un tableau, construire une plage dynamique qui grandit toute seule avec vos données, cumuler jusqu'à un mois choisi avec DECALER + EQUIV, calculer une moyenne mobile sur 12 mois glissants, alimenter une liste déroulante et un graphique évolutifs — et surtout quand ne pas l'utiliser, car DECALER est une fonction volatile.
Le tout avec un fichier de 7 exercices auto-corrigés à télécharger gratuitement.
Sommaire
Télécharger le fichier d'exercices sur la fonction DECALER
Pour pratiquer en conditions réelles, téléchargez notre classeur d'entraînement gratuit : 7 exercices progressifs (du décalage simple à l'arbitrage avec INDEX) avec correction automatique ✅/❌ et un onglet Corrigé détaillant chaque formule en version française. L'exercice 1 est déjà résolu pour vous servir de modèle.
À quoi sert la fonction DECALER sur Excel ?
DECALER répond à un besoin précis : désigner une zone de cellules dont la position ou la taille change.
Là où une plage classique comme C2:C13 est figée dans le marbre, DECALER la recalcule à chaque fois, en fonction de ce que contient votre feuille.
Concrètement, nos stagiaires l'utilisent pour trois familles de besoins : additionner un nombre variable de lignes (un tableau qui s'allonge chaque mois), extraire une fenêtre glissante de données (les 3 ou 12 derniers mois), et alimenter une liste déroulante ou un graphique qui s'étendent automatiquement quand on ajoute une ligne.
C'est aussi une fonction qu'on rencontre beaucoup dans les fichiers hérités d'un prédécesseur : savoir la lire vous évitera de casser un classeur en le reprenant.
La syntaxe de DECALER : les 5 arguments expliqués
=DECALER(réf;lignes;colonnes;[hauteur];[largeur])
réf : le point de départ. Une cellule (ou une plage) à partir de laquelle Excel va compter. Ce n'est pas le résultat : c'est l'origine du repère.
lignes : le nombre de lignes à descendre. Une valeur positive descend, une valeur négative remonte, 0 reste sur place.
colonnes: le nombre de colonnes à décaler vers la droite. Négatif pour aller vers la gauche.
[hauteur] : facultatif. Le nombre de lignes de la plage renvoyée. Omis, il vaut 1.
[largeur] :facultatif. Le nombre de colonnes de la plage renvoyée. Omis, il vaut 1.
Retenez cette règle simple : avec 3 arguments, DECALER renvoie une cellule ; avec 5, elle renvoie une plage. Et une plage ne s'affiche pas telle quelle : il faut l'envelopper dans une SOMME, une MOYENNE, un NB, etc.
Premier exemple pas à pas : se décaler de 3 lignes et 2 colonnes
Exercice 1 : Commercial : DECALER à trois arguments
Objectif : retrouver le chiffre d'affaires du 3ᵉ commercial au T2 en partant de la cellule d'en-tête.
Cet exemple correspond à l'exercice 1 « Commercial : décalage simple lignes/colonnes » du fichier à télécharger, dont l'objectif est de retrouver le chiffre d'affaires du 3ᵉ commercial au 2ᵉ trimestre. Il est déjà résolu dans le classeur pour vous servir de modèle.
Imaginons un tableau dont l'en-tête « Commercial » est en B9, suivi de cinq commerciaux en lignes et de trois trimestres en colonnes (T1, T2, T3). On veut le CA du 3ᵉ commercial au T2 :
=DECALER(B9;3;2)
Décomposons :
on part de B9
on descend de 3 lignes (on arrive sur la ligne du 3ᵉ commercial en B12)
puis on se décale de 2 colonnes vers la droite (colonne T2). Résultat : 61 400 en D12
Le piège classique : on compte les décalages depuis la cellule de départ, pas les positions. La 3ᵉ ligne de données correspond à un décalage de 3 quand on part de l'en-tête, mais de 2 si l'on part de la première ligne de données. Partez toujours de l'en-tête, c'est le repère le plus stable.
Décaler une cellule, une ligne ou une colonne : DECALER ne déplace rien
C'est de loin le malentendu le plus fréquent, et aucun tutoriel ne prend la peine de le dissiper. Quand on tape « décaler une colonne Excel » ou « décaler une ligne », on cherche presque toujours à déplacer physiquement des données — pas à utiliser une fonction.
Or la fonction DECALER ne bouge aucune cellule. Elle se contente de renvoyer une référence, c'est-à-dire l'adresse d'une zone, que d'autres fonctions vont ensuite exploiter. Votre feuille reste strictement identique.
Pour vraiment déplacer des données, ce sont d'autres gestes : sélectionner la colonne puis Maj + glisser-déposer sur le bord de la sélection, ou Ctrl + X puis clic droit « Insérer les cellules coupées ». Rien à voir avec la fonction.
Notre repère de formation : si vous voulez que la feuille change d'aspect, c'est une manipulation ; si vous voulez qu'un calcul s'adapte, c'est DECALER.
SOMME et DECALER : renvoyer une plage entière avec hauteur et largeur
C'est l'objet de l'exercice 2 « Finance : total du 1er semestre », dont l'objectif est d'additionner les 6 premiers mois sans jamais écrire la plage C21:C26.
Exercice 2 : Finance : DECALER avec hauteur et largeur
Objectif : additionner le chiffre d'affaires des 6 premiers mois sans écrire la plage en dur.
Sur un tableau de CA mensuel (mois en colonne B, montants en colonne C à partir de C21), la formule s'écrit :
=SOMME(DECALER(C21;0;0;6;1))
On ne se décale pas (0 ligne, 0 colonne) mais on demande une plage de 6 lignes sur 1 colonne : DECALER renvoie donc C21:C26, et SOMME l'additionne. Résultat : 883 000 €.
Remplacez le 6 par une cellule de saisie et vous obtenez un total paramétrable en un clic — sans toucher à la formule.
Créer une plage dynamique avec DECALER et NBVAL
C'est le sujet de l'exercice 3 « RH : masse salariale à effectif variable » : totaliser les salaires même quand on ajoute un salarié en bas du tableau.
Exercice 3 : RH : DECALER combinée à NBVAL
Objectif : calculer la masse salariale d'un tableau dont le nombre de lignes évolue.
L'idée est de laisser NBVAL compter les cellules non vides, et d'injecter ce nombre dans l'argument hauteur de DECALER :
=SOMME(DECALER(C39;0;0;NBVAL(C39:C51);1))
Sept salariés renseignés ? NBVAL renvoie 7, DECALER construit C39:C45, le total tombe juste. Ajoutez un huitième salarié : la plage s'étend d'elle-même, sans la moindre retouche.
Attention à une condition : aucune cellule vide au milieu de la colonne. NBVAL compte les valeurs, pas les lignes ; un trou décale la fin de la plage et vous fait perdre la dernière ligne.
DECALER et EQUIV : cumuler jusqu'au mois choisi
Direction l'exercice 4 « Comptabilité : cumul jusqu'au mois choisi », dont l'objectif est de cumuler le CA de janvier jusqu'au mois de clôture saisi dans une cellule.
Exercice 4 : Comptabilité : DECALER pilotée par EQUIV
Objectif : cumuler le chiffre d'affaires de janvier jusqu'au mois de clôture indiqué par l'utilisateur.
La fonction EQUIV renvoie la position d'une valeur dans une plage. Cette position devient la hauteur de DECALER :
=SOMME(DECALER(C21;0;0;EQUIV(C58;B21:B32;0);1))
Si C58 contient « Mai », EQUIV renvoie 5, DECALER construit une plage de 5 lignes et SOMME cumule janvier à mai, soit 715 500 €. Changez le mois : le cumul se recalcule seul. C'est le cœur de tout tableau de bord mensuel un peu sérieux.
Pensez à figer les plages avec le signe $ si vous recopiez la formule sur plusieurs colonnes.
Calculer une moyenne mobile sur 12 mois glissants
C'est l'exercice 5 « Marketing : moyenne mobile 3 mois », dont l'objectif est de calculer la moyenne des derniers mois renseignés, quel que soit le mois en cours.
Exercice 5 : Marketing : DECALER en fenêtre glissante
Objectif : obtenir la moyenne des 3 derniers mois renseignés, sans modifier la formule chaque mois.
Le principe : on se décale jusqu'à la fin des données, puis on remonte du nombre de périodes voulu. NBVAL donne la position de la dernière ligne :
=MOYENNE(DECALER(C21;NBVAL(C21:C32)-3;0;3;1))
Avec 12 mois saisis, on descend de 9 lignes et on prend une fenêtre de 3 : octobre, novembre, décembre. Moyenne : 184 300 €. Le mois suivant s'ajoute ? La fenêtre glisse toute seule.
Pour un cumul 12 mois glissants — l'indicateur préféré des contrôleurs de gestion, parce qu'il neutralise la saisonnalité — remplacez simplement le 3 par 12 et MOYENNE par SOMME. Vous obtenez un CA annuel mobile qui se met à jour à chaque nouvelle ligne, sans jamais toucher à la formule.
Créer une liste déroulante dynamique avec DECALER
Cet usage prolonge l'exercice 6 « Gestion de projet : dernière ligne saisie », dont l'objectif est de repérer la dernière valeur d'une liste qui s'allonge.
Exercice 6 : Gestion de projet : DECALER pour la dernière ligne
Objectif : renvoyer le budget de la dernière tâche saisie, quel que soit le nombre de lignes remplies.
Une liste déroulante classique pointe une plage figée : ajoutez un élément en dessous, il n'apparaît pas. La solution consiste à créer une plage nommée dynamique. Dans Formules > Gestionnaire de noms, créez un nom (par exemple Equipe) avec pour référence :
=DECALER(Feuil1!$B$2;0;0;NBVAL(Feuil1!$B$2:$B$100);1)
Il ne reste qu'à choisir Données > Validation des données > Liste, et à saisir =Equipe comme source. Chaque nouvel élément saisi apparaît immédiatement dans la liste.
Pour renvoyer la dernière valeur saisie d'une colonne, la même logique donne :
=DECALER(C73;NBVAL(C73:C82)-1;0)
Alimenter un graphique dynamique avec DECALER
Même mécanique, appliquée cette fois à un graphique. Créez deux plages nommées dynamiques — une pour les étiquettes, une pour les valeurs —, puis dans Sélectionner des données, remplacez les références de la série par =NomDuClasseur.xlsx!Valeurs.
Le nom du classeur est obligatoire : Excel refuse une plage nommée « nue » dans la définition d'une série. Une fois en place, chaque ligne ajoutée à la source étend le graphique automatiquement.
À noter : sur Excel 2016 et versions suivantes, mettre vos données sous forme de tableau structuré (Ctrl + L) fait la même chose sans aucune formule.
Nos formateurs recommandent le tableau structuré par défaut, et DECALER uniquement quand la source ne peut pas être convertie — par exemple une plage alimentée par un autre outil.
DECALER en anglais : OFFSET (et MOIS.DECALER n'a rien à voir)
Dans un Excel en anglais, DECALER se nomme OFFSET. La traduction est automatique à l'ouverture du fichier : vous n'avez rien à convertir, seul l'affichage change. Attention en revanche au séparateur d'arguments, qui devient la virgule : =OFFSET(B9,3,2).
Les équivalences les plus utiles autour de DECALER :
DECALER = OFFSET · NBVAL = COUNTA · EQUIV = MATCH · INDEX = INDEX · INDIRECT = INDIRECT · SOMME = SUM · MOYENNE = AVERAGE
Un point qui piège beaucoup de monde : MOIS.DECALER n'est pas une variante de DECALER. C'est une fonction de date (EDATE en anglais) qui ajoute ou retire un nombre de mois à une date : =MOIS.DECALER(A1;3) renvoie la date trois mois plus tard. Idem pour JOUR.DECALER (WORKDAY), qui décale un nombre de jours ouvrés. Les noms se ressemblent, les usages n'ont rien de commun.
DECALER, INDEX ou INDIRECT : laquelle choisir ?
C'est l'objet de l'exercice 7 « Direction : la même chose sans DECALER », dont l'objectif est de reproduire le résultat de l'exercice 1 avec INDEX, une fonction non volatile.
Les trois fonctions renvoient une référence, mais elles ne se valent pas :
INDEX — extrait une valeur par sa position dans une plage. Non volatile, rapide, à privilégier pour toute recherche ponctuelle. Notre article dédié à la fonction INDEX détaille sa combinaison avec EQUIV.
INDIRECT — construit une référence à partir de texte : =INDIRECT("B"&A1) pointe la cellule dont le numéro de ligne est saisi en A1. Pratique pour viser une feuille dont le nom est choisi par l'utilisateur. Elle est volatile elle aussi, et elle casse dès qu'une feuille est renommée ou une ligne supprimée, car elle ne suit pas les références.
DECALER — la seule des trois capable de renvoyer une plage redimensionnable. C'est son avantage exclusif, et la seule vraie raison de la choisir.
Le point que la plupart des tutoriels passent sous silence : DECALER est une fonction volatile. Elle se recalcule à chaque frappe dans le classeur, même si aucune de ses cellules sources n'a bougé. Une poignée de formules ne pose aucun problème ; plusieurs centaines sur un fichier lourd, et le classeur devient franchement pénible à utiliser.
La règle que nous donnons en formation : DECALER pour une plage qui change de taille, INDEX pour tout le reste. Ainsi, l'exemple du 3ᵉ commercial au T2 s'écrit tout aussi bien =INDEX(B9:E14;4;3) — même résultat, sans le coût du recalcul permanent.
Exercice 7 : Direction : INDEX à la place de DECALER
Objectif : retrouver le résultat de l'exercice 1 avec INDEX, pour mesurer quand DECALER est superflue.
Les erreurs fréquentes avec DECALER
#REF! — le décalage sort de la feuille. C'est le cas typique d'un décalage négatif trop grand : depuis B2, se décaler de -5 lignes n'existe pas. Vérifiez le signe et l'ampleur de vos arguments lignes et colonnes.
#VALEUR! — une hauteur ou une largeur nulle ou négative. NBVAL(...)-1 sur une colonne vide donne -1 : protégez la formule avec SIERREUR.
Un résultat décalé d'une ligne — vous comptez les positions au lieu des décalages. Depuis l'en-tête, la 1ʳᵉ ligne de données correspond à un décalage de 1, pas de 0.
Une plage qui n'affiche rien — vous avez demandé une plage (5 arguments) sans l'envelopper dans SOMME, MOYENNE ou NB. Excel ne sait pas afficher plusieurs cellules dans une seule.
Un classeur devenu lent — la volatilité. Comptez vos DECALER et remplacez par INDEX toutes celles qui ne renvoient qu'une seule cellule.
FAQ sur la fonction DECALER
À quoi sert la fonction DECALER dans Excel ?
Elle renvoie une référence construite à partir d'une cellule de départ, d'un décalage en lignes et en colonnes, et éventuellement d'une hauteur et d'une largeur. Son intérêt principal est de créer des plages dont la taille s'adapte automatiquement aux données.
Comment associer DECALER et RECHERCHEV ?
On imbrique DECALER dans l'argument « table_matrice » de la RECHERCHEV, pour faire varier la zone de recherche. Dans la pratique, INDEX + EQUIV fait la même chose plus simplement et sans volatilité : c'est ce que nous recommandons.
Pourquoi dit-on que DECALER est volatile ?
Parce qu'Excel la recalcule à chaque modification du classeur, même sans rapport avec ses cellules sources. Sur un fichier volumineux, cela ralentit sensiblement les calculs.
Quelle est la différence entre DECALER et MOIS.DECALER ?
Aucun rapport malgré les noms proches : DECALER (OFFSET) manipule des références de cellules, MOIS.DECALER (EDATE) ajoute ou retire des mois à une date.
Comment décaler une colonne sur Excel ?
Pour déplacer réellement une colonne, sélectionnez-la puis maintenez Maj en la glissant à sa nouvelle place, ou utilisez Ctrl + X puis « Insérer les cellules coupées ». La fonction DECALER, elle, ne déplace rien.
DECALER fonctionne-t-elle dans Google Sheets ?
Oui, sous le nom OFFSET, avec la même syntaxe et les mêmes arguments facultatifs.
Peut-on utiliser DECALER dans un tableau croisé dynamique ?
Pas à l'intérieur, mais on peut lui faire définir la plage source d'un tableau croisé dynamique pour qu'il s'étende automatiquement — même si le tableau structuré (Ctrl + L) reste la méthode la plus simple.
