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).
Formule en cellule D4 :
= SOMMEDE( DECALER(B2 ; 0 ; 0 ; D2) )
Détail du calcul : 4 500€
= 1 500 => Point de départ (cellule B2 = CA de janvier)
+ 1 400 (cellule B3 = CA de Février) + 1 600 (cellule B4 = CA de Mars) =>
Car décalage de 3 par rapport au point de départ (cellule B2)
= CA des 3 premiers mois.
=> Voir aussi comment utiliser la formule INDIRECT
![]( https://excel-en-ligne.fr/wp-content/uploads/2018/05/dddSSSXXXXSdd-Copie-1.jpg)
![]( https://excel-en-ligne.fr/wp-content/uploads/2018/05/Sans-titreDDD.png )