L'intérêt de cette fonction n'apparaît pas d'emblée. C'est pourtant une des fonctions d'excel les plus utile à l'usage, en particulier dès que l'on souhaite qu'une liste, un graphique, un tableau croisé dynamique ... se mette à jour automatiquement dès qu'on ajoute des données sur une feuille.

Utilisation standard

La fonction Décaler (offset en anglais)  permet comme son nom l'indique de sélectionner une cellule ou une plage de cellule sur la feuille à partir d'une cellule de départ en indiquant de combien de lignes et de colonnes on veut se décaler et quelle est la taille de la plage à sélectionner. Vu comme ça, on se dit que les références relatives dans excel font cela très bien. C'est exact mais la fonction décaler est très utile quand les valeurs du décalage sont variables, par exemple dépendant du résultat d'un calcul.
La fonction Decaler admet 5 paramètres. Les 3 premiers sont obligatoires, les 2 derniers facultatifs.
Le moyen mnémotechnique : décaler=(départ;bas;droite;hauteur;largeur).
Explications : Départ indique la référence de la cellule ... de départ. Par exemple D10
Bas indique de combien de cellules on veut décaler ce point de départ vers le bas. 3 positionne le curseur en D13. Si on veut se déplacer vers le haut on indique une valeur négative : -3 nous positionnerait en D7.
Droite indique de combien de cellules vers la droite on déplace le point de départ. 4 nous place en H10, -4 en ... impossible car on ne peut pas aller dans une colonne à gauche de A !
si dans une cellule on écrit
=decaler(D10;3;3), cela renvoie la valeur de la cellule G13 et
=decaler(D10;3;-3) celle de la cellule A13.
Dans ces deux exemples, comme nous n'avons indiqué que les trois premiers paramètres de la fonction, les deux derniers sont ignorés et valent par défaut chacun 1 : c'est la largeur et la hauteur de la plage renvoyée, donc ici une seule cellule.

Decaler(D10;0;0) me laisse en D10.
Decaler(D10;0;0;3;3) renvoie une plage de cellule dont le coin supérieur gauche est D10 et qui fait 3 cellules de haut sur trois de large. Autrement dit, cela renvoie la plage D10:G13.
On peut se dispenser d'écrire les 0 dans ce cas mais il faut quand même écrire les ;
decaler(D10;0;0;3;3) renvoie la même chose que decaler(D10;;3;3).
On peut faire la moyenne de cette plage :
=moyenne(decaler(D10;;3.3))

Si maintenant on écrit
=moyenne(decaler(D10;-7;2;1;4)), on calcule la moyenne d'une plage dont le coin supérieur gache est situé en F3 et qui fait de 1 cellule de haut, 4 de large, c'est à dire F3:I3

Utilisation de DECALER avec des paramètres dont la valeur varie

Ecrire = moyenne(decaler (D10;-7;2;1;4)) ne paraît guère intéressant. Il est plus rapide d'écrire =moyenne et de sélectionner à la souris la plage F3:I3) !
Decaler devient réellement intéressant quand on veut appliquer la même fonction (ici moyenne mais cela pourrait être une formule bien plus complexe) sur une plage qui varie en fonction d'autres paramètres.
Au lieu d'écrire en dur -7 dans les paramètres de décaler, je peux écrire cette valeur dans la cellule A1.
=decaler(D10;A1;A2;A3;A4)  utilisera toujours D10 comme cellule de départ mais les VALEURS contenues dans les cellules A1 à A4 comme paramètres. Rien n'interdit bien entendu qu'A1 contienne elle même une formule renvoyant des valeurs variant avec la valeurs d'autres cellules...
Vous pouvez bien entendu jouer avec les références absolues et relatives comme pour n'importe quelle autre fonction d'excel. Ceci vous permet de "tirer" une formule contenant la fonction DECALER en maintenant fixe la cellule de référence et en faisant varier la plage renvoyée ou au contraire faire varier la cellule de départ tout en imposant les mêmes paramètres de décalage... ou une combinaison de tout ceci.

Utilisation de cellules nommées

Vous pouvez également utiliser dans les paramètres de la fonction décaler des cellules nommées (menu insertion/nom/définir ou dans excel 2007 formules/définir un nom). Définissez par exemple le nom Décalage_bas et attribuez lui la valeur -7. Vous écrirez alors votre formule =decaler(D10;decalage_bas;2;1;4)
Bien entendu, Decalage_bas peut aussi être un nom de variable défini dans une macro VBA...

Utilisation de DECALER pour définir des plages de façon dynamique : définir la plage qui va de A1 jusqu'à la dernière cellule non vide de la colonne A.

Cette utilisation est certainement une de celles qui est la plus utile dès que l'on a un classeur qui sert à recueillir des données au fil du temps et que l'on souhaite que les données nouvellement inscrites soient prises en compte dans les calculs sans avoir besoin de modifier toutes les formules ou les graphiques d'un classeur.

Prenons un exemple simple : en colonne A de la feuille 1, vous inscrivez tous les jours une valeur, disons la température maximale de la journée. Vous avez besoin par la suite dans votre classeur de faire référence à la plage qui va de A1 jusqu'à la dernière cellule remplie de votre colonne. Si vous voulez simplement calculer la moyenne, cette fonction ne tenant pas compte des cellules vides, il vous suffirait d'écrire = moyenne(A1:A10000) en prenant de la marge. Mais si votre calcul ne doit pas inclure les cellules vides, ou si le nombre de données étant très grand, il est important pour des raisons de temps de calcul de ne travailler que sur le plus petit nombre de données possibles, il vaut identifier cette plage et faire en sorte qu'elle s'agrandisse (ou diminue) en fonction des données qu'elle contient.

Nous allons définir la plage d'intérêt avec la fonction DECALER.
Comment renvoyer la plage qui va de A1 à A37 s'il y a 37 données dans la colonne A ?
Nous partons de A1. La cellule de départ de la plage ne subit pas de décalage de ligne ou de colonne donc les 2° et 3° paramètres sont 0. La hauteur de la plage est égal au nombre de valeurs contenues dans la colonne A. Cette valeur est retournée par la fonction =nbval($A:$A). Largeur de la plage est 1. Nous pouvons donc écrire
=decaler(A1;0;0;nbval($A:$A);1) ou encore de façon plus concise =decaler(A1;;;nbval($A:$A))
On peut faire la moyenne de cette plage comme décrit plus haut : =moyenne(decaler(A1;;;nbval($A:$A)) ou l'utiliser dans toute formule de notre choix. Comme cette écriture est un peu lourde et qu'on peut aussi se tromper en l'écrivant, il est vivement conseillé de l'utiliser pour définir un nom qui sera utilié dans la formule.
Ouvrez l'éditeur de nom (insertion/nom/définir ou formules/définir un nom). Dans la zone de nom tapez par exemple maplagedynamique et dans la zone de formule en bas tapez = decaler($A$1;;;nbval($A:$A))
attention à bien mettre les $ pour fixer la référence à la cellule de départ et à la colonne A. Sinon gare aux résultats ! si vous écrivez en effet dans la cellules D10 = moyenne(maplagedynamique) sans avoir fixé ces références, vous renverriez une plage... variable en fonction de la cellule ou vous écrivez la formule. Essayez...

Astuce : pour vérifier que votre plage est bien définie comme vous le souhaitez, après avoir saisi votre formule dans l'éditeur de nom, validez pour enregistrer le nom puis, toujours dans l'éditeur de nom cliquez dans la formule. Un cadre pointillé entoure alors sur la feuille de calcul la plage renvoyée par votre formule DECALER. Une erreur fréquente dans la saisie c'est d'oublier un ; ou une )...

Vérifiez maintenant que si vous ajoutez un nom en dessous de la dernière cellule remplie de la colonne A, votre formule la prend bien compte ! C'est magique :-)

Si vos données sont en ligne, =decaler($A$1;;;;nbval($1:$1)) sélectionne la plage A1:n1 ou n correspond à la lettre de la dernière colonne contenant une cellule remplie.

Attention : Il y a une limitation majeure à cette utilisation : votre colonne A ne doit rien contenir d'autres que les valeurs à prendre en compte dans votre plage. Ou alors, vous devez ajuster votre formule. L'exemple type est celui d'un colonne contenant un titre sur la première ligne. Dans ce cas, votre plage dynamique sera définie de la façon suivante :
=decaler($A$2;0;0;nbval($A:$A)-1;1) : les valeurs commencent en A2 et non en A1 et le nombre de valeurs est égal au nombre de cellules remplies de la colonne A, moins 1 correspondant à la cellule A1 qui contient votre titre.

Il ne FAUT PAS qu'il y ait de TROUS dans votre colonne. Vos données doivent se suivre les unes en dessous des autres. La hauteur de la plage est en effet définie en fonction du nombre de valeurs et non pas en fonction de l'adresse de la dernière cellule de la colonne. Donc si vous supprimez des valeurs, vous devez faire remonter les cellules restantes pour combler le vide créé. Si vous ne pouvez pas faire autrement qu'avoir des vides dans votre colonne alors vous devez définir votre plage par une autre méthode, DECALER vous renverra toujours une plage (aucun message d'erreur) mais celle-ci ne correspondra pas à ce que vous croyez.

Astuce : Vous pouvez sans problème faire débuter votre plage en A10 si vous avez besoin d'espace au dessus de vos données. Mais si vous remplissez par exemple la cellule A8 APRES avoir défini votre nom, il faudra impérativement que vous réajustiez celui-ci. Pour éviter cela, mettez par exemple une apostrophe dans les cellules situées dans la zone au dessus de vos données. Votre formule sera alors =decaler($A$10;;;nbval($A:$A)-9). Si vous tapez par la suite une info dans la cellule A8, vous ne ferez que remplacer l'apostrophe par votre texte et cela ne modifiera pas la définition de la plage.

Il arrive parfois que dans un tableau, la colonne A contienne par exemple des références et qu'il n'y ait pas de trous dans cette colonne mais que la colonne B qui contient les prix des références contienne des vides, certaines données n'étant pas encore disponible. Dans ce cas vous pouvez ruser et prendre comme hauteur de la plage "prix" le nombre de valeurs de la colonne A : prix=decaler($B$2;;;nbval($A:$A)-1) avec une étiquette prix en B1.

Vous pouvez bien sur définir aussi de façon dynamique tout un tableau :
Si vous définissez bdd=decaler($A$1;;;nbval($A:$A);nbval($1:$1)) votre tableau bdd prendra en compte les nouvelles valeurs ajoutées dans les lignes comme dans les colonnes.

Utilisation des noms dynamiques

Un des outils pratiques d'excel, c'est la liste de validation. Elle vous permet de ne laisser que des choix définis pour entrer une valeur dans une cellule. On accède à cette fonction par le menu données/Validation. Dans la boîte de dialogue qui s'ouvre, choisissez liste. Vous pouvez taper en dur les valeurs autorisées, faire référence à une plage de cellule SUR LA MEME FEUILLE qui contienne ces données, ou faire appel à un nom défini. L'appel à un nom défini permet non seulement de voir d'emblée de quoi l'on parle (MaListe est plus parlant que B234:B289...) mais permet en plus de faire référence à des cellules situées sur une autre feuille. Tapez simplement =maliste si tel est le nom défini de cette liste.
Et bien sur, si vous avez défini ma liste de façon dynamique en utilisant la fonction DECALER comme décrit plus haut, vous pouvez ajouter des valeurs autorisées simplement en les tapant en dessous des existantes (attention, si vous supprimez une valeur il faut remonter les autres pour combler le trou).

Nous avons précedemment défini le tableau bdd de façon dynamique (TCD). Si vous créez un tableau croisé dynamique à partir de ce tableau en tapant =bdd dans la plage de référence du TCD, celui-ci prendra en compte toute nouvelle donnée que vous y ajouterez sans avoir besoin de redéfinir la plage (il faut cependant actualiser le TCD en cliquant sur le bouton ad-hoc).

Vous trouverez d'autres exemples de l'utilisation des noms dynamiques sur les pages consacrées aux graphiques dynamiques.

Cette page a été vue 2169 fois.