Fonction Excel OFFSET (Table des matières)

  • OFFSET dans Excel
  • Formule OFFSET dans Excel
  • Comment utiliser la fonction OFFSET dans Excel?

OFFSET dans Excel

La fonction OFFSET dans Excel aide l'utilisateur à renvoyer une cellule ou une plage de cellules qui est un non spécifié. des lignes et des colonnes d'une cellule. Nous pouvons spécifier le non. de rangées et non. de colonnes à retourner.

Voici la formule OFFSET dans Excel:

La fonction OFFSET dans Excel comprend les arguments suivants:

  • Référence: C'est l'argument à partir duquel nous voulons baser le décalage. Il peut s'agir d'une référence de cellule ou d'une plage de cellules adjacentes, sinon Offset renvoie la valeur #VALUE! Valeur d'erreur.
  • Lignes: c'est le non. de lignes à décaler. Si nous utilisons un nombre positif, il compense les lignes ci-dessous et si un nombre négatif est utilisé, il compense les lignes ci-dessus.
  • Colonnes: C'est pour non. de colonnes à compenser. Donc, c'est le même concept que pour les lignes, si nous utilisons un nombre positif, il se décale vers les colonnes de droite et si un nombre négatif est utilisé, alors, ses décalages vers les colonnes de gauche.
  • Hauteur: cet argument est facultatif. Ce devrait être un nombre positif. Il s'agit d'un nombre qui représente le nombre de lignes dans la référence renvoyée.
  • Largeur: il s'agit également d'un argument facultatif. Ce doit être un nombre positif. Il s'agit d'un nombre qui représente le nombre de colonnes dans la référence renvoyée.

Remarques:

  • La fonction OFFSET dans Excel renvoie le #REF! valeur d'erreur, si les lignes et les colonnes décalent la référence sur le bord de la feuille de calcul.
  • Dans la fonction OFFSET, elle est supposée avoir la même hauteur ou largeur qu'une référence si la hauteur ou la largeur est omise.
  • OFFSET renvoie une référence, il ne déplace en fait aucune cellule ou plage de cellules ni ne modifie la sélection. Il peut être utilisé avec n'importe quelle fonction attendant un argument de référence.

Comment utiliser la fonction OFFSET dans Excel?

La fonction OFFSET dans Excel est très simple et facile à utiliser. Laissez comprendre le fonctionnement de la fonction OFFSET dans Excel par un exemple de formule OFFSET.

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

Exemple 1

La formule de décalage renvoie une référence de cellule basée sur un point de départ, des lignes et des colonnes que nous spécifions. Nous pouvons le voir dans l'exemple ci-dessous:

= DÉCALAGE (A1, 3, 1)

La formule indique à Excel de considérer la cellule A1 comme point de départ (référence), puis de déplacer 3 lignes vers le bas (lignes) et 1 colonne vers la gauche (argument colonnes). Après cela, la formule OFFSET renvoie la valeur dans la cellule B4.

La photo de gauche montre que l'itinéraire de la fonction et la capture d'écran de droite montrent comment nous pouvons utiliser la formule OFFSET sur une base de données en temps réel. La différence entre les deux formules est que la seconde (à droite) inclut une référence de cellule (D1) dans l'argument de la ligne. Mais puisque la cellule D1 contient le numéro 1 et que le même nombre apparaît exactement dans l'argument lignes de la première formule, les deux renverraient un résultat identique - la valeur dans B2.

Exemple # 2 Utilisation de la fonction OFFSET pour Sum

Comme nous savons que la fonction OFFSET peut également être utilisée avec l'autre fonction d'Excel, nous verrons donc dans cet exemple avec la fonction SUM.

Dans l'image ci-dessus, supposons que nous devons trouver la somme des notes, nous pouvons également utiliser la fonction OFFSET.

La formule est ci-dessous:

Ainsi, dans la formule ci-dessus D5 est la première cellule où les données commencent, qui est une référence en tant que cellule de départ. Après cela, la valeur des lignes et des colonnes est 0, nous pouvons donc la mettre à 0, 0. Ensuite, il y a 4, ce qui signifie 4 lignes en dessous de la référence pour laquelle Excel doit calculer la somme, puis, enfin, c'est 1, cela signifie qu'il y a 1 colonne comme largeur.

Nous pouvons renvoyer l'image ci-dessus pour relier l'exemple expliqué.

Et voici le résultat:

Il suffit d'appuyer sur Entrée après avoir entré une formule dans la cellule requise, puis nous avons le résultat.

Choses à retenir sur la fonction OFFSET dans Excel

  • La fonction OFFSET dans Excel renvoie simplement une référence, elle ne déplace aucune cellule ou plage de cellules en réalité.
  • Si la formule OFFSET renvoie une plage de cellules, les lignes et les colonnes font toujours référence à la cellule supérieure gauche de la plage renvoyée.
  • La référence doit inclure une cellule ou une plage de cellules, sinon la formule renverra l'erreur #VALUE.
  • Si les lignes et les colonnes spécifiées sur le bord de la feuille de calcul, la formule de décalage dans Excel renverra le #REF! Erreur.
  • La fonction OFFSET peut être utilisée dans n'importe quelle autre fonction Excel qui accepte une référence de cellule ou de plage dans ses arguments.

Par exemple, si nous utilisons la formule = OFFSET (A1, 3, 1, 1, 3) seule, cela donnera une #VALUE! Erreur, car une plage de retour (1 ligne, 3 colonnes) ne tient pas dans une cellule. Cependant, si nous l'assemblons dans la fonction SUM comme ci-dessous:

= SUM (OFFSET (A1, 3, 1, 1, 3))

La formule de décalage renvoie la somme des valeurs sur une ligne par une plage de 3 colonnes qui est 3 lignes en dessous et 1 colonne à droite de la cellule A1, c'est-à-dire le total des valeurs dans les cellules B4: D4.

Comme nous pouvons le voir, le total de 36 + 63 + 82 de Mar en surbrillance est égal à 181, ce qui est arrivé à la suite dans G2 après application de la somme et de la formule de décalage. (surligné en jaune)

Utilisation de la fonction OFFSET dans Excel

Comme nous avons vu que ce que la fonction OFFSET fait réellement à l'aide de l'exemple ci-dessus, nous pouvons avoir des questions qui expliquent pourquoi déranger la formule OFFSET chantée qui est un peu complexe, pourquoi ne pas simplement utiliser une référence directe comme sum ou B4: D4?

La fonction OFFSET dans Excel est très bonne pour ci-dessous:

  • Obtention de la plage à partir de la cellule de départ:

Parfois, nous ne connaissons pas l'adresse exacte ou réelle de la plage, nous ne savons que d'où elle part d'une certaine cellule. Dans ce cas, nous pouvons utiliser la fonction OFFSET, qui est facile à utiliser.

  • Création d'une plage dynamique:

Les références comme l'exemple B1: C4 ci-dessus sont statiques, cela signifie qu'elles se réfèrent toujours à une plage donnée ou fixe. Mais dans certains cas, les tâches sont plus faciles à effectuer avec des plages dynamiques. Cela est particulièrement utile lorsque nous travaillons avec des données changeantes. Par exemple, nous pourrions avoir une feuille de calcul où une nouvelle ligne ou colonne est insérée ou ajoutée chaque jour / semaine / mois, dans ce cas, la fonction OFFSET vous aidera.

Limitations et alternatives de la fonction OFFSET dans Excel

Comme chaque formule dans Excel a ses propres limitations et alternatives, nous avons vu comment et quand utiliser la fonction OFFSET dans Excel.

Voici les limites critiques de la fonction OFFSET dans Excel:

  • Fonction gourmande en ressources:

Cela signifie que chaque fois qu'il y a un changement dans les données source, la formule sera recalculée pour l'ensemble entier, ce qui occupera Excel plus longtemps. S'il y a une petite feuille de calcul, cela n'affectera pas beaucoup mais s'il y a un certain nombre de feuilles de calcul, Excel peut prendre du temps pour recalculer.

  • Difficulté de revue:

Comme nous le savons, les références renvoyées par la fonction Offset sont dynamiques ou changeantes et peuvent contenir une grande formule, qui peut être difficile à corriger ou à modifier selon les exigences.

Alternatives de la fonction OFFSET dans Excel:

  • Fonction INDEX:

La fonction INDEX dans Excel peut également être utilisée pour créer une plage dynamique de références. Ce n'est pas exactement la même chose que OFFSET. Mais comme OFFSET, la fonction INDEX n'est pas très volatile, donc elle ne ralentira pas Excel qui est une limitation pour OFFSET.

  • Fonction INDIRECTE:

Nous pouvons également utiliser la fonction INDIRECT pour créer une plage dynamique de références à partir de nombreuses sources comme les valeurs de cellule , le texte et les plages nommées.

Articles recommandés

Cela a été un guide pour OFFSET dans Excel. Ici, nous discutons de la formule OFFSET dans Excel et 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. MS Excel: fonction LOOKUP
  2. Meilleur exemple de fonction Excel XIRR
  3. Guide étonnant sur la fonction NOT dans Excel
  4. IPMT dans Excel avec des exemples

Catégorie:

Conseils Excel