Funkcja JEŻELI — unikanie pułapek związanych z formułami zagnieżdżonymi

Funkcja JEŻELI — unikanie pułapek związanych z formułami zagnieżdżonymi

Uwaga: Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

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)

Aby wyrażenia IF może zawierać dwóch wyników. Pierwszy wynik to, jeśli porównania ma wartość PRAWDA, druga, jeśli porównania ma wartość FAŁSZ.

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. Instrukcję JEŻELI najlepiej jest stosować przy minimalnej liczbie warunków, na przykład mężczyzna/kobieta, tak/nie/być może. Czasem jednak 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.

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.

Złożona zagnieżdżona instrukcja JEŻELI — formuła w komórce E2 to =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"))))))))))))
  • =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ą:

  1. Jeżeli wynik testu (w komórce D2) jest większy niż 89, uczeń otrzymuje stopień A.

  2. Jeżeli wynik testu jest większy niż 79, uczeń otrzymuje stopień B.

  3. Jeżeli wynik testu jest większy niż 69, uczeń otrzymuje stopień C.

  4. Jeżeli wynik testu jest większy niż 59, uczeń otrzymuje stopień D.

  5. W przeciwnym razie uczeń otrzymuje stopień F.

W tym przykładzie określonego jest stosunkowo bezpieczne, ponieważ nie jest korelacji między wyników testów i ocen literowych zostanie zmieniona, więc nie wymaga dużo konserwacji. Ale myśli — co zrobić, jeśli musisz segmentu ocen między A + A i A - (i tak dalej)? Teraz instrukcji jeżeli cztery warunek wymaga ponownego ma 12 warunków! Poniżej opisano, jak formuła będzie wyglądać teraz:

  • =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.

Formuła w komórce D9 to 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)))))
  • =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? Wymagałoby to dużego nakładu 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ą:

Formuła w komórce D9 to JEŻELI(C9>5000;10%;JEŻELI(C9>7500;12,5%;JEŻELI(C9>10000;15%;JEŻELI(C9>12500;17,5%;JEŻELI(C9>15000;20%;0)))))

Czy widzisz, na czym polega problem? Porównanie kolejności porównania zysk z poprzedniego przykładu. Który sposób jest to jeden rozpoczęcie pracy? To prawo ma od dołu w górę (5000 zł do zł, 15 000), nie na odwrót. Ale Dlaczego który się transakcji big? Jest on transakcji big, ponieważ formuła nie można przekazać pierwszej oceny dla wartości ponad 5000 zł. Załóżmy, że masz 12 500 $ przychodów — wykonywania instrukcji jeżeli zwróci 10%, ponieważ jest większa niż 5000 zł i będą już dostępne. Może to być niezwykle problematyczny, ponieważ w wielu sytuacjach niezauważalnie Przejdź typy błędów, dopóki nie zostały one miały negatywny wpływ. Aby wiedzy, które są niektóre poważne problemów z złożonych zagnieżdżonych instrukcji Jeżeli, co możesz zrobić? W większości przypadków zamiast tworzenia złożonych formuły z funkcją jeżeli można użyć funkcji Wyszukaj.pionowo. Przy użyciu funkcji Wyszukaj.pionowo, najpierw należy utworzyć tabelę odwołania:

Formuła w komórce D2 to =WYSZUKAJ.PIONOWO(C2;C5:D17;2;PRAWDA)
  • =WYSZUKAJ.PIONOWO(C2;C5:D17;2;PRAWDA)

Ta formuła komunikat wyszukać wartości w komórce C2 w zakresie C5:C17. Jeśli argument zostanie znaleziony, zwracany jest tekst odpowiadająca jej wartość w tym samym wierszu w kolumnie D.

Formuła w komórce C9 to =WYSZUKAJ.PIONOWO(B9;B2:C6;2;PRAWDA)
  • =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 została omówiona znacznie bardziej szczegółowo tutaj, ale z pewnością jest dużo prostsza 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?

Ma teraz, Funkcja IFS zastąpić wiele zagnieżdżonych instrukcji Jeżeli przy użyciu jednej funkcji. Dlatego zamiast naszym przykładzie ocen początkowej, który ma 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 usługi Office 365. Jeśli jesteś subskrybentem usługi Office 365, upewnij się, że masz najnowszą wersję pakietu Office.

Wypróbuj usługę Office 365 lub najnowszą wersję programu Excel

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Tematy pokrewne

Klip wideo: Zaawansowane funkcje Jeżeli
Funkcja IFS (Office 365, programie Excel 2016 lub nowszy)
LICZ.Jeżeli będzie zliczanie wartości na podstawie kryteriów pojedynczy
LICZ.warunki policzy wartości w oparciu o wiele kryteria
Suma.Jeżeli będzie sumowanie wartości na podstawie kryteriów pojedynczy
Suma.warunków będzie sumowanie wartości na podstawie wielu kryteriów
funkcji oraz
funkcji lub
Wyszukaj.pionowo
Omówienie formuł w programie Excel
jak unikać niepoprawnych formuł
Wykrywanie błędów w formułach
Funkcje logiczne
Funkcje (alfabetycznie) w programie Excel
funkcje programu Excel (według kategorii)

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×