LOC – 12/05/2018 – 1

Plan de la fiche

1. Anatomie d’une formule

1.1 Formule et résultat

1.2 Cellule et résultat

1.3 Composition d’une formule

2. Rédiger une formule

2.1 Priorité des opérateurs

2.2 Utiliser les parenthèses

2.3 Automatiser les références de cellules

2.4 Paramètres des fonctions

3. Quelques précisions

3.1 Calculs automatiques ou à la demande

3.2 La fonction Somme()

3.3 Copie de cellules et transformation de formules

3.4 Valeur, précision et affichage

3.5 Cellules vides et valeurs nulles

Valeur d’origine

Valeurs nulles

À retenir au sujet des types de données

3.6 Fiches de présentation des fonctions

Présentation

Cette fiche rassemble ce qu’il faut savoir, avant d’utiliser des formules de calcul et les fonctions.

Présentation

Un tableur tire son nom des tableaux de calculs qu’il permet de réaliser facilement. Cette fiche est donc au cœur de notre sujet. Paradoxalement, c’est un sujet facile.

1. Anatomie d’une formule

Les calculs sont réalisés par les formules. Nous avons vu à la fiche 2.2 Saisir l’information, saisir une formule comment on saisit une formule dans une cellule. Nous allons approfondir cette question pour mieux utiliser les formules.

1.1 Formule et résultat

Il faut bien comprendre qu’une cellule contenant une formule propose en fait deux valeurs bien différentes : la formule dans sa rédaction et son résultat.

La formule, c’est du texte, obéissant à un certain nombres de règles. Comme tel, ce texte est éditable comme dans n’importe quelle cellule contenant du texte.

Le résultat, c’est l’interprétation de la formule par le système. Nous verrons que cette interprétation peut être automatique ou à la demande. Quoi qu’il en soit, une cellule contenant une formule contient aussi un résultat.

En règle générale, la formule est visible dans la zone édition Formule, et le résultat apparaît en clair dans la cellule. Il est possible de faire apparaître la formule dans la cellule :

Bien qu’invisible dans cette option, le résultat est toujours disponible.

1.2 Cellule et résultat

Le tandem Cellule – Résultat est analogue au tandem Variable – Valeur, comme en algèbre quand par exemple, X=5. X est une variable, un contenant. 5 est un résultat, contenu dans la variable. Quelles que soient les hypothèses, la variable s’appelle toujours X ; en revanche, sa valeur varie. Il en est de même pour une cellule : elle garde toujours la même référence ; mais le résultat de sa formule dépend du contenu des paramètres et change avec les variations de leurs valeurs.

Ce changement ne doit pas nous faire oublier qu’une cellule ne contient qu’un seul résultat à la fois. Nous sommes avec un tableur dans un autre univers que les fonctions algébriques comme z=2x+y où le résultat, z, est une séquence de résultats matérialisée par le graphe de la fonction. Si plusieurs résultats sont à prévoir en fonction de variations du domaine de définition, on utilisera des fonctions conditionnelles dans la formule. C’est un peu lourd, mais ça marche parfaitement.

1.3 Composition d’une formule

Une formule commence par le caractère « = » et sa suite ressemble à une expression algébrique dans laquelle les variables sont remplacées par les références de cellules.

Les composants d’une formule sont : les séparateurs, les opérateurs et les opérandes. Une formule peut être composée de plusieurs expressions, délimitées par les séparateurs et reliées par les opérateurs. Ces expressions peuvent être, selon leur résultat, numériques, de chaînes de caractères ou logiques (VRAI ou FAUX).

Les séparateurs sont les parenthèses, ouvrante et fermante.

Les opérateurs dépendent du résultat :

• numérique : les 4 opérations +, -, *, /, et l’élévation à la puissance ^. Le signe – posé devant une expression est un opérateur de changement de signe.
• de chaîne de caractères : & pour assembler deux textes.
• logique : <, >, >=, >=, <>.

Les opérandes sont les constantes, les variables (références de cellules) et les fonctions. Une fonction est un code suivi du jeu de parenthèses (ouvrante et fermante) à l’intérieur desquelles sont précisés les paramètres (aucun ou plusieurs séparés par un « ; »). Ces paramètres sont des opérandes (constantes, variables ou autres fonctions).

Dans la formule, les opérandes sont généralement séparés par un opérateur.

2. Rédiger une formule

Pour qu’une formule soit reconnue comme telle, il est impératif que la cellule qui l’accueille soit de type Nombre. Si la cellule est de type Texte, la formule sera prise comme du texte et ne sera pas interprétée comme formule. 

2.1 Priorité des opérateurs

Une formule est interprétée en commençant par la gauche. Néanmoins, les opérateurs numériques n’ont pas tous la même priorité. Il faut donc le savoir pour les mettre dans l’ordre voulu.

En fait, trois priorités dans les opérateurs numériques : l’élévation à la puissance a la priorité 1 ; la multiplication et la division, la priorité 2 ; l’addition et la soustraction, la priorité 3.

Tous les opérateurs logiques ont la même propriété, à l’exception de non() qui est prioritaire sur les autres.

Il est possible de forcer les priorités en jouant avec les séparateurs.

2.2 Utiliser les parenthèses

Les parenthèses facilitent la lecture des formules complexes et permettent de forcer la priorité des opérateurs. Il ne faut pas hésiter à les utiliser.

En mode édition, quand le curseur est proche d’une parenthèse, celle-ci se met en gras ainsi que sa correspondante. Le repérage en est facilité.

Les expressions entre parenthèses ont priorité sur tous les opérateurs. En cas d’expressions emboîtées, la plus intérieure est interprétée la première.

2.3 Automatiser les références de cellules

Au lieu de taper la référence d’une cellule, il est possible de cliquer dans cette cellule en cours de frappe et sa référence est insérée automatiquement au point d’insertion. La référence apparaît en négatif (contraste inversé). Il ne s’agit pas, à proprement parler d’une sélection car il est possible de continuer naturellement la frappe (avec un opérateur ou un séparateur). Mais tant que l’effet négatif est affiché, un clic dans une autre cellule pose sa référence en remplacement.

Cette insertion automatique de référence joue aussi en sélectionnant une plage de cellules : les références extrêmes en diagonale sont insérées dans la formule séparées par « : ».

Il est possible aussi d’insérer automatiquement le nom d’une zone nommée en la sélectionnant dans la liste des plages nommées :

Transformer une référence relative en référence absolue, est expliqué ici : 3.2 Le référencement des cellules. J’aborde aussi dans cette fiche la grande richesse des séquences de formule.

2.4 Paramètres des fonctions

Les fiches de la partie 3. Calculs présentent les fonctions en détail. Une fonction peut avoir des paramètres de types différents (Nombre, Date, Texte, Logique). Il existe des compatibilités (Nombre et Date, par exemple). Mais ces types doivent être respectés sous peine de recevoir le code erreur 504.

En règle générale, un paramètre peut être indistinctement une constante ou une variable. Néanmoins, quelques fonctions exigent des paramètres en constantes.

Certains paramètres expriment des zones de cellules : une zone est définie par les références des cellules extrêmes en diagonale, séparées par “:”. Par exemple, la fonction Somme(A2:A10) donnera le total des cellules de A2 à A10 compris.

Une cellule ou une zone peut être nommée, en recevant un nom plus explicite que les références. Cela facilite la compréhension de la formule. Le nommage des zones et leur utilisation sont décrits ici : 1.5 Gérer les zones nommées.

3. Quelques précisions

3.1 Calculs automatiques ou à la demande

Sauf dans des tableaux complexes et importants, le calcul des formules est généralement automatique. Il est paramétré ici :

Quand le calcul n’est pas automatique, [F9] lance le calcul à la demande.

Quand le calcul n’est pas automatique, en validant la saisie d’une formule, celle-ci est calculée. Mais les cellules qui font appel à celle-ci ne sont pas recalculées. Il faut les sélectionner et [F9] pour qu’elles soient recalculées. Seul l’activation du choix Calculer automatiquement recalcule toutes les cellules du classeur.

3.2 La fonction Somme()

Du fait de sa fréquence d’utilisation élevée, la fonction Somme() présente une facilité de saisie intéressante.

3.3 Copie de cellules et transformation de formules

Lorsque une ou des cellules comportant des formules sont copiées ailleurs dans le classeur, les références des formules de ces cellules sont modifiées. C’est expliqué ici : 3.2 Le référencement des cellules.

3.4 Valeur, précision et affichage

Une cellule peut conserver une valeur (d’origine ou pas), différente de celle qui est affichée. La valeur visible, affichée, est le résultat du format de la cellule appliqué à sa valeur. Que la valeur soit constante ou calculée ne change rien.

La valeur mémorisée par une cellule est visible, sans formatage, quand la cellule est active, dans la zone d’édition Formule. Quand cette valeur est du texte, elle est précédée d’une apostrophe. Les dates sont au format JJ/MM/AAAA.

La précision d’un nombre réel se définit avec le nombre de ses décimales. Il existe une option système (Outils, Options, LibreOffice Calc, Calculs, ‡ Calculs généraux ‡ ◩ Exactitude comme affiché) qui permet de corriger la précision des nombres au visible. Autrement dit, avec cette option active, la précision d’un nombre est définie par son format. Cette option est globale et cela peut être ennuyeux. La fonction Arrondi() permet d’obtenir le même résultat à la demande.

3.5 Cellules vides et valeurs nulles

Par défaut, toutes les cellules d’un nouveau tableau sont de type Nombre.

Toute cellule créée par insertion de colonne ou de ligne récupère le format de sa colonne de gauche ou de sa ligne précédente.

Valeur d’origine

On appelle valeur d’origine la valeur que prend une cellule, soit dans un nouveau tableau, soit créée par insertion. Donc, en fait, toute cellule porte une valeur à partir de sa création. Cette valeur est une constante. Elle pourra être remplacée par une autre constante ou une formule lors de la construction du tableau.

Tant qu’une cellule porte sa valeur d’origine, elle apparaît vide. Le formatage du contenu (police, effets) n’a aucun effet sur cette apparence, mais il est enregistré dans la cellule et il sera appliqué dès qu’une valeur (constante ou calculée) y sera enregistrée.

Toute cellule vide peut intervenir dans le calcul d’une formule sans déclencher d’anomalie. En fonction du type de cellule, la valeur d’origine est une constante nulle, c’est à dire :

• Nombre : 0 ;
• Date : 0 ;
• Texte : “”, appelée aussi chaîne de caractères vide ;
• Logique : FAUX.

Dès qu’une constante ou une formule est saisie dans une cellule, celle-ci perd sa valeur d’origine.

Valeurs nulles

Une valeur nulle est différente de la valeur d’origine. C’est soit une constante, soit le résultat d’une formule.

Le format enregistré dans la cellule s’applique aux valeurs nulles.

Pour les Nombres nuls, l’affichage est conditionné par une option système : dans Outils, Options, LibreOffice Calc, Affichage, ‡ Afficher ‡ ▣ Valeurs zéro. Par défaut, les cellules portant des nombres nuls apparaissent vides. En cochant la case, les zéros apparaissent. À ma connaissance, il n’existe pas de format spécifique permettant de s’affranchir du paramétrage système.

Étant donné qu’il est toujours possible de changer le type d’une cellule en modifiant son format (passer de Nombre à Texte, par exemple), la question se pose de savoir que devient la valeur et son affichage.

Première règle : la cellule conserve la valeur saisie et les différentes propriétés du format déclaré, même quand la propriété type d’information du format est modifiée.

Deuxième règle : la valeur nulle d’une cellule apparaît différemment suivant le sens des transformations de type.

• Type initial : Nombre ; valeur saisie : 0.
  • Nouveau type : Date ; valeur : 30/12/1899
  • Nouveau type : Texte ; valeur : “0”
  • Comme la saisie initiale s’est faite sur une cellule de type Nombre, l’affichage quel que soit le nouveau type se fera en fonction du paramétrage système de l’affichage des valeurs zéro. La cellule restera donc soit vide, soit affichera la valeur indiquée lisible dans la zone d’édition.
• Type initial : Date ; valeur saisie 30/12/1899 (date nulle)
  • Nouveau type : Nombre ; valeur affichée : 0
  • Nouveau type : Logique ; valeur affichée : FAUX
  • Nouveau type : Texte ; valeur affichée “0”.
• Type initial : Date ; valeur saisie 00/00/0000
  • Nouveau type : Nombre ; valeur affichée 00/00/0000
  • Nouveau type Logique ; valeur affichée : VRAI
  • Nouveau type Texte ; valeur affichée : 00/00/0000
  • Explication : la date 00/00/0000 n’existe pas. Elle ne peut donc être enregistrée sous forme de nombre (le nombre de jours écoulés depuis le 30/12/1899. Alors, elle est enregistrée comme du texte et se comporte comme tel. Voir plus bas.
• Type initial : Logique ; valeur saisie FAUX
  • Nouveau type : Nombre; valeur affichée : 0
  • Nouveau type : Date ; valeur affichée : 30/12/1899
  • Nouveau type : Texte ; valeur affichée : “0”
• Type initial : Texte ; pas de valeur saisie ou saisie d’une ou plusieurs espaces
  • Nouveau type : Nombre ; valeur affichée vide
  • Nouveau type : Date ; valeur affichée vide
  • Nouveau type : Logique ; valeur affichée vide
  • Explication : le type initial Texte l’emporte sur les types ultérieurs.
• Type initial : Texte ; valeur saisie “123”
  • Nouveau type Nombre ; valeur affichée : 123. Ce nombre sera reconnu dans les calculs arithmétiques. Dans la zone d’édition, il apparaît précédé d’une apostrophe qui indique son origine Texte.
  • Nouveau type Date ; valeur affichée : 123
  • Nouveau type Logique : 123.
  • Explication : comme dans le cas précédent, le type initial Texte l’emporte sur les types ultérieurs.

À retenir au sujet des types de données

Les données saisies dans des cellules Texte restent du texte.

Si le texte est composé exclusivement d’espaces, sa valeur logique est FAUX comme un texte vide. De même, une telle cellule reste une cellule de texte qu’il n’est pas possible d’intégrer dans une formule arithmétique : l’anomalie #valeur indique que la formule ne peut être exécutée.

En revanche, si le texte est transposable en nombre, bien que restant une cellule de texte, cette cellule sera acceptée dans des calculs arithmétiques.

Une cellule Nombre peut être intégrée dans une formule de texte comme s’il s’agissait d’un texte. La fonction Texte() permet de formater ce nombre avant son intégration.

Une cellule Logique aura la valeur FAUX si la valeur numérique est nulle ou si le texte est vide ou composé d’espaces. Dans tous les autres cas, sa valeur est VRAI.

3.6 Fiches de présentation des fonctions

Les fiches de cette partie présentent les fonctions par type. Le ou les types sont précisés dans l’intitulé de la fiche. J’ai volontairement délaissé les fonctions de spécialistes (trigonométrie, statistiques, probabilités, finances, binaire informatique, etc.). J’ai voulu éliminer toutes les imprécisions de l’aide, notamment dans la définition des paramètres et leur fonctionnement.

Le plan de ces fiches regroupe les fonctions par similitude. La rédaction de la fiche, dans les sous-paragraphes présente les fonctions par ordre alphabétique. L’ordre est donc différent entre le plan et le contenu. Sachant cela, chacun se repèrera à sa guise.

Pour chaque fonction, quand c’est utile, j’indique les fonctions identiques, comparables ou voisine :

• Identique indique les fonctions qui ne diffèrent que par leur nom. Les paramètres utilisés et les résultats sont identiques.
• Comparable indique deux fonctions dont le résultat est bien différent mais leurs paramètres sont identiques ou très voisins. En général, ce qui est dit pour l’une et valable pour l’autre en faisant les adaptations nécessaires. J’utilise l’expression “Ressemble à…”
• Voisine indique des fonctions dont parfois les noms se ressemblent, qui donnent des résultats proches ou plus élaborés. La connaissance de fonctions voisines permet de mieux choisir la bonne fonction en raison du contexte. J’utilise l’expression : “ Voir aussi…”.

Pour mieux se repérer, je pose les expressions en minuscule (avec l’initiale en majuscule), en italique, et suivi des parenthèses ouverte et fermante. Dans le titre de présentation de la fonction, je mets les paramètres entre parenthèses à la suite du nom de la fonction. Et dans la présentation, je cite ces paramètres en italique et minuscules,avec l’initiale en majuscule.

Informations complémentaires

Liens

1.5 Gérer les zones nommées

2.2 Saisir l’information, saisir une formule

3.2 Le référencement des cellules

LOC – 14/08/2018 – 2

Présentation

Cette fiche détaille le paramétrage de deux présentations de confort : le fond rouge ou vert dans la colonne Référence de MaCave pour indiquer un vin au stock nul ou pas, et l’alternance d’un fond discret sur les lignes paires pour favoriser la lecture grâce à un effet de trame.

1. Une couleur suivant le stock

Je veux être informé sans avoir à consulter la colonne stock si le vin indiqué sur une ligne est en stock ou pas. J’ai choisi de mettre dans la première colonne un fond rouge si le stock est nul, ou vert dans le cas contraire. Ce coloriage permet en outre de mettre en valeur la référence à noter pour enregistrer les entrées ou les sorties.

Colorier une cellule est une opération de formatage. Quand ce formatage dépend d’une condition, il s’agit d’un formatage conditionnel. Cette fonctionnalité est puissante. Je la présente en détail ici : 4.2 Le formatage conditionnel.

Je pars de la cellule A2 de la feuille MaCave.

▼ Format, ▼ Formatage conditionnel, ▼ Gérer… : une fenêtre de gestion des formats conditionnels s’ouvre.

Trois lignes apparaissent. Seules les deux premières nous intéressent ici.

Pour chaque ligne, deux indications : la zone aux cellules de laquelle la première condition indiquée s’applique. Il suffit de sélectionner une ligne et ▼ ◄ Éditer… ► pour rentrer dans le détail. Je prends la première ligne.

La condition 1 est déployée :

elle exploite une formule :

qui teste si la référence n’est pas vide (ligne existante) et si le stock de la ligne ($K2) est nul. Si cette formule retourne une valeur Vrai, le style Ma cave vide s’applique à la cellule. Ce style se caractérise par un fond de couleur rouge.

En cliquant sur la condition 2, elle se déploie : ($K2<>0). Si le stock n’est pas nul, le style Ma cave pleine est appliquée à la cellule (un fond vert).

En bas de fenêtre, la zone indique toutes les cellules auxquelles s’appliquent ces conditions, en clair, la colonne Référence.

2. Une trame pour faciliter la lecture

Pour les tableaux s’étalant sur tout l’écran et occupant de nombreuses lignes, il est intéressant de colorier une trame pour faciliter la lecture. Par exemple, pour faire simple, il s’agit de mettre un fond légèrement coloré sur les lignes paires.

Nous venons de voir comment colorier le fond d’une cellule en fonction d’une valeur (stock nul ou pas). Ici, nous allons voir comment colorier une ligne.

J’ouvre la feuille Entrées et je sélectionne la cellule A2. J’appelle la fenêtre de formatage conditionnel comme ci-dessus. Une seule condition est affichée sur une zone qui correspond aux lignes préparées de la feuille (A2 à J101).

Je sélectionne la première ligne et ▼ ◄ Éditer… ► ; la condition joue sur le résultat d’une formule, et cette formule est :

Cette expression assemble deux conditions avec Et() ; elle retourne VRAI si les deux conditions sont VRAI.

La première teste si la ligne (Ligne() ) est inférieure ou égale au nombre d’éléments non vide dans la colonne A (Nbval() ). En clair, la ligne en cours doit être comprise dans les lignes existantes.

La deuxième teste si le numéro de ligne est pair.

Si ces deux condition sont vraies (une ligne existante paire), le style Ligne paire est appliquée à la ligne dans la zone. Le style Ligne paire est caractérisé uniquement par un fond de couleur bleu pâle.

Cet exemple permet de comprendre un peu mieux comment fonctionne le formatage conditionnel.

Toutes les cellules de la zone sont concernées par les conditions indiquées. Le calcul des conditions indiquées est donc fait pour chaque cellule de la zone. Des variables sont disponibles comme ici ligne() : Colonne(), Actuelle() désignent respectivement la colonne courante ou la cellule actuelle.

Informations complémentaires

Liens

4.2 Le formatage conditionnel

Actuelle()

Colonne()

Ent()

Et()

Ligne()

Nbval()

LOC – 14/08/2018 – 2

Présentation

Cette fiche présente en détail deux formules : le calcul du stock et la récupération, sur les feuilles Entrées et Sorties, des caractéristiques d’un lot. Mais au préalable, il me faut parler de la préparation des feuilles et des plages nommées.

1. Préparation des feuilles

L’application fonctionne parce que les feuilles sont préparées. Chacune est prévue pour enregistrer un nombre maximum de lignes (100 dans l’exemple). Cela signifie que sur les 100 lignes prévues, des formats sont enregistrés et surtout, les formules sont présentes dans toutes les colonnes calculées.

2. Plages nommées

J’utilise les plages nommées pour définir des plages de données. Plusieurs avantages :

les formules sont plus explicites avec des plages nommées qu’avec des références lignes-colonnes ;

en cas d’extension des zones, il est rapide de mettre à jour les plages nommées sans avoir besoin de corriger les formules ; et même, quand l’extension de zone se fait par insertion de lignes ou de colonnes à l’intérieur de la zone, les plages nommées concernées sont mises à jour automatiquement.

Ces plages nommées tiennent compte de la préparation des feuilles et du nombre maximum de lignes prévues. L’intégration des plages nommées permet d’étendre le nombre maximum de lignes facilement.

Plages nommées de l’application

Je désigne les plages nommées avec un préfixe correspondant à leur feuille (Cave, Entrées, Sorties). Sans autre complément, la plage indique la zone d’informations de la première à la dernière colonne : ces plages sont utiles pour les fonctions de type base de données pour des recherches ou extractions. Avec le suffixe « Ref », la plage indique la colonne Référence. Avec le suffixe « N », la plage indique le nombre de bouteilles mouvementées en Entrées ou Sorties.

À noter enfin : chacune des deux tables de libellés sont des plages nommées.

3. Formule de calcul du stock (feuille MaCAve)

Cette formule, en K2, se présente ainsi :

Elle fait appel deux fois à la fonction Somme.si().

Cette fonction comprend 3 paramètres :

• Liste de recherche
• Information à rechercher
• Liste des éléments à totaliser

La liste de recherche est la colonne Référence de la feuille Entrées (EntreesRef, correspondant à la colonne A) ou de la feuille Sorties (SortiesRef, colonne A).

L’information à rechercher dans cette liste de recherche est la Référence inscrite en début de ligne de la feuille MaCave (sur la ligne 2, en A2).

La liste de éléments à totaliser est la colonne Nombre de bouteilles de la feuille Entrées (EntreesN, colonne H) ou Sorties (SortiesN, colonne C).

Comment fonctionne cette formule ?

Nous sommes sur la feuille MaCave, en ligne 2. La formule en K2 veut calculer le stock du lot dont la référence est en A2. Quand ce stock sera calculé, le système passera à la ligne 3, avec la formule K3, pour calculer le stock de la référence en A3, et ainsi de suite jusqu’à la fin du tableau de MaCave.

Dans un premier temps, la formule recherche la référence lue en A2 dans la feuille Entrées, dans la colonne Référence, zone nommée EntreesRef. Pour cela, elle balaie toute la colonne. Quand elle trouve sur une ligne la même référence que la référence lue, elle cumule le montant (nombre de bouteilles entrées de la colonne H) lu sur cette ligne. Puis elle passe à la ligne suivante, jusqu’à la fin du tableau Entrées. Il peut y avoir aucune, une ou plusieurs occurrences : cela importe peu, le cumul est fait selon les situations.

Dans un deuxième temps, la formule fait une recherche analogue mais dans la feuille Sorties.

Quand les deux cumuls sont terminés, la formule retourne leur différence (Entrées moins Sorties) et la pose comme résultat dans la cellule : c’est le stock.

Ces calculs ne sont effectués que lorsqu’une cellule mentionnée dans la formule (ou ses fonctions) est modifiée, c’est à dire concrètement quand une entrée ou une sortie sont enregistrées. Ils sont faits aussi au moment du chargement du classeur en mémoire.

4. Récupération des caractéristiques

Cette récupération se présente sur plusieurs colonnes des feuilles Entrées et Sorties.

Pour l’exemple, prenons la feuille Entrées, ligne 2, colonne E, Désignation : cette formule ressemble à :

La fonction Si() comprend trois paramètres :

• une condition : $A2<>“”;
• une fonction à exécuter si la condition donne un résultat VRAI;
• une fonction à exécuter si la condition donne un résultat FAUX.

En clair : si la cellule de début de ligne ($A2) n’est pas vide, la fonction Recherchev() est appliquée pour charger la cellule ; sinon, la cellule est laissée à vide.

La fonction Recherchev() comprend 4 paramètres :

• élément à rechercher (ici, $A2, la référence de début de ligne) ;
• matrice (ici, cave, correspond à tout le tableau de la feuille MaCave) ;
• colonne de l’élément à extraire (numéro dans la matrice en partant de 1 pour la première colonne ; ici, 5, correspond à la colonne E Désignation) ;
• type d’extraction (un code, 0 ou 1).

L’objectif de la fonction Recherchev() est de trouver une information (élément à extraire) présente, éventuellement, dans la ligne d’une matrice ; la recherche se fait par identification sur l’élément à rechercher dans la première colonne de la matrice (recherche verticale).

Deux types d’extraction sont possibles.

• Soit l’information d’identification recherchée dans la première colonne de la matrice doit être strictement identique à l’élément à rechercher. Dans ce cas, la première colonne de la zone de recherche peut présenter des éléments dans le désordre. Si l’élément à rechercher est présent, l’élément à extraire est retourné ; mais si aucune identification stricte n’est possible, une anomalie est retournée à la place de l’élément à extraire (Valeur non disponible, #N/D). Le type d’extraction indiqué est non trié (0), comme dans notre formule. La recherche s’arrête dès qu’une ligne est trouvée.
• L’autre type d’extraction ne nous intéresse pas ici.

Comment fonctionne Recherchev() ?

Le système balaie la première colonne de la matrice en comparant à chaque ligne la valeur lue avec l’élément à rechercher et en tenant compte du type d’extraction. Quand il trouve une ligne répondant aux critères, il retourne l’information présente sur cette ligne dans la colonne de l’élément à extraire.

Informations complémentaires

Liens

Recherchev()

Si()

Somme.si()

LOC – 14/08/2018 – 2

Présentation

Cette fiche détaille comment est organisée la saisie des informations dans l’application.

1. Trois moments

L’entrée des informations dans MaCave se fait essentiellement en trois moments :

• la création d’un nouveau lot,
• l’entrée d’un lot,
• la sortie d’une ou plusieurs bouteilles d’un lot.

Il existe des opérations annexes qui peuvent intervenir lors du premier moment.

2. Création d’un nouveau lot

Contexte : une bouteille ou un carton de bouteilles doivent être mis en cave. À cette occasion, un nouveau lot doit normalement être créé dans MaCave.

Si exceptionnellement, cette entrée concerne le réapprovisionnement d’un lot déjà existant, il n’y a pas de création de lot à ce moment. Il s’agit tout simplement d’une entrée.

Méthode : j’ouvre la feuille MaCave et je la trie sur la colonne Référence. Je saisis une nouvelle ligne en créant la référence (dans mon système, Lettre de l’année, et numéro d’ordre égal au dernier +1). Puis bouteille en main, je saisi toutes les caractéristiques. Je m’arrête à la colonne Stock qui n’a pas à être saisie puisqu’elle sera calculée.

Je passe ensuite au deuxième moment Entrée d’un lot.

Puis je mets mon vin en cave en notant les emplacements que je saisis ensuite dans MaCave. Il s’agit d’informations non critiques mais bien utiles quand les casiers sont nombreux.

Concernant les caractéristiques Région-crû et Couleur-type, j’ai opté pour des saisies contrôlées. Seuls les libellés présents dans des tables associées sont disponibles. Ce qui implique, quand un libellé n’existe pas de le créer. Les tables sont dans la feuille Libellés.

3. Entrée d’un lot

Contexte : ce moment concerne l’entrée d’un nouveau lot ou le réapprovisionnement d’un lot déjà existant. Dans un réapprovisionnement, les bouteilles du lot ont toutes leurs caractéristiques identiques à celles d’un lot déjà existant. Comme rien de permet de distinguer les bouteilles d’origine des nouvelles, elles sont traitées globalement.

Méthode : je retrouve dans la feuille MaCave, le lot correspondant et je note sa référence. Dans la feuille Entrées, je commence une nouvelle ligne en saisissant cette référence. Les caractéristiques s’affichent en clair me permettant un contrôle visuel. Je saisis ensuite les informations propres à l’entrée : date, nombre de bouteille, prix d’achat et commentaires. Je vérifie dans la feuille MaCave que le stock a été mis à jour.

4. Sortie d’une ou plusieurs bouteilles

Contexte : je sors de ma cave une ou plusieurs bouteilles pour ma consommation ou pour faire un cadeau.

Méthode : j’ai dans ma cave un petit carnet où je note une caractéristique essentielle de la ou des bouteilles sorties. Parfois je sors plusieurs bouteilles et j’en rapporte une ou deux. Ce petit carnet me permet de ne rien oublier. Ensuite, avec ce carnet, je recherche dans MaCave le lot sorti et je note sa référence. Je commence une nouvelle ligne sur la feuille Sorties en saisissant cette référence. Les caractéristiques du lot s’affichent pour contrôle. Je saisis alors le informations propres à la sortie : date, nombre de bouteilles, occasion, convives et appréciation du vin. Je vérifie dans MaCave que le stock a été mis à jour. Il me reste à mettre à jour les emplacements.

LOC – 14/08/2018 – 2

Présentation

Cette fiche détaille comment la gestion d’une cave domestique peut être analysée en vue d’une solution avec un tableur.

1. Le contexte

Il s’agit d’une collection de bouteilles conservées a priori en plusieurs exemplaires dans un espace réservé pendant plusieurs années. Chaque lot ou crû est identifié par un certain nombre de caractéristiques.

Un seul espace est géré. La problématique de gérer plusieurs espaces n’est pas envisagée.

L’application doit prendre en compte un existant. Cet existant est traité comme des entrées de l’année zéro.

2. Les résultats attendus

2.1 Liste des bouteilles à jour en permanence

L’application doit donner la liste des bouteilles et le nombre en cave pour chaque crû. Pour chaque lot, les caractéristiques disponibles à la consultation sont :

• la région ou le crû : pour permettre des regroupements cohérents, cette information doit être choisie dans une liste extensible ;
• la couleur ou le type : rouge, rosé, pétillant. Cette information doit être choisie dans une liste extensible ;
• le millésime : information importante, mais éventuellement absente ;
• la désignation : nom donné au vin par son producteur et indiqué sur l’étiquette ; parfois absente ;
• le producteur : normalement indispensable, mais parfois inconnu ;
• la qualité : appréciation avec un nombre d’étoiles (1 = médiocre, 5 = excellent) donnée par les experts ;
• Années de développement : à boire, maturité, apogée ;

Pour chaque lot, le stock doit être calculé automatiquement en fonction des entrées et des sorties.

Enfin, pour chaque lot, les emplacements doivent pouvoir être indiqués en vue de trouver une bouteille facilement.

Le tableur, avec ses tris sur n’importe quelle colonne ou les filtres, doit permettre de trouver un crû parmi d’autres très facilement.

2.2 Suivi des entrées et des sorties

L’entrée d’un lot comme la sortie d’une ou plusieurs bouteilles d’un lot doivent pouvoir être enregistrées pour être suivies dans le temps.

3. Les données

Les données sont les informations entrées dans le système en vue d’obtenir les résultats souhaités. Les données se répartissent en deux grandes catégories : les informations permanentes et les informations historiques.

3.1 Informations permanentes

Il s’agit des bouteilles présentes en cave, avec leurs caractéristiques détaillées ci-dessus. Toutes ces informations sont disponibles sur l’étiquette de la bouteille au moment de l’entrée d’un lot.

Par souci de simplicité, ces informations doivent pouvoir être saisies dans le même tableau. La liste des bouteilles peut servir de cadre.

3.2 Informations historiques

Il s’agit d’informations variables dans leur fréquence d’apparition dont une caractéristique essentielle est la date. Pour une collection comme une cave, ce sont les mouvements d’entrée et de sortie. Chaque mouvement peut concerner une ou plusieurs bouteilles. Plusieurs mouvements espacés dans le temps peuvent concerner le même lot (réapprovisionnement pour les entrées, sorties bouteille par bouteille). Chaque mouvement doit faire référence à un lot de la liste et l’information faisant le lien entre la base et les mouvements est la référence créée en même temps qu’une nouvelle ligne dans la liste.

4. Les principes

4.1 Base de données

La cave est une base de données avec le principe « une information n’est présente qu’une fois ».

Ainsi, la désignation d’un lot est entrée dans le classeur lors de la saisie d’une nouvelle ligne dans la liste. Dans les autres feuilles, Entrées et Sorties, cette désignation apparaît pour la clarté et la compréhension du mouvement. Mais ici, il s’agit d’une récupération calculée de l’information présente dans la liste.

Cette récupération calculée a plusieurs conséquences.

Dans les feuilles de mouvement, chaque mouvement doit faire référence à un lot existant dans la liste des bouteilles de la cave. Sinon, la formule de calcul renvoie une anomalie.

Si la désignation d’un lot dans la liste est modifiée, cette modification est automatiquement reportée dans les lignes de ce lot existantes dans les feuilles de mouvement.

Ce qui est possible pour la désignation l’est également pour les autres caractéristiques d’un lot.

4.2 La référence du lot : lien entre tables relationnelles

Dans les bases de données dites relationnelles, les liens entre tables se font avec une information commune qu’elles se partagent. Ce lien qu’on appelle index, est souvent un identificateur unique permettant de faire référence à un article précis.

La référence d’un lot est cet identificateur unique. Créée à chaque nouvelle ligne (article de la liste), il est saisi sur chaque ligne mouvement pour permettre de retrouver les caractéristiques du lot sans avoir à saisir autre chose.

5. Ressources

Un classeur LibreOffice peut fonctionner comme une base de données grâce à des fonctions très puissantes, et en particulier Recherchev() que nous étudierons à la fiche 5.4 MaCAve, calculs.

Informations complémentaires

Liens

5.4 MaCAve, calculs

LOC – 14/08/2018 – 2

Version

L’exemple MaCave-exemple a été mis au point avec la version 6.0.6 de LibreOffice.

Présentation

J’ai téléchargé l’exemple comme indiqué à la fiche 5. Exemple : Ma cave.

L’application LibreOffice est installée sur mon ordinateur et elle est opérationnelle.

J’ouvre MaCave-exemple à la première feuille MaCave.

[Alt+ Tab], raccourci bien pratique, permet de passer d’une application (le site loc.geneses.fr) à une autre (le classeur MaCave-exemple).

1. Vue d’ensemble

1.1 Feuille MaCave

Description

Une ligne pour chaque crû, dont les différentes colonnes nous permettent de connaître les principales caractéristiques: référence, région-crû, couleur-type, millésime, désignation, producteur, qualité, disponibilité (à boire, maturité, apogée), stock et emplacement.

La colonne Réf. montre des données sur fond vert ou fond rouge. Les lignes commençant avec un fond rouge correspondent à des crûs épuisés (stock nul).

Les lignes paires apparaissent avec un fond légèrement coloré permettant une meilleure lisibilité des lignes.

La première ligne reste en place quand on déroule le tableau verticalement. De même, quand on déroule le tableau horizontalement, les quatre premières colonnes restent en place pour pouvoir identifier les crûs. Les rangées figées apparaissent suivies d’une ligne plus prononcée sur la grille.

Certaines colonnes ont leur titre sur un fond coloré bleu. Ce fond coloré indique un bouton qui trie automatiquement le tableau : Ref. par référence, Région Crû par Région/Crû et Millésime, Début, Maturité, Apogée par cette qualité, Région Crû et Millésime. Voilà une application pratique des macros (4.6 Les macros).

Petits exercices de manipulation :

[CTrl+ F8] : ce raccourci met en évidence les valeurs. Il agit en bascule. Quand les valeurs sont mises en évidence, les constantes de type Texte restent en noir, les constantes numériques sont en bleu, les formules (stock) en vert.

Autre exercice :

Les informations de la colonne Stock sont remplacées par ### signifiant que la colonne est trop étroite pour afficher les nouvelles informations (les formules en clair). Si je clique dans une de ces cellules, sa formule se révèle dans la zone d’édition Formule. C’est la seule colonne de cette feuille portant des formules. Je remets les choses en ordre avant de passer à la feuille suivante.

1.2 Entrées

Cette feuille enregistre toutes les entrées de bouteilles dans ma cave, en précisant la date d’entrée, le nombre, le prix d’achat et des commentaires.

Si je mets en évidence les valeurs ([Ctrl+ F8], je constate que seules les colonnes Réf. et Commentaires sont du texte saisi. La Date d’entrée, le Nombre de bouteilles et le Prix d’achat sont des informations numériques saisies. Toutes les autres colonnes sont des formules : elles permettent de récupérer les informations des crûs directement à partir de la feuille MaCave.

Deux colonnes ont leur titre sur un fond coloré vert (Réf. et Date). Ce sont des boutons de tri comparables à ceux de la première feuille.

Petit exercice :

Je choisis une réf. existante quelconque (la dernière du tableau fera l’affaire).

Dans la feuille MaCave je note le stock de cette référence.

Je reviens à la feuille Entrées et je saisis à la première ligne disponible la référence notée. Toutes les informations du crû de cette référence s’affichent. Il me reste à saisir les informations propres à l’entrée : la Date, le nombre de bouteilles (6 par exemple), le prix d’achat et les commentaires.

Je reviens à la feuille MaCave et je constate que le stock de la référence a augmenté de 6 bouteilles automatiquement.

1.3 Sorties

Cette feuille est analogue à la feuille Entrées mais pour des mouvements inverses. Les informations relatives à une sortie sont : la référence, la date, le nombre, l’occasion, les convives et l’appréciation du vin.

1.4 Libellés

Comprend deux tables correspondant aux listes de choix Région-Crû et Couleur-type.

2. Notions clés

2.1 Le crû

Dans cette application, j’appelle crû, un vin identifié par sa désignation, son producteur et son millésime.

Ce crû peut rentrer en cave en une ou plusieurs fois (réapprovisionnements à l’identique). En fait, rien ne permet de distinguer, dans la cave, des bouteilles par leur entrée, bien que les circonstances (achat ou cadeau), le fournisseur et le prix peuvent varier.

Un crû occupe une ligne, et une seule, dans la première feuille MaCave. En revanche, ce crû peut occuper plusieurs lignes Entrées, et Sorties bien entendu.

Si deux crûs ne diffèrent que par leur millésime par exemple, ils seront présents sur deux lignes distinctes, une par millésime.

2.2 La référence

Chaque crû est identifié par une référence, saisie en début de ligne de toutes les feuilles.

J’ai choisi un système qui vaut ce qu’il vaut : une lettre et un numéro d’ordre.

La lettre A désigne les crûs présents dans ma cave quand j’ai mis en place l’application en 2016. La lettre B désigne les crûs entrés en 2017, la lettre C, en 2018 etc. Le numéro d’ordre permet d’éliminer les références homonymes, sans autres prétentions.

La référence est créée à l’entrée d’un nouveau crû.

Posée en début de ligne des feuilles Entrées et Sorties, elle permet de faire le lien entre les informations de MaCave et celles des feuilles de mouvement.

2.3 Stock et mouvement

La feuille MaCave sert aussi de feuille de stock. Elle est statique par opposition aux feuilles Entrées et sorties que j’appelle aussi Mouvements. Celles-ci sont modifiées chaque fois qu’un mouvement de bouteille affecte le stock.

3. Articulations

À l’entrée d’un nouveau crû, une nouvelle ligne est créée dans MaCave. Cette opération permet d’enregistrer le maximum d’informations sur le crû.

Ensuite, une nouvelle ligne est créée sur la feuille Entrées. En saisissant en première colonne Réf. la référence du crû enregistré dans la feuille MaCave, les informations du crû sont affichées sur cette nouvelle ligne. Il reste seulement à saisir les informations propres à l’entrée : date, nombre de bouteilles, prix d’achat et commentaires. Cette saisie met le stock du crû à jour sur la feuille MaCave.

À chaque sortie, il suffit de créer une nouvelle ligne sur la feuille Sorties. En tapant la référence du crû, les informations correspondantes sont affichées sur la ligne. Il suffit de saisir les informations propres à la sortie: date, nombre de bouteilles, circonstances, appréciations.

La Référence du crû est donc l’information pivot entre toutes les feuilles. Les différents tris possibles, la fonction recherche dans la feuille MaCave permet de trouver rapidement le crû concerné par un mouvement et donc sa référence pour saisir ce mouvement.

Sur les feuilles mouvement, la saisie d’une référence inconnue génère des messages d’anomalie à la place des informations sur le crû. Il suffit de saisir une référence correcte pour que tout rentre dans l’ordre.

Informations complémentaires

Liens

2.3 Manipuler le contenu des cellules, § 5.2 Suppression du contenu de cellules active ou sélectionnées

4.6 Les macros

5. Exemple : Ma cave

LOC – 07/05/2018 – 2

Plan de la fiche

1. Téléchargement

2. Gérer les fichiers d’exercices ou d’exemples

Présentation

Cette fiche explique comment télécharger les exercices ou les exemples et comment les gérer en local.

Les exercices sont des tableaux imageant l’enseignement des fiches. Je propose en général un classeur par fiche, avec une feuille par paragraphe.

L’exemple est une applications concrète, MaCave avec son classeur MaCave-exemple qui permet de découvrir l’application avec des données réelles. Tout est modifiable. Au besoin, l’application peut être rechargée autant que nécessaire.

1. Téléchargement

Chaque exercice ou exemple est téléchargeable avec le lien indiqué.

Plusieurs démarches sont possibles.

Je préconise d’ouvrir sur son ordinateur un dossier, par exemple :  Classeurs_LOC ; et dans ce dossier, un sous-dossier par exercice ou exemple. Ces sous-dossiers permettent de créer et conserver des fichiers personnels.

En actionnant le lien, j’obtiens une boîte de dialogue qui me demande d’enregistrer le fichier ou de l’ouvrir.

• Avec Enregistrer, le fichier est enregistré dans mon dossier Téléchargements (paramètres du navigateur). Charge à moi, ensuite, de le transférer dans le dossier Claseurs_LOC.
• Avec Ouvrir, et si LibreOffice est associé à mon navigateur, le fichier est directement ouvert ; seulement, il est en lecture seule. Il me faut l’enregistrer dans son dossier et l’ouvrir à nouveau pour le trouver modifiable. Cette approche est recommandée.

2. Gérer les fichiers d’exercices ou d’exemples

Le nombre de téléchargement n’est pas limité. Ceci dit, il est plus simple de travailler en autonome.

Je préconise donc de renommer tous les fichiers téléchargés avec le suffixe “-0”, indiquant par là qu’il s’agit du fichier d’origine. Ensuite, je ne travaille que sur des copies que je suffixe suivant les besoins “-1”, “-2” etc. Pour retrouver la version d’origine, j’ouvre le fichier “-0”.

LOC – 12/06/2018 – 2

Présentation

Pour conclure cette première partie, voici une approche qui pourra favoriser votre travail d’architecte des tableaux de calcul. Un tableau sert nécessairement à saisir des informations. Il contient bien entendu des calculs. Et il présente le résultat de ces calculs. Un bon architecte de tableaux doit respecter ces trois contraintes.

Dans un tableau simple, ces trois fonctions ne sont pas distinctes. Une cellule va permettre de rentrer une information, la cellule suivante affichera le résultat d’un calcul, et la présentation sera négligée.

Dans un tableau destiné à documenter un rapport, saisies et calculs ne seront pas dissociés. En revanche la présentation sera plus soignée : équilibre des colonnes, couleurs, mises en forme, etc.

Dans des applications, comme l’exemple MaCave que je présente en détails plus loin, ces trois fonctions sont bien différenciées. C’est nécessaire pour organiser les opérations et les traitements. Une feuille de saisie est construite pour favoriser et sécuriser au maximum l’entrée des données : formats distincts pour les rubriques et les données saisies, contrôles de cohérence, messages d’anomalies. Une feuille de présentation ne comporte que des formules récupérant les données saisies ou les résultats des calculs. C’est un tableau construit pour être lisible le plus facilement possible. Enfin, des feuilles de calculs intermédiaires permettent de décortiquer les calculs par étape. Cela facilite la mise au point, la recherche d’anomalies, les contrôles de vraisemblance et les évolutions.

1. Saisie

La fonction Saisie regroupe les opérations permettant d’entrer des données dans le classeur, c’est-à-dire des informations venant de l’extérieur. Par extension, elle rassemble aussi les traitements associés à la saisie (gestion des listes de libellés obligatoires, contrôle de cohérence) et plus généralement, la conception et la mise au point de feuilles ou zones spécifiques à l’action de saisir des données.

Une feuille de saisie est organisée de telle sorte que l’enchaînement des données à saisir correspond à la logique de présentation de ces informations. Une information se présente rarement isolée pour être saisie. Elle fait partie d’un ensemble, d’une suite d’informations.

La feuille de saisie présente cette suite en respectant les intitulés de données pour que l’opérateur de saisie soit guidé au mieux. Je préconise une information à saisir par ligne. La première colonne donne l’intitulé, la deuxième est la zone de saisie, la troisième apporte des précisions : valeurs interdites, domaine de définition, etc. Éventuellement, la 4e donne un résultat de cohérence ou un message d’anomalie.

Il ne faut pas craindre de visualiser les documents d’origine avec des pavés distincts portant en titre sur 3 ou 4 colonnes, le titre du support d’origine. Je préconise de réserver une couleur de fond spécifique pour les cellules à saisir. Quand la matrice est au point, il peut être judicieux de protéger les cellules non accessibles en saisie : le saut d’une cellule à l’autre avec [Tab] n’accèdera qu’aux cellules à saisir. Gain de temps et de sécurité.

Quand dans un tableau, des paramètres, appelés encore chiffres clés ou données initiales, sont saisis, il est impératif qu’ils soient regroupés au mieux dans une zone spécifique ou pavé.Si l’ensemble est conséquent, il est préférable d’affecter une feuille à ces saisies.

Si le tableau est une liste de ligne, il sera utile de prévoir un tramage des lignes sur deux couleurs pour faciliter le repérage à la relecture. Dans ce cas, le coloriage de la ligne d’en-tête sera étudié. J’ai l’habitude de proposer cette ligne en négatif : fond sombre et caractères clairs. Les colonnes à saisir seront d’une couleur, celles calculées d’une autre couleur. L’intérêt est de favoriser la compréhension intuitive du cadre pour celui qui saisit.

2. Les calculs

Il ne faut pas craindre de construire des feuilles pour les calculs intermédiaires. Ces feuilles n’ont pas besoin d’une mise en forme particulière. Les formules complexes peuvent s’y développer sans gêner le travail de saisie ou la clarté de la présentation des résultats. Il y est plus facile de chercher les anomalies.

Néanmoins, je soigne la présentation des en-têtes de colonnes et des lignes de totalisation en leur affectant le même format. Par ailleurs, je compose l’intitulé des en-têtes pour faire apparaître clairement les hypothèses retenues dans les calculs affichés.

3. Les présentations

À proprement parler, les feuilles de présentation ne contiennent aucune zone saisie, ni aucun calcul. En revanche, la quasi totalité des cellules sont des formules qui appellent le contenu de cellules ailleurs dans le classeur, notamment les calculs. J’apprécie aussi, dans les listes quelles qu’elles soient, de partir d’une unique liste de références et par le jeu des zones nommées, d’utiliser ensuite les formules pour l’afficher dans d’autres feuilles (Exemple : Crûs dans MaCave).

Un équilibre est à trouver entre l’affichage et l’impression. L’aperçu permet de formater facilement la page imprimé. Encore faut-il que la feuille à imprimer puisse l’être sans trop de contraintes. En fait, avant d’entrer dans les détails de présentation de la page affichée, il faut dans un aller et retour fréquent, voir le résultat dans l’aperçu pour l’impression. Il faut penser l’impression au moment de dessiner une feuille de présentation.

La charte des couleurs est importante. Elle se traduit essentiellement par la couleur de fond des cellules, mais aussi par celle des bordures. La police de caractères a son importance. Personnellement, je mets par écrit ces caractéristiques pour en garder la trace et les reproduire facilement.

Le cadre de la page peut être allégé pour les feuilles de présentation : plein écran, absence des bordées de références colonnes ou lignes. Par ailleurs, les feuilles de présentation doivent être protégées par précaution. Il sera impératif de fixer des lignes d’en-tête et éventuellement des colonnes têtes de lignes.

Tous ces détails montrent l’intérêt de dissocier saisie, calculs et présentations. Pour une meilleure efficacité dans chaque domaine, et une plus grande sécurité.

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

3.1 Applications concrètes

3.2 Précisions

Intégration d’éléments venant d’autres feuilles

Les insertions faussent le jeu normal

Notation absolu dans les séquences de formules

Zones nommées

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.

– 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

1.5 Gérer les zones nommées

LOC 27/03/2018 – 1

Présentation

Après la présentation générale de la fiche précédente, 1.1 Le classeur LOC : création, édition, sauvegarde et rappel, nous rentrons maintenant dans les détails.

1. Le classeur

Le classeur est un fichier particulier, géré par le module Calc de LibreOffice. Il est reconnaissable à son extension .ods.

Comme tout fichier, il est gérable avec l’explorateur de fichiers du système d’exploitation : pour le copier, le déplacer, le renommer ou le supprimer. Ce fichier peut être protégé en lecture seule, ce qui élimine le risque de le modifier par inadvertance. C’est ainsi que tous les Travaux pratiques téléchargés le sont en lecture seule. Il suffit que j’enregistre le fichier sur mon disque pour qu’il retrouve tous ses droits.

Un certain nombre de paramètres sont définis au niveau de classeur. Ils s’appliquent par défaut aux nouvelles fenêtres. Mais plusieurs d’entre eux peuvent être modifiés au niveau d’une feuille particulière sans que ces modifications n’affectent les autres feuilles. Cette possibilité peut générer une certaine confusion que je lève en deux temps : 1. Présentation des paramètres par défaut, ci-dessous, et 2. Paramétrages particuliers des feuilles, ci-après au §2. La feuille de calcul.

1.1 Paramètres généraux de l’application

Ces paramètres jouent sur deux niveaux : définition des paramètres effectifs sur tout le contexte du classeur, et proposition de paramétrage par défaut pour des paramétrages spécifiques au niveau du classeur ou de la feuille.

Ces paramètres sont accessibles ici :

1.11 Paramètres effectifs sur tout le classeur

Les valeurs proposées par défaut sont acceptables. Sauf cas très particulier, elles n’ont pas à être modifiées.

J’aurais préféré comme intitulé : Nouvelle cellule sélectionnée quand je valide la saisie d’un cellule avec [Entrée]. 

Par exemple, quand je saisis une liste verticale, je souhaite passer à la cellule du bas. En revanche, quand je saisis un fichier, avec une ligne par fiche, je souhaite passer à la cellule de droite. Il est dommage que cette option ne soit pas modifiable à partir de la feuille de calcul. Car suivant les saisies, la direction devrait pouvoir être modifiée plus facilement.

2. La feuille de calcul

3. Les colonnes

4. Les lignes

5. La cellule

La cellule est une zone à la croisée d’une ligne et d’un colonne qui reçoit l’information, et qui est caractérisée par des méta-informations (formatage, appartenance à des ensembles, etc.)

5.1 Préliminaires

5.11 Présentation

La cellule peut être active : elle se repère avec un cadre épais, et un petit carré en bas à droite sur lequel le curseur de la souris se transforme en croix.

La cellule peut être sélectionnée : elle se repère avec un fond de cellule coloré différemment de la feuille.

5.12 Référencement

Une cellule est nécessairement référencée par sa ligne et sa colonne, et accessoirement la feuille du classeur. Le système de référencement est précisé dans les options. Généralement, on utilise [Lettre colonne, numéro de ligne] ; exemple : « A1 ».

Dans les formules, les deux composantes d’une cellule (colonne et ligne) peuvent être notées simplement « A1 » (référence flottante) ou en absolu « $A$1 », ou avec les combinaisons possibles : « $A1 », « A$1 ». L’importance et l’utilité de ces notations sont expliquées dans la fiche 1.5 Les formules.

Une cellule, et surtout un groupe de cellules adjacentes, peuvent être référencés par un nom de plage. Ce référencement se fait ici :

Je considère que les noms de plages sont incontournables dans les applications, en particulier quand des listes importantes sont susceptibles d’être saisies et traitées. Nous y revendrons en détail dans les cas concrets Ma cave et Trésorerie domestique.

5.12 Activer une cellule et sélectionner un groupe de cellules

J’active une cellule en cliquant dedans.

Pour sélectionner un groupe de cellules, je clique dans une cellule qui sera la cellule active en fin de sélection, et sans lâcher le bouton, je dirige la souris vers la fin de la sélection, vers le bas pour une partie de colonne, vers la droite pour une partie de la ligne ou en diagonale pour sélectionner des cellules sur plusieurs lignes ou colonnes.

Je peux aussi sélectionner un groupe de cellules en ▼ dans la première du groupe (qui devient la cellule active) puis [Maj+ ▼] dans la dernière (ligne, colonne ou diagonale).

Je peux aussi sélectionner des cellules ou groupes de cellules non adjacentes. Je commence par sélectionner un groupe de cellules comme ci-dessus. Puis avec [Ctrl+] je refais une nouvelle sélection. La première sélection reste en place. Je peux répéter de nouvelles sélections de la même manière. Pratique pour formater des cellules éparses dans un tableau de paramètres, par exemple.

5.13 Déplacer une cellule ou un groupe de cellules

L’opération se fait en deux temps : 1. Activer la cellule ou sélectionner le groupe de cellules à déplacer ; 2. Cliquer dans la cellule ou le groupe et sans lâcher le bouton, le faire glisser vers sa destination. La cellule ou le groupe sont matérialisés par une bordure plus épaisse. La cellule qui était active avant le déplacement, le reste après.

5.14 Copier le contenu d’une cellule dans une autre

Une cellule est activée. Je veux la copier dans une autre cellule.

[Ctrl+ C] (ou dans Menu, ▼ Édition ▼ Copier). La cellule avec son information et ses méta-informations (format) est copiée dans le presse-papier. J’active la cellule de réception et [Ctrl+ V] (Coller).

Pour un groupe de cellules, l’opération est analogue : je sélectionne le groupe, je copie, je sélectionne la future cellule active du groupe et je colle.

Si les options le prévoient et si la ou les cellules de destination ne sont pas vides, une alerte me propose d’abandonner ou de continuer.

5.15 Étendre une cellule ou un groupe de cellules

Cette extension est une copie particulière. Je pars d’une cellule active (ou d’un groupe de cellules). Je clique sur le carré noir en bas à droite de la cellule ou du groupe et sans lâcher le bouton, je le déplace vers le bas ou vers la droite. La cellule (ou le groupe) est répétée avec la même information et le même format.

5.2 Cellule et information

La cellule peut contenir plusieurs types d’information dont certains sont associés à un format particulier.

Le paramétrage se fait :

Le nom de l’onglet est mal choisi ; j’aurais préféré Type d’information.

Nombre, Pourcentage, Monnaie : ce type autorise des nombres entiers ou décimaux.

Pourcentage transforme le nombre saisi en le divisant par 10 et l’affiche suivi de « % ». Exemple : saisie : {20}, enregistré (0.20), affiché : 20%.

Monnaie associe un format monétaire : le nombre est suivi du symbole de la monnaie.

Le format est à choisir dans la liste. Le nombre de décimales et le séparateur des milliers est à préciser. Reste de temps anciens : les négatifs peuvent être affichés en rouge. Intéressant parfois, le nombre de zéros non significatifs (en début de nombre) peut être défini. Un encadré montre le résultat des choix.

Date et heure : ce sont des nombres entiers qui calculent automatiquement la date du jour à partir d’un point de départ précisé dans les options. Le format est à choisir dans la liste, ou à composer selon sa préférence en utilisant la syntaxe qui se devine dans les exemples. Je note qu’en dehors des lettres codes (J, M, A, H, M et S) tous les autres caractères posés dans le format seront affichés tels quels (y compris les espaces).

Si je change mon option de point de départ des dates (Options Calcul), toutes les dates déjà saisies dans mon classeur seront modifiées.

Scientifique : notation classique pour des nombres importants.

Fraction : un nombre entier suivi d’une espace est complété par une fraction : le dénominateur indique le nombre de décimales (le numérateur) à ajouter. Le résultat est éventuellement arrondi sur la dernière décimale. Ce mode est plus une aide à la saisie et à la lecture.

Valeur logique : en clair Vrai ou Faux. En fait, une cellule vide est toujours considérée comme fausse. Mais c’est un peu plus compliqué et c’est expliqué ici : 3. Calculs.

Texte : la cellule est destinée à recevoir n’importe quoi, y compris des nombres.

À l’ouverture d’une feuille, toutes les cellules par défaut sont typées nombre. Cela se comprend dans un programme sensé faire des calculs. Cela permet aussi de saisir des formules qui sont interprétées comme telles. Car si la cellule est typée Texte une formule saisie ne sera pas interprétée comme telle mais comme du texte. J’y reviens dans 1.5 Les Formules.

5.2 Cellule et formatage

La même fenêtre permet de préciser d’autres éléments du format de la cellule :

Cet onglet permet de préciser :

• la police avec laquelle l’information de la cellule sera affichée et imprimée,
• le style (normal, gras ou italique, suivant les polices),
• la taille des caractères (corps)
• la langue (pour le contrôle orthographique).

Cet onglet complète le précédent :

• Couleur des caractères,
• Surlignage, forme, épaisseur et couleur du trait
• Barré, idem pour le trait,
• Soulignage, idem pour le trait,
• Relief et forme.

Cet onglet propose des fonctionnalités à connaitre.

D’abord il permet de préciser le cadrage horizontal et vertical de l’information à l’intérieur de la cellule. Ces réglages sont accessibles directement dans des bordées de boutons.

Ensuite il permet de faire pivoter le texte, normalement horizontal, jusqu’à la vertical dans un sens ou un autre.

Pour les cellules de type Texte, il est possible de préciser si le texte passe à la ligne automatiquement avec coupure éventuelle des mots. Inversement, il est possible de préciser si le texte doit être adapté à la largeur de la cellule : dans ce cas, la taille des caractères est réduite parfois  à la limite de la lisibilité. Si le texte n’est pas adapté à la largeur de la cellule, et s’il dépasse le bord droit, il s’affiche sur les cellules suivantes tant que celles-ci sont vides ; sinon, la partie qui dépasse n’est pas perdue, mais elle n’est pas visible.

Je reviens en détail sur ces caractéristiques dans 4.1 Les formats.

J’aborde ce sujet dans les saisies.