Fonction Excel OFFSET (Exemple + Vidéo)
Quand utiliser la fonction Excel OFFSET
La fonction Excel OFFSET peut être utilisée lorsque vous souhaitez obtenir une référence qui compense le nombre spécifié de lignes et de colonnes à partir du point de départ.
Ce que ça renvoie
Il renvoie la référence vers laquelle la fonction OFFSET pointe.
Syntaxe
=OFFSET(référence, lignes, cols, [hauteur], [largeur])
Arguments d'entrée
- référence - La référence à partir de laquelle vous souhaitez effectuer le décalage. Il peut s'agir d'une référence de cellule ou d'une plage de cellules adjacentes.
- Lignes - le nombre de lignes à décaler. Si vous utilisez un nombre positif, il se décale sur les lignes ci-dessous, et si un nombre négatif est utilisé, il se décale sur les lignes ci-dessus.
- cols - le nombre de colonnes à décaler. Si vous utilisez un nombre positif, il se décale vers les colonnes de droite, et si un nombre négatif est utilisé, il se décale vers les colonnes de gauche.
- [la taille] - il s'agit d'un nombre qui représente le nombre de lignes dans la référence renvoyée.
- [largeur] - il s'agit d'un nombre qui représente le nombre de colonnes dans la référence renvoyée.
Comprendre les bases de la fonction Excel OFFSET
La fonction Excel OFFSET est probablement l'une des fonctions les plus déroutantes d'Excel.
Prenons un exemple simple d'un jeu d'échecs. Il y a une pièce aux échecs appelée une tour (également connue sous le nom de tour, marquis ou recteur).
[Image avec l'aimable autorisation: Wonderful Wikipedia]
Maintenant, comme toutes les autres pièces d'échecs, une tour a un chemin fixe sur lequel elle peut se déplacer sur l'échiquier. Il peut aller tout droit (à droite/gauche ou haut/bas du plateau), mais il ne peut pas aller en diagonale.
Par exemple, supposons que nous ayons un échiquier dans Excel (comme indiqué ci-dessous), dans une case donnée (D5 dans ce cas), la tour ne peut aller que dans les quatre directions mises en évidence.
Maintenant, si je vous demande de prendre la tour de sa position actuelle à celle surlignée en jaune, que diriez-vous à la tour ?
Vous lui demanderez de faire deux pas vers le bas et deux pas vers la droite… n'est-ce pas ?
Et c'est une approximation proche du fonctionnement de la fonction OFFSET.
Voyons maintenant ce que cela signifie dans Excel. Je veux commencer par la cellule D5 (qui est l'endroit où se trouve la tour), puis descendre de deux rangées et deux colonnes vers la droite et récupérer la valeur de la cellule là-bas.
La formule serait :
=OFFSET(d'où commencer, combien de lignes vers le bas, combien de colonnes vers la droite)
Comme vous pouvez le voir, la formule de l'exemple ci-dessus dans la cellule J1 est =OFFSET(D5,2,2).
Il a commencé à D5, puis est descendu de deux rangées et de deux colonnes à droite et a atteint la cellule F7. Il a ensuite renvoyé la valeur dans la cellule F7.
Dans l'exemple ci-dessus, nous avons examiné la fonction OFFSET avec 3 arguments. Mais il y a deux autres arguments facultatifs qui peuvent être utilisés.
Regardons un exemple simple ici :
Supposons que vous souhaitiez utiliser la référence à la cellule A1 (en jaune) et que vous souhaitiez faire référence à toute la plage surlignée en bleu (C2:E4) dans une formule.
Comment feriez-vous cela en utilisant un clavier? Vous devez d'abord accéder à la cellule C2, puis sélectionner toutes les cellules de C2:E4.
Voyons maintenant comment faire cela en utilisant la formule OFFSET :
=DÉCALAGE(A1,1,2,3,3)
Si vous utilisez cette formule dans une cellule, elle renverra une #VALUE ! erreur, mais si vous entrez dans le mode d'édition et sélectionnez la formule et appuyez sur F9, vous verrez qu'il renvoie toutes les valeurs qui sont surlignées en bleu.
Voyons maintenant comment fonctionne cette formule :
=DÉCALAGE(A1,1,2,3,3)
- Le premier argument est la cellule où il doit commencer.
- Le deuxième argument est 1, ce qui indique à Excel de renvoyer une référence qui a été décalée d'une ligne.
- Le troisième argument est 2, ce qui indique à Excel de renvoyer une référence qui a été décalée de 2 colonnes.
- Le quatrième argument est 3. Ceci spécifie que la référence doit couvrir 3 lignes. C'est ce qu'on appelle l'argument de hauteur.
- Le cinquième argument est 3. Ceci spécifie que la référence doit couvrir 3 colonnes. C'est ce qu'on appelle l'argument largeur.
Maintenant que vous avez la référence à une plage de cellules (C2:E4), vous pouvez l'utiliser dans une autre fonction (telle que SUM, COUNT, MAX, AVERAGE).
J'espère que vous avez une bonne compréhension de base de l'utilisation de la fonction Excel OFFSET. Voyons maintenant quelques exemples pratiques d'utilisation de la fonction OFFSET.
Fonction Excel OFFSET - Exemples
Voici deux exemples d'utilisation de la fonction Excel OFFSET.
Exemple 1 - Recherche de la dernière cellule remplie dans la colonne
Supposons que vous ayez des données dans une colonne comme indiqué ci-dessous :
Pour trouver la dernière cellule de la colonne, utilisez la formule suivante :
=OFFSET(A1,COUNT(A:A)-1,0)
Cette formule suppose qu'il n'y a pas de valeurs en dehors de celles affichées et que ces cellules ne contiennent pas de cellule vide. Il fonctionne en comptant le nombre total de cellules remplies et décale la cellule A1 en conséquence.
Par exemple, dans ce cas, il y a 8 valeurs, donc COUNT(A:A) renvoie 8. Nous décalons la cellule A1 de 7 pour obtenir la dernière valeur.
Exemple 2 - Création d'une liste déroulante dynamique
Vous pouvez étendre le concept présenté dans l'exemple 1 pour créer des plages nommées dynamiques. Ceux-ci peuvent être utiles si vous utilisez les plages nommées dans des formules ou dans la création de listes déroulantes et que vous êtes susceptible d'ajouter/supprimer des données de la référence qui crée la plage nommée.
Voici un exemple:
Notez que la liste déroulante s'ajuste automatiquement lorsque l'année est ajoutée ou supprimée.
Cela se produit car la formule utilisée pour créer la liste déroulante est dynamique et identifie tout ajout ou suppression et ajuste la plage en conséquence.
Voici comment procéder :
- Sélectionnez la cellule dans laquelle vous souhaitez insérer la liste déroulante.
- Accédez à Données -> Outils de données -> Validation des données.
- Dans la boîte de dialogue Validation des données, dans l'onglet Paramètres, sélectionnez Liste dans la liste déroulante.
- Dans la source, entrez la formule suivante : =OFFSET(A1,0,0,COUNT(A:A),1)
- Cliquez sur OK.
Voyons comment fonctionne cette formule :
- Les trois premiers arguments de la fonction OFFSET sont A1, 0 et 0. Cela signifie essentiellement qu'elle renverrait la même cellule de référence (qui est A1).
- Le quatrième argument est pour la hauteur et ici la fonction COUNT renvoie le nombre total d'éléments dans la liste. Il suppose qu'il n'y a pas de blancs dans la liste.
- Le cinquième argument est 1, ce qui indique que la largeur de la colonne doit être un.
Notes complémentaires:
- OFFSET est une fonction volatile (à utiliser avec précaution).
- Il recalcule chaque fois que le classeur Excel est ouvert ou chaque fois qu'un calcul est déclenché dans la feuille de calcul. Cela pourrait augmenter le temps de traitement et ralentir votre classeur.
- Si la valeur de hauteur ou de largeur est omise, elle est prise comme celle de la référence.
- Si Lignes et cols sont des nombres négatifs, le sens du décalage est inversé.
Alternatives à la fonction de décalage d'Excel
En raison de certaines des limitations de la fonction Excel OFFSET, vous souhaitez souvent envisager des alternatives:
- Fonction INDEX : La fonction INDEX peut également être utilisée pour renvoyer une référence de cellule. Cliquez ici pour voir un exemple sur la façon de créer une plage nommée dynamique à l'aide de la fonction INDEX.
- Tableau Excel : si vous utilisez des références structurées dans un tableau Excel, vous n'avez pas à vous soucier de l'ajout de nouvelles données et de la nécessité d'ajuster les formules.
Fonction Excel OFFSET - Tutoriel vidéo
- Fonction RECHERCHEV Excel.
- Fonction RECHERCHEH Excel.
- Fonction INDEX Excel.
- Excel Fonction INDIRECT.
- Fonction MATCH Excel.
