Generisk formel
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Sammanfattning
För att räkna värden som är utom tolerans i en uppsättning data kan du använda en formel baserad på funktionerna SUMPRODUCT och ABS. I exemplet som visas är formeln i F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
där "data" är det namngivna området B5: B14, "target" är det namngivna området F4 och "tolerans" är det namngivna området F5.
Förklaring
Denna formel räknar hur många värden som inte ligger inom en fast tolerans. Variationen för varje värde beräknas med detta:
ABS(data-target)
Eftersom det angivna intervallet "data" innehåller 10 värden kommer subtrahering av målvärdet i F4 att skapa en matris med 10 resultat:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
ABS-funktionen ändrar alla negativa värden till positiva:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Denna array jämförs med den fasta toleransen i F5:
ABS(data-target)>tolerance
Resultatet är en matris eller SANTA FALSKA värden, och det dubbla negativa ändrar dessa till enor och nollor. Inuti SUMPRODUCT ser den slutliga matrisen ut så här:
(0;0;1;0;1;0;0;1;0;1)
där nollor representerar värden inom tolerans och 1s representerar värden utanför tolerans. SUMPRODUCT summerar sedan objekten i matrisen och returnerar ett slutresultat, 4.
Alla värden inom tolerans
För att returnera "Ja" om alla värden i ett dataintervall ligger inom en given tolerans och "Nej" om inte, kan du anpassa formeln så här:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Om SUMPRODUCT returnerar ett tal som är större än noll, utvärderar IF det logiska testet som SANT. Ett nollresultat utvärderas som FALSE.
Markera värden utifrån tolerans
Du kan markera värden utifrån tolerans med en villkorlig formateringsregel baserad på en sådan formel:
=ABS(B5-target)>tolerance
Denna sida listar fler exempel på villkorlig formatering med formler.
