Vos données Excel changent souvent, il est donc utile de créer une plage définie dynamique qui s'étend et se contracte automatiquement à la taille de votre plage de données. Voyons comment.
Lors de l'utilisation d'une plage dynamique définie, vous n'aurez pas besoin de modifier manuellement les plages de vos formules, graphiques croisés dynamiques et tableaux lorsque les données changent. Cela se fera automatiquement.
Deux formules sont utilisées pour créer des plages dynamiques: DÉCALAGE et INDEX. Cet article se concentrera sur l'utilisation de la fonction INDEX, car c'est une approche plus efficace. 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 à colonne unique vue ci-dessous.
Nous avons besoin que cela soit dynamique, donc si plus de pays sont ajoutés ou supprimés, la gamme se met à jour automatiquement.
Pour cet exemple, nous voulons éviter la cellule d'en-tête. En tant que tel, nous voulons la gamme $ UNE $ 2: $ UNE $ 6, mais dynamique. Pour ce faire, cliquez sur Formules> Définir le nom.
Scribe “les pays” dans le cadre “nom” puis entrez la formule ci-dessous dans la zone “Fait référence à”.
=$A$2:INDICE($UNE:$UNE,COUNTA($UNE:$UNE))
Taper cette équation dans une cellule de votre 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épart de la plage (A2 dans notre cas) puis suivez l'opérateur de plage (:).
=$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 alors utilisée avec la fonction COUNT. COUNT compte le nombre de cellules qui ne sont pas vides dans la colonne A (six dans notre cas).
INDICE($UNE:$UNE,COUNTA($UNE:$UNE))
Cette formule demande à la fonction INDEX de renvoyer la plage de la dernière cellule non vide de la colonne A ($ UNE $ 6).
Le résultat final est $ UNE $ 2: $ UNE $ 6, et à cause de la fonction COUNT, c'est dynamique, puisqu'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, formule, graphique ou où nous devons nous référer aux noms de tous les pays.
Créer une plage dynamique bidirectionnelle définie
Le premier exemple n'était dynamique qu'en hauteur. Malgré cela, avec une légère modification et une autre fonction COUNT, 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 dynamique définie, qui comprend les en-têtes. Cliquez sur Formules> Définir le nom.
Scribe “Ventes” dans le cadre “nom” et entrez la formule ci-dessous dans la case “Fait référence à”.
=$A$1:INDICE($1:$1048576,COUNTA($UNE:$UNE),COUNTA($1:$1))
Cette formule utilise $ UNE $ 1 comme cellule de départ. Ensuite, La fonction INDEX utilise une plage de la feuille de calcul entière ($ 1: $ 1048576) chercher et retourner.
Une des fonctions COUNTA est utilisée pour compter les lignes qui ne sont pas vides, et un autre est utilisé pour les colonnes qui ne sont pas vides, ce qui le rend dynamique dans les deux sens. Même si cette formule est partie de A1, aurait pu spécifier n'importe quelle cellule de départ.
Vous pouvez maintenant utiliser ce nom défini (Ventes) dans une formule ou sous forme de série de données de graphique pour la rendre dynamique.
