Funkcja Excel z wieloma IF
Wiele instrukcji JEŻELI lub zagnieżdżonych Jeśli w programie Excel jest instrukcja JEŻELI wewnątrz innej instrukcji JEŻELI. Możemy dołączyć dodatkowe instrukcje JEŻELI do argumentów „wartość_jeżeli_prawda” i „wartość_jeżeli_fałsz” normalnej formuły JEŻELI w programie Excel. Kiedy musimy przetestować więcej niż jeden warunek w tym samym czasie i zwrócić różne wartości, używamy zagnieżdżonych IF lub wielu IF w Excelu.
Wyjaśnione
W danych programu Excel zdarzają się sytuacje, w których musimy użyć więcej niż jednego lub dwóch warunków, aby znaleźć określone dane, skromne użycie funkcji If lub funkcja z funkcją logiczną może nie być pomocna, więc możemy użyć wielu instrukcji If w programie Excel inside pojedyncza instrukcja If, w zagnieżdżonej instrukcji if, jeśli warunek jest spełniony, wynik jest wyświetlany, ale jeśli warunek nie jest spełniony, następnie, jeśli instrukcja jest wykonywana.
Formuła „JEŻELI” jest używana, gdy chcemy przetestować warunek i zwrócić jedną wartość, jeśli warunek jest spełniony, i inną wartość, jeśli nie jest spełniony.
Każdy kolejny FI jest włączany do argumentu „wartość_jeżeli_fałsz” poprzedniego FI. Tak więc zagnieżdżona formuła programu Excel IF działa w następujący sposób:
Składnia
JEŻELI (warunek1, wynik1, JEŻELI (warunek2, wynik2, JEŻELI (warunek3, wynik3,…)))
Przykłady
Przykład 1
Jeśli chcemy dowiedzieć się, jak uczeń wypadł na egzaminie. Istnieją dwa wyniki egzaminów studenta, a całkowity wynik (sumę tych dwóch wyników) definiujemy jako „dobry”, „średni” i „zły”. Wynik byłby „dobry”, jeśli jest większy lub równy 60, „średni”, jeśli mieści się w przedziale od 40 do 60, i „zły”, jeśli jest mniejszy lub równy 40.
Powiedzmy, że pierwszy wynik jest przechowywany w kolumnie B, a drugi w kolumnie C.
Poniższa formuła nakazuje programowi Excel zwracanie wartości „Dobra”, „Średnia” lub „Zła”:
= JEŻELI (D2> = 60, „Dobrze”, JEŻELI (D2> 40, „Średnia”, „Zła”))
Ta formuła zwraca wynik, jak podano poniżej:
Przeciągnij formułę, aby uzyskać wyniki do pozostałych komórek.
Widzimy, że jedna zagnieżdżona funkcja JEŻELI jest wystarczająca w tym przypadku, ponieważ musimy uzyskać tylko 3 wyniki.
Przykład nr 2
Teraz załóżmy, że chcemy przetestować jeszcze jeden warunek w powyższych przykładach: łączny wynik 70 i więcej jest klasyfikowany jako „Doskonały”.
= JEŻELI (D2> = 70, „Doskonale”, JEŻELI (D2> = 60, „Dobrze”, JEŻELI (D2> 40, „Średnia”, „Zła”)))
Ta formuła zwraca wynik, jak podano poniżej:
Doskonale:> = 70
Dobry: od 60 do 69 lat
Średnia: od 41 do 59
Źle: <= 40
Przeciągnij formułę, aby uzyskać wyniki do pozostałych komórek.
W podobny sposób możemy dodać kilka warunków „Jeśli”.
Przykład nr 3
Jeśli chcemy przetestować kilka zestawów różnych warunków, warunki te można wyrazić za pomocą logicznego OR i AND, zagnieżdżając funkcje wewnątrz instrukcji IF, a następnie zagnieżdżając instrukcje IF w siebie.
Na przykład, jeśli mamy dwie kolumny zawierające liczbę celów postawionych przez pracownika w 2 kwartałach: Q1 i Q2 i chcemy obliczyć premię za wyniki pracownika na podstawie wyższej liczby docelowej.
Możemy stworzyć formułę z logiką:
- Jeśli cele Q1 lub Q2 są większe niż 70, pracownik otrzymuje 10% premii,
- jeśli któryś z nich jest większy niż 60, pracownik otrzymuje 7% premii,
- jeśli któryś z nich jest większy niż 50, pracownik otrzymuje 5% premii,
- jeśli którykolwiek z nich jest większy niż 40, to pracownik otrzymuje 3% premii, w przeciwnym razie żadnej premii.
Dlatego najpierw piszemy kilka instrukcji OR, takich jak (B2> = 70, C2> = 70), a następnie zagnieżdżamy je w logicznych testach funkcji JEŻELI w następujący sposób:
= JEŻELI (LUB (B2> = 70, C2> = 70), 10%, JEŻELI (LUB (B2> = 60, C2> = 60), 7%, JEŻELI (LUB (B2> = 50, C2> = 50 ), 5%, JEŻELI (LUB (B2> = 40, C2> = 40), 3%, ””))))
Ta formuła zwraca wynik, jak podano poniżej:
Przeciągnij formułę, aby uzyskać wyniki dla pozostałych komórek.
Przykład 4
Teraz powiedzmy, że chcemy przetestować jeszcze jeden warunek w powyższym przykładzie jako:
- Jeśli cele na kwartał 1 i 2 są większe niż 70, pracownik otrzymuje 10% premii
- jeśli oba są większe niż 60, pracownik otrzymuje 7% premii
- jeśli oba są większe niż 50, pracownik otrzymuje 5% premii
- jeśli oba są większe niż 40, pracownik otrzymuje 3% premii
- W przeciwnym razie bez premii.
Więc najpierw piszemy kilka instrukcji AND, takich jak (B2> = 70, C2> = 70), a następnie zagnieżdżamy je: testy funkcji JEŻELI w następujący sposób:
= JEŻELI (I (B2> = 70, C2> = 70), 10%, JEŻELI (I (B2> = 60, C2> = 60), 7%, JEŻELI (I (B2> = 50, C2> = 50 ), 5%, JEŻELI (I (B2> = 40, C2> = 40), 3%, ””))))
Ta formuła zwraca wynik, jak podano poniżej:
Przeciągnij formułę, aby uzyskać wyniki do pozostałych komórek.
Rzeczy do zapamiętania
- Funkcja wielokrotnego JEŻELI ocenia testy logiczne w kolejności, w jakiej występują w formule, i gdy tylko jeden warunek zostanie uznany za prawdziwy, kolejne warunki nie są testowane.
- Na przykład, jeśli weźmiemy pod uwagę drugi przykład omówiony powyżej, zagnieżdżona formuła JEŻELI w programie Excel ocenia pierwszy test logiczny (D2> = 70) i zwraca „Doskonały”, ponieważ warunek jest prawdziwy w poniższym wzorze:
= JEŻELI (D2> = 70, „Doskonale”, JEŻELI (D2> = 60 „Dobrze”, JEŻELI (D2> 40, „Średnia”, „Zła”))
Teraz, jeśli odwrócimy kolejność funkcji JEŻELI w programie Excel w następujący sposób:
= JEŻELI (D2> 40, „Średnia”, JEŻELI (D2> = 60 ,, „Dobra”, JEŻELI (D2> = 70, „Doskonała”, „Zła”))
W tym przypadku formuła sprawdza pierwszy warunek, a ponieważ 85 jest większe lub równe 70, wynik tego warunku również ma wartość Prawda, więc formuła zwróci wartość „Średnia” zamiast „Doskonała” bez testowania kolejnych warunków.
Właściwa kolejność
Nieprawidłowe zamówienie
Uwaga: Zmiana kolejności funkcji JEŻELI w programie Excel zmieniłaby wynik.
- Oceń logikę formuły - aby zobaczyć ocenę krok po kroku wielu warunków JEŻELI, możemy skorzystać z funkcji „Oceń formułę” w programie Excel na karcie Formuła w grupie Inspekcja formuł. Kliknięcie przycisku „Oceń” spowoduje wyświetlenie wszystkich etapów procesu oceny.
- Na przykład w drugim przykładzie ocena pierwszego testu logicznego zagnieżdżonej formuły JEŻELI będzie wyglądać następująco: D2> = 70; 85> = 70; Prawdziwe; Doskonały.
- Równoważenie nawiasów : Jeśli nawiasy nie są zgodne pod względem liczby i kolejności, formuła wielokrotnego JEŻELI nie zadziała.
- Jeśli mamy więcej niż jeden zestaw nawiasów, pary nawiasów są cieniowane różnymi kolorami, tak aby nawiasy otwierające pasowały do nawiasów zamykających.
- Ponadto po zamknięciu nawiasów pasująca para jest podświetlona.
- Liczby i tekst należy traktować inaczej : w formule wielokrotnej / zagnieżdżonej JEŻELI tekst powinien być zawsze ujęty w podwójne cudzysłowy.
- Wiele JEŻELI może często sprawiać kłopoty : trudno jest zarządzać tak wieloma prawdziwymi i fałszywymi warunkami i nawiasami zamykającymi w jednym oświadczeniu. Zawsze dobrze jest użyć innych narzędzi, takich jak funkcja JEŻELI lub WYSZUKAJ.PIONOWO na wypadek, gdyby wiele IF było trudnych do utrzymania w programie Excel.
