Ile grup jest powyżej progu — porada #304

Dzisiaj chce porozmawiać o problemie zliczania grup powyżej progu. Żeby lepiej zobrazować problem pokaże dane sformatowane warunkowo (komórki wypełnione na żółto) i ich prezentację na wykresie z linią progu.

Porada 304 - Ile grup powyżej progu - 01

Najlepiej można zobaczyć grupy (góry), które wyrastają ponad próg na wykresie. Łatwo jest je też policzyć – jest ich 4. Ale nie chcemy tego liczyć ręcznie za każdym razem, gdy dane się zmienią, albo gdy mamy dużo więcej danych niż w przykładzie. Co chcemy zrobić oczywiście wykorzystać formułę Excela, ale najpierw wyjaśnijmy jak sobie możemy wykryć grupę (górę).

Są 2 możliwości, albo patrzymy na zbocze wznoszące, albo opadające, czyli albo sprawdzamy, że wartość jest mniejsza od progu, a kolejna większa lub równa progowi (znak równości może być przy pierwszym lub drugim punkcie, który sprawdzamy w zależności, czy próg uznajemy już za naszą ‘górkę’ czy jeszcze nie), albo pierwsza wartość większa lub równa progowi, a kolejna mniejsza od progu. W tym przykładzie będziemy szukać zboczy wznoszących.

Wiemy co chcemy znaleźć, ale jak to znaleźć w Excelu za pomocą formuły? Mamy operacje porównania jako warunki i chcemy je zliczać, więc świetnie do tego nadaje się funkcja LICZ.WARUNKI. Wystarczy, że sprawdzimy, czy nasze dane, od pierwszej do przedostatniej wartości, są mniejsze od progu, a następnie sprawdzimy, czy dane od drugiej do ostatniej wartości są większe bądź równe progowi:

=LICZ.WARUNKI(A2:A14;""&E1;A3:A15;">="&E1)

Porada 304 - Ile grup powyżej progu - 02

Czyli sprawdzamy obszary, które w stosunku do siebie są przesunięte o 1 komórkę.

Niestety wynik jaki otrzymujemy jest błędny – brakuje nam jednej ‘górki’. Taka sytuacja występuje wtedy kiedy nasze dane zaczynają się już od wartości większej (lub równej) progowi. Na szczęście bardzo łatwo skorygować naszą formułę. Wystarczy, że dodamy warunek sprawdzający, czy pierwsza dane jest większa równa od wyznaczonego progu. Musimy pamiętać o kolejności działań i dodatkowe porównanie zapisać w nawiasach (dodatkowe obliczenie poza funkcją LICZ.WARUNKI):

=LICZ.WARUNKI($A$2:$A$14;""&$E$1;$A$3:$A$15;">="&E1)+(A2>=$E$1)

Porada 304 - Ile grup powyżej progu - 03

Operacja porównania (A2>=$E$1) daje w wyniku wartość PRAWDA i FAŁSZ, które w wyniku dodawania są konwertowane odpowiednio na 1 i 0, czyli jeśli pierwsza wartość w danych przekracza wartość progu, to mamy jedną górkę ekstra i dodajemy ją do wyniku funkcji LICZ.WARUNKI, ale jeśli nie przekracza progu to nic nie dodajemy — nie ma górki na początku danych.

Odpowiednio wizualizując sobie problem udało nam się znaleźć proste rozwiązanie w Excelu 🙂

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP