Developpez.com

Plus de 14 000 cours et tutoriels en informatique professionnelle à consulter, à télécharger ou à visionner en vidéo.

Developpez.com - Microsoft Office
X

Choisissez d'abord la catégorieensuite la rubrique :


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?

               Version PDF   Version hors-ligne

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])
info 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

=E10
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.

info 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.

info 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,

=NBVAL(A:A)
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.



               Version PDF   Version hors-ligne

Valid XHTML 1.1!Valid CSS!

Copyright © 2008 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et 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.

Responsables bénévoles de la rubrique Microsoft Office : Pierre Fauconnier - Arkham46 -