Excel – zagnieżdżone IF i alternatywy

Dziękujemy za dołączenie do nas! Za kilka chwil otrzymasz wiadomość powitalną.

Spojrzenie na zagnieżdżone instrukcje JEŻELI i alternatywy, które są łatwiejsze do wykonania i zrozumienia później.

Autor: David Goodmanson

W moim ostatnim artykule przyjrzałem się prostemu stwierdzeniu IF w programie Excel oraz ich przydatni kuzyni CountIf i SumIF. Dotknąłem także zagnieżdżonej instrukcji JEŻELI, w której w jednej komórce jest wiele funkcji JEŻELI.

Tym razem przyjrzymy się zagnieżdżonemu JEŻELI bardziej szczegółowo, a zwłaszcza alternatywom, które są łatwiejsze do wykonania i zrozumienia później.

W dalszej części przyjrzymy się kilku alternatywnym sposobom wykorzystania instrukcji IF w pewnym kodzie VBA (Visual Basic for Applications), a także instrukcji Select Case.

Numer oceny w kolumnie B jest zamieniany na opis tekstowy w kolumnie C. W programie Excel można zrobić wiele sposobów W prostym przykładzie, takim jak ten, zagnieżdżony IF jest wystarczający, ale są też inne opcje są łatwiejsze do odczytania przez innych ludzi.

Nikt nigdy nie tworzył długiej grupy zagnieżdżonych IF. Zwykle z czasem skrada się każdemu. Prosty zagnieżdżony IF z 2 lub 3 opcjami może rosnąć wraz z coraz większą liczbą IF do tego stopnia, że jest nieczytelny i podatny na błędy w kodowaniu. Oto „prosty” zagnieżdżony JEŻELI z 40 poziomami:

=IF(A2="A",TRUE,IF(A2="B",TRUE,IF(A2="C",TRUE,IF(A2="D",TRUE,
IF(A2="E",TRUE,IF(A2="F",TRUE,IF(A2="G",TRUE,IF(A2="H",TRUE,
IF(A2="I",TRUE,IF(A2="J",TRUE,IF(A2="K",TRUE,IF(A2="L",TRUE,
IF(A2="M",TRUE,IF(A2="N",TRUE,IF(A2="O",TRUE,IF(A2="P",TRUE,
IF(A2="Q",TRUE,IF(A2="R",TRUE,IF(A2="S",TRUE,IF(A2="T",TRUE,
IF(A2="U",TRUE,IF(A2="V",TRUE,IF(A2="W",TRUE,IF(A2="X",TRUE,
IF(A2="Y",TRUE,IF(A2="Z",TRUE,IF(A2="AA",TRUE,IF(A2="AB",TRUE,
IF(A2="AC",TRUE,IF(A2="AD",TRUE,IF(A2="AE",TRUE,IF(A2="AF",TRUE,
IF(A2="AG",TRUE,IF(A2="AH",TRUE,IF(A2="AI",TRUE,
IF(A2="AJ",TRUE,IF(A2="AK",TRUE,IF(A2="AL",TRUE,IF(A2="AM",TRUE,
IF(A2="AN",TRUE,FALSE))))))))))))))))))))))))))))))))))))))))

W programie Excel 2007 możesz mieć 64 zagnieżdżone IF w jednej formule, ale powodzenia w debugowaniu takiej linii! Zagnieżdżone wartości IF są nieco łatwiejsze do kontrolowania w ostatnich wersjach programu Excel, które mają kodowanie kolorami formuł itp. Pomimo tych pomocników, najlepiej unikać silnie zagnieżdżonych IF.

Jeśli uważasz, że zagnieżdżone opcje mogą zostać rozszerzone później, rozważ jedna z alternatyw od samego początku.

Zagnieżdżone JEŻELI

Po pierwsze, dla funkcji Zagnieżdżone JEŻELI poniżej pokazano wzór dla komórki C3.

=IF(B3=5, "Boom", IF(B3=4, "Recovery", IF(B3=3, "Turning Point",
IF(B3=2, "Recession", IF(B3=1, "Depression")))))

Zagnieżdżony IF działa od lewej do prawej, oceniając test logiczny, np. B3 = 5, jeśli ta ocena jest prawdziwa, to IF wstawia tekst „Boom”. Jeśli B3 nie jest równe 5, to IF sprawdza następny test logiczny (B3 = 4) i ocenia każdy krok po kolei.

  • JEŻELI kontynuuje ocenę każdego testu, aż test logiczny zostanie spełniony, a następnie zamyka funkcję.
  • Znak równości po lewej stronie pierwszego JEŻELI jest wymagany tylko raz dla tego pierwszego JEŻELI, a następnie , dla każdego poziomu zagnieżdżenia po prostu wstawiamy tekst JEŻELI. (Dotyczy to każdej funkcji zagnieżdżonego arkusza).
  • Liczba otwartych nawiasów „(” musi być dopasowana do liczby zamykających nawiasów) „)”. W powyższym przykładzie formuły jest 5 nawiasów otwartych, dopasowanych do 5 nawiasów zamykających na końcu. Najnowsze wersje programu Excel mają lepsze funkcje kolorowania i sprawdzania błędów, aby nawiasy i cudzysłowy były proste, ale nawet tak zagnieżdżone wartości IF można uciążliwe do rozwiązania.

Tabela przeglądowa

Jednym ze sposobów obejścia złożoności zagnieżdżonego IF jest użycie funkcji WYSZUKAJ.PIONOWO połączonej z tabelą odniesień w innym przypadku gdzie w arkuszu.

Nasza tabela wyglądałaby następująco:

Funkcja w kolumnie C oryginalnej tabeli brzmi teraz:

=VLOOKUP(B3,$G$2:$H$6,2,FALSE)

Zwykle tabelę przeglądową umieszcza się na innej karcie tego samego arkusza, ale dane mogą być pobierane z dowolnego miejsca.

VBA – instrukcja IF

Alternatywą jest utworzenie niestandardowej funkcji VBA i umieszczenie całej logiki IF w kodzie VBA.

Ma to tę zaletę, że jest dużo łatwiejszy do odczytania i zrozumienia.

Wadą jest potrzeba zabezpieczenia kodu i obawa, że kod VBA jest legalny i nie jest wirusem. Jeśli Twoi użytkownicy nie są przyzwyczajeni do otrzymywania arkuszy roboczych z dołączonym kodem, możesz chcieć uniknąć opcji VBA.

Oto fragment kodu, jest to ta sama logika, co zagnieżdżony IF na początku tego artykułu, ale ponieważ widać, jest dużo łatwiejszy do odczytania:

" Using a VBA if statement structure
" ==================================
If Rating = 5 Then
Prediction = "Boom"
ElseIf Rating = 4 Then
Prediction = "Recovery"
ElseIf Rating = 3 Then
Prediction = "Turning Point"
ElseIf Rating = 2 Then
Prediction = "Recession"
ElseIf Rating = 1 Then
Prediction = "Depression"
End If

VBA: Select Case

Wreszcie jeszcze łatwiejszą do odczytania strukturą jest instrukcja Select Case.

"Using a Select Case structure
"=============================
Select Case Rating
Case 5
Prediction = "Boom"
Case 4
Prediction = "Recovery"
Case 3
Prediction = "Turning Point"
Case 2
Prediction = "Recession"
Case 1
Prediction = "Depression"
End Select

Instrukcja Select Case to kolejna struktura VBA, która zapewnia sposób oceny zakresu alternatyw przy minimalnym wymaganym powtarzaniu klucza . Jest również prostszy i łatwiejszy do odczytania i powinien być używany, gdy istnieją więcej niż 3 alternatywy dla interesującej nas zmiennej.

VBA: i nie tylko…

Na koniec, aby dać ci pomysł większej mocy Select Case oto rozszerzona wersja Select Case:

Select Case Rating
Case 4.5 To 10
Prediction = "Boom"
Case 3.5 To 4.49
Prediction = "Recovery"
Case 2.5 To 3.49
Prediction = "Turning Point"
Case 1.5 To 2.49
Prediction = "Recession"
Case 0.5 To 1.4
Prediction = "Depression"
Case 0 To 0.49
Prediction = "Money under the Mattress"
Case Else
Prediction = "No prediction - there"s no economy left!"
End Select

Ta wersja sprawdza zakresy wartości (np. ocena od więcej niż lub równa 3,5, ale mniejsza lub równa 4,5) i pozwala komuś wystawić ocenę ułamkową, taką jak 2.5.

Dodatkowo na dole struktury znajduje się kontrolka „Else”. Zasadniczo obejmuje ona ev wszystko, czego nie omawialiśmy w naszych poprzednich Elseifs. Jeśli nic nie jest równe ocenie, wówczas część Else struktury zapewnia alternatywną kontrolę.

Cóż, to wszystko w przypadku serii funkcji JEŻELI. Zachowaj swoje opinie, pytania i sugestie. Do następnego razu.

Zobacz też

  • SUMA programu Excel to znacznie więcej
  • Tworzenie złożonego programu Excel komórka łatwa w użyciu
  • Więcej zagnieżdżonych opcji JEŻELI
  • Opcja WYBIERZ w programie Excel
  • Funkcja IF Excela
Dziękujemy za dołączenie do nas! Za kilka chwil otrzymasz wiadomość powitalną.

Leave a Reply

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *