Formule Excel OFFSET (Table des matières)

  • Qu'est-ce que la formule OFFSET dans Excel?
  • Comment utiliser la formule OFFSET dans Excel?

Qu'est-ce que la formule OFFSET dans Excel?

La formule OFFSET dans Excel donne une cellule ou une plage de cellules rectangulaires dynamiques en sortie qui est un sous-ensemble de la plage totale donnée avec un nombre spécifié de lignes, de colonnes, de hauteur et de largeur.

Syntaxe de la formule OFFSET dans Excel

Explication de la fonction OFFSET dans Excel:

  • référence: Le point de départ de l'OFFSET ou on peut dire une plage de cellules qui peut être considérée comme la base de l'OFFSET.
  • lignes : nombre de lignes à DÉCALER en dessous ou au-dessus de la référence de base.
  • cols: nombre de colonnes à DÉCALER à droite ou à gauche de la référence de base.
  • hauteur: nombre de lignes dans la référence renvoyée.
  • width: nombre de colonnes dans la référence renvoyée.

La hauteur et la largeur sont les paramètres facultatifs de cette fonction et peuvent être utilisées pour déterminer la taille de la référence créée.

Comment utiliser la formule OFFSET dans Excel?

Nous allons maintenant apprendre à écrire une formule de décalage pour obtenir la valeur de recherche. Comprenons la formule de décalage avec quelques exemples dans Excel.

Vous pouvez télécharger ce modèle Excel de formule OFFSET ici - Modèle Excel de formule OFFSET

Formule Excel OFFSET - Exemple # 1

Supposons que nous ayons les données ci-dessous.

  • Dans la cellule E2, entrez la formule suivante:

= DÉCALAGE (A1, 11, 1)

  • Une fois la formule terminée, appuyez sur Entrée et voyez le résultat.

Ce que cette formule a fait, c'est qu'elle a pris la référence de la cellule A1 (Année) et a baissé de 11 lignes après A1 (c'est-à-dire la ligne n ° 12), puis a gagné la valeur qui est la coordonnée de la 11 e ligne après A1 et la première colonne ( colonne A = 0 colonne B = 1 dans ce cas. Comme on le sait, on peut aller à droite et à gauche). Soit 141, 27 millions de dollars.

Formule Excel OFFSET - Exemple # 2

Nous allons maintenant voir un autre exemple de cellule de référence pour une année spécifique. Mettez la formule suivante dans la cellule E3:

= DÉCALAGE (A1, 11, 0)

  • Une fois la formule terminée, appuyez sur Entrée et voyez le résultat.

Si vous avez vu la formule, elle est la même que la précédente, la seule modification concerne la référence de la colonne. La référence de colonne 0 (zéro) signifie que la valeur sera capturée à partir de la colonne A pour la 11 e ligne après A1 (qui est 2010).

Formule Excel OFFSET - Exemple # 3

Dans cet exemple, nous calculerons la somme des dernières N lignes à l'aide de la fonction Excel OFFSET. La formule générale pour résumer les derniers N nombres est la suivante:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

Pour résumer les N dernières observations de vos données, vous pouvez utiliser la fonction SUM avec la fonction OFFSET et COUNT ensemble. Dans cet exemple, nous résumerons les 5 derniers points de données. Veuillez noter que lorsque nous disons N, c'est une généralisation de toute valeur entière. Ainsi, vous pouvez calculer pour les 3 derniers, 4 derniers, 5 derniers, etc. en fonction de votre ensemble de données ainsi que de vos besoins.

Faisons-le pour notre ensemble de données:

  • Saisissez la formule suivante dans la cellule E2 du fichier Excel.

= SUM (OFFSET (B1, COUNT (B: B), 0, -5))

  • Cliquez sur Entrée pour voir la sortie.

Comme nous l'avons déjà vu, la fonction Excel OFFSET génère des plages dynamiques rectangulaires lorsqu'elle reçoit une référence de départ.

  • Dans ce cas, la référence de départ est fournie en termes de B1 (comme premier argument de la fonction OFFSET).
  • Nous avons utilisé la fonction COUNT car nous avons besoin de la somme des observations de la dernière valeur. La fonction COUNT aide OFFSET à déterminer le nombre d'observations (lignes) dans la colonne B. Elle compte toutes les valeurs numériques de la colonne B (19 est le nombre).
  • Avec 19, la formule OFFSET devient:

= DÉCALAGE (B1, 19, 0, -5)

  • Cette formule commence le décalage à B1 avec 19 lignes et la même colonne (0). Ensuite, le paramètre de hauteur -5 aide cette fonction à étendre la plage vers l'arrière de 5 lignes (voir le signe négatif associé à 5).
  • La plage dynamique des cinq dernières observations est donnée en entrée de la fonction somme. Ce qui donne en additionnant ceux de la colonne B.

Formule Excel OFFSET - Exemple # 4

Dans cet exemple, nous calculerons les ventes médianes des 3 dernières années. La fonction OFFSET peut être utilisée comme argument pour le calculer.

  • Mettez la formule suivante dans la cellule E2:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Appuyez sur Entrée pour voir la sortie.

Cette formule fonctionne sur les lignes similaires à la précédente.

  • OFFSET Crée une plage dynamique des trois dernières observations de la colonne B à l'aide de la fonction COUNT (= OFFSET (A1, 19, 0, -3)).
  • Cela peut être donné en sortie à la fonction MEDIAN. Qui trie ensuite les observations dans l'ordre croissant ou décroissant, puis sélectionne la valeur la plus moyenne comme vous le voyez dans la sortie (212, 07 $).

Formule Excel OFFSET - Exemple # 5

Ici, nous allons calculer l'année médiane en utilisant la fonction MEDIAN et OFFSET.

  • Utilisez la formule suivante pour la même chose dans la cellule E3:

= MEDIAN (OFFSET (A1, COUNT (A: A), 0, -3))

  • Cliquez sur Entrée pour voir la sortie.

La même chose s'est produite ici. La formule a pris une plage dynamique créée par la fonction OFFSET avec l'aide de COUNT. Et l'a utilisé comme argument pour une fonction MEDIANE qui a ensuite trié les valeurs de l'année dans l'ordre croissant ou décroissant, puis donné la sortie en 2017.

L'utilisation de la formule OFFSET consiste à créer les plages dynamiques. Il est toujours préférable de travailler sur des plages dynamiques plutôt que sur des plages statiques. Comme vous pourriez avoir des données qui sont ajoutées de temps en temps. Par exemple, Employee Master. Vos données de base des employés seront toujours augmentées de temps en temps car il doit y avoir de nouveaux employés qui se joignent. Chaque fois, utiliser la même formule n'a aucun sens. Au lieu de cela, créez une plage dynamique en utilisant la fonction OFFSET et voyez la magie. Cela vous fera certainement gagner du temps.

Choses dont il faut se rappeler

  • La fonction OFFSET renvoie uniquement une référence à une plage particulière de cellules dans Excel. Il ne déplace pas les cellules de leurs positions d'origine.
  • Par défaut, cette fonction fonctionne sur la partie supérieure (en termes de lignes) et sur le côté droit (en termes de colonnes). Cependant, il peut être fourni avec des arguments négatifs pour inverser la fonctionnalité par défaut. Comme l'argument de ligne -négative permet de compenser les données de bas en haut et l'argument col négatif permet de compenser les données vers la gauche.
  • La formule OFFSET peut ralentir la fonctionnalité Excel car il s'agit d'une formule qui est recalculée chaque fois qu'il y a un changement dans la feuille de calcul.
  • L'erreur #REF sera levée si votre plage est en dehors de la plage de votre classeur.
  • Toute autre fonction qui attend une référence peut utiliser la fonction OFFSET en son sein.

Articles recommandés

Ceci est un guide de la formule OFFSET dans Excel. Nous discutons ici comment utiliser la fonction OFFSET dans Excel avec des exemples pratiques et un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles suggérés -

  1. Comment encapsuler du texte dans Excel?
  2. Guide simple pour trier dans Excel
  3. Fonction LOOKUP dans Excel
  4. Comment utiliser la fonction FIND dans Excel?

Catégorie:

Conseils Excel