10 porad jak zmniejszyć rozmiar swojego pliku Excela

W Excelu mieści się ok 1 mln wierszy i ok 16 tys. kolumn. Excel większość z tych komórek traktuje jako nieużywane, czyli nie zapamiętuje w związku z nimi żadnych informacji, dzięki czemu pusty plik zajmuje bardzo mało miejsca.

1. Usuń nieużywane wiersze i kolumny

Wraz z dopisywaniem danych do komórek, rośnie rozmiar pliku – jest to oczywiste. Zdarza się jednak tak, że Excel uważa że używanych komórek jest więcej niż byśmy chcieli – jeśli uważa, że są używane to tym samym zapamiętuje ich zawartość i zwiększa rozmiar pliku.

Dzieje się tak wtedy, gdy w pewnych komórkach zapiszemy jakieś wartości lub wstawimy format. Jeśli później usuniemy z nich zawartość lub pozbędziemy się formatu w zwykły sposób, to Excel wciąż pamięta je jako używane.

Na zdjęciu poniżej widać, że dane naszej tabeli kończą się w komórce D547, jednak kiedyś w komórce E550 znajdowała się jakaś wartość lub format i plik został zapisany. Dopiero potem usunięto tę wartość lub format, ale Excel wciąż pamięta tę komórkę jako używaną.

 

Rysunek 1. Ostatnia używana komórka

Może zdarzyć się nieraz tak, że przypadkiem wypełnimy kolorem całą kolumnę. Potem tego koloru się pozbędziemy, ale Excel wciąż pamięta, że cała kolumna (ciągnącą się przez wszystkie wiersze) jest używana – może to wyraźnie zwiększyć rozmiar pliku.

W jaki sposób sprawdzić, czy mam w pliku takie wiersze/kolumny?

Uzyj skrótu Ctrl + End – przenosi Cie on do ostatniej komórki, którą Excel uważa w tym arkuszu za używaną. Jeśli znacząco wykracza ona poza Twoje dane, to warto usunąć te dodatkowe wiersze/kolumny.

Jak pozbyć się zbędnych wierszy/kolumn?

Usuń całe wiersze/kolumny arkusza, czyli np. aby usunąć wiersze:

  1. Użyj skrótu Ctrl + End – przeniesie Cie na koniec arkusza (na rysunku 1. do komórki E550).
  2. Przesuń się do kolumny w której znajdują się Twoje dane (na rysunku 1 kolumna D).
  3. Mając zaznaczoną taką komórkę (D550) użyj skrótu Ctrl + Shift + Strzałka w górę. Dzięki czemu zostaną zaznaczone wszystkie puste komórki aż do Twojej tabeli. Zaznaczona zostanie też ostatnia komórka Twojej tabeli, a jej nie chcesz usuwać, dlatego naciśnij jeszcze Shift + strzałka w dół. Efekt powinien być taki jak na rysunku 2.
  4. Teraz przejdź na wstążce (w górnym menu) do zakładki Narzędzia główne, przycisk Usuń i wybierz Usuń wiersze arkusza (Rysunek 3.).

Podobnie postępuj ze zbędnymi kolumnami.

Rysunek 2. Zaznaczenie zbędnych pustych wierszy

Rysunek 3. Opcja usuwania wierszy

2. Wyczyść nieużywane komórki

Możesz mieć nieraz w swoim arkuszu wiele nieużywanych komórek, ale wewnątrz swoich danych. Przykładowo, masz tabelę, a w niej 100 wierszy z danymi. Przewidujesz, że w przyszłości tych danych może być do 10.000 wierszy. Jednocześnie chcesz mieć wiersz z podsumowaniem poniżej danych, więc wstawiasz go w wierszu nr 10.001 (rysunek 4). Nie ma więc dodatkowych wierszy/kolumn uznawanych przez Excela niepotrzebnie za używane, bo to Ty stwierdziłeś(aś), że są używane zapisując ten wiersz nr 10.001. Jeśli nic w komórkach pomiędzy wierszem 100, a 10.001 się nie znajduje, w tym również nie ma formatowania, to nie ma problemu. Gorzej jeśli te komórki zostały sformatowane – zostają wtedy uznane za wypełnione i automatycznie zwiększają rozmiar pliku.

Rysunek 4. Formuła na końcu tabeli

 

Zastanów się czy warto przechowywać format w tylu pustych komórkach, czy nie lepiej przypisać ten format, gdy już pojawią się w nich wartości.

Żeby wyczyścić komórki, tak aby mieć pewność, że nie są one uznawane przez program Excel za używane, wyczyść je za pomocą przycisku Wyczyść wszystko. Aby znaleźć ten przycisk przejdź do zakładki Narzędzia główne -> Wyczyść -> Wyczyść wszystko.

Tabele przestawne

Zanim przejdę do opisania konkretnych sytuacji związanych z tabelami przestawnymi, które zwiększają rozmiar pliku, muszę przedstawić Ci w kilku zdaniach jak tworzona jest tabela przestawna.

Wyobraź sobie plik w którym znajduje się jedynie prosta tabela (baza danych), ale o pokaźnych rozmiarach. Jest na tyle duża że cały plik zajmuje 10 MB. Jeśli stworzysz teraz tabelę przestawną z tych danych to plik będzie zajmował ok. 14-15 MB.

Tworząc tabelę przestawną, nawet nic do niej nie wstawiając, zwiększasz rozmiar pliku o 40-50% rozmiaru, który zajmowały dane źródłowe.

Spowodowane jest to tym, że Excel tworząc tabelę przestawną tworzy również tzw. cache, czyli rezerwuje pewne miejsce w pamięci komputera i zapisuje tam kopię danych. Dzięki temu, jeśli usuniesz dane źródłowe, to wciąż widzisz dane w tabeli przestawnej i możesz zmieniać ich konfigurację, dopóki nie odświeżysz tej tabeli przestawnej. Nie skupiajmy się tutaj na tym, dlaczego twórcy Excela tak to rozwiązali – ważne jest to, że Excel tworzy kopię takich danych.

Więcej przykładów optymalizacji?

Sprawdź za darmo calculatic.pl gdzie znajdziesz wiele więcej przykładowych zadań!

SPRAWDŹ ZA DARMO!
 

3. Zaznacz mniej danych dla tabeli przestawnej

Jeśli Twoja tabela źródłowa jest bardzo duża i składa się z wielu kolumn (np. 50), to zastanów się, czy potrzebujesz w tabeli przestawnej używać wszystkich tych kolumn. Może przyda Ci się tylko pierwsze 10 z nich? Jeśli tak, to możesz zmniejszyć rozmiar tabeli przestawnej 5-krotnie.

Policzmy to na przykładzie:

  • Tabela źrodłowa zajmuje 10 MB i składa się z 50 kolumn.
  • Tabela przestawna stworzona z całej tabeli źródłowej będzie zajmować ok. 5 MB.
  • Tabela przestawna stworzona tylko z 10 potrzebnych kolumn będzie zajmować tylko ok. 1MB
  • W rezultacie, zamiast otrzymać plik o wadze 15 MB, otrzymasz plik o rozmiarze 11 MB.

Rysunek 5. Zaznaczenie wszystkich kolumn przy tworzeniu tabeli przestawnej

Rysunek 6. Zaznaczenie części kolumn przy tworzeniu tabeli przestawnej

 

4. Upewnij się że tabele przestawne korzystają z takiej samej tabeli źródłowej

Wyobraź sobie, że masz w pliku Excela nie jedną, a kilka tabel przestawnych – wówczas dla każdej z nich tworzona jest kopia danych? Niekoniecznie.

Jeśli tabele przestawne tworzone są na bazie dokładnie takiej samej tabeli źródłowej, to tworzona jest tylko jedna kopia danych (nawet jeśli tabel przestawnych masz 10). Jeśli jednak tabele przestawne zbudowane są na minimalnie innym źródle, to dla każdej  z nich wykonywana jest osobna kopia danych, co znacząco zwiększa rozmiar pliku.

Jak może dojść do takich sytuacji?

Oto najpopularniejsze przypadki:

  1. Tworzysz kilka tabel przestawnych z jednej tabeli źródłowej, ale za jednym razem zaznaczyłeś(aś) dokładnie tabelę źródłową, ale innym razem tabelę źródłową z kilkoma dodatkowymi, pustymi wierszami (na wypadek gdyby danych przybyło w przyszłości).
  2. Tworzysz kilka tabel przestawnych z jednej tabeli źródłowej, ale w jednej z tabel przestawnych potrzebujesz informacji ze wszystkich kolumn tabeli źródłowej, więc zaznaczasz całą tabelę źródłową. Innym razem potrzebujesz danych tylko z kilku pierwszych kolumn, więc zaznaczasz tylko te kilka kolumn.

Jeśli używasz wielu tabel przestawnych, to sprawdź czy nie dotyczy to również Ciebie. Jeśli tak, to zmień źródła tych tabel na takie samo i sprawdź jak zmniejszył się rozmiar pliku. Aby to zrobić:

  1. Zaznacz dowolną komórkę w tabeli przestawnej – pojawią się wtedy w górnym menu zakładki z opcjami dotyczącymi tabel przestawnych.
  2. Przejdź do zakładki Analiza i wybierz Zmień źródło danych.
  3. W oknie, które się pojawi możesz zobaczyć jakie jest źródło i je ewentualnie zmienić, tak aby było takie samo jak w przypadku innej tabeli przestawnej.

Rysunek 7. Sprawdzenie jakie jest źródło tabeli przestawnej.

Pamiętaj, że jeśli masz wiele tabel przestawnych i korzystają one z zupełnie różnych tabel źródłowych, to powyższe rozwiązanie nie jest możliwe do zastosowania.

5. Ilość danych wyświetlanych w tabeli przestawnej

Jak wiesz z poprzednich paragrafów, samo stworzenie tabeli przestawnej już zwiększa rozmiar pliku (nawet jeśli nie umieścisz w niej danych). Jeśli jednak zaczniesz wstawiać dane do tabeli przestawnej to rozmiar pliku rośnie jeszcze bardziej.

Dzieje się tak dlatego, że po prostu kolejne komórki są wypełniane, to naturalnie zwiększa liczbę przechowywanych informacji w pliku. Dane widoczne w tabeli przestawnej zajmują mniej więcej tyle samo miejsca co dane w komórkach, ale tabela przestawna jest narzędziem, w którym za pomocą kilku krótkich kroków można zacząć wyświetlać bardzo dużo danych.

Zastanów się czy Twoja tabela przestawna nie wyświetla wielu danych (tysiące komórek) i jeśli tak, to czy koniecznie wszystkie te dane musza być widoczne, czy może można zastosować filtry w tabeli, żeby ograniczyć ilość wyświetlanych informacji.

Rysunek 8. Tabela przestawna wyświetlająca bardzo dużo danych

 

6. Wyłącz opcję „Zapisz dane źródłowe z plikiem”

Każda tabela przestawna ma domyślnie włączoną opcję Zapisz dane źródłowe z plikiem. Znajdziesz ją klikając na tabeli przestawnej prawym przyciskiem myszy i wybierając Opcje tabeli przestawnej. Przejdź w oknie do zakładki Dane i tam ją znajdziesz.

Rysunek 9. Po kliknięciu prawym przyciskiem na tabeli przestawnej.

 

Rysunek 10. Zakładka Dane w opcjach tabeli przestawnej

Co da mi wyłączenie tej opcji?

Wówczas w pliku nie będzie zapisywana kopia danych używanych w tabeli przestawnej, czyli nie będzie tych 40-50% rozmiaru tabeli źródłowej.

Więcej przykładów optymalizacji?

Sprawdź za darmo calculatic.pl gdzie znajdziesz wiele więcej przykładowych zadań!

SPRAWDŹ ZA DARMO!
 

Świetna wiadomość! Gdzie jest więc haczyk?

Wyłączenie tej opcji niesie ze sobą pewną małą niedogodność. Za każdym razem gdy otwierasz plik z taką tabelą przestawną, to niemożliwe jest wykonanie na niej żadnych operacji, bo Excel żąda najpierw odświeżenia tej tabeli – z racji tego, że dane źródłowe nie są zapisywane z plikiem, to tabela przestawna niejako ich nie widzi, więc jakiekolwiek operacje, które coś w tej tabeli zmieniają są niemożliwe. Po odświeżeniu, tabela przestawna pobiera sobie na nowo dane i już można normalnie z niej korzystać. Przy zamknięciu pliku dane nie są zapisywane wraz z tabelę przestawną, więc po ponownym otwarciu znów trzeba tę tabelę odświeżyć.

Aby nie wykonywać ręcznie za każdym razem takiego odświeżania, możesz zaznaczyć w zakładce Dane opcję Odswież dane podczas otwierania pliku i cieszyć się w pełni funkcjonalną tabelą przestawną i mniejszym rozmiarem pliku.

To rozwiązanie może nie spodobać się osobom, których tabele przestawne korzystają z takich wielkich tabel źródłowych, że ich odświeżanie za każdym razem trwa wiele sekund i jest uciążliwe. Rzadko jednak zdarzają się takie tabele.

7. Usunięcie danych źródłowych tabeli przestawnej

Jak pamiętasz, domyślnie dane zapamiętane dla tabeli przestawnej zajmują ok. 40%-50% rozmiaru danych źródłowych. Jeśli nie potrzebujesz więcej danych źródłowych, a potrzebne były one jedynie do stworzenia tabeli przestawnej, to możesz je usunąć – zaoszczędzisz wówczas 2 razy więcej miejsca, niż pozbywając się danych zapamiętanych w tabeli przestawnej.

8. Zapisz obraz w innym formacie

Obrazy zapisane w pliku Excela mogą zajmować dużo miejsca – bo po prostu grafika ma to do siebie, że często jest dużych rozmiarów. Spróbuj zapisać ją w innym formacie niż oryginalny:

  1. Skopiuj grafikę (zdjęcie)
  2. Przejdź do wklejania specjalnie
  3. Wybierz wklejanie jako JPEG

Rysunek 11. Opcja wklejania specjalnie

 

Nie zawsze rozmiar grafiki (zdjęcia) się zmniejszy – zależy to od oryginalnego formatu tej grafiki. Jeśli nie przyniesie to skutku, możesz spróbować zapisać ją w innym formacie niż JPEG.

9. Zastąp obraz clipartem

Obrazy mogą zajmować dużo miejsca. Jeśli używasz go tylko do zobrazowania czegoś w swoim pliku, a nie jest to np. firmowe logo, to zastanów się czy takiej „ciężkiej” grafiki nie zastąpić np. clipartem dostępnym w bazie MS Office – znajdziesz wiele clipartów w bazie online, wystarczy wpisać słowo kluczowe.

10. Zapisanie pliku jako binarny

Możesz zapisać swój plik Excela jako plik binarny, czyli z rozszerzeniem .xlsb. Dzięki temu jego rozmiar może uleć zmniejszeniu. Aby to zrobić:

  1. Przejdź do zakładki Plik.
  2. Wybierz Zapisz jako.
  3. W oknie zapisywania wybierz w polu Zapisz jako typ format Skoroszyt binarny programu Excel (*.xlsb).
  4. Potwierdź.

Rysunek 12. Zapisanie pliku Excela w formacie binarnym.

 

Powiązane wpisy poradnikowe