Le tableur
Partie 2 sur 6
4Désigner une cellule : les références
Pour calculer à partir du contenu d’une cellule, une formule doit pouvoir la désigner. C’est le rôle d’une référence. On en distingue quatre sortes : absolue, relative, mixte et nommée. Cette partie est la plus importante de l’article : la plupart des erreurs de calcul des débutants viennent d’une référence mal choisie. La figure 3 en résume l’enjeu.
4.1La référence relative : un décalage
Une référence relative n’exprime pas « la cellule B2 », mais « la cellule située à tel décalage de moi ». Elle « donne le décalage (en nombre de lignes et de colonnes) entre la cellule de référence et la cellule pointée ». Par convention, un décalage vers le haut ou vers la gauche est négatif. En style L1C1, ce décalage s’écrit explicitement : L(3)C(-2) désigne la cellule située trois lignes plus bas et deux colonnes à gauche de la cellule qui contient la formule ; LC(12) désigne la cellule sur la même ligne, douze colonnes plus loin (quand le décalage est nul, on n’écrit pas le zéro).
En style A1, le décalage est implicite : on écrit simplement l’adresse, sans signe dollar, et le logiciel garde en mémoire la position relative. C’est la raison pour laquelle une formule recopiée « se décale toute seule ». Si la cellule E2 contient =B2*C1, le logiciel comprend « multiplier la cellule 3 colonnes à gauche par celle qui est 1 colonne à gauche et 1 ligne au-dessus ». Recopiée en E3, la formule devient =B3*C2 : la même position relative, donc d’autres cellules.
4.2La référence absolue : une adresse fixe
Une référence absolue désigne une cellule de façon unique, quelle que soit la place de la formule. En style A1, on fait précéder la lettre de la colonne et le numéro de la ligne du signe dollar : $C$1. Autre exemple : $AC$34. En style L1C1, elle s’écrit L12C24, sans parenthèses. Une référence absolue est ce qu’il faut à une cellule qui contient un paramètre commun à tous les calculs : taux de TVA, taux de change, date de départ.
Voici un exemple pratique. Une colonne de prix hors taxe (HT, avant ajout de la TVA, la taxe sur la valeur ajoutée) doit donner le prix toutes taxes comprises (TTC). La formule est prix TTC = prix HT + prix HT × taux de TVA. Un taux de 19,6 % était autrefois en vigueur ; il est de 20 % pour le taux normal en France depuis le 1er janvier 2014. Si le prix HT est en D6 et le taux en D11, la formule en E6 s’écrit =D6+D6*D11. Recopiée vers E7, E8, E9, elle devient =D7+D7*D12, puis =D8+D8*D13… : le prix suit bien (D7, D8…) mais le taux glisse aussi vers des cellules vides ou fausses. C’est l’erreur classique : « la référence à la cellule donnant la TVA est erronée ». La solution est d’écrire =D6+D6*$D$11 : le taux reste en D11 sur toutes les lignes.
4.3La référence mixte : fixer la colonne ou la ligne
Une référence mixte est fixe sur un axe et relative sur l’autre. En style A1, on ne met le dollar que devant la lettre ou que devant le chiffre : $C5 garde la colonne C mais la ligne glisse ; F$18 garde la ligne 18 mais la colonne glisse. Les notations équivalentes en L1C1 sont L2C(3) ou L(4)C17. Les références mixtes sont précieuses pour les tables à double entrée, comme une table de multiplication : en B2, la formule =$A2*B$1 multiplie le nombre de la colonne A (colonne fixe) par celui de la ligne 1 (ligne fixe). Recopiée sur tout le tableau, elle donne la table complète avec une seule formule.
Un raccourci à connaître : dans la barre de formule, placez le curseur sur une référence et appuyez sur la touche F4 (sur certains ordinateurs portables, F4 avec la touche de fonction) ; la référence passe successivement de relative à absolue, à mixte (ligne fixe), puis à mixte (colonne fixe), puis revient à relative.
4.4La référence nommée : donner un nom
On peut aussi donner un nom à une cellule ou à une plage (on parle aussi d’étiquette). Dans Excel, on sélectionne la cellule puis on tape le nom dans la zone de nom, ou on passe par le Gestionnaire de noms de l’onglet Formules (autrefois, le chemin était Insertion, Nom, Définir). Une formule devient alors lisible : au lieu de =D6+D6*$D$11, on lit =D6+D6*TVA. Dans une facture avec une cellule total_ht et une cellule TVA, le calcul du total TTC est alors =total_ht*(1+TVA). Un nom a trois avantages : la formule est compréhensible, le nom suit la cellule si on la déplace, et il se comporte comme une référence absolue. Quelques règles : un nom ne contient pas d’espace (on utilise le tiret bas), ne commence pas par un chiffre et ne ressemble pas à une adresse (AB12 serait refusé).
4.5Référencer une autre feuille ou un autre fichier
Pour faire référence à une cellule d’une autre feuille du même classeur, on fait précéder l’adresse du nom de la feuille suivi d’un point d’exclamation : =Paramètres!B2 (avec des apostrophes si le nom contient une espace : ='Prix 2026'!B2). Une référence peut aussi viser un autre fichier, auquel cas le chemin du fichier apparaît dans la formule. Ces « liaisons externes » sont pratiques mais fragiles : si le fichier est déplacé ou renommé, le lien casse. Elles posent aussi des questions de sécurité abordées dans le coin cyber.
Pour s’exercer
Exercice 1 : lire et écrire des adresses
(a) Quelle est la colonne numéro 28 ? (b) Écrivez en style A1 la cellule L5C30. (c) Combien de cellules contient la plage C3:F8 ? (d) Que désigne B:B ?
Voir la réponse
(a) Après Z (26e) viennent AA (27e) puis AB, qui est la 28e colonne. (b) La colonne 30 est AD (26 + 4) ; la cellule est donc AD5. (c) La plage couvre les colonnes C à F (4 colonnes) et les lignes 3 à 8 (6 lignes), soit 4 × 6 = 24 cellules. (d) La colonne B tout entière.
Exercice 2 : la bonne référence
La cellule E2 contient =B2*C$1. On la copie (a) en E5 ; (b) en F2. Quelles formules obtient-on ? Que faudrait-il écrire pour que le taux situé en C1 reste fixe dans toutes les copies ?
Voir la réponse
(a) La copie descend de trois lignes : B2 devient B5, tandis que C$1 garde la ligne 1 (verrouillée) : =B5*C$1. (b) La copie se décale d’une colonne vers la droite : B2 devient C2 et C$1 devient D$1 (seule la ligne est verrouillée, pas la colonne) : =C2*D$1. Pour que le taux reste fixe partout, il faut une référence absolue : =B2*$C$1, ou mieux, nommer la cellule et écrire =B2*taux.
5Sélectionner des cellules, des lignes, des plages
Pour agir sur les données (copier, mettre en forme, calculer), il faut pouvoir sélectionner rapidement les cellules concernées. On y parvient à la souris et au clavier, et chaque sélection se note d’une façon précise dans une formule. Cette notation est celle que vous utiliserez tous les jours.
5.1Plage de cellules
Une plage (ou bloc de cellules) est un rectangle de cellules voisines. Pour la sélectionner, on clique sur la cellule de début et on déplace la souris, bouton enfoncé, jusqu’à la cellule de fin ; on peut aussi cliquer sur la première, puis cliquer sur la dernière en maintenant la touche Majuscule enfoncée. La zone de nom indique alors le nombre de lignes et de colonnes de la sélection. Une remarque utile : la cellule de début s’affiche dans une couleur différente (en blanc), mais elle fait bien partie de la sélection comme les autres.
Dans une formule, une plage s’écrit avec les adresses de la première et de la dernière cellule, séparées par deux-points : D16:F21 désigne tout le rectangle de la colonne D à la colonne F et de la ligne 16 à la ligne 21 (soit 3 colonnes fois 6 lignes, donc 18 cellules). On lit les deux-points comme « jusqu’à ».
5.2Cellules disjointes
On peut sélectionner des cellules qui ne se touchent pas (« disjointes » ou « non contiguës »). Il suffit de maintenir la touche Contrôle (Ctrl) enfoncée en cliquant sur les cellules ou plages suivantes. Dans une formule, on les sépare par des points-virgules (version française) : D6;D8;D9. Attention, c’est le même caractère que celui qui sépare les arguments d’une fonction, ce qui peut surprendre : =SOMME(D6;D8;D9) additionne trois cellules, alors que =SOMME(D6:D9) additionne les quatre cellules de D6 à D9.
5.3Lignes et colonnes entières
Un clic sur le numéro d’une ligne (son en-tête) sélectionne la ligne entière ; un clic sur la lettre d’une colonne sélectionne la colonne entière. Au clavier, on place le curseur dans une cellule de la ligne et on appuie sur Majuscule + Espace pour la ligne, Ctrl + Espace pour la colonne. Dans une formule, une colonne entière s’écrit B:B, une ligne entière 3:3. Plusieurs lignes voisines se notent avec deux-points (6:9, lignes 6 à 9) et des lignes disjointes avec des points-virgules (6;8;9) ; en style L1C1, on note LNumeroDebut:LNumeroFin. Les colonnes entières sont pratiques pour les calculs qui doivent suivre l’ajout de nouvelles lignes, mais elles ralentissent les gros fichiers.
5.4Toute la feuille
Le petit carré vide, à l’intersection des en-têtes de lignes et de colonnes (en haut à gauche de la feuille), sélectionne toute la feuille. Le raccourci Ctrl + A fait la même chose. C’est utile pour changer la police de tout le document, ajuster la largeur de toutes les colonnes, ou copier une feuille entière. Autres raccourcis qui font gagner du temps : Ctrl + Fin mène à la dernière cellule utilisée, Ctrl + flèche saute au bord du bloc de données, Ctrl + Maj + flèche sélectionne jusqu’à ce bord.