LOC – 12/05/2018 – 1
Plan de la fiche
2.3 Automatiser les références de cellules
3.1 Calculs automatiques ou à la demande
3.3 Copie de cellules et transformation de formules
3.4 Valeur, précision et affichage
3.5 Cellules vides et 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 :
Dans Menu : ▼ Affichage, Afficher la formule (bascule).
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 :
Le point d’insertion dans la formule est en place pour l’insertion;
Dans Menu : ▼ Insertion, Plages ou expressions nommées…<[Coller les noms];
▼ sur le nom à coller pour le sélectionner;
▼ ◄ Coller ►.
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 :
Dans Menu : ▼ Données, Calculer, Calculer automatiquement (Bascule).
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.
J’active la cellule destinée à recevoir la somme;
▼ Le symbole Σ dans la Ligne Formule : la formule apparaît dans la zone d’édition Formule, et la zone située au-dessus ou à gauche de la cellule comportant une suite de nombres est proposée en premier et unique paramètre;
Cette zone est entourée d’un rectangle bleu foncé, avec des poignées aux angles. En passant le curseur sur une de ces poignées, il se transforme en croix et permet par glissement de modifier le périmètre de la zone. La formule est modifiée au fil des déplacements;
Quand la zone est correctement définie, il me suffit de valider la saisie de la formule : [Entrée].
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.