Üldine valem
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Kokkuvõte
Andmete hulga tolerantsist väljas olevate väärtuste lugemiseks võite kasutada valemit, mis põhineb funktsioonidel SUMPRODUCT ja ABS. Näidatud näites on valem F6-s järgmine:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
kus "andmed" on nimega vahemik B5: B14, "sihtmärk" on nimega vahemik F4 ja "tolerants" on nimega vahemik F5.
Selgitus
See valem loeb, kui palju väärtusi ei ole fikseeritud tolerantsi vahemikus. Iga väärtuse variatsioon arvutatakse järgmiselt:
ABS(data-target)
Kuna nimega vahemik "data" sisaldab 10 väärtust, loob sihtväärtuse lahutamine F4-s massiivi 10 tulemusega:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Funktsioon ABS muudab kõik negatiivsed väärtused positiivseteks:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Seda massiivi võrreldakse F5 fikseeritud tolerantsiga:
ABS(data-target)>tolerance
Tulemuseks on massiivi või TRUE FALSE väärtused ja topeltnegatiiv muudab need ühedeks ja nullideks. SUMPRODUCTi sees näeb viimane massiiv välja selline:
(0;0;1;0;1;0;0;1;0;1)
kus nullid tähistavad tolerantsi piires olevaid väärtusi ja 1s tolerantsist väljas olevaid väärtusi. Seejärel summeerib SUMPRODUCT massiivi üksused ja tagastab lõpptulemuse 4.
Kõik väärtused tolerantsi piires
Kui tagastatakse "Jah", kui kõik andmevahemiku väärtused on antud tolerantsi piires, ja "Ei", kui ei, saate valemi kohandada järgmiselt.
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Kui SUMPRODUCT tagastab mis tahes arvu, mis on suurem kui null, hindab IF loogilise testi tõeks. Nulltulemust hinnatakse VALE.
Esitage väärtused tolerantsuse tõttu
Väärtusi saate tolerantsist välja tuua tingimusliku vormindamise reegliga, mis põhineb järgmisel valemil:
=ABS(B5-target)>tolerance
Sellel lehel on loetletud rohkem näiteid valemitega tingimusliku vormindamise kohta.
