Formule générique
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Sommaire
Pour compter les valeurs qui sont hors tolérance dans un ensemble de données, vous pouvez utiliser une formule basée sur les fonctions SOMMEPROD et ABS. Dans l'exemple illustré, la formule en F6 est:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
où «données» est la plage nommée B5: B14, «cible» est la plage nommée F4 et «tolérance» est la plage nommée F5.
Explication
Cette formule compte le nombre de valeurs qui ne sont pas dans la plage d'une tolérance fixe. La variation de chaque valeur est calculée avec ceci:
ABS(data-target)
Étant donné que la plage nommée "data" contient 10 valeurs, la soustraction de la valeur cible dans F4 créera un tableau avec 10 résultats:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
La fonction ABS change toutes les valeurs négatives en positives:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Ce tableau est comparé à la tolérance fixe en F5:
ABS(data-target)>tolerance
Le résultat est un tableau ou des valeurs TRUE FALSE, et le double négatif les change en uns et en zéros. À l'intérieur de SUMPRODUCT, le tableau final ressemble à ceci:
(0;0;1;0;1;0;0;1;0;1)
où les zéros représentent les valeurs comprises dans la tolérance et les 1 représentent les valeurs hors tolérance. SUMPRODUCT additionne ensuite les éléments du tableau et renvoie un résultat final, 4.
Toutes les valeurs dans la tolérance
Pour renvoyer "Oui" si toutes les valeurs d'une plage de données sont comprises dans une tolérance donnée, et "Non" sinon, vous pouvez adapter la formule comme suit:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Si SUMPRODUCT renvoie un nombre supérieur à zéro, IF évaluera le test logique comme TRUE. Un résultat nul sera évalué comme FALSE.
Mettre en évidence les valeurs hors tolérance
Vous pouvez mettre en évidence les valeurs hors tolérance avec une règle de mise en forme conditionnelle basée sur une formule comme celle-ci:
=ABS(B5-target)>tolerance
Cette page répertorie d'autres exemples de mise en forme conditionnelle avec des formules.
