Vispārēja formula
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Kopsavilkums
Lai uzskaitītu vērtības, kuras datu kopā ir ārpus pielaides, varat izmantot formulu, kuras pamatā ir funkcijas SUMPRODUCT un ABS. Parādītajā piemērā formula F6 ir:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
kur "dati" ir nosauktais diapazons B5: B14, "mērķis" ir nosauktais diapazons F4 un "pielaide" ir nosauktais diapazons F5.
Paskaidrojums
Šī formula saskaita, cik daudz vērtību nav fiksētas pielaides diapazonā. Katras vērtības variāciju aprēķina šādi:
ABS(data-target)
Tā kā nosauktajā diapazonā "dati" ir 10 vērtības, atņemot mērķa vērtību F4, tiks izveidots masīvs ar 10 rezultātiem:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
ABS funkcija negatīvās vērtības maina uz pozitīvām:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Šo masīvu salīdzina ar fiksēto pielaidi F5:
ABS(data-target)>tolerance
Rezultāts ir masīva vai TRUE FALSE vērtības, un dubultnegatīvs tos maina uz vienumiem un nullēm. SUMPRODUCT iekšpusē pēdējais masīvs izskatās šādi:
(0;0;1;0;1;0;0;1;0;1)
kur nulles apzīmē vērtības pielaides robežās un 1s norāda vērtības ārpus pielaides. Pēc tam SUMPRODUCT summē masīva vienumus un atgriež gala rezultātu 4.
Visas vērtības pielaides robežās
Lai atgrieztu "Jā", ja visas datu diapazona vērtības ir noteiktā pielaidē, un "Nē", ja nē, varat pielāgot šādu formulu:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Ja SUMPRODUCT atgriež jebkuru skaitli, kas ir lielāks par nulli, IF loģisko pārbaudi novērtēs kā PATIESU. Nulles rezultāts tiks vērtēts kā FALSE.
Izcelt vērtības no tolerances
Varat izcelt vērtības no pielaides ar nosacījuma formatēšanas kārtulu, kuras pamatā ir šāda formula:
=ABS(B5-target)>tolerance
Šajā lapā ir uzskaitīti vairāk nosacījuma formatēšanas ar formulām piemēri.
