Formules et fonctions de calcul
Introduction
La force du tableur est de recalculer automatiquement les résultats lorsqu'une donnée change. On utilise des formules et des fonctions prédéfinies.
I. Les formules
Une formule combine des références, des valeurs et des opérateurs :
| Opérateur | Signification | Exemple |
|---|---|---|
| + − | Addition, soustraction | =B2+C2 |
| * | Multiplication | =B2*C2 |
| / | Division | =B2/C2 |
| ^ | Puissance | =B2^2 |
| & | Concaténation de textes | =A2&" "&B2 |
Les priorités mathématiques s'appliquent ; les parenthèses modifient l'ordre de calcul.
II. Les références relatives et absolues
- Référence relative (ex. B2) : elle s'adapte lorsqu'on recopie la formule ;
- Référence absolue (ex.
$B$2) : elle reste fixe lors de la recopie. On l'utilise pour une valeur unique comme un taux de TVA placé dans une cellule ; - Référence mixte :
$B2(colonne fixe) ouB$2(ligne fixe).
La touche F4 permet de passer d'un type de référence à l'autre.
Exemple : le taux de TVA est en F1 ; la TVA de la ligne 4 se calcule par =D4*$F$1 ; recopiée vers le bas, la formule devient =D5*$F$1, =D6*$F$1…
III. Les fonctions de base
Syntaxe : =NOM(arguments).
| Fonction | Rôle | Exemple |
|---|---|---|
| SOMME | Additionne | =SOMME(B2:B10) |
| MOYENNE | Moyenne arithmétique | =MOYENNE(C2:C20) |
| MAX / MIN | Plus grande / plus petite valeur | =MAX(D2:D30) |
| NB | Compte les cellules contenant des nombres | =NB(B2:B50) |
| NBVAL | Compte les cellules non vides | =NBVAL(A2:A50) |
| ARRONDI | Arrondit à n décimales | =ARRONDI(E2;2) |
| AUJOURDHUI | Date du jour | =AUJOURDHUI() |
Dans la version française, les arguments sont séparés par un point-virgule (;).
IV. Les messages d'erreur
| Erreur | Cause |
|---|---|
| #DIV/0! | Division par zéro |
| #NOM? | Nom de fonction mal écrit |
| #VALEUR! | Type de donnée incorrect (texte dans un calcul) |
| #REF! | Référence à une cellule supprimée |
| ###### | Colonne trop étroite pour afficher la valeur |
V. Applications en gestion
- Facture : montant HT $=$ quantité × prix unitaire ; TVA $=$ HT × taux ; TTC $=$ HT + TVA ;
- Tableau d'amortissement : annuité $=$ VO × taux ; cumul ; VNA ;
- État de paie : salaire brut, cotisations, salaire net.
Exercice 1 — Facture
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Taux TVA | 20 % | ||||
| 3 | Article | Quantité | Prix unitaire | Montant HT | TVA | TTC |
| 4 | Chaises | 10 | 450 | |||
| 5 | Tables | 4 | 1 200 | |||
| 7 | Total |
Écrivez les formules des cellules D4, E4, F4 (recopiables vers le bas) et D7.
Voir le corrigé
- D4 : =B4*C4
- E4 :
=D4*$F$1(référence absolue au taux) - F4 : =D4+E4
- D7 : =SOMME(D4:D5)
Résultats : D4 = 4 500 ; E4 = 900 ; F4 = 5 400 ; D5 = 4 800 ; D7 = 9 300.
Exercice 2 — Statistiques d'une classe
Les notes de 30 élèves sont en C2:C31. Écrivez les formules pour obtenir : la moyenne, la note la plus haute, la note la plus basse, le nombre de notes saisies, la moyenne arrondie à 2 décimales.
Voir le corrigé
=MOYENNE(C2:C31) ; =MAX(C2:C31) ; =MIN(C2:C31) ; =NB(C2:C31) ; =ARRONDI(MOYENNE(C2:C31);2).
L'essentiel — Formules et fonctions
- Opérateurs : + − * / ^ & ; parenthèses pour les priorités.
- Relative (B2) s'adapte ; absolue (
$B$2) reste fixe ; mixte ($B2,B$2) ; touche F4. - Fonctions : SOMME, MOYENNE, MAX, MIN, NB, NBVAL, ARRONDI, AUJOURDHUI ; séparateur ;.
- Erreurs : #DIV/0!, #NOM?, #VALEUR!, #REF!, ######.
- Applications : facture (HT, TVA, TTC), amortissements, paie.