Excel : Fonction DECALER (Offset)

Publié le 24 mai 2011
par Sophie Marchand M.Sc., CPA, CGA, MVP
Fonction DECALER

Excel : Fonction DECALER (Offset)

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.

 

 


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 :

Jean-Philippe Provost
Écrit il y a 2 mois
Super formation !

Me permettra sans aucun doute de relevé la qualité de mes modélisations financières même si je modélise depuis plus de 10 ans.

Antoine Champagne
Écrit il y a 6 mois
Excellents outils

Des outils excellents pour bien organiser et rendre clair nos fichiers Excel. Je travaille à tous les jours avec excel et c'est bon de pouvoir bonifier et améliorer mon travail. Vraiment, Mme Marchand, vous êtes excellente; j'ai hâte à la formation suivante. Ça fait plaisir d'encourager une entreprise québécoise qui "excel" dans son domaine.

Paul Chainé
Écrit il y a 1 an
Vraiment très agréable à suivre

La narration et les explications se font avec un débit qui laisse le temps de voir et comprendre ce qui est expliqué, dans un style amical et décontracté, la simplicité des exercices est très appréciée ce qui fait qu'on les fait et c'est justement ce qui nous permet d'assimiler la matière.

Mélanie Hébert
Écrit il y a 2 ans
Très utile

J'aime que nous avons des excel que nous pouvons revoir par la suite j'aime que la formation soit faite avec des sections définies et claires et que chaque sujet ait son propre document de présentation ce qui permet de le consulter facilement au besoin

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top