Splošna formula
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Povzetek
Če želite v naboru podatkov šteti vrednosti, ki so nesprejemljive, lahko uporabite formulo, ki temelji na funkcijah SUMPRODUCT in ABS. V prikazanem primeru je formula v F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
kjer je "data" imenovano območje B5: B14, "target" je imenovano območje F4, "tolerance" pa imenovano območje F5.
Pojasnilo
Ta formula šteje, koliko vrednosti ni v območju fiksne tolerance. Sprememba posamezne vrednosti se izračuna s tem:
ABS(data-target)
Ker imenovani obseg "podatki" vsebuje 10 vrednosti, bo odštevanje ciljne vrednosti v F4 ustvarilo matriko z 10 rezultati:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Funkcija ABS spremeni vse negativne vrednosti na pozitivne:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Ta matrika se primerja s fiksno toleranco v F5:
ABS(data-target)>tolerance
Rezultat so vrednosti matrike ali TRUE FALSE, dvojni negativ pa jih spremeni v enake in ničle. Znotraj SUMPRODUCT je končni niz videti takole:
(0;0;1;0;1;0;0;1;0;1)
kjer ničle predstavljajo vrednosti znotraj tolerance, 1s pa vrednosti zunaj tolerance. Nato SUMPRODUCT sešteje elemente v matriki in vrne končni rezultat, 4.
Vse vrednosti znotraj tolerance
Če želite vrniti »Da«, če so vse vrednosti v območju podatkov znotraj določenega odstopanja, in »Ne«, če ne, lahko formulo prilagodite tako:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Če SUMPRODUCT vrne katero koli število, večje od nič, bo IF logični test ocenil kot TRUE. Ničelni rezultat bo ocenjen kot FALSE.
Poudarite vrednosti iz tolerance
Vrednosti iz tolerance lahko označite s pogojnim pravilom oblikovanja, ki temelji na taki formuli:
=ABS(B5-target)>tolerance
Na tej strani je naštetih več primerov pogojnega oblikovanja s formulami.
