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