Czyli formuła w formatowaniu warunkowym

Jakiś czas temu opisywałam jak wyróżnić najmniejszą wartość w wierszu, mając dane wiele wierszy. Pod tym postem padło pytanie jak zrobić tak, aby wyróżnić najmniejszą wartość, ale nie będącą zerem. No i pojawił się problem, bo funkcja MIN, użyta przeze mnie w tamtym wpisie, oczywiście takie zero zaznaczy.

Formatka wygląda tak:

Formatka

Będziemy wyróżniać najniższy niezerowy stan magazynowy. Zauważ, że zera zaznaczyłam na czerwono, aby łatwo je było wyłapać.

Do dzieła!

Formuła

Trudności są dwie:

1. mamy zaznaczyć wartość per wiersz
2. dla wierszy, gdzie najmniejsze jest zero funkcja MIN poda właśnie to zero.

Aby rozwiązać problem pierwszy – musimy napisać formułę w formatowaniu warunkowym i zastosować odpowiednie adresowanie komórek.

Aby rozwiązać problem drugi – użyjemy po prostu innej funkcji: MIN.K.

Zajmijmy się najpierw namierzeniem najmniejszej niezerowej wartości. Jeśli w danym wierszu najmniejszą wartośćią nie jest zero – chcemy ją wyświetlić. Jeśli natomiast najmniejsze jest zero – chcemy wyświetlić drugą co do kolejności najmniejszą wartość. Do tego celu użyjemy właśnie MIN.K. Funkcja ta potrafi zwrócić k-tą w kolejności najmniejszą wartość. My chcemy albo pierwszą (k=1) albo drugą (k=2). Owe k to drugi argument tej funkcji.

Czyli jeśli interesuje nas pierwsza najmniejsza wartość (czyli po prostu: najmniejsza), tak napiszemy funkcję MIN.K: =MIN.K(C4:I4;1), a gdy drugą najmniejszą- tak: =MIN.K(C4:I4;2).

Mam nadzieję, że proste :).

Problem w tym, że raz chcemy pierwszą najmniejszą raz drugą. Zależy to oczywiście od wartości tej pierwszej, najmniejszej wartości. Będziemy więc ją sprawdzać i zrobimy to za pomocą funkcji JEŻELI:

=JEŻELI(MIN(C4:I4)=0;MIN.K(C4:I4;2);MIN(C4:I4))

lub ewentualnie tak (ja tak wolę – jest czytelniej):

=MIN.K(C4:I4;JEŻELI(MIN(C4:I4)=0;2;1))

Powyższymi formułami po prostu wyświetlimy najmniejszą niezerową wartość. Teraz trzeba to ubrać tak, jak potrzebuje od nas formatowanie warunkowe, czyli w logikę: w wyniku chcemy uzyskać PRAWDA lub FAŁSZ. Dodatkowo pamiętajmy, że ta formuła ma działać dla każdej komórki ze stanami magazynowymi. Czyli wartość każdego stanu ma zostać w ten sposób sprawdzona, a mamy do dyspozycji tylko jedną formułę. Zastosuję więc adresowanie mieszane tak, aby zablkoować kolumny zakresów (jednowierszowych), w których badam najmniejszą wartość):

Całość będzie wyglądała tak:

=C4=MIN.K($C4:$I4;JEŻELI(MIN($C4:$I4)=0;2;1))

Teraz wystarczy to tylko przekopiować do formatowania warunkowego i wybrać kolor :).

Formatowanie warunkowe

Zanim jednak przekopiujemy – zaznaczmy zakres, który ma być formatowany (C4:I9. Uwaga, tutaj bardzo ważne jest to, aby zacząć zaznaczać od pierwszej komórki zakresu formatowanego, czyli C4. Dla Excela będzie to oznaczało, że formuła, którą napisaliśmy ma być stosowana począwszy od tej właśnie komórki. To tak, jak piszemy formułę w zwykłej tabeli i kopiujemy ją w dół. Też zaczynamy pisać od pierwszej komórki.

Ok. Teraz można tworzyć regułę formatowania, czyli:

1. Narzędzia główne/ Formatowanie warunkowe/ Nowa reguła/ Użyj formuły do określenia komórek, które należy sformatować
2. W pole formuła wklej treść formuły opisanej powyżej:
3. Wybierz formatowanie
4. I zatwierdź wszystko OK:
   

   Tworzenie reguły formatowania warunkowego

Oto wynik (ostatnio mam fazę na taki seledynowy ;)):

Wynik

Mam nadzieję, że proste :).

Powiązane produkty

• WEBINAR: Formatowanie warunkowe – tutaj poznasz więcej fajnych zastosowań formatowania warunkowego
• WEBINAR: Adresowanie komórek – tutaj dokładnie tłumaczę o co chodzi z tymi dolarami $.

Tutaj możesz pobrać plik z rozwiązaniem:
MalinowyExcel Wyróżnianie najmniejszej niezerowej wartości dw.xlsx

A tutaj wersja wideo:

(pojawi się wkrótce)