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

Vos données Excel changent fréquemment, il est donc utile de créer une plage dynamique définie qui s’étend et se contracte automatiquement à la taille de votre plage de données. Voyons comment.

En utilisant une plage dynamique définie, vous n’aurez pas besoin de modifier manuellement les plages de vos formules, graphiques et tableaux croisés dynamiques lorsque les données changent. Cela se fera automatiquement.

Deux formules sont utilisées pour créer des gammes dynamiques : OFFSET et INDEX. Cet article se concentrera sur l’utilisation de la fonction INDEX, qui est une approche plus efficace. La fonction OFFSET est une fonction volatile et peut ralentir les grandes feuilles de calcul.

Créer une plage dynamique définie dans Excel

Pour notre premier exemple, nous avons la liste de données en une seule colonne ci-dessous.

Nous avons besoin d’un système dynamique, de sorte que si d’autres pays sont ajoutés ou supprimés, la gamme se met automatiquement à jour.

Pour cet exemple, nous voulons éviter la cellule d’en-tête. Nous voulons donc la plage $A$2:$A$6, mais dynamique. Pour ce faire, cliquez sur Formules > Définir le nom.

Tapez « pays » dans la case « Nom » et entrez ensuite la formule ci-dessous dans la case « Références ».

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

Il est parfois plus rapide et plus facile de taper cette équation dans une cellule de tableur et de la copier ensuite dans la zone « Nouveau nom ».

Comment cela fonctionne-t-il ?

La première partie de la formule spécifie la cellule de départ de la gamme (A2 dans notre cas) et ensuite l’opérateur de gamme ( 🙂 suit.

=$A$2:

L’utilisation de l’opérateur de plage oblige la fonction INDEX à renvoyer une plage au lieu de la valeur d’une cellule. La fonction INDEX est alors utilisée avec la fonction COUNTA. COUNTA compte le nombre de cellules non vides dans la colonne A (six dans notre cas).

INDEX($A:$A,COUNTA($A:$A))

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

Le résultat final est $A$2:$A$6, et grâce à la fonction COUNTA, il est dynamique, car il trouvera la dernière ligne. Vous pouvez maintenant utiliser ce nom défini de « pays » dans une règle de validation des données, une formule, un tableau, ou partout où nous avons besoin de référencer les noms de tous les pays.

Créer une gamme dynamique bidirectionnelle définie

Le premier exemple n’était que dynamique en hauteur. Cependant, avec une légère modification et une autre fonction COUNTA, vous pouvez créer une gamme qui est dynamique à la fois en hauteur et en largeur.

Dans cet exemple, nous utiliserons les données présentées ci-dessous.

Cette fois, nous allons créer une gamme dynamique définie, qui comprend les en-têtes. Cliquez sur Formules > Définir le nom.

Tapez « ventes » dans la case « Nom » et entrez la formule ci-dessous dans la case « Références ».

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

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

Une des fonctions COUNTA est utilisée pour compter les lignes non vides, et une autre est utilisée pour les colonnes non vides, ce qui la rend dynamique dans les deux sens. Bien que cette formule parte de A1, vous auriez pu spécifier n’importe quelle cellule de départ.

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