Všeobecný vzorec
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Zhrnutie
Ak chcete v množine údajov spočítať hodnoty, ktoré sú mimo tolerancie, môžete použiť vzorec založený na funkciách SUMPRODUCT a ABS. V zobrazenom príklade je vzorec v F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
kde „údaj“ je pomenovaný rozsah B5: B14, „cieľ“ je pomenovaný rozsah F4 a „tolerancia“ je pomenovaný rozsah F5.
Vysvetlenie
Tento vzorec počíta, koľko hodnôt nie je v rozmedzí pevnej tolerancie. Variácia každej hodnoty sa počíta takto:
ABS(data-target)
Pretože pomenovaný rozsah „data“ obsahuje 10 hodnôt, odpočítaním cieľovej hodnoty v F4 sa vytvorí pole s 10 výsledkami:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Funkcia ABS zmení všetky záporné hodnoty na pozitívne:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Toto pole je porovnané s pevnou toleranciou v F5:
ABS(data-target)>tolerance
Výsledkom je pole alebo TRUE FALSE hodnoty a dvojitý negatív ich zmení na jednotky a nuly. Konečné pole vo vnútri SUMPRODUCT vyzerá takto:
(0;0;1;0;1;0;0;1;0;1)
kde nuly predstavujú hodnoty v rámci tolerancie a 1 s predstavujú hodnoty mimo tolerancie. SUMPRODUCT potom sčíta položky v poli a vráti konečný výsledok, 4.
Všetky hodnoty sú v tolerancii
Ak chcete vrátiť hodnotu „Áno“, ak sú všetky hodnoty v rozsahu údajov v rámci danej tolerancie, a hodnotu „Nie“, ak nie, môžete upraviť nasledovne:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Ak SUMPRODUCT vráti akékoľvek číslo väčšie ako nula, IF vyhodnotí logický test ako TRUE. Nulový výsledok sa vyhodnotí ako FALSE.
Hodnoty zvýraznite mimo tolerancie
Hodnoty mimo tolerancie môžete zvýrazniť pomocou pravidla podmieneného formátovania založeného na tomto vzorci:
=ABS(B5-target)>tolerance
Táto stránka obsahuje ďalšie príklady podmieneného formátovania pomocou vzorcov.
