LibreOffice Calc – Les formules

Gregory Trolliet

5 octobre 2020

Présentation

Vos questions précises

Notions de base

Définition

  • Classeur
    Document qui contient plusieurs feuilles
  • Feuille de calcul
    Conteneur de cellules
  • Cellule
    Données à afficher et à manipuler
    Formules

Référence

  • C7
  • B5:D12
  • $MaFeuille.E2
  • $AutreFeuille.E2:E9
  • ‘file:///home/raghnarok/exo_1.ods’#$SaFeuille.E2:E9
    Édition→Lien vers des fichiers externes→Actualiser

Référence absolues

Permet de s’assurer que la cellule cible ne soit pas modifiée lors du déplacement, de l’insertion de nouvelles lignes ou colonnes ou lors de copie.

  • $C7 Colonne
  • D$12 Ligne
  • $E$7

Nommer des cellules

Intérêt:

  • Simplifier les formules
  • Ne pas avoir à se rappeler de la position
  • Permettre de modifier une référence sans devoir modifier toutes les formules

Attention, ne fonctionne pas entre différents classeurs.

Feuille→Plages et expressions nommées→Gérer

Exercices

Entrainez-vous à accéder correctement à des cellules ainsi qu’à les nommer.

Formats

Nombres

  • Différents formats complets:
    • 1.15
    • -0.456
  • Formats spéciaux:
    • 15%
    • CHF 14.50
    • 22/7

Heures

  • 09:30
  • 09:30:15
  • 09:30:15.179

Dates

  • 27.11.1987

Stockage

Tout est enregistré comme un nombre!

Plage de validité

Possibilité de définir une liste de valeurs possibles pour une cellule.
Données→Validité…

Éviter à l’utilisateur / utilisatrice de se tromper

Exercices

  • Expérimenter les différents formats d’affichage de Calc
  • Modifier la plage de validité d’une ou plusieurs cellules

Les opérateurs

Opérateurs arithmétiques

  • + Addition =1+1
  • ‒ Soustraction =2–1
  • * Multiplication =2*2
  • / Division =10/5
  • % Pourcentage =15%
  • ^ Exponentiation =2^3

Respectent l’ordre des opérations

Opérateurs logiques

  • = Égal A1=B1
  • > Supérieur à A1>B1
  • < Inférieur à A1<B1
  • >= Supérieur ou égal à A1>=B1
  • <= Inférieur ou égal à A1<=B1
  • <> Non égal A1<>B1

Concaténation de texte

  • & Concatène le texte =B2 & “,” & B3 & ” ” & B5

Opérateurs de plage

  • A2:B4 Plage de cellules
  • B:B Colonne entière
  • B:D Plusieurs colonnes
  • 17:17 Ligne entière
  • 5:17 Plusieurs lignes
  • Feuille1.A3:Feuille3.D4 Plage tridimensionnelle

Les formules

Nommage

Attention, Calc traduit le nom des formules.

Possibilité de remettre en anglais si désiré:
Outils → Options → LibreOffice Calc → Formule → Utiliser les noms de fonction anglais

Fonctionnement

  • Nom, en majuscules
  • Paramètre·s
  • Valeur de retour

Les fonctions usuelles

  • Somme
  • Moyenne
  • Médiane
  • Produit

=SOMME(A1:A3)

=MOYENNE(A1,A3)

Les fonctions spécifiques

  • Arrondi =ARRONDI(Nombre, précision)
  • Troncation =TRONQUE(Nombre, précision)
  • Année =ANNEE(Date)
  • Année bissextile? =ESTBISSEXTILE(Date)
  • …

Les fonctions matricielles

  1. Sélectionner une plage de la même taille que la source
  2. Entrer la formule
  3. Valider avec ctrl+shift+enter

Économise du temps de calcul et de l’espace disque

Les fonctions logiques

  1. Si
    =SI(Test, Valeur si vrai, Valeur si faux)
  2. Si avec plusieurs conditions
    =SI.CONDITIONS(Test1, Valeur1, Test2, Valeur2, …)
  3. Si avec plusieurs valeurs
    =SI.MULTIPLES(Expression, Valeur1, Résultat1, Valeur2, Résultat2, …)

Les fonctions de consolidation

  • Somme conditionnelle =SOMME.SI(Plage, critère, plage somme)
  • Somme de produits =SOMMEPROD(Plage1, Plage2)
  • Somme de produits conditionnelle
    =SOMMEPROD(Plage1, Plage2, Plage3=“pomme”)

Exercice

Calculer le nombre de kilos de chaque fruit commandé chaque mois. Pensez à décomposer le calcul.

Les erreurs

Erreurs courantes

  • #DIV0! → division par zéro → tester les valeurs
  • #NUM! → dépassement de la valeur maximum
  • #REF! → référence inexistante → feuille supprimée?

Exercice

Numérotation de lignes

  1. Numéroter les lignes automatiquement, même si on rajoute une ligne au milieu.
  2. Numéroter uniquement les lignes ayant des données.

Les fonctions conditionnelles

Assistant Fonction

Pensez à utiliser l’assistant!

Liste

  • MOYENNE.SI
  • NB.SI
  • SOMME.SI
  • MAX.SI
  • …

Les recherches et filtres

Les filtres

  • AutoFiltre: Données→AutoFiltre
  • Filtre standard: Données→Plus de filtres→Filtre standard…
  • Filtre spécial, intérêt uniquement pour des usages spécifiques

Recherche

La fonction RECHERCHE nécessite une liste triée.

=RECHERCHE(Critère, Vecteur recherche, Vecteur résultat)

RECHERCHEV et RECHERCHEH permettent de chercher dans la première ligne d’une matrice, de spécifier quelle sera la ligne de résultat et de travailler avec une liste non triée.

=RECHERCHEV(Critère, Matrice, Indice[, Triée])

Valeur cible

La valeur cible permet de trouver une solution à une équation en variant un unique paramètre.

Outils→Recherche de valeur cible

Le solveur permet de faire varier plusieurs paramètres.

Outils→Solveur

Tableau dynamique

Permet de faire du tri et des calculs automatiquement.

Données→Table dynamique→Insérer ou éditer

Questions?