Comment créer une plage nommée dynamique dans Excel ?
Généralement, les plages nommées sont très utiles pour les utilisateurs d’Excel : vous pouvez définir une série de valeurs dans une colonne, lui attribuer un nom, puis faire référence à cette plage par son nom plutôt que par ses coordonnées de cellules. Toutefois, dans la plupart des cas, vous devrez ultérieurement ajouter de nouvelles données pour étendre la plage référencée. Dans ce cas, il vous faudra retourner dans Formules > Gestionnaire de noms et redéfinir la plage afin d’inclure les nouvelles valeurs. Pour éviter cette manipulation répétitive, vous pouvez créer une plage nommée dynamique — ainsi, plus besoin d’ajuster manuellement les références de cellules chaque fois que vous ajoutez une ligne ou une colonne à votre liste !
Créer une plage nommée dynamique dans Excel en créant un tableau
Créer une plage nommée dynamique dans Excel à l’aide d’une fonction
Créer une plage nommée dynamique dans Excel avec du code VBA
Créer une plage nommée dynamique dans Excel en créant un tableau
Si vous utilisez Excel 2007 ou une version ultérieure, la méthode la plus simple pour créer une plage nommée dynamique consiste à transformer vos données en tableau Excel nommé.
Supposons que vous disposiez d’une plage de données comme celle-ci et que vous souhaitiez la transformer en une plage nommée dynamique.
1. Je vais d’abord définir un nom de cellule pour cette plage. Sélectionnez la plage A1:A6, saisissez le nom Date dans la zone Nom, puis appuyez sur la touche Entrée. Définissez de la même manière le nom Saleprice pour la plage B1:B6. J’ai également créé, en parallèle, la formule =SOMME(Saleprice) dans une cellule vide. Voir la capture d’écran :
2. Sélectionnez la plage, puis cliquez sur Insertion > Tableau. Voir la capture d’écran :
3. Dans la boîte de dialogue Créer un tableau, cochez l’option Mon tableau comporte des en-têtes (si la plage ne contient pas d’en-têtes, décochez-la), puis cliquez sur le bouton OK. Vos données sont ainsi instantanément converties en tableau. Voir les captures d’écran :
 |  |  |
4. Lorsque vous saisissez de nouvelles valeurs à la suite des données existantes, la plage nommée s’ajuste automatiquement et la formule associée est mise à jour en conséquence. Consultez les captures d’écran ci-dessous :
 | |  |
Remarques :
1. Les nouvelles données saisies doivent être adjacentes aux données existantes, sans aucune ligne ni colonne vide entre elles et les données déjà présentes.
2. Dans un tableau, vous pouvez insérer des données entre les valeurs existantes.
Créer une plage nommée dynamique dans Excel à l’aide d’une fonction
Dans Excel 2003 ou une version antérieure, la première méthode n’est pas disponible. Voici donc une autre solution. La fonction DECALER( ) peut vous aider, bien qu’elle soit un peu fastidieuse. Supposons que vous ayez une plage de données contenant des plages nommées, par exemple A1:A6, dont le nom est Date, et B1:B6, dont le nom est Saleprice. Par ailleurs, j’ai créé une formule pour Saleprice. Voir la capture d’écran :
Vous pouvez transformer le Nom de la cellule en Nom de la cellule dynamique en suivant les étapes ci-dessous :
1. Cliquez sur Formules > Gestionnaire de noms. Voir la capture d’écran :
2. Dans la boîte de dialogue Gestionnaire de noms, sélectionnez l’élément que vous souhaitez modifier, puis cliquez sur le bouton Modifier.
3. Dans la boîte de dialogue Modifier le nom qui s’ouvre, saisissez la formule suivante : =DECALER(Feuil1!$A$1; 0; 0; NBVAL($A:$A); 1) dans le champ Fait référence à. Voir la capture d’écran :
4. Cliquez ensuite sur OK, puis répétez les étapes 2 et 3 pour copier cette formule =DECALER(Feuil1!$B$1; 0; 0; NBVAL($B:$B)), 1) dans le champ Fait référence à pour le nom Saleprice du nom de la cellule.
5. Les plages nommées dynamiques sont désormais créées. Dès que vous saisissez de nouvelles valeurs après les données existantes, la plage nommée s’ajuste automatiquement et la formule associée est mise à jour en temps réel. Consultez les captures d’écran :
| | |
Remarque : Si des cellules vides se trouvent au milieu de votre plage, le résultat de votre formule sera incorrect. En effet, les cellules non vides ne sont pas prises en compte, ce qui raccourcit artificiellement la plage et exclut les dernières cellules.
Astuce : explication de cette formule :
- =DECALER(référence;lignes;colonnes;[hauteur];[largeur])
- =DECALER(Feuil1!$A$1; 0; 0; NBVAL($A:$A); 1)
- référence correspond à la position de la cellule de départ. Dans cet exemple, il s’agit de Feuil1!$A$1.
- ligne indique le nombre de lignes à décaler vers le bas par rapport à la cellule de départ (ou vers le haut si une valeur négative est utilisée). Dans cet exemple, 0 signifie que la liste commencera dès la première ligne.
- colonne correspond au nombre de colonnes à décaler vers la droite par rapport à la cellule de départ (ou vers la gauche si une valeur négative est utilisée). Dans la formule ci-dessus, 0 indique qu’aucune colonne supplémentaire ne sera ajoutée vers la droite.
- [hauteur] correspond à la hauteur (nombre de lignes) de la plage à partir de la position ajustée. $A:$A permet de compter tous les éléments saisis dans la colonne A.
- [largeur] correspond à la largeur (nombre de colonnes) de la plage à partir de la position ajustée. Dans la formule ci-dessus, la liste s’étend sur une seule colonne.
Vous pouvez personnaliser ces arguments en fonction de vos besoins.
Créer une plage nommée dynamique dans Excel avec du code VBA
Si vous avez plusieurs colonnes, vous pouvez répéter et saisir une formule distincte pour chacune des colonnes restantes, mais cela représenterait une tâche longue et répétitive. Pour simplifier cette opération, vous pouvez utiliser un code VBA afin de créer automatiquement la plage nommée dynamique.
1. Activez votre feuille de calcul.
2. Maintenez enfoncées les touches ALT + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
3. Cliquez sur Insertion > Module, puis collez le code suivant dans la fenêtre Module.
Code VBA : créer une plage nommée dynamique
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName <> "" Then
wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
End If
Next
End Sub
4. Appuyez ensuite sur la touche F5 pour exécuter le code. Des plages nommées dynamiques seront alors générées, portant les noms des valeurs de la première ligne, et une plage dynamique appelée MesDonnées couvrira l’ensemble des données.
5. Lorsque vous saisissez de nouvelles valeurs après les lignes ou colonnes existantes, la plage s’élargit automatiquement. Consultez les captures d’écran :
 |
 |
 |
Remarques :
1. Avec ce code, les noms de cellule ne s’affichent pas dans la zone Nom. Pour visualiser et utiliser facilement les noms de cellule, j’ai installé Kutools pour Excel, grâce auquel la fonction Navigation liste les noms de cellule dynamiques créés.
2. Grâce à ce code, la plage complète des données peut être étendue verticalement ou horizontalement — veillez toutefois à ne pas insérer de lignes vides ni de colonnes entre les données lors de la saisie de nouvelles valeurs.
3. Lorsque vous utilisez ce code, votre plage de données doit commencer à la cellule A1.
Article associé :
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels