Qu'est-ce que la fonction DECALER dans Excel ?
La fonction DECALER (nommée OFFSET dans les versions anglophones d'Excel) est l'une des plus déroutantes au premier abord, et pourtant l'une des plus puissantes une fois apprivoisée. Son principe est simple : à partir d'une cellule ou d'une plage de départ, elle renvoie une autre plage, décalée d'un certain nombre de lignes et de colonnes, et éventuellement redimensionnée. En clair, vous lui dites « pars de cette cellule, descends de 3 lignes, va 2 colonnes à droite, et donne-moi un bloc de 5 lignes sur 1 colonne ».
Ce qui fait l'intérêt de DECALER, c'est que les décalages et les dimensions ne sont pas obligés d'être des chiffres figés : ils peuvent être calculés. Vous pouvez donc fabriquer une plage qui s'agrandit toute seule quand vous ajoutez des lignes, un total qui ne porte que sur les douze derniers mois, ou une liste déroulante qui s'allonge automatiquement. C'est exactement ce qu'on appelle une plage dynamique, et DECALER est l'un des outils historiques pour en créer.
Concrètement, vous l'utiliserez pour suivre un chiffre d'affaires glissant, alimenter un graphique qui se met à jour seul, construire des menus déroulants qui évoluent avec vos données, ou récupérer la dernière valeur saisie dans une colonne. Voyons d'abord sa syntaxe, puis enchaînons sur des exemples que vous pourrez reproduire immédiatement.
En France, Excel sépare les arguments d'une fonction par un point-virgule, et non par une virgule. Vous écrivez donc =DECALER(A1; 3; 2). Si vous travaillez sur une version anglophone, la fonction s'appelle OFFSET et les arguments se séparent par des virgules.
La syntaxe de DECALER : 5 arguments
La fonction DECALER s'écrit ainsi :
=DECALER(référence ; lignes ; colonnes ; [hauteur] ; [largeur])Les trois premiers arguments sont obligatoires, les deux derniers sont facultatifs (c'est ce qu'indiquent les crochets). Voici le rôle exact de chacun :
| Argument | Obligatoire ? | Rôle |
|---|---|---|
| référence | Oui | La cellule ou la plage de départ, le point d'ancrage à partir duquel on se décale. |
| lignes | Oui | Nombre de lignes à parcourir vers le bas (valeur positive) ou vers le haut (valeur négative). 0 = même ligne. |
| colonnes | Oui | Nombre de colonnes à parcourir vers la droite (positive) ou vers la gauche (négative). 0 = même colonne. |
| [hauteur] | Non | Nombre de lignes que doit faire la plage renvoyée. Si omis, reprend la hauteur de la référence. |
| [largeur] | Non | Nombre de colonnes que doit faire la plage renvoyée. Si omis, reprend la largeur de la référence. |
Retenez surtout cette logique en deux temps. Les arguments lignes et colonnes déplacent le point de départ : ils répondent à la question « où aller ? ». Les arguments hauteur et largeur, eux, redimensionnent le bloc une fois arrivé : ils répondent à « quelle taille ramener ? ». Quand vous ne renseignez ni hauteur ni largeur, DECALER vous renvoie une seule cellule (si la référence est une seule cellule).
Un premier exemple pas à pas
Le plus simple pour comprendre DECALER est de la voir agir sur une grille. Imaginons un tableau de ventes : les mois sont en colonne A (de A2 à A13), et les montants correspondants en colonne B. Vous voulez récupérer le montant du quatrième mois sans cliquer dessus.
Choisir le point de départ
Votre ancre sera la cellule A1, l'angle haut-gauche de votre zone. Tous les décalages se compteront à partir d'elle. C'est le premier argument de la fonction.
Calculer le décalage
Le quatrième mois se trouve sur la ligne 5 de la feuille (A2 = mois 1, donc A5 = mois 4). Depuis A1, il faut descendre de 4 lignes. Le montant, lui, est dans la colonne B, soit une colonne à droite de A. Vous écrivez donc :
=DECALER(A1; 4; 1)Excel part de A1, descend de 4 lignes (il arrive en A5), se décale d'une colonne à droite (il arrive en B5) et renvoie le contenu de B5, c'est-à-dire le montant du quatrième mois. Vous n'avez pas eu à citer B5 directement : c'est tout l'intérêt, le décalage peut devenir variable.
Rendre le décalage variable
Remplacez le chiffre 4 par une référence vers une cellule de saisie, par exemple E1 où vous tapez le numéro du mois voulu :
=DECALER(A1; E1; 1)Désormais, il vous suffit de changer le nombre dans E1 pour que la formule pointe vers un autre mois. Vous venez de créer une cellule « pilotable » : c'est le socle de tous les usages avancés qui suivent.
Si la notion de référence de cellule et de signe égal vous semble encore floue, commencez par poser des bases solides avec notre guide de la fonction SOMME sur Excel avant d'attaquer DECALER, qui suppose ces fondamentaux acquis.
Créer une plage dynamique avec hauteur et largeur
C'est ici que DECALER prend toute sa dimension. Les deux derniers arguments, hauteur et largeur, vous permettent de renvoyer non plus une cellule, mais un bloc entier dont la taille s'adapte. Supposons que votre colonne B contienne des montants, et que ce nombre de montants change d'un mois sur l'autre : parfois 8 lignes, parfois 15. Comment désigner « toutes les valeurs, quelle que soit leur quantité » ?
La réponse combine DECALER et la fonction NBVAL, qui compte le nombre de cellules non vides d'une plage. Voici la formule :
=DECALER(B2; 0; 0; NBVAL(B:B); 1)Décortiquons-la. Le point de départ est B2, première cellule de données. Les arguments lignes et colonnes valent 0 : on ne bouge pas, on reste sur B2. La hauteur est NBVAL(B:B) : Excel compte les cellules remplies de la colonne B et en fait la hauteur du bloc. La largeur vaut 1 : une seule colonne. Résultat, la formule renvoie exactement la plage des montants, ni plus ni moins. Ajoutez une ligne de données, la plage grandit toute seule. C'est précisément ce qu'on attend d'une plage dynamique.
En pratique, on n'affiche pas une telle plage directement : on lui donne un nom dans le gestionnaire de noms (onglet Formules, « Définir un nom »), puis on utilise ce nom comme source d'un graphique ou d'un tableau croisé. Le graphique se met alors à jour automatiquement à chaque nouvelle saisie, sans la moindre manipulation.
DECALER + SOMME : le cumul glissant
Voici l'un des usages les plus appréciés en gestion. Vous voulez additionner non pas toute une colonne, mais seulement les N dernières valeurs : les trois derniers mois de chiffre d'affaires, par exemple, pour suivre une moyenne mobile. DECALER, glissée dans SOMME, fait exactement cela.
Imaginons vos mois de chiffre d'affaires en colonne B, de B2 à B13. Pour additionner les trois derniers mois en partant du bas du tableau :
=SOMME(DECALER(B13; -2; 0; 3; 1))Lisons la formule de l'intérieur vers l'extérieur. DECALER part de B13 (le dernier mois), remonte de 2 lignes pour arriver en B11, puis renvoie un bloc de 3 lignes sur 1 colonne, soit B11:B13. SOMME additionne ce bloc. Vous obtenez le cumul des trois derniers mois. Et si vous ajoutez le mois suivant en B14, il suffit d'ajuster le point d'ancrage pour que le calcul suive.
Pour rendre ce cumul totalement automatique, on combine souvent DECALER avec NBVAL afin de toujours pointer vers la dernière ligne, quel que soit le nombre de mois saisis. Le total des trois derniers mois s'écrit alors sans avoir à toucher la formule, même quand le tableau s'allonge :
=SOMME(DECALER(B1; NBVAL(B:B)-3; 0; 3; 1))Ici, NBVAL(B:B)-3 calcule à quelle ligne commencer pour attraper les trois dernières valeurs, où qu'elles soient. C'est le cumul glissant dans toute sa souplesse.
Envie de maîtriser vraiment Excel ?
Notre formation Excel vous accompagne des formules de base aux fonctions avancées comme DECALER, INDEX et les tableaux croisés dynamiques, en live avec un formateur dédié.
Une liste déroulante dynamique avec DECALER et NBVAL
Les listes déroulantes (validation des données) sont pratiques, mais souffrent d'un défaut : si vous les basez sur une plage fixe comme A2:A20, les nouveaux éléments que vous ajoutez en dessous n'apparaissent pas, et les cellules vides du bas polluent le menu. La solution consiste à alimenter la liste par une plage dynamique construite avec DECALER.
Supposons votre liste de choix en colonne A, à partir de A2. Créez d'abord un nom (onglet Formules, « Définir un nom »), par exemple MaListe, avec pour formule :
=DECALER(A2; 0; 0; NBVAL(A:A)-1; 1)Le -1 sert ici à ne pas compter la cellule d'en-tête en A1 dans la hauteur. Cette plage couvre exactement les éléments saisis, et grandit dès que vous en ajoutez. Ensuite, dans la fenêtre de validation des données (onglet Données, « Validation des données »), choisissez « Liste » comme critère et tapez =MaListe dans la source. Votre menu déroulant intègre désormais automatiquement chaque nouvel élément, sans jamais afficher de ligne vide. C'est un grand classique des tableaux de bord propres et maintenables.
DECALER ou INDEX : laquelle choisir ?
Beaucoup d'utilisateurs hésitent entre DECALER et INDEX, car les deux savent renvoyer une cellule ou une plage à partir de coordonnées. La distinction est importante, et notre recommandation est nette : dans la plupart des cas, préférez INDEX. Voici pourquoi, sans jargon.
La différence majeure tient à une notion technique appelée la volatilité. DECALER est une fonction dite « volatile » : elle se recalcule à chaque modification de la feuille, même quand ses données n'ont pas bougé. Sur un petit fichier, c'est invisible. Sur un gros classeur truffé de DECALER, cela peut ralentir sensiblement chaque saisie. INDEX, elle, n'est pas volatile : elle ne se recalcule que lorsque c'est nécessaire, ce qui préserve les performances.
| Critère | DECALER | INDEX |
|---|---|---|
| Volatilité | Volatile (recalcul permanent) | Non volatile (performante) |
| Plages dynamiques | Très adaptée, lecture intuitive | Possible, mais syntaxe moins évidente |
| Décalage relatif simple | Naturel et lisible | Naturel et lisible |
| Gros classeurs | À limiter | À privilégier |
En résumé : si vous construisez une vraie plage dynamique (graphique auto-extensible, liste déroulante qui s'allonge), DECALER reste plus lisible et fait très bien le travail, à condition de ne pas en abuser. Pour tout le reste, notamment les recherches de valeur, INDEX est plus rapide et plus sûre. Pour aller plus loin sur cette fonction, consultez notre guide INDEX EQUIV sur Excel, le duo de recherche flexible que beaucoup préfèrent à RECHERCHEV.
Le piège de la fonction volatile et les erreurs #REF!
Deux écueils reviennent systématiquement avec DECALER. Le premier, vous le connaissez maintenant : c'est la volatilité. Si vous remplissez un classeur de centaines de formules DECALER, chaque frappe au clavier déclenche un recalcul complet, et le fichier devient poussif. La parade : réservez DECALER aux quelques plages dynamiques qui en ont vraiment besoin, et basculez le reste sur INDEX ou sur des références directes.
Le second écueil est l'erreur #REF!. Elle survient lorsque DECALER tente de pointer en dehors de la feuille. Si vous demandez à Excel de remonter de 5 lignes depuis la ligne 3, vous sortez par le haut de la feuille : résultat, #REF!. Idem si vous vous décalez vers une colonne qui n'existe pas, à gauche de la colonne A. Vérifiez toujours que vos calculs de décalage ne peuvent pas produire une position négative ou hors limites.
Méfiez-vous des décalages calculés à partir de NBVAL quand une colonne contient des trous. NBVAL ne compte que les cellules non vides : si des lignes vides s'intercalent au milieu de vos données, la hauteur renvoyée sera trop courte et votre plage dynamique ratera les dernières valeurs. Gardez vos colonnes de données contiguës, sans cellule vide au milieu.
En résumé
La fonction DECALER (OFFSET) renvoie une plage décalée à partir d'un point d'ancrage, grâce à cinq arguments : la référence de départ, le décalage en lignes, le décalage en colonnes, puis la hauteur et la largeur facultatives du bloc à ramener. Sa vraie force est de fabriquer des plages dynamiques : combinée à NBVAL, elle alimente un graphique qui s'étend tout seul, une liste déroulante qui s'allonge, ou un cumul glissant sur les N dernières lignes. Gardez en tête ses deux limites, la volatilité qui pèse sur les gros classeurs et l'erreur #REF! quand on sort de la feuille, et préférez INDEX dès que vous n'avez pas réellement besoin d'une plage dynamique.
DECALER n'est qu'une pièce du puzzle des fonctions avancées d'Excel. Si vous souhaitez gagner en aisance et en autonomie, des plages dynamiques aux tableaux croisés en passant par les fonctions de recherche, notre formation Excel vous accompagne avec un formateur dédié rien que pour vous. Elle se déroule 100 % à distance, en live, ou en présentiel, avec inscription immédiate, démarrage rapide et paiement en 3× sans frais. Pour continuer sur votre lancée, parcourez aussi notre guide de la fonction SOMME et apprenez à construire une liste déroulante sur Excel.
