Maîtriser un tableur en référençant judicieusement ses données

CHOISIR LE TYPE DE RÉFÉRENCE

Dans Excel ou OpenOffice (LibreOffice), le moindre calcul fait appel à une formule. Constituées de simples calculs ou faisant appel à des fonctions plus évoluées (pour obtenir une somme, tester une condition, passer un texte en majuscules, etc.), la plupart sont simples à mettre en œuvre. La pierre d’achoppement se trouve en réalité au niveau de la référence de la cellule, qui aura une incidence sur la lisibilité du classeur et la facilité à le construire, notamment lors du déploiement des formules (réutilisation pour appliquer le même calcul à d’autres cellules).

RÉFÉRENCES ABSOLUES ET RÉFÉRENCES RELATIVES

La référence la plus simple, sans autre élément que les données de ligne(s) et de colonne(s) pour désigner la cellule ou la plage de cellules, est dite relative. Lorsque l’utilisateur saisit “= B2” dans une cellule quelconque, ce n’est pas l’adresse de la cellule B2 qui est mémorisée, mais sa position par rapport à la cellule où se trouve la formule. La référence relative est indiquée lorsque l’on souhaite effectuer le même traitement sur un ensemble de données, par exemple pour appliquer le même taux de taxe sur la valeur ajoutée (TVA) à toute une colonne.

Les valeurs employées comme des constantes (TVA, taux de remise) sont souvent saisies dans une cellule unique, utilisée dans tout le reste du tableur. Si la remise change, il suffit de modifier la cellule où elle a été définie pour adapter tout le classeur. On utilise alors une référence dite absolue, qui désigne précisément la cellule qui contient la valeur. Le marqueur de la référence absolue est le caractère $. Il peut s’appliquer à la ligne, à la colonne, ou au deux. L’exemple type d’une référence mixte (relative et absolue à la fois) est la simulation de plusieurs taux de remises.

La référence 3D, qui peut être absolue ou relative, lie sur plusieurs feuilles des cellules ou des plages de cellules qui contiennent une donnée de même nature. Par exemple, dans un classeur constitué de 12 feuilles correspondant au budget sur chacun des mois de l’année et construites de la même manière, la référence 3D permet d’effectuer la somme d’un poste de dépenses donné sur les 12 mois, situé sur la même cellule dans chacune des feuilles.

Deux grands types de syntaxes sont acceptés. En mode A1, configuré par défaut, les colonnes s’expriment par des lettres et les lignes par des nombres entiers. En mode L1C1, lignes et colonnes sont des nombres. Plus rarement employée, la notation L1C1 peut être activée via les options du tableur. Généralement considérée comme moins lisible, elle peut cependant se révéler plus pratique quand les positions doivent être calculées par programmation, ou lorsque c’est réellement l’ordre de la colonne qui est recherché (^{voir tableau}).

Le mode de déploiement souhaité pour la formule détermine le choix entre référence absolue et relative. Lorsque la même formule est appliquée dans le reste du classeur, le tableur tient compte du type de référence choisie, soit en l’adaptant (référence relative), soit en la préservant (référence absolue). Pour déployer une formule, on utilise le copier-coller ou la valise située au bas de la première cellule.

UNE APPROCHE SÉMANTIQUE

Dans l’emploi quotidien de leur tableur, nombre d’utilisateurs se contentent des deux références les plus basiques (relative et absolue), qui présentent en fait de nombreuses limitations. Notamment, dès que la feuille gagne en complexité (nombreuses formules, formules imbriquées, grands classeurs ou encore liens multiples entre fichiers), la moindre adaptation ou vérification peut se transformer en parcours du combattant. Le premier avantage de l’utilisation des noms est le gain en clarté. Par exemple, la formule “= Somme(VentesPremierTrimestre)” peut être comprise immédiatement, ce qui n’est pas le cas de “= Somme(B3 : C5)”. Les noms peuvent également être utilisés directement comme des constantes : par exemple, il est possible de définir deux noms, “remise_faible” et “remise_forte”, égaux à “= 10 %” et “= 20 %”, et d’y faire référence dans l’ensemble de la feuille. Mais l’intérêt des noms va bien plus loin. Ils permettent véritablement de construire un classeur selon une approche sémantique, la couche supplémentaire introduite entre la formule et l’utilisateur conduisant à aborder les données par leur signification, et non par leur emplacement. Faire appel au chiffre d’affaires d’une période ou au stock d’un ensemble de produits devient alors possible, sans avoir à se soucier de l’emplacement des valeurs, qui peuvent même se trouver dans un autre fichier (si le nom fait appel à un lien externe). Les mêmes données peuvent être appelées plusieurs fois dans le classeur (par exemple pour faire une moyenne ou une somme). Si la plage de données est agrandie, il suffira de changer la définition du nom concerné pour que les deux appels s’adaptent.

Une autre facilité offerte par les noms est l’obtention d’un chiffre par croisement entre deux séries de données. Par exemple, pour afficher le chiffre d’affaires du mois de mars 2008 à partir d’un tableau à deux dimensions (mois en abscisses, années en ordonnées), nul besoin de chercher où se trouvent les cellules sources, il suffit simplement de taper “= mars annee_2008”.

Une autre application des noms est la création de plages dynamiques, qui s’adaptent automatiquement lorsque des cellules sont ajoutées. L’exemple type est le graphique créé sur plusieurs jours, dont on souhaite l’adaptation automatique si d’autres journées sont ajoutées au tableau d’origine. Avec des formules classiques, l’utilisateur sera contraint de changer manuellement les séries de données dans la définition du graphique. Construit astucieusement, le nom peut pointer vers une plage dynamique, qui s’adaptera en taille à la saisie de l’utilisateur.

Nommer une plage est une opération rapide : il suffit d’utiliser le menu idoine (Insertion > Nom > Définir) ou, plus simplement, de sélectionner les valeurs concernées et de taper un libellé dans la zone de nom située en haut à gauche du classeur.

Lors de l’exploitation directe de tableaux de chiffres à deux dimensions, des séries de noms correspondant aux titres peuvent être créées automatiquement, sur la base des en-têtes de tableau, en utilisant une commande dédiée (Insertion > Nom > Créer).

En outre, pour désigner des données, l’utilisation d’étiquettes est prévue. Dans leur logique de fonctionnement, ces étiquettes s’apparentent aux noms : elles sont employées pour désigner les cellules d’un tableau au sein d’une même feuille, mais sans définition explicite de libellés correspondants. L’étiquette constitue une solution moins complète que le nom, mais sa mise en œuvre est plus simple, notamment dans la détermination automatique de la taille des séries de valeurs. Les étiquettes ne sont pas reconnues automatiquement, cette option doit être activée dans les préférences du tableau.

• Un classeur compatible Excel et OpenOffice (LibreOffice) est téléchargeable en complément sur le site WK-Vet.fr. Chacune de ses feuilles illustre une notion fondamentale dans la compréhension des références de cellules (absolues et relatives, 3D, noms, étiquettes, plages dynamiques) et présente de façon plus détaillée les manipulations à effectuer pour la mettre en œuvre. http://www.wk-vet.fr/mybdd/164/164_3466/references_cellules.html