LOC – 26/06/2018 – 2
Plan de la fiche
1. Une question de paramétrage
2. Référence relative ou absolue
3. Les rôles des caractères absolu ou relatif
Intégration d’éléments venant d’autres feuilles
Les insertions faussent le jeu normal
Notation absolu dans les séquences de formules
Présentation
Cette fiche est une étude d’ensemble sur la manière de référencer une cellule ou une zone de cellules dans les formules. J’explique aussi comment les références sont modifiées lors des copies de cellules enregistrant des formules
Un classeur est associé à cette fiche : télécharger le classeur 3_2-Referencement_cellules.
1. Une question de paramétrage
Le référencement d’une cellule est d’abord une question de paramétrage. Du temps du premier tableur de Microsoft (Multiplan), les cellules étaient référencées avec LxCy où (x) était le numéro de ligne (L), et (y) le numéro de colonne (C). Puis la référence des colonnes est devenue une lettre et progressivement, la référence d’une cellule est devenue une composition simple : (yx), lettre(s) de la colonne suivie(s) du numéro de ligne.
Aujourd’hui, le paramétrage laisse un choix.
Dans Menu : ▼ Outils, Options, LibreOffice Calc, Formule <[Options – LibreOffice Calc – Formule];
‡ Options de formule ‡
♫ Syntaxe de la formule :
…
– Calc A1 : (recommandé) les cellules sont référencées dans la syntaxe de LibreOffice Calc dans une composition simple : lettre(s) de la colonne suivie(s) du numéro de ligne.
– Excel A1 : idem, mais compatible avec la syntaxe d’Excel (quand un tableau Excel est directement ouvert dans Calc).
– Excel L1C1 : ancienne syntaxe d’Excel ; la lettre L est suivi du numéro de ligne, et la lettre C, suivie de la lettre ou des lettres de la colonne, l’ensemble accolé sans espace.
2. Référence relative ou absolue
Voir l’exemple en feuille 3_2-Referencement_cellules, § 2.
Dans les formules, les deux composantes d’une cellule (colonne et ligne) peuvent être notées simplement « A1 » (référence relative) ou en absolu « $A$1 », ou avec les combinaisons possibles : « $A1 », « A$1 ».
L’attribution du caractère absolu se fait en posant « $ » devant la ou les lettres de la colonne ou le numéro de ligne. Je note que le caractère relatif ou absolu peut être attribué indépendamment à la colonne, à la ligne, aux deux ou à aucune.
Les conséquences du caractère absolu ou relatif sont expliquées au paragraphe suivant.
Pour déclarer un caractère relatif, il suffit de saisir la composante sans poser le « $ » devant. Inversement, pour déclarer son caractère absolu, il suffit de poser le « $ » devant. La touche [F4] permet de modifier les caractères de la référence devant laquelle, dans laquelle ou juste à la suite de laquelle se trouve le point d’insertion pendant la saisie de la formule. [F4] joue comme une roue : les différentes possibilités sont proposées à tour de rôle.
3. Les rôles des caractères absolu ou relatif
C’est lors des copies de cellules que les caractères absolu ou relatif des références de cellules dans les formules jouent leur rôle.
Prenons comme exemple (§ 3. Copie de formules avec références relatives ou absolues), la cellule D13 copiée en I13. Cette cellule comprend une formule simple : =D12. Et D12 contient = « abcde ».
Telle quelle est enregistrée, la formule en D13 affiche « abcde ». Après copie, la formule en I13 est devenue : =I12. Or I12 est vide. Donc I13 est vide. Que s’est-il passé ?
Chacun des composants notés en relatif a été transformé, subissant un décalage identique à celui entre la cellule d’origine (D13, D14, etc.) et la cellule destination (I13, I14, etc.). Pour les colonnes, de D à I = 5 colonnes, donc la colonne dans la destination est devenue : D+5 = I. Pour la ligne, il n’y a pas de décalage.
L’exemple montre en colonne I le résultat de la copie des cellules de la colonne D. Il faut pointer les cellules I13 à I15 pour lire les formules dans la zone d’édition Formule.
3.1 Applications concrètes
Dans les tableaux, les notations relatives sont bien pratiques. Prenons l’exemple (§ 3.1 Applications concrètes) d’un tableau comportant sur chaque ligne des montants enregistrés dans 5 colonnes (de A à E), et une colonne total en (F). Le tableau comprend 3 lignes, 23 à 25.
Dans la cellule F23, j’enregistre la formule de totalisation : =somme(A23:E23). Puis je copie F23 dans F24:F25. Dans F24, la formule devient automatiquement =somme(A24:E24), dans F25 : =somme(A25:E25).
Maintenant, si je copie A23:F23 dans A29, la ligne A29 est identique à A23, y compris la formule qui a été modifiée dans la copie pour totaliser les cellules A29:E29.
Les notations absolues sont pratiques pour des paramètres comme des coefficients utilisés dans des tableaux. Prenons l’exemple d’une feuille (§ 3.1 Applications concrètes (2) ) comportant un premier pavé de coefficients : en B35 : 3, en B36 : 4;
Le tableau dans un deuxième pavé comprend 2 lignes 40 et 41 dont les formules calculent le produit de la somme (F23) par ces coefficients. En colonne A, les formules sont en absolu. En colonne B, les coefficients sont notés en relatif. À ce stade, ces produits sont justes.
Maintenant, je copie ce tableau A40:B41 dans A42. En colonne A, les résultats sont bons. Mais en colonne B, les références des coefficients (notées en relatif) ont été modifiées. Les résultats ne signifient plus rien.
3.2 Précisions importantes
Intégration d’éléments venant d’autres feuilles
Il n’y a aucun problème à intégrer dans des formules des références de cellules présentes sur une autre feuille. Le plus simple est d’insérer la référence de la cellule en allant cliquer dessus (après avoir affiché la feuille voulue) et de continuer la saisie ensuite ou de la valider : la feuille initiale s’affiche alors automatiquement.
Les références relatives sont décalées en cas de copie comme si toutes les références étaient sur la même feuille..
La référence d’une cellule issue d’une autre feuille est composée du nom de la feuille, et de la référence habituelle. Les deux sont séparées par “!”. Quand le nom de la feuille comprend des espaces il est posé entre guillemets simples. La référence de la feuille peut être notée en absolu (avec $ devant son nom) mais ce n’est pas indispensable.
Les insertions ou suppressions faussent le jeu normal
Prenons l’exemple d’un petit tableau de données (§ 3.2 Précisions importantes – Les insertions ou suppressions faussent le jeu normal. La formule de la somme est en relatif.
Je copie ce tableau, je modifie la formule de la somme en la mettant en absolu, et j’insère une nouvelle colonne. Bien qu’étant notée en absolu, la formule est modifiée : les références sont décalées pour tenir compte de l’insertion.
Je copie le premier tableau en faisant la même manipulation, mais ici, je supprime une colonne. La formule notée en absolu a été modifiée pour tenir compte de la suppression.
Donc, la fixité des notations absolues ne joue donc pas dans le cas d’insertions ou de suppressions de colonnes ou de lignes. Toutes les cellules impactées par ces insertions ou suppression ont leur formule modifiée comme on vient de le voir.
Notation absolue dans les séquences de formules
Dans les tableaux comme celui cité dans le premier exemple, l’usage veut que les colonnes soient notées en absolu, par opposition aux lignes qui elles, nécessitent dans l’exemple, une notation relative. La raison en est simple.
Cela concerne des références utilisées comme paramètres dans des fonctions complexes (comme celle de base de données). On constate parfois des anomalies dans les recherches ou les extractions quand les références qui devraient rester stables ne sont pas notées en absolu. En clair, pour les recherches en lignes, on note les références de lignes en relatif, et inversement pour les recherches en colonnes.
Incidemment, il est intéressant de voir ces paramètres stables notés avec le $ signifiant le caractère absolu de l’élément de référence.
Zones nommées
Les zones nommées, présentées en détail ici : 1.5 Gérer les zones nommées, sont une alternative vraiment très pratique aux notations absolues. Une zone nommée est définie avec une zone notée en absolu (y compris avec la référence de la feuille). Son utilisation dans les fonctions de base de données est conseillée.
Je précise que les zones nommées ne sont jamais notées en absolu dans une formule. Elles n’en ont pas besoin.
Informations complémentaires
Liens