Formula generică
=SUMPRODUCT(--(ABS(data-target)>tolerance))
rezumat
Pentru a număra valori care sunt în afara toleranței într-un set de date, puteți utiliza o formulă bazată pe funcțiile SUMPRODUCT și ABS. În exemplul prezentat, formula din F6 este:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
unde „date” este intervalul numit B5: B14, „țintă” este intervalul numit F4, iar „toleranță” este intervalul numit F5.
Explicaţie
Această formulă contează câte valori nu se află în intervalul unei toleranțe fixe. Variația fiecărei valori se calculează cu aceasta:
ABS(data-target)
Deoarece intervalul numit „date” conține 10 valori, scăderea valorii țintă în F4 va crea o matrice cu 10 rezultate:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Funcția ABS schimbă orice valori negative în pozitive:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Această matrice este comparată cu toleranța fixă din F5:
ABS(data-target)>tolerance
Rezultatul este o matrice sau valori FALSE ADEVĂRATE, iar dublul negativ le schimbă în unii și zerouri. În interiorul SUMPRODUCT, matricea finală arată astfel:
(0;0;1;0;1;0;0;1;0;1)
unde zerourile reprezintă valori în cadrul toleranței, iar 1 reprezintă valori în afara toleranței. SUMPRODUCT însumează apoi articolele din matrice și returnează un rezultat final, 4.
Toate valorile în cadrul toleranței
Pentru a returna „Da” dacă toate valorile dintr-un interval de date se încadrează într-o anumită toleranță și „Nu” dacă nu, puteți adapta formula astfel:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Dacă SUMPRODUCT returnează orice număr mai mare decât zero, IF va evalua testul logic ca fiind ADEVĂRAT. Un rezultat zero va fi evaluat ca FALS.
Evidențiați valorile din toleranță
Puteți evidenția valori din toleranță cu o regulă de formatare condițională bazată pe o formulă de genul acesta:
=ABS(B5-target)>tolerance
Această pagină listează mai multe exemple de formatare condiționată cu formule.
