Pobierz przykładowy plik z zadaniami z tego wpisu.

W Excelu 2019 pojawiła się nowa funkcja o nazwie WARUNKI, która w świetny sposób może zastąpić zagnieżdżoną funkcję JEŻELI.

Ranking sprzedawców

Załóżmy, że mamy w sklepie dwóch sprzedawców: Marka i Anię. W tabelce wpiszmy, ile klientów Ci sprzedawcy pozyskali w poszczególnych dniach. Chcemy, aby Excel w każdym wierszu podał imię sprzedawcy, który danego dnia pozyskał więcej klientów. Zadanie zrobimy na trzy sposoby.

Przejdź do komórki D2. Na początku zadanie wykonamy za pomocą dobrze znanej funkcji JEŻELI. Wpiszmy taką oto prostą formułę a następnie skopiujmy ją do pozostałych wierszy.

=JEŻELI(B2>C2; "Marek"; "Ania")

Funkcja JEŻELI przyjmuje trzy argumenty: test logiczny (pytanie); wartość jeśli prawda; wartość jeśli fałsz. Pierwszy argument to pytanie czy wartość w komórce B2 jest większa od wartości w komórce C2. W komórce B2 jest podana liczba klientów pozyskanych przez Marka. Jeśli warunek B2>C2 jest spełniony to pojawi się napis Marek w przeciwnym wypadku pojawi się napis Ania.

Niestety, ale funkcja JEŻELI ma pewną wadę: otóż mogą wystąpić tylko dwa przypadki: albo Marek pozyskał więcej klientów od Ani, albo Ania pozyskała więcej klientów. W przypadku gdy danego dnia oboje mieli tyle samo klientów na pytanie czy liczba w pierwszej komórce jest większa od liczby w drugiej komórce zostanie zwrócona wartość FAŁSZ czyli funkcja JEŻELI zwróci napis Ania.

Zagnieżdżając jedną funkcję JEŻELI wewnątrz drugiej możemy najpierw spytać się, czy obie komórki są sobie równe i wtedy Excel zwróci napis remis w przeciwnym wypadku wykona się druga funkcja JEŻELI, która z kolei sprawdzi kto pozyskał większą liczbę klientów. Niestety, ale taka funkcja jest mało czytelna (szczególnie, gdyby warunków było więcej).

=JEŻELI(B2=C2;"remis"; JEŻELI(B2>C2; "Marek"; "Ania"))

Pewnym rozwiązaniem jest funkcja WARUNKI, która przyjmuje parzystą liczbę argumentów: pierwszy argument to warunek (pytanie), drugi to wartość która ma być zwrócona gdy owy warunek jest spełniony. Możemy w sumie zdefiniować maksymalnie 127 warunków, ale w naszym przypadku będą tylko trzy.

=WARUNKI(B2=C2;"remis"; B2>C2;"Marek"; B2<C2;"Ania")

Dziennik szkolny

Prześledźmy inny przykład. Oto prosty arkusz do którego są wpisane oceny uczniów:

W komórce H2 użyłem funkcji o nazwie ŚREDNIA:

=ŚREDNIA(C2:G2)

Natomiast interesuje nas kolumna I. Tam pojawia się odpowiedni komentarz w zależności od tego, jaką średnią ma dany uczeń:

• zagrożony – jeśli średnia ocen jest mniejsza niż 2,0
• przeciętniak – jeśli średnia ocen jest mniejsza niż 4,5 (ale większa lub równa 2,0)
• prymus – jeśli średnia jest większa lub równa 4,5

Moglibyśmy użyć zagnieżdżoną funkcję JEŻELI:

=JEŻELI(H2<2;"zagrożony"; JEŻELI(H2<4,5;"przeciętniak";"prymus"))

Natomiast zagnieżdżona funkcja JEŻELI jest mało czytelna (szczególnie, gdyby tych przedziałów było kilka bądź kilkanaście). Dlatego preferuję funkcję WARUNKI, która będzie miała taką oto składnię:

=WARUNKI(H2<2;"zagrożony"; H2<4,5;"przeciętniak"; H2>=4,5;"prymus")

Prawidłowa kolejność argumentów

Funkcja WARUNKI działa w ten sposób, że jeśli dany warunek jest spełniony, to „wypisuje odpowiedni komunikat” i kończy swoje działanie. Prześledźmy działanie powyższej funkcji, gdy uczeń ma średnią 3,6.

• H4<2 – ten warunek nie jest spełniony, więc się nie pojawi komunikat zagrożony, funkcja WARUNKI sprawdzi czy pasuje kolejny warunek
• H4<4,5 – warunek został spełniony więc się pojawi komunikat przeciętniak i zakończy swoje działanie
• H4>=4,5 – ponieważ poprzedni warunek został spełniony więc funkcja zakończyła swoje działanie i ten warunek nawet nie był brany pod uwagę

A teraz specjalnie zmienimy kolejność argumentów:

=WARUNKI(H2<4,5; "przeciętniak"; H2<2;"zagrożony"; H2>=4,5;"prymus")

Prześledźmy działanie tej funkcji dla pierwszej osoby z listy, która ma średnią ocen 1,2:

• H2<4,5 – warunek jest prawdziwy (liczba 1,2 jak najbardziej jest mniejsza niż 4,5) więc pojawi się komunikat przeciętniak. Funkcja zakończy swoje działanie.
• H2<2,5 – ponieważ funkcja WARUNKI zakończyła swoje działanie więc ten warunek nawet nie będzie brany pod uwagę.
• H2>=4,5 j.w.

Wniosek: jeśli mamy kilka nachodzących na siebie warunków, to gdy pierwszy z nich zostanie spełniony funkcja zakończy swoje działanie. Dlatego warto jest podawać „posortowane” warunki w odpowiedniej kolejności np. rosnącej (wtedy gdy pytamy się po kolei czy dana komórka jest mniejsza od jakiejś wartości) lub malejącej (wtedy gdy pytamy się po kolei czy dana komórka jest większa od danej wartości)

Błąd #N/D

Błąd ten występuje, gdy nie jest spełniony żaden z warunków. Mamy taką oto listę pracowników:

Załóżmy, że będziemy oferować dodatkowe benefity (np. bilety do kina, owocowe czwartki) pewnym grupom społecznym (osoby wykluczone społecznie, którym trudniej jest się odnaleźć na rynku pracy)

• Kobietom
• Osobom poniżej 26 roku życia
• Osobom powyżej 50 roku życia
• Osobom mieszkającym na wsi

Tak więc nasza funkcja WARUNKI będzie wyglądała następująco:

=WARUNKI(C2="k";"tak"; D2<26;"tak"; D2>50;"tak"; E2="wieś";"tak")

Zwróć uwagę na to, co się stało w przypadku Pana Roberta i Bolesława. Przy nich pojawił się błąd #N/D, gdyż te osoby nie spełniają żadnego z czterech wyżej wymienionych warunków (ani nie czują się kobietami, ani nie są młodzi, ani nie są seniorami, jak również nie mieszkają na wsi).

Ponieważ funkcja WARUNKI nie znalazła żadnego dopasowania, to pojawił się owy błąd (jeśli znasz funkcję WYSZUKAJ.PIONOWO, to pewnie ten błąd jest znajomy). Istnieją dwa sposoby pozbycia się tego błędu.

Opakowanie błędu w funkcję JEŻELI.BŁAD

Ten temat opisywałem przy okazji omawiania funkcji WYSZUKAJ.PIONOWO, więc nie będę się zbytnio rozwodził. Nasza poprawiona formuła będzie wyglądała następująco:

=JEŻELI.BŁĄD(WARUNKI(C2="k";"tak";D2<26;"tak";D2>50;"tak";E2="wieś";"tak"); "brak")

Dodanie na końcu warunku, który zawsze jest prawdziwy

Sprawa jest banalna. Można dodać jakiś oczywisty warunek, który zawsze jest spełniony. Np. w kolumnie D (Wiek) zazwyczaj będzie liczba większa od 0 (chyba, że ktoś zrobi nam psikusa i nie wypełni tej kolumny, lub co gorsza w ramach dowcipu wpisze przy jakimś nazwisku ujemny wiek). Dlatego też dużo bezpieczniej jest dodać taki oto warunek: 0<1. Jakby na to nie patrzeć – zawsze w matematyce zero będzie mniejsze od jedynki. Czyli nasza funkcja WARUNKI będzie wyglądała następująco:

=WARUNKI(C2="k";"tak"; D2<26;"tak"; D2>50;"tak"; E2="wieś";"tak"; 0<1;"brak")