Refaktoryzacja formuł w Excelu: jak uporządkować arkusz

Refaktoryzacja formuł w Excelu to sprzątanie tego, co już działa, bez psucia niczego po drodze. W arkuszu kalkulacyjnym oznacza to wzięcie formuły, która liczy dobrze, ale wygląda, jakby ktoś pisał ją o 23:00 pod presją deadline'u, i zamienienie jej w coś, co sam otworzysz bez strachu za kwartał.

Biscuit go już dla Ciebie znalazł. Oto, co naprawdę robi różnicę.

Twoja formuła działa, więc czemu coś z nią jest nie tak?

Klasyczny sygnał, że czas na refaktoryzację: otwierasz plik sprzed pół roku i spędzasz trzy minuty nad jedną komórką, zanim zrozumiesz, co ona robi. Albo kopiujesz formułę do nowej kolumny i ta cicho się psuje, bo w środku był zaszyty numer wiersza na sztywno.

Refaktoryzacja nie zmienia wyniku. Zmienia strukturę, żeby wynik zostawał poprawny, gdy zmienia się otoczenie: nowe wiersze, nowe kolumny, przemianowany arkusz.

Pomiń to, jeśli Twój plik to jednorazowe obliczenie, którego nikt więcej nie dotknie. Refaktoryzacja ma swój koszt, czas i zamieszanie, a jeśli korzyść wynosi zero, ROI też.

Warto się tym zająć, jeśli z pliku korzysta kilka osób, aktualizujesz go regularnie albo formuły ciągną się łańcuchem przez kilka arkuszy.

Kilka sygnałów ostrzegawczych, na które warto zareagować:

• Formuła szersza niż kolumna przy powiększeniu 100%

• Więcej niż dwa poziomy zagnieżdżonego IF

• Twarde liczby wpisane w więcej niż trzech miejscach

• Koledzy proszą Cię o wyjaśnienie, co robi komórka, zamiast sami to odczytać

Każdy z tych sygnałów wystarczy, żeby zaplanować sesję refaktoryzacji.

Metoda Wydzielenia: podziel jedną formułę na nazwane kroki

To najbardziej przydatna technika refaktoryzacji, w arkuszach tak samo jak w kodzie. Chodzi o to: jeśli jedna formuła robi trzy rzeczy, dziel ją na trzy komórki, po jednej na krok, a potem połącz wyniki.

Przed (jedna komórka robi wszystko):

=IF(AND(A2<>"",DATEDIF(A2,TODAY(),"Y")>30),"Senior","Junior")

Po:

• Komórka C2: =DATEDIF(A2,TODAY(),"Y"), nazwana "Wiek w latach"

• Komórka D2: =AND(A2<>"",C2>30), nazwana "Czy senior?"

• Komórka E2: =IF(D2,"Senior","Junior"), wynik końcowy

Zobacz, jak to wygląda w prawdziwej komórce. Każdy krok czytasz osobno. Jeśli próg zmieni się z 30 na 25, zmieniasz jedną liczbę w jednej komórce. Jeśli DATEDIF się wywali, bo brakuje daty, widzisz dokładnie gdzie.

Kompromis: zużywasz więcej kolumn. Na wąskim arkuszu to może wyglądać na marnotrawstwo. Użyj obszaru komórek pomocniczych na osobnym arkuszu, jeśli brakuje miejsca. Nazwij go na przykład "Pomocnicze" albo "Obliczenia", żeby każdy od razu wiedział, co tam jest.

Działa tak samo w Excelu i Google Sheets, nie ma tu żadnego ograniczenia wersji.

Zastąp zmienną tymczasową zapytaniem: nie przechowuj tego, co możesz policzyć

Zmienna tymczasowa w arkuszu to komórka z wartością pośrednią, do której nic innego się bezpośrednio nie odwołuje. Potrzebowałeś jej tylko, żeby dojść do następnego kroku.

Czasem to się przydaje, jak wyżej. Ale gdy masz łańcuch pięciu komórek pomocniczych, z których liczy się tylko ostatnia, a pośrednie nigdy się nie zmieniają, możesz je zwinąć w jedną formułę.

Trzy komórki pomocnicze budujące jeden wynik:

• =B2*C2, wartość netto

• =D2*0.23, kwota VAT

• =D2+E2, suma

Zwinięte:

=(B2*C2)+(B2*C2*0.23)

Albo z nazwaną stawką VAT:

=B2*C2*(1+StawkaVAT)

Zasada: jeśli komórka pomocnicza jest używana tylko przez dokładnie jedną inną komórkę, zapytaj, czy ta formuła jest wystarczająco krótka, żeby wstawić ją bezpośrednio. Jeśli tak, wstaw ją. Jeśli nie, zostaw pomocniczą i porządnie ją nazwij.

Gdzie postawić granicę: formuła dłuższa niż 80 znaków zwykle jest za długa, żeby ją wklejać bezpośrednio. Wtedy lepiej sprawdza się Metoda Wydzielenia z poprzedniej sekcji. Zostawiaj pomocnicze komórki, gdy realnie poprawiają czytelność, usuwaj je, gdy są tylko szumem.

Hermetyzacja pól: chroń dane wejściowe, które nie powinny się zmieniać

W arkuszach hermetyzacja oznacza trzymanie stałych danych wejściowych, stawek, progów, wartości referencyjnych, w jednym miejscu zamiast rozrzuconych jako twarde liczby w formułach.

To jest ta formuła, o której wszyscy zapominają, a której wszyscy szukają.

Stawka VAT wpisana na sztywno jako 0.23 w 47 formułach w skoroszycie to pułapka konserwacyjna. Zmieniasz stawkę i musisz znaleźć wszystkie 47. Przegapisz trzy i masz błąd w obliczeniach.

Naprawa:

1. Utwórz arkusz Ustawienia z komórką nazwaną StawkaVAT = 0.23

2. Odwołuj się do niej w każdej formule: =B2*StawkaVAT

3. Gdy stawka się zmieni, aktualizujesz jedną komórkę. Reszta idzie za nią.

To dotyczy też odwołań do dat, dzisiejsza data jako stały punkt odniesienia, mnożników walutowych i progów kategorii. Jeśli wartość pojawia się więcej niż dwa razy i kiedykolwiek może się zmienić, należy do nazwanego zakresu.

Jak tworzyć nazwane zakresy:

• Excel: karta Formuły > Menedżer nazw > Nowa

• Google Sheets: Dane > Nazwane zakresy

Konwencja nazewnicza ma znaczenie. StawkaVAT jest jasne. SV nie jest. tmp_3 nie jest. Używaj nazw, które ktoś, kto nie napisał formuły, zrozumie w trzy sekundy.

Wyeliminuj duplikację: zasada DRY w formułach

DRY, czyli zasada niepowtarzania się to jedna z najczęściej cytowanych zasad w inżynierii oprogramowania i dotyczy też arkuszy wprost. Jeśli skopiowałeś tę samą formułę VLOOKUP do 15 kolumn z drobnymi wariacjami, masz problem konserwacyjny, który dopiero czeka, żeby wybuchnąć.

Dwa podejścia w zależności od sytuacji:

Excel 365 / Google Sheets: użyj XLOOKUP albo INDEX-MATCH z rozwinięciem tablicowym, żeby pobrać kilka kolumn jedną formułą.

=XLOOKUP(A2,TabelaWyszukiwania[ID],TabelaWyszukiwania[[Nazwa]:[Status]])

To zwraca jednocześnie Nazwę i Status, bez kopiowania formuł między kolumnami.

Starszy Excel (2016 lub wcześniej): utwórz jedno wywołanie MATCH w komórce pomocniczej, a potem użyj INDEX odwołującego się do tej pozycji:

• G2: =MATCH(A2,TabelaWyszukiwania[ID],0), jeden MATCH dla wszystkich kolumn

• H2: =INDEX(TabelaWyszukiwania[Nazwa],G2)

• I2: =INDEX(TabelaWyszukiwania[Status],G2)

Nadal odwołujesz się do tego samego wyniku MATCH wszędzie. Jeśli kolumna wyszukiwania się zmieni, aktualizujesz jedną formułę.

Warto zaznaczyć: XLOOKUP pojawił się w Excelu 2019 dla subskrybentów enterprise i w Excel 365. Jeśli Twój zespół korzysta z Excela 2016, lepszym wyborem jest podejście INDEX-MATCH. Sheets obsługuje XLOOKUP od 2022 roku na wszystkich planach.

Zastąp warunek czymś czytelniejszym

Zagnieżdżone IF-y to miejsce, w którym arkusze stają się nieczytelne. Trzy poziomy w głąb i tracisz większość czytelników, w tym siebie za pół roku.

Przed (łańcuch zagnieżdżonych IF):

=IF(A2>10000,"A",IF(A2>5000,"B",IF(A2>1000,"C","D")))

Opcja 1: IFS (Excel 2016+ / Sheets)

=IFS(A2>10000,"A",A2>5000,"B",A2>1000,"C",TRUE,"D")

Płaściej, ale nadal cztery warunki w jednej formule. Do zaakceptowania.

Opcja 2: VLOOKUP z tabelą referencyjną (niedoceniane podejście)

Utwórz małą tabelę referencyjną:

• 10001 i więcej: Kategoria A

• 5001 do 10000: Kategoria B

• 1001 do 5000: Kategoria C

• 0 do 1000: Kategoria D

Potem: =VLOOKUP(A2,TabelaProgow,2,TRUE)

TRUE (dopasowanie przybliżone) schodzi w dół posortowanej tabeli i zwraca ostatnią pasującą kategorię. Logika jest teraz w tabeli, nie zaszyta w formule. Zmieniasz próg? Edytujesz jeden wiersz w tabeli.

Warto zainwestować czas w podejście z tabelą referencyjną, bo czyni logikę możliwą do sprawdzenia i widoczną dla każdego, nawet dla osób, które nie czytają formuł. Pomiń to, jeśli się spieszysz: IFS wystarczy przy trzech warunkach lub mniej.

Funkcja SWITCH to kolejna opcja dostępna w Excelu 2019+ i Sheets, przydatna, gdy dopasowujesz dokładne wartości, a nie zakresy. Czyta się ją czyściej niż IFS przy dopasowaniach dokładnych.

Algorytm Zamiany: kiedy lepiej przepisać niż naprawiać

Czasem formuła nie jest zła, to po prostu złe podejście od samego początku. Odziedziczyłeś 200-znakową formułę sklejającą tekst przez CONCATENATE i &, i musisz dodać jeszcze jedno pole. Dobrym ruchem nie jest jej rozciąganie. Jest nim przepisanie jej z użyciem TEXTJOIN.

Przed:

=A2&" | "&B2&" | "&C2&" | "&D2

Po:

=TEXTJOIN(" | ",TRUE,A2:D2)

Jeśli Twój arkusz zmieni się jutro nie do poznania, ta formuła za nim nadąży. TEXTJOIN radzi sobie z pustymi komórkami, obsługuje tablice i nie wywali się, gdy dodasz kolumnę E.

Ta sama logika dotyczy:

• Zamiany VLOOKUP na XLOOKUP, gdy potrzebujesz dokładnego dopasowania i obsługi brakujących wartości

• Zamiany opakowania IFERROR na natywną obsługę błędów w nowszych funkcjach

• Zamiany formuł wpisywanych tablicowo (Ctrl+Shift+Enter) na natywne tablice dynamiczne w Excelu 365

Ta sama zasada działa w większej skali: jeśli utrzymujesz duży potok danych w arkuszu, który zamienił się w plątaninę odwołań INDIRECT, połączeń zewnętrznych i nazwanych zakresów wskazujących na inne nazwane zakresy, czasem właściwym ruchem refaktoryzacyjnym jest wyciągnięcie logiki do właściwego narzędzia, SQL, Python, Power Query, i użycie arkusza wyłącznie do wyświetlania danych. To większa decyzja, ale należy do tej samej kategorii.

Zanim zaczniesz: trzy rzeczy, które musisz mieć przygotowane

Refaktoryzacja formuł w Excelu działa tylko wtedy, gdy nie zepsujesz czegoś, czego nie da się odzyskać. W arkuszu oznacza to:

1. Kopię zapasową. Przed każdą sesją refaktoryzacji zapisz datowaną kopię skoroszytu. Nie "Zapisz jako" w tym samym folderze, tylko osobną lokalizację, backup w chmurze, mail do siebie, cokolwiek. Podziękujesz sobie za tydzień, gdy coś pójdzie nie tak.

2. Wiedzę, jakie powinny być obecne wyniki. Zanim cokolwiek zmienisz, zanotuj kluczowe wyniki: sumy, podsumowania, wartości ważnych komórek. Porównuj je z tymi samymi komórkami po każdej zmianie. Dziesięciosekundowa kontrola, która od razu wyłapuje regresje.

3. Jedną zmianę na raz. Nie rób Metody Wydzielenia, hermetyzacji trzech nazwanych zakresów i spłaszczenia zagnieżdżonego IF w tej samej sesji. Jeśli coś się popsuje, musisz wiedzieć, która zmiana to spowodowała. Zrób jedną rzecz, sprawdź, dopiero potem przejdź dalej.

Te trzy kroki zajmują około pięciu minut przygotowania. Oszczędziły znacznie więcej niż pięć minut przy każdej sesji refaktoryzacji, w której coś poszło nie tak, jak powinno.

Kiedy refaktoryzacja nie ma końca (i jak to zatrzymać)

Ryzykiem przy refaktoryzacji jest perfekcjonizm. Możesz spędzić całe popołudnie na reorganizacji skoroszytu, który już działał, i skończyć z czymś nieznacznie czystszym kosztem pół dnia.

Ustaw limit czasu, zanim zaczniesz: 45 minut na to zadanie. Priorytetyzuj formuły, które faktycznie sprawiają problemy, te, które ludzie źle odczytują, te, które psują się przy normalnym użyciu, te blokujące nową funkcję, którą musisz dodać.

Cała reszta: zostaw. Formuła, która działa, której nikt nie rusza, która nie spowodowała błędu od roku, jest w porządku. Wystarczająco dobre to legalny punkt zatrzymania.

Przydatne spojrzenie: refaktoryzacja nie polega na dopracowaniu skoroszytu do perfekcji. Polega na tym, żeby następna zmiana była łatwiejsza. Zapytaj siebie przed każdą zmianą: czy to sprawi, że następna rzecz, którą muszę zrobić, będzie szybsza albo bezpieczniejsza? Jeśli tak, zrób to. Jeśli nie, zatrzymaj się.

Dobrze zrobiona refaktoryzacja jest cicha. Nikt jej nie zauważa. Good boy, o to właśnie chodzi.