Noms définis et feuille de paramètres : taux, barèmes et constantes

Dans l’épisode 4, nous avons figé un taux de TVA avec $B$1. C’est correct, mais lisible seulement par celui qui sait que $B$1 contient le taux. Dans un classeur de douze feuilles, Paramètres!$B$2 ne dit rien à personne, pas même à vous dans trois mois. Les noms définis et la feuille de paramètres transforment ces adresses énigmatiques en mots.

Ce que vous saurez faire à la fin de l’épisode

• créer un nom pour une cellule ou une plage, de trois façons ;
• écrire des formules avec des noms plutôt que des adresses ;
• structurer une feuille de paramètres : taux, seuils, exercice, listes ;
• gérer, corriger et nettoyer les noms avec le gestionnaire ;
• éviter les noms interdits et les noms cassés.

Le principe : un mot pour une cellule

Un nom défini est une étiquette attachée à une cellule ou une plage. Quand la formule dit TauxTVA, Excel va chercher la cellule correspondante. Comparez :

Les trois avantages sont immédiats :

1. Lisibilité : la formule se lit comme une phrase.
2. Fiabilité : pas de $ à oublier, pas d’adresse décalée par une insertion de ligne dans Paramètres.
3. Maintenance : un seul endroit à changer, et le nom indique à quoi sert la cellule.

La feuille Paramètres

Reprenons le classeur d’Atlas Négoce, qui prépare des devis de mobilier de bureau. Tout ce qui peut changer d’un devis à l’autre ou d’une année à l’autre se range dans la feuille Paramètres :

Figure 1 : quatre paramètres, saisis en jaune. La zone Nom (repère 1) indique que la cellule active, B2 (repère 2), s’appelle TauxTVA ; la colonne C (repère 3) rappelle le nom, pour la documentation.

Une bonne feuille de paramètres répond à trois exigences : aucun paramètre n’est ailleurs (si un taux est écrit dans une formule ou dans un titre, on finira par l’oublier lors d’un changement), chaque paramètre est commenté (unité, source, date de dernière vérification) et les cellules modifiables sont repérables (fond jaune, convention de l’épisode 7). Pour des paramètres sensibles (taux légaux), indiquez la source dans une colonne Référence.

Créer un nom

Trois méthodes, de la plus rapide à la plus complète :

1. La zone Nom. Sélectionnez B2, cliquez dans la zone Nom, tapez TauxTVA, Entrée. C’est tout.
2. À partir de la sélection. Sélectionnez A2:B5 (libellés et valeurs), Formules › Créer à partir de la sélection, cochez Colonne de gauche. Excel nomme chaque valeur d’après son libellé en remplaçant les espaces par des soulignés (Taux_de_TVA_normal). Pratique, mais les noms sont longs : renommez-les ensuite.
3. Le gestionnaire de noms. Formules › Définir un nom, ou Ctrl + F3 pour ouvrir le gestionnaire : on y saisit le nom, la portée et la cellule désignée (Fait référence à).

Les règles de nommage

• Le nom commence par une lettre (ou un souligné) et ne contient aucun espace ni caractère spécial ; utilisez la casse mixte (TauxTVA) ou le souligné (Taux_TVA).
• Il ne doit pas ressembler à une adresse : TVA1 est la cellule de la colonne TVA, ligne 1 (les colonnes vont jusqu’à XFD), T20 aussi ; ils sont refusés. TauxTVA, Exercice et SeuilFranco sont valides.
• Il ne distingue pas majuscules et minuscules : tauxtva et TauxTVA désignent la même cellule.
• Préférez des noms explicites à des noms courts : TauxRemise plutôt que TR.

Quand vous écrivez une formule, Excel propose les noms définis dans la liste de saisie semi-automatique ; la touche F3 ouvre la liste des noms pour en coller un.

Utiliser les noms dans un devis

Figure 2 : le devis d’Atlas Négoce. Repère 1 : la TVA, =F2*TauxTVA ; repère 2 : la remise, =D2*TauxRemise ; repère 3 : le test du franco.

Les formules de la première ligne :

• Total HT : =B2*C2 → 10 × 850 = 8 500,00
• Remise : =D2*TauxRemise → 5 % de 8 500 = 425,00
• Net HT : =D2-E2 → 8 075,00
• TVA : =F2*TauxTVA → 20 % de 8 075 = 1 615,00
• TTC : =F2+G2 → 9 690,00

Pour les quatre articles, les totaux sont : Total HT 23 040,00 ; remise 1 152,00 ; Net HT 21 888,00 ; TVA 4 377,60 ; TTC 26 265,60. En B8, la formule =SI(F6>=SeuilFranco;"Franco de port";"Port à facturer") compare le net HT (21 888) au franco de 20 000 et affiche Franco de port.

L’intérêt se mesure aux modifications :

• si l’on passe la TVA à 10 % dans Paramètres, la TVA du devis devient 2 188,80 et le TTC 24 076,80, sans toucher une seule formule ;
• si l’on relève le franco à 25 000, la mention devient Port à facturer (21 888 < 25 000) ;
• si le taux de remise passe à 8 %, tout le devis suit.

Nommer une plage

On peut aussi nommer des zones : sélectionnez B2:B200, nommez-les Montants, puis écrivez =SOMME(Montants). Le nom est un raccourci, mais il reste statique : si la liste s’allonge au-delà de la ligne 200, le nom ne suit pas. Pour des listes qui grandissent, utilisez des tableaux Excel (épisode 8), qui offrent des noms de colonnes dynamiques, ou les fonctions dynamiques de l’épisode 48.

Les noms de plages servent particulièrement à deux usages : les listes déroulantes (épisode 11, la source de la liste peut être un nom) et les tables de correspondance utilisées par les fonctions de recherche (épisodes 19 à 21) : =RECHERCHEX(A2;PlanComptable_N°;PlanComptable_Intitulé).

Gérer les noms

Le gestionnaire (Ctrl + F3) affiche pour chaque nom : sa valeur actuelle, la cellule désignée, la portée et un commentaire. Les actions utiles :

• Modifier la cellule désignée si vous déplacez une table ;
• Supprimer les noms obsolètes, notamment ceux dont la valeur est #REF! (cellule supprimée) ; le bouton Filtrer › Noms avec erreurs les isole ;
• Filtrer par portée (feuille ou classeur) ;
• noter en commentaire l’unité et la source.

Un classeur hérité contient parfois des centaines de noms fantômes, venus de copies de feuilles d’autres fichiers : ils alourdissent le classeur et créent des messages de mise à jour de liens. Nettoyez-les avant de diffuser.

Les pièges

• Supprimer la cellule d’un nom : les formules qui l’utilisent renvoient #REF!. Ne supprimez pas une ligne de la feuille Paramètres : effacez son contenu, ou déplacez-la proprement.
• Mauvaise portée : un nom créé à portée feuille n’est pas reconnu depuis une autre feuille, et la formule renvoie #NOM?.
• Noms en double après copie de feuille : Excel peut créer une version locale du même nom. Dans le gestionnaire, vérifiez la colonne Étendue.
• Abus : nommer chaque cellule rend un classeur plus difficile à lire. Réservez les noms aux paramètres, aux listes de référence et aux plages utilisées à plusieurs endroits.

À vous de jouer

Dans le classeur d’exercice :

1. Onglet Paramètres : sélectionnez B2, lisez la zone Nom. Ouvrez le gestionnaire de noms (Ctrl + F3) et repérez les quatre noms.
2. Onglet Devis : cliquez sur G2 et E2, lisez leurs formules. Passez la TVA à 10 % dans Paramètres : quel est le nouveau TTC ?
3. Ajoutez un cinquième paramètre : « Frais de port » = 150 MAD, et nommez-le FraisPort.
4. Modifiez la cellule B8 pour qu’elle affiche le montant des frais : =SI(F6>=SeuilFranco;0;FraisPort).
5. Passez le franco à 25 000 : que devient la cellule ?

Correction. Avec une TVA de 10 %, TVA = 2 188,80 et TTC = 21 888 + 2 188,80 = 24 076,80. Avec la formule de l’étape 4 et un franco à 25 000, B8 affiche 150,00 (le net HT de 21 888 est inférieur au franco, donc les frais de port s’appliquent) ; avec le franco initial de 20 000, elle affiche 0.

À retenir

• Un nom défini remplace une adresse par un mot : =F2*TauxTVA.
• Une feuille Paramètres rassemble taux, seuils, exercice et listes ; chaque cellule y a un nom, une unité et une source.
• Création : zone Nom, Créer à partir de la sélection, gestionnaire (Ctrl + F3). Pas d’espaces, pas de noms qui ressemblent à une adresse.
• Nettoyez régulièrement les noms en erreur (#REF!) ; ne supprimez jamais une cellule désignée par un nom.
• Pour des listes qui grandissent, les tableaux Excel valent mieux que des noms de plages statiques.

Épisode 11 : la validation de données et les listes déroulantes, pour que ces paramètres soient utilisés et que personne ne saisisse n’importe quoi.