Bendroji formulė
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Santrauka
Norėdami suskaičiuoti reikšmes, kurių duomenų rinkinyje nėra tolerancijos, galite naudoti formulę, pagrįstą funkcijomis SUMPRODUCT ir ABS. Parodytame pavyzdyje formulė F6 yra:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
kur „duomenys“ yra įvardytas diapazonas B5: B14, „tikslas“ yra pavadintas diapazonas F4, o „tolerancija“ - pavadintas diapazonas F5.
Paaiškinimas
Ši formulė skaičiuoja, kiek verčių nėra fiksuoto nuokrypio diapazone. Kiekvienos vertės kitimas apskaičiuojamas taip:
ABS(data-target)
Kadangi pavadintame diapazone „duomenys“ yra 10 reikšmių, atėmus tikslinę vertę F4, bus sukurtas masyvas su 10 rezultatų:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
ABS funkcija pakeičia visas neigiamas vertes į teigiamas:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Šis masyvas lyginamas su fiksuota F5 tolerancija:
ABS(data-target)>tolerance
Rezultatas yra masyvo arba TRUE FALSE reikšmės, o dvigubas neigiamasis pakeičia juos į vienetus ir nulius. SUMPRODUCT viduje galutinis masyvas atrodo taip:
(0;0;1;0;1;0;0;1;0;1)
kur nuliai rodo tolerancijos ribas, o 1s - už tolerancijos. Tada SUMPRODUCT sumuoja masyvo elementus ir pateikia galutinį rezultatą 4.
Visos vertės neviršija tolerancijos
Jei norite grąžinti „Taip“, jei visos duomenų diapazono reikšmės neviršija nustatyto leistino nuokrypio, ir „Ne“, jei ne, galite pritaikyti formulę taip:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Jei SUMPRODUCT pateikia bet kokį skaičių, didesnį už nulį, IF loginį testą įvertins kaip TIESĄ. Nulis rezultatas bus vertinamas kaip NETIESA.
Pažymėkite vertes iš tolerancijos
Galite paryškinti netolerancijos reikšmes naudodami sąlyginio formatavimo taisyklę, pagrįstą tokia formule:
=ABS(B5-target)>tolerance
Šiame puslapyje pateikiama daugiau sąlyginio formatavimo formulių pavyzdžių.
