Obecný vzorec
=SUMPRODUCT(--(ABS(data-target)>tolerance))
souhrn
Chcete-li spočítat hodnoty, které jsou mimo toleranci v sadě dat, můžete použít vzorec založený na funkcích SUMPRODUCT a ABS. V zobrazeném příkladu je vzorec v F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
kde „data“ je pojmenovaný rozsah B5: B14, „target“ je pojmenovaný rozsah F4 a „tolerance“ je pojmenovaný rozsah F5.
Vysvětlení
Tento vzorec počítá, kolik hodnot není v rozsahu pevné tolerance. Variace každé hodnoty se vypočítá takto:
ABS(data-target)
Protože pojmenovaný rozsah „data“ obsahuje 10 hodnot, odečtením cílové hodnoty ve F4 se vytvoří pole s 10 výsledky:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Funkce ABS změní všechny záporné hodnoty na kladné:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Toto pole je porovnáno s pevnou tolerancí v F5:
ABS(data-target)>tolerance
Výsledkem je pole nebo TRUE FALSE hodnoty a dvojitý zápor se změní na jedničky a nuly. Uvnitř SUMPRODUCT vypadá konečné pole takto:
(0;0;1;0;1;0;0;1;0;1)
kde nuly představují hodnoty v rámci tolerance a 1 s představují hodnoty mimo toleranci. SUMPRODUCT poté sečte položky v poli a vrátí konečný výsledek, 4.
Všechny hodnoty v rámci tolerance
Chcete-li vrátit „Ano“, pokud jsou všechny hodnoty v rozsahu dat v rámci dané tolerance, a „Ne“, pokud ne, můžete upravit vzorec takto:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Pokud SUMPRODUCT vrátí libovolné číslo větší než nula, IF vyhodnotí logický test jako TRUE. Nulový výsledek bude vyhodnocen jako FALSE.
Zvýrazněte hodnoty mimo toleranci
Hodnoty mimo toleranci můžete zvýraznit pomocí pravidla podmíněného formátování založeného na tomto vzorci:
=ABS(B5-target)>tolerance
Tato stránka uvádí další příklady podmíněného formátování pomocí vzorců.
