Fonction OFFSET (DECALER) Excel — Une référence qui se déplace (et quand INDEX vaut mieux)

En bref — OFFSET renvoie une référence qui part d'une cellule d'ancrage, descend d'un nombre donné de lignes et se décale à droite d'un nombre donné de colonnes, et peut éventuellement saisir un bloc d'une hauteur et d'une largeur données. Syntaxe : =OFFSET(référence, lignes, colonnes, [hauteur], [largeur]). =OFFSET(A1, 2, 1) est la cellule deux lignes plus bas et une colonne à droite de A1 (c.-à-d. B3). Parce qu'elle renvoie une référence, pas une valeur, vous pouvez l'envelopper dans SUM, AVERAGE ou COUNT et elle se redimensionne à la demande. Deux avertissements : elle est volatile (elle se recalcule à chaque modification), et pour simplement choisir une cellule par sa position, INDEX non volatile fait le même travail sans le coût de performance. Réservez OFFSET aux fenêtres qui se déplacent vraiment.

=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))   ' additionner la colonne A quelle que soit sa hauteur
=SUM(OFFSET(B2, COUNT(B:B)-3, 0, 3, 1))  ' additionner les 3 derniers nombres de la colonne B

La plupart des gens rencontrent OFFSET comme le moteur d'une « plage dynamique » — une plage nommée ou une source de graphique qui s'étend automatiquement à mesure que des lignes sont ajoutées. Elle est bonne à ça, ainsi qu'à une poignée de problèmes de fenêtre mobile que rien d'autre n'exprime aussi élégamment. Mais elle est aussi volatile et facile à surexploiter, et Excel moderne a discrètement rendu la moitié de ses anciens cas d'usage obsolètes. Ce guide vous donne le modèle mental, les pièges, et une ligne claire sur le moment de basculer vers INDEX.

Ce que vous allez apprendre

Le modèle mental : partir d'une ancre, se déplacer, puis éventuellement saisir un bloc
Pourquoi OFFSET renvoie une référence — et pourquoi cela permet à SUM de l'envelopper
Le coût de la volatilité, et le piège du #REF! hors bord
La plage nommée dynamique classique — et pourquoi un Tableau ou une plage de déversement la surpasse aujourd'hui
La niche réellement difficile à remplacer : fenêtres mobiles et totaux glissants
Le choix de jugement : OFFSET face à INDEX non volatile

Remarque — Dans Excel en français, ces fonctions s'affichent sous des noms traduits : OFFSET → DECALER, INDEX → INDEX, SUM → SOMME, MATCH → EQUIV, COUNTA → NBVAL, SUMIFS → SOMME.SI.ENS, FILTER → FILTRE. Dans les blocs de code ci-dessous, les noms anglais sont conservés tels quels ; utilisez l'équivalent français dans votre version localisée d'Excel. Les codes d'erreur comme #REF! restent identiques en français.

Le modèle mental : partez d'ici, marchez jusque-là, saisissez cette quantité

OFFSET a cinq arguments, mais ils racontent une seule histoire simple. Partez de l'ancre, faites lignes pas vers le bas et colonnes pas vers la droite pour atterrir sur une nouvelle cellule, puis — si vous donnez hauteur et largeur — développez depuis cette cellule d'arrivée en un bloc de cette taille :

=OFFSET(A1, 2, 1)         ' atterrir sur B3 (2 vers le bas, 1 à droite), une cellule
=OFFSET(A1, 2, 1, 3, 2)   ' un bloc de 3 lignes × 2 colonnes à partir de B3  ->  B3:C5
=OFFSET(A1, -1, 0)        ' un nombre négatif remonte -> ligne au-dessus de A1 (ici, #REF!)

Les nombres positifs déplacent vers le bas et la droite ; les nombres négatifs vers le haut et la gauche. Omettez hauteur et largeur et le résultat a la même taille que l'ancre (généralement une seule cellule). C'est toute la fonction — un pointeur mobile qui peut aussi s'étirer en rectangle.

Pourquoi elle renvoie une référence, pas une valeur

C'est la caractéristique qui rend OFFSET utile et déroute les débutants. OFFSET ne rend pas le contenu du bloc — elle rend la référence vers le bloc. Seule dans une cellule, =OFFSET(A1,2,1,3,2) se déversera ou affichera la valeur du haut, mais son vrai rôle est d'être alimentée à une autre fonction :

=SUM(OFFSET(A1, 0, 0, 12, 1))     ' additionner un bloc de 12 lignes ancré en A1
=AVERAGE(OFFSET(A1, 0, 0, 12, 1)) ' moyenne de ce même bloc

Parce que la hauteur du bloc est un argument, vous pouvez en faire une formule — COUNTA(A:A), MATCH(...), une cellule dans laquelle l'utilisateur tape — et la plage se redimensionne d'elle-même. C'est toute la base de chaque « plage dynamique » construite sur OFFSET.

La plage dynamique classique — et son remplaçant moderne

Pendant des années, la façon standard de faire grandir un graphique ou une liste déroulante avec vos données était une plage nommée OFFSET :

' Nom "DonnéesVentes" =  OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)
' s'étend automatiquement au nombre de lignes que la colonne A contient actuellement

Ça marche. Mais sur n'importe quelle version actuelle d'Excel, c'est le mauvais choix par défaut, pour deux raisons. Premièrement, OFFSET est volatile — un classeur plein de ces plages recalcule à chaque frappe et commence à sembler lent. Deuxièmement, Excel dispose désormais d'outils non volatils qui font la même chose de façon plus lisible :

Un Tableau Excel (Ctrl+T) grandit automatiquement ; référencez-le comme Tableau1[Ventes] et chaque graphique, SUMIFS et liste déroulante pointant dessus s'étend tout seul — aucune formule, aucune volatilité.
Une plage de déversement issue d'une formule matricielle dynamique utilise l'opérateur # : si =FILTER(...) se déverse depuis E2, alors E2# est la plage vivante, toujours de la bonne taille.

Le verdict : si vous vous apprêtez à créer une plage nommée dynamique OFFSET en 2026, prenez plutôt un Tableau ou une référence de déversement. Réservez les plages OFFSET aux classeurs hérités que vous maintenez, pas aux nouveaux.

Le piège : marcher hors bord renvoie #REF!

OFFSET échoue bruyamment lorsque vous l'envoyez quelque part qui n'existe pas. Remontez au-dessus de la ligne 1 ou à gauche de la colonne A — ou dimensionnez un bloc qui dépasse la dernière ligne de la feuille — et vous obtenez #REF! :

=OFFSET(A1, -1, 0)          ' #REF!  — il n'y a pas de ligne au-dessus de A1
=OFFSET(A1, 0, 0, 0, 1)     ' #REF!  — un bloc ne peut pas avoir une hauteur nulle

La version subtile mord dans les plages dynamiques : si COUNTA($A:$A) compte une valeur égarée loin dans la colonne, ou compte zéro sur une colonne vide, l'argument de hauteur devient faux et toute la plage s'effondre ou renvoie une erreur. Ancrez au vrai sommet de vos données et assurez-vous que la formule de dimensionnement ne peut pas renvoyer 0.

La niche qu'OFFSET conserve : les fenêtres mobiles

Voici où OFFSET gagne encore sa place — une fenêtre qui glisse, où le début et la taille se déplacent ensemble. Les totaux glissants et les « N dernières lignes » en sont les cas emblématiques :

' Somme glissante des 3 entrées les plus récentes de la colonne B
=SUM(OFFSET(B2, COUNT(B2:B1000)-3, 0, 3, 1))

' Valeur N lignes sous un en-tête trouvé (un point de lecture mobile)
=OFFSET($A$1, MATCH("Total", $A:$A, 0)-1, 1)

Ce mouvement « ancrer, puis sauter à une position qu'une formule calcule, puis lire une fenêtre autour » est réellement pénible à exprimer autrement. Même ici, un total glissant basé sur des dates est souvent plus propre avec SUMIFS et une condition ">="&seuil — mais pour une fenêtre positionnelle, comptée en lignes, OFFSET reste l'outil le plus concis.

Le choix de jugement : OFFSET face à INDEX

L'unique règle qui corrigera la plupart des abus d'OFFSET : si vous utilisez OFFSET pour indexer dans une plage — « donne-moi la cellule à cette position » — basculez vers INDEX. INDEX renvoie elle aussi une référence, fait la même sélection positionnelle, et n'est pas volatile :

=OFFSET($A$1, n, 0)     ' façon volatile d'obtenir la (n+1)-ième cellule de la colonne A
=INDEX($A:$A, n+1)      ' même cellule, non volatile, Excel peut la tracer

Choisir une cellule/ligne/colonne par position → INDEX. Non volatile, traçable, se lit plus clairement. Cela couvre la grande majorité de ce pour quoi les gens utilisent OFFSET.
Faire grandir une plage à mesure que des données sont ajoutées → un Tableau ou une référence de déversement #.
Une fenêtre réellement mobile (total glissant, N dernières lignes, lecture-autour-d'une-correspondance) → OFFSET reste le bon choix, employé délibérément et en petit nombre.

L'avis tranché : OFFSET est une spécialiste, pas une valeur sûre du quotidien. Chaque OFFSET volatile que vous remplacez par INDEX, un Tableau ou une plage de déversement rend le classeur plus rapide et plus facile à tracer pour la personne suivante — et vous ne perdez rien d'autre que la latence de recalcul.

Comment ExcelMaster vous aide

Les formules OFFSET sont l'endroit où les feuilles de calcul deviennent lentes et illisibles — COUNTA imbriqués, hauteurs décalées d'une unité, et une volatilité que personne ne remarque jusqu'à ce que le fichier rame. ExcelMaster lit l'objectif — un graphique qui grandit avec les données, un total glissant sur 3 mois, la valeur à côté de la ligne qui dit « Total » — et l'écrit à la façon moderne : un Tableau ou une plage de déversement quand vous avez juste besoin de croissance, INDEX quand vous indexez par position, et un OFFSET serré uniquement quand une fenêtre mobile l'exige vraiment. Vous obtenez le comportement dynamique sans hériter d'une formule volatile à surveiller.

Foire aux questions

Que fait la fonction OFFSET dans Excel ?

Elle renvoie une référence située à un nombre donné de lignes et de colonnes d'une ancre de départ, éventuellement redimensionnée en un bloc : =OFFSET(A1, 2, 1) est la cellule deux lignes plus bas et une colonne à droite de A1. Parce qu'elle renvoie une référence, vous l'enveloppez généralement dans SUM, AVERAGE ou COUNT.

Pourquoi OFFSET est-elle considérée comme volatile ?

Excel ne peut pas prédire vers quelles cellules OFFSET pointera avant de calculer, si bien qu'il la réévalue à chaque recalcul, pas seulement quand ses entrées changent. Quelques-unes sont sans danger ; des milliers ralentissent le classeur de manière notable. C'est la principale raison de préférer INDEX ou un Tableau quand vous le pouvez.

OFFSET ou INDEX — laquelle utiliser ?

Pour choisir une cellule par position, utilisez INDEX — elle renvoie une référence tout comme OFFSET mais est non volatile et traçable. Réservez OFFSET aux fenêtres réellement mobiles (totaux glissants, « N dernières lignes ») où le début et la taille se déplacent ensemble.

Comment créer une plage dynamique avec OFFSET ?

Le motif classique est =OFFSET($A$1, 0, 0, COUNTA($A:$A), 1) dans une plage nommée, qui grandit à mesure que la colonne A se remplit. Sur Excel moderne, préférez plutôt un Tableau Excel ou une référence de déversement (A2#) — les deux s'étendent automatiquement sans volatilité.

Pourquoi mon OFFSET renvoie-t-il #REF! ?

Vous êtes sorti du bord de la feuille (lignes négatives au-dessus de la ligne 1, au-delà de la dernière colonne) ou vous avez donné à un bloc une hauteur ou une largeur de 0. Dans les plages dynamiques, cela signifie généralement que la formule de hauteur (comme COUNTA) a renvoyé 0 ou un décompte inattendu. Vérifiez l'ancre et les arguments de dimensionnement.

Testé dans

Testé dans : Excel 365 (Windows 11) — dernière vérification le 2026-07-01.

Guides associés : Excel INDIRECT · Excel ADDRESS · Excel SUMIFS · Excel FILTER · Excel XLOOKUP