Vos recrutements informatiques

700 000 développeurs, chefs de projets, ingénieurs, informaticiens...

Contactez notre équipe spécialiste en recrutement

FAQ ExcelConsultez toutes les FAQ

Nombre d'auteurs : 46, nombre de questions : 845, dernière mise à jour : 30 mars 2017 

 
OuvrirSommaireLes cellulesLes cellules et plages nommées

Par défaut, vous utilisez les étiquettes de colonnes et de lignes pour faire référence aux cellules dans la feuille de calcul (A1, J4, B2 :C10 ?).
Vous pouvez également créer des noms explicites pour représenter les cellules, les plages de cellules et les formules.
Un nom est une chaîne de caractères définie par l'utilisateur et facile à interpréter, pour remplacer les classiques références de cellules.
Par exemple, =A1*Coefficient est plus facile à comprendre que =A1*G5.
Le nom ne doit pas contenir d'espace ni de caractère spécial. Le caractère underscore _ est accepté. Un nom ne peut pas commencer par une valeur numérique.

Pour nommer une cellule ou une plage de cellule:
Sélectionnez une cellule ou une plage de cellules que vous souhaitez nommer.
Cliquez dans la zone Nom, à gauche de la barre de formule.
Saisissez un nom qui servira de référence pour la cellule.
Validez en appuyant sur la touche Entrée.

Vous pouvez aussi ajouter un nom en utilisant le menu Insertion/nom/Définir.
La boîte de dialogue qui s'affiche permet aussi de visualiser tous les noms existants dans le classeur.
Saisissez le nom dans le champ Noms dans le classeur.
Spécifier l'adresse de la cellule ou de la plage dans le champ Fait référence à :.
Cliquez sur le bouton Ajouter, puis sur le bouton OK pour valider.


Nommer les formules:
Cette méthode a de nombreux avantages:
* Raccourcir la longueur des formules dans la barre de formules. Il est possible de nommer plusieurs portions de formules (La limite de chaque nom est de 256 caractères).
* Nommer les formules permet d'accélérer sensiblement le temps de calcul.
* Les formules sont masquées dans la feuille de calcul.
* les formules matricielles nommées ne sont plus à valider par Ctrl+Maj+Entrée.

Pour attribuer un nom, tout d'abord, rédigez votre formule normalement dans la cellule.
Sélectionnez la fonction dans la barre de formules.
Faites un Copier de la formule (Ctrl+C).
Revalidez votre formule (Touche Entrée).
Ensuite, utilisez le menu Insertion/Nom/Définir.
Dans le champ "Noms dans le classeur": Saisissez le nom que vous souhaitez donner à la formule (par exemple EssaiNom).
Dans le champ "Fait référence à": Effacez la référence qui s'y trouve et faites un collage de la fonction (Ctrl+V).
Cliquez sur le bouton Ajouter, puis sur le bouton OK pour valider.
Effacez la formule contenue dans la cellule et remplacez la par: =EssaiNom.

Créé le 14 mai 2007  par SilkyRoad

La macro suivante liste les cellules et les plages nommées, dans l'ordre d'index des feuilles, et crée une table des matières avec liens hypertextes.

Vba
Sélectionnez

Sub ListeNoms_OrdreFeuilles()
    Dim N As Name
    Dim PlageNom As Range
    Dim i As Byte
    Dim NumLigne As Byte
 
    NumLigne = 1
    On Error Resume Next
 
    For i = 1 To Worksheets.Count
        For Each N In Worksheets(i).Parent.Names
            Set PlageNom = Nothing
            Set PlageNom = N.RefersToRange
 
            If Not PlageNom Is Nothing Then
                If Worksheets(i).Index = PlageNom.Worksheet.Index Then
                    Cells(NumLigne, 1) = N.Name
                    Cells(NumLigne, 2) = N.RefersToRange.Value
                    Worksheets(1).Hyperlinks.Add Anchor:=Cells(NumLigne, 3), _
                        Address:="", SubAddress:=N.RefersToRange.Address(external:=True)
                    NumLigne = NumLigne + 1
                End If
            End If
 
        Next N
    Next i
End Sub
Créé le 14 mai 2007  par SilkyRoad

Dans cet exemple, le filtre automatique est dans la feuille nommée Feuil1.

Vba
Sélectionnez

Sub NommerZoneFiltree()
ActiveWorkbook.Names.Add Name:="NomPlage_01", _
    RefersTo:="=Feuil1!" & _
    Feuil1.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address
End Sub
Créé le 14 mai 2007  par SilkyRoad

Cet exemple extrait la liste des noms contenus dans la Feuil1.

Vba
Sélectionnez

Dim Plage As Range
Dim Nm As Name
 
On Error Resume Next
'Boucle sur les noms du classeur
For Each Nm In ThisWorkbook.Names
    Set Plage = Nm.RefersToRange
 
    If Not Plage Is Nothing Then
        'Vérifie si le nom appartient à la feuille
        If Worksheets("Feuil1").Name = Plage.Worksheet.Name Then _
            MsgBox Nm.Name & ":" & Plage.Address
    End If
 
    Set Plage = Nothing
Next Nm
Créé le 11 avril 2007  par SilkyRoad
Vba
Sélectionnez

Sub SuppressionNoms()
    Dim Nom As Name
 
    For Each Nom In ActiveWorkbook.Names
        Nom.Delete
    Next Nom
End Sub




Une autre solution, de Bob Ulmas :

Vba
Sélectionnez

'Auteur : Bob Ulmas
ExecuteExcel4Macro "SUM(DELETE.NAME(NAMES()))"
Créé le 14 mai 2007  par SilkyRoad
Vba
Sélectionnez

Sub Renomme_Nom()
    Dim AncienNom As String, NouveauNom As String
    Dim Cible As String
 
    AncienNom = "NomPlage"
    NouveauNom = "MaPlage"
 
    'Récupère l'adresse de la cellule ou la plage nommée.
    Cible = Range(AncienNom).Name
    'Supprime le nom
    Range(AncienNom).Name.Delete
    'Renomme la plage initiale
    Range(Cible).Name = NouveauNom
End Sub
Créé le 14 mai 2007  par SilkyRoad

Vous souhaitez par exemple additionner les 5 dernières cellules de la colonne A, mais le nombre de lignes peut varier régulièrement.
Si vous utilisez une fonction classique (=SOMME(A6:A10)), la référence à la plage de cellule (A6:A10) devra être modifiée manuellement à chaque fois que le nombre de lignes change.

Pour y remédier, vous pouvez définir dynamiquement une plage, qui prendra en compte les 5 dernières cellules de la colonne, et lui attribuer un nom. Ensuite il suffira d'additionner la plage nommée.

Par exemple, saisissez des valeurs numériques dans la plage A1:A10.
Ensuite utilisez le menu Insertion/Nom/Définir.
Dans le champ "Nom dans le classeur:", vous indiquez PlageCible.
Dans le champ "Fait référence à:", vous indiquez =DECALER(Feuil1!$A$1;NBVAL(Feuil1!$A:$A)-5;;5;1)
Cliquez sur le bouton Ajouter, puis sur le bouton OK pour valider.
Retournez dans la feuille de calcul.
Dans la cellule B1 vous saisissez: =SOMME(PlageCible).
Si vous ajoutez des valeurs à la suite dans la colonne A, Les 5 dernières seront toujours prises en compte dans la fonction =SOMME(PlageCible).

=DECALER(Feuil1!$A$1;NBVAL(Feuil1!$A:$A)-5;;5;1) définit une plage constituée des 5 dernières cellules de la colonne A.
Dans cet exemple, les autres cellules sont supposées non vides.

Si la colonne A peut contenir moins de 5 valeurs, vous pourrez utiliser cette adaptation:
=DECALER(Feuil1!$A$1;SI(NBVAL(Feuil1!$A:$A)<5;0;NBVAL(Feuil1!$A:$A)-5);;5;1)

Créé le 14 mai 2007  par SilkyRoad
Vba
Sélectionnez

Sub Test()
    MsgBox CompteCellules_PlageNommee("NomPlageCellules")
End Sub
 
 
Function CompteCellules_PlageNommee(strNom As String) As Variant
    Dim Plage As Range
 
    On Error Resume Next
    Set Plage = Sheets("Feuil1").Range(strNom)
    On Error GoTo 0
 
    If Plage Is Nothing Then
        CompteCellules_PlageNommee = "Nom inconnu"
        Else
        CompteCellules_PlageNommee = Plage.Cells.Count
    End If
End Function




Vous pouvez aussi utiliser la fonction dans une cellule:
=CompteCellules_PlageNommee("NomPlageCellules")

Créé le 14 mai 2007  par SilkyRoad

Si vous utilisez l'argument Target associé aux évènements (Worksheet_SelectionChange, Worksheet_Change ?) pour récupérer le nom des cellules sélectionnées, appliquez la syntaxe Target.Cells(1).Name:

Vba
Sélectionnez

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    MsgBox Target.Cells(1).Name.Name
    On Error GoTo 0
End Sub
Créé le 20 août 2007  par SilkyRoad

Cliquez sur l'onglet Formules, puis sur le bouton "Gestionnaire de noms" dans le groupe "Noms définis". Sélectionnez le nom dans la liste, et cliquez sur le bouton "Modifier". Vous pouvez indiquer vos annotations dans le champ "Commentaire".

Créé le 2 octobre 2007  par SilkyRoad

Excel 2007 permet d'utiliser 16 384 colonnes dans chaque feuille de calcul (pour 256 colonnes dans les anciennes versions).
Les entêtes de colonnes vont donc désormais jusqu'à XFD.

Si par exemple, vous utilisiez une cellule nommée TVA2 sous Excel2003, celle-ci correspond à une référence de cellule sous Excel2007. L'application sait gérer ce cas de figure en faisant précéder automatiquement le nom par un symbole underscore. La cellule nommée deviendra donc _TVA2 sous Excel2007 et les formules faisant référence à ce nom seront automatiquement mises à jour.

Par contre, il vous restera à modifiez vos procédures VBA et toutes les fonctions INDIRECT faisant référence à ce nom.

Créé le 20 septembre 2008  par SilkyRoad
Vba
Sélectionnez

Dim Tableau() As String
Dim objNom As Name
 
'Boucle sur les noms du classeur
For Each objNom In ThisWorkbook.Names
    'Scinde le nom pour récupérer :
        'la référence : Tableau(0)
        'et
        'Le nom : Tableau(1)
    Tableau = Split(objNom.RefersTo, "!")
 
    'Redimensionne le nom en ajoutant une ligne à chaque
    'plage de cellule : Resize(Range(Tableau(1)).Rows.Count + 1)
    ThisWorkbook.Names.Add _
        Name:=objNom.Name, _
        RefersTo:=Tableau(0) & "!" & _
        Range(Tableau(1)).Resize(Range(Tableau(1)).Rows.Count + 1).Address
Next
Créé le 18 novembre 2008  par SilkyRoad

Si le nom est saisi dans la cellule A1, vous pouvez indiquer dans votre formule :

Formule
Sélectionnez

INDIRECT($A$1)
Créé le 18 novembre 2008  par SilkyRoad

Activez votre classeur.
Sélectionnez l'onglet Formules dans le ruban.
Groupe "Noms définis".
Cliquez sur le bouton "Utiliser dans la formule".
Sélectionnez "Coller des noms" dans la liste.
Cliquez sur le bouton "Coller une liste".
La liste des noms et leur adresse s'affiche dans la feuille de calcul, à partir de la cellule active.

Créé le 18 novembre 2008  par SilkyRoad

Par défaut, un nom créé est utilisable directement dans tout le classeur.
Il est également possible de créer un nom qui ne sera visible que depuis une feuille spécifique.

Pour toutes les versions:
Dans la zone de nom (à gauche de la zone de formule), vous pouvez saisir le nom en le préfixant avec le nom de la feuille suivi du signe !
Exemple: Feuil1!MonNom, 'Ma Feuille'!MonNom

Vous pouvez également utiliser le gestionnaire de noms.

Dans Excel 2007:
Sélectionnez l'onglet Formules.
Cliquez sur le bouton "Gestionnaire de noms" dans le groupe "Noms définis".
Cliquez sur le bouton "Nouveau" dans la boîte de dialogue.
Indiquez le nom dans le champ "Nom".
Sélectionnez la feuille dans le menu déroulant "Zone :".
Spécifiez la cellule ou la plage de cellules source dans le champ "Fait référence à :".
Cliquez sur le bouton OK pour valider.

Dans les versions antérieures:
Menu Insertion/Nom/Définir...
Dans la zone Nom, vous saisissez le nom de la plage en le préfixant avec le nom de la feuille suivi du signe !
Exemple: Feuil1!MonNom, 'Ma Feuille'!MonNom
Un nom ayant une portée de feuille apparaît dans la liste avec, à sa droite, le nom de la feuille qui le contient.


Utilisation
Pour utiliser une plage attachée spécifiquement à une feuille sur une autre feuille du classeur, vous utiliserez son nom préfixé du nom de la feuille qui la contient suivi de !
En créant des noms attachés à une feuille spécifique, vous pouvez créez sur plusieurs feuilles des plages portant des noms identiques.

Mis à jour le 10 janvier 2010  par SilkyRoad, Pierre Fauconnier

Imaginons la plage de cellule $E$2:$E$9 contenant des données.
En E10, la formule =SOMME($E$2:$E$9).
Maintenant, nommez la plage de cellule E2:E9 Mes_Données.

Si vous vous placez en E10, la formule est toujours =SOMME(E2:E9).
En E10, affectez un nom (ApplyNames).
Dans la boîte de dialogue, sélectionnez le nom Mes_Données.
La formule en E10 est maintenant =SOMME(Mes_Données).

Créé le 19 février 2009  par Fred65200
  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2009 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.