in

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

Logo 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 en fonction de 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 de données à une seule colonne ci-dessous.

Plage de données pour rendre dynamique

Nous avons besoin que cela soit dynamique afin que si d’autres pays sont ajoutés ou supprimés, la plage se met automatiquement à jour.

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

Créer un nom défini dans Excel

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))

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

Utiliser une formule dans un nom défini

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 de 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 par «pays» dans une règle de validation des données, une formule, un graphique ou partout où nous avons besoin de référencer les noms de tous les pays.

Créer une plage définie dynamique bidirectionnelle

Le premier exemple était uniquement dynamique en hauteur. Cependant, avec une légère modification et une autre fonction COUNTA, vous pouvez créer une plage dynamique en hauteur et en largeur.

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

Données pour une plage dynamique bidirectionnelle

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

Créer un nom défini dans Excel

Tapez «ventes» dans la case «Nom» et entrez la formule ci-dessous dans la case «Se réfère à».

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

Formule de plage définie dynamique bidirectionnelle

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.

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ébut.

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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Renommez le compte invité dans Windows 7 pour une sécurité renforcée

Renommez le compte invité dans Windows 7 pour une sécurité renforcée

Que fait le BIOS d'un PC et quand dois-je l'utiliser?

Que fait le BIOS d’un PC et quand dois-je l’utiliser?