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

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 IF są niesamowicie niezawodne i tworzą podstawę wielu modeli arkuszy kalkulacyjnych, ale są również głównym powodem wielu problemów z arkuszem kalkulacyjnym. Idealnym rozwiązaniem jest zastosowanie instrukcji jeżeli do minimalnych warunków, takich jak męski/Kobieta, tak/nie/może nadawać nazwy kilku, ale czasami konieczne jest wypróbowanie bardziej złożonych scenariuszy, które wymagają zagnieżdżenia * więcej niż 3 Jeśli 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.

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.

Ten konkretny przykład jest relatywnie bezpieczny, ponieważ nie jest prawdopodobne, że korelacje między wynikami testów i ocenami liter zmienią się, więc nie będzie konieczne przeprowadzenie wielu czynności konserwacyjnych. Warto jednak wykonać następujące czynności — co zrobić, jeśli potrzebujesz dzielić oceny między A +, A i A-(itd.)? Teraz należy mieć cztery warunki, jeśli w celu uzyskania 12 warunków należy ponownie napisać instrukcję. Wygląd formuły wygląda teraz 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.

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

Gdy remarkably jest podobne do wcześniejszej przykładowej oceny, ta formuła to wspaniały przykład, w jaki sposób trudno jest zachować duże instrukcje jeżeli — co należy zrobić, jeśli Twoja organizacja zdecydowała się na dodanie nowych poziomów wynagrodzeń, a nawet zmienić istniejące wartości w dolarach lub wartościach procentowych? Masz dużo prac nad 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ą:

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, co jest niewłaściwe? Porównaj kolejność przychodów z poprzedniego przykładu. Który sposób tego przeszkadza? Jest to prawo od dołu ($5 000 do $15 000), a nie w inny sposób. Ale dlaczego warto mieć taką wielką transakcję? Jest to bardzo duży, ponieważ formuła nie może przekazywać pierwszej oceny o dowolnej wartości przekraczającej $5 000. Załóżmy, że otrzymano $12 500 w ramach przychodu — wyrażenie Jeżeli zwróci wartość 10%, ponieważ jest większe niż $5 000 i zostanie zatrzymane. Może to powodować problemy z niesamowicie, ponieważ w wielu sytuacjach błędy te są niezauważalne, dopóki nie miały negatywnego wpływu. Co można zrobić, wiedzą, że istnieją poważne pułapek związanych z złożonymi zagnieżdżonymi instrukcjami Jeżeli? W większości przypadków można użyć funkcji Wyszukaj. pionowo zamiast konstruowania złożonej formuły za pomocą funkcji jeżeli. Używając funkcji Wyszukaj. pionowo, należy najpierw utworzyć tabelę odwołań:

Formuła w komórce D2 to =WYSZUKAJ.PIONOWO(C2;C5:D17;2;PRAWDA)
  • =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.

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 obejmuje znacznie więcej szczegółów,ale jest to bardzo prostsze 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 IFS , która może zastępować wiele zagnieżdżonych instrukcji jeżeli za pomocą jednej funkcji. 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 usługi 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, 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 jeżelifunkcja pozycja. warunki
(Microsoft 365, Excel 2016 i nowsze)
funkcja LICZ. Jeżeli zlicza wartości na podstawie pojedynczych kryteriów
, funkcja LICZ zlicza wartości na podstawie wielu kryteriów
Funkcja suma będzie sumować wartości na podstawie pojedynczych kryteriów
, funkcja suma sumowanie wartości na podstawie wielu kryteriów
i
funkcji
Wyszukaj
. pionowo,Omówienie formuł w programie Excel
sposób unikania formuł
nieuszkodzonychwykrywa błędy w formułach
funkcjelogiczne
programu Excel (alfabetycznie)
funkcjeprogramu Excel (według kategorii)

Uwaga:  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. Czy możesz dać nam znać, czy te informacje były pomocne? Oto angielskojęzyczny artykuł do wglądu.

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.

×