La fonction DECALER est une fonction Excel avancée et polyvalente qui vous permet de renvoyer une cellule ou une plage de cellules décalées par rapport à une cellule de référence. Cette fonction est particulièrement utile pour créer des références dynamiques, des plages de données évolutives ou pour accéder à des informations situées à une position relative dans une feuille de calcul.
Sommaire
La syntaxe de la fonction DECALER dans Excel
La syntaxe de la fonction DECALER est la suivante:
Les paramètres de la fonction DECALER
Comme vous pouvez voir, cette fonction a 5 arguments dont 3 sont obligatoires et 2 sont facultatifs. Voyons les en détail…
Réf: La cellule de départ à partir de laquelle vous souhaitez effectuer le décalage. Ce paramètre est obligatoire.Lignes: Le nombre de lignes à décaler à partir de la cellule de référence (Réf). Une valeur positive décale vers le bas, tandis qu'une valeur négative décale vers le haut. Ce paramètre est obligatoire.Colonnes: Le nombre de colonnes à décaler à partir de la cellule de référence (Réf). Une valeur positive décale vers la droite, tandis qu'une valeur négative décale vers la gauche. Également obligatoire.Hauteur: Le nombre de lignes de la plage renvoyée. Si ce paramètre est omis, la plage renvoyée aura la même hauteur que la cellule de référence (Réf).Largeur: Le nombre de colonnes de la plage renvoyée. Si ce paramètre est omis, la plage renvoyée aura la même largeur que la cellule de référence (Réf).
Plusieurs exemples d'utilisation se trouvent plus loin…
Pour information:
- DECALER est une fonction volatile et peut entraîner des problèmes de performances dans les feuilles de calcul volumineuses ou complexes.
- l'équivalent anglophone de cette fonction est la fonction OFFSET
- DECALER est disponible dans toutes les versions d'Excel à partir de Excel 2003
La fonction DECALER en pratique
Ceci est une des fonctions Excel relativement sous-utilisées. Mais une fois maitrisée, elle peut s'avérer très utile. Comme vous pourrez le constater par exemple dans l'Exemple n°5 dans la section qui suit. Mais d'abord, pour vous éviter des surprises, voici quelques points d'attention lors de l'utilisation de DECALER :
-
- Les lignes et les colonnes peuvent être fournies sous forme de nombres négatifs pour inverser leur sens de décalage normal – les colonnes négatives sont décalées vers la gauche et les lignes négatives sont décalées au-dessus.
- DECALER ne renvoie qu'une référence, aucune cellule n'est déplacée.
- Si le décalage est en dehors du bord de la feuille de calcul, DECALER affichera la valeur d'erreur
#REF!.
- DECALER peut être utilisé avec n'importe quelle autre fonction Excel qui s'attend à recevoir une référence.
- Lorsque la
Hauteuret / ou laLargeurest omise, la hauteur et la largeur de référence sont utilisées.
Exemples d'utilisation de la fonction DECALER
Maintenant que vous avez vu la théorie, voici des exemple pratiques d'utilisation de cette fonction:
Exemple 1) Simple décalage
Commençons par un exemple basique: dans cet exemple, nous allons décaler la cellule A1 de 2 lignes et de deux colonnes. Le résultat sera donc logiquement la valeur de la cellule C3.
Exemple 2) Décalage "négatif"
Cette fois, nous allons décaler la cellule B2 d'une ligne vers le haut (donc -1) et d'une colonne vers la droite (donc 1). Le résultat correspondra alors à la valeur de la cellule C1.
Exemple 3) Décaler une cellule avec dimension différente
Dans cet exemple, la cellule de référence (A1) est une plage d'une seule cellule. Mais le résultat aura la taille 3×3 (donc largeur de 3 cellules et la hauteur de 3 cellules).
Comme vous pouvez le voir, nous indiquons la formule dans une seule cellule mais le résultat s'affiche dans 9 cellules en même temps!
Exemple 4) Décaler une plage de cellules
Essayons maintenant de déplacer une plage de cellules: on va prendre la plage A1:C3 et afficher cette plage décalée d'une ligne (pas de décalage de colonne cette fois-ci). Voici ce que cela donne:
Tout comme dans l'exemple précédent, nous indiquons la formule dans une seule cellule mais le résultat s'affiche dans 9 cellules…
Exemple 5) Utilisation plus complexe de la fonction DECALER
Et pour finir, un exemple pour montrer à quoi peut servir le décalage dans un tableau des ventes mensuelles sur plusieurs années.
Dans cet exemple, la fonction DECALER est utilisée deux fois. D'abord pour afficher les données de l'année choisie (l'année est dans la cellule I2, la formule dans la cellule I3):
Et ensuite pour afficher les données d'une année précédente au choix (N-1, N-2,…). Voir cellule J3 pour la formule:
Ceci vous donne un outil tout à fait dynamique où vous pouvez choisir l'année de référence selon vos besoins et le comparer avec une année précédente au choix…
Télécharger le fichier avec les exemples
Vous pouvez télécharger le fichier XLSX contenant ces exemples via le bouton ci-dessous…
Taille de fichier: 49,9 Ko
Pourquoi utiliser la fonction DECALER sur Excel?
En gros, la maîtrise de la fonction DECALER, vous ouvre la porte à des analyses de données plus avancées et à une gestion de données plus efficace dans Excel. Quelques exemples…
- Adaptabilité aux données dynamiques: La fonction DECALER s'ajuste automatiquement lorsque de nouvelles données sont ajoutées ou supprimées. Cela est particulièrement utile dans les tableaux de données qui sont fréquemment mis à jour, assurant que vos formules restent précises et à jour sans intervention manuelle.
- Création de plages de données flexibles: Avec DECALER, vous pouvez créer des plages de données qui se modifient en fonction de critères spécifiques. Cela est idéal pour les analyses qui nécessitent une sélection de données variable, comme les rapports mensuels ou les comparaisons de périodes.
- Amélioration des Tableaux de bord et des rapports: En utilisant DECALER dans vos tableaux de bord Excel, vous pouvez concevoir des visualisations de données qui s'adaptent dynamiquement aux changements. Cela rend vos rapports plus interactifs et réactifs, offrant une meilleure expérience utilisateur et des insights plus précis.
Conclusion
La fonction DECALER est un outil puissant qui vous permet de manipuler et d'accéder aux données de vos feuilles de calcul Excel de manière dynamique. Grâce à ce tutoriel, vous devriez maintenant être en mesure d'utiliser la fonction DECALER pour renvoyer des cellules ou des plages de cellules décalées, en vous basant sur des références relatives. N'hésitez pas à expérimenter et à combiner cette fonction avec d'autres fonctions Excel pour créer des solutions adaptées à vos besoins spécifiques.
Pour aller plus loin en Excel…
Et si vous voulez explorer un peu plus les possibilités que vous offre Excel, voici quelques articles qui pourraient vous intéresser…
- Comment faire la CONCATÉNATION en Excel (tutoriel)
- Tout sur l'utilisation de Excel en ligne qui est GRATUIT
- Liste de toutes les fonctions Excel (plus de 500 fonctions!)
- Les modes d'emploi des autres principales fonctions Excel: SI, NB.SI, SOMME.SI, RECHERCHEV, RECHERCHEX, …
- Grand tutoriel sur les Tableaux croisés dynamiques Excel
- Vous pouvez également consulter la page dédiée à la fonction DECALER sur le site de Microsoft.
