Le tableur : classeur, formules, références, paramètres et noms

05Nommer une cellule ou une plage

5.1 Principe

On peut donner un nom à une cellule ou à une plage, puis l'utiliser dans les formules à la place de l'adresse. La cellule Parametres!B2 devient Taux_TVA ; la formule =ARRONDI(F5*Taux_TVA;2) se lit sans aller voir où se trouve le taux.

5.2 Intérêts

• Lisibilité : Taux_TVA parle, Parametres!$B$2 non.
• Verrouillage implicite : un nom désigne toujours la même cellule, sans signes $.
• Robustesse : si on insère des lignes dans la feuille de paramètres, le nom suit la cellule.

5.3 Règles de nommage

Un nom a une portée : tout le classeur (cas courant) ou une seule feuille. Un nom peut aussi désigner une plage entière, par exemple Bareme pour un tableau de tranches (cours 28).

06Concevoir la structure d'un modèle

6.1 Les quatre zones

Un modèle de feuille de calcul propre sépare quatre zones.

6.2 Les bonnes pratiques de structure

• Une ligne d'en-têtes, une ligne par enregistrement, pas de ligne vide ni de total au milieu de la base.
• Pas de cellules fusionnées dans une zone de données (elles empêchent le tri, les filtres, la recopie).
• Une formule par colonne, recopiée sur toute la hauteur : une formule différente au milieu d'une colonne est un indice d'erreur.
• Un seul endroit par information : si le taux de TVA figure à deux endroits, ils finiront par diverger.
• Des libellés clairs, avec unités.

6.3 Modifier la structure d'un modèle existant

Cas fréquents d'énoncé : on demande d'ajouter une colonne (frais de port), de remplacer un taux fixe par un paramètre, ou de rendre un modèle utilisable pour une autre période. La méthode est toujours la même :

1. repérer les valeurs en dur et les déplacer en zone de paramètres ;
2. nommer les paramètres ;
3. remplacer les valeurs en dur par les noms ;
4. vérifier que les résultats n'ont pas changé (comparer avant et après sur un jeu d'essai, cours 31).

07Identifier les besoins d'automatisation

Avant d'écrire une formule ou une macro, on identifie ce qui mérite d'être automatisé.

Le choix de l'outil découle de la nature du besoin :

Exemples corrigés

Cas 1 : un devis qui reste exact quand les taux changent

Un fournisseur de papeterie établit un devis. La feuille Parametres contient en B2 le taux de TVA (20 %) et en B3 le taux de remise commerciale (5 %). Ces cellules sont nommées Taux_TVA et Taux_remise.

Feuille Devis, lignes de données de 5 à 8 :

Formules de la ligne 5, à recopier jusqu'à la ligne 8 :

Ligne de total : D9 =SOMME(D5:D8), et de même de E9 à H9.

Résultats attendus (recalculés hors tableur) :

Détail de la ligne 6 : D6 = 25 × 4,95 = 123,75 ; E6 = ARRONDI(123,75 × 0,05 ; 2) = ARRONDI(6,1875 ; 2) = 6,19 ; F6 = 117,56 ; G6 = ARRONDI(117,56 × 0,20 ; 2) = 23,51 (23,512 arrondi) ; H6 = 141,07.

Le paramètre change. La direction commerciale porte la remise de 5 % à 8 %. On modifie une seule cellule (B3) et le devis se recalcule :

L'erreur typique. Si E5 avait été écrite =ARRONDI(D5*Parametres!B3;2) sans $ ni nom, la recopie vers le bas aurait produit Parametres!B4, Parametres!B5, Parametres!B6 : des cellules vides. La remise serait de 7,60 sur la première ligne, de 0,00 sur les trois autres, sans message d'alerte, et le total des remises afficherait 7,60 au lieu de 25,01.

À retenir

Conclusion à destination du responsable commercial : le devis ne contient plus aucun taux écrit dans une formule. Passer la remise de 5 % à 8 % réduit le total TTC de 570,17 € à 552,17 € (moins 18,00 €) et ne demande de modifier qu'une cellule. La même méthode s'applique à la TVA si un taux est modifié : on change Taux_TVA, pas les formules.

Cas 2 : une grille de prix TTC avec des références mixtes

On veut afficher, pour quatre prix hors taxe et trois taux de TVA, le prix TTC. Les prix sont en colonne A (de A3 à A6), les taux en ligne 2 (de B2 à D2).

Formule saisie une seule fois en B3, puis recopiée vers la droite et vers le bas : =$A3*(1+B$2).

• $A3 : colonne figée (le prix reste dans la colonne A), ligne libre (elle change quand on descend) ;
• B$2 : ligne figée (le taux reste sur la ligne 2), colonne libre (elle change quand on va à droite).

Résultats attendus :

Contrôle sur C4 (prix 250, taux 10 %) : la formule recopiée s'est transformée en =$A4*(1+C$2), soit 250 × 1,10 = 275,00.

Avec une référence entièrement relative (=A3*(1+B2)), la recopie vers la droite (C3 devient =B3*(1+C2)) ferait multiplier un prix TTC déjà calculé au lieu du prix HT, et la recopie vers le bas (B4 devient =A4*(1+B3)) ferait lire un résultat à la place du taux : la grille serait fausse.

À retenir

Conclusion : une seule formule recopiée suffit pour remplir douze cellules, à condition de verrouiller chaque axe au bon endroit. Si l'entreprise ajoute un taux, on insère une colonne et on recopie la formule sans la réécrire.

Cas 3 : où automatiser et où paramétrer ?

Un service comptable reçoit chaque mois un relevé de 140 lignes de frais. Les opérations actuelles : recopier le relevé, calculer la TVA ligne par ligne avec des taux écrits dans les formules (=F5*0,2, =F6*0,1, etc.), classer chaque ligne selon son code, additionner par code.

À retenir

Conclusion : les trois premières opérations relèvent de formules et de paramètres : on supprime les taux en dur et on les remplace par des cellules nommées. Seule la dernière justifie une macro. Le gain attendu est autant en fiabilité qu'en temps : chaque taux n'est plus répété 140 fois.

Vocabulaire essentiel

Points clés à retenir

1. Un tableur calcule des formules commençant par = ; les arguments des fonctions sont séparés par un point-virgule.
2. Le format change l'affichage, pas la valeur ; seule une fonction d'arrondi dans la formule (ARRONDI, cours 26) modifie la valeur.
3. Une référence relative suit la recopie, une référence absolue ($B$3) ne bouge pas, une référence mixte ne verrouille qu'un axe.
4. Pour chaque référence : « suit-elle la formule ou reste-t-elle fixe ? »
5. Un taux ou un seuil se range dans une cellule de paramètre, jamais dans une formule.
6. Un nom (Taux_TVA) rend la formule lisible et verrouille la référence ; il ne ressemble jamais à une adresse de cellule.
7. Un modèle sépare paramètres, données, calculs et résultats, avec une formule identique par colonne.
8. On automatise ce qui est répété, volumineux, régi par une règle stable et risqué à la main.

Pièges fréquents

1. Oublier les $ sur un paramètre : les résultats sont faux sans aucun message.
2. Confondre $B5 et B$5 : on fige la colonne ou la ligne à l'envers dans une grille.
3. Écrire un taux dans une formule (=F5*0,2) : le modèle ne se met plus à jour.
4. Croire que le format arrondit : le nombre affiché 6,19 vaut encore 6,1875 dans les totaux.
5. Nommer une cellule TVA1 : le nom est refusé, car il ressemble à une adresse.
6. Mélanger saisies et calculs dans une même colonne : la recopie devient impossible.
7. Fusionner des cellules dans la zone de données : tris et filtres deviennent impraticables.
8. Confondre NB et NBVAL : NB ne compte que les nombres, NBVAL compte aussi les textes.

Q&R pour le tuteur IA

Q : Quelle est la différence entre B5, $B$5 et $B5 ? R : B5 est relative : elle se décale à la recopie. $B$5 est absolue : elle ne bouge jamais. $B5 est mixte : la colonne reste B, la ligne se décale.

Q : Pourquoi ne faut-il pas écrire le taux de TVA dans la formule ? R : Parce que le taux devient invisible, qu'il faut le modifier dans chaque formule s'il change, et que des taux différents peuvent coexister sans que personne le voie. Une cellule de paramètre (ou un nom) centralise la valeur.

Q : Comment nommer la cellule Parametres!B2 ? R : On lui donne un nom, par exemple Taux_TVA, sans espace et sans ressemblance avec une adresse de cellule. On l'utilise ensuite dans les formules : =ARRONDI(F5*Taux_TVA;2).

Q : La formule =D5*B3 en E5, recopiée en E6, que devient-elle et pourquoi est-ce un problème ? R : Elle devient =D6*B4. Le taux est alors cherché dans la cellule du dessous, souvent vide, donc nul. Il fallait écrire =D5*$B$3 ou utiliser un nom.

Q : Quelles zones doit comporter un modèle bien conçu ? R : Une zone de paramètres, une zone de données, une zone de calculs et une zone de résultats, avec une ligne d'en-têtes, une ligne par enregistrement et une formule identique par colonne.

Q : Quand une macro est-elle préférable à une formule ? R : Quand il faut enchaîner des gestes répétitifs ou parcourir une liste en modifiant des cellules (mise en forme, copie, traitement ligne par ligne). Un calcul qui renvoie une valeur à partir d'autres cellules se règle avec une formule.