Przestań pisać zagnieżdżone funkcje IF i IFS w Excelu: użyj zamiast tego SWITCH

Nieuporządkowane formuły w Excelu to nie tylko problem estetyczny — są też trudniejsze w utrzymaniu. Każde powtarzające się odwołanie do komórki i splątane nawiasy utrudniają audyt, edycję i udostępnianie pracy. Dlatego warto używać funkcji SWITCH. Zamienia ona bałagan w formułę, która jest bardziej przejrzysta, szybsza i łatwiejsza do zrozumienia.

Zagnieżdżona funkcja IF: strukturalny koszmar

Zagnieżdżone formuły IF są jak rosyjskie matrioszki w Microsoft Excel. Zaczynasz od prostego wyrażenia IF, ale potem chcesz dodać drugi warunek. Potem trzeci. Nagle jesteś kilka poziomów w głąb - zjawisko to nazywane jest zagnieżdżaniem.

Załóżmy, że używasz tej tabeli Excela do śledzenia postępów projektu.

Formuła, którą wpisujesz, aby określić status na podstawie ukończonych faz, wygląda tak:

=IF([@Completed]=0,"Pending",IF([@Completed]=1,"Active",IF([@Completed]=2,"Active",IF([@Completed]=3,"Closed","Error"))))

Jest to problematyczne z dwóch głównych powodów:

• Znużenie nawiasami: Za każdym razem, gdy otwierasz IF, musisz go zamknąć na samym końcu. W skomplikowanych arkuszach często zdarza się, że wpatrujesz się w ciąg )))), zastanawiając się, czy nie pominąłeś któregoś.
• Złożoność wizualna: Aby zrozumieć, co się dzieje, gdy kolumna Zakończono pokazuje 3, Twój mózg musi najpierw przejść przez dwa inne warunki IF.

Kiedy używasz zagnieżdżonych funkcji IF, budujesz kruchą wieżę logiki. Jeden niewłaściwy przecinek i cała konstrukcja się zawala.

Funkcja IFS: koszmar nadmiarowości

Kiedy Microsoft wprowadził funkcję IFS w Excelu, jej celem było rozwiązanie problemu z nawiasami, o którym wspominałem powyżej. Funkcja skutecznie uprościła strukturę, pozwalając na wypisanie warunków jeden po drugim bez ich zagnieżdżania.

Używając tej funkcji w tej samej tabeli, wpiszesz:

=IFS([@Completed]=0,"Pending",[@Completed]=1,"Active",[@Completed]=2,"Active",[@Completed]=3,"Closed")

Chociaż IFS jest ulepszeniem w porównaniu do zagnieżdżeń, wprowadza nowe problemy:

• Nadmierność: Zauważ, ile razy musisz wpisać (lub kliknąć) nazwę odwoływanej kolumny w formule, po jednym razie dla każdego testu logicznego. Tworzy to formuły pełne nadmiaru, które są dwa razy dłuższe niż potrzebne i trudniejsze w utrzymaniu.
• Brak domyślnego argumentu „else”: W odróżnieniu od oryginalnej funkcji IF, funkcja IFS nie ma końcowego argumentu „jeśli wszystko inne zawiedzie”. W efekcie, jeśli twoje dane nie pasują do żadnego z kryteriów, formuła zwraca błąd #N/A. Aby to naprawić, możesz dodać dodatkowe wyrażenie TRUE na końcu, ale spowoduje to jedynie zwiększenie złożoności formuły.

Krótko mówiąc, IFS rozwiązuje problem nawiasów, ale nie rozwiązuje bałaganu.

Rozwiązanie: SWITCH

Funkcja SWITCH pozwala zdefiniować swoje reguły w jednej przejrzystej liście, dzięki czemu formuła staje się mniej skomplikowanym obliczeniem, a bardziej prostym tłumaczeniem.

Składnia SWITCH

Funkcja SWITCH w Excelu ma logiczną składnię:

=SWITCH(target,value1,result1,[value2,result2],...,[default])

gdzie :

• target to wartość lub odwołanie do komórki, które mają być porównane z value1, value2, value3 i tak dalej.
• value1 jest pierwszą wartością, która zostanie porównana z celem.
• result1 jest wartością, która zostanie zwrócona, gdy value1 będzie pasować do target.
• value2,result2 to druga para wartość-wynik spośród maksymalnie 126.
• default jest pojedynczym argumentem po ostatecznym dopasowaniu wartości i wyniku, który reprezentuje wartość „else” (siatkę bezpieczeństwa) do zwrócenia, jeśli w argumentach value nie zostaną znalezione żadne dopasowania.

SWITCH w akcji

Oto formuła, której możesz użyć w kolumnie Status w swoim narzędziu śledzenia postępów:

=SWITCH([@Completed],0,"Pending",1,"Active",2,"Active",3,"Closed","Unknown")

Oto dlaczego jest to lepsza opcja w tym kontekście:

• Wskazujesz tylko raz: Odwołujesz się do kolumny "Zakończone" tylko raz. W dużym arkuszu kalkulacyjnym zmniejszenie liczby odwołań do jednej sprawia, że Twoja formuła jest znacznie bardziej uporządkowana, trudniejsza do zepsucia i łatwiejsza do zmiany.
• Łatwa czytelność: Ponieważ nie ma powtarzających się odwołań, możesz czytać to w prostym języku angielskim: „Spójrz na Kolumnę Zakończono; jeśli jest 0, Oczekujące; jeśli jest 1, Aktywne...” i tak dalej.
• Wbudowana siatka bezpieczeństwa: Jeśli ktoś wpisze numer w kolumnie Zakończone, który tam nie powinien się znaleźć, SWITCH wychwytuje błąd za pomocą argumentu „else” na końcu („Nieznany”).

Korzystając z funkcji SWITCH, przestajesz zmagać się ze skomplikowaną składnią i zaczynasz pisać formuły, które faktycznie mają sens.

Sztuczka SWITCH(TRUE): Ruch dla zaawansowanych użytkowników

Częstą krytyką funkcji SWITCH w Excelu jest to, że obsługuje ona tylko dokładne dopasowania, na przykład zamieniając liczbę 1 na słowo „Aktywny”. Jeśli chcesz używać symboli większy niż lub mniejszy niż, większość osób zakłada, że muszą wrócić do skomplikowanej logiki funkcji IF lub IFS.

Możesz jednak faktycznie używać SWITCH dla zakresów, stosując sprytny trik: rozpocznij formułę od TRUE:

=SWITCH(TRUE,[@Completed]=0,"Pending",[@Completed]<=2,"Active",[@Completed]=3,"Closed","Error")

Ustawiając pierwszy argument na TRUE, mówisz Excelowi, aby kontynuował przeszukiwanie formuły, dopóki nie znajdzie stwierdzenia prawdziwego. Oznacza to, że możesz uwzględnić zarówno 1, jak i 2 jednocześnie, zwracając "Aktywny" w jednym argumencie.

Zapisz się do newslettera, aby otrzymywać czystsze formuły w Excelu

Zapisz się do newslettera, aby otrzymywać czystsze formuły w Excelu

Subskrybując, wyrażasz zgodę na otrzymywanie newslettera i wiadomości marketingowych oraz akceptujesz Warunki korzystania z serwisu i Politykę prywatności Valnet. Możesz zrezygnować w każdej chwili.

Innym powodem, dla którego jest to przełomowe, jest to, że zyskujesz matematyczną elastyczność instrukcji IF, zachowując jednocześnie czystą, liniową strukturę listy SWITCH.

Kompromis „raz punkt”

Oto zastrzeżenie: używanie sztuczki TRUE nieco podważa zasadę „punkt raz”, o której mówiłem wcześniej. Ponieważ używasz operatorów matematycznych (< lub >), musisz powtórzyć odwołanie dla każdego testu logicznego, tak jak robiłbyś to w formule IFS.

Jednak w tym przypadku można nagiąć tę zasadę, ponieważ wymieniasz minimalne powtarzanie na maksymalną organizację. Nawet przy powtarzających się odwołaniach struktura SWITCH utrzymuje twoją logikę w ścisłej, liniowej liście. Oznacza to, że nie musisz szukać zamykających nawiasów ani zastanawiać się, w którym zagnieżdżeniu aktualnie się znajdujesz.

Również nadal korzystasz z tego ostatniego argumentu „else” na końcu — zabezpieczenia, którego IFS nie oferuje.

Wybór odpowiedniej funkcji

Chociaż funkcja SWITCH jest świetna do porządkowania logiki, nie jest ona magiczną różdżką na każdą sytuację. Są momenty, kiedy zagnieżdżone formuły lub złożone instrukcje IFS są w rzeczywistości bardziej logicznym wyborem:

Jeśli zauważysz, że w jednej formule używasz tego samego skomplikowanego obliczenia wielokrotnie, użyj funkcji LET, która pozwala przypisywać nazwy wynikom obliczeń. Deklarując zmienne na początku formuły, możesz sprawić, że Twoje formuły będą jeszcze bardziej przejrzyste, szybsze i łatwiejsze do sprawdzenia.