Excel: La fonction DECALER()
Date de publication : 15/02/2008 , Date de mise à jour : 15/02/2008
Par
Pierre Fauconnier (Pierre Fauconnier sur DVP)
A quoi sert DECALER() et comment l'utiliser?
I. Introduction
I-A. Utilité de la fonction DECALER
II. Syntaxe et première utilisation
II-A. Syntaxe
II-B. Exemples d'utilisation
II-B-1. Mise en place du tableau
II-B-2. Développement du raisonnement
II-B-3. Première utilisation: DECALER() renvoie une cellule
II-B-3-a. Rendre DECALER() dynamique
II-B-4. Deuxième utilisation: DECALER() renvoie une plage
I. Introduction
I-A. Utilité de la fonction DECALER
La plupart des fonctions d'Excel renvoient des valeurs. Certaines renvoient du texte,
d'autres des dates, d'autres encore des valeurs numériques, des valeurs booléennes, ...
DECALER est une des rares fonctions intégrées d'Excel qui
renvoie une plage de cellules. Elle pourra donc être utilisée
dans toute formule en remplacement d'une plage de données "en dur".
L'intérêt de DECALER() est qu'il sera possible de faire
varier ses paramètres, en fonction de la configuration de la feuille, de
valeurs dans des cellules, ...
II. Syntaxe et première utilisation
II-A. Syntaxe
|
=DECALER(PlageDepart ; DecalageLignes ; DecalageColonnes ; [NombreLignes]; [NombreColonnes])
|
 |
Comme dans toutes les fonctions Excel, les paramètres en crochets sont optionnels.
|
PlageDepart: Plage qui sert d'ancrage à la plage renvoyée par DECALER().
Toute plage valide est acceptée en argument
DecalageLignes: Nombre de lignes à parcourir à partir du coin supérieur gauche de
PlageDepart
Ce nombre est positif lorsque l'on parcourt la feuille vers le bas,
négatif lorsque l'on veut parcourir la feuille en remontant les lignes
DecalageColonnes: Nombre de colonnes à parcourir à partir du coin supérieur gauche
de PlageDepart
Ce nombre est positif lorsque l'on parcourt la feuille de gauche à droite, et négatif
lorsque l'on parcourt la feuille de droite à gauche
NombreLignes: Nombre de lignes de la plage renvoyée par DECALER().
Ce nombre est obligatoirement positif non nul
NombreColonnes: Nombre de colonnes de la plage renvoyée par DECALER()
Ce nombre est obligatoirement positif non nul.
Nous verrons plus loin dans le cours les limites et contraintes à respecter
lors de l'emploi de ces paramètres.
II-B. Exemples d'utilisation
Un exemple valant mieux qu'un long discours, nous allons utiliser DECALER()
pour renvoyer la dernière saisie d'un tableau
II-B-1. Mise en place du tableau
Examinons un tableau comme illustré sur l'image suivante
Premier tableau
Je souhaite pouvoir retrouver la valeur saisie dans la dernière cellule
du tableau, à savoir E10. Je peux donc, dans une cellule de mon choix, par
exemple E12,
utiliser la formule
Et je récupère donc le nombre 600.
Si je saisis une nouvelle entrée en ligne 11, H2 n'est pas adapté
et me renvoie toujours la valeur de E10, alors que voudrais maintenant
récupérer la valeur de E11.
II-B-2. Développement du raisonnement
Pour solutionner ce problème, commençons par décortiquer ce qui me permet de
récupérer la valeur de E11
Le raisonnement que je vais suivre, par rapport au début de mon tableau, va être
de descendre de x lignes, puis d'aller à droite de x colonnes.
Par rapport à mon tableau, je vais partir de A1, descendre de
10 lignes puis partir vers la droite
sur 4 colonnes pour arriver
en E11.
Parcours de A1 vers E11
II-B-3. Première utilisation: DECALER() renvoie une cellule
Comment demander à Excel de réaliser cela à ma place? Avec DECALER().
La cellule A1 est celle qui est à l'origine de mon parcours. C'est le paramètre
PlageDepart.
De cette cellule, je parcours 10 lignes (vers le bas) pour arriver en ligne 11. C'est
le paramètre DecalageLignes.
De A11 où je suis arrivé, je vais parcourir 4 colonnes (vers la droite) pour
arriver en E11.
Nous pouvons donc constater qu'avec ces trois paramètres, nous avons pu
définir la cellule E11. Plus exactement, nous avons défini le
coin supérieur gauche de la plage qui sera renvoyée par DECALER().
Il nous reste à préciser les deux derniers paramètres (optionnels) qui précisent
le nombre de lignes et de colonnes de la plage renvoyée. Dans notre cas, nous
renvoyons une seule cellule, soit une plage de 1 ligne et de 1 colonne et
nous préciserons donc 1 pour les paramètres NombreLignes et NombreColonnes.
Revenons en H2 pour saisir notre formule. L'assistant Fonctions permet d'être
"pris en charge". Je le montre ici pour l'exemple, car la terminologie utilisée
par l'assistant n'est pas toujours claire, et dans la suite du cours, nous pourrons
saisir la formule directement.
|
Il est d'autant plus utile de connaître la syntaxe de DECALER() que dans
certains contextes d'utilisation, nous ne pourrons pas nous appuyer sur l'assistant
pour nous guider.
|
En H2, appelons l'assistant et choisissons DECALER() parmi les fonctions
proposées.
DECALER dans les fonctions de recherche de l'assistant f(x)
La zone de saisie des 5 paramètres de DECALER()
Dans cette fenêtre, nous allons saisir les valeurs examinées plus haut.
|
La zone Résultat mentionne Volatile. Nous ne pouvons donc pas, à ce
stade, vérifier la validité des paramètres.
|
Validons la formule, pour voir apparaître la valeur de E11 en H2.
La valeur de E11 renvoyée en H2 grâce à DECALER
Bon. A ce stade, il n'y a pas encore grand chose de variable dans les
paramètres de DECALER(), et donc cette fonction, pour l'instant, ne
fait rien d'autre que =E11 de façon plus compliquée.
II-B-3-a. Rendre DECALER() dynamique
Ce qui va rendre DECALER() dynamique, c'est que nous allons substituer une
fonction d'Excel à un des paramètres fixes de la fonction.
Si on reprend le schéma précédent, on peut remarquer que l'on parcourt un
nombre de lignes qui correspond au nombre de cellules remplies de la colonne A.
On parcourt 10 lignes (11 cellules pleines - le titre)
Il suffit donc de trouver le moyen de dire à Excel de parcourir autant de lignes qu'il
y a de cellules remplies en A, -1 pour le titre.
La fonction NBVAL(Plage) va permettre cela. En effet,
saisi dans une cellule renverra la nombre de cellules remplies en A.
NBVAL pour récupérer le nombre de cellules remplies dans une plage
Et voici enfin la fonction DECALER() dynamique
=DECALER(A1;NBVAL(A:A)-1;4;1;1)
|
La fonction DECALER() avec un paramètre variable
En ajoutant maintenant une nouvelle saisie en ligne 12, la fonction DECALER()
s'adapte automatiquement pour proposer le nouveau dernier montant saisi
Le 'nouveau' dernier montant saisi
II-B-4. Deuxième utilisation: DECALER() renvoie une plage
Dans cette section, nous allons voir que, en jouant avec les paramètres de
DECALER(), nous pouvons renvoyer une plage de cellules, par exemple
pour en calculer la somme
En reprenant notre tableau, nous souhaitons calculer la somme des montants pour
les prestations saisies.
Copyright © 2008 Pierre Fauconnier.
Aucune reproduction, même partielle, ne peut être faite
de ce site ni de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à
trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.
