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ą niesamowicie niezawodne i stanowią podstawę wielu modeli arkuszy kalkulacyjnych, ale są również główną przyczyną wielu problemów z arkuszami kalkulacyjnymi. Najlepiej, jeśli instrukcja JEŻELI powinna mieć zastosowanie do minimalnych warunków, takich jak mężczyzna/kobieta, tak/nie/może, aby wymienić tylko kilka, ale czasami może być konieczne oszacowanie bardziej złożonych scenariuszy wymagających zagnieżdżenia* więcej niż 3 funkcje JEŻELI razem.
* „Zagnieżdżanie” odnosi się do praktyki łączenia wielu funkcji w jednej formule.
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 argumentu test_logiczny ma wartość PRAWDA. |
|
wartość_jeżeli_fałsz (opcjonalne) |
Wartość, która ma zostać zwrócona, jeśli wynik argumentu test_logiczny ma wartość FAŁSZ. |
Uwagi
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ć wymaganej 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, to 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ż nie jest prawdopodobne, że korelacja między wynikami testów a ocenami liter zmieni się, więc nie będzie wymagać dużej konserwacji. Ale oto myśl — co zrobić, jeśli trzeba podzielić oceny między A +, A i A- (i tak dalej)? Teraz cztery warunki JEŻELI instrukcja musi być przepisany, aby mieć 12 warunków! Formuła będzie teraz wyglądać następująco:
-
=JEŻELI(B2>97;"A+";JEŻELI(B2>93;"A"; 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, a przy tym 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...
Choć jest niezwykle podobna do wcześniejszego przykładu ocen, ta formuła jest doskonałym przykładem tego, jak trudno jest utrzymać duże instrukcje JEŻELI — co by było potrzebne, gdyby Twoja organizacja zdecydowała się dodać nowe poziomy wynagrodzenia, a nawet zmienić istniejące wartości dolara lub procentu? Chcesz mieć dużo pracy na rękach!
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ą:
Widzisz, co jest nie tak? Porównaj kolejność porównań przychodów z poprzednim przykładem. W którą stronę idzie ten? To prawda, to będzie od dołu do góry (5.000 dolarów do 15.000 dolarów), a nie na odwrót. Ale dlaczego to ma być tak wielka sprawa? To wielka sprawa, ponieważ formuła nie może przejść pierwszej oceny dla żadnej wartości powyżej 5000 zł. Załóżmy, że masz 12 500 zł przychodu — instrukcja JEŻELI zwróci wartość 10%, ponieważ jest większa niż 5000 zł i zostanie zatrzymana. Może to być niezwykle problematyczne, ponieważ w wielu sytuacjach tego typu błędy są niezauważone, dopóki nie mają negatywnego wpływu. Więc wiedząc, że istnieją poważne pułapki ze złożonymi zagnieżdżonymi instrukcjami JEŻELI, co możesz zrobić? W większości przypadków można użyć funkcji WYSZUKAJ.PIONOWO zamiast tworzyć złożoną formułę z funkcją JEŻELI. Za pomocą funkcji WYSZUKAJ.PIONOWO należy 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.
Funkcja WYSZUKAJ.PIONOWO jest tutaj omówiona znacznie bardziej szczegółowo, ale z pewnością jest o wiele prostsza niż 12-poziomowa, złożona zagnieżdżona instrukcja JEŻELI! 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 istnieje 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;"D";PRAWDA;"F")
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.Kup subskrypcję lub wypróbuj platformę Microsoft 365
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w Społecznościach.
Tematy pokrewne
Klip wideo: zaawansowane funkcje JEŻELIFunkcja WARUNKI (Microsoft 365, Excel 2016 i nowsze)Funkcja LICZ.JEŻELI zlicza wartości na podstawie jednego kryteriumFunkcja LICZ.WARUNKI będzie zliczać wartości na podstawie wielu kryteriówFunkcja SUMA.JEŻELI zsumuje wartości na podstawie jednego kryteriumFunkcja SUMA.WARUNKÓW zsumuje wartości na podstawie wielu kryteriówORAZ funkcjiLUB funkcjiWYSZUKAJ.PIONOWOOmówienie formuł w programie ExcelJak unikać niepoprawnych formułWykrywanie błędów w formułachFunkcje logicznefunkcje programu Excel (alfabetycznie)Funkcje programu Excel (według kategorii)
