Funkcja JEŻELI jest jedną z najważniejszych funkcji w Excelu. Służy ona do zwrócenia jednej z dwóch wartości, w zależności od tego, czy warunek jest spełniony. Funkcja JEŻELI przyjmuje trzy argumenty:
- Pierwszy argument funkcji JEŻELI to tzw. Test logiczny. Na chłopski rozum test logiczny można porównać do pytania, na które odpowiedź będzie brzmiała PRAWDA lub FAŁSZ (albo TAK lub NIE).
- Drugi argument jest to wartość, którą zwróci funkcja JEŻELI jeśli odpowiedź na pytanie jest twierdząca (wartość zwrócona przez test logiczny wynosi PRAWDA).
- Trzeci argument jest to wartość jaką ma zwrócić funkcja gdy warunek nie został spełniony (wartość zwrócona przez test logiczny wynosi FAŁSZ)
Pobierz skoroszyt z ćwiczeniami
Rozpatrzmy taki oto prosty przykład:
Chcemy aby w kolumnie E przy każdym nazwisku pojawił się ciąg znaków Tak lub Nie w zależności od tego, czy uczeń zdobył minimum 40 punktów. Nasza funkcja JEŻELI (którą należy wpisać do komórki E2) mogłaby wyglądać następująco:
=JEŻELI(D2>=40; "Tak"; "Nie")Następnie taką formułę możemy skopiować do pozostałych komórek. I teraz przy każdym uczniu pojawi się napis Tak lub Nie. Zwróć uwagę, że wartości zwracane przez funkcję JEŻELI są wartościami tekstowymi (a nie liczbowymi) więc wartości Tak i Nie muszą być ujęte w cudzysłów.
Oto graficzna składnia funkcji JEŻELI:
To co jest w drugim i trzecim argumencie funkcji JEŻELI jest to tekst, jaki zwraca ta funkcja. Ten tekst jest „dla nas” i nie musi być na sztywno Tak, Nie. Równie dobrze moglibyśmy wpisać coś takiego:
=JEŻELI(D2>=40; "Zdał"; "Oblał")Albo
=JEŻELI(D2>=40; "Kujon"; "Leser")Oczywiście tutaj w teście logicznym mamy na sztywno zapisaną wartość 40. Dużo lepiej by było porównać liczbę zdobytych punktów nie bezpośrednio z liczbą 40 a z zawartością komórki I1 do której wpisałem próg zaliczenia. Dlaczego? Otóż jeśli zbyt mało osób uzyska zaliczenie wystarczy wtedy wpisać nową wartość progu zaliczenia do komórki I1 bez potrzeby modyfikowania formuły w kilkunastu komórkach. Więc zmieńmy tę formułę na taką:
=JEŻELI(D2>=$I$1; "Tak"; "Nie")Zwróć uwagę, że adres komórki I1 musiałem „zadolarować”. Jeśli nie wiesz o co chodzi z tymi dolarami, rzuć okiem na notkę Adresowanie względne i bezwzględne (absolutne).
Operatory porównania
Powyższe zadanie moglibyśmy zrobić też „na odwrót”. W poprzednich przypadkach pytaliśmy się, „czy uczeń zdobył wystarczającą liczbę punktów” (czyli czy wartość w komórce D2 jest większa lub równa 40) i gdy warunek został spełniony to funkcja zwróciła tekst Tak, w przeciwnym wypadku funkcja zwróciła tekst Nie. Albo możemy również się spytać „czy uczeń oblał”:
=JEŻELI(D2<$I$1; "Nie"; "Tak")Zwróć uwagę, że teraz mamy „dziubek” skierowany w drugą stronę. Nie ma też operatora = po dziubku, gdyż teraz się pytamy, czy liczba punktów jest mniejsza. Z tego powodu też należało zamienić kolejnością wartości zwracane przez funkcje (zamieniamy miejscami Tak z Nie gdyż jeśli uczeń nie przekroczył progu zaliczenia to po prostu nie zdał).
Funkcja JEŻELI zazwyczaj porównuje ze sobą dwie wartości. Oto jakich operatorów porównania możemy użyć w funkcji JEŻELI:
| Operator | Opis |
| = | równe np. D2=40 (czyli czy uczeń zdobył „idealnie” 40 punktów) |
| > | większe od np. D2>15 |
| >= | większe lub równe (czyli 40 punktów też się łapie na zaliczenie) |
| < | mniejsze od |
| <= | mniejsze lub równe |
| <> | różne od |
Zwracanie wartości liczbowych przez funkcję JEŻELI
Rozpatrzmy taki przykład
Mamy tutaj pracowników. I przy każdym pracowniku wpisałem liczbę spóźnień. Chcemy tym lepszym naliczyć 100 zł premii (załóżmy, że za mniej niż 3 spóźnienia naliczamy premię). No to napiszmy naszą funkcję:
=JEŻELI(D2<3; 100)Tutaj zwróć uwagę na fakt, że funkcja JEŻELI zwraca liczbę dlatego drugi argument funkcji jeżeli nie jest opakowany w cudzysłów. Niestety ale gdy pominie się trzeci argument funkcji JEŻELI to tam, gdzie warunek nie został spełniony pojawiła się wartość FAŁSZ:
Aby temu zapobiec należy osobom, które nie spełniły owego warunku przyznać premię w wysokości 0 zł. A więc tak powinna wyglądać nasza funkcja:
=JEŻELI(D2<3; 100; 0)Zwróć uwagę, że „pytaliśmy się” czy liczba spóźnień jest mniejsza od 3. Ponieważ liczba spóźnień zazwyczaj jest liczbą całkowitą (nie można mieć 2,5 spóźnienia – albo danego dnia się spóźniłeś albo przyszedłeś punktualnie) to możemy też spytać się czy liczba spóźnień jest mniejsza lub równa 2:
=JEŻELI(D2<=2; 100; 0)Zwracanie prostych wyrażeń arytmetycznych
Do tej porty funkcja JEŻELI zwracała albo tekst albo liczbę. Ale równie dobrze może ona zwrócić proste wyrażenie arytmetyczne np. D2*5%
Rzućmy okiem na takie zadanie:
Jeśli pracownik ma pensję minimum 2500 (czyli większą lub równą) to chcemy mu naliczyć premię w wysokości 5% pensji zasadniczej. W przeciwnym wypadku premia wynosi 0. Nasza formuła będzie wyglądała następująco:
=JEŻELI(D2>=2500; D2*5%; 0)Ponieważ procent to nic innego jak ułamek, możemy równie dobrze wysokość pensji pomnożyć przez ułamek 0,05:
=JEŻELI(D2>=2500; D2*0,05; 0)Argument tekstowy w teście logicznym
W młodszych grupach w przedszkolach możemy się spotkać z dwoma rodzajami „przedszkolanek” nauczyciel oraz asystent nauczyciela. Aby piastować stanowisko nauczyciela należy mieć ukończone wykształcenie wyższe (dlatego każdy nauczyciel wychowania wczesnoszkolnego obraża się, gdy taką osobę potocznie nazywa się przedszkolanką). Taki nauczyciel często ma do pomocy zatrudnionego asystenta nauczyciela, który może mieć wykształcenie średnie + ukończony odpowiedni kurs. Tak więc uzupełnijmy naszą tabelkę:
Zwróć uwagę, że tutaj mamy tylko dwa rodzaje stanowisk: albo szukamy nauczyciela, albo asystenta. Jeśli Twoja funkcja jeżeli będzie wyglądała następująco:
=JEŻELI(C2=nauczyciel; "wyższe"; "średnie")To Excel zwróci błąd #NAZWA?. Otóż nie porównujemy komórki C2 z wartością (np. komórką o zdefiniowanej nazwie nauczyciel) tylko z tekstem, dlatego w teście logicznym przy porównywaniu zawartości komórki C2 do tekstu należy słowo nauczyciel opakować w cudzysłów:
=JEŻELI(C2="nauczyciel"; "wyższe"; "średnie")Zagnieżdżanie funkcji w teście logicznym
W języku polskim jest taka reguła, że imiona damskie kończą się na literę a. Imiona męskie kończą się na wszystkie pozostałe litery, tylko nie ma a (jest kilka wyjątków np. Kuba, Bonawentura, Barnaba, Dyzma, Kosma, Juda ale na tym etapie nauki Excela nie będziemy komplikować ćwiczenia).
Oczywiście reguła nie tyczy się zagranicznych imion (tak więc jak odwiedzi nas kolega ze wschodu o imieniu Wania lub Sasza, to według Excela będzie on kobietą).
Do czego można wykorzystać tę wiedze? Czasami mamy taką sytuację, że posiadamy bazę pracowników, ale nie ma w niej kolumny PŁEĆ a np. chcemy za pomocą korespondencji seryjnej wysłać zaproszenia tylko do kobiet.
Oto przykładowa baza danych:
Możemy w kolumnie D wyodrębnić ostatnią literę imienia korzystając z funkcji PRAWY:
=PRAWY(B2; 1)Następnie w kolumnie E można skorzystać z funkcji JEŻELI:
=JEŻELI(D2="a"; "Kobieta"; "Mężczyzna")Ale w teście logicznym zamiast odwołania do komórki D2 moglibyśmy wpisać to co było pierwotnie wpisane w komórce D2. I wtedy nasza funkcja będzie wyglądała następująco:
=JEŻELI(PRAWY(B2; 1)="a"; "Kobieta"; "Mężczyzna")Zwracanie pustego ciągu znaków
W następnym arkuszu mamy kolejne pomiary
Jeśli danego dnia odnotowana temperatura przekroczyła 6,5 stopnia to chcemy aby pojawił się komunikat ZEPSUTE MIĘSO. W przeciwnym przypadku niech komórka będzie pusta.
Jeśli napiszesz funkcję JEŻELI w ten sposób:
=JEŻELI(B2>6,5; "ZEPSUTE MIĘSO")To w komórkach gdzie temperatura była niższa pojawi się słowo FAŁSZ. Możemy to zrobić nieco inaczej:
=JEŻELI(B2>6,5; "ZEPSUTE MIĘSO"; "świeże mięso")Albo zamiast słowa świeże mięso możemy zwrócić np. „samą kreskę”
=JEŻELI(B2>6,5; "ZEPSUTE MIĘSO"; "-")Ale jeśli jako trzeci argument będzie tylko dwukrotnie wpisany cudzysłów (czyli „nic” w cudzysłowie) to zostanie zwrócony pusty ciąg znaków:
=JEŻELI(B2>6,5; "ZEPSUTE MIĘSO"; "")
Porównywanie ze sobą dwóch komórek
Do tej pory zazwyczaj porównywaliśmy czy w danej komórce liczba jest większa/mniejsza od jakiejś wartości. Ale równie dobrze możemy porównywać ze sobą dwie komórki. Oto prosty ranking sprzedawców:
Jeśli danego dnia większą sprzedaż miał Marek, to chcemy aby w komórce D2 pojawiło się słowo Marek. Jeśli danego dnia Ania pozyskała większą liczbę klientów to w komórce D2 chcemy aby pojawiło się słowo Ania.
Zadanie bardzo proste, ale często sprawia kłopoty. W komórce D2 „pytamy się” czy liczba w B2 jest większa od liczby z komórki C2. Wtedy większą sprzedaż ma Marek. W przeciwnym przypadku większą sprzedaż ma Ania. No więc do dzieła!
=JEŻELI(B2>C2; "Marek"; "Ania")Jeśli imiona handlowców (które są w komórkach B1 i C1) w kolejnych tygodniach by się zmieniały, to wtedy owe imiona nie powinny być na sztywno zdefiniowane w funkcji JEŻELI, tylko musimy się odwołać do komórek B1 i C1 (pamiętaj o dolarach!):
=JEŻELI(B2>C2; $B$1; $C$1)Taka formuła jest bardziej uniwersalna, ale również mniej czytelna.
Zagnieżdżona funkcja JEŻELI
No dobra. Zrobiłem tutaj małego psikusa. Otóż w piątek obie osoby pozyskały tę samą liczbę klientów. Niestety w takim wypadku funkcja JEŻELI twierdzi, że w piątek konkurs wygrała Ania (bo pytaliśmy się, czy w komórce gdzie jest podana liczba klientów zdobytych przez Marka jest liczba większa od liczby znajdującej się w komórce gdzie jest liczba klientów pozyskanych przez Anię). A powinien być remis! Jest to związane z tym, że funkcja jeżeli może zwrócić tylko jedną z dwóch wartości. Tutaj możemy np. użyć zagnieżdżonej funkcji JEŻELI (czyli będziemy mieć JEŻELI wewnątrz JEŻELI). Oto jedno z przykładowych rozwiązań:
=JEŻELI(B2=C2; "Remis!"; JEŻELI(B2>C2; "Marek"; "Ania"))Jak działa taka dziwna konstrukcja? Otóż najpierw wykona się zewnętrzna funkcja JEŻELI, która sprawdza czy obie wartości są sobie równe. Jeśli tak, to funkcja zwróci tekst Remis. Natomiast jeśli obie wartości są sobie różne, to zewnętrzna funkcja JEŻELI zwróci wartość jeśli fałsz, a tą wartością nie jest np. słowo „brak remisu” tylko druga (zwana wewnętrzną) funkcja JEŻELI.
Wskazówka: zagnieżdżona funkcja JEŻELI (szczególnie, gdy tych warunków jest więcej) jest mało czytelna. Dlatego też jeśli masz Excela w wersji 2019 zapoznaj się z opisem funkcji WARUNKI. W pewnych sytuacjach może się również przydać znajomość funkcji WYSZUKAJ.PIONOWO (wyszukiwanie przybliżone, gdy szukasz „w jaki przedział wpada dana wartość”).
Wskazówka2: Zapoznaj się z opisem funkcji ORAZ i LUB. Pozwalają one "zadać więcej niż jedno pytanie w funkcji JEŻELI"
