Pourquoi nommer vos plages dans Excel ?
Nommer des plages dans Excel peut apporter beaucoup de flexibilité à vos classeurs.
Tout d'abord, les plages nommées ont un nom explicite
Cela vous permet, à vous ou à tout autre utilisateur, de comprendre plus facilement vos formules ou de déceler les erreurs. Si vous avez une formule comme
où A1:A50 fait référence aux montants des ventes et B1:B50 aux mois, vous pouvez la rendre beaucoup plus simple si A1:A50 est nommé « Ventes » et B1:B50 est nommé « Mois ». Alors votre formule sera :
et vos formules deviennent claires pour vous et pour tous ceux qui ont besoin de travailler avec votre classeur.
Deuxièmement, les plages nommées peuvent être basées sur des formules
Cela vous permet donc de faire référence à des plages de taille variable. Une telle plage nommée ajustera automatiquement vos formules, graphiques ou tableaux croisés dynamiques aux nouvelles données. Par exemple, disons que vous avez un graphique qui montre comment vos ventes mois par mois.
Lorsque de nouvelles données sont disponibles le mois suivant, vous souhaitez que le graphique tienne automatiquement compte de la nouvelle ligne, n'est-ce pas ? Eh bien, c'est quelque chose que vous pouvez faire si la plage du graphique est nommée.
Troisièmement, une plage nommée vous permet de taper des formules plus rapidement
Lorsque vous saisissez le début d'une plage nommée dans une formule, une info-bulle vous proposera des plages nommées existantes. Vous pouvez double-cliquer dessus ou appuyer sur Tab pour valider le choix. Cela peut s'avérer très utile, notamment pour éviter de naviguer entre vos différentes feuilles Excel à la recherche d'une plage puis d'une autre.
Lors de la saisie d'une formule, vous pouvez également appuyer sur F3 pour afficher la liste des plages nommées, puis double-cliquer sur un nom pour l'insérer dans votre formule.
Quatrièmement, les plages nommées sont très utiles pour les développeurs VBA
Se référant à une plage basée sur ses coordonnées, par ex. "A1", n'est vraiment pas flexible. Si la cellule est déplacée, le code ne la comprendra pas et s'appliquera toujours à "A1" quoi qu'il arrive. Mais si vous faites référence à une plage par son nom, cela vous permet de la déplacer, voire de faire référence à des plages dynamiques avec une taille variable directement dans votre code.
Écrire votre code avec des variables, comme : ActiveSheet.Range("MyRange").Value, où MyRange est une plage nommée, est toujours plus flexible que de se référer simplement à la position de la plage à un moment donné heure : ActiveSheet.Range("A1").Value
Alors, comment nommer des plages ?
Nommer la plage peut être fait très simplement, donc aucune excuse pour ne pas le faire ! Il existe différentes façons de le faire.
Méthode 1 : Utilisation de la zone de nom : simple et rapide
Sélectionnez la plage de cellules que vous souhaitez nommer
Cliquez sur la zone de nom, à gauche de la barre de formule
Tapez le nom de votre plage, par exemple "Ventes"
Appuyez sur Entrée
Tadaaa, vous avez maintenant une plage nommée !
Méthode 2 : Créer à partir de la sélection - utile pour créer plusieurs noms à la fois dans un tableau
Excel vous permet de créer automatiquement des noms basés sur des étiquettes. Regardez l'exemple à droite. Ce qui serait cool, c'est de nommer automatiquement "Ventes" la plage A2:A8, nommer "Mois" la plage B2:B8, etc.
Si vous avez une grande base de données, nommer manuellement chaque plage comme celle-ci peut prendre un certain temps réel. Heureusement, Excel vous permet de nommer automatiquement chaque plage en fonction des étiquettes en haut, à gauche, à droite ou en bas.
Comment ça marche ?
1. Sélectionnez une plage entière de cellules, où vous souhaitez en créer plusieurs
2. Dans l'onglet Formule du ruban, cliquez sur "Créer à partir de la sélection
3. Dans la boîte de dialogue, choisissez l'emplacement des étiquettes. Dans cet exemple, les étiquettes sont en haut de la plage sélectionnée, nous choisissons donc "Haut".
4. Ça y est, nous avons maintenant créé automatiquement des gammes. Vous pouvez le vérifier en sélectionnant la plage B2:B8. La zone de nom à gauche de la barre de formule affichera "Mois".
Méthode 3 : Utiliser des tableaux : créer rapidement des plages dynamiques
Vous pouvez créer des tables pour générer automatiquement des noms pour chaque colonne.
Alors que la méthode "Créer à partir de la sélection" permettait de créer des noms à partir d'étiquettes situées de n'importe quel côté de vos données, cela ne créera que des noms à partir d'étiquettes dans la ligne d'en-tête. Une autre limitation est que les noms sont généralement plus longs car ils contiennent le nom de la table.
Cependant, l'utilisation de tableaux permet à la plage nommée de s'ajuster automatiquement lorsque vous ajoutez de nouvelles données dans le tableau. Ceci est très utile car vous n'avez pas à changer la référence du nom à chaque fois.
Pour créer un tableau, sélectionnez la plage de cellules, accédez à l'onglet "Insérer" du ruban et cliquez sur "Tableau". Ensuite, des noms sont automatiquement créés pour chaque colonne. Dans le tableau "Table1" de l'illustration ci-dessus, un nom a été créé pour la colonne Ventes. Saisissez :
Table1[Sales] fera référence à toute la colonne des ventes sans en-tête (plage A2 : A8 dans l'exemple ci-dessus). Par exemple, vous pouvez taper n'importe où =SUM(Table1[Sales]) pour calculer la somme dans la colonne des ventes.
Table1[@Sales] fera référence à la valeur des ventes dans la même ligne. Par exemple, tapez =Table1[@Sales] en B4 et vous obtiendrez la valeur en A4.
Bien que nous ne le détaillons pas ici, vous pouvez également faire référence à des parties spécifiques du tableau en utilisant le signe # suivi de Tous, Données, En-têtes ou Totaux.
Ainsi, lorsque vous avez créé une table, tapez simplement son nom dans une formule suivi de "[" et vous obtiendrez les suggestions de noms comme indiqué ci-dessous :
Dernier conseil, mieux vaut renommer la table pour faciliter encore plus l'utilisation des noms dans les formules, notamment si vous avez de nombreuses tables. Pour renommer un tableau, cliquez sur n'importe quelle cellule du tableau, puis allez dans l'onglet "Design" et tapez le nouveau nom dans "Nom du tableau".
💡Pour en savoir plus sur les Tableaux, lire cet article sur les 12 raisons d'utiliser des Tableaux dans Excel
Méthode 4 : Saisir une formule - utile pour créer des plages nommées dynamiques
Dernière méthode, vous pouvez définir une plage nommée avec une formule :
Allez dans l'onglet "Formule" depuis le ruban
Cliquez sur "Définir le nom"
Dans le champ "Nom", saisissez le nom souhaité pour la plage (vérifiez ci-dessous les règles de nommage des plages)
Dans le champ "Fait référence à", saisissez votre formule
Valider pour enregistrer vos modifications
Une formule incontournable à utiliser dans la dénomination de plage est la formule DECALER. Vous pouvez l'utiliser pour créer des plages dynamiques, dont la taille s'ajustera au nombre de lignes ou de colonnes. Dans l'exemple ci-dessous, la plage "MyRange" s'ajuste au nombre de lignes et de colonnes :
La cellule de référence est Sheet2 !$A$1. C'est le point de départ.
Ensuite, les lignes à décaler sont 0. Cet argument n'est pas nécessaire pour cette utilisation de la formule DECALER mais peut être utile dans d'autres utilisations.
Alors les colonnes à décaler sont 0. Cet argument n'est pas nécessaire pour cette utilisation de la formule DECALER mais peut être utile dans d'autres utilisations.
Le nombre de lignes à ajouter à partir de la cellule de référence est NBVAL(Sheet2!A:A), qui compte le nombre de cellules non vides dans la colonne A.
Le nombre de colonnes à ajouter à partir de la cellule de référence est NBVAL(Sheet2!1:1), qui compte le nombre de cellules non vides dans la ligne 1.
Cette méthode présente de multiples avantages, permettant de créer des tableaux croisés dynamiques se référant à une plage variable (et sans la ligne "Vides"), des graphiques avec une plage de référence variable afin qu'ils puissent s'adapter aux nouvelles données, etc.
Quelques règles à garder à l'esprit pour nommer les plages
Il y a quelques règles à garder à l'esprit lorsque vous nommez des plages :
Le premier caractère doit être soit une lettre, un trait de soulignement (_) ou une barre oblique inverse (\)
Tous les autres caractères doivent être soit des lettres, des chiffres, des points ou des traits de soulignement
Les noms ne peuvent pas avoir d'espaces
Les noms ne peuvent pas être une référence existante. Vous ne pouvez pas nommer votre plage "$A$1" car elle existe déjà !
Les noms ne reconnaissent pas la casse, donc MyRange, MYRANGE ou myrange sont tous identiques.
Modification d'une plage nommée
Si vous avez créé un nom et souhaitez le modifier ou modifier les cellules auxquelles il fait référence, vous pouvez simplement :
Allez dans l'onglet "Formule" depuis le ruban
Cliquez sur "Gestionnaire de noms"
Dans la liste des noms, sélectionnez la plage nommée que vous souhaitez modifier
Modifiez le "Nom" ou le "Fait référence à"
Valider pour enregistrer vos modifications et fermer le gestionnaire de noms
Conclusion : nommer des plages dans Excel présente de nombreux avantages, en facilitant la lecture de vos classeurs, en accélérant la saisie et le débogage des formules, et en permettant de créer des plages de taille variable pour rendre vos feuilles de calcul dynamiques et facile à mettre à jour.