Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit

Bienvenue dans ce cours Excel qui vous montre l’utilisation de la fonction OFFSET (fonctions de recherche et de référence)

Excel : La fonction OFFSET

La fonction Offset est une fonction de recherche d’un grand intérêt, surtout si elle est combinée avec d’autres fonctions et supportée par un outil tel que la validation des données de liste.
La fonction DECALER renvoie, par rapport à une cellule ou à une plage de cellules, une référence à une plage décalée d’un nombre spécifié de lignes et de colonnes. La référence retournée peut faire référence à une seule cellule ou à un intervalle ; dans la fonction OFFSET, il est en effet possible d’indiquer le nombre de lignes et de colonnes de l’intervalle qui doivent être retournées.

Définir la fonction Offset

Pour illustrer la fonction Offset, le tableau ci-dessus est utilisé comme exemple. Dans laquelle la cellule B2 est identifiée comme une référence.
À partir de cette cellule, il faut indiquer combien de lignes et combien de colonnes le déplacement doit être pris en compte, et enfin la taille de la zone finale qui doit être prise en compte.
Syntaxe de la fonction :
= Offset(Réf ; Lignes ; Colonnes ; [Hauteur] ; [Largeur])

Arguments de la fonction Offset :
Ref C’est la référence à la cellule à partir de laquelle vous voulez commencer à calculer le déplacement.
Lignes Nombre de lignes, vers le haut (exprimé sous forme de nombre négatif) ou vers le bas, que vous souhaitez indiquer comme référence pour la cellule supérieure gauche de la zone de navigation.
Colonnes Nombre de colonnes, à gauche (exprimé en nombre négatif) ou à droite, que vous souhaitez indiquer comme référence pour la cellule supérieure gauche de la zone de la référence à renvoyer.

Hauteur exprimée en nombre de lignes de la référence à retourner. La hauteur ne peut être qu’une valeur positive.
Largeur Largeur exprimée en nombre de colonnes de la référence renvoyée. La largeur ainsi que la hauteur ne peuvent être qu’une valeur positive.

Exemple de réglage de la fonction Offset

Nous voulons obtenir la valeur de l’agence 2 au mois d’avril. La fonction sera la suivante :
= Offset (B2 ; 4 ; 2)
Dans lequel le premier opérateur B2 indique la référence à la cellule à partir de laquelle partir et à partir de laquelle on veut commencer le calcul du déplacement. Dans les arguments suivants, le nombre de colonnes 2 et le nombre de lignes 4 sont exprimés respectivement, dont l’intersection détermine la référence de la cellule D6 qui contient la valeur recherchée.

Imbriquer la fonction de décalage dans une fonction de somme

Dans cet exemple, au lieu de rechercher une seule donnée, nous voulons faire référence et additionner les valeurs contenues dans une zone de recherche à 4 cellules, relatives aux mois de janvier, février, mars et avril, toujours de l’Agence 2.
Dans ce cas, lors de l’insertion de la fonction DECALAGE, il faut également indiquer la valeur des deux opérateurs Hauteur et Largeur
= DÉCALAGE (Réf ; Lignes ; Colonnes ; [Hauteur] ; [Largeur])
Plus précisément, la syntaxe de la fonction sera la suivante :
= SOMME (DECALAGE (B2; 1; 2; 4; 1))
:: La fonction OFFSET doit être imbriquée dans la fonction SOMME qui indiquera à Excel comment les valeurs contenues dans la plage de cellules doivent être traitées.

:: Observez comment, en indiquant la hauteur, la cellule D3 a été identifiée comme la première cellule de l’opérateur Lignes.
Par la suite, la hauteur 4 signifie les quatre cellules sous-jacentes, y compris D3.

Autre exemple d’application Fonction Offset

Exemple. vous souhaitez ajouter les valeurs contenues dans une zone de cellule entière avec la fonction OFFSET.
La fonction doit définir à la fois la hauteur et la largeur de la zone concernée.
Concernant l’exemple proposé dans l’image, la syntaxe correcte de la fonction sera la suivante.
= SOMME (DECALAGE (B2; 9; 1; 4; 4))

Dans la Fonction la Référence comme dans les cas précédents reste la cellule B2.
Lignes la valeur saisie est 9 (référence à la ligne du mois de septembre) ; Colonnes la valeur saisie est 1. (Les lignes et les colonnes coupent donc la cellule C11)

A partir de la cellule C11, dans l’opérateur Hauteur et Largeur. la valeur 4 est indiquée, soit 4 colonnes et 4 lignes.

:: l’utilisation de la fonction OFFSET devient particulièrement intéressante lorsque la définition de la valeur de la cellule à retourner ou de la surface à calculer peut être sélectionnée à travers les listes de validation des données. Sinon il est plus facile d’appliquer d’autres fonctions comme la très simple SOMME.

Configurez une validation de données de liste et recherchez une valeur en appliquant la fonction MATCH et Offset

Après avoir défini la validation des données de la liste, grâce à la fonction COMPARER, il sera possible de rechercher la position de la ligne et de la colonne de l’agence et du mois sélectionné. = CORRESPONDANCE (H3 ; C2 : F2 ; 0) e = CORRESPONDANCE (I3 ; B3 : B14 ; 0)
Les deux fonctions COMPARE doivent être imbriquées dans les opérateurs de ligne et de colonne de la fonction = OFFSET (B2 ; 4 ; 2)

La syntaxe de la Formule complète sera la suivante : = OFFSET (B2; MATCH (I3; B3: B14; 0); MATCH (H3; C2: F2; 0))

:: Pour plus d’informations sur la saisie de la validation des données, voir : Créer une liste déroulante et vérifier la saisie des données

Merci à www.valterborsato.it 🙂