Exceller

Fonction Excel OFFSET

Excel Offset Function

Fonction Excel OFFSETRésumé

La fonction Excel OFFSET renvoie une référence à une plage construite avec cinq entrées: (1) un point de départ, (2) un décalage de ligne, (3) un décalage de colonne, (4) une hauteur en lignes, (5) une largeur en Colonnes. OFFSET est pratique dans les formules qui nécessitent une plage dynamique.

Objectif Créer un décalage de référence à partir du point de départ donné Valeur de retour Une référence de cellule. Syntaxe = OFFSET (référence, lignes, cols, [hauteur], [largeur]) Arguments
  • référence - Le point de départ, fourni comme référence de cellule ou plage.
  • Lignes - Le nombre de lignes à décaler sous la référence de départ.
  • cols - Le nombre de colonnes à décaler à droite de la référence de départ.
  • la taille - [facultatif] La hauteur en lignes de la référence renvoyée.
  • largeur - [facultatif] La largeur en colonnes de la référence renvoyée.
Version Excel 2003 Notes d'utilisation

La fonction Excel OFFSET renvoie une plage dynamique construite avec cinq entrées: (1) un point de départ, (2) un décalage de ligne, (3) un décalage de colonne, (4) une hauteur en lignes, (5) une largeur en colonnes.



Le point de départ (le référence argument) peut être une cellule ou une plage de cellules. Le Lignes et cols les arguments sont le nombre de cellules à «décaler» par rapport au point de départ. Le la taille et largeur Les arguments sont facultatifs et déterminent la taille de la plage créée. Lorsque la taille et largeur sont omis, ils ont par défaut la hauteur et la largeur de référence .



formule de la valeur actuelle des flux de trésorerie futurs

Par exemple, pour référencer C5 à partir de A1, référence est A1, Lignes vaut 4 et cols vaut 2:

 
= OFFSET (A1,4,2) // returns reference to C5

Pour référencer C1: C5 depuis A1, référence est A1, Lignes vaut 0, cols vaut 2, la taille vaut 5 et largeur vaut 1:



 
= OFFSET (A1,0,2,5,1) // returns reference to C1:C5

Remarque: la largeur peut être omise, car elle sera par défaut à 1.

Il est courant de voir OFFSET enveloppé dans une autre fonction qui attend une plage. Par exemple, pour SUM C1: C5, commençant à A1:

 
= SUM ( OFFSET (A1,0,2,5,1)) // SUM C1:C5

L'objectif principal d'OFFSET est de permettre aux formules de s'ajuster dynamiquement aux données disponibles ou à l'entrée de l'utilisateur. La fonction OFFSET peut être utilisée pour construire un plage nommée dynamique pour les graphiques ou les tableaux croisés dynamiques, afin de garantir que les données source sont toujours à jour.



Remarque: la documentation Excel indique la taille et largeur ne peut pas être négatif, mais les valeurs négatives semblent avoir bien fonctionné depuis le début des années 90 . La fonction OFFSET dans Google Sheets n'autorisera pas de valeur négative pour les arguments de hauteur ou de largeur.

appliquer le thème du cadre au classeur actuel

Exemples

Les exemples ci-dessous montrent comment OFFSET peut être configuré pour renvoyer différents types de plages. Ces écrans ont été pris avec Excel 365 , donc OFFSET renvoie un tableau dynamique lorsque le résultat est plus d'une cellule. Dans les anciennes versions d'Excel, vous pouvez utiliser le Touche F9 pour vérifier les résultats renvoyés par OFFSET.

Exemple 1

Dans l'écran ci-dessous, nous utilisons OFFSET pour renvoyer la troisième valeur (mars) dans la deuxième colonne (ouest). La formule en H4 est:

 
= OFFSET (B3,3,2) // returns D6

Exemple de fonction OFFSET 1

Exemple # 2

Dans l'écran ci-dessous, nous utilisons OFFSET pour renvoyer la dernière valeur (juin) dans la troisième colonne (Nord). La formule en H4 est:

 
= OFFSET (B3,6,3) // returns E9

Exemple de fonction OFFSET 2

Exemple # 3

Ci-dessous, nous utilisons OFFSET pour renvoyer toutes les valeurs de la troisième colonne (Nord). La formule en H4 est:

 
= OFFSET (B3,1,3,6) // returns E4:E9

Exemple de fonction OFFSET 3

Exemple # 4

Ci-dessous, nous utilisons OFFSET pour renvoyer toutes les valeurs de mai (cinquième ligne). La formule en H4 est:

 
= OFFSET (B3,5,1,1,4) // returns C8:F8

Exemple de fonction OFFSET 4

Exemple # 5

Ci-dessous, nous utilisons OFFSET pour renvoyer les valeurs d'avril, mai et juin pour la région Ouest. La formule en H4 est:

 
= OFFSET (B3,4,2,3,1) // returns D7:D9

Exemple de fonction OFFSET 5

Exemple # 6

Ci-dessous, nous utilisons OFFSET pour renvoyer les valeurs d'avril, mai et juin pour l'Ouest et le Nord. La formule en H4 est:

formule pour calculer la valeur future de l'investissement
 
= OFFSET (B3,4,2,3,2) // returns D7:E9

Exemple de fonction OFFSET 6

Remarques

  • OFFSET ne renvoie qu'une référence, aucune cellule n'est déplacée.
  • Tous les deux Lignes et cols peuvent être fournis sous forme de nombres négatifs pour inverser leur sens de décalage normal - négatif cols décalage vers la gauche et négatif Lignes décalage ci-dessus.
  • OFFSET est un ' fonction volatile '- il recalculera à chaque changement de feuille de calcul. Les fonctions volatiles peuvent ralentir l'exécution de classeurs plus volumineux et plus complexes.
  • OFFSET affichera #REF! valeur d'erreur si le décalage est en dehors du bord de la feuille de calcul.
  • Lorsque la hauteur ou la largeur est omise, la hauteur et la largeur de référence est utilisé.
  • OFFSET peut être utilisé avec toute autre fonction qui s'attend à recevoir une référence.
  • La documentation Excel dit la taille et largeur ne peut pas être négative, mais les valeurs négatives fonctionnent.


^