Dans mon article récent, j’ai tout parlé de lien: / excel-range-name-all-about-excel-named-ranges-excel-range-name [plages nommées dans Excel]. Lors de l’exploration des plages nommées, le sujet de la plage dynamique est apparu. Donc, dans cet article, je vais expliquer, comment pouvez-vous créer une plage dynamique dans Excel.

243

Qu’est-ce que la plage nommée dynamique dans Excel?

Une plage nommée normale est statique. Si vous définissez C2: C10 comme élément, l’élément fera toujours référence à C2: C10, jusqu’à ce que et à moins que vous ne le modifiiez manuellement. Dans l’image ci-dessous, nous comptons les blancs dans la liste des éléments. Il montre 2.

S’il était dynamique, il aurait montré 0.

244

Une plage de noms dynamique est une plage de noms qui se développe et se réduit en fonction des données. Par exemple, si vous avez une liste d’éléments dans la plage C2: C10 et que vous la nommez Items, elle devrait s’étendre à C2: C11 si vous ajoutez un nouvel élément dans la plage et devrait se réduire si vous réduisez lorsque vous supprimez comme ci-dessus.

Comment créer une plage de noms dynamique

Créer des plages nommées à l’aide de tableaux Excel

Oui, link: / table-excel-2007-17-amazing-features-of-excel-tables [excel tables] peut créer des plages nommées dynamiques. Ils créeront chaque colonne dans une table nommée plage qui est hautement dynamique.

Mais les noms de table présentent un inconvénient: vous ne pouvez pas les utiliser dans la validation des données et la mise en forme conditionnelle. Mais des plages nommées spécifiques peuvent y être utilisées.

Utiliser les formules INDIRECT et COUNTA Pour dynamiser une plage de noms, nous pouvons utiliser link: / lookup-formulas-excel-indirect-function [INDIRECT]

et link: / counting-excel-counta-function [fonction COUNTA]

  1. Comment? Voyons voir.

Formule générique à écrire dans Se réfère à: section

=INDIRECT("$startingCell:$endingColumnLetter$"&COUNTA($columnLetter:$columnLetter))

La formule générique ci-dessus peut sembler complexe mais elle est en fait facile. Voyons par un exemple.

L’idée de base est de déterminer la dernière cellule utilisée.

Exemple de plage dynamique Dans l’exemple ci-dessus, nous avons eu un élément de plage de nom statique dans la plage C2: C10. Rendons-le dynamique.

245

  • Ouvrez le gestionnaire de noms en appuyant sur CTRL + F3.

    • Maintenant, si un nom existe déjà sur la plage, cliquez dessus puis cliquez sur modifier. Sinon, cliquez sur Nouveau.

    • Nommez-le Item.

    • Dans se réfère à: Section écrire sous la formule.

=INDIRECT("$C2:$C$"&COUNTA($C:$C))

  • Appuyez sur le bouton OK.

Et c’est fait. Maintenant, chaque fois que vous tapez Item dans la zone de nom ou dans n’importe quelle formule, il fera référence à C2 à la dernière cellule utilisée dans la plage.

246

Attention: aucune cellule ne doit être vide entre les plages. Sinon, la plage sera réduite du nombre de cellules vides.

Comment ça marche?

Comme je l’ai dit, sa seule question est de trouver la dernière cellule utilisée. Pour cet exemple, aucune cellule ne doit être vide entre les deux. Pourquoi? Tu sauras.

La fonction INDIRECT d’Excel convertit un texte en plage.

lien: / lookup-formulas-excel-indirect-function [INDIRECT] (« $ C $ 2: $ C $ 9 »)

fera référence à la plage absolue $ C $ 2: $ C $ 10. Nous avons juste besoin de trouver le dernier numéro de ligne dynamiquement (9).

Puisque toutes les cellules ont une valeur dans la plage C2: C10, nous pouvons utiliser link: / counting-excel-counta-function [fonction COUNTA] pour trouver la dernière ligne.

Donc, = INDIRECT (« $ C2: $ C $ » & cette partie corrige la ligne et la colonne de départ et COUNTA ($ C: $ C) calcule dynamiquement la dernière ligne utilisée.

Alors oui, voici comment vous pouvez Créez les plages nommées dynamiques les plus efficaces qui fonctionneront avec toutes les formules et fonctionnalités d’Excel. Vous n’avez pas besoin de modifier à nouveau votre plage nommée lorsque vous modifiez des données.

Télécharger le fichier: