Comment utiliser la fonction OFFSET dans Excel

La fonction OFFSET d'Excel vous permet de créer une référence qui est, littéralement,compenser à partir d'un point de départ. Il permet aux références de s'ajuster dynamiquement aux modifications apportées aux données et à la structure de votre feuille de calcul.

La syntaxe OFFSET

Avant de voir comment cela fonctionne en pratique, passons en revue la syntaxe. La fonction OFFSET a cinq arguments :

=OFFSET(A,B,C,D,E)

où

• UN est la référence de cellule du point de départ,
• B est le nombre de lignes à descendre à partir du point UN,
• C est le nombre de colonnes à déplacer à partir du point UN,
• D (facultatif) est le nombre de lignes (hauteur) affichées dans le résultat, et
• E (facultatif) est le nombre de colonnes (largeur) affichées dans le résultat.

Valeurs B, C, Det E Il peut s'agir de références numériques ou de cellules.

Utiliser une valeur négative dans
B
créerait un décalage vers le haut et une valeur négative dans
C
créerait un décalage vers la gauche.

Un exemple de base de OFFSET

Pour illustrer le fonctionnement de OFFSET, j'utiliserai un exemple très basique et non pratique. Dactylographie

=OFFSET(A1,2,3,2,3)

dans la cellule A6 utiliserait la cellule A1 comme point de départ (UN), descendez de 2 lignes (B), se déplacer sur 3 colonnes (C), et renvoie une série de valeurs de 2 cellules de haut (D) et 3 cellules de large (E) :

Si je n'avais pas tapé de valeurs D et E dans ma formule, le résultat aurait par défaut fait référence à la cellule D3 (Cerf) seule. En effet, Excel suppose que le résultat est de la même taille que la référence si vous ne spécifiez pas la hauteur et la largeur.

Sur la base de ce principe, vous pouvez également définir la taille du résultat dans la limite de la valeur UN. Par exemple, en tapant

=OFFSET(A1:A3,2,3)

renverrait un résultat de trois cellules de haut, car il s'agit de la taille du point de départ UN.

Utiliser OFFSET dans le monde réel

Maintenant, je vais vous montrer comment OFFSET peut être utilisé dans des exemples concrets.

Exemple concret 1

Ici, j'ai dix salariés, et le nombre d'unités qu'ils ont vendues sur trois semaines. Je vais utiliser OFFSET pour créer trois rapports de données.

Dans le premier rapport de données (violet), je souhaite qu'Excel m'indique le nombre d'unités vendues par un employé lorsque je saisis son identifiant et un numéro de semaine. Pour y parvenir, dans la cellule H4, je vais taper

=OFFSET(B1,H2,H3)

où B1 est le point de départ, H2 indique à Excel le nombre de lignes qu'il doit décaler par rapport au point de départ (en d'autres termes, le numéro d'employé), et H3 indique à Excel le nombre de colonnes à décaler par rapport au point de départ (en d'autres termes, le numéro de la semaine). Remarquez comment j'ai utilisé les références de cellules ici, car cela signifie que les données que la fonction OFFSET extrait de ma table peuvent être dynamiques.

Je commence par la cellule B1 comme première entrée car je veux que la première semaine soit la première cellule à droite de ce point de départ. Ainsi, chaque fois que vous utilisez la fonction OFFSET, réfléchissez bien à l’endroit où devrait être votre point de départ.

Après avoir tapé la formule ci-dessus dans la cellule H4, je tape 6 dans la zone ID (cellule H2), et 2 dans la case semaine (cellule H3). Cela me dira combien d'unités Ollie a vendues au cours de la semaine 2, car la fonction OFFSET extrait les données de la cellule six lignes vers le bas et deux colonnes en face de mon point de départ, la cellule B1.

Je peux désormais modifier les valeurs des cellules H2 et H3 à tout moment pour extraire n'importe quel chiffre individuel du tableau.

Ensuite, je souhaite créer un rapport d'employé dans le tableau bleu. Lorsque je saisis un identifiant d'employé, Excel m'indiquera automatiquement le nom de l'employé (en utilisant la fonction RECHERCHEV), le nombre total de semaines de données dont nous disposons (en utilisant la fonction COUNTIF) et le total et la moyenne des unités vendues par cet employé (en utilisant SOMME et MOYENNE avec OFFSET).

Tout d’abord, je vais saisir un identifiant d’employé dans la cellule H7 pour commencer. C'est parti pour la carte d'employé 3qui est Jason. Ensuite, j'utiliserai la fonction RECHERCHEV pour renvoyer le nom de Jason dans la cellule H8 :

=VLOOKUP(H7,A2:B11,2)

Maintenant, je vais utiliser la fonction COUNTIF avec un caractère générique pour indiquer à Excel de compter combien de cellules de la ligne 1 contiennent le mot « Semaine » suivi d'un nombre :

=COUNTIF(1:1,"Week*")

Je peux désormais utiliser ces informations pour calculer le nombre total et moyen d'unités vendues par Jason. Tout d'abord, dans la cellule H10, je vais taper

=SUM(OFFSET(B1,H7,1,1,H9))

où B1 est mon point de départ, H7 est le nombre de lignes à décaler vers le bas (ID employé), la première 1 est le nombre de colonnes à décaler vers la droite, la seconde 1 est la hauteur du résultat, et H9 est la largeur du résultat (le nombre total de semaines). Cependant, étant donné que OFFSET est intégré à la fonction SOMME, le résultat ne sera qu'une seule cellule.

Je peux copier et coller la même formule dans la cellule H11, mais changer SUM en MOYENNE, puisque j'utilise les mêmes références.

=AVERAGE(OFFSET(B1,H7,1,1,H9))

Je peux maintenant modifier la valeur de la cellule H7 pour générer un rapport pour tout autre employé de mon tableau de données. De plus, si je devais ajouter une semaine supplémentaire à mes données, la cellule H9 passerait automatiquement à 4 et les calculs ultérieurs dans les cellules H10 et H11 seraient également mis à jour.

Enfin, je souhaite créer un rapport hebdomadaire dans le tableau orange. Cela m'obligera à saisir un numéro de semaine, et Excel générera ensuite le total et la moyenne des unités vendues pour cette semaine. je vais commencer par taper 1 dans la cellule H14, donc Excel produira les données de la semaine 1. Ensuite, dans la cellule H15, je taperai

=SUM(OFFSET(B1,1,H14,10,1))

où B1 est le point de départ, le premier 1 est le nombre de lignes à décaler vers le bas, H14 est le nombre de colonnes à décaler vers la droite (numéro de semaine), 10 est la hauteur du résultat, et la seconde 1 est la largeur du résultat. Cependant, étant donné que OFFSET est intégré à la fonction SUM, le résultat ne sera qu'une seule cellule.

Enfin, je peux copier et coller cette formule dans la cellule H15 et changer SUM en MOYENNE.

=AVERAGE(OFFSET(B1,1,H14,10,1))

Exemple concret 2

Dans cet exemple, je vais vous montrer comment utiliser OFFSET avec un tableau formaté et à quoi il ressemble lorsqu'il est utilisé pour renvoyer un résultat dans plusieurs cellules.

Ici, j'ai les ventes et les bénéfices actuels jusqu'à présent cette année dans un tableau formaté, et je souhaite que mon tableau de rapport sur la droite affiche individuellement le total des bénéfices des trois derniers mois, ainsi que le bénéfice moyen et total pour la même période.

Tout d'abord, j'ai besoin d'Excel pour déterminer combien de mois complets se sont écoulés jusqu'à présent, de sorte que lorsque j'utilise OFFSET, je puisse utiliser ce chiffre pour indiquer à Excel à quelle distance du point de départ je dois compenser. Pour ce faire, dans la cellule F1, je vais taper

=MONTH(TODAY())

qui affichera le numéro du mois en cours. Dans ce cas, nous sommes le 11 novembre.

Nous pouvons désormais utiliser ce numéro de mois pour afficher les bénéfices des trois derniers mois. Dans la cellule F3, je tape

=OFFSET(

et cliquez sur la cellule A1, mon point de départ. Cela ajoutera automatiquement l'en-tête de colonne du mois du tableau formaté dans la formule :

=OFFSET(Table1((#Headers),(Month))

Maintenant, je vais ajouter une virgule et continuer ma formule OFFSET. L'étape suivante consiste à indiquer à Excel le nombre de lignes à décaler vers le bas. Ce sera le mois en cours (cellule F1) moins trois :

=OFFSET(Table1((#Headers),(Month)),SUM(F1-3),

Enfin, je dirai à Excel que nous devons décaler deux colonnes vers la droite, et le résultat sera de trois lignes de haut et d'une colonne de large.

=OFFSET(Table1((#Headers),(Month)),SUM(F1-3),2,3,1)

Remarquez comment les cellules F4 et F5 contiennent des données, même si la formule n'est qu'en F3. C'est ce qu'on appelle un tableau dispersé, car les données ont débordé sur la cellule active.

Désormais, lorsque le mois passe au 12 (décembre), Excel déplacera automatiquement ce résultat d'une cellule vers le bas, car la valeur dans la cellule F1 aura augmenté d'une unité.

Je peux alors compléter mon tableau en tapant

=AVERAGE(F3#)

dans la cellule F7, et

=SUM(F3#)

dans la cellule F8.

Le symbole octothorp (#) est la manière dont Excel calcule un tableau renversé.

Choses à retenir

Il y a deux choses importantes à retenir lors de l'utilisation de OFFSET :

1. OFFSET est une fonction volatile, ce qui signifie qu'elle cherche toujours à se mettre à jour. Cela pourrait entraîner des problèmes de performances s'il est utilisé dans une feuille de calcul particulièrement volumineuse ou sur un ordinateur à faible mémoire.
2. Tous les exemples ci-dessus sont utilisés pour illustrer les différentes manières d'utiliser la fonction OFFSET. Il peut exister d'autres moyens plus efficaces pour créer les mêmes résultats, par exemple en utilisant la fonction INDIRECT. Cependant, une fois que vous aurez compris le fonctionnement des fonctions, vous pourrez décider laquelle est la mieux adaptée à votre situation !

---

Personnellement, j'utilise la fonction OFFSET pour enregistrer et analyser les données de mon équipe de football préférée. Après tout, Excel n'est pas réservé aux comptables : vous pouvez l'utiliser à la maison en parallèle de vos loisirs !