Comment créer une plage définie dynamique dans Excel



Les données dans Excel sont souvent sujettes à des mises à jour. Il est donc très pratique de créer une plage nommée dynamique qui s’ajuste automatiquement en fonction de l’étendue des données. Découvrons comment procéder.

L’avantage d’utiliser une plage nommée dynamique est que vous n’aurez plus besoin de modifier manuellement les références de plages dans vos formules, graphiques et tableaux croisés dynamiques lorsque les données évoluent. Tout se fera de manière automatique.

Deux formules sont généralement utilisées pour créer des plages dynamiques : DECALER et INDEX. Cet article se concentrera sur l’utilisation de la fonction INDEX, car elle est plus performante. La fonction DECALER, étant volatile, peut ralentir les grands classeurs.

Créer une plage nommée dynamique dans Excel

Pour illustrer notre premier exemple, nous utiliserons la liste de données à une seule colonne présentée ci-dessous.

Notre objectif est de rendre cette plage dynamique. Ainsi, si de nouveaux pays sont ajoutés ou supprimés, la plage se mettra à jour en conséquence.

Pour cet exemple, nous ne voulons pas inclure la cellule d’en-tête. Nous voulons donc que la plage soit $A$2:$A$6, mais de manière dynamique. Pour cela, allez dans l’onglet Formules et cliquez sur Définir un nom.

Dans le champ « Nom », saisissez « pays ». Ensuite, dans le champ « Fait référence à », entrez la formule suivante :

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

Il est parfois plus rapide et plus facile de saisir cette équation dans une cellule de feuille de calcul, puis de la copier dans la zone Nouveau nom.

Comment ça marche?

La première partie de la formule spécifie la cellule de départ de la plage (A2 dans notre cas), suivie de l’opérateur de plage (:) :

=A$2:

L’utilisation de l’opérateur de plage force la fonction INDEX à renvoyer une plage et non la valeur d’une seule cellule. La fonction INDEX est ensuite combinée à la fonction NBVAL. NBVAL compte le nombre de cellules non vides dans la colonne A (six dans notre cas).

INDEX(A:A,NBVAL(A:A))

Cette formule demande à la fonction INDEX de renvoyer la plage allant jusqu’à la dernière cellule non vide de la colonne A ($A$6).

Le résultat final est $A$2:$A$6, et grâce à la fonction NBVAL, elle est dynamique, car elle trouvera la dernière ligne. Vous pouvez maintenant utiliser ce nom nommé « pays » dans une règle de validation de données, une formule, un graphique ou partout où vous avez besoin de référencer les noms de tous les pays.

Créer une plage nommée dynamique bidirectionnelle

Le premier exemple était dynamique seulement en hauteur. Cependant, avec une légère modification et une autre fonction NBVAL, il est possible de créer une plage dynamique à la fois en hauteur et en largeur.

Pour cet exemple, nous utiliserons les données ci-dessous.

Cette fois, nous allons créer une plage nommée dynamique incluant les en-têtes. Allez dans l’onglet Formules et cliquez sur Définir un nom.

Dans le champ « Nom », saisissez « ventes », puis dans le champ « Fait référence à », entrez la formule suivante :

=A$1:INDEX(1:1048576,NBVAL(A:A),NBVAL(1:1))

Cette formule utilise $A$1 comme cellule de départ. La fonction INDEX utilise ensuite la plage de la feuille entière ($1:$1048576) pour rechercher et renvoyer.

Une fonction NBVAL est utilisée pour compter les lignes non vides et une autre pour les colonnes non vides, rendant ainsi la plage dynamique dans les deux directions. Bien que cette formule commence à partir de A1, vous pourriez choisir n’importe quelle cellule de départ.

Vous pouvez maintenant utiliser ce nom défini (« ventes ») dans une formule ou comme série de données de graphique pour les rendre dynamiques.