Wiele IF w programie Excel

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 IF w argumentach „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

Możesz pobrać ten szablon programu Multiple Ifs Excel tutaj - Szablon programu Multiple Ifs Excel

Przykład 1

Jeśli chcemy dowiedzieć się, jak uczeń wypadł na egzaminie. Istnieją dwa wyniki egzaminów studenta, a łączny wynik (suma 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 wynosi od 40 do 60, oraz „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 w tym przypadku wystarczy jedna zagnieżdżona funkcja JEŻELI, ponieważ musimy uzyskać tylko 3 wyniki.

Przykład nr 2

Teraz powiedzmy, ż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ą:

  1. Jeśli cele Q1 lub Q2 są większe niż 70, pracownik otrzymuje 10% premii,
  2. 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,
  1. jeśli któryś z nich jest większy niż 40, 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:

  1. Jeśli cele na pierwszy i drugi kwartał są większe niż 70, pracownik otrzymuje 10% premii
  2. jeśli oba są większe niż 60, pracownik otrzymuje 7% premii
  3. jeśli oba są większe niż 50, pracownik otrzymuje 5% premii
  4. jeśli oba są większe niż 40, pracownik otrzymuje 3% premii
  5. W przeciwnym razie bez premii.

Dlatego 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 (AND (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 pojawiają się one 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 IF, możemy użyć funkcji „Oceń formułę” w programie Excel na karcie Formuła w grupie kontrolnej formuły. 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 IF 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 IF 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 oraz nawiasami zamykającymi w jednym stwierdzeniu. 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.