Les plages nommées sont l’une de ces anciennes fonctionnalités croustillantes dans Excel que peu d’utilisateurs comprennent. Les nouveaux utilisateurs peuvent trouver bizarre et effrayant, et même les vieilles mains peuvent les éviter, car ils semblent inutiles et complexes.

Mais les plages nommées sont en fait assez cool fonctionnalité. Ils peuvent rendre les formules * beaucoup* plus faciles à créer, lire et maintenir. Et en prime, ils rendent les formules plus faciles à réutiliser (plus portables).

En fait, j’utilise des plages nommées tout le temps lors des tests et du prototypage de formules. Ils m’aident à faire fonctionner les formules plus rapidement., J’utilise également des plages nommées parce que je suis paresseux et que je n’aime pas taper des références complexes:)

Les bases des plages nommées dans Excel

Qu’est-ce qu’une plage nommée?

Une plage nommée est juste un nom lisible par l’utilisateur pour une plage de cellules dans Excel. Par exemple, si je nomme la plage A1:A100 « data », je peux utiliser MAX pour obtenir la valeur maximale avec une formule simple:

 =MAX(data) // max value






La beauté des plages nommées est que vous pouvez utiliser des noms significatifs dans vos formules sans penser aux références de cellules., Une fois que vous avez une plage nommée, simplement l’utiliser comme une référence de cellule. Toutes ces formules sont valables avec la plage nommée « données »:


=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min value




Vidéo: Comment créer une plage nommée


Création d’une plage nommée est facile


la Création d’une plage nommée est rapide et facile. Sélectionnez simplement une plage de cellules et tapez un nom dans la zone Nom. Lorsque vous appuyez sur Retour, le nom est créé:


 


 Pour tester rapidement la nouvelle plage, choisissez le nouveau nom dans la liste déroulante à côté de la zone nom. Excel sélectionnera la plage sur la feuille de calcul.,


Excel peut créer des noms automatiquement (ctrl + maj + F3) 


Si vous avez des données bien structurées avec des étiquettes, vous pouvez demander à Excel de créer des plages nommées pour vous. Sélectionnez simplement les données, ainsi que les étiquettes, et utilisez la commande « Créer à partir de la sélection » dans l’onglet Formules du ruban:




Vous pouvez également utiliser le raccourci clavier control + shift + F3.


l’Utilisation de cette fonctionnalité, nous pouvons créer des plages nommées pour la population de 12 états en une seule étape:






Lorsque vous cliquez sur OK, les noms sont créés., Vous trouverez tous les noms nouvellement créés dans le menu déroulant à côté de la zone nom:




Avec les noms créés, vous pouvez les utiliser dans des formules comme celle-ci


=SUM(MN,WI,MI)




Mettre à jour les plages nommées dans> Une fois que vous avez créé une plage nommée, utilisez le Gestionnaire de noms (Control + F3) pour mettre à jour si nécessaire. Sélectionnez le nom avec lequel vous souhaitez travailler, puis modifiez directement la référence (c’est-à-dire modifiez « se réfère à »), ou cliquez sur le bouton à droite et sélectionnez une nouvelle plage.,


 


 Il n’est pas nécessaire de cliquer sur le bouton Modifier pour mettre à jour une référence. Lorsque vous cliquez sur Fermer, le nom de la plage sera mis à jour.


Remarque: si vous sélectionnez une plage nommée entière sur une feuille de calcul, vous pouvez la faire glisser vers un nouvel emplacement et la référence sera mise à jour automatiquement. Cependant, je ne connais pas de moyen d’ajuster les références de plage en cliquant et en faisant glisser directement sur la feuille de calcul. Si vous connaissez un moyen de le faire, carillon ci-dessous!


Voir toutes les plages nommées (ctrl + F3)


À voir rapidement toutes les plages nommées dans un classeur, utilisez le menu déroulant en regard de la zone nom.,


Si vous voulez voir plus de détails, ouvrez le Gestionnaire de noms (Control + F3), qui répertorie tous les noms avec des références, et fournit également un filtre: 




Remarque: sur un Mac, il n’y a pas de Gestionnaire de noms, vous verrez donc la boîte


Copiez et collez toutes les plages nommées (F3) 


Si vous voulez un enregistrement plus persistant des plages nommées dans un classeur, vous pouvez coller la liste complète des noms où vous le souhaitez., Allez dans Formules > Utiliser dans la formule (ou utilisez le raccourci F3), puis choisissez Coller les noms > Coller la liste:




Lorsque vous cliquez sur le bouton Coller la liste, vous verrez les noms et les références collés dans la feuille:




Voir les noms directement sur la feuille de calcul


Si vous réglez le niveau de zoom à moins de 40%, Excel affichera les noms de plage directement sur la feuille de calcul:




Merci pour ce conseil, Felipe!,


les Noms ont des règles


Lors de la création de plages nommées, suivez ces règles:


les plages Nommées dans les formules


les plages Nommées sont faciles à utiliser dans les formules


Par exemple, disons que vous nommez une cellule dans votre classeur « mise à jour ». L’idée est que vous pouvez mettre la date actuelle dans la cellule (Ctrl + 😉 et faire référence à la date ailleurs dans le classeur.






La formule en B8 ressemble à ceci:


="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")




Vous pouvez coller cette formule n’importe où dans le classeur, et il affichera correctement., Chaque fois que vous modifiez la date dans « mise à jour », le message sera mis à jour partout où la formule est utilisée. Voir cette page pour plus d’exemples.


Les plages nommées apparaissent lors de la saisie d’une formule 


Une fois que vous avez créé une plage nommée, elle apparaît automatiquement dans les formules lorsque vous tapez la première lettre du nom. Appuyez sur la touche tab pour entrer le nom lorsque vous avez une correspondance et que vous souhaitez qu’Excel entre le nom.,




les plages Nommées peuvent travailler comme des constantes


Parce que les plages nommées sont créés dans un emplacement central, vous pouvez les utiliser comme des constantes sans une référence de cellule. Par exemple, vous pouvez créer des noms comme « MPG » (miles par gallon) et « CPG » (coût par gallon) avec et attribuer des valeurs fixes:




Ensuite, vous pouvez utiliser ces noms où vous voulez dans les formules, et mettre à jour leur valeur dans un emplacement central.,




les plages Nommées sont absolues par défaut


Par défaut, les plages nommées se comportent comme des références absolues. Par exemple, dans cette feuille de calcul, la formule pour calculer le carburant serait:


=C5/$D$2




  


 La référence à D2 est absolue (verrouillée) de sorte que la formule peut être copiée vers le bas sans D2 changement.,


Si nous nommons D2 « MPG », la formule devient: 


=C5/MPG






 Puisque MPG est absolu par défaut, la formule peut être copiée dans la colonne D telle quelle.


les plages Nommées peuvent également être relatif


Bien que les plages nommées sont absolues par défaut, elles peuvent également être relatif. Une plage nommée relative fait référence à une plage relative à la position de la cellule active au moment de la création de la plage. Par conséquent, les plages nommées relatives sont utiles pour construire des formules génériques qui fonctionnent partout où elles sont déplacées.,


 Par exemple, vous pouvez créer une plage générique nommée « CellAbove » comme ceci:


    

  1.  Select cell A2
    2. 

  2.  Control + F3 to open Name Manager
    3. 

  3.  Tab into ‘Refers to’ section, then type: =A1
    4. 



 CellAbove va maintenant récupérer la valeur de la cellule ci-dessus où qu’elle soit utilisée.


Important: assurez-vous que la cellule active est à l’emplacement correct avant de créer le nom.


Appliquer les plages nommées à des formules existantes


Si vous avez des formules existantes qui n’utilisent pas des plages nommées, vous pouvez demander à Excel d’appliquer les plages nommées dans les formules pour vous., Commencez par sélectionner les cellules contenant les formules que vous souhaitez mettre à jour. Exécutez ensuite les Formules > Définir les Noms de > Appliquer les Noms.




 


 Excel remplacera alors les références qui ont une plage nommée correspondante par le nom lui-même.


Vous pouvez également appliquer des noms avec find and replace: 




Important: Enregistrez une sauvegarde de votre feuille de calcul et sélectionnez uniquement les cellules que vous souhaitez modifier avant d’utiliser find and replace sur les formules.,


Principaux avantages des plages nommées 


Les plages nommées facilitent la lecture des formules 


Le plus grand avantage des plages nommées est qu’elles facilitent la lecture et la maintenance des formules. En effet, ils remplacent les références cryptiques par des noms significatifs. Par exemple, considérez cette feuille de travail avec des données sur les planètes de notre système solaire., Sans plages nommées, une formule RECHERCHEV pour aller chercher de la « Position » de la table est assez cryptique:


=VLOOKUP($H$4,$B$3:$E$11,2,0)






Cependant, avec B3:E11 nommé « data », et H4 nommé « planète », nous pouvons écrire des formules comme ceci:


=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites






En un coup d’œil, vous pouvez voir la différence dans ces formules dans la colonne de l’index.


Les plages nommées rendent les formules portables et réutilisables 


Les plages nommées peuvent faciliter la réutilisation d’une formule dans une feuille de calcul différente., Si vous définissez des noms à l’avance dans une feuille de calcul, vous pouvez coller une formule qui utilise ces noms et cela « fonctionnera ». C’est un excellent moyen de faire fonctionner rapidement une formule.


 Par exemple, cette formule compte les valeurs uniques dans une plage de données numériques: 


=SUM(--(FREQUENCY(data,data)>0))




 Pour « porter » rapidement cette formule dans votre propre feuille de calcul, nommez une plage « data » et collez la formule dans la feuille de calcul. Tant que « data » contient des valeurs numériques, la formule fonctionnera immédiatement.,


Astuce: Je vous recommande de créer les noms de plage nécessaires *en premier* dans le classeur de destination, puis de copier dans la formule en tant que texte uniquement (c’est-à-dire ne copiez pas la cellule qui contient la formule dans une autre feuille de calcul, copiez simplement le texte de la formule). Cela empêche Excel de créer des noms à la volée et vous permet de contrôler entièrement le processus de création de noms. Pour copier uniquement du texte de formule, copiez du texte à partir de la barre de formule ou copiez via une autre application (navigateur, éditeur de texte, etc.).




les plages Nommées peuvent être utilisées pour la navigation


les plages Nommées sont parfaits pour une navigation rapide., Sélectionnez simplement le menu déroulant à côté de la zone nom et choisissez un nom. Lorsque vous relâchez la souris, la plage sera sélectionnée. Lorsqu’une plage nommée existe sur une autre feuille, vous accédez à la feuille automatiquement.




les plages Nommées bien travailler avec liens


les plages Nommées rendre les liens hypertextes facile. Par exemple, si vous nommez A1 dans Sheet1 « home », vous pouvez créer un lien hypertexte ailleurs qui vous y ramène.,






Pour utiliser une plage nommée à l’intérieur de la fonction de lien HYPERTEXTE, ajouter un symbole livre en face de la plage nommée:


=HYPERLINK("#home","take me home")




Note: curieusement, vous pouvez pas de lien hypertexte à une table comme vous pouvez une gamme normale de nom. Cependant, vous pouvez définir un nom égal à une table (c’est-à-dire =Table1) et un lien hypertexte vers cela. Si quelqu’un connaît un moyen de lier directement une table, carillon!,


Plages nommées pour la validation des données 


Les plages de noms fonctionnent bien pour la validation des données, car elles vous permettent d’utiliser une référence logiquement nommée pour valider l’entrée avec un menu déroulant. Ci-dessous, la plage G4:G8 est nommée « statuslist », puis appliquez la validation des données avec une liste liée comme ceci:




Le résultat est un menu déroulant dans la colonne E qui n’autorise que les valeurs de la plage nommée:




 Les plages de noms sont extrêmement utiles lorsqu’elles s’adaptent automatiquement aux nouvelles données d’une feuille de calcul., Une plage configurée de cette manière est appelée « plage nommée dynamique ». Il existe deux façons de rendre une plage dynamique: les formules et les tableaux.


Plage nommée dynamique avec une table 


Une table est le moyen le plus simple de créer une plage nommée dynamique. Sélectionnez n’importe quelle cellule dans les données, puis utilisez le contrôle de raccourci + T:




Lorsque vous créez un tableau Excel, un nom est automatiquement créé (par exemple Table1), mais vous pouvez renommer le tableau à votre guise. Une fois que vous avez créé une table, elle se développera automatiquement lorsque des données seront ajoutées.,




Plage nommée dynamique avec une formule 


Vous pouvez également créer une plage nommée dynamique avec des formules, en utilisant des fonctions comme OFFSET et INDEX. Bien que ces formules soient modérément complexes, elles fournissent une solution légère lorsque vous ne souhaitez pas utiliser de table., Les liens ci-dessous fournissent des exemples avec des explications complètes:




    

  • Exemple de formule de plage dynamique avec INDEX
    • 

  • Exemple de formule de plage dynamique avec DÉCALAGE
    • 



Noms de table dans la validation de données


Étant donné que les tableaux Excel fournissent une plage dynamique automatique, ils semblent être un ajustement naturel pour les règles de validation de données, où l’objectif est de valider par rapport à une liste qui peut être en constante évolution. Cependant, un problème avec les tables est que vous ne pouvez pas utiliser directement les références structurées pour créer des règles de validation de données ou de mise en forme conditionnelle., En d’autres termes, vous ne pouvez pas utiliser un nom de table dans les zones de mise en forme conditionnelle ou de validation de données.


 Cependant, comme solution de contournement, vous pouvez définir named une plage nommée qui pointe vers une table, puis utiliser la plage nommée pour la validation des données ou la mise en forme conditionnelle. La vidéo ci-dessous décrit cette approche en détail.


Vidéo: Comment utiliser des plages nommées avec des tables


 Suppression des plages nommées 


 Remarque: Si vous avez des formules qui font référence à des plages nommées, vous pouvez mettre à jour les formules avant de supprimer les noms. Sinon, vous verrez # NAME?, erreurs dans les formules qui font toujours référence à des noms supprimés. Enregistrez toujours votre feuille de calcul avant de supprimer les plages nommées au cas où vous auriez des problèmes et que vous deviez revenir à l’original.


Les plages nommées s’ajustent lors de la suppression et de l’insertion de cellules 


Lorsque vous supprimez *une partie* d’une plage nommée, ou si vous insérez des cellules/lignes / colonnes à l’intérieur d’une plage nommée, la référence de plage s’ajustera en conséquence et restera valide. Toutefois, si vous supprimez toutes les cellules qui entourent une plage nommée, la plage nommée perdra la référence et l’affichage d’une erreur #REF., Par exemple, si je nomme A1 « test », puis supprime la colonne A, le gestionnaire de noms affichera « se réfère à » comme:


=Sheet1!#REF!




Supprimer les noms avec le gestionnaire de noms


Pour supprimer manuellement les plages nommées d’un classeur, ouvrez le gestionnaire de noms, sélectionnez une plage et cliquez Si vous souhaitez supprimer plusieurs noms en même temps, vous pouvez Maj + Clic ou Ctrl + Clic pour sélectionner plusieurs noms, puis supprimer en une seule étape.,


Supprimer des noms avec des erreurs


Si vous avez beaucoup de noms avec des erreurs de référence, vous pouvez utiliser le bouton filtrer dans le gestionnaire de noms de filtre sur les noms des erreurs:






Puis maj+clic pour sélectionner tous les noms et les supprimer.


Plages et portée nommées 


Les plages nommées dans Excel ont quelque chose appelé « portée », qui détermine si une plage nommée est locale pour une feuille de calcul donnée ou globale dans l’ensemble du classeur. Les noms globaux ont une portée de « classeur », et les noms locaux ont une portée égale au nom de la feuille sur laquelle ils existent., Par exemple, la portée d’un nom local pourrait être « Feuil2 ». 


Le but de la portée 


Les plages nommées avec une portée globale sont utiles lorsque vous souhaitez que toutes les feuilles d’un classeur aient accès à certaines données, variables ou constantes. Par exemple, vous pouvez utiliser une plage nommée globale une hypothèse de taux d’imposition utilisée dans plusieurs feuilles de calcul.


Locale


une portée Locale signifie un nom est ne fonctionne que sur la feuille il a été créé. Cela signifie que vous pouvez avoir plusieurs feuilles de calcul dans le même classeur qui utilisent toutes le même nom., Par exemple, vous avez peut-être un classeur avec des feuilles de suivi mensuelles (une par mois) qui utilisent des plages nommées avec le même nom, toutes étendues localement. Cela peut vous permettre de réutiliser les mêmes formules dans différentes feuilles. La portée locale permet aux noms de chaque feuille de fonctionner correctement sans entrer en collision avec les noms des autres feuilles.


Pour faire référence à un nom avec une portée locale, vous pouvez préfixer le nom de la feuille au nom de la plage: 


Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenue




Les noms de plage créés avec la zone nom ont automatiquement une portée globale., Pour remplacer ce comportement, ajoutez le nom de la feuille lors de la définition du nom:


Sheet3!my_new_name




Portée globale


La portée globale signifie qu’un nom fonctionnera n’importe où dans un classeur. Par exemple, vous pouvez nommer une cellule « last_update », entrez une date dans la cellule. Ensuite, vous pouvez utiliser la formule ci-dessous pour afficher la date de dernière mise à jour dans n’importe quelle feuille de calcul.


=last_update




 Les noms globaux doivent être uniques dans un classeur.


Portée locale 


Les plages nommées à portée locale ont un sens pour les feuilles de calcul qui utilisent des plages nommées uniquement pour les hypothèses locales., Par exemple, vous avez peut-être un classeur avec des feuilles de suivi mensuelles (une par mois) qui utilisent des plages nommées avec le même nom, toutes étendues localement. La portée locale permet aux noms de chaque feuille de fonctionner correctement sans entrer en collision avec les noms des autres feuilles.


Gestion de la portée de la plage nommée 


Par défaut, les nouveaux noms créés avec la boîte de noms sont globaux et vous ne pouvez pas modifier la portée d’une plage nommée après sa création. Cependant, comme solution de contournement, vous pouvez supprimer et recréer un nom avec la portée souhaitée.,


Si vous voulez changer plusieurs noms à la fois de global à local, il est parfois logique de copier la feuille qui contient les noms. Lorsque vous dupliquez une feuille de calcul contenant des plages nommées, Excel copie les plages nommées dans la deuxième feuille, en changeant la portée en local en même temps. Une fois que vous avez la deuxième feuille avec des noms à portée locale, vous pouvez éventuellement supprimer la première feuille.


Jan Karel Pieterse et Charles Williams ont développé un utilitaire appelé Name Manager qui fournit de nombreuses opérations utiles pour les plages nommées. Vous pouvez télécharger l’utilitaire Name Manager ici.,