Les formules et les fonctions

On arrive au cœur du tableur, ce qui en fait bien plus qu'un joli tableau. Une formule, c'est un calcul que le logiciel effectue, garde en mémoire et refait tout seul dès qu'une donnée change. Ce chapitre est le plus important du module : tout ce qui suit en découle, et une seule notion y demande vraiment de l'attention, celle des références relatives et absolues.

Une formule commence toujours par `=`

Formule

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.

D6fx

	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.

Les mêmes deux nombres, six opérations. Comparer les deux dernières lignes : sans parenthèses la multiplication passe d'abord et l'on obtient 18, avec elles l'addition passe d'abord et l'on obtient 28. Changer le 10 en A2 pour voir la première ligne se recalculer seule.

Pourquoi =A1+B1 plutôt que =10+4. Les deux donnent 14. Ce qui les distingue est ce qui se passe ensuite.
=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.

La feuille suivante chiffre trois opérations d'usinage au taux horaire de l'atelier. Les colonnes C et D affichent exactement les mêmes coûts, et une seule des deux est juste pour de bon : il suffit de changer le taux pour savoir laquelle.

B1fx

	A	B	C	D
1	Taux horaire (€/h)	45
2
3	Opération	Durée (h)	Coût, taux désigné	Coût, taux tapé
4	Tournage	2	90	90
5	Fraisage	3,5	157,5	157,5
6	Perçage	0,5	22,5	22,5
7	Total	6	270	270
8

B1 contient la valeur « 45 ». Ce n'est pas une formule : rien ne se recalculera ici.

Trois opérations chiffrées deux fois : la colonne C désigne le taux de B1, la colonne D l'a tapé dans chaque formule. Les deux totaux valent 270 €.

À manipuler

Passer le taux de B1 à 52. La colonne C suit et monte à 312 € ; la colonne D reste à 270 € sans le moindre message, parce que le 45 est enfermé dans chacune de ses formules.
Sélectionner ensuite C4, C5 et C6 : chaque formule a été écrite à la main. La section sur les références montre comment n'en écrire qu'une.

Vérification rapideon peut se reprendre

Que se passe-t-il si l'on tape A1+B1 sans le signe égal ?

Les fonctions

Fonction et plage

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
`=ARRONDI.SUP(A1;0)` / `=ARRONDI.INF(A1;0)`	arrondit toujours vers le haut, toujours vers le bas

D3fx

	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.

Six élèves, dont un absent. La moyenne vaut 13 et non 10,83 : elle ne divise que par les cinq notes réelles, parce que « absent » est du texte. C'est aussi pourquoi NB rend 5 et NBVAL rend 6, et l'écart entre les deux est précisément le nombre de cases non numériques.

NB et NBVAL, le contrôle en une seconde

NB 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.

Arrondir dans le bon sens

ARRONDI va au plus proche : 10,4 donne 10, et 7,8 donne 8. C'est ce qu'on attend d'un montant ou d'une mesure, mais beaucoup de questions d'atelier ne se posent pas ainsi. Combien de cartons faut-il pour expédier 250 pièces rangées par 24 ? Le calcul donne 10,42, et la réponse est 11 : un dixième de carton reste un carton. Combien de cartons complets peut-on fermer ? 10, et les dix pièces qui restent attendent le lot suivant.

Chaque question a sa fonction. ARRONDI.SUP arrondit toujours vers le haut, ARRONDI.INF toujours vers le bas, et ARRONDI au plus proche. Toutes trois prennent en second argument le nombre de décimales à garder : 0 pour un nombre entier de cartons.

F2fx

	A	B	C	D	E	F	G	H
1	Lot	Pièces	Par carton	Cartons, calcul brut	ARRONDI	ARRONDI.SUP	ARRONDI.INF	Reste (pièces)
2	Lot A	250	24	10,42	10	11	10	10
3	Lot B	130	24	5,42	5	6	5	10
4	Lot C	187	24	7,79	8	8	7	19
5	Lot D	96	24	4	4	4	4	0
6

F2 contient la formule =ARRONDI.SUP(D2;0), et affiche 11. Les cellules qu'elle utilise sont teintées.

Quatre lots, trois arrondis. Pour le lot C, ARRONDI donne 8 cartons, le bon nombre, mais par hasard : pour les lots A et B, il en prévoit un de moins qu'il n'en faut, et dix pièces restent chaque fois sur l'établi (colonne H). ARRONDI.SUP répond à « combien de cartons prévoir », ARRONDI.INF à « combien de cartons complets ».

À manipuler

Passer le lot D de 96 à 97 pièces : une seule pièce de plus, et ARRONDI.SUP réclame un cinquième carton, pendant qu'ARRONDI en reste à quatre.
Chercher ensuite, en changeant les pièces du lot A, à partir de combien ARRONDI se remet à tomber juste : il faut que le calcul brut atteigne la demie, soit 252 pièces.

Juste aujourd'hui, faux demain

Sur le lot C, ARRONDI et ARRONDI.SUP donnent le même 8, et une feuille construite avec la mauvaise fonction passerait tous les contrôles ce jour-là. Il suffit que le calcul brut tombe sous la demie, comme pour les lots A et B, pour qu'il manque un carton au quai d'expédition.
La bonne fonction ne se choisit donc pas en regardant le résultat, mais en formulant la question : « au moins assez » appelle ARRONDI.SUP, « seulement ce qui est complet » appelle ARRONDI.INF.
On croise aussi ENT, qui garde la partie entière : sur des nombres positifs, elle donne la même chose que ARRONDI.INF(…;0). Elles ne divergent que sur les négatifs, où ENT(-2,5) rend -3 et ARRONDI.INF(-2,5;0) rend -2.

Vérification rapideon peut se reprendre

Une commande de 250 pièces se range par cartons de 24. Quelle formule donne le nombre de cartons à préparer pour tout expédier ?

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.

Les deux sortes de références

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.

Le $ 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.

C4fx

	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.

Le taux de TVA est stocké une seule fois, en B1, et la formule le désigne par une référence absolue. Recopier C4 vers le bas : la partie B4 se décale en B5, B6, B7, alors que $B$1 ne bouge jamais. Changer ensuite le taux en B1 met à jour toute la colonne d'un coup.

Ce qui se passe sans les $

Si la formule avait été écrite =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.

Les références mixtes servent dès qu'un tableau se lit dans les deux sens. Le devis ci-dessous croise des durées d'opération, en colonne A, avec le taux horaire de quatre machines, en ligne 2. Chaque case fait la même multiplication, durée par taux, et une seule formule suffit pour les vingt cases : la durée se lit toujours en colonne A, d'où $A, et le taux toujours en ligne 2, d'où $2.

D5fx

	A	B	C	D	E
1	Durée (h)	Tour	Fraiseuse	Rectifieuse	Centre 5 axes
2	Taux horaire (€/h)	45	60	75	110
3	0,5	22,5	30	37,5	55
4	1	45	60	75	110
5	2	90	120	150	220
6	4	180	240	300	440
7	8	360	480	600	880
8

D5 contient la formule =$A5*D$2, et affiche 150. Les cellules qu'elle utilise sont teintées.

Vingt coûts, une seule formule : la durée se lit en colonne A, le taux en ligne 2. Deux heures de rectifieuse coûtent 150 €.

À manipuler

Afficher les formules : d'une case à l'autre, seules changent la lettre du taux et le numéro de la durée, jamais le A ni le 2 qui portent un $.
Pour voir ce que les $ évitent, remplacer la formule de B3 par =A3*B2 et la recopier vers le bas : B4 devient =A4*B3 et multiplie une heure par le coût de la case du dessus, 22,5 € au lieu de 45 €. « Rétablir la feuille » remet tout en place.

Vérification rapideon peut se reprendre

1.=A1*$B$1 recopiée d'une ligne vers le bas devient…

2.Une colonne de prix TTC affiche des zéros à partir de la deuxième ligne. La cause la plus probable est…

3.La formule =$A3*B$2, écrite en B3, est recopiée en C4. Que devient-elle ?

La recopie

Trois façons de recopier, et la troisième est la meilleure.

La poignée de recopie : le petit carré en bas à droite de la cellule sélectionnée. On l'attrape et on glisse.
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.

Vérification rapideon peut se reprendre

Sur une colonne de six cases dont une contient « absent », =NB() et =NBVAL() rendent…

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

B4fx

	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

B2 #DIV/0! B3 #DIV/0! B4 #NOM? B5 #VALEUR!

B4 contient la formule =SOMM(A2:A3), et affiche #NOM?. Les cellules qu'elle utilise sont teintées.

Cinq formules, dont quatre échouent. La colonne A montre leur texte, la colonne B les calcule pour de vrai. Corriger le SOMM de la ligne 4 en SOMME, dans la barre de formule, pour voir l'erreur disparaître.

Écrire une formule sans qu'elle se déclenche

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 erreur

Ces croisillons signifient seulement que la colonne est trop étroite pour afficher le nombre. La valeur est intacte, les calculs qui en dépendent sont justes.
Il 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.

Vérification rapideon peut se reprendre

1.#NOM? signale…

2.Une cellule affiche ######. Que faut-il faire ?

Mettre en pratique

Quatre résultats à prévoir avant de les taper. Le dernier porte sur ce qui trompe le plus dans une feuille réelle : une cellule de texte au milieu d'une colonne de nombres.

Prévoir le résultat avant de le taper

1.
Avec $A2 = 10$ et $B2 = 4$ , que rend la formule =A2+B2*2 ?
2.
Et que rend =(A2+B2)*2 ?
3.
Une colonne contient les notes 14, 8, 17, « absent », 11 et 15. Que rend =SOMME sur cette plage de six cellules ?
4.
Sur la même plage, de combien la valeur rendue par =MOYENNE dépasse-t-elle la somme divisée par six ?

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.

Une formule commence toujours par =

Les fonctions

Arrondir dans le bon sens

Références relatives et absolues

La recopie

Les messages d'erreur

Mettre en pratique

Dans la bibliothèque d'exercices

Une formule commence toujours par `=`