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

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

En utilisant une plage définie dynamique, 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 produira automatiquement.

Deux formules sont utilisées pour créer des plages dynamiques: OFFSET et INDEX. Cet article se concentrera sur l’utilisation de la fonction INDEX car il s’agit d’une approche plus efficace. OFFSET est une fonction volatile et peut ralentir les grandes feuilles de calcul.

Créer une plage définie dynamique dans Excel

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

Nous avons besoin que cela soit dynamique pour que si plus de pays sont ajoutés ou supprimés, la gamme est automatiquement mise à jour.

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

Tapez « pays » dans la case « Nom » puis entrez la formule ci-dessous dans la case « Fait référence à ».

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

La saisie de cette équation dans une cellule de feuille de calcul, puis sa copie dans la zone Nouveau nom est parfois plus rapide et plus facile.

Comment cela marche-t-il?

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

= $ A $ 2:

L’utilisation de l’opérateur de plage force la fonction INDEX à renvoyer une plage au lieu de la valeur d’une cellule. La fonction INDEX est ensuite 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 vide dans la colonne A ($ A $ 6).

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

Créer une plage définie dynamique bidirectionnelle

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 plage dynamique à la fois en hauteur et en largeur.

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

Cette fois, nous allons créer une plage définie dynamique, 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 «Fait référence à».

= $ 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 rechercher et retourner.

L’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 ait commencé à partir 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 de graphique pour les rendre dynamiques.

Voici quelques sources : Microsoft Support  -  Wikipedia windows