La formule DECALER - Apprendre Excel

Principe : La formule DECALER permet de renvoyer la référence d’une cellule ou d’une plage de cellules en fonction d’un décalage exprimé en nombre de lignes/nombre de colonnes ainsi que d’une taille exprimée en hauteur/largeur.

= DECALER(réf ; lignes ; colonnes ; [hauteur] ; [largeur])

Référence par rapport à laquelle le décalage doit être opéré (=Point de départ).
La table (colonne ou ligne) dans laquelle on souhaite retrouver la valeur cherchéeNombre de lignes vers le haut ou vers le bas dont la référence de la cellule tout en haut à gauche de la réf doit être décalée :

– Valeur positive pour effectuer un décalage en dessous de la référence de départ.

– Valeur négative pour effectuer un décalage au-dessus de la référence de départ.

Nombre de colonnes vers la gauche ou vers la droite dont la référence de la cellule tout en haut à gauche de la réf doit être décalée :

– Valeur positive pour effectuer un décalage à droite de la référence de départ.

– Valeur négative pour effectuer un décalage à gauche de la référence de départ.

[Facultatif] : Hauteur de la plage de cellules de la référence renvoyée => Doit être un nombre positif.
[Facultatif] : Largeur de la plage de cellules de la référence renvoyée => Doit être un nombre positif.

Remarques :

Cette formule ne sert pas à décaler des cellules dans la feuille Excel, elle renvoie simplement une référence.
Si les arguments hauteur ou largeur sont omis, les valeurs par défaut de ces arguments sont celles de la réf.
La formule DECALER peut être utilisée avec toutes les fonctions exigeant une référence comme argument.

Exemple 1 : Liste déroulante dynamique.

On souhaite créer une liste déroulante dynamique (dont la taille s’adapte en fonction du nombre de valeurs) à partir d’une liste de modèle de voitures dont la source n’est pas mise sous forme de tableau.

> La liste à partir de laquelle on souhaite créer la liste déroulante se trouve dans la feuille « Modèles » :

=> Pour parvenir à créer cette liste déroulante dynamique il faut combiner les formules DECALER et NBVAL :

> La formule DECALER va permettre de sélectionner la liste de manière dynamique grâce à l’argument [hauteur].

> La formule NBVAL va justement permettre de définir le nombre de cellules à prendre en compte pour l’argument [hauteur] de la formule DECALER en comptant le nombre de cellules non vides dans la liste de voitures.

=> Il faut donc insérer cette formule combinée pour la source de la liste déroulante du choix du modèle.

Formule pour la source de la liste déroulante :

= DECALER (Modèles!A2 ; 0 ; 0 ; NBVAL(Modèles!A:A)-1)

Modèles!A2 = Première valeur de la liste (« C1 » dans l’exemple).
0 ; 0 = Aucun décalage ni sur les lignes ni sur les colonnes pour rester sur la première valeur de la liste comme point de départ.
NBVAL(Modèles!A:A)-1 = Pour la hauteur on compte le nombre de cellules non vides dans la colonne dans laquelle on a la plage de cellules qui sert à créer la liste déroulante et on enlève 1 pour ne pas prendre en compte la cellule non vide qui correspond à l’en-tête de colonne.

=> Si on ajoute le modèle « Picasso » dans la liste de véhicules :

=> La source de la liste déroulante s’adapte à cette nouvelle entrée :

Exemple 2 : Combinaison avec la formule SOMME.

On souhaite calculer le CA généré sur les n premiers mois : n correspond donc au nombre de mois pour lesquels on souhaite calculer le total du CA cumulé (en partant du mois de Janvier).