LOC – 11/06/2018 – 2

Plan de la fiche

1. Position du problème

2. Quelques règles de base

2.1 Respecter la séquence des documents saisis

2.2 Caractériser les cellules à saisir

2.3 Protéger la feuille sans protéger les cellules à saisir

2.4 Favoriser les pavés de saisie

3. Les tableaux de saisie

4. Gérer les hypothèses

5. Protéger les saisies

5.1 Protéger les cellules

5.2 Protéger/dé-protéger la feuille

5.3 Pratiquement, comment faire ?

5.4 Conséquences cachées des protections

Présentation

Cette fiche présente comment organiser des feuilles ou des zones de saisies. Ces conseils ou principes n’ont rien d’académique. Ils sont le fruit de mon expérience.

1. Position du problème

J’appelle saisie l’opération qui consiste à enregistrer dans une feuille de calcul des informations nouvelles qu’on appelle parfois données en ce sens qu’elles sont données au système pour être traitées.

Par sa structure, une feuille de calcul ne nécessite pas de distinguer, dans l’espace, les zones de saisies des zones de calcul ou de présentation des résultats.

À l’usage, pourtant, il est bien pratique de ne pas avoir à chercher où rentrer telle ou telle information. Dans ce but, je préconise de regrouper les zones à saisir sur une feuille spécifique ou dans des zones affectées à ce travail.

2. Quelques règles de base

2.1 Respecter la séquence des documents saisis

En clair, je vais organiser mes zones de saisie de telle sorte que je puisse saisir les informations dans l’ordre où je les lis. Si plusieurs sources sont utilisées, je matérialiserai chacune dans un pavé qui portera le nom de la source.

2.2 Caractériser les cellules à saisir

J’affecte une couleur de fond spécifique pour toutes les cellules à saisir. Par exemple, vert pastel.

2.3 Protéger la feuille sans protéger les cellules à saisir

Si par précaution, je protège la feuille, seules les cellules à fond vert ne seront pas protégées pour permettre leur saisie. En revanche, les autres cellules seront inaccessibles en saisie.

Dans une feuille protégée où seules les cellules à saisir sont accessibles, la saisie en est facilitée. Quand on valide la saisie d’une cellule avec [Tab], la prochaine cellule active qui se présente à la saisie est la prochaine disponible (à droite sur la ligne, puis sur les lignes au-dessous de gauche à droite), c’est-à-dire la suivante non protégée.

2.4 Favoriser les pavés de saisie

Les pavés de saisie, idéalement, présentent 3 colonnes :

• L’intitulé de l’information à saisir ;
• L’information saisie proprement dite ;
• Des précisions détaillées sur les contraintes de l’information à saisir : absence, valeur nulle, espace de définition, relations avec d’autres informations.
• J’ajoute volontiers une quatrième colonne, calculée, donnant le résultat d’un contrôle de cohérence : respect d’une fourchette de valeurs, information non nulle, compatibilité avec d’autres informations, etc.

En titre, sur les 3 ou 4 colonnes fusionnées, je pose l’intitulé de la source.

3. Les tableaux de saisie

J’appelle tableau de saisie, une feuille qui enregistre par exemple les mouvements de trésorerie dans un compte, les entrées ou les sorties des bouteilles dans la gestion d’une cave, etc.

Chaque opération fait l’objet d’une nouvelle ligne sur laquelle, les données sont majoritaires. Quelques zones peuvent être calculées comme le nouveau solde en fin de ligne en trésorerie.

Dans ces tableaux, plus qu’ailleurs, le respect d’un format harmonisé par colonne est impératif. Il facilite le contrôle visuel et le confort de travail. Par exemple, pour les dates : le même format JJ/MM/AA ; pour les nombres : séparateur des milliers et deux décimales ; pour les données de texte : même cadrage à gauche, sauf exception, même police et sans effet particulier de caractères.

Au niveau des lignes, je préconise un tramage en utilisant le formatage conditionnel. Celui-ci est expliqué à la fiche 4.4 Le formatage conditionnel. Pour faire apparaître par exemple un tramage alterné sur deux lignes (une normale, l’autre avec un fond bleu pâle), il suffit d’utiliser un style pour les lignes paires. Les formules du formatage conditionnel font le nécessaire.

Ces indications (format harmonisé des colonnes, formules de calculs, tramage) nécessitent de « préparer » les tableaux de saisie. Pour cela, il faut pouvoir évaluer le nombre maximum de lignes à préparer. Les formats harmonisés par colonne peuvent être installés en copiant le format de la première ligne sur toute la colonne. Pour le formatage conditionnel, la zone sera définie de la 2e (en tenant compte d’une ligne d’en-tête spécifique) à la énième ligne. Les formules seront copiées, de la 2e ligne à la dernière prévue. Ces contraintes sont minimes par rapport au confort qui en résulte et la sécurité du travail.

Comme ce maximum risque d’évoluer, et de toutes façons pour faciliter la rédaction des formules de calcul ou de présentation, je préconise vivement d’attribuer des noms de zones au tableau préparé : un nom pour l’ensemble du tableau (pratique dans les formules de base de données) et un nom pour chaque colonne susceptible de rentrer dans des calculs ultérieurs. Pour les paramètres et les hypothèses, je n’hésite pas, non plus, à affecter un nom à une cellule en vue de faciliter la compréhension immédiate des formules.

4. Gérer les hypothèses

Les hypothèses sont des informations initiales susceptibles de varier fréquemment. Je les distingue des paramètres, informations essentielles plus stables utilisées dans les calculs, avec parfois des variantes ponctuelles.

Dans la structure d’un classeur, les hypothèses doivent être placées le plus près possible du début de la première feuille. Car c’est d’elles que dépendent les calculs et les présentations.

Un pavé intitulé Hypothèses fait l’affaire.

Mais le plus important, c’est de pouvoir mentionner ces hypothèses en regard des résultats. Soit dans l’en-tête de certaines colonnes, ou le libellé de totaux. Une hypothèse doit donc avoir un intitulé clair et explicite (intitulé long) et un intitulé plus court ou abrégé pour être intégré dans les titres de colonnes ou de lignes.

Le pavé de saisie comportant des hypothèses se présente alors ainsi :

• L’intitulé (long) de l’hypothèse ;
• La valeur de l’hypothèse saisie ;
• L’intitulé court correspondant ;
• éventuellement, des précisions sur la valeur à saisir.

5. Protéger les saisies

Dans les tableaux aux formules sophistiquées, il est souhaitable de mettre les cellules hébergeant les formules, à l’abri d’une saisie malheureuse qui pourrait les écraser. Comme il peut être judicieux d’interdire la suppression ou l’insertion de lignes ou de colonnes qui perturberaient la structure de la feuille.

Cette protection se fait en deux temps :

5.1 Protéger les cellules

1. Au niveau de chaque cellule, la protection est déclarée dans un onglet de la fenêtre de formatage,

5.2 Protéger/Dé-protéger la feuille

2. Au niveau de la feuille, sa propre protection rend active les protections déclarées pour les cellules :

 

Le tableau qui suit est mal conçu car il énonce le contraire des protections (autorisations). Il nécessite une petite gymnastique mentale pour être interprété correctement.

5.3 Pratiquement, comment faire ?

Comme la protection est une mesure d’exception, je commence par déclarer que toutes les cellules ne sont pas protégées :

Cette opération réinitialise en quelque sorte toutes les cellules à « non protégée ».

Dans un deuxième temps :

Enfin, dans un troisième temps, je protège la feuille pour rendre les protections efficaces.

5.4 Conséquences cachées des protections

Si la protection est bien pratique pour éviter des pertes de formules sophistiquées, elle provoque néanmoins quelques restrictions, normales mais parfois surprenantes, dans des fonctionnalités.

Une feuille protégée contenant des cellules protégées ne peut pas être triée. Il faut déprotéger la feuille, faire le tri et protéger à nouveau la feuille ensuite.

Pour modifier un style, il est indispensable de déprotéger toutes les feuilles du classeur. Cela se comprend aisément. La modification d’un style risque de modifier toutes les cellules dont le format est basé sur ce style. Une cellule protégée n’est pas modifiable. Donc, il est nécessaire de lever les protections le temps de gérer les styles.

Informations complémentaires

Liens

4.4 Le formatage conditionnel

LOC – 11/06/2018 – 2

Plan de la fiche

1. Au niveau de LibreOffice…

2. … et au niveau de Calc (même fenêtre)

Notes – 2. au niveau de Calc

3. Au niveau du classeur

3.1 Édition

3.2 Affichage

3.3 Données

3.4 Outils

4. À propos des macros

Préalables

LibreOffice doit être installé sur votre ordinateur. C’est expliqué ici : 1. Cadre.

Pour enregistrer les paramètres ci-dessous, il vous suffit, à partir de LibreOffice, d’ouvrir un nouveau classeur.

Pour bien comprendre la présentation de ces paramètres, voir la fiche : PRT 1.3 Procédures.

Présentation

Calc offre une multitude de paramétrages qui n’ont pas tous la même incidence. Certains jouent au niveau du système (par exemple, dans le calcul des dates), d’autres sur l’interface avec l’utilisateur (saisie du point décimal, par exemple), d’autres enfin sur l’apparence de la feuille.

Par ailleurs, quand je fournis des exemples avec des procédures associées, ceux-ci ont été réalisés dans mon environnement et ses paramétrages. Pour que le rendu visuel corresponde à ce que j’énonce, il est souhaitable que votre propre environnement soit défini avec les mêmes paramétrages. Pour que vous ne soyez pas perdu par des aperçus ou parfois des comportements non conformes, je vous invite à adopter un certain nombre de paramétrages de base.

En tenant compte des endroits pour définir les paramétrages, je considère trois niveaux : LibreOffice, LibreOffice Calc et le classeur (notamment au niveau de l’affichage). Si vous utilisez d’autres modules de LibreOffice, charge à vous d’interpréter les paramétrages de premier niveau indiqué ici pour LibreOffice ; en effet, vos paramétrages habituels risquent d’en être impactés.

Je rappelle que ▢ indique une case à cocher laissée non cochée, et ▣ une case à cochée cochée (Voir PRT 1.3 Procédures).

Seuls les paramétrages déterminants sont précisés ci-dessous. Pour consulter et modifier les paramètres :

 

1. Au niveau de LibreOffice…

Les premiers niveaux sont indiqués avec « + » en début de ligne. Pour déployer un niveau, il suffit de cliquer sur +.

Je n’indique que les paramètres pour lesquels une valeur précise est nécessaire.

+ LibreOffice

…

Sécurité (voir ci-après § 4. À propos des macros).

Personnalisation

‡ Thèmes Firefox ‡

⨀ Apparence par défaut, ne pas utiliser de thème.

Couleur de l’interface

‡ Jeu de couleurs ‡

♫ Jeu : {LibreOffice}.

Avancé

‡ Fonctionnalités optionnelles ‡

▣ Activer l’enregistreur de macros.

+ Chargement/Enregistrement

Général

‡ Charger ‡

▣ Charger les paramètres utilisateur avec le document

▣ Charger les paramètres d’impression avec le document

‡ Enregistrer ‡

▣ Enregistrer les informations de récupérations toutes les ♪ {10} minutes

▣ Enregistrer les URL relatifs au système de fichiers

+ Paramètres linguistiques – Important

Langues

‡ Langues de… ‡

♫ Interface utilisateur : { par défaut – Français (France) }

♫ Paramètres locaux : { par défaut – Français (France) }

▣ Touche séparateur de décimales : identique au paramètre de la locale

♫ Monnaie par défaut : { par défaut – EUR }

♪ Motifs d’acceptation des dates : D/M/Y; D/M; D.M.Y; D-M-Y

‡ Langue par défaut des documents ‡

♫ Occidental : { par défaut – Français (France) }

2. … et au niveau de Calc (même fenêtre)

+ LibreOffice Calc – Important

Général

‡ Mesures ‡

♫ Unité de mesure : { centimètre }

♫ Tabulations : {1.25 cm}

‡ Actualisation ‡

⨀ Actualiser les liens au chargement ⨀ {sur demande}

‡ Paramètres de saisie ‡

▣ Appuyer sur Entrée pour déplacer la sélection ♫ vers le {bas}

▣ Mettre en évidence la sélection dans les en-têtes de lignes/colonnes

▣ Afficher un avertissement d’écrasement lors du collage des données

Par défaut

‡ Nouveau classeur ‡

♫ Nombre de feuilles : {1}

♪ Nom de préfixe pour la nouvelle feuille : {Feuille}

Affichage

‡ Afficher ‡

▣ Indicateur de commentaires

▣ Mise en évidence des valeurs (voir note 1)

▣ Ancrer

▣ Débordement de texte

▣ Afficher les références en couleur

‡ Aides visuelles ‡

♫ Lignes de la grille : {Afficher}

♫ Couleur : {#DDDDDD}

‡ Fenêtre ‡

▣ En-têtes de lignes et colonnes

▣ Barre de défilement horizontale

▣ Barre de défilement verticale

▣ Onglets des feuilles

‡ Objets ‡

♫ Objets/images : {Afficher}

♫ Diagrammes : {Afficher}

♫ Objets de dessin : {Afficher}

Calcul

‡ Références circulaires ‡

▣ Itération

♫ Pas : {100}

♫ Valeur de changement minimum : {0.001}

‡ Dates ‡ ⨀ 30.12.1899 ⨀ (voir note 2)

‡ Calculs généraux ‡

▣ Respecter la casse

▣ Exactitude comme affiché

▢ Les critères de recherche avec = ou <> doivent correspondre à des cellules entières

⨀ { Autoriser les expressions régulières dans les formules }⨀

Formule

‡ Options de formule ‡

♫ Syntaxe de la formule : { Calc A1 }

‡ Séparateurs ‡

♪ Fonction : {;}

‡ Paramètres de calculs détaillés ‡

⨀ Paramétrages par défaut ⨀

‡ Recalculer au chargement du fichier ‡

♫ Excel 2007… : {Ne jamais recalculer}

♫ Classeur ODF… : {Ne jamais recalculer}

 

Je termine sans oublier de valider mon travail d’enregistrement des paramètres :

Notes  – 2. Au niveau de Calc

(1) : ce paramètre colorie le contenu des cellules :

• en vert si c’est une formule,
• en bleu si c’est une constante nombre,
• en noir si c’est une constante texte.

Les néophytes apprécient pour s’y retrouver plus facilement. Avec un peu d’expérience, cette facilité devient inutile. À paramétrer selon votre niveau.

(2) : Ce choix détermine le calcul des dates et peut difficilement être modifié. En effet, les dates dans Calc sont enregistrées dans les cellules de type Date sous forme d’un nombre de jours depuis une date de départ, objet de ce paramétrage. Si la date de départ est modifiée, toutes les dates apparaîtront changées. Ce qui me fait dire que ce paramètre peut en pratique, être difficilement modifié. (Pour plus de précisions, voir : 3.5 Fonctions de Date et Heure.

3. Au niveau du classeur

Les paramètres se situent en plusieurs endroits. Dans la liste qui suit, un paramètre activé apparaît au menu avec son icône sur fond bleu clair ; désactivé, l’icône apparaît sur fond blanc. Parfois, l’icône est remplacé par V (activé) ou rien (désactivé).

3.1 Édition

Mode édition: activé ; permet de saisir, dans certaines conditions directement dans la cellule.

3.2 Affichage

Normal : activé

Disposition des barres d’outils : Par défaut

Barre d’outils :

Formatage : activé

Rechercher : activé

Standard : activé

Barre de formule : activé

Barre de formule : activé

Barre d’état : activé

.

En-têtes des lignes et colonnes : activé

.

Lignes de la grille de la feuille active : activé

.

Volet latéral : activé

Navigateur : activé.

3.3 Données

Calculer automatiquement : activé.

3.4 Outils

Ce dernier paramètre est important. Il évite des déconvenues difficiles à rectifier lors de la saisie des constantes de texte entre guillemets dans les formules.

4. À Propos des macros

Les macros sont des petits programmes. Quand on charge un classeur contenant des macros à partir d’interner, celles-ci peuvent contenir des virus. Il est donc important pour la sécurité de votre système, de pouvoir activer ou désactiver les macros d’un classeur au moment de son chargement. Il va sans dire que cette précaution est inutile pour les classeurs personnels enregistrés sur votre disque dur.

Je propose le paramétrage suivant qui s’accorde avec le téléchargement de mes exercices et exemple. Ce paramétrage est proposé en version 6.0.6. Dans les versions antérieures, il est un peu plus direct, mais si vous n’avez pas encore migré en version 6, vous vous y retrouverez facilement.

Important : il est nécessaire de fermer LibreOffice pour que les paramétrages soient pris en compte ensuite.

Avec ce paramétrage, quand vous ouvrirez un classeur, le système vous proposera d’activer ou de désactiver les macros. Faites votre choix pour continuer.

Informations complémentaires

Liens

1. Cadre

3.5 Fonctions de Date et Heure

PRT 1.3 Procédures

LOC – 01/06/2018 – 1

Plan de la fiche

1. Saisir l’information

1.1 Cellule active

1.2 Saisir dans une cellule

Pour saisir dans une cellule, deux approches

Saisir du texte ou des nombres (constantes)

Saisir une formule

1.3 Édition du texte en cours de saisie

1.4 Validation, modes et effets

Valider une saisie avec [Entrée]

Valider une saisie avec [Tab] ou [Maj+ Tab]

2. Choisir la zone de saisie

Précisions sur les paramétrage Validation et mode de saisie

3. Utiliser les listes de choix

3.1 Définir la liste de choix

3.2 Préciser la validité des zones saisies

3.3 Comportement en saisie

3.4 Raffinements

3.5 Conclusion

Présentation

Cette fiche apporte les bases indispensables pour saisir de l’information dans un tableau et la modifier. La fiche suivante 2.3 Manipuler le contenu des cellules s’attachera déplacer, copier, transformer le contenu des cellules saisies.

1. Saisir l’information

1.1 Cellule active

Dans toute feuille ouverte, une cellule est active, et une seule. Elle se reconnaît à sa bordure plus grasse et un petit carré noir dans le coin en bas à droite. Dans une nouvelle feuille, c’est la cellule A1. Sinon, c’est la cellule qui était active au dernier enregistrement du classeur.

Dans les bandes de références Lignes et Colonnes, le numéro de ligne et la lettre de la colonne de la cellule active, apparaissent sur un fond distinct des autres.

La référence de cette cellule est affichée au début de la ligne Formule, juste au-dessus du tableau.

Le contenu de la cellule active est affiché dans la zone d’édition Formule à la suite de la référence.

Pour activer une cellule, plusieurs approches :

1.2 Saisir dans une cellule

On ne peut saisir que dans une cellule active. Il convient donc d’activer, si nécessaire, la cellule envisagée.

Pour saisir dans une cellule, deux approches :

Quand une zone a le focus, le point d’insertion est dedans, au début si la zone est vide, ou sinon, là où le clic a été fait.

Que la saisie se fasse dans la zone d’édition formule ou dans la cellule directement, les caractères saisis sont visibles dans les deux endroits. Je peux passer de l’un à l’autre sans inconvénient avec ▼ pour prendre le focus.

Saisir du texte ou des nombres (constantes)

Il suffit de taper les caractères normalement et de terminer (valider) la saisie avec [Entrée].

Suivant les paramétrages de langue conseillés, la frappe du “.” au clavier numérique donne le séparateur décimal de la langue locale(en Français de France : “,”).

Dans un texte, il est possible d’insérer un saut de ligne avec [Ctrl+ Entrée].

• Dans la zone d’édition Formule, la ligne précédente n’est plus visible, mais elle est toujours là. Elle est accessible par déplacement avec les flèches à gauche ou à droite.
• Si la saisie est dans la cellule, les lignes restent visibles et accessibles.

Le cadrage de l’information, les effets de caractères sont des questions de formatage de la cellule, totalement indépendants de la saisie.

La saisie une fois validée, l’information est intégrée au classeur telle quelle.

Saisir une formule

La saisie d’une formule se caractérise uniquement par le premier caractère qui doit être {=}. Le reste du texte se tape comme les constantes.

Pour qu’une formule soit reconnue comme telle, la cellule doit être de type Nombre ou assimilé (Monnaie, Pourcentage, Date, Heure, Scientifique). Sinon (Type Texte), la formule, malgré le {=} en début de texte, est prise comme du texte et ne sera pas interprétée par le système.

La formule, une fois sa saisie validée, est intégrée comme information dans le classeur. Elle reste accessible dans la zone d’édition formule. Elle passe alors par deux phases.

• La première, appelée compilation, est une phase de reconnaissance syntaxique : le système vérifie si les composants de la formule (opérandes, opérateurs, séparateurs) sont intelligibles. Si ce n’est pas le cas, une erreur est affichée dans la cellule.
• La deuxième phase, interprétation, est l’exécution proprement dite de la formule. Une erreur peut encore survenir à ce stade : elle est affichée pour pouvoir corriger la formule. Si tout est bon, le résultat de la formule est affiché dans la cellule ; la formule, quant à elle, reste visible dans la zone d’édition de la formule.

La casse n’a pas d’importance. Néanmoins, je préconise de taper les noms de fonction, les références de cellules et les noms de zones, en minuscules. En cas de nom inconnu, la compilation s’arrête : ce qui est passé en majuscules en bon, le premier nom resté en minuscules est inconnu.

La signification des codes erreurs est accessible dans l’aide avec l’index « codes d’erreur;liste ».

1.3 Édition du texte en cours de saisie

Je parle ici des manipulations possibles du texte ou des formules en cours de saisie.

Quelle que soit la zone de saisie (cellule ou zone d’édition Formule), le texte en cours de saisie peut être manipulé comme dans n’importe quel éditeur de texte ou traitement de texte. Ces opérations sont plus simples dans la zone d’édition de la formule.

Le point d’insertion peut être déplacé avec les flèches de direction (►, ◄) ou de positionnement ([Début], [Fin]). Bien entendu, ▼ positionne le point d’insertion à volonté.

Les touches d’effacement fonctionnent : [Retour arrière] efface le caractère précédent, sans effet si le point d’insertion est en début de ligne ; [Suppr] efface le caractère suivant, sans effet en fin de zone.

Une portion de texte peut être sélectionnée, soit avec le point d’insertion (▼ … ▲ sur la sélection), soit avec positionnement en début de sélection (▼) puis [Maj+] et positionnement (▼) en fin de zone. Un texte sélectionné peut être supprimé ou copié dans le presse-papier. De même, le contenu du presse-papier peut être inséré à la position du point d’insertion ([Ctrl+ V]). Un texte sélectionné peut être déplacé dans la zone de saisie avec un glisser-déposer. [Ctrl+ X] permet de couper l’extrait sélectionner pour le coller ailleurs.

Ces opérations d’édition en cours de saisie doivent être distinguées des opérations sur une cellule active (ou un groupe de cellules sélectionnées). Ici, nous sommes dans du traitement de texte. Là, nous sommes dans la manipulation d’objets, en l’occurrence, la cellule ; voir ci-après.

1.4 Validations, modes et effets

La validation de la saisie se fait normalement avec [Entrée]. Elle peut se faire aussi avec [Tab] ou [Maj+ Tab]. Les conséquences sont différentes. Dans tous les cas, la validation fait perdre à la cellule saisie sa qualité de cellule active ; cette qualité est récupérée par la cellule voisine dans laquelle la saisie peut commencer directement. Je précise ici le sens du déplacement de la cellule saisie vers la suivante.

Valider une saisie avec [Entrée]

fait passer à la cellule voisine indiquée ici :

Si la case Appuyer… est cochée, c’est le sens choisi dans le combo Vers le bas,… qui s’applique. Je note que ce paramétrage est global pour l’application.

Ce sens peut être modifié ponctuellement sur la feuille en cours ici :

Valider une saisie avec [Tab] ou [Maj+ Tab]

passe indépendamment du paramétrage précédent, à la cellule de droite. Valider une saisie avec [Maj+ Tab] passe à la cellule de gauche.

2. Choisir la zone de saisie

La saisie peut donc se faire soit dans la zone d’édition Formule, soit dans la cellule directement. Il n’y a aucune contrainte pour choisir l’une plutôt que l’autre. C’est essentiellement une question de confort.

Le mode choisi, s’il était nécessaire de le vérifier est affiché ici :

Dans Menu : Édition, en dernière ligne : Mode édition. Cette ligne est activée quand la saisie se fait dans la cellule (ce que Calc appelle le mode édition). Cette ligne fonctionne comme une bascule. Elle permet donc de passer d’un mode à l’autre.

Plus rapidement, il suffit de prendre le focus dans une des zones pour y activer la saisie. Le focus se prend avec ▼ dans la zone.

Néanmoins, le mode édition apporte un avantage dans la compréhension des formules. Les références de cellules ou de zones sont colorées, et les cellules ou zones ont leur bordure coloriée de la même couleur. De plus, le passage du point d’insertion sur une parenthèse la pose en gras ainsi que sa correspondante, avant ou après. C’est bien pratique pour s’y retrouver parfois.

Précisions sur les paramétrages validation et mode de saisie

Il existe deux paramétrages système qui rentrent en concurrence dans la déclaration par défaut du mode de validation.

Comme on le voit, la déclaration de la touche [Entrée] aurait deux fonctions différentes dont le lien ne semble pas évident. Il n’existe du reste pas !

Le premier paramètre indique qu’on peut valider une saisie avec [Entrée] et précise où aura lieu la saisie de la cellule suivante.

Le deuxième paramètre s’applique à une cellule active en dehors de sa saisie pour préciser à quoi sert la touche [Entrée] dans cette situation.

Si ni (1) ni (2) ne sont cochées, il ne sera pas possible de valider une saisie avec [Entrée]. Seules [Tab] ou [Maj+ Tab] pourront être utilisées pour ce faire. Et appuyer sur [Entrée] en dehors d’une saisie n’aura aucun effet sur la cellule active.

Si (1) est cochée seule, [Entrée] pourra valider une saisie et la cellule suivante correspondra au déplacement indiqué.

Si (2) est cochée seule, [Entrée] valide la saisie (curieux, mais c’est ainsi que ça se passe). Mais la cellule reste active et la saisie est de nouveau possible en mode édition, c’est-à-dire, dans le langage de LibreOffice Calc, directement dans la cellule. Il n’est pas possible d’en sortir si ce n’est en validant avec [Tab] ou [Maj+ Tab] ou en sélectionnant manuellement une autre cellule.

Si (1) et (2) sont toutes les deux cochées, tout se passe comme si (1) était cochée seule.

Personnellement, j’ai coché le premier paramètre et pas le second, et je valide habituellement mes saisies avec [Tab].

3. Utiliser les listes de choix

Dans les saisies en tableau, il est fréquent que les valeurs à saisir dans une colonne doivent, pour des raisons de cohérence des analyses futures, faire partie exclusivement de valeurs prévues. Par exemple, en trésorerie pour affecter les dépenses à des catégories. Toutes les dépenses doivent avoir une catégorie, et uniquement une des catégories prévues. Autre exemple : dans la gestion des crûs d’une cave, la couleur du vin ou sa zone de production, etc.

Dans les applications sophistiquées, on utilise des codes. À chaque code est associée une signification et une liste de code permet de choisir le bon. Il n’est pas possible d’utiliser un code inexistant.

La liste de choix remplace avantageusement les codes. L’utilisateur fait face à des informations en clair. Et il est entouré des mêmes garanties.

3.1 Définir la liste de choix

Une liste de choix doit être définie. Je pose mes listes de choix dans une feuille spécifique. Chaque liste est un pavé particulier portant en titre l’intitulé de la liste.

Ce pavé a normalement une seule colonne. J’y saisis les valeurs qui apparaîtront dans le tableau de saisie. Je nomme la zone de ces valeurs (sans le titre), par exemple : CAT_liste pour liste des catégories. Le nommage des zones est expliqué ici :1.5 Gérer les zones nommées.

Mais je peux utiliser aussi un tableau de saisie dans lequel la liste de choix est présente. Prenons l’exemple d’une trésorerie domestique qui gère aussi les budgets. Toutes les écritures sont imputées à une catégorie. Et toutes les catégories ont un budget défini. Le tableau de saisie de ces budgets est la liste idéale des catégories ; elle n’a pas besoin d’exister par ailleurs pour être définie comme liste de choix dans le tableau de saisie des écritures.

L’intérêt de mettre les listes de choix dans une feuille distincte se manifeste lors des ajouts. Il suffit d’insérer une ligne dans le tableau pour saisir un nouveau choix : la zone nommée de ce tableau sera automatiquement mise à jour dans ses dimensions.

3.2 Préciser la validité des zones saisies

Ensuite, dans la préparation du tableau de saisie, il faut associer chaque cellule de la zone à saisir, avec la liste de choix. En fait, il est possible de sélectionner le groupe de cellules concernées pour faire l’opération en une fois.

Précisions

Autoriser les cellules vides : le libellé est explicite. en cochant la case, il sera possible de laisser la cellule vide. Mais si la case n’est pas cochée, la saisie devra impérativement comporter un code de la liste.

Afficher la liste de sélection : ce choix, que j’indique nécessaire, peut être supprimé quand la liste de choix est trop importante. Il est plus simple alors de ne pas la voir.

Trier les entrées dans l’ordre croissant : d’abord, la liste peut être triée dans sa zone. Ensuite, certaines personnes, du fait de l’habitude, préfère garder une liste initiale sans changement, et voir les ajouts en fin de liste. À voir…

3.3 Comportement en saisie

Avec ce paramétrage, désormais toutes les cellules associées à une liste se présentent, quand elles sont actives, avec une flèche sur le côté droit.

▼ sur cette flèche affiche la liste si le paramétrage le prévoit. Seule une valeur de cette liste sera acceptée en saisie, éventuellement une saisie vide si spécifiée en paramètres.

3.4 Raffinements

La fenêtre de paramétrage des validités permet de définir une aide à la saisie et un message en cas d’erreur.

3.5 Conclusion

Voilà une fonctionnalité intéressante, facile à mettre en œuvre et particulièrement efficace pour la cohérence des résultats.

Informations complémentaires

Liens

1.5 Gérer les zones nommées

2.3 Manipuler le contenu des cellules

LOC – 31/05/2018 – 2

Plan de la fiche

1. Le classeur

1.1 Créer un nouveau classeur

1.2 Ouvrir un classeur existant

au format .ods

aux formats propriétaires (.xls ou autres)

au format texte .csv

1.3 Sauvegarder un classeur déjà existant

1.4 Sauvegarder un nouveau classeur

1.5 Exporter un tableau au format CSV

16. Autres sauvegardes

2. La feuille

3. La page

Ligne Menu

Lignes Boutons

Ligne Formule

Partie principale

Autres zones horizontales

4. Lignes et colonnes

5. La cellule

Présentation

Cette fiche propose de décrire le cadre d’utilisation du tableur en précisant les éléments : le classeur, la feuille, les lignes et les colonnes, la cellule. Il est important, en effet, de bien connaître l’environnement auquel  vous allez être confronté, et en particulier savoir comment je nomme ses différentes zones. Ces noms vous permettront par la suite un repérage plus facile.

1. Le classeur

Le classeur LibreOffice Calc est un fichier repérable avec son extension .ods. Comme tel, il est exploitable avec l’explorateur de fichiers pour le copier, le déplacer, le supprimer, le renommer. Il est possible de lui adjoindre un mot de passe.

1.1 Créer un nouveau classeur

LibreOffice n’est pas ouvert

Je lance LibreOffice comme vu dans LOW 1.2 Lancer et découvrir LibreOffice en choisissant Créer un nouveau classeur.

LibreOffice est ouvert

À partir de n’importe quel fichier ouvert dans n’importe quel module LibreOffice :

Dans les deux cas, un classeur vierge est ouvert à l’écran.

La barre de titre porte le nom provisoire Sans nom 1 – LibreOffice Calc.

1.2 Ouvrir un classeur existant

Un classeur au format .ods

À partir de l’explorateur de fichier, un double-clic sur le classeur l’ouvre.

À partir de LibreOffice :

Un classeur aux formats propriétaires (.xls ou autres)

Il est possible d’ouvrir un classeur créé par une autre application, directement à partir de LibreOffice Calc comme ci-dessus. LibreOffice reconnaît sans difficulté les autres formats.

Un classeur au format texte .csv

Il est possible d’ouvrir un classeur enregistré au format texte .csv directement à partir de LibreOffice Calc. L’ouverture comporte la transformation automatique du fichier d’entrée, moyennant la saisie de quelques options de traduction :

1.3 Sauvegarder un classeur déjà existant

Après avoir enregistré des données dans un classeur, je dois le sauvegarder pour les conserver. Deux approches :

 

1.4 Sauvegarder un nouveau classeur

1.5 Exporter un tableau au format .csv

De même qu’il est possible d’ouvrir un fichier .csv pour le traiter comme un tableau, il est possible de créer un fichier .csv à partir d’une feuille. Si le classeur contient plusieurs feuilles, c’est uniquement la feuille active qui est exportée.

1.6 Autres sauvegardes

Il est possible de sauvegarder le classeur directement sur un cloud avec Fichier, Enregistrer le fichier distant…

Il est possible de sauvegarder le classeur sous un autre format qu’.ods (.xls, …). De même qu’il est possible d’exporter l’aperçu en format PDF. Ces fonctionnalités de transformation sont détaillées ici : 4.7 Aperçu, impressions et autres transformations.

En cours de travail, le système procède à des sauvegardes automatiques à un rythme défini dans Outils, Options, Chargement/Enregistrement, Général, ‡ Enregistrer ‡ ◩ Enregistrer les informations de récupération automatique toutes les ♪ minutes. Ce paramètre est normalement activé avec 10 minutes.

2. La feuille

Un classeur est composée de feuilles. Une feuille correspond à un tableau. Chaque feuille est identifiée par un nom. Ce nom est visible sur un onglet, en bas de la feuille. Le nombre de feuilles proposé par défaut dans un nouveau classeur est défini dans Outils, Options, LibreOffice Calc, Par défaut ; (mis à 1 dans mes paramétrages).

Les noms de feuilles sont libres. Deux feuilles ne peuvent avoir le même nom. Les caractères [, ], *, ?, :, / et \ sont interdits. Mais il faut savoir que les noms de feuilles entrent dans la composition des références de cellules, en particulier dans les formules. Pour ne pas alourdir leur composition, un bon compromis est à trouver entre la brièveté du nom et sa signification.

Les opérations sur les feuilles sont accessibles par le Menu Feuille ou par le menu contextuel ouvert avec ▲ sur l’onglet d’une feuille. Des raccourcis clavier sont prévus aussi. Je présente ces opérations en détail ici : 1.3 Gérer les feuilles.

3. La page

La page est le tableau visible dans la fenêtre de l’application LibreOffice Calc. La page correspond toujours à une feuille précise du classeur. Il est important pour se repérer facilement de bien identifier les différentes zones de la page.

Ligne Menu

Elle apparaît en haut de la fenêtre, juste au-dessous de la barre de titre.

Fichier : procédures classiques ;

Édition : procédures classiques ;

Affichage : ces fonctionnalités conditionnent le visuel de la page ;

Insertion : il est possible d’insérer des éléments très variés, dans un tableau, dans une cellule, dans le texte ou la formule d’une cellule, ou encore dans la page imprimée (en-tête et pied-de-page).

Format : il s’agit ici de mettre en forme le texte d’une cellule, d’appeler la gestion des styles, de préciser les caractéristiques des caractères, des lignes, des colonnes. Le formatage conditionnel permet d’adapter le format d’une cellule en fonction de son contenu ou du contenu d’une autre. On peut enfin jouer sur le formatage des éléments insérés (images, diagrammes, objets, etc.).

Feuille : Ce menu permet de gérer une feuille et ses composants comme une entité particulière.

Données : ce menu permet d’appréhender les informations du classeur ou de la feuille en cours comme des ensembles pour leur appliquer des opérations groupées.

Outils et Fenêtre : fonctions classiques.

Lignes Boutons

Sous la ligne Menu, une ou plusieurs lignes de boutons sont affichées ; les boutons visibles sont ceux demandés dans le sous-menu Affichage/ Barre d’outils.

Au survol de chacun, sa fonction est affichée.

Un bouton peut être disponible, indisponible ou actif :

• Un bouton est actif quand la fonctionnalité qu’il représente est appliquée dans la cellule active ou les cellules sélectionnées ; exemple [Centré] pour le contenu. Un bouton actif apparaît sur un fond bleu (comme la sélection qu’il affecte).
• Un bouton indisponible ne peut pas être utilisé pour la ou les cellules sélectionnée. Cette indisponibilité dépend du contexte. Par exemple, le bouton [Fusionner des cellules] n’est disponible que si au moins deux cellules contiguës sont sélectionnées. Seul un bouton disponible peut être activé.

À chaque nouvelle sélection ou activation de cellules, la panoplie des boutons actifs change pour s’adapter aux cellules sélectionnées.

Ligne Formule

Sous les boutons, se situe la ligne Formule.

La première zone donne la référence de la cellule active. À la création d’un nouveau classeur, la première cellule en haut à gauche est active. Sa référence est A1.

La deuxième zone comprend quelques boutons et la zone d’édition disponible pour saisir le contenu de la cellule active. J’appelle cette zone zone d’édition Formule et je la cite comme telle très souvent.

Partie principale

Comprend trois zones :

• le navigateur, à gauche et généralement masqué au départ ; ▼ sur la flèche au centre contre la bordure gauche du tableau proprement dit permet de le déployer ;
• le tableau proprement dit ;
• le volet latéral, à droite, en partie masqué, avec une flèche analogue à celle du navigateur.

Le tableau proprement dit comprend en tête une ligne des Références Colonnes (A, B, C, etc.) et verticalement à gauche, une colonne des Références Lignes (numéros).

Les cellules sont matérialisées par une grille discrète qui est affichée à volonté (Dans Menu : Affichage, Lignes de la grille de la feuille active).

La cellule active se repère avec une bordure plus épaisse et un point en bas à droite au survol duquel le curseur se transforme en croix.

Les cellules sélectionnées se repèrent avec un fond bleu clair (quand le fond est coloré, la couleur est ombrée).

Autres zones horizontales

En pied du tableau, la bordée d’onglets des feuilles.

En pied de fenêtre, une autre rangée de boutons (pour la recherche, en autres).

La barre d’état donne sur plusieurs zones, des informations pratiques : le numéro de la feuille en cours sur le nombre de feuilles ouvertes, le style, le formatage de l’affichage, etc. On retrouve des ressemblances avec la barre d’état des documents texte de LOW.

4. Les lignes et colonnes

Les lignes et les colonnes dessinent dans la feuille une trame qui permet de localiser et d’identifier les cellules.

Les colonnes sont identifiées par des lettres, et les lignes par des numéros. Ces identificateurs sont affichés respectivement sur une ligne Références Colonnes (au-dessus du tableau) et une colonne Références Lignes, (à gauche du tableau).

Je détaille les opérations sur les lignes et les colonnes ici : 1.4 Gérer les lignes et les colonnes.

5. La cellule

La cellule est le cœur du tableur. Elle contient l’information. Cette information peut être une constante : saisie, elle est figée tant qu’elle n’est pas remplacée par une autre information. L’information peut aussi être une formule. C’est le résultat de la formule qui est visible comme information de la cellule. Et le plus souvent la formule fait appel à d’autres cellules. Le résultat tient donc compte des informations présentes dans les cellules appelées. Par exemple, une formule Somme() donne une information Total de toutes les cellules indiquées en paramètres.

Mais la cellule est aussi un concept plus complexe. C’est d’abord un ensemble de propriétés qui définissent son fonctionnement. Ainsi, une cellule vierge est différente d’une cellule présentant une information nulle (nombre) ou vide (texte). Une cellule affectée à un nombre ne se comportera pas dans les formules comme une cellule affectée à un texte.

La cellule est aussi un cadre pour l’information qu’elle contient : police, effets de caractères, cadrage horizontal et vertical, couleur du fond, bordure et effet de la bordure, etc.

La cellule possède aussi des droits d’accès. Elle peut être protégée rendant sa saisie impossible.

Enfin, la cellule est un système de référence à deux niveaux. Sous Calc, une cellule est généralement référencée avec la ou les lettres de sa colonne et le numéro de la ligne ; exemple A1. C’est sous cette forme que les cellules sont désignées dans les formules, comme des variables. Ces références peuvent prendre deux formes, relative ou absolue. Et cette forme peut être exprimée distinctement pour la colonne et pour la ligne. La forme absolue est indiquée avec « $ » devant l’identificateur. Exemples : $A1, $A$1, A$1. Ces subtilités sont précisées à la fiche 3.2 Le référencement des cellules.

La cellule et son contenu est un objet qu’il est possible d’éditer (copie, suppression, déplacement). Le contenu peut lui aussi être édité, distinctement de la cellule. La fiche 1.6 Gérer les cellules comme objets-cadres donnent tous les détails pour se retrouver dans ce foisonnement de fonctionnalités.

Informations complémentaires

Liens

1.3 Gérer les feuilles

1.4 Gérer les lignes et les colonnes

1.6 Gérer les cellules comme objets-cadres, §1. Cellule active ou sélectionnée

2.2 Cellule active

3.2 Le référencement des cellules

4.7 Aperçu, impressions et autres transformations

LOW 1.2 Lancer et découvrir LibreOffice

 

LOC – 13/05/2018 – 1

Plan de la fiche

1. Fonctions calculant des références

Adresse()

Colonne()

Feuille()

Indirect()

Ligne()

2. Fonctions de dénombrement

Colonnes()

Feuilles()

Lignes()

3. Fonctions de recherche

Choisir()

Decaler()

Equiv()

Recherche()

RechercheH()

RechercheV()

4. Autres fonctions

Lien.hypertexte()

Type.erreur()

Typeerreur()

Style()

Présentation

Ces fonctions méritent d’être connues car elles sont indispensables dès qu’on rentre dans des calculs sophistiqués. Elles permettent en effet de connaître des éléments indispensables dans la composition des formules quand ces éléments doivent être calculés.

L’assistant Fonctions les appelle Fonctions de feuille de calcul. L’aide de Calc les appelle Fonctions de classeur.

1. Fonctions calculant des références

Adresse (No_ligne; No_colonne ; Type_référence [; Notation [; Feuille]])

Retourne une expression de type Texte correspondant à la référence d’une cellule pour son insertion dans une formule.

No_ligne est un entier positif indiquant la ligne de la cellule référencée.

No_colonne est un entier positif indiquant le rang de la colonne dans la feuille (ce n’est pas sa lettre).

Type de référence : 1 = $A$1 ; 2 = A$1 ; 3= $A1 ; 4 = A1.

Notation : si =0, notation L1C1 ; absent ou toute autre valeur = notation A1.

Feuille : Nom de la feuille, de type Texte. Si indiqué, le nom de la feuille est inclus dans la référence, séparé par “!” de la référence de base. Si le nom de la feuille comporte des espaces, il est mis entre guillemets simples.

Voir aussi : Indirect().

Colonne (Référence)

Renvoie le numéro de colonne (un nombre de 1 à N, et non la ou les lettres) de la Référence.

Référence est une référence valide de cellule et non une expression de type Texte.

Utile pour transformer un rang de colonne en nombre et faire des opérations arithmétiques dessus.

Comparable à Ligne().

Feuille (Zone)

Détermine le numéro interne de la feuille (de 1 à N) dans laquelle se trouve la zone.

Indirect (Référence; Notation)

Renvoie le contenu d’une cellule de la Référence.

Référence est une expression de type Texte correspondant à la référence d’une cellule : constante entre guillemets doubles ou référence d’une cellule portant une expression de type Texte compatible avec le référencement d’une cellule.

Notation : si =0, notation L1C1 ; absent ou toute autre valeur = notation A1.

Cette fonction est particulièrement pratique pour intégrer une référence calculée, en particulier en combinaison avec Adresse().

Voir aussi Adresse().

Ligne (Référence)

Renvoie le numéro de ligne de la Référence.

Référence est une référence valide de cellule et non une expression de type Texte

Utile pour faire des opérations arithmétiques avec le numéro de ligne

Comparable à Colonne().

2. Fonctions de dénombrement

Colonnes (Zone)

Retourne le nombre de colonnes de la Zone.

Zone peut être une référence de zone (exemple : A1:F15) ou un nom de plage nommée. Mais pas une constante de type Texte.

Feuilles (Zone)

Retourne le nombre de feuilles comprises dans Zone (Zone peut couvrir plusieurs feuilles).

Zone peut être une référence de zone (exemple : A1:F15) ou un nom de plage nommée. Mais pas une constante de type Texte.

Si Zone est omis, renvoie le nombre de feuilles du classeur.

Lignes (Zone)

Retourne le nombre de lignes de la Zone.

Zone peut être une référence de zone (exemple : A1:F15) ou un nom de plage nommée. Mais pas une constante de type Texte.

3. Fonctions de recherche

La plupart de ces fonctions acceptent les expressions régulières dans l’expression des valeurs à chercher. C’est expliqué ici : 3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières. 

Choisir (Index; Valeur-1; Valeur-2; … Valeur-30)

Retourne la Valeur-i pour i= Index.

Index est un entier positif de 1 à 30.

Decaler (Référence; Lignes; Colonnes [; Nb de lignes [; Largeur]])

Retourne la valeur d’une cellule dont la référence est calculée à partir de Référence en appliquant une décalage de Lignes et de Colonnes.

Référence est une référence valide de cellule et non une expression de type Texte, posée sans guillemets.

Lignes est un nombre entier positif, négatif ou nul précisant le décalage en ligne vers le bas (Positif) ou vers le haut (Négatif).

Colonnes est un nombre entier positif, négatif ou nul précisant le décalage en colonne vers la droite (Positif) ou vers la gauche (Négatif).

Quand le paramètre Nb de lignes est présent, la fonction est une fonction de matrice. Je ne traite pas ici les fonctions de matrice réservées aux initiés.

Equiv (À chercher; Rangée [; Mode])

Retourne le rang de l’élément À chercher dans Rangée en fonction du Mode.

À chercher est une valeur de type quelconque.

Rangée peut être une ligne ou une colonne, complète ou partielle exprimée avec une référence de zone, dans laquelle la fonction va chercher À chercher. Les éléments de Rangée doivent être du même type que À chercher.

Mode peut être -1, 0 ou 1. Si omis, = 1.

• Avec 1, Rangée est supposée triée par ordre croissant. La recherche s’arrête dès qu’un élément présente une valeur supérieure à celle de À chercher. La position de l’élément précédent est retournée.
• Avec -1, Rangée est triée par ordre décroissant. La recherche s’arrête dès qu’un élément présente une valeur inférieure à celle de À chercher. La position de l’élément précédent est retournée.
• Avec 0, la recherche ne peut trouver dans Zone (non triée) qu’une valeur identique à À Chercher. La fonction retourne la position du premier élément trouvé. Si aucun élément n’est trouvé, la fonction renvoie #N/D.

Recherche (À rechercher; Zone; Résultat)

Dans une Zone qui comprend au moins deux rangées (lignes ou colonnes), renvoie la valeur trouvée dans Résultat à la position où À rechercher a été trouvé dans la première rangée de Zone.

Zone doit être triée sur la première colonne dans l’ordre croissant.

Résultat est le numéro de rangée (à partir de 2) dans Zone où se trouve les résultats à renvoyer.

Il se peut que À rechercher ne soit pas trouvé exactement dans la première rangée de Zone. Dans ce cas, c’est la position du dernier élément trouvé inférieur à À rechercher, qui est retenue. La fonction retourne donc toujours une valeur.

RechercheH (À rechercher; Zone; Résultat[; Mode])

Voisine de Recherche() avec quelques nuances importantes.

Mode est de type Logique. Si omis, =VRAI ou 1.

Si Mode = VRAI, la première ligne de zone est triée par ordre croissant ; comme pour Recherche(), et la fonction retournera toujours une valeur.

Si Mode = FAUX ou 0, la première ligne n’a pas besoin d’être triée. La fonction retournera une valeur que si elle est égale à À rechercher ; sinon, une erreur #N/D est retournée.

La recherche est horizontale, en lignes. Voir aussi RechercheV() pour la recherche verticale en colonnes.

RechercheV (À rechercher; Zone; Résultat[; Mode])

Voisine de Recherche() avec quelques nuances importantes.

Mode est de type Logique. Si omis, =VRAI ou 1.

Si Mode = VRAI, la première colonne de zone est triée par ordre croissant ; comme pour Recherche(), la fonction retournera toujours une valeur.

Si Mode = FAUX ou 0, la première colonne n’a pas besoin d’être triée. La fonction retournera une valeur que si elle est égale à À rechercher ; sinon, une erreur #N/D est retournée.

La recherche est verticale, en colonnes. Voir aussi RechercheH() pour la recherche horizontale en lignes.

4. Autres fonctions

Lien.hypertexte(Adresse [; Titre])

Renvoie Titre, s’il est fourni, sinon Adresse.

Associe le lien Adresse au contenu de la cellule.

Adresse et Titre sont deux expressions de type Texte. L’adresse doit être complète (http://…).

Le lien est associé au contenu (Titre) et non à la cellule : pour actionner le lien, il faudra ▼▼ sur le contenu ; le lien n’est pas accessible dans la cellule en dehors du contenu.

Il est possible de poser un lien directement sans passer par une fonction.C’est expliqué ici : 2.3 Manipuler le contenu des cellules, § 6. Associer un hyperlien au contenu.

Type.erreur(Référence)

Retourne le numéro interne d’erreur de la cellule en Référence.

La signification des numéros internes est dans l’aide de la fonction Type.erreur. Ne pas confondre avec Typeerreur().

Typeerreur (Référence)

Retourne le numéro d’erreur de la cellule en Référence.

Ces numéros sont précisés dans l’aide Calc, à l’index Codes d’erreur; liste. Ne pas confondre avec Type.erreur().

Style (Style [;Durée; Style-2])

Permet d’appliquer le Style à une cellule, et éventuellement un deuxième, Style-2, après un délai en secondes précisé avec Durée.

En pratique, cette fonction termine toujours une formule. Sa valeur retournée est “0”, le nombre nul.

Dans les cellules de type Nombre, il suffit de mettre la fonction en fin de formule à la suite de l’opérateur « + ». Elle ne change pas sa valeur.

Dans les formules de type Texte, il faut la poser avec &T() qui ne retourne rien si l’argument n’est pas de type Texte.

Style et Style-2 doivent exister. Voir ici : 4.1 Les styles.

Associée à la fonction Actuelle(), la fonction Style() permet de formater une cellule en fonction de son résultat sans passer par le formatage conditionnel. Voir l’exemple dans l’aide, à l’index Style, fonction.

Informations complémentaires

Liens

2.3 Manipuler le contenu des cellules, § 6. Associer un hyperlien au contenu

3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières

3.9. Fonctions diverses, § Actuelle

4.1 Les styles

LOC – 27/06/2018 – 2

Plan de la fiche

1. Valeurs logiques

Vrai()

Faux()

2. Opérateurs d’ensemble

Non()

Et()

Ou()

Xor()

3. Fonctions conditionnelles

Si()

Si.conditions()

Si.multiple()

4. Fonctions de résolution d’erreur

Sierreur()

Sina()

Présentation

Calc appelle ces fonctions, des fonctions logiques. Elles regroupent les fonctions de base Vrai() et Faux(), les opérateurs en langage des ensembles, les tests. Elles sont peu nombreuses, mais leur efficacité est irremplaçable.

1. Valeurs logiques

Ces fonctions n’ont pas de paramètres. On s’en sert comme des variables logiques.

Vrai()

Retourne la valeur logique VRAI.

Je note que le système donne aussi la valeur logique VRAI à toute expression de type Nombre, non nulle. Cela signifie que ces expressions utilisées dans un contexte qui attend une valeur logique, seront reçues comme VRAI. En soi, il n’y a pas de différence entre la fonction Vrai() et la valeur logique VRAI.

Faux()

Retourne la valeur logique FAUX.

Je note que le système donne aussi la valeur logique FAUX à toute expression de type Nombre nulle. Cela signifie que ces expressions, utilisées dans un contexte qui attend une valeur logique, seront reçues comme FAUX. En soi, il n’y a pas de différence entre la fonction Faux() et la valeur logique FAUX.

2. Opérateurs d’ensembles

Non (Argument)

Argument peut être une constante ou une variable de type Nombre ou logique.

Retourne la valeur logique inverse de l’argument.

Et (Argument-1; Argument-2; … Argument-30)

Argument-x peut être une constante, une variable ou une expression de type Nombre ou Logique ayant une valeur logique VRAI ou FAUX.

Retourne VRAI si tous les arguments sont VRAI ; retourne FAUX dans le cas contraire. Le système arrête l’exploration des arguments dès qu’il en trouve un = FAUX.

Ou (Argument-1; Argument-2; … Argument-30)

Argument-x peut être une constante, une variable, une expression de type Nombre ou Logique ayant une valeur logique VRAI ou FAUX.

Retourne VRAI si au moins un argument est VRAI. Et FAUX si tous les arguments sont faux. Le système arrête l’exploration des arguments dès qu’il en trouve un = VRAI.

Xor( Argument-1; Argument-2; … Argument-30)

Argument-x peut être une constante, une variable, une expression de type Nombre ou Logique ayant une valeur logique VRAI ou FAUX.

Retourne VRAI si la fonction évalue un nombre impair d’arguments à VRAI.

3. Fonctions conditionnelles

Si (Test [; Si vrai [;Si faux]] )

Test est une constante, une variable ou une expression (comparaison souvent) de type Nombre ou Logique ayant une valeur VRAI ou FAUX.

Si Test est VRAI, le résultat = Si vrai ; si Test est faux, le résultat est Si faux.

Si vrai et Si faux sont des constantes, des variables ou des expressions de type quelconque.

Si vrai et Si faux peuvent être absents tous les deux. Dans ce cas, la fonction retourne la valeur logique de Test, VRAI ou FAUX.

Quand Si faux est seul absent, si Test est VRAI, alors la fonction retourne Si vrai ; sinon la fonction retourne FAUX.

Voir aussi Si.conditions().

Si.conditions (Test-1; Resultat-1; Test-2; Resultat-2; … Test-30; Resultat-30)

Test-x est une constante, une variable ou une expression de type Nombre ou Logique ayant une valeur VRAI ou FAUX.

La fonction retourne Resultat-i au premier Test-i vrai. Si aucun Test-x n’est vrai, la fonction retourne le code erreur #N/D.

Si.multiple (Base; Test-1; Resultat-1; Test-2;Resultat-2;… Test-30; Resultat-30)

Test-x est une constante, une variable ou une expression de type Nombre ou Logique pouvant prendre les valeurs VRAI ou FAUX.

Base est une variable ou une expression de type quelconque.

La fonction retourne Resultat-i au premier Test-i = Base.

Si aucun Test-x n’est trouvé, la fonction renvoie #N/D.

4. Fonctions de résolution d’erreur

Sierreur (OK; Alternative erreur)

OK est une fonction à résoudre.

Alternative erreur est une expression quelconque.

OK est exécutée et si aucune erreur n’est renvoyée, Sierreur() est terminée.

Si une erreur devrait être renvoyée, alors Alternative erreur est renvoyée à sa place.

Sina (OK; Alternative erreur #N/A)

Fonction analogue à Sierreur() quand l’erreur est #N/A.

LOC – 13/05/2018 – 1

Plan de la fiche

1. Avant de commencer

2. Détail des fonctions

2.1 Fonctions retournant une valeur interne

Aujourdhui(), Maintenant()

Date(), Temps()

Dateval(), Tempsval()

Fin.mois()

Mois.decaler()

Serie.jour.ouvre()

Serie.jour.ouvre.intl()

Dimanchedepaques()

2.2 Calculs sur les dates retournant un nombre

Datedif()

Annee(), Mois(), Jour()

Heure(), Minute(), Seconde()

Jours(), Jours360(), Joursem()

Fraction.annee()

No.semaine.iso()

No.semaine.OOO()

No.semaine()

Nb.jours.ouvres.intl()

Présentation

Cette fiche présente en détail les fonctions et les calculs de dates, et par extension, de temps (heures, minutes, secondes).

1. Avant de commencer

Une date, sous LibreOffice Calc, est un concept complexe.

Une valeur interne

En valeur interne, une date est un nombre entier de jours écoulés depuis le jour zéro, le 30/12/1899 par défaut. Ce jour zéro peut être modifié dans les options ; mais attention, c’est un paramétrage au niveau du système tableur qui s’applique à tous les classeurs. Il est donc fortement recommandé de ne pas modifier cette option à la légère.

De plus, avec sa valeur décimale éventuelle, la valeur interne intègre aussi le calcul du temps, les heures, minutes et secondes.

Des composants

Une date comme un temps sont composés d’éléments : jour, mois, année, heure, minute, seconde. J’appelle ces éléments, des composants.

Une chaîne de caractères

Une date peut être exprimée comme une chaîne de caractères respectant un format utilisant une combinaison quelconque de composants et de séparateurs (/ – : .). J’utilise personnellement le format JJ/MM/AAAA. Pour les temps, le format habituel est : HH:MM:SS.

Certains formats permettent aux chaînes de caractères correspondantes d’être reconnues comme des dates. Ces formats sont déclarés au niveau de LibreOffice, Outils, Options, Paramètres linguistiques, Langues, Motifs d’acceptation des dates : D/M/Y, D.M.Y, D-M-Y (D = jour, M = mois, Y = Année). Cela signifie qu’une cellule contenant une chaîne de caractère comme “12/06/2018” pourra être reconnue dans une formule comme un date, si le paramètre en question doit être une date.

Un nombre

Un nombre peut être reconnu comme une date. Il s’agit alors de la valeur interne de la date.

Rappel important : les formats

Dans une cellule de type Date, la valeur enregistrée est toujours la valeur interne. C’est le format de la cellule qui conditionne le visible.

Les codes composants utilisés dans ces formats dépendent de la langue du classeur. En français :

AA ou AAAA : l’année sur 2 ou 4 chiffres (ou avec Y en anglais) ;

M ou MM ou MMM ou MMMM : (exemple pour septembre) le mois sur 1 ou 2 chiffres (M) : (9) ; sur 2 chiffres (MM) : (09) ; avec un intitulé abrégé (MMM) : (Sept.) ; avec un intitulé complet (MMMM) : (Septembre) ;

J ou JJ : quantième jour (1 à 31) sur 1 ou 2 chiffres (8), ou 2 chiffres (08) ;

NN ou NNN : intitulé du jour (Lundi à Dimanche) abrégé (NN) : (Lun.) ; complet (NNN) : (Lundi).

H ou HH : heures de 0 à 23 sur un ou deux chiffres, ou 2 chiffres ;

M ou MM : minutes de 0 à 59 sur un ou deux chiffres, ou 2 chiffres ;

S ou SS : secondes de 0 à 59 sur un ou deux chiffres, ou 2 chiffres.

Vocabulaire propre à cette fiche

J’appelle valeur interne : la date et le temps exprimés avec leur valeur interne, un nombre entier de jours écoulés depuis le jour zéro, complété de décimales pour préciser le temps.

J’appelle date, temps ou date-temps : l’information sous forme explicite, généralement visible avec un format reconnu comme format standard de date par le système LibreOffice ou le format de la cellule.

J’appelle composant : l’année, le mois, le jour, l’heure, la minute ou la seconde entrant dans la composition d’une date-temps ; les composants sont des nombres entiers; ils sont séparés par des séparateurs (/ – : .).

2. Détails des fonctions

Je distingue les fonctions retournant une valeur interne, c’est-à-dire une date ou un temps, reconnu comme étant de type Date et heure et les fonctions retournant un nombre, c’est-à-dire de type Nombre. Beaucoup de confusions perturbent les utilisateurs de ses fonctions. Séparer leur résultat par type d’information permet d’y voir plus clair.

2.1 Fonctions retournant une valeur interne

Aujourdhui(), Maintenant()

Ces fonctions sont analogues et n’ont pas de paramètres.

Aujourd’hui() donne la valeur interne de la date système en nombre entier de jours écoulés depuis le jour zéro.

Maintenant() donne la valeur interne de la date-temps système en nombre entier de jours, les décimales précisant le temps.

Date (Année; Mois; Jour)

Renvoie une valeur interne en fonction des paramètres.

Année doit être exprimée entre 1583 et 9957, ou 0 et 99 (la correspondance avec des années à 4 chiffres est précisée dans les paramètres système (Outils, Options, LibreOffice, Général, ‡ Années (deux chiffres) ‡ Interpréter comme années entre ♪ …).

Si pour Mois et Jour, les valeurs indiquées sont irréelles, les composants indiqués sont recalculés avec leurs premières valeurs réelles suivantes.

Temps (Heure; Minute; Seconde)

Renvoie une valeur interne de temps à partir des composants exprimés en entiers.

Dateval (“expression”)

Renvoie une valeur interne à partir d’une chaîne de caractères.

La chaîne de caractères doit exprimer une date dans un format strict reconnu par LibreOffice (voir ci-dessus, § 1. Avant de commencer). Cette chaîne doit être mise entre guillemets doubles.

Expression ne peut être qu’une chaîne de caractères ; si Expression est une référence de cellule, une erreur 502 : Erreur de paramètres est sortie.

Tempsval (« Expression »)

Retourne une valeur interne de temps à partir d’un temps Exprimé en chaîne de caractères.

Fin.mois (Date-départ; nombre-de-mois)

Retourne la valeur interne d’une date de fin de mois, calculée à partir de la Date-départ à laquelle est ajouté le Nombre-de-mois. Nombre-de-mois peut être négatif, nul ou positif. Date-départ peut être une référence de cellule de type Date ou une constante numérique (valeur interne) ou de type Date (entre guillemets doubles).

Mois.decaler (Date-départ; Nombre-de-mois)

Renvoie la valeur interne d’une date calculée en ajoutant Nombre-de-mois à Date-départ. Le calcul ne tient pas compte des jours. Nombre-de-mois peut être négatif.

La date résultante retient le quantième jour de Date-départ, éventuellement adapté à la fin de mois comme dans l’exemple. 

Serie.jour.ouvre (Date-départ; Nombre-jours-ouvrés; Liste-jours-fériés)

Retourne la valeur interne d’une date calculée à partir d’une Date-départ, un nombre de jours ouvrés et une liste de jours fériés.

Date-départ est une valeur interne ordinaire éventuellement interprétée à partir d’une constante de type Date.

Nombre-de-jours-ouvrés est un nombre entier.

Liste-de-jours-fériés est une zone de cellules contenant chacune une valeur interne de date de jours fériés.

Il faut comprendre la formule ainsi : voici la date du n-ième jour ouvré en partant de Date-départ et en tenant compte des jours fériés indiqués dans la Liste-de-jours-fériés. Sont aussi considérés comme jours fériés, les samedis et dimanches.

Voir aussi Serie.jour.ouvre.intl().

Serie.jour.ouvre.intl (Date-départ; Nb-jours-ouvrés; [Def-WE; Liste-jours-fériés])

Retourne une valeur interne de date calculée à partir d’une Date-départ, et un Nombre-jours-fériés, compte-tenu d’une définition des jours ouvrés dans la semaine ou de la Définition des WE et d’une Liste de jours fériés.

Def-WE peut prendre deux formes : un code ou une chaîne de caractères.

Les codes possibles précisent le ou les jours composant le WE : 1 (par défaut si omis) = samedi et dimanche, 2 = Dimanche et lundi ; 3 = Lundi et mardi ; 4 = Mardi et mercredi ; 5 = Mercredi et jeudi ; 6 = Jeudi et vendredi ; 7 = Vendredi et samedi. 11 = Dimanche seul, 12 = lundi seul, etc.

La chaîne de caractères a 7 caractères, 0 (zéro) ou 1, un pour chaque jour en commençant par Lundi. 0 = jour travaillé, 1 = jour de WE. Exemple : “0000011” fait référence à une semaine travaillée du lundi au vendredi. Elle est exprimée entre guillemets doubles.

Liste-jours-fériés est une suite de cellules contenant chacune la valeur interne d’une date de jours fériés.

Cette fonction a un corollaire donnant un nombre de jours ouvrés entre deux dates : Nb.jours.ouvres.intl().

Dimanchedepaques(Année)

Retourne une valeur interne correspondant à la date du dimanche de Pâques de l’Année.

2.2 Calculs sur les dates retournant un nombre

 Datedif (Date-début; Date-fin; Param-calcul)

Retourne une différence entre deux dates. Date-fin doit être supérieur ou égal à Date-debut.

La différence égale simplement : Date-fin moins Date-debut.

Param-calcul précise la nature de la différence :

« d » : nombre entier de jours;

« m » : nombre entier de mois;

« y » : nombre entier d’années;

« md » : nombre positif entier de jours en éliminant les années et les mois ; le calcul est fait uniquement sur les composants Jours. Si Jour de Date-fin est plus petit que celui de Date-début, le système lui ajoute 30 avant de sortir la différence.

« yd » : nombre positif entier de jours en éliminant les années ; le calcul est fait comme avec le paramètre “d” en considérant que les années sont identiques, ou en ajoutant 1 à Année de Date-fin si Mois de Date-fin est inférieur à Mois de Date-début.

« ym » : nombre positif entier de mois en ne calculant la différence que sur les numéros de mois. Les Jours ne sont pas pris en compte. Si Mois de Date-fin est inférieur à celui de Date-début, 12 lui est ajouté pour obtenir une différence positive.

Annee (Valeur-interne), Mois (Valeur-interne), Jour (Valeur-interne)

À partir d’une valeur interne de date (variable ou constante, numérique ou chaîne de caractères reconnue comme date), retourne l’année (nombre), le mois (nombre de 1à 12) ou le jour (nombre de 1 à 31).

Heure (Valeur-interne), Minute(Valeur-interne), Seconde(Valeur-interne)

Retourne l’heure (de 0 à 23), la minute (de 0 à 59) ou la seconde (de 0 à 59) à partir d’une valeur interne décimale.

Jours (Date-départ; Date-arrivée)

Retourne la différence en nombre entier de jours entre une Date-départ et une Date-arrivée. Si Date-arrivée est postérieure à Date-départ, le nombre de jours sera négatif.

Voir aussi Jours360().

Jours360 (Date-début; Date-fin; [Type-calcul])

Retourne un nombre entier de jours écoulés depuis Date-début jusqu’à Date-fin dans le cadre de l’année financière, comportant 12 mois de 30 jours.

Type-de calcul : =1 si méthode américaine (NASD) par défaut si omis ; =2 si méthode européenne.

Voir aussi Jours().

Joursem (Date; Type-calcul)

Retourne le numéro du jour de Date dans la semaine en fonction du Type-calcul.

Date est une valeur interne. Type de calcul peut prendre les valeurs :

1 (par défaut si omis) : retourne 1 pour Dim. 7 pour Sam.

2 : 1 pour Lundi, 7 pour Dimanche

3 : 0 pour Lundi, 6 pour Dimanche

11 : 1 pour Lundi, 7 pour Dimanche ; 12 : 1 pour Mardi, 7 pour Lundi ; 13 : 1 pour Mercredi, 7 pour Mardi, etc.

Fraction.annee(Date-départ; Date-fin; [Type-de-calcul])

Retourne un nombre décimal (fraction) indiquant la part de l’intervalle Date-départ – Date-fin dans une année entière.

Type-de-calcul précise :

= 0 (par défaut si absent) : méthode américaine (NASD), 12 mois de 30 jours) ;

= 1 : nombre exact de jours dans les mois et l’année ;

= 2 : nombre exact de jours dans les mois, année de 360 jours ;

= 3 : nombre exact de jours dans les mois, année de 365 jours ;

= 4 : méthode européenne, 12 mois de 30 jours.

No.semaine.iso (Valeur-interne)

Retourne le numéro de semaine d’une valeur interne suivant la norme ISO. Selon la norme ISO, une semaine commence le lundi et la première semaine de l’année est celle qui contient le 4 janvier.

No.semaine.OOO(Valeur-interne)

Obsolète. Pour l’interopérabilité avec les versions LibreOffice antérieures à 5.1.0, identique à No.semaine.iso.

No.semaine(Valeur-interne, [Param])

Avec Param = 21 ou = 150 (voir ci-après), cette formule est identique à No.semaine.iso. (Système 2 de numérotation des semaines)

Sinon, cette formule utilise le système 1 pour numéroter les semaines. La semaine contenant le 1er janvier est la première semaine de l’année, et le premier jour de la semaine est variable. Param permet de préciser de premier jour selon sa valeur :

=1 ou =17 : dimanche,

=2 ou =11 : lundi,

=12 : mardi, =13 : mercredi, =14 : jeudi, =15 : vendredi, =16 : samedi.

=21 ou 150 : lundi suivant le système 2.

Si Param est omis, il est pris pour =1.

Nb.jours.ouvres.intl(Date-début; Date-fin; [Def-WE; Liste-jours-fériés])

Retourne un nombre de jours ouvrés entre une Date-début et Date-fin en tenant compte de la Définition des WE et d’une Liste-de-jours-fériés.

Date-début est le premier jour possible de jour ouvré ; Date-fin est le dernier jour possible.

Def-WE peut prendre deux formes : un code ou une chaîne de caractères.

Les codes possibles précisent le ou les jours composant le WE : 1 (par défaut si omis) = samedi et dimanche, 2 = Dimanche et lundi ; 3 = Lundi et mardi ; 4 = Mardi et mercredi ; 5 = Mercredi et jeudi ; 6 = Jeudi et vendredi ; 7 = Vendredi et samedi. 11 = Dimanche seul, 12 = lundi seul, etc.

La chaîne de caractère a 7 caractères, 0 (zéro) ou 1, un pour chaque jour en commençant par Lundi. 0 = jour ouvré, 1 = jour de WE – non ouvré. Exemple : “0000011” fait référence à une semaine travaillée du lundi au vendredi.

Liste-jours-fériés est une suite de cellules contenant chacune la valeur interne d’une date de jours fériés.

Cette fonction a un corollaire donnant une date au bout d’un certain nombre de jours ouvrés : Serie.jour.ouvre.intl().

 

LOC – 27/06/2018 – 2

Plan de la fiche

1. Avant de commencer

1.1 Le paradoxe «Formule – Texte»

1.2 formats et cadrages

1.3 Approche pédagogique

1.4 Vocabulaire et syntaxe

2. Manipulation de texte

Cherche()

Concatener()

Droite()

Epurage()

Exact()

Gauche()

Majuscule()

Minuscule()

Nompropre()

Remplacer()

Rept()

Stxt()

Substitue()

Supprespace()

T()

3. Texte vers nombre

Arabe()

Cnum()

Decimal()

Nbcar()

Trouve()

4. Nombre vers texte

Romain()

Base()

Ctxt()

Franc()

Texte()

5. Codage

Car()

Code()

Unicar()

Unicode()

Présentation

Les fonctions de texte sont particulièrement appréciées par ceux qui soignent la mise en forme de leurs tableaux. Elles sont relativement faciles à mettre en œuvre et méritent donc qu’on s’y intéresse

Préalables

Connaître la résolution d’erreur avec la fonction Sierreur() : 3.3 Composer une formule, § 5. Messages d’erreurs.

Caractères génériques et expressions régulières : 3.3 Composer une formule, § 3.3 Utiliser les caractères génériques ou les expressions régulières.

1. Avant de commencer 

1.1 Le paradoxe «Formule – Texte»

Une des grandes forces d’un tableur est de faire aussi des opérations sur les chaînes de caractères.

Je rappelle qu’une cellule destinée à recevoir une formule de calcul (fût-ce sur des textes) doit être déclarée de type Nombre ou assimilée (Date, Heure, Pourcentage, Monnaie, Fraction). C’est la première condition pour qu’une formule soit reconnue. La deuxième condition, c’est que le contenu de cette cellule commence par “=”. Une cellule de type  Texte recevant une formule (commençant par “=”) affichera la formule comme un texte sans l’interpréter.

Le paradoxe est là : déclarer une cellule Nombre pour y enregistrer une formule destinée à traiter des textes pouvant donner comme résultat une information de type Texte.

1.2 Formats et cadrages

Il existe en fait deux niveaux de formatage dans une cellule. Le niveau cellule et le niveau information. Au niveau Cellule, le format va décider la couleur du fond, la bordure, le cadrage de l’information (à droite, à gauche, au centre), la forme de l’information (police, couleur). Au niveau de l’information, le format précise la nature de l’information (Nombre, Date, Texte), sa présentation (séparateurs, décimales).

Les formules de Texte génèrent de l’information, indépendamment du format au niveau Cellule. Le format échappe en général aux formules. Néanmoins, ce formatage au niveau Cellule peut être calculé avec la fonction Formatage conditionnel. Je le présente en détail ici : 4.2 Le formatage conditionnel.

1.3 Approche pédagogique

Je présente les formules de Texte en les regroupant par type d’opérations :

• Manipulations de texte : ces formules partent d’un texte pour donner un résultat de type Texte ou donner des informations sur ce texte.
• Texte vers nombre : ces formules partent d’un texte exprimant un nombre pour générer un résultat de type Nombre.
• Nombre vers texte : ces formules transforment une information de type Nombre en un résultat de type Texte.
• Codages : ces formules retournent un ou des codes à partir d’un texte. Les formules relatives aux codages DBCS, JIS, etc. sont ignorées.

1.4 Vocabulaire et syntaxe

J’emploie indistinctement pour désigner une chaîne de caractères, les mots : Texte, Caractère, Chaîne de caractères.

Les Textes peuvent être des constantes ou des références de cellules Texte. Les constantes utilisées en paramètres des fonctions sont toujours entre guillemets doubles. Certains paramètres peuvent n’admettre que des constantes.

Les guillemets doubles s’obtiennent sur un clavier AZERTY français avec la touche « 3 ». Mais les préférences syntaxiques LibreOffice doivent préciser que ceux-ci ne doivent pas être remplacés. C’est expliqué ici : 1.1 Paramétrages de base conseillés, § 3.4 Outils.

2. Manipulations de texte

Cherche (Texte cherché; Base; [à partir de])

Retourne la position (de 1 à N) du Texte cherché dans Base. La recherche peut commencer à partir de (premier caractère (1) si omis).

Texte cherché est une séquence quelconque de caractères.

La recherche se fait indépendamment de la casse.

Si la recherche n’aboutit pas, la fonction retourne #Valeur!

Voir aussi Trouve().

Concatener (Texte-1; Texte-2; … ; Texte-30)

Retourne un Texte résultant de la mise bout à bout des Texte-1, Texte-2, etc.

Admet 30 paramètres maximum.

Résultat équivalent à Texte-1&Texte-2&…&Texte-30.

Droite (Base[; N derniers caractères])

Retourne les N derniers caractères (à droite) de Base.

N derniers caractères est un nombre entier positif, éventuellement nul ou supérieur au nombre de caractères de Base. Omis, = 1.

Base est une constante Texte entre guillemets doubles ou la référence d’une cellule quelconque.

Comparable à : Gauche().

Voir aussi Stxt().

Epurage (Base)

Retourne Base après suppression de tous les caractères non imprimables. Cette fonction garantie une impression sans anomalie ni bizarrerie.

Exact (Texte-1; Texte-2)

Compare Texte-1 et Texte-2 et retourne la valeur VRAI ou FAUX.

La casse est prise en compte. Si les deux Texte sont identiques, le résultat est VRAI, sinon, le résultat est faux.

Gauche (Base[; N premiers caractères])

Retourne le N premiers caractères (à gauche) de Base.

Base est une constante Texte entre guillemets doubles ou la référence d’une cellule quelconque.

N premiers caractères est un nombre entier positif, éventuellement nul ; si omis = 1.

Comparable à Droite().

Voir aussi Stxt().

Majuscule (Base)

Retourne Base après avoir converti les minuscules en majuscules.

Base peut être une constante Texte entre guillemets doubles ou la référence d’une cellule quelconque.

Voir aussi Minuscule(), Nompropre().

Minuscule (Base)

Retourne Base après avoir converti les majuscules en minuscules.

Base peut être une constante Texte entre guillemets doubles ou la référence d’une cellule quelconque.

Voir aussi Majuscule(), Nompropre().

Nompropre (Base)

Retourne Base après avoir converti l’initiale de chaque mot en majuscule et les autres caractères en minuscules.

Base peut être une constante Texte entre guillemets doubles ou la référence d’une cellule quelconque.

Cette forme est courante en anglais US, mais ne convient pas à la syntaxe habituelle du français.

Voir aussi Majuscule(), Minuscule().

Remplacer (Texte de départ; Position; Nb de caractères ; Texte de remplacement)

Retourne Texte de départ après avoir remplacé les Nb de caractères à partir de la Position par le Texte de remplacement.

Texte de départ est une chaîne de caractères entre guillemets doubles ou la référence d’une cellule quelconque.

Position est un nombre entier positif non nul dans Texte de départ à partir de laquelle le remplacement va se faire. Pour le premier caractère, Position = 1. Si Position est supérieur au nombre de caractères de Texte de départ, Texte de remplacement sera collé en fin de Texte de départ. Si le paramètre est incorrect, une erreur #502 est retournée.

Nb de caractères indique combien de caractères dans Texte de départ vont être supprimés à partir de Position par le Texte de remplacement. Peut être nul, dans ce cas Texte de remplacement est inséré à Position dans Texte de départ. Peut être supérieur au nombre de caractères de Texte de départ, et dans ce cas, Texte de remplacement remplace purement et simplement Texte de départ.

Rept (Base; Répétition)

Retourne un texte composé de Répétition fois Base.

Base est une constante Texte entre guillemets doubles ou la référence d’une cellule quelconque.

Répétition est un nombre entier positif éventuellement nul.

Voir aussi Substitue().

Stxt (Base; Position de départ; Nb caractères)

Extrait à partir de Position de départ, Nb caractères de Base.

Base est une constante Texte entre guillemets doubles ou la référence d’une cellule quelconque.

Position de départ est un entier positif non nul. S’il est plus grand que le nombre de caractères de Base, la fonction renvoie un texte vide.

Nb caractères est un entier positif éventuellement nul ou supérieur au nombre de caractères disponibles.

Voir aussi Droite(), Gauche().

Substitue (Base; Texte à remplacer; Texte de remplacement [; Nb fois])

Retourne Base après avoir remplacé partout ou le Nb de fois indiqué, Texte à remplacer par Texte de remplacement.

Base, Texte à remplacer et Texte de remplacement sont des constantes Texte entre guillemets doubles ou les références de cellules quelconques.

Nb de fois est un entier positif éventuellement nul. Si absent = partout.

Voir aussi Rept().

Supprespace (Base)

Retourne Base après avoir supprimé éventuellement les espaces en début ou fin de chaîne, et les espaces excédentaires entre les mots pour n’en laisser qu’un.

(Dans l’exemple, les espaces sont matérialisées par “·”)

T (Valeur)

Si Valeur est une référence de cellule de type Texte ou une constante Texte, retourne le contenu de cette cellule ou la constante. Sinon, retourne une chaîne vide.

Cette fonction permet de confirmer la nature Texte d’une information.

3. Texte vers nombre

Arabe (Base)

Retourne un nombre en chiffres arabes à partir du nombre en chiffres romains.

Base contient un nombre en chiffres romains, posé entre guillemets doubles, ou la référence d’une cellule Texte.

La fonction inverse est Romain().

Cnum (Base)

Retourne Base dans un format numérique.

Base peut être une chaîne de caractères entre guillemets doubles ou la référence d’une cellule Texte.

Si Base ne peut pas être transformé en nombre, une erreur #502 est retournée.

Decimal (Nombre; Base)

Interprète Nombre dans la Base de numérotation et retourne le nombre correspondant, de type Nombre.

Nombre peut être une constante entre guillemets doubles ou une référence de cellule Texte. La casse n’est pas prise en compte.

Base est un entier positif pouvant prendre les valeurs de 2 à 36.

Si l’interprétation n’est pas possible, une erreur #502 (Paramètre incorrect) est retournée.

En Base 16 : X, x, 0X, 0x au début ou h ou H en fin de Texte sont ignorés ; comme b ou B en base 2.

La fonction réciproque est Base().

Nbcar (Base)

Retourne le nombre de caractères de Base.

Une espace est comptée pour un caractère.

Trouve (Texte à trouver; Base [; à partir de])

Retourne la position de Texte à trouver dans Base, en commençant éventuellement la recherche à partir de.

Texte à trouver et Base sont des références de cellules quelconques, des constantes Texte entre guillemets doubles ou des Nombres. La casse est prise en compte.

Si Texte à trouver n’existe pas dans Texte de base, #VALEUR! est retournée.

Voir aussi Cherche().

4. Nombre vers texte

Romain (Nombre; [Mode])

Retourne Nombre en chiffres romains.

Nombre est un nombre exprimé en chiffres arabes ou la référence d’une cellule de type Nombre.

Mode indique comment calculer le nombre en chiffres romains. Plus la valeur est élevée, plus le nombre sera simplifié. Si omis, =0.

La fonction inverse est Arabe().

Base (Nombre; Base; Longueur minimum)

Retourne un texte exprimant Nombre dans Base.

Nombre est un nombre entier positif.

Base est le système dans lequel Nombre est à convertir : 10 pour décimal, 2 pour binaire, etc.

Longueur minimum  permet d’obtenir un nombre converti d’une longueur minimale en ajoutant si nécessaire des zéros à gauche.

La fonction réciproque est Decimal().

Ctxt (Nombre; Nb décimales [; Séparateur milliers])

Retourne un texte à partir de Nombre, avec le Nb décimales voulu, et le Séparateur milliers.

Si Séparateur milliers est omis ou =0, le paramétrage local du séparateur est respecté. Si différent de zéro, le séparateur est omis.

Voir aussi Texte().

Franc (Monnaie [; Nb de décimales])

Monnaie est une valeur de type Nombre.

Convertit Monnaie en un Texte correspondant au format de la monnaie locale, en forçant éventuellement le Nombre de décimales.

La monnaie locale est définie ici : Outils, Options, Paramètres linguistiques, Langues ‡ Langues de ‡ ♫ Monnaie par défaut.

Le séparateur décimal de Monnaie doit correspondre à celui de la langue locale.

Monnaie peut aussi être la référence d’une cellule de type Nombre.

Texte (Nombre; Format)

Transforme Nombre en Texte en suivant le Format indiqué.

Nombre est un nombre décimal quelconque, variable ou constante.

Format est la référence d’une cellule Texte ou une constante Texte. La syntaxe dépend de la langue de la cellule. Les modèles sont dans Format, Cellules… | Nombres | ‡ Catégorie ‡ Nombres…

Voir aussi Ctxt().

5. Codages

Car (Code)

Retourne le caractère correspondant au Code ASCII en fonction du mappage des caractères du système.

car(10) est le caractère de contrôle pour sauter à la ligne.

De 0 à 127, les caractères sont communs à tous les jeux de caractères (ASCII 7 bits). Au-delà, les caractères dépendent du jeu utilisé (ISO-8859-1, UTF-8, etc.).

Dans LibreOffice, le jeu de caractères utilisé en compatibilité HTML est défini ici : Outils, Options, Chargement/Enregistrement, Compatibilité HTML, ♫ Jeu de caractères.

La fonction réciproque est Code().

Code (Texte)

Retourne le code ASCII du premier caractère de Texte.

Texte peut être une constante ou la référence d’une cellule Texte.

La fonction réciproque est Car(). S’y reporter pour plus de précisions.

Unicar (Nombre)

Retourne un caractère correspondant au code Unicode de Nombre.

La fonction réciproque est Unicode().

Unicode (Texte)

Retourne le code Unicode du premier caractère de Texte.

La fonction réciproque est Unicar().

Informations complémentaires

Liens

1.1 Paramétrages de base conseillés, § 3.4 Outils

3.3 Composer une formule, § 3.3 Utiliser les caractères génériques ou les expressions régulières

3.3 Composer une formule, § 5. Messages d’erreurs

4.2 Le formatage conditionnel

 

LOC – 13/05/2018 – 2

Plan de la fiche

1. Avant de commencer

2. Fonctions courantes

2.1 Calculs

Abs(), Exp(), Fact(), Mod()

Produit(), Puissance(), Quotient(), Racine()

Signe()

Somme()

2.2 Troncatures et arrondis

Arrondi(), Arrondi_precision(), Arrondi.au.multiple(), Arrondi.inf(), Arrondi.sup()

Ent(), Tronque()

Impair(), Pair()

Iso.plafond(), Plafond(), Plafond.math(), Plafond.precis()

Plancher(), Plancher.math(), Plancher.precis()

2.3 Autres

Alea()

Alea.entre.borne()

Pgcd()

Ppcm()

3. Fonctions orientées base de données et statistiques

Somme.si()

Somme.si.ens()

Grande.valeur(), Petite.valeur()

Max(), Maxa(), Max.si()

Min(), Mina(), Min.si()

Mode(), Mode.multiple(), Mode.simple()

Moyenne(), Moyennea(), Moyenne.si(), Moyenne.si.ens()

Nb(), Nb.si(), Nb.si.ens()

Nb.vide(), Nbval()

Rang()

4. Fonctions exotiques

Couleur()

Euroconvert()

Présentation

Les fonctions mathématiques sont nombreuses. J’ignore ici les fonctions trigonométriques, statistiques et probabilités, binaires (bit à bit – logique informatique) et financières. Ces fonctions sont spécialisées et largement parlantes pour ceux qui veulent les utiliser.

Les fonctions courantes sont regroupées en trois paquets : calculs, troncatures et arrondis, autres.

Un paragraphe est consacré aux fonctions orientées base de données et statistiques, et un autre aux fonctions que je qualifie d’exotiques, amusantes à découvrir.

1. Avant de commencer

Les formules mathématiques n’ont pas nécessairement recours aux fonctions. Il est possible de construire des formules en utilisant des constantes ou des variables, et les opérateurs + – * / ^. Avec ou sans séparateurs (parenthèses).

2. Détail des fonctions courantes

2.1 Calculs

Abs(Nombre)

Retourne la valeur absolue d’un nombre, c’est-à-dire pour un nombre négatif, sa valeur positive.

Exp(Exposant)

Retourne la valeur e^Exposant

Fact(Nombre)

Retourne la factorielle de Nombre.

Mod(Dividende; Diviseur)

Retourne le reste de la division entière de Dividende par Diviseur.

Identique à Quotient().

Produit(Liste de nombres)

Retourne le produit des Nombres de la Liste. 30 arguments sont possibles. Une zone de cellules (références extrêmes en diagonale séparée par “:”) peut être posée en argument, ainsi qu’une zone nommée.

Comparable à Somme().

Puissance(Base; Exposant)

Équivalent à Base^Exposant.

Voir aussi Exp().

Quotient(Dividende; Diviseur)

Retourne le reste de la division entière de Dividende par Diviseur.

Identique à Mod().

Racine(Nombre)

Retourne la racine carrée de Nombre.

Voir Racine.pi().

Signe(Nombre)

Renvoie le signe de Nombre : -1, si négatif; 1, si positif; 0 si Nombre est nul.

Somme(Liste de nombres)

Retourne la somme des Nombres de la liste. 30 arguments sont possibles. Une zone de cellules (références extrêmes en diagonale séparée par “:”) peut être posée en argument, ainsi qu’une zone nommée.

Ressemble à Produit() ; voir aussi Somme.si().

2.2 Troncatures et arrondis

Arrondi(Base [, Nombre de décimales])

Renvoie Base en ne gardant que le Nombre de décimales arrondies au plus près.

Si Nombre de décimales est omis, il est pris pour 0.

Voir aussi : Arrondi_precision(), Arrondi.au.multiple, Arrondi.inf(), Arrondi.sup().

Arrondi_precision(Base; Niveau)

Niveau est le nième chiffre de Base, en partant de la gauche, qui supporte l’arrondi, décimales comprises (sans compter le séparateur).

Fonction intéressante pour avoir des nombres ronds quand le nombre de chiffres devant la virgule est connu.

Voir aussi : Ent(), Arrondi(), Arrondi.au.multiple, Arrondi.inf(), Arrondi.sup().

Arrondi.au.multiple(Base; Arrondi)

Retourne Base arrondie au plus près avec Arrondi.

L’intitulé de la fonction, dans les deux premiers exemples, pourrait être Arrondi au cent près.

Comparée à Arrondi_precision(), cette fonction permet un arrondi identique dans une liste de nombres quel que soit le nombre de chiffres devant la virgule de chacun.

Voir aussi Arrondi(), Arrondi_precision(), Arrondi.inf(), Arrondi.sup().

Arrondi.inf(Base; Nombre de décimales)

Retourne Base avec Nombre de décimales. La dernière décimale n’est pas modifiée. Arrondi dit « par défaut », « vers le bas » ou « vers zéro ».

Voir aussi : Arrondi(), Arrondi_precision(), Arrondi.au.multiple, Arrondi.sup().

Arrondi.sup(Base; Nombres de décimales)

Retourne Base avec Nombre de décimales arrondi par excès.

Voir aussi : Arrondi(), Arrondi_precision(), Arrondi.au.multiple, Arrondi.inf().

Ent(Nombre)

Transforme Nombre en entier arrondi par défaut.

Identique à Arrondi(Nombre; 0).

Impair(Nombre)

Si Nombre est positif, retourne le premier entier impair plus grand que  Nombre (ou égal à Nombre si celui-ci est un entier impair positif).

Si Nombre est négatif, retourne le premier entier impair négatif plus petit que Nombre (ou égal à Nombre si celui-ci est un entier impair négatif).

Comparable à Pair().

Iso.plafond(Nombre; Multiple)

Renvoie un multiple de Multiple immédiatement supérieur à Nombre, quel que soit le signe de Nombre.

Pair(Nombre)

Si Nombre est positif, retourne le premier entier pair supérieur à Nombre (ou égal à Nombre si celui-ci est un entier pair positif).

Si Nombre est négatif, retourne le premier entier pair négatif inférieur à  Nombre (ou égal à Nombre si celui-ci est un entier pair négatif).

Comparable à Impair().

Plafond(Nombre; Multiple [; Mode])

Retourne le Multiple immédiatement supérieur à Nombre suivant le Mode indiqué (Multiple par excès).

Nombre et Multiple doivent être de même signe (positifs ou négatifs tous les deux ; sinon, une erreur #502 est retournée).

Mode est une valeur logique (FAUX, 0 ou omis ; VRAI, 1 ou toute autre valeur positive) qui n’est prise en compte que si Nombre et Multiple sont négatifs. FAUX retourne alors le multiple le plus proche de zéro (plus grand que Nombre) ; VRAI retourne le multiple le plus loin de zéro (plus petit que Nombre).

Voir Plafond.math(), Plafond.precis(), Plancher(), Plancher.math, Plancher.precis.

Plafond.math(Nombre; Multiple [;Mode])

Fonction identique à Plafond().

Voir Plafond(), Plafond.precis(), Plancher(), Plancher.math(), Plancher.precis().

Plafond.precis(Nombre; Multiple)

Fonction comparable à Plafond() avec cette nuance que Nombre et Multiple peuvent être de signe différent, et que Multiple est toujours interprété comme étant positif.

Voir Plafond(), Plafond.math(), Plancher(), Plancher.math(), Plancher.precis().

Plancher(Nombre; Multiple; Mode)

Fonction analogue à Plafond() avec cette nuance que le résultat est le Multiple le plus près de zéro (Multiple par défaut).

Voir Plafond(), Plafond.math(), Plafond.precis(), Plancher.math(), Plancher.precis().

Plancher.math(Nombre; Multiple; Mode)

Fonction identique à Plancher().

Voir Plafond(), Plafond.math(), Plafond.precis(), Plancher(), Plancher.precis().

Plancher.precis(Nombre; Multiple)

Fonction analogue à Plancher() avec cette nuance que Nombre et Multiple peuvent être de signe différent, et que Multiple est toujours interprété comme étant positif.

Voir Plafond(), Plafond.math(), Plafond.precis(), Plancher(), Plancher.math().

Tronque(Base; Nombre de décimales)

Transforme Base en ne laissant que le Nombre de décimales, sans arrondi.

Les chiffres non significatifs ne sont pas ajoutés aux décimales.

2.3 Autres

Alea()

Renvoie un nombre aléatoire entre 0 et 1. Il suffit de le multiplier par une puissance de 10 pour avoir un nombre à plusieurs chiffres, et d’arrondir le résultat.

Voir aussi : Alea.entre.bornes().

Alea.entre.bornes(Minimum; Maximum)

Renvoie un nombre aléatoire entre et y compris Minimum et Maximum.

Minimum et Maximum sont deux nombres quelconque.

Alea.entre.bornes (0; 1) est identique à Alea().

Pgcd(Liste de nombres)

Retourne le plus grand commun diviseur des Nombres de la Liste. 30 nombres maximum peuvent être mis en paramètres.

Comparable à Produit(), Somme().

Voir aussi Ppcm().

Ppcm(Liste de nombres)

Retourne le plus petit commun multiple des Nombres de la Liste. 30 nombres maximum peuvent être mis en paramètres.

Comparable à Produit(), Somme().

Voir aussi Pgcd().

3. Fonctions orientées base de données et statistiques

Calc classe (dans l’assistant fonction et dans l’aide) les fonctions Somme.si() et Somme.si.ens() dans les fonctions mathématiques, et les autres fonctions décrites dans ce paragraphe dans les fonctions statistiques.

Somme.si (Plage de recherche; Valeur recherchée; Valeur additionnée)

Voilà une fonction particulièrement puissante.

Plage de recherche est une colonne servant de de référence dans un tableau quelconque.

Valeur recherchée est une valeur unique qui va être cherchée dans la Plage de recherche. Ce peut être une variable ou une constante. La constante peut utiliser les caractères génériques (voir ici : 3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières).

Valeur additionnée est une colonne du tableau quelconque dans laquelle se trouve les éléments à additionner.

La fonction balaie toute la colonne Plage de recherche. Quand elle trouve une ligne portant la Valeur recherchée, elle additionne la valeur de la colonne Valeur additionnée présente sur la même ligne. Et continue la recherche jusqu’à la fin de la Plage de recherche.

Un bon exemple de l’utilisation de cette fonction est dans la colonne Stock de la première feuille de MaCave. Le stock est calculé en ajoutant les entrées et en soustrayant les sorties à partir d’une concordance des lignes sur la référence du lot (première colonne dans les trois tableaux).

Cette fonction est classée par Calc dans les fonctions mathématiques

Voir aussi : Somme.si.ens().

Somme.si.ens(Zone à totaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Voilà une extension de la fonction Somme.si().

127 couples Zone à tester, Test sont possibles.

Zone à totaliser est une colonne dans laquelle se trouve des montants que la fonction va totaliser en fonction des critères qui suivent. Il faut que tous les tests de la fonction soient VRAI pour que la somme soit effectuée. Ce qui revient à dire que la fonction cesse ses explorations dès qu’un test sort FAUX.

Les zones Zone à totaliser, Zones à tester doivent être de dimensions identiques, sinon l’erreur #502 est retournée. Elles peuvent être en colonnes ou en lignes.

Test x peut être une référence de cellule : dans ce cas, si la valeur de cette cellule est trouvée dans la Zone à tester x à la position y, le test retourne VRAI et la somme située à la position y de Zone à totaliser est ajouté au résultat de la fonction.

La cellule référencée peut contenir une expression conditionnelle analogue à celle du Test x.

Test x peut contenir une expression conditionnelle (constante de texte ou concaténation de plusieurs expressions de type Texte). Cette expression est de la forme : opérateur de comparaison (en constante de texte entre guillemets doubles) suivi du critère de comparaison (constante avec ou sans fonctions retournant du texte). La constante peut intégrer des caractères génériques. Voir ici : 3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières).

Voici un exemple dans une application de trésorerie domestique. La cellule qui contient la formule ci-dessous récupère toutes les dépenses d’une catégorie donnée ($A2) antérieures ou égales à une date donnée (TBB_date_fin).

La formule contient trois parties : la colonne dans laquelle se trouvent les montants à totaliser sous conditions (CPT1_liste_Dep) et deux ensembles zone à tester et test : sur les catégories (CPT1_liste_Categ; $A2) et sur les dates (CPT1_liste_Dates; « <= »&TBB_date_fin).

Concernant la zone des montants à totaliser sous conditions, je note qu’elle se situe sur une autre feuille que ma formule (CPT1). Il en sera de même pour les zones à tester.

Concernant le test sur les catégories, je note que la catégorie à tester ($A2) est sur la même feuille que la formule. En fait, cette formule est dans un tableau (CALC) qui récupère les montants réels par catégories pour les comparer aux budgets.

Concernant le test sur les dates, je note que la condition exploite une zone nommée (TBB_date_fin) récupérée dans le tableau de bord (TBB) qui rassemble les paramètres de l’analyse budgétaire.

Comment fonctionne la formule ?

Le système commence par mettre à zéro le cumul calculé. Il balaie ensuite le tableau CPT1 dans la colonne Catégorie. Quand il trouve une catégorie égale à celle présente sur la même ligne que la formule (je note que la référence ligne (2 dans A2) est relative), il s’arrête sur la ligne et passe au test suivant. Je note cette ligne : ligne Y.

Dans la zone CPT1_Listes_Dates, le système teste la date de la ligne Y en regard de TBB_fin_mois. Si la date est effectivement inférieure ou égale, il prend le montant dans la zone CPT1_Liste_Dep, à la ligne Y et le cumule dans le total de la formule. Si la date est supérieure, il ne fait rien et revient au balayage du tableau CPT1 dans la colonne Catégorie et passe à la ligne suivante (Y+1).

En fin de colonne, il renvoie le cumul fait comme résultat de la formule.

De la sorte, et quelle que soit la position de la Catégorie dans le tableau CPT1, les sommes correspondantes sont rassemblées en un montant si les dates d’opération sont inférieures ou égales à celle calculée en argument. Et ce montant est le résultat de la formule.

Voir aussi Somme.si().

Grande.valeur (Plage de nombres; Rang)

Renvoie la plus grande valeur de rang Rang (la première plus grande, la deuxième plus grande, etc.) trouvée dans Plage de nombres, .

Comparable à Petite.valeur().

Max (Nombre1; Nombre2; …; Nombre30)

Renvoie le NombreX le plus grand, ou 0 si les NombreX appellent des cellules de type Texte. Si un des arguments est une chaîne de caractères, renvoie l’erreur 504.

Trente arguments sont possibles, et un argument peut être une zone de cellules extrêmes séparées par “:”.

Comparable à Min(), Moyenne(), Produit(), Somme().

Voir aussi Maxa(), Max.si().

Maxa (Argument 1; Argument 2; …; Argument 30)

Retourne l’argument maximum.

Si un argument est de type Texte, il est pris pour 0.

Si aucun argument n’est de type Texte, identique à Max().

Max.si (Zone à maximaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Comparable à Somme.si.ens().

Les Zones (à maximaliser, à tester X) doivent être de mêmes dimensions sinon Err.502 est retournée.

Test X accepte, en fonction des paramétrages système, les caractères génériques et les expressions régulières.

Les valeurs de type Texte sont prises pour 0.

Comparable à Min.si(), Moyenne.si().

Min (Nombre1; Nombre2; …; Nombre30)

Comparable à Max().

Mina (Argument 1; Argument 2; …; Argument 30)

Comparable à Maxa().

Min.si (Zone à minimaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Comparable à Max.si().

Mode (Argument 1; Argument 2; …; Argument 30)

Renvoie l’Argument X le plus fréquent de la liste des arguments.

Si tous les arguments sont différents, renvoie l’erreur #Valeur! Voir Sierreur() pour s’en sortir.

Si plusieurs arguments ont la même fréquence, renvoie le plus petit.

Voir Mode.multiple(), Mode.simple().

Mode.multiple (Argument 1; Argument 2; …; Argument 30)

Fonction de matrice. Préférer Mode.simple().

Mode.simple (Argument 1; Argument 2; …; Argument 30)

Identique à Mode().

Moyenne (Nombre1; Nombre2; …; Nombre30)

Comparable à Min(), Max(), Moyenne, Produit(), Somme().

Si Nombre X contient une chaîne de caractères, il est ignoré de la liste (dans la somme et le nombre).

Voir aussi Moyennea().

Moyennea (Nombre1; Nombre2; …; Nombre30)

Par rapport à Moyenne() : si un Nombre X contient du texte, il est pris pour 0 et compté dans le dénominateur.

Comparable à Mina(), Maxa().

Moyenne.si (Plage de recherche; Valeur recherchée[; Valeur additionnée])

Comparable à Somme.si().

Si Valeur additionnée est omise, la Plage de recherche est prise comme zone de Valeurs additionnée.

Si une cellule dans la zone Valeur additionnée est vide ou contient du texte, elle est ignorée (dans la somme et le nombre).

Si Valeur recherchée n’est pas trouvée, renvoie l’erreur #Div/0!.

Voir aussi Moyenne.si.ens().

Moyenne.si.ens (Zone à totaliser; Zone à tester 1; Test 1; Zone à tester 2; Test 2; etc.)

Comparable à Somme.si.ens().

Si une cellule de Zone à totaliser est vide ou contient du texte, elle est ignorée.

Si une cellule de Zone à totaliser contient la valeur VRAI, elle est prise pour 1; si elle contient la valeur FAUX, elle est prise pour 0.

Si les zones sont de dimensions différentes, une erreur Err. 502 est retournée. 

Nb (Argument 1; Argument 2; …; Argument 30)

Renvoie le nombre d’arguments de type non-texte.

Voir aussi Nb.si(), Nb.si.ens().

Nb.si (Plage de recherche; Valeur recherchée)

Ressemble à Somme.si().

Renvoie le nombre d’éléments de la Plage de recherche satisfaisant au critère Valeur recherchée.

Valeur recherchée admet les expressions régulières.

Nb.si.ens (Zone à tester 1; Test 1; Zone à tester 2; Test 2; …; Zone à tester 127; Test 127)

Ressemble à Somme.si.ens().

Nb.vide (Plage de recherche)

Renvoie le nombre de cellules vides de la Plage de recherche.

Une cellule vide est une cellule sans information. Une cellule nulle contient l’information 0.

Voir aussi Nbval().

Nbval (Plage de recherche)

Renvoie le nombre de cellules non vides de la Plage de recherche.

Voir aussi Nb.vide().

Petite.valeur (Plage de nombres; Rang)

Comparable à Grande.valeur().

Rang (Valeur cherchée; Zone de recherche [; Tri])

Renvoie le rang dans Zone de recherche, de Valeur recherchée. Si Tri = 1, la zone de recherche est parcouru naturellement du premier au dernier élément. Si Tri =0 (valeur par défaut), la Zone de recherche est parcouru du dernier élément au premier.

La fonction retourne le rang du premier élément trouvé.

Si Valeur recherchée n’existe pas dans Zone de recherche, #Valeur! est retourné. Voir Sierreur() pour s’en sortir au mieux.

4. Fonctions exotiques

Couleur(Rouge; Vert; Bleu [; Alpha])

Retourne un nombre correspondant aux couleurs entrées. Ce nombre en base décimale peut ensuite être traduit en base hexadécimale pour correspondre aux couleurs HTML.

Pour les accros des présentations colorées, permet de faire des tests sur les couleurs.

Euroconvert(Valeur; De monnaie; Vers monnaie; Précision)

Cette fonction permet de convertir Valeur exprimée en De monnaie dans la monnaie Vers monnaie. De monnaie et Vers Monnaie doivent correspondre aux monnaies remplacées par l’Euro au moment de sa mise en place. Elles sont exprimées avec leur code ISO 4217 (EUR, FRF, DEM, etc.). Précision indique si la transformation comporte toutes les décimales ou seulement celles de Vers monnaie. Précision omis est pris pour 0.

Utile en son temps, cette fonction est devenue quasiment obsolète.

Informations complémentaires

Liens

3.3 Composer une formule, §3.3 Utiliser les caractères génériques ou les expressions régulières

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 :

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.

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 :

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