GoldenLine
Zaloguj się
Damian Kowalski

Damian Kowalski

Temat: Obliczanie skoroszytu podczas uruchamiania

Witam wszystkich.

Mam spory problem podczas mojej codziennej pracy, ponieważ mój excelowy plik ( 37mb , zapisany jako skoroszyt binarny ) od jakiegoś czasu otwiera się znacznie dłużej. Cały czas pracowałem na automatycznym obliczaniu, lecz excel nie liczył wtedy kiedy nie zmieniałem danych, czyli zapisywanie pliku zajmowało kilka sekund, uruchamianie też.
Teraz uruchamia się około 15 minut, bo tyle też trwa przeliczenie go, kiedy podmieniam codziennie dane.
Próbowałem wszystkiego, ale nie ma żadnej poprawy. W dodatku, wszelkie optymalizacje mijają się z celem, ponieważ zmiana z paru sekund na kilkanaście minut, mówi ewidentnie o tym, że coś się "zepsuło" a nie plik spowolnił. Dodam, że plik pobiera dane z zewnątrz, i wcześniej wyglądało to tak, że nie aktualizował danych do momentu uruchomienia tych plików. Teraz podczas uruchamiania głównego pliku pyta czy aktualizować dane , specjalnie wybieram, że nie bo wiem, że potem je zmienie i tym samym rozpocznę obliczanie skoroszytu, a on mimo wszystko oblicza.
Czy ktoś miał podobne przeboje z dużymi plikami, i wie co może być przyczyną takich zachowań ?Ten post został edytowany przez Autora dnia 19.06.19 o godzinie 13:26
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Obliczanie skoroszytu podczas uruchamiania

Zapaskudziłeś plik i tyle. Nie ma tutaj żadnej winy w działaniu pliku. Praca z plikiem to jedno, a wielkość to drugie.
1. Zamień formuły na obliczone wartości danych, które nie musza być ciągle obliczane.
2. Sprawdź aktywny obszar roboczy, jeśli wychodzi poza dane, przenieś je do nowego arkusza, a stary skasuj.
3. Sprawdź jak masz TP to ile źródeł danych je utrzymuje - jeśli masz niepotrzebne cache to wywal je.
4. Sprawdź odwołanie do innych plików oraz formatowanie warunkowe. Potraktuj je tak samo jak formuły, które obliczają się za każdą zmianą jakiejkolwiek komórki twojego skoroszytu.
5. Sprawdź ukryte arkusze, może w nich jest podobna sytuacja.
6. Sprawdź czy nie masz importowanych obrazków, czy grafik, które nie są zoptymalizowane wielkością/wagą.
Link do wypowiedziLink
Damian Kowalski

Damian Kowalski

Temat: Obliczanie skoroszytu podczas uruchamiania

Jeśli by tak było, to plik nie powinien stale spowalniać ? Sytuacja wygląda tak, że to już któryś z kolei plik służący mi do codziennej pracy (nadzór nad produkcją). Pracuje ponad rok i kilka razy w tym czasie budowałem nowy plik, który był lepszą wersją poprzedniego, ten jest już na prawdę zoptymalizowany i nie mogę np. zamienić formuł na wartości, ponieważ dwa razy dziennie podmieniam dane i muszę przeliczyć te formuły ( to mnie nie boli, zajmuje to 15 minut i się z tym liczę ). Tak jak pisałem, problemem jest to, że od jakiegoś tygodnia włączanie pliku wiąże się z przeliczaniem całego skoroszytu, mimo, że te dane były już obliczone poprzedniego dnia. Wcześniej plik włączał się w kilka sekund i dopóki nie podmieniłem mu danych działał ok, nie wymagał obliczania.
Dzięki za rzeczową instrukcję, z 6 punktów mogę wstępnie odrzucić 5 bo albo mnie nie dotyczą albo już je robiłem, zastanawia mnie jedynie punkt trzeci. Czym jest TP ? Mógłbyś rozwinąć co masz na myśli ?
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Obliczanie skoroszytu podczas uruchamiania

TP = Tabela przestawna, która posiada pola obliczeniowe danego zakresu, który jest obliczany pośrednio bazując na cache'u dołączonego do pliku. Klucz w tym aby optymalizować te dane oraz bazować na wspólnych widokach (jeśli zawierają połączenia zewnętrzne).
Link do wypowiedziLink
Damian Kowalski

Damian Kowalski

Temat: Obliczanie skoroszytu podczas uruchamiania

Ok, dzięki za rozwinięcie.
Tabel przestawnych mam mnóstwo, w pliku, który pobiera dane z tego głównego.W tabelach przestawnych nie mam obliczeń, prezentuje wartości w postaci SUM, lecz zakresem danych tych tabel przestawnych są pola obliczeniowe właśnie w moim głównym pliku. Ale tak do działało od zawsze, i nie sprawiało takiego problemu jak teraz.

Myślisz, że to może mieć coś wspólnego z pamięcią podręczną ?
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Obliczanie skoroszytu podczas uruchamiania

Sorki, ale nie kumasz bazy. Nie wiesz jak tabela jest zbudowana, bo skupiasz się tylko na jej widoku w arkuszu. Zbadaj ile masz cache w pliku (nie ma to nic wspólnego z pamięcią podręczną). Poniżej masz kod który w immediate pokaże ci listę zakresów tabel. Jeśli opierają się na tym samym zakresie lub częściowo tym samym , po powinno się uwspólnić ten zakres sprowadzając do minimalnej ilości cache:
sub test()

Dim pc As PivotCache

For Each pc In ActiveWorkbook.PivotCaches

  Debug.Print pc.SourceData

Next 

and sub

Najłatwiej robi się to przez kopię istniejącej tabeli o najszerszym/niezbędnym spektrum danych i budowę od nowa na bazie skasowanej działającej na jej miejscu na podobnym zakresie danych sklejając się z innego zestawu pól. Jeśli twoje TP mają zawsze inne zakresy i nie ma możliwości ich uwspólnić to zastanów się nad ich rozmiarem. Może operowanie na nazwach obszarów, które zmierzą max wierszy dla TP będzie rozwiązaniem. Jeśli używasz połączeń zewnętrznych to agregacja na poziomie SQLa będzie ograniczeniem danych wpływających na ich gromadzenie.

Poza tym, tabele przechowują stare parametry wyboru, które nie muszą być już używane (np po odświeżeniu danych) i należy je przeczyścić opcją TP w nowych wersjach albo kodem, co też ograniczy ilość miejsca w pliku:
sub test2()

Dim pt As PivotTable

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

  For Each pt In ws.PivotTables

    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

  Next pt

Next ws

and sub

Co do wcześniejszych porad, to skoro wiesz lepiej bo wykluczasz takie ewentualności, to rady nie potrzebujesz, a jeżeli pow informacji nie wiedziałeś to zastanów się ponownie nad tą moją listą z punktami dokładniej.
Link do wypowiedziLink
Bogdan Gilarski

Bogdan Gilarski www.excelperfect.pl
Perfect And
Practical

Temat: Obliczanie skoroszytu podczas uruchamiania

Zgadujemy - przetestuj podstawowe lekarstwa, zlokalizujmy potencjalne przyczyny choroby :)
1. Wciśnij F5 - specjalne - obiekty - OK - wciśnij Delete Ctrl+S i uruchom ponownie
2. Na próbę na kopii pliku - usuń wszystkie TP i zobacz efekty.
Bufor (cashe) TP - nie tylko ilość, ale również częstotliwość odświeżeń ma znaczenie i to duże. Ten sam bufor nie kasuje wszystkich poprzednich danych, śmieci zostają, dopisują się nowe i plik rośnie, przeliczanie buforów trwa. Jedyne wyjście to budowanie raportów od nowa.
3. Na kopi, usuń wszystkie formaty warunkowe i reguły poprawności danych i jak powyżej
4. Na kopii, menu - Dane - łącza - usuń wszystkie łącza i jak powyżej.

Jak nic nie zadziałało, to napisz
Jaką masz wersję Excela?
Co to znaczy podmieniam dane - opisz bardzo precyzyjnie, czy kopiuj wklej, czy zaciągasz z innych źródeł, ew. czym?
Czy używasz makr? Są wśród nich procedury zdarzeniowe?
Wciśnij Ctrl + End w kilku najważniejszych arkuszach i podaj adres komórki aktywowanej w ten sposób komórki, czy nie odbiega znacząco od zakresu danych
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj