Conclure pour un échantillon en utilisant une feuille de calcul
Présentation
Un échantillon prélevé pour analyses comprend des unités analysées séparément. Il faut, habituellement, cinq unités.
Pour chaque analyse, un certain nombre de critères sont appliqués, avec un plan à deux ou trois classes. Pour la compréhension de l'analyse, se reporter aux pages 106 et 107 de Microbiologie alimentaire.
Pour conclure à une analyse d'un échantillon, il faut déterminer, pour chaque critère, l'acceptation ou le refus du lot en fonction des procédures précisées dans les plans à deux ou trois classes. La conclusion finale tient compte de l'ensemble des analyses réalisées pour l'échantillon.
La solution adoptée ici, pour un usage et des raisons pédagogiques, est une feuille de tableur (excel). Il est probable que des imperfections ou des erreurs subsistent que vous pourrez signaler Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser..
Trois versions sont proposées :
- Version simplifiée avec l'application d'un seul critère. Pour télécharger la feuille : cliquer ici.
- Version complète avec l'application de 9 critères possibles. Pour télécharger la feuille : cliquer ici.
- Version Fabien CÉZARD. Pour télécharger la feuille : cliquer ici.
Technique
version simplifiée
Dans cette version est ajouté le nombre maximum d'analyses comprises entre m et M acceptées dans le cadre du plan à trois classes. Dans la version complète, cette valeur est fixée à 2 (sur 5).
Quelques indications sur les formules utilisées :
- en B13, la formule matricielle compte le nombre de résultats (d'unités) (=SOMME(SI(ESTNUM(C10:G10);1;0)))
- en B14, la formule matricielle calcule le nombre de résultats supérieurs à m (=SOMME(SI(ESTNUM(C10:G10);SI(C10:G10>minf;1;0);0)))
- en B15, idem pour M (=SOMME(SI(C10:G10>M;1;0)))
- en B16, la formule calcule le nombre d'unités comprises entre m et M sachant que dans le cas où m=M ce nombre est égal au nombre d'unités supérieures à m (=SI(minf<>M;B14-B15;B14))
- en B17, la formule matricielle compte les erreurs (=SOMME(SI((C4:C7)="a";1;0))+SOMME(SI((C13)="a";1;0))) signalées par des formules en C4:C7 et C13. La valeur trouvée doit être 0.
- en B19, on conclut (=SI(B17>0;"pas de conclusion possible erreur sur les données";SI(ET(B16<=acc;B15<1);"acceptation du lot pour ce paramètre";"refus du lot pour ce paramètre")))
version complète
Les précisions concernent les formules utilisées. Il est un bon exercice de tenter de refaire la feuille soi-même pour comprendre le fonctionnement du tableur. On pourra donc s'aider de la solution proposée, l'améliorer… ou même en trouver une meilleure qui pourra être publiée sur le site si vous le souhaitez (à votre nom bien sûr).
L'affichage utilise les formats conditionnels.
Quelques indications sur les formules utilisées :
- en B7, la formule vérifie que M est bien supérieur à m… (=SI(B6<B5;"Problème";"-"))
- en B14, la formule vérifie , s'il y a un critère m, que le nombre d'unités de l'échantillon est bien de 5 (=SI(ESTNUM(B5);SI(B18<>5;"nombre unité ≠ 5";"-");"--")).
- en B16, la formule calcule le nombre d'unités aux résultats inférieures à M (=SOMME(SI(B9:B13>B$6;1;0))). ATTENTION, cette formule est matricielle et doit être validée par Ctrl+Maj+Entrée).
- en B17, de la même façon, la formule calcule le nombre d'unités entre m et M (=SOMME(SI(B9:B13>B$5;1;0))-B16 en formule matricielle)
- en B18, la formule calcule le nombre d'unités (=NB(B9:B13)).
- en B20, la formule affiche refus (s'il y a un critère) ou accepté en fonction des règles et n<5 si le nombre d'unités est différent (=SI(ESTNUM(B5);SI(B18=5;SI(B16>0;"refus";SI(B17>2;"refus";"accepté"));"n < 5");"--")).
- en B22, la formule affiche, s'il y a un critère, refus du lot s'il y a un refus pour un critère et accepté sinon. Si les analyses pour un critère ne portent pas sur les 5 unités, elle ne tient pas compte de l'analyse. (=SI(ESTNUM(B5);SI(B18=5;SI(B16>0;"refus";SI(B17>2;"refus";"accepté"));"n < 5");"--"))
version Fabien CÉZARD
Fabien a réalisé une feuille plus performante que vous pourrez analyser. La présentation est différente… À vous de choisir ou d'en créer une autre.
Date de mise à jour : 10 février 2011