Formuła ogólna
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Podsumowanie
Aby policzyć wartości poza zakresem tolerancji w zbiorze danych, można użyć formuły opartej na funkcjach SUMPRODUCT i ABS. W pokazanym przykładzie formuła w F6 to:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
gdzie „dane” to nazwany zakres B5: B14, „cel” to nazwany zakres F4, a „tolerancja” to nazwany zakres F5.
Wyjaśnienie
Ta formuła zlicza, ile wartości nie mieści się w zakresie ustalonej tolerancji. Odchylenie każdej wartości jest obliczane w ten sposób:
ABS(data-target)
Ponieważ nazwany zakres „dane” zawiera 10 wartości, odjęcie wartości docelowej w F4 spowoduje utworzenie tablicy z 10 wynikami:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Funkcja ABS zmienia wszelkie wartości ujemne na dodatnie:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Ta tablica jest porównywana ze stałą tolerancją w F5:
ABS(data-target)>tolerance
Wynikiem jest tablica lub wartości PRAWDA FAŁSZ, a podwójna wartość ujemna zmienia je na jedynki i zera. Wewnątrz SUMPRODUCT ostatnia tablica wygląda następująco:
(0;0;1;0;1;0;0;1;0;1)
gdzie zera reprezentują wartości w granicach tolerancji, a 1s reprezentują wartości poza tolerancją. SUMPRODUCT następnie sumuje elementy w tablicy i zwraca wynik końcowy, 4.
Wszystkie wartości w granicach tolerancji
Aby zwrócić „Tak”, jeśli wszystkie wartości w zakresie danych mieszczą się w określonej tolerancji, lub „Nie”, jeśli nie, możesz dostosować formułę w następujący sposób:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Jeśli SUMPRODUCT zwróci dowolną liczbę większą od zera, funkcja JEŻELI oceni test logiczny jako PRAWDA. Wynik zerowy zostanie oceniony jako FAŁSZ.
Wyróżnij wartości poza tolerancją
Możesz wyróżnić wartości poza tolerancją za pomocą warunkowej reguły formatowania opartej na formule takiej jak ta:
=ABS(B5-target)>tolerance
Ta strona zawiera więcej przykładów formatowania warunkowego za pomocą formuł.
