Funkcja JEŻELI umożliwia logiczne porównanie wartości z oczekiwanym wynikiem przez przetestowanie, czy warunek jest spełniony, czy nie.
- = JEŻELI (jakieś wyrażenie jest prawdziwe, to wykonaj określone działanie, a w przeciwnym razie wykonaj inne działanie)
Instrukcja JEŻELI może zatem generować dwa wyniki. Pierwszy wynik otrzymamy, jeśli porównanie jest prawdziwe, a drugi — jeśli porównanie jest fałszywe.
Instrukcje JEŻELI są niezwykle wydajne i stanowią podstawę wielu modeli arkuszy kalkulacyjnych, ale są też główną przyczyną wielu problemów z arkuszem kalkulacyjnym. Najlepiej jest stosować instrukcję JEŻELI przy minimalnej liczbie warunków, na przykład mężczyzna/kobieta, tak/nie/być może, ale czasem może być konieczne oszacowanie bardziej złożonych scenariuszy, które wymagają zagnieżdżenia* równocześnie więcej niż 3 funkcji JEŻELI.
* "Zagnieżdżanie" odnosi się do praktyki łączenia wielu funkcji w jednej formule.
Szczegóły techniczne
Użyj funkcji JEŻELI, jednej z funkcji logicznych, aby zwrócić jedną wartość, jeśli warunek jest prawdziwy, lub inną wartość, jeśli jest fałszywy.
Składnia
JEŻELI(test_logiczny;wartość_jeżeli_prawda;[wartość_jeżeli_fałsz])
Na przykład:
- =JEŻELI(A2>B2;"Budżet przekroczony";"OK")
- =JEŻELI(A2=B2;B4-A4;"")
| Nazwa argumentu | Opis |
|---|---|
|
test_logiczny (wymagane) |
Warunek, który ma zostać sprawdzony. |
|
wartość_jeżeli_prawda (wymagane) |
Wartość, która ma zostać zwrócona, jeśli wynik logical_test ma wartość PRAWDA. |
|
wartość_jeżeli_fałsz (opcjonalne) |
Wartość, która ma zostać zwrócona, jeśli wynik logical_test ma wartość FAŁSZ. |
Spostrzeżenia
Program Excel pozwala zagnieżdżać maksymalnie 64 funkcje JEŻELI, ale zdecydowanie nie zaleca się korzystania z tej możliwości. Dlaczego?
- Poprawne skonstruowanie wielu instrukcji JEŻELI wymaga poświęcenia dużej ilości uwagi i dokładnego przeanalizowania logicznego działania wszystkich warunków. Jeśli nie uda Ci się idealnie dokładnie zagnieździć formuły, to może ona działać w 75% przypadków, ale dla pozostałej części danych może zwracać nieoczekiwane wyniki. Niestety, szanse na znalezienie tych 25% problematycznych przypadków są małe.
- Zarządzanie wieloma instrukcjami JEŻELI może okazać się niezwykle trudne, zwłaszcza gdy od ich napisania upłynie trochę czasu lub — co gorsza — wystąpi konieczność przeanalizowania instrukcji utworzonych przez inną osobę.
Jeśli instrukcja JEŻELI, nad którą pracujesz, zdaje się nie mieć końca, oznacza to, że czas odłożyć mysz i jeszcze raz przemyśleć strategię.
Poniżej przedstawiono, jak poprawnie utworzyć złożoną zagnieżdżoną instrukcję JEŻELI, używając wielu warunków JEŻELI, i kiedy należy uznać, że nadszedł czas, aby skorzystać z innego narzędzia programu Excel.
Przykłady
Poniższy przykład przedstawia w miarę standardową zagnieżdżoną instrukcję JEŻELI, pozwalającą oszacować, jak wyniki z testów przekładają się na stopnie uczniów.
- =JEŻELI(D2>89;"A";JEŻELI(D2>79;"B";JEŻELI(D2>69;"C";JEŻELI(D2>59;"D";"F"))))
Ta złożona zagnieżdżona instrukcja JEŻELI kieruje się następującą prostą logiką:
- Jeżeli wynik testu (w komórce D2) jest większy niż 89, uczeń otrzymuje stopień A.
- Jeżeli wynik testu jest większy niż 79, uczeń otrzymuje stopień B.
- Jeżeli wynik testu jest większy niż 69, uczeń otrzymuje stopień C.
- Jeżeli wynik testu jest większy niż 59, uczeń otrzymuje stopień D.
- W przeciwnym razie uczeń otrzymuje stopień F.
Ten konkretny przykład jest stosunkowo bezpieczny, ponieważ jest mało prawdopodobne, że korelacja między wynikami testów a ocenami zmieni się, więc nie wymaga wiele uwagi. Ale oto myśl - co zrobić, jeśli musisz podzielić oceny na A+, A i A- (i tak dalej)? Teraz instrukcja JEŻELI z czterema warunkami wymaga przepisania, tak aby zawierała 12 warunków! Formuła będzie teraz wyglądać następująco:
- =JEŻELI(B2>97;"A+";JEŻELI(B2>93;"5";JEŻELI(B2>89;"A-";JEŻELI(B2>87;"B+";JEŻELI(B2>83;"B";JEŻELI(B2>79;"B-";JEŻELI(B2>77;"C+";JEŻELI(B2>73;"C";JEŻELI(B2>69;"C-";JEŻELI(B2>57;"D+";JEŻELI(B2>53;"D";JEŻELI(B2>49;"D-";"F"))))))))))))
Jest ona w dalszym ciągu poprawna pod względem funkcjonalnym i będzie działać zgodnie z oczekiwaniami, ale napisanie jej zajmuje dużo czasu, a jeszcze więcej testowanie, czy działa tak, jak powinna. Innym poważnym problemem jest fakt, że wyniki i odpowiadające im oceny trzeba było wprowadzić ręcznie. Jakie jest prawdopodobieństwo, że pojawi się przypadkowy błąd? Wyobraź sobie teraz, że robisz to 64 razy przy bardziej skomplikowanych warunkach. Na pewno jest to możliwe, ale czy rzeczywiście chcesz włożyć tyle wysiłku i narazić się na prawdopodobne błędy, które naprawdę trudno będzie zauważyć?
Porada
Każda funkcja w programie Excel wymaga otwierającego i zamykającego nawiasu okrągłego (). W programie Excel różne części formuły są podczas edytowania oznaczone innym kolorem, co pomaga zorientować się, gdzie co jest. Na przykład podczas edytowania powyższej formuły w miarę przesuwania się kursora poza każdy nawias zamykający ")" odpowiadający mu nawias otwierający zostanie oznaczony tym samym kolorem. Może to być szczególnie przydatne w przypadku złożonych formuł zagnieżdżonych, gdy próbujesz ustalić, czy masz wystarczającą ilość odpowiadających sobie nawiasów.
Dodatkowe przykłady
Poniżej przedstawiono dość typowy przykład obliczania prowizji od sprzedaży na podstawie poziomów osiągniętych przychodów.
- =JEŻELI(C9>15000;20%;JEŻELI(C9>12500;17,5%;JEŻELI(C9>10000;15%;JEŻELI(C9>7500;12,5%;JEŻELI(C9>5000;10%;0)))))
Według tej formuły JEŻELI(C9 jest większe niż 15 000, wówczas dochód wynosi 20%, JEŻELI(C9 jest większe niż 12 500, to 17,5% i tak dalej...
O ile ta formuła jest bardzo podobna do wcześniejszego przykładu z ocenami, o tyle stanowi dobry przykład tego, jak trudna może być obsługa dużych instrukcji JEŻELI. Co trzeba byłoby zrobić, gdyby w organizacji zdecydowano się na dodanie nowych poziomów wynagrodzenia, a nawet zmianę istniejących kwot w złotych lub wartości procentowych? Miałbyś dużo pracy!
Porada
Możesz wstawić podziały wierszy na pasku formuły, aby ułatwić odczytywanie długich formuł. Wystarczy przed tekstem, który ma zostać przeniesiony do nowego wiersza, nacisnąć klawisze ALT+ENTER.
Oto przykład scenariusza prowizji z nieprawidłową logiką:
Czy widzisz, co jest nie tak? Porównaj kolejność porównań przychodu z poprzednim przykładem. W którą stronę zmierza? Zgadza się, idzie od dołu do góry (5 000 USD do 15 000 USD), a nie na odwrót. Ale dlaczego miałoby to być takie ważne? Jest to problem, ponieważ formuła nie może wyjść poza pierwsze oszacowanie dla żadnej wartości przekraczającej 5000 zł. Załóżmy, że masz 12 500 zł przychodu — instrukcja JEŻELI zwróci 10%, ponieważ kwota przekracza 5000 zł, i na tym się zatrzyma. To może być przyczyną poważnych problemów, ponieważ w wielu sytuacjach tego typu błędy pozostają niezauważone, dopóki nie pojawią się ich negatywne skutki. Co możesz zrobić, wiedząc, że złożone zagnieżdżone instrukcje JEŻELI wiążą się z pewnymi poważnymi pułapkami? W większości przypadków możesz użyć funkcji WYSZUKAJ.PIONOWO, zamiast tworzyć złożoną formułę za pomocą funkcji JEŻELI. Korzystając z funkcji WYSZUKAJ.PIONOWO, musisz najpierw utworzyć tabelę odwołań:
- =WYSZUKAJ.PIONOWO(C2;C5:D17;2;PRAWDA)
Ta formuła służy do wyszukiwania wartości z komórki C2 w zakresie C5:C17. Jeśli ta wartość zostanie znaleziona, wówczas zwracana jest odpowiadająca jej wartość z tego samego wiersza z kolumny D.
- =WYSZUKAJ.PIONOWO(B9;B2:C6;2;PRAWDA)
Ta formuła także wyszukuje wartość z komórki B9 w zakresie B2:B22. Jeśli ta wartość zostanie znaleziona, wówczas zwracana jest odpowiadająca jej wartość z tego samego wiersza z kolumny C.
Uwaga
W obu przypadkach funkcja WYSZUKAJ.PIONOWO ma na końcu formuły argument PRAWDA, co oznacza, że ma ona ustalić przybliżone dopasowanie. Innymi słowy, będzie ona dopasowywać dokładne wartości w tabeli odnośników, a także wartości znajdujące się między nimi. W takim przypadku konieczne jest posortowanie tabel odnośników w kolejności rosnącej od wartości najmniejszej do największej.
Omówiono tu znacznie bardziej szczegółowo funkcję WYSZUKAJ.PIONOWO, ale z pewnością jest to dużo prostsze niż złożona zagnieżdżona instrukcja JEŻELI o 12 poziomach. Ma ona też inne mniej oczywiste zalety:
- Tabele odwołań funkcji WYSZUKAJ.PIONOWO znajdują się obok, są otwarte i dobrze widoczne.
- Wartości w tabeli można łatwo aktualizować i nie trzeba modyfikować formuły w przypadku zmiany warunków.
- Jeśli nie chcesz, aby ktoś widział tabelę odwołań lub coś w niej zmieniał, po prostu umieść ją w innym arkuszu.
Czy wiesz?
Obecnie dostępna jest funkcja WARUNKI , która może zastąpić wiele zagnieżdżonych instrukcji JEŻELI jedną funkcją. Dlatego zamiast początkowego przykładu z ocenami, który zawierał 4 zagnieżdżone funkcje JEŻELI:
- =JEŻELI(D2>89;"A";JEŻELI(D2>79;"B";JEŻELI(D2>69;"C";JEŻELI(D2>59;"D";"F"))))
Można wprowadzić znacznie prostszą pojedynczą funkcję WARUNKI:
- =WARUNKI(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"2";PRAWDA;"1")
Funkcja WARUNKI jest bardzo wygodna, ponieważ nie musisz przejmować się wszystkimi instrukcjami JEŻELI i nawiasami.
Uwaga
Ta funkcja jest dostępna tylko dla posiadaczy subskrypcji platformy Microsoft 365. Jeśli jesteś subskrybentem platformy Microsoft 365, upewnij się, że masz najnowszą wersję pakietu Office.
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społecznościach.