Fonctions statistiques avancées dans Excel
Compter, additionner ou faire une moyenne seulement pour les lignes qui remplissent une ou plusieurs conditions : c’est le rôle des fonctions NB.SI, SOMME.SI, MOYENNE.SI et de leurs versions .ENS. Associées aux fonctions logiques SI, ET, OU et NON, à SOMMEPROD et à SOUS.TOTAL, elles couvrent l’essentiel des tableaux de suivi sans passer par un tableau croisé dynamique.
Une conditionNB.SI · SOMME.SI
Plusieurs conditionsSOMME.SI.ENS
Conditions calculéesSOMMEPROD
Lignes visiblesSOUS.TOTAL(109;…)
1
Les fonctions logiques SI, ET, OU, NON
| Fonction | Renvoie | Exemple |
|---|---|---|
SI(test;si_vrai;si_faux) | Une valeur selon le résultat du test | =SI(F2>=2000;"Prime";"") |
ET(test1;test2;…) | VRAI si tous les tests sont vrais | =ET(F2>=2000;E2>=8) |
OU(test1;test2;…) | VRAI si au moins un test est vrai | =OU(B2="Nord";B2="Est") |
NON(test) | Inverse VRAI et FAUX | =NON(C2="Excel") |
Simulateur : qui touche la prime ?
=SI(
(
Montant ≥
; Stagiaires ≥) ; « Prime » ; « »)
SI imbriqués ou SI.CONDITIONS
=SI(F2>=3000;"A";SI(F2>=1500;"B";"C")) s’écrit aussi =SI.CONDITIONS(F2>=3000;"A";F2>=1500;"B";VRAI;"C") (Excel 2019 et Microsoft 365).Texte entre guillemetsDans un test, le texte s’écrit entre guillemets droits :
B2="Nord". Sans guillemets, Excel cherche un nom défini et renvoie #NOM?.
Atelier : NB.SI, SOMME.SI, MOYENNE.SI, les fonctions .ENS et SOMMEPROD
Résultat
2
NB.SI, SOMME.SI, MOYENNE.SI et NB.VIDE
| Syntaxe | Question | Résultat |
|---|---|---|
=NB.SI(C2:C25;"Excel") | Combien de sessions Excel ? | 8 |
=SOMME.SI(C2:C25;"Excel";F2:F25) | Chiffre d’affaires Excel | 21 420 € |
=MOYENNE.SI(C2:C25;"Excel";F2:F25) | Montant moyen d’une session Excel | 2 677,50 € |
=NB.VIDE(H2:H25) | Évaluations non renseignées | 4 |
Ordre des argumentsDans SOMME.SI et MOYENNE.SI, la plage à additionner vient en dernier ; dans les versions .ENS, elle vient en premier. C’est la source d’erreur n° 1.
Cellules videsMOYENNE et MOYENNE.SI ignorent les cellules vides (elles ne comptent pas pour zéro). NB.VIDE les compte, NBVAL compte les non vides.
3
Écrire les critères
| Critère | Signification |
|---|---|
"Excel" | Égal à Excel (majuscules indifférentes) |
"<>Nord" | Différent de Nord |
">=2000" | Supérieur ou égal à 2 000 |
">="&K1 | Seuil lu dans la cellule K1 (concaténation avec &) |
">="&DATE(2026;4;1) | À partir du 1er avril 2026 |
"*Point*" · "W???" | Contient « Point » · 4 caractères commençant par W |
"" | Cellule vide |
Critère dans une cellulePlacez le critère dans une cellule (K1 = Excel) et écrivez
=NB.SI(C2:C25;K1) : il suffit de changer K1 pour relancer le calcul, idéal avec une liste déroulante.Opérateur et référence
">=K1" compare au texte « K1 ». Il faut sortir la référence des guillemets : ">="&K1.4
NB.SI.ENS, SOMME.SI.ENS, MOYENNE.SI.ENS, MAX.SI.ENS
Les fonctions .ENS acceptent jusqu’à 127 couples plage_critères ; critère. Une ligne est retenue si toutes les conditions sont vraies (logique ET).
| Formule | Résultat |
|---|---|
=NB.SI.ENS(C2:C25;"Excel";B2:B25;"Nord") | 2 sessions |
=SOMME.SI.ENS(F2:F25;C2:C25;"Excel";B2:B25;"Nord") | 4 200 € |
=MOYENNE.SI.ENS(F2:F25;B2:B25;"Est";E2:E25;">=5") | 4 005 € |
=MAX.SI.ENS(F2:F25;B2:B25;"Sud") | 3 600 € |
=SOMME.SI.ENS(F2:F25;A2:A25;">="&DATE(2026;4;1)) | 28 480 € (2e trimestre) |
Et pour un OU ?Additionnez deux fonctions :
=SOMME.SI(B2:B25;"Nord";F2:F25)+SOMME.SI(B2:B25;"Est";F2:F25), ou utilisez SOMMEPROD.MIN.SI.ENSMême syntaxe que MAX.SI.ENS. Ces deux fonctions existent depuis Excel 2019 / Microsoft 365.
5
SOMMEPROD
SOMMEPROD multiplie des plages terme à terme puis additionne. Chaque condition entre parenthèses vaut 1 (VRAI) ou 0 (FAUX) : on peut donc calculer avec n’importe quelle condition, y compris sur un résultat de calcul.
| Formule | Résultat |
|---|---|
=SOMMEPROD(E2:E25;F2:F25) | Somme des produits Stagiaires × Montant |
=SOMMEPROD((C2:C25="Excel")*(F2:F25-G2:G25)) | Marge des sessions Excel : 10 060 € |
=SOMMEPROD(((B2:B25="Nord")+(B2:B25="Est")>0)*F2:F25) | CA Nord OU Est : 39 600 € |
=SOMMEPROD((MOIS(A2:A25)=5)*F2:F25) | CA de mai : 9 820 € |
Plages de même tailleToutes les plages doivent avoir le même nombre de lignes, sinon #VALEUR!. Évitez les colonnes entières (C:C) : le calcul devient très lent.
Multiplier = ET, additionner = OU
(test1)*(test2) vaut 1 si les deux sont vrais ; ((test1)+(test2)>0) vaut 1 si l’un des deux l’est.6
SOUS.TOTAL
=SOUS.TOTAL(n°_fonction;plage) calcule sur les cellules visibles quand la liste est filtrée, et ignore les autres SOUS.TOTAL de la plage.
Simulateur : filtre automatique et SOUS.TOTAL
1 à 11 ou 101 à 111Les codes 1-11 ignorent les lignes filtrées ; les codes 101-111 ignorent aussi les lignes masquées à la main.
Ligne des totauxLa ligne des totaux d’un tableau de données (Ctrl + L) écrit automatiquement
=SOUS.TOTAL(109;[Montant HT]).Exercice de synthèse : tableau de suivi des sessions
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Colonne I :
=SI(ET(F2>=2000;E2>=8);"Prime";"")→ 8 primes. Remplacez ET par OU : 12 primes. - Feuille Synthèse : NB.SI, SOMME.SI et MOYENNE.SI pour la formation choisie en B1 (Excel : 8 ; 21 420 € ; 2 677,50 €) et NB.VIDE sur les évaluations (4).
- SOMME.SI.ENS par formation et par région (Excel Nord : 4 200 €), MOYENNE.SI.ENS et MAX.SI.ENS.
- SOMMEPROD : marge des sessions Excel (10 060 €) et CA de mai (9 820 €).
- Filtrez la région Nord : comparez SOMME (53 690 €) et SOUS.TOTAL(9;…) (20 340 €).