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

Ważne : Ten artykuł został przetłumaczony maszynowo, zobacz zastrzeżenie. Angielskojęzyczną wersję tego artykułu można znaleźć tutaj .

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

Gdy program Excel będzie można zagnieździć maksymalnie 64 różne funkcje Jeżeli, w ogóle zalecane jest to zrobić. Dlaczego?

  • Wiele instrukcji jeżeli wymagają dużą myślowych poprawnie i upewnij się, że ich można poprawnie oblicza do każdego warunku Dosunięty do końca. Jeśli nie użyto do formuły 100% dokładnie w taki sposób, następnie go może 75% czasu pracy, ale zwracać nieoczekiwane wyniki 25% czasu. Niestety prawdopodobieństwo: Przechwytywanie 25% są obsługiwane.

  • Wiele instrukcji jeżeli może stać się bardzo trudne, zwłaszcza gdy wrócić później trochę czasu i spróbujesz wiadomo, które lub odpowiadającą innej osobie, próbował wykonać.

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 nadal funkcjonalny dokładne i działa zgodnie z oczekiwaniami, ale zajmuje dużo czasu, aby zapisać i dłużej Sprawdź, czy to, co ma. Inny problem glaring to, że przeglądały ręcznie wprowadź wyników i ocen literowych równoważne. Co to są prawdopodobieństwo, że przypadkowo masz pisowni? Załóżmy teraz próby wykonania bardziej złożone kryteria tym 64 razy! Na pewno jest możliwe, ale czy na pewno chcesz poddać sobie tego rodzaju nakładu i prawdopodobne błędy, które będą naprawdę trudne do?

Porada : Każda funkcja w programie Excel wymaga otwierający i zamykający nawias okrągły (). Program Excel próbuje pomóc Ci zorientować się, co przeznaczonych przez kolorowania różne części formuły podczas edytowania go. Na przykład, gdyby edytowanie powyższej formuły, podczas Przenieś kursor poza każdego z nawiasami zakończenia ")", jego odpowiednich nawias otwierający zmieni się na tym samym kolorem. Może to być przydatne w złożone formuły zagnieżdżonej, gdy próbujesz ustalanie, czy masz za mało nawiasów zgodne.

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

Podczas jego nadzwyczajnie podobne do wcześniejszej ocen przykład ta formuła jest doskonałe przykładem jak trudne może być do obsługi dużych instrukcji jeżeli — co chcesz należy zrobić, jeśli Twoja organizacja zdecydowała dodać nowe poziomy rekompensaty, a nawet zmienić mogłoby Kwota istniejącego lub wartości procentowe? Czy masz wiele zadań na dłoni!

Porada : Na pasku formuły, aby ułatwić długiej formuły można wstawiać podziały wierszy. Przed tekstem, który ma być zawijany do nowego wiersza, po prostu naciśnij 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)

Podobnie ta formuła wyszukuje wartość z komórki B9 w zakresie B2:B22. Jeśli argument zostanie znaleziony, zwracany jest tekst odpowiadająca jej wartość w tym samym wierszu w kolumnie C.

Uwaga : Na koniec formuły, co oznacza, że chcemy, aby ustalić dopasowanie approxiate obie te VLOOKUPs Użyj argument PRAWDA. Innymi słowy będą zgodne dokładne wartości w tabeli odnośników, a także wartości, które wykraczają między nimi. W tym przypadku tabel odnośników muszą być uporządkowane od najmniejszych do największych kolejności rosnąco.

Wyszukaj.pionowo jest objęte znacznie więcej szczegółów tutaj, ale się, że jest to znacznie łatwiejsze niż poziom 12, złożonych zagnieżdżonej instrukcji Jeżeli! Istnieją inne mniej oczywiste korzyści:

  • 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ł
Używanie błędów sprawdzania wykrywanie błędów w formułach
Funkcje logiczne
Funkcje programu Excel (lista alfabetyczna)
funkcje programu Excel (według kategorii)

Uwaga : Zrzeczenie dotyczące tłumaczenia maszynowego: Ten artykuł został przetłumaczony przez system komputerowy bez interwencji człowieka. Firma Microsoft udostępnia te tłumaczenia maszynowe, aby ułatwić użytkownikom, którzy nie znają języka angielskiego, korzystanie z zawartości dotyczącej produktów, usług i technologii firmy Microsoft. Ponieważ ten artykuł został przetłumaczony maszynowo, może zawierać błędy w słownictwie, składniowe lub gramatyczne.

Rozwijaj swoje umiejętności
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.

×