Przeliczanie formuł w Excel trwa zbyt długo

Zbyt długie przeliczanie formuł w Excelu wynika zwykle z nadmiaru zmiennych odwołań i formuł tablicowych.

Dlaczego Excel długo przelicza skoroszyt

Opóźnienia pojawiają się wtedy, gdy Excel musi ponownie obliczyć dużą liczbę zależnych komórek po zmianie jednej wartości. Każda formuła tworzy zależność od komórek, zakresów albo wyników innych formuł. Jeżeli skoroszyt zawiera wiele arkuszy, rozbudowane tabele i łańcuchy odwołań, pojedyncza edycja może uruchomić przeliczanie znacznej części pliku. Szczególnie obciążające są formuły odwołujące się do całych kolumn, na przykład A:A lub B:B, gdy rzeczywiste dane zajmują tylko kilka tysięcy wierszy.

Dużo czasu mogą zabierać także formuły tablicowe, funkcje wyszukujące oraz konstrukcje wielokrotnie wykonujące te same obliczenia. Przykładem jest zagnieżdżenie SUMPRODUCT, INDEX, MATCH, XLOOKUP albo FILTER w wielu tysiącach komórek, zwłaszcza gdy każda formuła analizuje szeroki zakres. Na wydajność wpływają również odwołania do innych skoroszytów, połączenia z zewnętrznymi źródłami danych, tabele przestawne oraz funkcje oznaczane jako zmienne, takie jak NOW, TODAY, RAND, RANDBETWEEN i INDIRECT. Funkcje zmienne mogą być wykonywane ponownie przy wielu operacjach, nawet gdy użytkownik nie zmienił bezpośrednio danych używanych przez daną formułę.

Ustawienie trybu obliczeń i obserwacja zależności

Excel może pracować w trybie obliczeń automatycznych, automatycznych z wyjątkiem tabel danych albo ręcznych. Ustawienie znajduje się w karcie Formuły, w grupie Opcje obliczania. Tryb ręczny ogranicza samoczynne przeliczanie podczas edycji dużych arkuszy, ale nie naprawia przyczyny problemu. Po jego włączeniu wyniki mogą pozostać nieaktualne do czasu użycia polecenia Oblicz teraz albo klawisza F9. Skrót Shift+F9 przelicza aktywny arkusz, natomiast Ctrl+Alt+F9 wymusza pełne przeliczenie wszystkich otwartych skoroszytów.

Przed zmianą formuł warto sprawdzić, które komórki powodują największe obciążenie. Polecenia Śledź poprzedniki i Śledź zależności pokazują relacje między komórkami, a Oceń formułę pozwala prześledzić kolejne etapy obliczenia. Należy zwrócić uwagę na odwołania do całych kolumn, powtarzające się zakresy, niepotrzebne formuły w pustych wierszach oraz rozległe zakresy nazwane. Warto również sprawdzić, czy skoroszyt nie zawiera tysięcy formatowanych lub wypełnionych komórek poza właściwym obszarem danych, ponieważ nadmiernie rozbudowany używany zakres może zwiększać rozmiar pliku i czas operacji.

Ograniczenie kosztownych formuł bez zmiany wyników

Najczęściej skutecznym działaniem jest zawężenie zakresów do rzeczywistej liczby rekordów. Zamiast przeszukiwać całą kolumnę, można odwołać się do konkretnej tabeli lub zakresu obejmującego dane. Tabele programu Excel, utworzone poleceniem Formatuj jako tabelę, pozwalają stosować odwołania strukturalne i automatycznie rozszerzać zakres po dopisaniu rekordów. Nie należy jednak bez potrzeby umieszczać rozbudowanych formuł w setkach tysięcy pustych wierszy. Jeżeli kilka komórek oblicza identyczne wartości pośrednie, warto wyliczyć je raz i wykorzystać wynik w kolejnych formułach. W nowszych wersjach Excela funkcja LET może przechowywać wynik pośredni w obrębie jednej formuły, ograniczając powtarzanie tego samego wyrażenia.

Dane importowane z plików lub systemów zewnętrznych można przygotować przed umieszczeniem ich w arkuszu, używając narzędzia Power Query. Filtrowanie kolumn i wierszy na etapie pobierania zmniejsza liczbę danych, które muszą być później analizowane przez formuły. Warto także oddzielić dane źródłowe od raportu, ograniczyć liczbę reguł formatowania warunkowego i usunąć nieużywane połączenia. Formuły tablicowe należy projektować tak, aby przetwarzały tylko potrzebny zakres. Gdy wynik nie musi reagować na każdą zmianę źródła, można skopiować sprawdzone wyniki i wkleić je jako wartości, zachowując wcześniejszą kopię pliku z formułami.

Sprawdzenie komputera i serwisowa ocena skoroszytu

Jeżeli po uporządkowaniu skoroszytu przeliczanie nadal trwa długo, trzeba rozdzielić problem pliku od problemu środowiska pracy. Należy sprawdzić, czy opóźnienie występuje także w nowym, małym skoroszycie, a następnie uruchomić ten sam plik na innym komputerze. Znaczenie może mieć ilość dostępnej pamięci RAM, obciążenie procesora, szybkość dysku oraz działanie dodatków programu Excel. Duży skoroszyt zapisany na wolnym dysku sieciowym lub otwierany przez synchronizowany katalog może reagować wolniej również podczas zapisu i odświeżania danych. Pomocne jest uruchomienie Excela w trybie awaryjnym za pomocą polecenia excel /safe, ponieważ pozwala sprawdzić, czy za opóźnienia odpowiada dodatek lub niestandardowa konfiguracja.

Warszawskie Pogotowie Komputerowe zajmuje się profesjonalną naprawą laptopów i komputerów PC, a w ramach diagnostyki może sprawdzić stan systemu, dysku, pamięci operacyjnej oraz konfigurację programu Excel. Serwisowa ocena obejmuje także analizę konkretnego skoroszytu: zakresów używanych przez formuły, połączeń zewnętrznych, dodatków i ustawień obliczeń. Przed wprowadzaniem zmian należy wykonać kopię pliku, zachować wersję oryginalną i zweryfikować wyniki po każdej większej modyfikacji. Dzięki temu można odróżnić skrócenie czasu obliczeń od przypadkowej zmiany logiki raportu lub utraty części danych.