Des formules qui tiennent la route
Deux super-pouvoirs au programme : figer une référence avec le $ (pour qu'elle ne bouge pas quand on recopie) et les SI imbriqués (pour classer selon plusieurs seuils). Plus la maîtrise des erreurs et des fonctions statistiques.
Les erreurs de calcul
Lire et corriger les messages en #…| Erreur | Signification | Solution |
|---|---|---|
##### | Colonne trop étroite (pas une vraie erreur) | Élargir la colonne |
#DIV/0! | Division par zéro | Vérifier le diviseur, ou =SIERREUR(...) |
#NOM? | Nom de fonction mal écrit | Corriger l'orthographe (ex. SOMME) |
#VALEUR! | Mauvais type (texte + nombre) | Vérifier les cellules utilisées |
#REF! | Référence supprimée | Ctrl+Z ou réécrire la référence |
Astuce : =SIERREUR(formule ; "") affiche un résultat propre (vide ou un texte) au lieu d'une erreur. Très utile pour les divisions.
Référence relative vs absolue (le $)
Figer une cellule pour qu'elle ne bouge pas à la recopie🔒 Fichier : exercices-excel-BA3.xlsx — feuille « Références TVA ».
Le problème. Quand on recopie une formule, les références se décalent (référence relative). Parfois on veut qu'une cellule reste fixe — par exemple le taux de TVA, écrit une seule fois.
On fige une référence en ajoutant des $ devant la colonne et/ou la ligne. Raccourci : tape la référence puis appuie sur F4 (Mac : ⌘+T).
| Écriture | Type | Comportement à la recopie |
|---|---|---|
B1 | Relative | Colonne et ligne se décalent |
$B$1 | Absolue | Reste toujours sur B1 |
B$1 | Mixte | La ligne reste fixe, la colonne bouge |
$B1 | Mixte | La colonne reste fixe, la ligne bouge |
Exemple sur le fichier : le taux de TVA est en B1. En C4 écris :
=B4*(1+$B$1)Recopie vers le bas : B4 devient B5, B6… mais $B$1 reste figé sur le taux. Sans les $, la formule chercherait le taux en B2, B3… (vide) et donnerait un résultat faux.
Copier / déplacer une formule
Ce qui change, ce qui reste fixe- Copier (Ctrl+C / Ctrl+V) une formule : les références relatives se décalent, les références absolues ($) restent.
- Déplacer (couper Ctrl+X, ou glisser le bord de la cellule) : la formule garde exactement ses références — rien ne se décale.
À retenir : copier ≠ déplacer. Copier adapte les références relatives ; déplacer les conserve telles quelles.
Utiliser du texte dans une formule
L'opérateur & colle du texte et des valeurs :
="Bonjour "&A2
=A2&" a vendu pour "&B2&" €"Les fonctions statistiques
SOMME, MOYENNE, MAX, MIN, NB=SOMME(B2:B20) total
=MOYENNE(B2:B20) moyenne
=MAX(B2:B20) plus grand
=MIN(B2:B20) plus petit
=NB(B2:B20) compte les nombres
=NBVAL(B2:B20) compte les cellules non videsNB vs NBVAL : NB ne compte que les nombres ; NBVAL compte toutes les cellules non vides (texte compris). Pour compter selon une condition, on verra NB.SI au niveau MASTERE.
SI, ET, OU
Une condition, puis plusieurs=SI(condition ; valeur_si_VRAI ; valeur_si_FAUX)
=ET(cond1 ; cond2) vrai si TOUTES vraies
=OU(cond1 ; cond2) vrai si AU MOINS une vraieOn combine souvent ET/OU dans un SI :
=SI(ET(B2>=10 ; C2>=10) ; "Admis" ; "À revoir")Les SI imbriqués
Classer selon plusieurs seuils🎓 Fichier : exercices-excel-BA3.xlsx — feuille « SI imbriqué ».
L'idée. Quand il y a plus de deux cas, on place un SI dans le « sinon » d'un autre SI. On enchaîne les seuils du plus exigeant au moins exigeant (ou l'inverse, mais toujours dans un ordre cohérent).
Exemple : une mention selon la note (en B2) :
=SI(B2>=16 ; "Très bien" ;
SI(B2>=14 ; "Bien" ;
SI(B2>=12 ; "Assez bien" ;
SI(B2>=10 ; "Passable" ; "Insuffisant"))))Pièges classiques : autant de parenthèses fermantes que de SI ouverts, et un ordre des seuils cohérent (ici du plus haut au plus bas).
Depuis les versions récentes, la fonction SI.CONDITIONS permet d'écrire la même chose plus lisiblement : =SI.CONDITIONS(B2>=16;"Très bien";B2>=14;"Bien";…).