Chociaż w programie Excel funkcja JEŻELI jest niezbędna w podstawowych testach logicznych, złożone warunki często wymagają zagnieżdżonych instrukcji JEŻELI w celu usprawnienia przetwarzania danych. W tym obszernym przewodniku szczegółowo omówimy podstawy zagnieżdżonego JEŻELI, od składni po praktyczne zastosowania, w tym kombinacje zagnieżdżonych JEŻELI z warunkami AND/OR. Ponadto podzielimy się sposobami poprawy czytelności zagnieżdżonych funkcji JEŻELI, a także kilkoma wskazówkami dotyczącymi zagnieżdżonych JEŻELI, a także odkryjemy potężne alternatywy, takie jak WYSZUKAJ.PIONOWO, IFS i inne, dzięki którym złożone operacje logiczne będą łatwiejsze w użyciu i bardziej wydajne.

• Składnia zagnieżdżonego JEŻELI
• Praktyczne przykłady zagnieżdżonych JEŻELI
• Zagnieżdżone, jeśli z warunkiem AND/OR

• Uczynienie zagnieżdżonego IF łatwym do odczytania
• Kolejność zagnieżdżonych funkcji JEŻELI
• Liczby i tekst należy traktować inaczej
• Ograniczenia zagnieżdżonego JEŻELI

• Korzystanie z funkcji WYSZUKAJ.PIONOWO
• Korzystanie z IFS
• Używając WYBIERZ i DOPASUJ

Funkcja JEŻELI w programie Excel a zagnieżdżone instrukcje JEŻELI

Funkcja JEŻELI i zagnieżdżone instrukcje JEŻELI w programie Excel służą podobnym celom, ale różnią się znacznie pod względem złożoności i zastosowania.

Użycie zagnieżdżonego JEŻELI

W tej sekcji przedstawiono podstawowe użycie zagnieżdżonych instrukcji JEŻELI w programie Excel, w tym składnię, praktyczne przykłady i sposób ich używania z warunkami AND lub OR.

Składnia zagnieżdżonego JEŻELI

Zrozumienie składni funkcji jest podstawą jej prawidłowego i efektywnego zastosowania w Excelu. Zacznijmy od składni zagnieżdżonych instrukcji if.

Składnia:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Argumenty:

• Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
• Result1: This is the value returned if Condition1 is TRUE.
• Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
• Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
• Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
  
  In short, this expression can be interpreted as follows:
  Test condition1, if TRUE, return result1, if FALSE,
  test condition2, if TRUE, return result2, if FALSE,
  test condition3, if TRUE, return result3, if FALSE,
  return result4

Pamiętaj, że w zagnieżdżonej strukturze JEŻELI każdy kolejny warunek jest oceniany tylko wtedy, gdy wszystkie poprzednie warunki mają wartość FAŁSZ. To sprawdzanie sekwencyjne ma kluczowe znaczenie dla zrozumienia działania zagnieżdżonych elementów IF.

Praktyczne przykłady zagnieżdżonych JEŻELI

Przyjrzyjmy się teraz zastosowaniu zagnieżdżonej funkcji JEŻELI na dwóch praktycznych przykładach.

Przykład 1: System oceniania

Jak pokazano na zrzucie ekranu poniżej, załóżmy, że masz listę wyników uczniów i chcesz przypisywać oceny na podstawie tych wyników. Aby wykonać to zadanie, możesz użyć zagnieżdżonego JEŻELI.

Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))

Przykład 2: Obliczanie prowizji od sprzedaży

Wyobraź sobie scenariusz, w którym przedstawiciele handlowi otrzymują różne stawki prowizji w zależności od ich osiągnięć sprzedażowych. Jak pokazano na zrzucie ekranu poniżej, chcesz obliczyć prowizję sprzedawcy w oparciu o te różne progi sprzedażowe, a zagnieżdżone instrukcje IF mogą Ci w tym pomóc.

Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

Zagnieżdżone, jeśli z warunkiem AND/OR

W tej sekcji modyfikuję powyższy pierwszy przykład „system oceniania”, aby zademonstrować, jak połączyć zagnieżdżone warunki JEŻELI z warunkiem AND lub OR w programie Excel. W poprawionym przykładzie oceniania wprowadziłem dodatkowy warunek oparty na „Wskaźniku frekwencji”.

Używanie zagnieżdżonego if z warunkiem AND

Jeśli uczeń spełni zarówno kryteria punktacji, jak i frekwencji, otrzyma ocenę podwyższoną. Na przykład, ocena ucznia, który uzyskał co najmniej 60 punktów i frekwencję na poziomie 95% lub więcej, zostanie podniesiona o jeden poziom, na przykład z A do A+, z B do B+ i tak dalej. Jeżeli jednak frekwencja będzie niższa niż 95%, ocena będzie przeprowadzana według pierwotnych kryteriów punktowych. W takich przypadkach musimy użyć zagnieżdżonej instrukcji JEŻELI z warunkiem AND.

Wybierz pustą komórkę (w tym przypadku D2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Używanie zagnieżdżonego if z warunkiem OR

W takim przypadku ocena ucznia zostanie podniesiona o jeden poziom, jeśli jego wynik wyniesie 95 lub więcej lub jeśli jego frekwencja wyniesie 95% lub więcej. Oto, jak możemy to osiągnąć, używając zagnieżdżonych warunków JEŻELI i LUB.

Wybierz pustą komórkę (w tym przypadku D2), wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać wynik. Następnie przeciągnij Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Wskazówki i porady dotyczące zagnieżdżonych JEŻELI

W tej sekcji omówiono cztery przydatne porady i wskazówki dotyczące zagnieżdżonych JEŻELI.

Uczynienie zagnieżdżonego IF łatwym do odczytania

Typowa zagnieżdżona instrukcja JEŻELI może wyglądać zwięźle, ale może być trudna do rozszyfrowania.

W poniższym wzorze trudno jest szybko określić, gdzie kończy się jeden warunek, a zaczyna inny, zwłaszcza gdy wzrasta jego złożoność.

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Rozwiązanie: Dodawanie podziałów wierszy i wcięć

Aby zagnieżdżone JEŻELI były łatwe do odczytania, możesz podzielić formułę na wiele wierszy, przy czym każdy zagnieżdżony JEŻELI będzie w nowym wierszu. W formule po prostu umieść kursor przed JEŻELI i naciśnij klawisze Alt + Enter.

Po rozbiciu powyższej formuły wyświetli się ona następująco:

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

Dzięki temu formatowi jest wyraźniej widoczne, gdzie znajdują się poszczególne warunki i odpowiadające im dane wyjściowe, co zwiększa czytelność formuły.

Kolejność zagnieżdżonych funkcji JEŻELI

Kolejność warunków logicznych w zagnieżdżonej formule JEŻELI jest kluczowa, ponieważ określa, w jaki sposób Excel ocenia te warunki, a tym samym wpływa na końcowy wynik formuły.

Prawidłowa formuła

W przykładzie System ocen używamy poniższej formuły do ​​przypisywania ocen na podstawie wyników.

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

Program Excel ocenia warunki w zagnieżdżonej formule JEŻELI sekwencyjnie, od pierwszego do ostatniego warunku. Ta formuła sprawdza najpierw najwyższy próg wyniku (>= 90 dla „A”), a następnie przechodzi do niższych progów. Zapewnia porównanie wyniku z najwyższą oceną, do której się kwalifikuje. Jeśli pierwszy warunek jest prawdziwy (A2>=90), zwraca „A” i nie ocenia żadnych dalszych warunków.

Nieprawidłowo uporządkowana formuła

Jeśli kolejność warunków zostanie odwrócona, zaczynając od najniższego progu, zwrócone zostaną nieprawidłowe wyniki.

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

W tym błędnym wzorze wynik 95 od razu spełniałby pierwszy warunek B2>=60 i zostałby błędnie przypisany do oceny „D”.

Liczby i tekst należy traktować inaczej

W tej sekcji dowiesz się, jak liczby i tekst są traktowane inaczej w zagnieżdżonych instrukcjach JEŻELI.

Liczby

Liczby służą do porównań arytmetycznych i obliczeń. W zagnieżdżonych instrukcjach JEŻELI można bezpośrednio porównywać liczby za pomocą operatorów takich jak >, <, =, >= i <=.

Tekst

W zagnieżdżonych instrukcjach IF tekst powinien brzmieć: ujęte w podwójny cudzysłów. Zobacz A, B, C, D i F w następującym wzorze:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Ograniczenia zagnieżdżonego JEŻELI

W tej sekcji wymieniono kilka ograniczeń i wad zagnieżdżonych JEŻELI.

Złożoność i czytelność:

Chociaż Excel pozwala na zagnieżdżenie do 64 różnych funkcji JEŻELI, nie jest to wcale wskazane. Im więcej poziomów zagnieżdżenia, tym bardziej złożona staje się formuła. Może to prowadzić do tworzenia formuł trudnych do odczytania, zrozumienia i utrzymania.

Podatne na błędy:

Co więcej, złożone zagnieżdżone instrukcje IF mogą być podatne na błędy i trudne do debugowania lub modyfikacji.

Trudne do rozszerzenia lub skalowania:

Jeśli zmieni się Twoja logika lub będziesz musiał dodać więcej warunków, głęboko zagnieżdżone elementy IF mogą być trudne do modyfikacji lub rozszerzenia.

Zrozumienie tych ograniczeń jest kluczem do efektywnego korzystania z zagnieżdżonych instrukcji JEŻELI w programie Excel. Często łączenie zagnieżdżonych IF z innymi funkcjami lub poszukiwanie alternatywnych podejść może prowadzić do bardziej wydajnych i łatwiejszych w utrzymaniu rozwiązań.

Alternatywy dla zagnieżdżonych JEŻELI

W tej sekcji wymieniono kilka funkcji programu Excel, których można użyć jako alternatywy dla zagnieżdżonych instrukcji JEŻELI.

Korzystanie z funkcji WYSZUKAJ.PIONOWO

Aby wykonać powyższe dwa praktyczne przykłady, możesz użyć funkcji WYSZUKAJ.PIONOWO zamiast zagnieżdżonych instrukcji JEŻELI. Oto jak możesz to zrobić:

Przykład 1: System oceniania z funkcją WYSZUKAJ.PIONOWO

Tutaj pokażę, jak używać WYSZUKAJ.PIONOWO do przypisywania ocen na podstawie wyników.

Krok 1: Utwórz tabelę przeglądową dla ocen

Po pierwsze, musisz utworzyć tabelę przeglądową (w tym przypadku E1:F6) dla zakresu wyników i odpowiadających im ocen. Note: Wyniki w pierwszej kolumnie tabeli należy posortować rosnąco.

Krok 2: Zastosuj funkcję WYSZUKAJ.PIONOWO, aby przypisać oceny

Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę klucz do uzyskania pierwszej klasy. Wybierz tę komórkę z formułą i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę ocen.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Przykład 2: Obliczanie prowizji od sprzedaży za pomocą funkcji WYSZUKAJ.PIONOWO

Możesz także użyć WYSZUKAJ.PIONOWO, aby obliczyć prowizję od sprzedaży w programie Excel. Wykonaj następujące czynności.

Krok 1: Utwórz tabelę przeglądową dla ocen

Po pierwsze, musisz utworzyć tabelę przeglądową sprzedaży i odpowiadającą jej stawkę prowizji, np. E2:F4 w tym przypadku. Note: Sprzedaż w pierwszej kolumnie tabeli należy posortować rosnąco.

Krok 2: Zastosuj funkcję WYSZUKAJ.PIONOWO, aby przypisać oceny

Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij klawisz Enter, aby otrzymać pierwszą prowizję. Wybierz tę komórkę z formułą i przeciągnij jej uchwyt wypełnienia w dół, aby uzyskać pozostałe wyniki.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

Korzystanie z IFS

Połączenia Funkcja IFS upraszcza proces, eliminując potrzebę zagnieżdżania i sprawia, że ​​formuły są łatwiejsze do odczytania i zarządzania nimi. Zwiększa czytelność i usprawnia obsługę wielu kontroli warunkowych. Aby korzystać z funkcji IFS, upewnij się, że używasz programu Excel 2019 lub nowszego albo masz subskrypcję Office 365. Zobaczmy, jak można to zastosować na praktycznych przykładach.

Przykład 1: System oceniania z IFS

Zakładając te same kryteria oceniania co poprzednio, funkcję IFS można zastosować w następujący sposób:

Wybierz pustą komórkę, np. C2, wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać pierwszy wynik. Wybierz tę komórkę wynikową i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")

Przykład 2: Obliczanie prowizji od sprzedaży za pomocą IFS

W scenariuszu naliczania prowizji od sprzedaży funkcję IFS stosuje się w następujący sposób:

Wybierz pustą komórkę, np. C2, wprowadź poniższą formułę i naciśnij Wchodzę aby uzyskać pierwszy wynik. Wybierz tę komórkę wynikową i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)

Używając WYBIERZ i DOPASUJ

Podejście WYBIERZ i DOPASUJ może być bardziej wydajne i łatwiejsze w zarządzaniu w porównaniu z zagnieżdżonymi instrukcjami JEŻELI. Ta metoda upraszcza formułę i sprawia, że ​​aktualizacje lub zmiany są prostsze. Poniżej pokażę, jak używać kombinacji funkcji WYBIERZ i DOPASUJ, aby poradzić sobie z dwoma praktycznymi przykładami w tym artykule.

Przykład 1: System oceniania z WYBIERZ i DOPASUJ

Możesz użyć kombinacji funkcji WYBIERZ i DOPASUJ, aby przypisać oceny na podstawie różnych wyników.

Krok 1: Utwórz tablicę odnośników z wyszukiwanymi wartościami

Najpierw musisz utworzyć zakres komórek zawierający wartości progowe, które będzie przeszukiwać funkcja MATCH, np. w tym przypadku $E$2:$E$6. Note: Aby funkcja DOPASUJ działała poprawnie w przypadku użycia przybliżonego typu dopasowania, liczby w tym zakresie muszą być posortowane rosnąco.

Krok 2: Zastosuj WYBIERZ i DOPASUJ, aby przypisać oceny

Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę klucz do uzyskania pierwszej klasy. Wybierz tę komórkę z formułą i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

Przykład 2: Obliczanie prowizji od sprzedaży za pomocą IFS

Korzystanie z kombinacji WYBIERZ i DOPASUJ do obliczenia prowizji od sprzedaży może być również skuteczne, zwłaszcza gdy stawki prowizji opierają się na określonych progach sprzedaży. Zobaczmy, jak możemy to zrobić.

Krok 1: Utwórz tablicę odnośników z wyszukiwanymi wartościami

Najpierw musisz utworzyć zakres komórek zawierający wartości progowe, które będzie przeszukiwać funkcja MATCH, np. w tym przypadku $E$2:$E$4. Note: Aby funkcja DOPASUJ działała poprawnie w przypadku użycia przybliżonego typu dopasowania, liczby w tym zakresie muszą być posortowane rosnąco.

Krok 2: Zastosuj WYBIERZ i DOPASUJ, aby uzyskać wyniki

Wybierz pustą komórkę (w tym przypadku C2), wprowadź poniższą formułę i naciśnij Wchodzę klucz do uzyskania pierwszej klasy. Wybierz tę komórkę z formułą i przeciągnij ją Wypełnij uchwyt w dół, aby uzyskać resztę wyników.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

Podsumowując, opanowanie zagnieżdżonych instrukcji IF w programie Excel to cenna umiejętność, która zwiększa zdolność radzenia sobie ze złożonymi logicznymi scenariuszami w analizie danych i procesach podejmowania decyzji. Chociaż zagnieżdżone elementy IF są przydatne w przypadku złożonych operacji logicznych, ważne jest, aby pamiętać o ich ograniczeniach. Prostsze alternatywy, takie jak WYSZUKAJ.PIONOWO, IFS i WYBIERZ z funkcją MATCH, mogą w niektórych scenariuszach zapewnić bardziej usprawnione rozwiązania. Uzbrojeni w te spostrzeżenia, możesz teraz bez obaw stosować najodpowiedniejsze techniki programu Excel do zadań związanych z analizą danych, zapewniając przejrzystość, dokładność i wydajność swoich arkuszy kalkulacyjnych. Dla tych, którzy chcą głębiej zagłębić się w możliwości Excela, nasza strona internetowa oferuje mnóstwo samouczków. Tutaj znajdziesz więcej porad i wskazówek dotyczących Excela.

Powiązane artykuły