Qu'est-ce que la fonction CRITERE.
CRITERE.LOI.BINOMIALE (BINOM.INV en anglais) est une fonction statistique qui répond à une question précise : combien de succès minimum faut-il pour que la probabilité cumulée atteigne un seuil donné ? En contrôle qualité, ça donne le nombre de défauts à partir duquel tu déclenches une alerte. En marketing, c'est le nombre de conversions minimum pour valider une hypothèse.
Cette fonction est conservée pour compatibilité avec Excel 2007 et versions antérieures. Si tu crées un nouveau classeur, Microsoft recommande d'utiliser LOI.BINOMIALE.INVERSE, qui donne exactement les mêmes résultats avec une meilleure précision numérique. Mais si tu travailles sur des fichiers anciens ou si tu dois livrer des classeurs compatibles tous environnements, CRITERE.LOI.BINOMIALE reste tout à fait valide.
Syntaxe
=CRITERE.LOI.BINOMIALE(essais; prob_succès; alpha)Clique sur un argument pour aller à son explication.
Les trois paramètres sont obligatoires. Pour les nouveaux classeurs, préfère LOI.BINOMIALE.INVERSE qui a la même syntaxe et une meilleure précision. Le résultat est toujours un entier car il représente un nombre de succès.
Comprendre chaque paramètre de la fonction CRITERE.LOI.BINOMIALE
Les trois arguments arrivent toujours dans le même ordre : combien d'essais tu observes, quelle chance a chaque essai d'être un succès, puis le niveau de confiance que tu vises. Aucun n'est facultatif, il te faut les trois. Le couple du milieu et de la fin est celui qui piège : prob_succès reste petit (0,05 pour 5%) tandis qu'alpha grimpe haut (0,95 pour 95%), et les inverser donne un résultat faux sans la moindre alerte d'Excel.
essais
: le nombre total d'essais indépendants dans ton expérienceCe peut être le nombre de pièces testées, de visiteurs sur ton site, de candidats contactés ou de commandes traitées. Ce paramètre doit être un entier positif (supérieur ou égal à 0).
Excel tronque automatiquement les décimales si tu passes un nombre non entier. Par exemple, 100,8 sera traité comme 100.
prob_succès
: la probabilité qu'un seul essai soit un succèsCette valeur doit être comprise entre 0 et 1 exclus, exprimée en décimale. Un succès peut être un défaut de fabrication, une conversion, un clic ou n'importe quel événement selon ton contexte.
Attention au piège classique : si ton taux est de 5%, tu entres 0,05 et non 5. Excel attend une probabilité, pas un pourcentage.
Attention : Ne confonds pas prob_succès et alpha. prob_succès est ton taux d'événement attendu (souvent petit, comme 0,05 pour 5%). alpha est ton niveau de confiance (souvent élevé, comme 0,95 pour 95%). Les inverser fausse complètement le résultat sans déclencher d'erreur.
alpha
: le niveau de confiance ou seuil de probabilité cumulée que tu veux atteindreC'est une valeur entre 0 et 1 qui définit à quel point tu veux être sûr de ton résultat. Plus alpha est élevé, plus ton seuil sera conservateur : tu acceptes moins de risque, donc tu te fixes une limite plus haute.
alpha = 0,90 correspond à 90% de confiance (standard). alpha = 0,95 correspond à 95% de confiance (rigoureux). alpha = 0,99 correspond à 99% de confiance (très conservateur, souvent utilisé en contrôle qualité industriel).
Exemples pratiques pas à pas
Comment calculer un seuil d'alerte qualité avec la fonction CRITERE.LOI.BINOMIALE
La ligne de production sort des lots toute la journée et il faut savoir, sans arrêter la chaîne pour rien, à partir de combien de pièces défectueuses un lot devient suspect. Le taux de défaut historique est de 4 %, donc trouver quelques défauts est normal et n'a rien d'alarmant. Le vrai problème est que les lots n'ont pas tous la même taille, si bien qu'un seuil unique posé à la main serait trop sévère sur les petits lots et trop laxiste sur les gros. La fonction CRITERE.LOI.BINOMIALE calcule le seuil propre à chaque taille de lot, pour le niveau de confiance que tu retiens.
- 1Dans une cellule, écris
=CRITERE.LOI.BINOMIALE(. - 2En 1er argument : clique sur le nombre de pièces contrôlées,
B2. Cette cellule vaut200pour le lotL-001. C'est la seule référence qui change d'une ligne à l'autre, et c'est elle qui fait varier le seuil d'un lot au suivant. - 3En 2ᵉ argument : saisis la probabilité qu'une pièce sorte défectueuse,
0,04. Elle s'écrit en décimal et jamais en pourcentage. Un4tapé à sa place sortirait de l'intervalle autorisé, et la formule renverrait#NOMBRE!au lieu d'un seuil. - 4En 3ᵉ argument : saisis le niveau de confiance visé,
0,95. C'est celui qu'on intervertit le plus souvent avec le précédent, sans qu'Excel signale quoi que ce soit. La probabilité de défaut reste petite là où le niveau de confiance grimpe haut, et ce contraste suffit à les distinguer. Le porter à0,99remonterait le seuil de ce lot de13à15. - 5Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas. Chaque lot reçoit alors le seuil calculé sur sa propre taille,
10pour les150pièces du lotL-002et27pour les500du lotL-010.
=CRITERE.LOI.BINOMIALE(B2; 0,04; 0,95)200 pièces à 4 % de défaut, la fonction renvoie 13. C'est le plus petit nombre de défauts dont la probabilité cumulée franchit les 95 % : à 13 la probabilité atteint 96,9 %, alors qu'à 12 elle n'était encore qu'à 94,0 %. Le seuil n'est donc pas la moyenne attendue, qui vaut 8 défauts sur ce lot, mais la limite au-delà de laquelle le hasard n'explique plus ce que tu observes.
Comment déclencher une alerte quand les défauts dépassent le seuil avec les fonctions CRITERE.LOI.BINOMIALE et SI
Les lots contrôlés hier soir sont sur ton bureau avec leur nombre de défauts, et il faut trancher lot par lot avant la reprise de la production. Comparer les défauts entre eux ne mène nulle part puisque les lots n'ont pas la même taille, et lire le seuil dans un tableau annexe à chaque ligne prendrait la matinée. Tu veux le verdict directement en face de chaque lot, calculé sur sa propre taille. La fonction CRITERE.LOI.BINOMIALE fournit le seuil de la ligne et la fonction SI le compare aux défauts réellement constatés.
- 1Dans une cellule, écris
=SI(. - 2En 1er argument : clique sur les défauts constatés du lot,
C2, puis écris la comparaison au seuil et referme-la,>CRITERE.LOI.BINOMIALE(B2; 0,04; 0,95). La taille du lot y reste relative, enB2, pour que chaque ligne soit jugée sur son propre effectif. Le taux de défaut0,04et la confiance0,95sont écrits en dur, parce qu'ils décrivent le procédé et la règle de contrôle plutôt qu'un lot précis. Le>est strict, si bien qu'un lot tombant pile sur son seuil reste conforme. - 3En 2ᵉ argument : saisis le texte à afficher quand les défauts dépassent le seuil,
"Alerte". Les guillemets sont obligatoires. Sans eux, Excel chercherait une plage nomméeAlerte, ne la trouverait pas et renverrait#NOM?. - 4En 3ᵉ argument : saisis le texte à afficher dans le cas contraire,
"Conforme". Un vrai mot vaut mieux qu'une cellule laissée vide, qui ne distinguerait plus un lot validé d'un lot que personne n'a encore regardé. - 5Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas. Le verdict tombe alors lot par lot, chacun mesuré sur son propre seuil.
=SI(C2>CRITERE.LOI.BINOMIALE(B2; 0,04; 0,95); "Alerte"; "Conforme")L-101 compte 15 défauts pour un seuil calculé à 13, d'où l'alerte. Compare avec L-103 et ses 18 défauts, pourtant jugé conforme : il porte sur 300 pièces, son seuil vaut donc 18 lui aussi, et le test ne bascule qu'au-dessus. C'est tout l'intérêt de recalculer le seuil ligne par ligne plutôt que de comparer des nombres bruts, car 18 défauts sur 300 pièces et 15 sur 200 ne racontent pas la même histoire.
Comment gérer l'erreur #NOMBRE! d'un niveau de confiance invalide avec les fonctions CRITERE.LOI.BINOMIALE et SIERREUR
Le niveau de confiance des contrôles est désormais réglable depuis une colonne, pour que le service qualité puisse durcir ou assouplir la surveillance sans toucher aux formules. Le revers est arrivé vite : quelqu'un a saisi une confiance supérieure à 1 et toute la colonne s'est mise à afficher un code d'erreur, sans que personne comprenne d'où il venait. La fonction ne pardonne pas les probabilités hors de l'intervalle autorisé, et c'est heureux, parce qu'un tel seuil ne veut rien dire. La fonction SIERREUR intercepte le code et le remplace par un message qui envoie corriger la bonne cellule.
- 1Dans une cellule, écris
=SIERREUR(. - 2En 1er argument : écris le calcul à tenter et referme-le,
CRITERE.LOI.BINOMIALE(B2; 0,04; C2). La taille du lot enB2et le taux de défaut0,04ne réservent aucune surprise, puisqu'aucun des deux ne vient d'une saisie libre. Le niveau de confiance, lui, est désormais lu enC2, et c'est cette cellule qui porte le1,05de la troisième ligne. - 3En 2ᵉ argument : saisis le texte de repli,
"Seuil invalide". Il désigne le réglage à reprendre au lieu de masquer le problème, ce qu'une cellule vide ferait en laissant croire que le contrôle n'a jamais été demandé. - 4Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas. Chaque contrôle affiche alors son seuil, ou le message de repli quand la confiance saisie sort de l'intervalle que la fonction accepte.
=SIERREUR(CRITERE.LOI.BINOMIALE(B2; 0,04; C2); "Seuil invalide")1,05, sans doute un 105 % tapé un peu vite, et la fonction ne peut rien en faire puisqu'une probabilité ne dépasse jamais 1 : elle renvoie #NOMBRE!, que la fonction SIERREUR remplace par un message lisible. Les autres lignes montrent au passage ce que règle vraiment ce paramètre, le seuil montant de 13 à 15 quand on exige 99 % de confiance, et tombant à 8 quand on se contente de 50 %.
Les erreurs fréquentes avec la fonction CRITERE.LOI.BINOMIALE
Presque tous les soucis viennent du même réflexe : taper 5 au lieu de 0,05 pour un taux de 5%. Excel attend une probabilité entre 0 et 1, pas un pourcentage, et te renvoie aussitôt #NOMBRE!. Les autres pièges sont plus sournois car ils ne déclenchent aucune erreur : inverser prob_succès et alpha, ou confondre le seuil cumulé que renvoie la fonction avec la probabilité d'un nombre exact de succès.
Erreur #NOMBRE! : paramètres hors limites
prob_succès ou alpha sont en dehors de l'intervalle ]0 ; 1[, ou essais est négatif. Par exemple, entrer 5 au lieu de 0,05 pour un taux de 5% déclenche cette erreur. C'est l'erreur la plus fréquente avec cette fonction.
Solution : Vérifie que prob_succès et alpha sont bien des décimales (0,05 et non 5). Si tes valeurs viennent de cellules calculées, ajoute une protection : =SIERREUR(CRITERE.LOI.BINOMIALE(A1; B1; C1); "Paramètres invalides").
Résultat trop élevé ou contre-intuitif
Les paramètres prob_succès et alpha ont été inversés. Mettre 0,95 comme probabilité de succès au lieu de 0,05 produit un résultat complètement différent sans déclencher d'erreur, ce qui est particulièrement difficile à détecter.
Solution : Rappelle-toi : prob_succès est le taux de l'événement que tu surveilles (souvent petit, comme 0,04 pour 4%), alpha est ton niveau de confiance (souvent élevé, comme 0,95 pour 95%). Nomme tes cellules pour rendre la distinction visuelle.
Confusion entre distribution cumulée et probabilité individuelle
CRITERE.LOI.BINOMIALE travaille sur la distribution cumulée P(X ≤ k), pas sur la probabilité individuelle P(X = k). Le résultat représente un seuil maximum, pas une valeur exacte.
Solution : Reformule mentalement : « il y a alpha% de chances d'avoir AU MAXIMUM k succès ». Pour calculer la probabilité d'avoir exactement k succès, utilise LOI.BINOMIALE(k; essais; prob_succès; FAUX) à la place.
Résultat peu fiable avec un petit nombre d'essais
Avec moins de 20 essais, la loi binomiale discrète a très peu de valeurs possibles et le résultat peut sembler trop conservateur ou trop laxiste selon le cas.
Solution : Pour des analyses robustes, vise au minimum 30 essais. Si tu ne peux pas augmenter la taille de l'échantillon, réduis alpha (par exemple de 0,95 à 0,90) pour accepter plus d'incertitude et documente cette limitation dans ton rapport.
CRITERE.LOI.BINOMIALE vs LOI.BINOMIALE.INVERSE vs LOI.BINOMIALE vs LOI.POISSON
Réserve CRITERE.LOI.BINOMIALE aux fichiers qui doivent tourner sur Excel 2007 ou plus ancien : sur un classeur récent, LOI.BINOMIALE.INVERSE fait exactement le même calcul avec une meilleure précision. Si tu cherches une probabilité plutôt qu'un seuil de succès, c'est LOI.BINOMIALE qu'il te faut, et quand tes événements sont rares sur un grand volume, LOI.POISSON colle mieux que la loi binomiale.
| Critère | CRITERE.LOI.BINOMIALE | LOI.BINOMIALE.INVERSE | LOI.BINOMIALE | LOI.POISSON |
|---|---|---|---|---|
| Type de calcul | Inverse (cumulée) | Inverse (cumulée) | Directe (P ou cumulée) | Directe (événements rares) |
| Version Excel | Excel 2007 et avant | Excel 2010+ | Toutes versions | Toutes versions |
| Statut | Compatibilité uniquement | Recommandée | Standard | Standard |
| Résultat | Nombre de succès (k) | Nombre de succès (k) | Probabilité (0 à 1) | Probabilité (0 à 1) |
| Usage recommandé | Anciens classeurs | Nouveaux projets | Calculs standards | Événements rares |
Astuces avancées avec CRITERE.LOI.BINOMIALE
Automatise ta décision qualité avec un SI imbriqué
Au lieu de lire le résultat de CRITERE.LOI.BINOMIALE et de le comparer mentalement à ta mesure, imbrique-la directement dans un SI : =SI(NombreObservé > CRITERE.LOI.BINOMIALE(essais; prob; alpha); "Hors contrôle"; "Acceptable"). Ta feuille de contrôle devient un tableau de bord binaire : tu vois immédiatement les lignes en alerte sans aucun calcul intermédiaire.
Crée un tableau de sensibilité pour visualiser l'impact d'alpha
Configure un tableau à double entrée avec différentes valeurs d'alpha (0,80 / 0,90 / 0,95 / 0,99) en colonnes et différentes tailles d'échantillon en lignes. Tu verras comment le seuil d'acceptation évolue et tu pourras choisir ton niveau de rigueur en connaissance de cause : parfait pour présenter à des non-statisticiens sans avoir à expliquer les formules.
Documente tes hypothèses dans un onglet dédié
Ajoute un onglet « Hypothèses » dans ton classeur où tu notes d'où viennent ta valeur de prob_succès (mesure historique, norme secteur, estimation) et pourquoi tu as choisi ce niveau alpha. Dans six mois, tu seras soulagé de ne pas avoir à reconstruire le raisonnement. Et si une hypothèse change, tu sais exactement quelle cellule mettre à jour.
Questions fréquentes
Aucune différence de calcul : les deux fonctions donnent exactement le même résultat avec la même syntaxe. LOI.BINOMIALE.INVERSE est la version moderne (Excel 2010+) avec une meilleure précision numérique. CRITERE.LOI.BINOMIALE est conservée uniquement pour la compatibilité avec les anciens classeurs Excel 2007 et antérieurs. Si tu crées un nouveau classeur, utilise directement LOI.BINOMIALE.INVERSE.
Parce que la fonction renvoie un nombre de succès, et on ne peut pas avoir 7,5 pièces défectueuses ou 34,2 conversions. Excel cherche le plus petit entier k tel que la probabilité cumulée P(X ≤ k) atteigne au moins le seuil alpha, et renvoie cet entier. C'est un arrondi vers le haut implicite : si le seuil exact correspond à une valeur non entière, Excel prend l'entier supérieur pour garantir que la probabilité cumulée soit bien supérieure ou égale à alpha.
Alpha représente ton niveau de confiance souhaité. Avec alpha = 0,95 (95%), tu cherches le nombre de succès k tel qu'il y a 95% de chances d'avoir AU MAXIMUM k succès sur tes essais. C'est comme définir une limite de sécurité : si tu observes plus que k succès, tu peux être sûr à 95% que ce n'est pas dû au hasard et qu'il faut investiguer.
Oui, mais pour des échantillons très grands (plus de 1 000 essais), la distribution binomiale peut être approximée par une loi normale, ce qui est plus rapide à calculer. LOI.NORMALE.INVERSE peut alors être une alternative plus performante. En pratique, CRITERE.LOI.BINOMIALE fonctionne sans problème jusqu'à plusieurs milliers d'essais : le calcul reste instantané dans Excel.
Excel renvoie l'erreur #NOMBRE! car alpha doit être strictement compris entre 0 et 1. De même, si prob_succès est hors de cet intervalle ou si essais est négatif, tu obtiens la même erreur.
Protège tes formules avec SIERREUR ou vérifie tes paramètres en amont avec des conditions SI.
Pour aller plus loin
Continue sur ta lancée après la fonction CRITERE.LOI.BINOMIALE : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
