Formules & calculs.
Le cœur d'Excel. 5 formules couvrent 80 % de vos besoins — plus la différence entre référence relative et absolue, qui sépare un tableau qui marche d'un tableau plein de bugs.
Objectifs de ce chapitre
- Écrire une formule simple avec opérateurs
- Maîtriser SOMME, MOYENNE, SI
- Comprendre RECHERCHEV
- Distinguer références absolues vs relatives
1 Anatomie d'une formule
Une formule Excel commence toujours par =. C'est ce qui dit à Excel « ceci n'est pas du texte, c'est un calcul ».
- =A1+B1 · somme de A1 et B1
- =A1*1,2 · A1 multiplié par 1,2
- =(A1+B1)/2 · moyenne de A1 et B1
- =A1*B1+C1 · multiplie A1 et B1, puis ajoute C1
- =A1&"€" · concaténation. Renvoie A1 suivi du symbole €
+ (addition), - (soustraction), * (multiplication), / (division), ^ (puissance), & (concaténation de texte). Priorité : ^, puis * et /, puis + et -. Utilisez des parenthèses pour forcer l'ordre.
2 Les 5 formules essentielles
80 % de vos besoins sont couverts par 5 formules. Apprenez-les vraiment — c'est l'investissement le plus rentable dans Excel.
- =SOMME(A1:A10) · addition d'une plage. La plus utilisée
- =MOYENNE(A1:A10) · moyenne arithmétique. Ignore les cellules vides
- =SI(A1>100;"OK";"KO") · test conditionnel. Si A1 > 100 → renvoie « OK », sinon « KO »
- =NB.SI(A1:A10;"Validé") · compte combien de cellules contiennent « Validé ». Très utile
- =RECHERCHEV(A2;Tableau;2;FAUX) · cherche A2 dans la 1ère colonne du tableau, renvoie la valeur de la 2ème colonne. Le VLOOKUP en français
RECHERCHEV(quoi chercher ; où ; quelle colonne ; FAUX). FAUX = match exact. Dans 90 % des cas, mettez FAUX. Sinon Excel fait du match approximatif et vous donne des résultats faux.
3 Références : relatives vs absolues
Dans une formule, une cellule comme A1 peut être relative ou absolue. Comprendre la différence évite la plupart des bugs de recopie.
- A1 (relative) · si vous recopiez la formule à droite, A1 devient B1, C1… Si vous recopiez en bas, A1 devient A2, A3…
- $A$1 (absolue) · reste $A$1 quoi que vous fassiez. Idéal pour pointer une cellule constante (ex : taux de TVA)
- $A1 (colonne absolue, ligne relative) · A reste fixe, mais le numéro de ligne change
- A$1 (colonne relative, ligne absolue) · la ligne reste fixe, la colonne change
-
1
Sélectionnez la référence
Dans la formule, cliquez sur la référence à figer.
-
2
Appuyez sur F4
Le 1er F4 ajoute les $ sur la ligne ET la colonne : $A$1.
-
3
F4 à nouveau pour cycler
2ème F4 = ligne uniquement (A$1). 3ème F4 = colonne uniquement ($A1). 4ème F4 = retour en relatif (A1).
-
4
Validez
Entrée. La référence est figée comme vous le souhaitez.
Vous écrivez =B1*A1 (où A1 contient le taux de TVA). Vous recopiez en bas : hop, =B2*A2… mais A2 est vide ! La solution : =B1*$A$1. Un F4 sur A1 dans la formule ajoute les $ et fige le taux.
4 Erreurs courantes et correction
Excel affiche ses erreurs sous forme de codes. Apprenez à les diagnostiquer d'un coup d'œil au lieu de paniquer.
- #DIV/0! · division par zéro. Solution : =SI(B1=0;"";A1/B1) ou =SIERREUR(A1/B1;"")
- #VALEUR! · type incorrect (du texte additionné à un nombre). Vérifiez les formats de cellule
- #REF! · référence cassée (vous avez supprimé une cellule référencée). Reconstruisez la formule
- #NOM? · nom de fonction mal orthographié (SOMM au lieu de SOMME). Corrigez l'orthographe
- #N/A · RECHERCHEV n'a rien trouvé. Vérifiez que la valeur cherchée existe bien
Pour gérer proprement les erreurs : =SIERREUR(votre formule ; "valeur si erreur"). Ex : =SIERREUR(RECHERCHEV(…) ; "Non trouvé"). Vos tableaux deviennent propres et lisibles.
1) Créez un tableau avec les colonnes Nom (A), Quantité (B), Prix (C), Montant (D). 2) En D2 : =B2*C2, recopiez sur toute la colonne. 3) Total en D11 : =SOMME(D2:D10). 4) Moyenne en D12 : =MOYENNE(D2:D10). 5) Compte des montants supérieurs à 100 : =NB.SI(D2:D10;">100").
Voir la solution
Si vos formules calculent correctement et que recopier =B2*C2 vers le bas a bien donné =B3*C3, =B4*C4…, vous avez compris les références relatives. Vous pouvez désormais manipuler 80 % des tableaux Excel pros.
À retenir
- Formule = commence par =. Opérateurs : + - * / ^ &. Parenthèses pour forcer l'ordre.
- 5 formules essentielles : SOMME, MOYENNE, SI, NB.SI, RECHERCHEV. À mémoriser.
- Références : relative (A1) bouge à la copie, absolue ($A$1) reste fixe. F4 pour figer.
- Erreurs : #DIV/0!, #VALEUR!, #REF!, #NOM?, #N/A. Encapsuler avec SIERREUR.
Quiz du chapitre 3
3 questions rapides. Cliquez votre réponse — le feedback apparaît immédiatement.
1. Que fait $A$1 dans une formule ?
2. Comment compter les cellules contenant « Validé » ?
3. Comment éviter une erreur #DIV/0! ?
Aujourd'hui, je peux…
Cliquez pour cocher ce que vous maîtrisez après ce chapitre.
Allez plus loin avec une formation 1-to-1.
Si ce chapitre vous a plu, la Formation Excel 1-to-1 va plus loin : on construit ensemble vos formules métier (TCD, RECHERCHEX, calculs conditionnels), sur vos vraies données, à votre rythme.
