Comment utiliser la fonction OFFSET dans Excel

La fonction OFFSET vous permet de faire indirectement référence à une cellule ou à une plage de cellules dans Excel. Avec OFFSET, vous pouvez sélectionner un point de référence de début, puis saisir une adresse vers les cellules cibles plutôt que de les décrire directement.

Cela rend la fonction OFFSET exceptionnellement bénéfique dans les feuilles de calcul Excel dynamiques. Vous pouvez utiliser la fonction OFFSET par elle-même, mais sa véritable possibilité apparaît juste lorsque vous l’imbriquez dans diverses autres fonctions d’Excel. Continuez à lire pour découvrir tout sur OFFSET dans Excel avec trois exemples.

Qu’est-ce que la fonction OFFSET dans Excel ?

La fonction OFFSET renvoie un renvoi à une cellule ou à une série de cellules. OFFSET prend 5 spécifications, et aussi sa structure de phrase est comme ci-dessous :

OFFSET va certainement prendre la position de l’élément référence et, après cela, se déplacer à partir de là par la variété de rangées et cols , et ensuite retourner la référence de la cellule de destination.

Si le hauteur et largeur sont définis à plus d’un, après quoi une série de cellules car emplacement sera certainement retournée. La hauteur ainsi que la taille sont deux critères facultatifs. Si vous les laissez vides, OFFSET les lira comme 1 ainsi que retournera une référence de cellule solitaire.

Comment utiliser la fonction OFFSET dans Excel

Pour utiliser OFFSET dans Excel, vous devez saisir les 3 paramètres appelés. Dès que vous définissez la cellule de recommandation, OFFSET se déplacera certainement de cette cellule vers les cellules situées en dessous par la variété de la.lignes critère. Il se déplacera certainement vers la droite par le nombre de lignes du critère . cols spécification.

À partir de là, OFFSET renverra une recommandation à la cellule de destination. Si vous définissez des spécifications pour la hauteur et également largeur de même, OFFSET renverra certainement des recommandations à une série de cellules, où la cellule de destination initiale sera la cellule supérieure gauche.

Cela pourrait sembler rendu complexe sur le papier, pourtant c’est beaucoup plus facile à comprendre en action. Voyons exactement ce que fait la fonction OFFSET avec quelques exemples.

1. Exemple de la fonction OFFSET : Se référer à une seule cellule

Pour commencer, utilisons la fonction OFFSET par elle-même pour obtenir une compréhension de la façon dont elle parcourt la feuille de calcul. Dans cet exemple, nous avons un tas de chiffres dans une feuille de calcul Excel. Nous allons très probablement utiliser la fonction OFFSET pour décrire C4.

Lorsque la fonction OFFSET est utilisée simplement, elle donnera certainement le résultat du contenu de la cellule de destination. Nous devrions donc obtenir le contenu de la cellule C4, qui est le nombre cinq, lorsque nous entrons la formule. Allons-y.

1. Sélectionnez une cellule pour entrer la formule. Nous allons utiliser la cellule G 1.
2. Dans la barre de formule, saisissez la formule ci-dessous : Cette formule entre en contact avec la cellule OFFSET pour absorber le réglage de la cellule A 1 , après quoi on relocalise vers le bas par 3 cellules, et après cela relocaliser à droite par 2 cellules pour atteindre la cellule C 4.
3. Appuyez sur Enter.

Une fois que vous avez appuyé sur Enter et aussi la formule est établie, la cellule retournera certainement 5. Il s’agit du nombre de la cellule C4, donc votre formule a fonctionné ! Vous pouvez également utiliser la fonction d’examen pour mieux comprendre les solutions Excel, telles que OFFSET.

2. Exemple de la fonction OFFSET : Se référer à une plage de cellules

En utilisant les deux critères optionnels, l’élévation ainsi que la largeur, vous pouvez de la même manière renvoyer une variété de cellules avec la fonction OFFSET dans Excel. Reprenons là où nous nous sommes arrêtés lors de la dernière instance.

Cette fois, nous allons retourner les cellules C4 à D9 en utilisant la fonction OFFSET dans Excel. Comme précédemment, nous allons très probablement utiliser A1 comme facteur de recommandation de début.

1. Sélectionnez la cellule dans laquelle vous voulez entrer la formule. Nous sommes plus que susceptibles d’utiliser la cellule. G 1 à nouveau.
2. Dans la barre de formule, saisissez la formule indiquée ci-dessous : Cette formule fonctionne de manière similaire à la précédente, si ce n’est qu’une fois qu’elle atteint la cellule d’emplacement (C4), elle absorbera certainement 6 lignes et aussi 2 colonnes par opposition à la cellule cible seule. C4 sera la première cellule du tout nouveau tableau.
3. Appuyez sur Enter.

Maintenant, la plage de cellules C4 à D9 doit apparaître là où vous avez mis la formule. Le résultat sera certainement une variété.

3. Exemple de la fonction OFFSET : Formules composées

Les deux instances précédentes étaient un échauffement pour voir exactement comment OFFSET fonctionne dans Excel. OFFSET est généralement imbriqué avec d’autres fonctions Excel dans des situations pratiques. Montrons-le avec un nouvel exemple.

Dans cet exemple, nous avons les revenus d’un emploi secondaire tout au long des années 2017 à 2021. Dans la partie supérieure, nous avons une cellule qui est destinée à montrer les revenus globaux dans les années prévues à partir de 2021.

L’objectif ici est de créer une feuille de calcul dynamique en utilisant OFFSET dans Excel. Lorsque vous changez le nombre d’années pour le total, la formule calculant cette valeur doit également évoluer.

Pour atteindre cet objectif, nous allons très probablement utiliser la fonction OFFSET avec la fonction SUM. Plutôt que de se référer directement aux cellules, nous allons probablement utiliser la fonction OFFSET. De cette façon, nous pouvons obtenir dynamiquement la variété de cellules qui seront certainement incorporées dans SUM.

Dans cet exemple, l’année 2021 se trouve dans la cellule H5, et la variété d’années pour le total se trouve dans la cellule C1. Nous avons en fait utilisé un formatage personnalisé dans la cellule contenant les années. La valeur réelle est le nombre d’années, et le texte Years est un suffixe. Commençons par créer une formule pour calculer le total. Nous allons probablement utiliser la fonction SUM d’Excel.

1. Sélectionnez la cellule où vous voulez calculer le global.
2. Dans la barre de formule, entrez la formule indiquée ci-dessous : Cette formule comprend . OFFSET et SOMME. Dans l’étape initiale, OFFSET sera certainement le plus probable à la cellule. H 5. À partir de là, il se déplacera certainement par. rangs et colonnes, et ensuite il retournera certainement une variété de cellules à partir de H 5. Ce choix sera 1 cellule en hauteur, et aussi – C1 cellules en largeur. Cela indique que OFFSET renverra une plage commençant par H 5 et se déplaçant vers la gauche de C 1 cellules.
3. Dans la dernière action, la fonction SOMME va additionner les chiffres de la variété entre eux et aussi les sortir.
4. Appuyez sur Entrez.

Le nombre que vous obtenez comme total actuel dépend de la variété d’années que vous avez entrée. Si la variété d’années est préparée à 1, alors vous obtiendrez certainement le montant total pour l’année 2021 seulement.

Remarquez que le total se met à jour instantanément lorsque vous transformez la variété d’années. Si vous modifiez la variété d’années à 7, la fonction SUM calculera le montant total de 2015 à 2021.

Feuilles de calcul dynamiques avec OFFSET

La fonction OFFSET d’Excel a plusieurs spécifications, ce qui peut causer une complication en soi, mais une fois que vous aurez appris à l’utiliser, vous apprendrez que, comme plusieurs autres fonctions d’Excel, OFFSET est une fonction conviviale.

Bien qu’il n’y ait pas beaucoup d’utilité à OFFSET en soi, lorsqu’elle est associée à d’autres fonctions d’Excel, OFFSET vous donne le pouvoir de développer des feuilles de calcul dynamiques aussi bien que sophistiquées.