3.3 Composer une formule

LOC – 13/05/2018 – 1

Plan de la fiche

1. Comment Calc exploite les formules

2. Syntaxe de base

2.1 Les opérandes

Constantes

Variables

Fonctions

Expressions

2.2 Les opérateurs

2.3 Priorité des opérateurs

3. Rédaction d’une formule

3.1 Automatiser les références de cellules

3.2 Copier/Coller de cellules avec formules

3.3 Utiliser les caractères génériques ou les expressions régulières

4. Recours à l’aide

5. Messages d’erreur

 

Présentation

Une formule est une suite de termes exploitée par le système pour fournir à la cellule qui la contient une valeur calculée. La puissance d’un tableur vient de la variété des calculs. Au-delà des classiques opérations arithmétiques, les formules permettent de manipuler du texte, des dates, et d’associer des tests utilisant les fonctionnalités des ensembles (ET, OU inclusif, OU exclusif, NON, etc.). Les fonctions de recherche et d’extraction dans les bases de données sont particulièrement performantes.

Cette fiche décrit comment rédiger une formule.

1. Comment Calc exploite les formules

La formule est une suite de mots ou de caractères isolés agencés suivant une syntaxe précise.

Dès que la saisie de la formule est validée avec [Entrée], un module du système appelé compilateur traduit la formule dans un langage interne. Si une erreur est détectée à ce niveau, le compilateur renvoie un message d’erreur car la formule ne peut donner aucun résultat. Si cette cellule est utilisée dans d’autres formules, celles-ci renvoient, elles aussi, le même message d’erreur. Il suffit de corriger l’anomalie initiale pour que tout rentre dans l’ordre.

Dans un deuxième temps, la formule traduite par le compilateur est interprétée, c’est à dire calculée. Mais, le plus important, c’est que cette phase de calcul est étendue à toutes les formules utilisant cette cellule. Cela signifie que si la cellule dont je valide la formule est un opérande d’une autre formule du classeur, cette dernière sera recalculée à la validation. Et il en sera ainsi pour toutes les formules concernées par cette cellule.

Le calcul automatique est un choix au niveau du tableau (Données, Calculer, Calculer automatiquement). Si le tableau est volumineux, ce calcul peut prendre du temps et il peut être préférable de le débrayer ; voir ici : 3.1 Généralités, §3.1 Calculs automatiques ou à la demande.

2. Syntaxe de base

Une formule commence par le caractère “=” et sa saisie se termine par [Entrée] (validation).

Elle comprend un ou plusieurs opérandes séparés par des opérateurs.

Il est possible aussi de créer à l’intérieur d’une formule, des expressions entre parenthèses. Une expression est comme une sous-formule.

2.1 Opérandes

Les constantes

Les nombres sont enregistrés normalement, avec le signe “-” devant pour les nombres négatifs. Le séparateur décimal dépend de la langue (en Français de France, c’est la virgule, tapée avec le point du clavier numérique). La notation scientifique est possible.

Les dates sont enregistrées avec leur format, généralement JJ/MM/AAAA. Le format accepte aussi des combinaisons avec N ou J (D en anglais) : NNNN indique le jour en clair (Lundi), NN le jour en abrégé (Lun.), J le quantième sur un ou deux chiffres, JJ le quantième sur 2 chiffres, et idem pour le mois avec M, MM, MMM et MMMM .

Les textes sont enregistrés entre guillemets doubles (“…”).

Les valeurs logiques avec leur valeur : VRAI  (1) ou FAUX (0).

Les variables

Ce sont les références des cellules, en notation Colonne-Ligne, exemple A2 ; en notation relative, absolue ou panachée ($A2, A$2, $A$2). Voir : 3.2 Le référencement des cellules, § 2 Référence relative ou absolue.

Dans certains cas, les zones sont acceptées (références des cellules extrêmes séparées par “:”).

Ce peut être aussi des zones nommées. Voir : 1.5 Gérer les zones nommées.

Les fonctions

Il existe un nombre important de fonctions par type de calcul. Une fonction comprend son intitulé suivi d’une liste de paramètres (parfois vide) entre parenthèses.

Les paramètres de la liste sont séparés par des  “;”. Le nombre de paramètres peut être variable, mais toujours limité (à 30 dans Somme(), par exemple). S’ils peuvent être généralement n’importe quel opérande (constante, variable, fonction, expression), leur type de donnée et leur type d’opérande peut être imposé par la fonction ou leur place dans la liste des paramètres. Le non-respect de cette contrainte génère une erreur de code #504.

Les expressions

Une expression est une partie de formule entre parenthèses.

2.2 Les opérateurs

Calculs : les quatre opérations de base : +, , /, *, et ^(puissance). Le signe moins posé devant une expression (variable, fonction) change le signe de ce qui suit.

Comparaisons : <, >, =, <=, >=, <> (sans espace intermédiaire pour les caractères doubles).

Concaténation de texte : & ; exemple : =“Same” & “di” = “Samedi”

Plage de cellules : “:” pour séparer les cellules extrêmes en diagonale de la plage (du haut à gauche vers le bas à droite). “!” : pour l’intersection de plages, cellules communes aux plages (opérateur ET d’ensemble). “~” : pour la concaténation de plages, l’union des deux plages (cellules des deux plages, celles étant à l’intersection n’étant prises qu’une fois – opérateur OU d’ensembles).

2.3 Priorité des opérateurs

Une formule est interprétée en commençant par la gauche. Néanmoins, tous les opérateurs n’ont pas la même priorité.

Les expressions entre parenthèses sont exploitées en premier, et quand plusieurs expressions sont emboîtées, la plus intérieure est exploitée la première.

Tous les opérateurs de comparaison ont la même propriété.

Dans les opérateurs numériques, l’élévation à la puissance a la priorité 1 ; la multiplication et la division ont la priorité 2 ; l’addition et la soustraction, la priorité 3.

Concrètement, l’interpréteur lit la formule en commençant par la gauche. S’il trouve une parenthèse ouverte, il isole le contenu jusqu’à la parenthèse fermante, répétant cette opération si plusieurs parenthèses sont imbriquées. L’expression qui en résulte (sans parenthèses) est interprétée en isolant les opérandes séparés par des opérateurs de comparaisons. Chaque terme est ensuite analysé en calculant les expressions mathématiques dans l’ordre de priorité des opérateurs (puissance, multiplication et division, addition et soustraction). La pile des blocs est remontée intégrant à chaque niveau le résultat calculé. En fin de pile, la formule libère son résultat.

3. Rédaction d’une formule

Je rédige une formule comme du texte. Voir ici : 2.2 Saisir l’information, § 1. Saisir l’information.

Les espaces sont facultatifs lors de l’enregistrement pour faciliter la lecture (sauf dans les textes ou les références, évidemment).

Les opérandes peuvent être tapés en majuscules ou minuscules. Mais je conseille de taper les références de cellules, les zones nommées et les fonctions en minuscules.

Les parenthèses permettent de regrouper des parties de formule complexe facilitant leur lecture. En mode édition, quand le point d’insertion est proche d’une parenthèse, elle est mise en gras avec son homologue. C’est bien pratique.

3.1 Automatiser les références de cellules

En cours de saisie d’une formule, au lieu de taper la référence d’une cellule, il est possible de cliquer dans cette cellule 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 (d’un opérateur ou 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 pour des plages de cellules sélectionnées : les références extrêmes en diagonale sont insérées dans la formule séparées par « : ».

Cette insertion joue aussi quand il faut saisir la référence d’une cellule présente sur une autre feuille. Laissant le point d’insertion à l’endroit de l’insertion, il suffit d’afficher la feuille (en cliquant sur son onglet), de cliquer sur la cellule.Je continue la saisie comme si je n’avais pas changé de feuille. La feuille initiale s’affiche à nouveau et le point d’insertion est en attente dans la zone d’éditon Formule pour poursuivre la saisie ou valider la formule.

Voir aussi : 3.2 Le référencement des cellules.

3.2 Copier/coller de cellules avec formules

Voir ici : 3.2 Le référencement des cellules, §3.1 Applications concrètes pour comprendre la transformation des formules de cellules copiées et le rôle des références absolues ou relatives.

Il est possible aussi de copier des parties de formules.Nous sommes là dans une problématique de traitement de texte classique.

Pour rédiger une formule complexe, je préconise de procéder par étape. Une partie de la formule est composée dans une cellule à part. Son résultat est vérifié. Et ensuite, cette partie est intégrée par copier-coller dans la formule définitive, idéalement entre parenthèses.

3.3 Utiliser les caractères génériques ou les expressions régulières

3.31 Les caractères génériques

Un caractère générique (*, ? et ~) peut être utilisé dans une constante de type texte pour remplacer une suite de caractères quelconques (*) ou un seul caractère quelconque (?). « Cr?me » trouvera par exemple crime, crème, etc. « Cr* » trouvera cri, croix, etc. Le caractère tilde (~) est posé devant * ou ? quand ces deux caractères font partie de la recherche et doivent être pris comme tels et non comme caractère générique. (Pour taper le tilde : [Alt Gr+ 2 (au clavier alphabétique)] et [Espace]).

Les caractères génériques sont susceptibles d’être utilisés dans les fonctions (Recherche(), Equiv(), Somme.si() Somme.si.ens(), etc. En fait, ils sont possibles uniquement par compatibilité avec le tableur Excel. Et leur utilisation est conditionnée à un paramétrage précis :

Dans Menu : ▼ Outils, Options, LibreOffice Calc, Calcul; ‡ Calculs généraux ‡;

Autoriser les caractères génériques dans les formules.

Cette fonctionnalité est donc restrictive et exclusive : elle interdit l’utilisation des expressions régulières, plus générales et plus puissantes, mais nécessitant peut-être un apprentissage moins intuitif.

Le terme caractère générique est souvent utilisé dans l’Aide de Calc pour parler des codes spécifiques aux expressions régulières. C’est une confusion regrettable. Dans ce site, je distingue bien caractères génériques objet de ce paragraphe, et les codes spécifiques des expressions régulières objet du paragraphe suivant.

3.32 Expressions régulières

Une expression régulière est une expression de type constante utilisant un ou plusieurs codes spécifiques. Le code spécifique joue le rôle d’un joker spécialisé dans le remplacement d’un ou de plusieurs caractères quelconques en fonction de leur position.

La reconnaissance des expressions régulières dans les constantes de type Texte est conditionnée par le paramétrage système défini ici :

Dans Menu : ▼ Outils, Options, LibreOffice Calc, Calcul; Calculs généraux;

Autoriser les expressions régulières dans les formules.

La liste des codes spécifiques avec leur rôle respectif est dans l’aide à la fiche, accessible dans l’index, Expressions régulières; liste de. Quand le paramétrage est posé, toutes les formules acceptant des constantes de type Texte acceptent les expressions régulières. Leur utilisation est un peu déroutante, mais leur puissance les fait adopter sans hésitation..

4. Recours à l’aide de Calc

L’aide Calc est utile, en particulier dans la compréhension des fonctions. En cours de saisie, il est possible d’utiliser l’assistant fonctions. Il s’active avec ▼ sur le bouton Fx [Assistant Fonctions] devant la zone d’édition Formule. Dans la fenêtre qui s’affiche, il est possible de composer la formule par étape. En bas de fenêtre, ◄ Aide ► renvoie directement à l’aide concernant la fonction sélectionnée dans l’assistant.

Dans l’aide (Dans Menu : ▼ Aide, Aide de LibreOffice [F1]), les fonctions sont directement accessibles à partir de l’index. Bien vérifier auparavant que l’aide sélectionnée dans le premier combo, en haut à gauche concerne LibreOffice Calc. En effet, les mêmes fonctions existent pour Calc et pour Base (l’application de Bases de données), et leur fonctionnement est parfois différents. Et taper le nom de la fonction dans la zone Index.

5. Messages d’erreurs et solutions

### est affiché quand le résultat de la formule ne tient pas dans la cellule. Il suffit d’agrandir la colonne en largeur pour voir le résultat en clair.

Un formatage particulier (Dans Menu : Formatage, Cellules…, | Alignement |, ‡ Propriétés ‡, ▣ Ajuster à la taille de la cellule) permet aussi d’adapter la taille des caractères du contenu à la taille de la cellule. Mais la lisibilité est parfois problématique.

 

Les codes d’erreur sont présentés très clairement dans l’aide de LibreOffice Calc (index : Codes d’erreur; Liste). Ils sont numérotés de 500 à 532 et certains sont remplacés par un libellé abrégé :

#NUM! : calcul impossible sur un nombre en virgule flottante dont la taille est hors des limites posées par LibreOffice Calc.

#VALEUR! : la formule a trouvé un opérande de type Texte  alors qu’elle attendait un opérande de type Nombre. Il s’agit souvent d’une faute de frappe.

#REF! : la formule attend une référence de cellule et trouve autre chose. Il peut s’agit aussi de la référence d’une feuille inexistante. Ici encore, les fautes de frappe sont souvent en cause.

#NOM! : comme #REF! mais plus étendu : plage nommée inexistante, absence d’une référence attendue, etc.

#DIV/0! : division par zéro ou fonction statistique impossible.

 

Quand une cellule affiche une erreur de formule et si cette cellule est utilisée dans les formules d’autres cellules, l’erreur est répercutée en chaîne. Ne pas s’affoler : il suffit de corriger l’erreur dans la cellule initiale pour tout rentre dans l’ordre.

Il est possible de détecter l’endroit de l’anomalie en observant la transformation de la cellule quand toutes les opérandes sont saisis en minuscules.

Les opérandes saisis en minuscules et reconnus par le compilateur sont transformés en majuscules (pour les zones nommées ou les noms de feuille sans espace, les majuscules apparaissent respectées) quand il les accepte. L’anomalie se situe donc à partir du premier opérande non transformé en partant de la gauche.

 

Certaines fonctions génèrent parfois un code erreur dont on aimerait bien se passer. Par exemple, la fonction Trouve() quand elle ne trouve rien !

Il est possible de traiter écologiquement les erreurs avec la fonction Sierreur(Fonction initiale; Fonction en cas d’erreur). La Fonction initiale est la fonction à exécuter dont on veut éliminer le message d’erreur. Elle est exécutée. Si elle ne renvoie pas de message de d’erreur, Sierreur() est terminée. Si Fonction initiale déclenche une erreur, la Fonction en cas d’erreur est exécutée à sa place.

Informations complémentaires

Liens

1.5 Gérer les zones nommées

2.2 Saisir l’information

3.1 Généralités, §3.1 Calculs automatiques ou à la demande

3.2 Le référencement des cellules

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *