Dlaczego skoroszyt Excel działa wolno?

Skoroszyty używamy do najróżniejszych rzeczy, głównie przygotowywania raportów i analiz, zbierania danych. Często też w nich powstaje wsad do prezentacji albo jakaś dokumentacja. Niekiedy nasz skoroszyt Excel działa bardzo wolno. Może się długo otwierać, ciągle przeliczać albo reagować z opóźnieniem. Przyczyn może być wiele i nie zawsze łatwo je znaleźć. Poniżej przedstawiam najczęstsze przyczyny takiej sytuacji. Przedstawiony zestaw powodów wolnego działania możesz też do przeanalizowania skoroszytów innych niż Excel.

Formuły – najczęstszy powód przez który skoroszyt Excel działa wolno

Jednym z najbardziej oczywistych powodów dlaczego skoroszyt Excel działa wolno, to użyte w pliku formuły. Ich działanie przekłada się głównie na czas przeliczeń. Excel na szczęście przelicza wartości tylko wtedy, gdy jest to konieczne. Ta konieczność to sytuacja, w której Excel wie, że wynik w danej komórce mógł ulec zmianie. Przeliczenia możemy podzielić na jednorazowe oraz „cykliczne” (nazwa własna). Jednorazowe to na przykład te występujące w danych bazowych wykorzystywanych w raporcie. Są to przekształcenia danych i funkcjonowanie słowników oraz wyszukiwanie wartości. Przeliczenia cykliczne to przeliczenia wynikające z normalnego użytkowania pliku. Mamy tu na myśli zmiany filtrów, parametrów, fragmentatorów itp.

Problemy z przeliczeniami jednorazowymi leży głównie po naszej stronie, ponieważ wykonywane są one w trakcie przygotowywania lub odświeżania raportu. Wtedy możemy tracić wiele czasu i nerwów, w skrajnych przypadkach nie jesteśmy w stanie odświeżyć raportu. Z przeliczeniami cyklicznymi męczą się użytkownicy. Skarżą się na powolne działanie pliku i bezużyteczność takiego raportu. Wyeliminowanie tych problemów wymaga od nas dobrej znajomości funkcji, ich działania oraz metod stosowania.

Problemy z formułami i rozwiązania

Zbyt duża ilość formuł

Należy ograniczyć ilość formuł do tych, które faktycznie są potrzebne. Nie ma sensu liczyć rzeczy, które nigdzie nie są wykorzystywane. Nie wprowadzajmy też skomplikowanych przeliczeń w komórkach, w których nie pojawi się wynik. Np. mamy przeciągnięty zakres formuł z dużym zapasem przy danych źródłowych, których jest znacznie mniejsza ilość. Jeśli nie możemy pozbyć się formuł to przynajmniej wprowadźmy warunki, np. że jeżeli jakaś komórka jest pusta to niech wynikiem formuły będzie 0 lub wartość pusta „”, a nie ten sam efekt po skomplikowanych przeliczeniach.

Nieefektywne wykorzystanie formuł

To bardzo częsty błąd nowicjuszy budujących spory raport. Należy wiedzieć kiedy wykorzystania danej formuły jest najefektywniejsze. Np. gdy szukamy pojedynczej wartości to zawsze wykorzystujemy WYSZUKAJ.PIONOWO. Jej prędkość działania wynika z tego, że gdy znajdzie pierwszy wynik to nie szuka kolejnych. Odwrotnie działają takie formuły jak LICZ.JEŻELI lub SUMA.JEŻELI, one zawsze sprawdzają wszystkie komórki w podanym zakresie. Są to formuły, które bardzo często obciążają plik. Innym przykładem może być sam zapis formuły. Pierwszy przykład to zagnieżdżanie tych samych przeliczeń w różnych częściach całego zestawu formuł w danej komórce. Drugi dotyczy sposobu zapisu formuł np. jeżeli chcemy przemnożyć wartość przez 10% to lepiej w formule użyć 0,1 niż 10%. Zapis 10% dla Excela to dodatkowa czynność bo musi sobie to zamienić na prostą liczbę. Należy też pamiętać, że czasami skoroszyt Excel działa wolno przez formuły zaszyte przy sprawdzaniu poprawności danych lub formatowaniu warunkowym. To też zestaw formuł, które muszą być na bieżąco przeliczane.

Niepotrzebne rozdrabnianie przeliczeń

Wiele osób obawia się bardzo złożonych i zagnieżdżonych w sobie formuł co jest zrozumiałe. Rozbijanie skomplikowanych formuł na mniejsze elementy w osobnych komórkach jest czytelniejsze i łatwiejsze do sprawdzenia. Dla Excela sprawa wygląda nieco inaczej. Musi on najpierw określić kolejność przeliczenia komórek, ich powiązania. W przeciwnym razie dawałby błędne wyniki. Następie zaczyna przeliczać w tej kolejności każdą komórkę i dodatkowo musi w niej wyświetlić wynik. Z tych komórek musi wynik pobrać i przekazać do następnych. Powoduje to zarówno wolniejsze przeliczenia, jak i znaczący wzrost rozmiaru pliku ponieważ każda komórka z formułą zajmuje dodatkową pamięć. Dlatego najefektywniejsze jest wprowadzanie całości obliczeń w jedną komórkę.

Złe parametry

Są to głównie niepotrzebnie zaznaczane zbyt duże zakresy albo parametr wyliczany w nieefektywny sposób.

Powielanie formuł

Złą praktyką jest powielanie tych samych przeliczeń. Jeżeli potrzebujemy w innej części arkusza tą samą wartość to lepiej zamiast wyliczać ją ponownie użyć odwołania, do komórki która ją już wcześniej wyliczyła.

Niepotrzebne przekształcenia

Często dane źródłowe wymagają przekształceń. Oczyszczamy i standaryzujemy dane, wyszukujemy wartości ze słowników, tworzymy klucze do tych wyszukiwań. To wszystko nie powinno być robione w raporcie jeśli mamy umieszczoną w nim znaczącą ilość danych. Przekształcenia danych przeprowadzamy korzystając z Power Query lub makr. Przeliczenia oraz powiazania ze słownikami powierzamy Power Pivotowi oraz modelowi danych jaki w nim zbudujemy. Te narzędzia są zaprojektowane pod takie działania, dlatego są o wiele efektywniejsze.

Dalej Twój skoroszyt Excel działa wolno? To nie wszystko!

Mamy już listę problemów związanych z formułami. To od nich trzeba zacząć, jeżeli obliczenia trwają zbyt długo lub zbyt często. Przejdziemy teraz do problemów dotyczących danych i stosowanych formatowań oraz struktury plików, które powodują, że skoroszyt działa zbyt wolno. Często to właśnie one powodują wolne działanie plików Excel.

Ilość i rodzaj danych

Dane w pliku to zazwyczaj główny powód zbyt dużej wielkości pliku, przez co skoroszyt Excel działa wolno. Ilość danych przede wszystkim przekłada się na czas otwierania pliku, ale też mozolne zapisywanie, pobieranie czy wysyłanie. Jeśli chcesz dowiedzieć się jak one wpływają na rozmiar pliku to wystarczy, że zapiszesz plik zawierający tylko te dane. Może się okazać, że odpowiadają one za prawie cały rozmiar pliku.

Zbyt duża ilość danych

Wiele raportów wymaga korzystania z setek tysięcy czy milionów wierszy z danymi. Jeżeli jest to konieczne to należy ustalić czy muszą być one w takiej formie zamieszczone wprost w arkuszu. Jeśli tak bo np. użytkownicy chcą sobie te dane przeglądać i filtrować to być może da się je jakoś skondensować. Np. mamy dane o sprzedaży produktów z dokładnym czasem transakcji. Jeżeli nikt nie będzie analizował sprzedaży z dokładnością do minut to możemy zrobić plik pośredni, który skondensuje dane do godzin czy dni. Gdy danych musimy mieć dużą ilość wtedy najlepiej wykorzystać Power Query. Dane pobierane przez PQ zazwyczaj zajmują o około 90% mniej miejsca niż ich wielkość w źródle.

Niepotrzebne dane

Szczególnie dotyczy to kolumn z danymi oraz zakresu danych. Nad każdą kolumną z danymi powinniśmy się zastanowić czy będzie wykorzystywana przez nas lub użytkowników raportu. Jeśli nie to nie ma sensu jej wrzucać do pliku. Tak samo dane historyczne, jeśli w bieżącym raporcie nie są analizowane stare dane to też nie są potrzebne i możemy je usunąć. Wystarczy że będą w starym raporcie, do którego zainteresowani zawsze mogą zajrzeć.

Zła struktura danych

Jest to przede wszystkim widoczne przy filtrowaniu dużych zbiorów danych. Filtrowanie wymaga dla programu przeanalizowanie zbioru elementów w danej kolumnie i pokazanie zakresów komórek spełniających określone warunku. Im więcej zakresów tym wolniejsze na nich działania. Dla pewności zrób eksperyment. Wypełnij 100 000 wierszy powtarzalnym ciągiem liczb od 1 do 5, następnie zafiltruj jakąś liczbę i spróbuj usunąć widoczne wiersze. Następnie zrób podobną czynność ale przed filtrowaniem posortuj wartości. Sam zobaczysz jak duża jest różnica w czasie usuwania wierszy. Dlatego jeżeli wiesz, że użytkownicy pracują z danymi to posortuj dane w kolumnach według tego jak często są używane.

Złe typy danych

Złe typy danych – każdy typ danych zajmuje pewną z góry ustaloną pamięć. I tak liczba 1 najmniej pamięci zajmie jako wartość typu prawda/fałsz, więcej jako normalna liczba, a najwięcej jako tekst. Dlatego błędem jest przechowywanie np. danych dotyczących dat czy czasu jako tekst.

Problem danych w tabelach przestawnych

Różne zakresy danych dla tabel przestawnych – tabela przestawna tworzy sobie wewnętrznie ukrytą tabelę danych ze źródła jakie jej określono. Jest to dodatkowa tabela, która zajmuje pamięć. Przekonać się o tym możesz, gdy utworzysz tabelę przestawną a następnie wyczyścisz dane z jakich ona korzysta. Dopóki nie odświeżysz tabeli to możesz z niej normalnie korzystać, nadal ma dostęp do danych. Jeżeli mamy dla tabel przestawnych ustawionych takie samo źródło to korzystają z tej samej wewnętrznej tabeli. Jeżeli mają różne to Excel tworzy różne tabele wewnętrzne, które dodają kolejne megabajty. Warto wyrobić sobie nawyk nietworzenia kolejnych tabel od ponownego zaznaczania tych samych danych, ale kopiować istniejące tabele. Jeszcze lepszym rozwiązaniem jest korzystanie z modelu danych w Power Pivocie. Wtedy dane przechowywane są tylko w jednym miejscu i tabele korzystają z całego modelu danych. Przy dużych zbiorach danych i wielu tabelach oszczędza nam to wiele pamięci.

Formatowanie

Na formatowanie zazwyczaj patrzymy tylko pod kątem ich efektu wizualnego. Od strony technicznej formatowanie to zestaw parametrów, które program musi zapisywać dla każdej komórki. Niestandardowe czcionki, kolory, obramowania itd. to zajmowana pamięć na zapisanie tych parametrów.

Niepotrzebne formatowanie

Czyli zmiany podstawowych parametrów formatowania komórek w miejscach, gdzie nie ma danych. Dochodzi do tego, gdy ustawiamy formatowanie dla całych kolumn lub wierszy albo w miejscach, których nie używamy.

Zbyt wiele zmian formatowania

Niektórzy poświęcają wiele czasu na upiększanie swoich plików, a nie zawsze ma to sens. Przy dużych plikach warto przemyśleć czy ustawianie wielu różnych czcionek cokolwiek wnosi do raportu. Tak samo stosowanie kilkunastu kolorów czy rodzajów obramowania. Musimy mieć na uwadze, że każde formatowanie warunkowe to wynik logiczny formuły, która musi określić czy formatowanie zastosować czy nie. Zbyt duża liczba takich formuł powoduje, że Excel działa wolno.

Źle ustawione formatowanie warunkowe

Jeżeli używamy formatowania warunkowego warto sprawdzić czy jest ono ustawione tylko we właściwych zakresach. Często gdy tworzymy raport to kopiujemy jego fragmenty, przeciągamy zakresy, kopiujemy formatowanie. Potem okazuje się, że formatowanie warunkowe nie działa w jednym zakresie tylko w kilkudziesięciu zakresach. Może to nie być widoczne na pierwszy rzut oka, bo dane w tych zakresach po prostu nie będą spełniały warunków formatowania warunkowego.

Struktura pliku

Struktura pliku to też zestaw ustawień jakie program musi zapisywać i obsługiwać.

Używanie dziesiątek arkuszy

Jest to zazwyczaj nieefektywne działanie. Szczególnie jeśli zawierają one te same dane tylko z innymi ustawieniami. Na przykład mamy w osobnych arkuszach wyniki poszczególnych sprzedawców, a budowa arkuszy niczym się nie różni. Lepiej wtedy stworzyć arkusz podsumowujący wyniki poszczególnych osób oraz drugi służący do szczegółowej analizy wybranego sprzedawcy, którego wybierzemy z listy.

Odwołania do innych plików

Jeżeli nie jest to konieczne to lepiej nie utrzymywać zbędnych połączeń do innych plików. Szczególnie problematyczne będzie to dla osób, które mają ustawione automatyczne odświeżanie łączy.

Niewłaściwy format pliku

Krótko mówiąc, najlepiej nie używać starych formatów plików. Nie zawierają one optymalizacyjnych rozwiązań oraz mają wiele ograniczeń.

Ukryte i niewłaściwe elementy przez które również skoroszyt Excel może działać wolno

Ukryte elementy

Sprawdźmy czy nie mamy poukrywanych arkuszy, które nie są wykorzystywane. Możemy też mieć ukryte kolumny czy wiersze, które zawierają zbędne dane lub formaty.

Niewłaściwe elementy

Mogą się pojawić, gdy zwyczajnie kopiujemy dane z Internetu lub plików generowanych przez jakieś programy. Mogą tam się pojawić jakieś ikony, znaczki itp. które niepotrzebnie zajmują pamięć. Lepiej takie dane wklejać jako wartości lub pobierać przez Power Query.

Podsumowanie

Jednym z najprostszych i najszybszych sposobów na zweryfikowanie obciążenia pliku jest użycie skrótu Ctrl+Shift+G. Wyświetlą się nam wtedy statystyki skoroszytu, które pokażą ilość formuł, tabel, komórek z danymi itd. To może pomóc określić przyczynę problemów.

Z przedstawionych powodów warto zrobić sobie prostą checklistę, którą użyjesz zawsze wtedy, gdy Twój skoroszyt Excel działa zbyt wolno. Problemy z wydajnością mogą pojawić się zarówno w Twoich plikach, jak i w plikach od innych osób, na których będziesz musiał(a) pracować. Pokaż w takiej sytuacji, że jesteś optymalizacyjnym ekspertem.