Fonction OFFSET Excel

La fonction DECALER (OFFSET) d’Excel renvoie une référence à une cellule ou à une plage de cellules décalée d’un certain nombre de lignes et de colonnes par rapport à une cellule de départ.

Syntaxe

=OFFSET ()reference, rows, cols, [height], [width])

Arguments

Valeur de retour

La fonction DECALER renvoie une référence décalée d’un point de départ donné.

Remarques sur la fonction

1. La valeur d’erreur #VALEUR! s’affiche lorsque la référence pointe vers une plage de cellules non contiguës.
2. La valeur d’erreur #REF! apparaît lorsque le décalage en lignes ou en colonnes dépasse les limites de la feuille de calcul.

Exemples

Exemple 1 : Utilisation de base de la fonction DECALER

Renvoie une référence à une cellule avec la formule suivante :
=OFFSET(B2,3,1)

Dans ce cas, B2 constitue le point de départ ; les chiffres 3 et 1 indiquent un décalage de 3 lignes vers le bas et de 1 colonne vers la droite à partir de la cellule B2, ce qui renvoie finalement la valeur située en C5. Voir la capture d’écran :

Renvoie une référence à une plage de cellules avec la formule suivante :
=OFFSET(B2,3,1,2,2)

Dans ce cas, vous obtenez les résultats de la plage 2×2 située trois lignes en dessous et une colonne à droite de la cellule B2.

Remarque : l’erreur #VALEUR! apparaît si vous sélectionnez une seule cellule pour appliquer la fonction DECALER dans le but de renvoyer une plage de cellules. Vous devez sélectionner une plage de 2×2 (par exemple, quatre cellules vides), saisir la formule, puis appuyer sur Ctrl+Maj+Entrée pour obtenir les résultats.

Exemple 2 : Utiliser la fonction DECALER pour additionner une plage de valeurs

Comme mentionné précédemment, si vous utilisez la fonction DECALER seule dans une cellule unique, par exemple =OFFSET(B2,3,1,2,2), elle renvoie une erreur #VALEUR!. Toutefois, en combinant les fonctions SOMME et DECALER comme illustré dans la capture d’écran ci-dessous, vous obtenez directement la somme des valeurs de la plage C5:D6.

1. Sélectionnez une cellule vide, copiez la formule ci-dessous, puis appuyez sur la touche Entrée pour obtenir le résultat.
=SUM(OFFSET(B2,3,1,2,2)))

Exemple 3 : Additionner des colonnes selon certains critères

Comme illustré dans la capture d’écran ci-dessous, comment obtenir le total des ventes de mangues du dimanche au mardi ? Procédez comme suit.

1. Sélectionnez une cellule vide, copiez la formule ci-dessous, puis appuyez sur la touche Entréepour obtenir le résultat.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Remarques:

1. Dans la formule ci-dessus, MATCH(G4;B3:B8) recherche « Mangue » et renvoie sa position dans la plage B3:B8. Comme « Mangue » se trouve 5 lignes en dessous de la cellule B2, la fonction renvoie le nombre 5 ;
2. Puisque le résultat de la fonction EQUIV est 5 et que le dernier argument vaut 0, la fonction DECALER s’écrit désormais OFFSET(C2:E2;5;0), ce qui signifie que le décalage commence à partir de la plage C2:E2, descend de 5 lignes et ne se décale d’aucune colonne, ce qui donne la référence à la plage C7:E7 ;
3. Enfin, la fonction SOMME s’écrit SUM(C7:E7) et renvoie la somme des valeurs de la plage C7:E7.

Autres exemples

Comment additionner toutes les n lignes vers le bas dans Excel ?

Comment créer une plage nommée dynamique dans Excel ?

Comment renvoyer la valeur d’une cellule toutes les cinq lignes (ou toutes les *n* lignes) dans Excel ?