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.
Table des matières
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.
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.
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.
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.
Cette fois, nous allons créer une plage 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 «Se réfère à».
=$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.
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 pourriez avoir spécifié 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.