Generisk formel
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Resumé
For at tælle værdier, der er ude af tolerance i et datasæt, kan du bruge en formel baseret på funktionerne SUMPRODUCT og ABS. I det viste eksempel er formlen i F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
hvor "data" er det navngivne område B5: B14, "mål" er det navngivne område F4, og "tolerance" er det navngivne område F5.
Forklaring
Denne formel tæller, hvor mange værdier der ikke er inden for området med en fast tolerance. Variationen af hver værdi beregnes med dette:
ABS(data-target)
Da det navngivne interval "data" indeholder 10 værdier, oprettes en matrix med 10 resultater ved at trække målværdien i F4:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
ABS-funktionen ændrer eventuelle negative værdier til positive:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Denne matrix sammenlignes med den faste tolerance i F5:
ABS(data-target)>tolerance
Resultatet er en matrix eller SAND FALSKE værdier, og det dobbelte negative ændrer disse til ens og nuller. Inde i SUMPRODUCT ser det endelige array sådan ud:
(0;0;1;0;1;0;0;1;0;1)
hvor nuller repræsenterer værdier inden for tolerance, og 1s repræsenterer værdier uden for tolerance. SUMPRODUCT summerer derefter elementerne i arrayet og returnerer det endelige resultat 4.
Alle værdier inden for tolerance
For at returnere "Ja", hvis alle værdier i et dataområde er inden for en given tolerance, og "Nej", hvis ikke, kan du tilpasse formlen sådan:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Hvis SUMPRODUCT returnerer et tal større end nul, vil IF evaluere den logiske test som SAND. Et resultat på nul vurderes som FALSK.
Fremhæv værdier ud af tolerance
Du kan fremhæve værdier ud af tolerance med en betinget formateringsregel baseret på en formel som denne:
=ABS(B5-target)>tolerance
Denne side viser flere eksempler på betinget formatering med formler.
