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 dynamique définie 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 pour 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 «Se réfère à».

=$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 maintenant 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.

En relation :  Comment jouer à Myst sur votre smartphone

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.

Moyens Staff
Moyens I/O Staff vous a motivé, donner des conseils sur la technologie, le développement personnel, le style de vie et des stratégies qui vous aider.