Praca z dużymi zbiorami danych w MS Excel

INFO_18_12.jpg

Podczas wdrażania raportów czy dashboardów opartych na rozwiązaniach oferowanych przez MS Excel, nierzadko można usłyszeć pytania odnoszące się do ograniczeń, jakie występują w samym programie. Jedną z głównych obaw, o których mówią menedżerowie, jest strach przed analizą dużych zbiorów danych w Excelu (powyżej 1 GB). Użytkownicy programu, bazując na swoich doświadczeniach, opisują historię skoroszytów, które zawieszały się np. podczas prac związanych z odświeżaniem lub otwieraniem dużych zbiorów danych.

Na tym etapie warto zdiagnozować, czy wszystkie kłopoty, jakich doświadczają użytkownicy, wynikają z ograniczeń cechujących program, czy też część z nich można wytłumaczyć nie dość dobrymi schematami posługiwania się samym programem. W niniejszym artykule chciałbym przedstawić kilka prostych rozwiązań usprawniających pracę w Excelu na dużych wolumenach danych.

Microsoft Excel w wersji od 2010 r. dysponuje 1 048 576 wierszami i 16 384 kolumnami. Jeżeli w wersji z 2007 r. zapiszemy plik z rozszerzeniem xlsx, parametry te będą takie same. Dodatkowo możemy wyczytać, że jeśli dane, które przetwarzamy, dodamy do Excela jako model danych (Power Pivot), to w Excelu w wersji 32-bitowej będziemy dysponowali dwugigabajtową wirtualną przestrzenią adresową, współdzieloną przez program Excel, skoroszyty i inne dodatki Excela uruchomione w tym samym procesie. Udział modelu danych w tym zakresie mieści się w przedziale 500–700 MB, z możliwym zmniejszeniem zakresu, jeżeli załadowane są inne modele danych. Jeśli dysponujemy 64-bitową wersją programu, twarde ograniczenia dla wielkości pliku nie są z góry określone. Zakłada się, że rozmiar skoroszytu w tym wypadku limituje tylko dostępna pamięć systemowa i zasoby komputera. Tak przyjęte rozwiązanie pozwala już na analizę dziesiątków milionów wierszy bez uszczerbku na płynności działania programu.

Nowe rozwiązania

Znając ograniczenia, jakie występują w programie, warto przyjrzeć się rozwiązaniom stosowanym przez użytkowników pracujących na dużych zbiorach danych. Najczęstszy błąd pojawiający się przy ich analizie jest związany z formą zapisu, z którego czerpiemy dane.

Niejednokrotnie w firmach można zobaczyć „napuchnięte” do granic możliwości pliki Excela, w których źródłem danych są obszerne tabele zaszyte w programie. Przedstawione rozwiązanie poważnie zwiększa wagę pliku, znacząco opóźniając wszystkie czynności wykonywane w skoroszycie. Dla przykładu rozmiar pliku z danymi zapisanymi w programie w postaci obszernej tabeli (1 048 576 wierszy i 13 kolumn), bez tworzenia na jej podstawie tabeli przestawnej, wynosi 125 142 KB, podczas gdy ten sam rezultat zapisany w formie zapytania zmniejsza rozmiar pliku do 13 KB. Warto zatem pamiętać, aby źródło danych, z którego chcemy korzystać, zapisywać jako zapytanie poprzez dodatek Power Query, a nie jako tabelę zaszytą w pliku.

Dostęp możliwy dla zalogowanych użytkowników serwisu. Jeśli posiadasz aktywną prenumeratę przejdź do LOGOWANIA. Jeśli nie jesteś jeszcze naszym Czytelnikiem wybierz najkorzystniejszy WARIANT PRENUMERATY.

Zaloguj Zamów prenumeratę
Drukuj

Zobacz również

Archiwum

Polecamy