Diagnozujesz powolne arkusze kalkulacyjne Excel?

Skontaktował się ze mną klient, skarżąc się na arkusze kalkulacyjne Excel, których otwarcie trwało zbyt długo. Używają programu Excel do tworzenia faktur, więc mają setki arkuszy kalkulacyjnych Excel z lekkim formatowaniem i bardzo prostymi obliczeniami. Podczas sortowania arkuszy kalkulacyjnych według rozmiaru zauważyłem, że podczas gdy większość arkuszy kalkulacyjnych zawierała się w przedziale 10-250 tys., Istniała garść arkuszy kalkulacyjnych o rozmiarach plików 2-3 MB i więcej. Co dziwne, rozmiary plików nie były ogromne, nie przechowują dużo danych, tylko trochę formatowania, może dwie lub trzy strony drukowanych faktur, ale ich ilość była prawie identyczna z mniejszymi (i normalnie otwierającymi) arkuszami kalkulacyjnymi .

Podczas otwierania pliku potrzebna ilość pamięci RAM zwiększyłaby się z 3 MB do 400 MB i całkowicie zajmowałaby jeden rdzeń (testowany na dwurdzeniowym w biurze i moim czterordzeniowym laptopie) podczas uruchamiania. Myślałem, że w jakiś sposób złapali jakiś kod VBA, ale nie ma makr, nie ma kodu VBA. Ctrl + End pokazuje 39 wierszy i około 12 kolumn (kończy się na M). Nawet usunąłem dane, wiersz po wierszu lub kolumna po kolumnie, dopóki nie pozostały żadne dane, a to wciąż daje mi ten sam problem.

Przejrzałem wiele wyszukiwań w Google, ale nigdzie nie dotarłem. Czy ktoś może zaoferować jakąkolwiek pomoc?

Znalazłem odpowiedź na mój problem!

Korzystając ze wskazówek podanych przez allquixotic, otworzyłem pliki xlsx w 7-Zip i porównałem rozmiary plików. Był jeden plik, który był znacznie większy niż inne. Plik xl \ rysunki \ rysunek1.xml był plikiem zawierającym wiele odniesień do formatów w kółko.

Spędziłem kilka godzin, próbując wymyślić wzór, ale nie byłem w stanie. Nic, co nie byłem w stanie zrobić, nie mogło sprawić, że zadziała! Następnie, po ataku pogorszenia, właśnie usunąłem ten cholerny plik i próbowałem ponownie otworzyć w programie Excel (2010 - nie testowałem go w programie Excel 2007).

Skarżył się, że plik został uszkodzony i zapytał, czy chcę podjąć próbę naprawy. Naprawa pliku po prostu usunęła kształt, ale w ogóle nie zmieniła formatowania pliku. Musiałem ponownie zapisać plik jako ten sam plik, co było trochę dziwne, ale zadziałało!

Jak wspomniałem wcześniej, nie testowałem tego rozwiązania dla żadnych produktów innych niż Excel 2010, więc nie wiem, czy plik kształtu miał krytyczne znaczenie dla programu Excel 2k7 lub jakichkolwiek produktów OpenOffice. Ale jeśli masz podobny problem, mam nadzieję, że może to być pomocne.

Czy używasz binarnego formatu .xls lub nowego formatu .xlsx opartego na języku XML? Ogólnie rzecz biorąc, format .xlsx powoduje znaczne zmniejszenie wielkości plików.

Sprawdź rzeczy takie jak nadmierna liczba stylów przechowywanych w dokumencie.

Spróbuj „usunąć dane osobowe” (funkcja programu Excel / Word / itp.), Aby usunąć niektóre rodzaje cruft, które mogą znajdować się w pliku.

Jeśli arkusz kalkulacyjny jest lub został wcześniej udostępniony, może zawierać w sobie stare dane udostępniania.

Jednym prostym rozwiązaniem jest skopiowanie i wklejenie odpowiednich danych z tego arkusza kalkulacyjnego do nowego, a następnie zapisanie go w formacie .xlsx i sprawdzenie, jak mały jest. Jeśli jest bardzo mały, masz odpowiedź - program Excel źle rozlicza struktury danych w wewnętrznym formacie pliku.

Sprawdź także skomplikowane lub okrągłe formuły i odniesienia do arkuszy zewnętrznych (szczególnie tych na dyskach sieciowych). Jeśli uważasz, że formuła może być wolna, możesz przejść przez nią za pomocą narzędzia kontroli formuł.

Ostatnia rzecz: jeśli zapiszesz go jako .xlsx i nadal jest duży, spróbuj pobrać narzędzie zwiększające wydajność OpenXML SDK: http://www.microsoft.com/en-us/download/details.aspx?id=5124

Otwórz w nim .xlsx i po prostu spójrz na wszystkie elementy w pliku i sprawdź, czy coś jest oczywiście obce. Może to wymagać znajomości XML i konkretnych schematów OpenXML, ale jest to pewny sposób, aby dowiedzieć się, co powoduje wzdęcia.

PS - jeśli takie rzeczy Cię denerwują, przestań używać formatów / programów Microsoft lub sugeruj to swojemu klientowi. Jeśli zaczniesz wyszukiwać „wzdęcia bazy danych Microsoft Access”, zobaczysz, że Microsoft ma długą historię pozwalania swoim zastrzeżonym formatom na wyciek ton niepotrzebnych danych na dysk, który nigdy nie zostanie wyczyszczony. To jak naprawdę paskudny wyciek pamięci, który zjada dysk zamiast pamięci RAM.

Innym możliwym rozwiązaniem byłoby:

1. Utwórz kopię problematycznego pliku Excel
2. Otwórz tę kopię
3. Naciśnij CTRL + A, a następnie kliknij „Wyczyść” -> „Wyczyść formaty”
4. Powtórz krok 3 w każdym arkuszu
5. Zapisz plik i spróbuj go ponownie otworzyć

Gdy formatowanie jest zbyt duże, zwłaszcza jeśli do wielu pojedynczych komórek stosuje się wiele różnych stylów, program Excel naprawdę ma trudności z zastosowaniem formatowania podczas otwierania plików.

Oczywiście po usunięciu całego formatowania powstanie tylko tekst z domyślnym stylem zastosowanym do wszystkich komórek bez obramowań, cieniowania itp. Ale w ten sposób możesz wskazać przyczynę problemu.

Aby radykalnie zmniejszyć rozmiar, możesz wypróbować format .xlsb. Powinno zmniejszyć więcej niż jakikolwiek inny format oparty na XML, który zapewnia Excel.

Otwórz jeden z tych arkuszy kalkulacyjnych i spójrz na format. Szukaj czegoś zwykłego. Spróbuj skopiować całą kolumnę do innego arkusza kalkulacyjnego programu Excel i wklej tylko wartości (bez formatowania). Sprawdź, czy to pomoże.

W moim przypadku problem dotyczył „Formatuj jako tabelę”. Tabela miała 16000 pustych kolumn. Myślę, że jest to jedna z najczęstszych przyczyn tego rodzaju problemów.

Spojrzałem na wszystkie reguły formatowania warunkowego w całym arkuszu i było mnóstwo. Przeszedłem i wyczyściłem je wszystkie, a następnie stworzyłem te, których naprawdę potrzebowałem, a teraz jest naprawdę szybki. Pozbyłem się również wszystkich „połączeń”, których nie potrzebowałem, i zapisz jako .xlsb po tym, jak wszystko działało dobrze, a teraz jest jeszcze szybsze: o)