Decaler : une fonction vraiment indispensable.

A quoi sert la fonction DECALER ? Quels en sont les paramètres ?

DECALER(départ;décalage bas;décalage droite; hauteur; largeur)

Définition

La fonction DECALER (offset en anglais) sert à définir une plage de cellules dans la feuille excel en partant d'une cellule de départ, on décale la référence de cette cellule d'un certain nombre de lignes et de colonnes, ce qui définit la cellule en haut à gauche de la plage et enfin on indique la largeur et la hauteur de la plage.

Attention si on écrit dans la feuille de calcul

=DECALER($B$2; 7 ; 2 ; 16 ; 3)
Excel renvoie une valeur d'erreur car excel ne sait pas que faire d'une plage de cellule inscrite de cette façon.
Pour tester votre compréhension de cette formule, inscrivez quelque chose dans chacune des cellules de la plage en bleu et écrivez dans la feuille
=NBVAL(DECALER($B$2; 7 ; 2 ; 16 ; 3))
Excel évalue cette formule, remplace  DECALER($B$2; 7 ; 2 ; 16 ; 3) par la plage renvoyée : D9:24 et renvoie le nombre de cellules contenant une valeur dans cette plage (16x3=48).

Paramètres

Elle s'écrit avec 5 paramètres :

  1. Cellule de départ
  2. Décalage vers le bas (vers le haut si valeur négative)
  3. Décalage vers la droite (vers la gauche si valeur négative)
  4. Hauteur de la plage (en nombre de cellules)
  5. Largeur de la plage (en nombre de cellules)

Dans l'exemple ci-dessus

DECALER($B$2; 7 ; 2 ; 16 ; 3)

  • on part de B2
  • On descend de 7 cellules, on "arrive" donc en B9.
  • On se décale de 2 cellules vers la droite. On arrive donc en D9.
  • On définit alors à partir de cette cellule une plage de 16 cellules de haut et de 3 de large.
  • C'est à dire la plage D9:F24

Si le décalage vertical (paramètre 2) ou  le décalage horizontal (paramètre 3) ne sont pas précisés, ils sont égaux à 0. Mais ce sont des paramètres obligatoires.
Si les paramètres hauteur ou largeur sont omis (ce sont des paramètres optionnels), ils sont égaux à 1. Ils ne peuvent jamais être nusl (une plage a forcément au moins une cellule de haut et de large.

DECALER($B$2;0;0;2;2)

qu'on peut aussi écrire

DECALER($B$2;;;2;2)

définit la plage B2:C3 (plage de 2 lignes et 2 colonnes partant de B2 puisqu'on n'a pas fait de décalage vertical ou horizontal.

DECALER($B$2;1;0;0;0)

peut s'écrire

DECALER($B$2;1;0) 

mais pas

DECALER($B$2;1)

Attention :

DECALER($B$2;-3;1;0;0) 

renverra une erreur car on ne peut décaler B2 de 3 cellules vers le haut : on se retrouverait en dehors de la feuille.
 

Usage

Euh... ça m'a l'air super compliqué ce truc ! pourquoi écrire DECALER($B$2;1;0;0;0) alors qu'il est si facile d'écrire D9:F24 ?

Cette fonction est en général utilisée en n'écrivant pas "en dur" ses paramètres comme ci-dessus mais en les faisant calculer par excel.

On peut remplacer un ou plusieurs paramètres par la référence à une cellule contenant une valeur :

DECALER($B$2;A1;A2;A3;A4)

ou par une autre formule

DECALER($B$2;1;0;nbval($B:$B);1)

En pratique c'est presque toujours de cette façon qu'on utilise DECALER.

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.

Une des utilisations les plus fréquentes de DECALER permet de définir une plage qui descend jusqu'à la dernière cellule remplie d'une colonne.

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.

En colonne A de la feuille 1, vous inscrivez tous les jours la température maximale de la journée.
Si vous voulez calculer la moyenne de toutes les valeurs saisies, il vous suffirait d'écrire = moyenne(A1:A10000) en prenant de la marge.
Mais si vous ne connaissez pas la taille de la plage, ou si celle-ci est très grande, il est important pour des raisons de temps de calcul de ne travailler que sur le plus petit nombre de données possibles.
Avec la fonction DECALER on va définir une plage qui s'agrandit et se rétrécit automatiquement pour s'adapter aux données.

Si la colonne A ne contient que les valeurs à moyenner, sans cellules vides, sans étiquettes... la hauteur de la plage est égale au nombre de valeurs qu'elle contient, ce que l'on trouve par la formule =NBVAL($A:$A)

DECALER($A$1;0;0;NBVAL($A:$A))

définit donc la plage qui va de A1 à la dernière cellule remplie dans la colonne A.
Si on a une étiquette (température maxi) en A1,

DECALER($A$1;1;0;NBVAL($A:$A)-1)

définit la plage qui va de A2 à la dernière cellule de la colonne A.
et

MOYENNE(DECALER($A$1;1;0;NBVAL($A:$A)-1))

renvoie la moyenne de cette plage.

Créer un nom dynamique

Comme cette écriture pour définir la plage 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 TempMax et dans la zone de formule en bas tapez

DECALER($A$1;1;0;NBVAL($A:$A)-1)

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(TempMax) sans avoir fixé ces références, vous renverriez une plage... variable en fonction de la cellule ou vous écrivez la formule. Essayez...

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 :-)

Limitation

Il y a une limitation majeure à cette utilisation : la colonne A ne doit rien contenir d'autre que les valeurs à prendre en compte dans votre plage. Ou alors, vous devez ajuster votre formule.

Il ne FAUT PAS qu'il y ait de cellules vides 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 vous devez effacer une valeur, supprimez la mais ne laissez pas de cellule vide.

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 mis au point votre formule, il faudra impérativement que vous la réajustiez.
Pour éviter cela vous pouvez modifier votre formule pour tenir compte du nombre de cellules remplies dans la plage A1:A9:
DECALER($A$10;;;NBVAL($A:$A)-NBVAL($A1:$A9)))

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.

Nous avons précedemment défini le tableau bdd de façon dynamique. Si vous créez un tableau croisé dynamique (TCD) à 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.

Remplacer la fonction DECALER par l'utilisation d'un tableau pour définir des noms et des plages dynamiques en un clic

La version 2003 d'excel a introduit la notion de "Listes" qui a changé de nom avec la version 2007 pour devenir "tableau", en même temps que les fonctionnalités étaient grandement améliorées.
Dans la majorité cas cette fonctionnalité extrêmement pratique et simple permet d'obtenir en un clic la même chose que ce qui est décrit plus haut. Plus besoin de se rappeller comment construire =decaler($A$1;1;0;nbval($A:$A)-1)
Et gros avantage, vous pouvez mettre autre chose que vos données dans les colonnes référencées dans la formule sans que ça mette le bazar.

Ou trouver cette fonctionnalité  ?

Sur l'onglet accueil/style/mettre sous forme de tableau
Cette page : Les tableaux et les suivantes présentent en détail les multiples avantages de cette solution.

Aperçu rapide des avantages des tableaux par rapport à l'utilisation de la fonction decaler

  • Un tableau est autiomatiquement dynamique : toute valeur que vous écrivez dans la première ligne vide juste sous le tableau s'y intègre automatiquement.
  • Dans un tableau, excel définit automatiquement un nom interne pour chaque colonne du tableau. Ce nom est automatiquement dynamique.
  • Si un graphique est associé à ce tableau, ce graphique se modifie tout seul pour prendre en compte les nouvelles données. Aucun besoin donc de définir les séries du graphique avec la fonction decaler
  • Si un tableau croisé dynamique est construit en utilisant un tableau comme base, la source s'ajuste automatiquement lorsque le tableau se modifie (mais il faut quand même cliquer sur le bouton actualiser du TCD)
  • Si vous utilisez une colonne de tableau pour faire une liste de validation, elle est également dynamique.
  • Vous pouvez mettre plusieurs tableaux sur une même feuille, les uns en dessous des autres sans avoir à vous préoccuper de ce qui n'appartient pas au tableau.
  • La sélection d'une colonne entière se fait d'un simple clic sur le haut de la colonne. Finis les aller-retours avec l'ascenceur. C'est particulièrement agréable quand il y a beaucoup de données. Idem pour la sélection des lignes et même de l'ensemble du tableau.

Utilisation de DECALER pour définir des plages glissantes

On peut avoir besoin de faire la somme (ou la moyenne) des X dernières valeurs d'une plage qui s'étend au fur et à mesure qu'on ajoute des données.
Exemple typique : faire la moyenne des ventes des 3 derniers mois

Avec les données ci-dessous, versions excel 97-2002

decalerglissant1.png

la formule

=MOYENNE(DECALER($B$1;NBVAL(B:B)-1;0;-3;1))

renvoie la moyenne des 3 cellules sélectionnées sur la figure
Attention, limite habituelle, il ne faut pas qu'il y ait d'autres données dans la colonne B !
Si vos données ne commencent pas en ligne 2, pensez à ajuster les paramètres.
Le principe est toujours le même :
on part de B1, on descend du nombre de cellules remplies de la colonne B, moins une pour ne pas tenir compte du titre. on arrive en B17.
On ne décale pas vers la droite (donc on reste en B17)
on sélectionne alors une plage de 3 cellules VERS LE HAUT (donc on met -3 et non pas 3).
on peut omettre le dernier paramètre : 1 qui représente la largeur de la plage (1 étant la valeur par défaut).

Avec les versions 2003 et +

on transforme les données en tableau (listes dans excel 2003)
On n'a plus à se préoccuper

  • ni de la position du tableau de la feuille (on se fiche de savoir si il commence en B2, D148 ou autre)
  • ni du contenu des autres cellules de la feuille, y compris dans la colonne B

ce qui est à mon avis un avantage MAJEUR car il évite bien des erreurs (qui n'a pas un jour ajouté un truc dans une cellule de la colonne B sans se rendre compte que du coup la moyenne calculée avec decaler était fausse ?)
decalerglissant2.png

la formule à utiliser devient

=MOYENNE(DECALER(Tableau1[[#En-têtes];[ventes]];NBVAL(Tableau1[ventes]);0;-3;1))

Bien entendu, il ne faut pas saisir soi même les références de type Tableau1[[#En-têtes]... il faut le faire à la souris c'est bien plus rapide et plus sur.
La syntaxe est légèrement différente entre les versions 2003, 2007 et 2010 (miracle ça ne change pas entre 2010 et 2013 !) mais le principe est le même et encore une fois c'est excel qui écrit pour vous.

Définir un nom pour une "plage glissante"

On peut évidemment aussi définir un nom en utilisant cette façon de faire.
Avec excel 97-2003 : insertion/nom/définir  MaPlageGlissante
fait référence à :

=DECALER($B$1;NBVAL(B:B)-1;0;-3;1)

Avec excel 2007 et + : onglet formules/définir un nom MaPlageGlissante

fait référence à 
=DECALER(Tableau1[[#En-têtes];[ventes]];NBVAL(Tableau1[ventes]);0;-3;1)

on peut alors écrire

=moyenne(MaPlageGlissante)

ou 

=somme(MaPlageGlissante)

ou bien utiliser ce nom dynamique pour construire un graphique qui ne représente que les 3 dernières valeurs
 

Commentaires

bonjour,

J'ai réaliser un graphique avec mise à jour automatique grace à la fonction décaler. Seulement voilà j'aurais besoin de copier cette feuille et lorsque je le fais un problème se pose. Les données du graphique que j'avais définie grace au nom de plage (type ='feuil1'date) ne se copie pas. Je retrouve à la place une plage classique (type ='feuil1'$A$12:$P$12). Il n'y a donc plus prise en compte de la fonction décaler ! Suis-je obliger de redéfinir les plages de données du graphique par le nom de plage systématiquement ? ou y a-t-il un moeyn de conserver les données?
Merci de votre réponse !

Bonjour

si vous copiez collez vous devez effectivement redéfinir les plages. Essayez plutot de dupliquer la feuille en faisant un clic droit sur le nom de l'onglet, puis déplacer ou copier, cochez copier.


Ce mois-ci sur Excelabo

- Pas de nouvelle page.
- 3 pages modifiées.