Excel est votre outil de productivité ultime. Apprenez à créer des plages nommées dans Excel à l'aide de VBA et trouvez des conseils pour gagner du temps lorsque vous travaillez avec des données et des formules.
Microsoft Excel est un logiciel puissant que vous pouvez utiliser pour créer, suivre et analyser des données. En plus des fonctionnalités standards d'Excel, vous pouvez également créer des macros VBA pour automatiser certaines tâches.
Souhaitez-vous en savoir plus sur la création de plages nommées et l'utilisation de macros VBA dans Excel ? Si tel est le cas, vous êtes au bon endroit. Dans cet article de blog, nous verrons comment créer des plages nommées dans Excel à l'aide de VBA.
Nous fournirons également quelques exemples de la manière dont vous pouvez utiliser les plages nommées dans votre propre travail. Commençons !
Qu'est-ce qu'une plage nommée dans Microsoft Excel ?
En utilisant une plage nommée au lieu d’une référence de colonne/ligne, vous pouvez faire référence à une cellule ou à un groupe de cellules avec un nom personnalisé. La possibilité de spécifier les données à l'intérieur de vos cellules constitue un énorme avantage de l'utilisation des plages nommées.
Voici un exemple rapide de la façon dont vous pouvez utiliser les plages nommées pour savoir si les frais d'expédition sont facturés avec le prix du produit :
- = (B7 + B5 * C4) * (1 + A3)
- =(ShippingCharge + ProductPrice * Quantité) * (1 + TaxRate)
L'option numéro deux, comme vous pouvez le constater, vous donne une indication immédiate si le prix des produits inclut les frais d'expédition. Cela permet à l'utilisateur de comprendre facilement comment fonctionne la formule sans avoir à perdre de temps à parcourir les cellules pour comprendre de quoi il s'agit.
Comment utiliser des plages nommées dans Excel ?
En tant qu'analyste financier, vous devrez peut-être expérimenter différents taux. Tout ce qui va d’un taux d’imposition à un taux d’inflation projeté peut être utilisé comme exemple. Vous pouvez utiliser des plages nommées pour organiser des variables qui sont rarement modifiées (comme le mois ou l'année) ou qui resteront statiques pendant une longue période (comme le taux d'inflation).
Voici une liste de noms courants que vous pouvez utiliser souvent lorsque vous travaillez avec des plages nommées dans Excel :
- Date du rapport
- Année
- Mois
- FcstID
- Taux d'imposition
- Données brutes
Ce ne sont là que quelques-unes des possibilités : vous pouvez nommer une plage de tout ce que vous voulez qui respecte les règles d'Excel pour les plages nommées.
Créer des noms uniques
Créer une plage nommée est un jeu d'enfant. Tout ce que vous avez à faire maintenant est de sélectionner la ou les cellules auxquelles vous souhaitez faire référence et de saisir un nom dans la zone Nom.
Étant donné que votre nom ne peut contenir aucun espace, vous devez mettre en majuscule la première lettre de chaque nouveau mot ou utiliser un trait de soulignement (_) pour séparer les termes. Pour finaliser l'établissement de la plage nommée, appuyez sur la touche ENTRÉE après avoir tapé le nom.
Remarque : toute plage nommée établie avec la zone de nom a une portée de classeur. Cela signifie que n'importe quelle feuille de calcul de votre fichier Excel peut accéder à la plage nommée.
Création de noms à l'aide du gestionnaire de noms
Vous pouvez modifier et créer de nouvelles plages nommées dans le gestionnaire de noms (onglet Formules > groupe Noms définis > bouton Gestionnaire de noms) si vous souhaitez personnaliser encore plus vos plages nommées.
Vous devez savoir que le gestionnaire de noms vous permet de :
- Changer le nom d'une plage nommée qui existe déjà,
- Faire un changement de formule de référence,
- Définir le périmètre des feuilles de calcul dont le nom est accessible.
Maintenant que vous comprenez ce qu'est une plage nommée et comment en créer une, examinons quelques exemples pratiques de la façon dont vous pouvez les utiliser dans votre travail.
Exemples pratiques d'utilisation des plages nommées
Exemple 1 : Création d'une plage dynamique
Une plage dynamique est une plage nommée qui s'ajuste automatiquement pour inclure les nouvelles données ajoutées à la feuille de calcul. C'est un excellent moyen de gagner du temps lorsque vos données changent souvent.
Exemple 2 : Création d'une plage 3D
Une plage 3D est une plage nommée qui fait référence aux données de plusieurs feuilles de calcul. Cela peut vous faire gagner beaucoup de temps si vous travaillez avec de grandes quantités de données organisées de la même manière sur plusieurs feuilles de calcul.
Exemple 3 : Création d'un tableau nommé
Un tableau nommé est un type de plage dynamique qui fait référence à un tableau de valeurs. Cela peut être un excellent moyen de référencer des données organisées sous forme de tableau.
Automatiser les plages nommées avec des macros VBA
Maintenant que nous avons passé en revue rapidement les plages nommées, examinons quelques macros VBA qui peuvent vous aider à automatiser leur utilisation.
Ajouter une plage nommée
Le code VBA ci-dessous montre comment construire différentes sortes de plages nommées.
|
Sous-NameRange_Add() 'OBJECTIF : Différentes manières de créer une plage nommée 'SOURCE : SoftwareKeep.com Diminuer la cellule selon la plage Dim rng As Range Dim RangeName As String Dim CellName As String 'Référence de cellule unique (portée du classeur) RangeName = "Prix" NomCellule = "D7"
Définir la cellule = Feuilles de calcul("Feuil1").Plage(CellName) Ce classeur.Des nomsAjouter un nom :=RangeName, RefersTo :=cell 'Référence de cellule unique (portée de la feuille de calcul) RangeName = "Année" NomCellule = "A2"
Définir la cellule = Feuilles de calcul("Feuil1").Plage(CellName) Feuilles de calcul("Feuil1").Des nomsAjouter un nom :=RangeName, RefersTo :=cell Référence sur la plage de cellules (portée du classeur) RangeName = "mesDonnées" NomCellule = "F9:J18"
Définir la cellule = Feuilles de calcul("Feuil1").Plage(CellName) Ce classeur.Des nomsAjouter un nom :=RangeName, RefersTo :=cell 'Plage nommée secrète (n'apparaît pas dans le gestionnaire de noms) RangeName = "Nom d'utilisateur" NomCellule = "L45"
Définir la cellule = Feuilles de calcul("Feuil1").Plage(CellName) Ce classeur.Des nomsAjouter un nom :=RangeName, RefersTo :=cellule, Visible :=False Fin du sous |
Parcourir les plages nommées
Le code de macro VBA suivant montre comment parcourir les plages nommées de votre feuille de calcul.
|
Sous-NamedRange_Loop() 'OBJECTIF : Supprimer toutes les plages nommées dans le classeur actif 'SOURCE : SoftwareKeep.com Dim nm comme nom 'Parcourir chaque plage nommée dans le classeur Pour chaque nm dans ActiveWorkbook.Noms Débogage.Imprimez le nm.Nom, nm.Fait référence à Nm suivant
'Parcourir chaque plage nommée dans une feuille de calcul spécifique Pour chaque nm dans les feuilles de calcul ("Sheet1").Noms Débogage.Imprimez le nm.Nom, nm.Fait référence à Nm suivant Fin du sous |
Supprimer toutes les plages nommées
Cette fonction VBA vous aidera à nettoyer une grande partie des plages nommées dans Excel dont vous n'avez plus besoin dans votre projet.
|
Sous-NamedRange_DeleteAll() 'OBJECTIF : Supprimer toutes les plages nommées dans ActiveWorkbook (zones d'impression facultatives) 'SOURCE : SoftwareKeep.com Dim nm comme nom Dim DeleteCount As Long 'Supprimer également les zones d'impression ? UserAnswer = MsgBox("Voulez-vous ignorer les zones d'impression ?", vbYesNoCancel) Si UserAnswer = vbYes Alors SkipPrintAreas = True Si UserAnswer = vbCancel Alors quittez Sub 'Gestionnaire d'erreurs en cas d'erreurs de suppression de fonction En cas d'erreur, sauter GoTo 'Parcourez chaque nom et supprimez Pour chaque nm dans ActiveWorkbook.Noms Si SkipPrintAreas = True And Right(nm.Nom, 10) = "Print_Area" Puis GoTo Skip
'Gestionnaire d'erreurs en cas d'erreurs de suppression de fonction En cas d'erreur, sauter GoTo
'Supprimer la plage nommée nm.Supprimer SupprimerCount = SupprimerCount + 1
Sauter : 'Réinitialiser le gestionnaire d'erreurs Sur erreur GoTo 0 Suivant
'Résultat du rapport Si DeleteCount = 1 Alors MsgBox "[1] le nom a été supprimé de ce classeur." Autre MsgBox Les noms "[" & DeleteCount & "] ont été supprimés de ce classeur." Fin si Fin du sous |
Supprimer les plages nommées comportant des erreurs
Seules les plages nommées comportant des défauts seront supprimées avec ce code VBA. Ces problèmes peuvent être déclenchés par la suppression de feuilles de calcul ou de lignes/colonnes.
|
Sous-NamedRange_DeleteErrors() 'OBJECTIF : Supprimer toutes les plages nommées avec l'erreur #REF dans ActiveWorkbook 'SOURCE : SoftwareKeep.com Dim nm comme nom Dim DeleteCount As Long 'Parcourez chaque nom et supprimez Pour chaque nm dans ActiveWorkbook.Noms Si InStr(1, nm.RefersTo, "#REF!") > 0 Alors 'Gestionnaire d'erreurs en cas d'erreurs de suppression de fonction En cas d'erreur, sauter GoTo
'Supprimer la plage nommée nm.Supprimer SupprimerCount = SupprimerCount + 1 Fin si Sauter : 'Réinitialiser le gestionnaire d'erreurs Sur erreur GoTo 0 Suivant
'Résultat du rapport Si DeleteCount = 1 Alors MsgBox "[1] le nom erroné a été supprimé de ce classeur." Autre MsgBox "[" & DeleteCount & "] les noms erronés ont été supprimés de ce classeur." Fin si
Fin du sous |
TL;DR
- Les plages nommées constituent un excellent moyen d’organiser vos données dans Excel.
- Vous pouvez les utiliser pour créer des plages dynamiques, des plages 3D et des tableaux nommés.
- Vous pouvez également automatiser votre utilisation des plages nommées avec les macros VBA.
Conclusion
Excel est un outil étonnant qui peut rendre passionnantes les tâches les plus banales grâce à de vastes fonctionnalités d'automatisation. Si vous souhaitez vous simplifier la vie, apprendre à travailler avec des outils avancés tels que les plages nommées avec VBA est une excellente façon de commencer.
Nous espérons que cet article vous a aidé à en savoir un peu plus sur l'utilisation des plages nommées dans Excel. Comme toujours, si vous avez des questions, n'hésitez pas à nous laisser un commentaire ci-dessous !
Merci d'avoir lu.
Encore une chose
Vous cherchez d’autres conseils ? Consultez nos autres guides sur notre blog ou visitez notre Centre d'aide pour une mine d'informations sur la façon de résoudre divers problèmes.
Inscrivez-vous à notre newsletter et accédez rapidement à nos articles de blog, à nos promotions et à nos codes de réduction. De plus, vous serez le premier informé de nos derniers guides, offres et autres mises à jour intéressantes !
Articles recommandés
» Comment ajouter et supprimer des zéros non significatifs dans Excel
» Comment utiliser les formules « Si la cellule contient » dans Excel
» Comment verrouiller et déverrouiller le verrouillage du défilement dans Excel ?
N'hésitez pas à nous contacter pour toute question ou demande que vous souhaiteriez que nous traitions.