Generisk formel
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Sammendrag
For å telle verdier som er utenfor toleranse i et datasett, kan du bruke en formel basert på funksjonene SUMPRODUCT og ABS. I eksemplet vist er formelen i F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
der "data" er det navngitte området B5: B14, "mål" er det navngitte området F4, og "toleranse" er det navngitte området F5.
Forklaring
Denne formelen teller hvor mange verdier som ikke er innenfor en fast toleranse. Variasjonen av hver verdi beregnes med dette:
ABS(data-target)
Fordi det nevnte området "data" inneholder 10 verdier, vil subtraksjon av målverdien i F4 skape en matrise med 10 resultater:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
ABS-funksjonen endrer eventuelle negative verdier til positive:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Denne matrisen sammenlignes med den faste toleransen i F5:
ABS(data-target)>tolerance
Resultatet er en matrise eller SANNE FALSE-verdier, og det dobbelte negative endrer disse til en og null. Inne i SUMPRODUCT ser den endelige matrisen slik ut:
(0;0;1;0;1;0;0;1;0;1)
der nuller representerer verdier innenfor toleranse, og 1s representerer verdier utenfor toleranse. SUMPRODUCT summerer deretter elementene i matrisen, og returnerer det endelige resultatet, 4.
Alle verdier innenfor toleranse
For å returnere "Ja" hvis alle verdier i et dataområde er innenfor en gitt toleranse, og "Nei" hvis ikke, kan du tilpasse formelen slik:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Hvis SUMPRODUCT returnerer et tall som er større enn null, vil IF evaluere den logiske testen som SANT. Et nullresultat vil bli vurdert som FALSE.
Fremhev verdier ut av toleranse
Du kan markere verdier ut av toleranse med en betinget formateringsregel basert på en formel som denne:
=ABS(B5-target)>tolerance
Denne siden viser flere eksempler på betinget formatering med formler.
