Przekształć wieloetapową sumę wartości logicznych w jedną formułę

Mam kilka odczytów czujnika i sprawdzam, czy wartości mieszczą się w dopuszczalnym zakresie.

Aby to zrobić, używam IFdo sprawdzenia, czy wartości są >lub <obliczona (średnia) wartość. Wyniki są przechowywane w odpowiednich kolumnach. Na koniec sumuję wyniki, aby uzyskać liczbę, która jest poza zakresem (tj. Powyżej średniej).

Na przykład Axjest porównywany z Mean. Axdostać albo 1czy 0w If value is outside accepted bounds. Ax:

Następnie suma If value is outside accepted bounds. Axjest wykonywane, aby uzyskać Number of values outside bound. Ax:

Pytanie
Jak przekonwertować to na jedną formułę?

Poszukiwana funkcja to COUNTIF():

Wprowadź następującą formułę G3i ctrl-enter / copy-paste / fill-right w G3:I3:

=COUNTIF(A3:A8,">"&D3)

COUNTIF() sprawdza każdą wartość w pierwszym argumencie pod kątem kryteriów w drugiej, i zlicza liczbę jej spełnienia.

Korzystanie COUNTIF()jest najprostszym i najlepszym rozwiązaniem.

Oczywiście możesz użyć bardziej skomplikowanej / trudniejszej do zrozumienia formuły takiej jak

=SUMPRODUCT(--(A3:A8>D3))

lub tablica wprowadzona jako podobna

{=SUM(--(A3:A8>D3))}

lub nawet bardziej niepotrzebnie skomplikowaną wersję.

Nie można jednak skorzystać z żadnej z tych w tym konkretnym przypadku.

Jeśli faktem jest, że wydaje się, że jesteś zainteresowany zmniejszeniem liczby kolumn pomocniczych, jeszcze lepszym rozwiązaniem ogólnym byłoby zrezygnowanie z kolumn średnich pomocniczych:

Wprowadź następującą formułę D3i ctrl-enter / copy-paste / fill-right w D3:F3:

=COUNTIF(A3:A8,">"&AVERAGE(A3:A8))

(I tak, ta formuła może być trudniejsza do zrozumienia dla początkującego przez konwersję na =SUMPRODUCT(--(A3:A8>AVERAGE(A3:A8)))lub {=SUM(--(A3:A8>AVERAGE(A3:A8)))}.)

Funkcja SUMPRODUCT może również rozwiązać Twój problem.

Napisz ten wzór w G102 i wypełnij go od G102 do I102:

=SUMPRODUCT(--(A102:A107>D102:D107))

Uwaga: Dostosuj adres komórki w formule zgodnie z potrzebami.