Comment créer une référence automatiquement sur Excel ? La fonction DECALER()

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce tutoriel, nous allons découvrir une fonction très pratique d’Excel : la fonction DECALER(). Celle-ci permet de générer une référence à une ou plusieurs cellules de manière dynamique. Il suffit en effet de partir d’une cellule de départ puis d’effectuer un décalage exprimé en nombre de lignes ou de colonnes.

Lisez bien ce tutoriel jusqu’au bout, nous y découvrirons en effet de nombreux exemples pour apprendre à mettre cette fonction en pratique. Nous pourrons ainsi utiliser la fonction DECALER() pour mettre en place un menu déroulant dynamique très simplement.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

 

1. Présentation de la fonction DECALER()

La fonction DECALER() que nous allons découvrir dans ce tutoriel a été introduite avec la version 2003 d’Excel.

Elle permet d’obtenir une référence d’une ou plusieurs cellules, positionnée(s) à un endroit donné à partir d’une cellule de départ, en effectuant comme son nom l’indique un décalage de cellules sur la feuille de calcul.

La fonction DECALER() attend cinq arguments :

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

Où :

  • Réf : il s’agit de la cellule, ou de la plage constituée de plusieurs cellules qui vont servir de point de départ à la fonction DECALER()
  • Lignes et Colonnes : ces deux arguments permettent de spécifier le décalage à effectuer à partir de la référence de départ. Une valeur positive permet d’effectuer un décalage vers la droite ou vers le bas, tandis qu’une valeur négative permet d’effectuer un décalage vers la gauche ou vers le haut.
  • Hauteur et Largeur : ces deux arguments facultatifs permettent de spécifier le nombre de cellules à récupérer. Si ces arguments sont omis, la référence retournée sera identique à la référence de départ.

 

2. Exemples

Pour illustrer le fonctionnement de DECALER(), voyons quelques exemples.

Pour cela, nous allons utiliser le tableau de synthèse des ventes mensuelles d’une entreprise pour les trois dernières années :

Excel formation - decaler - 01

 

   2.1. Récupérer une cellule avec la fonction DECALER()

 

Pour récupérer une valeur avec la fonction DECALER(), nous allons partir d’une cellule, ici la cellule A7, située tout en haut à gauche du tableau.

Puis, les paramètres Lignes et Colonnes vont nous permettre de spécifier le décalage à opérer.

Pour obtenir les ventes du mois mai 2020, nous allons donc effectuer un décalage de cinq cellules vers le bas afin d’atteindre le mois de mai, puis de trois cellules vers la droite pour l’année 2020 :

 =DECALER(A7;5;3) 

Excel formation - decaler - 02

 

   2.2. Récupérer une valeur dynamiquement

 

En suivant le même principe, nous pouvons également souhaiter récupérer une valeur dynamiquement en utilisant la fonction DECALER().

Pour cela, nous pouvons utiliser l’une des fonctions de recherche, comme par exemple EQUIV() que nous avons pu découvrir dans le détail dans le tutoriel dédié à la fonction INDEX-EQUIV().

La fonction EQUIV() permet en effet de connaître la position d’un élément au sein d’une matrice.

Par exemple, pour connaître la position du mois de mai dans la première colonne, nous utiliserons la formule suivante :

 =EQUIV(I10;A8:A19;0) 

Excel formation - decaler - 03

Excel nous retourne alors la valeur 5.

Ensuite, pour obtenir le résultat souhaité, il suffit d’utiliser ces fonctions EQUIV() en tant qu’argument Lignes et Colonnes :

 =DECALER(A7;EQUIV(I10;A8:A19;);EQUIV(I11;B7:D7;))  

Excel formation - decaler - 04

 

   2.3. La fonction DECALER() comme argument de fonction

 

Comme nous l’avons vu un peu plus tôt, la fonction DECALER() permet de générer une référence à une plage de cellules.

Nous pouvons donc l’utiliser pour établir automatiquement les coordonnées de la plage à inclure en tant qu’argument d’une autre fonction.

Par exemple, imaginons que nous souhaitions calculer la somme des ventes réalisées au cours d’une année (sans utiliser la ligne total évidement).

Pour cela, nous pourrons faire appel à l’argument facultatif hauteur pour récupérer la somme des 12 lignes :

 =SOMME(DECALER(A7;1;3;12)) 

Excel formation - decaler - 05

 

Nous pouvons également utiliser cette méthode pour calculer le montant des ventes trimestrielles :

 =SOMME(DECALER(A7;(I17-1)*3+1;EQUIV(I16;B7:D7;);3))  

Excel formation - decaler - 06

Ici, nous commençons par calculer la ligne sur laquelle se trouve le premier mois du trimestre que nous obtenons avec le calcul :

 (I17-1)*3+1 

Puis pour récupérer la colonne qui correspond à l’année souhaitée, nous utilisons la portion de code découverte dans la sous-partie précédente :

 EQUIV(I16;B7:D7;) 

Et pour finir, nous demandons à Excel de retourner la plage des cellules qui correspond à trois cellules de haut.

 

   2.4. Mettre en plus un menu déroulant dynamique

 

Enfin, comme nous l’avons déjà vu dans le tutoriel dédié au menus déroulants en cascades, il est possible d’en automatiser la construction en utilisant la validation des données.

Pour cela, nous allons partir de la base des données suivante dans laquelle nous retrouvons une liste des grandes villes classées par pays :

Excel formation - decaler - 07

Nous souhaitons alors pouvoir sélectionner l’une de ces villes automatiquement après avoir choisi le pays correspondant.

Pour cela, nous commençons par sélectionner la cellule dans laquelle nous souhaitons insérer le menu déroulant, puis nous nous rendons dans le menu Données > Validation des données :

Excel formation - decaler - 08

Dans la fenêtre qui s’affiche, nous choisissons de valider les données en fonction d’une liste, puis dans le champ source, nous saisissons la formule :

 =DECALER(A6;1;EQUIV(B11;B6:G6;0);3) 

Excel formation - decaler - 09

Avec cette formule, nous souhaitons récupérer les trois lignes de la colonne dans laquelle se trouve le nom du pays saisi dans la cellule B11 :

Excel formation - decaler - 10

 

 



Articles qui pourraient vous intéresser

Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?
Comment identifier la cellule qui se répète le plus de fois sur Excel ? (Fonction MODE)
Comment améliorer votre gestion de données avec les fonctions base de données d’Excel ?
Créer une somme intelligente et dynamique sur Excel sans VBA
Quelle fonction permet de lister le nom des feuilles de calcul Excel sans VBA ?
Comment créer un décompte entre deux dates (jours, heures, minutes et secondes) sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.