Excel : Fonction DECALER (OFFSET)

Publié le 24 mai 2011
par Sophie Marchand M.Sc.
Fonction DECALER

La fonction DECALER (Offset) est probablement une des fonctions les plus puissantes d’Excel. Utilisée seule, elle revêt peu d’intérêt mais lorsqu’elle est imbriquée dans d’autres fonctions, elle permet de multiples possibilités. Il faut toutefois l’utiliser avec parcimonie puisqu’elle fait partie des fonctions volatiles d’Excel, soit ces fonctions qui se recalculent chaque fois qu’une action est posée dans Excel, ce qui peut parfois gruger sur la performance d’un fichier. Il faut donc être vigilant dans la façon d’utiliser cette fonction.

 

Comment interpréter la fonction DECALER

La fonction DECALER utilise les paramètres suivants, qui peuvent être interprétés comme suit :

Ref : Point de départ
Lignes : Déplacement vers le bas (en termes de nombre de lignes)
Colonnes : Déplacement vers la droite (en termes de nombre de colonnes)
Hauteur : Hauteur de la plage de cellules (en termes de nombre de cellules)
Largeur : Largeur de la plage de cellules (en termes de nombre de cellules)

 

Paramètres de la fonction DECALER

 

Dans l’exemple ci-dessous, en jaune, vous pouvez constater qu’en partant de la cellule C10, en se déplaçant de deux cellules vers le bas et ensuite de deux cellules vers la droite, on se retrouve sur la cellule E12. En sélectionnant une cellule de hauteur et une cellule de largeur, on obtient 3,434$. Cette trajectoire s’écrit comme suit : =DECALER(C10;2;2;1;1).

 

Fonction DECALER

 

Maintenant, examinons l’exemple en bleu, qui utilise la fonction DECALER, imbriquée dans une fonction SOMME. Vous pouvez constater qu’en partant de la cellule K10, en se déplaçant de deux cellules vers le bas et ensuite de 3 cellules vers la gauche (le signe négatif signifie vers la gauche vs droite), on se retrouve sur la cellule H12. Ensuite, en sélectionnant une plage de une cellule de hauteur et deux de largeurs, on obtient les cellules H12 et I12. En faisant la somme de ces deux cellules, on obtient 10,286$. Ceci se traduit par l’équation: SOMME(DECALER(K10;2;-3;1;2)).

 

Je vous invite à vous pratiquer avec les autres exemples de l’image ci-dessus.

 

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

 

Dans quel contexte la fonction DECALER peut-elle s’avérer utile?

Dans l’exemple ci-dessous, on cherche à obtenir les ventes mensuelles et les ventes cumulatives d’un mois et d’une année déterminés par l’utilisateur.

 

Ventes cumulées

 

Tout d’abord, on a nommé des plages de cellules (pour savoir comment nommer des plages de cellules, cliquez ici). Ensuite on a utilisé la fonction INDEX/EQUIV pour trouver les ventes mensuelles (pour savoir comment utiliser la fonction INDEX/EQUIV, cliquez ici). Finalement, pour trouver les ventes cumulatives, on a imbriqué la fonction DECALER dans une fonction SOMME et nous avons aussi utilisé la fonction EQUIV pour déterminer nos déplacements vers le bas et vers la droite. Ainsi, dans le tableau ci-dessus :

  • on part de la cellule B9
  • on se déplace ensuite vers le bas de x cellules (x étant la position de l’année (sélectionnée dans la cellule D4) dans la plage nommée Années, i.e. celle regroupant les cellules B10 à B12). Dans l’exemple retenu ci-dessus, l’année sélectionnée est 2009 et elle est donc au 2ième rang de la plage de cellules Années. On se déplace donc vers le bas de 2 cellules, pour se retrouver sur B11.
  • on se déplace ensuite vers la droite de 1. On se retrouve donc sur la cellule C11.
  • de là, on fait la somme de la plage de cellules partant de C11 jusqu’à la colonne H, i.e. de x cellules vers la droite, (x étant la position du mois (sélectionné dans la cellule D3) dans la plage de données nommée Mois, i.e. celle regroupant les cellules C9 à N9). Dans l’exemple retenu ci-dessus, le mois sélectionné est Juin et il est donc au 6ième rang de la plage de cellules Mois. On se déplace donc de 6 vers la droite pour faire la somme des cellules C11 à H11. Cette équation se traduit comme suit : =SOMME(DECALER(B9,EQUIV(D4,Années,0),1,1,EQUIV(D3,Mois,0)))

 

Quels sont les avantages d’utiliser cette fonction ?

Avec la fonction définie ci-dessus, un usager peut simplement choisir une année et un mois de référence à partir des cellules D3 et D4 et obtenir dynamiquement les ventes du mois sélectionné et les ventes cumulatives de janvier jusqu’au mois sélectionné, pour une année de référence.

 

Voye le tout en action dans ce tutoriel vidéo

Bon visionnement !

 

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’approfondir vos connaissances avec Excel, dont celles touchant aux fonctions, nous vous recommandons notre formation Excel – Modélisation financière (niveau 1).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

3 réflexions sur “Excel : Fonction DECALER (OFFSET)”

  1. Ping : EXCEL: FONCTION DECALER (OFFSET) | Bienvenue à BM Formation

  2. Ping : Décaler (OFFSET) | Pearltrees

  3. Ping : Lechien (gypsya) | Pearltrees

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut