Ostatnio opisywałam użycie funkcji MIN i MAX jako alternatywę dla funkcji JEŻELI. Funkcje te działają szybciutko i pozwalają uniknąć powtarzania formuły w funkcji JEŻELI. Aczkolwiek, w porównaniu do niej, mają pewne ograniczenie, które w „normalnym” ich użyciu jest zbawienne, natomiast w tym, które opisałam ostatnio – może powodować nieoczekiwane wyniki. Dlatego właśnie o tym dziś napiszę.

Kiedy używamy funkcji MIN i MAX do sprawdzenia pewnych granic czy limitów, np. limit roczny kosztów uzyskania przychodów czy wyświetlanie zera zamiast ujemnego podatku, sytuacja jest prosta: dla podatku wybieramy zawsze większą wartość (zero lub podatek) – funkcja MAX, a dla kosztów – zawsze mniejszą (poniesiony koszt lub limit kosztów) – funkcja MIN. Schemat formuł wygląda tak:

=MAX(0; Podatek)

=MIN(LimitKUP; KUP)

To jak najbardziej działa, jednak ma pewne ograniczenie: w takiej formie nie zadziała poprawnie, gdy zmienne podatek lub KUP będą puste. Kiedy to może wystąpić? Załóżmy, że będziemy liczyli koszty uzyskania przychodu (pusty podatek raczej nie wystąpi, eh). Przyjrzyjmy się sytuacji, gdy przygotowujemy do tego uniwersalną formatkę.  Oto przykład:

Formatka

W żółtych komórkach w kolumnie E mamy limity KUP – z definicji zawsze uzupełnione. W kolumnie K uzupełniamy poniesione koszty – nie wszystkie musimy ponieść, ale miejsce jest przygotowane. I w ramce w białych komórkach kolumny K chcemy uzyskać koszty, które możemy sobie odliczyć od przychodu, z uwzględnieniem limitów oczywiście.

Na powyższej formatce mamy sytuację, że człowiek pracował tylko na umowę o pracę z normalnymi kosztami. Po naszej formule spodziewamy się, że formuła wyświetli nam koszty do odliczenia tylko w przypadku tej umowy, a dla pozostałych – zero. No i tutaj jest zonk. Dotychczasowa formuła, wyświetli poprawną wartość tylko dla umowy o pracę z normalnymi kosztami, czyli dla tej pozycji, dla której użytkownik podał koszty. Dla pozostałych wyświetli… wartość limitu!!! Kompletnie nie tak, jak tego chcemy.Dlaczego? Excel pominie bowiem wartość z pustych żółtych komórek Poniesione koszty. Jest na szczęście prosty sposób, aby tego uniknąć. O nim w dalszej części wpisu oczywiście.

Tak po prostu działają funkcje MIN i MAX (dotyczy obu – ja omówię na podstawie funkcji MIN). Nikt nie ma im tego za złe np. w takiej (standardowej) sytuacji: chcemy znaleźć najmniejszą wartość pensji wśród 10 pracowników, z których 2 nie ma danych:

Standardowe użycie funkcji MIN

Funkcja działa zgodnie z oczekiwaniami i pomija puste komórki. Gdyby tego nie zrobiła – najniższą wartością byłoby zero. Tutaj takie działanie funkcji jest jak najbardziej pożądane i ok.

U nas niestety tak nie jest… Zobaczcie, co będzie, gdy do białych komórek z formatki (K13:K17) wpiszę formułę:

=MIN(E4;K4)

Otrzymam taki wynik:

Ups!

Prawidłowy wynik otrzymaliśmy jedynie w przypadku zaznaczonym na żółto na obrazku . W pozostałych – puste komórki zostały pominięte, więc funkcja dostaje de facto tylko jedną wartość do „porównania”.

Jak temu zaradzić? Trzeba spowodować, żeby Excel „zobaczył” pustą komórkę. Wow, bardzo odkrywcze 😉 Robi się to tak, że po prostu wykonujemy na komórce jakąkolwiek operację matematyczną, która nie zmienia wyniku komórki. Czyli np: *1, /1, +0, -0 albo tzw. podwójna negacja, czyli: –. Nie ma znaczenia, wybierzcie tę, która do Was najbardziej przemawia. Ja najbardziej lubię –, ponieważ jest najszybsza i najczytelniejsza.

Wracając do naszych kosztów uzyskania przychodu, nasza formuła zatem powinna wyglądać tak:

=MIN(E4;--K4)

lub np. tak:

=MIN(E4;K4*1)

Teraz Excel wykona operację matematyczną na komórce, która może być pusta. Jeśli nie będzie – ok, nic nie tracimy. Jeśli będzie – Excel ją „zobaczy” właśnie poprzez tę operację. Zobaczy ją, ponieważ coś musi z nią zrobić.

Efekt jest taki:

Wynik

Tak zdecydowanie lepiej to wygląda 🙂 Mi wiele razy to już uratowało życie. Mam nadzieję, że Wam też się przyda! Jeśli tak – napiszcie proszę w komentarzu pod tym wpisem, w jakich sytuacjach okazało się to przydatne.

Powiązane produkty:

• WEBINAR: Jak zastąpić funkcję JEŻELI w Excelu? Nie zawsze, kiedy rozwiązanie excelowego problemu kojarzy nam się z funkcją JEŻELI – musimy jej używać. Jest szereg innych, często prostszych funkcji, które można użyć zamiast niej, choćby MIN czy MAX. Nie twierdzę oczywiście, że JEŻELI jest zła – wręcz przeciwnie: często jest ona jedynym wyjściem. Jednak nie zawsze i o tym jest webinar.

Plik do pobrania:
MalinowyExcel_Koszty uzuskania przychodu-MIN i MAX ograniczenie dw.xlsx
I wersja wideo: