Comment utiliser la distribution hypergéométrique dans Excel

Dans le paysage des probabilités discrètes et de l’inférence statistique appliquée, la modélisation rigoureuse des tirages au sein d’une population finie sans remise constitue une problématique fondamentale. Alors que la majorité des approches introductives privilégient l’indépendance stochastique postulée par le schéma bernoullien classique, de nombreuses situations concrètes issues des sciences comportementales, de la recherche clinique, du contrôle qualité industriel ou de l’audit financier s’inscrivent dans un cadre où chaque observation altère substantiellement la composition du milieu résiduel. Ignorer cette dépendance séquentielle conduit inévitablement à biaiser l’estimation des erreurs types et à fausser les conclusions inférentielles, en particulier lorsque la fraction sondée représente une part non négligeable de l’univers statistique.

Le logiciel Microsoft Excel s’est progressivement imposé comme un vecteur d’ingénierie quantitative incontournable, démocratisant l’accès à des fonctions analytiques avancées autrefois réservées aux environnements de calcul scientifique spécialisés comme R, SAS ou MATLAB. L’introduction et le perfectionnement de la fonction LOI.HYPERGEOMETRIQUE.N (désignée sous l’appellation HYPGEOM.DIST dans les distributions anglo-saxonnes) offrent désormais aux chercheurs, analystes de données et praticiens une précision algorithmique remarquable pour quantifier exactement la survenue d’événements discrets sous contrainte d’épuisement de population. Maîtriser cette fonction requiert néanmoins une double compétence : appréhender avec rigueur l’architecture combinatoire de la loi hypergéométrique et apprivoiser les subtilités syntaxiques propres à l’écosystème du tableur.

Ce guide exhaustif a pour vocation d’articuler la théorie mathématique la plus stricte aux impératifs opérationnels de la modélisation sur tableur. À travers une démarche progressive jalonnée de démonstrations, d’analyses comparatives, d’exemples empiriques détaillés et d’implémentations interactives, ce document explore l’ensemble du cycle d’exploitation de la distribution hypergéométrique dans Excel : de la définition des paramètres fondamentaux jusqu’à l’automatisation par des scripts VBA, en passant par le calcul de la p-valeur du test exact de Fisher et la gestion des limites de calcul sur grands effectifs.

1. Fondements théoriques de la loi hypergéométrique

Pour aborder l’implémentation numérique de la distribution hypergéométrique, il est indispensable de poser préalablement son cadre probabiliste formel. Cette loi de probabilité discrète régit les phénomènes où le hasard s’exerce sur un ensemble borné d’éléments sans possibilité de réintroduction, conditionnant ainsi chaque étape du processus d’échantillonnage par la mémoire des états antérieurs.

1.1 Définition formelle et cadre probabiliste sans remise

Le schéma d’échantillonnage aléatoire simple sans remise s’articule autour d’une population finie, conventionnellement désignée par l’entier $N$. Au sein de cet ensemble dénombrable et clos, chaque individu statistique est classé de façon binaire et mutuellement exclusive selon qu’il possède ou non une caractéristique d’intérêt spécifique, qualifiée conventionnellement de « succès ». Le cardinal de ce sous-ensemble porteur de la caractéristique est noté $K$, tandis que le sous-ensemble complémentaire regroupant les « échecs » possède un cardinal de $N – K$. Lors de l’extraction consécutive ou simultanée d’un groupe d’individus de taille $n$ (avec $n le N$), chaque tirage individuel modifie irrévocablement la structure proportionnelle de la population restante, introduisant une dépendance statistique fondamentale entre les épreuves successives.

Sur le plan stochastique, la variable aléatoire discrète $X$ est définie comme le décompte absolu du nombre de succès observés dans l’échantillon prélevé de taille $n$. Contrairement au schéma de Bernoulli qui repose sur l’indépendance stricte et la stationnarité d’une probabilité de succès constante au fil d’essais répétés avec remise, le tirage hypergéométrique est intrinsèquement dynamique. L’état du système probabiliste à l’instant $t+1$ est strictement conditionné par l’historique des tirages survenus entre les instants $1$ et $t$. Cette absence d’invariance temporelle de la probabilité marginale interdit l’usage des lois à mémoire nulle et impose un recours exclusif à l’analyse combinatoire pour formaliser l’espace probabilisé associé.

La distinction épistémologique entre processus bernoullien et loi hypergéométrique touche au statut ontologique de la population. Dans le premier cas, la population est conceptualisée comme infinie ou renouvelable à l’identique par réinjection immédiate de l’unité prélevée. Dans le second cas, la population est appréhendée comme un réservoir fini de ressources discrètes dont l’épuisement progressif restreint l’espace des possibles. C’est précisément cette non-stationnarité des probabilités élémentaires de transition qui confère à la distribution hypergéométrique sa spécificité mathématique et sa valeur analytique pour les sciences empiriques opérant sur des cohortes restreintes.

1.2 Comparaison structurelle entre loi binomiale et loi hypergéométrique

L’écart fondamental entre la distribution binomiale et la distribution hypergéométrique réside dans l’hypothèse de remise (ou de remplacement). Dans une épreuve binomiale modélisée par les paramètres $n$ et $p$, la probabilité élémentaire $p = K / N$ demeure rigoureusement invariable d’une extraction à l’autre. À l’inverse, au sein du modèle hypergéométrique, la probabilité de sélectionner un succès lors du $i$-ième tirage, sachant que $j$ succès ont déjà été extraits au cours des $i-1$ étapes précédentes, s’exprime sous la forme conditionnelle :

$$P(\text{Succès}_i mid S_{i-1} = j) = \frac{K – j}{N – (i – 1)}$$

Cette formulation met en lumière la sensibilité du processus à l’épuisement des stocks. Si les tirages initiaux concentrent une proportion anormalement élevée de succès, la probabilité d’occurrence des succès ultérieurs s’effondre de manière mécanique, induisant une autocorrélation négative intrinsèque entre les résultats successifs.

D’un point de vue asymptotique, la loi hypergéométrique converge vers la loi binomiale lorsque la taille de la population $N$ tend vers l’infini, sous réserve que la proportion globale $p = K / N$ demeure constante. En pratique méthodologique, on considère généralement que lorsque la fraction de sondage (définie par le ratio $n / N$) est inférieure au seuil critique de 5 % voire 10 % (soit $n / N < 0{,}05$ ou $0{,}10$), les fluctuations de la probabilité conditionnelle deviennent négligeables. L’impact de l’altération de la population est alors si ténu que l’approximation binomiale est statistiquement acceptable.

Cependant, dès lors que le ratio d’échantillonnage excède ces seuils — une situation omniprésente lors de la collecte de données sur petits effectifs, en psychologie expérimentale clinique ou lors de tests de conformité sur des séries de production limitées —, l’utilisation injustifiée de la loi binomiale engendre une surestimation systématique de la variance théorique. Cette distorsion amplifie indûment l’incertitude apparente du modèle et dégrade la puissance des tests statistiques subséquents, justifiant l’obligation d’un calcul hypergéométrique exact sous Excel.

1.3 Propriétés mathématiques fondamentales : espérance, variance et asymétrie

L’analyse des moments d’ordre un et deux de la variable hypergéométrique $X \sim \mathcal{H}(N, K, n)$ révèle des analogies profondes avec le modèle binomial, tout en soulignant une divergence déterminante quant à la dispersion des valeurs autour de la tendance centrale. L’espérance mathématique de $X$ s’établit formellement comme le produit de la taille de l’échantillon par la proportion initiale de succès au sein de la population parente :

$$E(X) = n \cdot \frac{K}{N} = n \cdot p$$

Cette égalité parfaite entre l’espérance hypergéométrique et l’espérance binomiale démontre que l’absence de remise n’induit aucun biais sur l’estimation de la moyenne théorique : le nombre moyen de succès attendu demeure identique, que le tirage s’effectue avec ou sans réinjection.

La divergence mathématique s’exprime pleinement dans la formulation de la variance. La variance d’une variable hypergéométrique intègre de manière obligatoire le facteur de correction pour population finie (FCPF), également qualifié de coefficient d’exhaustivité :

$$V(X) = n \cdot \frac{K}{N} \cdot \left(1 – \frac{K}{N}\right) \cdot \left(\frac{N – n}{N – 1}\right) = n \cdot p \cdot (1 – p) \cdot \left(\frac{N – n}{N – 1}\right)$$

Puisque la taille de l’échantillon $n$ est supérieure ou égale à 1 dans tout protocole non trivial, le ratio $(N – n) / (N – 1)$ est strictement inférieur à l’unité dès lors que $n > 1$. Il en résulte que la variance de la loi hypergéométrique est systématiquement inférieure à la variance d’une loi binomiale de mêmes paramètres nominaux. Ce phénomène de contraction de la variance s’explique intuitivement : le tirage sans remise « verrouille » progressivement la variabilité résiduelle en épuisant les possibilités de déviation par rapport à la composition initiale.

Les moments d’ordre supérieur, à savoir le coefficient d’asymétrie (skewness) et le coefficient d’aplatissement (kurtosis), dépendent étroitement de la balance entre $K$ et $N – K$. L’asymétrie s’annule rigoureusement lorsque $K = N / 2$ (soit $p = 0{,}5$), conférant à la fonction de masse une symétrie axiale parfaite autour de $n/2$. Dès lors que $p ne 0{,}5$, la distribution présente une asymétrie positive si $p < 0{,}5$ ou négative si $p > 0{,}5$, amplifiée par la réduction de la taille de la population $N$. L’aplatissement reflète quant à lui une distribution plus resserrée autour de son mode que la distribution binomiale correspondante, conséquence directe du facteur de correction d’exhaustivité.

2. Formulation mathématique et décomposition des paramètres

Pour transposer avec succès les concepts probabilistes au sein des matrices de calcul d’Excel, il convient d’analyser l’architecture combinatoire qui gouverne le calcul de la probabilité ponctuelle d’une variable hypergéométrique, ainsi que la délimitation rigoureuse de son domaine de définition mathématique.

2.1 Analyse de l’équation combinatoire standard

La probabilité d’observer exactement $k$ succès au cours d’un tirage sans remise s’énonce selon la formule classique des quotients combinatoires. Cette équation mobilise la notion de combinaisons simples au sens de l’analyse combinatoire :

$$P(X = k) = \frac{\binom{K}{k} \binom{N – K}{n – k}}{\binom{N}{n}}$$

Où chaque coefficient binomial se définit par le rapport factoriel standard :

$$\binom{a}{b} = C_a^b = \frac{a!}{b!(a – b)!}$$

La signification combinatoire de cette équation repose sur la règle fondamentale du produit (ou principe multiplicatif) appliquée aux dénombrements d’événements conjoints et indépendants dans leur catégorisation :

• Le terme $\binom{K}{k}$ : quantifie le nombre exact de manières distinctes de sélectionner $k$ éléments porteurs du succès parmi le sous-ensemble disponible de $K$ succès présents dans la population.
• Le terme $\binom{N – K}{n – k}$ : détermine le nombre exhaustif de combinaisons permettant de prélever les $n – k$ éléments non porteurs (échecs) parmi les $N – K$ individus de la population ne présentant pas la caractéristique d’intérêt.
• Le numérateur conjoint $\binom{K}{k} \binom{N – K}{n – k}$ : constitue le produit cartésien dénombrant l’ensemble des configurations favorables à la réalisation simultanée de ces deux sélections distinctes.
• Le dénominateur $\binom{N}{n}$ : représente le cardinal de l’univers probabiliste global ($\Omega$), c’est-à-dire le nombre absolu d’échantillons uniques de taille $n$ susceptibles d’être extraits sans remise de la population totale de taille $N$.

La probabilité $P(X = k)$ apparaît ainsi comme le rapport pur entre le volume des arrangements d’échantillons favorables et l’espace global des tirages équiprobables possibles. C’est précisément cet édifice factoriel qu’Excel calcule en arrière-plan lors de l’appel aux fonctions dédiées.

2.2 Définition rigoureuse des quatre variables clés

La réussite de l’implémentation opérationnelle sur tableur repose sur l’identification non ambiguë des quatre paramètres constitutifs du modèle, dont les notations peuvent parfois prêter à confusion en raison de divergences de nomenclature entre les corpus théoriques et l’interface logicielle de Microsoft :

• $N$ (Taille de la population ou nombre_pop) : Cette grandeur scalaire représente le périmètre exhaustif de la collection d’objets, d’individus ou d’unités expérimentales composant le système clos étudié. Il s’agit d’un entier strictement positif délimitant l’univers de référence avant tout début de procédure de tirage.
• $K$ (Succès dans la population ou population_s) : Ce paramètre dénombre la quantité absolue d’unités au sein de la population $N$ qui partagent la condition binaire spécifique définie comme « succès ». Il respecte impérativement la contrainte $0 le K le N$.
• $n$ (Taille de l’échantillon ou nombre_échantillon) : Il correspond au volume global d’unités statistiques prélevées lors de l’opération d’échantillonnage. Ce tirage s’effectuant sans réinjection, la taille $n$ ne peut matériellement excéder la population totale $N$ ($1 le n le N$).
• $k$ (Succès dans l’échantillon ou échantillon_s) : C’est la valeur prise par la variable aléatoire $X$. Elle dénombre les occurrences effectives du succès observées au sein du groupe prélevé de taille $n$. Elle est conditionnée conjointement par la valeur de $n$ et celle de $K$.

Une confusion fréquente chez les utilisateurs débutants consiste à permuter $K$ et $n$, ou à inverser le numérateur et le dénominateur des ratios empiriques. Il convient de mémoriser que les variables indicées par « s » dans Excel font référence aux succès (au sein de l’échantillon ou de la population), tandis que les variables mentionnant « nombre » quantifient l’enveloppe globale du groupe considéré.

2.3 Conditions de validité et délimitation du support mathématique

La variable aléatoire $X$ ne peut pas prendre n’importe quelle valeur entière comprise entre $0$ et $n$. En effet, les contraintes combinatoires imposées par l’épuisement de la population définissent un support mathématique strict borné par deux valeurs extrêmes que le praticien doit impérativement appréhender :

La borne inférieure théorique, notée $k_{\min}$, est formalisée par l’expression :

$$k_{\min} = \max(0, n – (N – K))$$

Cette condition signifie que si la taille de l’échantillon prélevé $n$ excède le contingent total d’échecs disponibles dans la population ($N – K$), l’échantillonneur est mathématiquement contraint de tirer un certain nombre incompressible de succès. Par exemple, si une population contient 100 individus ($N=100$) dont seulement 10 échecs ($N-K=10$), un tirage de 20 unités ($n=20$) contiendra obligatoirement au minimum $20 – 10 = 10$ succès. Dans cette configuration, $P(X < 10) = 0$.

Symétriquement, la borne supérieure théorique, désignée par $k_{\max}$, est contrainte par :

$$k_{\max} = \min(n, K)$$

L’observateur ne peut en aucun cas récolter dans son échantillon plus de succès qu’il n’en existe dans la population globale ($k le K$), ni plus de succès que le nombre total d’individus extraits ($k le n$).

Le support admissible de la loi hypergéométrique est donc rigoureusement l’ensemble des entiers $k in \mathbb{N}$ satisfaisant à l’inégalité jointe :

$$\max(0, n – N + K) le k le \min(n, K)$$

Toute tentative d’évaluer la fonction en dehors de cet intervalle de validité conduit mathématiquement à une probabilité nulle ($P(X = k) = 0$), une situation que l’algorithme sous-jacent d’Excel gère parfois par le renvoi d’une erreur d’exécution si les contraintes d’intégrité relationnelle entre paramètres sont transgressées.

3. Introduction aux fonctions Excel dédiées à la loi hypergéométrique

L’environnement de calcul Microsoft Excel a connu d’importantes mutations dans son moteur statistique. Comprendre l’évolution de ses fonctions permet d’optimiser l’écriture des formules tout en garantissant la pérennité et la compatibilité des modèles élaborés.

3.1 La fonction LOI.HYPERGEOMETRIQUE.N (HYPGEOM.DIST) : syntaxe moderne

À compter de la publication d’Excel 2010, les équipes d’ingénierie de Microsoft ont entrepris une vaste refonte des fonctions statistiques standard afin de s’aligner sur les exigences de précision algorithmique fixées par la communauté scientifique internationale, notamment en exploitant des bibliothèques fiables d’évaluation des fonctions gamma et de factorielles. Cette refonte s’est traduite par l’introduction du suffixe « .N » (ou « .DIST » dans la version internationale de langue anglaise), donnant naissance à la fonction contemporaine standardisée :

LOI.HYPERGEOMETRIQUE.N(échantillon_s ; nombre_échantillon ; population_s ; nombre_pop ; cumulative)

Dans la déclinaison anglophone du logiciel, cette syntaxe correspond strictement à :

HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)

L’avancée majeure réside dans l’intégration du cinquième argument booléen (cumulative), qui faisait défaut dans les déclinaisons antérieures du tableur. Ce paramètre offre la possibilité d’arbitrer directement entre l’évaluation de la fonction de masse de probabilité exacte et celle de la fonction de répartition cumulative unilatérale, sans imposer à l’utilisateur de construire des sommes matricielles lourdes et sources d’erreurs d’arrondi. Cette standardisation garantit en outre une cohérence terminologique avec les autres lois fondamentales implémentées, telles que LOI.BINOMIALE.N ou LOI.NORMALE.N.

3.2 La fonction historique LOI.HYPERGEOMETRIQUE et rétrocompatibilité

Dans les versions d’Excel antérieures à 2010 (notamment Excel 2003 et 2007), l’unique fonction disponible pour modéliser ce schéma était la fonction LOI.HYPERGEOMETRIQUE (sans extension « .N »), correspondant à HYPGEOMDIST en anglais. Sa signature syntaxique se réduisait à quatre arguments obligatoires :

LOI.HYPERGEOMETRIQUE(échantillon_s ; nombre_échantillon ; population_s ; nombre_pop)

Cette forme originelle présentait une limitation structurelle : elle était strictement cantonnée au calcul de la probabilité ponctuelle exacte $P(X = k)$. Toute tentative d’obtenir une probabilité cumulée nécessitait l’encapsulation de la fonction dans une structure de sommation matricielle complexe du type :

=SOMME(LOI.HYPERGEOMETRIQUE(LIGNE(INDIRECT("0:" & k)) ; n ; K ; N))

Bien que Microsoft maintienne cette ancienne fonction à des fins de rétrocompatibilité pour assurer l’ouverture sans rupture des classeurs conçus au début des années 2000, son utilisation dans des projets contemporains est déconseillée. En effet, les anciens algorithmes présentaient une vulnérabilité accrue aux erreurs d’annulation catastrophique et de dépassement de capacité dès lors que les valeurs de $N$ devenaient importantes. La modernisation systématique des classeurs vers la syntaxe LOI.HYPERGEOMETRIQUE.N constitue donc un prérequis méthodologique d’assurance qualité.

3.3 Gestion des paramètres régionaux et séparateurs de syntaxe

L’utilisation de fonctions statistiques dans un contexte de recherche internationale ou d’entreprise multinationale soulève la question de la portabilité des formules face aux variations des paramètres régionaux des systèmes d’exploitation. Le système d’analyse d’Excel adapte son comportement selon les conventions typographiques locales définies dans les paramètres Windows ou macOS de la machine hôte :

• Environnements francophones : La virgule étant le séparateur décimal conventionnel (ex. 3,1415), Excel assigne obligatoirement le caractère point-virgule (;) comme délimiteur d’arguments au sein des fonctions. La formule s’écrit alors : =LOI.HYPERGEOMETRIQUE.N(k; n; K; N; FAUX).
• Environnements anglophones : Le point opérant comme séparateur décimal (ex. 3.1415), le délimiteur d’arguments standard devient la virgule (,). La syntaxe équivalente s’articule ainsi : =HYPGEOM.DIST(k, n, K, N, FALSE).

L’inattention portée à ces conventions provoque immédiatement des erreurs de syntaxe de type « Nous avons détecté une erreur dans votre formule » lors de transferts de code entre documentations techniques internationales et feuilles de calcul locales. Il est également salutaire de rappeler qu’Excel traduit automatiquement les noms de fonctions lors de l’ouverture d’un classeur sur une machine configurée dans une autre langue, pourvu que la structure des séparateurs ait été initialement intègre.

4. Analyse détaillée des arguments de LOI.HYPERGEOMETRIQUE.N

La maîtrise de la fonction moderne LOI.HYPERGEOMETRIQUE.N exige une compréhension chirurgicale de chacun de ses cinq arguments. Un paramétrage rigoureux conditionne non seulement l’exactitude des calculs, mais prévient également les écueils de référence circulaire ou de rupture de propagation matricielle.

4.1 Arguments relatifs à l’échantillon : échantillon_s et nombre_échantillon

Les deux premiers arguments positionnels délimitent le champ d’observation microscopique du modèle, à savoir la structure numérique du groupe extrait de la population mère :

1. échantillon_s (représentant $k$) : Cet argument spécifie le nombre quantitatif de succès dont on cherche à déterminer la probabilité d’apparition au sein de l’échantillon. Il s’agit impérativement d’un nombre entier naturel. Si une valeur décimale est transmise, l’algorithme d’Excel tronque automatiquement la partie décimale sans avertissement préalable (une saisie de 3,9 sera lue comme 3). Ce paramètre doit scrupuleusement satisfaire à l’inégalité de base $0 le k le n$.

2. nombre_échantillon (représentant $n$) : Cet argument définit la cardinalité exhaustive de l’échantillon extrait. Il détermine la capacité d’absorption maximale d’événements lors de l’expérience de tirage. Sa valeur doit obligatoirement être supérieure ou égale à échantillon_s. Dans le cadre de simulations dynamiques où $k$ varie le long d’une colonne tandis que $n$ demeure fixe, il convient de figer la référence de cellule associée à nombre_échantillon à l’aide des signes dollars (par exemple $B$3) pour éviter tout décalage intempestif lors de l’incrémentation automatique par poignée de recopie.

4.2 Arguments relatifs à la population : population_s et nombre_pop

Les troisième et quatrième arguments circonscrivent le niveau macroscopique du système probabiliste, à savoir les propriétés d’échelle de la population d’origine :

3. population_s (représentant $K$) : Cette valeur indique la quantité totale d’individus, objets ou unités statistiques qui arborent le statut de « succès » dans l’ensemble de la population cible. Ce nombre doit être un entier positif ou nul, rigoureusement inférieur ou égal à la population globale $N$. Une incohérence fréquente réside dans la saisie d’un ratio ou pourcentage (ex. 20%) au lieu de l’effectif brut absolu : transmettre 0,20 au lieu de 20 conduira Excel à tronquer la valeur à $0$, faussant l’intégralité du modèle probabiliste.

4. nombre_pop (représentant $N$) : Il s’agit du volume numérique total de la population de référence. Ce paramètre constitue le plafond absolu du modèle : les relations $N ge n$ et $N ge K$ sont obligatoires. Toute violation de ces inégalités fondamentales déclenche immédiatement une erreur d’évaluation logicielle. Tout comme pour les paramètres de l’échantillon, un ancrage absolu de ces cellules (ex. $B$1 et $B$2) est vivement préconisé dans l’architecture des feuilles de calcul méthodiques.

4.3 Le paramètre logique ‘cumulative’ : densité versus répartition

Le cinquième argument constitue un commutateur logique binaire déterminant la nature de la fonction mathématique exécutée par le moteur de calcul :

• Modalité FAUX (ou valeur numérique 0) : Calcule la fonction de masse de probabilité (densité discrète). Le tableur évalue strictement la probabilité ponctuelle d’obtenir un échantillon contenant un nombre de succès exactement égal à $k$, soit formellement $P(X = k)$. Cette modalité est sollicitée lors de recherches de cotes exactes ou dans le tracé des diagrammes en bâtons de distributions empiriques.
• Modalité VRAI (ou valeur numérique 1) : Calcule la fonction de répartition cumulative. Le tableur effectue en interne la sommation discrète des probabilités pour toutes les valeurs d’occurrence inférieures ou égales à $k$, soit :
  $$P(X le k) = \sum_{i=k_{\min}}^{k} P(X = i)$$

Cette modalité cumulative est indispensable pour la construction d’intervalles de confiance, l’estimation des risques d’erreur de première espèce dans les tests statistiques et le calcul de seuils de rejet en contrôle qualité unilatéral.

5. Mise en œuvre pratique pas à pas dans une feuille de calcul Excel

L’exploitation de fonctions statistiques requiert une rigueur d’ingénierie logicielle garantissant la traçabilité des hypothèses, la limitation des erreurs de manipulation et l’auditabilité des calculs. Nous détaillons ici l’architecture standard d’un modèle d’échantillonnage hypergéométrique sous Excel.

5.1 Structuration méthodologique des cellules et nommage des plages

Une bonne pratique de modélisation sur tableur commence par la ségrégation fonctionnelle de l’interface en trois zones distinctes : la zone des paramètres d’entrée, la zone des vérifications d’intégrité et la zone des résultats calculés.

Il est recommandé d’allouer une plage dédiée pour la saisie des quatre paramètres scalaires fondamentaux, par exemple dans les cellules B1 à B4 de la feuille de calcul :

• Cellule B1 : Taille de la population ($N$)
• Cellule B2 : Succès dans la population ($K$)
• Cellule B3 : Taille de l’échantillon ($n$)
• Cellule B4 : Succès observés ($k$)

Afin de rendre les formules auto-documentées et d’éviter les confusions d’indexation, il est judicieux de mobiliser le Gestionnaire de noms d’Excel (accessible via l’onglet Formules > Définir un nom). On assignera respectivement les identifiants : N_pop, K_succes, n_ech et k_obs.

Il est en outre fortement recommandé d’implémenter des règles de validation de données (onglet Données > Validation des données) sur ces cellules afin de proscrire toute saisie de valeurs négatives, de nombres réels non entiers, ou d’incohérences hiérarchiques telles qu’un n_ech supérieur à N_pop.

5.2 Calcul pas à pas d’une probabilité ponctuelle exacte P(X = k)

Pour calculer la probabilité exacte d’observer précisément $k$ succès, positionnez-vous sur la cellule cible (par exemple B6) et saisissez la formule mobilisant la plage de noms préalablement établie :

=LOI.HYPERGEOMETRIQUE.N(k_obs ; n_ech ; K_succes ; N_pop ; FAUX)

À des fins de validation algorithmique, il est formateur de construire parallèlement le calcul combinatoire manuel en exploitant la fonction native COMBIN d’Excel dans la cellule adjacente C6 :

=(COMBIN(K_succes ; k_obs) * COMBIN(N_pop - K_succes ; n_ech - k_obs)) / COMBIN(N_pop ; n_ech)

L’exécution de ces deux formules renvoie un résultat strictement superposable au millionième près. Il convient de formater la cellule résultante sous forme de pourcentage statistique en appliquant un format de nombre personnalisé à quatre décimales (ex. 0,0000 %), afin de rendre lisibles les probabilités d’événements rares sans masquer leur significativité.

5.3 Calcul pas à pas des probabilités cumulées unilatérales et bilatérales

Dans la pratique inférentielle, les questions de recherche se formulent rarement sous la forme ponctuelle stricte $P(X = k)$, mais plutôt sous la forme de seuils de dépassement cumulatifs unilatéraux ou bilatéraux :

1. Probabilité cumulée inférieure $P(X le k)$ : Elle s’obtient directement en passant le commutateur logique à VRAI :

=LOI.HYPERGEOMETRIQUE.N(k_obs ; n_ech ; K_succes ; N_pop ; VRAI)

2. Probabilité cumulée supérieure stricte $P(X > k)$ : En vertu de l’axiome de complémentarité de l’espace probabilisé ($P(X > k) = 1 – P(X le k)$), la formule sous Excel s’écrit :

=1 - LOI.HYPERGEOMETRIQUE.N(k_obs ; n_ech ; K_succes ; N_pop ; VRAI)

3. Probabilité cumulée supérieure au sens large $P(X ge k)$ : Étant donné que la distribution hypergéométrique est une loi discrète définie exclusivement sur l’ensemble des entiers naturels, l’égalité $P(X ge k) = 1 – P(X le k – 1)$ s’applique impérativement. La transcription Excel s’établit comme suit :

=1 - LOI.HYPERGEOMETRIQUE.N(k_obs - 1 ; n_ech ; K_succes ; N_pop ; VRAI)

4. Probabilité d’encadrement central $P(k_1 le X le k_2)$ : Pour déterminer la probabilité que le nombre de succès soit compris entre deux bornes discrètes inclusives $k_1$ et $k_2$ (avec $k_1 < k_2$), on applique le principe de soustraction des fonctions de répartition :

=LOI.HYPERGEOMETRIQUE.N(k2 ; n_ech ; K_succes ; N_pop ; VRAI) - LOI.HYPERGEOMETRIQUE.N(k1 - 1 ; n_ech ; K_succes ; N_pop ; VRAI)

Cette approche modulaire permet d’évaluer n’importe quel segment probabiliste sur la chaîne de support de la distribution.

6. Exemples pédagogiques classiques de calcul combinatoire

Afin d’ancrer la théorie dans des contextes applicatifs concrets, nous abordons ici trois cas emblématiques résolus étape par étape à l’aide des fonctionnalités d’Excel.

6.1 L’exemple paradigmatique du tirage de cartes sans remise

Considérons un jeu standardisé de 52 cartes à jouer ($N = 52$). Ce jeu renferme exactement quatre reines ($K = 4$). Un joueur extrait successivement deux cartes de ce paquet sans procéder à la moindre remise ($n = 2$). On cherche à quantifier la probabilité exacte que le joueur obtienne une main composée rigoureusement de deux reines ($k = 2$).

Sur le plan mathématique analytique, ce problème correspond à l’arbre de probabilité conditionnelle élémentaire :

$$P(\text{Reine}_1 \cap \text{Reine}_2) = P(\text{Reine}_1) \cdot P(\text{Reine}_2 mid \text{Reine}_1) = \frac{4}{52} \cdot \frac{3}{51} = \frac{12}{2652} \approx 0{,}00452489$$

Au sein d’Excel, la transcription de cette expérience par la distribution hypergéométrique s’opère instantanément par la formule suivante :

=LOI.HYPERGEOMETRIQUE.N(2 ; 2 ; 4 ; 52 ; FAUX)

Le tableur restitue la valeur numérique exacte de 0,004525 (soit $0{,}4525 %$). Supposons désormais que nous élargissions le tirage à une main de poker constituée de 5 cartes ($n = 5$) et que nous souhaitions déterminer la probabilité d’obtenir au moins une reine ($k ge 1$). L’application de la règle du complémentaire via l’argument cumulatif donne :

=1 - LOI.HYPERGEOMETRIQUE.N(0 ; 5 ; 4 ; 52 ; VRAI)

Le résultat affiché par Excel est de 0,341158 (soit environ $34{,}12 %$), validant la puissance de l’évaluation cumulée face au calcul combinatoire direct des cas disjoints (une, deux, trois ou quatre reines).

6.2 Contrôle qualité industriel et échantillonnage de lots finis

Dans un contexte d’ingénierie manufacturière, une entreprise réceptionne une expédition de 100 microprocesseurs critiques ($N = 100$). Le protocole de contrôle de conformité stipule qu’un audit exhaustif étant trop coûteux, l’ingénieur préleveur doit extraire au hasard et sans remise un échantillon de contrôle de 15 unités ($n = 15$). Le protocole prévoit le rejet intégral du lot si l’échantillon comporte 2 composants défectueux ou plus.

Supposons que le lot contienne à l’insu de l’acheteur exactement 10 microprocesseurs défectueux ($K = 10$). Quelle est la probabilité que le lot soit accepté à tort (risque du client), c’est-à-dire que le préleveur observe 0 ou 1 composant non conforme ($k le 1$) ?

La probabilité d’acceptation du lot s’évalue dans Excel par l’instruction :

=LOI.HYPERGEOMETRIQUE.N(1 ; 15 ; 10 ; 100 ; VRAI)

Le tableur renvoie une probabilité d’acceptation égale à $0{,}5486$ (soit $54{,}86 %$). La probabilité de rejet légitime du lot ($k ge 2$) correspond donc au complémentaire :

=1 - LOI.HYPERGEOMETRIQUE.N(1 ; 15 ; 10 ; 100 ; VRAI)

Soit $45{,}14 %$. Cette modélisation constitue la clé de voûte de l’élaboration des courbes d’efficacité (Operating Characteristic Curves – OC) : en faisant varier la valeur théorique de $K$ de $0$ à $30$ dans une colonne d’Excel, l’ingénieur peut visualiser la courbe de sensibilité du plan d’échantillonnage et ajuster la taille d’échantillon $n$ pour garantir un niveau de protection optimal.

6.3 Tirages de loterie et jeux de hasard institutionnels

Les jeux de loterie nationaux fournissent une illustration idéale d’applications combinatoires hypergéométriques à grande échelle. Considérons une grille standard de loto comprenant un tirage de 6 numéros gagnants parmi une population totale de 49 boules numérotées, sans remise ($N = 49$, $n = 6$, $K = 6$). La variable $k$ représente le nombre de numéros gagnants cochés sur la grille d’un joueur.

Nous pouvons établir sous Excel la matrice complète d’évaluation des probabilités de gain selon le nombre d’occurrences favorables $k$ (allant de 0 à 6 numéros exacts) en déployant la formule suivante (en considérant que la valeur de $k$ est renseignée en cellule A10) :

=LOI.HYPERGEOMETRIQUE.N(A10 ; 6 ; 6 ; 49 ; FAUX)

Les résultats calculés par Excel mettent en lumière la structure probabiliste du jeu :

• $k = 6$ (Jackpot) : =LOI.HYPERGEOMETRIQUE.N(6; 6; 6; 49; FAUX) $\approx 7{,}1511 \times 10^{-8}$ (soit 1 chance sur 13 983 816)
• $k = 5$ : =LOI.HYPERGEOMETRIQUE.N(5; 6; 6; 49; FAUX) $\approx 1{,}8449 \times 10^{-5}$ (soit 1 chance sur 54 201)
• $k = 4$ : =LOI.HYPERGEOMETRIQUE.N(4; 6; 6; 49; FAUX) $\approx 9{,}6862 \times 10^{-4}$ (soit 1 chance sur 1 032)
• $k = 3$ : =LOI.HYPERGEOMETRIQUE.N(3; 6; 6; 49; FAUX) $\approx 0{,}017650$ (soit 1 chance sur 57)
• $k = 0$ : =LOI.HYPERGEOMETRIQUE.N(0; 6; 6; 49; FAUX) $\approx 0{,}435965$ (soit $43{,}60 %$ de probabilité de n’avoir aucun bon numéro)

La sommation vérificatrice de l’ensemble de ces probabilités discrètes via la fonction =SOMME(...) aboutit exactement à $1{,}0000000$, confirmant que l’ensemble du spectre d’événements de l’univers probabilisé est couvert.

7. Applications empiriques en recherche psychologique et sciences comportementales

Si la théorie des jeux et le contrôle industriel utilisent fréquemment la loi hypergéométrique, les sciences comportementales et la psychométrie constituent un champ d’application où les hypothèses d’indépendance sont fréquemment mises à mal par l’étroitesse des cohortes étudiées.

7.1 Échantillonnage au sein de cohortes cliniques fermées restreintes

En neuropsychologie clinique ou en psychiatrie expérimentale, les praticiens travaillent régulièrement avec des cohortes institutionnelles fermées d’effectif restreint, par exemple une unité spécialisée accueillant $N = 30$ patients atteints d’un trouble bipolaire réfractaire aux sels de lithium. Parmi ces patients, un sous-groupe de $K = 8$ individus présente une mutation génétique rare affectant un transporteur sérotoninergique. Dans le cadre d’un essai pilote en double aveugle évaluant une nouvelle molécule psychoactive, le chercheur sélectionne sans remise un sous-échantillon de $n = 10$ sujets assignés au groupe recevant la molécule expérimentale, les 20 autres demeurant sous placebo.

La question du biais d’assignation est ici cruciale : quelle est la probabilité que le groupe expérimental contienne par inadvertance 5 patients porteurs de la mutation ou plus ($k ge 5$), créant ainsi un déséquilibre génétique majeur faussant l’interprétation de l’efficacité thérapeutique ?

Sous Excel, le calcul s’établit par l’expression :

=1 - LOI.HYPERGEOMETRIQUE.N(4 ; 10 ; 8 ; 30 ; VRAI)

Le logiciel retourne une valeur de $0{,}0308$ (soit $3{,}08 %$). Bien que ce risque soit relativement faible, sa modélisation ex ante permet aux expérimentateurs d’évaluer la pertinence de recourir à une randomisation stratifiée plutôt qu’à un tirage aléatoire simple sans remise, prévenant ainsi la contamination des données de recherche.

7.2 Protocoles cognitifs et tâches informatisées de tri sans remise

Dans l’évaluation des fonctions exécutives et de la flexibilité cognitive, plusieurs tests informatisés renommés reposent sur des banques fermées de stimuli visuels. Considérons une tâche expérimentale dérivée du Wisconsin Card Sorting Test (WCST) ou de l’Iowa Gambling Task (IGT), au sein de laquelle un sujet est confronté à un paquet virtuel de $N = 60$ cartes stimuli qui s’épuise sans remise au cours de l’épreuve. Dans cette banque fermée, $K = 18$ cartes comportent un signal de punition asymétrique (renforcement négatif).

Au terme d’un bloc exploratoire de $n = 12$ essais, un participant amnésique ou présentant des lésions du cortex préfrontal orbitaire a tiré $k = 7$ cartes associées à une punition. L’analyste cherche à déterminer si cette performance traduit un déficit spécifique d’apprentissage par renforcement ou si elle peut être attribuée aux fluctuations combinatoires du hasard.

La probabilité d’observer par pur hasard une telle concentration de cartes punitives ($k ge 7$) se quantifie sous Excel par la relation :

=1 - LOI.HYPERGEOMETRIQUE.N(6 ; 12 ; 18 ; 60 ; VRAI)

Excel restitue $P(X ge 7) = 0{,}0224$ (soit $2{,}24 %$). Étant donné que cette valeur est inférieure au seuil conventionnel de significativité statistique de $\alpha = 0{,}05$, le chercheur peut rejeter l’hypothèse nulle de tirage aléatoire et conclure à un biais d’orientation pathologique du participant vers les options sous-optimales.

7.3 Sélection d’items dans des banques finies de tests psychométriques

Les protocoles de passation adaptative ou d’abréviation de questionnaires psychométriques posent fréquemment le problème du tirage d’items sans remise au sein d’une banque fermée d’étalonnage. Supposons qu’un inventaire d’évaluation de la personnalité comportant $N = 50$ items renferme $K = 15$ énoncés à formulation inversée (destinés à neutraliser les biais d’acquiescement). Un chercheur souhaite créer une forme courte exploratoire en sélectionnant aléatoirement $n = 10$ items.

Le contrôle de la représentativité psychométrique exige que la forme courte contienne une proportion d’items inversés similaire à la version originale, soit idéalement 3 items inversés ($10 \times 15/50 = 3$). Quelle est la probabilité que la forme abrégée comprenne un nombre déséquilibré d’items inversés, défini par $k le 1$ ou $k ge 5$ ?

Pour résoudre ce problème dans Excel, nous calculons conjointement les probabilités des deux queues de distribution :

=LOI.HYPERGEOMETRIQUE.N(1 ; 10 ; 15 ; 50 ; VRAI) + (1 - LOI.HYPERGEOMETRIQUE.N(4 ; 10 ; 15 ; 50 ; VRAI))

Le calcul produit un résultat d’environ $0{,}1472 + 0{,}1378 = 0{,}2850$ ($28{,}50 %$). Il apparaît ainsi qu’un tirage purement aléatoire sans remise génère près de 28,5 % de configurations présentant un biais d’acquiescement non maîtrisé. Ce constat conduit les psychométriciens à substituer au tirage aléatoire des procédures de sélection stratifiée optimisée.

8. Construction d’une table de distribution complète et visualisation graphique

Pour analyser un système probabiliste discret, la restitution globale de sa distribution sous forme tabulaire et graphique est bien plus informative que le calcul isolé de points ponctuels. Nous abordons ici les techniques permettant de générer une table dynamique et d’ériger des représentations visuelles conformes aux normes de publication.

8.1 Génération dynamique du vecteur de support complet

Dans les versions modernes d’Excel prenant en charge les formules de tableaux dynamiques (Excel 365 et Excel 2021), la création de la suite des valeurs de $k$ peut s’automatiser entièrement à l’aide de la fonction SEQUENCE. Rappelons que le support de la distribution s’étend de $k_{\min} = \max(0, n – N + K)$ à $k_{\max} = \min(n, K)$.

En admettant que nos paramètres soient déclarés dans les cellules N_pop, K_succes et n_ech, on peut calculer automatiquement dans deux cellules distinctes les bornes du support :

• Cellule D1 ($k_{\min}$) : =MAX(0 ; n_ech - (N_pop - K_succes))
• Cellule D2 ($k_{\max}$) : =MIN(n_ech ; K_succes)

Dans la colonne A, à partir de la cellule A6, on déploie dynamiquement l’ensemble des valeurs admissibles de $k$ par la formule suivante :

=SEQUENCE(D2 - D1 + 1 ; 1 ; D1 ; 1)

Dans la colonne adjacente B6, on génère le vecteur matriciel des densités de probabilités exactes $P(X = k)$ en référençant la plage propagée :

=LOI.HYPERGEOMETRIQUE.N(A6# ; n_ech ; K_succes ; N_pop ; FAUX)

Le symbole dièse (#) assure la propagation automatique de la fonction sur toute la longueur de la plage générée par SEQUENCE. Dans la colonne C6, on implémente la fonction de répartition cumulative :

=LOI.HYPERGEOMETRIQUE.N(A6# ; n_ech ; K_succes ; N_pop ; VRAI)

Enfin, une vérification d’intégrité est insérée en bas de colonne : l’instruction =SOMME(B6#) doit renvoyer rigoureusement la valeur numérique 1,000000.

8.2 Conception d’un histogramme de masse de probabilité standardisé

La représentation visuelle de la fonction de masse requiert la construction d’un diagramme en colonnes (ou histogramme discret) respectant les conventions sémiologiques statistiques :

1. Sélectionnez la plage des valeurs calculées de $P(X = k)$ (colonne B) et accédez à l’onglet Insertion > Graphiques > Histogramme groupé (colonnes 2D).
2. Cliquez avec le bouton droit sur le graphique, sélectionnez Sélectionner des données, puis sous l’étiquette Étiquettes de l’axe horizontal (abscisses), cliquez sur Modifier et sélectionnez la plage des valeurs de $k$ (colonne A).
3. Formatez les séries de données : cliquez avec le bouton droit sur les barres verticales, sélectionnez Mettre en forme une série de données, et modifiez le paramètre Largeur du coup d’œil (intervalle) à environ 50 % voire 100 %. Contrairement aux variables continues où l’intervalle est fixé à 0 %, les variables discrètes doivent conserver un espacement visuel tangible matérialisant l’absence de valeurs intermédiaires non entières.
4. Ajoutez des étiquettes de données en tête de colonnes pour les valeurs significatives afin de faciliter la lecture directe des pourcentages sans surcharge visuelle.

Il est en outre possible d’implémenter une mise en forme conditionnelle sur les séries graphiques pour colorer d’une teinte distincte (ex. rouge cramoisi) les barres correspondant à la région critique de rejet d’une hypothèse ($p < 0{,}05$), isolant visuellement l’aire de décision statistique.

8.3 Tracé de l’ogive de distribution cumulative

L’observation simultanée de la densité et de la distribution cumulative s’opère par la réalisation d’un graphique combiné à double échelle :

1. Sélectionnez conjointement les colonnes des abscisses $k$, des probabilités ponctuelles $P(X = k)$ et des probabilités cumulées $P(X le k)$.
2. Rendez-vous dans l’onglet Insertion > Graphiques combinés > Créer un graphique combiné personnalisé.
3. Configurez la série $P(X = k)$ sous forme d’Histogramme groupé assigné à l’Axe principal (échelle de gauche bornée entre 0 et la densité maximale).
4. Configurez la série $P(X le k)$ sous forme de Ligne avec marqueurs assignée à l’Axe secondaire (échelle de droite rigoureusement verrouillée entre $0{,}0$ et $1{,}0$).

Ce double tracé permet d’apprécier la vitesse de saturation de l’espace probabilisé. Le point d’inflexion maximal de la courbe sigmoïde cumulative coïncide géométriquement avec le mode de la distribution hypergéométrique, facilitant la lecture intuitive des quantiles clés (premier quartile, médiane à 0,5, troisième quartile).

9. Tests d’hypothèses et test exact de Fisher via la distribution hypergéométrique

L’un des apports majeurs de la loi hypergéométrique à la statistique inférentielle moderne réside dans la modélisation du célèbre test exact de Fisher, conçu pour éprouver l’indépendance de deux variables qualitatives dichotomiques.

9.1 Fondement mathématique du test exact de Fisher pour tableaux 2×2

Le test exact de Fisher s’applique aux tableaux de contingence croisant deux facteurs binaires au sein d’un format matriciel $2 \times 2$ standardisé :

Classification	Groupe 1 (Succès)	Groupe 2 (Échec)	Total Marginal
Condition Traitement	$a$	$b$	$a + b$
Condition Contrôle	$c$	$d$	$c + d$
Total Marginal	$a + c$	$b + d$	$N = a + b + c + d$

L’intuition de Ronald A. Fisher a été de conditionner l’inférence sur la totalité des totaux marginaux du tableau : les sommes en lignes ($a+b$ et $c+d$) ainsi que les sommes en colonnes ($a+c$ et $b+d$) sont considérées comme fixées à l’avance par le protocole ou par conditionnement mathématique. Sous l’hypothèse nulle ($H_0$) d’absence d’association entre le traitement et la réponse, la valeur de la cellule supérieure gauche $a$ suit de manière exacte une loi hypergéométrique régie par les paramètres de marge :

• Population totale : $N = a + b + c + d$
• Succès totaux dans la population : $K = a + c$ (marge de la première colonne)
• Taille de l’échantillon prélevé : $n = a + b$ (marge de la première ligne)
• Succès observés dans l’échantillon : $k = a$

Le test de Fisher résout ainsi de façon élégante les failles structurelles du test d’indépendance du Chi-deux ($\chi^2$), ce dernier perdant toute validité théorique lorsque les effectifs théoriques attendus tombent en dessous de 5 individus par cellule (règle de Cochran).

9.2 Calcul manuel d’une p-valeur exacte dans Excel à l’aide de LOI.HYPERGEOMETRIQUE.N

Supposons un essai clinique portant sur 24 sujets ($N=24$) répartis entre un groupe sous traitement ($a+b=12$) et un groupe sous placebo ($c+d=12$). On observe une amélioration clinique chez 9 patients traités ($a=9$, d’où $b=3$) et chez seulement 2 patients sous placebo ($c=2$, d’où $d=10$). Le total marginal des améliorations est donc de $K = a+c = 11$.

Pour tester l’hypothèse unilatérale selon laquelle le traitement augmente significativement le taux d’amélioration ($H_1 : a ge 9$), nous devons évaluer la somme des probabilités de la table observée et des tables plus extrêmes conservant strictement les mêmes totaux marginaux :

• Table observée : $a = 9$
• Table plus extrême 1 : $a = 10$
• Table plus extrême 2 : $a = 11$ (valeur maximale possible car $K=11$)

Dans Excel, la p-valeur unilatérale supérieure s’obtient immédiatement à l’aide de l’argument cumulatif :

=1 - LOI.HYPERGEOMETRIQUE.N(8 ; 12 ; 11 ; 24 ; VRAI)

Le calcul renvoie la valeur $p = 0{,}00684$ ($0{,}684 %$), attestant d’une supériorité hautement significative du traitement au seuil conventionnel $\alpha = 0{,}01$.

Pour le test bilatéral de Fisher, la règle standardisée consiste à sommer les probabilités de toutes les tables théoriquement envisageables (pour $a$ variant de $k_{\min}$ à $k_{\max}$) dont la probabilité ponctuelle hypergéométrique est inférieure ou égale à celle de la table observée ($P(a) le P(a_{\text{obs}})$). Dans notre exemple, on construit la colonne des probabilités de $a=0$ jusqu’à $a=11$, et on applique l’instruction :

=SOMME.SI(Plage_Probas ; "<=" & Proba_Observée ; Plage_Probas)

Cette méthodologie d’ingénierie statistique fournit une implémentation exacte du test bilatéral sans recourir à un logiciel tiers.

9.3 Comparaison critique avec la fonction native TEST.FISHER d’Excel

Une confusion terminologique majeure piège de nombreux chercheurs au sein de la bibliothèque de fonctions d’Excel : l’existence d’une fonction native intitulée TEST.FISHER (ou FISHERTEST).

Il est impératif d’avertir le praticien : la fonction TEST.FISHER d’Excel ne réalise absolument pas le test exact de Fisher pour tableaux de contingence !

Cette fonction implémente en réalité la transformation z de Fisher, un opérateur mathématique continu appliqué au coefficient de corrélation linéaire de Bravais-Pearson ($r$) pour normaliser sa distribution d’échantillonnage asymptotique :

$$z = \frac{1}{2} \ln\left(\frac{1 + r}{1 – r}\right) = \text{arc\tanh}(r)$$

Toute tentative d’injecter des effectifs de contingence au sein de TEST.FISHER aboutit soit à des aberrations statistiques, soit à une erreur logicielle si la valeur n’est pas comprise entre $-1$ et $+1$. Pour accomplir un authentique test exact de Fisher sous Excel, il n’existe pas d’autre alternative que de modéliser manuellement la distribution par la fonction LOI.HYPERGEOMETRIQUE.N, ou de développer une fonction personnalisée en Visual Basic for Applications (VBA).

10. Diagnostic des erreurs courantes et optimisation algorithmique

Le moteur d’évaluation d’Excel est régi par des règles de typage et de bornage arithmétique strictes. Savoir diagnostiquer rapidement l’origine d’un dysfonctionnement au sein d’une formule hypergéométrique fait partie intégrante de l’expertise statistique.

10.1 Traitement de l’erreur #NOMBRE! (#NUM!) et violations de bornes

Le message d’erreur d’exécution #NOMBRE! constitue l’anomalie la plus fréquente lors de la manipulation de LOI.HYPERGEOMETRIQUE.N. Ce code indique que l’un des arguments transmis est numériquement incompatible avec les restrictions mathématiques du domaine de définition. Voici les causes génératrices et leurs remèdes :

• $k > n$ ou $k > K$ : L’utilisateur sollicite un nombre de succès supérieur à la taille de l’échantillon ou au total des succès disponibles. Solution : Imbriquez un contrôle =MIN(n; K) dans la cellule d’entrée.
• $n > N$ ou $K > N$ : La taille d’échantillon ou le réservoir de succès outrepasse la taille de la population parente. Solution : Vérifier la cohérence macroscopique des données d’inventaire.
• $k < 0$, $n le 0$, $K < 0$ ou $N le 0$ : Saisie de scalaires strictement négatifs ou nuls sur des grandeurs de dénombrement physiques obligatoirement positives.
• $k < n – (N – K)$ : La valeur de $k$ testée est inférieure au plancher incompressible imposé par la saturation des échecs.

Afin de prémunir vos modèles contre ces arrêts d’exécution et sécuriser la présentation aux utilisateurs finaux, encapsulez la formule dans un wrapper conditionnel robuste :

=SI(OU(k_obs < MAX(0 ; n_ech - N_pop + K_succes) ; k_obs > MIN(n_ech ; K_succes)) ; 0 ; LOI.HYPERGEOMETRIQUE.N(k_obs ; n_ech ; K_succes ; N_pop ; FAUX))

Cette structure garantit le renvoi propre d’une probabilité nulle lorsque $k$ se situe hors du support, sans faire planter la feuille de calcul.

10.2 Analyse de l’erreur #VALEUR! (#VALUE!) et problèmes de formatage

Le code d’erreur #VALEUR! signale une incompatibilité de type de données au sens informatique du terme :

• Présence de caractères textuels ou d’espaces masqués : L’une des cellules référencées contient une chaîne non numérique invisible (ex. une espace insécable consécutive à un import web, typographiquement codée sous l’entité ASCII 160). L’utilisation préalable de la formule =SUPPRESPACE(EPURAGE(A1)) permet d’assainir les flux de données brutes.
• Formatage erroné des booléens : L’argument cumulative a été renseigné par une chaîne de caractères encadrée de guillemets, telle que "FAUX" ou "VRAI". Excel peut échouer à caster cette chaîne en un type logique natif. Renseignez directement les identificateurs booléens réservés non guillemetés FAUX / VRAI ou les équivalents entiers 0 / 1.
• Gestion des valeurs décimales non intentionnelles : Si un argument résulte d’un calcul intermédiaire (ex. $n = 100 \times 0{,}155 = 15{,}5$), Excel tronque implicitement à $15$. Si l’utilisateur attendait un arrondi commercial à $16$, une discordance interne survient. Recourez explicitement à la fonction ARRONDI(valeur ; 0) ou ENT(...) en amont.

10.3 Instabilité numérique et limites de calcul sur très grands effectifs

Bien que l’algorithme révisé d’Excel 2010 ait considérablement repoussé les seuils de saturation, le calcul de la distribution hypergéométrique met en jeu des fonctions factorielles qui croissent à des cadences vertigineuses. La fonction factorielle standard plafonne sous Excel à $170! \approx 7{,}257 \times 10^{306}$. Toute valeur supérieure déclenche un dépassement de capacité arithmétique (overflow).

Pour surmonter cette barrière, le moteur interne de LOI.HYPERGEOMETRIQUE.N exploite le logarithme népérien de la fonction Gamma (identifiable via la fonction de feuille de calcul LNGAMMA), appliquant la transformation fondamentale :

$$\ln(n!) = \ln(\Gamma(n + 1))$$

$$\ln\left(\frac{\binom{K}{k} \binom{N-K}{n-k}}{\binom{N}{n}}\right) = \ln\binom{K}{k} + \ln\binom{N-K}{n-k} – \ln\binom{N}{n}$$

Puis en prenant l’exponentielle du résultat global. Néanmoins, lorsque $N$ dépasse plusieurs dizaines de millions d’éléments conjugué à des tailles d’échantillons massives, des erreurs d’annulation catastrophique dans la mantisse flottante en double précision (norme IEEE 754 à 53 bits de précision) peuvent provoquer des dérives dans les queues de distribution extrêmes. Si vos travaux exigent l’estimation de p-valeurs infinitésimales (ex. $p < 10^{-15}$), l’emploi d’environnements à précision arbitraire ou de formules d’approximations asymptotiques devient nécessaire.

11. Approximations statistiques et critères de substitution sous Excel

Dans de multiples configurations d’analyse décisionnelle, la complexité calculatoire de la loi hypergéométrique incite à lui substituer des distributions asymptotiques plus maniables. Excel permet de confronter ces approximations et de valider leur précision.

11.1 L’approximation par la loi binomiale (LOI.BINOMIALE.N)

L’approximation binomiale repose sur l’atténuation des effets de dépendance lorsque la taille de la population $N$ est très vaste par rapport à l’échantillon $n$. La règle empirique universellement acceptée pose que l’approximation est valide dès lors que la fraction de sondage est inférieure à 5 % :

$$\frac{n}{N} le 0{,}05 \quad (\text{ou de façon tolérante } \frac{n}{N} le 0{,}10)$$

Dans ce contexte, la variable hypergéométrique $X \sim \mathcal{H}(N, K, n)$ converge en loi vers la variable binomiale $Y \sim \mathcal{B}(n, p)$ où $p = K / N$. Sous Excel, la fonction de substitution s’écrit :

=LOI.BINOMIALE.N(k ; n ; K / N ; cumulative)

Étude comparative chiffrée sous Excel : Considérons une population industrielle de taille $N = 1000$ contenant $K = 200$ pièces défectueuses ($p = 0{,}20$). On extrait un échantillon de $n = 20$ unités (fraction de sondage $n/N = 0{,}02 le 0{,}05$). Évaluons la probabilité d’observer exactement $k = 4$ défectueux :

• Modèle hypergéométrique exact : =LOI.HYPERGEOMETRIQUE.N(4 ; 20 ; 200 ; 1000 ; FAUX) $to 0{,}219661$
• Modèle binomial approché : =LOI.BINOMIALE.N(4 ; 20 ; 0,20 ; FAUX) $to 0{,}218199$

L’écart absolu n’est que de $0{,}00146$ (soit une distorsion inférieure à $0{,}15 %$). L’approximation binomiale s’avère donc tout à fait pertinente dans ce cadre, offrant une formulation conceptuelle plus intuitive.

11.2 L’approximation par la loi normale (LOI.NORMALE.N)

En vertu du théorème central limite, lorsque la taille de l’échantillon $n$ grandit et que les paramètres ne sont pas asymétriques, la loi hypergéométrique tend vers une distribution normale continue. Les critères d’admissibilité usuels requièrent simultanément :

• $n ge 30$
• $n \cdot \frac{K}{N} ge 5 \quad \text{et} \quad n \cdot \left(1 – \frac{K}{N}\right) ge 5$

La distribution normale équivalente possède pour paramètres la moyenne $\mu$ et l’écart-type $\sigma$ rectifié par le facteur de correction pour population finie :

$$\mu = n \cdot p = n \cdot \frac{K}{N}$$

$$\sigma = \sqrt{n \cdot p \cdot (1 – p) \cdot \frac{N – n}{N – 1}}$$

Puisque nous effectuons une transition d’une distribution discrète (sur des bâtons isolés) vers une loi continue (sous une courbe de Gauss fluide), il est indispensable d’appliquer la correction de continuité de Yates ($\pm 0{,}5$) lors de l’implémentation sous Excel :

1. Probabilité cumulée $P(X le k)$ approchée : On évalue la densité continue jusqu’à l’extrémité supérieure de l’intervalle élémentaire, soit $k + 0{,}5$ :

=LOI.NORMALE.N(k + 0,5 ; mu ; sigma ; VRAI)

2. Probabilité ponctuelle $P(X = k)$ approchée : On évalue l’aire comprise sous la cloche normale entre $k – 0{,}5$ et $k + 0{,}5$ :

=LOI.NORMALE.N(k + 0,5 ; mu ; sigma ; VRAI) - LOI.NORMALE.N(k - 0,5 ; mu ; sigma ; VRAI)

Sans cette correction de continuité, l’approximation normale sous-estime systématiquement les probabilités de crête et déphase les queues de distribution.

11.3 Tableau d’aide à la décision pour le choix de la loi appropriée

Afin de guider l’analyste dans le choix de l’algorithme adapté à son jeu de données, la matrice décisionnelle ci-dessous résume les préconisations méthodologiques en vigueur :

Taille Population ($N$)	Fraction de Sondage ($n/N$)	Conditions Paramétriques	Loi Recommandée dans Excel	Formule Préconisée
Toute taille ($N le 10^5$)	$n/N > 0{,}05$	Toute valeur de $k, n, K$	Loi Hypergéométrique exacte	LOI.HYPERGEOMETRIQUE.N
Grande ($N > 10^3$)	$n/N le 0{,}05$	$n < 30$ ou $n \cdot p < 5$	Approximation Binomiale	LOI.BINOMIALE.N
Grande ($N > 10^3$)	$n/N le 0{,}05$	$n ge 30$ et $n \cdot p ge 5$	Approximation Normale	LOI.NORMALE.N (avec correction)
Moyenne ($N le 10^4$)	$n/N > 0{,}05$	$n ge 30$ et $n \cdot p (1-p) ge 5$	Normale + Facteur FCPF	LOI.NORMALE.N (avec $\sigma_{\text{FCPF}}$)

Sur le plan des revues scientifiques avec comité de lecture, il est recommandé de privilégier la modélisation hypergéométrique exacte dès lors que les données le permettent : l’argument de l’économie computationnelle n’étant plus recevable sur les ordinateurs modernes, la précision pure demeure la règle d’or académique.

12. Automatisation avancée et modélisation dynamique dans Excel

L’exploitation industrielle ou scientifique de la distribution hypergéométrique gagne à s’affranchir de la saisie manuelle de formules par l’intégration d’algorithmes VBA personnalisés, de moteurs de simulation empirique de Monte-Carlo et d’interfaces de pilotage visuel interactives.

12.1 Création d’une fonction personnalisée en VBA pour le test exact complet

L’environnement Visual Basic for Applications (VBA) intégré à Excel permet d’étendre les capacités logicielles en créant une fonction utilisateur (UDF – User Defined Function) calculant directement la p-valeur bilatérale exacte de Fisher à partir des quatre cellules d’une matrice $2 \times 2$.

Pour insérer cette fonction, ouvrez l’éditeur VBA (raccourci Alt + F11), insérez un nouveau module standard (Insertion > Module) et transcrivez le code suivant :

Function FISHER_EXACT_BILATERALE(cellule_a As Long, cellule_b As Long, _
                                 cellule_c As Long, cellule_d As Long) As Double
    Dim N As Long, K As Long, n_ech As Long, a As Long
    Dim p_obs As Double, p_cur As Double, p_somme As Double
    Dim a_min As Long, a_max As Long
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    ' Définition des marges
    N = cellule_a + cellule_b + cellule_c + cellule_d
    K = cellule_a + cellule_c
    n_ech = cellule_a + cellule_b
    a = cellule_a

    ' Bornes de support de la cellule 'a'
    If (n_ech - (N - K)) > 0 Then
        a_min = n_ech - (N - K)
    Else
        a_min = 0
    End If

    If n_ech < K Then
        a_max = n_ech
    Else
        a_max = K
    End If

    ' Probabilité de la table observée (seuil de tolérance numérique 1E-12)
    p_obs = wf.HypGeom_Dist(a, n_ech, K, N, False)
    p_somme = 0#

    ' Sommation de toutes les configurations de probabilité <= à la table observée
    Dim i As Long
    For i = a_min To a_max
        p_cur = wf.HypGeom_Dist(i, n_ech, K, N, False)
        If p_cur <= (p_obs + 0.000000000001) Then
            p_somme = p_somme + p_cur
        End If
    Next i

    If p_somme > 1# Then p_somme = 1#
    FISHER_EXACT_BILATERALE = p_somme
End Function

Une fois compilée, cette fonction s’utilise immédiatement au sein de la feuille de calcul comme n’importe quelle fonction native d’Excel en saisissant simplement l’instruction :

=FISHER_EXACT_BILATERALE(A1 ; B1 ; A2 ; B2)

Ce script supprime le besoin de construire des tables de calcul intermédiaires pour chaque matrice étudiée, autorisant le traitement automatisé de centaines de tableaux de contingence en quelques millisecondes.

12.2 Simulation de Monte-Carlo d’un échantillonnage sans remise

La simulation de Monte-Carlo offre un moyen empirique d’appréhender le processus de convergence de la fréquence observée vers la probabilité hypergéométrique théorique. Dans les versions modernes d’Excel, nous pouvons modéliser l’extraction sans remise sans recourir au VBA, grâce à l’articulation matricielle des fonctions TRIERPAR et ALEA.ENTRE.BORNES.

Supposons une urne contenant $N = 20$ éléments codés par des zéros et des uns, où $K = 6$ éléments valent $1$ (succès) et $14$ éléments valent $0$ (échecs). On place cette population dans la plage A1:A20.

Pour simuler l’extraction d’un échantillon de $n = 5$ unités sans remise, déployez dans la cellule C1 la formule suivante :

=INDEX(TRIERPAR(A1:A20 ; ALEA.ENTRE.BORNES(1 ; 1000000 ; LIGNE(A1:A20))) ; SEQUENCE(5))

Cette structure assigne à chaque individu un coefficient stochastique temporaire, réordonne aléatoirement la population par TRIERPAR (simulant un mélange physique de l’urne), puis extrait les 5 premières observations. La sommation du résultat par =SOMME(C1#) renvoie le nombre de succès $k$ obtenu lors de ce tirage spécifique.

En répétant cette expérience 10 000 fois au moyen d’une table de données d’hypothèse (onglet Données > Analyse de scénarios > Table de données), la fréquence empirique des tirages où $k = 2$ converge précisément vers la valeur calculée par =LOI.HYPERGEOMETRIQUE.N(2 ; 5 ; 6 ; 20 ; FAUX), soit environ $0{,}3973$ ($39{,}73 %$). Cette démarche apporte une confirmation empirique robuste à la déduction combinatoire analytique.

12.3 Élaboration d’un tableau de bord analytique interactif pour chercheurs

L’aboutissement ergonomique de la modélisation sous Excel réside dans la conception d’un simulateur interactif piloté par des contrôles de formulaire (barres de défilement ou compteurs) :

1. Activez l’onglet Développeur dans les options d’Excel (Fichier > Options > Personnaliser le ruban > Développeur).
2. Insérez des contrôles Toupie ou Barre de défilement (section Contrôles de formulaire) associés respectivement aux cellules cibles de $N$, $K$ et $n$.
3. Configurez les propriétés des contrôles en fixant des valeurs minimales et maximales cohérentes (ex. $N in [10 ; 500]$, $K in [0 ; N]$, $n in [1 ; N]$).
4. Liez ces cellules sources au bloc de génération dynamique par SEQUENCE développé à la section 8.1.
5. Le graphique combiné de distribution conçu à la section 8.3 réagit alors instantanément en temps réel à chaque manipulation des curseurs par l’analyste.

Cette maquette dynamique permet d’observer immédiatement la déformation de la cloche hypergéométrique : l’utilisateur visualise en direct le resserrement de la distribution lorsque $n$ se rapproche de $N$, l’annulation de la variance lorsque $n = N$ (l’échantillon devenant la population, $k$ vaut obligatoirement $K$ avec une certitude absolue $P=1$), ainsi que la bascule de l’asymétrie selon que la proportion de succès $K/N$ s’écarte de $0{,}5$. Cet outil constitue un levier d’exploration empirique précieux pour la formation continue, l’enseignement supérieur et le dimensionnement optimal d’échantillons en recherche expérimentale.

Références

• Fisher, R. A. (1922). On the mathematical foundations of theoretical statistics. Philosophical Transactions of the Royal Society of London. Series A, Containing Papers of a Mathematical or Physical Character, 222(594-604), 309-368. https://doi.org/10.1098/rsta.1922.0009
• Fisher, R. A. (1935). The design of experiments. Oliver and Boyd.
• Feller, W. (1968). An introduction to probability theory and its applications (Vol. 1, 3rd ed.). John Wiley & Sons.
• Microsoft Support. (s. d.). Fonction LOI.HYPERGEOMETRIQUE.N. Support Microsoft. Consulté le 15 mai 2024, à l’adresse https://support.microsoft.com/fr-fr/office/loi-hypergeometrique-n-fonction-loi-hypergeometrique-n-280d6eb6-ccd9-4134-975f-5785762a023b
• National Institute of Standards and Technology. (2012). NIST/SEMATECH e-Handbook of Statistical Methods. U.S. Department of Commerce. https://doi.org/10.18434/M32189
• Siegel, S., & Castellan, N. J. (1988). Nonparametric statistics for the behavioral sciences (2nd ed.). McGraw-Hill.
• Wackerly, D. D., Mendenhall, W., & Scheaffer, R. L. (2008). Mathematical statistics with applications (7th ed.). Cengage Learning.