Téléchargez l'exemple de classeur
Ce didacticiel montre comment utiliser le Fonction de décalage Excel dans Excel pour créer un décalage de référence à partir d'une cellule initiale.
Présentation de la fonction OFFSET
La fonction OFFSET démarre avec une référence de cellule définie et renvoie une référence de cellule avec un nombre spécifique de lignes et de colonnes décalées par rapport à la référence d'origine. Les références peuvent être une cellule ou une plage de cellules. Offset permet également de redimensionner la référence d'un nombre donné de lignes/colonnes.
(Remarquez comment les entrées de formule apparaissent)
Syntaxe et entrées de la fonction IFERROR :
| 1 | =OFFSET(référence,lignes,cols,hauteur,largeur) |
référence - La référence de cellule initiale à partir de laquelle vous souhaitez effectuer le décalage.
Lignes - Le nombre de lignes à décaler.
cols - Le nombre de colonnes à décaler.
la taille - OPTIONNEL : Ajustez le nombre de lignes dans la référence.
largeur - OPTIONNEL : Ajustez le nombre de colonnes dans la référence.
Qu'est-ce que la fonction OFFSET ?
La fonction OFFSET est l'une des fonctions de feuille de calcul les plus puissantes car elle peut être assez polyvalente dans ce qu'elle crée. Il donne à l'utilisateur la possibilité de définir une cellule ou une plage dans une variété de positions et de tailles.
ATTENTION : La fonction OFFSET fait partie des fonctions volatiles. La plupart du temps, lorsque vous travaillez dans votre feuille de calcul, l'ordinateur ne recalculera une formule que si les entrées ont changé de valeur. Une fonction volatile, cependant, recalcule tous fois que vous apportez une modification à n'importe quelle cellule. Des précautions doivent être prises pour s'assurer que vous ne causez pas un temps de recalcul important en raison d'une utilisation excessive de la fonction volatile ou du fait que de nombreuses cellules dépendent du résultat d'une fonction volatile.
Exemples de lignes de base
A chaque utilisation de la fonction OFFSET, vous devez donner un point de départ, ou un point d'ancrage. Regardons ce tableau pour aider à comprendre ceci :
Nous utiliserons "Bob" dans la cellule B3 comme point d'ancrage. Si nous voulions saisir la valeur juste en dessous (Charlie), nous dirions que nous voulons décaler la ligne de 1. Notre formule ressemblerait à
| 1 | =DÉCALAGE(B3, 1) |
Si nous voulions passer à la vitesse supérieure, ce serait un changement négatif. Vous pouvez penser à cela car le numéro de ligne diminue, nous devons donc soustraire. Ainsi, pour obtenir la valeur ci-dessus (Adam), nous écririons
| 1 | =DÉCALAGE(B2, -1) |
Exemples de colonnes de base
Poursuivant l'idée de l'exemple précédent, nous allons ajouter une autre colonne à notre table.
Si nous voulions prendre le professeur pour Bob, nous pourrions utiliser la formule
| 1 | =DÉCALAGE(B2, 0, 1) |
Dans ce cas, nous avons dit que nous voulions décaler zéro ligne (c'est-à-dire rester sur la même ligne) mais que nous voulions décaler 1 colonne. Pour les colonnes, un nombre positif signifie un décalage vers la droite et des nombres négatifs signifie un décalage vers la gauche.
DÉCALAGE et CORRESPONDANCE
Supposons que vous disposiez de plusieurs colonnes de données et que vous souhaitiez donner à l'utilisateur la possibilité de choisir la colonne à partir de laquelle récupérer les résultats. Vous pouvez utiliser la fonction INDEX ou vous pouvez utiliser OFFSET. Étant donné que MATCH renverra la position relative d'une valeur, nous devrons nous assurer que le point d'ancrage se trouve à gauche de notre première valeur possible. Considérez la disposition suivante :
En B2, nous écrirons cette formule :
| 1 | =DÉCALAGE(B2, 0, CORRESPONDANCE(A2, $C$1:$F$1, 0)) |
Le MATCH va chercher "Fév" dans la gamme C1:F1 et le trouver dans le 2sd cellule. L'OFFSET déplacera alors 1 colonne vers la droite de B2 et saisira la valeur souhaitée de 9. Notez que l'OFFSET n'a aucun problème à utiliser la même cellule qui contient la formule comme point d'ancrage.
REMARQUE : Cette technique peut être utilisée en remplacement de RECHERCHEV ou RECHERCHEH lorsque vous souhaitez renvoyer une valeur à partir de la gauche/au-dessus de votre plage de recherche. C'est parce que OFFSET peut faire des décalages négatifs.
OFFSET pour obtenir une plage
Vous pouvez utiliser les 4e et 5e arguments dans la fonction OFFSET pour renvoyer une plage plutôt qu'une seule cellule. Supposons que vous vouliez additionner 3 colonnes dans ce tableau.
| 1 | =MOYENNE(DECALAGE(A1, CORRESPONDANCE(F2,A2:A5,0),1,1,3)) |
Dans F2, nous avons sélectionné le nom d'un élève pour lequel nous voulons récupérer ses notes moyennes aux tests. Pour ce faire, nous utiliserons la formule
| 1 | =MOYENNE(DECALAGE(A1, CORRESPONDANCE(F2,A2:A5,0),1,1,3)) |
Le MATCH va rechercher notre nom dans la colonne A et retourner la position relative, qui est 3 dans notre exemple. Voyons comment cela sera évalué. Tout d'abord, l'OFFSET va aller vers le bas 3 lignes de A1 et 1 colonne jusqu'au droit de A1. Cela nous place dans la cellule B3.
| 1 | =MOYENNE(DECALAGE(A1, 3, 1, 1, 3)) |
Ensuite, nous allons redimensionner la plage. La nouvelle plage aura B3 comme cellule en haut à gauche. Ce sera 1 rangée de haut et 3 colonnes de haut, nous donnant la plage B4:D4.
| 1 | =MOYENNE(DECALAGE(A1,3, 1, 1, 3)) |
Notez que même si vous pouvez légitimement mettre des valeurs négatives dans les arguments de décalage, vous ne pouvez utiliser que des valeurs non négatives dans les arguments de dimensionnement.
A la fin, notre fonction MOYENNE voit :
| 1 | =MOYENNE(B4:D4) |
Ainsi, nous obtenons notre solution de 86.67
OFFSET avec SUM dynamique
Étant donné que OFFSET est utilisé pour rechercher une référence, plutôt que de pointer directement vers la cellule, il est plus utile lorsque vous traitez des données auxquelles des lignes ont été ajoutées ou supprimées. Considérez le tableau suivant avec un total en bas
| 1 | =SOMME(B2:B4) |
Si nous avions utilisé une formule SUM de base ici de "=SUM(B2:B4)" puis inséré une nouvelle ligne pour ajouter un enregistrement pour Bill, nous aurions la mauvaise réponse
Au lieu de cela, réfléchissons à la façon de résoudre ce problème du point de vue de Total. Nous voulons vraiment tout saisir de la cellule B2 à la cellule juste au dessus de notre total. La façon dont nous pouvons écrire cela dans une formule est de faire un décalage de ligne de -1. Ainsi, nous utilisons ceci comme formule pour notre total dans la cellule B5 :
| 1 | =SOMME(B2:OFFSET(B5,-1,0)) |
Cette formule fait ce que nous venons de décrire : commencez à B2 et allez à 1 cellule au-dessus de notre cellule totale. Vous pouvez voir comment, après avoir ajouté les données de Bill, notre total est correctement mis à jour.
OFFSET pour obtenir les N derniers éléments
Disons que vous enregistrez des ventes mensuelles mais que vous souhaitez pouvoir consulter les 3 derniers mois. Plutôt que d'avoir à mettre à jour manuellement vos formules pour continuer à ajuster au fur et à mesure que de nouvelles données sont ajoutées, vous pouvez utiliser la fonction OFFSET avec COUNT.
Nous avons déjà montré comment utiliser OFFSET pour saisir une plage de cellules. Pour déterminer combien de cellules nous devons déplacer, nous utiliserons COUNT pour trouver combien Nombres sont dans la colonne B. Regardons notre exemple de tableau.
| 1 | =SUM(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
Si nous commencions à B1 et décalions 4 lignes (le nombre de nombres dans la colonne B), nous nous retrouverions au bas de notre fourchette, B5. Cependant, comme OFFSET ne peut pas redimensionner avec une valeur négative, nous devons faire quelques ajustements pour que nous nous retrouvions en B3. L'équation générale pour cela va être de faire
| 1 | COMPTE(… ) - N + 1 |
Nous prenons le nombre de colonnes entières, soustrayons le nombre que nous voulons retourner (puisque nous allons redimensionner pour les saisir), puis ajoutons 1 (puisque nous commençons essentiellement notre décalage à la position zéro).
Ici, vous pouvez voir que nous avons configuré une plage pour obtenir la somme, la moyenne et le maximum des N derniers mois. Dans E1, nous avons entré la valeur de 3. Dans E2, notre formule est
| 1 | =SUM(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
La section en surbrillance est notre équation générale que nous venons de discuter. Nous n'avons pas besoin de décaler de colonnes. Nous allons ensuite redimensionner la plage à 3 cellules de haut (déterminée par la valeur dans E1) et 1 colonne de large. Notre SOMME prend alors cette plage et nous donne le résultat de 1 850 $. Nous avons également montré que vous pouvez calculer la moyenne de max de cette même plage en basculant simplement la fonction externe de SUM à tout ce que la situation exige.
OFFSET listes de validation dynamique
En utilisant la technique montrée dans le dernier exemple, nous pouvons également créer des plages nommées qui pourraient être utilisées dans la validation des données ou des graphiques. Cela peut être utile lorsque vous souhaitez configurer une feuille de calcul mais que vous vous attendez à ce que nos listes/données changent de taille. Disons que notre magasin commence à vendre des fruits, et nous avons actuellement 3 choix.
Pour créer une liste déroulante de validation des données que nous pouvons utiliser ailleurs, nous allons définir la plage nommée MyFruit comme
| 1 | =$A$2:OFFSET($A$1, COUNTA($A:$A)-1, 0) |
Au lieu de COUNT, nous utilisons COUNTA car nous avons affaire à des valeurs de texte. Pour cette raison, notre COUNTA sera supérieur d'un car il comptera la cellule d'en-tête dans A1 et donnera une valeur de 4. Si nous décalons cependant de 4 lignes, nous nous retrouverions dans la cellule A5 qui est vide. Pour ajuster cela, nous soustrayons le 1.
Maintenant que nous avons notre configuration de plage nommée, nous pouvons configurer une validation de données dans la cellule C4 en utilisant un type de liste, avec la source :
| 1 | =MonFruit |
Notez que la liste déroulante ne montre que nos trois éléments actuels. Si nous ajoutons ensuite d'autres éléments à notre liste et revenons à la liste déroulante, la liste affiche tous les nouveaux éléments sans que nous ayons à modifier aucune des formules.
Précautions à prendre lors de l'utilisation d'OFFSET
Comme mentionné au début de cet article, OFFSET est une fonction volatile. Vous ne le remarquerez pas si vous l'utilisez dans quelques cellules, mais si vous commencez à l'impliquer dans des centaines de calculs et que vous remarquerez rapidement que votre ordinateur passe un temps considérable à recalculer chaque fois que vous apportez des modifications. .
De plus, comme OFFSET ne nomme pas directement les cellules qu'il examine, il est plus difficile pour les autres utilisateurs de revenir plus tard et de modifier vos formules si nécessaire.
Au lieu de cela, il serait conseillé d'utiliser des tableaux (introduits dans Office 2007) qui permettent des références structurelles. Celles-ci ont aidé les utilisateurs à donner une référence unique dont la taille s'ajustait automatiquement à mesure que de nouvelles données étaient ajoutées ou supprimées.
L'autre option à utiliser à la place de OFFSET est la puissante fonction INDEX. INDEX vous permet de construire toutes les plages dynamiques que nous avons vues dans cet article sans le problème d'être une fonction volatile.
Notes complémentaires
Utilisez la fonction OFFSET pour renvoyer une valeur de cellule (ou une plage de cellules) en décalant un nombre donné de lignes et de colonnes à partir d'une référence de départ. Lorsqu'elles ne recherchent qu'une seule cellule, les formules OFFSET atteignent le même objectif que les formules INDEX, en utilisant une technique légèrement différente. La véritable puissance de la fonction OFFSET réside dans sa capacité à sélectionner une plage de cellules à utiliser dans une autre formule.
Lorsque vous utilisez la fonction OFFSET, vous définissez une cellule ou une plage de cellules de départ initiale. Ensuite, vous indiquez le nombre de lignes et de colonnes à décaler à partir de cette cellule initiale. Vous pouvez également redimensionner la plage; ajouter ou soustraire des lignes ou des colonnes.
Revenir à la liste de toutes les fonctions dans Excel
DÉCALAGE dans Google Sheets
La fonction OFFSET fonctionne exactement de la même manière dans Google Sheets que dans Excel :
