Tuto Excel : créer une plage nommée dynamique

Ce tutoriel explique comment créer des plages nommées dynamiques dans Excel. Il vous montre également comment les utiliser dans des formules pour avoir des mises à jour automatiques des valeurs.

Dans le tutoriel précèdent, nous avons passé en revue différentes méthodes pour créer des plages nommées statiques. En effet, ce genre de plages nommées fait toujours référence à la même cellule. Ainsi, vous devrez mettre à jour manuellement la plage de référence chaque fois que vous ajoutez une nouvelle entrée ou que vous supprimez une. Cela peut être déroutant, surtout lorsque vous travaillez sur des feuilles de calculs qui subissent à des changements réguliers. Chaque fois que vous obtenez de nouvelles données, vous devrez peut-être mettre à jour les plages nommées dans Excel.

Pour résoudre ce problème, vous pouvez créer des plages nommées dynamiques qui prendront automatiquement en compte les données supplémentaires et les incluront dans la plage nommée existante.

Table des matières

Comment créer une plage nommée dynamique dans Excel /

Définir une plage nommée avec la formule DECALER NBVAL

Définir une plage nommée avec la formule INDEX NBVAL

Comment créer une plage dynamique bidimensionnelle dans Excel ?

Création d'une plage nommée dynamique en créant une table

Créer une liste déroulante avec une plage nommée dynamique

Règles de création d'une plage nommée dynamique dans Excel

Comment créer une plage nommée dynamique dans Excel ?

Commençons par un exemple simple. Dans la capture d’écran ci-dessous, nous avons une liste de produits dans la colonne « A » et les ventes mensuelles associées dans la colonne « B ». Ici, de nouveaux produits peuvent s’ajouter à la liste selon les ventes. Il s’agit alors d’un nombre de ligne variable.

Afin de créer une plage nommée dynamique qui prendra en compte les nouvelles lignes qui seront ajoutées plu tard, suivez les étapes ci-dessous :

  1. Accédez à l’onglet « Formules».
  2. Dans le groupe « Noms définis», cliquez sur le bouton « Définir un nom ». Vous pouvez également utiliser le raccourci clavier « Ctrl + F3 ».
  1. La boite de dialogue « Nouveau nom» s’ouvre. Tapez le nom que vous souhaitez dans la zone « Nom ». Pour cet exemple, nous allons saisir « Produits ».
  1. Ensuite, dans « Zone», sélectionnez la portée de cette plage nommée. Vous pouvez choisir « Classeur » ou sélectionner une feuille de calcul particulière.
  1. Dans la zone « Fait référence à», utilisez l’une des formules DECALER NBVAL ou INDEX NBVAL expliquées ci-dessous.

Définir une plage nommée avec la formule DECALER NBVAL

La formule la plus utilisée pour créer des plages nommées dynamiques dans Excel est la formule DECALER NBVAL.

La syntaxe de la fonction DECALER est :

DECALER(réf ; lignes ; colonnes ; [Hauteur] ; [Largeur])

La syntaxe de la fonction NBVAL est :

NBVAL(valeur1 ; valeur2 ;…)

La formule à utiliser pour créer une plage nommée dynamique est comme suit :

DECALER(première_cellule ;0 ;0 ; NBVAL(colonne) ;1)

  • Le premier argument « première_cellule » est le premier élément à inclure dans la plage nommée. Dans cet exemple, il s’agit de la cellule $A$2. Vous devez le remplacer par la référence de la première cellule dans votre liste. Donc, si la première cellule était « C1 », vous utiliseriez : $C$1.
  • Les arguments « lignes et colonnes » sont toutes les deux égales à , car il n'y a ni colonnes ni lignes à décaler.
  • Dans le quatrième argument de la fonction DECALER, nous utilisons la formule NBVAL. Cette formule, retourne le nombre de cellules non vides dans la colonne A. Ce nombre sera transmis comme argument « Hauteur » à la fonction DECALER pour lui indiquer le nombre de ligne à retourner.
  • Le seul argument de la fonction NBVAL est la référence absolue de la colonne contenant votre liste. Dans cet exemple, nous utilisons $A:$A puisque la liste des produits est dans la colonne A.
  • Pour le dernier argument de la fonction DECALER, laissez-le vide (car il est facultatif) ou entrez simplement 1 (la largeur de la plage est de 1 colonne).

Dans l’exemple ci-dessus, pour créer une plage nommée pour la colonne A contenant les noms des produits, nous allons entrer la formule ci-dessous dans la zone « Fait référence à » :

=DECALER(Feuil1!$A$2; 0; 0; NBVAL(Feuil1!$A:$A); 1)

Comme le montre la capture ci-dessous, nous avons créé une plage nommée pour la liste des produits de la colonne A sans prendre en compte l’entête.

Cliquez sur « Ok » une fois vous avez terminé.

Notez qu’Excel ajoute le nom de la feuille de calcul actuelle à la formule. Au cas où vous faites référence à une cellule dans une autre feuille de calcul, vous devez inclure son nom de la même manière que dans la formule ci-dessus.

Remarque :

La fonction DECALER peut être utilisée sans les arguments optionnels « hauteur » et « largeur », pour renvoyer la valeur d'une cellule. Si nous utilisons ces arguments facultatifs, la formule renverra une plage et sera utilisée comme formule autonome dans une cellule qu'elle résoudra comme une erreur. Mais utilisée dans la fenêtre « Nouveau nom », elle renvoie une plage, qui peut être utilisée pour créer une plage qui se met à jour en fonction du nombre d'éléments dans la liste des produits.

Définir une plage nommée avec la formule INDEX NBVAL

Vous pouvez également utiliser une autre formule basée sur la fonction INDEX et la fonction NBVAL dans la zone de texte « Fait référence à » pour créer une plage nommée dynamique dans Excel.

La syntaxe de la formule INDEX NBVAL est comme suit :

  • Première_cellule :INDEX(colonne ; NBVAL(colonne))
  • « Première_cellule : » représente la référence absolue de la première cellule dans la liste.

Pour la suite de la formule, nous utilisons la fonction INDEX(matrice ; no_lig ; no_col) en combinaison avec la fonction NBVAL(valeur1 ;valeur2 ;…) pour retourner la référence de fin.

  • Dans l’argument « matrice » de la fonction INDEX,nous allons fournir toute la colonne A, et dans l’argument « no_lig » nous allons utiliser la fonction NBVAL pour obtenir le nombre de cellules non vides dans la colonne A.

Ainsi, la formule à utiliser dans cet exemple est comme suit :

=$A$2:INDEX($A:$A; NBVAL($A:$A))

Comment fonctionne cette formule ?

Nous avons notre liste de produits dans la colonne A, avec 10 cellules non vides (y compris l’entête). Ainsi, la fonction NBVAL retourne 10. Ce nombre est fourni à la fonction INDEX comme argument « no_lig ». Par conséquent, la fonction INDEX retourne $A$10 qui est le dernier élément de la liste. Et puisque nous avons défini la première cellule sur $A$2,la formule retourne donc la référence $A$2:$A$10 qui sera utilisée comme plage de référence dans la zone « Fait référence à » de la boite de dialogue « Nouveau nom ».

Nous pouvons maintenant utiliser cette plage nommée dans des formules. Par exemple, pour calculer le nombre de produits dans la colonne A, nous allons utiliser la formule ci-dessous :

=NBVAL(Produits)

Pour vous assurer qu’il s’agit bien d’une plage nommée dynamique, nous allons essayer d’ajouter un nouvel élément à la liste et nous allons voir si le résultat de notre formule sera mis à jour ou non.

Comment créer une plage dynamique bidimensionnelle dans Excel ?

Nous avons, ci-dessous, un ensemble de données dans lequel non seulement le nombre de lignes est dynamique, mais également le nombre de colonnes.

 Nous souhaitons créer une plage nommée bidimensionnelle. Pour réaliser une telle tâche, nous allons apporter des modifications à la formule INDEX NBVAL comme suit :

Première_cellule :INDEX($1$1048567 ;NBVAL(première_colonne) ;NBVAL(première_ligne))

  • Nous utilisons la première fonction NBVAL comme argument « no_lig » de la fonction INDEX pour retourner la dernière ligne non vide.
  • La deuxième fonction NBVAL est utilisée comme argument « no_col » pour retourner la référence de la dernière colonne non vide.
  • Dans l’argument « matrice » de la fonction INDEX, nous utilisons la référence $1:$1048576 pour désigner la feuille de calcul entière. Cette référence est valide uniquement pour Excel 2007-2016, si vous avez la version 2003 ou une version antérieure, utilisez $1:$65535 à la place.

Nous allons maintenant utiliser cette formule pour définir une plage nommée bidimensionnelle pour cet ensemble de données que nous appellerons « Ventes ». Cette plage va inclure les chiffres de ventes des deux mois (Juin et juillet) et s’ajustera à chaque fois qu’une nouvelle ligne (produit) ou colonne (mois) s’ajoute.

Les données de ventes commencent à B2. La formule est donc :

=$B$2:INDEX($1:$1048576; NBVAL($B:$B); NBVAL($2:$2))

Vous pouvez maintenant tester votre plage nommée. Pour ce faire, insérez les deux formules ci-dessous dans la feuille de calcul :

=SOMME(Ventes)

=SOMME(B2:C4)

Ces deux formules vont calculer la somme de toutes les ventes mensuelles.

Comme vous pouvez le voir, les deux formules retournent le même résultat. Cependant, lorsque vous insérez une nouvelle ligne ou une nouvelle colonne dans votre ensemble de données, le résultat de la formule utilisant la plage nommée sera automatiquement mis à jour et celui de la deuxième formule reste intacte.

Création d'une plage nommée dynamique en créant une table

Il existe une autre méthode, que la plupart ne connait pas, pour créer une plage nommée dynamique dans Excel. Cette méthode consiste à créer un tableau basé sur la plage nommée existante. Ensuite, cette plage nommée sera modifiée en tant que plage nommée dynamique. Si vous êtes intéressé par cette méthode, suivez les étapes ci-dessous :

  1. Tout d’abord, sélectionnez les cellules pour lesquelles vous souhaitez créer une plage nommée dynamique (A1:A4 dans cet exemple).
  2. Ensuite, tapez le nom que vous souhaitez affecter à la plage dans la zone « Nom » comme illustré ci-dessous :
  1. Accédez à l'onglet « Insertion » et cliquez sur la commande « Tableau » dans le groupe « Tableaux ».
  1. La boîte de dialogue « Créer un tableau» apparaîtra.
  2. Cochez ou décochez l’option « Ma table a des en-têtes» dans la boîte de dialogue « Créer un tableau », selon que vos données ont un en-tête ou non.
  3. Cliquez sur le bouton « OK ».

Vous pouvez maintenant ajouter une valeur après les données de la plage nommée. Ici, nous avons ajouté une nouvelle ville « Ifrane ». Ainsi, une nouvelle cellule est ajoutée dans le tableau et également dans la plage nommée.

Créer une liste déroulante avec une plage nommée dynamique

Dans cette section de notre tutoriel, nous allons rendre les choses un peu complexes ! Nous allons travailler sur l’exemple précèdent, mais cette fois-ci, nous allons créer une liste déroulante qui affiche les noms des villes que nous avons dans la plage de cellules A1:A4. Cependant, la plage nommée doit être dynamisée, de sorte que quel que soit le nombre de villes que nous ajoutons, elle continuera à s'étendre.

Pour réussir cette tâche, nous allons utiliser l’une des formules déjà vues dans les sections précédentes. Nous allons opter pour la formule DECALER NBVAL, puisqu’elle est plus facile à implémenter.

Accédez à l’onglet « Formules » et cliquez sur « Définir un nom ». Tapez un nom dans la zone « Nom » et puis dans le champ « Fait référence à », entrez la formule suivante :

=DECALER(Feuil3!$A$2; 0; 0; NBVAL($A:$A) ;1)

La syntaxe :

  • Dans le premier argument de cette formule « réf », entrez la première cellule de la plage contenant les villes (A2). Cela indique à Excel où commence la plage nommée.
  • Pour les deux arguments « ligne » et « colonne », entrez , car cette cellule d'origine « A2 » restera fixe en tant que début de la liste.
  • L’argument « hauteur » est la hauteur de notre plage. En entrant 4, par exemple, la plage sera composée de 4 cellules. Elle sera donc : A2:A5 (ce qui signifie 4 cellules). Pour rendre cette hauteur dynamique au lieu d'un nombre statique pour cet argument, nous avons utilisé une autre formule qui est NBVAL, qui compte le nombre de cellules non vides dans une plage.
  • La formule NBVAL : =NBVAL($A:$A) compte le nombre de cellules non vides dans la colonne A.
  • Dans la liste initiale des villes, cette formule renverra 5 puisque nous avons 5 cellules remplies dans la plage $A$A.
  • Dans le second cas, lorsque nous avons ajouté deux villes supplémentaires, cette formule a renvoyé 7.
  • L’utilisation de cette formule dans l’argument « hauteur » a rendu dynamique la hauteur de notre plage (formule de décalage).
  • Pour le dernier argument de la fonction DECALER, laissez-le vide (car il est facultatif) ou entrez simplement « 1 » (la largeur de la plage est de 1 colonne).

Pour utiliser cette plage nommée dans un menu déroulant, suivez les étapes ci-dessous :

  1. Commencez par sélectionner la ou les cellules où vous voulez que votre liste déroulante apparaisse.
  2. Ensuite, accédez à l’onglet « Données» et cliquez sur le bouton « Validation des données » dans le groupe « Outils de données ».
  1. Dans la fenêtre de validation des données, choisissez « Liste» dans la section « Autoriser ».
  1. Dans le champ « Source», entrez le nom de la plage nommée (=Ville).
  1. Cliquez sur « OK». Et voilà, notre liste est prête à l’utilisation !

Maintenant vous pouvez tester votre liste déroulante. Essayez d'ajouter d'autres villes à la liste et elles seront immédiatement disponibles dans le menu déroulant.

Règles de création d'une plage nommée dynamique dans Excel

Lors de la définition des noms, vous devez garder à l'esprit certaines choses. Suivez les règles ci-dessous, qui sont prédéterminées par Microsoft :

  • La première lettre du nom doit commencer par une lettre, un trait de soulignement (_) ou une barre oblique inverse (\).
  • Vous ne pouvez pas laisser d'espace entre deux mots.
  • Vous ne pouvez pas utiliser une référence à une cellule comme nom, par exemple, A50, B10, C55, etc.
  • La définition des noms n'est pas sensible à la casse. « MOIS » et « mois » sont les mêmes.

Télécharger le fichier Excel de ce tutoriel

Article publié le 23 Août 2020par Hanane Mouqqadim