Praca z dużymi zbiorami danych w MS Excel
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.
Wykorzystałeś swój limit bezpłatnych treści
Pozostałe 62% artykułu dostępne jest dla zalogowanych użytkowników portalu. Zaloguj się, wybierz plan abonamentowy albo kup dostęp do artykułu/dokumentu.
Zaloguj się
