VBATools

Mój Excela zwolnił, a skoroszyty puchą

by on Sep.10, 2018, under Excel, Porady

Co zrobić gdy nasz magazyn danych z biegiem czasu rośnie do tego stopnia, że nie da się z nim pracować. Jakie są przyczyny takiego stanu i jak zapobiec takim przypadkom? Wiem że ilość danych nie jest zbyt wielka i jeden plik który zawiera więcej danych ma 16 mega, a inny 80mb i chodzi jak ślimak.

Do rozwiązania mamy dwa zagadnienia i nie są one często spowodowane łatkami producenta, czy słabymi parametrami jednostki.

Przedstawmy w takim razie rozwiązanie w dwóch punktach:

 

  • Prędkość działania:

Główną zaletą działania arkuszy kalkulacyjnych jest możliwość wprowadzenia formuł, które w odwołaniu do innych komórek lub zakresów przedstawiają odpowiednie dla naszych potrzeb obliczenia. Formuły, które najbardziej obciążają nasz skoroszyt to formuły tablicowe lub wyszukania. Same och obliczenie jest dość skomplikowane, a powielenie tego obliczenia x 1000/x 10tyś (co w przypadku przeciągania ich w dół arkusza jest dość proste), powoduje iż procesor naszej maszyny musi zrealizować takie obliczenie za każdym razem edycji jakiejkolwiek komórki. Tak jakiejkolwiek, ponieważ ona może wpłynąć na wynik takiej formuły. Spowolnienie to również problem odwołania się do zasobów innego skoroszytu, nierzadko połączona z formułą bazującą na zakresie tego obcego zakresu. Ponadto formuły można odszukać w formatowaniu warunkowym, ponieważ formatowanie to, to takie samo obliczenie wyrażone kolorem, co zwrot wyniku liczby. Operacyjnie gdy procesor skończy obliczać jeden wynik, przechodzi do następnego również wtedy gdy mamy wielowątkowość. Często zdarzyć się może na słabszych jednostkach iż nasz Excel się zawiesza i nie chce się odwiesić. Dzieje się to dlatego że wynik ostatniej komórki jaka ma zostać obliczona jest przerywany przez rekalkulacje poprzednich obliczeń – co nigdy nie prowadzi do ich końca. Jedyną możliwą w tedy reakcją użytkownika jest ubicie procesu Excel. Dlatego należy oszacować, czy utrzymanie takiego obliczenia jest dla nas przydatne, czy przypadkiem nie chodzi nam o sam wynik? niezmienny w czasie, raz obliczony.

Jeśli tak, to co stoi na przeszkodzie aby w arkuszach, które odnoszą się do poprzednich okresów, zmienić formułę na wynik tej formuły, otrzymując go zamiast “żywego” obliczenia. Zaznaczamy więc kolumnę, obszar lub kilka obszarów [Ctrl+C], prawy klawisz myszy [PKM] i wklej na wartości lub jak pokazuje na rys obok i czekamy na ponowne obliczenie i zwrot jedynie wartości. Dla plików o dość dużej strukturze lub w przypadku potrzeby wysyłki danych o małej pojemności (np jako załącznik poczty) polecam takie rozwiązanie: Zmień arkusze na wartości.

 

  • Pojemność pliku:

Pojemność pliku zależna jest od danych jakie przechowuje skoroszyt. Jest to ściśle zależne od importowanych doń informacji. Mogą to być zdjęcia/grafika, tabele przestawne zawierające cache danych źródłowych, a także prozaiczna edycja komórek z poza zakresu naszych danych operacyjnych. Tym ostatnim się zajmijmy, ponieważ to często przypadek bezwiednie zachodzący, wynikający bezpośrednio z błędu użytkownika który ciężko wprost zdefiniować. Wystarczy bowiem że użytkownik przez nieuwagę naciśnie klawisz end i spowoduje edycję komórki, którą może również potem usunąć, excel zapamięta tą lokalizację jako roboczą, powiekszając tym samym polecenie archiwizowania danych, co prowadzi do zwiększenia objętości. Oczywiście komórki te na przekroju tego zakresu nie zawierają żadnych danych, ale plik XML w którym sapis jest prowadzony, a z kolei który zawarty jest w kontenerze pliku xlsx, zaczyna zapisywać stan zerowy dla każdej z komórek (zamiast je opuścić w zapisie). Jeśli dodamy do tego ilość arkuszy, które mogą być jego kopią, robi się z tego bardzo niewygodny kolos. To iż danych nie ma, to użytkownik nie wie jak usunąć coś czego nie ma, a kasowanie kolumn lub wierszy z takiego zakresu nie przynosi żadnych efektów. Rozwiązaniem zatem jest utworzenie nowego, czystego arkusza i skopiowanie doń tylko aktywnego obszaru danych (ściśle zaznaczonego), dopasowanie szer kolumn itp., a następnie usunięcie źródłowego arkusza z takim rozszerzonym obszarem. Sprawdzenie czy operacja taka jest zasadna, to kombinacja klawiszy [Shift+Ctrl+End]. Jeśli zaznaczona komórka jest daleko odległa, to znaczy że aby odchudzić plik, zabieg taki musimy wykonać. Wykorzystaniem pomysłu realizującego automatycznie ten cel jest dodatek Kopiuj kolumny czy Kopia zakresów, który oblicza zakres dokładnie taki, jak jego długość, co znacznie przyśpiesza pracę migracji, ale oczywiście można to wykonać również na “piechotę” wg instrukcji opisanej pow. Po wykonaniu takiej operacji i powtórnym zapisie skoroszyt powinien być znacznie mniejszy.

 

(c) Shon Oskar – www.VBATools.pl

Wszelkie prawa zastrzeżone. Żadna część ani całość tego artykułu nie może być powielana ani publikowana bez zgody autora.
:, , , , , ,

2 Comments for this entry

  • Rafał B.

    Brakuje moim zdaniem najważniejszej porady w tytułowym problemie: jesli to mozliwe, to zmiana rozszerzenia pliku na plik binarny np. xlsm na xlsb. Pozdrawiam uprzejmie!

    • vbatools

      Musze to sprawdzić, bo faktycznie część moich plików posiada takie rozszerzenie, ale przebudowa XMLa w którym jest zapis o komórkach zachodzi też po zapisie domyślnego pliku…

Leave a Reply