Les formules et les fonctions
Ce que ce chapitre apporte
- Écrire une formule et employer les opérateurs de calcul dans le bon ordre de priorité.
- Distinguer une référence relative d'une référence absolue, et choisir laquelle employer.
- Employer les fonctions de base : SOMME, MOYENNE, MAX, MIN, NB, NBVAL.
- Recopier une formule sur toute une colonne et prévoir ce qui s'y décale.
- Identifier un message d'erreur et remonter à sa cause.
Une formule commence toujours par =
Une formule est un calcul écrit dans une cellule. Elle commence obligatoirement par le signe =, et la cellule affiche alors son résultat, pas son texte.
C'est la règle qui n'admet aucune exception : sans le =, rien n'est calculé. La cellule affiche simplement ce qu'on a tapé.
| Opérateur | Rôle | Exemple |
|---|---|---|
+ | addition | =A1+B1 |
- | soustraction | =A1-B1 |
* | multiplication | =A1*B1 |
/ | division | =A1/B1 |
^ | puissance | =A1^2 |
& | mettre bout à bout du texte | =A1&" "&B1 |
Les priorités sont celles des mathématiques : la multiplication avant l'addition, et les parenthèses au-dessus de tout.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Valeur A | Valeur B | Opération | Résultat |
| 2 | 10 | 4 | addition | 14 |
| 3 | 10 | 4 | soustraction | 6 |
| 4 | 10 | 4 | multiplication | 40 |
| 5 | 10 | 4 | division | 2,5 |
| 6 | 10 | 4 | sans parenthèses | 18 |
| 7 | 10 | 4 | avec parenthèses | 28 |
| 8 |
D6 contient la formule =A6+B6*2, et affiche 18. Les cellules qu'elle utilise sont teintées.
=A1+B1 plutôt que =10+4=10+4 fige les nombres à l'intérieur du calcul. Si la valeur change, il faut rouvrir la formule, et le faire dans chaque ligne où elle apparaît.=A1+B1 désigne des cases. Changer A1 suffit, et tout ce qui dépend du résultat se met à jour en cascade.La règle qui en découle est presque absolue : ne jamais taper dans une formule un nombre qui figure déjà quelque part dans la feuille. Un taux de TVA, un taux de change, un prix unitaire : chacun mérite sa cellule, et les formules la désignent.
Les fonctions
Une fonction est un calcul prêt à l'emploi. On lui donne des arguments entre parenthèses, séparés par des points-virgules.
Une plage désigne un bloc de cellules par ses deux coins, séparés par deux points : A1:A5 va de A1 à A5, B2:D4 désigne un rectangle de neuf cellules.
| Fonction | Rôle |
|---|---|
=SOMME(A1:A5) | additionne toute la plage |
=MOYENNE(A1:A5) | moyenne des valeurs numériques |
=MAX(A1:A5) / =MIN(A1:A5) | la plus grande, la plus petite |
=NB(A1:A5) | combien de nombres dans la plage |
=NBVAL(A1:A5) | combien de cellules non vides |
=ARRONDI(A1;2) | arrondit à deux décimales |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Élève | Note | Statistiques | |
| 2 | Amina | 14 | Somme | 65 |
| 3 | Bastien | 8 | Moyenne | 13 |
| 4 | Chloé | 17 | Maximum | 17 |
| 5 | Dorian | absent | Minimum | 8 |
| 6 | Elias | 11 | Nombre de notes | 5 |
| 7 | Fatou | 15 | Nombre d'élèves | 6 |
| 8 |
D3 contient la formule =MOYENNE(B2:B7), et affiche 13. Les cellules qu'elle utilise sont teintées.
NB et NBVAL, le contrôle en une secondeNB compte les nombres, NBVAL compte les cellules non vides. Sur une colonne censée ne contenir que des nombres, les deux doivent donner le même résultat.
S'ils diffèrent, l'écart est exactement le nombre de cases qui contiennent du texte, et c'est là qu'il faut regarder. C'est le contrôle le plus rapide qui existe pour attraper le total faux du chapitre 2.
Ici l'écart vaut 1, et il correspond au « absent » de Dorian, ce qui est voulu. Sur une colonne de montants, il ne le serait pas.
Références relatives et absolues
C'est la notion la plus importante du chapitre, et la seule qui demande vraiment de s'arrêter.
Relative (A1) : quand la formule est recopiée ailleurs, la référence se décale d'autant. =A1+B1 recopiée une ligne plus bas devient =A2+B2.
Absolue ($A$1) : les $ verrouillent. Recopiée n'importe où, la référence continue de désigner A1.
Mixte (A$1 ou $A1) : un seul des deux est verrouillé. A$1 garde la ligne 1 mais change de colonne ; $A1 garde la colonne A mais change de ligne.
$ se lit « ne bouge pas »Le symbole se place devant ce qu'il verrouille. Dans $B$1, le premier $ verrouille la colonne B, le second verrouille la ligne 1.
Un moyen de ne plus hésiter : lire la référence à voix haute en disant « ne bouge pas » à chaque $. $B1 se lit « colonne B ne bouge pas, ligne 1 libre ».
Et pour éviter de les taper : sélectionner la référence dans la barre de formule et appuyer sur F4. La touche fait tourner les quatre combinaisons, B1, $B$1, B$1, $B1, puis revient au début.
Dans la feuille ci-dessous, le taux de TVA est en B1. Sélectionner C4, puis appuyer plusieurs fois sur « recopier vers le bas » et regarder ce que devient la formule à chaque ligne.
| A | B | C | |
|---|---|---|---|
| 1 | Taux de TVA | 1,2 | |
| 2 | |||
| 3 | Article | Prix HT | Prix TTC |
| 4 | Clavier | 25,9 | 31,08 |
| 5 | Souris | 12,5 | |
| 6 | Écran | 149 | |
| 7 | Casque | 79,9 | |
| 8 |
C4 contient la formule =B4*$B$1, et affiche 31,08. Les cellules qu'elle utilise sont teintées.
$=B4*B1, la recopie vers le bas l'aurait transformée en =B5*B2, puis =B6*B3.La deuxième multiplierait le prix par une cellule vide, c'est-à-dire par zéro, et donnerait un prix TTC de 0. La troisième multiplierait par le mot « Article », et donnerait
#VALEUR!.Le premier cas est le plus dangereux des deux : zéro n'est pas un message d'erreur. Une colonne pleine de zéros passe pour un problème de données, et l'on cherche pendant une heure ce qui tient à un
$ manquant.Le réflexe : dès qu'une formule désigne une cellule unique qui sert à toute une colonne, la verrouiller.
La recopie
Le double-clic sur cette poignée : la formule descend automatiquement jusqu'au bas du bloc de données voisin. Sur mille lignes, c'est instantané, et l'on ne risque pas de s'arrêter trois lignes trop tôt.
Sélectionner la plage entière puis
Ctrl + B remplit vers le bas d'un seul geste, sans souris du tout.Dans les trois cas, ce sont les références relatives qui se décalent, et elles seules.
Les messages d'erreur
Une formule qui échoue affiche un code plutôt qu'un résultat. Chacun désigne une cause précise.
| Message | Ce qu'il signifie | Cause habituelle |
|---|---|---|
#DIV/0! | division par zéro | le diviseur est vide ou nul |
#VALEUR! | mauvais type | du texte là où un nombre est attendu |
#NOM? | nom inconnu | faute de frappe dans un nom de fonction |
#REF! | référence détruite | la cellule visée a été supprimée |
#N/A | valeur introuvable | RECHERCHEV n'a rien trouvé |
###### | trop étroit | la colonne est trop mince, il suffit de l'élargir |
| A | B | C | |
|---|---|---|---|
| 1 | Ce qui est écrit | Ce que ça donne | Pourquoi |
| 2 | =10/0 | #DIV/0! | une division par un zéro écrit en clair |
| 3 | =10/B7 | #DIV/0! | B7 est vide, et une case vide vaut zéro |
| 4 | =SOMM(A2:A3) | #NOM? | SOMM n'existe pas : il manque un E |
| 5 | ="deux"+1 | #VALEUR! | du texte dans une addition |
| 6 | =6*7 | 42 | une formule sans problème, pour comparer |
| 7 |
B4 contient la formule =SOMM(A2:A3), et affiche #NOM?. Les cellules qu'elle utilise sont teintées.
La colonne A de la figure contient bien le texte =10/0, et pourtant rien n'est calculé. C'est l'apostrophe du chapitre 2 : le contenu réel de A2 est '=10/0, et l'apostrophe ne s'affiche pas.
C'est la façon normale de documenter une formule dans une feuille, à côté de la cellule qui l'applique, sans que la documentation se mette à calculer.
###### n'est pas une erreurIl suffit d'élargir la colonne, ou de double-cliquer sur la bordure de son en-tête pour qu'elle s'ajuste toute seule.
C'est le seul « message » du tableau qui ne signale aucun problème de fond, et c'est pourtant celui qui inquiète le plus.
Exercices type
En D2, quelle formule donne la moyenne d'Alice, dont les deux notes sont en B2 et C2 ?
=MOYENNE(B2:C2)
On aurait pu écrire =(B2+C2)/2, qui donne le même résultat sur deux notes. MOYENNE est préférable pour deux raisons.
Elle s'étend sans effort : =MOYENNE(B2:F2) sur cinq notes reste aussi courte, là où l'addition devient illisible.
Elle ignore les cases vides au lieu de les compter pour zéro. S'il manque l'une des deux notes d'Alice, =(B2+C2)/2 divise quand même par deux et donne une moyenne fausse ; MOYENNE divise par une.
Pour remplir toute la colonne : sélectionner D2 et double-cliquer sur la poignée de recopie.
Une colonne de prix TTC affiche des zéros à partir de la deuxième ligne. Que s'est-il passé ?
Il manque des $ sur la référence au taux.
La formule a probablement été écrite =B4*B1 au lieu de =B4*$B$1. À la recopie, B1 s'est décalée en B2, B3, et ainsi de suite. Ces cellules étant vides, elles valent zéro, et tout prix multiplié par zéro fait zéro.
Le piège est que zéro n'est pas un message d'erreur. Rien ne prévient, et l'on soupçonne les données avant de soupçonner la formule.
La correction : rouvrir la première formule, verrouiller la référence unique avec F4, et recopier de nouveau.
Quand faut-il verrouiller une référence ?
Dès qu'elle désigne une cellule unique qui sert à toute une colonne ou à toute une ligne.
Le cas typique est une constante rangée à part : un taux de TVA, un taux de change, un prix unitaire, un objectif. Elle est écrite une seule fois, et toutes les formules la désignent.
Le test qui tranche : se demander ce que la formule doit devenir une ligne plus bas. Si une référence doit suivre la ligne, la laisser relative ; si elle doit rester au même endroit, la verrouiller.
Et quand seule la ligne doit rester fixe, ou seule la colonne, il existe les références mixtes A$1 et $A1.
Une cellule affiche `#NOM?`. Où chercher ?
Dans l'orthographe du nom de fonction, presque toujours.
SOMM au lieu de SOMME, MOYEN au lieu de MOYENNE, ou un nom anglais dans une version française : SUM donne #NOM? dans un Excel français.
Deux autres causes, plus rares : une plage nommée qui n'existe pas ou plus, et du texte oublié sans ses guillemets, comme =SI(A1>10;Réussi;Échoué) où Réussi est pris pour un nom.
Le moyen le plus sûr d'éviter la faute de frappe est de laisser l'assistant compléter : taper =SOM fait apparaître la liste des fonctions, et la touche Tabulation choisit celle qui est surlignée.
Quelle différence entre `=NB(B2:B7)` et `=NBVAL(B2:B7)` ?
NB compte les nombres. NBVAL compte les cellules non vides, quel que soit leur contenu.
Sur une colonne de six élèves dont un est marqué « absent », NB rend 5 et NBVAL rend 6.
C'est le contrôle le plus rapide pour détecter du texte caché dans une colonne de chiffres : si les deux diffèrent sur une colonne censée être numérique, l'écart est exactement le nombre de cases fautives.
Une variante utile : =NB.VIDE(B2:B7) compte les cases vides, ce qui permet de vérifier qu'aucune donnée ne manque.
Pourquoi ne jamais taper un taux directement dans une formule ?
Parce qu'il devient invisible et introuvable.
Écrire =B4*1,2 répété sur deux cents lignes signifie que le jour où le taux change, il faut modifier deux cents formules, sans savoir lesquelles contiennent ce 1,2 et lesquelles contiennent un autre nombre.
Le ranger dans une cellule, avec une étiquette à côté, donne trois avantages d'un coup : on voit la valeur, on la change en un seul endroit, et quelqu'un d'autre comprend le fichier.
C'est la même idée qu'une constante nommée en programmation, et le même bénéfice.
1.Que se passe-t-il si l'on tape A1+B1 sans le signe égal ?
2.=A1*$B$1 recopiée d'une ligne vers le bas devient…
3.Une colonne de prix TTC affiche des zéros à partir de la deuxième ligne. La cause la plus probable est…
4.Sur une colonne de six cases dont une contient « absent », =NB() et =NBVAL() rendent…
5.#NOM? signale…
6.Une cellule affiche ######. Que faut-il faire ?
La méthode
- Sélectionner la case du résultat et taper
=: tout ce qui suit est lu comme un calcul. - Cliquer sur les cases à employer au lieu de retaper leurs valeurs. Le clic écrit la référence à la bonne place.
- Sortir chaque constante dans sa propre cellule, avec une étiquette dans la colonne d'à côté, et la désigner depuis la formule.
- Avant de recopier, examiner chaque référence une par une : celle qui doit suivre la ligne reste relative, celle qui doit rester en place se verrouille avec
F4. - Recopier par un double-clic sur la poignée, le petit carré en bas à droite de la cellule : la formule descend jusqu'au bas du bloc voisin.
- Contrôler la dernière ligne recopiée : la sélectionner et lire sa formule. Elle doit désigner sa propre ligne et la même constante que la première.
- Lire le message d'erreur avant de corriger.
#NOM?envoie à l'orthographe de la fonction,#VALEUR!à du texte dans un calcul,#REF!à une cellule supprimée,######seulement à la largeur de la colonne. - Contrôler une colonne de chiffres avec
=NB()et=NBVAL()sur la même plage : deux résultats différents signalent du texte caché.
Synthèse
- Toute formule commence par
=; sans lui, rien n'est calculé. - Écrire
=A1+B1plutôt que=10+4: c'est ce qui rend le recalcul possible. - Une plage se note
A1:A5, et les arguments d'une fonction se séparent par des points-virgules. SOMME,MOYENNE,MAX,MINignorent le texte ;MOYENNEne divise que par les nombres trouvés.- L'écart entre
NBetNBVALest le contrôle le plus rapide pour détecter du texte caché. - Relative
A1se décale à la recopie ; absolue$A$1ne bouge pas ; mixteA$1ne fixe qu'une moitié. - La touche F4 fait tourner les quatre combinaisons de
$. - Un
$oublié donne souvent des zéros silencieux, plus dangereux qu'un message d'erreur. - Un double-clic sur la poignée recopie jusqu'au bas du bloc de données.
- Chaque message d'erreur désigne une cause :
#DIV/0!un diviseur nul,#VALEUR!du texte dans un calcul,#NOM?un nom inconnu,#REF!une cellule supprimée,#N/Aune valeur introuvable. ######n'est pas une erreur : la colonne est trop étroite, c'est tout.
Mettre en pratique
Ce que devient une formule recopiée, et le zéro qui ne prévient pas.
- Ce que devient une formule recopiéeNiveau 2
- Le zéro qui ne prévient pasNiveau 3
- Choisir le bon verrouNiveau 3