Rappels essentiels avant de commencer
Avant d'aborder les fonctions avancées, voici les bases indispensables sur lesquelles tout repose : la syntaxe des formules, les références de cellules et les types de données.
Anatomie d'une formule Excel
Toute formule commence par le signe =. Sans lui, Excel traite la saisie comme du texte. Une formule combine des fonctions, des références de cellules, des opérateurs et des valeurs.
= Signe obligatoire + Fonction ( Arguments séparés par ; ) SOMME(A1:A10) → additionne les cellules A1 à A10 MOYENNE(B2:B50) → moyenne de la plage B2:B50 NB(C1:C100) → nombre de cellules avec valeur numérique NBVAL(D1:D50) → nombre de cellules non vides Opérateurs arithmétiques : A1 + B1 addition A1 - B1 soustraction A1 * B1 multiplication A1 / B1 division A1 ^ 2 puissance (A1 au carré) A1 & " ans" concaténation de texte
Références relatives vs absolues
C'est le concept le plus important pour copier des formules correctement. Une référence relative s'adapte quand tu la copies, une référence absolue reste fixe.
A1 Relative — ligne ET colonne s'adaptent à la copie $A$1 Absolue — rien ne bouge (touche F4) $A1 Mixte — colonne A fixe, ligne s'adapte A$1 Mixte — ligne 1 fixe, colonne s'adapte Exemple : calcul d'une TVA à partir d'un taux fixe en B1 =A2 * $B$1 ← $B$1 reste fixe même en copiant vers le bas
Raccourci clé : appuie sur F4 après avoir cliqué sur une référence de cellule pour basculer entre les modes Relative → Absolue → Mixte ligne → Mixte colonne.
Fonction SI
La fonction SI est la base de toute logique conditionnelle dans Excel. Elle évalue une condition et retourne une valeur si elle est vraie, une autre si elle est fausse.
Syntaxe
Opérateurs de comparaison
| Opérateur | Signification | Exemple |
|---|---|---|
| = | Égal à | A1=10 → VRAI si A1 vaut 10 |
| <> | Différent de | A1<>"Oui" → VRAI si A1 ≠ "Oui" |
| > | Supérieur à | A1>100 → VRAI si A1 dépasse 100 |
| >= | Supérieur ou égal | A1>=18 → VRAI si A1 ≥ 18 |
| < | Inférieur à | B2<0 → VRAI si B2 est négatif |
| <= | Inférieur ou égal | B2<=100 → VRAI si B2 ≤ 100 |
Exemples concrets
Exemple 1 : mention selon la note (note en B2) =SI(B2>=10 ; "Admis" ; "Recalé") Exemple 2 : valeur numérique — bonus si CA > objectif =SI(C5>D5 ; C5*0,1 ; 0) Exemple 3 : cellule vide ou non =SI(A1="" ; "Non renseigné" ; A1) Exemple 4 : retourner une formule dans le si_vrai =SI(E2>=10 ; E2*1,05 ; E2*0,95) → +5% si ≥10, -5% sinon
Résultat dans le tableur
| A | B | C | |
|---|---|---|---|
| 1 | Élève | Note /20 | Résultat |
| 2 | Martin | 14 | = SI(B2>=10;"Admis";"Recalé") |
| 3 | Sophie | 8 | = SI(B3>=10;"Admis";"Recalé") |
| 4 | Lucas | 12 | = SI(B4>=10;"Admis";"Recalé") |
| A | B | C | |
|---|---|---|---|
| 2 | Martin | 14 | Admis |
| 3 | Sophie | 8 | Recalé |
| 4 | Lucas | 12 | Admis |
Attention aux guillemets ! Les chaînes de texte doivent être entre guillemets droits "Admis". Les nombres et références de cellules ne prennent pas de guillemets. Écrire "10" au lieu de 10 compare du texte, pas un nombre — résultats imprévisibles !
Fonctions ET et OU
ET et OU permettent de combiner plusieurs conditions dans un SI. Elles évaluent plusieurs tests logiques et retournent VRAI ou FAUX — toujours utilisées à l'intérieur d'un SI.
Syntaxe
Table de vérité
| Condition A | Condition B | ET(A;B) | OU(A;B) |
|---|---|---|---|
| VRAI | VRAI | VRAI | VRAI |
| VRAI | FAUX | FAUX | VRAI |
| FAUX | VRAI | FAUX | VRAI |
| FAUX | FAUX | FAUX | FAUX |
Exemples — ET
Admis si note ≥ 10 ET assiduité = "Oui" =SI(ET(B2>=10 ; C2="Oui") ; "Admis" ; "Recalé") Remise si quantité ≥ 100 ET client = "Fidèle" =SI(ET(D5>=100 ; E5="Fidèle") ; D5*0,85 ; D5) → -15% si les 2 conditions sont remplies Stock critique si ≥ 5 ET ≤ 20 unités =SI(ET(F3>=5 ; F3<=20) ; "Critique" ; "OK")
Exemples — OU
Prioritaire si VIP OU commande > 1000€ =SI(OU(B2="VIP" ; C2>1000) ; "Prioritaire" ; "Standard") Alerte si rupture OU délai dépassé =SI(OU(D3=0 ; E3>30) ; "⚠ ALERTE" ; "✓ OK") Eligible si Région = "Nord" OU = "Est" =SI(OU(F5="Nord" ; F5="Est") ; "Éligible" ; "Hors zone")
Astuce : ET et OU peuvent accepter jusqu'à 255 conditions. Tu peux aussi les combiner : SI(ET(A1>0 ; OU(B1="X" ; C1="Y")) ; "Oui" ; "Non")
SI imbriqués — plusieurs niveaux
Quand deux résultats ne suffisent pas, on imbrique des SI dans d'autres SI. Chaque valeur_si_faux peut elle-même être un nouveau SI — jusqu'à 64 niveaux dans Excel moderne.
Principe d'imbrication
Structure de base (lire de gauche à droite) =SI( condition1 ; résultat1 ; ← si condition1 = VRAI SI( condition2 ; résultat2 ; ← si condition1 = FAUX et condition2 = VRAI résultat3 ← si toutes les conditions = FAUX ) )
Exemple 1 — Mentions scolaires
=SI(B2>=16 ; "Très bien" ; SI(B2>=14 ; "Bien" ; SI(B2>=12 ; "Assez bien" ; SI(B2>=10 ; "Passable" ; "Insuffisant")))) Logique de lecture : ≥16 → "Très bien" ≥14 → "Bien" (donc entre 14 et 15,99) ≥12 → "Assez bien" (donc entre 12 et 13,99) ≥10 → "Passable" (donc entre 10 et 11,99) <10 → "Insuffisant"
Exemple 2 — Grille tarifaire
Quantité en D5, Prix unitaire en E5 =SI(D5>=500 ; E5*0,70 ; ≥500 : -30% SI(D5>=200 ; E5*0,80 ; ≥200 : -20% SI(D5>=100 ; E5*0,90 ; ≥100 : -10% E5))) <100 : prix normal
=SI(B2>=10;"Passable";
SI(B2>=12;"Assez bien";
...))
/* Logique INVERSÉE !
Si B2=14, la 1ère condition
est vraie → "Passable"
Les suivants ne sont
jamais évalués. */ =SI(B2>=16;"Très bien";
SI(B2>=14;"Bien";
SI(B2>=12;"Assez bien";
SI(B2>=10;"Passable";
"Insuffisant"))))
/* Tester TOUJOURS du seuil
le plus ÉLEVÉ au plus
BAS. */ Alternative moderne — SI.CONDITIONS (Excel 2019+) : plus lisible que les SI imbriqués.=SI.CONDITIONS(B2>=16;"Très bien"; B2>=14;"Bien"; B2>=12;"Assez bien"; B2>=10;"Passable"; VRAI;"Insuffisant")
MAX, MIN et variantes
Les fonctions statistiques permettent d'extraire les valeurs extrêmes d'une plage, de les classer et d'effectuer des analyses rapides sur des données numériques.
MAX et MIN — bases
Valeur maximale d'une plage =MAX(B2:B50) Valeur minimale =MIN(B2:B50) Plusieurs plages non contiguës =MAX(B2:B10 ; D2:D10 ; F5) 2ème plus grande valeur (GRANDE.VALEUR) =GRANDE.VALEUR(B2:B50 ; 2) ← 2 = 2ème rang =GRANDE.VALEUR(B2:B50 ; 3) ← 3 = 3ème rang 2ème plus petite valeur (PETITE.VALEUR) =PETITE.VALEUR(B2:B50 ; 2) Rang d'une valeur dans une plage =RANG(B2 ; B$2:B$50 ; 0) ← 0 = ordre décroissant (1 = le plus grand) ← 1 = ordre croissant (1 = le plus petit)
Combinaisons utiles avec SI
Valeur jamais inférieure à 0 (plancher) =MAX(A1 ; 0) → retourne A1 si positif, sinon 0 Valeur plafonnée à 100 =MIN(A1 ; 100) → retourne A1 si ≤100, sinon 100 Valeur entre un plancher ET un plafond =MIN(MAX(A1 ; 0) ; 100) → toujours entre 0 et 100 Mettre en valeur le max dans SI =SI(B2=MAX($B$2:$B$50) ; "🏆 Meilleur" ; "")
| Fonction | Rôle | Exemple |
|---|---|---|
| MAX(plage) | Plus grande valeur de la plage | =MAX(B2:B100) |
| MIN(plage) | Plus petite valeur de la plage | =MIN(B2:B100) |
| GRANDE.VALEUR(plage;k) | k-ième plus grande valeur | =GRANDE.VALEUR(B2:B100;3) |
| PETITE.VALEUR(plage;k) | k-ième plus petite valeur | =PETITE.VALEUR(B2:B100;2) |
| RANG(valeur;plage;ordre) | Position d'une valeur dans la plage | =RANG(B2;$B$2:$B$50;0) |
| MEDIANE(plage) | Valeur médiane (milieu statistique) | =MEDIANE(B2:B100) |
| ECARTYPE(plage) | Écart-type (dispersion des données) | =ECARTYPE(B2:B100) |
NB.SI, SOMME.SI, MOYENNE.SI
Ces fonctions combinent un calcul (compter, additionner, faire une moyenne) avec un filtre conditionnel — sans avoir besoin de filtrer manuellement les données.
NB.SI — compter selon un critère
Combien de fois "Paris" apparaît en colonne A =NB.SI(A2:A100 ; "Paris") Combien de valeurs > 50 =NB.SI(B2:B100 ; ">50") ← opérateur entre guillemets ! Critère dans une cellule de référence =NB.SI(A2:A100 ; E1) ← valeur de E1 comme critère Critère avec joker * (tout ce qui contient "tech") =NB.SI(A2:A100 ; "*tech*") Variante : NB.SI.ENS — plusieurs critères =NB.SI.ENS(A2:A100 ; "Paris" ; B2:B100 ; ">1000") → compte les lignes où A="Paris" ET B>1000
SOMME.SI — additionner selon un critère
Total des ventes de la région "Nord" (région en A, montant en C) =SOMME.SI(A2:A100 ; "Nord" ; C2:C100) Total des montants > 500€ =SOMME.SI(C2:C100 ; ">500" ; C2:C100) Variante : SOMME.SI.ENS — région "Nord" ET vendeur "Martin" =SOMME.SI.ENS(C2:C100 ; ← plage à sommer EN PREMIER A2:A100 ; "Nord" ; B2:B100 ; "Martin")
SOMME.SI vs SOMME.SI.ENS : attention à l'ordre des arguments ! Dans SOMME.SI, la plage à sommer est en 3ème position. Dans SOMME.SI.ENS, elle est en 1ère position. C'est un piège classique.
MOYENNE.SI — moyenne conditionnelle
Note moyenne des élèves de la classe "TS1" (classe en A, notes en B) =MOYENNE.SI(A2:A100 ; "TS1" ; B2:B100) Moyenne des salaires > 2000€ =MOYENNE.SI(C2:C100 ; ">2000" ; C2:C100)
RECHERCHEV — la fonction clé
RECHERCHEV (VLOOKUP) cherche une valeur dans la première colonne d'un tableau et retourne une valeur de la même ligne dans une autre colonne. Indispensable pour relier deux tables de données.
Syntaxe
Exemple concret — Catalogue produits
| A — Réf. | B — Désignation | C — Prix HT | D — Stock | |
|---|---|---|---|---|
| 1 | Code | Produit | Prix HT | Stock |
| 2 | P001 | Souris optique | 12,50 € | 85 |
| 3 | P002 | Clavier mécanique | 45,00 € | 32 |
| 4 | P003 | Écran 24" | 189,00 € | 14 |
| 5 | P004 | Casque audio | 29,90 € | 0 |
Je cherche le prix de P003 (code saisi en G2) =RECHERCHEV(G2 ; $A$1:$D$100 ; 3 ; FAUX) ↑ ↑ ↑ ↑ G2=P003 tableau col 3 = Prix Exacte (figé) (B=2, C=3, D=4) Récupérer la désignation (colonne 2) =RECHERCHEV(G2 ; $A$1:$D$100 ; 2 ; FAUX) Récupérer le stock (colonne 4) =RECHERCHEV(G2 ; $A$1:$D$100 ; 4 ; FAUX)
Gérer les erreurs #N/A avec SIERREUR
Si le code n'existe pas → afficher message propre au lieu de #N/A =SIERREUR( RECHERCHEV(G2 ; $A$1:$D$100 ; 3 ; FAUX) ; "Produit introuvable" )
→ Cherche TOUJOURS dans la 1ère colonne → Ne peut pas regarder à GAUCHE de la clé → Sensible à l'ajout de colonnes (le no. d'index change) → Moins performant sur très grandes plages
=INDEX(C2:C100 ;
EQUIV(G2;A2:A100;0))
→ Cherche dans n'importe
quelle colonne
→ Peut retourner à gauche
→ Plus robuste aux
modifications de tableau
→ Plus performant Excel 2021 / Microsoft 365 : la fonction RECHERCHEX (XLOOKUP) remplace avantageusement RECHERCHEV — elle cherche dans n'importe quelle direction, gère nativement les erreurs et retourne plusieurs colonnes d'un coup.
Fonctions Texte
Les fonctions texte permettent de manipuler, extraire, nettoyer et transformer des chaînes de caractères — indispensable pour nettoyer des données importées ou construire des références.
GAUCHE / DROITE / MILIEU — extraction =GAUCHE(A1 ; 3) ← 3 premiers caractères =DROITE(A1 ; 4) ← 4 derniers caractères =MILIEU(A1 ; 3 ; 5) ← 5 caractères à partir du 3ème NBCAR — longueur d'une chaîne =NBCAR(A1) ← nombre de caractères MAJUSCULE / MINUSCULE / NOMPROPRE =MAJUSCULE(A1) ← TOUT EN MAJUSCULES =MINUSCULE(A1) ← tout en minuscules =NOMPROPRE(A1) ← Première Lettre En Majuscule SUPPRESPACE — supprime les espaces en trop =SUPPRESPACE(A1) ← " Jean Dupont " → "Jean Dupont" CONCATENER / & — assembler des chaînes =CONCATENER(A1 ; " " ; B1) =A1 & " " & B1 ← équivalent, plus court =CONCAT(A1:C1) ← CONCAT accepte une plage (Excel 2019+) CHERCHE / TROUVE — position d'un caractère =CHERCHE("@" ; A1) ← position de @ (insensible casse) =TROUVE("@" ; A1) ← idem mais sensible à la casse SUBSTITUE — remplacer du texte =SUBSTITUE(A1 ; "ancien" ; "nouveau") TEXTE — formater un nombre en texte =TEXTE(A1 ; "0,00 €") ← "1234,5" → "1 234,50 €" =TEXTE(A1 ; "dd/mm/yyyy") ← date → "15/06/2024"
Cas pratique — extraire prénom et nom
A1 contient "Marie Dupont" — trouver la position de l'espace Position de l'espace : =CHERCHE(" " ; A1) → 6 (l'espace est au 6ème caractère) Extraire le prénom (avant l'espace) : =GAUCHE(A1 ; CHERCHE(" " ; A1) - 1) → "Marie" Extraire le nom (après l'espace) : =DROITE(A1 ; NBCAR(A1) - CHERCHE(" " ; A1)) → "Dupont"
Excel 365 / 2021 : utilisez plutôt =FRACTIONNER.TEXTE(A1;" ") qui sépare automatiquement sur plusieurs colonnes — bien plus simple que GAUCHE/DROITE/CHERCHE.
Fonctions Date et Heure
Excel stocke les dates comme des nombres entiers (jours depuis le 01/01/1900). Comprendre ce principe permet de calculer des durées, des échéances et d'extraire des informations temporelles.
DATE DU JOUR et MAINTENANT =AUJOURDHUI() ← date du jour (se recalcule à l'ouverture) =MAINTENANT() ← date et heure actuelles EXTRAIRE des composantes d'une date =JOUR(A1) ← numéro du jour (1-31) =MOIS(A1) ← numéro du mois (1-12) =ANNEE(A1) ← année en 4 chiffres =JOURSEM(A1 ; 2) ← 1=Lun, 2=Mar, ... 7=Dim =NO.SEMAINE(A1 ; 2) ← numéro de semaine ISO CONSTRUIRE une date depuis des valeurs =DATE(2024 ; 12 ; 31) ← construit le 31/12/2024 =DATE(ANNEE(A1) ; MOIS(A1) + 1 ; 1) ← premier jour du mois suivant CALCULER des durées =B1 - A1 ← nombre de jours entre A1 et B1 =DATEDIF(A1 ; B1 ; "Y") ← années complètes entre A1 et B1 =DATEDIF(A1 ; B1 ; "M") ← mois complets =DATEDIF(A1 ; B1 ; "D") ← jours complets NB.JOURS.OUVRES — jours ouvrés entre deux dates =NB.JOURS.OUVRES(A1 ; B1) ← exclut automatiquement samedis et dimanches SERIE.JOUR.OUVRE — date N jours ouvrés plus tard =SERIE.JOUR.OUVRE(AUJOURDHUI() ; 30) ← date dans 30 jours ouvrés
Cas pratiques courants
Âge d'une personne (date de naissance en A1) =DATEDIF(A1 ; AUJOURDHUI() ; "Y") & " ans" → "32 ans" Retard d'une livraison (date prévue A1, livrée B1) =SI(B1>A1 ; B1-A1 & " jours de retard" ; "Dans les délais") Contrat expiré ou actif (échéance en A1) =SI(A1<AUJOURDHUI() ; "⚠ Expiré" ; SI(A1<AUJOURDHUI()+30 ; "⏳ Expire bientôt" ; "✓ Actif")) Regrouper des dates par mois (pour tableaux croisés) =TEXTE(A1 ; "mmm yyyy") → "juin 2024" =TEXTE(A1 ; "T\T yyyy") → nécessite un calcul de trimestre ="T" & ENT((MOIS(A1)-1)/3)+1 & " " & ANNEE(A1) → "T2 2024"
| Unité DATEDIF | Signification |
|---|---|
| "Y" | Nombre d'années complètes entre les deux dates |
| "M" | Nombre de mois complets |
| "D" | Nombre de jours |
| "YM" | Mois restants après calcul des années (pour "X ans et Y mois") |
| "MD" | Jours restants après calcul des mois |
Félicitations — tu maîtrises maintenant les fonctions avancées Excel ! Prochaines étapes recommandées : les tableaux croisés dynamiques pour analyser de grands volumes de données, les graphiques avancés et la mise en forme conditionnelle pour visualiser automatiquement les anomalies.
