Yleinen kaava
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Yhteenveto
Jos haluat laskea toleranssin ulkopuoliset arvot tietojoukossa, voit käyttää SUMPRODUCT- ja ABS-funktioihin perustuvaa kaavaa. Esitetyssä esimerkissä kaava kohdassa F6 on:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
missä "data" on nimetty alue B5: B14, "kohde" on nimetty alue F4 ja "toleranssi" on nimetty alue F5.
Selitys
Tämä kaava laskee, kuinka monta arvoa ei ole kiinteän toleranssin alueella. Kunkin arvon vaihtelu lasketaan tällä tavalla:
ABS(data-target)
Koska nimetty alue "data" sisältää 10 arvoa, vähentämällä kohdearvo F4: ssä luodaan taulukko, jossa on 10 tulosta:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
ABS-toiminto muuttaa negatiiviset arvot positiivisiksi:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Tätä taulukkoa verrataan kiinteään toleranssiin F5: ssä:
ABS(data-target)>tolerance
Tuloksena on taulukko tai TOSI EPÄTOSI-arvot, ja kaksoisnegatiivi muuttaa nämä yksiköiksi ja nolliksi. SUMPRODUCTin sisällä viimeinen taulukko näyttää tältä:
(0;0;1;0;1;0;0;1;0;1)
missä nollat edustavat toleranssiarvoja ja 1s edustavat toleranssiarvoja. SUMPRODUCT summaa sitten taulukon kohteet ja palauttaa lopputuloksen 4.
Kaikki arvot toleranssin sisällä
Jos haluat palauttaa "Kyllä", jos kaikki arvoalueet ovat tietyn toleranssin sisällä, ja "Ei", jos ei, voit mukauttaa kaavaa seuraavasti:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Jos SUMPRODUCT palauttaa minkä tahansa numeron, joka on suurempi kuin nolla, IF arvioi loogisen testin TOSI. Nolla tulos arvioidaan EPÄTOSI.
Korosta arvot suvaitsevaisuuden ulkopuolella
Voit korostaa arvoja toleranssista ehdollisen muotoilusäännön avulla, joka perustuu tällaiseen kaavaan:
=ABS(B5-target)>tolerance
Tällä sivulla on lisää esimerkkejä ehdollisesta muotoilusta kaavoilla.
