8 sposobów na zmniejszenie rozmiaru pliku Excel (które faktycznie działają)

Wiadomo, że skoroszyty programu Excel stają się boleśnie powolne i ciężkie, ponieważ dodaje się do nich więcej danych / formuł / obrazów.

Czasami łatwo jest pozbyć się dodatkowej flab i kontrolować rozmiar pliku Excela, a czasami tak nie jest.

A ponieważ Excel nie jest narzędziem internetowym, większość z nas pracuje i współpracuje, wysyłając pliki Excela pocztą elektroniczną. A duży plik Excela, który waży 50 MB, może być trudny do udostępnienia współpracownikom/klientom.

W mojej ostatniej pracy używaliśmy klienta pocztowego Lotus Notes, który jest powolny jak ślimak, i wysyłanie ciężkich plików Excela było torturą.

Chociaż nie ma magicznego sposobu na zmniejszenie rozmiaru skoroszytu programu Excel, istnieje kilka technik, których można użyć, aby zmniejszyć niektóre flab.

Uwaga: Wpływ tych technik na zmniejszenie rozmiaru pliku programu Excel zależy od danych. Niektórym może to pomóc w zmniejszeniu rozmiaru o 50%, a dla niektórych może skończyć się zmniejszeniem rozmiaru tylko o 5%.

Wskazówki dotyczące zmniejszania rozmiaru pliku Excel

Poniżej znajdują się techniki, których możesz użyć, aby zmniejszyć rozmiar pliku skoroszytu programu Excel.

Dla każdej wskazówki wykonałem kilka testów i dodałem migawki, aby pokazać wpływ na rozmiar pliku. Otrzymane wyniki będą się różnić w zależności od danych i plików.

Na podstawie skoroszytu możesz skorzystać z jednej lub kilku z tych wskazówek.

Usuń niepotrzebne arkusze/dane

Najlepsze rozwiązania to często te najłatwiejsze.

A ta technika jest bardziej zdrowa i mniej czarodziejska Excela.

Jeśli masz skoroszyt programu Excel zawierający arkusze, które nie są potrzebne (i nie są używane w żadnych formułach), sprawdź, czy możesz się ich pozbyć.

Ma to bezpośredni związek z rozmiarem pliku Excel. Tak więc, gdy tylko usuniesz niektóre arkusze i zapiszesz skoroszyt, natychmiast zobaczysz zmniejszony rozmiar pliku.

Wyniki testu:

Stworzyłem plik Excel z 10 arkuszami roboczymi, w których każdy arkusz ma 2,6 miliona punktów danych (26 kolumn po 100 000 punktów danych w każdej kolumnie).

Wynikowy rozmiar tego skoroszytu to 143 MB (a jego zapisanie i otwarcie zajmuje trochę czasu).

Teraz, gdy usunąłem pięć arkuszy, rozmiar zmniejszył się o połowę (duh).

Jak widać, dane, które masz w arkuszu, mają bezpośrednią korelację z rozmiarem pliku.

Konwertuj na format binarny (XLSB)

Czy wiesz, że samo zapisanie pliku Excela w formacie XLSB może zmniejszyć rozmiar pliku?

Tak, to prawda.

Wystarczy, że zapisując skoroszyt, użyj formatu .xlsb.

Oto kroki, aby to zrobić:

1. Kliknij kartę Plik.
2. Kliknij Zapisz jako.
3. Kliknij Przeglądaj.
4. W oknie dialogowym Zapisz jako zmień typ pliku na Skoroszyt binarny programu Excel (.xlsb)

Wyniki testu

Miałem skoroszyt programu Excel, który zajmował 14,6 MB miejsca.

Po przekonwertowaniu tego samego pliku do formatu XLSB udało mi się zmniejszyć rozmiar do 10,4 MB (redukcja rozmiaru ~28%).

Co to jest XLSB?

Gdy zapisujesz plik Excel (.xlsx lub .xlsm), zostaje on zapisany w formacie XML.

Na przykład, jeśli zmienię rozszerzenie pliku XLSX na ZIP, a następnie go otworzę, otrzymam to.

A jeśli dalej przejdę do folderu „xl”, a następnie przejdę do folderu arkusza roboczego, pokaże mi arkusze robocze w tym skoroszycie (a te arkusze robocze są zapisywane jako dokument XML).

Teraz zróbmy to samo z plikiem XLSB.

Więc najpierw zmieniam rozszerzenie pliku na ZIP, a następnie otwieram go i znajduję sheet1. Oto, co otrzymujesz:

Zauważysz, że zarówno XLSX, jak i XLSB zapisują arkusz roboczy w innym formacie.

XLSX/XLSM zapisuje go jako plik XML, a XLSB zapisuje jako plik binarny (.bin).

I bez zbytniej techniki, pliki XML mają duży rozmiar w porównaniu z plikiem binarnym.

Tak więc po zapisaniu skoroszytu programu Excel w formacie XLSB rozmiar pliku jest zmniejszony. W naszym powyższym przykładzie redukcja rozmiaru wyniosła ~30%.

XLSB kontra XLSX/XLSM

Jeśli chodzi o korzystanie z formatu XLSB, redukcja rozmiaru jest ogromną korzyścią. Ale musisz być trochę ostrożny podczas pracy z formatem XLSB.

Zalety formatu XLSB:

1. Zmniejsza rozmiar pliku Excel. Może to być przydatne, gdy masz duże pliki i chcesz je udostępnić za pośrednictwem poczty e-mail. Możesz szybko zmniejszyć rozmiar, zapisując go w XLSB.
2. Pliki XLSB mają tendencję do otwierania i zapisywania szybciej niż plik XML. W oparciu o ograniczone testy, które wykonałem, pliki XLSB otwierały się i oszczędzały 25%-50% szybciej. Ta różnica jest zauważalna tylko w dużych plikach Excela. Przy mniejszych plikach zarówno XLSB, jak i XLSX są szybkie.

Chociaż XLSB wygląda świetnie, jest kilka powodów, dla których powinieneś trzymać się plików XLSX/XLSM:

1. W przypadku formatu XLSB nie masz możliwości sprawdzenia, czy ma makro, czy nie. To sprawia, że ​​jest to bardziej ryzykowne, ponieważ ktoś może go użyć do wykonania złośliwego kodu.
2. Pliki XLSB są zakodowane w zastrzeżonym formacie pliku bin, podczas gdy XML jest czytelnym formatem pliku typu open source. Oznacza to, że jeśli korzystasz z narzędzi innych firm w programie Excel, lepiej użyć formatu XML.
3. Dodatek Power Query nie może odczytać danych z pliku XLSB. Jeśli więc korzystasz z dodatku Power Query, lepiej mieć dane w formatach XLSX/XLSM.

Zasadniczo, jeśli rozmiar twojego pliku jest mały (mniej niż 5 MB), lepiej trzymać się formatów XLSX/XLSM.

Opierając się na tym, co słyszałem od ludzi i czytałem na wielu forach, wiele osób woli używać XLSB jako formatu pliku, jeśli chodzi o używanie Excela.

Oto dobry artykuł na temat zrozumienia formatu XLSB.

Usuń niepotrzebne formuły

Zbyt wiele formuł zwiększy rozmiar pliku skoroszytu.

Jeśli więc masz zestaw danych, którego potrzebujesz, ale nie potrzebujesz zawartych w nim formuł, lepiej przekonwertować te formuły na wartości.

Oto kroki, aby szybko przekonwertować formuły na wartości w programie Excel:

1. Wybierz cały arkusz/zestaw danych.
2. wciśnij F5 klucz.
3. W oknie dialogowym Przejdź do kliknij „Specjalne”.
4. W oknie dialogowym Przejdź do specjalnego wybierz Formuły.
5. Kliknij OK.

Powyższe kroki wybrałyby wszystkie komórki zawierające formułę.

Teraz wykonaj poniższe czynności, aby przekonwertować te formuły na wartości:

1. Skopiuj zaznaczone komórki (kontrolka + C)
2. Kliknij kartę Strona główna.
3. W grupie Schowek kliknij Wklej.
4. Kliknij ikonę Wklej wartość.

Powyższe kroki przekonwertowałyby wszystkie komórki z formułami na wartości.

Wyniki testu

Stworzyłem plik Excel z 2,7 milionami punktów danych (1048576*26), a następnie dodałem trzy kolumny z formułami (SUM, COUNT i AVERAGE).

Następnie przekonwertowałem te formuły na wartości i zapisałem plik pod inną nazwą.

Poniżej wynik:

Plik bez formuł ma ~8% mniejszy rozmiar (i zauważ, że miałem tylko 3 kolumny formuł).

Jeśli masz dużo formuł, może to zwiększyć rozmiar.

NOTATKA: Niestabilne formuły mogą naprawdę zwiększyć rozmiar pliku. W ramach testu zastąpienie formuły SUM formułą lotną (OFFSET) doprowadziło do skoku wielkości pliku (z 186 MB do 198 MB). Jeśli więc masz nietrwałe formuły, które możesz zastąpić formułami nielotnymi lub możesz je przekonwertować na wartości, pomoże to zmniejszyć rozmiar pliku.

Kompresuj obrazy (przed i po przesłaniu)

Jeśli pracujesz z plikami Excela, które zawierają obrazy, musisz zoptymalizować te obrazy i upewnić się, że rozmiar jest mniejszy.

Problem z obrazami polega na tym, że gdy je kompresujesz, następuje również obniżenie jakości, czego możesz nie chcieć.

Podczas pracy z obrazami najlepszym sposobem na utrzymanie niskiego rozmiaru pliku Excela jest skompresowanie obrazów przed wstawieniem ich do programu Excel.

Dobrym narzędziem online do tego jest TinyPNG (możesz też użyć Photoshopa lub innego podobnego narzędzia).

To bezpłatne narzędzie, które może zmniejszyć rozmiar obrazu nawet o 90% i nadal zapewnia przyzwoitą jakość obrazu.

Poniżej znajduje się zrzut ekranu, na którym udało mi się skompresować obraz o rozmiarze 3,9 MB do 331 KB przy minimalnej utracie jakości.

Po przesłaniu tych skompresowanych obrazów do programu Excel można je dodatkowo skompresować za pomocą wbudowanej opcji w programie Excel.

Oto kroki, aby skompresować obraz w programie Excel:

1. Wybierz obraz, który chcesz skompresować.
2. Kliknij kartę Formatowanie narzędzi obrazu. Jest to karta kontekstowa, która pojawia się dopiero po kliknięciu obrazu.
3. W grupie Dostosuj kliknij opcję „Kompresuj obraz”.
4. W oknie dialogowym Kompresuj obrazy:
   • Usuń zaznaczenie „Zastosuj tylko do tego obrazu”, jeśli chcesz skompresować wszystkie obrazy w skoroszycie.
   • Wybierz E-mail (96 ppi), aby uzyskać maksymalną kompresję.
5. Kliknij OK.
6. Zapisz plik.

Spowoduje to skompresowanie wszystkich obrazów i pomoże zmniejszyć rozmiar pliku skoroszytu programu Excel.

Wyniki testu:

Użyłem opcji kompresji obrazu w programie Excel za pomocą jednego obrazu.

Poniżej wyniki:

Korzystając z opcji kompresji obrazu, zmniejszono rozmiar pliku z 445 KB do 43 KB (~90% zmniejszenie rozmiaru pliku).

Zależałoby to również od obrazu. Obraz, który jest już skompresowany, może nie prowadzić do znacznego zmniejszenia rozmiaru.

Usuń pamięć podręczną Pivot, aby zaoszczędzić miejsce

Jeśli pracujesz z tabelami przestawnymi i zmagasz się z dużymi plikami Excela, wiedza o pamięci podręcznej przestawnej może być przydatna.

Podczas tworzenia tabeli przestawnej przy użyciu zestawu danych program Excel automatycznie tworzy pamięć podręczną przestawną.

Pamięć podręczna Pivot to obiekt, który przechowuje replikę źródła danych.

Chociaż nie możesz go zobaczyć, jest on częścią skoroszytu i jest połączony z tabelą przestawną. Gdy wprowadzasz jakiekolwiek zmiany w tabeli przestawnej, nie używa ona źródła danych, a raczej używa pamięci podręcznej przestawnej.

Chociaż wydaje Ci się, że jesteś bezpośrednio połączony z danymi źródłowymi, w rzeczywistości podczas wprowadzania zmian w tabeli przestawnej uzyskujesz dostęp do pamięci podręcznej przestawnej (a nie do danych źródłowych).

Teraz, ponieważ tworzysz replikę zestawu danych, zajmuje to trochę miejsca.

Tak więc, gdy tylko utworzysz tabelę przestawną, zauważysz, że rozmiar pliku Excela się zwiększa.

W powyższym przykładzie, jak tylko dodam tabelę Pivot, rozmiar pliku zwiększa się dwukrotnie.

Obecnie istnieją dwa sposoby zmniejszenia rozmiaru pliku podczas pracy z tabelami przestawnymi.

1. Zachowaj dane źródłowe i usuń pamięć podręczną Pivot
2. Zachowaj Pivot Cache i usuń dane źródłowe

Przyjrzyjmy się obu tym technikom.

Zachowaj dane źródłowe i usuń pamięć podręczną Pivot

Po zakończeniu pracy z tabelą przestawną i jej zapisaniu możesz zmusić program Excel do zapisania tylko danych źródłowych i wynikowej tabeli przestawnej, a nie pamięci podręcznej przestawnej.

Spowoduje to, że plik Excela będzie lżejszy i będziesz mieć sposób na odtworzenie pamięci podręcznej Pivot, gdy będziesz jej potrzebować następnym razem.

Poniżej znajduje się różnica w rozmiarze podczas zapisywania pliku Excel z pamięcią podręczną Pivot i bez niej.

Oto kroki, aby NIE zapisz pamięć podręczną Pivot:

1. Zaznacz dowolną komórkę w tabeli przestawnej.
2. Kliknij kartę Analiza.
3. Kliknij ikonę „Opcje” tabeli przestawnej.
4. W oknie dialogowym Opcje tabeli przestawnej kliknij kartę Dane.
5. W opcjach danych tabeli przestawnej odznacz „Zapisz dane źródłowe z plikiem”. Ta opcja zapewnia, że ​​pamięć podręczna przestawna nie zostanie zapisana podczas zapisywania skoroszytu (i pomaga w zmniejszeniu rozmiaru pliku programu Excel).
6. Zaznacz opcję „Odśwież dane podczas otwierania pliku”. Ta opcja zapewnia, że ​​zaraz po otwarciu skoroszytu tabela przestawna zostanie odświeżona, a pamięć podręczna przestawna zostanie automatycznie wygenerowana. Jeśli nie zaznaczysz tej opcji, będziesz musiał ręcznie odświeżyć tabelę przestawną, aby wygenerować pamięć podręczną przestawną.
7. Kliknij OK.

Teraz, gdy zapiszesz plik za pomocą tabeli przestawnej, zauważysz zmniejszony rozmiar pliku.

Zachowaj pamięć podręczną Pivot i usuń dane źródłowe

Innym sposobem zmniejszenia rozmiaru pliku programu Excel jest usunięcie danych źródłowych tabeli przestawnej i zachowanie tylko pamięci podręcznej przestawnej.

Działa to tak, jak pamięć podręczna przestawna jest lustrzanym odbiciem danych i nie uzyskujesz dostępu do danych źródłowych podczas wprowadzania zmian w tabeli przestawnej, zamiast tego używasz pamięci podręcznej przestawnej.

Poniżej znajdują się wyniki po zapisaniu pliku Excel bez danych źródłowych.

Aby to zrobić, po prostu usuń dane (przez usunięcie danych lub arkusza zawierającego dane).

Jeśli chcesz odzyskać dane źródłowe, kliknij dwukrotnie komórkę „Grand Total” w tabeli przestawnej, a natychmiast wygeneruje cały zestaw danych w nowym arkuszu.

Skompresuj plik (ZIP IT)

To jest coś, co możesz zrobić poza aplikacją Excel.

Po spakowaniu pliku Excel natychmiast zobaczysz zmniejszenie rozmiaru o około 10-15%.

Następnie możesz udostępnić ten spakowany plik pocztą e-mail, a osoba, która go otrzyma, może rozpakować plik i użyć skoroszytu programu Excel.

Oto kroki, aby spakować plik Excel (dla systemu Windows):

1. Kliknij prawym przyciskiem myszy plik Excel
2. Najedź kursorem na opcję Wyślij do.
3. Kliknij opcję „Folder skompresowany (skompresowany)”.

Spowoduje to utworzenie spakowanego pliku dla wybranego skoroszytu programu Excel.

Zauważysz również zmniejszenie rozmiaru pliku (jak pokazano poniżej).

Wyniki testu

Dzięki tej technice udało mi się zmniejszyć rozmiar pliku skoroszytu programu Excel o 8%.

Ta technika działa lepiej w przypadku plików o rozmiarze 20-50 MB. Wraz ze wzrostem rozmiaru pliku procentowa redukcja nie jest bardzo wysoka.

Usuń formatowanie danych

Formatowanie danych, takie jak stosowanie koloru tła, dodawanie obramowań lub zmiana stylu czcionki, może zwiększyć rozmiar pliku.

Chociaż na podstawie moich testów zauważyłem, że nie ma to dużego wpływu.

Poniżej znajdują się rozmiary dwóch skoroszytów programu Excel, jeden z 2,6 milionami punktów danych (w jednym arkuszu), a drugi z tymi samymi danymi, ale zastosowanym formatowaniem (kolor komórki, obramowanie i kursywa).

Wyniki testu

Widać, że różnica między tymi dwoma plikami nie jest tak duża (chociaż ma to wpływ, gdy masz dużo danych).

Plik z danymi to 14,6 MB, a plik z danymi i formatowaniem to 14,9 MB.

Jeśli więc chcesz zmniejszyć rozmiar pliku skoroszytu programu Excel, możesz poszukać formatowania, którego nie potrzebujesz. Usunięcie go może zaoszczędzić trochę miejsca na dysku.

Oto kroki, aby natychmiast usunąć całe formatowanie, zachowując dane w nienaruszonym stanie:

1. Wybierz cały zestaw danych (z którego chcesz usunąć formatowanie).
2. Kliknij kartę Strona główna.
3. W grupie Edytowanie kliknij „Wyczyść”.
4. Kliknij „Wyczyść formaty”.
5. Zapisz skoroszyt.

Usuń formatowanie warunkowe

Podobnie jak zwykłe formatowanie danych, formatowanie warunkowe również zwiększa rozmiar pliku.

Jednak na podstawie moich testów zauważyłem, że różnica jest mniejsza (chyba że masz wiele reguł zastosowanych do wielu arkuszy z ogromnymi zestawami danych).

Niemniej jednak dobrym pomysłem jest usunięcie formatowania warunkowego z dowolnego zestawu danych, w którym go nie potrzebujesz.

Oprócz rozmiaru jest jeszcze jeden dobry powód, aby usunąć formatowanie warunkowe - SZYBKOŚĆ.

Formatowanie warunkowe jest niestabilne i za każdym razem, gdy nastąpi jakakolwiek zmiana w arkuszu, jest ono ponownie obliczane. Może to prowadzić do wolniejszego skoroszytu.

Wyniki testu

Stworzyłem plik z 27,2 milionami punktów danych i zastosowałem do niego 5 warunkowych reguł formatowania.

Różnica w wielkości wydaje się wynosić tylko kilka KB.

Przeczytaj także: Wskazówki dotyczące szybkiego tworzenia arkuszy kalkulacyjnych Excel

Wskazówki dotyczące zwiększania wydajności programu Excel

Chociaż powyższe metody pomogą zmniejszyć rozmiar pliku programu Excel, oto kilka dodatkowych wskazówek, jak poprawić wydajność skoroszytów programu Excel:

1. Unikaj używania lotnych formuł. Oprócz zwiększania rozmiaru pliku, lotne formuły spowalniają również skoroszyty. Przykłady zmiennych funkcji to RAND, TODAY, OFFSET, INDIRECT itp.
2. Jeśli musisz utworzyć podsumowania dużego zestawu danych, zamiast używać formuł, sprawdź, czy możesz zamiast tego użyć tabeli przestawnej. Tabela przestawna jest szybka i bardzo intuicyjna w porównaniu do formuł.
3. Jeśli wyodrębniasz dane z wielu źródeł lub łączysz dane z wielu plików, użyj dodatku Power Query, aby pobrać i przekształcić dane. Możesz zapisać dane jako połączenie dodatku Power Query zamiast przechowywać je w arkuszu. Możesz łatwo utworzyć tabelę przestawną, korzystając z połączeń utworzonych za pomocą dodatku Power Query.
4. Konwertuj dane tabelaryczne na tabelę programu Excel i używaj tabeli programu Excel w formułach. Sprawia, że ​​skoroszyt działa szybciej, a formuły tworzone przy użyciu odwołań strukturalnych do tabel programu Excel są łatwiejsze w zarządzaniu (ponieważ są bardziej intuicyjne).
5. Unikaj odwoływania się do całego wiersza lub kolumny w formule. Pomoże to w zwiększeniu wydajności Twoich formuł.
6. Jeśli w skoroszycie jest dużo formuł, wyłącz Obliczanie automatyczne i przełącz na obliczanie ręczne. Dzięki temu za każdym razem, gdy nastąpi zmiana w arkuszach, formuły nie zostaną ponownie obliczone. Możesz dokonać tej zmiany, przechodząc do karty Formuła i zmieniając „Opcje obliczeń”.

Mam nadzieję, że metoda i wskazówki zawarte w tym samouczku okażą się przydatne.

Co jeszcze mógłbym dodać do tego samouczka, aby był bardziej użyteczny? Daj mi znać w komentarzach.