Általános képlet
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Összegzés
A tűréshatáron kívül eső értékek számításához az SUMPRODUCT és az ABS funkciókon alapuló képletet használhat. A bemutatott példában az F6 képlete a következő:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
ahol az "adatok" a B5: B14 elnevezett tartomány, a "cél" az F4 megnevezett tartomány és a "tolerancia" az F5 nevű tartomány.
Magyarázat
Ez a képlet megszámolja, hogy hány érték nincs a rögzített tűréshatár tartományában. Az egyes értékek variációját ezzel számolják:
ABS(data-target)
Mivel az elnevezett "data" tartomány 10 értéket tartalmaz, az F4-ben levő célérték levonása 10 eredményt tartalmazó tömböt hoz létre:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Az ABS funkció minden negatív értéket pozitívra változtat:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Ezt a tömböt összehasonlítjuk az F5 fix tűrésével:
ABS(data-target)>tolerance
Az eredmény egy tömb vagy IGAZ HAMIS érték, és a kettős negatív ezeket eggyel és nullára változtatja. A SUMPRODUCT belsejében a végső tömb így néz ki:
(0;0;1;0;1;0;0;1;0;1)
ahol a nullák a tűrésen belüli értékeket, az 1s pedig a tűrésen kívüli értékeket képviselik. Ezután a SUMPRODUCT összegzi a tömb elemeit, és visszaadja a végeredményt, 4.
Minden érték a tűréshatáron belül
Ha az "Igen" értéket vissza szeretné adni, ha az adattartományban szereplő összes érték egy adott tűréshatáron belül van, és a "Nem" értékre, ha nem, akkor a következő képletet alkalmazhatja:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Ha a SUMPRODUCT bármely nullánál nagyobb számot ad vissza, az IF a logikai tesztet IGAZ-nak értékeli. A nulla eredményt HAMISként értékeljük.
Jelölje ki az értékeket tűrésből
Az alábbi képlet alapján feltételes formázási szabálysal emelheti ki az értékeket tűrésből:
=ABS(B5-target)>tolerance
Ez az oldal további példákat sorol fel a képletekkel történő feltételes formázáshoz.
