Formuła ogólna
=COUNTIF(range,""&high)
Podsumowanie
Aby policzyć wartości komórek, które nie należą do dwóch liczb, możesz użyć funkcji LICZ.JEŻELI. W przedstawionym przykładzie formuła w komórce K5 skopiowana w dół to:
=COUNTIF(C5:G5,""&J5)
W każdym nowym wierszu ta formuła zwraca liczbę wartości spoza najniższej i najwyższej wartości w kolumnach I i J.
Wyjaśnienie
Celem tego przykładu jest policzenie wartości liczbowych zarejestrowanych w ciągu 5 dni, które nie mieszczą się między dwiema liczbami, wartością niską i wysoką. Innymi słowy, aby policzyć wartości, które są „poza zakresem”. Zauważ, że każdy wiersz oznaczony AG ma swój własny dolny i górny limit w kolumnach I i J.
Na początku możesz pomyśleć o użyciu funkcji LICZ.WARUNKI z dwoma kryteriami. Jednak ponieważ funkcja COUNTIFS łączy kryteria z logiką AND, nie można jej używać z dwoma kryteriami w tym scenariuszu. Logika wartości mniejsza niż niższa ORAZ większa niż wartość wyższa zawsze zawodzi, a wynik będzie zawsze równy zero. Zamiast tego potrzebujemy logiki OR.
Jednym prostym rozwiązaniem jest dwukrotne użycie funkcji LICZ.JEŻELI w następujący sposób:
=COUNTIF(C5:G5,""&J5)
Pierwszy LICZ.JEŻELI zlicza wartości poniżej wartości w I5, a drugi LICZ.JEŻELI zlicza wartości powyżej wartości w J5. Po dodaniu tych dwóch wyników poprawnie obsługują wymaganą logikę: mniej niż I5 LUB większe niż J5. Zwróć uwagę, że operatory większe niż (">") i mniejsze niż ("<") są łączone z odwołaniami do komórek za pomocą operatora ampersand (&), co jest dziwactwem funkcji RACON.
Z SUMPRODUCT
Nieco bardziej eleganckim rozwiązaniem jest użycie funkcji SUMPRODUCT z dwoma logicznymi wyrażeniami, takimi jak to:
=SUMPRODUCT((C5:G5J5))
Zauważ, że nie musimy używać konkatenacji z odwołaniami do komórek, tak jak w przypadku LICZ.JEŻELI powyżej; standardowe wyrażenia działają dobrze.
To jest przykład użycia algebry boolowskiej z dodatkiem (+), który tworzy logikę OR. Kiedy te wyrażenia są obliczane, mamy dwie tablice wartości PRAWDA i FAŁSZ, jak poniżej:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,TRUE)+(FALSE,FALSE,TRUE,FALSE,FALSE))
Operacja matematyczna automatycznie przekształca wartości PRAWDA i FAŁSZ na 1 i 0. Wynik można zwizualizować w następujący sposób:
=SUMPRODUCT((0,0,0,0,1)+(0,0,1,0,0))
W rezultacie powstaje pojedyncza tablica zawierająca dwie jedynki:
=SUMPRODUCT((0,0,1,0,1))
Mając tylko jedną tablicę do przetworzenia, SUMPRODUCT sumuje elementy w tablicy i zwraca wynik końcowy równy 2.
