Excel OFFSET Fonction

L'Excel Fonction OFFSET renvoie une référence à une cellule ou à une plage de cellules décalée d'une cellule spécifique d'un nombre donné de lignes et de colonnes.

Syntaxe

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

Arguments

Valeur de retour

La fonction OFFSET renverra un décalage de référence de cellule à partir d'un point de départ donné.

Notes de fonction

1. #VALUE! La valeur d'erreur sera renvoyée lorsque la référence fait référence à une plage de cellules discontinues.
2. #REF! La valeur d'erreur sera renvoyée lorsque les lignes et les colonnes décalent la référence sur le bord de la feuille de calcul.

Exemples

Exemple 1: utilisation de base de la fonction OFFSET

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

Dans ce cas, B2 est le point de départ, les nombres 3 et 1 signifient que pour déplacer 3 lignes vers le bas et 1 colonne à droite de la cellule B2, et renvoyer finalement la valeur dans C5. Voir la capture d'écran:

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

Dans ce cas, vous obtiendrez les résultats de la plage 2 x 2, soit 3 lignes en dessous et 1 colonne à droite de la cellule B2.

Notes: #VALEUR! Une erreur se produit lorsque vous sélectionnez une seule cellule pour appliquer la fonction OFFSET pour renvoyer une plage de cellules. Vous devez sélectionner une plage 2 x 2 (dit 4 cellules vides), entrez la formule et appuyez sur le Ctrl + Shift + Entrer clés pour obtenir les résultats.

Exemple 2: Utilisez la fonction OFFSET pour additionner une plage de valeurs

Comme nous l'avons mentionné ci-dessus, si vous essayez d'utiliser la fonction OFFSET telle que =OFFSET(B2,3,1,2,2) seul, dans une seule cellule, il renverra une #VALEUR! Erreur. Cependant, si vous combinez la fonction SOMME et OFFSET comme illustré ci-dessous, la somme des valeurs de la plage C5: D6 sera renvoyée directement.

1. Sélectionnez une cellule vide, copiez-y la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat.
=SUM(OFFSET(B2,3,1,2,2)))

Exemple 3: Somme des colonnes en fonction de certains critères

Comme illustré ci-dessous, comment obtenir les ventes totales de mangue du dimanche au mardi? Veuillez essayer comme ci-dessous.

1. Sélectionnez une cellule vide, copiez-y la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Notes:

1. Dans la formule ci-dessus, MATCH(G4,B3:B8) recherche Mango et retrouve sa position en gamme B3: B8. Voyons voir, Mango localise 5 lignes sous la cellule B2, elle renvoie donc le nombre 5;
2. Comme le résultat MATCH est 5 et que la dernière virgule représente ici 0, la fonction OFFSET s'affiche maintenant comme OFFSET(C2:E2,5,0), ce qui signifie que le décalage commence à partir de la plage C2: E2, déplacez de 5 lignes vers le bas et de 0 colonne à droite pour obtenir la référence à la plage C7: E7;
3. Ensuite, la fonction SOMME s'affiche comme SUM(C7:E7), et enfin renvoyer la somme des valeurs dans la plage C7: E7.

Plus d'exemples

Comment additionner toutes les n lignes dans Excel?

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

Comment renvoyer la valeur de la cellule toutes les cinq ou nième ligne dans Excel?